New database trigger "before Statement"
-----------------------------------------
Key: CORE-5451
URL: http://tracker.firebirdsql.org/browse/CORE-5451
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Affects Versions: 4.0 Initial, 4.0 Alpha 1, 4.0 Beta 1
Environment: all
Reporter: Holger Klemt
i had often situations where s specific sql syntax used by a customer needs a
small modification when it is used in firebird
a very simple example based on a script from mysql
-a command like "create table ... if not exists" could be easily replace by the
firebird command "create or alter table"
-a table definition with a datatype INT for example can be replaced by INTEGER
-...
An option for fb4.x to solve such a problem globally could be the follwing idea:
Any sql statement is processed by a new database trigger where the sourcecode
of the statement can be manipulated
using old. and new. instance
a typical mysql statement like this
CREATE TABLE if not exists db.people (First text, Last text, Age int)
is replaced with the following trigger
CREATE OR ALTER trigger TrgStatementCleaner
active before any statement position 0
AS
begin
new.statement=old.statement;
new.statement=replace(new.statement, 'TRUNCATE TABLE ' ,'DELETE FROM ');
new.statement=replace(new.statement, 'db.','');
new.statement=replace(new.statement, 'CREATE TABLE if not exists','CREATE OR
ALTER TABLE');
new.statement=replace(new.statement, 'text','BLOB SUB_TYPE TEXT');
new.statement=replace(new.statement, 'int', 'INTEGER');
new.statement=replace(new.statement,
'VERYLONGVARNAMEINASPTHATMAKESNOSENSE','VLVNTMSN');
end
by
CREATE OR ALTER TABLE people (First BLOB SUB_TYPE TEXT, Last BLOB SUB_TYPE
TEXT, Age INTEGER)
which is now compatible to firebird.
Beeing able to develop a platform specific statement trigger where the whole
functionality of trigger programming (incl reg exp etc)
allows to manipulate the sql statement before it is processed by the internal
firebird parser would allow to implement a very easy
way to add a sql language compatibility level and existing web application
currently only support mysql might work with a very easy
way. When final replacements of the stament was done, the trigger can add the
original source (or simply a hash) in a local cache table
and when a new statement comes with an existing hash, it can take the
translated version from the cache table.
What do you think about such an idea? it is a greate advantage also for
backwards compatibility
inside firebird (see too long variable names available only until fb25 ;-))
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
