org.ufoss.kotysa.SqlClient.kt Maven / Gradle / Ivy

Go to download
/*
 * This is free and unencumbered software released into the public domain, following 
 */

package org.ufoss.kotysa

import org.ufoss.kotysa.columns.TsvectorColumn
import org.ufoss.kotysa.postgresql.Tsquery
import java.math.BigDecimal
import java.util.stream.Stream

/**
 * Sql Client, to be used with a blocking driver
 */
public sealed interface SqlClient {

    public infix fun  insert(row: T)
    public fun  insert(vararg rows: T)

    public infix fun  insertAndReturn(row: T): T
    public fun  insertAndReturn(vararg rows: T): List

    public infix fun  createTable(table: Table)
    public infix fun  createTableIfNotExists(table: Table)

    public infix fun  deleteFrom(table: Table): SqlClientDeleteOrUpdate.FirstDeleteOrUpdate
    public infix fun  deleteAllFrom(table: Table): Int = deleteFrom(table).execute()

    public infix fun  update(table: Table): SqlClientDeleteOrUpdate.Update

    public infix fun  select(column: Column): SqlClientSelect.FirstSelect
    public infix fun  select(table: Table): SqlClientSelect.FirstSelect
    public infix fun  selectAndBuild(dsl: (ValueProvider) -> T): SqlClientSelect.Fromable
    public fun selectCount(): SqlClientSelect.Fromable
    public infix fun  selectCount(column: Column<*, T>): SqlClientSelect.FirstSelect
    public infix fun  selectDistinct(column: Column): SqlClientSelect.FirstSelect
    public infix fun  selectMin(column: MinMaxColumn): SqlClientSelect.FirstSelect
    public infix fun  selectMax(column: MinMaxColumn): SqlClientSelect.FirstSelect
    public infix fun  selectAvg(column: NumericColumn): SqlClientSelect.FirstSelect
    public infix fun  selectSum(column: IntColumn): SqlClientSelect.FirstSelect
    public infix fun  select(dsl: SqlClientSubQuery.Scope.() -> SqlClientSubQuery.Return)
            : SqlClientSelect.FirstSelect

    public infix fun  selectCaseWhenExists(
        dsl: SqlClientSubQuery.SingleScope.() -> SqlClientSubQuery.Return
    ): SqlClientSelect.SelectCaseWhenExistsFirst

    // Postgresql specific
    public fun selectTsRankCd(tsvectorColumn: TsvectorColumn<*>, tsquery: Tsquery): SqlClientSelect.FirstSelect =
        throw UnsupportedOperationException("Only PostgreSQL supports selectTsRankCd")

    public infix fun  selectStarFrom(
        dsl: SqlClientSubQuery.Scope.() -> SqlClientSubQuery.Return
    ): SqlClientSelect.From

    public infix fun  selectFrom(table: Table): SqlClientSelect.FromTable =
        select(table).from(table)

    public infix fun  selectCountFrom(table: Table): SqlClientSelect.FromTable =
        selectCount().from(table)

    public infix fun  selectAllFrom(table: Table): List = selectFrom(table).fetchAll()
    public infix fun  selectCountAllFrom(table: Table): Long = selectCountFrom(table).fetchOne()!!
}

public interface H2SqlClient : SqlClient
public interface MysqlSqlClient : SqlClient
public interface PostgresqlSqlClient : SqlClient, SqlClientQuery.ToTsquery
public interface MssqlSqlClient : SqlClient
public interface MariadbSqlClient : SqlClient
public interface SqLiteSqlClient : SqlClient

public class SqlClientSelect private constructor() : SqlClientQuery() {

    public interface Selectable : SelectableFull {
        override fun  select(column: Column<*, T>): FirstSelect
        override fun  select(table: Table): FirstSelect
        override fun  selectAndBuild(dsl: (ValueProvider) -> T): Fromable
        override fun  selectCount(column: Column<*, T>?): FirstSelect
        override fun  selectDistinct(column: Column<*, T>): FirstSelect
        override fun  selectMin(column: MinMaxColumn<*, T>): FirstSelect
        override fun  selectMax(column: MinMaxColumn<*, T>): FirstSelect
        override fun  selectAvg(column: NumericColumn<*, T>): FirstSelect
        override fun selectSum(column: IntColumn<*>): FirstSelect
        override fun  select(dsl: SqlClientSubQuery.Scope.() -> SqlClientSubQuery.Return): FirstSelect
        override fun  selectCaseWhenExists(
            dsl: SqlClientSubQuery.SingleScope.() -> SqlClientSubQuery.Return
        ): SelectCaseWhenExistsFirst

