Getting used to SQLite

Introduction

Once upon a time, I happened to think SQLite was worth trying. Gave it a shot, but could not figure out where to start. Created some tables and quickly dropped the idea of using it anywhere, because I didn’t know where to go from there.

Recently, I have been a keen admirer of bash scripts. I even started a project (https://github.com/neppramod/spring-mvc) that creates spring 3, hibernate, mysql project using a config file to change almost every aspect of the project. Some day I might even create a separate bash script to run ls. In that project I quickly reached a wall when I could not figure out why the DAO was not working. I have figured out the problem (bloody open sessions) and have created fresh project. I will soon have to convert them to scripts and commit the changes. I might even write an article describing how to change different aspects of the project.

Until that time, we will give some love to sqlite.

SQLite Intro

If you look at http://www.sqlite.org/about.html you will find a line that says “Think of SQLite not as a replacement for Oracle but as a replacement for fopen()”. SQLite is just that. It creates one flat file in the filesystem (which you can view using strings) and writes everything on to it. You just create a file (database file) using some scripts, create tables and are ready to go. You distribute your application with this file and use it to store and access what ever data your application needs (I am not talking about database applications). Lets create a database with a table called todo.

$ sqlite3 todo.db

 sqlite> create table todo (id integer primary key, data text);
# ctrl + d to get out
$ strings todo.db

 SQLite format 3

 wtabletodotodo

 CREATE TABLE todo (id integer primary key, data text)

Using sqlite

Since we have created a table called todo we can load up todo.db as above and start tying our insert, select, delete commands. Here however, we are interested in doing so using our programming skills (or whatever you may want to call it).

If you want to create one entry however (I will let you do that) run following line inside sqlite.

$ sqlite3 todo.db
 sqlite> insert into todo (data) values ('Hello World');
 sqlite> select * from todo;
 1|Hello World

As you can see above, we get primary key auto increment because we said id was primary key. That’s the default behaviour of sqlite.

C Source Code to insert queries

============================
/**
 * simplesqlite3.c
 * open a database and execute a SQL string

$ gcc -o simplesqlite3 simplesqlite3.c -lsqlite3
$ ./simplesqlite3 test.db "create table notes (t text)"

$ ./simplesqlite3 test.db "insert into  notes (t) values ('
> This is some random
> stuff to add'
>);"

$ ./simplesqlite3 test.db "select * from notes"
 */

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
    int i;

    for (i = 0; i < argc; i++) {
        //printf("%s = %s", azColName[i], argv[i] ? argv[i] : "NULL");
        printf("%s ", argv[i] ? argv[i] : "NULL");
    }

    printf("\n");
    
    return 0;
}

int main(int argc, char **argv)
{
    sqlite3 *db;
    char *zErrMsg = 0;
    int rc;

    // Check if there are 3 arguments total (with name of program)
    if (argc != 3) {
        fprintf(stderr, "Usage : %s DATABASE SQL-STATEMENT\n", argv[0]);
        exit(1);
    }

    // Open the database db from file, returns 0 on no error
    rc = sqlite3_open(argv[1], &db);

    if (rc) {
        fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(1);
    }

    rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg);
    //printf("\n");

    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error : %s\n", zErrMsg);

        /* This will free zErrMsg if assigned */
        if (zErrMsg)
            free(zErrMsg);
    }

    sqlite3_close(db);

    return 0;
}
 ==========================
Compile the source code and execute our one more insert using this file

 $ gcc -o simplesqlite3 simplesqlite3.c -lsqlite3
 $ ./simplesqlite3 todo.db "insert into todo(data) values('This is something cool');"
 $ ./simplesqlite3 todo.db "select * from todo;"

Some bash magic

Lets create a new bash(or sh) script (todo.sh) that runs insert when we specify -a parameter

#!/bin/sh

# $0 is name of script
# $1 is first script
# $2 is second script
# And so on...
# ./todo.sh -a "You note"
echo "name of script is $0 $1 $2 $3"
echo "count : " $#

if [ $1 = '-a' ]; then
    echo "adding";
    $PWD/simplesqlite3 $PWD/todo.db "insert into todo (data) values ('$2');"
fi

Conclusion

You might have a question. Why did I choose bash over c. Because string handling in c is awful. Once you have your service (logic) setup you should most of the times use bash to write dirty fast to execute codes. How can you improve this bash script? There is some help listed on comments, on how to access arguments. You can create variables like DATA_DIRECTORY=”~/data”, and use the variable using $DATA_DIRECTORY. There needs to be a space on each side while writing clauses like if above, and it needs to have closing fi too. Apart from that bash is dead simple. Can you create a todo manager using other parameters like -d for delete, -l for list? Good luck.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s