Design Considerations
Some areas of concern are listed as tickets in JIRA already. Other areas are not yet well-formed enough to create specific, actionable tickets:
- DDL support is weak, e.g., JDBC-2 table spec support - DONE; added in 0.3.0 but more DDL support could (should?) be provided
- Inconsistencies between structmaps, tuples and regular maps (
JDBC-3); no longer uses structmap - DONE; new API (0.3.0) is more consistent about maps and sequences - Global connection variable (db) - DONE; addressed by new API in 0.3.0
- Lack of connection pooling (
JDBC-4); better served thru 3rd party pooling, e.g., c3p0 - DONE; documented - Inability to extend the processing, lack of HOF hooks, e.g.,
JDBC-6 allow pre-processing of PreparedStatement- partially DONE? - Updating state is not signified with ! - DONE; addressed by new API in 0.3.0
- API feels imperative, not functional / idiomatic for Clojure - DONE; addressed by new API in 0.3.0
Additional Suggestions
The following suggestions have been made and are under discussion:
- Overriding default identifiers and entities functions
- identifiers could be done via the db-spec since only the query function uses it (passed to result-set-seq)
- entities are only used by the DSL to generate SQL so it's not clear yet how this would interact with the db-spec
- Providing a canonical Database Specification a la Ring Spec
- Awaiting more information...
Supported Databases
What databases and versions should clojure.java.jdbc support? c.j.j is developed and tested against MySQL 5.0 and MySQL 5.1, HSQLDB, SQLite, Derby, MS SQL Server (both MS driver and jTDS driver). The following databases and versions are considered supported in 0.3.0:
- MySQL 5.0 - 5.5 (using the 5.1.6 connector)
- SQL Server - 2008 (should 2005 be supported?) - tested with jtds 1.2.4 and the Microsoft sqljdbc4 3.0 driver
- Derby 10.8.1.2 (should earlier versions be supported?)
- HSQLDB 1.8.0.10 (should earlier versions be supported?)
- SQLite 3 (using the org.xerial/sqlite-jdbc 3.7.2 driver)
Additionally, users of c.j.j are successfully running against:
- PostgreSQL - 8.2 upward (this is considered "supported" although it is not part of the general testbed)
- H2 (to be confirmed)
- Oracle (versions unknown)
- TeraData (??)
The build system needs to be updated to run tests against at least some of these. Currently c.j.j is unique in requiring such external resources for testing.
Labels:
7 Comments
Hide/Show CommentsJul 06, 2011
Stuart Sierra
SQLite might be a good one to support as well.
Jul 17, 2011
Sean Corfield
I might be more inclined to support H2 and Derby before SQLite. Thoughts?
Jul 19, 2011
Joe Gallo
also hsqldb, another pure-java database
Jul 19, 2011
Sean Corfield
0.0.5 just added support for HSQLDB - it cannot return generated keys from an insert so c.j.j was updated to accommodate that.
Jun 15, 2012
Sean Corfield
SQLite is supported now (and part of the build/test suite, along with Derby and HSQLDB).
Jun 22, 2012
Kevin Downey
{:op :select
:columns [{:name "some_col" :table "some_table"}
{:name "col2" :table "some_table2"}]
:where ['(= foo {:name "some_col" :table "some_table"})]
:primary-table "some_table"
:joins [{:type :left :target "some_table2"
:on ['(= {:name "some_col" :table "some_table"}
{:name "col2" :table "some_table2"})]}]
:parameters '[foo]}
;;=>
"SELECT some_col.some_table, col2.some_table2 FROM some_table LEFT OUTER JOIN some_table2 ON some_col.some_table = col2.some_table2 WHERE some_col.some_table = ?"
EIDT
corrected example generated sql
Jun 22, 2012
Kevin Downey
the advantages to having a data structure representation of sql expressions over strings or macros or an api is huge.
data can be manipulated with ease generically, apis and strings cannot.