Sqlite: Difference between revisions

From Bitpost wiki
(New page: see here for more: Reusable\sqlite Reusable\sqlite\SQLite_Static_Library Reusable\sqlite\SQLite_Static_Library\ToDo.txt)
 
No edit summary
 
(2 intermediate revisions by the same user not shown)
Line 1: Line 1:
see here for more:
I've built this into Windows apps, it's nice and small.  See here for more:


  Reusable\sqlite
  Reusable\sqlite
  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