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

[Pepijn Van Eeckhoudt]

On 18 Nov 2013, at 06:13, Simon Slavin  wrote:
> On 18 Nov 2013, at 3:38am, Peter Aronson  wrote:
> 
>> It might be simpler to simply specify a minimum release of SQLite that must 
>> be supported,
> 
> Actually this is how lots of apps specify their file format.  Part of the 
> file header is a version number.  When the API opens the file it checks to 
> see that its version is equal or higher to the version number encoded in the 
> file.  If not, it presents an error message.
> 
> This means you don't have to encode complicated logic about which file 
> version requires which abilities.  All you need to do is compare two 
> versions.  It's neat.

That would be the ideal solution but, unless I missed it, there’s no such 
number in the sqlite database header that we can use for this purpose. Schema 
format number seems like the closest thing that you could use for this, but it 
wasn’t bumped with the addition of partial indices and without rowid.

I guess Peter’s ‘try to open with SQLite 3.7’ heuristic is the best we’ll be 
able to do. The only downside of that approach is that you can’t distinguish 
between incompatible DDL and actual database corruption.

Pepijn
___
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'?

[Nico Williams]

On Fri, Nov 15, 2013 at 5:14 AM, 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?

My take:

a) one of the upcoming SQLite4's primary benefits over SQLite3 is that
SQLite4 does not have implied rowids,

b) because implied rowids were a mistake,

c) because implied rowids often resulted in an extra index per-table
(when a rowid is not desired), which means:

 - a bigger db
 - at least slower data modification statements (to update the extra index)
 - slower queries if suitable covering indexes are not declared
(because of the extra implied join)
 - slower queries even if suitable covering indexes are declared
(because of the extra cache thrashing on writes)

d) and because implied rowids almost certainly resulted in extra C
code that goes away when the rowids are made explicit (when desired),
since, after all, SQLite3 already had code to deal with multiple
indexes, and so on.

(d) is a big deal, IMO.  Removing redundant code and reusing more of
the existing code infrastructure means improving maintainability,
which leads to faster bug-fix and feature development cycles.

(c) is also a big deal: faster updates, faster queries, smaller databases.

What's not to like about not having implied rowids?

DB navigator-type apps might have been using rowid as a crutch for
manual, ex-application DB navigation, but that's all rowids are for
such apps: crutches.

I can't think of a single case where I would like to have a rowid
other than cases where an explicit INTEGER PRIMARY KEY column is
natural for solving some task.  Even autoincrement: I'd rather do it
in the app.

Nico
--
___
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'?

[Simon Slavin]

On 18 Nov 2013, at 3:38am, Peter Aronson  wrote:

> It might be simpler to simply specify a minimum release of SQLite that must 
> be supported,

Actually this is how lots of apps specify their file format.  Part of the file 
header is a version number.  When the API opens the file it checks to see that 
its version is equal or higher to the version number encoded in the file.  If 
not, it presents an error message.

This means you don't have to encode complicated logic about which file version 
requires which abilities.  All you need to do is compare two versions.  It's 
neat.

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'?

[Peter Aronson]

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.

Indeed. The lack of rowid itself is not an issue. It's that someone could 
create a database using SQLite 3.8.x features, pass it on to someone with a 
SQLite 3.7 based app and run into runtime problems that they weren't expecting. 
We're looking for some way to reliably detect these kinds of cases or 
alternatively language to put in the spec to prevent this situation in the 
first place.

AFAICT there's no simple value in the database we can use to check for this. I 
mistakenly thought we could use the schema format number for this. Queries on 
sqlite_master could work but we would need to come up with a way to use this 
reliably. The suggested queries check for known problems. I'm worried that this 
will be an inherently brittle check since the next sqlite revision could 
introduce a new feature for which the spec has no checks in place.

Perhaps we should make the allowed DDL subset a part of the spec. That way we 
make explicit what is allowed and anything outside of that is forbidden.

Pepijn


Thinking about this further, it occurs to me that a brute force way of 
testing this would be to keep around a separate executable with a 
statically linked version of a minimum release of SQLite you want to 
support, and have it try to open and access a database to make sure it 
didn't have any unsupported database objects.  (It would, BTW, also fail 
in the same way if there are any unrecognized functions in any check 
constraints, but that's probably OK.)  Parsing the sqlite_master table 
for DDL SQL unsupported at a specified release would probably require 
extracting SQLite's SQL parser to perform correctly (but then, if you 
did that, you could extract the parser for your minimum release 
supported), which isn't exactly making things easy to use.


Peter

___
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'?

[Peter Aronson]

On 11/16/2013 10:46 AM, RSmith wrote:


Perhaps we should make the allowed DDL subset a part of the spec. 
That way we make explicit what is allowed and anything outside of 
that is forbidden. Pepijn 


