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 < <mailto:lists at darko.org> 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 <mailto:drh at sqlite.org> drh at sqlite.org _______________________________________________ sqlite-users mailing list <mailto:sqlite-users at mailinglists.sqlite.org> sqlite-users at mailinglists.sqlite.org <http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users