Re: [sqlite] EXISTS optimisation?

[Dinu]

Quick note to self and others:
IN() and EXISTS() in all RDB's I know of are the uncle noone mentions; that
is to say, they have different compare semantics than JOIN so the naive
strategy is to evaluate them as dependent subqueries, not correlated ones,
which would be consistent with the behavior I noticed. However, I do know of
Maria and Postgres that do a decent job at optimizing EXISTS () (which I
think is by all means the correct semantic for this intent). But there's by
no means a golden standard across RDB's so that's why it would be very
useful to have some documentation on it, as it's one of the migration
pitfalls.

I'm still in debt with the TC, will work on it the next days.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[J. King]

Please forgive the off-topicedness of this message, but I would like to second 
that this mailing list is incredibly edifying, and I would also like thank 
everyone who has both answered and asked questions in the time I've been 
subscribed. I've stumbled across answers here that I never thought to ask, many 
of which have been helpful in refining my application. My reading here has been 
quite invaluable. 

On November 28, 2017 9:24:57 PM EST, Peter Halasz  
wrote:
>That's terrific! Looking forward to it being in a full release.
>
>Hopefully the next tool developer to come along won't need to write a
>custom SQL parser, get lost in irrelevant datatype documentation, sift
>through op codes from an unnestable "explain" statement, compile a
>custom
>SQLite C extension, re-implement SQLite's ROWID logic, or need to sign
>up
>to this mailing list--which has genuinely been very helpful and
>enlightening--because there will be a "table_ipk" pragma statement that
>does what they need.
>
>Cheers,
>
>Peter H.  (Pengo)
>
>On Wed, Nov 29, 2017 at 3:10 AM, petern 
>wrote:
>
>> Hey Peter.  Good on you.  Lobbying for sensible fixes to the public
>API
>> does pay off sometimes.
>>
>> There's a new branch in the timeline.  [Watch for a merge here:
>> https://www.sqlite.org/src/timeline?n=50 ]
>>
>> https://www.sqlite.org/src/info/2494132a2b1221a4
>>
>>   **   PRAGMA table_ipk()  **  ** If  has an INTEGER
>> PRIMARY KEY column that is an alias for  ** the ROWID, then return
>the
>> name of that column.  If  does not  ** have a ROWID alias, or
>> if it does not have a ROWID, or if  is  ** a view or virtual
>> table or if it does not exist, then return no rows.
>>
>> The lesson here is that one may safely ignore the sometimes
>authoritative
>> sounding status quo mafia who occupy this mailing list but have no
>source
>> check-in credentials whatsoever.
>>
>> On Mon, Nov 27, 2017 at 4:38 PM, Peter Halasz
>
>> wrote:
>>
>> > Disclaimer: I am not a developer on linq2db. Nor am I a developer
>on "DB
>> > Browser for SQLite", nor am I a developer of any of the other tools
>which
>> > help developers create SQLite databases and integrate them with
>their
>> > projects through UIs and APIs built on SQLite and its metadata.
>> >
>> > However, it's quite clear to me, as only a user of just a few of
>these
>> > tools and libraries, that they would be improved greatly if they
>had a
>> > direct way of querying which field in a SQLite table was acting as
>an
>> alias
>> > of ROW ID.
>> >
>> > linq2db, for example, has no simple way of definitely telling
>whether a
>> > field is a true INTEGER PRIMARY KEY, so its developers have left it
>as an
>> > exercise for its library users to debug errors which come up as a
>result,
>> > and to manually "correct column definitions". Something which can
>result
>> in
>> > much time wasted unnecessarily tracking down the bugs, finding
>special
>> API
>> > calls to work around them, and a less than favorable view of SQLite
>for
>> its
>> > documentation of these anomalies.
>> >
>> > linq2db could, in theory, fix this issue. But it would take a bunch
>of
>> > error-prone work to re-create SQLite's ROW ID rules, so they have
>chosen
>> > not to. They have simply chosen to ignore the weird behavior that
>comes
>> > form not differentiating an INTEGER PRIMARY KEY from an INT PRIMARY
>KEY.
>> I
>> > don't blame them for having other priorities. After all, linq2db
>> integrates
>> > with 12 other database engines. And this isn't about linq2db, it's
>about
>> > every tool that comes across the need for this metadata.
>> >
>> > Many other tools also have bugs or poor UX because they cannot
>access a
>> > simple bit of metadata. This could be solved quite easily if SQLite
>> simply
>> > exposed this data.
>> >
>> > I was honestly stunned when I discovered this basic information,
>> something
>> > which completely changes the behavior of a field, is not accessible
>to
>> > users.
>> >
>> > Please can SQLite developers make the ROW ID status of a field
>visible in
>> > future versions?
>> >
>> > It could be done either directly through a PRAGMA statement, or
>slightly
>> > less directly by exposing "isRowId()" or "collseq()" style methods
>as
>> > posted by peter.nichvolodov in the previous thread.
>> >
>> > I really hope there is not resistance this idea. It's really
>amazing to
>> me
>> > that such a mature, battle-tested database engine does not fully
>expose
>> its
>> > metadata and field behavior.
>> >
>> > Cheers
>> >
>> > Peter H.
>> > ___
>> > 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] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[Peter Halasz]

That's terrific! Looking forward to it being in a full release.

Hopefully the next tool developer to come along won't need to write a
custom SQL parser, get lost in irrelevant datatype documentation, sift
through op codes from an unnestable "explain" statement, compile a custom
SQLite C extension, re-implement SQLite's ROWID logic, or need to sign up
to this mailing list--which has genuinely been very helpful and
enlightening--because there will be a "table_ipk" pragma statement that
does what they need.

Cheers,

Peter H.  (Pengo)

On Wed, Nov 29, 2017 at 3:10 AM, petern  wrote:

