date:20131115





Here's a thought:  What does your hypothetical function return for a table 
defined as follows:

 CREATE TABLE strange(rowid TEXT, _rowid_ TEXT, oid TEXT);

That table has a rowid, but it is completely inaccessible to the application.  
Does your function return TRUE or FALSE?

My point:  I think any application that depends on there being a column named "rowid" that is the key to the table is already 
broken.  WITHOUT ROWID does not add any new brokenness.


Yeah, that Schema would break most systems depending on a rowid today even, regardless of rowid-less tables, so the danger is 
ever-present.  Point taken (and view shared) - but it won't be the first brokenness-habit to permeate SQL-programs the world over. 
However, even if so broken, with a fix as easy as a single SQL query upon opening a table, I doubt the issue merrits any more time.


For Peter & Pepijn - I think the issue is essentially a forward-compatibility problem moreso than a backward-compatibility one. So I 
think your idea on introducing some version control would be the least painful.


Thank you kindly.
*goes off to import TABLE strange() into some systems for fun*
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intended use case for 'without rowid'?


On 15 Nov 2013, at 10:50pm, Peter Aronson  wrote:

> Actually, we were talking about the schema format number at offset 44.

Thanks.  I missed that one.  I can see an argument that allowing 'without 
rowid' should require increasing that to 5.

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

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Peter Aronson

Actually, we were talking about the schema format number at offset 44.  
However, neither that nor any of the other you point out will let you know if a 
without rowid table is present.  That's discovered when parsing the contents of 
the sqlite_master table.
 
Peter

From: Simon Slavin 
>To: Peter Aronson ; General Discussion of SQLite Database 
> 
>Sent: Friday, November 15, 2013 3:29 PM
>Subject: Re: [sqlite] Intended use case for 'without rowid'?
>
>
>I'm confused.  By 'Schema Version Number' are people meaning this:
>
>
>
>Or the header string at offset 0 in this:
>
>
>
>Or the value written at offset 92 in this:
>
>
>
>?
>
>The first one, which has a name nearest to 'Schema Version Number', should 
>have nothing to do with databases popping up with 'without row'.  On the other 
>hand, an application which is testing to see whether it understands the file 
>format can usefully check the value at 92 and make sure it's less than or 
>equal to such-and-such value.  Beginning to allow 'without rowid' must 
>increase the value used.
>
>Simon.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intended use case for 'without rowid'?

I'm confused.  By 'Schema Version Number' are people meaning this:



Or the header string at offset 0 in this:



Or the value written at offset 92 in this:



?

The first one, which has a name nearest to 'Schema Version Number', should have 
nothing to do with databases popping up with 'without row'.  On the other hand, 
an application which is testing to see whether it understands the file format 
can usefully check the value at 92 and make sure it's less than or equal to 
such-and-such value.  Beginning to allow 'without rowid' must increase the 
value used.

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

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Peter Aronson

The GeoPackage is both a use format, and an interchange specification.  Both 
Pepijen and I are involved with writing general purpose software that should, 
in theory, be able read any properly formed GeoPackage SQLite database.  If one 
of these databases contains a partial index or a without rowid (or some new, 
future feature that is also not backwards compatible), and our software is not 
running a late enough release of SQLite, we won't be able to read it (or 
someone using one of our spatial type libraries on an older version of SQLite). 
 I think the way to handle this is probably for any particular release of the 
GeoPackage spec to specify the oldest version of SQLite that it is required to 
be readable at, and thus require that features that would prevent that not be 
allowed in conforming GeoPackage databases.  So, maybe, GeoPackage 1.0 will 
require working with any version of SQLite from say, 3.7.0 onward, which would 
forbid partial indexes and
 without rowid tables in conforming databases.  And later releases could kick 
the minimum up.
 
Peter

