DB/Flwor
FLWOR - functional query for relational data
Introduction
Thinking of relational databases, SQL may cause too much declarative abstraction, preventing to envision relational operations in simpler, palpable notation. SQL and code are uneasy companions, just recall the still-born embedded SQL.
Interestingly, relatively recent data access tools (XQuery, .NET Linq, Apache PigLatin, JASON Jaql, etc.) have embraced a different language integration strategy: less abstract and with more functional programming aspects, which can be referred to as FLWOR (pronounced as "flower"), representing the stages of query for/let/where/order/return.
We will begin to experiment with a few such ideas using a simple textbook database from Date.
There are different ways to store and access data in J (table in boxed noun, noun with inverted columns, etc). Here we will represent relational data closely matching J named objects layout: a table as a J locale, and a column as a noun name in the table-locale.
This leads to a few conventions: nouns in the same locale should have the same number of items; a tupple in a table is cross-section of nouns in the same locale with the same index; a tupple in result set is combination of compatible columns selected by the same index or mask, etc.
J Notation
To get an idea how it works, here's some queries possible just after creating the database (see how to run literate scripts):
cocurrent ''conew'dbDate' NB. job ID,name whose city is Athens (JID__J,.JNAME__J) #~ CITY__J = <'Athens' +--+-------+ |J3|OCR | +--+-------+ |J4|Console| +--+-------+ NB. cities whose supplier has job in Athens ~.CITY__S #~ SID__S e. SID__SPJ #~ JID__SPJ e. JID__J #~ CITY__J=<'Athens' +------+-----+------+ |London|Paris|Athens| +------+-----+------+ NB. number of parts by color (~.COLOR__P) ,.<"0 #/.~COLOR__P +-----+-+ |Red |4| +-----+-+ |Green|1| +-----+-+ |Blue |2| +-----+-+ NB. count,min,max,average of part weight by city (~.CITY__P) ,.<"0 CITY__P (# , <./ , >./ , +/ % #)/. WEIGHT__P +------+-+--+--+----+ |London|4|12|19|16 | +------+-+--+--+----+ |Paris |2|12|17|14.5| +------+-+--+--+----+ |Oslo |1|17|17|17 | +------+-+--+--+----+
Relational Notation
With the help of relational cover definitions [{{#file: "relational"}} Download script: relational ]
where =: #~ in =: e. distinct =: ~. groupby=: 1 : (':';' (~.y) ,.<"0 x u/.~ y') '`count min max sum avg'=: #`(<./)`(>./)`(+/)`(+/%#)
The same constructs as above look familiar to database users
NB. job ID,name whose city is Athens (JID__J,.JNAME__J) where CITY__J = <'Athens' +--+-------+ |J3|OCR | +--+-------+ |J4|Console| +--+-------+ NB. cities whose supplier has job in Athens distinct CITY__S where SID__S in SID__SPJ where JID__SPJ in JID__J where CITY__J = <'Athens' +------+-----+------+ |London|Paris|Athens| +------+-----+------+ NB. number of parts by color,city PID__P count groupby COLOR__P,.CITY__P +-----+------+-+ |Red |London|4| +-----+------+-+ |Green|Paris |1| +-----+------+-+ |Blue |Oslo |1| +-----+------+-+ |Blue |Paris |1| +-----+------+-+ NB. count,min,max,average of part weight by city WEIGHT__P (count,min,max,avg) groupby CITY__P +------+-+--+--+----+ |London|4|12|19|16 | +------+-+--+--+----+ |Paris |2|12|17|14.5| +------+-+--+--+----+ |Oslo |1|17|17|17 | +------+-+--+--+----+
Demo Script
Generic class structure that populates its instance with names of tables declaratively filled with data. [{{#file: "flwor.ijs"}} Download script: flwor.ijs ]
NB. flwor - for/let/where/order/return prototype coclass 'dbDate' create=: 3 : 0 schema=: 2&}.&.>'tb'nl_dbDate_ 0 (schema)=: cocreate&.> a:#~#schema for_t. schema do. ('tdefine__',>t)~ ('tb',>t)~ end. ) destroy=: 3 : 0 coerase ".&> schema codestroy'' )
Utility verb to define a relational table from string as J locale with noun names as inverted columns. [{{#file: "flwor.ijs"}} Download script: flwor.ijs ]
NB. ========================================================= NB. utility dtb_z_=: #~ [: +./\. ' '&~: NB. delete trailing blanks cut2_z_=: ] ({.~ ; (}.~ >:)) i.~ NB. cut in two at x tdefine_z_=: 3 : 0 y=. <@dtb ;. _1 ;. _2 y h=. {. y NB. header d=. }. y NB. data 'n t'=. <"1 |: ':' cut2&> h NB. name, type o=. ('n'i.{.&>t) { '0&".&>';']' NB. type operation c=. o (128!:2)&.>"_ 1 <"1|:d NB. columns of data (n)=: c ) «relational»
The test data tables are simply delimited text nouns in names prefixed with tb. First row is column names, with optional type delimited by ":". [{{#file: "flwor.ijs"}} Download script: flwor.ijs ]
NB. ========================================================= NB. data tbJ=: 0 : 0 |JID|JNAME |CITY |J1 |Sorter |Paris |J2 |Display|Rome |J3 |OCR |Athens |J4 |Console|Athens |J5 |RAID |London |J6 |EDS |Oslo |J7 |Tape |London ) tbP=: 0 : 0 |PID|PNAME|COLOR|WEIGHT:n|CITY |P1 |Nut |Red |12 |London |P2 |Bolt |Green|17 |Paris |P3 |Screw|Blue |17 |Oslo |P4 |Screw|Red |14 |London |P5 |Cam |Blue |12 |Paris |P6 |Cog |Red |19 |London |P7 |Cog |Red |19 |London ) tbS=: 0 : 0 |SID|SNAME|STATUS:n|CITY |S1 |Smith|20 |London |S2 |Jones|10 |Paris |S3 |Blake|30 |Paris |S4 |Clark|20 |London |S5 |Adams|30 |Athens ) tbSP=: 0 : 0 |SID|PID|QTY:n |S1 |P1 |300 |S1 |P2 |200 |S1 |P3 |400 |S1 |P4 |200 |S1 |P5 |100 |S1 |P6 |100 |S2 |P1 |300 |S2 |P2 |400 |S3 |P2 |200 |S4 |P2 |200 |S4 |P4 |300 |S4 |P5 |400 ) tbSPJ=: 0 : 0 |SID|PID|JID|QTY:n |S1 |P1 |J1 |200 |S1 |P1 |J4 |700 |S2 |P3 |J1 |400 |S2 |P3 |J2 |200 |S2 |P3 |J3 |200 |S2 |P3 |J4 |500 |S2 |P3 |J5 |600 |S2 |P3 |J6 |400 |S2 |P3 |J7 |800 |S2 |P5 |J2 |100 |S3 |P3 |J1 |200 |S3 |P4 |J2 |500 |S4 |P6 |J3 |300 |S4 |P6 |J7 |300 |S5 |P2 |J2 |200 |S5 |P2 |J4 |100 |S5 |P5 |J5 |500 |S5 |P5 |J7 |100 |S5 |P6 |J2 |200 |S5 |P1 |J4 |100 |S5 |P3 |J4 |200 |S5 |P4 |J4 |800 |S5 |P5 |J4 |400 |S5 |P6 |J4 |500 )
Finally the test that produced the above results. [{{#file: "flwor.ijs"}} Download script: flwor.ijs ]
NB. ========================================================= Note 'J Notation' cocurrent '' conew 'dbDate' NB. job ID,name whose city is Athens (JID__J,.JNAME__J) #~ CITY__J = <'Athens' NB. cities whose supplier has job in Athens ~.CITY__S #~ SID__S e. SID__SPJ #~ JID__SPJ e. JID__J #~ CITY__J = <'Athens' NB. number of parts by color (~.COLOR__P) ,.<"0 #/.~COLOR__P NB. count,min,max,average of part weight by city (~.CITY__P) ,.<"0 CITY__P (# , <./ , >./ , +/ % #)/. WEIGHT__P destroy '' cocurrent_z_ 'base' ) Note 'Relational Notation' cocurrent '' conew 'dbDate' NB. job ID,name whose city is Athens (JID__J,.JNAME__J) where CITY__J = <'Athens' NB. cities whose supplier has job in Athens distinct CITY__S where SID__S in SID__SPJ where JID__SPJ in JID__J where CITY__J = <'Athens' NB. number of parts by color,city PID__P count groupby COLOR__P,.CITY__P NB. count,min,max,average of part weight by city WEIGHT__P (count,min,max,avg) groupby CITY__P destroy '' cocurrent_z_ 'base' )
See Also
- XQuery 1.0: An XML Query Language
- .NET Linq: Language-Integrated Query
- Jaql: A JSON Query Language
- Apache Pig Latin, platform for analyzing large data sets and a simple query algebra language
Contributed by Oleg Kobchenko