[sqlite] Dumb statement question...
On 6/8/15, Ward Willats wrote: > Can a prepared statement have more than 1 statement in it (and bind > parameters across the whole thing)? > > Something like: > > prepare_v2( h, "one statement ? ; two statement ?", -1, , NULL ) No. A prepared statement is *one* SQL statement, not more than one. > > bind_int( s, 1, ) > bind_int( s, 2, ) > > (I ask because I am getting a SQLITE_RANGE (25) error just after a ";" in a > compound statement I've compiled with a param I've bound earlier...) > > Thanks, > > -- Ward > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] User-defined types -- in Andl
On Mon, 8 Jun 2015 15:28:11 +1000 wrote: > Thanks for pointing it out, but I knew that the best way to show off a > language is with examples. That's why there are nine sample Andl > scripts comprising dozens of individual examples in the Samples > folder. My guess is if that you're asking me to write examples, the > real lesson is that I didn't make them easy enough to find. > > I have a formal grammar, but I don't expect anyone to read that. More > and better examples is the way to go. No, a big bold No. If I want implement your language in some product I need the formal grammar. Learn by example means learn white rules (the dos), I need to know the black rules too (the don'ts) to get full knowledge of the language. > Regards > David M Bennett FACS > > Andl - A New Database Language - andl.org --- --- Eduardo Morras
[sqlite] Dumb statement question...
Can a prepared statement have more than 1 statement in it (and bind parameters across the whole thing)? Something like: prepare_v2( h, "one statement ? ; two statement ?", -1, , NULL ) bind_int( s, 1, ) bind_int( s, 2, ) (I ask because I am getting a SQLITE_RANGE (25) error just after a ";" in a compound statement I've compiled with a param I've bound earlier...) Thanks, -- Ward
[sqlite] User-defined types -- in Andl
There are nine sample scripts and dozens of examples in the Samples folder. Obviously that's the area I need to work on. Regards David M Bennett FACS MD Powerflex Corporation, creators of PFXplus To contact us, please call +61-3-9548-9114 or go to www.pfxcorp.com/contact.htm -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Monday, 8 June 2015 12:26 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] User-defined types -- in Andl On 8 Jun 2015, at 3:14am, wrote: > I suggest you just read the samples off GitHub. They cover the entire > language. Download the binary, run them and you see what they do. Sorry but no. You have it reversed. Your code isn't going to touch my computer unless you have already convinced me that it's worth me investigating it. Just describe a few examples on your site. If you can show me "Look, it's a whole page in SQL but only half a page in Andl." or some similar advantage then so much the better. Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] User-defined types
Here is my best effort at translating this query into Andl.
(
source_packages [?(release =~
'^(sid|stretch|jessie|wheezy|squeeze)$' )
{ name, release, subrelease, version }] join
source_package_status [?(bug_name =~ '^(CVE-|TEMP-)')
{ rowid:=package, bug_name, vulnerable, urgency }] join
bugs [{ bug_name := name }] join
nvd_data [{ bug_name:=cve_name, cve_desc, range_remote }] join
debian_cve [{ bug, bug_name }] join
package_notes [{ rls := release }] [?(rls = release or
rls = '' and and fixed_version <> '')
{ name := package, bug_name, fixed_version }] join
package_notes_nodsa [{ name := package, comment }]
) [%(name, bug_name, release, sub_release)]
Please note:
1. Andl uses only natural joins and renaming. Since I don't have the
schema there could be name clashes.
2. Andl will remove any nulls or duplicates (pure relational model
only)
3. Andl uses regex rather than LIKE.
The Andl code is somewhat shorter than SQL, and it's more regular. The real
benefit comes when this query (or parts of it) are reused in combination
with others, because Andl is composable. That doesn't show up when it's just
emulating an SQL query.
If this database is available I would appreciate the opportunity to try this
out for real.
Regards
David M Bennett FACS
_
Andl - A New Database Language - andl.org
-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard
Hipp
Sent: Friday, 5 June 2015 7:27 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types
On 6/4/15, Darko Volaric < lists at darko.org>
wrote:
> My point about JSON, etc is that there is no reason not to use that as
> a query language if that makes it easier. If your system is efficient
> with JSON, why not accept a query that is formatted as JSON? It's not
> semantically different to SQL syntax. Here's an example (with a
> roughly JSON notation):
>
> {
> operation: "insert"
> table: "blah"
> columns: ["a", "b", "c"]
> values: [1.3, 2.0, 3.1]
> on-conflict: "replace"
> }
>
> That is equivalent to an INSERT SQL statement, but why form that SQL
> string, possibly using memory and time, when your system can spit out
> JSON (or whatever) effortlessly?
What is the JSON equivalent to the query shown below? Can you honestly say
that the JSON equivalent (whatever it looks like) is somehow easier to
generate, read, parse, and/or debug than the SQL?
SELECT
sp.name, st.bug_name,
(SELECT cve_desc FROM nvd_data WHERE cve_name = st.bug_name),
(SELECT debian_cve.bug FROM debian_cve
WHERE debian_cve.bug_name = st.bug_name
ORDER BY debian_cve.bug),
sp.release,
sp.subrelease,
sp.version,
(SELECT pn.fixed_version FROM package_notes AS pn
WHERE pn.bug_name = st.bug_name
AND pn.package = sp.name
AND(pn.release = sp.release OR (pn.release = '' AND fixed_version !=
''))),
st.vulnerable,
st.urgency,
(SELECT range_remote FROM nvd_data WHERE cve_name = st.bug_name),
(SELECT comment FROM package_notes_nodsa AS nd
WHERE nd.package = sp.name AND nd.release = sp.release
AND nd.bug_name = st.bug_name) AS nodsa FROM
source_package_status AS st,
source_packages AS sp, bugs
WHERE
sp.rowid = st.package
AND st.bug_name = bugs.name
AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )
AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release =
'jessie'
OR sp.release = 'wheezy' OR sp.release = 'squeeze' ) ORDER BY
sp.name, st.bug_name, sp.release, sp.subrelease;
--
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] User-defined types
Here is my best effort at translating this query into Andl.
(
source_packages [?(release =~
'^(sid|stretch|jessie|wheezy|squeeze)$' )
{ name, release, subrelease, version }] join
source_package_status [?(bug_name =~ '^(CVE-|TEMP-)')
{ rowid:=package, bug_name, vulnerable, urgency }] join
bugs [{ bug_name := name }] join
nvd_data [{ bug_name:=cve_name, cve_desc, range_remote }] join
debian_cve [{ bug, bug_name }] join
package_notes [{ rls := release }] [?(rls = release or
rls = '' and and fixed_version <> '')
{ name := package, bug_name, fixed_version }] join
package_notes_nodsa [{ name := package, comment }]
) [%(name, bug_name, release, sub_release)]
Please note:
1. Andl uses only natural joins and renaming. Since I don't have the
schema there could be name clashes.
2. Andl will remove any nulls or duplicates (pure relational model
only)
3. Andl uses regex rather than LIKE.
The Andl code is somewhat shorter than SQL, and it's more regular. The real
benefit comes when this query (or parts of it) are reused in combination
with others, because Andl is composable. That doesn't show up when it's just
emulating an SQL query.
If this database is available I would appreciate the opportunity to try this
out for real.
Regards
David M Bennett FACS
_
Andl - A New Database Language - andl.org
-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Richard
Hipp
Sent: Friday, 5 June 2015 7:27 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] User-defined types
On 6/4/15, Darko Volaric < lists at darko.org>
wrote:
> My point about JSON, etc is that there is no reason not to use that as
> a query language if that makes it easier. If your system is efficient
> with JSON, why not accept a query that is formatted as JSON? It's not
> semantically different to SQL syntax. Here's an example (with a
> roughly JSON notation):
>
> {
> operation: "insert"
> table: "blah"
> columns: ["a", "b", "c"]
> values: [1.3, 2.0, 3.1]
> on-conflict: "replace"
> }
>
> That is equivalent to an INSERT SQL statement, but why form that SQL
> string, possibly using memory and time, when your system can spit out
> JSON (or whatever) effortlessly?
What is the JSON equivalent to the query shown below? Can you honestly say
that the JSON equivalent (whatever it looks like) is somehow easier to
generate, read, parse, and/or debug than the SQL?
SELECT
sp.name, st.bug_name,
(SELECT cve_desc FROM nvd_data WHERE cve_name = st.bug_name),
(SELECT debian_cve.bug FROM debian_cve
WHERE debian_cve.bug_name = st.bug_name
ORDER BY debian_cve.bug),
sp.release,
sp.subrelease,
sp.version,
(SELECT pn.fixed_version FROM package_notes AS pn
WHERE pn.bug_name = st.bug_name
AND pn.package = sp.name
AND(pn.release = sp.release OR (pn.release = '' AND fixed_version !=
''))),
st.vulnerable,
st.urgency,
(SELECT range_remote FROM nvd_data WHERE cve_name = st.bug_name),
(SELECT comment FROM package_notes_nodsa AS nd
WHERE nd.package = sp.name AND nd.release = sp.release
AND nd.bug_name = st.bug_name) AS nodsa FROM
source_package_status AS st,
source_packages AS sp, bugs
WHERE
sp.rowid = st.package
AND st.bug_name = bugs.name
AND ( st.bug_name LIKE 'CVE-%' OR st.bug_name LIKE 'TEMP-%' )
AND ( sp.release = 'sid' OR sp.release = 'stretch' OR sp.release =
'jessie'
OR sp.release = 'wheezy' OR sp.release = 'squeeze' ) ORDER BY
sp.name, st.bug_name, sp.release, sp.subrelease;
--
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] User-defined types -- in Andl
Thanks for pointing it out, but I knew that the best way to show off a language is with examples. That's why there are nine sample Andl scripts comprising dozens of individual examples in the Samples folder. My guess is if that you're asking me to write examples, the real lesson is that I didn't make them easy enough to find. I have a formal grammar, but I don't expect anyone to read that. More and better examples is the way to go. Regards David M Bennett FACS Andl - A New Database Language - andl.org -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Monday, 8 June 2015 12:23 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] User-defined types -- in Andl On 8 Jun 2015, at 3:12am, wrote: > Is there a PDF? No, but that's a good idea. Did you check out the samples? > They cover the entire language, and I could turn those into a PDF much > faster than a real language. It would take about a month to write a > decent tutorial and reference, but that might make a good shortcut. My guess is that, if your objective is to attract readers, your time will be best spent composing a few examples. Formal grammar will be needed in the long run but only the real geeks will read it. Many people can read a few examples and figure out whether it's worth investigating the language further, whereas a formal grammar or a full tutorial would take more time to read than they would be willing to invest. Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual Table query - why isn't SQLite using my indexes?
Surely enough, this SQL: SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title, t4.category_id, t5."name" FROM rental t2 LEFT OUTER JOIN inventory t1 ON ( t1.inventory_id = t2.inventory_id ) LEFT OUTER JOIN film t3 ON ( t3.film_id = t1.film_id ) LEFT OUTER JOIN film_category t4 ON ( t4.film_id = t1.film_id ) LEFT OUTER JOIN category t5 ON ( t5.category_id = t4.category_id ) ; generates the desired xBestIndex call: jmpvtab BEST INDEX: Table: inventory nConstraints: 3 CONST[0]: 0 (inventory_id) = Usable CONST[1]: 1 (film_id) = Usable CONST[2]: 1 (film_id) = Usable allowing the "first constraint" heuristic to be successful. So, in the case where my application is generating SQL, I can fix it so that the ON is generated in the helpful order. But I also let users type in their own SQL. Would it be too much to ask SQLite to determine vs. based on context rather than inferring based on position? I'm looking at SQL In a Nutshell, 3rd Edition, page 353, and the JOIN syntax example shows the ON statements in the order SQLite dislikes. Thanks very much! Eric -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Monday, June 08, 2015 10:21 AM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes? Can you try changing LHS and RHS in the first ON expression? The older, larger query has inventory_id on the LHS and film_id on the RHS. Now you have all fields on the RHS. It would seem the QP may be inferring LEFT OUTER JOIN ON ( = ) And placing the fields first in the argument list And adding the fields only for symmetry reasons (they can never be used, because they point the "wrong way") -Urspr?ngliche Nachricht- Von: Eric Hill [mailto:Eric.Hill at jmp.com] Gesendet: Montag, 08. Juni 2015 15:55 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes? Revisiting a thread from about 3 weeks back, I have another xBestIndex puzzler. The example query from that thread was: SELECT t1.rental_date, t1.inventory_id, t8.film_id, t5.title AS Title, t3."name" AS Category, t4.customer_id, t4.store_id, t4.email, t5.length FROM rental t1 LEFT OUTER JOIN inventory t8 ON ( t8.inventory_id = t1.inventory_id ) LEFT OUTER JOIN customer t4 ON ( t4.customer_id = t1.customer_id ) LEFT OUTER JOIN film_category t7 ON ( t7.film_id = t8.film_id ) LEFT OUTER JOIN film t5 ON ( t5.film_id = t8.film_id ) LEFT OUTER JOIN category t3 ON ( t3.category_id = t7.category_id ) LEFT OUTER JOIN film_actor t6 ON ( t6.film_id = t5.film_id ) LEFT OUTER JOIN actor t2 ON ( t2.actor_id = t6.actor_id ) ; The "money" call to xBestIndex in that case looked like this: jmpvtab BEST INDEX: Table: inventory nConstraints: 3 CONST[0]: 0 (inventory_id) = Usable CONST[1]: 1 (film_id) = Usable CONST[2]: 1 (film_id) = Usable In response to this, I originally promised to create an index using all 3 constraints (two if which are the same), but SQLite rejected that and did a table scan instead, because it really only needed an index based on inventory_id. So the heuristic we ended up with, which did fix this specific query, was (from Gunter): > If you know nothing about a table except for the names of the fields > and the number of rows, then you are best off choosing the first > constraint only. (rows = cost = log n) Well, now I have a counter-example. This time, the SQL looks like this: SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title, t4.category_id, t5."name" FROM rental t2 LEFT OUTER JOIN inventory t1 ON ( t2.inventory_id = t1.inventory_id ) LEFT OUTER JOIN film t3 ON ( t3.film_id = t1.film_id ) LEFT OUTER JOIN film_category t4 ON ( t4.film_id = t1.film_id ) LEFT OUTER JOIN category t5 ON ( t5.category_id = t4.category_id ) ; For whatever reason, the "money" call to xBestIndex in this case looks like this: jmpvtab BEST INDEX: Table: inventory nConstraints: 3 CONST[0]: 1 (film_id) = Usable CONST[1]: 1 (film_id) = Usable CONST[2]: 0 (inventory_id) = Usable The order of the constraints is different! So, using the "first constraint" heuristic, I commit to indexing based on film_id, but indexing inventory on film_id not helpful for this query. SQLite sees that inventory is indexed on film_id and decides to use table scan for inventory, and it's game over. If SQLite calls to xBestIndex do not in some way convey
[sqlite] Virtual Table query - why isn't SQLite using my indexes?
Can you try changing LHS and RHS in the first ON expression? The older, larger query has inventory_id on the LHS and film_id on the RHS. Now you have all fields on the RHS. It would seem the QP may be inferring LEFT OUTER JOIN ON ( = ) And placing the fields first in the argument list And adding the fields only for symmetry reasons (they can never be used, because they point the "wrong way") -Urspr?ngliche Nachricht- Von: Eric Hill [mailto:Eric.Hill at jmp.com] Gesendet: Montag, 08. Juni 2015 15:55 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Virtual Table query - why isn't SQLite using my indexes? Revisiting a thread from about 3 weeks back, I have another xBestIndex puzzler. The example query from that thread was: SELECT t1.rental_date, t1.inventory_id, t8.film_id, t5.title AS Title, t3."name" AS Category, t4.customer_id, t4.store_id, t4.email, t5.length FROM rental t1 LEFT OUTER JOIN inventory t8 ON ( t8.inventory_id = t1.inventory_id ) LEFT OUTER JOIN customer t4 ON ( t4.customer_id = t1.customer_id ) LEFT OUTER JOIN film_category t7 ON ( t7.film_id = t8.film_id ) LEFT OUTER JOIN film t5 ON ( t5.film_id = t8.film_id ) LEFT OUTER JOIN category t3 ON ( t3.category_id = t7.category_id ) LEFT OUTER JOIN film_actor t6 ON ( t6.film_id = t5.film_id ) LEFT OUTER JOIN actor t2 ON ( t2.actor_id = t6.actor_id ) ; The "money" call to xBestIndex in that case looked like this: jmpvtab BEST INDEX: Table: inventory nConstraints: 3 CONST[0]: 0 (inventory_id) = Usable CONST[1]: 1 (film_id) = Usable CONST[2]: 1 (film_id) = Usable In response to this, I originally promised to create an index using all 3 constraints (two if which are the same), but SQLite rejected that and did a table scan instead, because it really only needed an index based on inventory_id. So the heuristic we ended up with, which did fix this specific query, was (from Gunter): > If you know nothing about a table except for the names of the fields > and the number of rows, then you are best off choosing the first > constraint only. (rows = cost = log n) Well, now I have a counter-example. This time, the SQL looks like this: SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title, t4.category_id, t5."name" FROM rental t2 LEFT OUTER JOIN inventory t1 ON ( t2.inventory_id = t1.inventory_id ) LEFT OUTER JOIN film t3 ON ( t3.film_id = t1.film_id ) LEFT OUTER JOIN film_category t4 ON ( t4.film_id = t1.film_id ) LEFT OUTER JOIN category t5 ON ( t5.category_id = t4.category_id ) ; For whatever reason, the "money" call to xBestIndex in this case looks like this: jmpvtab BEST INDEX: Table: inventory nConstraints: 3 CONST[0]: 1 (film_id) = Usable CONST[1]: 1 (film_id) = Usable CONST[2]: 0 (inventory_id) = Usable The order of the constraints is different! So, using the "first constraint" heuristic, I commit to indexing based on film_id, but indexing inventory on film_id not helpful for this query. SQLite sees that inventory is indexed on film_id and decides to use table scan for inventory, and it's game over. If SQLite calls to xBestIndex do not in some way convey which constraints matter and which ones don't, I don't see how I can use virtual tables. Here is the WhereTrace, in case it might help: *** Optimizer Start *** add: * 0.01.00 sqlite_master f 00100 N 0 cost 0,216,200 0 0.01.00 sqlite_master f 00100 N 0 cost 0,216,200 begin solver. (nRowEst=0) New0 cost=216,200 order=0 after round 0 0 cost=216 nrow=200 order=0 begin solver. (nRowEst=201) New0 cost=216,200 order=1 after round 0 0 cost=216 nrow=200 order=1 rev=0x0 Solution nRow=200 ORDERBY=1,0x0 0 0.01.00 sqlite_master f 00100 N 0 cost 0,216,200 *** Optimizer Finished *** *** Optimizer Start *** Solution nRow=1 0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1 *** Optimizer Finished *** *** Optimizer Start *** add: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43 skip: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43 add: * 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180 0 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43 1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180 begin solver. (nRowEst=0) New0 cost=271, 43 order=0 Update 1 cost=216,180 order=0 was 0 cost=271, 43 order=0 after round 0 1 cost=216 nrow=180 order=0 begin solver. (nRowEst=181) sort cost=239 (1/1) increases cost 271 to 272 New0 cost=272, 43 order=0 Update 1 cost=216,180
[sqlite] Virtual Table query - why isn't SQLite using my indexes?
Revisiting a thread from about 3 weeks back, I have another xBestIndex puzzler. The example query from that thread was: SELECT t1.rental_date, t1.inventory_id, t8.film_id, t5.title AS Title, t3."name" AS Category, t4.customer_id, t4.store_id, t4.email, t5.length FROM rental t1 LEFT OUTER JOIN inventory t8 ON ( t8.inventory_id = t1.inventory_id ) LEFT OUTER JOIN customer t4 ON ( t4.customer_id = t1.customer_id ) LEFT OUTER JOIN film_category t7 ON ( t7.film_id = t8.film_id ) LEFT OUTER JOIN film t5 ON ( t5.film_id = t8.film_id ) LEFT OUTER JOIN category t3 ON ( t3.category_id = t7.category_id ) LEFT OUTER JOIN film_actor t6 ON ( t6.film_id = t5.film_id ) LEFT OUTER JOIN actor t2 ON ( t2.actor_id = t6.actor_id ) ; The "money" call to xBestIndex in that case looked like this: jmpvtab BEST INDEX: Table: inventory nConstraints: 3 CONST[0]: 0 (inventory_id) = Usable CONST[1]: 1 (film_id) = Usable CONST[2]: 1 (film_id) = Usable In response to this, I originally promised to create an index using all 3 constraints (two if which are the same), but SQLite rejected that and did a table scan instead, because it really only needed an index based on inventory_id. So the heuristic we ended up with, which did fix this specific query, was (from Gunter): > If you know nothing about a table except for the names of the fields > and the number of rows, then you are best off choosing > the first constraint only. (rows = cost = log n) Well, now I have a counter-example. This time, the SQL looks like this: SELECT t2.rental_id, t2.rental_date, t1.film_id, t3.title, t4.category_id, t5."name" FROM rental t2 LEFT OUTER JOIN inventory t1 ON ( t2.inventory_id = t1.inventory_id ) LEFT OUTER JOIN film t3 ON ( t3.film_id = t1.film_id ) LEFT OUTER JOIN film_category t4 ON ( t4.film_id = t1.film_id ) LEFT OUTER JOIN category t5 ON ( t5.category_id = t4.category_id ) ; For whatever reason, the "money" call to xBestIndex in this case looks like this: jmpvtab BEST INDEX: Table: inventory nConstraints: 3 CONST[0]: 1 (film_id) = Usable CONST[1]: 1 (film_id) = Usable CONST[2]: 0 (inventory_id) = Usable The order of the constraints is different! So, using the "first constraint" heuristic, I commit to indexing based on film_id, but indexing inventory on film_id not helpful for this query. SQLite sees that inventory is indexed on film_id and decides to use table scan for inventory, and it's game over. If SQLite calls to xBestIndex do not in some way convey which constraints matter and which ones don't, I don't see how I can use virtual tables. Here is the WhereTrace, in case it might help: *** Optimizer Start *** add: * 0.01.00 sqlite_master f 00100 N 0 cost 0,216,200 0 0.01.00 sqlite_master f 00100 N 0 cost 0,216,200 begin solver. (nRowEst=0) New0 cost=216,200 order=0 after round 0 0 cost=216 nrow=200 order=0 begin solver. (nRowEst=201) New0 cost=216,200 order=1 after round 0 0 cost=216 nrow=200 order=1 rev=0x0 Solution nRow=200 ORDERBY=1,0x0 0 0.01.00 sqlite_master f 00100 N 0 cost 0,216,200 *** Optimizer Finished *** *** Optimizer Start *** Solution nRow=1 0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1 *** Optimizer Finished *** *** Optimizer Start *** add: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43 skip: * 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43 add: * 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180 0 0.01.00 sqlite_master f 04000 N 1 cost 271,53,43 1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180 begin solver. (nRowEst=0) New0 cost=271, 43 order=0 Update 1 cost=216,180 order=0 was 0 cost=271, 43 order=0 after round 0 1 cost=216 nrow=180 order=0 begin solver. (nRowEst=181) sort cost=239 (1/1) increases cost 271 to 272 New0 cost=272, 43 order=0 Update 1 cost=216,180 order=1 was 0 cost=272, 43 order=0 after round 0 1 cost=216 nrow=180 order=1 rev=0x0 Solution nRow=180 ORDERBY=1,0x0 1 0.01.00 sqlite_master f 00100 N 0 cost 0,216,180 *** Optimizer Finished *** *** Optimizer Start *** Solution nRow=1 0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1 *** Optimizer Finished *** *** Optimizer Start *** Solution nRow=1 0 0.01.00 sqlite_master f 01101 N 1 cost 0,33,1 *** Optimizer Finished *** *** Optimizer Start *** add: * 0.01.00 sqlite_master f 04000 N 1 cost
[sqlite] User-defined types -- in Andl
david at andl.org wrote on Monday, June 08, 2015 9:23 AM > > Ultimately, I don't think it will really matter, because the role of > Andl is to be platform independent. Do you care what your SQL product > is written in? > Absolutely. I wouldn't be using SQLite if it wasn't C/C++, and I suspect that I'm not the only one. It wouldn't even make sense for me to spend time looking at Andl, no matter how good it is. Implementation technology is critical to anyone that embeds SQLite. I'd guess that the SQLite developers' choice to use C was not accidental. Many people are perfectly productive using C/C++. Erik -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message.
[sqlite] Sqlite subquery parsing
Hello, Can you please tell me how Sub queries in select are parsed ? Can you please point out which rule in the parse.y file does the job of parsing this ? Thank you
[sqlite] User-defined types -- in Andl
On 8 Jun 2015, at 6:28am, wrote: > Thanks for pointing it out, but I knew that the best way to show off a > language is with examples. That's why there are nine sample Andl scripts > comprising dozens of individual examples in the Samples folder. My guess is > if that you're asking me to write examples, the real lesson is that I didn't > make them easy enough to find. I will admit that I didn't want to go find the depository before I was sufficiently interested in the language. So the only site I looked through was www.andl.com. If you make one blog entry for each example, then tag them all with the same tag, then have one of your navigation links show all posts with that tag, that should allow you to present the examples to people who are only getting as far as your blog. Simon.
[sqlite] User-defined types -- in Andl
I suggest you just read the samples off GitHub. They cover the entire language. Download the binary, run them and you see what they do. Regards David M Bennett FACS Andl - A New Database Language - andl.org -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Monday, 8 June 2015 4:00 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] User-defined types -- in Andl On 7 Jun 2015, at 6:51pm, Scott Doctor wrote: > Do you have a PDF that explains the language? There are plenty of blog entries which explain the language. I spent more time looking for some examples (I understand better from examples) and eventually found one. Simon. ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] User-defined types -- in Andl
Thank you for your thoughts. You covered quite a few topics. Is there a PDF? No, but that's a good idea. Did you check out the samples? They cover the entire language, and I could turn those into a PDF much faster than a real language. It would take about a month to write a decent tutorial and reference, but that might make a good shortcut. Why a new language? 1. Because although I know over a hundred computer languages and dialects, I don't know one that I could adapt to this job. The only possible candidates are the functional languages (Haskell et al) and they come with too much baggage (for some definition of baggage). 2. Because the aim is to do one thing and do it well. Andl is a small language -- you can learn it all in a few hours. Don't be fooled -- it's amazingly expressive. 3. Productivity. I have written over 100K lines of production C code and I hope I never write another line -- it just takes too long to get stuff done. 4. Why not SQL? Because Andl does what SQL does, but better. It hides the same things, but fixes the flaws and gaps. Regards David M Bennett FACS Andl - A New Database Language - andl.org -Original Message- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Scott Doctor Sent: Monday, 8 June 2015 3:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] User-defined types -- in Andl Do you have a PDF that explains the language? My opinion is that I have seen many languages come and go. Consider general programing languages. C is far superior to just about any language available. In fact the underlying code for most languages is written in C. So the question becomes, why does everyone see a need to keep creating new languages? Beyond the assembler instruction compiler for a new processor, a C compiler is usually the first compiler written for that processor. It is simple and straight forward to do, and I have done so many times. If you look at the evolution of the C language (even considering that bastard child C++) it has changed very little in 35 years, is available for all architectures, properly written code will compile for any architecture unmodified, and it has every hook needed to do any programming task. Many decades ago, an attempt was made to make a more efficient keyboard to replace the QWERTY keyboard. Some of the fledgling computer companies in the 1970's and 80's tried to get them accepted. From a straight technical perspective, they are more efficient. They all failed to be accepted. Consider what is easier. To train a new generation on the old stuff? or to re-train half a dozen generations on the new stuff? Choices are usually made on which is easier now versus the long term benefits later. What I find interesting is how many of these "New" languages are so similar to C. Java, PHP, and such, take the base constructs of C, then add "Special" additions to do what is basically just a C function. They change some syntax to make it more BASIC like, but the general technique still follows C. So why not just use C? In my current project, I debated just writing a bunch of C functions to handle my data. Once I got into it, I realized that beyond simply reading/writing some data structures, the code was getting complex very fast. SQLite lets me offload the low level details and just write a query with SQLite handling the parsing and search. Is it ideal, hardly. But the alternative is much more complicated and not worth the effort. If your data is just a few simple data structures, sure, just write some C code. But the reality is that most well developed programs quickly branch into ever increasing complexity. Regarding SQL, many companies are attempting to replace SQL with their flavor of an interface. Embarcadero (the old Borland) has in their development system a "Universal" database interface to make accessing databases "Universal". The idea being that a database designer just wants their data and does not care about the underlying mechanisms. Wait, that is the entire concept behind every programming language. If programmers cared about the underlying mechanism at every level and just wanted to write the most optimal code possible (which is a far off concept no longer desired for some reason) then all programs would be written in assembler. I used their system for a while. Now I just write the SQL directly and just link in SQLite instead of using Embarcadero's stuff. Although some of their constructs "seemed" to simplify some tasks, the program as a whole was actually more complex. My opinion why SQL has endured is that it actually hides from the programmer the internal complexity required to implement a task. While some of the syntax may be a bit quirky, so is talking to a teenager, but we adapt. If SQL did not do what is needed then people would not use it. The reality is that SQL actually is a well thought out language, even if the syntax can be a bit
[sqlite] SQLite.NET Class Library Documentation Feedback: Limitations
Is the following statement still relevant even with write ahead logging mode of latest sqlite "..timeouts will only occur if one thread is attempting to read while another thread is attempting to write.." Saurabh
[sqlite] User-defined types -- in Andl
On Sun, Jun 7, 2015 at 4:17 AM, wrote: > I've been reading this thread with great interest. It parallels the project > I've been working on: Andl. > > Andl is A New Database Language. > > Andl does what SQL does, but it is not SQL. Andl has been developed as a > fully featured database programming language following the principles set > out by Date and Darwen in The Third Manifesto. It includes a full > implementation of the Relational Model published by E.F. Codd in 1970, an > advanced extensible type system, database updates and other SQL-like > capabilities in a novel and highly expressive syntax. > > The intended role of Andl is to be the implementation language for the data > model of an application. It is already possible to code the business model > of an application in an SQL dialect, but few people do this because of > limitations in SQL. Andl aims to provide a language free of these problems > that works on all these platforms. > > The current implementation on SQLite uses a mixture of generated SQL and a > runtime VM. User-defined types are blobs, which the VM understands. A > future > implementation could generate SQLite VM code directly instead of SQL, which > would save some overhead. > > The website is andl.org. The GitHub project is > https://github.com/davidandl/Andl. It's a work in progress. Any feedback > welcomed. > ?Looks interesting. Too bad it's written in C#. I'm basically a Linux-only guy (use Windows at work under protest, so to speak). Yes, I can use Mono on Linux to compile C# and run it. And I may.? > > Regards > David M Bennett FACS > > Andl - A New Database Language - andl.org > > -- Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. My sister opened a computer store in Hawaii. She sells C shells down by the seashore. If someone tell you that nothing is impossible: Ask him to dribble a football. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] User-defined types -- in Andl
Any properly written documentation on any subject always begins with an executive summary (no more than a few pages), an overview (usually a dozen more pages), then gets into the nitty gritty. Consider if I want you to write a paragraph in Egyptian Hieroglyphics. So I provide you with a few "sample" sentences already written in Egyptian Hieroglyphics. Would you be able to both understand my examples, and write a proper paragraph in Egyptian Hieroglyphics? Regarding a formal definition. That should be the first thing you write when creating something new. That is where the details start to show collisions, issues, problems. To the contrary, when I start using something new, I do not want to sift through 22k pages of text just to get the concept. Very few manuals are written well. You need to be able to explain the entire language in "A Few" pages. A summary of the hieroglyphics. (operators. This is what, a dozen or so symbols) A one or two sentence description of each key word. (e.g. JOIN, SELECT, INSERT,... especially anything new) A one or two sentence explanation for each key word (or symbol) how it relates to the equivalent SQL. Any documentation on any topic should be structured as such. The need for a formal definition is obvious, but is usually used in the same fashion as a dictionary (the printed on paper kind). A few people will read the entire book. But most will just turn to the entry of interest skipping everything else. The trick is being able to find that one word quickly and getting "All" the needed information in a concise deliberate fashion. - Scott Doctor scott at scottdoctor.com - On 6/7/2015 10:28 PM, david at andl.org wrote: > Thanks for pointing it out, but I knew that the best way to show off a > language is with examples. That's why there are nine sample Andl scripts > comprising dozens of individual examples in the Samples folder. My guess is > if that you're asking me to write examples, the real lesson is that I didn't > make them easy enough to find. > > I have a formal grammar, but I don't expect anyone to read that. More and > better examples is the way to go.
[sqlite] User-defined types -- in Andl
On 8 Jun 2015, at 3:14am, wrote: > I suggest you just read the samples off GitHub. They cover the entire > language. Download the binary, run them and you see what they do. Sorry but no. You have it reversed. Your code isn't going to touch my computer unless you have already convinced me that it's worth me investigating it. Just describe a few examples on your site. If you can show me "Look, it's a whole page in SQL but only half a page in Andl." or some similar advantage then so much the better. Simon.
[sqlite] User-defined types -- in Andl
On 8 Jun 2015, at 3:12am, wrote: > Is there a PDF? No, but that's a good idea. Did you check out the samples? > They cover the entire language, and I could turn those into a PDF much > faster than a real language. It would take about a month to write a decent > tutorial and reference, but that might make a good shortcut. My guess is that, if your objective is to attract readers, your time will be best spent composing a few examples. Formal grammar will be needed in the long run but only the real geeks will read it. Many people can read a few examples and figure out whether it's worth investigating the language further, whereas a formal grammar or a full tutorial would take more time to read than they would be willing to invest. Simon.
[sqlite] Exception when DBConfig is done programatically.
Hello
Exception when using SQLite, Entity FW and programmatically configuring
DBConfiguration.
(BTW it works perfectly fine if settings are done in app.config instead of
programmatic. But as a library developer I do not want to ask clients to put
some config settings in their app.config. I have also seen the way to
programmatically add config settings in application config at run-time but I do
not want to use that approach.)
Configuration code
public class SQLitDbProviderFactoryResolver : IDbProviderFactoryResolver
{
public DbProviderFactory ResolveProviderFactory(DbConnection
connection)
{
return System.Data.SQLite.SQLiteFactory.Instance;
}
}
public class SqliteConfiguration : DbConfiguration
{
public SqliteConfiguration()
{
SetDefaultConnectionFactory(new
System.Data.Entity.Infrastructure.SqlConnectionFactory());
SetProviderFactoryResolver(new SQLitDbProviderFactoryResolver());
{
SetProviderFactory("System.Data.SQLite",
SQLiteFactory.Instance);
SetProviderServices("System.Data.SQLite",
(DbProviderServices)SQLiteProviderFactory.Instance.GetService(typeof(DbProviderServices)));
}
}
}
[DbConfigurationType(typeof(SqliteConfiguration))]
public class SqliteDbContext : DbContext
{
public virtual DbSet LibraryItems { get; set; }
public SqliteDbContext()
: base(new SQLiteConnection() { ConnectionString = "Data Source=
ReportingAppDb.sqlite" }, true)
{
try
{
var result = this.Database.SqlQuery("SELECT name FROM
sqlite_master WHERE type='table' AND
name='SchemaInfo';").FirstOrDefault();
}
catch(Exception ex)
{
throw;
}
}
}
The code in SqliteDbContext constructor in try catch block encounters exception.
Exception:
System.TypeInitializationException was unhandled by user code
HResult=-2146233036
Message=The type initializer for 'LibraryProject.StudentResources' threw an
exception.
Source=LibraryProject
TypeName=LibraryProject.StudentResources
StackTrace:
at LibraryProject.StudentResources.GetStudents()
at SQLiteAndEntity.MainWindow..ctor() in
c:\AjeyWork\Shared\SQLiteAndEntity\SQLiteAndEntity\MainWindow.xaml.cs:line 28
InnerException: System.Data.DataException
HResult=-2146233087
Message=An exception occurred while initializing the database. See the
InnerException for details.
Source=EntityFramework
StackTrace:
at
System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action
action)
at
System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization()
at
System.Data.Entity.Internal.LazyInternalContext.b__4(InternalContext
c)
at System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput
input)
at
System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1
action)
at
System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase()
at System.Data.Entity.Internal.InternalContext.Initialize()
at
System.Data.Entity.Internal.LazyInternalContext.get_ObjectContext()
at
System.Data.Entity.Internal.InternalContext.ExecuteSqlQuery[TElement](String
sql, Boolean streaming, Object[] parameters)
at
System.Data.Entity.Internal.InternalContext.ExecuteSqlQueryAsIEnumerator[TElement](String
sql, Boolean streaming, Object[] parameters)
at System.Data.Entity.Internal.InternalContext.ExecuteSqlQuery(Type
elementType, String sql, Boolean streaming, Object[] parameters)
at
System.Data.Entity.Internal.InternalSqlNonSetQuery.GetEnumerator()
at System.Data.Entity.Infrastructure.DbRawSqlQuery`1.GetEnumerator()
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1
source)
at LibraryProject.SqliteDbContext..ctor() in
c:\AjeyWork\Shared\SQLiteAndEntity\LibraryProject\SqliteDbContext.cs:line 75
at LibraryProject.StudentResources..cctor() in
c:\AjeyWork\Shared\SQLiteAndEntity\LibraryProject\SqliteDbContext.cs:line 97
InnerException: System.Data.Entity.Core.EntityCommandCompilationException
HResult=-2146232005
Message=An error occurred while preparing the command definition.
See the inner exception for details.
Source=EntityFramework
StackTrace:
at
System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.CreateCommandDefinition(ObjectContext
context, DbQueryCommandTree tree)
at
System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlanFactory.Prepare(ObjectContext
context, DbQueryCommandTree tree, Type elementType, MergeOption mergeOption,
Boolean streaming, Span
[sqlite] sqlite web server on port 80 down? (IPv6)
>> $ curl -6 http://www.sqlite.org/ >> curl: (7) Failed to connect to www.sqlite.org port 80: >> Connection refused Richard Hipp wrote: > Unknown cause. Problem cleared by running "service xinetd restart". That was quick! Works now, thanks. (Btw, it was down for at least since beginning of June. Something to put into nagios or perhaps on uptimerobot.com) Mark
[sqlite] sqlite web server on port 80 down? (IPv6)
For a web browser that does not implement a 'happy eyeballs' IPv6 -> IPv4 fallback mechanism, the www.sqlite.org web server on port 80 is unreachable, although it does respond to https on port 443, and ping6 is fine too. $ curl -6 http://www.sqlite.org/ curl: (7) Failed to connect to www.sqlite.org port 80: Connection refused $ curl -6 https://www.sqlite.org/ (good) $ curl -4 http://www.sqlite.org/ (good) $ curl -4 https://www.sqlite.org/ (good) $ ping6 www.sqlite.org PING6(56=40+8+8 bytes) xxx --> 2600:3c00::f03c:91ff:fe96:b959 16 bytes from 2600:3c00::f03c:91ff:fe96:b959, icmp_seq=0 hlim=45 time=138.542 ms Looks like a configuration omission/inconsistency on a web server, or perhaps on a firewall. Would be nice to fix it to avoid surprising visitors which try connecting over non-SSL channel. Regards Mark
[sqlite] User-defined types
On 2015-06-04 03:04, Darko Volaric wrote: > Regarding PgSQL, an advantage of encoding your own binary types is that you > can copy them straight into your code and execute with them directly - I > use the same encoding/data structures throughout and they serve my code and > requirements instead of the database's or its API. That will only work if the data serialization format of a software is the same its your in-memory representation. Most likely you will have to deal with endianness and related issues. It is also a more general problem of "zero-copy" serialization formats. Text has its advantages and disadvantages of course. For my purpose a text format with a regular grammar is an acceptable format in terms of storage efficiency and serialization and deserialization performance. - Matthias-Christian
