Table mapping

This step allows describing how to map a table to a class (aka Entity).

Table of content

• Mapping of the database model
  • Entities = simple Kotlin classes
  • Mapping entities to database tables
  • Declare your indexes
  • Identity
  • Generic table mapping
• Data types
  • PostgreSQL
  • MySQL
  • H2
  • MSSQL
  • MariaDB
  • Oracle
  • SqLite

Mapping of the database model

Entities = simple Kotlin classes

Kotlin's data classes are great for entities because their main purpose is to hold data.
These are 2 simple entities that we will use :

data class Role(
        val label: String,
        val id: UUID = UUID.randomUUID()
)

data class User(
        val firstname: String,
        val lastname: String,
        val isAdmin: Boolean,
        val roleId: UUID,
        val messageCount: Int = 0,
        val alias: String? = null,
        val id: Int? = null
)

Mapping entities to database tables

This is the ORM (object-relational mapping) step. Define all mappings rules between your entities and the database tables

• columns
• primary and foreign keys
• indexes
• identity (for auto-generated number columns)

This DSL is based on type and nullability of the fields of your entities.

object Roles : H2Table<Role>("roles") {
    val id = uuid(Role::id)
            .primaryKey()
    val label = varchar(Role::label)
}

object Users : H2Table<User>("users") {
    val id = autoIncrementInteger(User::id)
        .primaryKey("PK_users")
    val firstname = varchar(User::firstname, "fname")
    val lastname = varchar(User::lastname, "lname")
    val isAdmin = boolean(User::isAdmin)
    val roleId = uuid(User::roleId)
            .foreignKey(Roles.id, "FK_users_roles")
    val messageCount = integer(User::messageCount)
    val alias = varchar(User::alias)
}

private val tables = tables().h2(Roles, Users)

TIP

auto-incremented and identity entity fields (see below) can be either nullable, with a null default value, or non-null with a default value of 0 or negative.

Declare your indexes

In Kotysa, indexes are part of the table mapping. Just add unique on a column, or create an index from several columns of a table.

object Roles : H2Table<Role>("roles") {
    val id = uuid(Role::id)
        .primaryKey()
    val label = varchar(Role::label)
        .unique() // unique index on label column
}

object Users : H2Table<User>("users") {
    // ...
    val firstname = varchar(User::firstname, "fname")
    val lastname = varchar(User::lastname, "lname")
    // ...
    init {
        // creates an index on the firstname, lastname column pair
        index(setOf(firstname, lastname), indexName = "full_name_index")
    }
}

Identity

In Kotysa, identity is part of the table mapping. Simply declare auto-generated Int and Long columns as identity

TIP

Identity is available for PostgreSQL, Oracle, MSSQL and H2

object OracleEntities : OracleTable<OracleEntity>() {
    val id = number(OracleEntity::id)
        .identity()
        .primaryKey()
}

Generic table mapping

In order to make integration tests of your repositories easier, Kotysa offers GenericTable

• extend GenericTable in this case instead of the DB specific one
• tables() DSL for H2, PostgreSQL and MSSQL accept a combination of DB specific tables OR GenericTable

object Roles : GenericTable<Role>("roles") {
    val id = uuid(Role::id)
        .primaryKey()
    val label = varchar(Role::label)
}

// in your main code, use PostgreSQL for example
private val mainTables = tables().postgresql(Roles)

// in your test code, use H2
private val testTables = tables().h2(Roles)

Data types

Tables below list all the data types that are supported by Kotysa for each database provider.

TIP

Kotysa uses Java 8+ java.time.* and kotlinx-datetime corresponding types for dates.

PostgreSQL

Kotlin type	Description	SQL type
String	Represents a variable-length character string, maximum length fixed	varchar
	Represents a variable-length character string, unlimited length	text
java.time.LocalDate or kotlinx.datetime.LocalDate	Represents a date without time part and without timezone	date
java.time.LocalDateTime or kotlinx.datetime.LocalDateTime	Represents a date+time without timezone	timestamp
java.time.OffsetDateTime	Represents a date+time with timezone. PostgreSQL uses UTC timezone to store TIMESTAMP WITH TIME ZONE, so you may have to override equals to use Instant based "isEqual" method on java.time.OffsetDateTime fields	timestampWithTimeZone
java.time.LocalTime or kotlinx.datetime.LocalTime	Represents a time without a date part and without timezone	time
Boolean	Represents a boolean state	boolean
java.util.UUID	Universally unique identifier (128 bit value)	uuid
Int	Represents an integer	integer
	Represents an auto-incremented integer	serial
Long	Represents a long	bigInt
	Represents an auto-incremented long	bigSerial
Float	Represents a single precision floating point number	real
Double	Represents a double precision floating point number	doublePrecision
BigDecimal	Represents a exact decimal number with fixed precision and scale	decimal
		numeric
ByteArray	Binary object stored as bytes	bytea

MySQL

Kotlin type	Description	SQL type
String	Represents a variable-length character string, maximum length fixed (size is mandatory in MySQL). Default size = 255	varchar
	Represents a variable-length character string, max length = 255	tinytext
	Represents a variable-length character string, max length = 65_535	text
	Represents a variable-length character string, max length = 16_777_215	mediumtext
	Represents a variable-length character string, max length = 4_294_967_295	longtext
java.time.LocalDate or kotlinx.datetime.LocalDate	Represents a date without time part and without timezone	date
java.time.LocalDateTime or kotlinx.datetime.LocalDateTime	Represents a date+time without timezone	datetime
java.time.LocalTime or kotlinx.datetime.LocalTime	Represents a time without a date part and without timezone	time
Boolean	Represents a boolean state	boolean
Int	Represents an integer	integer
	Represents an auto-incremented integer	autoIncrementInteger
