[sqlite] BEGINNER - Transactions in shell script

[R.Smith]

On 2015-09-05 10:18 PM, Petr L?z?ovsk? wrote:
> Have some shell scripts working with sqlite. Receiving incoming payments from 
> bank via HTTP API and pushing it into database. This script will start 
> periodically, every single hour.
>
> Want to prevent situation only few payments are written and script failed for 
> some reason. Have read about sqlite transactions and understood so I should 
> start every writing sequence with BEGIN; statement, than made all inserts and 
> than COMMIT; statement. Is this all how it is working? Should I do something 
> more to prevent such unwanted situation?

It is indeed all you need. If the transaction fails an error will write 
to stdout (assuming you use the SQlite3.exe or other CLI) - it might be 
prudent in your scripts to watch for errors and call ROLLBACK (as 
opposed to COMMIT). Note that not all errors need to roll-back, You can 
specify in the schema the error handling, and some within the SQL 
statements. On the other hand you might wish to roll back in special 
cases even on non-error circumstances.

See:
http://www.sqlite.org/syntax/conflict-clause.html

in conjunction with:
http://www.sqlite.org/lang_createtable.html
and other constraint specifications.


Cheers,
Ryan

[sqlite] Third test of json and index expressions, now it works

[R.Smith]

On 2015-09-05 08:58 PM, Domingo Alvarez Duarte wrote:
> Hello !
>
> Again your proposition doesn't stand up !

Again?
That was my first post on the subject and it was in reply to Darko whom 
asked for a specific reason, so I supplied one. (Which by the way isn't 
to say I am 100% convinced by the reason either, but no less, it's a 
valid reason).

> There is no "waste of cpu cycles" for work that is not done, I mean for the
> ones that write queries for the machine instead to the humans, they will
> continue to have the same results (less parsing steps, besides the parsing
> normally accounts to something like 0.01% to 0.0001% or less of the time
> spent on the query).

If using aliases as valid where/group clause substitutes involved a mere 
extra line of parsing code, then your assertion might hold water - but I 
posit that believing that is akin to believing the Earth is flat.


Cheers,
Ryan

[sqlite] BEGINNER - Transactions in shell script

[Petr Lázňovský]

Have some shell scripts working with sqlite. Receiving incoming payments from 
bank via HTTP API and pushing it into database. This script will start 
periodically, every single hour. 

Want to prevent situation only few payments are written and script failed for 
some reason. Have read about sqlite transactions and understood so I should 
start every writing sequence with BEGIN; statement, than made all inserts and 
than COMMIT; statement. Is this all how it is working? Should I do something 
more to prevent such unwanted situation?

thanks, L. 

[sqlite] MemoryBarrier compile error on WIndows MinGW gcc 4.8.1

[Keith Medcalf]

Trunk does not compile with MinGW [gcc version 4.8.1 (GCC)] on Windows.

sqlite3x.c: In function 'sqlite3MemoryBarrier':
sqlite3x.c:20410:17: error: expected expression before ')' token
   MemoryBarrier();

/*
** Try to provide a memory barrier operation, needed for initialization only.
*/
SQLITE_PRIVATE void sqlite3MemoryBarrier(void){
  MemoryBarrier();
}

This references a definition in winnt.h

#if _WIN32_WINNT >= _WIN32_WINNT_VISTA
# if defined(_AMD64_) || defined(__X86_64)
#  define MemoryBarrier __faststorefence

# elif defined(_IA64_)

#  define MemoryBarrier __mf
# else

   void __mingworg_MemoryBarrier(void);
#  define MemoryBarrier __mingworg_MemoryBarrier
# endif

#else

# define MemoryBarrier
#endif

If the _WIN32_WINNT value is less than Windows 6/Vista [0x600] then the error 
is generated.  If the version is 0x600 or greater, it compiles just fine.

Of course, versions of WinNT less than 6 are "no longer diddled by Microsoft" 
(meaning they are stable development targets).


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Richard Hipp
> Sent: Saturday, 5 September, 2015 21:02
> To: General Discussion of SQLite Database
> Cc: Michael Catanzaro
> Subject: Re: [sqlite] Thread safety problem encountered by people working
> on WebKit
> 
> On 9/5/15, Darin Adler  wrote:
> > Hi folks.
> >
> > I?m sending this on behalf of Michael Catanzaro, a contributor to the
> WebKit
> > open source project, who is working on a WebKit bug report, "Crash when
> > WebCore::SQLiteFileSystem::openDatabase is called from multiple threads"
> > , which seems to be
> caused
> > by an issue in SQLite. In short, we've noticed many applications that
> use
> > WebKit crash when sqlite3_initialize is called simultaneously in
> multiple
> > threads in the Fedora platform
> > 
> > 
> > 
> >   despite the fact
> that
> > sqlite3_initialize is documented to be thread-safe and called
> automatically
> > by the library when needed < https://sqlite.org/c3ref/initialize.html>.
> >
> > Michael is planning a workaround in WebKit that will call
> sqlite3_initialize
> > manually exactly once before WebKit uses sqlite, using std::once to deal
> > with the thread safety issue.
> >
> > We?d like to file an SQLite bug report about this, and as I understand
> it,
> > the proper way for a newcomer to do that is to send mail here.
> >
> > In the process of trying to report this, Michael discovered that the
> page
> > explaining how to report bugs against SQLite
> >  lists an incorrect
> email
> > address, . Mail to that address is rejected.
> >
> 
> Thanks for the bug report.  Code which might fix this has been checked
> into trunk.  I have also corrected the email address on the bug-report
> procedures page.
> 
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BEGINNER - Transactions in shell script

[Simon Slavin]

On 5 Sep 2015, at 9:18pm, Petr L?z?ovsk?  wrote:

> Have some shell scripts working with sqlite. Receiving incoming payments from 
> bank via HTTP API and pushing it into database. This script will start 
> periodically, every single hour. 
> 
> Want to prevent situation only few payments are written and script failed for 
> some reason. Have read about sqlite transactions and understood so I should 
> start every writing sequence with BEGIN; statement, than made all inserts and 
> than COMMIT; statement. Is this all how it is working? Should I do something 
> more to prevent such unwanted situation?

You have it correct.  If you put multiple change commands in one transaction, 
then either they are all executed or none of them are executed.  Even if your 
program crashes in the middle of a command, when SQLite opens the file again it 
works out what happened and restores a 'clean' database.

Theoretically you would have to always open a transaction for SQL.  Issuing 
INSERT before BEGIN would result in an error message.  But SQL is kind and 
opens a one-command transaction if you forget.

Simon.

[sqlite] Third test of json and index expressions, now it works

[Domingo Alvarez Duarte]

Hello !  

It seems that you mix apples to oranges and try to compare then.  

One thing is formal grammar/syntax and the other is
implementation/optimization/execution.  

The fact that sqlite does things in one way now do not mean it's the
best/definitive way of implement things and that can be changed.  

We are not going against anyone here we are discussing the way thins are/have
been done and questioning the reason of it and possible alternatives for the
same/different outcomes.  

All of it towards a more friendly/useful tool, but it seems that you fell
attacked and try to negate most propositions with sometimes naive arguments. 


Relax and try to see the reasons behind other peoples view.  

Let's be friends! Cheers !  
>  Sat Sep 05 2015 8:55:55 pm CEST CEST from "Simon Slavin"
>  Subject: Re: [sqlite] Third test of json and index
>expressions, now it works
>
>  On 5 Sep 2015, at 7:07pm, Darko Volaric  wrote:
> 
>  
>>That's not a valid reason since it's trivial for SQLite to transform
>> aliases by substituting their names with their definitions. It could be
>> handled in the parser code.
>> 

