[sqlite] JSON expressions for records and synchoronisation

[Simon Slavin]

A web site of no official standing features two JSON formats which might 
interest readers of this list.

The first is for describing a table of data:



It does something close to what a CREATE TABLE statement does: define fields, 
their types, and their constraints.

The second is for expressing database changes in a way useful for 
synchronisation of multiple copies of a table of data across HTTP:




With a cursory glance I do see important incompatibilities with SQLite.  The 
first of those two pages defines an empty string (a zero-length string) as 
equivalent to a missing value, whereas SQLite makes a distinction between that 
and NULL.  It then goes on to declare a field type called 'null' for reasons 
that make no sense to me.

Simon.

[sqlite] Regression with pragma index_list version 3.8.9

[gwenn]

Hello,
There are two extra columns (origin, partial) in the result returned
by pragma index_list.
Just for your information, it causes a regression test failure because
only 3 columns are expected:
--- FAIL: TestTableIndexes (0.00s)
meta_test.go:163: error listing indexes: incorrect argument count for
Stmt.Scan: have 3 want 5 (wrapper specific error)

if err = s.Scan(nil, &i.Name, &i.Unique); err != nil {

I will patch my code...
Regards.

[sqlite] Request: Metadata about C API constants and functions

[R.Smith]

On 2015-04-10 04:11 PM, Richard Hipp wrote:
> https://www.sqlite.org/toc.db

Thank you Richard!
Will this link always have the latest DB?


This db format is preferred, but in case anyone is still interested in 
the webby formats, here is the DB in XML:
http://sqlc.rifin.co.za/toc.xml

and JSON:
http://sqlc.rifin.co.za/toc.json

[sqlite] step and reset or reset and step?

[Paolo Bolzoni]

Dear list,

The subject already says it all, I was wondering what is the best
practice for a prepared statement that need to be used an unknown
number of times.

It is better to reset and step (as many times as needed) or step
and reset after? Or there is no real difference?

Yours faithfully,
Paolo

[sqlite] Request: Metadata about C API constants and functions

[Simon Slavin]

On 10 Apr 2015, at 5:00pm, R.Smith  wrote:

> On 2015-04-10 04:11 PM, Richard Hipp wrote:
>> https://www.sqlite.org/toc.db
> 
> Thank you Richard!
> Will this link always have the latest DB?

If I may suggest ... in that database, introduce a new column which holds the 
version in which that call was introduced.  This would allow people to 
understand why FOREIGN KEYs, for example, don't work in the version of SQLite3 
that they're stuck with.

Don't really care what you do for initial values.  You could leave them all 
null which means 'was already implemented by version 3.8.9.  Or you could find 
some people who would want to put the correct values in.

Simon.

[sqlite] Regarding SeekGe and Next opcodes in VDBE

[Sairam Gaddam]

Yes sir, I know about " .explain ". Next time I would try that @ Richard
Hipp.
And thanks Clemens.

On Fri, Apr 10, 2015 at 3:57 PM, Clemens Ladisch  wrote:

> Sairam Gaddam wrote:
> > On Thu, Apr 9, 2015 at 1:04 PM, Clemens Ladisch 
> wrote:
> >> Sairam Gaddam wrote:
> >>> sql="create table em(name text primary key,age text,pts text);"\
> >>> "create table l(name text primary key,fame text);";
> >>>
> >>> sql = "select * from em,l where l.fame=em.age";
> >>>
> >>>4 Once 0   130   00
> >>>5 OpenAutoindex230 k(3,nil,nil,nil) 00
> >>>6 Rewind   1   130   00
> >>>7 Column   112   00
> >>>8 Column   103   00
> >>>9 Rowid140   00
> >>>   10 MakeRecord   231   00
> >>>   11 IdxInsert210   10
> >>>   12 Next 170   03
> >>>   13 Column   015   00
> >>>   14 IsNull   5   240   00
> >>>   15 SeekGE   2   245 1 00
> >>>   16 IdxGT2   245 1 00
> >>>   17 Column   006   00
> >>>   18 Copy 570   00
> >>>   19 Column   028   00
> >>>   20 Column   219   00
> >>>   21 Column   20   10   00
> >>>   22 ResultRow650   00
> >>>   23 Next 2   160   00
> >>>   24 Next 040   01
> >>>   ...
> >>>
> >>> whenever the condition in the where clause is false, the program jumps
> to
> >>> the instruction pointed by p2 of SeekGe
> >>
> >> Yes.
> >>
> >>> but if the condition proves to be false for the row 1 of both the
> >>> tables, then the program jumps to line 24(in this case) which
> >>> corresponds to outer table and takes the second row of outer table
> >>> for next iteration, then when will the program fetch 1st row
> >>> of table-1 and remaining rows of table-2 ???
> >>
> >> In the join loop, this VDBE program does not fetch any rows from the
> >> second table:
> >>
> >>   explain query plan select * from em,l where l.fame=em.age;
> >>   0|0|0|SCAN TABLE em
> >>   0|1|1|SEARCH TABLE l USING AUTOMATIC COVERING INDEX (fame=?)
> >>
> >> All accesses to "l" are actually handled by the temporary index (which
> >> is created by instructions 5..12).  One index search is enough to
> >> determine whether a fame value exists.
> >
> > Then why there is a loop (Next opcode at 23rd instruction) over second
> > table when it created an index ?
>
> Because there might be multiple index entries with the same fame value.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Sqlite 2 and Windows 7

[Ketil Froyn]

On 10 Apr 2015 15:05, "Gabriel Tiste"  wrote:
>
> I updated the faulty column to an empty string and noticed that I could
select all records in that table. That must be a sign that something was
written to that column that sqlite could not parse.
>
> Question:
> Are there a way to dump the database without any validation or error
checking? Can I access the content in the sqlite file somehow to see what
really resides in that column that makes it corrupt?
>

A hex editor?

Ketil

[sqlite] Index causing very slow queries

[Hamish Symington]

>> A follow up to this. If I run ANALYZE on the ?fast? version of the database
>> - ie *after* I?ve recreated the index - performance drops back to the
>> original slow speed.
> 
> Please run ".fullschema" using the sqlite3.exe command-line shell on
> your original database and send us the output.

Hi there,

Thanks for your reply, Richard. I?ve done that, and it?s listed all the CREATE 
TABLE and CREATE INDEX commands, as well as all the triggers I?ve got in there, 
and a bunch of ?INSERT INTO sqlite_stat1? statements. 

I don?t want to share the result in a public forum, because I don?t want the 
structure of our tables or the code of our triggers to be out there in the wild 
(normally our database is encrypted, so nobody without the key can look into 
it). I?m happy to send it to you off-list if you?ll keep it confidential and 
would find it useful, or I can strip out all the triggers and, perhaps, the 
tables which aren?t relevant, and post it publicly?  If that would help, do 
tell me what it is that you?re wanting to look at.

Best wishes,

Hamish

PS Not that it should make any difference, but I?m running Mac OS X. I can run 
the same tests on Windows if it would be helpful.

--
Hamish Symington
07815 081282  :  info at hamishsymington.com

I also set custom cryptic crosswords: www.customcrypticcrosswords.com 
(@CustomCryptics on Twitter)

[sqlite] Sqlite 2 and Windows 7

[Simon Slavin]

On 10 Apr 2015, at 3:09pm, Ketil Froyn  wrote:

> A hex editor?

Unless a page boundary is crossed, the data for one row of a table is all held 
together in the database file.  So if you can find the value of another column 
in the same row, and search for that using a hex editor, you should find the 
whole of that row.

Simon.

[sqlite] Regarding SeekGe and Next opcodes in VDBE

[Sairam Gaddam]

Then why there is a loop (Next opcode at 23rd instruction) over second
table when it created an index ?

On Thu, Apr 9, 2015 at 1:04 PM, Clemens Ladisch  wrote:

> Sairam Gaddam wrote:
> > sql="create table em(name text primary key,age text,pts text);"\
> > "create table l(name text primary key,fame text);";
> >
> > sql = "select * from em,l where l.fame=em.age";
> >
> >4 Once 0   130   00
> >5 OpenAutoindex230 k(3,nil,nil,nil) 00
> >6 Rewind   1   130   00
> >7 Column   112   00
> >8 Column   103   00
> >9 Rowid140   00
> >   10 MakeRecord   231   00
> >   11 IdxInsert210   10
> >   12 Next 170   03
> >   13 Column   015   00
> >   14 IsNull   5   240   00
> >   15 SeekGE   2   245 1 00
> >   16 IdxGT2   245 1 00
> >   17 Column   006   00
> >   18 Copy 570   00
> >   19 Column   028   00
> >   20 Column   219   00
> >   21 Column   20   10   00
> >   22 ResultRow650   00
> >   23 Next 2   160   00
> >   24 Next 040   01
> >   ...
> >
> > whenever the condition in the where clause is false, the program jumps to
> > the instruction pointed by p2 of SeekGe
>
> Yes.
>
> > but if the condition proves to be false for the row 1 of both the
> > tables, then the program jumps to line 24(in this case) which
> > corresponds to outer table and takes the second row of outer table
> > for next iteration, then when will the program fetch 1st row
> > of table-1 and remaining rows of table-2 ???
>
> In the join loop, this VDBE program does not fetch any rows from the
> second table:
>
>   explain query plan select * from em,l where l.fame=em.age;
>   0|0|0|SCAN TABLE em
>   0|1|1|SEARCH TABLE l USING AUTOMATIC COVERING INDEX (fame=?)
>
> All accesses to "l" are actually handled by the temporary index (which
> is created by instructions 5..12).  One index search is enough to
> determine whether a fame value exists.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Index causing very slow queries

[Hamish Symington]

Hi Simon,

Thanks for this. Removing the date() around Appointment_StartDate, and the 
trim() from around the ?, makes no difference. All results are similar - slow, 
till I drop and recreate the index (ie then fast); then becoming slow again 
when I run the ANALYZE command. 

Best wishes,

Hamish

>> LEFT JOIN Appointment ON Appointment_TxnUUID=Txn_UUID 
>>  WHERE ( date( Appointment_StartDate ) >= trim( ? ) AND date( 
>> Appointment_StartDate ) <= trim( ? ) ) )
> 
> The application of a function to the column values means that it is not 
> possible for the optimiser to penetrate this WHERE clause and work out a good 
> strategy.  Try to change this to
> WHERE Appointment_StartDate BETWEEN ? AND ?
> by converting the parameters you bind in to text, rather than by asking 
> SQLite to convert every value in the column to a date.
> 
> This isn't the only thing that's causing a problem, but it might be the 
> easiest to fix.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
Hamish Symington
07815 081282  :  info at hamishsymington.com

