Source

Clj-DBCP / src / main / clj / org / bituf / clj_dbcp.clj

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
(ns org.bituf.clj-dbcp
  (:import
    (java.io   Reader)
    (java.sql  DriverManager)
    (javax.sql DataSource)
    (java.util Properties)
    (org.bituf.clj_dbcp      ConnectionWrapper)
    (org.apache.commons.dbcp BasicDataSource))
  (:require
    [clojure.java.io :as io]
    [clojure.pprint  :as pp]))


(def ^{:doc "Boolean flag (default false) - prints the JDBC URL if true"
       :dynamic true}
      *show-jdbc-url* false)


(defstruct datasource-args
  :classname :url :username :password)


(defn make-datasource-args
  "Create an instance of 'datasource-args' using given args."
  [classname url username password]
  (struct datasource-args
    classname
    url
    username
    password))


(defn set-max-active!
  "Set max active connections at a time in the pool. You should call this before
  creating any connection from the datasource."
  [^BasicDataSource datasource max-active]
  (doto datasource
    (.setMaxActive max-active))
  datasource)


(defn set-min-max-idle!
  "Set min and max idle connections count in the pool. You should call this
  before creating any connection from the datasource."
  [^BasicDataSource datasource min-idle max-idle]
  (doto datasource
    (.setMinIdle min-idle)
    (.setMaxIdle max-idle))
  datasource)


(defn set-validation-query!
  "Set validation query for the datasource. You must call this *before*
  creating any connection from the datasource."
  [^BasicDataSource datasource ^String validation-query]
  (doto datasource
      (.setValidationQuery validation-query)
      (.setTestOnBorrow  true)
      (.setTestOnReturn  true)
      (.setTestWhileIdle true))
  datasource)


(defn make-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
    (make-datasource
      (:classname datasource-args)
      (:url       datasource-args)
      (:username  datasource-args)
      (:password  datasource-args))))


