[sqlite] FOREIGN KEY constraint failed

[Domingo Alvarez Duarte]

Thanks for reply !  

I already sent a proposal to Richard to add a pragma "PRAGMA DEBUG_MODE" and
when set throw any kind of error to stderr/sqlite3_(hook) this way ther is no
need to store temporary conditions to show later.  

And of course sqlite knows which table/field failed to flag the error, it
doesn't throw a dice to do it.


Cheers !  
>  Mon Apr 04 2016 10:35:35 PM CEST from "Petite Abeille"
>  Subject: Re: [sqlite] FOREIGN KEY constraint
>failed
>
>
>>On Apr 4, 2016, at 6:14 PM, Richard Hipp  wrote:
>> 
>> On 4/4/16, Domingo Alvarez Duarte  wrote:
>>  
>>>sqlite knows which table/field failed
>>> 

>>  No it doesn't, actually. 
>> 

>  And yet, that same question comes over, and over, and over, ad nauseam.
>Each and every time a poor soul is confronted with that obscure message.
>Sigh.
> 
> Oh well? 
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] FOREIGN KEY constraint failed

[Domingo Alvarez Duarte]

Thanks for reply !  

I also do it using the sqlite3_trace hook but when you have a database with a
bunch of foreign keys it would be a lot easier/faster if sqlite could tell us
exactly where it was (sqlite must know it to flag the error).  

I already found the problem but it took 3 hours to find.  

Cheers !  
>  Mon Apr 04 2016 10:17:00 PM CEST from "Tim Streater"
>  Subject: Re: [sqlite] FOREIGN KEY constraint failed
>
>  On 04 Apr 2016 at 17:36, Domingo Alvarez Duarte
> wrote: 
> 
>  
>>Fair enough !
>> 
>> But even then could it have let's say a place for record the last foreign
>>key
>> violation ?
>> 
>> Only one place will be better than nothing, it can be overwritten every
>>time
>> a foreign key is found and at least we could have a message like this:
>> 

>  You can write your own wrapper to do this. I have 850 places in my app
>where I do query or exec, so in the event of failure I need to know which one
>and what the sql was. When a problem occurs I log this information.
> 
> --
> Cheers -- Tim
> 
>   (, 0 bytes) [View| Download]
>  ?
>
>  
>
>  
>
>  



?

-- next part --
An embedded and charset-unspecified text was scrubbed...
Name: 
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20160404/b120d769/attachment.ksh>

[sqlite] FOREIGN KEY constraint failed

[Petite Abeille]

> On Apr 4, 2016, at 6:14 PM, Richard Hipp  wrote:
> 
> On 4/4/16, Domingo Alvarez Duarte  wrote:
>> sqlite knows which table/field failed
> 
> No it doesn't, actually. 

And yet, that same question comes over, and over, and over, ad nauseam. Each 
and every time a poor soul is confronted with that obscure message. Sigh.

Oh well? 

[sqlite] FOREIGN KEY constraint failed

[Tim Streater]

On 04 Apr 2016 at 17:36, Domingo Alvarez Duarte  
wrote: 

> Fair enough !
>
> But even then could it have let's say a place for record the last foreign key
> violation ?
>
> Only one place will be better than nothing, it can be overwritten every time
> a foreign key is found and at least we could have a message like this:

You can write your own wrapper to do this. I have 850 places in my app where I 
do query or exec, so in the event of failure I need to know which one and what 
the sql was. When a problem occurs I log this information.

--
Cheers  --  Tim

[sqlite] FOREIGN KEY constraint failed

[Domingo Alvarez Duarte]

Thanks for reply !  

Thank you for your solution I think it improves the actual situation !  

Cheers !  
>  Mon Apr 04 2016 08:00:38 PM CEST from "Stephen Chrzanowski"
>  Subject: Re: [sqlite] FOREIGN KEY constraint failed
>
>  I've never developed for a platform with a small memory footprint (Unless
> you talk about the Vic20 as being the smallest footprint I've used, but not
> relevant to SQLite, and my skill was writing text based paint program),
> but, throwing results like this into memory could cause more issues for
> devices that have KILOBYTES worth of memory to play with as a whole, versus
> a machine with Gigabytes of extra-never-been-used-in-its-lifespan memory.
> 
> If maybe there were some kind of interface that the library could expose so
> that if a constraint error occurs, something external to SQLite can deal
> with it, regardless of what the results are. If the developer wants to
> know what the constraint faults are, they just map their own routines to
> whatever interface SQLite could provide and the SQLite engine can just feed
> whatever it can. The owness of whatever that output is belongs to the
> developer, not to the SQLite dev team, and reservations on what the output
> of this new routine is apt to change at any time. If the events triggered
> by the constraint failures are invalid at the start, but then become valid
> later on, it'd be again up to the developer to determine what happened, not
> the SQLite dev team.
> 
> My two cents.
> 
> 
> On Mon, Apr 4, 2016 at 1:49 PM, Domingo Alvarez Duarte <
> sqlite-mail at dev.dadbiz.es> wrote:
> 
>  
>>Thank you for reply !
>> 
>> Good point, but for sure there is great minds here that can come with a
>> solution to this problem !
>> 
>> Sqlite could use a table in memory (or a hash table) and add delete from
>>it
>> as it found violations/resolve then and at then end it has all the
>> violations
>> to show, it can be done lazy so if no violations occur no hash table is
>> ever
>> created.
>> 
>> 
>> The actual situation is better than nothing but it's still frustrating !
>> 
>> Cheers !
>>  
>>>Mon Apr 04 2016 07:39:19 PM CEST from "Clemens Ladisch"
>>>  Subject: Re: [sqlite] FOREIGN KEY constraint failed
>>> 
>>> Domingo Alvarez Duarte wrote:
>>> 
>>>  
But even then could it have let's say a place for record the last foreign
 key
 violation ?
 
 

>>>  This would not help if that last constraint is no longer violated at the
>>> end of the transaction.
>>> 
>>> 
>>> Regards,
>>> Clemens
>>> ___
>>> 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-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] FOREIGN KEY constraint failed

[Domingo Alvarez Duarte]

I think I found a solution to it !  

If the cost of it is too much for normal operation we could have a "pragma
debug_foreign_key" (or debug everything) and turn it on, then run again the
failed statement and in this mode extra code that saves all info for a
detailed error message can be run without problem.  

