Kotlin FRM 0.1.0 Release
Issues
- Prepared Queries Add support for prepared queries.
- Query Compilation Compile queries and build out data classes.
API Changes
FRM
To ease and expedite the auto complete of FRM function calls on a query. They have been moved into a DSL class builder. In short once you start a query only valid options will be displayed to you. Only instaniation has changed, the calls after starting a query intent remain the same.
lazyInsert(table)
lazySelect(table)
lazySelectJoin(table)
lazyUpdate(table)
lazyDelete(table)
DSL
The DSL has added an extension method to start a query. Removing the need for specifying explicit types.
T.query { } // Where T is a descendant of ITable<IDatabase,ADatabaseRecord>
Users.query { }
Prepared Queries
Prepared queries allow for the data base to cache a query. Inserting variable in several key portions of the query. A common use case may be getting a user by ID.
select * from users where user_id = ?
This query would be cached on the data base server, increasing performance. On calls to this query you need to only pass the user ID.
Lazy Fields
With the introduction of prepared queries a new field was added. These are lazy fields, meaning the value will be provided later. This was done to ease readbility of the code. The lazy fields are generated during schema reflection. No work is needed on the end user. They contain the column
name, type, and other information.
Type Verification
I could not think of an optimal way that impeded usability to provide compiler time checking of prepared queries. That is to say in the above query we except an int
. How do we verify that what is passed is an int
or it wouldn't compile. This can be mitigated via query compilation.
For now though I have settled on run time checking. The prepared queries are passed via varargs
. It will go through each of the arguments and corresponding position in lazy conditions
, verifying that the types match.
Sample Query
DSL
sqlPreparedQuery<ExampleIDatabase, UsersRecord, Users> {
select() from Users where { Users.firstName equal Users.lazyFirstName }
}
FRM
val delayedUserProfiles = lazyPreparedSelectJoin(Users)
.withColumns(Users.id, Users.firstName, Users.lastName, UserProfile.bio)
.join(UserProfile) {
UserProfile.id equal Users.lazyId
}
.filter {
Users.firstName iLike Users.lazyLastName
}
// Passing Arguments
delayedUserProfiles.map(executor, 1, "%ja%")
The following have been added:
//FRM
lazyPreparedSelect(table)
lazyPreparedSelectJoin(table)
lazyPreparedUpdate(table)
lazyPreparedDelete(table)
lazyPreparedInsert(table)
//DSL
sqlPreparedQuery<Database, Record,Table> {
query
}
<T:ITable<IDatabase,ADatabaseRecord> T.preparedQuery
Users.preparedQuery {
query
}
Query Compilation [Alpha]
This is more for demo of where we're going. This will hopefully be another gradle plugin. But if not you can run the query compilation via the gradle application plugin.
What is it?
Given a query, just the query not a result. This will build out a constant object of the query. Containing:
- The SQL string built once and verified to be accepted by the database.
- A version
- Executor functions non blocking / async, and blocking
In addition it will build out additional data classes. The primary use case is with items like json_agg
or joins. Where you have to usually build out a seperate POJO / data class to cast the data into. This will handle serialization / deserialization, even to custom data classes.
Example
What we want to compile.
// Query
val testQuery = lazySelectJoin(Users)
.withColumns(Users.id, Users.firstName, Users.lastName,
jsonAgg(name = "orders") {
jsonObj {
"orderPlaced" to UserOrders.orderPlaced
"inventory" to jsonObj {
"id" to Inventory.id
"name" to Inventory.name
"price" to Inventory.price
}
}
}
)
.join(UserOrders) { UserOrders.userId equal Users.id }
.join(Inventory) { Inventory.id equal UserOrders.inventoryId }
.groupBy(Users.id, Users.firstName, Users.lastName).query.lockQuery(PostgresSQLBuilder())
// Compilation
val config = CompilerConfig(
"design.animus.kotlin.frm.sql.query.compiler.test",
"GetUserOrders",
1,
File("/home/sobrien/dev/kotlin-frm/sql/query/query-compiler/generated"),
Postgres
)
compileQuery(testQuery, config)
What is compiled.
data class GetUserOrdersRecord(
val id: String,
val firstName: String,
val lastName: String,
val orders: List<JsonOrdersRecord>
)
@Serializable
data class JsonOrdersRecord(
val orderPlaced: String,
val inventory: JsonInventoryRecord
)
@Serializable
data class JsonInventoryRecord(
val id: String,
val name: String,
val price: String
)
object GetUserOrders : ICompiledQuery<GetUserOrdersRecord> {
override val sqlQuery: String = """
SELECT users.id,users.first_name,users.last_name,
json_agg(json_build_object('orderPlaced',user_orders.order_placed,'inventory',json_build_object('id',inventory.id,'name',inventory.name,'price',inventory.price))) as orders
from jasync_example.users
join jasync_example.user_orders on user_orders.user_id = users.id
join jasync_example.inventory on inventory.id = user_orders.inventory_id
group by users.id,users.first_name,users.last_name
""".trimIndent()
override val version: Number = 1
}
As you can see we've built out the json agg into data objects. This is just an alpha to demo where the feature is going. But this will support multi platform in the future.
Closing Thoughts
Reflection
- Don't schedule features over the holiday.
- The prepared query change revealed a lot of pain points in the prior Executor interface. Causing a massive over haul of the under lying executor and
FRM
model.
Next
The next mile stone is 0.1.5, set to bring MySQL/MariaDB support, insert returns, and hopefully nested queries.