I am new to POCO and I need the sample code or some start up of how to implement Loadable Extension feature of sqlite3 using POCO ?
I have implemented the loadable extension feature through sqlite3 from below link
http://www2.sqlite.org/cvstrac/wiki?p=L ... Extensions
In this implementation I have basicallt 2 files as below.
sqlite_notify.c file
/* sqlite_notify.c */
#include <stdio.h>
#include <string.h>
#include <time.h>
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1;
#include <stdlib.h>
static void sqlite_notify( sqlite3_context *ctx, int num_values, sqlite3_value **values )
{
time_t now;
char filename[256];
char *name = (char *)sqlite3_value_text( values[0] );
char *action = (char *)sqlite3_value_text( values[1] );
FILE *f;
strcpy(filename, "/tmp/im6000-");
strncat(filename, name, 256 - sizeof("/tmp/"));
time(&now);
f = fopen(filename, "w");
if (!f) {
fprintf(stderr, "Error cannot open %s\n", filename);
return;
}
printf("sql notify: %s %s %s", name, action, ctime(&now)); /* for debug only. comment out for production */
fprintf(f, "%s", action); /* don't need time, part of file stat */
fclose(f);
}
int sqlite_notify_init( sqlite3 *db, char **error, const sqlite3_api_routines *api )
{
SQLITE_EXTENSION_INIT2(api);
sqlite3_create_function( db, "notify", 2, SQLITE_UTF8, NULL, sqlite_notify, NULL, NULL );
return SQLITE_OK;
}
sqlite_notify.so shared library is created from this file
and main function of my test application is
/* api_test.c
simple test of sqlite3 C API using a loadable extension.
*/
#include <stdio.h>
#include <sqlite3.h>
int main(int argc, char** argv)
{
sqlite3 *db;
char *zErrMsg = 0;
int rc;
const char canned_stmt[] = "INSERT INTO Patient(first_name, last_name, dob, sexId, id_number, notes) VALUES('Linus', 'Torvalds', '1969-12-28', 1, '9999-9', NULL);";
rc = sqlite3_open("patient.db", &db);
if(rc) {
fprintf(stderr, "Can't open database: %s\n", "patient.db");
sqlite3_close(db);
return 1;
}
// calling these C functions directly to enable and load the sqlite_notify.so extension.
// instead, you could run 'SELECT enable_load_extension(); SELECT load_extension(...)
rc = sqlite3_enable_load_extension(db, 1);
if( rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
rc = sqlite3_load_extension(db, "sqlite_notify.so", "sqlite_notify_init", &zErrMsg); // may need to set LD_LIBRARY_PATH
if( rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
fprintf(stderr, "try setting LD_LIBRARY_PATH to point to directory containing 'sqlite_notify.so'\n");
sqlite3_free(zErrMsg);
}
// OK, now I can run
rc = sqlite3_exec(db, canned_stmt, NULL, 0, &zErrMsg);
if( rc != SQLITE_OK ) {
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
sqlite3_close(db);
return 0;
}
I have called the Notify function from the SQL Trigger when data is inserted into Patient, my database schema is as below.
patient_schema.sql:
PRAGMA foreign_keys = ON;
BEGIN TRANSACTION;
-- time_open, time_close: in YYYY-MM-DD HH:MM:SS format ISO8601
-- dob: in YYYY-MM-DD format ISO8601
-- id_number: may one day want to make this a unique field. not now though.
CREATE TABLE Patient(
id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT,
last_name TEXT NOT NULL,
dob TEXT,
sexID INTEGER,
id_number TEXT,
notes TEXT,
FOREIGN KEY(sexID) REFERENCES Sex(id)
);
CREATE TABLE Sex(
id INTEGER PRIMARY KEY AUTOINCREMENT,
gender TEXT NOT NULL
);
INSERT INTO "Sex" VALUES(1,'(None)');
INSERT INTO "Sex" VALUES(2,'Male');
INSERT INTO "Sex" VALUES(3,'Female');
-- create incrementing Version on insert, updates, deletes to the other tables.
CREATE TABLE Version (
ver INTEGER,
timestamp TEXT
);
-- TODO: Is there a way to ensure that there never is another insert on this table?
INSERT INTO Version VALUES ( 0, ( SELECT datetime() ) );
CREATE TABLE YesNo(
id INTEGER PRIMARY KEY AUTOINCREMENT,
YesNoValue TEXT NOT NULL
);
INSERT INTO "YesNo" VALUES(1, 'YES');
INSERT INTO "YesNo" VALUES(2, 'NO');
-- Patient triggers
CREATE TRIGGER Patient_insert AFTER INSERT on Patient
BEGIN
UPDATE Version SET ver = ver + 1;
UPDATE Version SET timestamp = (SELECT datetime());
SELECT notify("Patient", "INSERT");
END;
CREATE TRIGGER Patient_update AFTER UPDATE on Patient
BEGIN
UPDATE Version SET ver = ver + 1;
UPDATE Version SET timestamp = (SELECT datetime());
SELECT notify("Patient", "UPDATE");
END;
CREATE TRIGGER Patient_delete AFTER DELETE on Patient
BEGIN
UPDATE Version SET ver = ver + 1;
UPDATE Version SET timestamp = (SELECT datetime());
SELECT notify("Patient", "DELETE");
END;
-- simple trigger for insert on Sex table (not likely, but just to demo)
CREATE TRIGGER AFTER INSERT on Sex
BEGIN
UPDATE Version SET ver = ver + 1;
UPDATE Version SET timestamp = (SELECT datetime());
SELECT notify("Sex", "INSERT");
END;
COMMIT;
But I need the way through POCO to implement exactly same thing.
I have installed POCO 1.4.3 version on my system from its source code and in order to use system sqlite3 library in POCO I have configured the POCO with option --unbundled.
Plz help me out
.Thanks in advance,
Vishal