This message was deleted.

Hey @tapac just wanted to give you a shoutout for all the hard work you do on this library, it’s genuinely a really nice way to interact with SQL servers and I like it a lot. Kudos for maintaining the library and also responding quickly to all our questions on this channel!

Hey @tapac, I was debugging an issue with my code and finally found that the problem is that the

limit

function does not generate sql when the limit size is 0, as the function implementation is

open fun queryLimit(size: Int, offset: Int, alreadyOrdered: Boolean) = buildString {
    if (size > 0) {
        append("LIMIT $size")
        if (offset > 0) {
            append(" OFFSET $offset")
        }
    }
}

Shouldn't be

if (size >= 0)

instead? My use case is that the limit size is generated dynamically, and when it is 0, the sql query results should be empty.

Is there an efficient way to update a model field in exposed only in the case that the value for the column in the DB is different than what I have in a variable?

In jetbrains.exposed.sql.Transaction.kt version 0.3, there was a

create(vararg tables: Table){}

method. I just updated to exposed version 0.22. What happened to this method!? 😞

Hello, Is there a way to get use store and retrieve

data

classes directly with exposed, something like:

object Users : Table() {}

// Get list of users
val usersList: List<User> = Users.select { Users.city.eq("a city") }

//Insert users 

val allUsers: List<User> = //list of users 
val aUser: User = //User instance

Users.insertAll(allUsers) // Inserts everything in to the user table
Users.insert(aUser) // Inserts a single user in to the table.

i.e Something that automatically maps/converts rows to data classes when reading stuff, and vice versa when inserting.

i am trying to put datetimes in with exposed.. .. my local timezone is

Europe/Berlin

so i tried converting them like so

ISODateTimeFormat.dateTimeParser().parseDateTime(dateString).withZone(DateTimeZone.UTC)

and logging shows me the field should be

2020-03-18T16:17:00.000Z

but when i look into the database i see

2020-03-18 17:17:00

this looks almost as if exposed converts to the local timezone and then discards the timezone info (or

DATETIME

has no timezone info?) how can i make sure i save stuff as

UTC

always ?

This message was deleted.

I need some help with a project. I have a table like this

object Parks : Table("p_parks") {
    val id = varchar("p_id", 45)
    val longitude = double("longitude")
    val latitude = double("latitude")

    override val primaryKey = PrimaryKey(id)
}

and im trying to select them by distance to a coordinate

c

with latitude

lat

and longitude

lon

, but i dont want to filter them by smth like "less than 10km away" but i want the 10 nearest. Is there any way to do this without selecting all?

and my function to get the distance is

fun distanceBetween(cord1: Coordinates, cord2: Coordinates): Double {
    val (lat1deg, lon1deg) = cord1
    val (lat2deg, lon2deg) = cord2

    val r = 6371e3; // metres
    val lat1 = lat1deg.toRadians()
    val lat2 = lat2deg.toRadians()
    val deltaLat = (lat2deg - lat1deg).toRadians()
    val deltaLon = (lon2deg - lon1deg).toRadians()

    val a = sin(deltaLat / 2).pow(2) +
            cos(lat1) * cos(lat2) * sin(deltaLon / 2).pow(2)
    val c = 2 * atan2(sqrt(a), sqrt(1 - a))

    return r * c;
}

@DownloadPizza if it works for you, here is how to calculate distance for a row using 'haversine' formula (exposed 0.21.1 / PostgreSQL):

object LocationTable: IntIdTable("LOCATION") {
...
    val latitude = double("latitude")
    val longitude = double("longitude")

    private val doubleT by lazyOf(DoubleColumnType())

    fun Expression<Double>.limitToOne() = CustomFunction<Double>("least", doubleT, this, 1.0.lit())
    fun Expression<Double>.radians() = CustomFunction<Double>("radians", doubleT, this)
    fun Expression<Double>.sin() = CustomFunction<Double>("sin", doubleT, this)
    fun Expression<Double>.cos() = CustomFunction<Double>("cos", doubleT, this)
    fun Expression<Double>.acos() = CustomFunction<Double>("acos", doubleT, this)

    fun Double.lit() = LiteralOp(doubleT, this)
    infix fun Expression<Double>.times(v: Expression<Double>) = TimesOp(this, v, doubleT)
    infix fun Expression<Double>.times(v: Double) = TimesOp(this, v.lit(), doubleT)
    infix fun Expression<Double>.plus(v: Expression<Double>) = PlusOp(this, v, doubleT)
    infix fun Column<Double>.minus(v: Double) = BracketOp(MinusOp(this, LiteralOp(doubleT, v), doubleT))

