DBMS-DEPENDENT BITS OF SNOOZE
DBMS-DEPENDENT BITS OF SNOOZE
Bits of the Snooze code that change from DBMS to DBMS:
Actually, most of this stays the same.
Most of the data manipulation (SELECT / INSERT / UPDATE / DELETE) syntax is cross-DBMS.
The data creation syntax has many bespoke elements.
Some ubiquitous "little things" are different: most notably quoting of identifiers. For example:
[my-table] in SQLite
`my-table` in MySQL
"my-table" in PostgreSQL
PASSING SQL TO THE DBMS
Once the SQL has been generated, we'll need a DBMS dependent way of actually running queries with it.
I believe the distinction between SQL generation and passing SQL to the DBMS to be an important one... we ought to recognise it now and structure the code accordingly. I have some kerayzee ideas for things like stored queries that would benefit from the distinction.
INTERPRETATION OF RESULT SETS
Of course, different database libraries serve different result sets.
We will need custom code for the creation of persistent structs for each library.
I propose two signatures: one for generating SQL (sql^) and one for interacting with the DB library (db^).
The SQL unit will benefit greatly from the work Noel and I did on de-syntaxifying sql-*.ss:
signature sql^ :
; Main functions:
create-sql : entity -> string
drop-sql : entity -> string
insert-sql : persistent-struct -> string
update-sql : persistent-struct -> string
delete-sql : persistent-struct -> string
v200:count-sql : select -> string
v200:select-sql : select -> string
join-sql : join -> string
; Internal functions:
alias-what-sql : alias -> string
alias-from-sql : alias -> string
expr-sql : expr -> string
ref-sql : ref -> string
order-sql : order -> string
limit-sql : limit -> string
; Super-internal functions:
quote-identifier : symbol -> string
quote-data : type any -> string
unquote-data : type string -> any
QUESTION: Is there any way we can rig this so it's easy to override some functions and not others? For example, the meat of v200:select-sql will be the same across the board, but quote-identifier will not be... could we, for example, have internal versions of the functions that pass the unit in as an argument so they can delegate to whatever implementation is necessary:
v200:select-sql* : sql^ select-query -> string
The DB unit will be a little simpler:
disconnect : any -> void ; again, any = connection object
; Data definition:
create-table : entity -> void
drop-table : entity -> void
; Data manipulation:
insert-struct : persistent-struct -> void
update-struct : persistent-struct -> void
delete-struct : persistent-struct -> void
find-count : select -> result
find-all : select -> (list-of result)
find-one : select -> result
with-transaction : thunk -> void
; Handy functions:
; (delete-by-id is an extra that I'd like to have)
find-by-id : entity integer -> persistent-struct
delete-by-id : entity integer -> void
and we'll have a DBMS-specific connect function to create the unit:
sqlite-connect : sqlite-config -> db^
mysql-connect : mysql-config -> db^
I suppose we create a package per database type:
and stick a file of the same name in each:
that exports the db^ unit and a configuration structure:
(module sqlite mzscheme
(struct sqlite-config (path))
we don't need to export the sql^ unit - it will only be referred to internally just to keep our code structured nicely.
It might be nice to have a "general" package containing utility functions for the units:
we can provide a stock unit general-sql@ that exports standard versions of all the relevant functions.
We might also be able to extract some generic code for interpreting results-sets.
WRAPPER FUNCTIONS AND SYNTAXES
The main body of non-DBMS-specific Snooze code will then become wrappers for whichever DB unit is currently loaded. We change with-database to be:
syntax with-database : db^ config expr ...
or maybe we desyntaxify it:
with-database : db^ config thunk -> void
Whichever strategy we adopt, we change current-connection to be:
parameter current-db : db^
and that'll be enough to pass all the relevant calls through.