> Hey Peter.  Good on you.  Lobbying for sensible fixes to the public API
> does pay off sometimes.
>
> There's a new branch in the timeline.  [Watch for a merge here:
> https://www.sqlite.org/src/timeline?n=50 ]
>
> https://www.sqlite.org/src/info/2494132a2b1221a4
>
>   **   PRAGMA table_ipk()  **  ** If  has an INTEGER
> PRIMARY KEY column that is an alias for  ** the ROWID, then return the
> name of that column.  If  does not  ** have a ROWID alias, or
> if it does not have a ROWID, or if  is  ** a view or virtual
> table or if it does not exist, then return no rows.
>
> The lesson here is that one may safely ignore the sometimes authoritative
> sounding status quo mafia who occupy this mailing list but have no source
> check-in credentials whatsoever.
>
> On Mon, Nov 27, 2017 at 4:38 PM, Peter Halasz 
> wrote:
>
> > Disclaimer: I am not a developer on linq2db. Nor am I a developer on "DB
> > Browser for SQLite", nor am I a developer of any of the other tools which
> > help developers create SQLite databases and integrate them with their
> > projects through UIs and APIs built on SQLite and its metadata.
> >
> > However, it's quite clear to me, as only a user of just a few of these
> > tools and libraries, that they would be improved greatly if they had a
> > direct way of querying which field in a SQLite table was acting as an
> alias
> > of ROW ID.
> >
> > linq2db, for example, has no simple way of definitely telling whether a
> > field is a true INTEGER PRIMARY KEY, so its developers have left it as an
> > exercise for its library users to debug errors which come up as a result,
> > and to manually "correct column definitions". Something which can result
> in
> > much time wasted unnecessarily tracking down the bugs, finding special
> API
> > calls to work around them, and a less than favorable view of SQLite for
> its
> > documentation of these anomalies.
> >
> > linq2db could, in theory, fix this issue. But it would take a bunch of
> > error-prone work to re-create SQLite's ROW ID rules, so they have chosen
> > not to. They have simply chosen to ignore the weird behavior that comes
> > form not differentiating an INTEGER PRIMARY KEY from an INT PRIMARY KEY.
> I
> > don't blame them for having other priorities. After all, linq2db
> integrates
> > with 12 other database engines. And this isn't about linq2db, it's about
> > every tool that comes across the need for this metadata.
> >
> > Many other tools also have bugs or poor UX because they cannot access a
> > simple bit of metadata. This could be solved quite easily if SQLite
> simply
> > exposed this data.
> >
> > I was honestly stunned when I discovered this basic information,
> something
> > which completely changes the behavior of a field, is not accessible to
> > users.
> >
> > Please can SQLite developers make the ROW ID status of a field visible in
> > future versions?
> >
> > It could be done either directly through a PRAGMA statement, or slightly
> > less directly by exposing "isRowId()" or "collseq()" style methods as
> > posted by peter.nichvolodov in the previous thread.
> >
> > I really hope there is not resistance this idea. It's really amazing to
> me
> > that such a mature, battle-tested database engine does not fully expose
> its
> > metadata and field behavior.
> >
> > Cheers
> >
> > Peter H.
> > ___
> > 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

Re: [sqlite] Datetime / Transactions / CLI

[Stephen Chrzanowski]

Although I don't do many long length transactions for date and times, I
kind of like the idea of having the control of over how the library allows
you to chose which way the dates and times are going to work. Default to
the current model, of course, to allow for backward compatibility, but,
either allow for a pragma to be set a particular way, or, a new compile
time directive to configure for the required mode.

On Tue, Nov 28, 2017 at 2:25 PM,  wrote:

>
>
> My original comments/requests to the developers still apply though:
>
> - Can the documentation be updated to include something like
>   Keith's description; and
> - Can we have some kind of control over whether 'now' is
>   step-/statement-/real-time
>
> Regards,
> Mark
> --
> Mark Lawrence
> ___
> 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] Datetime / Transactions / CLI

[nomad]

On Tue Nov 28, 2017 at 10:34:03AM -0700, Keith Medcalf wrote:
> 
> Datetime functions (that is, what constitutes "now") was, by default,
> step-stable.  The value is cached within the VDBE (statement object)
> on its first use per-step and retains the same value until the VDBE
> code yields a row.  Re-entry on the next step used to reset "now".
> 
> This was changed to per-statement stability and not per-step
> stability, though exactly when this change occurred I do not recall
> exactly (I believe it was made a SLOCHNG function when indexes on
> functions were added).
> 
> As I posted earlier you can patch the VDBE code to ensure that "now"
> is always statement stable rather than step stable by only resetting
> "now" on initial entry to the VDBE program (program counter == 0)
> rather than unconditionally.  Of course, the change that made "now"
> statement stable renders this patch moot.
> 
> In no case is now "transaction stable" as the value of now is cached
> within the statement object (VDBE program) and not in the connection
> object.

Thanks, that is quite informative. My original observations
(transaction stable times) were wrong - I was looking at a bunch of
trigger actions and forgot they were all part of one statement.

My original comments/requests to the developers still apply though:

- Can the documentation be updated to include something like
  Keith's description; and
- Can we have some kind of control over whether 'now' is
  step-/statement-/real-time

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

Re: [sqlite] Foreign key help

[x]

Good stuff Keith. One to archive.




From: sqlite-users  on behalf of 
Keith Medcalf 
Sent: Tuesday, November 28, 2017 4:02:35 PM
To: SQLite mailing list
Subject: Re: [sqlite] Foreign key help


And of course in the command line shell you can and should use

.lint fkey-indexes

to let you know if you are missing any indexes required for efficient 
foreign-key enforcement operations.

It will report missing indexes on the PARENT (table/columns referred to) and on 
CHILDREN (tables/columns referred from) since you need these indexes in order 
for foreign key enforcement to work without having to resort to table scans of 
the entire parent/child tables(s) on each row of an insert/update/delete 
operation affecting either a parent or child foreign key column.