    fun distance(coords: Coordinates) =
        (latitude.radians().sin() times Math.sin(Math.toRadians(coords.latitude)) plus
         latitude.radians().cos() times Math.cos(Math.toRadians(coords.latitude)) times (longitude minus coords.longitude).radians().cos()).limitToOne().acos() times Coordinates.TO_METRIC
}

where

data class Coordinates(val latitude: Double, val longitude: Double) {
    companion object {
        const val TO_METRIC = 1852.0 * 60.0 * 180.0 / Math.PI    
    }
}

and this function can be used as following:

query.andWhere { LocationTable.distance(basePoint) lessEq distanceInMeters }

DISCLAIMER: 1. I'm not sure that's 100% right (or idiomatic) way to do that with the exposed (still newcomer for the exposed) 2. I'm pretty sure that's kind of filtering 'closest/nearest' objects is very inefficient (indexes cannot be used), so let consider to use postgis extension or optimizing search in someway e.g. with bounding boxes.

Hi all, I am trying to figure out how Isolation Level "SERIALIZABLE" works. Here's what I want to achieve:

withContext(<http://Dispatchers.IO|Dispatchers.IO>) {
            println(TransactionManager.manager.defaultIsolationLevel) // prints 8 = SERIALIZABLE
            transaction {
                val countBefore = MyDao.find(Op.TRUE).count()
                println(countBefore)
                println("SLEEP")
                Thread.sleep(10000)
                println("UPDATE")
                val countUpdate = MyDao.find(Op.TRUE).count()
                println(countUpdate) // EXPECTING TO BE SAME AS countBefore
            }
        }

Now I have a separate query that inserts new rows into MyDao, and I trigger it during the 10 seconds of sleep of the first transaction:

withContext(<http://Dispatchers.IO|Dispatchers.IO>) {
            println(TransactionManager.manager.defaultIsolationLevel) // prints 8 = SERIALIZABLE
            transaction {
                MyDao.new { ... }
            }
        }

During setup, I have:

TransactionManager.manager.defaultIsolationLevel = Connection.TRANSACTION_SERIALIZABLE

What I expected is that the table behind MyDao gets locked for the time the first transaction is still running. But this is not the case and the first transaction prints 2 different counts as a result ...

Hey to all, I am trying to build an inheritance model with Exposed I have 2 classes inheriting from one. The strategy should be one table per class. Has anyone ever implemented this before? Is this possible at the moment?

Hello 👋 Is there an interest in adding some convenience functions to the library for enums support? For eg. postgres implementation could look like :

class PGEnum<T : Enum<T>>(enumTypeName: String, enumValue: T?) : PGobject() {
    init {
        value = enumValue?.name
        type = enumTypeName
    }
}

inline fun <reified T : Enum<T>> Table.postgresEnumeration(
    columnName: String,
    postgresEnumName: String
) = customEnumeration(columnName, postgresEnumName,
    { value -> enumValueOf<T>(value as String) }, { PGEnum(postgresEnumName, it) })

So that column declaration looks like :

internal object MyTable : Table("my_table_name") {
    val enumColumnA = postgresEnumeration<MyEnum>("column_name", "postgres_enum_name")
}

Which feature of

Exposed

is killer feature and you will not switch to another library if this feature will not be present in that library?

Is there an example using Scheme?

I can't figure out how to insert records, docs seem old. When I try to assign the values in the insert it says val cannot be reassigned, like its ignoring the insert part. Any help?

transaction {
    Categories.insert {

    }
}

object Categories : Table() {
    val category = varchar("category", 255)
    val description = varchar("description", 255)
    override val primaryKey = PrimaryKey(category, name = "PK_Category_category")
}

I'm still confused, anyone know how to set this up correctly for DB instantiation? I can't get these entity objects right with Id Tables and such...

object Users : IdTable<String>() {
    val username = varchar("username",255)
    val email = varchar("email",255)
    val created = datetime("created").nullable()
    val passwordHash = varchar("passwordHash",255).nullable()
    val passwordSalt = varchar("passwordSalt",255).nullable()
    val avatar = varchar("avatar",255).nullable()          //path to users picture
    val type = varchar("type",45).nullable()               //normal or primium
    override val primaryKey = PrimaryKey(username, name = "PK_User_username")
    override val id: Column<EntityID<String>>
    get() = TODO("Not yet implemented")
}
class User(username: Entity<String>) : EntityClass<String, Users>(){

}
val defaultUsers = listOf<String>()