From: RSmith 
>To: sqlite-users@sqlite.org 
>Sent: Friday, November 15, 2013 2:11 PM
>Subject: Re: [sqlite] Intended use case for 'without rowid'?
>
>
>Pepijn & Peter - I'm not sure how this will be an issue for the sort of 
>existing systems you describe?  You will need to actually 
>physically change your current schemas to produce the mentioned problems, 
>which if you don't, you have nothing to worry about.  The 
>only people I think should plan some changes are those making DB 
>admininstrator type systems where they cannot control what things 
>users open, in which case - yes, a parse error is on the books, but this 
>should be an easy addition for said devs.  (Never relying 
>on rowid turned out a pedanticism that paid off for me - it might be a lot of 
>changes for some though, so I understand the notion).
>
>You could also introduce a unique Application ID (see: 
>http://www.sqlite.org/pragma.html#pragma_application_id)
>
>or do a check:
>IF EXISTS (SELECT 1 FROM sqlite_master WHERE type="table" AND like('%WITHOUT 
>ROWID%',sql);
>
>- to simply know whether it's an incompatible file being opened and notify the 
>user as such.
>
>Being more pedantic - Schema X would still be Schema X and work exactly like a 
>Schema X worked before, the possibility that a Schema 
>Y might also be formed now does not mean Schema X works any different than 
>before, ergo this is not a true case for Schema versioning.
>
>- BUT -
>
>I would still like to see some kind of function, even a totally new one that 
>does not affect any backward compatibility, such as:
>
>*BOOL sqlite3_table_has_rowid(*tbl);
>
>where maybe if the 'tbl' parameter is empty it checks all tables and lets us 
>know whether any tables in the Schema does not contain 
>a rowid (FALSE) etc.
>
>
>The only reason I would want this is for speed (the query above might not be 
>very efficient, or, I might be wrong - an indication 
>would be appreciated).
>
>I hope this makes some sense - thanks.
>
>
>
>On 2013/11/15 21:17, Pepijn Van Eeckhoudt wrote:
>> Will without rowid introduce a new schema version number?
>>
>> If so, we’ll be ok since GeoPackage requires schema version 4.
>>
>> Pepijn
>>
>> On 15 Nov 2013, at 16:33, Peter Aronson  wrote:
>>
>>> One  additional thing not listed in this document -- use of a internal 
>>> rowid alias (OID, ROWID or _ROWID_) will produce a parse error on a query 
>>> against a WITHOUT ROWID table (unless, of course, it has an actual column 
>>> with the specified name),  which makes sense, of course, but could be an 
>>> issue for generic table handling code that currently uses any of those.
>>>
>>> The fact that the presence of such a table makes a database containing one 
>>> unreadable at releases before 3.8.2 is a bit of an issue for those using 
>>> SQLite as a data exchange format (like GeoPackages), but then that's true 
>>> with partial indexes too.
>>>
>>> Peter
>>>
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intended use case for 'without rowid'?

On Fri, Nov 15, 2013 at 4:11 PM, RSmith  wrote:

> I would still like to see some kind of function, even a totally new one
> that does not affect any backward compatibility, such as:
>
> *BOOL sqlite3_table_has_rowid(*tbl);
>
> where maybe if the 'tbl' parameter is empty it checks all tables and lets
> us know whether any tables in the Schema does not contain a rowid (FALSE)
> etc.
>

Here's a thought:  What does your hypothetical function return for a table
defined as follows:

 CREATE TABLE strange(rowid TEXT, _rowid_ TEXT, oid TEXT);

That table has a rowid, but it is completely inaccessible to the
application.  Does your function return TRUE or FALSE?

My point:  I think any application that depends on there being a column
named "rowid" that is the key to the table is already broken.  WITHOUT
ROWID does not add any new brokenness.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intended use case for 'without rowid'?

On Fri, Nov 15, 2013 at 4:11 PM, RSmith  wrote:

>
> I would still like to see some kind of function, even a totally new one
> that does not affect any backward compatibility, such as:
>
> *BOOL sqlite3_table_has_rowid(*tbl);
>
> where maybe if the 'tbl' parameter is empty it checks all tables and lets
> us know whether any tables in the Schema does not contain a rowid (FALSE)
> etc.
>


How about this:

   SELECT 1 FROM sqlite_master WHERE sql LIKE '%without%rowid%';

The above might give you a false positive, for a sufficient malicious
schema, but that seems unlikely in practice.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intended use case for 'without rowid'?

Pepijn & Peter - I'm not sure how this will be an issue for the sort of existing systems you describe?  You will need to actually 
physically change your current schemas to produce the mentioned problems, which if you don't, you have nothing to worry about.  The 
only people I think should plan some changes are those making DB admininstrator type systems where they cannot control what things 
users open, in which case - yes, a parse error is on the books, but this should be an easy addition for said devs.  (Never relying 
on rowid turned out a pedanticism that paid off for me - it might be a lot of changes for some though, so I understand the notion).


You could also introduce a unique Application ID (see: 
http://www.sqlite.org/pragma.html#pragma_application_id)

or do a check:
IF EXISTS (SELECT 1 FROM sqlite_master WHERE type="table" AND like('%WITHOUT 
ROWID%',sql);

- to simply know whether it's an incompatible file being opened and notify the 
user as such.

Being more pedantic - Schema X would still be Schema X and work exactly like a Schema X worked before, the possibility that a Schema 
Y might also be formed now does not mean Schema X works any different than before, ergo this is not a true case for Schema versioning.


- BUT -

I would still like to see some kind of function, even a totally new one that 
does not affect any backward compatibility, such as:

*BOOL sqlite3_table_has_rowid(*tbl);

where maybe if the 'tbl' parameter is empty it checks all tables and lets us know whether any tables in the Schema does not contain 
a rowid (FALSE) etc.



The only reason I would want this is for speed (the query above might not be very efficient, or, I might be wrong - an indication 
would be appreciated).


I hope this makes some sense - thanks.



On 2013/11/15 21:17, Pepijn Van Eeckhoudt wrote:

Will without rowid introduce a new schema version number?

If so, we’ll be ok since GeoPackage requires schema version 4.

Pepijn

On 15 Nov 2013, at 16:33, Peter Aronson  wrote:


One  additional thing not listed in this document -- use of a internal rowid 
alias (OID, ROWID or _ROWID_) will produce a parse error on a query against a 
WITHOUT ROWID table (unless, of course, it has an actual column with the 
specified name),  which makes sense, of course, but could be an issue for 
generic table handling code that currently uses any of those.

The fact that the presence of such a table makes a database containing one 
unreadable at releases before 3.8.2 is a bit of an issue for those using SQLite 
as a data exchange format (like GeoPackages), but then that's true with partial 
indexes too.

Peter



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

Re: [sqlite] SQLite ADO.NET for .Net Compact Framework 3.9

2013-11-15 Thread Joe Mistachkin


Paolo Patierno wrote:
> 
> I need to rebuild SQLite native DLL and Interop DLL with WEC 2013 compiler
> using Platform Builder and then recompiling SQLite ADO.NET assembly with a
> WEC2013 SDK (that use .Net CF 3.9).
> So I need to create a new solutions with other new projects.
> 

I've just checked in files that should assist with properly building things
for Windows Embedded Compact 2013, here:

https://system.data.sqlite.org/index.html/ci/0adcab7b75

Please let us know if these work for you.

--
Joe Mistachkin

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

Re: [sqlite] Intended use case for 'without rowid'?

On Fri, Nov 15, 2013 at 2:17 PM, Pepijn Van Eeckhoudt <
pep...@vaneeckhoudt.net> wrote:

> Will without rowid introduce a new schema version number?
>

No.  The syntax is fully compatible.  A change in the schema version number
would imply that there was some change in the meaning of the schema.  In
other words, a change in schema number would mean that an identical schema
could result in different bits-on-disk, or a different answer to the same
query.

An example of changing the schema number is when SQLite started to honor
the "DESC" modifier on column in CREATE INDEX, rather than ignoring it.
With that change, the meaning changed for statements such as:

CREATE INDEX ex1 ON tab1(a DESC, b ASC);

The bits on disk are different for the same index, depending on the schema
number.

But that is not the case here.  Bits-on-disk are unchanged for equivalent
schemas.  And the answers to queries are all the same.

Yes, new schemas are allowed now that were rejected before.  The same thing
happened when we added support for partial indices.  That is not a reason
to bump the schema number, though.

>
> If so, we’ll be ok since GeoPackage requires schema version 4.
>
> Pepijn
>
> On 15 Nov 2013, at 16:33, Peter Aronson  wrote:
>
> > One  additional thing not listed in this document -- use of a internal
> rowid alias (OID, ROWID or _ROWID_) will produce a parse error on a query
> against a WITHOUT ROWID table (unless, of course, it has an actual column
> with the specified name),  which makes sense, of course, but could be an
> issue for generic table handling code that currently uses any of those.
> >
> > The fact that the presence of such a table makes a database containing
> one unreadable at releases before 3.8.2 is a bit of an issue for those
> using SQLite as a data exchange format (like GeoPackages), but then that's
> true with partial indexes too.
> >
> > Peter
> >
> > On 11/15/2013 4:47 AM, Luís Simão wrote:
> >> SQLite answers those question in:
> >>
> >> http://www.sqlite.org/draft/withoutrowid.html
> >>
> >> BR
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Pepijn Van Eeckhoudt

Will without rowid introduce a new schema version number?

If so, we’ll be ok since GeoPackage requires schema version 4.

Pepijn

On 15 Nov 2013, at 16:33, Peter Aronson  wrote:

> One  additional thing not listed in this document -- use of a internal rowid 
> alias (OID, ROWID or _ROWID_) will produce a parse error on a query against a 
> WITHOUT ROWID table (unless, of course, it has an actual column with the 
> specified name),  which makes sense, of course, but could be an issue for 
> generic table handling code that currently uses any of those.
> 
> The fact that the presence of such a table makes a database containing one 
> unreadable at releases before 3.8.2 is a bit of an issue for those using 
> SQLite as a data exchange format (like GeoPackages), but then that's true 
> with partial indexes too.
> 
> Peter
> 
> On 11/15/2013 4:47 AM, Luís Simão wrote:
>> SQLite answers those question in:
>> 
>> http://www.sqlite.org/draft/withoutrowid.html
>> 
>> BR
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] EXPLAIN QUERY PLAN

2013-11-15 Thread jose isaias cabrera



"Simon Slavin" wrote...


On 15 Nov 2013, at 4:29pm, jose isaias cabrera  
wrote:



Is there any place that explain each of this entries?




However, don't feel you should understand them.  They're for geeks only.

The output from EXPLAIN QUERY PLAN can be very useful for people trying to 
optimize their code.  The output from EXPLAIN is really just for 
developers and geeks trying to understand SQLite itself.  If you need the 
output of EXPLAIN to explain why your code doesn't work, you have serious 
problems.


So yes, use EXPLAIN QUERY PLAN if you think it helps.  But use EXPLAIN 
only if you have spare time.


Thanks, Simon.

josé 


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

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Stephen Chrzanowski

On Fri, Nov 15, 2013 at 10:15 AM, RSmith  wrote:

>
> Now disregarding all the above - The very only reason you would use the
> WITHOUT ROWID optimization on any table is precisely because you are NOT
> using an integer primary key but because you are adding proper text values
> as the Primary key, so even if you could use the last_insert function to
> get a valid rowid then it won't help you because the table is referenced
> via text primary key and there is nothing useful the linked table can do
> woth the last_insert value... unless you are linking tables to each other
> using the actual rowid, which would be the very worst DB decision ever, so
> I'm sure that is not the case.
>
>
This is the paragraph that made the light turn on.  Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXPLAIN QUERY PLAN


On 15 Nov 2013, at 4:29pm, jose isaias cabrera  wrote:

> Is there any place that explain each of this entries?



However, don't feel you should understand them.  They're for geeks only.

The output from EXPLAIN QUERY PLAN can be very useful for people trying to 
optimize their code.  The output from EXPLAIN is really just for developers and 
geeks trying to understand SQLite itself.  If you need the output of EXPLAIN to 
explain why your code doesn't work, you have serious problems.

So yes, use EXPLAIN QUERY PLAN if you think it helps.  But use EXPLAIN only if 
you have spare time.

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

Re: [sqlite] help writing DELETE with JOIN

On Fri, Nov 15, 2013 at 11:44 AM, David Cotter  wrote:

> outside of the parens?
>
> shouldn't it go inside the parens?
> eg: say the playlist ID i want is "57", would i do this?
>

I think it gets the same result either way, right?  But it seems more
likely to use available indices if the playlistID=57 constraint is on the
WHERE clause of the UPDATE rather than the WHERE clause of the inner SELECT.

>
> also: what is the "1" for?
>

The result set of the SELECT inside of EXISTS(...) is ignored.  Return
whatever you like.  I choose the constant 1.

>
> sorry for my newb-ness, still learning!  but fun!
>
>  DELETE FROM playlist
> WHERE EXISTS(SELECT 1 FROM songlist
>  WHERE playlist.playlistID=songlist.playlistID
>AND playlist.playlistID=57
>AND songlist.stale)
>
>
>

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXPLAIN QUERY PLAN

2013-11-15 Thread jose isaias cabrera

"Jay A. Kreibich" wrote...

On Wed, Nov 13, 2013 at 12:20:42AM +, Walter Hurry scratched on the 
wall:

On Tue, 12 Nov 2013 17:47:05 -0500, jose isaias cabrera wrote:

> I am trying to speed up our queries and normalize our DB and I am
> reading,
>
> http://www.sqlite.org/eqp.html
>
> But, I am missing a lot.  Where do I read about the results and how to
> make changes to the DB to speed up/enhance the DB response?  Thanks.
>
Normalize first.
Then work out what queries you need, and add appropriate indexes.
How fast do you need it to be? If it's still not fast enough, consider
denormalizing selectively, and what the overhead will be in maintaining
redundant data.

 "Normalize 'til it hurts.  Denormalize 'til it works."

 (and in that order!)

doing that and it's hurting. :-)  Ok, one last question:  I ran this explain 
query and following it, is the result:
sqlite> explain SELECT id FROM LSOpenJobs WHERE bdate BETWEEN '2013-10-31' 
AND '

2013-01-01' AND status != 'd' AND Xtra4 != 'y' AND invoice > 0;
0|Trace|0|0|0||00|
1|String8|0|1|0|2013-10-31|00|
2|String8|0|2|0|2013-01-01|00|
3|String8|0|3|0|d|00|
4|String8|0|4|0|y|00|
5|Integer|0|5|0||00|
6|Goto|0|23|0||00|
7|OpenRead|0|7|0|40|00|
8|Rewind|0|21|0||00|
9|Column|0|10|6||00|
10|Lt|1|20|6|collseq(BINARY)|6a|
11|Gt|2|20|6|collseq(BINARY)|6a|
12|Column|0|29|7||00|
13|Eq|3|20|7|collseq(BINARY)|6a|
14|Column|0|39|8||00|
15|Eq|4|20|8|collseq(BINARY)|6a|
16|Column|0|18|9||00|
17|Le|5|20|9|collseq(BINARY)|6a|
18|Rowid|0|11|0||00|
19|ResultRow|11|1|0||00|
20|Next|0|9|0||01|
21|Close|0|0|0||00|
22|Halt|0|0|0||00|
23|Transaction|0|0|0||00|
24|VerifyCookie|0|320|0||00|
25|TableLock|0|7|0|LSOpenJobs|00|
26|Goto|0|7|0||00|
sqlite>

Is there any place that explain each of this entries?  I think I can figure 
very few of these out, but it would be nice to have a place that explain 
each of these.  Yes, I know I have to still normalize.  And it's slowing 
being done, but this is live data, so I have to be careful not to break 
anything.  thanks.

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

Re: [sqlite] help writing DELETE with JOIN

On Fri, Nov 15, 2013 at 11:01 AM, David Cotter  wrote:

> what about "playlistID=X" ?
>
> the playlist table has "playlistID", (different playlists)
> i only want the ones in a particular playlist
>
>
So add "AND playlistID=$x" to the WHERE clause.


DELETE FROM playlist
WHERE EXISTS(SELECT 1 FROM songlist
 WHERE playlist.playlistID=songlist.playlistID
   AND songlist.stale)
  AND playlistId=$x;


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Peter Aronson

One  additional thing not listed in this document -- use of a internal 
rowid alias (OID, ROWID or _ROWID_) will produce a parse error on a 
query against a WITHOUT ROWID table (unless, of course, it has an actual 
column with the specified name),  which makes sense, of course, but 
could be an issue for generic table handling code that currently uses 
any of those.


The fact that the presence of such a table makes a database containing 
one unreadable at releases before 3.8.2 is a bit of an issue for those 
using SQLite as a data exchange format (like GeoPackages), but then 
that's true with partial indexes too.


Peter

On 11/15/2013 4:47 AM, Luís Simão wrote:

SQLite answers those question in:

http://www.sqlite.org/draft/withoutrowid.html

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



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

Re: [sqlite] Intended use case for 'without rowid'?


I'm ALWAYS looking for a faster query (Who isn't? -- Except those edge
cases where management thinks the software is broken because the query is
TOO fast and doesn't trust the results) but the loss of some common use
functionality kind of has me wondering "Why?"


Well yes but...

Firstly, using the last_insert or equivalent function is not the SQL way to do things, but a rather clever optimization provided by 
most (if not all) SQL implementations that supports a very specific method of adding things to tables.  You could for instance 
rather remember (or find) the last added primary key through SQL, and then simply add data where YOU assign the primary key values 
(in stead of letting the autoinc do its thing) and then you have the key to insert in as many tables as needed - as it should work. 
The autoinc is just an added help for humans to make it easier since "everybody uses integer primary keys anyway", and it ensures 
unique values from a database-engin side removing that responsibility from your code. As far as SQL is concerned though, if you use 
a lot of matching keys to identify data in multiple tables, you should really be specifying those keys yourself and not rely on the 
various shortcuts.


It's probably faster even to specify Key values than wait for the DB engine to run its own Autoinc code for every insert (though 
this is very fast too).


Now disregarding all the above - The very only reason you would use the WITHOUT ROWID optimization on any table is precisely because 
you are NOT using an integer primary key but because you are adding proper text values as the Primary key, so even if you could use 
the last_insert function to get a valid rowid then it won't help you because the table is referenced via text primary key and there 
is nothing useful the linked table can do woth the last_insert value... unless you are linking tables to each other using the actual 
rowid, which would be the very worst DB decision ever, so I'm sure that is not the case.


Even in an after-insert Trigger (as another poster remarked) you would really need to know the Primary Key by direct reference 
already since the DB isnt making up it's own on an insert, I'm not sure how it would be of any value - but I might be 
misunderstanding the suggestion.


As to your question of: WHY?  I'm sure other posts describe the benefits well so I won't repeat it except to say that it is a very 
case-specific benefit and there is no need to use it for the normal tables you described.




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

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Dominique Devienne

On Fri, Nov 15, 2013 at 3:52 PM, Jean-Christophe Deschamps  wrote:

> At 15:45 15/11/2013, you wrote:
>
>  last_insert_rowid() is needed for autoincremented keys.
>>
>> WITHOUT ROWID tables do not have an autoincrementing key.  Your program
>> has to generate or get the key in some other way, so it knows the value
>> even before the record is inserted.
>>
>
> last_insert_primary_key would nevertheless be very useful in triggers.
>

But it can be a composite, how in what form do you "return" it? --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Igor Tandetnik


On 11/15/2013 9:52 AM, Jean-Christophe Deschamps wrote:

last_insert_primary_key would nevertheless be very useful in triggers.


How so? An AFTER INSERT trigger has the value in new.yourPKColumn. Can 
you show a scenario where this is insufficient, and a hypothetical 
last_insert_primary_key would save the day?

--
Igor Tandetnik

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

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Jean-Christophe Deschamps


At 15:45 15/11/2013, you wrote:


last_insert_rowid() is needed for autoincremented keys.

WITHOUT ROWID tables do not have an autoincrementing key.  Your program
has to generate or get the key in some other way, so it knows the value
even before the record is inserted.


last_insert_primary_key would nevertheless be very useful in triggers.

--
j...@antichoc.net  


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

Re: [sqlite] Intended use case for 'without rowid'?

On 15 Nov 2013, at 2:20pm, Stephen Chrzanowski  wrote:

> The lack of the last_insert function is kind of concerning, especially if
> you're making a "Person" list and you're simultaneously adding their
> contact info in the next query in your program.  You've got no reliable
> method of getting information back on the person you're inserted.  So,
> obviously, you wouldn't use it here.

The primary use for "without rowid" is for existence tables.  Lists of words 
and lists of real numbers are good examples.  The only thing you'd be using 
that table for is "I need a list of all the values that exist.".  You're 
probably going to build a big list, then spit them back out again sorted into 
order.  No need for a rowid because the primary key isn't an integer field.

For the sort of data in your example, you would want a conventional table with 
rowids, as you write.

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

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Clemens Ladisch

Stephen Chrzanowski wrote:
> The lack of the last_insert function is kind of concerning, especially if
> you're making a "Person" list and you're simultaneously adding their
> contact info in the next query in your program.  You've got no reliable
> method of getting information back on the person you're inserted.

last_insert_rowid() is needed for autoincremented keys.

WITHOUT ROWID tables do not have an autoincrementing key.  Your program
has to generate or get the key in some other way, so it knows the value
even before the record is inserted.

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

Re: [sqlite] Index performance

On 15 Nov 2013, at 12:55pm, L. Wood  wrote:

> The states are relatively few (50). There are tens of thousands of companies.
> 
> * If I frequently do queries like this:
> "SELECT * FROM Foo WHERE company_stock_symbol='bar' AND state='baz';"
> what index should I use?
> Should I use (company_stock_symbol), (state,company_stock_symbol), or 
> (company_stock_symbol,state)?

/You/ shouldn't use any of them.  You should let SQL pick what it needs.

But here's how to find out the answer to questions like that yourself if you 
don't understand how indexing works.

1) Create your table(s) and put in convincing data.
2) Run your query and make sure it returns the answers you want/expected.
3) Create all the indexes in all the orders you think could possibly be useful.
4) Run ANALYZE.
5) Use EXPLAIN QUERY PLAN on your query and find out which indexes were used
6) DROP all the indexes that weren't used.