I also set custom cryptic crosswords: www.customcrypticcrosswords.com 
(@CustomCryptics on Twitter)

[sqlite] Index causing very slow queries

[Simon Slavin]

On 10 Apr 2015, at 2:24pm, Hamish Symington  wrote:

> LEFT JOIN Appointment ON Appointment_TxnUUID=Txn_UUID 
>   WHERE ( date( Appointment_StartDate ) >= trim( ? ) AND date( 
> Appointment_StartDate ) <= trim( ? ) ) )

The application of a function to the column values means that it is not 
possible for the optimiser to penetrate this WHERE clause and work out a good 
strategy.  Try to change this to

WHERE Appointment_StartDate BETWEEN ? AND ?

by converting the parameters you bind in to text, rather than by asking SQLite 
to convert every value in the column to a date.

This isn't the only thing that's causing a problem, but it might be the easiest 
to fix.

Simon.

[sqlite] Index causing very slow queries

[Hamish Symington]

Hi there,

A follow up to this. If I run ANALYZE on the ?fast? version of the database - 
ie *after* I?ve recreated the index - performance drops back to the original 
slow speed.

Hamish

> On 10 Apr 2015, at 14:24, Hamish Symington  
> wrote:
> 
> Hello,
> 
> I have a curiously slow query, and I?m not sure if it?s caused by something 
> I?m doing (most likely) or by something odd in sqlite (almost certainly not). 
> 
> The tables we have are: 
> Appointment(Appointment_UUID, Appointment_TxnUUID, Appointment_StartDate, and 
> other columns)
> Txn(Txn_UUID, Txn_Created, Txn_Reference amongst others). 
> TxnCalc(TxnCalc_TxnUUID, TxnCalc_Date amongst others).
> 
> The query I am running is this (using a prepared statement): 
> 
> SELECT TxnCalc_TxnUUID AS Txn_UUID 
>   FROM (
>   SELECT Txn.Txn_UUID AS TempTxn_UUID 
>   FROM Txn 
>   LEFT JOIN Appointment ON Appointment_TxnUUID=Txn_UUID 
>   WHERE ( date( Appointment_StartDate ) >= trim( ? ) AND date( 
> Appointment_StartDate ) <= trim( ? ) ) ) 
>   AS SubQueryResult,
>   TxnCalc, 
>   Txn 
>   WHERE TxnCalc_TxnUUID=TempTxn_UUID 
>   AND TxnCalc_TxnUUID=Txn_UUID 
>   GROUP BY TxnCalc_TxnUUID 
>   ORDER BY TxnCalc_Date DESC, 
>   Txn_Reference DESC, 
>   Txn_Created DESC
> 
> If possible, I?d like you to ignore the structure of the query, which isn?t 
> quite as good as it could be, I know; it?s the next bit that?s curious. 
> 
> We have indexes on Txn_UUID, TxnCalc_TxnUUID, Appointment_UUID - nothing 
> controversial there - and also on Appointment_TxnUUID. That field has many, 
> many blank values (as Appointments are most often linked to other things) and 
> only a very few non-blank values. They?re blank (ie ??) and not NULL, if that 
> makes a difference. 
> 
> When running that query using SQLite 3.7.14.1, it takes around 230ms to run. 
> The query plan is this:
> SCAN TABLE Txn (~3670 rows)
> SEARCH TABLE Appointment USING AUTOMATIC COVERING INDEX 
> (Appointment_TxnUUID=?) (~5 rows)
> SEARCH TABLE TxnCalc USING INDEX idx_TxnCalc_TxnUUID (TxnCalc_TxnUUID=?) (~1 
> rows)
> SEARCH TABLE Txn USING INDEX idx_Txn_UUID (Txn_UUID=?) (~1 rows)
> USE TEMP B-TREE FOR GROUP BY
> USE TEMP B-TREE FOR ORDER BY
> Interestingly, it?s not using the index which we created for the SEARCH TABLE 
> Appointment.
> 
> When running the query using SQLite 3.8.8, it takes around 1600ms to run. The 
> query plan is this:
> SCAN TABLE Txn USING COVERING INDEX idx_Txn_UUID
> SCAN TABLE Appointment
> SEARCH TABLE TxnCalc USING INDEX idx_TxnCalc_TxnUUID (TxnCalc_TxnUUID=?)
> SEARCH TABLE Txn USING INDEX idx_Txn_UUID (Txn_UUID=?)
> USE TEMP B-TREE FOR GROUP BY
> USE TEMP B-TREE FOR ORDER BY
> ie no index on Appointment at all. 
> 
> Now for the curious bit. If I drop the index which I made, and recreate it 
> using the exact same code with which I created it when I made my database - 
> CREATE INDEX idx_Appointment_TxnUUID ON Appointment(Appointment_TxnUUID) - 
> using sqlite 3.7.14.1 the query is down to 12ms, and using 3.8.8, it?s down 
> to 4ms. 
> 
> With the database in its ?slow? state, running ANALYZE makes no difference. 
> Likewise, REINDEX idx_Appointment_TxnUUID makes no difference. 
> 
> Background: I create the database, make the indexes, and then do a bunch of 
> INSERTs into it, along with UPDATES, DELETES etc. - basically using it as a 
> normal workhorse database. 
> 
> So, the question, after this somewhat long explanation: should deleting and 
> recreating an index like this cause such a massive performance improvement? 
> If so, why? What am I doing wrong in the first place? 
> 
> Thanks in advance for your assistance,
> 
> Hamish
> 
> --
> Hamish Symington
> 07815 081282  :  info at hamishsymington.com
> 
> I also set custom cryptic crosswords: www.customcrypticcrosswords.com 
> (@CustomCryptics on Twitter)
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

