Starting with SQLite

SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine. The primary benefits of using SQLite is that you can create a self-contained database in your application.

What is so great about this? Well, for starters you can:

• Manipulate data inside your program using standard SQL construct.

• Zero database configuration – but you get database-like capabilities for your program.

• Simple, easy to use API. You can actually use SQLite with just 3 main API.

• Self-contained: no external dependencies.

• Faster than popular client/server database engines for most common operations.

With SQLite, it is possible for you to distribute single application binary, with almost complete database and SQL querying capabilities. Compared to another embedded database such Berkeley DB, which offers just key and value assignment, SQLite is much more versatile and easy to use. You can just use all the SQL syntax that you have become familiar with to manipulate your data. The data itself is stored inside a single disk file on your local file-system, which can grow to a maximum of 2 terabytes (2⁴¹ bytes) in size. For more information, visit SQLite site.

Under Ubuntu, to install SQLite, issue the following command:

sudo apt-get install sqlite3 libsqlite3-dev libsqlite3-0 sqlite3-doc

Lets get started. For this tutorial, we will create a simple user name and password storage program, which we will call MyPass. This simple program allows you to associate domain with accompanying user name and password. The domain itself can be anything, such as “gmail.com”, “yahoo.com”, “ATM”, etc. The table structure is very simple, and is given below. This program is developed under Linux, but should works with little or no modification under Microsoft Windows.

Table 1 – mypass table structure

The flow of our program little is like this:

1. Start up

2. Check number of arguments. If no argument specified, terminate.

3. Open or create the database. If mypass table does not exist, create it.

4. Parse the argument, and exit if the argument is not.

5. If argument is “add”, then ask user for domain, username, and password. Execute SQL query to insert it into database.

6. If argument is “show”, then ask for domain. Search database using SQL construct and then return the result.

7. If argument is “delete”, then ask for domain. Delete the matching domain from database using SQL construct.

8. End of program.

Enough with theories, lets start coding. Given in the table below is the source code for mypass.c. The line number just serves as a reference, so do not copy it to your code editor!

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

#define PASS_DB "mypass.db" /* database file name */
#define MAX_LEN 100 /* maximum field length */

sqlite3* db; /* sqlite database handler */
char *errMsg = 0; /* pointer to error message */

/* Display result of a query */
static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
    int i = 0;

    /* Iterate throw result rows */
    for(i=0; i<argc; i++)
        printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
    printf ("\n");

    return (EXIT_SUCCESS);
}

/* Request username and password for domain, and add to database */
int do_add (int argc, char *argv[])
{
    char *domain, *username, *password, *query;
    int n = 0, ret = 0;

    /* Allocate memory */
    domain = calloc (1, MAX_LEN);
    username = calloc (1, MAX_LEN);
    password = calloc (1, MAX_LEN);
    query = calloc (1, MAX_LEN);

    /* Ask user input */
    printf ("Enter domain, e.g. mail.yahoo.com: ");
    fgets (domain, MAX_LEN, stdin);
    printf ("Enter user name: ");
    fgets (username, MAX_LEN, stdin);
    printf ("Enter password: ");
    fgets (password, MAX_LEN, stdin);

    /* remove trailing newline */
    domain[strlen(domain) - 1] = '\0';
    username[strlen(username) - 1] = '\0';
    password[strlen(password) - 1] = '\0';

    /* Insert to database */
    sqlite3_snprintf (MAX_LEN, query,
        "INSERT INTO mypass VALUES ('%q','%q','%q')",
        domain, username, password);
    printf ("query = %s\n", query);
    n = sqlite3_exec(db, query, callback, 0, &errMsg);

    /* Check return result */
    if ( n!= SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", errMsg);
        ret = EXIT_FAILURE;
    }
    else
        ret = EXIT_SUCCESS;

    /* Free allocated memory */
    free (domain);
    free (username);
    free (password);
    free (query);
    return (ret);
}

/* Show user name and password for requested domain */
int do_show (int argc, char *argv[])
{
    char *domain, *query;
    int n = 0, ret = 0;

    /* Allocate memory */
    domain = calloc (1, MAX_LEN);
    query = calloc (1, MAX_LEN);

    /* Request user input */
    printf ("Enter domain, e.g. mail.yahoo.com: ");
    fgets (domain, MAX_LEN, stdin);

    /* remove trailing newline */
    domain[strlen(domain) - 1] = '\0';

    /* Query from database */
    sqlite3_snprintf (MAX_LEN, query,
        "SELECT * FROM mypass WHERE domain='%q'",
        domain);
    printf ("query = %s\n", query);
    n = sqlite3_exec(db, query, callback, 0, &errMsg);

    /* Check return result */
    if ( n != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", errMsg);
        ret = EXIT_FAILURE;
    }
    else
        ret = EXIT_SUCCESS;

    /* Free allocated memory */
    free (domain);
    free (query);
    return (ret);
}

/* Delete user name and password from selected domain */
int do_delete (int argc, char *argv[])
{
    char *domain, *query;
    int n = 0, ret = 0;

    /* Allocate memory */
    domain = calloc (1, MAX_LEN);
    query = calloc (1, MAX_LEN);

    /* Request user input */
    printf ("Enter domain, e.g. mail.yahoo.com: ");
    fgets (domain, MAX_LEN, stdin);

    /* remove trailing newline */
    domain[strlen(domain) - 1] = '\0';

    /* Execute to database */
    sqlite3_snprintf (MAX_LEN, query,
        "DELETE FROM mypass WHERE domain='%q'",
        domain);
    printf ("query = %s\n", query);

    /* Check return result */
    n = sqlite3_exec(db, query, callback, 0, &errMsg);
    if ( n != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", errMsg);
        ret = EXIT_FAILURE;
    }
    else
        ret = EXIT_SUCCESS;

    /* Free allocated memory */
    free (domain);
    free (query);
    return (ret);
}

