And for pragma [schema.]trigger_info(name):
Currently only returns (schema table name op tm) through there is no reason
that it could not parse out even more information that is in the loaded schema
...
mkpragmatab.tcl
NAME: trigger_info
FLAG: NeedSchema Result1 SchemaOpt
ARG: 1
COLS: schema table name op tm
IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
pragma.c code:
case PragTyp_TRIGGER_INFO:
if (zRight)
{
Trigger *trig = 0;
HashElem *he;
char *optype;
int i;
for(he=sqliteHashFirst(&(pDb->pSchema->trigHash)); he;
he=sqliteHashNext(he))
{
trig = sqliteHashData(he);
if (sqlite3_stricmp(trig->zName, zRight) == 0)
break;
trig = 0;
}
if (!trig) break;
pParse->nMem = 5;
switch (trig->op)
{
case TK_INSERT:
optype = "insert";
break;
case TK_UPDATE:
optype = "update";
break;
case TK_DELETE:
optype = "delete";
break;
default:
optype = "unknown";
}
sqlite3VdbeMultiLoad(v, 1, "sssss",
pDb->zDbSName,
trig->table,
trig->zName,
optype,
trig->tr_tm == 1 ? "before" : "after");
}
break;
---
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 Keith Medcalf
>Sent: Friday, 21 June, 2019 15:01
>To: SQLite mailing list
>Subject: Re: [sqlite] pragma trigger_info?
>
>
>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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