?  
>  Mon Apr 04 2016 07:49:57 PM CEST from "Domingo Alvarez Duarte"
>  Subject: Re: [sqlite] FOREIGN KEY constraint
>failed
>
>  Thank you for reply ! 
> 
> Good point, but for sure there is great minds here that can come with a
> solution to this problem ! 
> 
> Sqlite could use a table in memory (or a hash table) and add delete from it
> as it found violations/resolve then and at then end it has all the
>violations
> to show, it can be done lazy so if no violations occur no hash table is
>ever
> created. 
> 
> 
> The actual situation is better than nothing but it's still frustrating ! 
> 
> Cheers ! 
>  
>>Mon Apr 04 2016 07:39:19 PM CEST from "Clemens Ladisch"
>>  Subject: Re: [sqlite] FOREIGN KEY constraint failed
>> 
>> Domingo Alvarez Duarte wrote:
>> 
>>  
>>>But even then could it have let's say a place for record the last foreign
>>> key
>>> violation ?
>>> 
>>> 

>>  This would not help if that last constraint is no longer violated at the
>> end of the transaction.
>> 
>> 
>> Regards,
>> Clemens
>> ___
>> 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] FOREIGN KEY constraint failed

[Domingo Alvarez Duarte]

Thank you for reply !  

Good point, but for sure there is great minds here that can come with a
solution to this problem !  

Sqlite could use a table in memory (or a hash table) and add delete from it
as it found violations/resolve then and at then end it has all the violations
to show, it can be done lazy so if no violations occur no hash table is ever
created.  


The actual situation is better than nothing but it's still frustrating !  

Cheers !  
>  Mon Apr 04 2016 07:39:19 PM CEST from "Clemens Ladisch"
>  Subject: Re: [sqlite] FOREIGN KEY constraint failed
>
>  Domingo Alvarez Duarte wrote:
>  
>>But even then could it have let's say a place for record the last foreign
>>key
>> violation ?
>> 

>  This would not help if that last constraint is no longer violated at the
> end of the transaction.
> 
> 
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] FOREIGN KEY constraint failed

[Clemens Ladisch]

Domingo Alvarez Duarte wrote:
> But even then could it have let's say a place for record the last foreign key
> violation ?

This would not help if that last constraint is no longer violated at the
end of the transaction.


Regards,
Clemens

[sqlite] Error help

[Simon Slavin]

On 4 Apr 2016, at 7:31pm, Daniel Telvock  
wrote:

>  I was taught the basics in SQLite with Firefox, and
> am already disappointed that I can't really do what I want to do with the
> Firefox plug in.

Plenty of other ways to use SQLite with a graphical GUI:





Simon.

[sqlite] Error help

[Simon Slavin]

On 4 Apr 2016, at 7:22pm, Daniel Telvock  
wrote:

> Even when trained to use SQLite I was told it could handle large CSV files,
> and this one was only 1000 rows

SQLite handles it fine.  I have a 39 Gigabyte database using SQLite.

The thing you're using is not SQLite.  It's a program which does a lot of 
things, using SQLite but also lots of code written by people who have nothing 
to do with SQLite.  The error you reported is not an SQLite error.  The 'NS' at 
the beginning of the error name is a hang over from back when Mozilla was 
called 'NetScape'.

Simon.

[sqlite] Error help

[Simon Slavin]

On 4 Apr 2016, at 6:17pm, Richard Hipp  wrote:

> It is.  Don't confuse SQLite with the Firefox plugin for SQLite.  The
> limit you have reached seems to be a limitation with the Firefox
> plugin, not with SQLite itself.

I confirm that the NS_ERROR_STORAGE_CONSTRAINT error is generated by the 
Mozilla codebase (i.e. Netscape).  If you would like to download the SQLite 
shell tool and try the import using that, you'll probably have no trouble 
importing your data.



 Precompiled Binaries

Simon.

[sqlite] FOREIGN KEY constraint failed

[Domingo Alvarez Duarte]

Fair enough !  

But even then could it have let's say a place for record the last foreign key
violation ?  

Only one place will be better than nothing, it can be overwritten every time
a foreign key is found and at least we could have a message like this:  

"FOREIGN KEY constraint failed (x : last = (offending table, offending field)
|| (constraint name))"  

Where "x" is the number of violations and the last violation recorded, this
way at least we can start solving then one by one (probably in most cases
will be only one).  

Cheers !

[sqlite] FOREIGN KEY constraint failed

[Domingo Alvarez Duarte]

Hello !  

I know this was discussed here before several times, but it seems that still
we have poor solution for this on sqlite:  

FOREIGN KEY constraint failed  

I have some databases with lots of foreign keys and I sometimes I need to
change the structure of some tables and I get this message, it's better than
nothing but sqlite knows which table/field failed but do not tell us making
it hard on some databases to find the problem.  

Can this message also include the offending table/key ?  

Cheers !  

?

[sqlite] Error help

[Simon Slavin]

On 4 Apr 2016, at 5:04pm, Daniel Telvock  
wrote:

> When you have a columns that has lengthy text, is there a way to label it
> so SQLite won't spit it out as an error?

There is no problem with storing lengthy text in a SQLite field.  It makes no 
difference how you label the column since everything gets converted to TEXT 
anyway.  Whatever your problem is it's not related to labelling.

Please state your platform and development environment.

Simon.

[sqlite] Awesome SQLite Update - New Book - Getting Started with SQL(lite and SQLiteStudio)

[Gerald Bauer]

Hello,
  For you enjoyment I have added three more entries to the Awesome
SQLite [1] collection:

  - sqlite-web (github: coleifer/sqlite-web) by Charles Leifer -- a
web-based SQLite database browser written in Python

 - sqliteweb (github: hypebeast/sqliteweb) by Sebastian Ruml -- a
web-based SQLite database browser written in Go

