Re: [sqlite] 50% faster than 3.7.17

[Donald Shepherd]

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% 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 CPU cycles.
>
> This performance gain is over and above the query planner improvements that
> have also been made.  We are constantly looking for new ways to run queries
> and adding those ways into the query planner.  For example, in the previous
> release, we added a new way to evaluate IN operators with non-constant
> right-hand-sides that was reported on this mailing list to make some
> queries run 5 times faster.
>
> The 50% faster number above is not about better query plans.  This is 50%
> faster at the low-level grunt work of moving bits on and off disk and
> search b-trees.  We have achieved this by incorporating hundreds of
> micro-optimizations.  Each micro-optimization might improve the performance
> by as little as 0.05%.  If we get one that improves performance by 0.25%,
> that is considered a huge win.  Each of these optimizations is unmeasurable
> on a real-world system (we have to use cachegrind to get repeatable
> run-times) but if you do enough of them, they add up.
>
> A full 10% of the performance gain has come since the previous release.
> There have been a lot of changes.  All our tests pass, and we still have
> 100% branch test coverage, so we are confident that we didn't break too
> much.  But your testing is an important part of our quality process.
> Please download a source archive or a DLL and give the latest alpha a
> whirl, and let us know if you encounter any problems.
>
> P.S.:  Measurements were done using the "speedtest1 --size 5" workload on
> Ubuntu 10.13 and gcc 4.8.1 with -Os.  YMMV.  Version 3.7.17 requires
> 1432835574 CPU cycles and the 3.8.7 alpha requires just 953861485 CPU
> cycles, as measured by cachegrind.
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Expose struct Mem and struct Vdbe to other application

[Hick Gunter]

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, but not as 
neat as using a hammer in the first place, and also hard on the pliers.

-Ursprüngliche Nachricht-
Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
Gesendet: Dienstag, 23. September 2014 06:29
An: General Discussion of SQLite Database
Betreff: 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 for the external
applications) is called
Is there are a way , other than using the sqlite_column_ functions, since I 
have to make one function call per column, it is very costly.
Can I not expose the Vdbe and Mem structs by placing it in sqlite3.h file like 
struct sqlite3,which is exposed that way ?

Thanks
Prakash

On Mon, Sep 22, 2014 at 8:38 PM, Hick Gunter  wrote:

> 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 given VDBE Program implements EXACTLY the SQL
> query it was prepared with and works only with EXACTLY the schema it
> was prepared against.
>
> Are you trying to implement "stored procedures"?
>
> -Ursprüngliche Nachricht-
> Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
> Gesendet: Montag, 22. September 2014 15:37
> An: General Discussion of SQLite Database
> Betreff: [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.
> Including them in the header file gave me the error "incomplete
> defintion of struct"
>
> Can you kindly help me?
>
> Thanks
> Prakash
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use
> of the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you
> have received this communication in error, please immediately notify
> the sender by return e-mail message and delete all copies of the
> original communication. Thank you for your cooperation.
>
>
> ___
> 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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
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

[Prakash Premkumar]

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 for the external
applications) is called
Is there are a way , other than using the sqlite_column_ functions, since I
have to make one function call per column, it is very costly.
Can I not expose the Vdbe and Mem structs by placing it in sqlite3.h file
like struct sqlite3,which is exposed that way ?

Thanks
Prakash

On Mon, Sep 22, 2014 at 8:38 PM, Hick Gunter  wrote:

> 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 given VDBE Program implements EXACTLY the SQL query it was
> prepared with and works only with EXACTLY the schema it was prepared
> against.
>
> Are you trying to implement "stored procedures"?
>
> -Ursprüngliche Nachricht-
> Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
> Gesendet: Montag, 22. September 2014 15:37
> An: General Discussion of SQLite Database
> Betreff: [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.
> Including them in the header file gave me the error "incomplete defintion
> of struct"
>
> Can you kindly help me?
>
> Thanks
> Prakash
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> 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] sqlite max arguments assistance

