/*********************************************************************************
 *      Copyright:  (C) 2012 Guo Wenxue<guowenxue@gmail.com>  
 *                  All rights reserved.
 *
 *       Filename:  sqlite_test.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(11/29/2012~)
 *         Author:  Guo Wenxue <guowenxue@gmail.com>
 *      ChangeLog:  1, Release initial version on "11/29/2012 10:09:29 AM"
 *                 
 ********************************************************************************/

#include <stdio.h>
#include <unistd.h>
#include <stdlib.h>
#include <string.h>

#include <sqlite3.h>

#define SQL_CMD_LEN     256

int data_callback(void * para, int n_column, char ** column_value, char ** column_name)
{
    char     *arg = (char *)para;
    int      i;

    printf("%s", arg);

    for(i=0; i<n_column; i++)
    {
        printf("%4s: %s\n", column_name[i], column_value[i]);
    }

    return 0;
}

/********************************************************************************
 *  Description:
 *   Input Args:
 *  Output Args:
 * Return Value:
 ********************************************************************************/
int main (int argc, char **argv)
{
    int               rv;
    sqlite3           *db;
    char              *errmsg = NULL;
    char              sqlcmd[SQL_CMD_LEN];

    /* open the database.db, if it not exist then create it */
    if(SQLITE_OK != (rv=sqlite3_open("database.db", &db)) )
    {
        printf("Open/create database failed\n");
        return -1;
    }

    /* create a table if it doesn't exist  */
    strncpy(sqlcmd, "create table if not exists cplus(ID integer primary key autoincrement, name nvarchar(32), time not null default current_timestamp)", sizeof(sqlcmd));
    if(SQLITE_OK != (rv=sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg)) )
    {
        printf("create ID table failed [%d]: %s\n", rv, errmsg);
        sqlite3_free(errmsg);
    }

    //snprintf(sqlcmd, sizeof(sqlcmd), "insert into cplus(name) values('%s')", "guowenxue");
    snprintf(sqlcmd, sizeof(sqlcmd), "insert into cplus values(1, '%s', datetime('2012-01-01 20:35:10'))", "guowenxue");
    if(SQLITE_OK != (rv=sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg)) )
    {
        printf("Add item into cplus failed [%d]: %s\n", rv, errmsg);
        sqlite3_free(errmsg);
    }

    //snprintf(sqlcmd, sizeof(sqlcmd), "insert into cplus(name) values('%s')", "jason");
    snprintf(sqlcmd, sizeof(sqlcmd), "insert into cplus values(2, '%s', datetime('now'))", "jason");
    if(SQLITE_OK != (rv=sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg)) )
    {
        printf("Add item into cplus failed [%d]: %s\n", rv, errmsg);
        sqlite3_free(errmsg);
    }

    //snprintf(sqlcmd, sizeof(sqlcmd), "insert into cplus(name) values('%s')", "adrian");
    snprintf(sqlcmd, sizeof(sqlcmd), "insert into cplus values(3, '%s', datetime('now'))", "adrian");
    if(SQLITE_OK != (rv=sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg)) )
    {
        printf("Add item into cplus failed [%d]: %s\n", rv, errmsg);
        sqlite3_free(errmsg);
    }

    strncpy(sqlcmd, "select * from cplus", sizeof(sqlcmd));
    if(SQLITE_OK != (rv=sqlite3_exec(db, sqlcmd, data_callback, "\n==== Query all the items ====\n", &errmsg)) )
    {
        printf("Query all items from cplus failed [%d]: %s\n", rv, errmsg);
        sqlite3_free(errmsg);
    }

    strncpy(sqlcmd, "select * from cplus where name='guowenxue'", sizeof(sqlcmd));
    if(SQLITE_OK != (rv=sqlite3_exec(db, sqlcmd, data_callback, "\n==== Query all guowenxue ====\n", &errmsg)) )
    {
        printf("Query guowexue from cplus failed [%d]: %s\n", rv, errmsg);
        sqlite3_free(errmsg);
    }

    printf("\n==== delete all guowenxue in the table ====\n");
    strncpy(sqlcmd, "delete from cplus where name='guowenxue'", sizeof(sqlcmd));
    if(SQLITE_OK != (rv=sqlite3_exec(db, sqlcmd, NULL, NULL, &errmsg)) )
    {
        printf("Delete guowenxue from cplus failed [%d]: %s\n", rv, errmsg);
        sqlite3_free(errmsg);
    }

    strncpy(sqlcmd, "select * from cplus", sizeof(sqlcmd));
    if(SQLITE_OK != (rv=sqlite3_exec(db, sqlcmd, data_callback, "\n==== Query all the items ====\n", &errmsg)) )
    {
        printf("Query all items from cplus failed [%d]: %s\n", rv, errmsg);
        sqlite3_free(errmsg);
    }

    {
        char   **result;
        int    rows, cols;
        int    i, j, index;

        strncpy(sqlcmd, "select * from cplus", sizeof(sqlcmd));
        if(SQLITE_OK != (rv=sqlite3_get_table(db, sqlcmd, &result, &rows, &cols, &errmsg)) )
        {
            printf("%s command failed: %s\n", sqlcmd, errmsg);
        }

        printf("rows:%d cols:%d\n", rows, cols);

        index = cols;

        for(i=0; i<rows; i++)
        {
            for(j=0; j<cols; j++)
            {
                printf("KeyName: %4s  KeyValue: %s\n", result[j], result[index]);
                index ++;
            }
        }

        sqlite3_free_table(result);
    }

cleanup:
    sqlite3_close(db);
    return 0;
} /* ----- End of main() ----- */