sqlite

8. 8. 2018

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.

bash to sqlite ↓

echo ".schema one" | sqlite3 "$HOME/tmp/sqtest.db"
# or use 2nd argument to sqlite as command
sqlite3 "$HOME/tmp/sqtest.db" ".schema one"

https://www.sqlite.org/cli.html

SQLite commands are normally terminated by a semicolon. In the shell you can also use the word “GO” (case-insensitive) or a slash character “/” on a line by itself to end a command.

Full example for creating table, inserting values and dumping entire table back to stdin

db="$HOME/tmp/sqtest.db"
rm "$db"
# Create single column db where column is named 'stuff'
sqlite3 "$db" "create table woot(stuff text) ;"
# where 'stuff' is the name of the column
sqlite3 "$db" "insert into woot values('mkay') ;"
sqlite3 "$db" "insert into woot values('drugs are bad') ;"
sqlite3 "$db" "insert into woot values('very long string with smilie :)') ;"

# show all
sqlite3 "$db" "select * from woot ;"

mkay
drugs are bad
very long string with smilie :)

# search for smilie
sqlite3 "$db" "select * from woot where stuff like '%:)%' ;"
very long string with smilie :)"

Inside the sqlite

# let's go into sqlite3 interpreter and add stuff to stuff
sqlite3 "$db" # and I'am in
insert into woot values ('another smilie :)') ;
.exit

http://www.dataversity.net/review-pros-cons-different-databases-relational-versus-non-relational/
https://www.amazon.com/dp/1430232250//ref=cm_sw_su_dp?tag=nethta-20

linux cli bash