Commits

Shantanu Kumar committed 77f6b0c

clojure 1.3 compatibility
support JDBC-ODBC bridge (both regular and lite)
support Firebird database using Type-4 JDBC driver
test with OSS-JDBC 0.4

Comments (0)

Files changed (7)

 
 - [TODO] Test-cases for untested databases:
   Oracle, IBM DB2, SQL Server, Sybase
+- [TODO] Support Excel files using JDBC driver
+
+
+## 0.4 / 2011-Jan-??
+
+- Clojure 1.3 compatibility
+- Support for JDBC-ODBC bridge - both regular and vanilla/lite
+- Support for Firebird database using Type 4 JDBC driver
+- Test with OSS-JDBC 0.4
 
 
 ## 0.3 / 2010-Dec-29
   <groupId>org.bituf</groupId>
   <artifactId>clj-dbcp</artifactId>
   <packaging>jar</packaging>
-  <version>0.3</version>
+  <version>0.4-SNAPSHOT</version>
   <name>Clj-DBCP</name>
   <description>
     Clj-DBCP is a simple Java-6/Clojure wrapper around the Apache DBCP library
       <groupId>org.clojure</groupId>
       <artifactId>clojure-contrib</artifactId>
       <version>1.2.0</version>
+      <scope>test</scope>
       <optional>true</optional>
     </dependency>
     <!-- Apache DBCP -->
     <dependency><!-- All Open Source JDBC Drivers available via Maven -->
       <groupId>org.bituf</groupId>
       <artifactId>oss-jdbc</artifactId>
-      <version>0.3</version>
+      <version>0.4-SNAPSHOT</version>
       <scope>test</scope>
     </dependency>
     <dependency><!-- Simple-JNDI: Emulate JNDI container -->

src/main/clj/org/bituf/clj_dbcp.clj

 (ns org.bituf.clj-dbcp
+  (:import
+    (java.sql DriverManager)
+    (org.bituf.clj_dbcp ConnectionWrapper))
+  (:import javax.sql.DataSource)
   (:import org.apache.commons.dbcp.BasicDataSource))
 
