DuckDb

class DuckDb(dbName: String, dbDirectory: Path = KSL.dbDir, deleteIfExists: Boolean = true) : Database

Facilitates the creation of a database backed by DuckDb. The database will be empty.

Return

a DuckDb configured database

Parameters

dbName

the name of the database

dbDirectory

the directory containing the database. By default, KSL.dbDir.

deleteIfExists

If true, an existing database in the supplied directory with the same name will be deleted and an empty database will be constructed.

Constructors

Link copied to clipboard
constructor(tableDefinitions: Set<DbTableData>, dbName: String, dbDirectory: Path = KSL.dbDir, deleteIfExists: Boolean = true)

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.

constructor(dbName: String, dbDirectory: Path = KSL.dbDir, deleteIfExists: Boolean = true)

Types

Link copied to clipboard

Properties

Link copied to clipboard
override val dataSource: DataSource

the DataSource backing the database

Link copied to clipboard
override val dbURL: String?

The URL used to establish the connection to the database.

Link copied to clipboard
override var defaultSchemaName: String?

Sets the name of the default schema

Link copied to clipboard
override var label: String

identifying string representing the database. This has no relation to the name of the database on disk or in the dbms. The sole purpose is for labeling of output

Link copied to clipboard

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.

Link copied to clipboard
Link copied to clipboard
open override val schemaNames: List<String>

The list may be empty if the database does not support the schema concept.

Link copied to clipboard
open override val userDefinedTables: Map<String?, List<String>>

Uses the longLastingConnection property for the connection.

Link copied to clipboard
open override val views: Map<String?, List<String>>

Uses the longLastingConnection property for the connection.

Functions

Link copied to clipboard
fun <T : DbTableData> appendDbDataToTable(data: List<T>, tableName: String, schemaName: String? = defaultSchemaName)

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.

Link copied to clipboard
open fun areAllTablesEmpty(schemaName: String? = defaultSchemaName): Boolean

Uses the longLastingConnection property for the connection.

Link copied to clipboard
open fun asString(): String
Link copied to clipboard
open fun containsSchema(schemaName: String): Boolean

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.

Link copied to clipboard
open fun containsTable(tableName: String, schemaName: String? = defaultSchemaName): Boolean

Checks if the supplied table exists in the schema.

Link copied to clipboard
open fun containsView(viewName: String, schemaName: String? = defaultSchemaName): Boolean

Uses the longLastingConnection property for the connection.

Link copied to clipboard
Link copied to clipboard
open fun <T : DbTableData> createSimpleDbTable(tableDefinition: T, autoCreateSchema: Boolean = false)

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.

Link copied to clipboard
open fun <T : DbTableData> createSimpleDbTables(tableDefinitions: Set<T>, autoCreateSchema: Boolean = false)

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.

Link copied to clipboard
open fun deleteAllFrom(schemaName: String? = defaultSchemaName)

Deletes all data from tables within the specified schema. If there is null, then the tables not associated with a schema are deleted.

open fun deleteAllFrom(tableName: String, schemaName: String? = defaultSchemaName): Boolean

Deletes all data from the named table.

open fun deleteAllFrom(tableNames: List<String>, schemaName: String? = defaultSchemaName)

Deletes all data from the tables in the list.

Link copied to clipboard
open fun dropSchema(schemaName: String, tableNames: List<String>, viewNames: List<String>)

Drops the named schema from the database. If no such schema exist with the name, then nothing is done.

Link copied to clipboard
open fun executeCommand(command: String): Boolean

Executes a single command on a database connection

Link copied to clipboard
open fun executeCommands(commands: List<String>): Boolean

Consecutively executes the list of SQL queries supplied as a list of strings The strings must not have ";" semicolon at the end.

Link copied to clipboard
open fun executeScript(path: Path): Boolean

Executes the commands in the script on the database

Link copied to clipboard
open override fun exportAllTablesAsCSV(schemaName: String?, pathToOutPutDirectory: Path, header: Boolean)

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.

Link copied to clipboard
open override fun exportAllTablesAsInsertQueries(schemaName: String?, out: PrintWriter)

Writes all table data as insert queries to the PrintWriter

Link copied to clipboard
open override fun exportAllViewsAsCSV(schemaName: String?, pathToOutPutDirectory: Path, header: Boolean)

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.

Link copied to clipboard
fun exportAsLoadableCSV(exportDir: Path = KSL.dbDir)

Exports the database to a directory with loadable default CSV format. See DuckDb documentation.

Link copied to clipboard
fun exportAsLoadableParquetFiles(exportDir: Path = KSL.dbDir)

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.

Link copied to clipboard
open override fun exportInsertQueries(tableName: String, schemaName: String?, out: PrintWriter)

Writes the insert queries associated with the supplied table to the PrintWriter.

Link copied to clipboard
open override fun exportTableAsCSV(tableName: String, schemaName: String?, out: PrintWriter, header: Boolean)

Writes the table as comma separated values.

