Re: [sqlite] Index performance

On Fri, 15 Nov 2013 12:55:07 + "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?

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

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

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

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

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] SQLite ADO.NET for .Net Compact Framework 3.9

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

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] EXPLAIN QUERY PLAN

"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

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

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

Re: [sqlite] EXPLAIN QUERY PLAN

"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

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

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

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] Simple string question

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'

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

Re: [sqlite] Simple string question

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

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

[sqlite] help writing DELETE with JOIN

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

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] Index performance

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

[sqlite] Index performance

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'

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] .import error: cannot open large file

On 15 Nov 2013, at 9:23am, Luís Simão wrote: > Not sure if it helps, but you could try > >sqlite3 somedb '.import /dev/stdin hugetable' < huge.file > > making OS handle file reading instead of another process (cat). If the source really is a file then the right way

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

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

Not sure if it helps, but you could try sqlite3 somedb '.import /dev/stdin hugetable' < huge.file making OS handle file reading instead of another process (cat). LS 2013/11/14 lpryszcz > Hi, Often I pipe tables (from .gz or multiple files). I found it also work >

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

I think that solution isn't so simple. 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. 2013/11/15