--
Hamish Symington
07815 081282  :  info at hamishsymington.com

I also set custom cryptic crosswords: www.customcrypticcrosswords.com 
(@CustomCryptics on Twitter)

[sqlite] Request: Metadata about C API constants and functions

[R.Smith]

On 2015-04-10 02:09 PM, Simon Slavin wrote:
> On 10 Apr 2015, at 12:53pm, R.Smith  wrote:
>
>> It doesn't matter, C API calls or pragma, whatever works best as long as 
>> there is /some/ way to get to that info.  Usually though, most C API calls 
>> valuable to end users too, ends up having a pragma that can produce similar 
>> output.
> I think there are two different things being discussed here:
>
> The original user was asking for details about the calls built into the 
> SQLite API like sqlite3_step().
>
> The other user was asking for details about what loadable extensions are 
> currently loaded.

Right you are, my bad, both issues are important though: I see Roger 
already asked for a compiled version of the db containing the 
information as Richard suggested exists - which would make documentation 
linking much easier for any wrapper or gui programmer and would 
definitely be a boon - I'd like this very much too. (And I'd prefer it 
in SQLite DB format as I'm sure Roger would, just did not realise that 
is even an option, usually documenty things are all XMLed up).

And then there is the ability to query available function calls 
registered (or embedded) with the SQLite engine you are using at the 
moment (which might well be linked via a library plus loadable 
extensions). This would be fantastic to have too.

As for the usual C API calls supported, I think knowing the version 
number (which you can already obtain via query or API) suffices, so 
happy there if the DB mentioned above is available.

[sqlite] Index causing very slow queries

[Hamish Symington]

Hello,

I have a curiously slow query, and I?m not sure if it?s caused by something I?m 
doing (most likely) or by something odd in sqlite (almost certainly not). 

The tables we have are: 
Appointment(Appointment_UUID, Appointment_TxnUUID, Appointment_StartDate, and 
other columns)
Txn(Txn_UUID, Txn_Created, Txn_Reference amongst others). 
TxnCalc(TxnCalc_TxnUUID, TxnCalc_Date amongst others).

The query I am running is this (using a prepared statement): 

SELECT TxnCalc_TxnUUID AS Txn_UUID 
FROM (
SELECT Txn.Txn_UUID AS TempTxn_UUID 
FROM Txn 
LEFT JOIN Appointment ON Appointment_TxnUUID=Txn_UUID 
WHERE ( date( Appointment_StartDate ) >= trim( ? ) AND date( 
Appointment_StartDate ) <= trim( ? ) ) ) 
AS SubQueryResult,
TxnCalc, 
Txn 
WHERE TxnCalc_TxnUUID=TempTxn_UUID 
AND TxnCalc_TxnUUID=Txn_UUID 
GROUP BY TxnCalc_TxnUUID 
ORDER BY TxnCalc_Date DESC, 
Txn_Reference DESC, 
Txn_Created DESC

