[sqlite] CTE/Sudoku talk (was User-defined types -- in Andl)
I'm in NY, but just by coincidence I'll be very close to Charlotte for a graduation on the same darn day as your CTE/Sudoku talk. So close, yet so far! I am also going to miss your Git: Just say "No!" talk which I would have loved to see. So, if these talks are permanently available on YouTube (not just live streamed) please let us know. -- Bill Drago Staff Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / William.Drago at L-3COM.com > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite- > users-bounces at mailinglists.sqlite.org] On Behalf Of Richard Hipp > Sent: Wednesday, June 10, 2015 12:01 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] User-defined types -- in Andl > > On 6/9/15, david at andl.org wrote: > > I don't remember the last time I saw SQL like this. Understanding it > > might be the challenge... > > I'll be giving a talk on CTEs this Saturday at the Southeastern > Linuxfest (http://www.southeastlinuxfest.org/) during which I will > explain and demonstrate how to write a simple CTE that solves a sudoku > puzzle. If you cannot attend in person, I'm told that the talk will be > streamed live to youtube. > > -- > 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 CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments.
[sqlite] implicit vs explicit joins
On 2015-06-11 01:36 PM, Igor Korot wrote: > Clemens, > > On Thu, Jun 11, 2015 at 7:01 AM, Clemens Ladisch > wrote: >> Paul Sanderson wrote: >>> I have just been asked whether implicit and explicit join are the same >>> >>> select text, handle.id from message,handle where handle_id = handle.ROWID; >> This is an implicit *inner* join. >> >>> SELECT message."text", handle.id FROM message LEFT JOIN handle ON >>> message.handle_id = handle."ROWID"; >> This is an explicit *outer* join. >> >> Outer joins are one reason for using explicit joins, but you want to >> compare against a query with the same semantics: >> >>select text, handle.id from message join handle on handle_id = >> handle.ROWID; > But even outer joins can be rewritten to use standard semantics: > > SELECT FROM WHERE ; > > So what is the purpose to use joins at all? How will this statement: SELECT FROM WHERE ; ever be able to also signify a left outer join? I have never come across such an example, but if there is a way then I would like to learn about it. i.e. just to be sure we are on the same page, consider in this next SQL, how can I write the second query (in the format above) to produce the same results as the first Query? CREATE TABLE T1(id INT, name TEXT); CREATE TABLE T2(id INT, T1_Ref INT); INSERT INTO T1 VALUES (1, 'John'), (2, 'James'), (3, 'Joe'); INSERT INTO T2 VALUES (1,1), (2,4), (3,3), (4,7); -- First Query SELECT T2.id, T2.T1_Ref, T1.name FROM T2 LEFT JOIN T1 ON T1.id=T2.T1_Ref -- id | T1_Ref | name -- | -- | -- -- 1 |1 | John -- 2 |4 | Null -- 3 |3 | Joe -- 4 |7 | Null -- Second Query SELECT T2.id, T2.T1_Ref, T1.nameFROM T2, T1WHERE T1.id=T2.T1_Ref -- id |T1_Ref| name -- | | -- -- 1 | 1 | John -- 3 | 3 | Joe
[sqlite] implicit vs explicit joins
Thank You Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 11 June 2015 at 12:01, Clemens Ladisch wrote: > Paul Sanderson wrote: >> I have just been asked whether implicit and explicit join are the same >> >> select text, handle.id from message,handle where handle_id = handle.ROWID; > > This is an implicit *inner* join. > >> SELECT message."text", handle.id FROM message LEFT JOIN handle ON >> message.handle_id = handle."ROWID"; > > This is an explicit *outer* join. > > Outer joins are one reason for using explicit joins, but you want to > compare against a query with the same semantics: > > select text, handle.id from message join handle on handle_id = handle.ROWID; > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] confusing error msgs
Hi all. Yes, I'm a newby. I have some past experience using dBASE III+ and I currently use freeBASIC for my programming needs. I have a need to put a small database in one of my freeBASIC applications and SQLite seemed to fit the bill. However I am having some problems. I have 4 sql "CREATE TABLE" files: event.sql, timeslip.sql, baseline.sql and current.sql. Here is the code in the various files: CREATE TABLE event( ev_rec_no INTEGER PRIMARY KEY, ev_date TEXT(10), LOCATION TEXT(40), SANCTION TEXT(10), elevation DECIMAL(6, 2), evNote_1 TEXT (76), evNote_2 TEXT (76), evNote_3 TEXT (76), evNote_4 TEXT (76), evNote_5 TEXT (76) ); CREATE TABLE timeslip ( ts_rec_no INTEGER PRIMARY KEY, evnt_rec_no INTEGER, FOREIGN KEY (evnt_rec_no) REFERENCES event(ev_rec_no), stEvent_date TEXT (10), stLocation TEXT (50), stSanction TEXT (10), sfElev DECIMAL (6, 2), sfDist DECIMAL (5, 4), sfWgt DECIMAL (5, 2), usLaunch_rpm INTEGER, usShift_rpm INTEGER, stRun_time TEXT (5), stAMPM CHAR (1), stRun_type TEXT (2), stLane CHAR (1), stCarNum TEXT (5), stClass TEXT (8), sfCl_index DECIMAL (6, 4), sfOvUn DECIMAL (6, 4), sfDial DECIMAL (7,4),-- dial in sfRt DECIMAL (6, 4), -- reaction time sfTime_60_ft DECIMAL (7, 4), sfTime_330_ft DECIMAL (7, 4), sfTime_660_ft DECIMAL (7, 4), sfMPH_660_ft DECIMAL (8, 4), sfTIme_1000_ft DECIMAL (7, 4), sfMPH_1000_ft DECIMAL (8, 4), sfTime_1320_ft DECIMAL (7, 4), sfMPH_1320_ft DECIMAL (8,4), sfFirst DECIMAL (7,4), sfMOV DECIMAL (6, 4), stResult CHAR (1), -- won or loss sfTempFah DECIMAL(6,2), sfRelHumid DECIMAL (4,3), sfVapPress DECIMAL (4,3), sfBaroPress DECIMAL 4,3), stType CHAR(1), stNote_1 TEXT (76), stNote_2 TEXT (76), stNote_3 TEXT (76), stNote_4 TEXT (76), stNote_5 TEXT (76) ); CREATE TABLE baseline( bl_rec_no INTEGER PRIMARY KEY NOT NULL, stTestDate TEXT (10), sfElev DECIMAL(6, 2), sfTempFah DECIMAL(6,2), sfRelHumid DECIMAL (4,3), sfVapPress DECIMAL (4,3), sfBaroPress DECIMAL 4,3), stType CHAR(1), sfMaxTrq DECIMAL (7,3), sfMaxHP DECIMAL (7, 3), sfCorrecFact DECIMAL (4,3), stCorrecType TEXT (3), sfWgt DECIMAL (5, 2), stNote_1 TEXT (76), stNote_2 TEXT (76), stNote_3 TEXT (76), stNote_4 TEXT (76), stNote_5 TEXT (76) ); CREATE TABLE current( cr_rec_no INTEGER PRIMARY KEY, stCurDate TEXT (12), sfElev DECIMAL(6, 2), sfTempFah DECIMAL(6,2), sfRelHumid DECIMAL (4,3), sfVapPress DECIMAL (4,3), sfBaroPress DECIMAL 4,3), stType CHAR(1), stWgt DECIMAL (5,2), sfMPHcf DECIMAL (5, 4), sfETcf DECMIAL (5, 4), stNote_1 TEXT (76), stNote_2 TEXT (76), stNote_3 TEXT (76), stNote_4 TEXT (76), stNote_5 TEXT (76) ); Now I have a batch file that I use to construct the actual databases. Here is that batch file: REM ** REM db.bat - this batch file creats database for use with Performance Tuner REM ** del *.bak REM sqlite3 -init event.sql pt__x3xx.db REM sqlite3 -init event.sql event.db REM C:\Documents and Settings\All Users\Documents\src\perftune\sqlite_test>sqlite3 - REM init event.sql event.db REM -- Loading resources from event.sql REM SQLite version 3.7.5 REM Enter ".help" for instructions REM Enter SQL statements terminated with a ";" REM sqlite> .exit REM sqlite3 -init timeslip.sql timeslip.db REM C:\Documents and Settings\All Users\Documents\src\perftune\sqlite_test>sqlite3 - REM init timeslip.sql timeslip.db REM -- Loading resources from timeslip.sql REM Error: near line 3: near "stEvent_date": syntax error REM sqlite3 -init baseline.sql baseline.db REM C:\Documents and Settings\All Users\Documents\src\perftune\sqlite_test>sqlite3 - REM init baseline.sql baseline.db REM -- Loading resources from baseline.sql REM Error: near line 2: near "4": syntax error REM sqlite3 -init current.sql current.db REM C:\Documents and Settings\All Users\Documents\src\perftune\sqlite_test>sqlite3 - REM init current.sql current.db REM -- Loading resources from current.sql REM Error: near line 3: near "4": syntax error I have added the various error msgs I get when running the batch file. You'll notice that the lines that construct event.db run fine. The other 3, not so much. Most confusing to me are the msgs for baseline.sql and current.sql. I can discern no difference between those files and event.sql. I have tried to search the on-line documentation but a search on "error" turns up MANY, MANY entries. So I thought I'd turn to the mailing list. I should also note that I only run the batch file for one table at a time, the others are commented out. Any help would be
[sqlite] implicit vs explicit joins
Paul Sanderson wrote: > I have just been asked whether implicit and explicit join are the same > > select text, handle.id from message,handle where handle_id = handle.ROWID; This is an implicit *inner* join. > SELECT message."text", handle.id FROM message LEFT JOIN handle ON > message.handle_id = handle."ROWID"; This is an explicit *outer* join. Outer joins are one reason for using explicit joins, but you want to compare against a query with the same semantics: select text, handle.id from message join handle on handle_id = handle.ROWID; Regards, Clemens
[sqlite] User-defined types -- in Andl
That's about 30 hours from here. I might have to pass, for now. But if it's on Youtube I'd be interested. I think I understand recursive CTEs well enough now from the description in the documentation and studying the code. I'm impressed at the brevity of your solution, although in practice it does not perform well. The far more complex implementation from Pasma performs better, I think largely because it applies rules to narrow the search space. My challenge now is to implement that feature in Andl. 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: Thursday, 11 June 2015 2:01 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] User-defined types -- in Andl On 6/9/15, david at andl.org wrote: > I don't remember the last time I saw SQL like this. Understanding it > might be the challenge... I'll be giving a talk on CTEs this Saturday at the Southeastern Linuxfest (http://www.southeastlinuxfest.org/) during which I will explain and demonstrate how to write a simple CTE that solves a sudoku puzzle. If you cannot attend in person, I'm told that the talk will be streamed live to youtube. -- 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] static sqlite database
Eduardo, I need to do range searches based on id's so R-tree doesn't help, but your suggestion of attaching database sounds useful. I will also look at the size of the page and post my results. On Wed, Jun 10, 2015 at 7:19 PM Eduardo Morras wrote: > On Wed, 10 Jun 2015 14:13:29 + > Igor Stassiy wrote: > > > Thanks for all your replies. Please let me clarify: > > > > I need to do certain range operations efficiently and I prefer SQL > > functionality, plus SQLite gives a cross platform file storage > > format. The table having 3.2 megabytes is just an example. I am > > storing much more (orders of 10^5 of points) however the effect is > > similar. > > > > When I store several tables side by side the effect seems to be worse > > (I assume SQLite leaves the possibility for inserts and hence needs > > to have some space to do them efficiently, please correctly if I am > > wrong). SQLite forums suggest to store tables in separate files to > > avoid vacuum space in between the tables, are there any other methods > > to save space? > > Have you look at R-Tree extension? https://www.sqlite.org/rtree.html > > It creates a table and indexs to store and query latitude and longitude > efficiently. > > Or spatialite? https://en.wikipedia.org/wiki/SpatiaLite > http://www.gaia-gis.it/gaia-sins/ > > It's opensource under MPL. > > A page is dedicated only to one thing, table A, table B, internal sqlite3 > data, index C, whatever... A page can't store data from one table and data > from another table or index. This means that if your page size (default > 1024bytes) is not defined wise enough (f.ex. 64KB=65536byes) and your > tables uses a few bytes (say 128bytes), the rest of the page is empty > (65536-128=65408 bytes) for future table grows. There are page header and > tail with data you must count too, so it's a bit less. Note that you want > to store a big table with data, and the space lost on unwise page size is > minimal. > > You can have empty pages inside the database because you deleted data or > drop an index or a table, but in your static db, it shouldn't happen. In > any case, a vacuum and analyze solve the issue. > > I use the trick to store tables in separate db files and attach them to > main db for speed on mechanical hard disks, because those files on > different disks maximize the iops. An autovacumm and proper db maintenance > should minimize the vacuum space between tables. > > HTH > > --- --- > Eduardo Morras > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] Virtual tables/xBestIndex: Is this a bug?
On 06/11/2015 03:49 AM, Eric Hill wrote: > Is it a bug that SQLite changes the order of the constraints passed to > xBestIndex based merely on the order of the ON clause, when SQL (AFAIK) says > nothing about what the order of the ON clause should be? No. The order is undefined. However, it is less than perfect that the constraints on "film_id=" are marked usable when they are really not. That's the root of your problem I think - SQLite is asking for the wrong thing. When it eventually figures out that it can't actually use the plan it requested from xBestIndex (because the film_id= constraint is not actually usable) it falls back to a linear scan. There is now a change on the trunk that should fix this: http://www.sqlite.org/src/info/7b446771cadedafb Dan.
[sqlite] implicit vs explicit joins
I have just been asked whether implicit and explicit join are the same and was given an example There queries below produce the same results but explain indicates that they are different. My question though is in what instances are the actual results of such a query likely to be different and is there a reason to use explicit over implicit sqlite> explain select text, handle.id from message,handle where handle_id = handle.ROWID; 0|Init|0|14|0||00| 1|OpenRead|0|5|0|6|00| 2|OpenRead|1|12|0|2|00| 3|Rewind|0|11|0||00| 4|Column|0|5|1|0|00| 5|MustBeInt|1|10|0||00| 6|NotExists|1|10|1||00| 7|Column|0|2|2||00| 8|Column|1|1|3||00| 9|ResultRow|2|2|0||00| 10|Next|0|4|0||01| 11|Close|0|0|0||00| 12|Close|1|0|0||00| 13|Halt|0|0|0||00| 14|Transaction|0|0|44|0|01| 15|TableLock|0|5|0|message|00| 16|TableLock|0|12|0|handle|00| 17|Goto|0|1|0||00| sqlite> explain SELECT message."text", handle.id FROM message LEFT JOIN handle ON message.handle_id = handle."ROWID"; 0|Init|0|19|0||00| 1|OpenRead|0|5|0|6|00| 2|OpenRead|1|12|0|2|00| 3|Rewind|0|16|0||00| 4|Integer|0|1|0||00| 5|Column|0|5|2|0|00| 6|MustBeInt|2|12|0||00| 7|NotExists|1|12|2||00| 8|Integer|1|1|0||00| 9|Column|0|2|3||00| 10|Column|1|1|4||00| 11|ResultRow|3|2|0||00| 12|IfPos|1|15|0||00| 13|NullRow|1|0|0||00| 14|Goto|0|8|0||00| 15|Next|0|4|0||01| 16|Close|0|0|0||00| 17|Close|1|0|0||00| 18|Halt|0|0|0||00| 19|Transaction|0|0|44|0|01| 20|TableLock|0|5|0|message|00| 21|TableLock|0|12|0|handle|00| 22|Goto|0|1|0||00| Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence
[sqlite] implicit vs explicit joins
An INNER join (signified by the join operators "," (comma), JOIN or INNER JOIN) is a very different beast than an OUTER join (signified by the join operators LEFT JOIN or LEFT OUTER JOIN). The first returns that subset of the cartesian product of the two tables where the join condition is met. Which may be empty. The second addtitionally returns, for each LHS row that is not already in the result set of the join, that LHS row joined to a row of all NULLs. And thus contains a least one row for each LHS row. If each and every record in your message table contains a valid rowid from the handle table, you will never notice the difference. Try SELECT * FROM message WHERE handle_id NOT IN (SELECT rowid FROM handle); it is probably empty. -Urspr?ngliche Nachricht- Von: Paul Sanderson [mailto:sandersonforensics at gmail.com] Gesendet: Donnerstag, 11. Juni 2015 12:46 An: General Discussion of SQLite Database Betreff: [sqlite] implicit vs explicit joins I have just been asked whether implicit and explicit join are the same and was given an example There queries below produce the same results but explain indicates that they are different. My question though is in what instances are the actual results of such a query likely to be different and is there a reason to use explicit over implicit sqlite> explain select text, handle.id from message,handle where handle_id = handle.ROWID; 0|Init|0|14|0||00| 1|OpenRead|0|5|0|6|00| 2|OpenRead|1|12|0|2|00| 3|Rewind|0|11|0||00| 4|Column|0|5|1|0|00| 5|MustBeInt|1|10|0||00| 6|NotExists|1|10|1||00| 7|Column|0|2|2||00| 8|Column|1|1|3||00| 9|ResultRow|2|2|0||00| 10|Next|0|4|0||01| 11|Close|0|0|0||00| 12|Close|1|0|0||00| 13|Halt|0|0|0||00| 14|Transaction|0|0|44|0|01| 15|TableLock|0|5|0|message|00| 16|TableLock|0|12|0|handle|00| 17|Goto|0|1|0||00| sqlite> explain SELECT message."text", handle.id FROM message LEFT JOIN handle ON message.handle_id = handle."ROWID"; 0|Init|0|19|0||00| 1|OpenRead|0|5|0|6|00| 2|OpenRead|1|12|0|2|00| 3|Rewind|0|16|0||00| 4|Integer|0|1|0||00| 5|Column|0|5|2|0|00| 6|MustBeInt|2|12|0||00| 7|NotExists|1|12|2||00| 8|Integer|1|1|0||00| 9|Column|0|2|3||00| 10|Column|1|1|4||00| 11|ResultRow|3|2|0||00| 12|IfPos|1|15|0||00| 13|NullRow|1|0|0||00| 14|Goto|0|8|0||00| 15|Next|0|4|0||01| 16|Close|0|0|0||00| 17|Close|1|0|0||00| 18|Halt|0|0|0||00| 19|Transaction|0|0|44|0|01| 20|TableLock|0|5|0|message|00| 21|TableLock|0|12|0|handle|00| 22|Goto|0|1|0||00| Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] CTE/Sudoku talk (was User-defined types -- in Andl)
On 6/11/15, Drago, William @ CSG - NARDA-MITEQ wrote: > I'm in NY, but just by coincidence I'll be very close to Charlotte for a > graduation on the same darn day as your CTE/Sudoku talk. So close, yet so > far! I am also going to miss your Git: Just say "No!" talk which I would > have loved to see. > > So, if these talks are permanently available on YouTube (not just live > streamed) please let us know. > I'm not in charge of the conference so I don't know. CCed this email to the people at SELF who are better able to answer. Maybe they will reply. If not, I'll probably find out next week. -- D. Richard Hipp drh at sqlite.org
[sqlite] implicit vs explicit joins
Clemens, On Thu, Jun 11, 2015 at 7:01 AM, Clemens Ladisch wrote: > Paul Sanderson wrote: >> I have just been asked whether implicit and explicit join are the same >> >> select text, handle.id from message,handle where handle_id = handle.ROWID; > > This is an implicit *inner* join. > >> SELECT message."text", handle.id FROM message LEFT JOIN handle ON >> message.handle_id = handle."ROWID"; > > This is an explicit *outer* join. > > Outer joins are one reason for using explicit joins, but you want to > compare against a query with the same semantics: > > select text, handle.id from message join handle on handle_id = handle.ROWID; But even outer joins can be rewritten to use standard semantics: SELECT FROM WHERE ; So what is the purpose to use joins at all? Thank you. > > > Regards, > Clemens > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] implicit vs explicit joins
Of course they are not the same. One is an implicit equijoin and the other is an explicit outer join. Wondering if they are different is like windering if the colour of a car makes a difference in how fast it will go, then testing a yellow lambourgini against a purple volkswagon bug, and concluding that yellow cars are faster than purple cars. > -Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > bounces at mailinglists.sqlite.org] On Behalf Of Paul Sanderson > Sent: Thursday, 11 June, 2015 04:46 > To: General Discussion of SQLite Database > Subject: [sqlite] implicit vs explicit joins > > I have just been asked whether implicit and explicit join are the same > and was given an example > > There queries below produce the same results but explain indicates > that they are different. > > My question though is in what instances are the actual results of such > a query likely to be different and is there a reason to use explicit > over implicit > > > sqlite> explain select text, handle.id from message,handle where > handle_id = handle.ROWID; > > 0|Init|0|14|0||00| > 1|OpenRead|0|5|0|6|00| > 2|OpenRead|1|12|0|2|00| > 3|Rewind|0|11|0||00| > 4|Column|0|5|1|0|00| > 5|MustBeInt|1|10|0||00| > 6|NotExists|1|10|1||00| > 7|Column|0|2|2||00| > 8|Column|1|1|3||00| > 9|ResultRow|2|2|0||00| > 10|Next|0|4|0||01| > 11|Close|0|0|0||00| > 12|Close|1|0|0||00| > 13|Halt|0|0|0||00| > 14|Transaction|0|0|44|0|01| > 15|TableLock|0|5|0|message|00| > 16|TableLock|0|12|0|handle|00| > 17|Goto|0|1|0||00| > > > > sqlite> explain SELECT message."text", handle.id FROM message LEFT > JOIN handle ON message.handle_id = handle."ROWID"; > > 0|Init|0|19|0||00| > 1|OpenRead|0|5|0|6|00| > 2|OpenRead|1|12|0|2|00| > 3|Rewind|0|16|0||00| > 4|Integer|0|1|0||00| > 5|Column|0|5|2|0|00| > 6|MustBeInt|2|12|0||00| > 7|NotExists|1|12|2||00| > 8|Integer|1|1|0||00| > 9|Column|0|2|3||00| > 10|Column|1|1|4||00| > 11|ResultRow|3|2|0||00| > 12|IfPos|1|15|0||00| > 13|NullRow|1|0|0||00| > 14|Goto|0|8|0||00| > 15|Next|0|4|0||01| > 16|Close|0|0|0||00| > 17|Close|1|0|0||00| > 18|Halt|0|0|0||00| > 19|Transaction|0|0|44|0|01| > 20|TableLock|0|5|0|message|00| > 21|TableLock|0|12|0|handle|00| > 22|Goto|0|1|0||00| > > > > > Paul > www.sandersonforensics.com > skype: r3scue193 > twitter: @sandersonforens > Tel +44 (0)1326 572786 > http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic- > Toolkit > -Forensic Toolkit for SQLite > email from a work address for a fully functional demo licence > ___ > 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
Yes, I had noticed those. Thank you. Both these and the 'challenge' depend on the recursive CTE. As noted elsewhere, I need to implement that before going any further with these. If you have any other challenges I would still be interested. 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 Dominique Devienne Sent: Tuesday, 9 June 2015 9:57 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] User-defined types -- in Andl On Tue, Jun 9, 2015 at 1:50 PM, wrote: > Thank you. Exactly so. One of the problems with this kind of project > is finding 'good enough' challenges to tackle. > See also from the CTE doc: - https://www.sqlite.org/lang_with.html#sudoku - https://www.sqlite.org/lang_with.html#mandelbrot Thanks, --DD ___ 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
After some code review... The main problem with this code is the use of integers to maintain bit flags tracking cell usage. The advantage is that when the Sudoku search phase with its associated backtracking has to make a copy of the game board, the amount of data to copy is low. The disadvantage is that the code is unreadable. Trying to manage a bit mask with 81 bits using 64 bit integers makes it at least twice as bad. But really the core of the algorithm is that it relies on 4 recursive CTEs. These are to construct the cell layout bit masks and digits, one to process the game rules, and one to do the backtracking search. They are crucial to the query. Currently Andl has recursive function calls but it does not have recursive CTEs. A recursive CTE is something special because rather than being true recursion it is actually a generator that populates a table with new rows according to a query. It does this by means of a queue rather than a stack. Andl could implement this algorithm using recursion, but it would be slow and might well produce a stack overflow, and would not be a good outcome. So the answer is: I plan to add 'recursive' queries to Andl. This may take a little while. Thanks for the challenge. If in the meantime you have any others that do not use recursive CTEs I would be interested. 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 david at andl.org Sent: Tuesday, 9 June 2015 9:51 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] User-defined types -- in Andl Thank you. Exactly so. One of the problems with this kind of project is finding 'good enough' challenges to tackle. I'll let you know how I get on. [BTW I don't remember the last time I saw SQL like this. Understanding it might be the challenge...] 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 Jean-Christophe Deschamps Sent: Tuesday, 9 June 2015 5:16 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] User-defined types -- in Andl At 08:27 09/06/2015, you wrote: >Andl is at a slightly higher level than SQL for writing simple queries. >Where it shines is writing complex queries that involve user-defined >types, custom transformations and custom aggregations. For complex >relational operations there is nothing I know that can come close, >productivity wise. `--- You call for complexity, you get it! What I would find pretty convincing is seeing how andl would translate the self-contained sudoku solver posted by E. Pasma in this post: http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2014-Mar ch/051982.html Granted, this is far from the typical SQL you can find in routine use, but I believe that andl being able to elegantly translate it would certainly impress a number of readers and make many of us more interested in digging further. I'm not throwing such a mayhem challenge at you in the tone of "if andl can't do that, then shut up". Maybe andl is not yet complete enough today to achieve that and this wouldn't be a big issue. But if it can I'm sure andl will attract more attention. JcD ___ 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