If you do not have the necessary indexes defined for these relationships you 
might erroneously think that referential integrity enforcement is excessively 
expensive, and continue to use "insert/update/delete with prayers" to maintain 
referential integrity rather than have the database engine do it for you.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>On 28 Nov 2017, at 3:26pm, x  wrote:
>
>> If I have foreign keys in place but always have foreign_keys = OFF
>then one day start SQLite with foreign_keys = ON what happens? Does
>SQLite suddenly check all foreign keys and report / delete violations
>or does it leave everything as is and just enforce foreign keys from
>that point on?
>
>No.  There is no automatic check.  If you have broken a foreign key
>constraint while "foreign_keys = OFF" it may only be discovered some
>time in the future, or it may live on for as long as the database is
>used.
>
>However, you can force SQLite to check all foreign keys any time you
>want using
>
>
>
>   PRAGMA foreign_key_check
>
>If it returns no rows, then there are no problems.  This check will
>work even while "foreign_keys = OFF", so you can check it before you
>turn that PRAGMA back on.
>
>Simon.
>___
>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

Re: [sqlite] Datetime / Transactions / CLI

[Simon Slavin]

On 28 Nov 2017, at 5:34pm, Keith Medcalf  wrote:

> This would indicate that "now" has statement-stability and not 
> transaction-stability, which matches with my observations.

You’re right, I was wrong.  Thanks for the correction.

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

Re: [sqlite] Datetime / Transactions / CLI

[Keith Medcalf]

Datetime functions (that is, what constitutes "now") was, by default, 
step-stable.  The value is cached within the VDBE (statement object) on its 
first use per-step and retains the same value until the VDBE code yields a row. 
 Re-entry on the next step used to reset "now".

This was changed to per-statement stability and not per-step stability, though 
exactly when this change occurred I do not recall exactly (I believe it was 
made a SLOCHNG function when indexes on functions were added).

As I posted earlier you can patch the VDBE code to ensure that "now" is always 
statement stable rather than step stable by only resetting "now" on initial 
entry to the VDBE program (program counter == 0) rather than unconditionally.  
Of course, the change that made "now" statement stable renders this patch moot.

In no case is now "transaction stable" as the value of now is cached within the 
statement object (VDBE program) and not in the connection object.

This case is where I have "forced" "now" to be statement stable ... although 
removing that patch appears to make no difference.
Note this is with the current head of trunk.

The current source code contains the following comment (search the amalgamation 
for iCurrentTime):

/*
** Return the current time for a statement.  If the current time
** is requested more than once within the same run of a single prepared
** statement, the exact same time is returned for each invocation regardless
** of the amount of time that elapses between invocations.  In other words,
** the time returned is always the time of the first call.
*/

This would indicate that "now" has statement-stability and not 
transaction-stability, which matches with my observations.

import apsw
import apswrow
import time
db = apsw.Connection(':memory:')
db.cursor().execute('begin immediate');
while True:
 for row in db.cursor().execute('select value, julianday() from generate_series 
where start=1 and stop=10'):
  print row
  time.sleep(1)

Row(value=1, julianday=2458086.2071575928)
Row(value=2, julianday=2458086.2071575928)
Row(value=3, julianday=2458086.2071575928)
Row(value=4, julianday=2458086.2071575928)
Row(value=5, julianday=2458086.2071575928)
Row(value=6, julianday=2458086.2071575928)
Row(value=7, julianday=2458086.2071575928)
Row(value=8, julianday=2458086.2071575928)
Row(value=9, julianday=2458086.2071575928)
Row(value=10, julianday=2458086.2071575928)
Row(value=1, julianday=2458086.2072733566)
Row(value=2, julianday=2458086.2072733566)
Row(value=3, julianday=2458086.2072733566)
Row(value=4, julianday=2458086.2072733566)
Row(value=5, julianday=2458086.2072733566)
Row(value=6, julianday=2458086.2072733566)
Row(value=7, julianday=2458086.2072733566)
Row(value=8, julianday=2458086.2072733566)
Row(value=9, julianday=2458086.2072733566)
Row(value=10, julianday=2458086.2072733566)
Row(value=1, julianday=2458086.2073891205)
Row(value=2, julianday=2458086.2073891205)
Row(value=3, julianday=2458086.2073891205)
Row(value=4, julianday=2458086.2073891205)
Row(value=5, julianday=2458086.2073891205)
Row(value=6, julianday=2458086.2073891205)
Row(value=7, julianday=2458086.2073891205)
Row(value=8, julianday=2458086.2073891205)
Row(value=9, julianday=2458086.2073891205)
Row(value=10, julianday=2458086.2073891205)
Row(value=1, julianday=2458086.207504896)
Row(value=2, julianday=2458086.207504896)



---
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 Simon Slavin
>Sent: Tuesday, 28 November, 2017 09:02
>To: SQLite mailing list
>Subject: Re: [sqlite] Datetime / Transactions / CLI
>
>
>
>On 28 Nov 2017, at 3:50pm, no...@null.net wrote:
>
>> Can someone point me to the documentation for behaviour of
>date/time
>> functions inside transactions? In my code it appears time is
>frozen.
>
>Correct.  The value of 'now' is frozen at the time a transaction
>begins.  This is to ensure that if many rows are created/updated
>(perhaps even in different tables) they are all assigned the same
>timestamp.
>
>If you need to record, instead, the timestamp that data was entered
>into your user-interface, then you need to record that time in your
>programming language, not rely on when the data hits SQL.
>
>> The command-line client on the other hand doesn't behave the same
>way:
>>
>>  sqlite> begin immediate;
>>  sqlite> select julianday();
>>  julianday()
>>  
>>  2458086.15509343
>>  sqlite> select julianday();
>>  julianday()
>>  
>>  2458086.15511422
>>
>> I'm guessing that the CLI is kind of broken
>
>That’s an interesting observation and I am surprised by it.
>
>Simon.
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[petern]

Hey Peter.  Good on you.  Lobbying for sensible fixes to the public API
does pay off sometimes.

There's a new branch in the timeline.  [Watch for a merge here:
https://www.sqlite.org/src/timeline?n=50 ]

https://www.sqlite.org/src/info/2494132a2b1221a4

  **   PRAGMA table_ipk()  **  ** If  has an INTEGER
PRIMARY KEY column that is an alias for  ** the ROWID, then return the
name of that column.  If  does not  ** have a ROWID alias, or
if it does not have a ROWID, or if  is  ** a view or virtual
table or if it does not exist, then return no rows.

The lesson here is that one may safely ignore the sometimes authoritative
sounding status quo mafia who occupy this mailing list but have no source
check-in credentials whatsoever.

On Mon, Nov 27, 2017 at 4:38 PM, Peter Halasz 
wrote:

> Disclaimer: I am not a developer on linq2db. Nor am I a developer on "DB
> Browser for SQLite", nor am I a developer of any of the other tools which
> help developers create SQLite databases and integrate them with their
> projects through UIs and APIs built on SQLite and its metadata.
>
> However, it's quite clear to me, as only a user of just a few of these
> tools and libraries, that they would be improved greatly if they had a
> direct way of querying which field in a SQLite table was acting as an alias
> of ROW ID.
>
> linq2db, for example, has no simple way of definitely telling whether a
> field is a true INTEGER PRIMARY KEY, so its developers have left it as an
> exercise for its library users to debug errors which come up as a result,
> and to manually "correct column definitions". Something which can result in
> much time wasted unnecessarily tracking down the bugs, finding special API
> calls to work around them, and a less than favorable view of SQLite for its
> documentation of these anomalies.
>
> linq2db could, in theory, fix this issue. But it would take a bunch of
> error-prone work to re-create SQLite's ROW ID rules, so they have chosen
> not to. They have simply chosen to ignore the weird behavior that comes
> form not differentiating an INTEGER PRIMARY KEY from an INT PRIMARY KEY. I
> don't blame them for having other priorities. After all, linq2db integrates
> with 12 other database engines. And this isn't about linq2db, it's about
> every tool that comes across the need for this metadata.
>
> Many other tools also have bugs or poor UX because they cannot access a
> simple bit of metadata. This could be solved quite easily if SQLite simply
> exposed this data.
>
> I was honestly stunned when I discovered this basic information, something
> which completely changes the behavior of a field, is not accessible to
> users.
>
> Please can SQLite developers make the ROW ID status of a field visible in
> future versions?
>
> It could be done either directly through a PRAGMA statement, or slightly
> less directly by exposing "isRowId()" or "collseq()" style methods as
> posted by peter.nichvolodov in the previous thread.
>
> I really hope there is not resistance this idea. It's really amazing to me
> that such a mature, battle-tested database engine does not fully expose its
> metadata and field behavior.
>
> Cheers
>
> Peter H.
> ___
> 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] Foreign key help

