Taula de continguts

Introducció

Si vols pots crear una consulta SQL amb un String, però el compilador no et por dir si la consulta és correcta ni et pot ajudar a refactoritzar les consultes si hi ha canvis en l'esquema de la base de dades.

Una altre opció és crear la consulta amb el propi llenguatge Kotlin mitjançant un llenguatge específic de domini (DSL) per SQL..

Objecte Table

Una taula SQL es defineixe mitjançant un esquema.

Per exemple, pots crear les taules deparment i employee amb aquestes sentències sql:

TODO revisar

create table department(
  id int primary key auto_increment,
  name text not null,
  location text not null
);

create table employee(
  id int not null primary key auto_increment,
  name text not null,
  job text not null,
  manager_id int,
  hire_date date not null,
  salary bigint not null,
  department_id int not null references deparment
);

Però aquest esquema no el pots utilitzar directament, entre altres coses perquè no està escrit en Kotlin.

El primer pas és definir directament l'esquema en Kotlin mitjançant un objecte que exten la classe Table.

A continuació tens les taules anteriors definides mitjançant objectes:

object Departments : Table<Nothing>("department") {
    val id = int("id").primaryKey()
    val name = text("name")
    val location = text("location")
}

object Employees : Table<Nothing>("employee") {
    val id = int("id").primaryKey()
    val name = text("name")
    val job = text("job")
    val managerId = int("manager_id")
    val hireDate = date("hire_date")
    val salary = long("salary")
    val departmentId = int("department_id")
}

We can see that both Departments and Employees are extending from Table whose constructor accepts a table name as the parameter.

La classe té un paràmetre genèric de tipus que és el tipus de la classe amb la qual podem vincular la taula per poder treballar directament amb objectes enlloc de files tal com veurem a TODO(link entity).

Però en SQL DSL treballem directament amb els resultats SQL, i per això utilitzem el paràmetre de tipus Nothing.

Columns are defined as properties in table objects by Kotlin’s val keyword, their types are defined by type definition functions, such as int, long, varchar, date, etc.

Commonly, these type definition functions follow the rules below:

• They are all Table class’s extension functions that are only allowed to be used in table object definitions.
• Their names are corresponding to the underlying SQL types’ names.
• They all accept a parameter of string type, that is the column’s name.
• Their return types are Column<C>, in which C is the type of current column. We can chaining call the extension function primaryKey to declare the current column as a primary key.

In general, we define tables as Kotlin singleton objects, but we don’t really have to stop there.

For example, sometimes our table is one-off, we don’t need to use it twice, so it’s not necessary to define it as a global object, for fear that the naming space is polluted. This time, we can even define the table as an anonymous object inside a function:

val t = object : Table<Nothing>("config") {
    val key = varchar("key").primaryKey()
    val value = varchar("value")
}

// Get all configs as a Map<String, String>
val configs = database.from(t).select().associate { row -> row[t.key] to row[t.value] }

Activitats

1.- Crea la taula config, inserta alguns valors i verifica que el codi funciona.

Solució

create table config (key string primary key, value string);
insert into config ...

SqlType

SqlType is an abstract class which provides a unified abstraction for data types in SQL.

Based on JDBC, it encapsulates the common operations of obtaining data from a ResultSet and setting parameters to a PreparedStatement.

In the section above, we defined columns by column definition functions, eg. int, varchar, etc. All these functions have a SqlType implementation behind them.

For example, here is the implementation of int function:

// TODO: add imports
import java.sql.Types

// TODO typeName is sql type name?
object IntSqlType: SqlType<Int>(Types.INTEGER, typeName = "int") {
    
    override fun doSetParameter(ps: PreparedStatement, index: Int, parameter: Int) {
        ps.setInt(index, parameter)
    }

    override fun doGetResult(rs: ResultSet, index: Int): Int? {
        return rs.getInt(index)
    }
}

Has de passar al constructor el tipus java.sql.? i el tipus natiu sql??'.

IntSqlType is simple, it just obtaining int query results via ResultSet.getInt and setting parameters via PreparedStatement.setInt.

I a continuació tenim la funció d'extensió BaseTable<*>.int() que registra la columna amb l'objecte IntSqlType:

fun BaseTable<*>.int(name: String): Column<Int> {
    return registerColumn(name, IntSqlType)
}

TODO canviar ordre columnes (en segon lloc sql types)

Here is a list of SQL types supported in Ktorm by default:

En quant al temps, pots veure que:

1. Pels tipus sql timestamp, time i date tens dos tipus de funcions en funció de si treballes amb els tipus java.time.* o java.sql.* (tenen el prefix jdbc).

2. Que JDBC només té el tipus Types.TIMESTAMP pels tipus sql timestamp i datetime, però que ktorm té les dos funcions equivalents que mapejen a tipus diferents (java.time.Instant i java.time.LocalDateTime.

Extend More Data Types

Sometimes, Ktorm’s built-in data types may not satisfy your requirements.

For example, you may want to save a JSON column to a table, many relational databases have supported JSON data type, but raw JDBC haven’t yet, nor Ktorm doesn’t support it by default.

However, you can do it by yourself:

// TODO imports

class JsonSqlType<T : Any>(
    val objectMapper: ObjectMapper,
    val javaType: JavaType
) : SqlType<T>(Types.VARCHAR, "json") {

    override fun doSetParameter(ps: PreparedStatement, index: Int, parameter: T) {
        ps.setString(index, objectMapper.writeValueAsString(parameter))
    }

    override fun doGetResult(rs: ResultSet, index: Int): T? {
        val json = rs.getString(index)
        if (json.isNullOrBlank()) {
            return null
        } else {
            return objectMapper.readValue(json, javaType)
        }
    }
}

The class above is a subclass of SqlType, it provides JSON data type support via the Jackson framework.

Now we have JsonSqlType, how can we use it to define a column?

Looking back the int function’s implementation above, we notice that there is a registerColumn function called. This function is exactly the entry point provided by Ktorm to support datatype extensions.

We can also write an extension function like this:

inline fun <reified C : Any> BaseTable<*>.json(
    name: String,
    mapper: ObjectMapper = sharedObjectMapper
): Column<C> {
    return registerColumn(name, JsonSqlType(mapper, mapper.constructType(typeOf<C>())))
}

The usage is as follows:

object Foo : Table<Nothing>("foo") {
    val bar = json<List<Int>>("bar")
}

In this way, Ktorm is able to read and write JSON columns now.

Activitats

1.- Crea una taula en postgres amb una columna de tipus JSON, insereix dades, i verifica que pots ...

TODO

Solució

2.- Actually, this is one of the features of the ktorm-jackson module, if you really need to use JSON columns, you don’t have to repeat the code above, please add its dependency to your project:

compile "org.ktorm:ktorm-jackson:${ktorm.version}"

Funció de transformació

Escriure una implementació de SqlType porta un cert temps i coneixement.

En alguns casos és més fàcil escriure una funció de transformació a canvi d'una degradació en el rendiment si la funció és complexa.

We can extend data types based on existing ones by adding some specific transformations to them. In this way, we get new data types without writing SqlType implementations manually.

For example, in the following code, we define a column of type Column<UserRole>, but the underlying SQL type in the database is still int. We just perform some transformations while obtaining column values or setting parameters into prepared statements.

val role = int("role").transform({ UserRole.fromCode(it) }, { it.code })

Please note such transformations will perform on every access to a column, that means you should avoid heavy transformations here.