If possible, I?d like you to ignore the structure of the query, which isn?t 
quite as good as it could be, I know; it?s the next bit that?s curious. 

We have indexes on Txn_UUID, TxnCalc_TxnUUID, Appointment_UUID - nothing 
controversial there - and also on Appointment_TxnUUID. That field has many, 
many blank values (as Appointments are most often linked to other things) and 
only a very few non-blank values. They?re blank (ie ??) and not NULL, if that 
makes a difference. 

When running that query using SQLite 3.7.14.1, it takes around 230ms to run. 
The query plan is this:
SCAN TABLE Txn (~3670 rows)
SEARCH TABLE Appointment USING AUTOMATIC COVERING INDEX (Appointment_TxnUUID=?) 
(~5 rows)
SEARCH TABLE TxnCalc USING INDEX idx_TxnCalc_TxnUUID (TxnCalc_TxnUUID=?) (~1 
rows)
SEARCH TABLE Txn USING INDEX idx_Txn_UUID (Txn_UUID=?) (~1 rows)
USE TEMP B-TREE FOR GROUP BY
USE TEMP B-TREE FOR ORDER BY
Interestingly, it?s not using the index which we created for the SEARCH TABLE 
Appointment.

When running the query using SQLite 3.8.8, it takes around 1600ms to run. The 
query plan is this:
SCAN TABLE Txn USING COVERING INDEX idx_Txn_UUID
SCAN TABLE Appointment
SEARCH TABLE TxnCalc USING INDEX idx_TxnCalc_TxnUUID (TxnCalc_TxnUUID=?)
SEARCH TABLE Txn USING INDEX idx_Txn_UUID (Txn_UUID=?)
USE TEMP B-TREE FOR GROUP BY
USE TEMP B-TREE FOR ORDER BY
ie no index on Appointment at all. 

Now for the curious bit. If I drop the index which I made, and recreate it 
using the exact same code with which I created it when I made my database - 
CREATE INDEX idx_Appointment_TxnUUID ON Appointment(Appointment_TxnUUID) - 
using sqlite 3.7.14.1 the query is down to 12ms, and using 3.8.8, it?s down to 
4ms. 

With the database in its ?slow? state, running ANALYZE makes no difference. 
Likewise, REINDEX idx_Appointment_TxnUUID makes no difference. 

Background: I create the database, make the indexes, and then do a bunch of 
INSERTs into it, along with UPDATES, DELETES etc. - basically using it as a 
normal workhorse database. 

So, the question, after this somewhat long explanation: should deleting and 
recreating an index like this cause such a massive performance improvement? If 
so, why? What am I doing wrong in the first place? 

Thanks in advance for your assistance,

Hamish

--
Hamish Symington
07815 081282  :  info at hamishsymington.com

I also set custom cryptic crosswords: www.customcrypticcrosswords.com 
(@CustomCryptics on Twitter)

[sqlite] JSON expressions for records and synchoronisation

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/10/2015 01:51 PM, Simon Slavin wrote:
> With a cursory glance I do see important incompatibilities with
> SQLite.

I use JSON as the data format for $work stuff (startups) for years,
and these JSON schemas etc miss why some of us use JSON.  If you are
using a statically typed language like Java or C++, then arbitrary
data is very difficult to deal with.  XML is very popular in that
world because DTDs mean you can make it statically typed.  The mindset
is that if everything can be specified and constrained in advance,
then successfully compiling the code means it is mostly correct.

JSON like several other languages (eg Python, Ruby) is dynamically
typed.  You can stick anything you want in variables/fields, since the
type is associated with the value, not the variable name.  This allows
for far greater flexibility, and especially means you do not have to
decide in advance exactly what you will do with data.  Dealing with
XML is a huge pain!  Code is trivial to deal with whatever you get.
Importing code does not mean it is correct - running it is how you
figure that out.

These schema and similar efforts seem to be an exercise in getting the
worst of all worlds, combining rigid languages with flexible data by
trying to make the data inflexible.  Use the right tool for the job.
It is also why this and efforts like unsql don't take off - they are
bandaids for a mindset being applied in the wrong place.

/rant

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUoPX8ACgkQmOOfHg372QSywwCfbHzpZxiOz+/D3wO2UgbbNuMS
YGYAniyVNFM9I9JIX2H6Mi9yuiIYV6Np
=1ZX9
-END PGP SIGNATURE-

[sqlite] Request: Metadata about C API constants and functions

[R.Smith]

On 2015-04-10 09:48 AM, Zsb?n Ambrus wrote:
> On 4/10/15, Dominique Devienne  wrote:
>> But that's build time. There is no way (i.e. pragma) to list registered SQL
>> functions at runtime.
>>
>> This is something that I've asked for before, and I'm having trouble
>> understanding why nobody cares.
>>
>> When you run an SQLite "shell", the host program can have enabled/disabled
>> built-in functions at build-time, and/or added any number of custom SQL
>> functions. But you have no way to know.
> Um, you're talking about SQL functions.  But I think Roger asked for C
> functions in the public C api of Sqlite.

It doesn't matter, C API calls or pragma, whatever works best as long as 
there is /some/ way to get to that info.  Usually though, most C API 
calls valuable to end users too, ends up having a pragma that can 
produce similar output.

I would very much like this too.

[sqlite] Sqlite 2 and Windows 7

[Simon Slavin]

On 10 Apr 2015, at 1:35pm, Gabriel Tiste  wrote:

> Your assumption regarding versions are not accurate unfortunatly. We are 
> actually using a sqlite2 database(old application...).

Good grief.  You need a SQLite2 expert.  If you can find one.  Sorry I can't 
help.

Simon.

[sqlite] Sqlite 2 and Windows 7

[Simon Slavin]

On 10 Apr 2015, at 12:33pm, Gabriel Tiste  wrote:

> We can do a select on columns but not select * on affected table except the 
> last column. Including that in the select cause the client to say that table 
> is missing or logic is not correct.
> 
> Are there any known issues with sqlite2

Are you absolutely positively sure you're using SQLite2 and not SQLite3 ?  I'm 
going to assume you actually mean you're using SQLite3 which was released in 
2004.

> and windows 7? How can I investigate whats causing this error when I try to 
> select * from the affected table?

Please execute the following:

PRAGMA database.integrity_check

and give us some idea about what it returns.

You can run the command using your own program, or make a copy of the database 
(on another computer ?) and use the SQLite shell tool.

Simon.