[Keith Medcalf]

And of course in the command line shell you can and should use

.lint fkey-indexes

to let you know if you are missing any indexes required for efficient 
foreign-key enforcement operations.

It will report missing indexes on the PARENT (table/columns referred to) and on 
CHILDREN (tables/columns referred from) since you need these indexes in order 
for foreign key enforcement to work without having to resort to table scans of 
the entire parent/child tables(s) on each row of an insert/update/delete 
operation affecting either a parent or child foreign key column.

If you do not have the necessary indexes defined for these relationships you 
might erroneously think that referential integrity enforcement is excessively 
expensive, and continue to use "insert/update/delete with prayers" to maintain 
referential integrity rather than have the database engine do it for you.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>On 28 Nov 2017, at 3:26pm, x  wrote:
>
>> If I have foreign keys in place but always have foreign_keys = OFF
>then one day start SQLite with foreign_keys = ON what happens? Does
>SQLite suddenly check all foreign keys and report / delete violations
>or does it leave everything as is and just enforce foreign keys from
>that point on?
>
>No.  There is no automatic check.  If you have broken a foreign key
>constraint while "foreign_keys = OFF" it may only be discovered some
>time in the future, or it may live on for as long as the database is
>used.
>
>However, you can force SQLite to check all foreign keys any time you
>want using
>
>
>
>   PRAGMA foreign_key_check
>
>If it returns no rows, then there are no problems.  This check will
>work even while "foreign_keys = OFF", so you can check it before you
>turn that PRAGMA back on.
>
>Simon.
>___
>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] Datetime / Transactions / CLI

[Simon Slavin]

On 28 Nov 2017, at 3:50pm, no...@null.net wrote:

> Can someone point me to the documentation for behaviour of date/time
> functions inside transactions? In my code it appears time is frozen.

Correct.  The value of 'now' is frozen at the time a transaction begins.  This 
is to ensure that if many rows are created/updated (perhaps even in different 
tables) they are all assigned the same timestamp.

If you need to record, instead, the timestamp that data was entered into your 
user-interface, then you need to record that time in your programming language, 
not rely on when the data hits SQL.

> The command-line client on the other hand doesn't behave the same way:
> 
>   sqlite> begin immediate;
>   sqlite> select julianday();
>   julianday()
>   
>   2458086.15509343
>   sqlite> select julianday();
>   julianday()
>   
>   2458086.15511422
> 
> I'm guessing that the CLI is kind of broken

That’s an interesting observation and I am surprised by it.

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

Re: [sqlite] Foreign key help

[x]

Thanks David / Simon.




From: sqlite-users  on behalf of 
Simon Slavin 
Sent: Tuesday, November 28, 2017 3:36:51 PM
To: SQLite mailing list
Subject: Re: [sqlite] Foreign key help

On 28 Nov 2017, at 3:26pm, x  wrote:

> If I have foreign keys in place but always have foreign_keys = OFF then one 
> day start SQLite with foreign_keys = ON what happens? Does SQLite suddenly 
> check all foreign keys and report / delete violations or does it leave 
> everything as is and just enforce foreign keys from that point on?

No.  There is no automatic check.  If you have broken a foreign key constraint 
while "foreign_keys = OFF" it may only be discovered some time in the future, 
or it may live on for as long as the database is used.