Long	Represents a long	bigInt
	Represents an auto-incremented long	autoIncrementBigInt
Float	Represents a single precision floating point number	float
Double	Represents a double precision floating point number	doublePrecision
BigDecimal	Represents a exact decimal number with fixed precision and scale	decimal
		numeric
ByteArray	Binary object stored as bytes	binary
	Large binary object stored as bytes => only supported with jdbc	blob

H2

Kotlin type	Description	SQL type
String	Represents a variable-length character string, maximum length fixed	varchar
java.time.LocalDate or kotlinx.datetime.LocalDate	Represents a date without time part and without timezone	date
java.time.LocalDateTime or kotlinx.datetime.LocalDateTime	Represents a date+time without timezone	timestamp
		datetime
java.time.OffsetDateTime	Represents a date+time with timezone	timestampWithTimeZone
java.time.LocalTime or kotlinx.datetime.LocalTime	Represents a time without a date part and without timezone	time
Boolean	Represents a boolean state	boolean
java.util.UUID	Universally unique identifier (128 bit value)	uuid
Int	Represents an integer	integer
	Represents an auto-incremented integer	autoIncrementInteger
Long	Represents a long	bigInt
	Represents an auto-incremented long	autoIncrementBigInt
Float	Represents a single precision floating point number	real
Double	Represents a double precision floating point number	doublePrecision
BigDecimal	Represents a exact decimal number with fixed precision and scale	decimal
		numeric
ByteArray	Binary object stored as bytes	binary
	Large binary object stored as bytes => only supported with jdbc	blob

MSSQL

Kotlin type	Description	SQL type
String	Represents a variable-length character string, maximum length fixed	varchar
java.time.LocalDate or kotlinx.datetime.LocalDate	Represents a date without time part and without timezone	date
java.time.LocalDateTime or kotlinx.datetime.LocalDateTime	Represents a date+time without timezone	datetime
java.time.OffsetDateTime	Represents a date+time with timezone. MSSQL uses UTC timezone to store DATETIMEOFFSET, so you may have to override equals to use Instant based "isEqual" method on java.time.OffsetDateTime fields	dateTimeOffset
java.time.LocalTime or kotlinx.datetime.LocalTime	Represents a time without a date part and without timezone	time
Boolean	Represents a boolean state	bit
java.util.UUID	Universally unique identifier (128 bit value)	uniqueIdentifier
Int	Represents an integer	integer
Long	Represents a long	bigInt
Float	Represents a single precision floating point number	real
Double	Represents a double precision floating point number	float
BigDecimal	Represents a exact decimal number with fixed precision and scale	decimal
		numeric
ByteArray	Binary object stored as bytes	binary

MariaDB

Kotlin type	Description	SQL type
String	Represents a variable-length character string, maximum length fixed (size is mandatory in MariaDB). Default size = 255	varchar
	Represents a variable-length character string, max length = 255	tinytext
	Represents a variable-length character string, max length = 65_535	text
	Represents a variable-length character string, max length = 16_777_215	mediumtext
	Represents a variable-length character string, max length = 4_294_967_295	longtext
java.time.LocalDate or kotlinx.datetime.LocalDate	Represents a date without time part and without timezone	date
java.time.LocalDateTime or kotlinx.datetime.LocalDateTime	Represents a date+time without timezone	datetime
java.time.LocalTime or kotlinx.datetime.LocalTime	Represents a time without a date part and without timezone	time
Boolean	Represents a boolean state	boolean
Int	Represents an integer	integer
	Represents an auto-incremented integer	autoIncrementInteger
Long	Represents a long	bigInt
	Represents an auto-incremented long	autoIncrementBigInt
Float	Represents a single precision floating point number	float
Double	Represents a double precision floating point number	doublePrecision
BigDecimal	Represents a exact decimal number with fixed precision and scale	decimal
		numeric
ByteArray	Binary object stored as bytes	binary
	Large binary object stored as bytes => only supported with jdbc	blob

Oracle

Kotlin type	Description	SQL type
String	Represents a variable-length character string, maximum length fixed	varchar2
java.time.LocalDate or kotlinx.datetime.LocalDate	Represents a date without time part and without timezone	date
java.time.LocalDateTime or kotlinx.datetime.LocalDateTime	Represents a date+time without timezone	timestamp
java.time.OffsetDateTime	Represents a date+time with timezone	timestampWithTimeZone
Boolean	Represents a boolean state	number
Int	Represents an integer
Long	Represents a long
BigDecimal	Represents a exact decimal number with fixed precision and scale
Float	Represents a single precision floating point number	binaryFloat
Double	Represents a double precision floating point number	binaryDouble
ByteArray	Binary object stored as bytes	raw

SqLite

Kotlin type	Description	SQL type
String	Represents a variable-length character string, maximum length fixed	text
java.time.LocalDate or kotlinx.datetime.LocalDate	Represents a date without time part and without timezone
java.time.LocalDateTime or kotlinx.datetime.LocalDateTime	Represents a date+time without timezone
java.time.OffsetDateTime	Represents a date+time with timezone
java.time.LocalTime or kotlinx.datetime.LocalTime	Represents a time without a date part and without timezone
Boolean	Represents a boolean state	integer
Int	Represents an integer
	Represents an auto-incremented integer	autoIncrementInteger
Long	Represents a long	integer
	Represents an auto-incremented long	autoIncrementInteger
Float	Represents a single precision floating point number	real
Double	Represents a double precision floating point number
ByteArray	Binary object stored as bytes	blob