        override fun  selectStarFromSubQuery(
            dsl: SqlClientSubQuery.Scope.() -> SqlClientSubQuery.Return
        ): From

        // Postgresql specific
        override fun selectTsRankCd(tsvectorColumn: TsvectorColumn<*>, tsquery: Tsquery): FirstSelect
    }

    public interface SelectCaseWhenExistsFirst : SelectCaseWhenExists {
        override fun  then(value: U): SelectCaseWhenExistsFirstPart2
    }

    public interface SelectCaseWhenExistsFirstPart2 : SelectCaseWhenExistsPart2 {
        override fun `else`(value: U): FirstSelect
    }

    public interface Fromable : SqlClientQuery.Fromable, SqlClientQuery.Select {
        override fun  from(table: Table): FromTable
        override fun  from(dsl: SqlClientSubQuery.Scope.() -> SqlClientSubQuery.Return): From

        // Postgresql specific
        override fun from(tsquery: Tsquery): From
    }

    public interface FirstSelect : Fromable, SqlClientQuery.Select, Andable {
        override fun  and(column: Column<*, U>): SecondSelect
        override fun  and(table: Table): SecondSelect
        override fun  andCount(column: Column<*, U>): SecondSelect
        override fun  andDistinct(column: Column<*, U>): SecondSelect
        override fun  andMin(column: MinMaxColumn<*, U>): SecondSelect
        override fun  andMax(column: MinMaxColumn<*, U>): SecondSelect
        override fun  andAvg(column: NumericColumn<*, U>): SecondSelect
        override fun andSum(column: IntColumn<*>): SecondSelect
        override fun  and(dsl: SqlClientSubQuery.Scope.() -> SqlClientSubQuery.Return)
                : SecondSelect

        override fun  andCaseWhenExists(
            dsl: SqlClientSubQuery.SingleScope.() -> SqlClientSubQuery.Return
        ): AndCaseWhenExistsSecond

        // Postgresql specific
        override fun andTsRankCd(tsvectorColumn: TsvectorColumn<*>, tsquery: Tsquery): SecondSelect

        override fun `as`(alias: String): FirstSelect
    }

    public interface AndCaseWhenExistsSecond : AndCaseWhenExists {
        override fun  then(value: V): AndCaseWhenExistsSecondPart2
    }

    public interface AndCaseWhenExistsSecondPart2 : AndCaseWhenExistsPart2 {
        override fun `else`(value: V): SecondSelect
    }

    public interface SecondSelect : Fromable>, Andable {
        override fun  and(column: Column<*, V>): ThirdSelect
        override fun  and(table: Table): ThirdSelect
        override fun  andCount(column: Column<*, V>): ThirdSelect
        override fun  andDistinct(column: Column<*, V>): ThirdSelect
        override fun  andMin(column: MinMaxColumn<*, V>): ThirdSelect
        override fun  andMax(column: MinMaxColumn<*, V>): ThirdSelect
        override fun  andAvg(column: NumericColumn<*, V>): ThirdSelect
        override fun andSum(column: IntColumn<*>): ThirdSelect
        override fun  and(dsl: SqlClientSubQuery.Scope.() -> SqlClientSubQuery.Return)
                : ThirdSelect

        override fun  andCaseWhenExists(dsl: SqlClientSubQuery.SingleScope.() -> SqlClientSubQuery.Return)
                : AndCaseWhenExistsThird

        // Postgresql specific
        override fun andTsRankCd(tsvectorColumn: TsvectorColumn<*>, tsquery: Tsquery): ThirdSelect

        override fun `as`(alias: String): SecondSelect
    }

    public interface AndCaseWhenExistsThird : AndCaseWhenExists {
        override fun  then(value: W): AndCaseWhenExistsThirdPart2
    }

    public interface AndCaseWhenExistsThirdPart2 : AndCaseWhenExistsPart2 {
        override fun `else`(value: W): ThirdSelect
    }

    public interface ThirdSelect : Fromable>, Andable {
        override fun  and(column: Column<*, W>): Select
        override fun  and(table: Table): Select
        override fun  andCount(column: Column<*, W>): Select
        override fun  andDistinct(column: Column<*, W>): Select
        override fun  andMin(column: MinMaxColumn<*, W>): Select
        override fun  andMax(column: MinMaxColumn<*, W>): Select
        override fun  andAvg(column: NumericColumn<*, W>): Select
        override fun andSum(column: IntColumn<*>): Select
        override fun  and(dsl: SqlClientSubQuery.Scope.() -> SqlClientSubQuery.Return): Select
        override fun  andCaseWhenExists(
            dsl: SqlClientSubQuery.SingleScope.() -> SqlClientSubQuery.Return
        ): AndCaseWhenExistsLast