However, you can force SQLite to check all foreign keys any time you want using



PRAGMA foreign_key_check

If it returns no rows, then there are no problems.  This check will work even 
while "foreign_keys = OFF", so you can check it before you turn that PRAGMA 
back on.

Simon.
___
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] Datetime / Transactions / CLI

[nomad]

Can someone point me to the documentation for behaviour of date/time
functions inside transactions? In my code it appears time is frozen.
The command-line client on the other hand doesn't behave the same way:

sqlite> begin immediate;
sqlite> select julianday();
julianday()

2458086.15509343
sqlite> select julianday();
julianday()

2458086.15511422

I'm guessing that the CLI is kind of broken, given that the in-code
version matches with what PostgreSQL does.

On a related note, is a user-defined function the only way to get the
real system time within a transaction? Would the developers consider an
additional "real" (or similar) datetime modifier?

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

Re: [sqlite] Foreign key help

[David Raymond]

Leaves what's there alone and just starts enforcing from when you turn it on. 
It'll only go actively looking for issues if you do a pragma foreign_key_check;

sqlite> pragma foreign_keys = off;

sqlite> create table parent (id integer primary key);

sqlite> create table child (id integer primary key, parentID int references 
parent);

sqlite> insert into parent values (1), (2);

sqlite> insert into child (parentID) values (1), (2), (3), (4);

sqlite> pragma foreign_key_check;
table|rowid|parent|fkid
child|3|parent|0
child|4|parent|0

sqlite> pragma foreign_keys = on;

sqlite> insert into parent values (3);

sqlite> update child set parentID = parentID + 1;
Error: FOREIGN KEY constraint failed

sqlite> pragma foreign_key_check;
table|rowid|parent|fkid
child|4|parent|0


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of x
Sent: Tuesday, November 28, 2017 10:27 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Foreign key help

If I have foreign keys in place but always have foreign_keys = OFF then one day 
start SQLite with foreign_keys = ON what happens? Does SQLite suddenly check 
all foreign keys and report / delete violations or does it leave everything as 
is and just enforce foreign keys from that point on?

___
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] Foreign key help

[Simon Slavin]

On 28 Nov 2017, at 3:26pm, x  wrote:

> If I have foreign keys in place but always have foreign_keys = OFF then one 
> day start SQLite with foreign_keys = ON what happens? Does SQLite suddenly 
> check all foreign keys and report / delete violations or does it leave 
> everything as is and just enforce foreign keys from that point on?

No.  There is no automatic check.  If you have broken a foreign key constraint 
while "foreign_keys = OFF" it may only be discovered some time in the future, 
or it may live on for as long as the database is used.

However, you can force SQLite to check all foreign keys any time you want using



PRAGMA foreign_key_check

If it returns no rows, then there are no problems.  This check will work even 
while "foreign_keys = OFF", so you can check it before you turn that PRAGMA 
back on.

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

Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

[David Raymond]

With an integer primary key, not just any primary key. Probably something to do 
with the deterministic flag as well. Looks like in checking if it's gonna be a 
good integer for a rowid it calls it twice. Below you can see where random() 
gets called twice, so what the before trigger inserts into another table is 
different than what gets put into the original table. If you have a 
deterministic function like abs() it just calls it once, or if you have a 
non-integer-primary-key table it just calls it once.


sqlite> create table d (id integer primary key);

sqlite> create table d2 (id int primary key);

sqlite> create table t (tableName text, id int);

sqlite> create trigger bi_d before insert on d begin insert into t values ('d', 
new.id); end;

sqlite> create trigger bi_d2 before insert on d2 begin insert into t values 
('d2', new.id); end;

sqlite> insert into d values (random());

sqlite> insert into d2 values (random());

sqlite> select * from t;
tableName|id
d|-5810358455625904630
d2|-3456845157187719103

sqlite> select * from d;
id
6606271909038536929

sqlite> select * from d2;
id
-3456845157187719103

sqlite> explain insert into d values (random());
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 20000  Start at 20
1 OpenWrite  0 3 0 1  00  root=3 iDb=0; d
2 Function0  0 0 3 random(0)  00  r[3]=func(r[0])
3 NotNull3 5 000  if r[3]!=NULL goto 5
4 Integer-13 000  r[3]=-1
5 MustBeInt  3 0 000
6 Function0  0 0 4 random(0)  00  r[4]=func(r[0])
7 Copy   4 5 000  r[5]=r[4]
8 Affinity   4 1 0 D  00  affinity(r[4])
9 Program1 196 program01  Call: bi_d.default
10Copy   5 1 000  r[1]=r[5]
11NotNull1 13000  if r[1]!=NULL goto 13
12NewRowid   0 1 000  r[1]=rowid
13MustBeInt  1 0 000
14SoftNull   2 0 000  r[2]=NULL
15NotExists  0 17100  intkey=r[1]
16Halt   1555  2 0 d.id   02
17MakeRecord 2 1 7 D  00  r[7]=mkrec(r[2])
18Insert 0 7 1 d  31  intkey=r[1] data=r[7]
19Halt   0 0 000
20Transaction0 1 390  01  usesStmtJournal=1
21Goto   0 1 000
0 Init   0 1 0 -- TRIGGER bi_d  00  Start at 1; Start: 
bi_d.default (BEFORE INSERT ON d)
1 OpenWrite  0 2 0 2  00  root=2 iDb=0; t
2 NewRowid   0 1 000  r[1]=rowid
3 String80 2 0 d  00  r[2]='d'
4 Param  2 3 000  new.rowid -> $3
5 MakeRecord 2 2 4 BD 00  r[4]=mkrec(r[2..3])
6 Insert 0 4 1 t  39  intkey=r[1] data=r[4]
7 ResetCount 0 0 000
8 Halt   0 0 000  End: bi_d.default