- sqleton (github: inukshuk/sqleton) by Sylvester Keil -- ['skel?t?n];
visualizes your SQLite database schema (requires graphviz)


 Anything missing? Contributions welcome. Cheers.

 [1] https://github.com/planetopendata/awesome-sqlite

[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing

[Mike Bayer]

The "type" column in PRAGMA table_info() is now a blank string when the 
target object is a view in 3.12.0. In 3.11.0 and prior versions, the 
typing information is returned,

Version 3.11.0:


SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE users (
...> user_id INTEGER NOT NULL
...> );
sqlite> CREATE VIEW users_v AS SELECT * FROM users;
sqlite> PRAGMA table_info("users_v");
0|user_id|INTEGER|0||0



Version 3.12.0:


SQLite version 3.12.0 2016-03-29 10:14:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE users (
 user_id INTEGER NOT NULL
)
...>...>...> ;
sqlite> CREATE VIEW users_v AS SELECT * FROM users;
sqlite> PRAGMA table_info("users_v");
0|user_id||0||0


if it is by design that views no longer carry typing information, this 
is a major behavioral change and no mention of it occurs in the release 
notes for 3.12.0, so at the very least this change should be documented 
if expected.

[sqlite] FTS5 problem ? more than 3,000 queries !

[Dan Kennedy]

On 04/04/2016 05:43 AM, Domingo Alvarez Duarte wrote:
> Hello !
>
> I'm testing fts5 on a database with data from hacker news (around 13M
> records) and it took more than 2 hours to index, I have this application to
> log all queries executed for debugging and although the search isn't slow
> it's making a lot of queries.
>
> Is this normal ? More than 3,000 queries ?

Probably. Your query does "ORDER BY rank" with "rank" set to the built 
in bm25() function. One of the things bm25() needs is the size of each 
document that it is ranking. And obtaining this requires a separate 
query on the %_sz table for each row. So if the MATCH expression matches 
3000 rows, FTS5 makes 3000 SQL queries to obtain the size data.

Dan.





>
> Cheers !
>
> 
>
> CREATE TABLE "items" (
>  'id' integer PRIMARY KEY,
>  'parent' INTEGER,
>  'by' text COLLATE NOCASE,
>  'score' integer DEFAULT 0,
>  'title' text  COLLATE NOCASE,
>  'type' text  COLLATE NOCASE,
>  'url' text  COLLATE NOCASE,
>  'deleted' BOOLEAN DEFAULT 0,
>  'dead' BOOLEAN DEFAULT 0,
>  'comment' TEXT COLLATE NOCASE,
>  'time' integer NOT NULL,
>  descendants integer default 0
> );
>
> CREATE VIRTUAL TABLE fts_idx_items USING fts5(title, comment, content=items,
> content_rowid=id, prefix=3);
>
>
> The query:
>
> SQL:  SELECT a.id, a.parent, a.by, a.score,
>  snippet(fts_idx_items, 0, '', '', '...',
> 12) as title,
>  a.type, a.url,
>  --snippet(fts_idx_items, 1, '', '',
> '...', 12) as comment,
>  a.descendants,
>  datetime(a."time", 'unixepoch') AS time,
>  (julianday('now') - julianday(datetime(a."time",
> 'unixepoch'))) as elapsed_time
>  FROM items a, fts_idx_items b
>  where fts_idx_items match 'title:sql'
>  and a.id = b.rowid
>  --and a.type != 'comment'
>  ORDER BY rank --bm25(fts_idx_items)
>  LIMIT 30 OFFSET 0
>
> Generates:
>
> SQL:-- SELECT rowid, rank FROM 'main'.'fts_idx_items' ORDER BY
> bm25(fts_idx_items) ASC
>
> Followed by 28 of this:
>
> SQL:-- SELECT pgno FROM 'main'.'fts_idx_items_idx' WHERE
> segid=? AND term<=? ORDER BY term DESC LIMIT 1
>
> Followed by around 3,000 of:
>
> SQL:-- SELECT sz FROM 'main'.'fts_idx_items_docsize' WHERE id=?
>   
>
> Followed by 18 of:
>
> SQL:-- SELECT T.'id', T.'title', T.'comment' FROM
> 'main'.'items' T WHERE T.'id'=?
>
> SQL:-- SELECT sz FROM 'main'.'fts_idx_items_docsize' WHERE
> id=?
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] C API reference manpages

[Kristaps Dzonsons]

> How about the CC0 license? I think it's designed for these sorts of
> things (you want to make something public domain even if you're not
> allowed to) - https://creativecommons.org/about/cc0/

Jonathon,