[sqlite] Request: Metadata about C API constants and functions

[Simon Slavin]

On 10 Apr 2015, at 12:53pm, R.Smith  wrote:

> It doesn't matter, C API calls or pragma, whatever works best as long as 
> there is /some/ way to get to that info.  Usually though, most C API calls 
> valuable to end users too, ends up having a pragma that can produce similar 
> output.

I think there are two different things being discussed here:

The original user was asking for details about the calls built into the SQLite 
API like sqlite3_step().

The other user was asking for details about what loadable extensions are 
currently loaded.

Simon.

[sqlite] Sqlite 2 and Windows 7

[Gabriel Tiste]

I updated the faulty column to an empty string and noticed that I could select 
all records in that table. That must be a sign that something was written to 
that column that sqlite could not parse.

Question:
Are there a way to dump the database without any validation or error checking? 
Can I access the content in the sqlite file somehow to see what really resides 
in that column that makes it corrupt?

Best regards,

GT

-Ursprungligt meddelande-
Fr?n: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] F?r Gabriel Tiste
Skickat: den 10 april 2015 14:35
Till: General Discussion of SQLite Database
?mne: Re: [sqlite] Sqlite 2 and Windows 7

Your assumption regarding versions are not accurate unfortunatly. We are 
actually using a sqlite2 database(old application...).

When I use the sqlite.exe tool I got from sqlite.org

CMD:

sqlite.exe db.sqlite
 -> PRAGMA integrity_check;

I get OK.

Is it possible that we have written something to that table that SQL can't 
handle?

Best regards,

GT

-Ursprungligt meddelande-
Fr?n: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] F?r Simon Slavin
Skickat: den 10 april 2015 14:27
Till: General Discussion of SQLite Database
?mne: Re: [sqlite] Sqlite 2 and Windows 7


On 10 Apr 2015, at 12:33pm, Gabriel Tiste  wrote:

> We can do a select on columns but not select * on affected table except the 
> last column. Including that in the select cause the client to say that table 
> is missing or logic is not correct.
> 
> Are there any known issues with sqlite2

Are you absolutely positively sure you're using SQLite2 and not SQLite3 ?  I'm 
going to assume you actually mean you're using SQLite3 which was released in 
2004.

> and windows 7? How can I investigate whats causing this error when I try to 
> select * from the affected table?

Please execute the following:

PRAGMA database.integrity_check

and give us some idea about what it returns.

You can run the command using your own program, or make a copy of the database 
(on another computer ?) and use the SQLite shell tool.

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

[sqlite] Sqlite 2 and Windows 7

[Gabriel Tiste]

Yeah, and most of them are probably retired and sipping 
umbrella-decorated-drinks by the pool now. :)

Thanks anyway.

Best regards,

GT

-Ursprungligt meddelande-
Fr?n: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] F?r Simon Slavin
Skickat: den 10 april 2015 14:39
Till: General Discussion of SQLite Database
?mne: Re: [sqlite] Sqlite 2 and Windows 7


On 10 Apr 2015, at 1:35pm, Gabriel Tiste  wrote:

> Your assumption regarding versions are not accurate unfortunatly. We are 
> actually using a sqlite2 database(old application...).

Good grief.  You need a SQLite2 expert.  If you can find one.  Sorry I can't 
help.

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

[sqlite] Sqlite 2 and Windows 7

[Gabriel Tiste]

Your assumption regarding versions are not accurate unfortunatly. We are 
actually using a sqlite2 database(old application...).

When I use the sqlite.exe tool I got from sqlite.org

CMD:

sqlite.exe db.sqlite
 -> PRAGMA integrity_check;

I get OK.

Is it possible that we have written something to that table that SQL can't 
handle?

Best regards,

GT

-Ursprungligt meddelande-
Fr?n: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] F?r Simon Slavin
Skickat: den 10 april 2015 14:27
Till: General Discussion of SQLite Database
?mne: Re: [sqlite] Sqlite 2 and Windows 7


On 10 Apr 2015, at 12:33pm, Gabriel Tiste  wrote:

> We can do a select on columns but not select * on affected table except the 
> last column. Including that in the select cause the client to say that table 
> is missing or logic is not correct.
> 
> Are there any known issues with sqlite2

Are you absolutely positively sure you're using SQLite2 and not SQLite3 ?  I'm 
going to assume you actually mean you're using SQLite3 which was released in 
2004.

> and windows 7? How can I investigate whats causing this error when I try to 
> select * from the affected table?

Please execute the following:

PRAGMA database.integrity_check

and give us some idea about what it returns.

You can run the command using your own program, or make a copy of the database 
(on another computer ?) and use the SQLite shell tool.

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

[sqlite] Regarding SeekGe and Next opcodes in VDBE

[Clemens Ladisch]

Sairam Gaddam wrote:
> On Thu, Apr 9, 2015 at 1:04 PM, Clemens Ladisch  wrote:
>> Sairam Gaddam wrote:
>>> sql="create table em(name text primary key,age text,pts text);"\
>>> "create table l(name text primary key,fame text);";
>>>
>>> sql = "select * from em,l where l.fame=em.age";
>>>
>>>4 Once 0   130   00
>>>5 OpenAutoindex230 k(3,nil,nil,nil) 00
>>>6 Rewind   1   130   00
>>>7 Column   112   00
>>>8 Column   103   00
>>>9 Rowid140   00
>>>   10 MakeRecord   231   00
>>>   11 IdxInsert210   10
>>>   12 Next 170   03
>>>   13 Column   015   00
>>>   14 IsNull   5   240   00
>>>   15 SeekGE   2   245 1 00
>>>   16 IdxGT2   245 1 00
>>>   17 Column   006   00
>>>   18 Copy 570   00
>>>   19 Column   028   00
>>>   20 Column   219   00
>>>   21 Column   20   10   00
>>>   22 ResultRow650   00
>>>   23 Next 2   160   00
>>>   24 Next 040   01
>>>   ...
>>>
>>> whenever the condition in the where clause is false, the program jumps to
>>> the instruction pointed by p2 of SeekGe
>>
>> Yes.
>>
>>> but if the condition proves to be false for the row 1 of both the
>>> tables, then the program jumps to line 24(in this case) which
>>> corresponds to outer table and takes the second row of outer table
>>> for next iteration, then when will the program fetch 1st row
>>> of table-1 and remaining rows of table-2 ???
>>
>> In the join loop, this VDBE program does not fetch any rows from the
>> second table:
>>
>>   explain query plan select * from em,l where l.fame=em.age;
>>   0|0|0|SCAN TABLE em
>>   0|1|1|SEARCH TABLE l USING AUTOMATIC COVERING INDEX (fame=?)
>>
>> All accesses to "l" are actually handled by the temporary index (which
>> is created by instructions 5..12).  One index search is enough to
>> determine whether a fame value exists.
>
> Then why there is a loop (Next opcode at 23rd instruction) over second
> table when it created an index ?

