mMile Stone

Issues

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.