andrewoma / kwery Goto Github PK

    @Test
    fun `test allColumns returns a valid collection`() {
        val usersTable = UsersTable()
        assert(usersTable.allColumns.isNotEmpty())
    }

    private data class User(val id: String, val name: String, val age: Int)
    private class UsersTable : Table<User, UUID>("users") {
        val id by col(User::id, id = true)
        val userName by col(User::name)
        val age by col(User::age)

        override fun create(value: Value<User>): User {
            return User(value of id, value of userName, value of age)
        }

        override fun idColumns(id: UUID): Set<Pair<Column<User, *>, *>> {
            return setOf(this.id to id)
        }
    }

package com.company.thing

import com.github.andrewoma.kwery.core.DefaultSession
import com.github.andrewoma.kwery.core.Session
import com.github.andrewoma.kwery.core.dialect.HsqlDialect
import com.github.andrewoma.kwery.mapper.AbstractDao
import com.github.andrewoma.kwery.mapper.IdStrategy
import com.github.andrewoma.kwery.mapper.Table
import com.github.andrewoma.kwery.mapper.Value
import java.sql.DriverManager

class Thing(
        var id: Int,
        var name: String
)

object ThingTable: Table<Thing, Int>("thing") {
    val Id   by col(Thing::id, id = true)
    val Name by col(Thing::name)

    override fun create(value: Value<Thing>): Thing {
        return Thing(value of Id, value of Name)
    }
    override fun idColumns(id: Int) = setOf(Id of id)
}

class ThingDao(session: Session): AbstractDao<Thing, Int>(session, ThingTable, Thing::id)

fun main(args: Array<String>) {

    val conn = DriverManager.getConnection("jdbc:h2:mem:test")
    val session = DefaultSession(conn, HsqlDialect())

    session.update("""
        create table thing (
            id   integer auto_increment not null primary key,
            name character varying(255) not null,
        )
    """)

    val dao = ThingDao(session)
    dao.insert(Thing(0, "foo"), IdStrategy.Generated)
}

object Users : Table() {
    val id    = integer("id").primaryKey()
    val login = text("login")
}

object Scores : Table() {
    val score       = integer("score")
    val createdAt   = date("created_at")
    val userId      = integer("user_id") references Users.id
}

   override fun findAll(): List<Score> {
        return (Scores innerJoin Users).select { Scores.createdAt.greater(DateTime.now().minusDays(2)) }
                .orderBy(Scores.score, false)
                .limit(20)
                .map { r -> Score(r[Scores.score], r[Users.login]) }
}

class User(val id: Int, val login: String = "")

class PersistentScore(val id: Int, val user: User, score: Int) : Score(score, user.login)

object userConverter : SimpleConverter<User>(
        { row, c -> User(row.int(c)) },
        User::id
)

val tableConfig = TableConfiguration(
        defaults = standardDefaults + reifiedValue(User(0)),
        converters = standardConverters + reifiedConverter(userConverter)
)

object userTable: Table<User, Int>("users", tableConfig) {
    val Id          by col(User::id, id = true)
    val LoginVal    by col(User::login)

    override fun create(value: Value<User>): User =
        User(value of Id, value of LoginVal)

    override fun idColumns(id: Int) = setOf(Id of id)
}

object scoreTable : Table<PersistentScore, Int>("scores", tableConfig) {
    val Id          by col(PersistentScore::id, id = true)
    val ScoreVal    by col(PersistentScore::score, name = "score")
    val UserId      by col(PersistentScore::user)

    override fun create(value: Value<PersistentScore>): PersistentScore =
            PersistentScore(value of Id, value of UserId, value of ScoreVal)

    override fun idColumns(id: Int) = setOf(Id of id)
}
   fun findScores(): List<PersistentScore> {
        val name = "findScores"
        val sql = sql(name) { "select * from ${table.name} where created_at > :date order by score desc limit 10" }
        return session.select(sql, mapOf("date" to DateTime.now().minusDays(2).toString("yyyy-MM-dd")), options(name), table.rowMapper())
    }

/**
 * @author [David Maple](mailto:[email protected])
 */
