Re: [sqlite] Left join help.

2013-04-26 Thread James K. Lowden
On Fri, 26 Apr 2013 14:06:23 +0200 Clemens Ladisch wrote: > When using CROSS where standard SQL allows it (for an actual cartesian > product), the table ordering does not really matter for optimization > purposes because the DB has to do two nested full table scans anyway.

[sqlite] Opening an in-memory database in two connections, one read-write, one read-only.

2013-04-26 Thread Scott Hess
Someone over here was trying to use a shared-cache in-memory database with two connections, one a read-write connection for purposes of populating things, the other a read-only connection for purposes of letting a (trusted) user make queries. They were surprised to find out that they could run

Re: [sqlite] Order of columns in group by statement affects query performance

2013-04-26 Thread James K. Lowden
On Fri, 26 Apr 2013 11:12:15 +0200 Hick Gunter wrote: > It is not unreasonable to assume that in a well designed SQL > Statement the GROUP BY clause will be backed up by the necessary > index and an identical ORDER BY clause That is an entirely unreasonable assumption. Order

Re: [sqlite] What pragma to use to get maximum speed (at expense of safety)?

2013-04-26 Thread David King
> The idea of temporary tables in-memory is nice, but I do not know how > to apply it. > I see in the documentation I can use the TEMP in CREATE TABLE, but I am not > sure of the effect. > Does it mean that the table is created in memory and it is lost in > sqlite3_close? There are a few things

[sqlite] FW:

2013-04-26 Thread marco
http://www.horsecenter.com.br/i4jsow.php ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Programming API vs console

2013-04-26 Thread Simon Slavin
On 27 Apr 2013, at 3:29am, Igor Korot wrote: > sqlite> SELECT ownerid FROM owners WHERE ownername = 'Team 1' AND id = 1; > 53 For testing, kill the sub-select in your INSERT command and just put a 53 in there. See if that changes anything. > sqlite> Please retrieve the

Re: [sqlite] Programming API vs console

2013-04-26 Thread Igor Korot
Simon, On Fri, Apr 26, 2013 at 5:49 PM, Simon Slavin wrote: > > On 27 Apr 2013, at 12:27am, Igor Korot wrote: > > > And here is the log from the console: > > > > SQLite version 3.7.14 2012-09-03 15:42:36 > > Enter ".help" for instructions > > Enter SQL

Re: [sqlite] What pragma to use to get maximum speed (at expense of safety)?

2013-04-26 Thread Simon Slavin
On 27 Apr 2013, at 2:21am, Paolo Bolzoni wrote: > The idea of temporary tables in-memory is nice, but I do not know how > to apply it. > I see in the documentation I can use the TEMP in CREATE TABLE, but I am not > sure of the effect. Temporary tables are a

Re: [sqlite] What pragma to use to get maximum speed (at expense of safety)?

2013-04-26 Thread Paolo Bolzoni
The idea of temporary tables in-memory is nice, but I do not know how to apply it. I see in the documentation I can use the TEMP in CREATE TABLE, but I am not sure of the effect. Does it mean that the table is created in memory and it is lost in sqlite3_close? On Fri, Apr 26, 2013 at 8:07 PM,

Re: [sqlite] Programming API vs console