[jose isaias cabrera]

"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 numbers - 
and

comparing them accordingly, in alphabetical order.


Sorry, I'm not certain I know the answer to this as I don't generate the 
data.

Opening the csv file in Excel, I did have to change the transaction_amount
column from "general" to "number". I think your assumptions are correct,
though. I'll see if I can look at the values in the other database.

I know the data is generated from a different database and a file is 
created,

portalusemonthly.csv that's sent to a location where I can get it?

Is there anything I can do post export from the other database to change 
the

values correctly?


you can open it back in Excel and do a search replace for $ for "" to delete 
the $ in that column or through out the excel.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Keith Medcalf]

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 Boogie
>Sent: Monday, 22 September, 2014 15:03
>To: sqlite-users@sqlite.org
>Subject: 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 them accordingly, in alphabetical order.
>
>Sorry, I'm not certain I know the answer to this as I don't generate the
>data.
>Opening the csv file in Excel, I did have to change the
>transaction_amount
>column from "general" to "number". I think your assumptions are correct,
>though. I'll see if I can look at the values in the other database.
>
>I know the data is generated from a different database and a file is
>created,
>portalusemonthly.csv that's sent to a location where I can get it?
>
>Is there anything I can do post export from the other database to change
>the
>values correctly?
>
>--
>inum: 883510009027723
>sip: jungleboo...@sip2sip.info
>xmpp: jungle-boo...@jit.si
>___
>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] sqlite max arguments assistance

[Keith Medcalf]

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...@sqlite.org] On Behalf Of Jungle Boogie
>Sent: Monday, 22 September, 2014 13:12
>To: sqlite-users@sqlite.org
>Subject: [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 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.
>
>
>For example, this record is much higher in terms of transaction_amount:
>$16695.36
>
>This is a csv file that I've imported and I'm using SQLiteSpy with sqlite
>3.8.6 as well as
>FreeBSD lyander-fbsd 10.0-RELEASE-p9 FreeBSD 10.0-RELEASE-p9 #0: Mon Sep
>15
>14:32:29 UTC 2014
>r...@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC  i386
>also with sqlite3.8.6
>
>
>How am i misunderstanding max?
>--
>inum: 883510009027723
>sip: jungleboo...@sip2sip.info
>xmpp: jungle-boo...@jit.si
>___
>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] sqlite max arguments assistance

[Jungle Boogie]

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> 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> .mode csv
>> sqlite> .import C:/work/somedata.csv tab
>> (with correct values)
>>
>> Results in .schema assuming everything as text
> 
> because you defined them as text yourself.  You need to put your column 
> affinities in the CREATE command.  Delete your existing table and try 
> something more like this:
> 
>> create table august
>> (MERCHANT_ID INTEGER, DBA TEXT, WHITELABEL_ID INTEGER, ORDER_ID INTEGER, 
>> TRANSACTION_DISPLAY_DATE TEXT, TYPE TEXT ,STATE TEXT, TRANSACTION_AMOUNT 
>> REAL);
> 

Works perfectly!
sqlite> select max(transaction_amount) from august;
66882.4

I'll have some reading and playing around!

Thanks so much for everyone's assistance on this armature problem of mine.

> Simon.
-- 
inum: 883510009027723
sip: jungleboo...@sip2sip.info
xmpp: jungle-boo...@jit.si
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Simon Slavin]

> 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> .mode csv
> sqlite> .import C:/work/somedata.csv tab
> (with correct values)
> 
> Results in .schema assuming everything as text

because you defined them as text yourself.  You need to put your column 
affinities in the CREATE command.  Delete your existing table and try something 
more like this:

