Sqlite

From Bitpost wiki
Jump to navigation Jump to search

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