szaydel
1/4/2018 - 2:52 PM

sqlite open/create db snippets

Small driver program meant to validate consistent creation of a file and sqlite database in that file. This was needed after observing issues with Golang, where certain applications apparently failed to start correctly when a database file was not already there and needed to be created.

sqlitetest.c
content_copyfile_download
#include <stdlib.h>
#include <stdio.h>
#include <unistd.h>
#include "sqlite3.h"
// name: sqlitetest
// Compiled with:
// /opt/gcc-6/bin/gcc -m64 -lsqlite3 test.c -o sqlitetest

int main( int argc, char **argv )
{
    char            *file = "/tmp/test.db"; /* default to db in /tmp */
    sqlite3         *db = NULL;
    sqlite3_stmt    *stmt = NULL;
    int             rc = 0;
    long            counter = 0;
    int             open_flags = (
    SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE |
    SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_URI
    );

    char *tbl_create = "CREATE TABLE IF NOT EXISTS testing ( "
        "a    TEXT    UNIQUE, "
        "b    TEXT    PRIMARY KEY NOT NULL, "
        "c    TEXT    NOT NULL REFERENCES pools (pool_guid) ON DELETE CASCADE,"
        "d    TEXT);" ;

    if ( argc > 1 )
        file = argv[1];
    
    size_t need = snprintf(NULL, 0, "file:%s", file);
    char  *uri = malloc(need+1);
    sprintf(uri, "file:%s", file);

    while ( 1 ) {
        sqlite3_initialize( );
        rc = sqlite3_open_v2( uri, &db, open_flags, NULL );
        if ( rc != SQLITE_OK) {
            printf("sqlite failed to do sqlite3_open_v2 with rc: %d\n", rc);
            sqlite3_close( db );
            exit( -1 );
        }

        rc = sqlite3_prepare_v2( db, tbl_create, -1, &stmt, NULL );

        if ( rc != SQLITE_OK) {
            printf("sqlite failed to do sqlite3_prepare_v2 with rc: %d\n", rc);
                exit( -1 );
        }

        rc = sqlite3_step( stmt );
        if ( rc != SQLITE_DONE ) {
            printf("sqlite failed to do sqlite3_step with rc: %d\n", rc);
                exit ( -1 );
        }

        sqlite3_finalize( stmt );
        sqlite3_close( db );
        sqlite3_shutdown( );

        rc = unlink(file);
        if ( rc != 0 ) {
            printf("sqlite failed to do sqlite3_prepare_v2 with rc: %d\n", rc);
            exit(rc);
        }
        db = NULL;
        stmt = NULL;
        
        counter++;
        if ((counter % 1000) == 0) {
            printf("loops: %ld\n", counter);
        }
    }

    free(uri);
    return 0;
}