> create table august
> (MERCHANT_ID INTEGER, DBA TEXT, WHITELABEL_ID INTEGER, ORDER_ID INTEGER, 
> TRANSACTION_DISPLAY_DATE TEXT, TYPE TEXT ,STATE TEXT, TRANSACTION_AMOUNT 
> REAL);

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Jungle Boogie]

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, INTEGER and
> the like).
> By the way, lacking a currency or decimal type, the best way to
> represent money amounts is INTEGER, expressed as cents.
> 
>> Is there anything I can do post export from the other database to change the
>> values correctly?
> 
> Have a look at 
> http://sqlite.org/datatype3.html
> "2.3 Column Affinity Behavior Example"
> 

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> .mode csv
sqlite> .import C:/work/somedata.csv tab
(with correct values)

Results in .schema assuming everything as text

So I'll just need to make transaction_amount number

 
-- 
inum: 883510009027723
sip: jungleboo...@sip2sip.info
xmpp: jungle-boo...@jit.si
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Simon Slavin]

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



If the table already exists (with that column defined as REAL) when you import, 
it will use the existing column definitions.

If you cannot change the file you are importing then you will get a row with 
the column names in, which you can, of course, ignore.  But it would probably 
be better to delete the first line if you can.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Kees Nuyt]

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 answer to this as I don't generate the data.
> Opening the csv file in Excel, I did have to change the transaction_amount
> column from "general" to "number". I think your assumptions are correct,
> though. I'll see if I can look at the values in the other database.
> 
> I know the data is generated from a different database and a file is created,
> portalusemonthly.csv that's sent to a location where I can get it?

portalusemonthly.csv probably lists the amounts as 
.,"999.63",
.,"16695.36",

and/or the receiving table doesn't define column
transaction_amount as a numeric type (REAL, NUMBER, INTEGER and
the like).
By the way, lacking a currency or decimal type, the best way to
represent money amounts is INTEGER, expressed as cents.

> Is there anything I can do post export from the other database to change the
> values correctly?

Have a look at 
http://sqlite.org/datatype3.html
"2.3 Column Affinity Behavior Example"

-- 
Groet,

Kees Nuyt

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Jungle Boogie]

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 them accordingly, in alphabetical order.

Looking at our non-production database, the transaction_amount is stored as
numbers: NUMBER(19,4)

 
-- 
inum: 883510009027723
sip: jungleboo...@sip2sip.info
xmpp: jungle-boo...@jit.si
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Jungle Boogie]

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 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 may be able to do the import into SQLite then issue the 
> following command:
> 
> UPDATE august SET transaction_amount = REPLACE(transaction_amount,'$','')
> 
> before you do your SELECT.
> 

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?

> Simon.
-- 
inum: 883510009027723
sip: jungleboo...@sip2sip.info
xmpp: jungle-boo...@jit.si
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Simon Slavin]

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 may be able to do the import into SQLite then issue the 
following command:

UPDATE august SET transaction_amount = REPLACE(transaction_amount,'$','')

before you do your SELECT.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Jungle Boogie]

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 them accordingly, in alphabetical order.

Sorry, I'm not certain I know the answer to this as I don't generate the data.
Opening the csv file in Excel, I did have to change the transaction_amount
column from "general" to "number". I think your assumptions are correct,
though. I'll see if I can look at the values in the other database.
 
I know the data is generated from a different database and a file is created,
portalusemonthly.csv that's sent to a location where I can get it?

Is there anything I can do post export from the other database to change the
values correctly?

-- 
inum: 883510009027723
sip: jungleboo...@sip2sip.info
xmpp: jungle-boo...@jit.si
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Igor Tandetnik]

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;

My guess is that most, if not all, rows would report
typeof(transaction_amount) as 'text'.



typeof(transaction_amount)count(*)
text 135388


Just as I thought. You are storing your values as text - not as numbers 
- and comparing them accordingly, in alphabetical order.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[jungle Boogie]

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, rows would report
> typeof(transaction_amount) as 'text'.
>

typeof(transaction_amount)count(*)
text 135388


> --
> Igor Tandetnik
>


