Multiple commands

The command line utility sqlite is installed on all of the CS Department lab machines so that you can interface with your databases. Because SQLite stores a database as a file, you will need to run the command with the name of the file, e.g., sqlite <filename>. Once sqlite starts you can enter all your SQL commands and it will run them using that file as the database.

Dot commands

sqlite uses "dot commands" to tell you about your database. The dot commands all start with a "."--hence the name. The most useful dot command is ".help" because it prints a list of all the dot commands understood by sqlite. Another valuable command is .quit because it exits the sqlite program.

Single commands

The sqlite program also allows you to run single commands on the command line. You do this by placing the SQL command in quotes after the invocation of the sqlite database. For example, if I wanted to select all the rows from a table called friends in the mydb.db file, I would run this command: sqlite mydb.db "SELECT * FROM friends;". The output can be formatted in different ways, run sqlite -help to see the different output methods.

You can also run dot commands from the command line to learn more about your database. For example, to see all the tables in my database I could run sqlite mydb.db ".tables". To list all of the dot commands I could run sqlite mydb.db ".help".

Enabling SQLite in your PHP script

Because SQLite support is not a standard php module, you will need to load support for the module in the pages which you want to access your database. Adding these lines at the top of your script will take care of loading the module:

if (!extension_loaded('sqlite')) {
    dl('sqlite.so');
}

A sample session

This example will print out all the rows in a table called users that has this schema:

CREATE TABLE users (
    firstname VARCHAR(20),
    lastname VARCHAR(20),
    username VARCHAR(8)
);

Here is the php code to access that database:

<?php
if (!extension_loaded('sqlite')) {
    dl('sqlite.so');
}

$db = sqlite_open('test.sqlite', '666') or die ('Unable to open database!');

$result = sqlite_query('SELECT * FROM users', $db);

while ($row = sqlite_fetch_array($result, SQLITE_ASSOC)) {
    echo '<pre>\n';
    print_r($row);
    echo '</pre>\n';
}

sqlite_close($db);
?>

If you would like to execute any query that adds to the database (INSERT, CREATE) you will need to make the database file and the directory it resides in world writable. You can do this with these commands:

chmod 666 test.sqlite
chmod 777 .

Function reference

This is a very minimal reference for the functions provided by the SQLite module. For a more complete reference see the documentation at http://www.zend.com/manual/ref.sqlite.php

Basics

SQLite provides a flexible interface to its database engine using C. The basic steps are:

1. Include the SQLite library header file: #include <sqlite.h>
2. Declare a pointer of type sqlite that refers to the database sqlite *db;
3. Open the database using the sqlite_open function. You should pass it the path to the database, a mode integer which is always zero, and a pointer to a character array used to return error messages. This last argument can be NULL. You should assign the return value to the database pointer, sqlite_open will return NULL if there is an error. Here is an example: db=sqlite_open("~/db_dir/mydb.db", 0, NULL);
4. Perform your SQL operations on the open database. We will discuss this in detail in the following sections.
5. Close the database using the sqlite_close function, and pass it the database pointer: sqlite_close(db);
6. Compile the program using the SQLite linker flag -lsqlite: gcc myprog.c -o myprog -Wall -lsqlite

There are up to three different ways to perform any query on an SQLite database using the C interface: the sqlite_exec function, the sqlite_step function, or use a function from the extended API. The sqlite_exec function performs an SQL query, and then uses a callback function to return the results. The sqlite_step function steps row by row through the result of an SQL query without needing a callback function. The rest of the SQL API performs special purpose queries that are possible using either of the other methods, but are conveniently packaged as seperate function calls.

I recommend that you try each of your SQL statements on the command line using the sqlite utility before you embed them into your program. The following examples assume that the SQL statements work properly and omit any error checking.

Queries that return no results

Executing your SQL query is greatly simplified if you do not expect to receive any results. Inserts, deletes, and other queries that return no results only need two parameters passed to the sql_exec function, the last 3 parameters can be NULL. The first parameter is the pointer to the database, and the second parameter is the SQL query as a string literal or a character array.

These examples use a character array query of size ARRAY_SIZE. This example deletes all the rows from a test table in the database db:

//empty table
snprintf(query, ARRAY_SIZE, "DELETE FROM test");
sqlite_exec(db, query, NULL, NULL, NULL);

