Re: [sqlite] 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; inDb; 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

Re: [sqlite] endianess/signed issue on OpenBSD/sparc64 ?

[Jeremie Courreges-Anglas]

On Thu, Jun 13 2019, Richard Hipp  wrote:
> On 6/9/19, Landry Breuil  wrote:
>>
>> this broke on sparc64
>>>select 298.2564151;
>> -298.2564151
>>
>
> I cannot reproduce this (perhaps because I do not have access to a
> sparc64 platform running OpenBSD) and do not have any good ideas about
> what might be causing it.  If you can get me a temporary login on such
> a system, however, I will debug it for you.  Send private email to the
> address below.

This was indeed not a bug in SQLite, but in a sparc64-specific part of
the OpenBSD libc, as pointed out privately by Richard.  I have committed
a fix (picked up from FreeBSD):

  https://marc.info/?l=openbsd-cvs=156113647005489=2

Richard, thanks a lot for your time and expertise.

-- 
jca | PGP : 0x1524E7EE / 5135 92C1 AD36 5293 2BDF  DDCC 0DFA 74AE 1524 E7EE


signature.asc
Description: PGP signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] misuse of aggregate function max()

[Jean-Christophe Deschamps]

That is what I use now, together with the select as Hick mentioned.

An other version I played with is two indexes, one unique on (id,
ts_from) to find the last version if eol is not null and one unique on
(id, ts_eol) where eol = null to find the current active version of id.


Beware that in almost all contexts, null != null.  Hence your condition 
eol = null will never be satisfied.

Use isnull instead.

JcD

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] misuse of aggregate function max()

[ingo]

On 21-6-2019 11:46, Simon Slavin wrote:
> You will note that SQLite is perfectly happy with
> 
> CREATE UNIQUE INDEX idx_test_c
>ON test(id, ts_from, ts_eol)
> WHERE ts_eol = NULL
> ;

That is what I use now, together with the select as Hick mentioned.

An other version I played with is two indexes, one unique on (id,
ts_from) to find the last version if eol is not null and one unique on
(id, ts_eol) where eol = null to find the current active version of id.

Thanks,

Ingo
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] misuse of aggregate function max()

[Hick Gunter]

"Expressions in an index may not reference other tables and may not use 
subqueries nor functions whose result might change"

max(ts_from) not only depends on every other record in the table, but is also 
subject to change dependant on future inserts.

If what you want is a field that contains the highest previously seen ts_from, 
you will have to use a SELECT to determine the current max(ts_from).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von ingo
Gesendet: Freitag, 21. Juni 2019 11:29
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] misuse of aggregate function max()

CREATE TABLE test(
   id INTEGER NOT NULL,
  ts_from TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
   ts_eol TEXT DEFAULT NULL
);

CREATE UNIQUE INDEX idx_test
ON test(id, max(ts_from), ts_eol)
 WHERE ts_eol = NULL
;
--Error: misuse of aggregate function max()

Is this because max() is not deterministic, or because current_timestamp is 
not, or both?

Ingo
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] misuse of aggregate function max()

[Simon Slavin]

On 21 Jun 2019, at 10:29am, ingo  wrote:

> Is this because max() is not deterministic,
> or because current_timestamp is not,
> or both?

The max().  For the reason you identified: it would have to recalculate the 
index values for all rows in the table every time any of the rows changed.  You 
will note that SQLite is perfectly happy with

CREATE UNIQUE INDEX idx_test_c
   ON test(id, ts_from, ts_eol)
WHERE ts_eol = NULL
;
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] misuse of aggregate function max()

[ingo]

CREATE TABLE test(
   id INTEGER NOT NULL,
  ts_from TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
   ts_eol TEXT DEFAULT NULL
);

CREATE UNIQUE INDEX idx_test
ON test(id, max(ts_from), ts_eol)
 WHERE ts_eol = NULL
;
--Error: misuse of aggregate function max()

Is this because max() is not deterministic,
or because current_timestamp is not,
or both?

Ingo
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] OT!!!!! Understanding the WITH clause

[Luuk]

On 20-6-2019 08:11, Michael Falconer wrote:

SELECT peace FROM disaster WHERE disaster.cause = 'Windows';

Returns: *nix



The query should be

SELECT peace
FROM disaster
WHERE (disaster.cause LIKE '%Windows%'
   OR disaster.cause LIKE '%nix%'
   OR disaster.cause LIKE '%incompetence%'
   );


Also then return value might be different, depending on the time of day 
and possible other, at present not known, parameters.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users