2013-04-26 Thread Simon Slavin
On 27 Apr 2013, at 12:27am, Igor Korot wrote: > And here is the log from the console: > > SQLite version 3.7.14 2012-09-03 15:42:36 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> INSERT INTO playersdrafted VALUES( 125, 1, ( SELECT

Re: [sqlite] Transaction question

2013-04-26 Thread Igor Tandetnik
On 4/26/2013 7:18 PM, Igor Korot wrote: If I'm inside transaction and want to insert a record, will I see right after executing sqlite3_step() or I have to execute a "COMMIT" statement to see it? The connection that executed sqlite3_step() would see its own changes right away. Other

Re: [sqlite] Programming API vs console

2013-04-26 Thread Igor Korot
And here is the log from the console: SQLite version 3.7.14 2012-09-03 15:42:36 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> INSERT INTO playersdrafted VALUES( 125, 1, ( SELECT ownerid FROM owners WHERE ownername = "Team 1" AND id = 1 ), 38, 1, "OF" ); sqlite>

[sqlite] Transaction question

2013-04-26 Thread Igor Korot
Hi, ALL, If I'm inside transaction and want to insert a record, will I see right after executing sqlite3_step() or I have to execute a "COMMIT" statement to see it? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Version 3.7.17 Preview

2013-04-26 Thread Darren Duncan
On 2013.04.26 8:34 AM, Richard Hipp wrote: Please visit http://www.sqlite.org/draft/releaselog/3_7_17.html to see the proposed enhancements and changes in SQLite version 3.7.17. Your comments, criticisms and suggestions are welcomed and encouraged. I think the extension mechanism is valuable,

Re: [sqlite] What pragma to use to get maximum speed (at expense of safety)?

2013-04-26 Thread David King
auto_vacuum Turn off autovacuum and just run it yourself when you're idle foreign_keys Turn off foreign keys checks (or just don't use foreign keys) ignore_check_constraints Same journal_mode OFF might actually be faster than MEMORY, but disables rollback support locking_mode EXCLUSIVE can be

Re: [sqlite] Writing in a blob

2013-04-26 Thread Roland Hughes
It actually does make sense to add chunking to sqlite. There would be some computational overhead, but, that all depends on the chunk size and the cache size of the database. It makes no sense to implement YAFS (Yet Another File System) inside of SQLite. While many here view SQLite only in

[sqlite] What pragma to use to get maximum speed (at expense of safety)?

2013-04-26 Thread Paolo Bolzoni
The subject pretty much says it all, I use sqlite3 as a way to save temporary results from a calculation. In this context I do not care about safety of the data. If the program fails or there is a blackout I will just delete the sqlite3 file, eventually fix the bug, and restart. At the moment I

Re: [sqlite] Version 3.7.17 Preview

2013-04-26 Thread jose isaias cabrera
"Richard Hipp" wrote... Please visit http://www.sqlite.org/draft/releaselog/3_7_17.html to see the proposed enhancements and changes in SQLite version 3.7.17. Your comments, criticisms and suggestions are welcomed and encouraged. Wow! Lots of new features... Sweet! Snapshot

Re: [sqlite] Writing in a blob

2013-04-26 Thread Simon Slavin
On 26 Apr 2013, at 5:26pm, Stephen Chrzanowski wrote: > ALL THAT SAID, I doubt it'd get implemented I'm also in this category. In fact I hope it doesn't get implemented. Yes, technically it can be done. But it's the sort of thing people assign as Computer Science

Re: [sqlite] Writing in a blob

2013-04-26 Thread Richard Hipp
On Fri, Apr 26, 2013 at 12:26 PM, Stephen Chrzanowski wrote: > Streaming a chunk of data as one huge "thing" is going to be > faster in regards to writing and reading. > That depends. See http://www.sqlite.org/intern-v-extern-blob.html -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] Writing in a blob

2013-04-26 Thread Stephen Chrzanowski
+0.75 to Roland for splitting the data, and another +1 for reiterating the true functionality of what LITE means. Splitting the data into chunks will help with keeping a database small as it can throw the raw data into pages that were previously marked as deleted, thereby not increasing the

Re: [sqlite] Writing in a blob

2013-04-26 Thread Paolo Bolzoni
Writing a FS as sqlite3 as backend sounds an interesting challenge. But I would like recalling everyone, that the question was about writing an arbitrary precision integer in the DB considering that the library writes the representation in a FILE*. At the end I wrote a little FILE* wrapper

[sqlite] Version 3.7.17 Preview

2013-04-26 Thread Richard Hipp
Please visit http://www.sqlite.org/draft/releaselog/3_7_17.html to see the proposed enhancements and changes in SQLite version 3.7.17. Your comments, criticisms and suggestions are welcomed and encouraged. Snapshot amalgamation builds are available at http://www.sqlite.org/draft/download.html -

Re: [sqlite] Writing in a blob

2013-04-26 Thread Roland Hughes
Speaking as an IT professional with 20+ years in the field, I would have to say adding any kind of "file system" support to SQLite would be a horrible thing. Yes, I've used Oracle. I've also used the only real product Oracle has, RDB on OpenVMS. I've written books covering MySQL, PostgreSQL,

Re: [sqlite] Export Of BLOB Data

2013-04-26 Thread Clemens Ladisch
Nigel Verity wrote: > I am on Linux, but need to export the data to somebody on Windows > running MS Access 2010. > > Can anybody advise on how to handle the export of the BLOB data? AFAIK Access does not have blob literals. So the best way would be to copy the SQLite file to the Windows PC,

[sqlite] Export Of BLOB Data

2013-04-26 Thread Nigel Verity
Hi I have a table with a record definition broadly as follows: ID (Integer Primary Key) Field1 (Text) Field2 (Text) Field3 (BLOB) The BLOB field contains document scans in JPG and PDF format, and some source documents in ODT and DOC formats. I am on Linux, but need to export the data to

Re: [sqlite] sequential row numbers from query

2013-04-26 Thread Igor Tandetnik
On 4/26/2013 8:56 AM, hiteshambaliya wrote: I want to get serial numbers for selected record from 1 to number of rows. Your solution is right but in the situation when i want to sort by 'Party Name' column then the serial number depend on autoid field arrange also as sorted party name so i can't

Re: [sqlite] sequential row numbers from query

2013-04-26 Thread hiteshambaliya
Hi, I want to get serial numbers for selected record from 1 to number of rows. Your solution is right but in the situation when i want to sort by 'Party Name' column then the serial number depend on autoid field arrange also as sorted party name so i can't get it as serial records nos. Please

Re: [sqlite] Left join help.

2013-04-26 Thread Danilo Cicerone
Many thanks to you all, I really appraciate your helpfulness, Danilo 2013/4/26 Clemens Ladisch > Hick Gunter wrote: > > Actually "CROSS" just forces SQLite to use the tables in the order > > specified. > > This is an SQLite optimization extension. I used CROSS because this

Re: [sqlite] reusing statements after they were interrupted

2013-04-26 Thread Richard Hipp
On Thu, Apr 25, 2013 at 5:55 PM, Jason Boehle wrote: > is it still safe to reuse this statement even though reset() returns > an error? > Yes -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org

[sqlite] reusing statements after they were interrupted

2013-04-26 Thread Jason Boehle
I know the docs on sqlite3_reset() say that it will return an error code if the last call to step() on the statement returned an error. My question is...is it still safe to reuse this statement even though reset() returns an error? It appears that on iOS 6.1, if the last call to step() on the

Re: [sqlite] Port SQLite to VxWorks 6.8

2013-04-26 Thread pmb
We (Peter and me) have been having problems with the following lines of code from the sqlite3.c of the SQLite 3.7.16.1. *Problem 1: * Undefined symbol "isDelete". Any idea what this variable does? Or where it is defined? static int fillInUnixFile( . . . ) { . . . #if OS_VXWORKS if(

Re: [sqlite] Left join help.

2013-04-26 Thread Clemens Ladisch
Hick Gunter wrote: > Actually "CROSS" just forces SQLite to use the tables in the order > specified. This is an SQLite optimization extension. I used CROSS because this is the only explicit join type where standard SQL allows to omit the join expression. When using CROSS where standard SQL

Re: [sqlite] Left join help.

2013-04-26 Thread Hick Gunter
Actually "CROSS" just forces SQLite to use the tables in the order specified. The result sets are identical, except for ordering and/or speed. It is the LEFT join that creates the rows where there is no match on the right hand side. explain query plan select

Re: [sqlite] Left join help.

2013-04-26 Thread Hick Gunter
http://www.sqlite.org/lang_select.html (1) "If there is an ON clause specified, then the ON expression is evaluated for each row of the cartesian product as a boolean expression. All rows for which the expression evaluates to false are excluded from the dataset." (2) " If the join-op is a

Re: [sqlite] Left join help.

2013-04-26 Thread Clemens Ladisch
Danilo Cicerone wrote: > table appx stores item's(table itx) quantity load for each user (table > subj). I'd to know how many items each user has: > > Paul|Box|3 > Paul|Letter|0 > Paul|Pen|0 > John|Box|0 > John|Letter|4 > John|Pen|0 > > I tried: > > select sub_descr, itx_descr, app_load from subj

[sqlite] [SQLite.NET] DbProviderServices DB* methods implementation

2013-04-26 Thread Matthijs ter Woord
It's great to see SQLite to have a decent entityframework provider. One area that's missing is the DB* methods of the DbProviderServivices (used to check for and create/update databases). I want to implement that (almost have it working already). What's the best road to follow to get that

[sqlite] Left join help.

2013-04-26 Thread Danilo Cicerone
Hi to all, I'm looking for a query on the following schema: PRAGMA foreign_keys=ON; BEGIN TRANSACTION; CREATE TABLE subj ( sub_id INTEGER PRIMARY KEY, -- 00 sub_descr TEXT DEFAULT NULL -- 01 ); INSERT INTO "subj" VALUES(1,'Paul'); INSERT INTO "subj" VALUES(2,'John'); CREATE TABLE itx (

Re: [sqlite] Order of columns in group by statement affects query performance

2013-04-26 Thread Hick Gunter
Do you have any experience with SQLite virtual tables? I guess not. There are 20 issues here: 1) The abstract problem of choosing an Index for optimizing GROUP BY 2) the SQLite implementation (which I was referring to) Ad 1) Any index that covers all the GROUP BY fields is a "good" index

Re: [sqlite] Programming API vs console

2013-04-26 Thread Igor Korot
Hi, ALL, On Thu, Apr 25, 2013 at 2:41 PM, Random Coder wrote: > On Tue, Apr 23, 2013 at 8:47 PM, Igor Korot wrote: > > > query = wxString::Format(...); > > if( ( result = sqlite3_prepare_v2( m_handle, query, -1, , 0 ) ) != > > SQLITE_OK ) > > > > It's