This example inserts one row into the test table in database db:

//insert into table
snprintf(query, ARRAY_SIZE, "INSERT INTO test VALUES (\"val1\", 1, \"val2\", 2)");
sqlite_exec(db, query, NULL, NULL, NULL);

Callback function example

If you want to manipulate the results returned from your query, you have to provide a callback function to sqlite_exec. sqlite_exec takes five paramaters:

int sqlite_exec(sqlite *db, char *query, *callback, void *args, char **err)

• The first argument is a pointer of type sqlite which refers to the opened database.
• The second argument is a character array or string literal containing one or more SQL queries. If this query string contains multiple SQL queries, then all queries will be executed before any results are returned.
• Third is a function pointer referring to your callback function. The callback function must accept parameters as listed below. To pass a function pointer, refer to the function name (no arguments) proceeded with an ampersand. The "&" is used to pass by reference.
• Fourth is a void pointer. This pointer is used to pass data as arguments to your callback function. It is helpful to allocate a struct containing the data you want to pass, cast it to a void * in the function call, and then cast it back to the data struct at the start of the callback function.
• The last argument is a reference to a character pointer which is used to return an error message.
• sqlite_exec returns SQLITE_OK, or an error.

The callback function is called from sqlite_exec once for each row returned from the query. Each time it is called the callback function receives four arguments as parameters:

``int callback(void *args, int numCols, char **results, char **columnNames)``

• The first argument is a void pointer that you pass as the fourth argument to sql_exec. It is passed untouched to your callback. This allows you to get necessary data to your callback function.
• The second argument is the number of columns in the result row that is returned.
• The third argument is an array of character strings containing the result of the query.
• The last argument is an array of character strings which contain the column names of the fields in the result and their datatypes.
• The callback function should return 0 on a successful exit. If it returns a non-zero value, SQLite will abort the query, and restore the database to its original state.

Be aware that SQLite will return NULL for a null value in the database. Fields in the database are automatically set to null if a row is created without a value being specified for each non-numeric column. Therefore, to avoid segmentation faults, you should check for NULL before you dereference any string returned to you from a query.

Here is some example code. It prints the results from a query to the screen:

// callback function for sqlite_exec
int my_callback(void *args, int numCols, char **results, char **columnNames){
    int i=0;
    for (i=0; i<numCols; i++){
        /* Do something with this field in the results; I guess we'll print it.*/
        printf("%s\t", results[i]);
    }
    printf("\n");
    return 0;
}