Perhaps.
It would involve a rather large document though, one which an average 
user is sure to skip over but at least it provides indemnity, plus I 
don't see an enormous or sudden uptake of WITHOUT ROWID and/or partial 
Index tables in the general populous. By the time this is a 
generality, you should be a couple of versions of your system further 
down the product line.


Ryan


It might be simpler to simply specify a minimum release of SQLite that 
must be supported, and forbid any DDL statements that could not be 
parsed at that release, giving partial indexes (and maybe without rowid 
tables) as an example.


(Pepijn, when a you first access any object in database containing an 
object created using an unsupported DDL command, the prepare will fail 
with:


SQLITE_CORRUPT 11   /* The database disk image is malformed */


As that will trigger a parse of the contents of the sqlite_master 
table.  Note that this does not actually happen when you first open the 
database, unless you have a statically loaded extension that accesses a 
database object.)


Peter

___
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'?

[Jean-Christophe Deschamps]

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?


You're correct, there is no situation where such a thing would make sense. 


___
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'?

[RSmith]

Perhaps we should make the allowed DDL subset a part of the spec. That way we make explicit what is allowed and anything outside 
of that is forbidden. Pepijn 


Perhaps.
It would involve a rather large document though, one which an average user is sure to skip over but at least it provides indemnity, 
plus I don't see an enormous or sudden uptake of WITHOUT ROWID and/or partial Index tables in the general populous. By the time this 
is a generality, you should be a couple of versions of your system further down the product line.


Ryan
___
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 Van Eeckhoudt]

> 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.
Indeed. The lack of rowid itself is not an issue. It's that someone could 
create a database using SQLite 3.8.x features, pass it on to someone with a 
SQLite 3.7 based app and run into runtime problems that they weren't expecting. 
We're looking for some way to reliably detect these kinds of cases or 
alternatively language to put in the spec to prevent this situation in the 
first place.

AFAICT there's no simple value in the database we can use to check for this. I 
mistakenly thought we could use the schema format number for this. Queries on 
sqlite_master could work but we would need to come up with a way to use this 
reliably. The suggested queries check for known problems. I'm worried that this 
will be an inherently brittle check since the next sqlite revision could 
introduce a new feature for which the spec has no checks in place.

Perhaps we should make the allowed DDL subset a part of the spec. That way we 
make explicit what is allowed and anything outside of that is forbidden.

Pepijn
___
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'?

[James K. Lowden]

On Fri, 15 Nov 2013 17:15:39 +0200
RSmith  wrote:

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

Yes.  That's especially true in DBMSs that support more than one writer
at a time, where an autoincrementing key introduces contention where
there otherwise would be none. 

--jkl
___
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'?

[James K. Lowden]

On Fri, 15 Nov 2013 16:16:34 -0500
Richard Hipp  wrote:

> 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);
> 
> How about this:
> 
>SELECT 1 FROM sqlite_master WHERE sql LIKE '%without%rowid%';

Actually, this discussion highlights the fact that row_id always was an
attribute of a table, however hidden and magical.  Better than a new
function would be a new column in SQLITE_MASTER.  (LIKE is
often a clue that an attribute is encoded in the text!)  

--jkl
___
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'?

[RSmith]

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'?

[Simon Slavin]

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'?

[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 <slav...@bigfraud.org>
>To: Peter Aronson <pbaron...@att.net>; General Discussion of SQLite Database 
><sqlite-users@sqlite.org> 
>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:
>
><http://www.sqlite.org/pragma.html#pragma_schema_version>
>
>Or the header string at offset 0 in this:
>
><http://www.sqlite.org/fileformat.html>
>
>Or the value written at offset 92 in this:
>
><http://www.sqlite.org/fileformat.html>
>
>?
>
>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'?

[Simon Slavin]

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'?

[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 <rsm...@rsweb.co.za>
>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 <pbaron...@att.net> 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'?

[Richard Hipp]

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'?

[Richard Hipp]

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'?

[RSmith]

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] Intended use case for 'without rowid'?

[Richard Hipp]

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'?

[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] Intended use case for 'without rowid'?

[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] Intended use case for 'without rowid'?

[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'?

[RSmith]

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'?

[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'?

[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'?

[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'?

[Simon Slavin]

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'?

[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] Intended use case for 'without rowid'?

[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'?

[RSmith]

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'?

[RSmith]

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] Intended use case for 'without rowid'?

[Richard Hipp]

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] Intended use case for 'without rowid'?

[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'?

[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'?

[RSmith]

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] Intended use case for 'without rowid'?

[Luís Simão]

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] Intended use case for 'without rowid'?

[Pepijn Van Eeckhoudt]

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