Re: [sqlite] Atomic database structure initialization

> > There is also a PRAGMA user_version (see > http://www.sqlite.org/pragma.html#pragma_schema_version) which will let you > store a number in the database header so you can keep track of what version > of the "user schema" you have implemented in the database. Initially, when > the database

Re: [sqlite] Atomic database structure initialization

Paul wrote: >> pragma user_version; >> >> returns a single row with a single value which is the version, and the >> command, >> >> pragma user_version=n; >> >> lets you change it to n. Perhaps you can use this as a flag to tell yourself >> that you are working with an uninitialized database (value

Re: [sqlite] Definition of struct sqlite3_stmt

Prakash Premkumar wrote: > Can you please tell me where is the definition of the struct sqlite3_stmt ? There is no definition of struct sqlite3_stmt. Search for this instead: /* ** An instance of the virtual machine. This structure contains the complete ** state of the virtual machine.

Re: [sqlite] Definition of struct sqlite3_stmt

Thanks Clemens and hick On 22 Sep 2014 14:22, "Clemens Ladisch" wrote: > Prakash Premkumar wrote: > > Can you please tell me where is the definition of the struct > sqlite3_stmt ? > > There is no definition of struct sqlite3_stmt. > > Search for this instead: > > /* > ** An instance of the vi

Re: [sqlite] Version info in doc pages

The changes.html page is fine, but I would rather see something on the 'WITH clause' page itself. PHP online doc is a good example of how it can be done. Otherwise it would be like the Hitchhikers Guide to the Galaxy where the plans for the destruction of Earth were available for all to view and

Re: [sqlite] Version info in doc pages

I disagree with adding version info or dates of inclusion for things are added to the language. The fact that the code is there should be good enough, and if you need to know when something was added/removed/modified, the aforementioned doc will tell you. When something was added isn't as importa

Re: [sqlite] Atomic database structure initialization

> Paul wrote: > >> pragma user_version; > >> > >> returns a single row with a > single value which is the version, and the command, > >> > >> pragma > user_version=n; > >> > >> lets you change it to n. Perhaps you can use this > as a flag to tell yourself > >> that you are working with an uninit

Re: [sqlite] Atomic database structure initialization

> Paul wrote: > >> pragma user_version; > >> > >> returns a single row with a single value which is the version, and the > >> command, > >> > >> pragma user_version=n; > >> > >> lets you change it to n. Perhaps you can use this as a flag to tell > >> yourself > >> that you are working with an un

Re: [sqlite] 50% faster than 3.7.17

On Fri, Sep 19, 2014 at 09:14:17PM -0400, Richard Hipp wrote: > The latest SQLite 3.8.7 alpha version (available on the download page > http://www.sqlite.org/download.html) is 50% faster than the 3.7.17 release > from 16 months ago. That is to say, it does 50% more work using the same > number of

Re: [sqlite] Atomic database structure initialization

Paul wrote: > I can check whether user_version matches magic number without transaction. No. Executing "PRAGMA user_version" will start an automatic transaction if you didn't already start an explicit one. > Only when user_version does not match magic number I start transaction. This will never

Re: [sqlite] Atomic database structure initialization

On 22 Sep 2014, at 1:13pm, Paul wrote: > The only thing I am worried about is whether > > pragma user_version=n; > > respects transactions and will be rolled back automatically in case > if something happens between that statement and COMMIT. SQLite version 3.8.5 2014-08-15 22:37:57 Ente

Re: [sqlite] Atomic database structure initialization

> Paul wrote: > > I can check whether user_version matches magic number without transaction. > > No. Executing "PRAGMA user_version" will start an automatic transaction > if you didn't already start an explicit one. > > > Only when user_version does not match magic number I start transaction. >

Re: [sqlite] Atomic database structure initialization

> > On 22 Sep 2014, at 1:13pm, Paul wrote: > > > The only thing I am worried about is whether > > > > pragma user_version=n; > > > > respects transactions and will be rolled back automatically in case > > if something happens between that statement and COMMIT. > > SQLite version 3.8.5 20

Re: [sqlite] Atomic database structure initialization

> > > Paul wrote: > > > I can check whether user_version matches magic number without transaction. > > > > No. Executing "PRAGMA user_version" will start an automatic transaction > > if you didn't already start an explicit one. > > > > > Only when user_version does not match magic number I star

Re: [sqlite] Atomic database structure initialization

On 2014/09/22 15:03, Paul wrote: I suspect that no, not all accesses to the database file are done using transactions. What about read-only databases? Moreover, what about read-only medium? A transaction does not necessarily imply a write, only if there is an update of actual data, which dep

Re: [sqlite] Atomic database structure initialization

Paul wrote: Please note that *all* accesses to the database file are done with transactions, including reading and writing the user_version value. > > I suspect that no, not all accesses to the database file are done using > transactions. Read-only transactions just lock the database fi

Re: [sqlite] Atomic database structure initialization

>> Correct me if I have a wrong model of transaction in mind. >> Maybe sqlite does not write a byte to disk if inside a transaction >> there are only selects? >So, the answer to my question is: NO. >SQLite does no writes, the begin of a transaction is simply an >acquisition of write lock. "BEGIN

[sqlite] Expose struct Mem and struct Vdbe to other application

Hi, Let's assume I am writing a c code which directly invokes the sqlite_step statement. After the execution of the statement, I would like to access the pResultRow of Vdbe (which obtained by pVbe = (Vdbe*) pStmt ). How can I expose the struct Vdbe,Mem and the likes to external applications. Incl

Re: [sqlite] Atomic database structure initialization

Thank you for help, guys! I knew that sqlite is a great piece of software, now I have even more proofs :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Expose struct Mem and struct Vdbe to other application

Use the sqlite3_column_ functions to return result fields... Or you need to use the non-amalgamation sources and integrate them into your build environment. Such use is probably strongly discouraged by SQLite developers, as the internal structures are subject to change without notice. Also, a g

Re: [sqlite] 50% faster than 3.7.17

Hi, This 3.8.7alpha release seems to bring about 5% win from 3.8.6 , on my particular SQL test case. Question : "PRAGMA threads=2" didn't bring any speed-up on my "2 true" cores machine. Did I miss a compilation option ? ___ sqlite-users mailing list

Re: [sqlite] 50% faster than 3.7.17

On Mon, Sep 22, 2014 at 1:43 PM, big stone wrote: > Hi, > > This 3.8.7alpha release seems to bring about 5% win from 3.8.6 , on my > particular SQL test case. > > Question : "PRAGMA threads=2" didn't bring any speed-up on my "2 true" > cores machine. > > Did I miss a compilation option ? > The

Re: [sqlite] 50% faster than 3.7.17

On Mon, Sep 22, 2014 at 8:29 AM, Valentin Davydov wrote: > On Fri, Sep 19, 2014 at 09:14:17PM -0400, Richard Hipp wrote: > > > The latest SQLite 3.8.7 alpha version (available on the download page > > http://www.sqlite.org/download.html) is 50% faster than the 3.7.17 > release > > from 16 months

Re: [sqlite] 50% faster than 3.7.17

ok, Nearly all the time is spent in a big 'CTE' select. So maybe this sort of ugly CTE query is not threadable. with f0_k as (SELECT f.rowid as nof2, f.*, p.Dn, p.g1, p.g2, p.w, p.other FROM F0 AS f, Pt AS p WHERE f.Io =p.Io) ,F0_mcalc as (SELECT f.*, p.*, (case when Priority=999 then Cg e

[sqlite] sqlite max arguments assistance

Hello All, select * from august where transaction_amount = (select max(transaction_amount) from august) This statement should show be the merchant account with the top most expensive transaction from my table called august. Result: $999.63 (I trimmed out other items that I can't show). Same res

Re: [sqlite] sqlite max arguments assistance

On 22 Sep 2014, at 8:12pm, Jungle Boogie wrote: > Result: > $999.63 > (I trimmed out other items that I can't show). > > Same results with this: select max(transaction_amount) from august > $999.63 > > > But this is NOT the most expensive amount, but it is for a three digit dollar > amount. >

Re: [sqlite] sqlite max arguments assistance

On 9/22/2014 3:12 PM, Jungle Boogie wrote: select * from august where transaction_amount = (select max(transaction_amount) from august) This statement should show be the merchant account with the top most expensive transaction from my table called august. Result: $999.63 The fact that the res

Re: [sqlite] sqlite max arguments assistance

I feel sure the transaction amounts are strings, not numbers. Here is a quick example: create temp table gigo(a real) insert into gigo values ('$5.00') select a, typeof(a) from gigo gives: $5.00 text If you can remove the dollar signs in the CSV file you should do better. Hope this helps,

Re: [sqlite] sqlite max arguments assistance

Dear Simon, From: Simon Slavin Sent: Mon, 22 Sep 2014 20:14:08 +0100 To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite max arguments assistance > > > On 22 Sep 2014, at 8:12pm, Jungle Boogie wrote: > >> Result: >> $999.63 >> (I

Re: [sqlite] sqlite max arguments assistance

Dear Igor, From: Igor Tandetnik Sent: Mon, 22 Sep 2014 15:25:43 -0400 To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite max arguments assistance > > On 9/22/2014 3:12 PM, Jungle Boogie wrote: >> select * from august where transaction_amount = (s

Re: [sqlite] sqlite max arguments assistance

On 9/22/2014 3:42 PM, Jungle Boogie wrote: From: Igor Tandetnik The fact that the result is printed complete with $ sign suggests strongly that the values are stored, and compared, as strings. '$999.63' > '$16695.36' when using alphabetical comparison. This is my mistake--ignore the dollar si

Re: [sqlite] sqlite max arguments assistance

Hi Igor, On 22 September 2014 12:52, Igor Tandetnik wrote: > > > Dollar sign or not, the outcome you observe suggests that the values are > stored as strings. What does this query return? > > select typeof(transaction_amount), count(*) from august group by 1; > > My guess is that most, if not all,

Re: [sqlite] sqlite max arguments assistance

On 9/22/2014 4:08 PM, jungle Boogie wrote: Hi Igor, On 22 September 2014 12:52, Igor Tandetnik wrote: Dollar sign or not, the outcome you observe suggests that the values are stored as strings. What does this query return? select typeof(transaction_amount), count(*) from august group by 1;

Re: [sqlite] sqlite max arguments assistance

Dear Igor, From: Igor Tandetnik Sent: Mon, 22 Sep 2014 16:34:18 -0400 To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite max arguments assistance > > > Just as I thought. You are storing your values as text - not as numbers - and > comparing the

Re: [sqlite] sqlite max arguments assistance

On 22 Sep 2014, at 10:02pm, Jungle Boogie wrote: > Is there anything I can do post export from the other database to change the > values correctly? You need to strip the dollar signs off at some stage. Ideally you can do it in the CSV file before you import that into SQLite. Alternatively you

Re: [sqlite] sqlite max arguments assistance

Dear Simon, From: Simon Slavin Sent: Mon, 22 Sep 2014 22:22:00 +0100 To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite max arguments assistance > > > On 22 Sep 2014, at 10:02pm, Jungle Boogie wrote: > >> Is there anything I can

Re: [sqlite] sqlite max arguments assistance

Dear Igor, From: Igor Tandetnik Sent: Mon, 22 Sep 2014 16:34:18 -0400 To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite max arguments assistance > > > Just as I thought. You are storing your values as text - not as numbers - and > comparing the

Re: [sqlite] sqlite max arguments assistance

On Mon, 22 Sep 2014 14:02:57 -0700, Jungle Boogie wrote: > Igor Tandetnik > wrote Mon, 22 Sep 2014 16:34:18 -0400 >> >> Just as I thought. You are storing your values as text - not as numbers - and >> comparing them accordingly, in alphabetical order. > > Sorry, I'm not certain I know the answe

Re: [sqlite] sqlite max arguments assistance

On 22 Sep 2014, at 10:25pm, Jungle Boogie wrote: > Actually, none of the fields have the dollar sign, that's my mistake. Can I > tell sqlite pre or post import of the csv that the field is number or will it > always take it as it? See the section on CSV Import in

Re: [sqlite] sqlite max arguments assistance

Dear Kees, From: Kees Nuyt Sent: Mon, 22 Sep 2014 23:59:52 +0200 To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite max arguments assistance > > and/or the receiving table doesn't define column > transaction_amount as a numeric type (REAL, NUMBER

Re: [sqlite] sqlite max arguments assistance

> On 23 Sep 2014, at 12:24am, Jungle Boogie wrote: > > I did this: > sqlite> create table august > (MERCHANT_ID,DBA,WHITELABEL_ID,ORDER_ID,TRANSACTION_DISPLAY_DATE,TYPE,STATE,TRANSACTION_AMOUNT); > sqlite> .separator "," > sqlite> .import portalUseMonthly_20140901.csv august > > doing: > sqlite

Re: [sqlite] sqlite max arguments assistance

Dear Simon, From: Simon Slavin Sent: Tue, 23 Sep 2014 00:29:32 +0100 To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite max arguments assistance > > >> On 23 Sep 2014, at 12:24am, Jungle Boogie wrote: >> >> I did this: >> sqlite>

Re: [sqlite] sqlite max arguments assistance

Did you declare the field as containing numeric data, or a real? '$999.63' > '$16695.36' is true (with or without the $sign) 999.63 > 16695.36 is false looks like you are sorting text, not numbers. >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun..

Re: [sqlite] sqlite max arguments assistance

if there are no dollar signs, comma's, or other things that do not belong in numbers, then UPDATE august SET transaction_amount = cast(transaction_amount, real) >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Jungle Boo

Re: [sqlite] sqlite max arguments assistance

"Jungle Boogie" wrote... Dear Igor, From: Igor Tandetnik Sent: Mon, 22 Sep 2014 16:34:18 -0400 To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite max arguments assistance Just as I thought. You are storing your values as text - not as numb

Re: [sqlite] Expose struct Mem and struct Vdbe to other application

Thanks a lot for your reply Hick. I'm trying to split the results of joins. I have one struct per table and if I am joining 3 tables, I would like to fill the objects of the respective structs with the values from the ResultSet in Vdbe pResultSet and I want to do it before the callback(interface fo

Re: [sqlite] Expose struct Mem and struct Vdbe to other application

IMHO you are going down a dark and dangerous passage. If your approach really does require severe hacking of SQLite internals then maybe that is an indication that you really need to change the approach or acquire a different tool. Maybe you are trying to nail it with a set of pliers. Doable, bu

Re: [sqlite] 50% faster than 3.7.17

Are any of these improvements specifically in the area of the online backup API, or are they more in the general running of SQLite? On 20 September 2014 11:14, Richard Hipp wrote: > The latest SQLite 3.8.7 alpha version (available on the download page > http://www.sqlite.org/download.html) is 50