Overview
Features
Download
Documentation
Community
Add-Ons & Services

code for Enabling and Calling Loadable Extension using POCO

A general discussion forum.

code for Enabling and Calling Loadable Extension using POCO

Postby vishal » 06 Jul 2012, 16:46

Hi,

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
vishal
 
Posts: 2
Joined: 06 Jul 2012, 16:26

Re: code for Enabling and Calling Loadable Extension using P

Postby alex » 06 Jul 2012, 20:50

There is no way to implement this in Poco. But you can use Poco::Data::SQLite::Session::getProperty(const std::string&) to obtain underlying SQLite db handle and use it to register your external function
Code: Select all
Session tmp (Poco::Data::SQLite::Connector::KEY, "dummy.db");
sqlite3* pDB = AnyCast<sqlite3*>(tmp.getProperty("handle"));
// now you can use pDB to call sqlite API functions on it
sqlite3_enable_load_extension(pDB, 1);
// ...

This feature is, however, only available in trunk and will be available in 1.5 release. See SQLiteTest::testInMemory() in the trunk for example on how to use the feature.

HTH

P.S. Pasting your entire code here is diminishing your chances of getting help. Instead, you should explain with snippets what you are trying to do or publish the code elsewhere (e.g. on your web site).
alex
 
Posts: 1154
Joined: 11 Jul 2006, 16:27
Location: United_States

Re: code for Enabling and Calling Loadable Extension using P

Postby vishal » 11 Jul 2012, 10:10

Thanks Alex for your valuable input.

I am using Poco 1.4.3, so Is their any way to get the underlying database from POCO session in 1.4.3?

I tried getting the SessionImpl* from Session through Session::impl() interface, and this gives me Poco/Data/SessionImpl class object pointer, but I can not get underlying database handle from this Poco/Data/SessionImpl object pointer.

However their is one more SessionImpl class in Poco/Data/SQLite and this class has inline function
inline sqlite3* SessionImpl::db()
{
return _pDB;
}

but the problem is I do not get the pointer of Poco::Data::SQLite::SessionImpl class from the Session::impl() function and hence could not get the underlying database handle.

Once I get the underlying sqlite database handle from the POCO Session in version 1.4.3, I can go ahead with the loadable extension feature.

Your help is really appreciated.

Thanks,
Vishal
vishal
 
Posts: 2
Joined: 06 Jul 2012, 16:26

Re: code for Enabling and Calling Loadable Extension using P

Postby alex » 11 Jul 2012, 15:58

vishal wrote:but the problem is I do not get the pointer of Poco::Data::SQLite::SessionImpl class from the Session::impl() function and hence could not get the underlying database handle.

You can dynamic_cast it to the type you need:
Code: Select all
Session session("SQLite", "sample.db");
Poco::Data::SQLite::SessionImpl* pDB = dynamic_cast<Poco::Data::SQLite::SessionImpl*>(session.impl());
alex
 
Posts: 1154
Joined: 11 Jul 2006, 16:27
Location: United_States


Return to General Discussion

Who is online

Users browsing this forum: No registered users and 1 guest