sqlite3 memo 2014.08.21

//=== examples
sqlite3 ex3.db  "select * from invoice;"  > op1.txt
sqlite3 ex2.db   ip2.sql >  op2.txt
sqlite3 ex1.db  .dump  > ex1.sql


//=== sqlite3 命令列 (sqlite command line shell)

http://www.sqlite.org/cli.html
http://www.sqlite.org/download.html

下載 Precompiled Binaries for Windows
http://www.sqlite.org/2014/sqlite-shell-win32-x86-3080600.zip
解壓縮後得到 sqlite3.exe ,
為了方便, 把 sqlite3.exe 放到 python 的根目錄下



//=== create a new empty database file ex1
先進入 Git Bash
$

$ sqlite3 ex1
SQLite version 3.8.6. ...
Enter ".help" for usage hints.
sqlite> .q

$ ls ex1*
ex1


$ sqlite3 ex2.db
...
sqlite>.q
$ ls ex2*
ex2.db

//=== .q or .quit or .exit to escape sqlite cmd shell


//=== create a new table
$ sqlite3 ex1
sqlite> create table t1 (id Integer, name Text);
sqlite> create table t2 (id int, address text);
sqlite> .tables
t1 t2

sqlite> pragma table_info(t1);
0|id|Integer|0||0
1|name|Text|0||0


//=== insert record
sqlite> insert into t1 values(1, "name1");
sqlite> insert into t1 values(2, "name2");
sqlite> insert into t2 values(1, "addr1");
sqlite> insert into t2 values(2, "addr2");


???
sqlite> commit;
Error: cannot commit - no transaction is active


//=== open a database file
$ sqlite3 db_fname
or

$ sqlite3
sqlite> .open db_fnmae



//=== backup/dump a database or a table to a file
$ sqlite3 db_fname
sqlite> .output db.sql
sqlite> .dump

sqlite> .output t1.sql
sqlite> .dump t1

sqlite> .output t2.sql
sqlite> .dump t2

sqlite> .q
$ ls *.sql



//=== run sql script
$ sqlite3 db_fname "select * from t1;"
$ sqlite3 db_fname "insert into t1 values(3, 'name3');"
$ sqlite3 db_fname "pragma table_info(t1);"
$ sqlite3 db_fname < script_to_run.sql

//=== restore/import from a dump/backup.sql to another db file
 $ sqlite3 -init dbback.sql db_restored_fname


 //=== http://erikej.blogspot.com/2012/08/exporting-sql-server-compact-to-sqlite.html

export/dump/backup
$ sqlite3 ex1.db .dump > ex1_dump.sql

import/load/restore
$ sqlite3 ex1_restored.db < ex1_dump.sql


//=== encoding problem and other notes from erikej.blogspot.com
1. The dump file must be in UTF8 without BOM, i.e. plain utf-8 file

2. ALTER TABLE is limited, so constraints must be included in the CREATE TABLE statement

3. GO separator is not used, all statements must simply end with semicolon ;


???
7. SQLite binary data format: X’FFAABB’

8. not to include rowversion columns in the dump file ...




//===