        // Postgresql specific
        override fun andTsRankCd(tsvectorColumn: TsvectorColumn<*>, tsquery: Tsquery): Select

        override fun `as`(alias: String): ThirdSelect
    }

    public interface AndCaseWhenExistsLast : AndCaseWhenExists {
        override fun  then(value: U): AndCaseWhenExistsLastPart2
    }

    public interface AndCaseWhenExistsLastPart2 : AndCaseWhenExistsPart2 {
        override fun `else`(value: U): Select
    }

    public interface Select : Fromable>, Andable {
        override fun  and(column: Column<*, T>): Select
        override fun  and(table: Table): Select
        override fun  andCount(column: Column<*, T>): Select
        override fun  andDistinct(column: Column<*, T>): Select
        override fun  andMin(column: MinMaxColumn<*, T>): Select
        override fun  andMax(column: MinMaxColumn<*, T>): Select
        override fun  andAvg(column: NumericColumn<*, T>): Select
        override fun andSum(column: IntColumn<*>): Select
        override fun  and(dsl: SqlClientSubQuery.Scope.() -> SqlClientSubQuery.Return): Select
        override fun  andCaseWhenExists(
            dsl: SqlClientSubQuery.SingleScope.() -> SqlClientSubQuery.Return
        ): AndCaseWhenExistsLast

        // Postgresql specific
        override fun andTsRankCd(tsvectorColumn: TsvectorColumn<*>, tsquery: Tsquery): Select

        override fun `as`(alias: String): Select
    }

    public interface From : SqlClientQuery.From>, Whereable>, GroupBy, OrderBy,
        LimitOffset, Return {
        override fun  and(table: Table): FromTable
        override fun  and(dsl: SqlClientSubQuery.Scope.() -> SqlClientSubQuery.Return): From

        // Postgresql specific
        override fun and(tsquery: Tsquery): From
    }

    public interface FromTable : SqlClientQuery.FromTable>,
        SqlClientQuery.From>, From, Whereable>, GroupBy, OrderBy, LimitOffset, Return {
        override fun `as`(alias: String): FromTable
    }

    public interface Where : SqlClientQuery.Where>, OrderBy, GroupBy, LimitOffset,
        Return

    public interface GroupBy : SqlClientQuery.GroupBy>

    public interface GroupByPart2 : SqlClientQuery.GroupByPart2>, OrderBy, LimitOffset,
        Return

    public interface OrderBy : SqlClientQuery.OrderBy>

    public interface OrderByPart2 : SqlClientQuery.OrderByPart2>, GroupBy, LimitOffset,
        Return

    public interface LimitOffset : SqlClientQuery.LimitOffset>, Return

    public interface Return {
        /**
         * This query returns one result
         *
         * @throws NoResultException if no results
         * @throws NonUniqueResultException if more than one result
         */
        public fun fetchOne(): T?

        /**
         * This query returns one result, or null if no results
         *
         * @throws NonUniqueResultException if more than one result
         */
        public fun fetchOneOrNull(): T?

        /**
         * This query returns the first result
         *
         * @throws NoResultException if no results
         */
        public fun fetchFirst(): T?

        /**
         * This query returns the first result, or null if no results
         */
        public fun fetchFirstOrNull(): T?

        /**
         * This query returns several results as [List], can be empty if no results
         */
        public fun fetchAll(): List

        /**
         * This query returns several results as [Stream], can be empty if no results
         */
        public fun fetchAllStream(): Stream = fetchAll().stream()
    }
}


public class SqlClientDeleteOrUpdate private constructor() : SqlClientQuery() {
    public interface FirstDeleteOrUpdate : FromTable>, Whereable>, Return

    public interface DeleteOrUpdate : FromTable>, Whereable>, Return

    public interface Update : FirstDeleteOrUpdate, SqlClientQuery.Update, UpdateInt>

    public interface UpdateInt : FirstDeleteOrUpdate, SqlClientQuery.UpdateInt, UpdateInt>

    public interface Where : SqlClientQuery.Where>, Return

    public interface Return {
        /**
         * Execute delete or update and return the number of updated or deleted rows
         */
        public fun execute(): Int
    }
}