I think the problem is that LV is similar to Norway[1] in this regard,
so something like CC0 doesn't work as one would expect it to.  However,
the manpages generated by sqlite2mdoc are in whatever license (or
no-license) you want, so at the end of the day, it doesn't affect the
library or its included documentation.  (After all, tcl isn't PD, and
that's required as it is!)

Licensing aside, if anybody has any comments or issues on the tool or
its mdoc(7) output, let me know!

Best,

Kristaps

[1]
http://lists.ibiblio.org/pipermail/cc-community/2014-October/thread.html#8863
(``Open Definition 2.0 released'')


-- next part --
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 842 bytes
Desc: OpenPGP digital signature
URL: 
<http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/attachments/20160404/cfa317d8/attachment.pgp>

[sqlite] Difference between sqlite3 executable and application

[Simon Slavin]

On 4 Apr 2016, at 12:05pm, Domingo Alvarez Duarte  wrote:

> Thanks for reply !  
> 
> If I issue this command :  
> 
> PRAGMA case_sensitive_like = OFF;  
> 
> Then I get the same answer (I mean it uses indexes now):  
> 
> explain query plan select * from companies where post_code like 'rg%';  
> 
> SEARCH TABLE companies USING INDEX companies_post_code_idx (post_code>? AND
> post_code 
> It seems strange why sqlite is not using the indexes with the default
> case_sensitive_like settings, here is the schema:  

Well I'm glad I helped you fix the problem.  Unfortunately I do not know where 
SQLite gets its default setting for "PRAGMA case_sensitive_like".  The 
situation you reported does look rather strange.  Maybe someone else can help.

Simon.

[sqlite] Error help

[Daniel Telvock]

Understood, thanks.

Clearly, I am a neophyte at this stuff and have not learned how to use SQL
with the command line. I was taught the basics in SQLite with Firefox, and
am already disappointed that I can't really do what I want to do with the
Firefox plug in.

I guess I can try to learn Microsoft Access.

Dan Telvock
Environment Reporter
Investigative Post 
Twitter: @dantelvock
716-831-2626 ext. 3


On Mon, Apr 4, 2016 at 2:27 PM, Simon Slavin  wrote:

>
> On 4 Apr 2016, at 7:22pm, Daniel Telvock 
> wrote:
>
> > Even when trained to use SQLite I was told it could handle large CSV
> files,
> > and this one was only 1000 rows
>
> SQLite handles it fine.  I have a 39 Gigabyte database using SQLite.
>
> The thing you're using is not SQLite.  It's a program which does a lot of
> things, using SQLite but also lots of code written by people who have
> nothing to do with SQLite.  The error you reported is not an SQLite error.
> The 'NS' at the beginning of the error name is a hang over from back when
> Mozilla was called 'NetScape'.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Error help

[Daniel Telvock]

Using the non plugin option is a little above my head. I haven't been
trained to use the command line.

Even when trained to use SQLite I was told it could handle large CSV files,
and this one was only 1000 rows

Dan Telvock
Environment Reporter
Investigative Post 
Twitter: @dantelvock
716-831-2626 ext. 3


On Mon, Apr 4, 2016 at 2:21 PM, Richard Hipp  wrote:

> On 4/4/16, Simon Slavin  wrote:
> >
> > On 4 Apr 2016, at 6:17pm, Richard Hipp  wrote:
> >
> >> It is.  Don't confuse SQLite with the Firefox plugin for SQLite.  The
> >> limit you have reached seems to be a limitation with the Firefox
> >> plugin, not with SQLite itself.
> >
> > I confirm that the NS_ERROR_STORAGE_CONSTRAINT error is generated by the
> > Mozilla codebase (i.e. Netscape).  If you would like to download the
> SQLite
> > shell tool and try the import using that, you'll probably have no trouble
> > importing your data.
> >
>
> And once you get the data imported, probably the Firefox plugin will
> work fine for querying and general maintenance.
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Error help

[Richard Hipp]

On 4/4/16, Simon Slavin  wrote:
>
> On 4 Apr 2016, at 6:17pm, Richard Hipp  wrote:
>
>> It is.  Don't confuse SQLite with the Firefox plugin for SQLite.  The
>> limit you have reached seems to be a limitation with the Firefox
>> plugin, not with SQLite itself.
>
> I confirm that the NS_ERROR_STORAGE_CONSTRAINT error is generated by the
> Mozilla codebase (i.e. Netscape).  If you would like to download the SQLite
> shell tool and try the import using that, you'll probably have no trouble
> importing your data.
>

And once you get the data imported, probably the Firefox plugin will
work fine for querying and general maintenance.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] FOREIGN KEY constraint failed

[Stephen Chrzanowski]

I've never developed for a platform with a small memory footprint (Unless
you talk about the Vic20 as being the smallest footprint I've used, but not
relevant to SQLite, and my skill was writing text based paint program),
but, throwing results like this into memory could cause more issues for
devices that have KILOBYTES worth of memory to play with as a whole, versus
a machine with Gigabytes of extra-never-been-used-in-its-lifespan memory.

If maybe there were some kind of interface that the library could expose so
that if a constraint error occurs, something external to SQLite can deal
with it, regardless of what the results are.  If the developer wants to
know what the constraint faults are, they just map their own routines to
whatever interface SQLite could provide and the SQLite engine can just feed
whatever it can.  The owness of whatever that output is belongs to the
developer, not to the SQLite dev team, and reservations on what the output
of this new routine is apt to change at any time.  If the events triggered
by the constraint failures are invalid at the start, but then become valid
later on, it'd be again up to the developer to determine what happened, not
the SQLite dev team.

My two cents.


On Mon, Apr 4, 2016 at 1:49 PM, Domingo Alvarez Duarte <
sqlite-mail at dev.dadbiz.es> wrote:

> Thank you for reply !
>
> Good point, but for sure there is great minds here that can come with a
> solution to this problem !
>
> Sqlite could use a table in memory (or a hash table) and add delete from it
> as it found violations/resolve then and at then end it has all the
> violations
> to show, it can be done lazy so if no violations occur no hash table is
> ever
> created.
>
>
> The actual situation is better than nothing but it's still frustrating !
>
> Cheers !
> >  Mon Apr 04 2016 07:39:19 PM CEST from "Clemens Ladisch"
> >  Subject: Re: [sqlite] FOREIGN KEY constraint failed
> >
> >  Domingo Alvarez Duarte wrote:
> >
> >>But even then could it have let's say a place for record the last foreign
> >>key
> >> violation ?
> >>
>
> >  This would not help if that last constraint is no longer violated at the
> > end of the transaction.
> >
> >
> > Regards,
> > Clemens
> > ___
> > 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] C API reference manpages

[Jonathan Moules]

How about the CC0 license?
I think it's designed for these sorts of things (you want to make something 
public domain even if you're not allowed to) - 
https://creativecommons.org/about/cc0/

 On Fri, 01 Apr 2016 00:05:30 +0100 Kristaps Dzonsons  wrote  

>> As for public domain, I'm happy to put the sources under a similar 
>> license. I can't speak for the voodoo of the public domain and the EU 
>> (or is it country-by-country?), however. 
> 
> From an English translation I found of the Latvian law includes moral 
> rights and is closer to the droit d'auteur than copyright. So it seems 
> difficult to place your work in the public domain. 
> 
> I would advise you to contact the FSF, FSFE Legal or SFLC in this matter 
> if you want a general advice without legal fees. This entire topic as 
> been discussed in context of Wikimedia's projects, especially Wikipedia 
> and Wikimedia Commons, and CC0, so you might find information about 
> Latvia there. 

Matthias-Christian, 

I'm afraid I can't say anything useful about licenses, but in searching 
around, it appears that you're correct[1]. 

[sqlite] Error help

[Richard Hipp]

On 4/4/16, Daniel Telvock  wrote:
> Not sure of size. But I had thought one of benefits of using SQLite was
> ability to import larger CSV files or worksheets that slow down in Excel

It is.  Don't confuse SQLite with the Firefox plugin for SQLite.  The
limit you have reached seems to be a limitation with the Firefox
plugin, not with SQLite itself.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Difference between sqlite3 executable and application

[Domingo Alvarez Duarte]

Thanks for reply !  

If I issue this command :  

PRAGMA case_sensitive_like = OFF;  