Not sure why intelliJ is automatically wanting to add the last two lines to the Users object either.... I thought getters and setters were predefined in exposed?

It took 48 hours to get this right, man it was tough but beautiful when it works... just wish the docs where updated:

package com.newsworthy.db.tables

import org.jetbrains.exposed.dao.Entity
import org.jetbrains.exposed.dao.EntityClass
import org.jetbrains.exposed.dao.id.EntityID
import org.jetbrains.exposed.dao.id.IdTable
import org.jetbrains.exposed.sql.Column
import org.jetbrains.exposed.sql.Table
import org.joda.time.DateTime


val defaultCategories = mutableMapOf<EntityID<String>, String>(
    EntityID("general", Categories) to "Useless and frowned upon",
    EntityID("science", Categories) to "",
    EntityID("technology", Categories) to "",
    EntityID("world", Categories) to "",
    EntityID("local", Categories) to "",
    EntityID("top", Categories) to ""
)

class Category(category: EntityID<String>) : Entity<String>(category){
    companion object : EntityClass<String, Category>(Categories)
    var category by Categories.category
    var description by Categories.description
}

object Categories : IdTable<String>("Categories") {
        val category = varchar("category", 255).default("general").entityId().default(EntityID("unknown", Categories))
        val description = varchar("description", 255).nullable()
        override val primaryKey = PrimaryKey(category, name = "PK_Category_category")
        override val id: Column<EntityID<String>> = category
}

Doing a composit pk is documented, but how do you do a composite id?

Hey, anyone having this problem?

Could anybody please advise a guide how to test my app which uses postgres DB? I think mocking Exposed is quite tricky, so maybe I should just somehow create a DB for every test case. but anyway I don't know how. Of course I can declare something like "before every test start docker" and "after every test stop docker" via

ProcessBuilder

, but it seems too rough... Is there an in-memory DB that I can create in JVM and with which Exposed can work?

Is there a way to collapse the Report.new down so this takes up less lines... this.url = url and 10 more seems redundant and wasteful, is there some kind of map function?

if(notFound) {
    val outlet = transaction { Outlet.find { outlet eq outletEntityID }.limit(1).toList()[0] }
    val category = transaction { Category.find { Categories.category eq outletData[Outlets.defaultCategory] }.limit(1).toList()[0] }
    val title = it.title
    val summary = it.description.value.take(255)
    val content = it.contents.joinToString()
    val discovered = DateTime.now(DateTimeZone.UTC)
    val authored = DateTimeFormat.forPattern("EEE MMM dd HH:mm:ss zzz yyyy").parseDateTime("${it.publishedDate}")
    val thumbnailUrl = getReportFeatureImage(it) ?: outletData[defaultReportThumbnailUrl]

    transaction {
        Report.new {
            this.url = url
            this.outlet =outlet
            this.category = category
            this.title = title
            this.summary = summary
            this.content = content
            this.discovered = discovered
            this.authored = authored
            this.thumbnailUrl = thumbnailUrl
        }
    }
    counts.added += 1
} else {
    counts.existing += 1
}

It seems latest exposed only works with exactly h2 version

1.4.199

. Would it be possible for Exposed to depend on that version somehow instead of having to manually keeping it in sync ?

I stumpled upon the fact that the new java-time module doesnt use a TIMESTAMP column for Instants on MySQL. There are a couple of issues about it also. What are the odds of that changing ?

I'm thinking about using exposed with MemSQL which supports mariadb driver. I cannot figure out how to create a table because it needs these SHARD statement, how do I add that?

I mean a way / guidance to use Apache Derby with Exposed.

Does anyone have an example usage of the savepoints for rollbacks? https://github.com/JetBrains/Exposed/issues/320

Hi @tapac, I was reading this issue, Is an awesome idea. https://github.com/JetBrains/Exposed/issues/24#issuecomment-566725685 I think that this could be done using a extension/plugin to Kotlinx.serialization compiler plugin(does not know if they support this type of extension) ? I'm currently doing a proof of concept that I can use Kotlinx.serialization to generate MySQL queries using the serial descriptor. https://github.com/DevSrSouza/Kotlinx.serialization-sql/blob/master/src/jvmTest/kotlin/Test.kt