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.

Properties of the Main Database Classes

Figure D.8: Properties of the Main Database Classes

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.

Methods of the DatabaseIOIfc Interface

Figure D.9: Methods of the DatabaseIOIfc Interface

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.

Working with Embedded Databases

Figure D.10: Working with Embedded Databases

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 KSLDatabase Class

Figure D.11: The KSLDatabase Class

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.

Within Replication Statistical Data

Figure D.12: Within Replication Statistical 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.