pragma trigger_info?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

pragma trigger_info?

Dominique Devienne
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma trigger_info?

Keith Medcalf

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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: pragma trigger_info?

Keith Medcalf

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-
>[hidden email]] 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-
>>[hidden email]] 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
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users