Because there might be multiple index entries with the same fame value.


Regards,
Clemens

[sqlite] Sqlite 2 and Windows 7

[Gabriel Tiste]

We have an application running on Apache 2.2 / PHP and Sqlite 2. Lately we have 
heard from our customers regarding corrupt sqlite database files. We can't 
figure out what in our application causing this and suspects that something has 
happened when customer upgraded and ran our application on Windows 7 instead of 
Windows XP.

We can do a select on columns but not select * on affected table except the 
last column. Including that in the select cause the client to say that table is 
missing or logic is not correct.

Are there any known issues with sqlite2 and windows 7? How can I investigate 
whats causing this error when I try to select * from the affected table?

Any suggestions are welcome!

Best regards,

Gabriel Tiste

[sqlite] Request: Metadata about C API constants and functions

[Dominique Devienne]

On Fri, Apr 10, 2015 at 9:48 AM, Zsb?n Ambrus  wrote:

> On 4/10/15, Dominique Devienne  wrote:
> > But that's build time. There is no way (i.e. pragma) to list registered
> SQL functions at runtime.
>
> Um, you're talking about SQL functions.  But I think Roger asked for C
> functions in the public C api of Sqlite.


Right. Silly me... Thanks for getting that straight. And sorry to be OT.

Hopefully all my (valid to me) points will be considered though, and we can
get that runtime introspection of functions/modules, despite being
off-topic for this thread. Obviously I care about this :). --DD

[sqlite] step and reset or reset and step?

[Simon Slavin]

On 10 Apr 2015, at 9:35am, Paolo Bolzoni  
wrote:

> The subject already says it all, I was wondering what is the best
> practice for a prepared statement that need to be used an unknown
> number of times.
> 
> It is better to reset and step (as many times as needed) or step
> and reset after? Or there is no real difference?

The first _step() command after _prepare() and _bind(), and the first _step() 
after a _reset(), do database access and figure-out the strategy for accessing 
the correct rows.  The results of this are stored for later use.  It can also, 
depending on the command, lock the database.

The _reset() command releases this storage and any locks.  (It does not unbind 
any bound parameters.)

So I would recommend that you do _reset() as soon as you know you don't need 
any more _step() in the current query/execution.  The second of your two 
alternatives.

Lastly, you should call _finalize() on a statement before closing your 
connection, even if the last thing you did with it was _reset().  _finalize() 
releases memory used by bound variables, and also the memory used to store the 
statement itself.  It's equivalent to _close() for the statement or to deleting 
an object.

Simon.

[sqlite] Index causing very slow queries

[Richard Hipp]

On 4/10/15, Hamish Symington  wrote:
> Hi there,
>
> A follow up to this. If I run ANALYZE on the ?fast? version of the database
> - ie *after* I?ve recreated the index - performance drops back to the
> original slow speed.

Please run ".fullschema" using the sqlite3.exe command-line shell on
your original database and send us the output.

>
> Hamish
>
>> On 10 Apr 2015, at 14:24, Hamish Symington 
>> wrote:
>>
>> Hello,
>>
>> I have a curiously slow query, and I?m not sure if it?s caused by
>> something I?m doing (most likely) or by something odd in sqlite (almost
>> certainly not).
>>
>> The tables we have are:
>> Appointment(Appointment_UUID, Appointment_TxnUUID, Appointment_StartDate,
>> and other columns)
>> Txn(Txn_UUID, Txn_Created, Txn_Reference amongst others).
>> TxnCalc(TxnCalc_TxnUUID, TxnCalc_Date amongst others).
>>
>> The query I am running is this (using a prepared statement):
>>
>> SELECT TxnCalc_TxnUUID AS Txn_UUID
>>  FROM (
>>  SELECT Txn.Txn_UUID AS TempTxn_UUID
>>  FROM Txn
>>  LEFT JOIN Appointment ON Appointment_TxnUUID=Txn_UUID
>>  WHERE ( date( Appointment_StartDate ) >= trim( ? ) AND date(
>> Appointment_StartDate ) <= trim( ? ) ) )
>>  AS SubQueryResult,
>>  TxnCalc,
>>  Txn
>>  WHERE TxnCalc_TxnUUID=TempTxn_UUID
>>  AND TxnCalc_TxnUUID=Txn_UUID
>>  GROUP BY TxnCalc_TxnUUID
>>  ORDER BY TxnCalc_Date DESC,
>>  Txn_Reference DESC,
>>  Txn_Created DESC
>>
>> If possible, I?d like you to ignore the structure of the query, which
>> isn?t quite as good as it could be, I know; it?s the next bit that?s
>> curious.
>>
>> We have indexes on Txn_UUID, TxnCalc_TxnUUID, Appointment_UUID - nothing
>> controversial there - and also on Appointment_TxnUUID. That field has
>> many, many blank values (as Appointments are most often linked to other
>> things) and only a very few non-blank values. They?re blank (ie ??) and
>> not NULL, if that makes a difference.
>>
>> When running that query using SQLite 3.7.14.1, it takes around 230ms to
>> run. The query plan is this:
>> SCAN TABLE Txn (~3670 rows)
>> SEARCH TABLE Appointment USING AUTOMATIC COVERING INDEX
>> (Appointment_TxnUUID=?) (~5 rows)
>> SEARCH TABLE TxnCalc USING INDEX idx_TxnCalc_TxnUUID (TxnCalc_TxnUUID=?)
>> (~1 rows)
>> SEARCH TABLE Txn USING INDEX idx_Txn_UUID (Txn_UUID=?) (~1 rows)
>> USE TEMP B-TREE FOR GROUP BY
>> USE TEMP B-TREE FOR ORDER BY
>> Interestingly, it?s not using the index which we created for the SEARCH
>> TABLE Appointment.
>>
>> When running the query using SQLite 3.8.8, it takes around 1600ms to run.
>> The query plan is this:
>> SCAN TABLE Txn USING COVERING INDEX idx_Txn_UUID
>> SCAN TABLE Appointment
>> SEARCH TABLE TxnCalc USING INDEX idx_TxnCalc_TxnUUID (TxnCalc_TxnUUID=?)
>> SEARCH TABLE Txn USING INDEX idx_Txn_UUID (Txn_UUID=?)
>> USE TEMP B-TREE FOR GROUP BY
>> USE TEMP B-TREE FOR ORDER BY
>> ie no index on Appointment at all.
>>
>> Now for the curious bit. If I drop the index which I made, and recreate it
>> using the exact same code with which I created it when I made my database
>> - CREATE INDEX idx_Appointment_TxnUUID ON Appointment(Appointment_TxnUUID)
>> - using sqlite 3.7.14.1 the query is down to 12ms, and using 3.8.8, it?s
>> down to 4ms.
>>
>> With the database in its ?slow? state, running ANALYZE makes no
>> difference. Likewise, REINDEX idx_Appointment_TxnUUID makes no difference.
>>
>>
>> Background: I create the database, make the indexes, and then do a bunch
>> of INSERTs into it, along with UPDATES, DELETES etc. - basically using it
>> as a normal workhorse database.
>>
>> So, the question, after this somewhat long explanation: should deleting
>> and recreating an index like this cause such a massive performance
>> improvement? If so, why? What am I doing wrong in the first place?
>>
>> Thanks in advance for your assistance,
>>
>> Hamish
>>
>> --
>> Hamish Symington
>> 07815 081282  :  info at hamishsymington.com
>>
>> I also set custom cryptic crosswords: www.customcrypticcrosswords.com
>> (@CustomCryptics on Twitter)
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> --
> Hamish Symington
> 07815 081282  :  info at hamishsymington.com
>
> I also set custom cryptic crosswords: www.customcrypticcrosswords.com
> (@CustomCryptics on Twitter)
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Request: Metadata about C API constants and functions