(defn db-spec
  "Create a db-spec suitable for use by clojure.contrib.sql"
  [datasource]
  {: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\")"
  ([^javax.naming.Context init-ctx ^String 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 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\")
    (derby-args nil           \"//localhost/somedb\") ; connects to server
  Maven/Lein/Cake dependencies (or a later version):
    org.apache.derby/derby      /10.6.1.0 - driver for memory and file mode
    org.apache.derby/derbyclient/10.6.1.0 - driver for network mode
    org.apache.derby/derbynet   /10.6.1.0 - for launching the server itself
  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]
  (let [url (str "jdbc:derby:" (if (empty? db-protocol) ""
                                 (str db-protocol ":"))
              db-path)]
    (if *show-jdbc-url*
      (println "\n**** Derby JDBC URL ****\n" url))
    (make-datasource-args
      "org.apache.derby.jdbc.EmbeddedDriver" ; classname: must be in classpath
      url "sa" "sx"))) ; url, username and password


(defn derby-datasource
  "Helper function to create a Derby datasource
  See also:
    derby-network-datasource
    derby-filesystem-datasource
    derby-memory-datasource
    derby-args"
  [db-protocol db-path]
  (make-datasource
    (derby-args db-protocol db-path)))


(defn derby-network-datasource
  "Create a network based (connecting to a server) Derby datasource
  See also: derby-datasource, derby-args"
  [host:port db-name]
  (derby-datasource
    nil (format "//%s/%s;create=true;" host:port db-name)))


(defn derby-filesystem-datasource
  "Create a filesystem (directory) based Derby datasource
  See also: derby-datasource, derby-args"
  ([db-path]
    (derby-datasource
      nil (str db-path ";create=true;")))
  ([]
    (derby-filesystem-datasource default-db-name)))


(defn derby-memory-datasource
  "Create an in-memory Derby datasource
  See also: derby-datasource, derby-args"
  ([db-name]
    (derby-datasource
      "memory" (str db-name ";create=true;")))
  ([]
    (derby-memory-datasource
      default-db-name)))


; === 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\")
  Maven/Lein/Cake Dependencies:
    com.h2database/h2/1.2.140 (or a later version)
  See also:
    http://www.h2database.com/html/features.html"
  [db-protocol db-path]
  (let [url (str "jdbc:h2:" db-protocol ":" db-path)]
    (if *show-jdbc-url*
      (println "\n**** H2 JDBC URL ****\n" url))
    (make-datasource-args
      "org.h2.Driver" ; classname: must be in classpath
      url "sa" ""))) ; url, username and password


(defn h2-datasource
  "Create an H2 data source
  See also: h2-args"
  [db-protocol db-path]
  (make-datasource
    (h2-args db-protocol db-path)))


(defn h2-network-datasource
  "Create a network based (connecting to a server) H2 datasource
  See also: h2-datasource, h2-args"
  [host:port db-path]
  (h2-datasource
    "tcp" (format "//%s/%s" host:port db-path)))


(defn h2-filesystem-datasource
  "Create a filesystem (directory) based H2 datasource
  See also: h2-datasource, h2-args"
  ([db-path]
    (h2-datasource "file" db-path))
  ([]
    (h2-filesystem-datasource default-db-name)))


(defn h2-memory-datasource
  "Create an in-memory H2 datasource
  See also: h2-datasource, h2-args"
  ([db-name]
    (h2-datasource
      "mem" db-name))
  ([]
    (h2-memory-datasource "")))


; === 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\")
  Maven/Lein/Cake Dependencies:
    org.hsqldb/hsqldb/2.0.0 (or a later version)
  See also:
    http://hsqldb.org/doc/guide/ch01.html"
  [db-protocol db-path]
  (let [url (str "jdbc:hsqldb:" db-protocol ":" db-path)]
    (if *show-jdbc-url*
      (println "\n**** HSQL JDBC URL ****\n" url))
    (make-datasource-args
      "org.hsqldb.jdbcDriver" ; classname: must be in classpath
      url "sa" ""))) ; url, username and password  


(defn hsql-datasource
  "Create an HSQLDB/HyperSQL data source.
  See also: hsql-args"
  ([db-protocol db-path]
    (make-datasource
      (hsql-args db-protocol db-path)))
  ([]
    (hsql-datasource "mem" default-db-name)))


(defn hsql-network-datasource
  "Create a network based (connecting to a server) HSQLDB/HyperSQL datasource
  See also: hsql-datasource, hsql-args"
  [host:port db-path]
  (hsql-datasource
    "hsql" (format "//%s/%s" host:port db-path)))


(defn hsql-filesystem-datasource
  "Create a filesystem (directory) based HSQLDB/HyperSQL datasource
  See also: hsql-datasource, hsql-args"
  ([db-path]
    (hsql-datasource "file" db-path))
  ([]
    (hsql-filesystem-datasource default-db-name)))


(defn hsql-memory-datasource
  "Create an in-memory HSQLDB/HyperSQL datasource
  See also: hsql-datasource, hsql-args"
  ([db-name]
    (hsql-datasource "mem" db-name))
  ([]
    (hsql-memory-datasource 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]
  (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
  "Create MySQL data source
  See also: mysql-args"
  [db-host:port db-name username password]
  (let [args (mysql-args
               db-host:port db-name username password)
        ds   (make-datasource args)]
    (set-validation-query! ds
      "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]
  (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
  "Create PostgreSQL data source
  See also: pgsql-args"
  [db-host:port db-name username password]
  (let [args (pgsql-args
               db-host:port db-name username password)
        ds   (make-datasource args)]
    (set-validation-query! ds
      "SELECT version();")))


; === Oracle config ===
;
(defn oracle-args
  "Create datasource args for the Oracle database. Oracle JDBC URLs usually
  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:
    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]
  (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
  "Create Oracle data source
  See also: oracle-args"
  [db-host:port system-id username password]
  (let [args (oracle-args
               db-host:port system-id username password)
        ds   (make-datasource args)]
    (set-validation-query! ds
      "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:
    (db2-args \"localhost\"       \"sales\" \"salesuser\" \"secret\")
    (db2-args \"localhost:50000\" \"emp\"   \"empuser\"   \"SeCrEt\")"
  [db-host:port db-name username 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
  "Create DB2 data source
  See also: db2-args"
  [db-host:port db-name username password]
  (let [args (db2-args
               db-host:port db-name username password)
        ds   (make-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]
  (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
  "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   (make-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)))


; === SQLite-DB config ===
;
(defn sqlite-args
  "Create datasource args for the SQLite database. SQLite database can be
  hosted off the memory, or a file. Example JDBC URLs it should create are:
    jdbc:sqlite:C:/work/mydatabase.db        -- file
    jdbc:sqlite:/home/leo/work/mydatabase.db -- file
    jdbc:sqlite::memory:                     -- memory
  Arguments:
    db-path (String) for file   -- <filepath>
                     for memory -- :memory:
  Example:
    (sqlite-args \"C:/work/mydatabase.db\")
    (sqlite-args \":memory:\")
  Maven/Lein/Cake Dependencies:
    org.xerial/sqlite-jdbc/3.7.2 (or a later version)
  See also:
    http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC"
  [db-path]
  (let [url (str "jdbc:sqlite:" db-path)]
    (if *show-jdbc-url*
      (println "\n**** SQLite JDBC URL ****\n" url))
    (make-datasource-args
      "org.sqlite.JDBC" ; classname: must be in classpath
      url ; url
      "sa" ; username
      ""   ; password
      )))


(defn sqlite-datasource
  "Create an SQLite data source.
  See also: sqlite-args"
  ([db-path]
    (make-datasource (sqlite-args db-path)))
  ([]
    (sqlite-datasource ":memory:")))


(defn sqlite-filesystem-datasource
  "Create a filesystem (file) based SQLite datasource.
  See also: sqlite-datasource, sqlite-args"
  ([db-path]
    (sqlite-datasource db-path))
  ([]
    (sqlite-datasource (str "sqlite" default-db-name))))


(defn sqlite-memory-datasource
  "Create an in-memory SQLite datasource.
  See also: sqlite-datasource, sqlite-args"
  ([]
    (sqlite-datasource)))


; === CUBRID config ===
;
(defn cubrid-args
  "Create datasource args for the CUBRID database. The default database port is
  8001 but the JDBC connection typically happens through a broker at port 33000
  or 30000. Example JDBC URL:
    jdbc:cubrid:localhost:33000:demodb:::
  Arguments:
    db-host:port  (String) database hostname and :port-no (colon, then port-no)
    db-name       (String) database name to connect to
    username      (String) database username
    password      (String) password for the database user
  Examples:
    (cubrid-args \"localhost:33000\" \"sales\" \"salesuser\" \"secret\")
    (cubrid-args \"localhost:30000\" \"emp\"   \"empuser\"   \"SeCrEt\")
  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]
  (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
  "Create CUBRID data source
  See also: cubrid-args"
  [db-host:port db-name username password]
  (let [args (cubrid-args
               db-host:port db-name username password)
        ds   (make-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   (make-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   (make-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 lite-datasource
  "Create lite datasource that ignores setAutoCommit calls. Experimental - may
  not be supported in Clojure 1.3."
  [^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 lite-odbc-datasource
  "Experimental lite-datasource support for the likes of MS-Excel/Access files
  through ODBC. May not be supported in Clojure 1.3."
  ([^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 lite DataSource ****\n" url))
      (lite-datasource
        "sun.jdbc.odbc.JdbcOdbcDriver" ; driver classname
        url)))
  ([^String dsn]
    (lite-odbc-datasource dsn nil)))


;; ========== Loading DataSource from property files params ==========


(def ^{:doc "Path to the Properties file (in classpath or filesystem)"
       :dynamic true}
      *properties-file* "clj-dbcp.properties")


(defn load-properties
  "Load properties-file `file` (or *properties-file* if not specified) from
  classpath or filesystem. If the file is found in classpath, then filesystem is
  not looked up. Properties are returned as a map of property string names to
  string values."
  ([file] {:post [(and (map? %)
                    (every? string? (keys %))
                    (every? string? (vals %)))]}
    (let [pfile  (or (io/resource file) file)
          reader (io/reader pfile)
          props  (Properties.)]
      (.load ^Properties props ^Reader reader)
      (into {} props)))
  ([] {:pre [(string? *properties-file*)]}
    (load-properties *properties-file*)))


(defn default-profile
  "Return the default profile name from properties. You should explicitly
  declare it in the properties file, for example:
    default=staging
  If no default profile is specified, then `dev` is assumed after verifying
  that the dev profile exists in the properties.
  See also: load-properties"
  [props-map] {:pre [(and (map? props-map)
                       (every? string? (keys props-map))
                       (every? string? (vals props-map)))]}
  (or (get props-map "default")
    (and (some #(and (.startsWith ^String % "dev.") (> (count %) 4))
           (keys props-map)) "dev")
    (throw (IllegalArgumentException.
             "No default profile found, nor exists `dev` fallback profile."))))


(defn read-profile-properties
  "Read properties for the given profile (default profile is looked up if none
  specified.) The returned map is keyword to string-value map, and has the keys
  stripped of the profile name."
  [props-map ^String profile] {:post [(map? %)
                                       (every? keyword? (keys %))
                                       (every? string?  (vals %))]
                               :pre  [(map? props-map)
                                      (string? profile)]}
  (let [pf (str profile \.)
        pc (count pf)
        ks (filter #(and (.startsWith ^String % pf)
                      (> (count %) pc))
             (keys props-map))
        vs (map #(get props-map %) ks)]
    (zipmap (map #(keyword (.substring ^String % pc)) ks) vs)))


(defn on-memory
  "Helper function to create datasource (on memory) with default configuration."
  [factory-fn props]
  (factory-fn))


(defn on-filesystem
  "Helper function to create datasource (on filesystem) as per the arguments
  passed."
  [factory-fn props]
  (let [db-path (:db-path props)]
    (if db-path
      (factory-fn db-path)
      (factory-fn))))


(defn on-network
  "Helper function to create datasource (that connects over network) after
  verifying that sufficient arguments are passed."
  [factory-fn props] {:post [(instance? DataSource %)]
                      :pre  [(fn? factory-fn)
                             (map? props)
                             (contains? props :hostport)
                             (contains? props :database)]}
  (when (not (and (contains? props :hostport)
               (contains? props :database)))
    (throw (IllegalArgumentException.
             (format "Expected arguments: mandatory - `hostport` and `database`,
optional (in H2, HSQL, Derby etc) - `username`and `password` but found %s"
               (with-out-str (pp/pprint props))))))
  (let [hostport (:hostport props)
        database (:database props)
        username (:username props)
        password (:password props)]
    (if username
      (factory-fn hostport database username password)
      (factory-fn hostport database))))


(defn on-odbc
  "Create DataSource using ODBC DSN."
  [props]
  (let [odbc-dsn (:odbc-dsn props)
        username (:username props)
        password (:password props)]
    (when (or (not (string? odbc-dsn))
            (empty? odbc-dsn))
      (throw (IllegalArgumentException.
               (format "Expected `odbc-dsn` key but found %s"
                 (with-out-str (pp/pprint props))))))
    (if (contains? props username)
      (odbc-datasource odbc-dsn username password)
      (odbc-datasource odbc-dsn))))


(defn on-jdbc
  "Create simple DataSource based on JDBC parameters."
  [props]
  (let [classname (:classname props)
        jdbc-url (:jdbc-url props)
        username (:username props)
        password (:password props)]
    (when (not (every? string? [classname jdbc-url username password]))
      (throw (IllegalArgumentException.
               (format "Expected `classname`, `jdbc-url`, `username` and
`password` keys but found %s"
                 (with-out-str (pp/pprint props))))))
    (make-datasource
      classname jdbc-url username password)))


(def ^{:doc "Map of supported databases and respective datasource factory fns"}
      supported-dbs
  {:derby-memory       (partial on-memory derby-memory-datasource)
   :h2-memory          (partial on-memory h2-memory-datasource)
   :hsql-memory        (partial on-memory hsql-memory-datasource)
   :sqlite-memory      (partial on-memory sqlite-memory-datasource)
   ;; filesystem
   :derby-filesystem   (partial on-filesystem derby-filesystem-datasource)
   :h2-filesystem      (partial on-filesystem h2-filesystem-datasource)
   :hsql-filesystem    (partial on-filesystem hsql-filesystem-datasource)
   :sqlite-filesystem  (partial on-filesystem sqlite-filesystem-datasource)
   ;; network
   :derby-network      (partial on-network derby-network-datasource)
   :h2-network         (partial on-network h2-network-datasource)
   :hsql-network       (partial on-network hsql-network-datasource)
   ;; regular, open source, network-only databases
   :cubrid             (partial on-network cubrid-datasource)
   :firebird           (partial on-network firebird-datasource)
   :mysql              (partial on-network mysql-datasource)
   :pgsql              (partial on-network pgsql-datasource)
   ;; regular, proprietary, network-only databases
   :oracle             (partial on-network oracle-datasource)
   :db2                (partial on-network db2-datasource)
   :sqlserver          (partial on-network sqlserver-datasource)
   :sybase             (partial on-network sybase-datasource)
   })


(defn load-datasource-args
  "Load a map of string names and string values from property file for the
  given/default profile."
  ([^String profile] {:post [(map? %)]
                      :pre  [(string? profile)]}
    (read-profile-properties
      (load-properties) profile))
  ([] {:post [(map? %)]}
    (load-datasource-args (default-profile (load-properties)))))


(defn ^DataSource make-datasource-from-properties
  "Load DataSource-building properties and create a DataSource as per those."
  ([props] {:post [(instance? DataSource %)]
           :pre  [(map? props)
                  (every? keyword? (keys props))
                  (every? string?  (vals props))]}
    (cond
      ;; supported databases
      (and (contains? props :db-type)
          (contains? supported-dbs
            (keyword
              (:db-type props))))        ((get supported-dbs (keyword
                                                               (:db-type props)))
                                        props)
      ;; JDBC-ODBC bridge
      (contains? props :odbc-dsn)     (on-odbc props)
      ;; JDBC Driver
      (contains? props :jdbc-url)     (on-jdbc props)
      ;; Else
      :else                           (throw
                                        (IllegalArgumentException.
                                          (format
                                            "Expected either of keys `db-type`,
`odbc-dsn` and `jdbc-url` but found %s Valid db-type values are: %s"
                                            (with-out-str (pp/pprint props))
                                            (with-out-str
                                              (pp/pprint
                                                (keys supported-dbs))))))))
  ([] {:post [(instance? DataSource %)]}
    (let [props (load-datasource-args)]
      (make-datasource-from-properties props))))