-(def *show-jdbc-url* false)
+
+(def ^{:doc "Boolean flag (default false) - prints the JDBC URL if true"
+       :dynamic true}
+      *show-jdbc-url* false)
 
 
 (defstruct datasource-args
       (println "\n**** Derby JDBC URL ****\n" url))
     (make-datasource-args
       "org.apache.derby.jdbc.EmbeddedDriver" ; classname: must be in classpath
-      url ; url
-      "sa" ; username
-      "sx" ; password
-      )))
+      url "sa" "sx"))) ; url, username and password
 
 
 (defn derby-datasource
       (println "\n**** H2 JDBC URL ****\n" url))
     (make-datasource-args
       "org.h2.Driver" ; classname: must be in classpath
-      url ; url
-      "sa" ; username
-      ""   ; password
-      )))
+      url "sa" ""))) ; url, username and password
 
 
 (defn h2-datasource
       (println "\n**** HSQL JDBC URL ****\n" url))
     (make-datasource-args
       "org.hsqldb.jdbcDriver" ; classname: must be in classpath
-      url ; url
-      "sa" ; username
-      ""   ; password
-      )))
+      url "sa" ""))) ; url, username and password  
 
 
 (defn hsql-datasource
     (mysql-args \"localhost\"      \"sales\" \"salesuser\" \"secret\")
     (mysql-args \"localhost:3306\" \"emp\"   \"empuser\"   \"SeCrEt\")"
   [db-host:port db-name username password]
-  (make-datasource-args
-    "com.mysql.jdbc.Driver" ; classname: must be in classpath
-    (str "jdbc:mysql://" db-host:port "/" db-name) ; url
-    username ; username
-    password ; password
-    ))
+  (let [url (str "jdbc:mysql://" db-host:port "/" db-name)]
+    (if *show-jdbc-url*
+      (println "\n**** MySQL JDBC URL ****\n" url))
+    (make-datasource-args
+      "com.mysql.jdbc.Driver" ; classname: must be in classpath
+      url username password)))
 
 
 (defn mysql-datasource
     (pgsql-args \"localhost\"      \"sales\" \"salesuser\" \"secret\")
     (pgsql-args \"localhost:5432\" \"emp\"   \"empuser\"   \"SeCrEt\")"
   [db-host:port db-name username password]
-  (make-datasource-args
-    "org.postgresql.Driver" ; classname: must be in classpath
-    (str "jdbc:postgresql://" db-host:port "/" db-name) ; url
-    username ; username
-    password ; password
-    ))
+  (let [url (str "jdbc:postgresql://" db-host:port "/" db-name)]
+    (if *show-jdbc-url*
+      (println "\n**** PostgreSQL JDBC URL ****\n" url))
+    (make-datasource-args
+      "org.postgresql.Driver" ; classname: must be in classpath
+      url username password)))
 
 
 (defn pgsql-datasource
 ;
 (defn oracle-args
   "Create datasource args for the Oracle database. Oracle JDBC URLs usually
-  look like these:
+  look like these (using Type-4 thin driver):
     jdbc:oracle:thin:[user/password]@[host][:port]:SID
     jdbc:oracle:thin:[user/password]@//[host][:port]/SID
   Arguments:
     (oracle-args \"localhost\"      \"sales\" \"salesuser\" \"secret\")
     (oracle-args \"localhost:1521\" \"emp\"   \"empuser\"   \"SeCrEt\")"
   [db-host:port system-id username password]
-  (make-datasource-args
-    "oracle.jdbc.driver.OracleDriver" ; classname: must be in classpath
-    (str "jdbc:oracle:thin:@//" db-host:port "/" system-id) ; url
-    username ; username
-    password ; password
-    ))
+  (let [url (str "jdbc:oracle:thin:@//" db-host:port "/" system-id)]
+    (if *show-jdbc-url*
+      (println "\n**** Oracle JDBC URL ****\n" url))
+    (make-datasource-args
+      "oracle.jdbc.driver.OracleDriver" ; classname: must be in classpath
+      url username password)))
 
 
 (defn oracle-datasource
     (db2-args \"localhost\"       \"sales\" \"salesuser\" \"secret\")
     (db2-args \"localhost:50000\" \"emp\"   \"empuser\"   \"SeCrEt\")"
   [db-host:port db-name username password]
-  (make-datasource-args
-    "com.ibm.db2.jcc.DB2Driver" ; classname: must be in classpath
-    (str "jdbc:db2://" db-host:port "/" db-name) ; url
-    username ; username
-    password ; password
-    ))
+  (let [url (str "jdbc:db2://" db-host:port "/" db-name)]
+    (if *show-jdbc-url*
+      (println "\n**** DB2 JDBC URL ****\n" url))
+    (make-datasource-args
+      "com.ibm.db2.jcc.DB2Driver" ; classname: must be in classpath
+      url username password)))
 
 
 (defn db2-datasource
   See also: (1) props-str function
             (2) http://jtds.sourceforge.net/faq.html"
   [server-type db-host:port db-name username password properties]
-  (make-datasource-args
-    "net.sourceforge.jtds.jdbc.Driver" ; classname: must be in classpath
-    ;; JDBC URL
-    (format "jdbc:jtds:%s://%s%s%s" server-type db-host:port
-      (and db-name (str "/" db-name)) ; database name (can be empty)
-      (props-str properties)) ; properties (can be empty)
-    username ; username
-    password ; password
-    ))
+  (let [url (format "jdbc:jtds:%s://%s%s%s" server-type db-host:port
+              (and db-name (str "/" db-name)) ; database name (can be empty)
+              (props-str properties)) ; properties (can be empty)
+        ]
+    (if *show-jdbc-url*
+      (println "\n**** jTDS JDBC URL ****\n" url))
+    (make-datasource-args
+      "net.sourceforge.jtds.jdbc.Driver" ; classname: must be in classpath
+      ;; JDBC URL
+      url
+      username ; username
+      password ; password
+      )))
 
 
 (defn jtds-datasource
   See also: http://wiki.cubrid.org/index.php/CUBRID_Manuals/cubrid_2008_R3.0_manual/Getting_Started_with_CUBRID/JDBC_Sample
             http://j.mp/fS5Evv (short URL of the above)"
   [db-host:port db-name username password]
-  (make-datasource-args
-    "cubrid.jdbc.driver.CUBRIDDriver" ; classname: must be in classpath
-    (format "jdbc:cubrid:%s:%s:::" db-host:port db-name) ; url
-    username ; username
-    password ; password
-    ))
+  (let [url (format "jdbc:cubrid:%s:%s:::" db-host:port db-name)]
+    (if *show-jdbc-url*
+      (println "\n**** CUBRID JDBC URL ****\n" url))
+    (make-datasource-args
+      "cubrid.jdbc.driver.CUBRIDDriver" ; classname: must be in classpath
+      url
+      username ; username
+      password ; password
+      )))
 
 
 (defn cubrid-datasource
         ds   (create-datasource args)]
     (set-validation-query! ds
       "SELECT 1;")))
+
+
+; === Firebird config ===
+;
+(defn firebird-args
+  "Create datasource args for the Firebird database. Firebird uses TCP/IP only
+  for remote connections on port 3050 as default. The JDBC URL looks like this:
+    jdbc:firebirdsql://<host>[:port]/<database> - (using Type-4 driver)
+  where <database> is the path of the database file usually ending in \".gdb\",
+  for example:
+    jdbc:firebirdsql://localhost/C:/temp/bituf.gdb
+    jdbc:firebirdsql://cherryhost:3050//home/joe/finance.gdb
+  Arguments:
+    db-host:port  (String) database hostname and :port-no (colon, then port-no)
+    db-path       (String) database filesystem path to connect to
+    username      (String) database username
+    password      (String) password for the database user
+  Examples:
+    (firebird-args \"localhost\"      \"sales\" \"salesuser\" \"secret\")
+    (firebird-args \"localhost:3050\" \"emp\"   \"empuser\"   \"SeCrEt\")
+  See also: http://www.firebirdsql.org/index.php?op=devel&sub=jdbc&id=faq
+            (Some information in the FAQ is incorrect as of 02 Jan 2011.)
+            http://j.mp/eKzikA (short URL of the above)
+            http://tech.groups.yahoo.com/group/Firebird-Java/message/10365
+            http://j.mp/hf5Vtj (short URL of the above)"
+  [db-host:port db-path username password]
+  (let [url (format "jdbc:firebirdsql://%s/%s" db-host:port db-path)]
+    (if *show-jdbc-url*
+      (println "\n**** Firebird JDBC URL ****\n" url))
+    (make-datasource-args
+      "org.firebirdsql.jdbc.FBDriver" ; classname: must be in classpath
+      url username password)))
+
+
+(defn firebird-datasource
+  "Create Firebird data source - use this with Type-4 JDBC driver only.
+  See also: firebird-args"
+  [db-host:port db-path username password]
+  (let [args (firebird-args
+               db-host:port db-path username password)
+        ds   (create-datasource args)]
+    (set-validation-query! ds
+      "SELECT CAST(1 AS INTEGER) FROM rdb$database;")))
+
+
+; === JDBC-ODBC bridge config ===
+;
+(defn odbc-args
+  "Create datasource args for an ODBC DSN. The URL looks like this:
+     jdbc:odbc:<dsn><semicolon-separated-keyval-properties>
+  Arguments:
+    odbc-dsn   (String) ODBC DSN
+    username   (String) database username
+    password   (String) password for the database user
+    properties (String, can be nil) properties delimited by semicolon
+               (map)    {k1 v1 k2 v2 ...}
+  Examples:
+    (odbc-args \"ExcelSales\" \"salesuser\" \"secret\")
+  See also: http://download.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/bridge.doc.html
+            http://j.mp/hNatr1 (short URL of the above)"
+  [odbc-dsn username password properties]
+  (let [url (format "jdbc:odbc:%s%s" odbc-dsn (props-str properties))]
+    (if *show-jdbc-url*
+      (println "\n**** JDBC-ODBC Bridge URL ****\n" url))
+    (make-datasource-args
+      "sun.jdbc.odbc.JdbcOdbcDriver" ; classname: provided by JDK
+      url username password)))
+
+
+(defn odbc-datasource
+  "Create ODBC data source - use this for ODBC DSNs using JDBC-ODBC Bridge.
+  See also: odbc-args"
+  ([odbc-dsn username password properties]
+    (let [args (odbc-args
+                 odbc-dsn username password properties)
+          ds   (create-datasource args)]
+      (set-validation-query! ds
+        "SELECT 1;")
+      ds))
+  ([odbc-dsn username password]
+    (odbc-datasource odbc-dsn username password nil))
+  ([odbc-dsn]
+    (odbc-datasource odbc-dsn "" "" nil)))
+
+
+;; ===== Vanilla DataSource implementation =====
+
+
+(defn ^DataSource vanilla-datasource
+  [^String driver-class ^String url]
+  (let [ignore ["setAutoCommit"]]
+    (proxy [DataSource] []
+      (getConnection
+        ([]
+          (Class/forName driver-class)
+          (ConnectionWrapper.
+            (DriverManager/getConnection url) ignore))
+        ([^String username ^String password]
+          (Class/forName driver-class)
+          (ConnectionWrapper.
+            (DriverManager/getConnection url username password) ignore))))))
+
+
+(defn ^DataSource odbc-vanilla-datasource
+  ([^String dsn properties]
+    (let [url (format "jdbc:odbc:%s%s" dsn (props-str properties))]
+      (if *show-jdbc-url*
+        (println "\n**** JDBC-ODBC Bridge URL for vanilla DataSource ****\n" url))
+      (vanilla-datasource
+        "sun.jdbc.odbc.JdbcOdbcDriver" ; driver classname
+        url)))
+  ([^String dsn]
+    (odbc-vanilla-datasource dsn nil)))

src/main/java/org/bituf/clj_dbcp/ConnectionWrapper.java

+package org.bituf.clj_dbcp;
+
+import java.sql.Array;
+import java.sql.Blob;
+import java.sql.CallableStatement;
+import java.sql.Clob;
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.NClob;
+import java.sql.PreparedStatement;
+import java.sql.SQLClientInfoException;
+import java.sql.SQLException;
+import java.sql.SQLWarning;
+import java.sql.SQLXML;
+import java.sql.Savepoint;
+import java.sql.Statement;
+import java.sql.Struct;
+import java.util.Collection;
+import java.util.Map;
+import java.util.Properties;
+
+public class ConnectionWrapper implements Connection {
+
+    public static final String SET_AUTO_COMMIT = "setAutoCommit";
+    public static final String GET_AUTO_COMMIT = "getAutoCommit";
+    
+    private final Connection conn;
+    private final Collection<String> ignore;
+    
+    public ConnectionWrapper(Connection conn, Collection<String> ignore) {
+        this.conn = conn;
+        this.ignore = ignore;
+    }
+
+    public <T> T unwrap(Class<T> iface) throws SQLException {
+        return conn.unwrap(iface);
+    }
+
+    public boolean isWrapperFor(Class<?> iface) throws SQLException {
+        return conn.isWrapperFor(iface);
+    }
+
+    public Statement createStatement() throws SQLException {
+        return conn.createStatement();
+    }
+
+    public PreparedStatement prepareStatement(String sql) throws SQLException {
+        return conn.prepareStatement(sql);
+    }
+
+    public CallableStatement prepareCall(String sql) throws SQLException {
+        return conn.prepareCall(sql);
+    }
+
+    public String nativeSQL(String sql) throws SQLException {
+        return conn.nativeSQL(sql);
+    }
+
+    public void setAutoCommit(boolean autoCommit) throws SQLException {
+        if (!ignore.contains(SET_AUTO_COMMIT)) {
+            conn.setAutoCommit(autoCommit);
+        }
+    }
+
+    public boolean getAutoCommit() throws SQLException {
+        if (!ignore.contains(GET_AUTO_COMMIT)) {
+            return conn.getAutoCommit();
+        }
+        return false;
+    }
+
+    public void commit() throws SQLException {
+        conn.commit();
+    }
+
+    public void rollback() throws SQLException {
+        conn.rollback();
+    }
+
+    public void close() throws SQLException {
+        conn.close();
+    }
+
+    public boolean isClosed() throws SQLException {
+        return conn.isClosed();
+    }
+
+    public DatabaseMetaData getMetaData() throws SQLException {
+        return conn.getMetaData();
+    }
+
+    public void setReadOnly(boolean readOnly) throws SQLException {
+        conn.setReadOnly(readOnly);
+    }
+
+    public boolean isReadOnly() throws SQLException {
+        return conn.isReadOnly();
+    }
+
+    public void setCatalog(String catalog) throws SQLException {
+        conn.setCatalog(catalog);
+    }
+
+    public String getCatalog() throws SQLException {
+        return conn.getCatalog();
+    }
+
+    public void setTransactionIsolation(int level) throws SQLException {
+        conn.setTransactionIsolation(level);
+    }
+
+    public int getTransactionIsolation() throws SQLException {
+        return conn.getTransactionIsolation();
+    }
+
+    public SQLWarning getWarnings() throws SQLException {
+        return conn.getWarnings();
+    }
+
+    public void clearWarnings() throws SQLException {
+        conn.clearWarnings();
+    }
+
+    public Statement createStatement(int resultSetType, int resultSetConcurrency)
+            throws SQLException {
+        return conn.createStatement();
+    }
+
+    public PreparedStatement prepareStatement(String sql, int resultSetType,
+            int resultSetConcurrency) throws SQLException {
+        return conn.prepareStatement(sql, resultSetType, resultSetConcurrency);
+    }
+
+    public CallableStatement prepareCall(String sql, int resultSetType,
+            int resultSetConcurrency) throws SQLException {
+        return conn.prepareCall(sql, resultSetType, resultSetConcurrency);
+    }
+
+    public Map<String, Class<?>> getTypeMap() throws SQLException {
+        return conn.getTypeMap();
+    }
+
+    public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
+        conn.setTypeMap(map);
+    }
+
+    public void setHoldability(int holdability) throws SQLException {
+        conn.setHoldability(holdability);
+    }
+
+    public int getHoldability() throws SQLException {
+        return conn.getHoldability();
+    }
+
+    public Savepoint setSavepoint() throws SQLException {
+        return conn.setSavepoint();
+    }
+
+    public Savepoint setSavepoint(String name) throws SQLException {
+        return conn.setSavepoint(name);
+    }
+
+    public void rollback(Savepoint savepoint) throws SQLException {
+        conn.rollback(savepoint);
+    }
+
+    public void releaseSavepoint(Savepoint savepoint) throws SQLException {
+        conn.releaseSavepoint(savepoint);
+    }
+
+    public Statement createStatement(int resultSetType,
+            int resultSetConcurrency, int resultSetHoldability)
+            throws SQLException {
+        return conn.createStatement(resultSetType, resultSetConcurrency,
+                resultSetHoldability);
+    }
+
+    public PreparedStatement prepareStatement(String sql, int resultSetType,
+            int resultSetConcurrency, int resultSetHoldability)
+            throws SQLException {
+        return conn.prepareStatement(sql, resultSetType, resultSetConcurrency,
+                resultSetHoldability);
+    }
+
+    public CallableStatement prepareCall(String sql, int resultSetType,
+            int resultSetConcurrency, int resultSetHoldability)
+            throws SQLException {
+        return conn.prepareCall(sql, resultSetType, resultSetConcurrency,
+                resultSetHoldability);
+    }
+
+    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys)
+            throws SQLException {
+        return conn.prepareStatement(sql, autoGeneratedKeys);
+    }
+
+    public PreparedStatement prepareStatement(String sql, int[] columnIndexes)
+            throws SQLException {
+        return conn.prepareStatement(sql, columnIndexes);
+    }
+
+    public PreparedStatement prepareStatement(String sql, String[] columnNames)
+            throws SQLException {
+        return conn.prepareStatement(sql, columnNames);
+    }
+
+    public Clob createClob() throws SQLException {
+        return conn.createClob();
+    }
+
+    public Blob createBlob() throws SQLException {
+        return conn.createBlob();
+    }
+
+    public NClob createNClob() throws SQLException {
+        return conn.createNClob();
+    }
+
+    public SQLXML createSQLXML() throws SQLException {
+        return conn.createSQLXML();
+    }
+
+    public boolean isValid(int timeout) throws SQLException {
+        // TODO Auto-generated method stub
+        return false;
+    }
+
+    public void setClientInfo(String name, String value)
+            throws SQLClientInfoException {
+        conn.setClientInfo(name, value);
+    }
+
+    public void setClientInfo(Properties properties)
+            throws SQLClientInfoException {
+        conn.setClientInfo(properties);
+    }
+
+    public String getClientInfo(String name) throws SQLException {
+        return conn.getClientInfo(name);
+    }
+
+    public Properties getClientInfo() throws SQLException {
+        return conn.getClientInfo();
+    }
+
+    public Array createArrayOf(String typeName, Object[] elements)
+            throws SQLException {
+        return conn.createArrayOf(typeName, elements);
+    }
+
+    public Struct createStruct(String typeName, Object[] attributes)
+            throws SQLException {
+        return conn.createStruct(typeName, attributes);
+    }
+
+}

src/test/clj/org/bituf/test_clj_dbcp.clj

   (test-crud (db-spec datasource)))
 
 
+(deftest test-odbc
+  ;; vanilla/lite ODBC DSNs (e.g. Excel, Access etc - no advanced ODBC features)
+  (test-datasource
+    (odbc-vanilla-datasource (dbcred :odbc.vanilla.dsn)))
+  ;; regular full-blown ODBC DSNs (e.g. CUBRID, Oracle)
+  (test-datasource
+    (odbc-datasource (dbcred :odbc.regular.dsn))))
+
+
 (deftest test-derby
   ;; in-memory (default dbname)
   (test-datasource
     (sqlite-filesystem-datasource))
   ;; file system
   (test-datasource
-    (sqlite-filesystem-datasource "sqlitefile"))
-  )
+    (sqlite-filesystem-datasource "sqlitefile")))
 
 
 (defn test-network-datasource
         ((K :password) dbcred)))))
 
 