>  And by doing that you would not get optimization, since SQLite would have
>to work out the value once for the WHERE clause and once for the selected
>value. Which is what SQLite does now. Which is what you are complaining about
>in this thread.
> 
> Remember: SQLite /does/ do this, even though it's not required by the SQL
>standard.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Third test of json and index expressions, now it works

[Domingo Alvarez Duarte]

Hello !  

Again your proposition doesn't stand up !  

There is no "waste of cpu cycles" for work that is not done, I mean for the
ones that write queries for the machine instead to the humans, they will
continue to have the same results (less parsing steps, besides the parsing
normally accounts to something like 0.01% to 0.0001% or less of the time
spent on the query).  

Cheers !  

?  
>  Sat Sep 05 2015 8:34:00 pm CEST CEST from "R.Smith"  
>Subject: Re: [sqlite] Third test of json and index expressions, now it works
>
>  On 2015-09-05 08:07 PM, Darko Volaric wrote:
>  
>>So my question remains: why not have this user friendly feature? What are
>> the motivations for not having it?
>> 

>  Waste of CPU cycles punishing those users who stick to valid SQL for the 
> sins of those who like shortcuts.
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Third test of json and index expressions, now it works

[R.Smith]

On 2015-09-05 08:07 PM, Darko Volaric wrote:
> So my question remains: why not have this user friendly feature? What are
> the motivations for not having it?

Waste of CPU cycles punishing those users who stick to valid SQL for the 
sins of those who like shortcuts.

[sqlite] Third test of json and index expressions, now it works

[Simon Slavin]

On 5 Sep 2015, at 7:07pm, Darko Volaric  wrote:

> That's not a valid reason since it's trivial for SQLite to transform
> aliases by substituting their names with their definitions. It could be
> handled in the parser code.

And by doing that you would not get optimization, since SQLite would have to 
work out the value once for the WHERE clause and once for the selected value.  
Which is what SQLite does now.  Which is what you are complaining about in this 
thread.

Remember: SQLite /does/ do this, even though it's not required by the SQL 
standard.

Simon.

[sqlite] Third test of json and index expressions, now it works

[Scott Robison]

On Sep 5, 2015 4:10 PM, "Darko Volaric"  wrote:
>
> That's not true. SQLite doesn't have to discard that information after
> performing the substitutions. It can use it later for optimizations.
>
> I know that SQLite allows it. I'm not complaining about anything. Have a
> look at my original question, which is, restated:
>
> What is the rationale behind disallowing using aliases in the body of
> statements?

While I agree that it is can be useful, the primary reason I can think of
for disallowing it is preventing gratuitous incompatibilities between SQL
engines. This is particularly true when there are two options that can give
the same effect: expressing the select-from as a query vs a table name
(something like):

select * from (select a as b from c) where b is something

Or with a common table expression which I have so little experience with so
far that I'm not going to try to write an example on my phone. :)

All this being said, and without the experience of having written a SQL
engine personally, it sure feels like the standardization committee got
this wrong. It is such an intuitive way of thinking of using aliases that
it should be part of the standard. I only know from past experience with
other SQL products that it doesn't work in other places.

[sqlite] Third test of json and index expressions, now it works

[Domingo Alvarez Duarte]

Hello !  

Although your explanation is interesting it doesn't seems to be correct:  

First of all there is no point to to do anything if the whole sql statement
is no correct.  

I mean:  

- If there is any reference to inexistent columns/functions/tables/views  

- If any expression is not valid (ex SELECT a+=b/0 AS c ...)  

- If there is any ambiguity  

..  

Then and only then after a full parsing of all elements have passed the
grammatical/syntactical/logical analysis then we should proceed to make a
query plan to find the best alternative to answer the query.  

So if we need to do all of the mentioned checks before start to dig on the
data we already know all the expressions and it's aliases and are able to
switch between then without force users to explicitly and error prone
duplicate then more than once.  

Cheers !  
>  Sat Sep 05 2015 6:23:01 pm CEST CEST from "Simon Slavin"
>  Subject: Re: [sqlite] Third test of json and index
>expressions, now it works
>
>  On 5 Sep 2015, at 5:07pm, Darko Volaric  wrote:
> 
>  
>>That's not what I said, why don't you read/quote the whole sentence:
>> 
>> "Besides being part of the standard (I assume), what's the rationale for
>> this restriction?"
>> 
>> I'm asking why the SQL standard restricts the use of aliases in this way
>> and what the benefit of this restriction is.
>> 

>  I'm sorry. I did not understand your grammar. Please put it down to me
>being more used to British English than American English. I would have
>expected "Apart from being part of the standard ...".
> 
> The reason is that the SQL engine has to select the correct rows before it
>has to work out the value of each column in the row. Consider these
>statements:
> 
> SELECT s FROM myTable WHERE l/z < 456;
> SELECT * FROM myTable WHERE l/z < 456;
> SELECT (s-70)*l/z, myfunction(l/z) FROM myTable WHERE l/z < 456;
> 
> There is no point in SQL evaluating c-70*l/z or myfunction(l/z) for the
>whole table if only two or three are going to satisfy b < 456. So it does the
>WHERE clause first. Only once it has picked the right rows does it need to
>pay attention to the bit between SELECT and FROM. Maybe no rows will satisfy
>the WHERE clause and it won't have to bother at all. For a column alias to be
>useful in both parts of the clause the syntax might be more like
> 
> SELECT c-70*fract, myfunction(fract) FROM myTable WHERE (l/z AS fract) <
>456;
> 
> which is, of course, not valid SQL.
> 
> You might also be interested to see whether this works:
> 
> SELECT l/z AS fract, c-70*fract, myfunction(fract) FROM myTable WHERE l/z
>< 456;
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Thread safety problem encountered by people working on WebKit

[Darin Adler]

Hi folks.

I?m sending this on behalf of Michael Catanzaro, a contributor to the WebKit 
open source project, who is working on a WebKit bug report, "Crash when 
WebCore::SQLiteFileSystem::openDatabase is called from multiple threads" 
>, which seems to be caused by 
an issue in SQLite. In short, we've noticed many applications that use WebKit 
crash when sqlite3_initialize is called simultaneously in multiple threads in 
the Fedora platform > 
> 
> 
> despite the fact that 
sqlite3_initialize is documented to be thread-safe and called automatically by 
the library when needed < https://sqlite.org/c3ref/initialize.html 
>.

Michael is planning a workaround in WebKit that will call sqlite3_initialize 
manually exactly once before WebKit uses sqlite, using std::once to deal with 
the thread safety issue.

We?d like to file an SQLite bug report about this, and as I understand it, the 
proper way for a newcomer to do that is to send mail here.

In the process of trying to report this, Michael discovered that the page 
explaining how to report bugs against SQLite 
> lists an incorrect email 
address, mailto:sqlite-users at sqlite.org>>. Mail 
to that address is rejected.

? Darin

[sqlite] Thread safety problem encountered by people working on WebKit

[Darin Adler]

Hi folks.

I?m sending this on behalf of Michael Catanzaro, a contributor to the WebKit 
open source project, who is working on a WebKit bug report, "Crash when 
WebCore::SQLiteFileSystem::openDatabase is called from multiple threads" 
, which seems to be caused by 
an issue in SQLite. In short, we've noticed many applications that use WebKit 
crash when sqlite3_initialize is called simultaneously in multiple threads in 
the Fedora platform  
 
 
  despite the fact that 
sqlite3_initialize is documented to be thread-safe and called automatically by 
the library when needed < https://sqlite.org/c3ref/initialize.html>.

