D.6 KSL Database Utilities
Some of the database functionality for use when accessing simulation results has already been discussed in Chapter 5. This section presents some of the more general database utilities available within the KSL framework. These utilities basically exist to help with implementing the KSL database functionality. However, users may find some of this functionality useful for other purpose. However, these utilities are not meant as a substitute for more advanced database frameworks such as JOOQ, Exposed, and KTorm. We refer the interested reader to those libraries for more advanced work for database processing using Kotlin.
Figure D.8 illustrates the properties of the interfaces, DatabaseIOIfc
and DatabaseIfc
which represent the main functionality for working with databases.
The DatabaseIOIfc
interface is responsible for defining the functionality related to input and output. As shown in Figure D.9 the capabilities are easily discerned from the names of the methods. Exporting tables and views to Excel or CSV files are probably the most useful functions. For small tables, capturing the table as a Markdown table can be useful.
The DatabaseIfc
interface defines what a database can do and the Database
class provides a concrete implementation for working with a database. The functionality of the DatabaseIfc
interface is extensive. Besides the IO related functions, we note a few useful methods here. The execute
methods allow the execution of an SQL string, list of SQL commands, or script file. It is the user’s responsibility for forming an appropriate SQL string.
- executeCommand(String) Boolean
- executeCommand(String) Boolean
- executeCommands(List\(<\)String\(>\)) Boolean
- executeCommands(List\(<\)String\(>\)) Boolean
- executeScript(Path) Boolean
- executeScript(Path) Boolean
There are methods for checking if the database contains specific named tables or views.
- containsSchema(String) Boolean
- containsSchema(String) Boolean
- containsTable(String) Boolean
- containsTable(String, String) Boolean
- containsTable(String, String) Boolean
- containsTable(String) Boolean
- containsView(String) Boolean
- containsView(String) Boolean
A set of methods for fetching data from the database facilitate the execution of SQL select statements. The select all methods will select all the data from a named table or view.
- fetchCachedRowSet(String) CachedRowSet?
- fetchCachedRowSet(String) CachedRowSet?
- fetchOpenResultSet(String) ResultSet?
- fetchOpenResultSet(String) ResultSet?
- selectAll(String, String?) CachedRowSet?
- selectAll(String, String?) CachedRowSet?
- selectAllIntoOpenResultSet(String, String?) ResultSet?
- selectAllIntoOpenResultSet(String, String?) ResultSet?
The interface also provides some basic methods for inserting and updating data within the database by using Kotlin data classes, specifically sub-classes of the DbTableData
class, which extends the TabularData
class and allows the user to supply the data for a table in a data class instance similar to how it was done for working with tabular files. However, in this case more general mapping of data types to database types is permitted besides just numeric or text.
- insertDbDataIntoTable(List\(<\)String\(>\), String, String?) Int
- insertDbDataIntoTable(T, String, String?) Int
- insertDbDataIntoTable(List\(<\)String\(>\), String, String?) Int
- insertDbDataIntoTable(T, String, String?) Int
- updateDbDataInTable(List\(<\)String\(>\), String, String?) Int
- updateDbDataInTable(T, String, String?) Int
- updateDbDataInTable(List\(<\)String\(>\), String, String?) Int
- updateDbDataInTable(T, String, String?) Int
When working with the database it is useful to get a connection, access table meta data, check if tables have data, check if tables or views exist.
- getConnection() Connection
- getSchemas() List\(<\)String\(>\)
- getUserDefinedTables() List~\(<\)String\(>\)
- getViews() List\(<\)String\(>\)
- hasData(String?) Boolean
- hasData(String?) Boolean
- hasTables(String) Boolean
- hasTables(String) Boolean
- isTableEmpty(String, String?) Boolean
- isTableEmpty(String, String?) Boolean
- numRows(String, String?) Long
- numRows(String, String?) Long
- tableMetaData(String, String?) List\(<\)ColumnMetaData\(>\)
- tableMetaData(String, String?) List\(<\)ColumnMetaData\(>\)
- tableNames(String) List\(<\)String\(>\)
- tableNames(String) List\(<\)String\(>\)
- viewNames(String) List\(<\)String\(>\)
- viewNames(String) List\(<\)String\(>\)
We refer the interested reader to the KSL KDoc documentation for further details about using these methods.
The DatabaseIfc
functionality defines basic capabilities for working with any database implementation. The KSL provides functionality to create SQLite, Derby , and DuckDb embedded databases. In addition, the KSL facilitates the creation of a database on a Postgres database server.
Figure D.10 illustrates the functionality of the EmbeddedDbIfc
interface, and its SQLite, Derby, and DuckDb implementations. Readers interested in other databases can review these implementations for how to structure code for other databases. The PostgresDb
class provides similar functionality. The main purpose is to be able to supply a data source to the Database
class.
Figure D.11 provides the functionality for the KSLDatabase
class. Although most of this functionality has been mentioned within Chapter 5, it is useful to note that since the KSLDatabase
class is a database, it also has all of the previously mentioned database functionality.
The main notable methods involve the export, printing, or writing of the underlying data from the tables or views storing the simulation statistical results. In addition, there are a number of properties that will provide data frame representations of the simulation output data. For example, the data class WithinRepStatTableData
represents the statistics collected within each replication and is used to by the withinReplicationResponseStatistics
property to create a data frame that holds the statistical data. Figure D.12 provides the data elements held for within replication data.
The within replication statistical data represents the summary statistics of the data collected during a replication. The most relevant properties are the average
, minimum
, and maximum
. To compute across replication statistics we can use the average
for each replication. The statistical quantities are captured within the underlying database as discussed in Chapter 5. The user can post-process any of this data using commonly available database technology and SQL.
The database utilities also offers the ability to quickly create simple databases via the Database
companion object’s createSimpleDb()
function. The purpose of this function is to allow the creation of a quick and dirty database solution based on the DbTableData
data classes. By defining data classes that are sub-classes of DbTableData
, a CREATE TABLE
specification can be obtained and the database created. Then, the database can be used to insert data from instances of the DbTableData
sub-classes. The following code illustrates this possibility.
First, we define two data classes that extend the DbTableData
class. In this code, we define a Person
and a City
class, each with a property id
that will act as the primary key of the table. The primary key must be specified; however, the use of an auto-generated (surrogate) key is not supported at this time.
data class Person(
var id: Int,
var name: String,
var age: Int
) : DbTableData("Persons", listOf("id"))
data class City(
var id: Int,
var name: String,
var population: Int
) : DbTableData("Cities", listOf("id"))
Then, by providing a simple instance of these classes to the SimpleDb
class constructor, we will cause a database to be created that is setup to hold the data from the supplied data classes. If you want to see the CREATE TABLE
statements used to define the tables, then uncomment the two print statements in the following code.
val p = Person(1, "manuel", age = 10)
// println(p.createTableSQLStatement())
val c = City(1, "London", population = 1000)
// println(c.createTableSQLStatement())
val db = Database.createSimpleDb(setOf(p, c), "TestSimpleDb")
db.insertDbDataIntoTable(p)
db.insertDbDataIntoTable(c)
You can then used the full functionality provided by the KSL database utilities because the createSimpleDb()
function returns an instance of the DatabaseIfc
and DatabaseIOIfc
interfaces. This functionality is useful for setting up small databases to hold generated data. It does not facilitate more advanced data definition or object-relational mappings. Please see more advanced database frameworks such as JOOQ, Exposed, and KTorm for more robust solutions.