Return a create table statement for this datasets. Datatype appropriate columns will be created based on each column's metadata.

• conn-or-db-name - Either a Connection object or a string database name to use to map database-specific datatypes, such as UUID's, to the appropriate column datatype.
• dataset - dataset or sequence of column metadata maps each of which must contain :name. dataset's metadata itself must contain a :name key.

Options:

All options may be provided as metadata on the table itself.

• :table-name - table name to use.
• :primary-key - Either a string or a sequence of strings indicating the primary key clause.

Column Metadata:

Column metadata must have :name. Each column metadata map may have :sql-datatype in which case this will be used unchangd or :datatype which will be matched against the database name to decide the specific datatype to use.

Create a table. Exception upon failure to drop the table.

• conn - java.sql.Connection
• dataset - dataset to use. The dataset-name will be used as the table-name and the column names and datatypes will be used for the sql names and datatypes.

The dataset's dataset-name will be used for the table and if the dataset's metadata has a :primary-key member this will be interpreted as a sequence of column names to be used as the primary key.

Options:

• :table-name - set the name of the table to use. Overrides the dataset metadata.
• :primary-key - Array of column names to use as the primary key of this table. Overrides the dataset metadata.

Return the database name for this connection.

Return table mapping sql datatype to sql datatype integer for all datatypes this database supports.

Drop a table. Exception upon failure to drop the table.

• conn - java.sql.Connection
• dataset - string, keyword, symbol, or dataset

Drop the table indicated by this dataset if it exists.

Create a table if it does not exist. See documentation for create-table!

Internal method to, using a dataset, execute prepared statement batches drawn from the rows of the dataset. For this to work correctly, the connection needs to have autoCommit set to false and the sql statement's datatypes need to match the dataset datatypes.

See namespace documentation about column metadata and various options that may be associated on a per-column basis to change the way data is uploaded.

• conn - java.sql.Connection
• stmt-or-sql - Either a prepared statement or a string in which case the connection's .prepareStatement method will be called.
• dataset - dataset

Options

• batch-size - integer, defaults to 1024

Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement. This statement will be immediately committed.

Generic function that reads a value from a column.

Generic function that reads a column from a result set.

Insert a dataset into a table. Column datatypes will be inferred from the dataset datatypes or may be provided as metadata on each column. See namespace documentation for :column->sql.

Given a result set return a dataset that serialized the entire resultset into one dataset. See options for result-set->dataset-seq.

Given a result set return a sequence of datasets. Each dataset will be at most :batch-size num-rows in length. Note that the statement will stay open as long as the lazy sequence isn't fully realized which means if you do not realize the sequence you will leave a hanging statement open. There is currently no way to prematurely end the dataset sequence and close the sql statement via the public API.

Options:

• :batch-size - defaults to 64000. Returned datasets will be at most this number of rows long.
• :key-fn - Apply this to the column names -- an example would be keyword.
• :parser-fn - Map of post-key-fn column-name to tuple of [datatype result-set-read-fn] where result-set-read-fn takes a result-set and a column-index and must return data of the appropriate datatype. If the datatype is unknown then nil can be provided and the dataset will use the widest datatype that will fit the returned data or :boolean if all values are missing.
• :statement - The sql statement to close when finished.
• :close? - Close the result set when finished. Defaults to true.

Given result set metadata and a column index return the metadata. Note that the result set metadata for a particular column is save as :result-set-metadata in the column's metadata.

Add a database specific datatype mapping.

• database-name - name of database
• datatype - dtype-next datatype
• sql-datatype - sql datatype to to map to.
• sql-type-index - type index to use to set missing. See datatype-type-table for a way to find the sql type index from the sql datatype.
• result-set-read-fn - function that takes a result set and a column index and returns the data in dtype-next space e.g. a java.time.LocalTime object as opposed to a java.sql.Time object.
• col-read-fn - Function takes a column and a row idx and returns data as an sql datatype e.g. a java.sql.Time object as opposed to a java.time.LocalTime object.

Your type should be a proper tech.v3.datatype datatype meaning it is either an existing datatype or added via tech.v3.datatype.casting/add-object-datatype!.

Example:

For our example let's say we would like to support postgreSQL's jsonb datatype. We first create a record type to denote json data and register it with dtype-next. Next we build a dataset with a column of this datatype.

Next we register a way to input and get back this data from sql. After that columns of the new datatype will be automatically marshalled into/out of sql.

user> (def conn (tech.v3.dataset.sql-test-utils/connect :postgre-sql))
#'user/conn
user> conn
#object[org.postgresql.jdbc.PgConnection 0x421726aa "org.postgresql.jdbc.PgConnection@421726aa"]
user> (require '[tech.v3.dataset :as ds])
nil
user> (require '[tech.v3.datatype.casting :as casting])
nil
user> (require '[tech.v3.dataset.sql :as sql])
nil
user> (require '[clojure.data.json :as json])
nil
user> (defrecord JSONData [json-data])
JSONData
user> (casting/add-object-datatype! :json-data JSONData)
:ok
user> (def json-type-index (-> (sql/database-type-table conn)
                              (ds/filter-column "TYPE_NAME" #(= % "jsonb"))
                              (ds/row-at -1)
                              (get "DATA_TYPE")))
#'user/json-type-index
user> json-type-index
1111
user> (sql/database-name conn)
"PostgreSQL"
user> (sql/set-datatype-mapping!
       "PostgreSQL" :json-data "jsonb" json-type-index
       (fn [^java.sql.ResultSet rs col-idx]
         (when-let [json-obj (.getObject rs col-idx)]
           (-> json-obj
               (str)
               (json/read-str :key-fn keyword)
               (JSONData.))))
       (fn [col idx]
         (when-let [col-obj (col idx)]
           (-> col-obj
               (:json-data)
               (json/write-str)))))
:ok
user> (def table-name "jsontable")
#'user/table-name
user> (def test-ds (ds/->dataset {:jsoncol [(JSONData. {:a 1 :b 2})
                                            (JSONData. {:c 2 :d 3})]}
                                    {:dataset-name table-name}))
#'user/test-ds
user> test-ds
jsontable [2 1]:

|                  :jsoncol |
|---------------------------|
| {:json-data {:a 1, :b 2}} |
| {:json-data {:c 2, :d 3}} |
user> (test-ds :jsoncol)
#tech.v3.dataset.column<json-data>[2]
:jsoncol
[user.JSONData@db1bf962, user.JSONData@db1bf861]
user> (sql/create-table! conn test-ds)
nil
user> (sql/insert-dataset! conn test-ds)
nil
user> (sql/sql->dataset conn (str "select * from " table-name)
                        {:key-fn keyword})
_unnamed [2 1]:

|                  :jsoncol |
|---------------------------|
| {:json-data {:a 1, :b 2}} |
| {:json-data {:c 2, :d 3}} |

Given a connection and an sql statement return a single dataset. See options for result-set->dataset-seq.

Given a connection and an sql statement return a sequence of datasets. See options for result-set->dataset-seq.

Test if a table exists.

• conn - java.sql.Connection
• dataset - string, keyword, symbol, or dataset

Example:

  user> (sql/table-exists? dev-conn stocks)
  true