Hi all,
Short question:
Do you have or know where to find some regex (regular expressions) for
parsing SQLite statements such as create table, create trigger etc
into their component parameters?
More detail:
SQLite gives us the Name, Tbl_Name and SQL of create statements via
the SQLite_Master table. For instance, I can get all the triggers via:
select Name, Type, SQL, from SQLite_Master where type = 'trigger';
But as far as I know, there's no way to parse the SQL further, for
example to show column details (for tables, views and indexes) or
trigger steps (in triggers). "pragma table_info" does show some basic
info on tables and views such as column names, but I need the full
breakdown of the data.
Since SQLite must be parsing this information internally, I would hope
that the developers would provide some simple hooks to get at the
information from SQL, such as:
select Name, Type, Constraints, Constraint_Data from
SQLite_Table_Columns where Entity_Name = 'My Table';
select Name, Type, Join, Expression from SQLite_View_Columns where
Entity_Name = 'My View';
select Name, Database_Event, Occurs, Event, For_Each, "When" from
SQLite_Triggers;
select SQL from SQLite_Trigger_Steps where Entity_Name = 'My Trigger'
I am only accessing SQLite from scripting environments such as
sqlite3, perl, AppleScript, PHP, not C.
I've started working on some regex (regular expressions) to try to
parse the data out of the SQL returned from SQLite_Master. My regex
skills aren't great. For instance this is what I have so far to parse
out the trigger parameters:
(?x) # ignore spaces and comments
\s*create\strigger\s+
["'\[]?(.*?)["'\]]?\s+ # 1 name: extracted from
possible quotes
(before|after|instead\sof)\s+ # 2 occur: before, after or
instead of
(
insert|delete|update|(update\sof)\s+ # 4 database event
(.*?) # 5 update of columns
)\s+on\s+["'\[]?(.*?)["'\]]?\s+ # 6 table or view name
(?:for\seach\s(.*?)\s+)? # 7 for each row or statement
(?:when\s+(.*?)\s+)? # 8 when
begin\s+(.*?)\s+end\s* # 9 trigger steps
It does a fairly good job, for instance parsing this:
create trigger "Orders Entry update Customer"
instead of update of Customer on "Orders Entry"
for each row when new.Customer not null
begin
update Invoices set Customer = new.Customer where ID = old.Invoice;
end
into:
Orders Entry update Customer # 1 name: extracted from
possible quotes
instead of # 2 occur: before, after or
instead of
update of Customer
update of # 4 database event
Customer # 5 update of fields
Orders Entry # 6 table or view name
row # 7 for each row or statement
new.Customer not null # 8 when
update Invoices set Customer = ...; # 9 trigger steps
But it has a few problems, such as:
1. returns insert, delete and update in parameter 3, but "update of"
in parameter 4. I want them consistently in the same output parameter.
2. The dequoting of the entity names (parameter 1 and 6) isn't robust,
for instance failing if one quote is inside another, such as "My
table's Name".
So, before I get too far into it, I figured others of you out there
must already have some regexes that are suitable for this, or know of
a simpler approach. Or at worst any regex gurus out there that can
help fine tune the above?
Any help appreciated.
Thanks,
Tom
BareFeet
--
SQLite apps for Mac OS X compared:
http://www.tandb.com.au/sqlite/compare/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
