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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

> 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

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

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

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

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

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

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:

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.

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

lav...@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 'Sc

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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