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: [email protected]
[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