+(deftest test-firebird
+  (if (tu/is-true? (:firebird.testconn dbcred))
+    (test-network-datasource "firebird" firebird-datasource)
+    (fail "Firebird not tested")))
+
+
 (deftest test-mysql
   (if (tu/is-true? (:mysql.testconn dbcred))
     (test-network-datasource "mysql" mysql-datasource)
 
 
 (defn test-ns-hook []
+  (test-odbc)
   (test-derby)
   (test-h2)
   (test-hsql)
   (test-sqlite)
+  (test-firebird)
   (test-mysql)
   (test-cubrid)
   (test-pgsql)

src/test/clj/org/bituf/test_util.clj

 
 
 (def ^{:doc "Typically bound to javax.naming.Context"
-       :tag Context}
+       :dynamic true :tag Context}
       *root-context* nil)
 
 
-(def ^{:doc "Typically bound to an integer wrapped in an atom, e.g. (atom 0)"}
+(def ^{:doc "Typically bound to an integer wrapped in an atom, e.g. (atom 0)"
+       :dynamic true}
       *indent* nil)
 
 

src/test/dbcred.properties

+# JDBC-ODBC bridge DSNs
+odbc.regular.dsn=cubrid-bituf
+odbc.vanilla.dsn=excel-bituf
+
+# Firebird credentials
+# hostport can also be in the form "localhost:3050"
+firebird.testconn=true
+firebird.hostport=localhost
+firebird.database=D:/temp/bituf.gdb
+firebird.username=root
+firebird.password=root
+
 # MySQL credentials
 # hostport can also be in the form "localhost:3306"
 mysql.testconn=true
 mysql.password=root
 
 # CUBRID credentials
-# hostport can also be in the form "localhost:33000"
+# hostport is supposed to be in the form "localhost:33000"
 cubrid.testconn=true
 cubrid.hostport=localhost:33000
 cubrid.database=bituf
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.