Simple database for everyone

Photo by Franki Chamaki on Unsplash

The intention here is not to teach you SQL or make you a data engineer.

You just need to be aware that there is a simple tool which you quickly set up and start using if you need to collect your data in a structured form.

You can then come back to this article and get started.

Many times we develop a software or a script which is shared with rest of the team and maybe even outside. We might need to keep track of how many people are using it, how many times it is being used, what commands they are using, what time did they run it and so on.

We can create a simple log file and go on appending to it. Let’s say this is how my log looks like:

user, command, start_time, finish_time, success
user1, my_cmd, Sun Jan 19 15:43:34 2020, Sun Jan 19 15:45:34 2020, True
user2, my_cmd3, Sun Jan 19 15:49:34 2020, Sun Jan 19 15:49:54 2020, False
user1, my_cmd12, Sun Jan 19 15:53:34 2020, Sun Jan 19 15:55:34 2020, True
user4, my_cmd9, Sun Jan 19 16:43:34 2020, Sun Jan 19 16:45:39 2020, True
user2, my_cmd, Sun Jan 19 17:03:34 2020, Sun Jan 19 17:05:34 2020, False
user98, my_cmd, Sun Jan 19 18:43:00 2020, Sun Jan 19 18:48:04 2020, True

Now you have hundreds of such entries and you need to prepare some report showing the usage of your script to your team.

How would you get these insights from it?

user1 used the script 50 times

my_cmd1 was the most popular command with 649 usage

45 users didn’t have successful exit

Sunday was the busiest day of the week

user98 had the least success with his usage

Long ago, I would have simply started writing a perl or python script to read this csv file and would have come up with a very complicated flow which only worked for this specific log.

When the time comes to create a new log, I would have to repeat the same efforts again. And after a few times, I would have simply stopped using the user log because it is so much work. That would result in missing these insights which are very handy.

There is solution to this problem which is very very easy to implement and use. It is called “sqlite”

From their website:

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day

Simply put, it is a way to create your own database without having to worry about a whole lot of things Database engineers worry about.

All we want is a simple and consistent way to storing and retrieving data our team cares about. And as a hardware engineer, we won’t be using a lot of fancy features they provide anyway.

Don’t worry. I am going to show everything step by step. It is okay if you don’t get all of this right away. It took me some time as well.

To get started, there are two things we need to understand.

A database is like an excel workbook, which is not very useful until you have a worksheet in there. You can have multiple worksheets in the same workbook, which may or may not be related to each other.

Here, the workbook is equivalent to “database” and a worksheet is equivalent to a “table”.

So when you create a “database”, it is not very useful unless you add a “table” to it. You can think of a table as a structure made of rows and columns. The columns have headers. Each row is called an entry.

Below is a simple representation of a table using our data above.

table example

Now, it’s time to get our hands dirty.

To create a database, you would simply run this command:

sqlite3 my_tiny.db

This will create a database with the name my_tiny.db and you will be inside sqlite shell. It will look like this

Now here you can enter commands to create a new table, insert entries into that table, print from the table and do a lot many things.

But the first command we are going to try is to exit from this shell.

Simply type “.quit” and press enter and you will be back in your terminal.

Now, if you try to “ls” there and see if we created a database or not, you won’t see anything. Because we didn’t add tables to it yet so there is nothing there to show.

So let’s go ahead and try to create a table this time.

>>sqlite3 my_tiny.db
SQLite version 3.6.13
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>create table my_tiny_table (
       ID INT PRIMARY KEY NOT NULL,
       PROJECT TEXT NOT NULL,
       USER TEXT NOT NULL);

Here, we are asking sqlite to create a table with the name “my_tiny_table” with three headers (columns), “ID”, “PROJECT” and “USER”.

We are also telling it that the ID is of data type INT and that will be our primary key. PROJECT and USER will be of type TEXT. All of them will be NOT NULL means they will always have a value.

Primary key can be used to uniquely identify an entry. Don’t worry about it too much if you don’t get the use of it.

Now we have successfully created a table.

Let’s see if we can see it. Run “.tables”command

sqlite> .tables
my_tiny_table

and it will print the table name there.

Let’s add some entries to it.

sqlite> INSERT INTO my_tiny_table VALUES ('12345', "TEST_PROJECT", "TEST_USER");
sqlite> INSERT INTO my_tiny_table VALUES ('12346', "TEST_PROJECT1", "TEST_USER2");
sqlite> INSERT INTO my_tiny_table VALUES ('12347', "TEST_PROJECT2", "TEST_USER3");

Now we should be able to see those 3 entries like this:

sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM my_tiny_table;
ID          PROJECT       USER
----------  ------------  ----------
12345       TEST_PROJECT  TEST_USER
12346       TEST_PROJECT  TEST_USER2
12347       TEST_PROJECT  TEST_USER3

first two commands are just telling the sqlite shell to print it in nice table format. (use .help for more such commands)

And the SELECT command is printing everything from my_tiny_table.

This is pretty much it. Now we almost never need to start the sqlite shell to make or print entries from the table.

Exit the shell with .quit command

Now we will try to make an entry from the command line and print all the entries. This will be the mode of usage for us. Because we will be inserting entries from our main script.

>>sqlite3 my_tiny.db "INSERT INTO my_tiny_table VALUES ('1111', 'TEST_PROJECT1111', 'TEST_USER1111');"
>>sqlite3 my_tiny.db "SELECT * from my_tiny_table;"
12345|TEST_PROJECT|TEST_USER
12346|TEST_PROJECT1|TEST_USER2
12347|TEST_PROJECT2|TEST_USER3
1111|TEST_PROJECT1111|TEST_USER1111

The first command inserts an entry into the database with those values and the second command prints all the entries from the table.

Now let’s say we want to see entries of only TEST_USER2

>>sqlite3 my_tiny.db "SELECT * FROM my_tiny_table WHERE USER = 'TEST_USER2';"
12346|TEST_PROJECT1|TEST_USER2

BTW, all the commands are case insensitive, so “SELECT” is same as “select”.

And to count all the entries we made so far

>>sqlite3 my_tiny.db "SELECT COUNT(*) FROM my_tiny_table;"
4

TADA!!

You now know enough to get started and show off.

These are some more example commands

sqlite commands

To use sqlite from your perl or python scripts you can either import the packages or simply run system commands to insert entries or count entries.

For more details check out these links

Command Line Shell For SQLite

sqlite3 - Unix, Linux Command

*Non-interactive* SQLite3 usage from bash script

SQLite examples with Bash, Perl and Python

As always, please let me know if you have any questions!