After you've done this for 5 or 10 projects you'll find that most of the time 
you can make good guesses yourself and don't need to keep experimenting.

> * Does the answer change if the query condition is reversed? Like so:
> "SELECT * FROM Foo WHERE state='baz' AND company_stock_symbol='bar';" 

No.  SQLite analyzes your WHERE clauses in such a way that the order you 
originally specified the parts doesn't matter and has (almost) no influence on 
what's done.  You can write your SQL commands in the way that makes most sense 
to you, the programmer, and let the computer do the hard work.

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

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Stephen Chrzanowski

I'm kind of confused with this new 'toy'...  I read the doc linked, but
kind of scratching my head.  I FULLY acknowledge this is an optional
parameter.

So you create a table with the WITHOUT ROWID optional command, and you'll
lose the effect of sqlite3_last_insert_rowid() after an insert, which by my
understanding, the last insert result into a table without the "WITHOUT
ROWID" is retained if applicable, so basically stale information.  I was
thinking that this particular command would be useful in a table with no
primary key, but maybe in a table of FKs back to a table with a primary key
(Say a list of contact infos to a person) but a requirement of having a
primary key in the FK-only table would be required.

The lack of the last_insert function is kind of concerning, especially if
you're making a "Person" list and you're simultaneously adding their
contact info in the next query in your program.  You've got no reliable
method of getting information back on the person you're inserted.  So,
obviously, you wouldn't use it here.