Then I get the same answer (I mean it uses indexes now):  

explain query plan select * from companies where post_code like 'rg%';  

SEARCH TABLE companies USING INDEX companies_post_code_idx (post_code>? AND
post_code  Mon Apr 04 2016 12:39:35 PM CEST from "Simon Slavin"
>  Subject: Re: [sqlite] Difference between sqlite3
>executable and application
>
>  On 4 Apr 2016, at 10:47am, Domingo Alvarez Duarte
> wrote:
> 
>  
>>It would be a gross mistake if the databases weren't the same. 
>> 
>> It's an existing and populated database.
>> 

>  The only thing that comes to mind is that one app is using case sensitive
>and the other is not. Unfortunately the command to check the status seems to
>be missing from the documentation and therefore possibly missing from SQLite.
> 
> In both apps, before executing
> 
> select * from companies where post_code like 'rg%'
> 
> execute
> 
> PRAGMA case_sensitive_like = ON
> 
> and see what happens. Then perhaps try it off.
> 
> 
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Error help

[Daniel Telvock]

Not sure of size. But I had thought one of benefits of using SQLite was
ability to import larger CSV files or worksheets that slow down in Excel



Dan Telvock
Environment Reporter
Investigative Post 
Twitter: @dantelvock
716-831-2626 ext. 3


On Mon, Apr 4, 2016 at 12:52 PM, Stephen Chrzanowski 
wrote:

> Large CSV?  How large?  Because the constraint error is probably coming
> from the browser for an upload constraint on size versus SQLite throwing an
> error.
>
>
> On Mon, Apr 4, 2016 at 12:26 PM, Daniel Telvock <
> dtelvock at investigativepost.org> wrote:
>
> > I am using the Firefox plugin of SQLite and this is a large CSV file
> that I
> > am importing in the wizard. When I try to import, I get that error
> >
> > CHECK constraint failed: REDCs_refined ]
> > Exception Name: NS_ERROR_STORAGE_CONSTRAINT
> > Exception Message: Component returned failure code: 0x80630003
> > (NS_ERROR_STORAGE_CONSTRAINT) [mozIStorageStatement.execute]
> >
> > Dan Telvock
> > Environment Reporter
> > Investigative Post 
> > Twitter: @dantelvock
> > 716-831-2626 ext. 3
> >
> >
> > On Mon, Apr 4, 2016 at 12:21 PM, Simon Slavin 
> > wrote:
> >
> > >
> > > On 4 Apr 2016, at 5:04pm, Daniel Telvock <
> dtelvock at investigativepost.org
> > >
> > > wrote:
> > >
> > > > When you have a columns that has lengthy text, is there a way to
> label
> > it
> > > > so SQLite won't spit it out as an error?
> > >
> > > There is no problem with storing lengthy text in a SQLite field.  It
> > makes
> > > no difference how you label the column since everything gets converted
> to
> > > TEXT anyway.  Whatever your problem is it's not related to labelling.
> > >
> > > Please state your platform and development environment.
> > >
> > > 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-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Error help

[Stephen Chrzanowski]

Large CSV?  How large?  Because the constraint error is probably coming
from the browser for an upload constraint on size versus SQLite throwing an
error.


On Mon, Apr 4, 2016 at 12:26 PM, Daniel Telvock <
dtelvock at investigativepost.org> wrote:

> I am using the Firefox plugin of SQLite and this is a large CSV file that I
> am importing in the wizard. When I try to import, I get that error
>
> CHECK constraint failed: REDCs_refined ]
> Exception Name: NS_ERROR_STORAGE_CONSTRAINT
> Exception Message: Component returned failure code: 0x80630003
> (NS_ERROR_STORAGE_CONSTRAINT) [mozIStorageStatement.execute]
>
> Dan Telvock
> Environment Reporter
> Investigative Post 
> Twitter: @dantelvock
> 716-831-2626 ext. 3
>
>
> On Mon, Apr 4, 2016 at 12:21 PM, Simon Slavin 
> wrote:
>
> >
> > On 4 Apr 2016, at 5:04pm, Daniel Telvock  >
> > wrote:
> >
> > > When you have a columns that has lengthy text, is there a way to label
> it
> > > so SQLite won't spit it out as an error?
> >
> > There is no problem with storing lengthy text in a SQLite field.  It
> makes
> > no difference how you label the column since everything gets converted to
> > TEXT anyway.  Whatever your problem is it's not related to labelling.
> >
> > Please state your platform and development environment.
> >
> > 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] Error help

[Daniel Telvock]

I am using the Firefox plugin of SQLite and this is a large CSV file that I
am importing in the wizard. When I try to import, I get that error

CHECK constraint failed: REDCs_refined ]
Exception Name: NS_ERROR_STORAGE_CONSTRAINT
Exception Message: Component returned failure code: 0x80630003
(NS_ERROR_STORAGE_CONSTRAINT) [mozIStorageStatement.execute]

Dan Telvock
Environment Reporter
Investigative Post 
Twitter: @dantelvock
716-831-2626 ext. 3


On Mon, Apr 4, 2016 at 12:21 PM, Simon Slavin  wrote:

>
> On 4 Apr 2016, at 5:04pm, Daniel Telvock 
> wrote:
>
> > When you have a columns that has lengthy text, is there a way to label it
> > so SQLite won't spit it out as an error?
>
> There is no problem with storing lengthy text in a SQLite field.  It makes
> no difference how you label the column since everything gets converted to
> TEXT anyway.  Whatever your problem is it's not related to labelling.
>
> Please state your platform and development environment.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Error help

[Richard Hipp]

On 4/4/16, Daniel Telvock  wrote:
> There is a field that contains longer sentences for descriptions. It keeps
> giving me an error that the NS Error Storage Constraint.

Can you please provide more context about what you are trying to do?
I do not understand the description of your problem.


>
> I've tried labeling the column as VarCHar and Text to not avail.
>
>
> When you have a columns that has lengthy text, is there a way to label it
> so SQLite won't spit it out as an error?
>
>  CHECK constraint failed: REDCs_refined ]
> Exception Name: NS_ERROR_STORAGE_CONSTRAINT
> Exception Message: Component returned failure code: 0x80630003
> (NS_ERROR_STORAGE_CONSTRAINT) [mozIStorageStatement.execute]
>
>
> Dan Telvock
> Environment Reporter
> Investigative Post 
> Twitter: @dantelvock
> 716-831-2626 ext. 3
> ___
> 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] FOREIGN KEY constraint failed

