andrewoma / kwery Goto Github PK
View Code? Open in Web Editor NEWKwery is an SQL library for Kotlin
License: MIT License
Kwery is an SQL library for Kotlin
License: MIT License
Do tables really need to be singletons and why?
What if, for example, Table has some dependency, and passes it to entity constructor in Table#create
?
While Table
instance is not initialized
, columns are getting added.
If any other thread sees a Table
instance after that and try to initialize it, writes into columns
made by other thread may not be visible.
Unfortunately, I don't know any easy solutions to address the issue.
This is my first object that utilizes auto increment in MySQL. I've set the DAO to utilize generated keys:
/**
* @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())
}
}
}
})
}
}
SQL looks like this:
CREATE TABLE `topic` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`date_created` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
`date_modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
`version` bigint(20) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;
As you can see I do have an id
column, yet when performing an INSERT via the DAO I get an unexpected SQLException. Logging shows an expected INSERT syntax:
insert into topic(date_created, date_modified, name, version)
values ('2016-05-22 11:07:59.876', '2016-05-22 11:07:59.876', 'Love/Romance', 1);
The stacktrace shows the following:
Failed to execute TopicDao.insert in 1.921 ms (761521.415 ms)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
Reason: Column 'id' not found.. TXN: 3
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1076)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2580)
at com.github.andrewoma.kwery.core.Row.int(Row.kt:47)
at com.github.andrewoma.kwery.mapper.intConverter$1.invoke(Converters.kt:80)
at com.github.andrewoma.kwery.mapper.intConverter$1.invoke(Converters.kt:80)
at com.github.andrewoma.kwery.mapper.Table$rowMapper$1$1.of(Table.kt:300)
at com.quote.topic.topicTable.create(TopicDao.kt:25)
Any idea what I need to change configuration wise to prevent this?
What about adding default order to DAO, so all select queries will be ordered?
Hi,
I am using UUID to generate IDs instead of mysql AUTO_INCREMENT ID, but kwery does not allow that.
override fun <K> insert(sql: String, parameters: Map<String, Any?>, options: StatementOptions, f: (Row) -> K): Pair<Int, K> {
return withPreparedStatement(sql, listOf(parameters), options.copy(useGeneratedKeys = true)) { statement, ps ->
bindParameters(parameters, statement)
val rowsAffected = ps.executeUpdate()
interceptor.executed(statement)
val rs = ps.generatedKeys
try {
require(rs.next()) { "No generated key received" }
val keys = f(Row(rs))
statement.copy(rowsCounts = listOf(rowsAffected)) to (rowsAffected to keys)
} finally {
rs.close()
}
}
}
require(rs.next()) { "No generated key received" }
keeps throwing the exception "No generated key received" after inserting new data with UUID.
With the release of Kotlin coroutine, we're able to write concurrently executed coroutines even within the same thread. So I am wondering whether kwery supports async inteface or not ?
Why is nf
a val
of functional type instead of being fun
?
Why is it protected
, but not open
?
What's the purpose of this property?
When using a list parameter with any parameters following it, it gets translated into an incorrect parameter list that throws an exception under PostgreSQL.
For example:
session.update( "UPDATE sometable SET array_column = ARRAY [:arrayValues] WHERE id = :id", mapOf("arrayValues" to listOf(1), "id" to "2") )
This generates a length 9 parameter list. The first parameter will be "1", the second parameter will be "2", the 9th parameter will be null and thus trigger PostgreSQL to throw org.postgresql.util.PSQLException: No value specified for parameter 9. at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:257).
(Also, why are parameter slots for collections rounded up to a power of 2?)
@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)
}
}
@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)
}
}
Surprisingly, there's no count
method/property in Dao interface. I think it should be.
I wanted to model simple domain with 1-N relationship. I have this query working in core
:
select * from scores s join users u where s.created_at > '${date.toString("yyyy-MM-dd")}' order by s.score desc limit 20
But don't know how to model it with mapper
. With Exposed
it's as simple as this:
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]) }
}
I got something like this but, obviously, login
is set to empty string:
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())
}
Any help?
Are there any plans for a 0.18 release?
There are a couple of bugfixes on master missing from version 0.17.. It would have been nice to see those fixes released soon ๐
Is there any possibility to use a colon ':' character in a query? It seems to be always used as a query parameter.
For instance there is no way to use a string 'HH:mm' or PostgreSQL casting '3'::int.
AbstractDao#findById
executes such SQL for me:
select id, urlPathComponent, metaTitle, metaDescription, metaKeywords, linkText, h1, description, sortIndex, lastModified
from whatever
where id = :id
which is incorrect for PostgreSQL dialect: it requires camelCase column names to be in quotes, at least like this:
select id, "urlPathComponent", "metaTitle", "metaDescription", "metaKeywords", "linkText", h1, description, "sortIndex", "lastModified"
from whatever
where id = :id
Looks like there's an insidious bug where Kotlin is interpreting a field containing a String with a dollar sign ($) as an actual token for replacement in bind parameters:
Exception in thread "main" java.lang.IndexOutOfBoundsException: No group 5
at java.util.regex.Matcher.start(Matcher.java:375)
at java.util.regex.Matcher.appendReplacement(Matcher.java:880)
at com.github.andrewoma.kwery.core.DefaultSession.bindParameters(DefaultSession.kt:219)
Filling a String with any text containing '$' and performing an insert/update should recreate the issue.
A quick study seems to indicate that we could fix this in MysqlDialect::bind().
I'm always getting this exception when passing IdStrategy.Generated
to insert
.
Exception in thread "main" org.h2.jdbc.JdbcSQLException: Column "id" not found [42122-196]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.jdbc.JdbcResultSet.getColumnIndex(JdbcResultSet.java:3180)
at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3246)
at org.h2.jdbc.JdbcResultSet.getInt(JdbcResultSet.java:350)
at com.github.andrewoma.kwery.core.Row.int(Row.kt:47)
at com.github.andrewoma.kwery.mapper.intConverter$1.invoke(Converters.kt:80)
at com.github.andrewoma.kwery.mapper.intConverter$1.invoke(Converters.kt:80)
at com.github.andrewoma.kwery.mapper.Table$rowMapper$1$1.of(Table.kt:206)
at com.company.thing.ThingTable.create(EventServer.kt:28)
at com.company.thing.ThingTable.create(EventServer.kt:23)
at com.github.andrewoma.kwery.mapper.Table$rowMapper$1.invoke(Table.kt:204)
at com.github.andrewoma.kwery.mapper.Table$rowMapper$1.invoke(Table.kt:39)
at com.github.andrewoma.kwery.mapper.AbstractDao$insert$1$1.invoke(AbstractDao.kt:289)
at com.github.andrewoma.kwery.mapper.AbstractDao$insert$1$1.invoke(AbstractDao.kt:32)
at com.github.andrewoma.kwery.core.DefaultSession$insert$1.invoke(DefaultSession.kt:142)
at com.github.andrewoma.kwery.core.DefaultSession$insert$1.invoke(DefaultSession.kt:46)
at com.github.andrewoma.kwery.core.DefaultSession.withPreparedStatement(DefaultSession.kt:252)
at com.github.andrewoma.kwery.core.DefaultSession.insert(DefaultSession.kt:135)
at com.github.andrewoma.kwery.mapper.AbstractDao$insert$1.invoke(AbstractDao.kt:288)
at com.github.andrewoma.kwery.mapper.AbstractDao.withTransaction(AbstractDao.kt:100)
at com.github.andrewoma.kwery.mapper.AbstractDao.insert(AbstractDao.kt:277)
at com.company.thing.main(Main.kt:48)
Process finished with exit code 1
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)
}
Why Dialects are represented by open
classes? Does implementation inheritance really makes sense here? If no, then Dialects encapsulate no state and thus can be converted to 'object declarations' (singletons).
Hi, Andrew.
Thank you for your awesome job!
I've been stuck a little with generated keys using core "insert" function with MySQL, because 'GENERATED_KEY' should be specified instead of key column name.
I.e.
val (count, key) =
session.insert("insert blablabla", mapOf("field" to "value" ...)) {
it.long("GENERATED_KEY")
}
Can you add a small remark about this into documentation?
Thank you. Sorry for my English.
Hi Andrew,
Do you ever use pagination in your queries using offset, limit? I try to avoid large offsets but I continue to find examples where it's helpful. Currently I'm implementing it within a DAO with something like this:
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()
);
I'm wondering if this would be something worthy of adding to AbstractDao. Your thoughts?
Hi
you mentioned the kwery don't use Proxies or reflection ... but you use Proxies in transactional project.
There isn't a better solution to implement this feature ?
Thanks.
I noticed that I needed to call an Oracle Stored Procedure (would be nice to have a dialect for Oracle) and this framework had no support for doing it. I had to resort to obtaining the native connection and writing java style code to make it happen.
Would be really nice if you added support for CallableStatements so that they work roughly the same way as your select/update statements do. I also didn't see it on the roadmap either.
Consider following scenario: I want to batch insert raws into my postgres table. But some raws already exist. So I use UPSERT.
If I do this in kwery, I get exception: java.lang.IllegalArgumentException: Expected 10 keys but received 8.
DDL
CREATE TABLE user (
name TEXT NOT NULL PRIMARY KEY
);
Script:
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")
}
Exception stack trace:
Exception in thread "main" java.lang.IllegalArgumentException: Expected 100 keys but received 0
at com.github.andrewoma.kwery.core.DefaultSession$batchInsert$2.invoke(DefaultSession.kt:117)
at com.github.andrewoma.kwery.core.DefaultSession$batchInsert$2.invoke(DefaultSession.kt:46)
at com.github.andrewoma.kwery.core.DefaultSession.withPreparedStatement(DefaultSession.kt:252)
at com.github.andrewoma.kwery.core.DefaultSession.batchInsert(DefaultSession.kt:103)
at com.github.andrewoma.kwery.core.Session$DefaultImpls.batchInsert$default(Session.kt:86)
Kwery version: 0.17
I believe root cause is that kwery expects sql server to send generated key for each entry from parameter list (which is no longer the case in my scenario).
DefaultSession.kt:117:
require(keys.size == parametersList.size) { "Expected ${parametersList.size} keys but received ${keys.size}" }
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.