int main(int argc, char *argv[]){
/* We skip the code to declare variables and open the database */
//execute query
snprintf(query, QUERY_SIZE, "%s", argv[2]);
sqlite_exec(db, query, &my_callback, NULL, NULL);

/* We skip the rest of the code */

Step function example

Sometimes it is not convenient to have the callback function called for every row. If you need to manipulate multiple rows of data, then you should use the function sqlite_step. Using the step function requires you to compile a virtual machine for running your queries. The virtual machine reserves memory to store the results of your query until you have accessed them row by row using the step function. The pattern for using the step function, once the database is opened, consists of these steps:

# Declare a pointer to a virtual machine struct of type sqlite_vm: sqlite_vm *vm=NULL; # Execute an sqlite_compile statement once for each query. # Call the sqlite_step function once for each row of the result. # Execute sqlite_finalize to clean up all the memory used by the virtual machine. # Repeat the process until there are no more queries in the query string.

sqlite_compile takes five arguments:

``int sqlite_compile(sqlite *db, const char *query, const char **tail, sqlite_vm **vm, char **err)``

• The first argument is a pointer to an open database.
• The second argument is a character array or string literal containing the SQL queries. Only the first query in the string will be executed.
• The third argument is a reference to a pointer that will get set to the first character of the remaining queries in the query string. You don't need to allocate any memory for this pointer. When the last query is executed, the pointer is set to the '0' terminator at the end of the query string.
• The fourth argument is a reference to the pointer to the virtual machine. It will get set to point to a machine compiled with the results of the current query.
• The last argument is a reference to a character pointer used to return error messages.
• When successful, sqlite_compile returns SQLITE_OK.

The step function takes four arguments which are similiar to those required by a callback function:

``int sqlite_step(sqlite_vm *vm, int *numCols, const char ***results, const char ***columnNames)``

• The first argument is the pointer to the virtual machine compiled for this query.
• You should pass a reference to an integer as the second argument. This integer will be set to the number of columns in the result.
• The third argument should be a reference to an array of character arrays. It will be set to the current row resulting from the query, one column per entry. You don't need to any allocate memory.
• The last argument should be another reference to an array of character arrays. It will contain the column names and datatypes of the resulting row. Again, you don't need to allocate any memory.
• If sqlite_step has a row of results, then SQLITE_ROW is returned. If sqlite_step returns anything else, its third argument is set to NULL. When there are no more results to put into the third argument, SQLITE_DONE is returned. Otherwise, sqlite_step returns an error code.

sqlite_finalize cleans up all the memory used by the virtual machine while executing the query. It takes two arguments:

``int sqlite_finalize(sqlite_vm *vm, char **err)``

• The first argument is a pointer to the virtual machine that is to be cleaned up.
• The last argument is a pointer to a character array used to return error messages.
• sqlite_finalize returns SQLITE_OK, or an error code referring to the query as a whole.

The step function is called once for each row of data that you want returned. You need to compile a new virtual machine for each query. To execute every query in a multiple query stirng, you must loop until the tail variable is the string terminator.

If you call sqlite_finalize before sqlite_step has returned SQLITE_DONE, then it will interrupt the current operation and abort the query just as if a callback function had returned non-zero.

This example code prints the results from a single query to the screen:

int main(int argc, char *argv[]){
    const char *query_tail=NULL;
    sqlite_vm *vm=NULL;
    int numCols=0;
    const char **results=NULL;
    const char **columnNames=NULL;
    /* We skip the other variable declarations and the code to open the database */

    //execute query
    sqlite_compile(db, query, &query_tail, &vm, NULL);
    do {
        result=sqlite_step(vm, &numCols, &results, &columnNames);
        if (result==SQLITE_ROW){
            for (i=0; i<numCols; i++){
                /* Do something with this field in the results; I guess we'll print it.*/
                printf("%s\t", results[i]);
            }
            printf("\n");
        }
    } while (result==SQLITE_ROW);
    sqlite_finalize(vm, NULL);
    /* We skip the rest of the code */
}

Other functions

The SQLite C API includes some helper functions that simplify many operations. These functions can return the ROWID of the last row inserted, return the number of rows changed by the last operation, or test a string to see if it is a valid SQL statement. You can get more information about these functions in the official documentation for the SQLite C interface, found here: http://www.sqlite.org/c_interface.html

Error checking

The last variable passed to functions such as sqlite_open, sqlite_exec, sqlite_compile, sqlite_step, and sqlite_finalize is a reference to a character pointer. You don't have to allocate the memory used by this pointer; you can just set it to NULL. If after calling these functions the error pointer is not null, then you can read the error message to determine the problem. You should free the memory for the error message with free() when you are done with it.

Another way to detect errors is to compare the return values from sqlite_exec, sqlite_compile, and sqlite_finalize to SQLITE_OK. SQLITE_OK is a global variable located in sqlite.h. It signals a successful exit. If a return code from these functions does not equal SQLITE_OK, then you can get a description of the error from the error message pointer that you passed to the function. You can also compare the error to the list of error codes in the sqlite.h header file (found at /usr/include/sqlite.h) or to the list documented at [http://www.sqlite.org/c_interface.html sqlite.org].

sqlite_step returns either SQLITE_ROW or SQLITE_DONE on a successful exit. On an unsuccessful exit it returns either SQLITE_ERROR or SQLITE_MISUSE. Calling sqlite_finalize after receiving one of these codes will yield a more descriptive error.

Complete example programs

This example uses sql_exec and a callback function:

/*********************************
 * Executes a query on the database, then writes the results to a file.
 * Remember to compile the -lsqlite linker flag
 * *********************************************************/

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

struct Args {
    FILE *outfile;
    int first;
} args;

// callback function for sqlite_exec
int write_result(void *args, int numCols, char **results, char **columnNames){
    int i=0;
    struct Args *theargs=(struct Args *)args;

if (theargs->first) {
    for (i=0; i<numCols; i++){
        fprintf(theargs->outfile, "%s\t", columnNames[i]);
    }
    fprintf(theargs->outfile, "\n");
    theargs->first=0;
}
for (i=0; i<numCols; i++){
    fprintf(theargs->outfile, "%s\t", results[i]);
}
fprintf(theargs->outfile, "\n");
return 0;
}

int main(int argc, char *argv[]){
    FILE * outfile=NULL;
    const int QUERY_SIZE=150;
    char query[QUERY_SIZE];
    sqlite *db=NULL;
    char *errmsg=NULL;

    //check arguments
    if (argc != 4){
        printf("Usage: %s info.db \"Query\" dest.txt\n", argv[0]);
        printf("Query is standard SQL and can be multiple statements.\n");
        return -1;
    }

    //open database
    db=sqlite_open(argv[1], 0, errmsg);
    if (!db){
        fprintf(stderr, "Failed to open database %s. Error: %s\n", argv[1], errmsg);
        free(errmsg);
        return -2;
    }
    //open file
    outfile=fopen(argv[3], "w");
    if (!outfile){
        fprintf(stderr, "Failed to open input file %s\n", argv[3]);
        sqlite_close(db);
        return -3;
    }
    args.outfile=outfile;
    args.first=1;

    //execute query
    snprintf(query, QUERY_SIZE, "%s", argv[2]);
    sqlite_exec(db, query, &write_result, (void *) &args, &errmsg);
    if (errmsg) {
        printf("Error: %s\n", errmsg);
        free (errmsg);
    }

    //close everything
    sqlite_close(db);
    fclose(outfile);
    printf("Done\n");
    return 0;
}

This program does exactly what last program did, but it uses the step function instead of the callback:

/*********************************
 * Executes a query on the database, then writes the results to a file.
 * Remember to compile the -lsqlite linker flag
 * *********************************************************/

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

int main(int argc, char *argv[]){
    FILE * outfile=NULL;
    sqlite *db=NULL;
    const int QUERY_SIZE=150;
    char query[QUERY_SIZE];
    const char *query_tail=NULL;
    sqlite_vm *vm=NULL;
    char *errmsg=NULL;
    int result=0;
    int numCols=0;
    const char **results=NULL;
    const char **columnNames=NULL;
    int i=0;

    //check arguments
    if (argc != 4){
        printf("Usage: %s info.db \"Query\" dest.txt\n", argv[0]);
        printf("Query is standard SQL and can be multiple statements.\n");
        return -1;
    }

    //open database
    db=sqlite_open(argv[1], 0, errmsg);
    if (!db){
        fprintf(stderr, "Failed to open database %s. Error: %s\n", argv[1], errmsg);
        free(errmsg);
        return -2;
    }
    //open file
    outfile=fopen(argv[3], "w");
    if (!outfile){
        fprintf(stderr, "Failed to open input file %s\n", argv[3]);
        sqlite_close(db);
        return -3;
    }

    //execute query
    query_tail=query;
    snprintf(query, QUERY_SIZE, "%s", argv[2]);
    while (query_tail[0]!='\0') {
        result=sqlite_compile(db, query, &query_tail, &vm, &errmsg);
        if (result!=SQLITE_OK) {
            printf("Error: %s\n", errmsg);
            free (errmsg);
            exit (-4);
        }
        result=sqlite_step(vm, &numCols, &results, &columnNames);
        if (result==SQLITE_DONE || result==SQLITE_ROW){
            for (i=0; i<numCols; i++){
                fprintf(outfile, "%s\t", columnNames[i]);
            }
            fprintf(outfile, "\n");
            if (result==SQLITE_ROW){
                for (i=0; i<numCols; i++){
                    fprintf(outfile, "%s\t", results[i]);
                }
                fprintf(outfile, "\n");
            }
        }
        while (result==SQLITE_ROW){
            result=sqlite_step(vm, &numCols, &results, &columnNames);
            if (result==SQLITE_ROW){
                for (i=0; i<numCols; i++){
                    fprintf(outfile, "%s\t", results[i]);
                }
                fprintf(outfile, "\n");
            }
        }
        result=sqlite_finalize(vm, &errmsg);
        if (result!=SQLITE_OK) {
            printf("Error: %s\n", errmsg);
            free (errmsg);
            exit (-5);
        }
    }

    //close everything
    sqlite_close(db);
    fclose(outfile);
    printf("Done\n");
    return 0;
}