README (6871B)
1 2 SEPTEQL 3 4 +--- WARNING --------------------------------------------------------+ 5 | This is *work in progress*: syntax may (quite likely, will) change | 6 | without warning. | 7 +--------------------------------------------------------------------+ 8 9 SEPTEQL is a Lispy composable query language for talking to RDBMSes. 10 It translates to SQL, so (at least in principle) is database- and 11 driver-independent and will talk to anything. 12 13 * Motivating Examples 14 15 (to-sql '(join rel1 rel2 predicate)) 16 => "SELECT * FROM (REL1 JOIN REL2 ON PREDICATE) " 17 18 (to-sql '(project (att1 (as att2 name) att3) relation)) 19 => "SELECT ATT1,ATT2 AS NAME,ATT3 FROM RELATION " 20 21 (to-sql '(order (limit relation 5 15) attribute :desc) ) 22 ; why anyone would want to limit *first* and then order the result I 23 ; don't know, but look, it handles that too 24 => "SELECT * FROM (SELECT * FROM RELATION OFFSET 5 LIMIT 10 ) RELATION ORDER BY ATTRIBUTE DESC " 25 26 (to-sql '(group ((as (sum quantity) quantity) 27 (as (sum (* quantity cost)) value)) 28 (event-id event-description) 29 order-line)) 30 => "SELECT SUM(QUANTITY) AS QUANTITY,SUM((QUANTITY * COST)) AS VALUE,EVENT_ID,EVENT_DESCRIPTION FROM ORDER_LINE GROUP BY EVENT_ID,EVENT_DESCRIPTION " 31 32 Backquote and comma are your friends [1] 33 34 * Syntax overview 35 36 Look at tests.lisp for examples. This is all subject to change, so 37 UTSL is not bad advice. 38 39 ** Relations 40 41 At the risk of doing considerable violence to accepted terminology: a 42 relation is an "ordered bag" of tuples (aka rows) [2]. A tuple is a bag 43 of attributes (informally, columns), each attribute having a name and 44 a value. 45 46 (SELECT relation predicate) 47 - get rows that match 48 (PROJECT attributes relation) 49 - get columns that match 50 (JOIN r1 r2 expr) 51 - inner join of R1 and R2 where EXPR 52 (GROUP aggregates group-by rel) 53 - for each distinct value of the attributes GROUP-BY in REL, apply AGGREGATES 54 across all rows of REL 55 (ORDER relation attribute &optional direction) 56 - Sort RELATION according to ATTRIBUTE. DIRECTION is :asc or :desc 57 (RENAME relation new-name) 58 - what it says. Also has the effect of "flattening" a relation: for example 59 (project (a.foo (as b.foo bar)) (a join b on p)) 60 is OK, but not 61 (project (a.foo (as b.foo bar)) (rename (a join b on b) c)) 62 because the "a" and "b" prefixes get squashed. 63 (LIMIT relation start end) 64 - return rows START <= n < END. Note exclusive upper bound, as in CL 65 sequence functions 66 67 Changes likely in the following areas: 68 - ORDER to gain some way of sorting on multiple columns 69 - some kind of syntax for left and right joins (more probably 70 new operators than extending JOIN with keywords) 71 72 73 ** Scalars 74 75 Values that are not relations we call "scalars". Scalar expressions 76 are e.g. the predicate in a SELECT, the expr in a JOIN, and so on. 77 78 Scalars include attribute names, literals (string, numeric, booleans) and 79 functions that work on them. We use a Lisp-style prefix 80 representation: thus 81 82 1 83 "fish" 84 2d0 85 t 86 (+ 1 2) 87 (>= att1 17) 88 (like att2 "hello%") 89 (++ "hello " "world") ; string catenation using the SQL || operator 90 (sum quantity) 91 (case ((p1 res1) (p2 res2) ... (else res-default))) 92 93 CAUTION NEEDED: How we deal with NIL is largely undecided, as SQL has 94 distinct NULL and FALSE, but CL doesn't 95 96 Operators in the list *INFIX-SYNTAX* are transformed to infix syntax. 97 Some stuff (CASE, ++, AS) is handled specially. Anything we don't 98 recognise is assumed to be a call to a SQL function. 99 100 Note that CASE in SQL is more like CL's COND (where p1, p2 ... are 101 arbitrary predicates) than its CASE (where they are literals possible 102 values of a key form). To that end, we have defined COND as an alias 103 for CASE, mostly for the convenience of editors with magic indentation 104 that will otherwise get it wrong. 105 106 We don't have provision for many of the SQL string-processing 107 functions with stupid positional syntax; expect that to be added if 108 and when I need to use them. Or add them yourself and send patches. 109 110 111 * Installation 112 113 Works best with ASDF, but that's probably not a particularly onerous 114 requirement these days. If you don't have it, arrange for 115 defpackage.lisp and septeql.lisp to be compiled and loaded in that 116 order. 117 118 119 * Compatibility 120 121 Septeql is developed in SBCL and Postgresql. The author would like to 122 hear about importabilities that break it when used with other ANSI CL 123 implementations or SQL databases, but is not particularly interested 124 in filling it full of ugly workarounds if the problem is with the 125 Lisp/RDBMS rather than with Septeql. Done that too often already, 126 thanks. 127 128 129 * Object-Relational Mapping: a short rant 130 131 "And that means the Great Orm will come in the night and wind out my 132 entrails on a stick!" 133 "Does it?" 134 "And suck out my eyes, my mother said" 135 "Gosh!" said Teppic, fascinated. "Really?" He was quite glad his 136 bed was opposite Arthur's and would offer an unrivalled view. 137 138 - Terry Pratchett, Pyramids 139 140 141 Everyone who was anywhere near the dotcom boom has written an ORM at 142 some time in their life. Some of us who Just Don't Learn have written 143 several. 144 145 I'm going to ignore the fundamental theoretical problems with 146 declaring tuples and instances to be equivalent. This is because (a) 147 I don't pretend to understand them, I only know that C J Date is 148 smarter than I am [3], and (b) nobody who works with databases in the 149 so-called Real World cares one jot or tittle for the theory anyway. 150 So let's just say 151 152 * having to keep the object schema in sync with the database schema is 153 burdensome. You might have tools to autogenerate an empty database 154 from your object definitions, but being able to automatically migrate 155 all your existing data when you make schema changes is a lot rarer. 156 157 * the OO chase-the-pointers style of data interrogation makes for a 158 million DB roundtrips, which is grossly inefficient. Better to do the 159 joins in the database and get everything you need in one step, but how 160 do you squish that back into objects? 161 162 * object identity usually goes out the window when you can retrieve 163 the same row by more than one route. And semi-reliable object 164 identity is worse than none. 165 166 * there isn't a 1:1 relationship between entities in the problem 167 domain and tables in the database. If you have a Colour class that 168 exists solely to map onto the COLOUR table (because you can't put all 169 its contents in a dropdown list otherwise), I'd call that a code 170 smell. You'll never use it outside of the CRUD screens. 171 172 173 [1] Well, they're my friends anyway. They talk to me when I sleep, 174 you know. They tell me what to do. 175 176 [2] If we were talking to a proper relational system, there would be no 177 ordering and no duplicate rows. In the so-called Real World, however ... 178 179 [3] I only hope I can stay out of the dbdebunk.com Hall of Shame, is all.