If you're going to query the FK-only table against the Person table, you'd
obviously have an index on the FK field.  You'd probably never take a look
at a "primary key" in the FK-Only table, so having the additional
requirement of a PK in this table seems redundant.  I also know that rowid
already exists regardless if I use it or not in all tables prior to 3.8.2.

Maybe the coffee hasn't reached my brain yet, but but can someone give me
an "English" example of a use case where you'd use this reliably?

I'm ALWAYS looking for a faster query (Who isn't? -- Except those edge
cases where management thinks the software is broken because the query is
TOO fast and doesn't trust the results) but the loss of some common use
functionality kind of has me wondering "Why?"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intended use case for 'without rowid'?


Oh and of course the space saving for simple reference tables (basic 
Value-for-ref-lookups)  would be great.

To be sure, this does not just affect Text Keys, but all non-INTEGER primary 
keys, right?


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

Re: [sqlite] Intended use case for 'without rowid'?





On Fri, Nov 15, 2013 at 7:33 AM, RSmith > wrote:


Yes there would be a space-saving, but it is rather minimal. The real 
advantage is removing one complete lookup reference
cycle from a Query...


That was my original theory too.  But experimental evidence inverts this.

There is a program called "wordcount.c" (see http://www.sqlite.org/src/artifact/2c2cc111?ln) that tests the performance of WITHOUT 
ROWID.  It constructs a table like this:



etc.

Why yes of course there is no more need for an entire index and all the baggage accompanying it which makes for a ~50% reduction in 
a table with just those columns, but surely any normally useful data table (as opposed to this experimental type) consisting of many 
columns would receive a rapidly diminishing benefit in terms of size reduction?


 I am also convinced after some testing that the speed benefit might be somewhat bigger for these larger tables where more bytes 
distance exist for pages etc, but I might be wrong - will do some more testing later.

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

Re: [sqlite] Simple string question

2013-11-15 Thread Igor Tandetnik


On 11/15/2013 8:33 AM, L. Wood wrote:

But are there never quotes around the ?1, ?2, etc. even in SELECT statements? 
For example:

SELECT * FROM table WHERE col1=?1 AND col2=?2;

Is this correct or should there be '' around the ?1 or ?2 parameters?   



'?1' (with quotes) is a string literal consisting of two characters, ? 
and 1. ?1 (without quotes) is a parameter placeholder. Both are 
syntactically valid, but have completely different meaning.

--
Igor Tandetnik

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

Re: [sqlite] help writing DELETE with JOIN

On Fri, Nov 15, 2013 at 2:55 AM, David M. Cotter  wrote:

> i have a "song" table S that has "songID", "playlistID", and "stale"
> (boolean) as columns (among others)
> i have a "playlist" table P that has "playlistID" and "songID" as columns
> (among others)
>
> for a particular playlistID X, i want to delete all rows from P who's
> (P.playlistID == S.playlistID == X) and S.stale == true
>
>
(Composed in the mailer and untested:)

DELETE FROM playlist
WHERE EXISTS(SELECT 1 FROM songlist
 WHERE playlist.playlistID=songlist.playlistID
   AND songlist.stale);



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple string question

2013-11-15 Thread L. Wood

> D. Richard Hipp wrote:
> It is safer and faster to use the sqlite3_bind_text() interface.
>
> First prepare your statement like this:
>
> INSERT INTO my_table(col1) VALUES(?1);
>
> Then run:
>
> sqlite3_bind_text(pStmt, 1, zYourString, -1, SQLITE_TRANSIENT);
>
> Then run your statement:
>
> sqlite3_step(pStmt);
>
> Further information: http://www.sqlite.org/c3ref/bind_blob.html

Brilliant, what a great API!

But are there never quotes around the ?1, ?2, etc. even in SELECT statements? 
For example:

SELECT * FROM table WHERE col1=?1 AND col2=?2;

Is this correct or should there be '' around the ?1 or ?2 parameters?   
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index performance

On Fri, Nov 15, 2013 at 8:02 AM, Dominique Devienne wrote:

>
> What I wonder though is how many is "too many distinct values in the
> left-most columns", i.e. is 50 too many or not.
>
> Will there be a pragma for the cut-off number, or there's not really a
> cut-off number but instead the true cardinality of the left-most columns is
> evaluated and has bearings on the plan cost and it "wins" only if no other
> plans are cheaper. Richard?
>

The current decision algorithm will only consider a skip-scan if there is
an average of at least 50 occurrences of each distinct value in the index.
So it does not matter how many distinct values there are, only how many
distinct values there are relative to the total number of rows in the table.

Once the average-repeat-count>50 criteria is met, then the estimated cost
of running skip-scan is compared against the estimated cost of other query
plans (full table scan, various other indexed lookups, constructing a
transient index, etc.) and the plan with the lowest cost wins.

I emphasize that this is the *current* decision algorithm.  No doubt it
will be tuned for better performance as the code evolves.

Note that SQLite cannot know that there is an average of 50 or more
occurrences of each distinct value in the index unless you have run
ANALYZE.  Hence, the skip-scan algorithm will only be used after ANALYZE
has been run.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] help writing DELETE with JOIN