-- 
---
inum: 883510009027723
sip: jungleboo...@sip2sip.info
xmpp: jungle-boo...@jit.si
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Igor Tandetnik]

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 sign. I should have noted this
originally. Forgive me!


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, rows would report 
typeof(transaction_amount) as 'text'.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Jungle Boogie]

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 = (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 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 sign. I should have noted this
originally. Forgive me!

 
-- 
inum: 883510009027723
sip: jungleboo...@sip2sip.info
xmpp: jungle-boo...@jit.si
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Jungle Boogie]

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 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.
>>
>>
>> For example, this record is much higher in terms of transaction_amount:
>> $16695.36
> 
> What is the affinity of the transaction_amount column of your august table ?

Sorry, I don't know what this means. It looks like 50 grand is the largest
transaction amount, although Excel is having a hard time sorting the data well.


> 
> Simon.
-- 
inum: 883510009027723
sip: jungleboo...@sip2sip.info
xmpp: jungle-boo...@jit.si
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Gerry Snyder]

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,

Gerry

On 9/22/2014 12:12 PM, Jungle Boogie wrote:

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


For example, this record is much higher in terms of transaction_amount:
$16695.36

This is a csv file that I've imported and I'm using SQLiteSpy with sqlite
3.8.6 as well as
FreeBSD lyander-fbsd 10.0-RELEASE-p9 FreeBSD 10.0-RELEASE-p9 #0: Mon Sep 15
14:32:29 UTC 2014
r...@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC  i386
also with sqlite3.8.6


How am i misunderstanding max?


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Igor Tandetnik]

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

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite max arguments assistance

[Simon Slavin]

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.
> 
> 
> For example, this record is much higher in terms of transaction_amount:
> $16695.36

What is the affinity of the transaction_amount column of your august table ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite max arguments assistance

[Jungle Boogie]

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


For example, this record is much higher in terms of transaction_amount:
$16695.36

This is a csv file that I've imported and I'm using SQLiteSpy with sqlite
3.8.6 as well as
FreeBSD lyander-fbsd 10.0-RELEASE-p9 FreeBSD 10.0-RELEASE-p9 #0: Mon Sep 15
14:32:29 UTC 2014
r...@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC  i386
also with sqlite3.8.6


How am i misunderstanding max?
-- 
inum: 883510009027723
sip: jungleboo...@sip2sip.info
xmpp: jungle-boo...@jit.si
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 50% faster than 3.7.17

[big stone]

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 else Source end) AS
Sourcef
   FROM F0_k AS f, Sor AS p WHERE  p.omg  in (f.Cg, '') And
   f.Slic Between Be And En
   And p.PtGroup In (f.Io,f.g1,f.g2,f.w,f.other,'PL','SO','ST','Not_found')
)
,F0_mcalcmin as
  (SELECT nof2, min(priority) AS minp FROM F0_mcalc GROUP BY nof2)
,F0_mcalc_final as
 (SELECT f.*, round(qty*coefficient,3) AS qtyf FROM  F0_mcalcmin
  AS fm inner join  F0_mcalc AS f on f.nof2=fm.nof2 and
f.priority=fm.minp)
 select *   from F0_mcalc_final ;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 50% faster than 3.7.17

[Richard Hipp]

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 ago.  That is to say, it does 50% more work using the same
> > number of CPU cycles.
>
> Is there any similar benchmarks with regard to disk i/o operations rather
> than CPU? Especially random read of cache misses, I mean.
>

No, there are no such benchmarks.  On the other hand, we've been focused on
minimizing I/O in SQLite for the past decade.  I think we have extracted
about as much as we can fro m that vein.  But if you have any new ideas on
how we can further reduce the I/O, we'd love to hear from you.

-- 
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] 50% faster than 3.7.17

[Richard Hipp]

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 multi-thread sort should be on by default.  Probably you are just not
doing a big enough sort to make it worthwhile to start any new threads.
The "threads=2" pragma sets an upper limit on the number of threads.  There
is no guarantee that SQLite will use that many.

-- 
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] 50% faster than 3.7.17