sqlite> explain insert into d2 values (random());
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 20000  Start at 20
1 OpenWrite  0 4 0 1  00  root=4 iDb=0; d2
2 OpenWrite  1 5 0 k(2,,) 00  root=5 iDb=0; 
sqlite_autoindex_d2_1
3 Integer-16 000  r[6]=-1
4 Function0  0 0 7 random(0)  00  r[7]=func(r[0])
5 Copy   7 8 000  r[8]=r[7]
6 Affinity   7 1 0 D  00  affinity(r[7])
7 Program4 199 program01  Call: bi_d2.default
8 NewRowid   0 1 000  r[1]=rowid
9 Copy   8 2 000  r[2]=r[8]
10Affinity   2 1 0 D  00  affinity(r[2])
11SCopy  2 4 000  r[4]=r[2]; id
12IntCopy1 5 000  r[5]=r[1]; rowid
13MakeRecord 4 2 300  r[3]=mkrec(r[4..5]); 
for sqlite_autoindex_d2_1
14NoConflict 1 164 1  00  key=r[4]
15Halt   1555  2 0 d2.id  02
16IdxInsert  1 3 4 2  10  key=r[3]
17MakeRecord 2 

[sqlite] Foreign key help

[x]

If I have foreign keys in place but always have foreign_keys = OFF then one day 
start SQLite with foreign_keys = ON what happens? Does SQLite suddenly check 
all foreign keys and report / delete violations or does it leave everything as 
is and just enforce foreign keys from that point on?

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

Re: [sqlite] [EXTERNAL] Re: Possible User Defined Function (UDF) Bug?

[Hick Gunter]

I suspect that the udf() function is called once to build the record and once 
again to build the parameter list for the trigger program.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von no...@null.net
Gesendet: Dienstag, 28. November 2017 15:04
An: SQLite mailing list 
Betreff: [EXTERNAL] Re: [sqlite] Possible User Defined Function (UDF) Bug?

Here is a trimmed-down test case for my issue:

CREATE TABLE d (
id INTEGER NOT NULL PRIMARY KEY
);


CREATE TRIGGER
bi_d
BEFORE INSERT ON
d
FOR EACH ROW
BEGIN
select 1;
END;


INSERT INTO
d
VALUES(
udf(1)
);

VDBE Program Listing:
   0 Init 0   200   00 Start at 20
   1 OpenWrite020 1 00 root=2 iDb=0; d
   2 Function0153 udf(1)01 r[3]=func(r[5])
   3 NotNull  350   00 if r[3]!=NULL goto 5
   4 Integer -130   00 r[3]=-1
   5 MustBeInt300   00
   6 Function0164 udf(1)01 r[4]=func(r[6])
   7 Copy 470   00 r[7]=r[4]
   8 Affinity 410 D 00 affinity(r[4])
   9 Program  1   198 program   00 Call: bi_d.default
  10 Copy 710   00 r[1]=r[7]
  11 NotNull  1   130   00 if r[1]!=NULL goto 13
  12 NewRowid 010   00 r[1]=rowid
  13 MustBeInt100   00
  14 SoftNull 200   00 r[2]=NULL
  15 NotExists0   171   00 intkey=r[1]
  16 Halt  155520 d.id  02
  17 MakeRecord   219 D 00 r[9]=mkrec(r[2])
  18 Insert   091 d 31 intkey=r[1] data=r[9]
  19 Halt 000   00
  20 Transaction  012 0 01 usesStmtJournal=1
  21 TableLock021 d 00 iDb=0 root=2 write=1
  22 Integer  150   00 r[5]=1
  23 Integer  160   00 r[6]=1
  24 Goto 010   00

Note the two calls to Function0 in the above. If you create a udf() function 
that prints a message you will see that it does in fact get called twice.

I only see this happening when all three of the following statements are true:

- The UDF is used in a VALUES() statement
- The destination table "d" has a PRIMARY KEY
- There is a BEFORE INSERT trigger on "d"

--
Mark Lawrence
___
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] Possible User Defined Function (UDF) Bug?

[nomad]

Here is a trimmed-down test case for my issue:

CREATE TABLE d (
id INTEGER NOT NULL PRIMARY KEY
);


CREATE TRIGGER
bi_d
BEFORE INSERT ON
d
FOR EACH ROW
BEGIN
select 1;
END;


INSERT INTO
d
VALUES(
udf(1)
);

VDBE Program Listing:
   0 Init 0   200   00 Start at 20
   1 OpenWrite020 1 00 root=2 iDb=0; d
   2 Function0153 udf(1)01 r[3]=func(r[5])
   3 NotNull  350   00 if r[3]!=NULL goto 5
   4 Integer -130   00 r[3]=-1
   5 MustBeInt300   00
   6 Function0164 udf(1)01 r[4]=func(r[6])
   7 Copy 470   00 r[7]=r[4]
   8 Affinity 410 D 00 affinity(r[4])
   9 Program  1   198 program   00 Call: bi_d.default
  10 Copy 710   00 r[1]=r[7]
  11 NotNull  1   130   00 if r[1]!=NULL goto 13
  12 NewRowid 010   00 r[1]=rowid
  13 MustBeInt100   00
  14 SoftNull 200   00 r[2]=NULL
  15 NotExists0   171   00 intkey=r[1]
  16 Halt  155520 d.id  02
  17 MakeRecord   219 D 00 r[9]=mkrec(r[2])
  18 Insert   091 d 31 intkey=r[1] data=r[9]
  19 Halt 000   00
  20 Transaction  012 0 01 usesStmtJournal=1
  21 TableLock021 d 00 iDb=0 root=2 write=1
  22 Integer  150   00 r[5]=1
  23 Integer  160   00 r[6]=1
  24 Goto 010   00

Note the two calls to Function0 in the above. If you create a udf()
function that prints a message you will see that it does in fact get
called twice.

I only see this happening when all three of the following statements
are true:

- The UDF is used in a VALUES() statement
- The destination table "d" has a PRIMARY KEY
- There is a BEFORE INSERT trigger on "d"

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

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[David Raymond]

