#####################################
# SQLITE3 INPUT COMMANDS CHEATSHEET #
#####################################

######### dot commands ############

# dump all of the database
sqlite3 file1.db .dump

# see tables
sqlite3 file1.db .table

######### sql commands ############

# run one command (to select all from a table called $table1)
# Note needed to escape the $ if you do " ' ' "
sqlite3 file1.db "select * from '\$table1';"
# No need to escape the $ if you do ' " " ' (because bash doesnt expand $ within '')
sqlite3 file1.db 'select * from "$table1";'

# regular tables name
sqlite3 file1.db 'select * from table2;'
sqlite3 file1.db "select * from table2;"

# You can essentially stack these sql commands:
sqlite3 file1.db "select * from table2; select * from table3;"
sqlite3 file1.db "select * from '\$table1'; select * from table3;"

######### echo ############

# you can also echo in commands - echo dot commands
echo ".dump" | sqlite3 file1.db 
echo ".table" | sqlite3 file1.db 

# echo sql commands
echo "select * from '\$table1';" | sqlite3 file1.db 
echo 'select * from "$table1";' | sqlite3 file1.db 
echo 'select * from table2;' | sqlite3 file1.db 
echo "select * from table2;" | sqlite3 file1.db 

######### echo -e ############

# What about sqlite . commands with sql commands - then you need to echo them in with -e. dot commands need to be on a new line, so we need to emulate a new line with echo -e (using \n in the string). -e with echo allows echo to take in special characters such as \n for newline (or \t for tab, but we dont use the tab)

# Lets say you want to do the following 2 commands:
# .header on (which shows column names)
# select * from table2 (which shows the output of tables2)
# Since dot command need to end with a newline, our echo needs to be able to do that. Thats why we use a "echo -e" which we can specify special commands thru.

# rule1: after every dot command put a \n
# rule2: if not the first command, then put \n before every dot command
# rule3: dont forget to end sql commands with ;

# tip: just run the echo by itself to make sure each dot command is on its own line.

# syntax is:
echo -e "<.dotcommand>\n<sqlcommand1>;<sqlcommand2>;\n<.dotcommand>\n<sqlcommand3>;" | sqlite3 file.db

# simple example:
echo -e ".header on\nselect * from table2;" | sqlite3 file1.db 

# if we were to just run the echo command, you would see this (which helps understand it):
echo -e ".header on\nselect * from table2;"
# OUTPUT:
# .header on
# select * from table2;

# more chained complex command:
echo -e ".header on\nselect * from table2;select * from '\$table1';\n.header off\nselect * from '\$table4';" | sqlite3 file1.db

# running the echo by itself:
echo -e ".header on\nselect * from table2;select * from '\$table1';\n.header off\nselect * from '\$table4';"
# OUTPUT:
# .header on
# select * from table2;select * from '$table1';
# .header off
# select * from '$table4';