[Richard Hipp]

On 4/4/16, Domingo Alvarez Duarte  wrote:
> sqlite knows which table/field failed

No it doesn't, actually.  SQLite keeps a counter of FK violations and
resolutions, and if that counter is zero at the the end of the
transaction, then it knows that no FK constraints have failed.  If the
counter is non-zero, then it knows that one or more FK constraints
have failed, but it has no idea which ones.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Error help

[Daniel Telvock]

There is a field that contains longer sentences for descriptions. It keeps
giving me an error that the NS Error Storage Constraint.

I've tried labeling the column as VarCHar and Text to not avail.


When you have a columns that has lengthy text, is there a way to label it
so SQLite won't spit it out as an error?

 CHECK constraint failed: REDCs_refined ]
Exception Name: NS_ERROR_STORAGE_CONSTRAINT
Exception Message: Component returned failure code: 0x80630003
(NS_ERROR_STORAGE_CONSTRAINT) [mozIStorageStatement.execute]


Dan Telvock
Environment Reporter
Investigative Post 
Twitter: @dantelvock
716-831-2626 ext. 3

[sqlite] Difference between sqlite3 executable and application

[Domingo Alvarez Duarte]

It would be a gross mistake if the databases weren't the same.  

It's an existing and populated database.  

?  
>  Mon Apr 04 2016 11:31:45 AM CEST from "Simon Slavin"
>  Subject: Re: [sqlite] Difference between sqlite3
>executable and application
>
>  On 4 Apr 2016, at 10:19am, Domingo Alvarez Duarte
> wrote:
> 
>  
>>I do not see something significant here to justify sqlite3 using indexes
>>and
>> my application not using then. 
>> 

>  For the SQLite shell tool, are you opening the same database file or are
>you entering CREATE TABLE commands ?
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Difference between sqlite3 executable and application

[Simon Slavin]

On 4 Apr 2016, at 10:47am, Domingo Alvarez Duarte  wrote:

> It would be a gross mistake if the databases weren't the same.  
> 
> It's an existing and populated database.

The only thing that comes to mind is that one app is using case sensitive and 
the other is not.  Unfortunately the command to check the status seems to be 
missing from the documentation and therefore possibly missing from SQLite.

In both apps, before executing

select * from companies where post_code like 'rg%'

execute

PRAGMA case_sensitive_like = ON

and see what happens.  Then perhaps try it off.



Simon.

[sqlite] Difference between sqlite3 executable and application

[Domingo Alvarez Duarte]

Compile time options used:  

sqlite3 application (shell):  

gcc
-g
-O2
-DSQLITE_OS_UNIX=1
-I.
-Isqlite3/src
-Isqlite3/ext/rtree
-Isqlite3/ext/icu
-Isqlite3/ext/fts3
-Isqlite3/ext/async
-Isqlite3/ext/session
-D_HAVE_SQLITE_CONFIG_H
-DBUILD_sqlite
-DSQLITE_HAS_CODEC=1
-DSQLITE_ENABLE_FTS4=1
-DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_RTREE=1
-DSQLITE_ENABLE_COLUMN_METADATA=1
-DSQLITE_ENABLE_FTS3_PARENTHESIS=1
-DSQLITE_DEFAULT_FOREIGN_KEYS=1
-DSQLITE_USE_URI=1
-DSQLITE_ENABLE_UNLOCK_NOTIFY=1
-DSQLITE_USE_DECIMAL2=1
-DSQLITE_ENABLE_STAT4=1
-DCODEC_TYPE=CODEC_TYPE_AES2562
-DSQLITE_SOUNDEX=1
-DSQLITE_ENABLE_EXTENSION_FUNCTIONS=1
-DSQLITE_OMIT_PREPARED=1
-DSQLITE_ENABLE_SESSION=1
-DNDEBUG
-DSQLITE_THREADSAFE=1
-DSQLITE_ENABLE_FTS5
-DSQLITE_ENABLE_JSON1
-DSQLITE_OMIT_PREPARED=1
-DSQLITE_ENABLE_SESSION
-DSQLITE_ENABLE_PREUPDATE_HOOK
-DSQLITE_SMALL_STACK=1
-DHAVE_READLINE=0
-DHAVE_EDITLINE=1
-DSQLITE_ENABLE_JSON1
-DSQLITE_ENABLE_FTS4
-DSQLITE_ENABLE_EXPLAIN_COMMENTS
-o
sqlite3
sqlite3/src/shell.c
sqlite3.c

-ledit
-ldl
-lpthread
-lm
-Wl,-rpath
-Wl,/usr/local/lib  

?  

My application:  

