Duck Db
Facilitates the creation of a database backed by DuckDb. The database will be empty.
Return
a DuckDb configured database
Parameters
the name of the database
the directory containing the database. By default, KSL.dbDir.
If true, an existing database in the supplied directory with the same name will be deleted and an empty database will be constructed.
Constructors
This constructs a simple DuckDb database on disk. The database will contain empty tables based on the table definitions. If the database already exists on disk, it will be deleted and recreated.
Properties
the DataSource backing the database
Sets the name of the default schema
A connection that is meant to be used many times before manual closing. Many functions rely on this connection as their default connection. Do not close this connection unless you are really finished with the database. Since, this property is final it cannot be restored after closing.
The list may be empty if the database does not support the schema concept.
Uses the longLastingConnection property for the connection.
Functions
Uses DuckDb's Appender to more efficiently append records to the table. The data in the list must be associated with the named table. The table must be within the named schema. The data must not have an auto-increment field.
Uses the longLastingConnection property for the connection.
The name of the schema is first checked for an exact lexicographical match. If a match occurs, the schema is returned. If a lexicographical match fails, then a check for a match ignoring the case of the string is performed. This is done because SQL identifier names should be case-insensitive. If neither matches then false is returned.
Checks if the supplied table exists in the schema.
Uses the longLastingConnection property for the connection.
The purpose of this function is to allow the creation of a simple database table based on a DbTableData data class. By defining a data class that is a subclasses of DbTableData, a limited CREATE TABLE specification can be obtained and the table created. Then, the database can be used to insert data from instances of the DbTableData subclass. The DbTableData data class cannot have a auto-increment type primary key. In addition, the table will not have foreign key specifications nor referential integrity specifications. If supported by the underlying database engine, additional specifications could be added via alter table DDL specifications.
The purpose of this function is to allow the creation of simple database tables based on the DbTableData data classes. By defining data classes that are subclasses of DbTableData, a limited CREATE TABLE specification can be obtained and the table created. Then, the database can be used to insert data from instances of the DbTableData subclasses. The DbTableData data classes cannot have auto-increment type primary keys. In addition, the tables will not have foreign key specifications nor referential integrity specifications. If supported by the underlying database engine, additional specifications could be added via alter table DDL specifications.
Deletes all data from tables within the specified schema. If there is null, then the tables not associated with a schema are deleted.
Deletes all data from the named table.
Deletes all data from the tables in the list.
Executes a single command on a database connection
Consecutively executes the list of SQL queries supplied as a list of strings The strings must not have ";" semicolon at the end.
Executes the commands in the script on the database
Writes all tables as separate comma separated value files into the supplied directory. The files are written to text files using the same name as the tables in the database.
Writes all table data as insert queries to the PrintWriter
Writes all tables as separate comma separated value files into the supplied directory. The files are written to text files using the same name as the tables in the database.
Exports the database to a directory with loadable default CSV format. See DuckDb documentation.
Exports the database to a directory with loadable default Parquet file format. See DuckDb documentation.
Exports the database to a directory with loadable default Parquet format. See DuckDb documentation.
Writes the insert queries associated with the supplied table to the PrintWriter.
Writes the table as comma separated values.
Writes each table in the schema to an Excel workbook with each table being placed in a new sheet with the sheet name equal to the name of the table. The column names for each table are written as the first row of each sheet.
Writes each table in the list to an Excel workbook with each table being placed in a new sheet with the sheet name equal to the name of the table. The column names for each table are written as the first row of each sheet.
A simple wrapper to ease the use of JDBC for novices. Returns the results of a query in the form of a JDBC CachedRowSet. Errors in the SQL are the user's responsibility. Any exceptions are logged and squashed. The underlying query is closed.
A simple wrapper to ease the use of JDBC for novices. Returns the results of a query in the form of a JDBC ResultSet that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY . Errors in the SQL are the user's responsibility. Any exceptions are logged and squashed. It is the user's responsibility to close the ResultSet. That is, the statement used to create the ResultSet is not automatically closed.
It is best to use this function within a try-with-resource construct This method calls the DataSource for a connection from the underlying DataSource. You are responsible for closing the connection.
Copies the rows from the sheet to the table. The copy is assumed to start at row 1, column 1 (i.e. cell A1) and proceed to the right for the number of columns in the table and the number of rows of the sheet. The copy is from the perspective of the table. That is, all columns of a row of the table are attempted to be filled from a corresponding row of the sheet. If the row of the sheet does not have cell values for the corresponding column, then the cell is interpreted as a null value when being placed in the corresponding column. It is up to the client to ensure that the cells in a row of the sheet are data type compatible with the corresponding column in the table. Any rows that cannot be transfer in their entirety are logged to the supplied PrintWriter
Opens the workbook for reading only and writes the sheets of the workbook into database tables. The list of names is the names of the sheets in the workbook and the names of the tables that need to be written. They are in the order that is required for entering data so that no integrity constraints are violated. The underlying workbook is closed after the operation.
Inserts the data from the list into the supplied table tableName and schema schemaName. The DbData instances must be designed for the same table. The data instances are not updated to reflect any changes imposed by the database such as generated primary keys.
Inserts the data from the DbData instance into the supplied table tableName and schema schemaName. The DbData instance must be designed for the table.
Uses the longLastingConnection property for the connection.
Prints all table data as insert queries to the console
Prints all tables as text to the console.
Prints all tables as text to the console.
Prints the insert queries associated with the supplied table to the console
Prints the table as comma separated values to the console.
Prints the table as prettified text to the console.
Prints the table as prettified text to the console.
Uses the longLastingConnection property for the connection.
Uses the longLastingConnection property for the connection.
Selects data from the database and fills a list with instances of the data class. The factory must produce an instance of a subclass, T of DbData. Subclasses of type DbData are data classes that have been configured to hold data from a named table from the database. See the documentation on DbData for further information. The resulting list of data is not connected to the database in any way.
Updates the table based on the supplied data. The DbData instance must be designed for the table.
Updates the table based on the data from the list. The DbData instance must be designed for the table.
Writes all tables as text.
Writes all tables as text.
Writes all tables as text.
Writes the table as prettified text.
Writes the table as prettified text.