Along with the table_info pragma you could also look at the output of an 
explain statement to see how it gets the value. When it's a rowid table it uses 
a "Rowid" opcode, vs using a "Column" opcode for a non-rowid table.

sqlite> create table rowidPK
   ...> (
   ...>   x integer,
   ...>   y text,
   ...>   primary key (x)
   ...> );

sqlite> create table separatePK
   ...> (
   ...>   x integer primary key desc,
   ...>   y text
   ...> );

sqlite> pragma table_info(rowidPK);
cid|name|type|notnull|dflt_value|pk
0|x|integer|0||1
1|y|text|0||0

sqlite> pragma table_info(separatePK);
cid|name|type|notnull|dflt_value|pk
0|x|integer|0||1
1|y|text|0||0

sqlite> explain select x from rowidPK not indexed;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 8 000  Start at 8
1 OpenRead   0 3 0 0  00  root=3 iDb=0; rowidPK
2 Explain0 0 0 SCAN TABLE rowidPK  00
3 Rewind 0 7 000
4   Rowid  0 1 000  r[1]=rowid
5   ResultRow  1 1 000  output=r[1]
6 Next   0 4 001
7 Halt   0 0 000
8 Transaction0 0 100  01  usesStmtJournal=0
9 Goto   0 1 000

sqlite> explain select x from separatePK not indexed;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 8 000  Start at 8
1 OpenRead   0 4 0 1  00  root=4 iDb=0; 
separatePK
2 Explain0 0 0 SCAN TABLE separatePK  00
3 Rewind 0 7 000
4   Column 0 0 100  r[1]=separatePK.x
5   ResultRow  1 1 000  output=r[1]
6 Next   0 4 001
7 Halt   0 0 000
8 Transaction0 0 100  01  usesStmtJournal=0
9 Goto   0 1 000

sqlite>

It's too bad explain can't be used as a sub-query though.

sqlite> select opcode from (explain select x from rowidPK not indexed);
Error: near "select": syntax error


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Clemens Ladisch
Sent: Tuesday, November 28, 2017 3:39 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? 
Needed by many tools

Peter Halasz wrote:
> However, it's quite clear to me, as only a user of just a few of these
> tools and libraries, that they would be improved greatly if they had a
> direct way of querying which field in a SQLite table was acting as an alias
> of ROW ID.

The rowid column must have the type "integer", and must be the only
column in the primary key:

SELECT name
FROM pragma_table_info('MyTable')
WHERE type = 'integer' COLLATE NOCASE
GROUP BY pk != 0
HAVING max(pk) = 1;

(Before version 3.16.0, this was harder without pragma_table_info().)

But there is no reliable way to check for the PRIMARY KEY DESC exception,
or whether a table is a WITHOUT ROWID table.  You could try to read the
rowid/oid/_rowid_ columns, as long as the table does not use all three
of these names for other columns.


Regards,
Clemens
___
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] EXISTS optimisation?

[Clemens Ladisch]

Dinu wrote:
> I triple-checked it and it is indeed generating different execution plans

Probably different indexes?  Are these actual tables?

>> select * from t where exists (select * from lookup where x = t.x);
>> select * from t where x in (select x from lookup);
>> select t.* from t join lookup using (x);
>
> However, I am a bit confused by the examples you provided:
> The 3rd query is the equivalent of the first 2 only if the lookup table has
> an unique index on x (which it does). However, this would make a very
> restricted case of why you would use EXISTS().

It's just another example of how a query is executed the same; I was not
implying that it would be preferrable over the other ones.

(The third case is interesting if there is no index on the lookup column,
because with a join, SQLite estimates that it's worthwhile to create
a temporary index on the lookup column.  IN would create a temporary
index for the result of the subquery, while EXISTS would still execute
the subquery (now a table scan) for every outer row.)


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

Re: [sqlite] Getting an advance list of RowIDs for a query result set

[x]

>What about changing the remaining inner join to left join

>Select BaseTbl.RowID
>from BaseTbl
>left join Tbl_2 on Tbl2.Y = BaseTbl.Y
>where BaseTbl.Col=?

>and see if the SQLiter optimizer now leaves Tbl_2 out from the query
>plan.  It will only do that if it is not a 1-to-n join.

If Tbl_2 isn’t involved in the columns, where or order by then changing it to 
left join will mean it will definitely be left out so I don’t get what you mean 
E.Pasma.


I’ve now realised what caused my earlier confusion regarding inner joins. I was 
getting mixed up with foreign keys which do offer a way of finding redundant 
inner joins.

If a Tbl (which is not involved in the query columns, where or order by) is 
inner joined to BaseTbl on all the columns of a unique index and it turns out 
there is a foreign key matching that join then the Tbl can be left out if the 
BaseTbl columns are defined as NOT NULL. If they are allowed to be NULL then it 
can still be left out provided the NOT NULL condition(s) is added to the where.

Using the earlier example

Select BaseTbl.RowID
from BaseTbl
left join Tbl_1 on Tbl_1.X = BaseTbl.X
Inner join Tbl_2 on Tbl2.Y = BaseTbl.Y
left join Tbl_3 on Tbl3.Z = BaseTbl.Z
where BaseTbl.Col=?

If BaseTbl has FOREIGN KEY(Y) REFERENCES Tbl_2(Y) then

If BaseTbl.Y is defined as NOT NULL the query can be reduced to

Select BaseTbl.RowID
from BaseTbl
where BaseTbl.Col=?

If BaseTbl.Y allows NULLs then the query can be reduced to

Select BaseTbl.RowID
from BaseTbl
where BaseTbl.Col=? and BaseTbl.Y IS NOT NULL

Could someone confirm I’ve got the above right?

If I’m correct, I wonder if the optimiser takes (or could take) this into 
account. As lookup tables are probably the main use of foreign keys you’d think 
there’d be no shortage of such joins. That said, I suppose if you’re aware of 
what you want the optimiser to do it wouldn’t offer any advantage over left 
joining and adding the appropriate NOT NULL constraint(s) to the WHERE.

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

