What do you want for trigger info?
The following pragma code will return all the data in the currently loaded
schema for all attached databases (table/index/trigger names). It creates a
new pragma called DATABASE_INFO (and table pragme_database_info) that returns
three columns: schema type name where "schema" is the name of the schema, type
is 'table', 'index', or 'trigger', and name is the name of the thing.
You add new pragma's by modifying tool\mkpragmatab.tcl which creates pragma.h,
and adding the code to pragma.c
For the DATABASE_INFO pragma add the following to mkpragmatab.tcl and run it:
NAME: database_info
FLAG: NeedSchema Result0
COLS: schema type name
IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
add the following to src\pragma.c
case PragTyp_DATABASE_INFO:
{
int i;
HashElem *he;
char *zDbSName;
pParse->nMem = 3;
for(i=0; i<db->nDb; i++)
{
if( db->aDb[i].pBt==0 ) continue;
assert( db->aDb[i].zDbSName!=0 );
zDbSName = db->aDb[i].zDbSName;
for(he=sqliteHashFirst(&(db->aDb[i].pSchema->tblHash)); he;
he=sqliteHashNext(he))
{
Table *pData = sqliteHashData(he);
sqlite3VdbeMultiLoad(v, 1, "sss",
zDbSName,
pData->pSelect ? "view" : "table",
pData->zName);
}
for(he=sqliteHashFirst(&(db->aDb[i].pSchema->idxHash)); he;
he=sqliteHashNext(he))
{
Index *pData = sqliteHashData(he);
sqlite3VdbeMultiLoad(v, 1, "sss",
zDbSName,
"index",
pData->zName);
}
for(he=sqliteHashFirst(&(db->aDb[i].pSchema->trigHash)); he;
he=sqliteHashNext(he))
{
Trigger *pData = sqliteHashData(he);
sqlite3VdbeMultiLoad(v, 1, "sss",
zDbSName,
"trigger",
pData->zName);
}
}
}
break;
and rebuild sqlite3.c and you have added a new pragma DATABASE_INFO (that takes
no parameters).
The Trigger structure contains a bunch of information about the trigger (see
sqliteInt.h) and you can add more pragma's that retrieve that information in
various forms, assuming that you know what you want/need.
TRIGGER_LIST and TRIGGER_INFO pragma's can be added in the same way using the
existing INDEX_LIST and INDEX_INFO pragma's as templates ...
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Dominique Devienne
>Sent: Friday, 21 June, 2019 04:38
>To: General Discussion of SQLite Database
>Subject: [sqlite] pragma trigger_info?
>
>I trying to reconcile two manually maintained schemas,
>one that's custom and drives the database code at runtime,
>and another that's the SQLite (DDL) used to instantiate the DBs.
>Differences exist, because we humans at not that good at rigour.
>
>These are fairly large and old schemas, > 200 tables, > 4,000 columns
>so manual inspection is too error prone, so instead I'm automating
>it, via
>SQLite's introspection pragmas to get the "SQLite schema" side.
>
>But it seems there's nothing to get details on triggers, unlike for
>tables,
>views, and indexes.
>Did I miss it? If not, why isn't trigger introspection described as
>the
>rest of schema objects?
>
>I guess I'll have to parse the TRIGGER SQL myself, but we all know
>this is
>brittle and subject
>to break from one version of SQLite to another. Could this be added
>to
>SQLite please?
>
>Thanks, --DD
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