data class Topic(
        val name: String,
        val dateCreated: LocalDateTime = LocalDateTime.now(),
        val dateModified: LocalDateTime = LocalDateTime.now(),
        val version: Long = 1,
        val id: Int = 0
)

/**
 * @author [David Maple](mailto:[email protected])
 */
object topicTable : Table<Topic, Int>("topic", TableConfiguration(
        standardDefaults + timeDefaults,
        standardConverters + timeConverters,
        camelToLowerUnderscore
)), VersionedWithLong {
    val Id by topicTable.col(Topic::id, id = true)
    val Name by topicTable.col(Topic::name)
    val DateCreated by topicTable.col(Topic::dateCreated)
    val DateModified by topicTable.col(Topic::dateModified)
    val Version by topicTable.col(Topic::version, version = true)

    override fun idColumns(id: Int) = setOf(Id of id)

    override fun create(value: Value<Topic>) = Topic(
            id = value of Id,
            dateCreated = value of DateCreated,
            dateModified = value of DateModified,
            name = value of Name,
            version = value of Version
    )
}

/**
 * @author [David Maple](mailto:[email protected])
 */
class TopicDao(session: Session) : AbstractDao<Topic, Int>(
        session,
        topicTable,
        { it.id },
        "bigint(20) unsigned",
        IdStrategy.Generated,
        0
) {
    init {
        addListener(object : Listener {
            override fun onEvent(session: Session, event: Event) {
                if (event is TransformingEvent && event.new is Topic) {
                    val entity = event.new as Topic
                    if (event is PreInsertEvent) {
                        event.transformed = entity.copy(
                                dateCreated = LocalDateTime.now(),
                                dateModified = LocalDateTime.now()
                        )
                    }
                    if (event is PreUpdateEvent && event.new is Topic) {
                        event.transformed = entity.copy(dateModified = LocalDateTime.now())
                    }
                }
            }
        })
    }
}

val name = "findPaginated"
val sql = sql(name) {
    """
        SELECT
            $columns
        FROM
            ${table.name}
        LIMIT :offset, :limit
    """
}
return session.select(
        sql,
        mapOf("offset" to offset, "limit" to limit),
        options(name),
        table.rowMapper()
);

    @Test
    fun `test adding more columns after table instance construction`() {
        val usersTable = UsersTable()
        val dataColumnsBeforeModification = usersTable.dataColumns

        @Suppress("UNCHECKED_CAST")
        val emailColumn = Column(
                User::email, null,
                standardConverters[String::class.java] as Converter<String?>,
                "email", false, false, true, false
        )

        usersTable.addColumn(emailColumn)
        val dataColumnsAfterModification = usersTable.dataColumns

        assert(dataColumnsBeforeModification != dataColumnsAfterModification)
    }


    private data class User(val id: String, val name: String, val age: Int) {
        val email: String? = null
    }


    private class UsersTable : Table<User, UUID>("users") {

        val id by col(User::id, id = true)
        val userName by col(User::name)
        val age by col(User::age)

        override fun create(value: Value<User>): User {
            return User(value of id, value of userName, value of age)
        }

        override fun idColumns(id: UUID): Set<Pair<Column<User, *>, *>> {
            return setOf(this.id to id)
        }
    }

import com.github.andrewoma.kwery.core.SessionFactory
import com.github.andrewoma.kwery.core.dialect.PostgresDialect
import org.postgresql.ds.PGSimpleDataSource
import java.io.File
import javax.sql.DataSource

fun main(args: Array<String>) {
    val ds: DataSource = PGSimpleDataSource().apply {
        url = "jdbc:postgresql://localhost:5432/user_db"
        user = "root"
        password = ""
    }
    val factory = SessionFactory(ds, PostgresDialect())

    val users = File("users.csv").readLines().map { mapOf("name" to it) }
    factory.use { 
        it.batchInsert("""
            INSERT INTO user(name)
            VALUES (:name)
            ON CONFLICT (name) DO NOTHING""", users, f = {}).sumBy { it.first } 
    }
    println("done")
}