[Richard Hipp]

On 4/9/15, Roger Binns  wrote:
>
> So the URL for this SQLite database is?  :-)
>

https://www.sqlite.org/toc.db
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Request: Metadata about C API constants and functions

[Zsbán Ambrus]

On 4/10/15, Dominique Devienne  wrote:
> But that's build time. There is no way (i.e. pragma) to list registered SQL
> functions at runtime.
>
> This is something that I've asked for before, and I'm having trouble
> understanding why nobody cares.
>
> When you run an SQLite "shell", the host program can have enabled/disabled
> built-in functions at build-time, and/or added any number of custom SQL
> functions. But you have no way to know.

Um, you're talking about SQL functions.  But I think Roger asked for C
functions in the public C api of Sqlite.

Ambrus

[sqlite] Request: Metadata about C API constants and functions

[Dominique Devienne]

On Fri, Apr 10, 2015 at 2:42 AM, Richard Hipp  wrote:

> On 4/9/15, Roger Binns  wrote:
> > For each function, its name...
>

> As it happens, the SQLite documentation build process already parses
> out most of this and puts it into a database already.  If you
> check-out the documentation sources
> (https://www.sqlite.org/docsrc/timeline) and successfully build the
> documentation, it creates a
> docinfo.db file that contains a lot of what you have requested above.
> Probably it just needs to be enhanced a little.  Most of the relevant
> information is extracted from sqlite3.h using the
> https://www.sqlite.org/docsrc/artifact/5c48dd261dbe5804 script.


But that's build time. There is no way (i.e. pragma) to list registered SQL
functions at runtime.

This is something that I've asked for before, and I'm having trouble
understanding why nobody cares.

When you run an SQLite "shell", the host program can have enabled/disabled
built-in functions at build-time, and/or added any number of custom SQL
functions. But you have no way to know.

Even in the sqlite3 shell, the .load of extensions often triggers
registrations of new functions, and again you cannot know about this new
function(s) (or vtable module(s)).

You have to a-priori know all this, yet tools (the sqlite3 shell, the
myriad GUI shells out there) can't allow you to introspect all that. That's
a shame IMHO.

The info exists inside the runtime, all that's missing are the pragmas to
extract them. Can we please add these pragmas? The "truth" is in the
"code", especially with SQLite where features (and thus functions) can be
turned on/off at build-time. Neither the website or the doc are the "truth"
of what you are running, which is why we need a "programmatic" way to get
the info at runtime.

My $0.02. --DD

PS: Roger could then just *run* these pragmas and with some good naming
convention fetch additional info from the website to embed the 1-liner doc
he wants. (sqlite3 the shell tool should ideally provide built-in 1-liner
doc for the functions as well, but I already hear all the "Lite-in-SQLite
is not for nothing" if I even proposed to include that in the library
itself, or allow custom function to provide such a doc snippet during
registration...).

[sqlite] Sqlite 2 and Windows 7

[Richard Hipp]

On 4/10/15, Gabriel Tiste  wrote:
> We have an application running on Apache 2.2 / PHP and Sqlite 2. Lately we
> have heard from our customers regarding corrupt sqlite database files. We
> can't figure out what in our application causing this and suspects that
> something has happened when customer upgraded and ran our application on
> Windows 7 instead of Windows XP.
>
> We can do a select on columns but not select * on affected table except the
> last column. Including that in the select cause the client to say that table
> is missing or logic is not correct.
>
> Are there any known issues with sqlite2 and windows 7? How can I investigate
> whats causing this error when I try to select * from the affected table?
>

We are not aware of any problems.  However, development work on
sqlite2 stopped before windows7 came out and so sqlite2 was never
actually tested on windows7.  Sqlite3 was released and sqlite2 went
into maintenance-only mode in 2004.  The last bug-fix to sqlite2 was
in early 2007.  Windows7 came out in late 2009.

There is a branch in the SQLite source tree for sqlite2.
(https://www.sqlite.org/src/timeline?r=version_2&n=all)  If you find a
problem and submit a patch for Windows7, we'll be happy to check that
in for you.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] possible Bug

[Hick Gunter]

In sqlite 3.7.14 in debug mode it raises a constraint error that states that 
the first argument is an invalid mem struct at location 7 in the trigger 
subprogram

(gdb) print *pOp
$1 = {opcode = 75 'K', p4type = -4 '\374', opflags = 21 '\025', p5 = 107 'k', 
p1 = 4, p2 = 11, p3 = 5, p4 = {i = 352254408, p = 0x14fef9c8, z = 0x14fef9c8 
"@\372\376\024", pI64 = 0x14fef9c8, pReal = 0x14fef9c8, pFunc = 0x14fef9c8, 
pVdbeFunc = 0x14fef9c8, pColl = 0x14fef9c8, pMem = 0x14fef9c8, pVtab = 
0x14fef9c8, pKeyInfo = 0x14fef9c8, ai = 0x14fef9c8, pProgram = 0x14fef9c8, 
xAdvance = 0x14fef9c8}, zComment = 0x0}
(gdb) print aMem[4]
$2 = {db = 0x14fef698, z = 0x0, r = 0, u = {i = 0, nZero = 0, pDef = 0x0, 
pRowSet = 0x0, pFrame = 0x0}, n = 0, flags = 4224, type = 0 '\000', enc = 0 
'\000', pScopyFrom = 0x0, pFiller = 0x0, xDel = 0, zMalloc = 0x0}

asql> explain delete from ical;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Trace  0 0 000  NULL
1 Goto   0 29000  NULL
2 Integer0 1 000  NULL
3 Null   0 2 000  NULL
4 OpenRead   0 2 1 0  00  ical
5 Rewind 0 10000  NULL
6 Rowid  0 3 000  NULL
7 RowSetAdd  2 3 000  NULL
8 AddImm 1 1 000  NULL
9 Next   0 6 001  NULL
10Close  0 0 000  NULL
11OpenWrite  0 2 1 3  00  ical
12RowSetRead 2 26300  NULL
13NotExists  0 25300  NULL
14Copy   3 4 000  NULL
15NotExists  0 25300  NULL
16OpenRead   1 3 1 2  00  icalentry
17Rewind 1 22000  NULL
18Column 1 1 800  icalentry.calendar
19Ne 4 218 collseq(BINARY)  6b  NULL
20FkCounter  0 1 000  NULL
21Next   1 18001  NULL
22Close  1 0 000  NULL
23Delete 0 1 0 ical   00  NULL
24Program4 0 10program00  Call: fkey.abort
25Goto   0 12000  NULL
26Close  0 0 000  NULL
27ResultRow  1 1 000  NULL
28Halt   0 0 000  NULL
29Transaction1 1 000  NULL
30VerifyCookie   1 2 000  NULL
31TableLock  1 2 1 ical   00  NULL
32TableLock  1 3 1 icalentry  00  NULL
33Goto   0 2 000  NULL
0 Trace  0 0 0 -- TRIGGER 00  Start: .abort (AFTER 
DELETE ON ical)
1 Integer0 1 000  NULL
2 Null   0 2 000  NULL
3 Param  2 4 000  old.oid -> $4
4 OpenRead   0 3 1 2  00  icalentry
5 Rewind 0 12000  NULL
6 Column 0 1 500  icalentry.calendar
7 Ne 4 115 collseq(BINARY)  6b  NULL
8 Rowid  0 3 000  NULL
9 RowSetAdd  2 3 000  NULL
10AddImm 1 1 000  NULL
11Next   0 6 001  NULL
12Close  0 0 000  NULL
13OpenWrite  0 3 1 3  00  icalentry
14RowSetRead 2 30300  NULL
15NotExists  0 29300  NULL
16Copy   3 7 000  NULL
17Column 0 1 900  icalentry.calendar
18NotExists  0 29300  NULL
19FkIfZero   0 27000  NULL
20IsNull 9 27000  NULL
21SCopy  9 5 000  NULL
22MustBeInt  5 26000  NULL
23OpenRead   1 2 1 3  00  ical
24NotExists  1 26500  NULL
25Goto   0 27000  NULL
26FkCounter  0 -1000  NULL
27Close  1 0 

[sqlite] Request: Metadata about C API constants and functions

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 04/10/2015 12:33 AM, Dominique Devienne wrote:
> But that's build time. There is no way (i.e. pragma) to list
> registered SQL functions at runtime.

There is a ticket for this:

  https://www.sqlite.org/src/tktview/5896edbe463b6a1f0f14

It was rejected without explanation a year ago, after being open for 8
years.  Sadly there is no workaround as the information is just not
exposed in any useful way.

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlUn0SsACgkQmOOfHg372QS9HgCeJS+65yV4iagsRqCpSXs8+em/
hawAoNPy4Oqw4t2iEbJzuSSOrFq3IqWa
=zlHT
-END PGP SIGNATURE-

[sqlite] Regarding SeekGe and Next opcodes in VDBE

[Richard Hipp]

On 4/9/15, Sairam Gaddam  wrote:
>
> Below is a sample VDBE program:
>
>0 Init 0   270   00
>1 OpenRead 020 3 00
>2 OpenRead 1   150 2 00
>3 Rewind   0   250   00
>4 Once 0   130   00

Did you know that if you do this in the sqlite3.exe command-line tool
and you do ".explain" first, then sqlite3.exe will put the output into
neat columns and automatically indent the loops, for easier reading?
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] possible Bug