/* Open database, and create table if necessary */
int open_db ()
{
    int n = 0, exist = 0;
    FILE *fp;

    /* Check whether the database file existence, and flag it */
    if ((fp = fopen (PASS_DB, "r")) != NULL) {
        exist = -1;
        fclose (fp);
    }

    /* Open database file, or create it  */ 
    if (sqlite3_open (PASS_DB, &db)) {
        fprintf (stderr, "Can't open database: %s\n",
            sqlite3_errmsg (db));
        sqlite3_close (db);
        return (EXIT_FAILURE);
    }

    /* New database, so create required table */
    if (!exist) {
        n = sqlite3_exec(db,
            "CREATE TABLE mypass 
                (domain VARCHAR(100), username VARCHAR(100), 
                password VARCHAR(100))", 
            callback, 0, &errMsg);
        if ( n != SQLITE_OK) {
            fprintf(stderr, "SQL error: %s\n", errMsg);
            return (EXIT_FAILURE);
        }
        else
            return (EXIT_SUCCESS);
    }
    return (EXIT_SUCCESS);
}

/* Program entry point */
int main (1int argc, char *argv[])
{
    int ret = 0;
    printf ("MyPassword 1.0\n");

    /* Check number of arguments */
    if (argc < 2) {
        fprintf (stderr,
            "Neither 'add', 'show', or 'delete' were specified!\n");
        return (EXIT_FAILURE);
    }

    open_db ();

    /* Check arguments, and called selected function */
    if (strcasecmp (argv[1], "add") == 0)
        ret = do_add (argc, argv);
    else if (strcasecmp (argv[1], "show") == 0)
        ret = do_show (argc, argv);
    else if (strcasecmp (argv[1], "delete") == 0)
        ret = do_delete (argc, argv);
    else {
        fprintf (stderr,
            "Invalid operation specified, must be either
                'add', 'show', or 'delete'\n");
        ret = EXIT_FAILURE;
    }

    /* Close database and exit */
    sqlite3_close(db);
    return (ret);
}

Table 2 – Code for mypass.c

Compile the program using the following command:

gcc -o mypass mypass.c -lsqlite3

Example of the program output

bitubique@bitubique:~/projects/mypass$ ./mypass add
MyPassword 1.0
Enter domain, e.g. mail.yahoo.com: mail.yahoo.com
Enter user name: myusername
Enter password: mypassword
query = INSERT INTO mypass VALUES ('mail.yahoo.com','myusername','mypassword')

bitubique@bitubique:~/projects/mypass$ ./mypass show
MyPassword 1.0
Enter domain, e.g. mail.yahoo.com: mail.yahoo.com
query = SELECT * FROM mypass WHERE domain='mail.yahoo.com'
domain = mail.yahoo.com
username = myusername
password = mypassword

bitubique@bitubique:~/projects/mypass$ ./mypass delete
MyPassword 1.0
Enter domain, e.g. mail.yahoo.com: mail.yahoo.com
query = DELETE FROM mypass WHERE domain='mail.yahoo.com'

bitubique@bitubique:~/projects/mypass$ ./mypass show
MyPassword 1.0
Enter domain, e.g. mail.yahoo.com: mail.yahoo.com
query = SELECT * FROM mypass WHERE domain='mail.yahoo.com'

Lets go through the logical flow of the program.

• Line 1 to 12 include the necessary headers, define the database name, maximum field size, and declare database handle and error message pointer as global variables.

• Line 172 to 210 is our program entry point. First it checks whether any parameter is specified or not. Then it call the function that is responsible to open the database. Lastly, it checks the parameter for word “add”, “show”, or “delete”. If it encounters any of these words, control is then passed to respective functions. After executing the functions, it will close the database using sqlite3_close function.

• Line 147 to 178 is responsible to open the database file. It first try to verify whether the database file exist or not. If it does not exist, remember to create the table later on using the sqlite3_exec and passing the CREATE TABLE SQL command. Call to sqlite3_open will automatically create the database file if it does not exist yet.

• Line 13 to 25 is the callback function. We specify this callback function in our sqlite3_exec calls, and it will be invoked whenever we have any result returned, e.g. such as from SELECT statement.

• Line 26 to 72 consist of functions to add new entry to our database. What we do here is ask user for the domain, username, and password. Then, we compose the data entered into a proper SQL statement using call to sqlite3_snprintf. Remember to use %q instead of %s, as this properly escape character such as ' and “ in our SQL statement. Lastly, we execute our query using call to sqlite3_exec.

• Line 73 to 109 is the function to show our stored password. It asks user for the domain, and similar as above, SQL statement is constructed using sqlite3_snprintf. The SQL query is then executed as previous using sqlite3_exec. If there is a result to show, the function callback (line 13 – 25) is called to display the result.

• Line 110 to 146 is responsible to delete the stored user name and password. It asks user to input the domain, and and similar to above steps, call to database using SQL statement is then executed.

This is just a simple program highlighting the usage of SQLite. For more information, refer to the complete SQLite API.

Technorati Tags: C Programming SQLite Tutorial Tutorial