Hi Dan,
On 4/11/2016 6:51 PM, Daniel Prager wrote:
On Apr 12, 2016 7:53 AM, "George Neuner" <gneun...@comcast.net> wrote:
>
> My most common uses are to handle database connections and
> to embed free form SQL into Racket code.
Care to post one of your examples, with a bit of commentary?
Ok. I have attached the definition file for my SQL embedding macro. It
is a relatively simple minded syntax transformer, but it needs some
auxiliary functions so it is inconvenient to repeat it all here. I hope
the attachment survives to the mailing list.
First off, I want to say that the db module in Racket is awesome!
However ... like other database access libraries, it can be inconvenient
to compose complex queries because the functions expect SQL code to be
provided as a string.
Racket has multi-line string literals (#<<) which are suitable for
embedding *static* SQL [if you don't mind delimiters in the margin].
But AFAICS there is no way to splice environment references into a
multi-line literal - i.e. quasiquote/unquote(-splicing) - and so they
can't easily be used for dynamic SQL. And there are many needs for
dynamic SQL: in particular table names cannot be variables, so queries
that are reusable against multiple tables cannot be written statically.
Not to mention that multi-line strings won't auto-indent, and any
whitespace you might include for readability ends up in the code
string. SQL ignores whitespace outside of strings, but lots of
whitespace in the code possibly lengthens transmission time to a remote
DBMS and parse time for the query, and so may impact your application
performance (however slightly) as you repeatedly submit overly long code
strings.
So you - or at least *I* - end up with a lot of code that looks like:
(set! sql-cmd (string-join `( ; <- note quasiquote
:
",business as"
" (select " ,(prefix-fields (business-fields) "B")
" from (select distinct biz from stores) as S"
" join businesses as B on B.id = S.biz"
,(if search-txt
"join acts as A on A.biz = S.biz"
"" )
" )"
:
",ratings (biz,reviews,score) as"
" (select B.id"
" ,count_reviews( (select * from survey), B.id )"
" ,score_survey ( (select * from survey), B.id, 100 )"
" from (select id from business) as B"
" )"
:
)))
(set! result (query-rows db sql-cmd ... )
These CTEs are snippets from a 73 line query in one of my applications.
Not terribly easy to read even with DrRacket's syntax coloring: just
strings and little snips of Racket. Inefficient because the SQL code
string is being composed at runtime. I have timed string-join: it is
quite a bit slower to join lots of short strings than to join fewer long
strings.
So I created the code in embed_sql.rkt. The language it accepts is not
SQL precisely but a hybrid of SQL and Racket. For example, double
quoted Racket strings are converted to single quoted SQL strings. I
borrowed Racket's bytestrings to represent SQL strings that should be
double quoted: e.g., strange table names. Because commas are used
natively in SQL, I borrowed unquote-splicing ,@ syntax to embed Racket
expressions into SQL. Racket style comments are ignored everywhere.
SQL style -- comments aren't supported.
The mess above can be written [more nicely I think] as:
(set! sql-cmd
(SQL
:
,business as
(select ,@(prefix-fields (business-fields) "B")
from (select distinct biz from stores) as S
join businesses as B on B.id = S.biz
,@(if search-txt
(SQL join acts as A on A.biz = S.biz)
(SQL))
)
:
,ratings (biz,reviews,score) as
(select B.id
,count_reviews( (select * from survey), B.id )
,score_survey ( (select * from survey), B.id, 100 )
from (select id from business) as B
)
:
)
The macro turns static SQL into a simple string, and dynamic SQL into a
string-join quasiquoted form. It ignores whitespace and minimizes the
number of string fragments passed to the eventual string-join (which
will be done at runtime).
The macro can be used wherever a string is expected, so it can be used
directly to supply the command argument to a query function call. It
works ad hoc in the REPL assuming any embedded Racket code is valid.
It isn't perfect ... in particular errors occurring in embedded Racket
expressions can be hard to figure out. But it has handled all the SQL I
have thrown at it and I have used it in a number of applications. I'd
like eventually to improve it with some syntax coloring, but I haven't
figured out how to do that (or even if it's possible).
YMMV. Everyone may feel free to laugh [privately!] at the code.
George
--
You received this message because you are subscribed to the Google Groups "Racket
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
#lang racket/base
(require
(for-syntax racket/base)
(for-syntax racket/string)
(for-syntax racket/match)
(for-syntax racket/list)
)
(provide SQL)
; %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;
; syntax transformer to enabled embedding free format
; SQL code anywhere an SQL string is expected
;
; - "" strings are converted to SQL '' strings
; - #"" bytestrings are converted to SQL "" strings
; - use ,@<expr> to embed Racket code (unquote-splicing syntax)
;
; - Racket comments are ignored
; - SQL line comments (--) are not supported
;
(define-syntax (SQL stx)
(let* [
(source (cdr (syntax->datum stx)))
(ir (sql-code-walk source))
(fir (sql-flatten ir ))
(code (sql-condense fir ))
(static (andmap string? code ))
(result (if static
; result is the static code string
(car code)
; result is code to create the string at runtime
(list 'string-join (list 'quasiquote code))
))
]
;(eprintf "=> ~s\n\n" source)
;(eprintf "=> ~s\n\n" ir )
;(eprintf "=> ~s\n\n" fir )
;(eprintf "=> ~s\n\n" code )
;(eprintf "=> ~s\n\n" result)
(datum->syntax stx result)
))
; %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;
; walk a tree of SQL code converting syntax symbols to strings
;
; - convert "" string to SQL '' string
; - convert #"" bytestring to SQL "" string
; - convert (unquote ...) forms to comma separation
; - convert (unquote-splicing ...) forms to (unquote ...) forms
;
(define-for-syntax (sql-code-walk in)
(match in
[(? empty?)
""
]
[(? list?)
(for/list [(term in)]
(match term
[(? string?)
; output a SQL '' string
(format "'~a'" term)
]
[(? bytes?)
; output a SQL "" string
(format "\"~a\"" term)
]
[(list 'unquote form)
; output a comma and walk the remainder
(append '(",") (sql-code-walk (list form)))
]
[(list 'unquote-splicing form)
; change to an unquote form
(list 'unquote form)
]
[(? list?)
; embedded expression: surround it with
; parantheses and walk the expression
(append '("(")
(sql-code-walk term)
'(")"))
]
[else
(format "~a" term)
]
))
]
))
; %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;
; flatten a tree of SQL code
; - leave (unquote ...) forms in place
;
(define-for-syntax (sql-flatten in)
(match in
[(? empty?)
'()
]
[(list 'unquote _)
(list in)
]
[(? list?)
(append (sql-flatten (car in))
(sql-flatten (cdr in)))
]
[else
(list in)
]
))
; %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
;
; coalesce strings in a list of SQL code
; - leave (unquote ...) forms in place
;
; note:
; for static SQL, coalescing allows pasting the code
; into Racket as a single string. for dynamic SQL,
; it reduces string-join work at runtime.
;
(define-for-syntax (sql-condense lst)
(let walker [
(in lst)
(out '())
]
(cond
; done. return output
[(empty? in)
(reverse out)
]
; found string item. coalesce consecutive
; strings, output the coalesced string and
; advance input to first non-string item
[(string? (car in))
(let-values
[((fst rst) (splitf-at in string?))]
(walker rst (cons (string-join fst) out))
)
]
; not a string - output it and advance
[else
(walker (cdr in) (cons (car in) out))
]
)
))
; %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