Link copied to clipboard
open override fun exportToExcel(schemaName: String?, wbName: String, wbDirectory: Path)

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.

open override fun exportToExcel(tableNames: List<String>, schemaName: String?, wbName: String, wbDirectory: Path)

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.

Link copied to clipboard

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.

Link copied to clipboard

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.

Link copied to clipboard
override fun getConnection(): Connection

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.

Link copied to clipboard
open fun hasData(schemaName: String? = defaultSchemaName): Boolean

Uses the longLastingConnection property for the connection.

Link copied to clipboard
open fun hasTables(schemaName: String? = defaultSchemaName): Boolean

Checks if tables exist in the specified schema.

Link copied to clipboard
open override fun importSheetToTable(sheet: Sheet, tableName: String, numColumns: Int, schemaName: String?, numRowsToSkip: Int, rowBatchSize: Int, unCompatibleRows: PrintWriter): Boolean

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

Link copied to clipboard
open override fun importWorkbookToSchema(pathToWorkbook: Path, tableNames: List<String>, schemaName: String?, skipFirstRow: Boolean)

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.

Link copied to clipboard
open fun <T : DbTableData> insertAllDbDataIntoTable(data: List<T>, tableName: String, schemaName: String? = defaultSchemaName): Int

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.

Link copied to clipboard
open fun <T : DbTableData> insertDbDataIntoTable(data: T, tableName: String = data.tableName, schemaName: String? = defaultSchemaName): Int

Inserts the data from the DbData instance into the supplied table tableName and schema schemaName. The DbData instance must be designed for the table.

Link copied to clipboard
open fun isTableEmpty(tableName: String, schemaName: String? = defaultSchemaName): Boolean

Uses the longLastingConnection property for the connection.

Link copied to clipboard
open fun numRows(tableName: String, schemaName: String? = defaultSchemaName): Long

Uses the longLastingConnection property for the connection.

Link copied to clipboard
open override fun printAllTablesAsInsertQueries(schemaName: String?)

Prints all table data as insert queries to the console

Link copied to clipboard
open override fun printAllTablesAsMarkdown(schemaName: String?)

Prints all tables as text to the console.

Link copied to clipboard
open override fun printAllTablesAsText(schemaName: String?)

Prints all tables as text to the console.

Link copied to clipboard
open override fun printInsertQueries(tableName: String, schemaName: String?)

Prints the insert queries associated with the supplied table to the console

Link copied to clipboard
open override fun printTableAsCSV(tableName: String, schemaName: String?, header: Boolean)

Prints the table as comma separated values to the console.

Link copied to clipboard
open override fun printTableAsMarkdown(tableName: String, schemaName: String?)

Prints the table as prettified text to the console.

Link copied to clipboard
open override fun printTableAsText(tableName: String, schemaName: String?)

Prints the table as prettified text to the console.

Link copied to clipboard
open fun selectAll(tableName: String, schemaName: String? = defaultSchemaName): CachedRowSet?

Uses the longLastingConnection property for the connection.

Link copied to clipboard
open fun selectAllIntoOpenResultSet(tableName: String, schemaName: String? = defaultSchemaName): ResultSet?

Uses the longLastingConnection property for the connection.

Link copied to clipboard
open fun <T : TabularData> selectTableDataIntoDbData(factory: () -> T): List<T>

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.

Link copied to clipboard
fun summarize(tableName: String, schemaName: String? = defaultSchemaName): ResultSet?

Applies DuckDb's summarize query to the table/view

Link copied to clipboard
open fun tableMetaData(tableName: String, schemaName: String? = defaultSchemaName): List<ColumnMetaData>
Link copied to clipboard
open fun tableNames(schemaName: String?): List<String>
Link copied to clipboard
open override fun toString(): String
Link copied to clipboard
open fun <T : DbTableData> updateDbDataInTable(data: T, tableName: String = data.tableName, schemaName: String? = defaultSchemaName): Int

Updates the table based on the supplied data. The DbData instance must be designed for the table.

open fun <T : DbTableData> updateDbDataInTable(data: List<T>, tableName: String, schemaName: String? = defaultSchemaName): Int

Updates the table based on the data from the list. The DbData instance must be designed for the table.

Link copied to clipboard
open fun viewNames(schemaName: String?): List<String>
Link copied to clipboard
open override fun writeAllTablesAsMarkdown(schemaName: String?, out: PrintWriter)

Writes all tables as text.

Link copied to clipboard
open override fun writeAllTablesAsText(schemaName: String?, out: PrintWriter)

Writes all tables as text.

Link copied to clipboard
open override fun writeAllViewsAsMarkdown(schemaName: String?, out: PrintWriter)

Writes all tables as text.

Link copied to clipboard
open override fun writeTableAsMarkdown(tableName: String, schemaName: String?, out: PrintWriter)

Writes the table as prettified text.

Link copied to clipboard
open override fun writeTableAsText(tableName: String, schemaName: String?, out: PrintWriter)

Writes the table as prettified text.