Commits

Anonymous committed f413266

add JNDI data source API
add unit tests for JNDI data source API (test harness simple-jndi)
add (untested) jTDS (SQL Server and Sybase) data source API
move database credentials to properties file
add utility functions for handling Properties and JNDI tree printing

Comments (0)

Files changed (8)

 
 # Changes and TODO
 
-- [TODO] Move database credentials (unit tests) to properties file
-- [TODO] Support jTDS driver (MS SQL Server, Sybase)
-- [TODO] Add test-cases for untested databases: Oracle, IBM DB2
+- [TODO] Test-cases for untested databases: Oracle, IBM DB2, SQL Server, Sybase
+
+## 0.2 / 2010-Oct-14 (GMT+5:30)
+
 - [TODO] API for changing connection pool parameters (as of now it's Java API)
+- Move database credentials (unit tests) to properties file
+- JNDI datasource
+- Supported but not tested: jTDS (MS SQL Server, Sybase)
 
 ## 0.1 / 2010-Oct-05 (GMT+5:30)
 
   <groupId>org.bituf</groupId>
   <artifactId>clj-dbcp</artifactId>
   <packaging>jar</packaging>
-  <version>0.1</version>
+  <version>0.2-SNAPSHOT</version>
   <name>Clj-DBCP</name>
   <description>
     Clj-DBCP is a simple Java-6/Clojure wrapper around the Apache DBCP library
       <version>1.4</version>
     </dependency>
     <!-- JDBC Drivers -->
-    <dependency><!-- Apache Derby -->
-      <groupId>org.apache.derby</groupId>
-      <artifactId>derby</artifactId>
-      <version>10.6.1.0</version>
+    <dependency><!-- All Open Source JDBC Drivers available via Maven -->
+      <groupId>org.bituf</groupId>
+      <artifactId>oss-jdbc</artifactId>
+      <version>0.1</version>
       <scope>test</scope>
     </dependency>
-    <dependency><!-- Apache Derby Client -->
-      <groupId>org.apache.derby</groupId>
-      <artifactId>derbyclient</artifactId>
-      <version>10.6.1.0</version>
+    <dependency>
+      <groupId>simple-jndi</groupId>
+      <artifactId>simple-jndi</artifactId>
+      <version>0.11.4</version>
       <scope>test</scope>
     </dependency>
-    <dependency><!-- Apache Derby Tools (network server)-->
-      <groupId>org.apache.derby</groupId>
-      <artifactId>derbynet</artifactId>
-      <version>10.6.1.0</version>
-      <scope>test</scope>
-    </dependency>
-    <dependency><!-- H2 Database -->
-      <groupId>com.h2database</groupId>
-      <artifactId>h2</artifactId>
-      <version>1.2.140</version>
-      <scope>test</scope>
-    </dependency>
-    <dependency><!-- HSQLDB -->
-      <groupId>org.hsqldb</groupId>
-      <artifactId>hsqldb</artifactId>
-      <version>2.0.0</version>
-      <scope>test</scope>
-    </dependency>
-    <dependency><!-- MySQL Database -->
-      <groupId>mysql</groupId>
-      <artifactId>mysql-connector-java</artifactId>
-      <version>5.1.13</version>
-      <scope>test</scope>
-    </dependency>
-    <dependency><!-- Postgresql -->
-      <groupId>postgresql</groupId>
-      <artifactId>postgresql</artifactId>
-      <!-- <version>8.4-701.jdbc3</version> -->
-      <version>8.4-701.jdbc4</version>
-      <scope>test</scope>
-    </dependency>
-    <dependency><!-- SQL Server / Sybase -->
-      <groupId>net.sourceforge.jtds</groupId>
-      <artifactId>jtds</artifactId>
-      <version>1.2.4</version>
-      <scope>test</scope>
-    </dependency>
-    <!-- Firebird (Jaybird) -->
-    <!--
-    <dependency>
-      <groupId>org.firebirdsql.jdbc</groupId>
-      <artifactId>jaybird</artifactId>
-      <version>2.1.6</version>
-      <scope>test</scope>
-    </dependency>
-    -->
   </dependencies>
   <build>
     <plugins>

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

   {:datasource datasource})
 
 