2013-11-15 Thread David M. Cotter

i have a "song" table S that has "songID", "playlistID", and "stale" (boolean) 
as columns (among others)
i have a "playlist" table P that has "playlistID" and "songID" as columns 
(among others)

for a particular playlistID X, i want to delete all rows from P who's 
(P.playlistID == S.playlistID == X) and S.stale == true

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

Re: [sqlite] Intended use case for 'without rowid'?

On Fri, Nov 15, 2013 at 7:33 AM, RSmith  wrote:

>
> Yes there would be a space-saving, but it is rather minimal. The real
> advantage is removing one complete lookup reference cycle from a Query...
>

That was my original theory too.  But experimental evidence inverts this.

There is a program called "wordcount.c" (see
http://www.sqlite.org/src/artifact/2c2cc111?ln) that tests the performance
of WITHOUT ROWID.  It constructs a table like this:

   CREATE TABLE wordcount(
   word TEXT PRIMARY KEY,
   cnt INTEGER
   ) WITHOUT ROWID; -- "WITHOUT ROWID" optional

And then reads a large text corpus and applies code like the following to
each contiguous sequence of alphabetic characters:

INSERT OR IGNORE INTO wordcount VALUES($new,1)
-- if the previous was a no-op, then also do:
UPDATE wordcount SET cnt=cnt+1 WHERE word=$new

In this way, it constructs a table that contains a single entry for each
word in the corpus, together with a count of the number of occurrences of
that word.

Applying this to the complete text of the King James Bible (791319 words of
which 13539 are distinct) both with and WITHOUT ROWID gives results like
this:

with rowid:   database size = 481280 bytes,  time = 3.569 seconds
without rowid:  database size = 218112 bytes,  time = 3.299 seconds

So you can see that insert performance is a little faster (about 8%) but
the more noticeable change is that the database is less than half its
original size.  So the big winner here is database size rather than
performance.

Some queries are faster with a WITHOUT ROWID table.  For the example above
and queries of the form:

SELECT cnt FROM wordcount WHERE word=$word;

A rowid table needs to do two lookups whereas a WITHOUT ROWID table can get
by with only one.  However, the second lookup of a rowid table is on a
B*-tree table with integer keys and is therefore very fast, so the total
speedup is only about 30%, not 50% as you might expect.

In summary:

   Reduction in CPU time:   5% to 30%
   Reduction in disk used:   up to 55%
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index performance

2013-11-15 Thread Dominique Devienne

On Fri, Nov 15, 2013 at 1:55 PM, L. Wood  wrote:

> Suppose I have a table Foo with two columns: state, company_stock_symbol.
> (There are other columns but let's ignore them.)
>
> The states are relatively few (50). There are tens of thousands of
> companies.
>

I was just reading http://www.sqlite.org/draft/releaselog/3_8_2.html after
the WITHOUT ROWID post, and this sounds like the next optimization to be
introduced
http://www.sqlite.org/draft/optoverview.html#skipscan might has some
influence on your index choices in the future.

What I wonder though is how many is "too many distinct values in the
left-most columns", i.e. is 50 too many or not.

Will there be a pragma for the cut-off number, or there's not really a
cut-off number but instead the true cardinality of the left-most columns is
evaluated and has bearings on the plan cost and it "wins" only if no other
plans are cheaper. Richard? TIA, --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Index performance

2013-11-15 Thread L. Wood

Suppose I have a table Foo with two columns: state, company_stock_symbol. 
(There are other columns but let's ignore them.)

The states are relatively few (50). There are tens of thousands of companies.

* If I frequently do queries like this:
"SELECT * FROM Foo WHERE company_stock_symbol='bar' AND state='baz';"
what index should I use?
Should I use (company_stock_symbol), (state,company_stock_symbol), or 
(company_stock_symbol,state)?

* Does the answer change if the query condition is reversed? Like so:
"SELECT * FROM Foo WHERE state='baz' AND company_stock_symbol='bar';"   
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Pepijn Van Eeckhoudt


On 15-11-13 12:47, Luís Simão wrote:

SQLite answers those question in:

http://www.sqlite.org/draft/withoutrowid.html

Thanks for the pointer. That answered all my questions.

Pepijn



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



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

Re: [sqlite] Intended use case for 'without rowid'?

2013-11-15 Thread Dominique Devienne

On Fri, Nov 15, 2013 at 12:47 PM, Luís Simão  wrote:

> SQLite answers those question in:
> http://www.sqlite.org/draft/withoutrowid.html

Interesting, thanks. This was discussed early this year I recall (
http://www.mail-archive.com/sqlite-users@sqlite.org/msg75669.html), and is
equivalent to Oracle's Index Organized Tables I believe, so  advice on when
to use IOTs apply similarly. See for example
http://stackoverflow.com/questions/3382939 . Glad they're making their way
into SQLite. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intended use case for 'without rowid'?

It can already be downloaded and is quite exciting.

Yes there would be a space-saving, but it is rather minimal. The real advantage is removing one complete lookup reference cycle from
a Query where the Primary key is anything other than an INTEGER-typed primary key.

When you have an INTEGER primary key, and specifies it in a lookup (ex. ... WHERE ID=5) the BTREE implementation simply branch down
the tree to the exact leaf - (let's call this a reference cycle).

So for another type of index, which to my understanding even include INT typed Primary keys (along with TEXT, NUMERIC, etc)
[*Citation Needed] the old implementation had to store the BTREE for the index, find it's node in the Tree, obtain from it a rowid,
then run down that tree to find the data leaf. [This may be a simplified version of the actual events]. So NOW, we can traverse a
BTREE for the real Primary key directly to a data leaf (or page-link if the data is large) in stead of invoking another reference cycle.

Neat, isn't it?

Of course all those SQLite DB managers who depended on RowID for editing values etc. now need to take careful note of this
possibility that future tables may not sport any RowIDs and referencing it may fail. (or does it?)

I wonder if the Devs will consider adding a Pragma or a column in the table_info(tbl) Pragma to indicate this somehow, as opposed to
having to parse the table create sql to learn whether a rowid is available or not - OR - better even, make the word "rowid" (and/or
its various forms) become an alias for the Primary key (as opposed to the other way round) where tables have primary keys. Might
even make things like (... WHERE RowID LIKE "John%") possible.

These are just musing for now, anyone else have any thoughts?

On 2013/11/15 13:14, Pepijn Van Eeckhoudt wrote:
I've been looking into the upcoming 'without rowid' feature implementation to assess if it will have any impact on the OGC
GeoPackage specification.

One of the things I was wondering is what the intended use case of this feature is. Does it provide a performance boost and/or
space savings? If not, when would you use this?

Regards,

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

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

Re: [sqlite] .import error: cannot open large file