Hi,
I have a general data modeling question:
I have the data in this format
Description(1..n)
Name
Desc
Records (0..n)
Number
Type
Values(1..n)
Now its higly imposible to change this format because of legacy reason and
we use some proprietary single file database.
Now we want to use new db for example sqlite but not sure how to model this
in sql mainly becaues "Records" have "Values" this is also an array of some
values depending on the Type (Int,String,Double).
And "Description" can be sometimes of size 100Mb
Could someone suggest what would be a good way to model this as SQL tables?
Thank
-Sherry
On 5 September 2015 at 19:00, <sqlite-users-request at mailinglists.sqlite.org>
wrote:
> Send sqlite-users mailing list submissions to
> sqlite-users at mailinglists.sqlite.org
>
> To subscribe or unsubscribe via the World Wide Web, visit
>
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> or, via email, send a message with subject or body 'help' to
> sqlite-users-request at mailinglists.sqlite.org
>
> You can reach the person managing the list at
> sqlite-users-owner at mailinglists.sqlite.org
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of sqlite-users digest..."
>
>
> Today's Topics:
>
> 1. Re: sqlite-users Digest, Vol 93, Issue 4 (Peter Haworth)
> 2. Re: sqlite-users Digest, Vol 93, Issue 4 (R.Smith)
> 3. Re: Stable way to SELECT from SQLite db using WAL on network
> share? (Richard Hipp)
> 4. Re: Changes to create index on trunk is breaking old code
> (Domingo Alvarez Duarte)
> 5. Re: Using |DataDirectory| in connection string (.NET with
> System.Data.SQLite) (Joe Mistachkin)
> 6. First test of json and index expressions, not so good
> (Domingo Alvarez Duarte)
> 7. Re: First test of json and index expressions, not so good
> (Domingo Alvarez Duarte)
> 8. Re: First test of json and index expressions, not so good
> (Domingo Alvarez Duarte)
> 9. Second test of json and index expressions, not so good
> (Domingo Alvarez Duarte)
> 10. Re: Using |DataDirectory| in connection string (.NET with
> System.Data.SQLite) (Lee Gray)
> 11. Third test of json and index expressions, now it works
> (Domingo Alvarez Duarte)
> 12. Re: Third test of json and index expressions, now it works
> (Richard Hipp)
> 13. Re: Third test of json and index expressions, now it works
> (Simon Slavin)
> 14. Re: Third test of json and index expressions, now it works
> (Domingo Alvarez Duarte)
> 15. Re: Third test of json and index expressions, now it works
> (Simon Slavin)
> 16. Re: Third test of json and index expressions, now it works
> (Domingo Alvarez Duarte)
> 17. Re: Third test of json and index expressions, now it works
> (Domingo Alvarez Duarte)
> 18. Re: Third test of json and index expressions, now it works
> (Domingo Alvarez Duarte)
> 19. Re: Third test of json and index expressions, now it works
> (Domingo Alvarez Duarte)
> 20. Re: First test of json and index expressions, not so good
> (Luiz Am?rico)
> 21. UNQL as an extension to sqlite3 and now could use index
> expressions (Domingo Alvarez Duarte)
> 22. Re: First test of json and index expressions, not so good
> (Domingo Alvarez Duarte)
> 23. Re: UNQL as an extension to sqlite3 and now could use index
> expressions (Domingo Alvarez Duarte)
> 24. Re: First test of json and index expressions, not so good
> (Richard Hipp)
> 25. Re: Third test of json and index expressions, now it works
> (Darko Volaric)
> 26. Re: Third test of json and index expressions, now it works
> (Domingo Alvarez Duarte)
> 27. Re: Third test of json and index expressions, now it works
> (Simon Slavin)
> 28. Re: Third test of json and index expressions, now it works
> (Keith Medcalf)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Fri, 04 Sep 2015 16:09:20 +0000
> From: Peter Haworth <pete at lcsql.com>
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] sqlite-users Digest, Vol 93, Issue 4
> Message-ID:
> <
> CAGDT7eNA-yrNzi4q_LxyGOLhEsMRbYzRNcs2fhBE0Zi8UfWEwQ at mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> Thanks for the info.
>
> Could I get round the single value limitation with something like:
>
> IN ((SELECT 'abc','def' FROM myTable))
>
> Also, in the LIKE example, looks like I don't need single quote delimiters
> around the pattern?
>
>
> >
> > Message: 4
> > Date: Thu, 3 Sep 2015 12:54:46 -0400
> > From: Richard Hipp <drh at sqlite.org>
> > To: General Discussion of SQLite Database
> > <sqlite-users at mailinglists.sqlite.org>
> > Subject: Re: [sqlite] Variables in statements
> > Message-ID:
> > <CALwJ=
> > Mwy6Z07bWxUNACk6uAv2d6NmM8s4ETGcNbULw3jXZHH3g at mail.gmail.com>
> > Content-Type: text/plain; charset=UTF-8
> >
> > On 9/3/15, Peter Haworth <pete at lcsql.com> wrote:
> > >
> > > SELECT * FROM myTable WHERE myKey IN (:1)
> > >
> > > If the value I supply to be used as :1 is a single integer, the SELECT
> > > finds the correct rows. If the value is a comma separated list of
> > > integers, e.g 1,2 the SELECT statement does not return any rows and no
> > > error is returned. If I recode the SELECT to specify 1,2 instead of
> :1,
> > > the correct rows are returned.
> > >
> > > Should the :1 form work when a list is supplied as its value?
> >
> > No. Variables only work for single values, not lists.
> >
> >
> > >
> > > Similarly with a statement like this.
> > >
> > > SELECT * FROM myTable WHERE myText LIKE :1
> > >
> > > I've tried various ways of implementing that with the following LIKE
> > clause
> > > and :1 values:
> > >
> > > LIKE :1 - '%abc%'
> > > LIKE :1 - %abc%
> > > LIKE ':1' - %abc%
> > > LIKE '%:1%' - abc
> > >
> > > None of the above return any rows, but if I issue:
> > >
> > > SELECT * FROM myTable WHERE myText LIKE '%abc%'
> > >
> > > ... the correct rows are returned.
> > >
> > > I suspect this is a Livecode problem but wanted to check if what I am
> > > trying to do is syntactically correct before reporting it as a bug.
> > >
> >
> > I concur. This latter seems like a livecode problem. Similar things
> > work in SQLite. See
> > https://www.sqlite.org/src/artifact/0f0ee61?ln=295 for example. The
> > example uses $like instead of :1, but they both work the same.
> >
> > --
> > D. Richard Hipp
> > drh at sqlite.org
> >
> >
> > < <sqlite-users at mailinglists.sqlite.org>
>
>
> ------------------------------
>
> Message: 2
> Date: Fri, 4 Sep 2015 18:32:12 +0200
> From: "R.Smith" <rsmith at rsweb.co.za>
> To: sqlite-users at mailinglists.sqlite.org
> Subject: Re: [sqlite] sqlite-users Digest, Vol 93, Issue 4
> Message-ID: <55E9C78C.9050609 at rsweb.co.za>
> Content-Type: text/plain; charset=windows-1252; format=flowed
>
>
>
> On 2015-09-04 06:09 PM, Peter Haworth wrote:
> > Thanks for the info.
> >
> > Could I get round the single value limitation with something like:
> >
> > IN ((SELECT 'abc','def' FROM myTable))
>
> Maybe what you intend is more like this:
>
> IN (SELECT 'abc' UNION ALL SELECT 'def' UNION ALL SELECT 'ghi'... etc.)
>
> alternate:
>
> IN (SELECT A FROM B)
> - assuming table B contains a column A that already contains all the
> values you need to include in the IN.
>
>
> > Also, in the LIKE example, looks like I don't need single quote
> delimiters
> > around the pattern?
>
> The quote delimiters enables passing the value within an SQLite
> statement, it is not significant to the type of value or in any way
> significant to the LIKE parsing.
>
> You could as easily use an identifier or parameter sans quotes, but if
> you wish to compare like-ness by giving a string directly in an SQL
> statement, it has to be contained in single quotes. That is purely
> because of string-passing semantics in general. This means the following
> examples all do the same thing:
>
> "SELECT A, 'abc%' FROM B WHERE C LIKE 'abc%';"
> "SELECT A, :1 FROM B WHERE C LIKE :1;" passing the value: abc%
> "SELECT A, D FROM B WHERE C LIKE D;" assuming this is a sub-select
> within another query of which there is a column D that contains a value
> like: abc%
>
>
> (I may have misinterpreted what you meant, if so, apologies - feel free
> to ask again)
>
> Cheers!
> Ryan
>
>
>
> ------------------------------
>
> Message: 3
> Date: Fri, 4 Sep 2015 12:37:00 -0400
> From: Richard Hipp <drh at sqlite.org>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Stable way to SELECT from SQLite db using WAL on
> network share?
> Message-ID:
> <CALwJ=
> MxZ56rO3CWDpTkh0LdWRk+4dbSfn_pAkQvKx-EM26Rvtw at mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> On 9/4/15, Todd Biggins <tbiggins at gmail.com> wrote:
> > Thanks Richard!
> >
> > So if we remove the remote machine and focus on the same host - running
> as
> > a separate process based on System.Data.SQLite
> > can I safely run a SELECT query without harming the database integrity or
> > the 3rd party app writing to the database?
>
> That's all you need to do.
>
> There is one corner case: If the main application is writing
> extensively and the secondary reader app does a long-running read
> transaction, the read transaction can prevent the main application
> from resetting the WAL file. This can cause the WAL file to grow very
> large. Everything should still work - it will just use more disk
> space. The WAL will reset automatically once the read transaction
> completes.
>
> --
> D. Richard Hipp
> drh at sqlite.org
>
>
> ------------------------------
>
> Message: 4
> Date: Fri, 04 Sep 2015 20:22:36 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Changes to create index on trunk is breaking old
> code
> Message-ID: <55E9E16C-000007A8 at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> Hello again !
>
> Although this problem is now fixed on trunk maybe would be a good idea to
> issue warnings to prevent new code to use this flaws ?
>
> Maybe sqlite3 executable could emit warnings to stderr when it finds bad
> usage on a database for the problems that we already know, like this one ?
>
> ?
>
> Example:
>
> sqlite3 a_db_that_uses_malformed_sql.db
>
> -- warning
>
> -- use of single quotes on field names are not standard
>
> -- the_index_table_has_the_problem
>
> sqlite3>
>
> ?
>
> Cheers !
>
> ?
> > Fri Sep 04 2015 11:40:11 am CEST CEST from "Domingo Alvarez Duarte"
> ><sqlite-mail at dev.dadbiz.es> Subject: [sqlite] Changes to create index on
> >trunk is breaking old code
> >
> > Hello !
> >
> > I'm testing sqlite3 trunk on existing code and noticed that the changes
> to
> > "create index" using expressions/functions is breaking on old databases
> >
> > It seems that because I used the field name single quotes now it's
> > considering it an expression.
> >
> > On an old database I have the following to create an index:
> >
> > CREATE INDEX companies_number_idx ON companies ('number');
> >
> > ?
> >
> > Now if I try to do anything on that database with sqlite3 trunk I get
> this
> > error message:
> >
> > malformed database schema (companies_number_idx) - indexes on expressions
> >not
> > yet supported
> >
> > ?
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
> >
>
>
>
> ?
>
>
> ------------------------------
>
> Message: 5
> Date: Fri, 4 Sep 2015 12:06:13 -0700
> From: "Joe Mistachkin" <sqlite at mistachkin.com>
> To: "'General Discussion of SQLite Database'"
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Using |DataDirectory| in connection string (.NET
> with System.Data.SQLite)
> Message-ID: <5D592B9A0FE5434C89B6A746D1390518 at LACHRYMOSE>
> Content-Type: text/plain; charset="us-ascii"
>
>
> Lee Gray wrote:
> >
> > How do they make use of the connection string |DataDirectory| macro?
> >
>
> It is expanded when the connection is opened. It will either be replaced
> with the per-AppDomain "DataDirectory" datum or the base directory of the
> AppDomain.
>
> >
> > I've found lots of references online showing that it is indeed used,
> > but I haven't found how to extract the file path from it at runtime.
> >
>
> Currently, there is no easy way to do that using only publically accessible
> parts of System.Data.SQLite; however, this limitation will be addressed in
> the next release.
>
> --
> Joe Mistachkin
>
>
>
> ------------------------------
>
> Message: 6
> Date: Fri, 04 Sep 2015 22:09:05 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] First test of json and index expressions, not so
> good
> Message-ID: <55E9FA61-000007AC at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> 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 ?
>
> 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();
>
> ________
>
>
> ------------------------------
>
> Message: 7
> Date: Fri, 04 Sep 2015 22:17:56 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] First test of json and index expressions, not so
> good
> Message-ID: <55E9FC74-000007AF at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> Hello !
>
> Here is the database dump with 50 records:
>
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE json_tbl(id integer primary key, json text collate nocase);
> 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"}');
> INSERT INTO "json_tbl" VALUES(11,'{"id" : 11, "value" : "the_value_11"}');
> INSERT INTO "json_tbl" VALUES(12,'{"id" : 12, "value" : "the_value_12"}');
> INSERT INTO "json_tbl" VALUES(13,'{"id" : 13, "value" : "the_value_13"}');
> INSERT INTO "json_tbl" VALUES(14,'{"id" : 14, "value" : "the_value_14"}');
> INSERT INTO "json_tbl" VALUES(15,'{"id" : 15, "value" : "the_value_15"}');
> INSERT INTO "json_tbl" VALUES(16,'{"id" : 16, "value" : "the_value_16"}');
> INSERT INTO "json_tbl" VALUES(17,'{"id" : 17, "value" : "the_value_17"}');
> INSERT INTO "json_tbl" VALUES(18,'{"id" : 18, "value" : "the_value_18"}');
> INSERT INTO "json_tbl" VALUES(19,'{"id" : 19, "value" : "the_value_19"}');
> INSERT INTO "json_tbl" VALUES(20,'{"id" : 20, "value" : "the_value_20"}');
> INSERT INTO "json_tbl" VALUES(21,'{"id" : 21, "value" : "the_value_21"}');
> INSERT INTO "json_tbl" VALUES(22,'{"id" : 22, "value" : "the_value_22"}');
> INSERT INTO "json_tbl" VALUES(23,'{"id" : 23, "value" : "the_value_23"}');
> INSERT INTO "json_tbl" VALUES(24,'{"id" : 24, "value" : "the_value_24"}');
> INSERT INTO "json_tbl" VALUES(25,'{"id" : 25, "value" : "the_value_25"}');
> INSERT INTO "json_tbl" VALUES(26,'{"id" : 26, "value" : "the_value_26"}');
> INSERT INTO "json_tbl" VALUES(27,'{"id" : 27, "value" : "the_value_27"}');
> INSERT INTO "json_tbl" VALUES(28,'{"id" : 28, "value" : "the_value_28"}');
> INSERT INTO "json_tbl" VALUES(29,'{"id" : 29, "value" : "the_value_29"}');
> INSERT INTO "json_tbl" VALUES(30,'{"id" : 30, "value" : "the_value_30"}');
> INSERT INTO "json_tbl" VALUES(31,'{"id" : 31, "value" : "the_value_31"}');
> INSERT INTO "json_tbl" VALUES(32,'{"id" : 32, "value" : "the_value_32"}');
> INSERT INTO "json_tbl" VALUES(33,'{"id" : 33, "value" : "the_value_33"}');
> INSERT INTO "json_tbl" VALUES(34,'{"id" : 34, "value" : "the_value_34"}');
> INSERT INTO "json_tbl" VALUES(35,'{"id" : 35, "value" : "the_value_35"}');
> INSERT INTO "json_tbl" VALUES(36,'{"id" : 36, "value" : "the_value_36"}');
> INSERT INTO "json_tbl" VALUES(37,'{"id" : 37, "value" : "the_value_37"}');
> INSERT INTO "json_tbl" VALUES(38,'{"id" : 38, "value" : "the_value_38"}');
> INSERT INTO "json_tbl" VALUES(39,'{"id" : 39, "value" : "the_value_39"}');
> INSERT INTO "json_tbl" VALUES(40,'{"id" : 40, "value" : "the_value_40"}');
> INSERT INTO "json_tbl" VALUES(41,'{"id" : 41, "value" : "the_value_41"}');
> INSERT INTO "json_tbl" VALUES(42,'{"id" : 42, "value" : "the_value_42"}');
> INSERT INTO "json_tbl" VALUES(43,'{"id" : 43, "value" : "the_value_43"}');
> INSERT INTO "json_tbl" VALUES(44,'{"id" : 44, "value" : "the_value_44"}');
> INSERT INTO "json_tbl" VALUES(45,'{"id" : 45, "value" : "the_value_45"}');
> INSERT INTO "json_tbl" VALUES(46,'{"id" : 46, "value" : "the_value_46"}');
> INSERT INTO "json_tbl" VALUES(47,'{"id" : 47, "value" : "the_value_47"}');
> INSERT INTO "json_tbl" VALUES(48,'{"id" : 48, "value" : "the_value_48"}');
> INSERT INTO "json_tbl" VALUES(49,'{"id" : 49, "value" : "the_value_49"}');
> CREATE INDEX json_tbl_idx on json_tbl(json_extract(json, '$.value'));
> COMMIT;
>
>
>
> ?
>
>
> ------------------------------
>
> Message: 8
> Date: Fri, 04 Sep 2015 22:40:31 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] First test of json and index expressions, not so
> good
> Message-ID: <55EA01BF-000007B2 at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> Hello again !
>
> Well it's slow partly because of the linear scan but there are not too many
> records 5000.
>
> I also tried with "indexed by" but:
>
> _______
>
> sql = "select? json_extract(json, '$.value') AS val? from json_tbl indexed
> by json_tbl_idx where val = ?;";
> showPlan();
>
> ?
>
> AN ERROR HAS OCCURED [no query solution]
>
> _______
>
> Cheers !
>
> ?
>
>
> ------------------------------
>
> Message: 9
> Date: Sat, 05 Sep 2015 00:12:47 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Second test of json and index expressions, not so
> good
> Message-ID: <55EA175F-000007B5 at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> 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();
>
> __________
>
>
> ------------------------------
>
> Message: 10
> Date: Fri, 4 Sep 2015 22:17:12 +0000
> From: Lee Gray <Lee.Gray at morrisdickson.com>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Using |DataDirectory| in connection string (.NET
> with System.Data.SQLite)
> Message-ID:
> <1A370B89C093614D8C2AEC1A7DA09B1D2BE056B6 at MDEX01.morrisdickson.com
> >
> Content-Type: text/plain; charset="us-ascii"
>
> Great, thanks!
>
> -----Original Message-----
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Joe Mistachkin
> Sent: Friday, September 04, 2015 2:06 PM
> To: 'General Discussion of SQLite Database' <
> sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Using |DataDirectory| in connection string (.NET
> with System.Data.SQLite)
>
>
> Lee Gray wrote:
> >
> > How do they make use of the connection string |DataDirectory| macro?
> >
>
> It is expanded when the connection is opened. It will either be replaced
> with the per-AppDomain "DataDirectory" datum or the base directory of the
> AppDomain.
>
> >
> > I've found lots of references online showing that it is indeed used,
> > but I haven't found how to extract the file path from it at runtime.
> >
>
> Currently, there is no easy way to do that using only publically
> accessible parts of System.Data.SQLite; however, this limitation will be
> addressed in the next release.
>
> --
> Joe Mistachkin
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ------------------------------
>
> Message: 11
> Date: Sat, 05 Sep 2015 00:22:43 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Third test of json and index expressions, now it
> works
> Message-ID: <55EA19B3-000007B9 at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> 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
> (<expr>=?)
> 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();
>
> ________
>
>
> ------------------------------
>
> Message: 12
> Date: Sat, 5 Sep 2015 00:35:08 -0400
> From: Richard Hipp <drh at sqlite.org>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Third test of json and index expressions, now it
> works
> Message-ID:
> <CALwJ=
> MydSzzA7mLNRujDk_VRLqM8jATEK-tdmL0sbVWF0djJwA at mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> On 9/4/15, Domingo Alvarez Duarte <sqlite-mail at dev.dadbiz.es> 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
>
>
> ------------------------------
>
> Message: 13
> Date: Sat, 5 Sep 2015 09:14:34 +0100
> From: Simon Slavin <slavins at bigfraud.org>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Third test of json and index expressions, now it
> works
> Message-ID: <794EDDB2-666C-4AE1-9D81-712E54C66A83 at bigfraud.org>
> Content-Type: text/plain; charset=us-ascii
>
>
> On 5 Sep 2015, at 5:35am, Richard Hipp <drh at sqlite.org> 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.
>
>
> ------------------------------
>
> Message: 14
> Date: Sat, 05 Sep 2015 11:07:22 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Third test of json and index expressions, now it
> works
> Message-ID: <55EAB0CA-000007BF at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> 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 !
>
>
> ------------------------------
>
> Message: 15
> Date: Sat, 5 Sep 2015 10:16:26 +0100
> From: Simon Slavin <slavins at bigfraud.org>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Third test of json and index expressions, now it
> works
> Message-ID: <177BA274-8ABF-45CD-B895-35B96E7EB689 at bigfraud.org>
> Content-Type: text/plain; charset=us-ascii
>
>
> On 5 Sep 2015, at 10:07am, Domingo Alvarez Duarte <
> sqlite-mail at dev.dadbiz.es> 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.
>
> ------------------------------
>
> Message: 16
> Date: Sat, 05 Sep 2015 11:29:06 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Third test of json and index expressions, now it
> works
> Message-ID: <55EAB5E2-000007C3 at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> 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 (<expr>=?)
> 0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (<expr>=?)
> 0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (<expr>=?)
> 0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (<expr>=? AND rowid>?)
> 0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (<expr>=? AND rowid>?)
> 0|0|0|SEARCH TABLE json_tbl USING INDEX json_tbl_idx (<expr>=?)
>
> __________
>
> __________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" <
> drh at sqlite.org>
> >Subject: Re: [sqlite] Third test of json and index expressions, now it
> works
> >
> > On 9/4/15, Domingo Alvarez Duarte <sqlite-mail at dev.dadbiz.es> 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
> >
> >
> >
>
>
>
> ?
>
>
> ------------------------------
>
> Message: 17
> Date: Sat, 05 Sep 2015 11:37:34 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Cc: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Third test of json and index expressions, now it
> works
> Message-ID: <55EAB7DE-000007C6 at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> 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"
> ><slavins at bigfraud.org> Subject: Re: [sqlite] Third test of json and
> index
> >expressions, now it works
> >
> > On 5 Sep 2015, at 10:07am, Domingo Alvarez Duarte
> ><sqlite-mail at dev.dadbiz.es> 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
> >
> >
> >
>
>
>
> ?
>
>
> ------------------------------
>
> Message: 18
> Date: Sat, 05 Sep 2015 11:37:34 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Cc: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Third test of json and index expressions, now it
> works
> Message-ID: <55EAB7DE-000007C6 at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> 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"
> ><slavins at bigfraud.org> Subject: Re: [sqlite] Third test of json and
> index
> >expressions, now it works
> >
> > On 5 Sep 2015, at 10:07am, Domingo Alvarez Duarte
> ><sqlite-mail at dev.dadbiz.es> 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
> >
> >
> >
>
>
>
> ?
>
>
> ------------------------------
>
> Message: 19
> Date: Sat, 05 Sep 2015 11:57:53 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Third test of json and index expressions, now it
> works
> Message-ID: <55EABCA1-000007CB at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> 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 (<expr>=?)
> 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 (<expr>=?)
>
> _________
>
> _________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" <
> drh at sqlite.org>
> >Subject: Re: [sqlite] Third test of json and index expressions, now it
> works
> >
> > On 9/4/15, Domingo Alvarez Duarte <sqlite-mail at dev.dadbiz.es> 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
> >
> >
> >
>
>
>
> ?
>
>
> ------------------------------
>
> Message: 20
> Date: Sat, 5 Sep 2015 09:03:40 -0300
> From: Luiz Am?rico <brandkarl at gmail.com>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] First test of json and index expressions, not so
> good
> Message-ID:
> <CAFCCxUbo4EcrSh30PzGFT7Xy=
> es6X9cAFC9fzb7Kht_QSMdyfw at mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> Em 04/09/2015 17:09, "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> 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
>
>
> ------------------------------
>
> Message: 21
> Date: Sat, 05 Sep 2015 14:08:44 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] UNQL as an extension to sqlite3 and now could use
> index expressions
> Message-ID: <55EADB4C-000007CF at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> 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();
>
> ________
>
>
> ------------------------------
>
> Message: 22
> Date: Sat, 05 Sep 2015 14:11:29 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] First test of json and index expressions, not so
> good
> Message-ID: <55EADBF1-000007D2 at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> 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"
> ><brandkarl at gmail.com> Subject: Re: [sqlite] First test of json and index
> >expressions, not so good
> >
> > Em 04/09/2015 17:09, "Domingo Alvarez Duarte" <
> sqlite-mail at dev.dadbiz.es>
> > 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
> >
> >
> >
>
>
>
> ?
>
>
> ------------------------------
>
> Message: 23
> Date: Sat, 05 Sep 2015 14:14:41 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] UNQL as an extension to sqlite3 and now could
> use index expressions
> Message-ID: <55EADCB1-000007D5 at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> 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;
>
> ___________
>
> ?
>
>
> ------------------------------
>
> Message: 24
> Date: Sat, 5 Sep 2015 10:14:55 -0400
> From: Richard Hipp <drh at sqlite.org>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] First test of json and index expressions, not so
> good
> Message-ID:
> <CALwJ=MxiOw7ZATT_hJ38Rxur=rzBmMV7h3MUp=
> hzEOatuYFy_w at mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> On 9/5/15, Luiz Am?rico <brandkarl at gmail.com> 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
>
>
> ------------------------------
>
> Message: 25
> Date: Sat, 5 Sep 2015 07:19:00 -0700
> From: Darko Volaric <lists at darko.org>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Third test of json and index expressions, now it
> works
> Message-ID:
> <CAM3tuSEwwFFQ4MT4VtXRqg_JVrMYFptObwTZjJgz0OwVnXF=
> YA at mail.gmail.com>
> Content-Type: text/plain; charset=UTF-8
>
> 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 <drh at sqlite.org> wrote:
>
> > On 9/4/15, Domingo Alvarez Duarte <sqlite-mail at dev.dadbiz.es> 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
> >
>
>
> ------------------------------
>
> Message: 26
> Date: Sat, 05 Sep 2015 16:45:58 +0200
> From: "Domingo Alvarez Duarte" <sqlite-mail at dev.dadbiz.es>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Third test of json and index expressions, now it
> works
> Message-ID: <55EB0026-000007DA at dev.dadbiz.es>
> Content-Type: text/plain; charset=utf-8
>
> 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"
> ><lists at darko.org> 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 <drh at sqlite.org> wrote:
> >
> >
> >>On 9/4/15, Domingo Alvarez Duarte <sqlite-mail at dev.dadbiz.es> 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
> >
> >
> >
>
>
>
> ?
>
>
> ------------------------------
>
> Message: 27
> Date: Sat, 5 Sep 2015 16:49:20 +0100
> From: Simon Slavin <slavins at bigfraud.org>
> To: General Discussion of SQLite Database
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Third test of json and index expressions, now it
> works
> Message-ID: <BFF57539-1460-4DB9-A298-F5EA73E73B2A at bigfraud.org>
> Content-Type: text/plain; charset=us-ascii
>
>
> On 5 Sep 2015, at 3:19pm, Darko Volaric <lists at darko.org> 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.
>
> ------------------------------
>
> Message: 28
> Date: Sat, 05 Sep 2015 09:54:54 -0600
> From: "Keith Medcalf" <kmedcalf at dessus.com>
> To: "General Discussion of SQLite Database"
> <sqlite-users at mailinglists.sqlite.org>
> Subject: Re: [sqlite] Third test of json and index expressions, now it
> works
> Message-ID: <7766ac1716057545bbc342b1f27a083b at mail.dessus.com>
> Content-Type: text/plain; charset="us-ascii"
>
>
> > > 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-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> End of sqlite-users Digest, Vol 93, Issue 5
> *******************************************
>
