com.github.jlangch.venice.jdbc-postgresql.venice Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of venice Show documentation
Show all versions of venice Show documentation
Venice, a sandboxed Lisp implemented in Java.
;;;; __ __ _
;;;; \ \ / /__ _ __ (_) ___ ___
;;;; \ \/ / _ \ '_ \| |/ __/ _ \
;;;; \ / __/ | | | | (_| __/
;;;; \/ \___|_| |_|_|\___\___|
;;;;
;;;;
;;;; Copyright 2017-2024 Venice
;;;;
;;;; Licensed under the Apache License, Version 2.0 (the "License");
;;;; you may not use this file except in compliance with the License.
;;;; You may obtain a copy of the License at
;;;;
;;;; http://www.apache.org/licenses/LICENSE-2.0
;;;;
;;;; Unless required by applicable law or agreed to in writing, software
;;;; distributed under the License is distributed on an "AS IS" BASIS,
;;;; WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
;;;; See the License for the specific language governing permissions and
;;;; limitations under the License.
;;;; JDBC PostgreSQL utils
;;;; See:
;;;; https://docs.oracle.com/javase/8/docs/api/java/sql/package-summary.html
(ns jdbc-postgresql)
(load-module :jdbc-core ['jdbc-core :as 'jdbc])
(import :java.sql.DriverManager)
(import :java.sql.Connection)
(import :java.sql.Statement)
(import :java.sql.Types)
(import :java.util.Properties)
(defonce default-host "localhost")
(defonce default-port 5432)
(defn
^{ :arglists '(
"(create-connection user password)"
"(create-connection host port user password)"
"(create-connection host port database user password)"
"(create-connection host port database user password properties)" )
:doc """
Creates a PostgreSQL connection.
Arguments:
| [![width: 15%]] | [![width: 85%]] |
| *user* | A mandatory ser name |
| *password* | A mandatory password |
| *host* | An optional host. Defaults to "localhost" |
| *port* | An optional port. Defaults to 5432 |
| *database* | A mandatory database name |
| *properties* | Optional properties (a map). ¶\
E.g.: { "ssl" "true", "options" "-c statement_timeout=90000" } |
"""
:examples '(
"""
(do
(load-module :jdbc-core ['jdbc-core :as 'jdbc])
(load-module :jdbc-postgresql ['jdbc-postgresql :as 'jdbp])
(try-with [conn (jdbp/create-connection "localhost" 5432 "test" "pg" "pg")]
(-> (jdbc/execute-query conn "SELECT * FROM mytable WHERE foo = 500")
(jdbc/print-query-result))))
""")
:see-also '(
"jdbc-core/create-database"
"jdbc-core/drop-database" ) }
create-connection
([user password]
(assert (string? user))
(assert (string? password))
(let [url "jdbc:postgresql:/"]
(. :DriverManager :getConnection url user (or password ""))))
([host port user password]
(assert (string? host))
(assert (or (int? port) (long? port)))
(assert (string? user))
(assert (string? password))
(let [url "jdbc:postgresql://~{host}:~(long port)/"]
(. :DriverManager :getConnection url user (or password ""))))
([host port database user password]
(assert (string? host))
(assert (or (int? port) (long? port)))
(assert (string? database))
(assert (string? user))
(assert (string? password))
(let [url "jdbc:postgresql://~{host}:~(long port)/~{database}"]
(. :DriverManager :getConnection url user (or password ""))))
([host port database user password properties]
(assert (string? host))
(assert (or (int? port) (long? port)))
(assert (string? database))
(assert (string? user))
(assert (string? password))
(assert (map? properties))
(assert (every? string? (keys properties)))
(assert (every? string? (vals properties)))
(let [url "jdbc:postgresql://~{host}:~(long port)/~{database}"
props (as-properties user password properties)]
(. :DriverManager :getConnection url props))))
(defn
^{ :arglists '(
"(describe-table conn table & options)" )
:doc """
Describe the schema of a table.
¶¶*Options:*
| [![width: 10%]] | [![width: 90%]] |
| :mode | In `:print` mode prints the table description, \
in `:data` mode returns the description as a data \
structure. Defaults to `:print`.|
¶¶Example PostgreSQL Chinook database "album" table:
```
column_name data_type character_maximum_length is_nullable column_default
----------- ----------------- ------------------------ ----------- --------------
album_id integer NO
artist_id integer NO
title character varying 160 NO
```
"""
:examples '(
"""
(do
(load-module :jdbc-postgresql ['jdbc-postgresql :as 'jdbp])
(try-with [conn (jdbp/create-connection "localhost" 5432 "test" "pg" "pg")]
(jdbp/describe-table conn "album")))
""")
:see-also '(
"jdbc-postgresql/foreign-key-constraints") }
describe-table [conn table & options]
(let [opts (apply hash-map options)
mode (:mode opts)]
(try-with [stmt (jdbc/create-statement conn)]
(let [data (jdbc/execute-query stmt
"""
SELECT
column_name,
data_type,
character_maximum_length,
is_nullable,
column_default
FROM
information_schema.columns
WHERE
table_name = '~{table}';
""")]
(if (= mode :data)
data
(jdbc/print-query-result data))))))
(defn
^{ :arglists '(
"(foreign-key-constraints conn & options)" )
:doc """
List the foreign key constraints in a database
¶¶*Options:*
| [![width: 10%]] | [![width: 90%]] |
| :mode | In `:print` mode prints the foreign key \
constraints, in `:data` mode returns the constraints \
as a data structure. Defaults to `:print`.|
¶¶Example PostgreSQL Chinook database foreign key constraints:
```
table_name foreign_key pg_get_constraintdef
-------------- ------------------------------- -------------------------------------------------------------
album album_artist_id_fkey FOREIGN KEY (artist_id) REFERENCES artist(artist_id)
customer customer_support_rep_id_fkey FOREIGN KEY (support_rep_id) REFERENCES employee(employee_id)
employee employee_reports_to_fkey FOREIGN KEY (reports_to) REFERENCES employee(employee_id)
invoice invoice_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
invoice_line invoice_line_invoice_id_fkey FOREIGN KEY (invoice_id) REFERENCES invoice(invoice_id)
invoice_line invoice_line_track_id_fkey FOREIGN KEY (track_id) REFERENCES track(track_id)
playlist_track playlist_track_playlist_id_fkey FOREIGN KEY (playlist_id) REFERENCES playlist(playlist_id)
playlist_track playlist_track_track_id_fkey FOREIGN KEY (track_id) REFERENCES track(track_id)
track track_album_id_fkey FOREIGN KEY (album_id) REFERENCES album(album_id)
track track_genre_id_fkey FOREIGN KEY (genre_id) REFERENCES genre(genre_id)
track track_media_type_id_fkey FOREIGN KEY (media_type_id) REFERENCES
media_type(media_type_id)
```
"""
:examples '(
"""
(do
(load-module :jdbc-postgresql ['jdbc-postgresql :as 'jdbp])
(try-with [conn (jdbp/create-connection "localhost" 5432 "test" "pg" "pg")]
(jdbp/foreign-key-constraints conn)))
""")
:see-also '(
"jdbc-postgresql/describe-table") }
foreign-key-constraints [conn & options]
(let [opts (apply hash-map options)
mode (:mode opts)]
(try-with [stmt (jdbc/create-statement conn)]
(let [data (jdbc/execute-query stmt
"""
SELECT
conrelid::regclass AS table_name,
conname AS foreign_key,
pg_get_constraintdef(oid)
FROM
pg_constraint
WHERE
contype = 'f'
AND
connamespace = 'public'::regnamespace
ORDER BY
conrelid::regclass::text,
contype DESC;
""")]
(if (= mode :data)
data
(jdbc/print-query-result data))))))
(defn- as-properties [user password properties]
(let [props (. :Properties :new)]
(. props :setProperty "user" user)
(. props :setProperty "password" (or password ""))
(doseq [[n v] (seq properties)] (. props :setProperty n v))))