+(defn jndi-datasource
+  "Lookup JNDI DataSource. Example Tomcat 6 configuration (/WEB-INF/web.xml):
+  <resource-ref>
+    <description>
+      Resource reference to a factory for java.sql.Connection
+      instances that may be used for talking to a particular
+      database that is configured in the <Context>
+      configurartion for the web application.
+    </description>
+    <res-ref-name>
+      jdbc/EmployeeDB
+    </res-ref-name>
+    <res-type>
+      javax.sql.DataSource
+    </res-type>
+    <res-auth>
+      Container
+    </res-auth>
+  </resource-ref>
+  You can fetch this datasource as follows:
+    (jndi-datasource \"java:comp/env/jdbc/EmployeeDB\")"
+  ([init-ctx resource-ref-name]
+    (.lookup init-ctx resource-ref-name))
+  ([resource-ref-name]
+    (jndi-datasource
+      (javax.naming.InitialContext.) resource-ref-name)))
+
+
 (def ^{:doc "Default name for the in-memory database"}
       default-db-name "default")
 
     username      (String) database username
     password      (String) password for the database user
   Examples:
-    (oracle-args \"localhost\"      \"sales\" \"salesuser\" \"secret\")
-    (oracle-args \"localhost:1521\" \"emp\"   \"empuser\"   \"SeCrEt\")"
+    (db2-args \"localhost\"       \"sales\" \"salesuser\" \"secret\")
+    (db2-args \"localhost:50000\" \"emp\"   \"empuser\"   \"SeCrEt\")"
   [db-host:port db-name username password]
   (struct datasource-args
     "com.ibm.db2.jcc.DB2Driver" ; classname: must be in classpath
         ds   (create-datasource args)]
     (set-validation-query! ds
       "select * from sysibm.SYSDUMMY1;")))
+
+
+;; ===== jTDS related =====
+
+(defn- props-str
+  "Build properties string for jTDS JDBC URL. If the input is a string, return
+  as it is. If it is a map, return like this: \";k1=v1;k2=v2...\" "
+  [properties]
+  (let [as-str #(if (keyword? %) (name %)
+                 (str %))]
+    (apply str
+      (if (map? properties) (map #(format ";%s=%s"
+                                    (as-str (first %)) (as-str (last %)))
+                              (seq properties))
+        properties))))
+
+
+(defn jtds-args
+  "Create datasource args for MS SQL Server database (Using jTDS driver).
+  jTDS URLs usually look like these:
+    jdbc:jtds:<server-type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]
+  Arguments:
+    server-type   (String) \"sqlserver\" or \"sybase\"
+    db-host:port  (String) database hostname (optionally followed by :port-no)
+    db-name       (String, can be nil) database name to connect to
+    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:
+    (jtds-args \"sqlserver\" \"localhost\" \"sales\" \"salesuser\" \"secret\")
+    (jtds-args \"sybase\"    \"localhost\" \"emp\"   \"empuser\"   \"SeCrEt\")
+  See also: (1) props-str function
+            (2) http://jtds.sourceforge.net/faq.html"
+  [server-type db-host:port db-name username password properties]
+  (struct 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
+    ))
+
+
+(defn jtds-datasource
+  "Create jTDS data source
+  See also: jtds-args"
+  [server-type db-host:port db-name username password properties]
+  (let [args (jtds-args
+               server-type
+               db-host:port db-name username password
+               properties)
+        ds   (create-datasource args)]
+    (set-validation-query! ds
+      "select 1;")))
+
+
+; === MS SQL Server config ===
+;
+(defn sqlserver-datasource
+  "Create MS SQL Server data source (default port 1433)
+  See also: jtds-args, jtds-datasource
+            http://jtds.sourceforge.net/faq.html"
+  ([db-host:port db-name username password properties]
+    (jtds-datasource
+      "sqlserver"
+      db-host:port db-name username password properties))
+  ([db-host:port db-name username password]
+    (sqlserver-datasource
+      db-host:port db-name username password nil))
+  ([db-host:port username password]
+    (sqlserver-datasource
+      db-host:port nil username password nil)))
+
+
+; === Sybase config ===
+;
+(defn sybase-datasource
+  "Create Sybase data source (default port 7100)
+  See also: jtds-args, jtds-datasource
+            http://jtds.sourceforge.net/faq.html"
+  ([db-host:port db-name username password properties]
+    (jtds-datasource
+      "sybase"
+      db-host:port db-name username password properties))
+  ([db-host:port db-name username password]
+    (sybase-datasource
+      db-host:port db-name username password nil))
+  ([db-host:port username password]
+    (sybase-datasource
+      db-host:port nil username password nil)))

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

+(ns org.bituf.clj-dbcp.util
+  "Miscellaneous utility functions that may be useful while using Clj-DBCP, e.g.
+  1. Reading properties files
+  2. JNDI tree printing (diagnostics only - NOT RECOMMENDED FOR PRODUCTION USE)
+     Adapted from: http://tripoverit.blogspot.com/2007/03/print-jndi-tree.html"
+  (:import (javax.naming Binding Context InitialContext))
+  (:import (javax.naming NameClassPair NamingEnumeration)))
+
+
+;; ===== Properties handling =====
+
+(defn property-map
+  "Transform a given Properties instance to a map."
+  [^java.util.Properties properties]
+  (let [ks (into [] (.stringPropertyNames properties))
+        vs (into [] (map #(.getProperty properties %) ks))]
+    (zipmap ks vs)))
+
+
+(defn strkey-to-keyword
+  "Given a map with every key a string, convert keys to keywords.
+  Input: {\"a\" 10 \"b\" \"20\"}
+  Returns: {:a 10 :b \"20\"}"
+  [m]
+  (assert (map? m))
+  (into {} (map #(let [k (first %)]
+                   (assert (string? k))
+                   [(keyword k) (last %)]) (seq m))))
+
+
+(defn true-value?
+  "Tell whether a given value is equivalent to true."
+  [any]
+  (if (string? any)
+    (or
+      (= "true" (.toLowerCase any))
+      (= "on"   (.toLowerCase any)))
+    (if (number? any)
+      (> any 0)
+      (true? any))))
+
+
+;; ===== JNDI diagnostic functions (NOT RECOMMENDED FOR PRODUCTION USE) =====
+
+(def *root-context* nil)
+
+
+(defmacro with-root-context
+  [root-context & body]
+  `(do
+    (assert (not (nil? ~root-context)))
+    (assert (instance? Context ~root-context))
+    (binding [*root-context* ~root-context]
+      ~@body)))
+
+
+(def indent (atom 0))
+
+
+(defn increase-indent []
+  (swap! indent #(+ % 4)))
+
+
+(defn decrease-indent []
+  (swap! indent #(- % 4)))
+
+
+(defn print-entry
+  [^NameClassPair next-elem]
+  (let [indent-str (apply str
+                     (take @indent
+                       (repeat " ")))]
+    (println indent-str "-->"
+      (.getName next-elem)
+      " (" (type next-elem) "->" (.getClassName next-elem) ")"
+      )))
+
+
+(declare print-jndi-tree)
+
+
+(defn print-ne
+  [^NamingEnumeration ne ^String parent-ctx]
+  (loop []
+    (when (.hasMoreElements ne)
+      (let [next-elem (.nextElement ne)]
+        (print-entry next-elem)
+        (increase-indent)
+        (if (or (instance? Context next-elem)
+              (and (instance? NameClassPair next-elem)
+                (instance? Context (.getObject next-elem))))
+          (print-jndi-tree
+            (if (zero? (.length parent-ctx))
+              (.getName next-elem)
+              (str parent-ctx "/" (.getName next-elem))))
+          (println "** Not drilling "
+            (type (.getObject next-elem))))
+        (decrease-indent))
+      (recur))))
+
+
+(defn print-jndi-tree
+  ([^String ct]
+    (assert (not (nil? ct)))
+    (if (instance? Context *root-context*)
+      (print-ne (.list *root-context* ct) ct)
+      (print-entry *root-context*)))
+  ([]
+   (print-jndi-tree "")))
+
+
+(defn find-jndi-subcontext
+  "Find subcontext in a given JNDI context.
+  context  JNDI context
+  args     string keys"
+  [^Context context & args]
+  (assert (not (nil? args)))
+  (assert (not (some nil? args)))
+  (let [lufn (fn [ctx k] (.lookup ctx k))
+        nctx (reduce lufn context args)]
+    nctx))

src/test/clj/jndi.properties

+# JNDI test configuration
+# See: http://www.osjava.org/simple-jndi/manual/Configuring.html
+
+java.naming.factory.initial=org.osjava.sj.SimpleContextFactory
+#java.naming.factory.initial=org.osjava.sj.memory.MemoryContextFactory
+
+# relative directory, using the default file protocol
+org.osjava.sj.root=src/test/jndi/
+
+org.osjava.sj.colon.replace=--
+org.osjava.sj.delimiter=/
+
+# Simulate Tomcat's JNDI environment (prefix)
+# (commented out as we are using a directory scheme to simulate that structure)
+#org.osjava.sj.space=java:comp/env

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

 (ns org.bituf.test-clj-dbcp
   (:use org.bituf.clj-dbcp)
+  (:use org.bituf.clj-dbcp.util)
+  (:use org.bituf.dbserver-util)
   (:use [clojure.contrib.sql :as sql :only ()])
+  (:use clojure.contrib.java-utils)
+  (:use clojure.contrib.pprint)
   (:use clojure.test)
-  (:use org.bituf.dbserver-util))
+  (:import (javax.naming.spi.DirectoryManager)))
+
+
+(defn fail
+  [msg]
+  (is false msg))
+
+
+(def dbcred (let [props (strkey-to-keyword
+                          (property-map
+                            (read-properties "src/test/dbcred.properties")))]
+              (println "*** DB-Credentials properties map ***")
+              (pprint props)
+              props))
+
 
 (defn test-crud
   [dbspec]
         (stop-hsql-server server)))))
 
 
+(defn test-network-datasource
+  "Test a network JDBC datasource - MySQL, PostgreSQL, Oracle, DB2, jTDS etc.
+  dbtoken must be same as first token (before period) in dbcred.properties file.
+  Examples of dbtoken: mysql, pgsql etc."
+  [^String dbtoken dsfn]
+  (let [as-str #(if (keyword? %) (name %) (str %))
+        K      (fn [kw] (keyword
+                          (str dbtoken "." (as-str kw))))]
+    (test-datasource
+      (dsfn
+        ((K :hostport) dbcred)
+        ((K :database) dbcred)
+        ((K :username) dbcred)
+        ((K :password) dbcred)))))
+
+
 (deftest test-mysql
-  (test-datasource
-    (mysql-datasource "localhost" "cljdbcp" "root" "")))
+  (if (true-value? (:mysql.testconn dbcred))
+    (test-network-datasource "mysql" mysql-datasource)
+    (fail "MySQL not tested")))
 
 
 (deftest test-pgsql
-  (test-datasource
-    (pgsql-datasource "localhost" "cljdbcp" "root" "root")))
+  (if (true-value? (:pgsql.testconn dbcred))
+    (test-network-datasource "pgsql" pgsql-datasource)
+    (fail "PostgreSQL not tested")))
+
+
+(deftest test-jndi
+  "See also (not used): http://commons.apache.org/dbcp/guide/jndi-howto.html"
+  (with-root-context (javax.naming.InitialContext.)
+    (print-jndi-tree))
+  (let [ds (jndi-datasource "java:comp/env/myDataSource")]
+    (test-datasource ds)))
+
+
+(defmacro ig
+  "Ignore exceptions in a block while creating datasource connections"
+  [& body]
+  `(let []
+     (try
+       ~@body
+       (catch org.apache.commons.dbcp.SQLNestedException ~'_))))
+
+
+(deftest untested-datasources
+  ;; Oracle
+  (ig (oracle-datasource "localhost" "cljdbcp" "root" "root"))
+  ;; IBM DB2
+  (ig (db2-datasource "localhost" "cljdbcp" "root" "root"))
+  ;; SQL Server
+  (ig (sqlserver-datasource "localhost" "cljdbcp" "root" "root" {:k1 true :k2 10 "k3" "x"}))
+  (ig (sqlserver-datasource "localhost" "cljdbcp" "root" "root")) ; no properties
+  (ig (sqlserver-datasource "localhost" "root" "root")) ; no database name
+  ;; Sybase
+  (ig (sybase-datasource "localhost" "cljdbcp" "root" "root" {:k1 true :k2 10 "k3" "x"}))
+  (ig (sybase-datasource "localhost" "cljdbcp" "root" "root")) ; no properties
+  (ig (sybase-datasource "localhost" "root" "root"))) ; no database name
 
 
 (defn test-ns-hook []
   (test-h2)
   (test-hsql)
   (test-mysql)
-  (test-pgsql))
+  (test-pgsql)
+  (untested-datasources)
+  (test-jndi))

src/test/dbcred.properties

+# MySQL credentials
+# hostport can also be in the form "localhost:3306"
+mysql.testconn=true
+mysql.hostport=localhost
+mysql.database=cljdbcp
+mysql.username=root
+mysql.password=
+
+# PostgreSQL credentials
+# hostport can also be in the form "localhost:5432"
+pgsql.testconn=true
+pgsql.hostport=localhost
+pgsql.database=cljdbcp
+pgsql.username=root
+pgsql.password=root
+
+# Oracle credentials
+# hostport can also be in the form "localhost:1521"
+# database is SID
+oracle.testconn=false
+oracle.hostport=localhost
+oracle.database=cljdbcp
+oracle.username=root
+oracle.password=root
+
+# IBM DB2 credentials
+# hostport can also be in the form "localhost:50000"
+db2.testconn=false
+db2.hostport=localhost
+db2.database=cljdbcp
+db2.username=root
+db2.password=root
+
+# MS SQL Server credentials
+# hostport can also be in the form "localhost:1433"
+sqlserver.testconn=false
+sqlserver.hostport=localhost
+sqlserver.database=cljdbcp
+sqlserver.username=root
+sqlserver.password=root
+
+# Sybase credentials
+# hostport can also be in the form "localhost:7100"
+sybase.testconn=false
+sybase.hostport=localhost
+sybase.database=cljdbcp
+sybase.username=root
+sybase.password=root

src/test/jndi/java--comp/env/default.properties

+myDataSource/type=javax.sql.DataSource
+myDataSource/driver=org.apache.derby.jdbc.EmbeddedDriver
+myDataSource/url=jdbc:derby:memory:exampledb;create=true;
+myDataSource/user=foo
+myDataSource/password=bar