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

Reply via email to