Michael is planning a workaround in WebKit that will call sqlite3_initialize 
manually exactly once before WebKit uses sqlite, using std::once to deal with 
the thread safety issue.

We?d like to file an SQLite bug report about this, and as I understand it, the 
proper way for a newcomer to do that is to send mail here.

In the process of trying to report this, Michael discovered that the page 
explaining how to report bugs against SQLite 
 lists an incorrect email 
address, . Mail to that address is rejected.

? Darin

[sqlite] Second test of json and index expressions, not so good

[Richard Hipp]

On 9/4/15, Domingo Alvarez Duarte  wrote:
> select json_extract(json, '$.value') AS val
> from json_tbl  where val = 'the_value_1';

The latest trunk check-in now uses an index for this query


-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Third test of json and index expressions, now it works

[Simon Slavin]

On 5 Sep 2015, at 5:07pm, Darko Volaric  wrote:

> That's not what I said, why don't you read/quote the whole sentence:
> 
> "Besides being part of the standard (I assume), what's the rationale for
> this restriction?"
> 
> I'm asking why the SQL standard restricts the use of aliases in this way
> and what the benefit of this restriction is.

I'm sorry.  I did not understand your grammar.  Please put it down to me being 
more used to British English than American English.  I would have expected 
"Apart from being part of the standard ...".

The reason is that the SQL engine has to select the correct rows before it has 
to work out the value of each column in the row.  Consider these statements:

SELECT s FROM myTable WHERE l/z < 456;
SELECT * FROM myTable WHERE l/z < 456;
SELECT (s-70)*l/z, myfunction(l/z) FROM myTable WHERE l/z < 456;

There is no point in SQL evaluating c-70*l/z or myfunction(l/z) for the whole 
table if only two or three are going to satisfy b < 456.  So it does the WHERE 
clause first.  Only once it has picked the right rows does it need to pay 
attention to the bit between SELECT and FROM.  Maybe no rows will satisfy the 
WHERE clause and it won't have to bother at all.  For a column alias to be 
useful in both parts of the clause the syntax might be more like

SELECT c-70*fract, myfunction(fract) FROM myTable WHERE (l/z AS fract) < 456;

which is, of course, not valid SQL.

You might also be interested to see whether this works:

SELECT l/z AS fract, c-70*fract, myfunction(fract) FROM myTable WHERE l/z < 456;

Simon.

[sqlite] Third test of json and index expressions, now it works

[Simon Slavin]

On 5 Sep 2015, at 3:19pm, Darko Volaric  wrote:

> Besides being part of the standard (I assume)

You assume incorrectly.  In the classic SQL model, aliases to column names are 
assigned after the results have been returned.  In other words, aliases cannot 
be used in the WHERE clause.

Simon.

[sqlite] Third test of json and index expressions, now it works

[Domingo Alvarez Duarte]

Hello !  

I also prefer a "DRY" approach than be repeating things.  

Repetition is the mother of several errors !  

