[sqlite] JSON expressions for records and synchoronisation

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

[sqlite] Regression with pragma index_list version 3.8.9

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

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

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:

[sqlite] step and reset or reset and step?

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,

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

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

[sqlite] Regarding SeekGe and Next opcodes in VDBE

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

[sqlite] Sqlite 2 and Windows 7

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

[sqlite] Index causing very slow queries

>> 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.

[sqlite] Sqlite 2 and Windows 7

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

[sqlite] Regarding SeekGe and Next opcodes in VDBE

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

[sqlite] Index causing very slow queries

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

[sqlite] Index causing very slow queries

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

[sqlite] Index causing very slow queries

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

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

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

[sqlite] Index causing very slow queries

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,

[sqlite] JSON expressions for records and synchoronisation

-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

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

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

[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] 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

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

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

[sqlite] Sqlite 2 and Windows 7

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

[sqlite] Sqlite 2 and Windows 7

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

[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

[sqlite] Regarding SeekGe and Next opcodes in VDBE

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"; >>> >>>

[sqlite] Sqlite 2 and Windows 7

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

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

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

[sqlite] step and reset or reset and step?

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

[sqlite] Index causing very slow queries

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

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

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

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

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

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

[sqlite] Sqlite 2 and Windows 7

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

[sqlite] possible Bug

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 =

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

-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

[sqlite] Regarding SeekGe and Next opcodes in VDBE

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

[sqlite] possible Bug

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

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

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 >