Sqlite: Difference between revisions
No edit summary |
No edit summary |
||
| (One intermediate revision by the same user not shown) | |||
| Line 4: | Line 4: | ||
Reusable\sqlite\SQLite_Static_Library | Reusable\sqlite\SQLite_Static_Library | ||
Reusable\sqlite\SQLite_Static_Library\ToDo.txt | Reusable\sqlite\SQLite_Static_Library\ToDo.txt | ||
The command line '''sqlite3''' is usable: | |||
.headers on | |||
.mode columns | |||
.tables | |||
.schema #tablename# | |||
#sql.... | |||
.quit | |||
There is also '''sqlitebrowser'''. | |||
{| class="mw-collapsible mw-collapsed wikitable" | |||
! Sqlite timestamp-to-readable-date query | |||
|- | |||
| | |||
select quote, timestamp, strftime('%Y-%m-%d %H:%M:%S', datetime(timestamp, 'unixepoch')) from StockQuotes as s where s.symbol="TSLA" order by timestamp; | |||
|} | |||
{| class="mw-collapsible mw-collapsed wikitable" | |||
! Sqlite copy row (pita) | |||
|- | |||
| Here's how you can clone a row in sqlite: | |||
CREATE TEMPORARY TABLE tmp AS SELECT * FROM StockPicks where id=1; | |||
UPDATE tmp SET id = NULL; | |||
INSERT INTO StockPicks SELECT * FROM tmp; | |||
DROP TABLE tmp; | |||
|} | |||
{| class="mw-collapsible mw-collapsed wikitable" | |||
! Sqlite table size analysis | |||
|- | |||
| | |||
sqlite db bloat found with sqlite3_analyzer, here: | |||
http://www.renedohmen.nl/blog/2014/05/determine-sqlite-table-size-on-disk/ | |||
http://www.sqlite.org/download.html | |||
(put it under viper ~/apps) | |||
ran it like this: | |||
cd ~/development/thedigitalage/AbetterTrader/server/db_archive | |||
~/apps/sqlite-tools/sqlite-tools-linux-x86-3220000/sqlite3_analyzer at_server_test.sqlite__2018-03-14_EOD__.sqlite | |||
|} | |||
Latest revision as of 01:35, 2 March 2019
I've built this into Windows apps, it's nice and small. See here for more:
Reusable\sqlite Reusable\sqlite\SQLite_Static_Library Reusable\sqlite\SQLite_Static_Library\ToDo.txt
The command line sqlite3 is usable:
.headers on .mode columns .tables .schema #tablename# #sql.... .quit
There is also sqlitebrowser.
| Sqlite timestamp-to-readable-date query |
|---|
select quote, timestamp, strftime('%Y-%m-%d %H:%M:%S', datetime(timestamp, 'unixepoch')) from StockQuotes as s where s.symbol="TSLA" order by timestamp;
|
| Sqlite copy row (pita) |
|---|
Here's how you can clone a row in sqlite:
CREATE TEMPORARY TABLE tmp AS SELECT * FROM StockPicks where id=1; UPDATE tmp SET id = NULL; INSERT INTO StockPicks SELECT * FROM tmp; DROP TABLE tmp; |
| Sqlite table size analysis |
|---|
sqlite db bloat found with sqlite3_analyzer, here:
http://www.renedohmen.nl/blog/2014/05/determine-sqlite-table-size-on-disk/
http://www.sqlite.org/download.html
(put it under viper ~/apps)
ran it like this:
cd ~/development/thedigitalage/AbetterTrader/server/db_archive
~/apps/sqlite-tools/sqlite-tools-linux-x86-3220000/sqlite3_analyzer at_server_test.sqlite__2018-03-14_EOD__.sqlite
|