[R.Smith]

This is the minimal SQL for a test case needed to reproduce the oid - 
foreign key bug:

(By changing only 1 character renaming oid to xid in the first line, the 
SQL works as intended).

CREATE TABLE tParent(id INTEGER PRIMARY KEY, c1 TEXT,  oid INTEGER);
CREATE TABLE tChild( id INTEGER PRIMARY KEY,   Data TEXT,
   FOREIGN KEY (id) REFERENCES tParent(id)
 ON UPDATE CASCADE
 ON DELETE CASCADE
);
INSERT INTO tParent (c1) VALUES ('aaa');
INSERT INTO tChild (id, Data)  VALUES (last_insert_rowid(), 'FK A');

UPDATE tParent SET id = 5 WHERE id = 1;  -- This will fail but shouldn't
   -- OR --
DELETE FROM tParent WHERE id = 1;   -- This will fail but shouldn't


Note that
   - "oid" is never referenced again - it's mere presence in the schema 
causes the error.
   - This error does NOT happen when either "rowid" or "_rowid_" is used 
- only when using "oid".

[sqlite] Request: Metadata about C API constants and functions

[Simon Slavin]

On 10 Apr 2015, at 1:42am, Richard Hipp  wrote:

> As it happens, the SQLite documentation build process already parses
> out most of this and puts it into a database already.  If you
> check-out the documentation sources
> (https://www.sqlite.org/docsrc/timeline) and successfully build the
> documentation, it creates a
> docinfo.db file that contains a lot of what you have requested above.

Hmm.  We could add an output mode to the shell tool which outputs in JSON 
format.  And by "We" I mean someone who can write good C, which isn't me.

Simon.