/*
 Compile:
   gcc -o sqlite_insert sqlite_insert.c -L/usr/local/sqlite-3.2.0/lib -lsqlite3 -I/usr/local/sqlite-3.2.0/include
*/
                                                                                
#include <sqlite3.h>
#include <stdio.h>
#include <stdlib.h>
#include <sys/types.h>
#include <sys/time.h>
                                                                                
int sql_begin(sqlite3 *db) { /* {{{1 */
    char *errmsg;
    if (sqlite3_exec(db, "BEGIN TRANSACTION",
                     NULL, NULL, &errmsg) != SQLITE_OK) {
        printf("couldn't begin transaction:  %s\n", errmsg);
        return 0;
    } else {
        return 1;
    }
} /* 1}}} */
int sql_commit(sqlite3 *db) { /* {{{1 */
    char *errmsg;
    if (sqlite3_exec(db, "COMMIT TRANSACTION",
                     NULL, NULL, &errmsg) != SQLITE_OK) {
        printf("couldn't commit transaction:  %s\n", errmsg);
        return 0;
    } else {
        return 1;
    }
} /* 1}}} */
float elapsed(struct timeval start, struct timeval end) { /* {{{1 */
    return  (float) (end.tv_sec  - start.tv_sec ) +
           ((float) (end.tv_usec - start.tv_usec)/1000000);
} /* 1}}} */
                                                                                
int main(int argc, char *argv[]) {
    sqlite3 *db;
    sqlite3_stmt *Stmt;
    const char *zLeftover;
    char *errmsg,
         *dbfile = "/tmp/a.db"
         /*
         *dbfile = ":memory:"
         */
         ;
#define CMD_SIZE 1000
    char  rm_command[CMD_SIZE];
    int    rc, i, N, xact_size, n_this_xact = 0;
    double x, y, z;
    float  delta_T;
    struct timeval start_time, end_time;
                                                                                
    if (argc < 3) {
        printf("\nUsage:   %s  <N>  <X>\n\n", argv[0]);
        printf("\tInsert <N> rows into a table of an SQLite database\n");
        printf("\tusing transaction sizes of <X>.\n");
        printf("\tThe table has four columns of numeric data:\n");
        printf("\t  field_1 integer primary key\n");
        printf("\t  field_2 float\n");
        printf("\t  field_3 float\n");
        printf("\t  field_4 float\n");
        printf("\tThe integer field will have values 1..<N> while the\n");
        printf("\tdouble precision values are random on [-50.0, 50.0]\n");
        exit(0);
    }
    N         = atoi(argv[1]);
    xact_size = atoi(argv[2]);
    snprintf(rm_command, CMD_SIZE-1, "rm -f %s", dbfile);
    system(rm_command);  /* the database file must not exist before
                            calling sqlite3_open() and trying to insert */
                                                                                
    gettimeofday(&start_time, 0);
                                                                                
    rc = sqlite3_open(dbfile, &db);
                                                                                
    sql_begin(db);
    rc = sqlite3_prepare(db, 
            "create table table_name(field_1 integer primary key,"
                                     "field_2 float,  "
                                     "field_3 float,  "
                                     "field_4 float)",
           -1, &Stmt, &zLeftover);
    rc = sqlite3_step(Stmt);
    rc = sqlite3_finalize(Stmt);
                                                                                
    rc = sqlite3_prepare(db,
           "insert into table_name values(?,?,?,?)",
           -1, &Stmt, &zLeftover);
                                                                                
    for (i = 1; i <= N; i++) {
        x  = 50.0 - (100.0*rand()/(RAND_MAX+1.0));
        y  = 50.0 - (100.0*rand()/(RAND_MAX+1.0));
        z  = 50.0 - (100.0*rand()/(RAND_MAX+1.0));
        rc = sqlite3_bind_int(   Stmt, 1, i);
        rc = sqlite3_bind_double(Stmt, 2, x);
        rc = sqlite3_bind_double(Stmt, 3, y);
        rc = sqlite3_bind_double(Stmt, 4, z);
        rc = sqlite3_step(Stmt);
        rc = sqlite3_reset(Stmt);
        ++n_this_xact;
        if (!(n_this_xact % xact_size)) {
            /* have done xact_size inserts, finish this transaction
             * and start a new one
             */
            n_this_xact = 0;
            sql_commit(db);
            sql_begin( db);
        }
    }
    sql_commit(db);
    sqlite3_close(db);

    gettimeofday(&end_time, 0);
    delta_T = elapsed(start_time, end_time);
    printf(" %d inserts to %s in %.3f s = %.2f inserts/s\n",
            N, dbfile, delta_T, N/delta_T);

    return 0;
}