gcc
-Wall
-fno-strict-aliasing
-DSQ_ENABLE_INCLUDES=1
-DSQ_USE_MKSTEMP=1
-DSQ_USE_LOCALTIME_R=1
-DONLY_ASCII=1
-DPROFILE_SQVM0=1
-DSQ_JIT_LLVM44=1
-D_DEBUG_DUMP33=1
-DWITH_DAD_EXTRAS=1
-DSQ_SUBLATIN=1
-DNEED_SUBLATIN_C2=1
-DSQUSEDOUBLE=1
-DSQUSEDECIMAL64x=1
-DNO_EXCEPTION_KEY_NOT_FOUND0=1
-DNO_GARBAGE_COLLECTOR00=1
-DTHREADSAFE=1
-DSQLITE_DEFAULT_FILE_FORMAT=4
-DSQLITE_DEFAULT_AUTOVACUUM=1
-DSQLITE_DEFAULT_FOREIGN_KEYS=1
-DSQLITE_ENABLE_COLUMN_METADATA=1
-DSQLITE_ENABLE_EXTENSION_FUNCTIONS=1
-DSQLITE_ENABLE_FTS4=1
-DSQLITE_ENABLE_FTS5=1
-DSQLITE_ENABLE_FTS3_PARENTHESIS=1
-DSQLITE_ENABLE_UNLOCK_NOTIFY=1
-DSQLITE_ENABLE_RTREE=1
-DSQLITE_ENABLE_STAT4=1
-DSQLITE_HAS_CODEC=1
-DSQLITE_OMIT_TCL_VARIABLE=1
-DSQLITE_USE_URI=1
-DSQLITE_SOUNDEX=1
-DSQLITE_OMIT_PREPARED=1
-DSQLITE_ENABLE_JSON1=1
-DSQLITE_ENABLE_SESSION=1
-DSQLITE_ENABLE_EXPLAIN_COMMENTS=1
-DNO_POPEN=1
-DNO_SSL_DL=1
-DHAVE_STDINT=1
-DUSE_AXTLS=1
-DUSE_OPENSSL2=1
-DUSE_AXTLS_ON_MEMORY=1
-D_FILE_OFFSET_BITS=64
-DSSL_STATIC_LIBRARY=1
-DPDF_USING_ZLIB=1
-DRS232_STATIC=1
-DWITH_UUID=1
-DWITH_FFI=1
-DWITH_POSTGRESQL=1
-DWITH_LIBCLANG=1
-DWITH_MPDECIMAL=1
-DSQ_USE_EASYCURL=1
-fomit-frame-pointer
-fexpensive-optimizations
-O3
-Wall
-DWITH_FLTK=1
-DNDEBUG=1
-DWITH_FULL_DAD_EXTRAS=1
-DPROFILE_SQVM22=1
-D_SQ64=1
-DCONFIG_64=1
-DHAS_UNIX_DOMAIN_SOCKETS=1
-DUSE_SIGNAL_HANDLER=1
-DWITH_MYSQL=1
-DWITH_POSTGRESQL=1
-DWITH_DNS_SD2=1
-Iinclude
-Isqstdlib
-I../myaxtls
-I../discount
-I../../../local/postgresql/include
-I/usr/lib/jvm/default-java/include
-I/usr/lib/llvm-3.1/include
-I../SquiLu-ext/threadObject
-I..
-I../SquiLu-ext
-I/usr/include/mysql
-I../gumbo
-I../minizip
-I../unql/src
-I../../../local/clang-3.6/include
-I../../../local/include
-I../../zeromq-3.2.2/include
-I../fltk
-I../libharu/include
-I../flu
-c
SquiLu/SquiLu-ext/sqlite3.c
-o
obj/Release/SquiLu-ext/sqlite3.o  

?  

?  
>  Mon Apr 04 2016 10:59:17 AM CEST from "Simon Slavin"
>  Subject: Re: [sqlite] Difference between sqlite3
>executable and application
>
>  On 4 Apr 2016, at 9:53am, Domingo Alvarez Duarte
> wrote:
> 
>  
>>What compile time options if any can cause this ? 
>> 

>  I recommend you compare the results of
> 
> PRAGMA compile_options;
> 
> on the two platforms.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Difference between sqlite3 executable and application

[Domingo Alvarez Duarte]

Thanks for reply !  

Executing "PRAGMA compile_options;"  

My application:  

ENABLE_COLUMN_METADATA
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS4
ENABLE_FTS5
ENABLE_JSON1
ENABLE_RTREE
ENABLE_STAT4
ENABLE_UNLOCK_NOTIFY
HAS_CODEC
OMIT_TCL_VARIABLE
SOUNDEX
SYSTEM_MALLOC
THREADSAFE=1  

sqlite3 application (shell):  

ENABLE_COLUMN_METADATA
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS4
ENABLE_FTS5
ENABLE_JSON1
ENABLE_RTREE
ENABLE_STAT4
ENABLE_UNLOCK_NOTIFY
HAS_CODEC
HAVE_ISNAN
SMALL_STACK
SOUNDEX
SYSTEM_MALLOC
THREADSAFE=1  

I do not see something significant here to justify sqlite3 using indexes and
my application not using then.  

Cheers !  
>  Mon Apr 04 2016 10:59:17 AM CEST from "Simon Slavin"
>  Subject: Re: [sqlite] Difference between sqlite3
>executable and application
>
>  On 4 Apr 2016, at 9:53am, Domingo Alvarez Duarte
> wrote:
> 
>  
>>What compile time options if any can cause this ? 
>> 

>  I recommend you compare the results of
> 
> PRAGMA compile_options;
> 
> on the two platforms.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] FTS5 problem ? more than 3,000 queries !

[Domingo Alvarez Duarte]

And the indexes are not supposed to be used ?  

Shouldn't a index cover the ranking too ?  

It seems a mistake for me.  

Cheers !  
>  Mon Apr 04 2016 11:07:51 AM CEST from "Dan Kennedy"
>  Subject: Re: [sqlite] FTS5 problem ? more than 
>3,000
>queries !
>
>  On 04/04/2016 05:43 AM, Domingo Alvarez Duarte wrote:
>  
>>Hello !
>> 
>> I'm testing fts5 on a database with data from hacker news (around 13M
>> records) and it took more than 2 hours to index, I have this application
>>to
>> log all queries executed for debugging and although the search isn't slow
>> it's making a lot of queries
>> 
>> Is this normal ? More than 3,000 queries ?
>> 

