Re: [sqlite] Problem invoking php functions from a trigger
Yes, but how do I know in the PHP fired by the trigger what's the PHP
context of his parent?.
Let me explain it.
Inside the trigger I excute something like
select test();
where test is a registered php function.
When the trigger fires, the php function gets called, but the first thing I
need to have is a database handler. Right know, the first thing I do in
that test php is:
$dbh = new PDO('sqlite:mydb.sqlite');
so that creates a new (an different) context, then if I try to:
$res=$dbh-query(update set field=1 from same_table_that fired_the
trigger);
I run in a deadlock, the first trigger is locking the table, and I'm waiting
for that trigger to relese the lock. This will only end by time-out with the
error Database locked.
so, how can the php fired by a trigger knows whats the php sqlite context of
its caller??
thx again
2009/8/31 Kees Nuyt k.n...@zonnet.nl
On Mon, 31 Aug 2009 11:58:42 +0200, Alejandro Ruiz-Oriol
aruiz...@itelsys.com wrote:
I've been doing some further reading and I think that I already have the
answer and it's No, no way to avoid this.
As far as I've read, when you start any update function, SQLite makes an
exclusive write lock to the table. As far as I'm excuting from inside an
update trigger, the database is exclusivily write locked, so if that
trigger tries to write to the DB, we will end up in a deadlock.
The only thing that can save me from this behavior is if there is
someway
to tell SQLite that the update is over, before the trigger finished.
I've tried to put a commit inside the trigger, but I get a syntax error.
I'm working in to aporaches to the workarund. One i ssplit the table in
two
(due to the logic of my aplication, maybe I can do this, cause the files
updated by the trigger can have some kind of logic that I can use to split
the table in 2 different tables). The other is to start a background php
task that wait till the DB lock is released and then, tries the insert.
Any suggestion?
I think the registered callback function runs in the same
context as the php script that created the first conection,
and it should use that conenction, instead of creating a
second connection.
It's just a suggestion. I didn't try this myself.
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem invoking php functions from a trigger
On Tue, 1 Sep 2009 09:51:39 +0200, Alejandro Ruiz-Oriol
aruiz...@itelsys.com wrote:
Yes, but how do I know in the PHP fired by the trigger what's the PHP
context of his parent?.
Let me explain it.
Inside the trigger I excute something like
select test();
where test is a registered php function.
When the trigger fires, the php function gets called, but the first thing I
need to have is a database handler. Right know, the first thing I do in
that test php is:
$dbh = new PDO('sqlite:mydb.sqlite');
so that creates a new (an different) context, then if I try to:
$res=$dbh-query(update set field=1 from same_table_that fired_the
trigger);
I run in a deadlock, the first trigger is locking the table, and I'm waiting
for that trigger to relese the lock. This will only end by time-out with the
error Database locked.
so, how can the php fired by a trigger knows whats the php sqlite context of
its caller??
You have a php script. It opens a database handle and
registers a function. Then it executes some SQL code which
fires a trigger, which in his turn calls your registered
function.
All these nested calls execute in the same context, not
asynchronically. So the database handle you opened in the
top level is still valid when your registered function is
called. In other words, you don't have to open a second
handle.
Opening a second handle and starting a transaction causes
the lock.
How the function gets the handle is another matter.
If $dbh is a global variable you could simply use that.
All in all, it is quite complicated, probably too
complicated. Are you sure your registered function really
needs to use the database handle?
Can't you just use SQL in the trigger, and restrict the
function to what functions are useful for, like calculations
which can't be donein SQL or aggregation tasks?
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem invoking php functions from a trigger
Ok, I got it!!
In my php functions I use this code to connect to the DB (its in an include
file)
$dbh=$GLOBALS['dbh'];// Get Global Handle
if ($dbh==null)// First time? no Handle?
{
$dbh = new PDO('sqlite:/var/www/domocenter.sqlite'); // Create new
handle
$GLOBALS['dbh']; // Save it in globals
include_once(/var/www/arduino/sqlite_functions.php); // Include al
the functions needed
}
return($dbh); // Devover el handle a manejar.
2009/9/1 Kees Nuyt k.n...@zonnet.nl
On Tue, 1 Sep 2009 09:51:39 +0200, Alejandro Ruiz-Oriol
aruiz...@itelsys.com wrote:
Yes, but how do I know in the PHP fired by the trigger what's the PHP
context of his parent?.
Let me explain it.
Inside the trigger I excute something like
select test();
where test is a registered php function.
When the trigger fires, the php function gets called, but the first thing
I
need to have is a database handler. Right know, the first thing I do in
that test php is:
$dbh = new PDO('sqlite:mydb.sqlite');
so that creates a new (an different) context, then if I try to:
$res=$dbh-query(update set field=1 from same_table_that fired_the
trigger);
I run in a deadlock, the first trigger is locking the table, and I'm
waiting
for that trigger to relese the lock. This will only end by time-out with
the
error Database locked.
so, how can the php fired by a trigger knows whats the php sqlite context
of
its caller??
You have a php script. It opens a database handle and
registers a function. Then it executes some SQL code which
fires a trigger, which in his turn calls your registered
function.
All these nested calls execute in the same context, not
asynchronically. So the database handle you opened in the
top level is still valid when your registered function is
called. In other words, you don't have to open a second
handle.
Opening a second handle and starting a transaction causes
the lock.
How the function gets the handle is another matter.
If $dbh is a global variable you could simply use that.
All in all, it is quite complicated, probably too
complicated. Are you sure your registered function really
needs to use the database handle?
Can't you just use SQL in the trigger, and restrict the
function to what functions are useful for, like calculations
which can't be donein SQL or aggregation tasks?
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem invoking php functions from a trigger
Sorry I send it before time...
2009/9/2 Alejandro Ruiz-Oriol aruiz...@itelsys.com
Ok, I got it!!
In my php functions I use this code to connect to the DB (its in an include
file)
function OpenDB()
{
$dbh=$GLOBALS['dbh'];// Get Global Handle
if ($dbh==null)// First time? no Handle?
{
$dbh = new PDO('sqlite:/var/www/db.sqlite'); // Create new handle
$GLOBALS['dbh']; // Save it in globals
include_once(/var/www/arduino/sqlite_functions.php); // Include
al the functions needed
$dbh-sqliteCreateFunction('Test','Test'); // Register functions
}
return($dbh); // Return Handle
}
You are right, it's quite complicated, what I'm creating it's some kind of
state machine and if I do it this way, it's very easy to the rest of the
code to change states (just insert the new state in the DB) and a State
change can trigger diferent actions that can cause state changes again. So,
doing it this way, the client process only needs to know what state it wants
to change.
Thx for your help and advise!!!
2009/9/1 Kees Nuyt k.n...@zonnet.nl
On Tue, 1 Sep 2009 09:51:39 +0200, Alejandro Ruiz-Oriol
aruiz...@itelsys.com wrote:
Yes, but how do I know in the PHP fired by the trigger what's the PHP
context of his parent?.
Let me explain it.
Inside the trigger I excute something like
select test();
where test is a registered php function.
When the trigger fires, the php function gets called, but the first thing
I
need to have is a database handler. Right know, the first thing I do in
that test php is:
$dbh = new PDO('sqlite:mydb.sqlite');
so that creates a new (an different) context, then if I try to:
$res=$dbh-query(update set field=1 from same_table_that fired_the
trigger);
I run in a deadlock, the first trigger is locking the table, and I'm
waiting
for that trigger to relese the lock. This will only end by time-out with
the
error Database locked.
so, how can the php fired by a trigger knows whats the php sqlite context
of
its caller??
You have a php script. It opens a database handle and
registers a function. Then it executes some SQL code which
fires a trigger, which in his turn calls your registered
function.
All these nested calls execute in the same context, not
asynchronically. So the database handle you opened in the
top level is still valid when your registered function is
called. In other words, you don't have to open a second
handle.
Opening a second handle and starting a transaction causes
the lock.
How the function gets the handle is another matter.
If $dbh is a global variable you could simply use that.
All in all, it is quite complicated, probably too
complicated. Are you sure your registered function really
needs to use the database handle?
Can't you just use SQL in the trigger, and restrict the
function to what functions are useful for, like calculations
which can't be donein SQL or aggregation tasks?
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem invoking php functions from a trigger
Ok, you we're right!!
The trigger fires a PHP function that connects back to de database and, in
that connection I didn't register the php functions. Just in case someone
runs in the same problem, this is a strange situation, because the function
exists for PHP, but no for SQLite. So, the first time you have check if the
php function exist with a code like this
if (!function_exists(test))
{
include_once(/var/www/arduino/sqlite_functions.php);
}
and, after that, always register to SQLite.
$dbh-sqliteCreateFunction('Test','Test');
But, now, I'run into another problem (don't know if it's better to open
another thread)
As I said, the trigger fires a PHP script that connects back to the
database, and tries to update another row in the same table, and, when it
executes the update statement, it gets frozen. In some cases, I get the
Database lock error,
I don't really think this is a deadlock, as far as I'm not writing to the
same row and the trigger is configured as after update, so the row shoul
be released by the time trigger is fired, but I rather think that SQLite
considers this new connection as completely new and it's blocking the whole
database.
Do you think that this can be avoided somehow?
Thx in advance
Chano
2009/8/29 Kees Nuyt k.n...@zonnet.nl
On Fri, 28 Aug 2009 19:50:56 +0200, Alejandro Ruiz-Oriol
aruiz...@itelsys.com wrote:
Thank's Swithun
but I still have trouble.
Ok, I find out how to register functions with
PDO_Sqlite extensions. Just in case someone
is in the same situation, the way to do it is this:
$dbh = new PDO('sqlite:/whatever.sqlite');
$dbh-sqliteCreateFunction('Test','Test');
But I still have a problem:
If I use direct the test funcion in a quuery like
$res=$dbh-query(select test() from table);
it works, but if function test is invoked from a triiger it will say, my
sentence will be somethin like
$modulo=$dbh-query(UPDATE test SET x = 1);
I get this:
[0] = HY000
[1] = 1
[2] = no such function: Test
seems like the trigger is not using the same $dbh
¿any clue?
Are you sure the function is registered on the $dbh with
$dbh-sqliteCreateFunction(...);
on the $dbh in which the trigger fires?
You have to do that in every script that instantiates the
object.
Thx
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem invoking php functions from a trigger
On Mon, 31 Aug 2009 11:58:42 +0200, Alejandro Ruiz-Oriol aruiz...@itelsys.com wrote: I've been doing some further reading and I think that I already have the answer and it's No, no way to avoid this. As far as I've read, when you start any update function, SQLite makes an exclusive write lock to the table. As far as I'm excuting from inside an update trigger, the database is exclusivily write locked, so if that trigger tries to write to the DB, we will end up in a deadlock. The only thing that can save me from this behavior is if there is someway to tell SQLite that the update is over, before the trigger finished. I've tried to put a commit inside the trigger, but I get a syntax error. I'm working in to aporaches to the workarund. One i ssplit the table in two (due to the logic of my aplication, maybe I can do this, cause the files updated by the trigger can have some kind of logic that I can use to split the table in 2 different tables). The other is to start a background php task that wait till the DB lock is released and then, tries the insert. Any suggestion? I think the registered callback function runs in the same context as the php script that created the first conection, and it should use that conenction, instead of creating a second connection. It's just a suggestion. I didn't try this myself. -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem invoking php functions from a trigger
Hi everybody,
I'm having a problem when I invoke a function developed in php from a
trigger.
I've been using SQLIteManager to develop and test my functions and
everything work's perfect.
I've created this function:
*INSERT INTO user_function ( funct_name , funct_type , funct_code ,
funct_num_args , base_id )
VALUES ( 'Test' , 1 , 'function test() { syslog(LOG_ALERT,Hello trigger
world); }' , 0 , 2 ) * *;*
Then I've created this trigger:
*CREATE TRIGGER Test AFTER
INSERT ON Test_table FOR EACH ROW BEGIN
SELECT test ( ) ;
END * *;*
And, if I insert a new row in the test_table (from SQLIteManager) everything
works and I get the message in syslog.
BUT!!
if I run this php script
*$dbh = new PDO('sqlite:/var/www/test.sqlite');
$sql=INSERT INTO Test ( Nombre , IP , MAC , Descripcion_Modulo ) VALUES (
'2221' , '2121' , '1212' , '1212' ) ;
$modulo=$dbh-query($sql);
print_r($dbh-errorInfo()); *
from outside SQLiteManager I get this errorInfo():
*Array
(
[0] = HY000
[1] = 1
[2] = no such function: test
)*
can anybody help me??
Regards
Chano
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem invoking php functions from a trigger
Hello
AR if I run this php script
AR
AR *$dbh = new PDO('sqlite:/var/www/test.sqlite');
AR $sql=INSERT INTO Test ( Nombre , IP , MAC , Descripcion_Modulo ) VALUES (
AR '2221' , '2121' , '1212' , '1212' ) ;
AR $modulo=$dbh-query($sql);
AR print_r($dbh-errorInfo()); *
AR
AR from outside SQLiteManager I get this errorInfo():
AR
AR *Array
AR (
AR [0] = HY000
AR [1] = 1
AR [2] = no such function: test
AR )*
Being able to execute PHP functions from inside SQL is a bit of a bonus
feature. I imagine that PDO, which implements a subset of many SQL
databases' features, doesn't reach this far.
If you use the SQLite3 extension in PHP, you can register your own
functions. You would create the function in your PHP source, and then
register it when you open the database, and then call it from your SQL
statements.
Perhaps you could keep your user_function table, and query it to get the
PHP code for each function, eval it somehow and then register it with the
database connection.
There is more here:
http://www.php.net/manual/en/sqlite3.createfunction.php
I hope this helps.
Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem invoking php functions from a trigger
Thank's Swithun
but I still have trouble.
Ok, I find out how to register functions with PDO_Sqlite extensions. Just in
case someone is in the same situation, the way to do it is this:
$dbh = new PDO('sqlite:/whatever.sqlite');
$dbh-sqliteCreateFunction('Test','Test');
But I still have a problem:
If I use direct the test funcion in a quuery like
$res=$dbh-query(select test() from table);
it works, but if function test is invoked from a triiger it will say, my
sentence will be somethin like
$modulo=$dbh-query(UPDATE test SET x = 1);
I get this:
[0] = HY000
[1] = 1
[2] = no such function: Test
seems like the trigger is not using the same $dbh
¿any clue?
Thx
2009/8/28 Swithun Crowe swit...@swithun.servebeer.com
Hello
AR if I run this php script
AR
AR *$dbh = new PDO('sqlite:/var/www/test.sqlite');
AR $sql=INSERT INTO Test ( Nombre , IP , MAC , Descripcion_Modulo ) VALUES
(
AR '2221' , '2121' , '1212' , '1212' ) ;
AR $modulo=$dbh-query($sql);
AR print_r($dbh-errorInfo()); *
AR
AR from outside SQLiteManager I get this errorInfo():
AR
AR *Array
AR (
AR [0] = HY000
AR [1] = 1
AR [2] = no such function: test
AR )*
Being able to execute PHP functions from inside SQL is a bit of a bonus
feature. I imagine that PDO, which implements a subset of many SQL
databases' features, doesn't reach this far.
If you use the SQLite3 extension in PHP, you can register your own
functions. You would create the function in your PHP source, and then
register it when you open the database, and then call it from your SQL
statements.
Perhaps you could keep your user_function table, and query it to get the
PHP code for each function, eval it somehow and then register it with the
database connection.
There is more here:
http://www.php.net/manual/en/sqlite3.createfunction.php
I hope this helps.
Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