[big stone]

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

[Hick Gunter]

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 given VDBE Program implements EXACTLY the SQL query it was prepared 
with and works only with EXACTLY the schema it was prepared against.

Are you trying to implement "stored procedures"?

-Ursprüngliche Nachricht-
Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
Gesendet: Montag, 22. September 2014 15:37
An: General Discussion of SQLite Database
Betreff: [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.
Including them in the header file gave me the error "incomplete defintion of 
struct"

Can you kindly help me?

Thanks
Prakash
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

[Paul]

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

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

[Prakash Premkumar]

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.
Including them in the header file gave me the error "incomplete defintion
of struct"

Can you kindly help me?

Thanks
Prakash
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

[Keith Medcalf]

>> 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" acquires a read lock.  If an attempt is made to write, then it is 
escalated to a write lock.  "BEGIN IMMEDIATE" acquires a write lock from the 
outset and other attempts to obtain a lock will be blocked until the write lock 
is released (except in WAL mode, in which case a WRITE lock does not block 
subsequent attempts to READ, which see the database as it existed prior to the 
WRITE lock being obtained).




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

[Clemens Ladisch]

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 file.  (But they must take
the lock to ensure that no write transaction can happen at the same time.)

> Maybe sqlite does not write a byte to disk if inside a transaction
> there are only selects?

A file lock does not involve any writes (except for the unix-dotfile VFS,
but you would know if you were using it).  A rollback journal (or write-
ahead log) is written only for transactions that make changes.

Read  to see how this works.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

[RSmith]

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 depending on the transaction 
model you've chosen may or may not first produce another alternate file recording the transaction before committing it. SQLite is 
quite useful in this regard as a LOT of use cases involve embedded applications using read-only or static memory and SSD or other 
write-averse storage kinds.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

[Paul]

> 
> > 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 be faster than just starting a transaction explicitly
> > and checking the version once.
> > 
> > > 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.
> > 
> > Please have a look at this sentence, which you quoted in your mail:
> > >> 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.
> What about read-only databases? Moreover, what about read-only medium?
> 
> I am actually worried not of transaction per se but of its impact on HDD.
> My databases must be opened as fast as possilbe. 
> And in 95% of cases all executed queries are SELECTs.
> I don't want sqlite to write anything to disk when, within single database
> session, data is only extracted and no modifications to database are made.
> 
> 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.


Thank you all for help!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

[Paul]

> 
> 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
> Enter ".help" for usage hints.
> sqlite> pragma user_version;
> 0
> sqlite> pragma user_version=1;
> sqlite> pragma user_version;
> 1
> sqlite> BEGIN;
> sqlite> pragma user_version=2;
> sqlite> pragma user_version;
> 2
> sqlite> END;
> sqlite> pragma user_version;
> 2
> sqlite> BEGIN;
> sqlite> pragma user_version=3;
> sqlite> pragma user_version;
> 3
> sqlite> ROLLBACK;
> sqlite> pragma user_version;
> 2
> sqlite> 
> 
> It would appear that that PRAGMA is transaction-compliant and 
> transaction-safe.
> 
> Simon.

Thanks, Simon.

My head is too slow today. 
I don't know why I din't came up with such a simple idea to experiment with CLI 
:/

I can also use strace to see if sqlite does any writes to file system 
if I create transaction but do only selects.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

[Paul]

> 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 be faster than just starting a transaction explicitly
> and checking the version once.
> 
> > 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.
> 
> Please have a look at this sentence, which you quoted in your mail:
> >> 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.
What about read-only databases? Moreover, what about read-only medium?

I am actually worried not of transaction per se but of its impact on HDD.
My databases must be opened as fast as possilbe. 
And in 95% of cases all executed queries are SELECTs.
I don't want sqlite to write anything to disk when, within single database
session, data is only extracted and no modifications to database are made.

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?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

[Simon Slavin]

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
Enter ".help" for usage hints.
sqlite>  pragma user_version;
0
sqlite>  pragma user_version=1;
sqlite>  pragma user_version;
1
sqlite> BEGIN;
sqlite>  pragma user_version=2;
sqlite>  pragma user_version;
2
sqlite> END;
sqlite>  pragma user_version;
2
sqlite> BEGIN;
sqlite>  pragma user_version=3;
sqlite>  pragma user_version;
3
sqlite> ROLLBACK;
sqlite>  pragma user_version;
2
sqlite> 

It would appear that that PRAGMA is transaction-compliant and  transaction-safe.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

[Clemens Ladisch]

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 be faster than just starting a transaction explicitly
and checking the version once.

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

Please have a look at this sentence, which you quoted in your mail:
>> Please note that *all* accesses to the database file are done with
>> transactions, including reading and writing the user_version value.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 50% faster than 3.7.17

[Valentin Davydov]

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 CPU cycles.

Is there any similar benchmarks with regard to disk i/o operations rather 
than CPU? Especially random read of cache misses, I mean.

Valentin Davydov.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

[Paul]

> 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 is 0), that 
> >> another
> >> process is updating the database schema (change it to -1), or that it is at
> >> some internal revision number known to your program (it has a value > 0) 
> >> such
> >> that you do not need to change the schema or initialize it.
> >
> > This is actually very helpful!
> > This soultion avoids unnecessary transaction after database open.
> > I can use *double checked locking* and start transaction only if necessary.
> 
> Please note that *all* accesses to the database file are done with
> transactions, including reading and writing the user_version value.
> This PRAGMA makes it slightly easier to check the database, but you
> cannot avoid transaction this way (or with any other way).
> 
> (If the process correctly updates the database within a transaction,
> that value -1 would never be seen by another process.)
> 