>  Probably. Your query does "ORDER BY rank" with "rank" set to the built 
> in bm25() function. One of the things bm25() needs is the size of each 
> document that it is ranking. And obtaining this requires a separate 
> query on the %_sz table for each row. So if the MATCH expression matches 
> 3000 rows, FTS5 makes 3000 SQL queries to obtain the size data.
> 
> Dan.
> 
> 
> 
> 
> 
> 
>  
>>Cheers !
>> 
>> 
>> 
>> CREATE TABLE "items" (
>> 'id' integer PRIMARY KEY,
>> 'parent' INTEGER,
>> 'by' text COLLATE NOCASE,
>> 'score' integer DEFAULT 0,
>> 'title' text COLLATE NOCASE,
>> 'type' text COLLATE NOCASE,
>> 'url' text COLLATE NOCASE,
>> 'deleted' BOOLEAN DEFAULT 0,
>> 'dead' BOOLEAN DEFAULT 0,
>> 'comment' TEXT COLLATE NOCASE,
>> 'time' integer NOT NULL,
>> descendants integer default 0
>> );
>> 
>> CREATE VIRTUAL TABLE fts_idx_items USING fts5(title, comment,
>>content=items,
>> content_rowid=id, prefix=3);
>> 
>> 
>> The query:
>> 
>> SQL : SELECT a.id, a.parent, a.by, a.score,
>> snippet(fts_idx_items, 0, '', '', '...',
>> 12) as title,
>> a.type, a.url,
>> --snippet(fts_idx_items, 1, '', '',
>> '...', 12) as comment,
>> a.descendants,
>> datetime(a."time", 'unixepoch') AS time,
>> (julianday('now') - julianday(datetime(a."time",
>> 'unixepoch'))) as elapsed_time
>> FROM items a, fts_idx_items b
>> where fts_idx_items match 'title:sql'
>> and a.id = b.rowid
>> --and a.type != 'comment'
>> ORDER BY rank --bm25(fts_idx_items)
>> LIMIT 30 OFFSET 0
>> 
>> Generates:
>> 
>> SQL : -- SELECT rowid, rank FROM 'main'.'fts_idx_items' ORDER BY
>> bm25(fts_idx_items) ASC
>> 
>> Followed by 28 of this:
>> 
>> SQL : -- SELECT pgno FROM 'main'.'fts_idx_items_idx' WHERE
>> segid=? AND term<=? ORDER BY term DESC LIMIT 1
>> 
>> Followed by around 3,000 of:
>> 
>> SQL : -- SELECT sz FROM 'main''fts_idx_items_docsize' WHERE id=?
>> 
>> 
>> Followed by 18 of:
>> 
>> SQL : -- SELECT T.'id', T.'title', T.'comment' FROM
>> 'main'.'items' T WHERE T.'id'=?
>> 
>> SQL : -- SELECT sz FROM 'main'.'fts_idx_items_docsize' WHERE
>> id=?
>> 
>> ___
>> 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] Difference between sqlite3 executable and application

[Domingo Alvarez Duarte]

Hello !  

I have an application that uses sqlite3 and in one database I get different
responses between the sqlite3 executable and my application that uses the
same sqlite3.c:  

The query:  

explain query plan select * from companies where post_code like 'rg%';  

My application:  

0??? 0??? 0??? SCAN TABLE companies  

sqlite3 executable:  

0|0|0|SEARCH TABLE companies USING INDEX companies_post_code_idx (post_code>?
AND post_code

[sqlite] Difference between sqlite3 executable and application

[Simon Slavin]

On 4 Apr 2016, at 10:19am, Domingo Alvarez Duarte  wrote:

> I do not see something significant here to justify sqlite3 using indexes and
> my application not using then.  

For the SQLite shell tool, are you opening the same database file or are you 
entering CREATE TABLE commands ?

Simon.

[sqlite] Difference between sqlite3 executable and application

[Simon Slavin]

On 4 Apr 2016, at 9:53am, Domingo Alvarez Duarte  
wrote:

> What compile time options if any can cause this ? 

I recommend you compare the results of

PRAGMA compile_options;

on the two platforms.

Simon.

[sqlite] Awesome SQLite Update - New Book - Getting Started with SQL(lite and SQLiteStudio)

[Gerald Bauer]

Hello,

  I've updated the Awesome SQLite [1] collection over at Planet Open
Data. What's news?

   Added a new beginner's book:

Getting Started with SQL - A Hands-On Approach for Beginners - by
Thomas Nield; 2016; O'Reilly; 134 pages -- learn SQL with SQLite and
SQLiteStudio


  Anything missing? Contributions welcome. Cheers.

 [1] https://github.com/planetopendata/awesome-sqlite

[sqlite] FTS5 problem ? more than 3,000 queries !

[Domingo Alvarez Duarte]

Hello !  

I'm testing fts5 on a database with data from hacker news (around 13M
records) and it took more than 2 hours to index, I have this application to
log all queries executed for debugging and although the search isn't slow
it's making a lot of queries.  

Is this normal ? More than 3,000 queries ?  

Cheers !  

?  

CREATE TABLE "items" (
??? 'id' integer PRIMARY KEY,
??? 'parent' INTEGER,
??? 'by' text COLLATE NOCASE,
??? 'score' integer DEFAULT 0,
??? 'title' text? COLLATE NOCASE,
??? 'type' text? COLLATE NOCASE,
??? 'url' text? COLLATE NOCASE,
??? 'deleted' BOOLEAN DEFAULT 0,
??? 'dead' BOOLEAN DEFAULT 0,
??? 'comment' TEXT COLLATE NOCASE,
??? 'time' integer NOT NULL,
??? descendants integer default 0
);  

CREATE VIRTUAL TABLE fts_idx_items USING fts5(title, comment, content=items,
content_rowid=id, prefix=3);  


The query:  

SQL??? :? SELECT ??? a.id, a.parent, a.by, a.score,
??? ??? ??? ??? snippet(fts_idx_items, 0, '', '', '...',
12) as title,
??? ??? ??? ??? a.type, a.url,
??? ??? ??? ??? --snippet(fts_idx_items, 1, '', '',
'...', 12) as comment,
??? ??? ??? ??? a.descendants,
??? ??? ??? ??? datetime(a."time", 'unixepoch') AS time,
??? ??? ??? ??? (julianday('now') - julianday(datetime(a."time",
'unixepoch'))) as elapsed_time
??? ??? ??? FROM items a, fts_idx_items b
??? ??? ??? where fts_idx_items match 'title:sql'
??? ??? ??? and a.id = b.rowid
??? ??? ??? --and a.type != 'comment'
??? ??? ??? ORDER BY rank --bm25(fts_idx_items)
??? ??? ??? LIMIT 30 OFFSET 0  

Generates:  

SQL??? :??? -- SELECT rowid, rank FROM 'main'.'fts_idx_items' ORDER BY
bm25(fts_idx_items) ASC??? 

Followed by 28 of this: 

SQL??? :??? -- SELECT pgno FROM 'main'.'fts_idx_items_idx' WHERE
segid=? AND term<=? ORDER BY term DESC LIMIT 1  

Followed by around 3,000 of:  

SQL??? :??? -- SELECT sz FROM 'main'.'fts_idx_items_docsize' WHERE id=?


Followed by 18 of:  

SQL??? :??? -- SELECT T.'id', T.'title', T.'comment' FROM
'main'.'items' T WHERE T.'id'= 

SQL??? :??? -- SELECT sz FROM 'main'.'fts_idx_items_docsize' WHERE
id=