Commits

Anonymous committed 5d07dc6

first import

Comments (0)

Files changed (6)

+-*- markdown -*-
+
+# Changes and TODO
+
+- [TODO] Move database credentials (unit tests) to properties file
+- [TODO] Support jTDS driver (MS SQL Server, Sybase)
+- [TODO] Add tests-cases for file-mode and server-mode: Derby, HSQLDB, H2
+- [TODO] Add test-cases for untested databases: Oracle, IBM DB2
+- [TODO] API for changing connection pool parameters (as of now it's Java API)
+
+## 0.1 / 2010-Oct-02 (GMT+5:30)
+
+- BasicDataSource with driver classname, JDBC url, username and password
+- Support for validation query
+- Auto-select driver classname and validation query for supported database
+- No-arg API for launching in-memory instances of embedded databases
+- Supported+tested in-memory embedded use only: Apache Derby, HSQLDB, H2
+- Supported+tested: MySQL, PostgreSQL
+- Supported but not tested: Oracle, IBM DB2
+-*- markdown -*-
+
+# Clj-DBCP v0.1
+
+Clj-DBCP is a simple Java-6/Clojure wrapper around the Apache DBCP library for
+creating database connection pools and for embedding databases in applications.
+The supported operations are:
+
+* BasicDataSource with driver classname, JDBC url, username and password
+* Support for validation query
+* Auto-select driver classname and validation query for supported database
+* No-arg API for launching in-memory instances of embedded databases
+* Supported+tested for in-memory embedding: Apache Derby, HSQLDB, H2
+* Supported+tested: MySQL, PostgreSQL
+* Supported but not tested: Oracle, IBM DB2
+
+
+## Usage
+
+Maven/Leiningen dependency details are here: [http://clojars.org/org.bituf/clj-dbcp](http://clojars.org/org.bituf/clj-dbcp)
+
+Examples for usage can be found in the tutorial below:
+
+
+## Building/Installation
+
+You will need Maven 2 to build from sources. Execute the following:
+
+    $ mvn clean package  # packages up a JAR in "target" dir
+    $ mvn install        # to install to your local Maven repo
+    $ mvn clojure:gendoc # generate Clojure API documentation
+
+
+## License
+
+Copyright (C) 2010 Shantanu Kumar (kumar.shantanu at gmail dot com)
+
+Distributed under the Apache 2 License.
+
+
+# Tutorial
+
+Clj-DBCP can be used after including the following in your
+namespace:
+
+    (ns example.app
+      (:use org.bituf.clj-dbcp))
+
+**Important: You must include the JDBC drivers on the classpath on your own.**
+
+
+## Create in-memory instance of Apache Derby database
+
+    (db-spec
+      (derby-datasource))
+
+Example usage (example/app.clj):
+
+    (ns example.app
+      (:use org.bituf.clj-dbcp)
+      (:use [clojure.contrib.sql :as sql :only ()]))
+    
+    (def db-derby (db-spec
+                    (derby-datasource))) ; creates in-memory instance by default
+    
+    (defn crud
+      []
+      (let [table :emp
+            orig-record {:id 1 :name "Bashir" :age 40}
+            updt-record {:id 1 :name "Shabir" :age 50}
+            drop-table  #(sql/do-commands "DROP TABLE emp")
+            retrieve-fn #(sql/with-query-results rows
+                          ["SELECT * FROM emp WHERE id=?" 1]
+                          (first rows))]
+        (sql/with-connection db-derby
+          ;; drop table if pre-exists
+          (try (drop-table)
+            (catch Exception _)) ; ignore exception
+          ;; create table
+          (sql/do-commands
+            "CREATE TABLE emp (id INTEGER, name VARCHAR(50), age INTEGER)")
+          ;; insert
+          (sql/insert-values table (keys orig-record) (vals orig-record))
+          ;; retrieve
+          (println (retrieve-fn))
+          ;; update
+          (sql/update-values table ["id=?" 1] updt-record)
+          ;; drop table
+          (drop-table))))
+
+
+## Create in-memory instance of HSQL database
+
+    (db-spec
+      (hsql-datasource))
+
+Example usage: see Derby example
+
+
+## Create in-memory instance of H2 database
+
+    (db-spec
+      (h2-datasource))
+
+Example usage: see Derby example
+
+
+## Create data source for MySQL database
+
+    (db-spec
+      (mysql-datasource "localhost" "exampledb" "dbuser" "secret"))
+
+Example usage: see Derby example
+
+
+## Create data source for PostgreSQL database
+
+    (db-spec
+      (pgsql-datasource "localhost" "exampledb" "dbuser" "secret"))
+
+Example usage: see Derby example
+<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
+  <modelVersion>4.0.0</modelVersion>
+  <groupId>org.bituf</groupId>
+  <artifactId>clj-dbcp</artifactId>
+  <packaging>jar</packaging>
+  <version>0.1-SNAPSHOT</version>
+  <name>Clj-DBCP</name>
+  <description>
+    Clj-DBCP is a simple Java-6/Clojure wrapper around the Apache DBCP library
+    for creating database connection pools and for embedding databases in
+    applications.
+  </description>
+  <url>http://code.google.com/p/bitumenframework/</url>
+  <repositories>
+    <repository>
+      <id>build.clojure.org</id>
+      <url>http://build.clojure.org/snapshots/</url>
+      <snapshots>
+        <enabled>true</enabled>
+      </snapshots>
+    </repository>
+    <repository>
+      <id>clojars.org</id>
+      <url>http://clojars.org/repo</url>
+    </repository>
+  </repositories>
+  <dependencies>
+    <dependency>
+      <groupId>junit</groupId>
+      <artifactId>junit</artifactId>
+      <version>3.8.1</version>
+      <scope>test</scope>
+    </dependency>
+    <dependency>
+      <groupId>org.clojure</groupId>
+      <artifactId>clojure</artifactId>
+      <version>1.2.0</version>
+      <optional>true</optional>
+    </dependency>
+    <dependency>
+      <groupId>org.clojure</groupId>
+      <artifactId>clojure-contrib</artifactId>
+      <version>1.2.0</version>
+      <optional>true</optional>
+    </dependency>
+    <!-- Apache DBCP -->
+    <dependency>
+      <groupId>commons-dbcp</groupId>
+      <artifactId>commons-dbcp</artifactId>
+      <version>1.4</version>
+    </dependency>
+    <!-- JDBC Drivers -->
+    <dependency><!-- Apache Derby -->
+      <groupId>org.apache.derby</groupId>
+      <artifactId>derby</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>hsqldb</groupId>
+      <artifactId>hsqldb</artifactId>
+      <version>1.8.0.7</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>
+    -->
+    <!-- DB Connection Pool: C3P0 -->
+    <dependency><!-- C3P0 -->
+      <groupId>c3p0</groupId>
+      <artifactId>c3p0</artifactId>
+      <version>0.9.1</version>
+      <scope>test</scope>
+    </dependency>
+  </dependencies>
+  <build>
+    <plugins>
+      <plugin>
+        <groupId>com.theoryinpractise</groupId>
+        <artifactId>clojure-maven-plugin</artifactId>
+        <version>1.3.3</version>
+        <executions>
+          <execution>
+            <id>compile</id>
+            <phase>compile</phase>
+            <goals>
+              <goal>compile</goal>
+            </goals>
+          </execution>
+          <execution>
+            <id>test-compile</id>
+            <phase>test-compile</phase>
+            <goals>
+              <goal>testCompile</goal>
+            </goals>
+          </execution>
+          <execution>
+            <id>test</id>
+            <phase>test</phase>
+            <goals>
+              <goal>test</goal>
+            </goals>
+          </execution>
+        </executions>
+        <configuration>
+          <clojureOptions>-Dbasedir=${basedir}</clojureOptions>
+          <compileDeclaredNamespaceOnly>true</compileDeclaredNamespaceOnly>
+          <sourceDirectories>
+            <sourceDirectory>src/main/clj</sourceDirectory>
+          </sourceDirectories>
+          <testSourceDirectories>
+            <testSourceDirectory>src/test/clj</testSourceDirectory>
+          </testSourceDirectories>
+          <testScript>${basedir}/src/test/script/runtests.clj</testScript>
+        </configuration>
+        
+      </plugin>
+    </plugins>
+  </build>
+</project>

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

+(ns org.bituf.clj-dbcp
+  (:import org.apache.commons.dbcp.BasicDataSource))
+
+(defstruct datasource-args
+  :classname :url :username :password)
+
+
+(defn create-datasource
+  "Create basic data source (instance of the BasicDataSource class).
+  Arguments:
+    classname         (String) the fully qualified driver classname
+    url               (String) the JDBC URL
+    username          (String) the database username
+    password          (String) password for the username
+    validation-query  (String, optional) to check database connection is valid
+  See also:
+    http://commons.apache.org/dbcp/apidocs/org/apache/commons/dbcp/BasicDataSource.html"
+  ([classname url username password] ; 4 args
+    (doto (BasicDataSource.)
+      (.setDriverClassName classname)
+      (.setUsername username)
+      (.setPassword password)
+      (.setUrl url)))
+  ([datasource-args] ; 1 arg
+    (create-datasource
+      (:classname datasource-args)
+      (:url       datasource-args)
+      (:username  datasource-args)
+      (:password  datasource-args)))
+  ([classname url username password validation-query] ; 5 args
+    (doto (create-datasource
+            classname url username password)
+      (.setValidationQuery validation-query)
+      (.setTestOnBorrow  true)
+      (.setTestOnReturn  true)
+      (.setTestWhileIdle true)))
+  ([datasource-args validation-query] ; 2 args
+    (create-datasource
+      (:classname datasource-args)
+      (:url       datasource-args)
+      (:username  datasource-args)
+      (:password  datasource-args)
+      validation-query)))
+
+
+(defn db-spec
+  "Create a db-spec suitable for use by clojure.contrib.sql"
+  [datasource]
+  {:datasource datasource})
+
+
+(def ^{:doc "Default name for the in-memory database"}
+      default-db-name "default")
+
+
+; === Derby Config ===
+;
+(defn derby-args
+  "Create data source args for the Apache Derby database. Derby can be hosted
+  off a file on the file system, or from the classpath (read-only) or a JAR file
+  (read-only).
+  Arguments:
+    db-protocol  (String) directory|memory|classpath|jar (default: directory)
+    db-path      (String) for directory: <dir-containing-the-db>
+                          for memory:    <db-name>
+                          for classpath: /<db-name>
+                          for jar:       (<jar-path>)<[dir/]db-name>
+  Examples:
+    (derby-args \"\"          \"db1\") ; db-protocol=directory assumed
+    (derby-args \"directory\" \"london/sales\")
+    (derby-args \"memory\"    \"sample\")
+    (derby-args \"classpath\" \"/db1\") ; 'db1' is a directory in classpath
+    (derby-args \"jar\"       \"(C:/dbs.jar)products/boiledfood\")
+  See also:
+    http://db.apache.org/derby/docs/dev/devguide/cdevdvlp17453.html
+    http://db.apache.org/derby/docs/dev/devguide/rdevdvlp22102.html"
+  [db-protocol db-path]
+  (struct datasource-args
+    "org.apache.derby.jdbc.EmbeddedDriver" ; classname: must be in classpath
+    (str "jdbc:derby:" (or (empty? db-protocol)
+                         (str db-protocol ":"))
+      db-path) ; url
+    "" ; username
+    "" ; password
+    ))
+
+
+(defn derby-datasource
+  ([db-protocol db-path]
+    (create-datasource
+      (derby-args db-protocol db-path)))
+  ([db-path]
+    (derby-datasource nil db-path))
+  ([]
+    (derby-datasource "memory" (str default-db-name ";create=true"))))
+
+
+; === H2 config ===
+;
+(defn h2-args
+  "Create datasource args for the H2 database. H2 database can be hosted off
+  the memory, a file or a TCP-port.
+  Arguments:
+    db-protocol  (String) file|mem|tcp
+    db-path      (String) for file: <filepath>
+                          for mem: [<databaseName>]
+                          for tcp: //<server>[:<port>]/[<path>]<databaseName>
+                          for tcp: //<host>/<database>
+  Example:
+    (h2-args \"mem\"  \"\") ; private in-memory database
+    (h2-args \"mem\"  \"sample\") ; named in-memory database
+    (h2-args \"file\" \"/home/eddie/sample\")
+    (h2-args \"tcp\"  \"//localhost:9092/sample\")
+    (h2-args \"tcp\"  \"//localhost/sample\")
+    (h2-args \"tcp\"  \"//localhost/home/dir/sample\")
+  See also:
+    http://www.h2database.com/html/features.html"
+  [db-protocol db-path]
+  (struct datasource-args
+    "org.h2.Driver" ; classname: must be in classpath
+    (str "jdbc:h2:" db-protocol ":" db-path) ; url
+    "sa" ; username
+    ""   ; password
+    ))
+
+
+(defn h2-datasource
+  "Create H2 data source.
+  See also: h2-args"
+  ([db-protocol db-path]
+    (create-datasource
+      (h2-args db-protocol db-path)))
+  ([]
+    (h2-datasource "mem" "")))
+
+
+; === HSQL-DB (HyperSQL) config ===
+;
+(defn hsql-args
+  "Create datasource args for the HyperSQL database. HyperSQL database can be
+  hosted off the memory, a file or a TCP-port. Example JDBC URLs it should
+  create are these:
+    jdbc:hsqldb:hsql://localhost/xdb -- server
+    jdbc:hsqldb:file:/opt/db/testdb -- file
+    jdbc:hsqldb:mem:aname -- memory
+  Arguments:
+    db-protocol  (String) file|mem|hsql (hsql = server-mode, default port 9001)
+    db-path      (String) for file: <filepath>
+                          for mem: [<databaseName>]
+                          for tcp: //<server>[:<port>]/[<path>]<databaseName>
+                          for tcp: //<host>/<database>
+  Example:
+    (hsql-args \"mem\"  \"\") ; private in-memory database
+    (hsql-args \"mem\"  \"sample\") ; named in-memory database
+    (hsql-args \"file\" \"/home/eddie/sample\")
+    (hsql-args \"hsql\" \"//localhost:9001/sample\")
+    (hsql-args \"hsql\" \"//localhost/sample\")
+    (hsql-args \"hsql\" \"//localhost/home/dir/sample\")
+  See also:
+    http://hsqldb.org/doc/guide/ch01.html"
+  [db-protocol db-path]
+  (struct datasource-args
+    "org.hsqldb.jdbcDriver" ; classname: must be in classpath
+    (str "jdbc:hsqldb:" db-protocol ":" db-path) ; url
+    "sa" ; username
+    ""   ; password
+    ))
+
+
+(defn hsql-datasource
+  "Create HyperSQL data source.
+  See also: hsql-args"
+  ([db-protocol db-path]
+    (create-datasource
+      (hsql-args db-protocol db-path)))
+  ([]
+    (hsql-datasource "mem" default-db-name)))
+
+
+; === MySQL config ===
+;
+(defn mysql-args
+  "Create datasource args for the MySQL database.
+  Arguments:
+    db-host:port  (String) database hostname (optionally followed by :port-no)
+    db-name       (String) database name to connect to
+    username      (String) database username
+    password      (String) password for the database user
+  Examples:
+    (mysql-args \"localhost\"      \"sales\" \"salesuser\" \"secret\")
+    (mysql-args \"localhost:3306\" \"emp\"   \"empuser\"   \"SeCrEt\")"
+  [db-host:port db-name username password]
+  (struct datasource-args
+    "com.mysql.jdbc.Driver" ; classname: must be in classpath
+    (str "jdbc:mysql://" db-host:port "/" db-name) ; url
+    username ; username
+    password ; password
+    ))
+
+
+(defn mysql-datasource
+  "Create MySQL data source
+  See also: mysql-args"
+  [db-host:port db-name username password]
+  (create-datasource
+    (mysql-args
+      db-host:port db-name username password)
+    "SELECT 1;"))
+
+
+; === PostgreSQL config ===
+;
+(defn pgsql-args
+  "Create datasource args for the PostgreSQL database.
+  Arguments:
+    db-host:port  (String) database hostname (optionally followed by :port-no)
+    db-name       (String) database name to connect to
+    username      (String) database username
+    password      (String) password for the database user
+  Examples:
+    (pgsql-args \"localhost\"      \"sales\" \"salesuser\" \"secret\")
+    (pgsql-args \"localhost:5432\" \"emp\"   \"empuser\"   \"SeCrEt\")"
+  [db-host:port db-name username password]
+  (struct datasource-args
+    "org.postgresql.Driver" ; classname: must be in classpath
+    (str "jdbc:postgresql://" db-host:port "/" db-name) ; url
+    username ; username
+    password ; password
+    ))
+
+
+(defn pgsql-datasource
+  "Create PostgreSQL data source
+  See also: pgsql-args"
+  [db-host:port db-name username password]
+  (create-datasource
+    (pgsql-args
+      db-host:port db-name username password)
+    "SELECT version();"))
+
+
+; === Oracle config ===
+;
+(defn oracle-args
+  "Create datasource args for the Oracle database. Oracle JDBC URLs usually
+  look like these:
+    jdbc:oracle:thin:[user/password]@[host][:port]:SID
+    jdbc:oracle:thin:[user/password]@//[host][:port]/SID
+  Arguments:
+    db-host:port  (String) database hostname (optionally followed by :port-no)
+    system-id     (String) system ID to connect to
+    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\")"
+  [db-host:port system-id username password]
+  (struct 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
+    ))
+
+
+(defn oracle-datasource
+  "Create Oracle data source
+  See also: oracle-args"
+  [db-host:port system-id username password]
+  (create-datasource
+    (oracle-args
+      db-host:port system-id username password)
+    "SELECT 1 FROM DUAL;"))
+
+
+; === DB2 config ===
+;
+(defn db2-args
+  "Create datasource args for IBM DB2 database (Using Type 4 universal driver).
+  DB2 JDBC URLs usually look like these:
+    jdbc:db2://<host>[:<port>]/<database_name>
+  Arguments:
+    db-host:port  (String) database hostname (optionally followed by :port-no)
+    db-name       (String) database name to connect to
+    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\")"
+  [db-host:port db-name username password]
+  (struct 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
+    ))
+
+
+(defn db2-datasource
+  "Create DB2 data source
+  See also: db2-args"
+  [db-host:port db-name username password]
+  (create-datasource
+    (oracle-args
+      db-host:port db-name username password)
+    "select * from sysibm.SYSDUMMY1;"))

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

+(ns org.bituf.test-clj-dbcp
+  (:use org.bituf.clj-dbcp)
+  (:use [clojure.contrib.sql :as sql :only ()])
+  (:use clojure.test))
+
+(defn test-crud
+  [dbspec]
+  (let [table :emp
+        orig-record {:id 1 :name "Bashir" :age 40}
+        updt-record {:id 1 :name "Shabir" :age 50}
+        drop-table  #(sql/do-commands "DROP TABLE emp")
+        retrieve-fn #(sql/with-query-results rows
+                      ["SELECT * FROM emp WHERE id=?" 1]
+                      (first rows))]
+    (sql/with-connection dbspec
+      ;; drop table if pre-exists
+      (try (drop-table)
+        (catch Exception _))
+      ;; create table
+      (sql/do-commands
+        "CREATE TABLE emp (id INTEGER, name VARCHAR(50), age INTEGER)")
+      ;; insert
+      (sql/insert-values table (keys orig-record) (vals orig-record))
+      ;; retrieve-check
+      (is (= orig-record (retrieve-fn)))
+      ;; update
+      (sql/update-values table ["id=?" 1] updt-record)
+      ;; retrieve-check
+      (is (= updt-record (retrieve-fn)))
+      ;; delete
+      ;; drop table
+      (drop-table))))
+
+
+(defn test-datasource
+  "Test a datasource. It creates a dbspec out of the supplied datasource and
+  sends for testing."
+  [datasource]
+  (test-crud (db-spec datasource)))
+
+
+(deftest test-derby
+  (test-datasource
+    (derby-datasource)))
+
+
+(deftest test-hsql
+  (test-datasource
+    (hsql-datasource)))
+
+
+(deftest test-h2
+  (test-datasource
+    (h2-datasource)))
+
+
+(deftest test-mysql
+  (test-datasource
+    (mysql-datasource "localhost" "cljdbcp" "root" "")))
+
+
+(deftest test-pgsql
+  (test-datasource
+    (pgsql-datasource "localhost" "cljdbcp" "root" "root")))
+
+
+(defn test-ns-hook []
+  (test-derby)
+  (test-hsql)
+  (test-h2)
+  (test-mysql)
+  (test-pgsql))

src/test/script/runtests.clj

+(ns runtests
+  (:require org.bituf.test-clj-dbcp)
+  (:use clojure.test))
+
+
+(binding []
+  (run-tests
+    'org.bituf.test-clj-dbcp))