***Sorry, my previous message was a disaster 

Of course, that is why I mentioned *double checked locking*. 
I can check whether user_version matches magic number without transaction. 
Only when user_version does not match magic number I start transaction. 
Then I check again, inside exclusive transaction, then perform database 
schema initialization, if user_version is still not a magic number. 
Finally assing user_version that magic number and commit transaction. 

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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

[Paul]

> 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 is 0), that another > >> process is updating the database 
> schema (change it to -1), or that it is at > >> some internal revision number 
> known to your program (it has a value > 0) such > >> that you do not need to 
> change the schema or initialize it. > > > > This is actually very helpful! > 
> > This soultion avoids unnecessary transaction after database open. > > I can 
> use *double checked locking* and start transaction only if necessary. > > 
> Please note that *all* accesses to the database file are done with > 
> transactions, including reading and writing the user_version value. > This 
> PRAGMA makes it slightly easier to check the database, but you > cannot avoid 
> transaction this way
  (or with any other way). > > (If the process correctly updates the database 
within a transaction, > that value -1 would never be seen by another process.) 
Of course, that is why I mentioned *double checked locking*. I can check 
whether user_version matches magic number without transaction. Only when 
user_version does not match magic number I creat transaction. Then I check 
again, inside exclusive transaction, then perform database schema 
initialization, if user_version is still not a magic number. Finally assing 
user_version that magic number and commit transaction. 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version info in doc pages

[Stephen Chrzanowski]

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
important to the syntax I need to know "now", and even at a cursory glance,
the date it was added isn't relevant to what I need to know, and albeit for
a single download, a few hundred bytes of additional HTML code wouldn't be
a lot of info, but, on a free site with no adverts for public domain
software, I'll gladly do a bit of extra leg work to find when something was
added, if I need to know at all.  What other things should be added to the
document?  When syntax is changed?  Forget about the syntax itself, is the
date information added when a parameter is added, removed, modified?  If
this kind of information were to be added, what determines what is flagged
to have a 'date added' report?

As for HGTTG, the linked doc isn't exactly burried under 30 levels concrete
floor in some building on some planet in a galaxy far, far away. ;)

On Mon, Sep 22, 2014 at 7:40 AM, HarryD  wrote:

> 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
> comment on, for many years in a place lightyears away, but known to but a
> select few.
>
> On Sun, Sep 21, 2014 at 3:26 PM, Kevin Benson 
> wrote:
>
> > http://www.sqlite.org/changes.html
> >
> > 2014-02-03 (3.8.3)
> > •Added support for common table expressions and the WITH clause.
> >
> >
> > --
> >--
> >   --
> >  --Ô¿Ô--
> > K e V i N
> >
> > On Sun, Sep 21, 2014 at 8:46 AM, HarryD  wrote:
> >
> > > I find it something of a shortcoming that the doc pages (
> > > http://www.sqlite.org/lang_with.html) do not mention the applicable
> > > version
> > > of sqlite.
> > >
> > > Example: I read about the 'with clause'.  Exciting!  But when trying
> it,
> > it
> > > didn't work because I am using an older version. Not that old but still
> > old
> > > enough.
> > >
> > > Nowhere on the doc page is any mention of the first version it appeared
> > > in.  That would have been really helpful, and I think it is critical
> that
> > > it should appear there!
> > > ___
> > > 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version info in doc pages

[HarryD]

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
comment on, for many years in a place lightyears away, but known to but a
select few.

On Sun, Sep 21, 2014 at 3:26 PM, Kevin Benson 
wrote:

> http://www.sqlite.org/changes.html
>
> 2014-02-03 (3.8.3)
> •Added support for common table expressions and the WITH clause.
>
>
> --
>--
>   --
>  --Ô¿Ô--
> K e V i N
>
> On Sun, Sep 21, 2014 at 8:46 AM, HarryD  wrote:
>
> > I find it something of a shortcoming that the doc pages (
> > http://www.sqlite.org/lang_with.html) do not mention the applicable
> > version
> > of sqlite.
> >
> > Example: I read about the 'with clause'.  Exciting!  But when trying it,
> it
> > didn't work because I am using an older version. Not that old but still
> old
> > enough.
> >
> > Nowhere on the doc page is any mention of the first version it appeared
> > in.  That would have been really helpful, and I think it is critical that
> > it should appear there!
> > ___
> > 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] Definition of struct sqlite3_stmt

[Prakash Premkumar]

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 virtual machine.  This structure contains the
> complete
>   ** state of the virtual machine.
>   **
>   ** The "sqlite3_stmt" structure pointer that is returned by
> sqlite3_prepare()
>   ** is really a pointer to an instance of this structure.
>   ...
>   */
>   struct Vdbe {
>
>
> Regards,
> Clemens
> ___
> 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] Definition of struct sqlite3_stmt

[Clemens Ladisch]

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.
  **
  ** The "sqlite3_stmt" structure pointer that is returned by sqlite3_prepare()
  ** is really a pointer to an instance of this structure.
  ...
  */
  struct Vdbe {


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

[Clemens Ladisch]

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 is 0), that 
>> another
>> process is updating the database schema (change it to -1), or that it is at
>> some internal revision number known to your program (it has a value > 0) such
>> that you do not need to change the schema or initialize it.
>
> This is actually very helpful!
> This soultion avoids unnecessary transaction after database open.
> I can use *double checked locking* and start transaction only if necessary.

Please note that *all* accesses to the database file are done with
transactions, including reading and writing the user_version value.
This PRAGMA makes it slightly easier to check the database, but you
cannot avoid transaction this way (or with any other way).

(If the process correctly updates the database within a transaction,
that value -1 would never be seen by another process.)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Atomic database structure initialization

[Paul]

> 
> 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 is created empty, the value is zero.
> 
> 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 is 0), that 
> another process is updating the database schema (change it to -1), or that it 
> is at some internal revision number known to your program (it has a value > 
> 0) such that you do not need to change the schema or initialize it.
> 

This is actually very helpful! 
This soultion avoids unnecessary transaction after database open.
I can use *double checked locking* and start transaction only if necessary.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users