Re: [sqlite] Recursive aggregate query?

[nomad]

On Tue Nov 28, 2017 at 10:13:56AM +0100, no...@null.net wrote:
> I don't understand the error message generated by the following
> schema/query:
> 
> CREATE TABLE x(
> id integer
> );
> 
...
> 
> WITH
> x
> AS

Woops - I just realized the CTE uses a name already defined as a table.
Sorry for the noise.

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

[sqlite] Recursive aggregate query?

[nomad]

I don't understand the error message generated by the following
schema/query:

CREATE TABLE x(
id integer
);

CREATE TABLE y(
id integer
);

CREATE TABLE y_sequence (
seq INTEGER PRIMARY KEY AUTOINCREMENT
);


WITH
x
AS
(SELECT
0 AS name,
0 as rows

  UNION ALL

SELECT
'sqlite_sequence' AS "name",
COUNT(*) AS rows
FROM
sqlite_sequence

  UNION ALL

SELECT
'y_sequence' AS "name",
COUNT(*) AS rows
FROM
y_sequence

  UNION ALL

SELECT
'y' AS "name",
COUNT(*) AS rows
FROM
y

  UNION ALL

SELECT
'x' AS "name",
COUNT(*) AS rows
FROM
x
)
SELECT
sm.name AS name,
x.rows AS rows,
SUM(CASE
WHEN
sm2.type="index"
THEN
1
ELSE
0
END) AS "indexes",
SUM(CASE
WHEN
sm2.type="trigger"
THEN
1
ELSE
0
END) AS "triggers",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%BEFORE INSERT%"
THEN
1
ELSE
0
END) AS "bi",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%AFTER INSERT%"
THEN
1
ELSE
0
END) AS "ai",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%BEFORE UPDATE%"
THEN
1
ELSE
0
END) AS "bu",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE
"%AFTER UPDATE%"
THEN
1
ELSE
0
END) AS "au",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%BEFORE DELETE%"
THEN
1
ELSE
0
END) AS "bd",
SUM(CASE
WHEN
sm2.type="trigger" AND sm2.sql LIKE "%AFTER DELETE%"
THEN
1
ELSE
0
END) AS "ad"
FROM
sqlite_master sm
  LEFT JOIN
sqlite_master sm2
  ON
sm2.tbl_name = sm.tbl_name
  LEFT JOIN
x
  ON
x.name = sm.tbl_name
WHERE
sm.tbl_name NOT LIKE ? AND sm.type = ?
GROUP BY
sm.name
ORDER BY
sm.name
;

DBD::SQLite::db prepare failed: recursive aggregate queries not supported 
at /usr/lib/perl5/site_perl/5.22/DBIx/ThinSQL.pm line 250.


I don't quite see where the recursive aggregate is. Can anyone
enlighten me?

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

Re: [sqlite] sites inaccessible

[Dominique Devienne]

On Sat, Oct 29, 2016 at 8:09 AM, Dan Kennedy  wrote:

> On 10/29/2016 12:28 PM, jungle Boogie wrote:
>
>> Hi Dr. Hipp,
>>
>> Probably a low concern for you at 1:30am your time but I can't connect
>> to fossil-scm.org or sqlite.org over port 80.
>>
>> $ curl http://sqlite.org/
>> curl: (7) Failed to connect to sqlite.org port 80: Connection refused
>>
>> $ curl http://fossil-scm.org
>> curl: (7) Failed to connect to fossil-scm.org port 80: Connection refused
>>
>
> Thanks for posting this. Restarted xinetd and things seem to be working
> again.


It's not for me. I sent a private email yesterday to DRH about this too.
--DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] journal_size_limit is ignored when using SQLITE_DBCONFIG_NO_CKPT_ON_CLOSE

[Kniep Stefan (CM/ESN3)]

Hi Simon,

thanks for the hint. I won't do that.
--
Stefan


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

Re: [sqlite] EXISTS optimisation?

[Dinu]

Thanks Clemens,

I triple-checked it and it is indeed generating different execution plans,
with the queries being absolutely equivalent. I will try to produce a
minimal test case (right now the query where this occurs is a 100 lines long
monster). 

However, I am a bit confused by the examples you provided:
The 3rd query is the equivalent of the first 2 only if the lookup table has
an unique index on x (which it does). However, this would make a very
restricted case of why you would use EXISTS(). 

In my case, this is completely opposite: x is definitely not unique in the
lookup table, that's precisely why I'm using EXISTS or IN, to avoid the row
multiplication generated by a JOIN.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Any chance of exposing INTEGER PRIMARY KEY metadata? Needed by many tools

[Clemens Ladisch]

Peter Halasz wrote:
> However, it's quite clear to me, as only a user of just a few of these
> tools and libraries, that they would be improved greatly if they had a
> direct way of querying which field in a SQLite table was acting as an alias
> of ROW ID.

The rowid column must have the type "integer", and must be the only
column in the primary key:

SELECT name
FROM pragma_table_info('MyTable')
WHERE type = 'integer' COLLATE NOCASE
GROUP BY pk != 0
HAVING max(pk) = 1;

(Before version 3.16.0, this was harder without pragma_table_info().)

But there is no reliable way to check for the PRIMARY KEY DESC exception,
or whether a table is a WITHOUT ROWID table.  You could try to read the
rowid/oid/_rowid_ columns, as long as the table does not use all three
of these names for other columns.


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

Re: [sqlite] How to use sqlite3_table_column_metadata?

[Clemens Ladisch]

Bart Smissaert wrote:
> If I pass a valid connection  and  valid table name but null for all other
> input parameters I get:
>
> return value:  0
>
> How does that work?

The documentation 
says:
| If the column-name parameter to sqlite3_table_column_metadata() is
| a NULL pointer, then this routine simply checks for the existence of
| the table and returns SQLITE_OK if the table exists and SQLITE_ERROR
| if it does not.


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