3. Basic Usage

Creating Columns

Before creating a table, you need to create columns that you can use for the table.

Built-in Column Types

• INT
• varchar()
• TEXT
• BOOLEAN
• TIMESTAMP
• JSON
• BLOB
• UUID

Creating a Custom Column Type

All you need to do is extend the Column.Type class, and implement

abstract fun format(value: T): String

Here is the implementation for varchar(), which can have varying parameters to specify how long it should be:

fun varchar(size: Int): Type<String> {
    return object : Type<String>("VARCHAR($size)") {
        override fun format(value: String): String {
            return "'$value'"
        }
    }
}

Creating a Column

Columns are created using the Column.Type class.

Here is an example using the INT column type:

Column.Type.INT.createColumn(
            tableName = "my_table
            name: "my_column",
            collation = Collation("my_collation"),
            storageType = Column.StorageType.DEFAULT,
            compressionType = Column.CompressionType.PGLZ,
            constraints = arrayOf(Column.Constraint.NOT_NULL, Column.Constraint.PRIMARY_KEY)
        )

You do not need to include collation, storageType, compressionType, or constraints if you do not need them.

Creating a Table

Now you are able to create a table, and here is an example of how you can do so:

import com.fisherl.databasehelper.column.Column.Constraint.NOT_NULL
import com.fisherl.databasehelper.column.Column.Constraint.PRIMARY_KEY
import com.fisherl.databasehelper.column.Column.Type.UUID
import com.fisherl.databasehelper.column.Column.Type.Companion.varchar
import com.fisherl.databasehelper.column.Column.Type.INT

const val USER_TABLE_NAME = "users"

const val USER_ID_COL = UUID.createColumn(USER_TABLE_NAME, "id", constraints = arrayOf(NOT_NULL, PRIMARY_KEY))
const val USER_NAME_COL = varchar(50).createColumn(USER_TABLE_NAME, "name", constraints = arrayOf(NOT_NULL))
const val USER_EMAIL_COL = varchar(100).createColumn(USER_TABLE_NAME, "email", constraints = arrayOf(NOT_NULL))
const val USER_BALANCE_COL = INT.createColumn(USER_TABLE_NAME, "balance")

const val USER_TABLE = Table<User>(
    USER_TABLE_NAME,
    listOf(USER_ID_COL, USER_NAME_COL, USER_EMAIL_COL, USER_BALANCE_COL),
    ifNotExists = true
)

Note that I am directly importing many of the objects to make the example cleaner, but you may do whatever you think is best.

Statements

A statement is created using the DatabaseDialect class. Currently there is only PostgresDialect, so that is what I will be using for examples. The following examples will user the user table and columns from above.

Create Table Statement

val statement: String = PostgresDialect.createTableStatement(USER_TABLE)

This creates the following statement:

CREATE TABLE IF NOT EXISTS animals (id UUID NOT NULL PRIMARY KEY, name VARCHAR(50) NOT NULL, email varchar(50) NOT NULL, balance INT);

Select Statement

val selectAllUsersStatement = SelectStatement.Builder(
    USER_TABLE,
    listOf(USER_ID_COL, USER_NAME_COL, USER_EMAIL_COL, USER_BALANCE_COL)
).build()
val statement: String = PostgresDialect.createSelectStatement(selectAllUsersStatement)

This creates the following statement:

SELECT user.id, user.name, user.email, user.balance FROM user;

Insert Statement

val insertUserStatement = InsertStatement(
    USER_TABLE,
    listOf(USER_ID_COL, USER_NAME_COL, USER_EMAIL_COL, USER_BALANCE_COL)
)
val statement: String = PostgresDialect.createInsertStatement(insertUserStatement)

This creates the following statement:

INSERT INTO user (user.id, user.name, user.email, user.balance) VALUES (?, ?, ?, ?);

Delete Statement

val deleteAllUsersStatement = DeleteStatement(
    USER_TABLE
)
val statement: String = PostgresDialect.createDeleteStatement(deleteAllUsersStatement)

This creates the following statement:

DELETE FROM user;

Update Statement

val updateUserStatement = UpdateStatement(
    USER_TABLE
    listOf(USER_BALANCE_COL)
)
val statement: String = PostgresDialect.createUpdateStatement(updateUserStatement)

This creates the following statement:

UPDATE user SET user.balance=?;