Cheers !  
>  Sat Sep 05 2015 4:19:00 pm CEST CEST from "Darko Volaric"
>  Subject: Re: [sqlite] Third test of json and index
>expressions, now it works
>
>  Besides being part of the standard (I assume), what's the rationale for
> this restriction?
> 
> It would seem that standard SQL is being willfully less efficient and more
> error prone by making the user rewrite expressions.
> 
> Isn't this in the same category as manifest typing, where a more liberal
> approach is an improvement?
> 
> 
> On Fri, Sep 4, 2015 at 9:35 PM, Richard Hipp  wrote:
> 
>  
>>On 9/4/15, Domingo Alvarez Duarte  wrote:
>> 
>>  
>>>Would be nice to sqlite be able to recognize aliases and also do not call
>>> column expressions multiple times.
>>> 
>>> print("json=", db.exec_get_one("select json_extract(json, '$.value') AS

>>  val
>>  
>>>from json_tbl where val = 'the_value_1';"));
>>> 
>>> 

>>  This is not valid SQL, actually. For clarity, here is the (invalid)
>> SQL reformatted:
>> 
>> SELECT a+b AS x FROM t1 WHERE x=99;
>> 
>> You are not suppose to be able to access the "x" alias within the WHERE
>> clause.
>> 
>> Yes, I know that SQLite allows this. But it does so grudgingly, for
>> historical reasons. It is technically not valid. Note that none of
>> MySQL, Oracle, PostgreSQL, nor SQLServer allow statements like the
>> above.
>> 
>> Because the SQL is technically not valid, I am less inclined to spend
>> a lot of time trying to optimize it.
>> 
>> I really wish there was a way for me to fix this historical
>> permissiveness in SQLite without breaking millions of (miscoded)
>> iPhone/Android apps. I'd do so if I could.
>> --
>> D. Richard Hipp
>> drh at sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 

>  ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Third test of json and index expressions, now it works

[Darko Volaric]

That's not true. SQLite doesn't have to discard that information after
performing the substitutions. It can use it later for optimizations.

I know that SQLite allows it. I'm not complaining about anything. Have a
look at my original question, which is, restated:

What is the rationale behind disallowing using aliases in the body of
statements?

I think it's an important question since it's obviously easier (trivial) to
do common sub-expression elimination with aliases and it's clearly easier
for humans to write correct code using them. I'm wondering what the evils
of aliases in bodies are, because I can't think of any.

Maybe its because SQL(ite) always wants to reevaluate expressions wherever
they occur, and the implied semantics of body aliases run counter to this.
If that's the case, my next question would be why that behavior is
desirable.

On Sat, Sep 5, 2015 at 11:55 AM, Simon Slavin  wrote:

>
> On 5 Sep 2015, at 7:07pm, Darko Volaric  wrote:
>
> > That's not a valid reason since it's trivial for SQLite to transform
> > aliases by substituting their names with their definitions. It could be
> > handled in the parser code.
>
> And by doing that you would not get optimization, since SQLite would have
> to work out the value once for the WHERE clause and once for the selected
> value.  Which is what SQLite does now.  Which is what you are complaining
> about in this thread.
>
> Remember: SQLite /does/ do this, even though it's not required by the SQL
> standard.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] UNQL as an extension to sqlite3 and now could use index expressions

[Domingo Alvarez Duarte]

Hello !  

And here is the database dump for the example:  

___  

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE users(id INTEGER PRIMARY KEY, name TEXT, json_misc TEXT);
INSERT INTO "users" VALUES(1,'Domingo','{"color":"cyan", "has_eyes": true}');
CREATE TABLE "unql_users"(x);
INSERT INTO "unql_users"
VALUES('{"id":1,"name":"Domingo","color":"cyan","has_eyes":true}');
CREATE VIEW users_unql AS SELECT json_misc AS x FROM users;
COMMIT;  

___  

?

[sqlite] First test of json and index expressions, not so good

[Domingo Alvarez Duarte]

Hello !  

Yes it is but I discover later some flaws on my assumptions look at further
on this thread to see the end result.  

Cheers !  
>  Sat Sep 05 2015 2:03:40 pm CEST CEST from "Luiz Am?rico"
>  Subject: Re: [sqlite] First test of json and index
>expressions, not so good
>
>  Em 04/09/2015 17:09, "Domingo Alvarez Duarte" 
> escreveu:
> 
>  
>>Hello !
>> 
>> This is my first test with json and index expressions !
>> 
>> How to make usage of the expression index on queries ?
>> 
>> Why is json_extract so slow ?
>> 

>  Just for information
> 
> Is json_extract from https://github.com/groner/sqlite-json ?
> 
> Luiz
> 
> 
>  
>>Cheers !
>> 
>> Output
>> 
>> Time to insert 5000 0.032227 records by second =
>> 155149
>> count= 4999
>> json= the_value_1
>> 0 0 0 SCAN TABLE json_tbl
>> Time to select raw 5000 0.00244 records by second =
>> 2.04918e+06
>> Time to select json_extrat no index 5000 8.12196 records by
>> second = 615.615
>> Time to create index 5000 0.00605 records by second =
>> 826446
>> 0 0 0 SCAN TABLE json_tbl
>> Time to select json_extrat indexed 5000 7.38144 records by
>> second = 677.375
>> 
>> 
>> 
>> 
>> 
>> Program
>> 
>> local max_count = 5000;
>> local sql = "CREATE TABLE IF NOT EXISTS json_tbl(id integer primary key,

>  json
>  
>>text collate nocase);";
>> 
>> local db = SQLite3(":memory:");
>> db.exec_dml(sql);
>> 
>> local stmt = db.prepare("insert into json_tbl(json) values(?);");
>> 
>> local start = os.clock();
>> 
>> db.exec_dml("begin;");
>> for(local i=1; i < max_count; ++i)
>> {
>> stmt.bind(1, format([==[{"id" : %d, "value" : "the_value_%d"}]==], i,
>> i));
>> stmt.step();
>> stmt.reset();
>> }
>> stmt.finalize();
>> db.exec_dml("commit;");
>> 
>> local time_spent = os.clock() -start;
>> print("Time to insert", max_count, time_spent, "records by second = ",
>> max_count/time_spent);
>> 
>> 
>> print("count=", db.exec_get_one("select count(*) from json_tbl"));
>> print("json=", db.exec_get_one("select json_extract(json, '$.value') AS

>  val
>  
>>from json_tbl where val = 'the_value_1';"));
>> 
>> sql = "select json_extract(json, '$.value') AS val from json_tbl where
>> val = ?;";
>> 
>> local showPlan = function()
>> {
>> stmt = db.prepare("explain query plan " + sql);
>> while(stmt.next_row())
>> {
>> local line = "";
>> for(local i=0, len = stmt.col_count(); i < len; ++i)
>> {
>> line += "\t" + stmt.col(i);
>> }
>> print(line);
>> }
>> stmt.finalize();
>> }
>> 
>> showPlan();
>> 
>> start = os.clock();
>> stmt = db.prepare("select * from json_tbl");
>> while(stmt.next_row())
>> {
>> }
>> stmt.finalize();
>> 
>> time_spent = os.clock() -start;
>> print("Time to select raw", max_count, time_spent, "records by second = ",
>> max_count/time_spent);
>> 
>> start = os.clock();
>> stmt = db.prepare(sql);
>> for(local i=1; i < max_count; ++i)
>> {
>> stmt.bind(1, format("the_value_%d", i));
>> stmt.step();
>> //print(stmt.col(0));
>> stmt.reset();
>> }
>> stmt.finalize();
>> 
>> time_spent = os.clock() -start;
>> print("Time to select json_extrat no index", max_count, time_spent,

>  "records
>  
>>by second = ", max_count/time_spent);
>> 
>> start = os.clock();
>> db.exec_dml("create index json_tbl_idx on json_tbl(json_extract(json,
>> '$.value'));");
>> time_spent = os.clock() -start;
>> print("Time to create index", max_count, time_spent, "records by second =

>  ",
>  
>>max_count/time_spent);
>> 
>> showPlan();
>> 
>> start = os.clock();
>> stmt = db.prepare(sql);
>> for(local i=1; i < max_count; ++i)
>> {
>> stmt.bind(1, format("the_value_%d", i));
>> stmt.step();
>> //print(stmt.col(0));
>> stmt.reset();
>> }
>> stmt.finalize();
>> 
>> time_spent = os.clock() -start;
>> print("Time to select json_extrat indexed", max_count, time_spent,

>  "records
>  
>>by second = ", max_count/time_spent);
>> 
>> db.close();
>> 
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

>  ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] UNQL as an extension to sqlite3 and now could use index expressions

[Domingo Alvarez Duarte]

Hello !  

I did for one project a kind of sqlite3 extension using UNQL
(http://unql.sqlite.org/index.html/wiki?name=UnQL) it has some flaws (and I
did some bug fixes) like only performing linear scan on collections, but now
with index expressions support on sqlite3 it could be revived back with
indexes and work very well, I say this because UNQL has a lot of sugar to
work with json.  

With something like this sqlite will even be more usefull on a lot more
projects.  

Anyway to revive UNQL and make it as extension to sqlite officially ?  

Cheers !  

Example:  

output of "test-unql-json.nut"  

cyan
cyan
{"color":"cyan","has_eyes":true}
{"id":1,"name":"Domingo","color":"cyan","has_eyes":true}  

  

test-unql-json.nut  

local function unql_exec(db, sql)
{
??? local stmt = db.prepare(sql);
??? local rc = stmt.step();
??? stmt.finalize();
??? return rc;
}

local function unql_exec_get_one(db, sql)
{
??? local stmt = db.prepare(sql);
??? local rc = stmt.step();
??? local value = stmt.value();
??? stmt.finalize();
??? return value;
}

local sqlite3_db = SQLite3("unql_json.db");
local unql_db = xjd1(sqlite3_db);

sqlite3_db.exec_dml("DROP TABLE IF EXISTS users;");
sqlite3_db.exec_dml("CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY,
name TEXT, json_misc TEXT);");
sqlite3_db.exec_dml("CREATE VIEW IF NOT EXISTS users_unql AS SELECT json_misc
AS x FROM users;");
sqlite3_db.exec_dml([==[INSERT INTO users(id, name, json_misc) VALUES(1,
'Domingo', '{"color":"cyan", "has_eyes": true}')]==]);

unql_exec(unql_db, "DROP COLLECTION unql_users;");
unql_exec(unql_db, "CREATE COLLECTION unql_users;");
local rc = unql_exec(unql_db, [==[INSERT INTO unql_users VALUE {"id":1,
"name":"Domingo", "color":"cyan", "has_eyes": true}]==]);

print(sqlite3_db.exec_get_one("SELECT json_extract(json_misc, '$.color') AS
color FROM users"));
print(sqlite3_db.exec_get_one("SELECT json_extract(x, '$.color') AS color
FROM unql_users"));

print(unql_exec_get_one(unql_db, "SELECT FROM users_unql WHERE
users_unql.color ==? \"cyan\";"));
print(unql_exec_get_one(unql_db, "SELECT FROM unql_users WHERE
unql_users.color ==? \"cyan\";"));
//print(unql_exec_get_one(unql_db, "SELECT FROM unql_users WHERE
json_extract(unql_users, \"$.color\") ==? \"cyan\";"));


unql_db.close();
sqlite3_db.close();  

[sqlite] Third test of json and index expressions, now it works

[Domingo Alvarez Duarte]

Hello again !  

There is still some opportunities for constant folding that sqlite is not
using, I'm not saying that is easy to implement.  

The same principle could be applied to deterministic functions where all of
it's parameters end up been constants.  

_output of "sqlite3 < test.sql" commented  

0|0|0|SCAN TABLE json_tbl
0|0|0|SCAN TABLE json_tbl
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=?)
0|0|0|SCAN TABLE json_tbl < here sqlite could do a compile time constant
folding and use index
0|0|0|SCAN TABLE json_tbl < same here
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=?)  

_  

_test.sql  

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE json_tbl(id integer primary key, json text collate nocase);
CREATE VIEW json_tbl_value_idx_view AS 
??? SELECT *, '$.' AS idx1, 'value' AS idx2, '$.value' as path,
json_extract(json, '$.value') AS val 
??? FROM json_tbl;

CREATE VIEW json_tbl_value_idx2_view AS 
??? SELECT *, idx1 || idx2 AS idx_path -- compile time constant fold
opportunity
??? FROM json_tbl_value_idx_view;

CREATE INDEX json_tbl_idx on json_tbl(json_extract(json, '$.value'));

EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_idx_view WHERE 'the_value_33'
= json_extract(json, '$.' || idx2); -- compile time constant fold opportunity

EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_idx_view WHERE 'the_value_33'
= json_extract(json, idx1 || idx2); -- compile time constant fold opportunity

EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_idx_view WHERE 'the_value_33'
= json_extract(json, path);

EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_idx2_view WHERE
'the_value_33' = json_extract(json, idx_path);

EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_idx2_view WHERE
'the_value_33' = json_extract(json, idx1 || idx2); -- compile time constant
fold opportunity

EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_idx2_view WHERE
'the_value_33' = json_extract(json, path);

COMMIT;  

_  
>  Sat Sep 05 2015 6:35:08 am CEST CEST from "Richard Hipp"  
>Subject: Re: [sqlite] Third test of json and index expressions, now it works
>
>  On 9/4/15, Domingo Alvarez Duarte  wrote:
> 
>  
>>Would be nice to sqlite be able to recognize aliases and also do not call
>> column expressions multiple times.
>> 
>> print("json=", db.exec_get_one("select json_extract(json, '$.value') AS
>>val
>> from json_tbl where val = 'the_value_1';"));
>> 
>> 

>  This is not valid SQL, actually. For clarity, here is the (invalid)
> SQL reformatted:
> 
> SELECT a+b AS x FROM t1 WHERE x=99;
> 
> You are not suppose to be able to access the "x" alias within the WHERE
>clause.
> 
> Yes, I know that SQLite allows this. But it does so grudgingly, for
> historical reasons. It is technically not valid. Note that none of
> MySQL, Oracle, PostgreSQL, nor SQLServer allow statements like the
> above.
> 
> Because the SQL is technically not valid, I am less inclined to spend
> a lot of time trying to optimize it.
> 
> I really wish there was a way for me to fix this historical
> permissiveness in SQLite without breaking millions of (miscoded)
> iPhone/Android apps. I'd do so if I could.
> -- 
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Third test of json and index expressions, now it works

[Domingo Alvarez Duarte]

Hello !  

Thanks for reply !  

I still think that is worth having warnings for the already known quirks on
sqlite, probably even show it to stderr when building in debug mode because I
believe at some point people will do some debugging.  

Or maybe even better having a pragma "PRAGMA check_valid_sql_statements=ON"
and add a note on the documentation to developers to try at least once before
deploy and or in development mode to be sure they have clean/valid sql
statements.  

It probably will not clean all existing code but I believe it can gradually
improve the situation.  

Cheers !  
>  Sat Sep 05 2015 11:16:26 am CEST CEST from "Simon Slavin"
>  Subject: Re: [sqlite] Third test of json and index
>expressions, now it works
>
>  On 5 Sep 2015, at 10:07am, Domingo Alvarez Duarte
> wrote:
> 
>  
>>What about the warning messages to stderr through sqlite3 when opening
>> databases with invalid sql constructions, this way we can gradually have
>>less
>> and less code written in a non compliant way.
>> 

>  It might be acceptable to put warning code in sqlite3.exe and its
>equivalents for other platforms. But the majority of SQLite installation are
>on things which are not personal computers: mobile phones, GPS units, Digital
>TV boxes, etc.. They don't have stderr. They don't even have stdout.
> 
> Also I don't think it's worth doing at all. The developers are currently
>playing with SQLite4 which does not have to support the same level of
>backward compatibility as SQLite3. It can just refuse to parse commands which
>the development team does not wish to support.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Third test of json and index expressions, now it works

[Domingo Alvarez Duarte]

Hello !  

Thanks to point out that plain sql is better to demonstrate a point when
possible !  

And after your answer I did another tests and could see that by using views
then we can achieve the use of aliases in a clean way.  

This is supposed to be valid, doesn't it ?  

__Output of "sqlite3 < test.sql"  

0|0|0|SCAN TABLE json_tbl
0|0|0|SCAN TABLE json_tbl
0|0|0|SCAN TABLE json_tbl
0|0|0|SEARCH TABLE json_tbl USING INTEGER PRIMARY KEY (rowid>?)
0|0|0|SEARCH TABLE json_tbl USING INTEGER PRIMARY KEY (rowid>?)
0|0|0|SCAN TABLE json_tbl
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=? AND rowid>?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=? AND rowid>?)
0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (=?)  

__  

__test.sql  

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE json_tbl(id integer primary key, json text collate nocase);
CREATE VIEW json_tbl_value_view AS SELECT id,? json_extract(json, '$value')
AS val FROM json_tbl;  


INSERT INTO "json_tbl" VALUES(1,'{"id" : 1, "value" : "the_value_1"}');
INSERT INTO "json_tbl" VALUES(2,'{"id" : 2, "value" : "the_value_2"}');
INSERT INTO "json_tbl" VALUES(3,'{"id" : 3, "value" : "the_value_3"}');
INSERT INTO "json_tbl" VALUES(4,'{"id" : 4, "value" : "the_value_4"}');
INSERT INTO "json_tbl" VALUES(5,'{"id" : 5, "value" : "the_value_5"}');
INSERT INTO "json_tbl" VALUES(6,'{"id" : 6, "value" : "the_value_6"}');
INSERT INTO "json_tbl" VALUES(7,'{"id" : 7, "value" : "the_value_7"}');
INSERT INTO "json_tbl" VALUES(8,'{"id" : 8, "value" : "the_value_8"}');
INSERT INTO "json_tbl" VALUES(9,'{"id" : 9, "value" : "the_value_9"}');
INSERT INTO "json_tbl" VALUES(10,'{"id" : 10, "value" : "the_value_10"}');

EXPLAIN QUERY PLAN SELECT * FROM json_tbl WHERE json_extract(json, '$.value')
= 'the_value_33';

EXPLAIN QUERY PLAN SELECT * FROM json_tbl WHERE 'the_value_33' =
json_extract(json, '$.value');

EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_view WHERE 'the_value_33' =
val;

EXPLAIN QUERY PLAN 
WITH RECURSIVE above33 AS (SELECT * FROM json_tbl WHERE id > 33)
SELECT * FROM above33 WHERE json_extract(json, '$.value') = 'the_value_40';

EXPLAIN QUERY PLAN 
WITH RECURSIVE above33 AS (SELECT id,? json_extract(json, '$.value') AS val
FROM json_tbl WHERE id > 33)
SELECT * FROM above33 WHERE val = 'the_value_40';

EXPLAIN QUERY PLAN 
WITH RECURSIVE allofit AS (SELECT id,? json_extract(json, '$.value') AS val
FROM json_tbl)
SELECT * FROM allofit WHERE val = 'the_value_40';


CREATE INDEX json_tbl_idx on json_tbl(json_extract(json, '$.value'));

EXPLAIN QUERY PLAN SELECT * FROM json_tbl WHERE json_extract(json, '$.value')
= 'the_value_33';

EXPLAIN QUERY PLAN SELECT * FROM json_tbl WHERE 'the_value_33' =
json_extract(json, '$.value');

EXPLAIN QUERY PLAN SELECT * FROM json_tbl_value_view WHERE 'the_value_33' =
val;

EXPLAIN QUERY PLAN 
WITH RECURSIVE above33 AS (SELECT * FROM json_tbl WHERE id > 33)
SELECT * FROM above33 WHERE json_extract(json, '$.value') = 'the_value_40';

EXPLAIN QUERY PLAN 
WITH RECURSIVE above33 AS (SELECT id,? json_extract(json, '$.value') AS val
FROM json_tbl WHERE id > 33)
SELECT * FROM above33 WHERE val = 'the_value_40';

EXPLAIN QUERY PLAN 
WITH RECURSIVE allofit AS (SELECT id,? json_extract(json, '$.value') AS val
FROM json_tbl)
SELECT * FROM allofit WHERE val = 'the_value_40';

COMMIT;  

__  
>  Sat Sep 05 2015 6:35:08 am CEST CEST from "Richard Hipp"  
>Subject: Re: [sqlite] Third test of json and index expressions, now it works
>
>  On 9/4/15, Domingo Alvarez Duarte  wrote:
> 
>  
>>Would be nice to sqlite be able to recognize aliases and also do not call
>> column expressions multiple times.
>> 
>> print("json=", db.exec_get_one("select json_extract(json, '$.value') AS
>>val
>> from json_tbl where val = 'the_value_1';"));
>> 
>> 

>  This is not valid SQL, actually. For clarity, here is the (invalid)
> SQL reformatted:
> 
> SELECT a+b AS x FROM t1 WHERE x=99;
> 
> You are not suppose to be able to access the "x" alias within the WHERE
>clause.
> 
> Yes, I know that SQLite allows this. But it does so grudgingly, for
> historical reasons. It is technically not valid. Note that none of
> MySQL, Oracle, PostgreSQL, nor SQLServer allow statements like the
> above.
> 
> Because the SQL is technically not valid, I am less inclined to spend
> a lot of time trying to optimize it.
> 
> I really wish there was a way for me to fix this historical
> permissiveness in SQLite without breaking millions of (miscoded)
> iPhone/Android apps. I'd do so if I could.
> -- 
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
>
>  



?

[sqlite] Third test of json and index expressions, now it works

[Darko Volaric]

That's not a valid reason since it's trivial for SQLite to transform
aliases by substituting their names with their definitions. It could be
handled in the parser code.

Meanwhile it's much harder for a human to do the opposite.

So my question remains: why not have this user friendly feature? What are
the motivations for not having it?

On Sat, Sep 5, 2015 at 9:23 AM, Simon Slavin  wrote:

>
> On 5 Sep 2015, at 5:07pm, Darko Volaric  wrote:
>
> > That's not what I said, why don't you read/quote the whole sentence:
> >
> > "Besides being part of the standard (I assume), what's the rationale for
> > this restriction?"
> >
> > I'm asking why the SQL standard restricts the use of aliases in this way
> > and what the benefit of this restriction is.
>
> I'm sorry.  I did not understand your grammar.  Please put it down to me
> being more used to British English than American English.  I would have
> expected "Apart from being part of the standard ...".
>
> The reason is that the SQL engine has to select the correct rows before it
> has to work out the value of each column in the row.  Consider these
> statements:
>
> SELECT s FROM myTable WHERE l/z < 456;
> SELECT * FROM myTable WHERE l/z < 456;
> SELECT (s-70)*l/z, myfunction(l/z) FROM myTable WHERE l/z < 456;
>
> There is no point in SQL evaluating c-70*l/z or myfunction(l/z) for the
> whole table if only two or three are going to satisfy b < 456.  So it does
> the WHERE clause first.  Only once it has picked the right rows does it
> need to pay attention to the bit between SELECT and FROM.  Maybe no rows
> will satisfy the WHERE clause and it won't have to bother at all.  For a
> column alias to be useful in both parts of the clause the syntax might be
> more like
>
> SELECT c-70*fract, myfunction(fract) FROM myTable WHERE (l/z AS fract) <
> 456;
>
> which is, of course, not valid SQL.
>
> You might also be interested to see whether this works:
>
> SELECT l/z AS fract, c-70*fract, myfunction(fract) FROM myTable WHERE l/z
> < 456;
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Third test of json and index expressions, now it works

[Domingo Alvarez Duarte]

Hello !  
>  I really wish there was a way for me to fix this historical
> permissiveness in SQLite without breaking millions of (miscoded)
> iPhone/Android apps. I'd do so if I could.
> --
>
>  ?
>
>  



What about the warning messages to stderr through sqlite3 when opening
databases with invalid sql constructions, this way we can gradually have less
and less code written in a non compliant way.  

>sqlite3  

sqlite>SELECT a+b AS x FROM (select 4 as a, 5 as b) WHERE x=99;  

-- warning aliases are not supposed to be used on where clauses  

-- historical mistake of sqlite to accept it  

-- please rewrite your code in a compliant way, sqlite can stop support this
at any time  

?  

Cheers !

[sqlite] Third test of json and index expressions, now it works

[Simon Slavin]

On 5 Sep 2015, at 10:07am, Domingo Alvarez Duarte  wrote:

> What about the warning messages to stderr through sqlite3 when opening
> databases with invalid sql constructions, this way we can gradually have less
> and less code written in a non compliant way.

It might be acceptable to put warning code in sqlite3.exe and its equivalents 
for other platforms.  But the majority of SQLite installation are on things 
which are not personal computers: mobile phones, GPS units, Digital TV boxes, 
etc..  They don't have stderr.  They don't even have stdout.

Also I don't think it's worth doing at all.  The developers are currently 
playing with SQLite4 which does not have to support the same level of backward 
compatibility as SQLite3.  It can just refuse to parse commands which the 
development team does not wish to support.

Simon.

[sqlite] First test of json and index expressions, not so good

[Richard Hipp]

On 9/5/15, Luiz Am?rico  wrote:
>
> Just for information
>
> Is json_extract from https://github.com/groner/sqlite-json ?
>

No.  The json_extract() function at
https://www.sqlite.org/src/artifact/bd51e8c1?ln=1155-1191 is a
completely original implementation written by me.  I did not reference
or use any prior code.  I did not know about the groner implementation
until you post.  The interface design is derived from the MySQL
documentation at https://dev.mysql.com/doc/refman/5.7/en/json.html
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Third test of json and index expressions, now it works

[Keith Medcalf]

> > Besides being part of the standard (I assume)

> You assume incorrectly.  In the classic SQL model, aliases to column names
> are assigned after the results have been returned.  In other words,
> aliases cannot be used in the WHERE clause.

Or group by clause ...

> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Third test of json and index expressions, now it works

[Simon Slavin]

On 5 Sep 2015, at 5:35am, Richard Hipp  wrote:

> I really wish there was a way for me to fix this historical
> permissiveness in SQLite without breaking millions of (miscoded)
> iPhone/Android apps.  I'd do so if I could.

That's what SQLite4 is for.  I hope.

Simon.

[sqlite] Third test of json and index expressions, now it works

[Darko Volaric]

That's not what I said, why don't you read/quote the whole sentence:

"Besides being part of the standard (I assume), what's the rationale for
this restriction?"

I'm asking why the SQL standard restricts the use of aliases in this way
and what the benefit of this restriction is.

On Sat, Sep 5, 2015 at 8:49 AM, Simon Slavin  wrote:

>
> On 5 Sep 2015, at 3:19pm, Darko Volaric  wrote:
>
> > Besides being part of the standard (I assume)
>
> You assume incorrectly.  In the classic SQL model, aliases to column names
> are assigned after the results have been returned.  In other words, aliases
> cannot be used in the WHERE clause.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] First test of json and index expressions, not so good

[Luiz Américo]

Em 04/09/2015 17:09, "Domingo Alvarez Duarte" 
escreveu:
>
> Hello !
>
> This is my first test with json and index expressions !
>
> How to make usage of the expression index on queries ?
>
> Why is json_extract so slow ?

Just for information

Is json_extract from https://github.com/groner/sqlite-json ?

Luiz

>
> Cheers !
>
> Output
>
> Time to insert50000.032227records by second =
> 155149
> count=4999
> json=the_value_1
> 000SCAN TABLE json_tbl
> Time to select raw50000.00244records by second =
> 2.04918e+06
> Time to select json_extrat no index50008.12196records by
> second = 615.615
> Time to create index50000.00605records by second =
> 826446
> 000SCAN TABLE json_tbl
> Time to select json_extrat indexed50007.38144records by
> second = 677.375
>
> 
>
>
>
> Program
>
> local max_count = 5000;
> local sql = "CREATE TABLE IF NOT EXISTS json_tbl(id integer primary key,
json
> text collate nocase);";
>
> local db = SQLite3(":memory:");
> db.exec_dml(sql);
>
> local stmt = db.prepare("insert into json_tbl(json) values(?);");
>
> local start = os.clock();
>
> db.exec_dml("begin;");
> for(local i=1; i < max_count; ++i)
> {
> stmt.bind(1, format([==[{"id" : %d, "value" : "the_value_%d"}]==], i,
> i));
> stmt.step();
> stmt.reset();
> }
> stmt.finalize();
> db.exec_dml("commit;");
>
> local time_spent = os.clock() -start;
> print("Time to insert", max_count, time_spent, "records by second = ",
> max_count/time_spent);
>
>
> print("count=", db.exec_get_one("select count(*) from json_tbl"));
> print("json=", db.exec_get_one("select json_extract(json, '$.value') AS
val
> from json_tbl  where val = 'the_value_1';"));
>
> sql = "select  json_extract(json, '$.value') AS val  from json_tbl where
> val = ?;";
>
> local showPlan = function()
> {
> stmt = db.prepare("explain query plan " + sql);
> while(stmt.next_row())
> {
> local line = "";
> for(local i=0, len = stmt.col_count(); i < len; ++i)
> {
> line += "\t" + stmt.col(i);
> }
> print(line);
> }
> stmt.finalize();
> }
>
> showPlan();
>
> start = os.clock();
> stmt = db.prepare("select * from json_tbl");
> while(stmt.next_row())
> {
> }
> stmt.finalize();
>
> time_spent = os.clock() -start;
> print("Time to select raw", max_count, time_spent, "records by second = ",
> max_count/time_spent);
>
> start = os.clock();
> stmt = db.prepare(sql);
> for(local i=1; i < max_count; ++i)
> {
> stmt.bind(1, format("the_value_%d", i));
> stmt.step();
> //print(stmt.col(0));
> stmt.reset();
> }
> stmt.finalize();
>
> time_spent = os.clock() -start;
> print("Time to select json_extrat no index", max_count, time_spent,
"records
> by second = ", max_count/time_spent);
>
> start = os.clock();
> db.exec_dml("create index json_tbl_idx on json_tbl(json_extract(json,
> '$.value'));");
> time_spent = os.clock() -start;
> print("Time to create index", max_count, time_spent, "records by second =
",
> max_count/time_spent);
>
> showPlan();
>
> start = os.clock();
> stmt = db.prepare(sql);
> for(local i=1; i < max_count; ++i)
> {
> stmt.bind(1, format("the_value_%d", i));
> stmt.step();
> //print(stmt.col(0));
> stmt.reset();
> }
> stmt.finalize();
>
> time_spent = os.clock() -start;
> print("Time to select json_extrat indexed", max_count, time_spent,
"records
> by second = ", max_count/time_spent);
>
> db.close();
>
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Third test of json and index expressions, now it works

[Darko Volaric]

Besides being part of the standard (I assume), what's the rationale for
this restriction?

It would seem that standard SQL is being willfully less efficient and more
error prone by making the user rewrite expressions.

Isn't this in the same category as manifest typing, where a more liberal
approach is an improvement?


On Fri, Sep 4, 2015 at 9:35 PM, Richard Hipp  wrote:

> On 9/4/15, Domingo Alvarez Duarte  wrote:
> >
> > Would be nice to sqlite be able to recognize aliases and also do not call
> > column expressions multiple times.
> >
> > print("json=", db.exec_get_one("select json_extract(json, '$.value') AS
> val
> > from json_tbl  where val = 'the_value_1';"));
> >
>
> This is not valid SQL, actually.  For clarity, here is the (invalid)
> SQL reformatted:
>
>SELECT a+b AS x FROM t1 WHERE x=99;
>
> You are not suppose to be able to access the "x" alias within the WHERE
> clause.
>
> Yes, I know that SQLite allows this.  But it does so grudgingly, for
> historical reasons.  It is technically not valid.  Note that none of
> MySQL, Oracle, PostgreSQL, nor SQLServer allow statements like the
> above.
>
> Because the SQL is technically not valid, I am less inclined to spend
> a lot of time trying to optimize it.
>
> I really wish there was a way for me to fix this historical
> permissiveness in SQLite without breaking millions of (miscoded)
> iPhone/Android apps.  I'd do so if I could.
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Third test of json and index expressions, now it works

[Richard Hipp]

On 9/4/15, Domingo Alvarez Duarte  wrote:
>
> Would be nice to sqlite be able to recognize aliases and also do not call
> column expressions multiple times.
>
> print("json=", db.exec_get_one("select json_extract(json, '$.value') AS val
> from json_tbl  where val = 'the_value_1';"));
>

This is not valid SQL, actually.  For clarity, here is the (invalid)
SQL reformatted:

   SELECT a+b AS x FROM t1 WHERE x=99;

You are not suppose to be able to access the "x" alias within the WHERE clause.

Yes, I know that SQLite allows this.  But it does so grudgingly, for
historical reasons.  It is technically not valid.  Note that none of
MySQL, Oracle, PostgreSQL, nor SQLServer allow statements like the
above.

Because the SQL is technically not valid, I am less inclined to spend
a lot of time trying to optimize it.

I really wish there was a way for me to fix this historical
permissiveness in SQLite without breaking millions of (miscoded)
iPhone/Android apps.  I'd do so if I could.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Third test of json and index expressions, now it works

[Domingo Alvarez Duarte]

Hello again !  

I was trying to use the alias in the where clause hopping sqlite would be
smart enough to recognize it and use the index but that doesn't happen.  

So I tried again repeating the expression instead of using the alias and now
sqlite recognize the index and run very fast.  

Would be nice to sqlite be able to recognize aliases and also do not call
column expressions multiple times.  

Cheers !  

Output  

Time to insert??? 5000??? 0.035002??? records by second = ???
142849
count=??? 4999
json=??? the_value_1
??? 0??? 0??? 0??? SCAN TABLE json_tbl
Time to select raw??? 5000??? 0.002689??? records by second = ???
1.85943e+06
Time to select raw one by one??? 5000??? 7.60242??? records by
second = ??? 657.685
Time to select json_extrat no index??? 5000??? 7.59418??? records by
second = ??? 658.399
Time to create index??? 5000??? 0.006153??? records by second =
??? 812612
??? 0??? 0??? 0??? SEARCH TABLE json_tbl USING INDEX json_tbl_idx
(=?)
Time to select json_extrat indexed??? 5000??? 0.034494??? records by
second = ??? 144953  

  

Program  

local max_count = 5000;
local sql = "CREATE TABLE IF NOT EXISTS json_tbl(id integer primary key, json
text collate nocase);";

local db = SQLite3(":memory:");
db.exec_dml(sql);

local stmt = db.prepare("insert into json_tbl(json) values(?);");

local start = os.clock();

db.exec_dml("begin;");
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format([==[{"id" : %d, "value" : "the_value_%d"}]==], i,
i));
??? stmt.step();
??? stmt.reset();
}
stmt.finalize();
db.exec_dml("commit;");

local time_spent = os.clock() -start;
print("Time to insert", max_count, time_spent, "records by second = ",
max_count/time_spent);


print("count=", db.exec_get_one("select count(*) from json_tbl"));
print("json=", db.exec_get_one("select json_extract(json, '$.value') AS val
from json_tbl? where val = 'the_value_1';"));

sql = "select? json_extract(json, '$.value') AS val? from json_tbl where
json_extract(json, '$.value') = ?;";

local showPlan = function()
{
??? stmt = db.prepare("explain query plan " + sql);
??? while(stmt.next_row())
??? {
??? ??? local line = "";
??? ??? for(local i=0, len = stmt.col_count(); i < len; ++i)
??? ??? {
??? ??? ??? line += "\t" + stmt.col(i);
??? ??? }
??? ??? print(line);
??? }
??? stmt.finalize();
}

showPlan();

start = os.clock();
stmt = db.prepare("select * from json_tbl");
while(stmt.next_row())
{
}
stmt.finalize();

time_spent = os.clock() -start;
print("Time to select raw", max_count, time_spent, "records by second = ",
max_count/time_spent);

start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();

time_spent = os.clock() -start;
print("Time to select raw one by one", max_count, time_spent, "records by
second = ", max_count/time_spent);

start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();

time_spent = os.clock() -start;
print("Time to select json_extrat no index", max_count, time_spent, "records
by second = ", max_count/time_spent);

start = os.clock();
db.exec_dml("create index json_tbl_idx on json_tbl(json_extract(json,
'$.value'));");
time_spent = os.clock() -start;
print("Time to create index", max_count, time_spent, "records by second = ",
max_count/time_spent);

showPlan();

start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();

time_spent = os.clock() -start;
print("Time to select json_extrat indexed", max_count, time_spent, "records
by second = ", max_count/time_spent);

/*
local function unql_exec(db, sql)
{
??? local the_stmt = db.prepare(sql);
??? local rc = the_stmt.step();
??? the_stmt.finalize();
??? return rc;
}

start = os.clock();
local db_unql = xjd1(db);

unql_exec(db_unql, "CREATE COLLECTION unql_json;");

stmt = db_unql.prepare("INSERT INTO unql_json VALUE ?;");
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format([==[{"id" : %d, "value" : "the_value_%d"}]==], i,
i));
??? stmt.step();
??? stmt.reset();
}
stmt.finalize();

stmt = db_unql.prepare("SELECT FROM unql_json WHERE unql_json.value = ?;");
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();

time_spent = os.clock() -start;
print("Time to select unql", max_count, time_spent, "records by second = ",
max_count/time_spent);
*/

//db.backup("json.db");
db.close();  

[sqlite] Second test of json and index expressions, not so good

[Domingo Alvarez Duarte]

Hello !  

I was not been fair with my raw select against json_extract now I'm doing it
one by one and the difference with json_extract is basically none.  

But I still could not manage to use the index expression to speedup the
query.  

__Output  

Time to insert??? 5000??? 0.03179??? records by second = ???
157282
count=??? 4999
json=??? the_value_1
??? 0??? 0??? 0??? SCAN TABLE json_tbl
Time to select raw??? 5000??? 0.002456??? records by second = ???
2.03583e+06
Time to select raw one by one??? 5000??? 7.40657??? records by
second = ??? 675.076
Time to select json_extrat no index??? 5000??? 7.68341??? records by
second = ??? 650.753
Time to create index??? 5000??? 0.006101??? records by second =
??? 819538
??? 0??? 0??? 0??? SCAN TABLE json_tbl
Time to select json_extrat indexed??? 5000??? 7.68529??? records by
second = ??? 650.593  

__  

__Program  

local max_count = 5000;
local sql = "CREATE TABLE IF NOT EXISTS json_tbl(id integer primary key, json
text collate nocase);";

local db = SQLite3(":memory:");
db.exec_dml(sql);

local stmt = db.prepare("insert into json_tbl(json) values(?);");

local start = os.clock();

db.exec_dml("begin;");
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format([==[{"id" : %d, "value" : "the_value_%d"}]==], i,
i));
??? stmt.step();
??? stmt.reset();
}
stmt.finalize();
db.exec_dml("commit;");

local time_spent = os.clock() -start;
print("Time to insert", max_count, time_spent, "records by second = ",
max_count/time_spent);


print("count=", db.exec_get_one("select count(*) from json_tbl"));
print("json=", db.exec_get_one("select json_extract(json, '$.value') AS val
from json_tbl? where val = 'the_value_1';"));

sql = "select? json_extract(json, '$.value') AS val? from json_tbl where
val = ?;";

local showPlan = function()
{
??? stmt = db.prepare("explain query plan " + sql);
??? while(stmt.next_row())
??? {
??? ??? local line = "";
??? ??? for(local i=0, len = stmt.col_count(); i < len; ++i)
??? ??? {
??? ??? ??? line += "\t" + stmt.col(i);
??? ??? }
??? ??? print(line);
??? }
??? stmt.finalize();
}

showPlan();

start = os.clock();
stmt = db.prepare("select * from json_tbl");
while(stmt.next_row())
{
}
stmt.finalize();

time_spent = os.clock() -start;
print("Time to select raw", max_count, time_spent, "records by second = ",
max_count/time_spent);

start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();

time_spent = os.clock() -start;
print("Time to select raw one by one", max_count, time_spent, "records by
second = ", max_count/time_spent);

start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();

time_spent = os.clock() -start;
print("Time to select json_extrat no index", max_count, time_spent, "records
by second = ", max_count/time_spent);

start = os.clock();
db.exec_dml("create index json_tbl_idx on json_tbl(json_extract(json,
'$.value'));");
time_spent = os.clock() -start;
print("Time to create index", max_count, time_spent, "records by second = ",
max_count/time_spent);

showPlan();

start = os.clock();
stmt = db.prepare(sql);
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? //print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();

time_spent = os.clock() -start;
print("Time to select json_extrat indexed", max_count, time_spent, "records
by second = ", max_count/time_spent);

/*
local function unql_exec(db, sql)
{
??? local the_stmt = db.prepare(sql);
??? local rc = the_stmt.step();
??? the_stmt.finalize();
??? return rc;
}

start = os.clock();
local db_unql = xjd1(db);

unql_exec(db_unql, "CREATE COLLECTION unql_json;");

stmt = db_unql.prepare("INSERT INTO unql_json VALUE ?;");
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format([==[{"id" : %d, "value" : "the_value_%d"}]==], i,
i));
??? stmt.step();
??? stmt.reset();
}
stmt.finalize();

stmt = db_unql.prepare("SELECT FROM unql_json WHERE unql_json.value = ?;");
for(local i=1; i < max_count; ++i)
{
??? stmt.bind(1, format("the_value_%d", i));
??? stmt.step();
??? print(stmt.col(0));
??? stmt.reset();
}
stmt.finalize();

time_spent = os.clock() -start;
print("Time to select unql", max_count, time_spent, "records by second = ",
max_count/time_spent);
*/

//db.backup("json.db");
db.close();  

__