SQLite3 multithreaded example

README

A little test of sqlite3 thread safety across multiple processes.

Ensure sqlite is built with:
#define SQLITE_THREADSAFE 1

poll.c will create database and table if necessary and start polling for inserts.

writer.c will insert the current time into the db once every second.

TimerQueue code borrowed from:
http://msdn.microsoft.com/en-us/library/ms687003(v=vs.85).aspx

poll.c

#include <stdio.h>
#include <Windows.h>
#include <time.h>
#include <sqlite3.h>

/* Poll database every n seconds */
HANDLE gDoneEvent;

VOID CALLBACK poll(PVOID lpParam, BOOLEAN TimerOrWaitFired)
{
	__time64_t ltime;
	sqlite3 *db;
	sqlite3_stmt *stmt;
	int result = SQLITE_OK;

	db = (sqlite3 *) *((int *) lpParam);

	_time64(&ltime);

	sqlite3_prepare_v2(db, "select max(time) from time", -1, &stmt, NULL);

	for (result = sqlite3_step(stmt); result == SQLITE_ROW; result = sqlite3_step(stmt))
	{
		sqlite3_int64 found_time;
		found_time = sqlite3_column_int64(stmt, 0);
		printf("Found time: %ld\n", found_time);
	}

	sqlite3_finalize(stmt);

    //SetEvent(gDoneEvent);
}

int main(void)
{
	HANDLE hTimer = NULL;
    HANDLE hTimerQueue = NULL;

	sqlite3 *db;
	sqlite3_stmt *stmt;
	int table_exists = 0;

	printf("Creating database time.db\n");	
	sqlite3_open("time.db", &db);

	if (db == NULL)
	{
		printf("Failed to open DB\n");
		return 1;
	}

	sqlite3_prepare_v2(db, "SELECT name FROM sqlite_master WHERE type='table' AND name='time'", -1, &stmt, NULL);
	if (sqlite3_step(stmt) == SQLITE_ROW)
	{
		printf("table: '%s' exists.\n", sqlite3_column_text(stmt, 0));
		table_exists = 1;
	}
	sqlite3_finalize(stmt);

	if (!table_exists)
	{
		printf("Creating table 'time'.\n");
		sqlite3_prepare_v2(db, "create table time(time INTEGER)", -1, &stmt, NULL);
		if (sqlite3_step(stmt) != SQLITE_DONE)
		{
			printf("Failed to create table 'time'.\n");
			return 1;
		}
		sqlite3_finalize(stmt);
	}

	_tzset();

	// Use an event object to track the TimerRoutine execution
    gDoneEvent = CreateEvent(NULL, TRUE, FALSE, NULL);
    if (NULL == gDoneEvent)
    {
        printf("CreateEvent failed (%d)\n", GetLastError());
        return 1;
    }

	// Create the timer queue.
    hTimerQueue = CreateTimerQueue();
    if (NULL == hTimerQueue)
    {
        printf("CreateTimerQueue failed (%d)\n", GetLastError());
        return 2;
    }

    // Set a timer to call the timer routine in 1 seconds.
    if (!CreateTimerQueueTimer(
		&hTimer,
		hTimerQueue, 
        (WAITORTIMERCALLBACK)poll,
		&db,
		1000,
		1000,
		0))
    {
        printf("CreateTimerQueueTimer failed (%d)\n", GetLastError());
        return 3;
    }

	// Wait for the timer-queue thread to complete using an event 
    // object. The thread will signal the event at that time.

    if (WaitForSingleObject(gDoneEvent, INFINITE) != WAIT_OBJECT_0)
        printf("WaitForSingleObject failed (%d)\n", GetLastError());

    CloseHandle(gDoneEvent);

    // Delete all timers in the timer queue.
    if (!DeleteTimerQueue(hTimerQueue))
        printf("DeleteTimerQueue failed (%d)\n", GetLastError());
	
	/*
	printf("Got results:\n");
	while (sqlite3_step(stmt) != SQLITE_DONE) {
		int i;
		int num_cols = sqlite3_column_count(stmt);
		
		for (i = 0; i < num_cols; i++)
		{
			switch (sqlite3_column_type(stmt, i))
			{
			case (SQLITE3_TEXT):
				printf("%s, ", sqlite3_column_text(stmt, i));
				break;
			case (SQLITE_INTEGER):
				printf("%d, ", sqlite3_column_int(stmt, i));
				break;
			case (SQLITE_FLOAT):
				printf("%g, ", sqlite3_column_double(stmt, i));
				break;
			default:
				break;
			}
		}
		printf("\n");

	}
	*/

	sqlite3_finalize(stmt);
	sqlite3_close(db);

	return 0;
}

writer.c

#include <stdio.h>

#include <Windows.h>
#include <time.h>

#include <sqlite3.h>

/* Insert rows into the table periodically */

HANDLE gDoneEvent;

VOID CALLBACK perform_insert(PVOID lpParam, BOOLEAN TimerOrWaitFired)
{
	__time64_t ltime;
	sqlite3 *db = NULL;
	sqlite3_stmt *stmt = NULL;
	int result;
	char query[256];

	_time64(&ltime);
	db = (sqlite3 *) *((int *) lpParam);

	printf("time: %ld\n", ltime);
	sprintf_s(query, 256, "insert into time values(%ld)", ltime);
	sqlite3_prepare_v2(db, query, strlen(query) + 1, &stmt, NULL);
	
	result = sqlite3_step(stmt);
	if (result != SQLITE_DONE)
		printf("Failed to insert!\n");

	sqlite3_finalize(stmt);

    //SetEvent(gDoneEvent);
}

int main(void)
{
    HANDLE hTimer = NULL;
    HANDLE hTimerQueue = NULL;
	sqlite3 *db = NULL;
	sqlite3_stmt *stmt = NULL;

	printf("Opening database time.db...");
	sqlite3_open("time.db", &db);

	if (db == NULL)
	{
		printf("Failed to open DB\n");
		return 1;
	}
	printf(" done.\n");

	printf("Hit any key to begin inserts...\n");
	getc(stdin);

	_tzset();

    // Use an event object to track the TimerRoutine execution
    gDoneEvent = CreateEvent(NULL, TRUE, FALSE, NULL);
    if (NULL == gDoneEvent)
    {
        printf("CreateEvent failed (%d)\n", GetLastError());
        return 1;
    }

    // Create the timer queue.
    hTimerQueue = CreateTimerQueue();
    if (NULL == hTimerQueue)
    {
        printf("CreateTimerQueue failed (%d)\n", GetLastError());
        return 2;
    }

    // Set a timer to call the timer routine in 1 seconds.
    if (!CreateTimerQueueTimer(
		&hTimer,
		hTimerQueue, 
        (WAITORTIMERCALLBACK)perform_insert,
		&db,
		1000,
		1000,
		0))
    {
        printf("CreateTimerQueueTimer failed (%d)\n", GetLastError());
        return 3;
    }


    // Wait for the timer-queue thread to complete using an event 
    // object. The thread will signal the event at that time.

    if (WaitForSingleObject(gDoneEvent, INFINITE) != WAIT_OBJECT_0)
        printf("WaitForSingleObject failed (%d)\n", GetLastError());

    CloseHandle(gDoneEvent);

    // Delete all timers in the timer queue.
    if (!DeleteTimerQueue(hTimerQueue))
        printf("DeleteTimerQueue failed (%d)\n", GetLastError());

	sqlite3_finalize(stmt);
	sqlite3_close(db);

	return 0;
}