In this post, I will document a couple of cases where one can quickly create an SQLite3 database by issuing commands in a Bash shell (the default terminal shell in Linux and Mac OS X).

Case 1: Create a database with a table for images containing rows/tuples with the image url and a caption for the image. The images are in a directory located in resources/img/gallery, and the database will be stored in a directory at resources/sql.

Creating the database

touch resources/sql/img.db.sql

The touch command is used to change (update) the given file’s timestamp, but if the file (in this case resources/sql/img.db.sql) does not exist it will be created. Now, to make things easier to fit and read on a single command line, I’ll create a Bash variable the stores the command to access the SQLite3 database.

db='sqlite3 resources/sql/img.db.sql'

Creating and populating the img table

echo "CREATE TABLE img (url TEXT, caption TEXT);" | $db

for x in `ls resources/img/gallery`; do
echo "INSERT INTO img (url,caption) VALUES ('resources/img/gallery/$x','Insert Caption...');" | $db
done

Now the table should be created and populated with the desired content (captions to be inserted at a later time).

echo "SELECT * FROM img;" | $db
resources/img/gallery/0001.jpg|Insert Caption...
resources/img/gallery/0002.jpg|Insert Caption...
resources/img/gallery/0004.jpg|Insert Caption...
resources/img/gallery/0006.jpg|Insert Caption...
... remaining output omitted ...

More cases to come…