Forem: keyridan

Kotlin reflection and prepared statement tutorial

keyridan — Mon, 10 Feb 2020 20:51:01 +0000

Recently I decided to write my own habit tracker. In my project for access to the database, I opted for exposed. It’s a lightweight SQL library, which I find pretty amazing and easy to use. But at some point, I faced the need to write a bit more complex query, mainly to calculate habit streakson the database side. In that case exposed FAQ has a native SQL solution, which you can implement on top of the library. It lets you execute queries and map its result. This improvement allows you to write your queries in such a manner:

What can be a real problem here is an SQL injection since the code executes the whole string as SQL statement. It doesn’t allow you to separate parameters to use them in the context of parameters, not as a SQL command. That’s why I decided to go with an old good PreparedStatement. In this tutorial, I will try to improve its use with Kotlin type system. All code is available over on GitHub, with one branch per chapter so that you can follow along.

Basic work with Prepared Statement - master branch here
Adding user table - userTable branch here
Adding map for ResultSet? - mapResult branch here
Extract values with reflection - reflect-getValue branch here
Extract entities with reflection - reflect-dataClass branch here
Extract values using types - types-getValue branch here
Query parameters - types-getValue branch here
Conclusion

1. Basic work with Prepared Statement — master branch

Let me quickly introduce the base project. In the gradle file we have such libraries as h2database, config4k and exposed for database connection and transactions. For testing purposes, we added dependencies: JUnit and assertk.

File AppConfig.kt loads database configs from application.conf. TransactionManager gets database connection and provides us with the currentTransaction. Its method tx allows us to run queries within a transaction.

The main focus of this tutorial is Queries and QueriesKtTest. There is the first version of native SQL executor:

Nothing fancy here. It takes currentTransaction, creates preparedStatement from our string and executes it.

Test for this method:

As you can see we have to work directly with the ResultSet, call next() to obtain record and parse string value of the column with getString() method. Let’s add a new table and see how it adds complexity to the code.

2. Adding user table — userTable branch

Let’s quickly introduce a new table in terms of exposed library. For this purpose we’ll add Tables.kt:

Object Users represents table with two columns: id: Int, name: Varchar. Test in this branch is more complicated. Instead of tx method we will use a new one:

It creates schema with our table and rollback transaction afterwards.

The test now inserts 2 user rows into the table and reads them one after another. Let’s create map for ResultSet to make parsing easier.

3. Adding map for `ResultSet?` — mapResult branch

This sections contains just a few tweaks. Our test lost few lines of code:

Now it directly calls getValue.

All resultSet.next() now can be found in the map method. It takes transform ResultSet as a function. In our case, it’s the same getString with the name of the column. Basically, getValue maps through the result set and takes its string values.

Of course, not all our query result values are strings. Let’s use some reflection to expand the capabilities of our method.

4. Extract values with reflection — reflect-getValue branch

As soon as we don’t want to create tons of method for each type that we want to extract from the result set, we’ll use reflection.

Here we compare class we want to extract with some known classes. We call ::class to obtain Kotlin classKClass<T> value of them. In the case of a match, we call the corresponding ResultSet method.

The toValue function:

Note that it also takes kClass parameter. But we don’t want to pass directly the class value. We’ll leave this part of work to Kotlin and use its magic of inline fun and reified type parameteres.

With reified we can access the type T and get class of it, the compiler knows the actual type used as a typed argument.

Now we call getValue as .getValue<String>(“name”) or getValue<Int>(“id”). In our case we don’t even need to change anything in our test:

The compiler has enough information about type T from the definition of the resultSet variable.

Amazing! Let’s add some more reflection to be able to convert ResultSet to data class.

5. Extract entities with reflection — reflect-dataClass branch

Let’s create data class for our Users table in QueiesKtTest.

How can we create a class with reflection? We can obtain information about the class constructor and its parameters.

With the type and name of each parameter we extract values from the result set. And to create a class instance we call the constructor with all these values as vararg arguments. One more cool thing is how we determine the KClass values of the parameters. We use reflection jvmErasure:

Returns the KClass instance representing the runtime class to which this type is erased to on JVM.

Here are another examples of reified usage to convert the result set to data class:

Note that function can be inline only if it calls public functions. In this case, we can easily call one inline function from another one. Here’s the updated test:

We changed getValue(“name”) to toEntities().

6. Extract values using types types — getValue branch

Reflection is amazing, but all its magic is in runtime. While we would rather rely on type system. Let’s try rewrite getValue.

First, we need to create classes that will represent our columns with types and names:

Let’s repeat extract method in terms of these new types:

While we use sealed classes here we don’t need to use else branch, because compiler can prove that all possible cases are covered.

Here with usual inline and reified you can use infix notation. All these let us write code such as: BooleanColumn(“name”) from result

Let’s create a new test and use all our result conversion functions:

These time we use row_number() function in the query. It returns an integer value, the column name is rn. To parse the result we can use:

7. Query parameters — queryParameters branch

We have a couple of options to extract values from the query result. But we still have no way to provide arguments to prepared statement. Let’s now focus on that.

As in the last chapter let’s add some more types:

And match through them:

As soon as we can express our parameters, we need to update exec function to pass these values to the prepared statement:

For each parameter we call its prepared statement set version and also pass its index, so don’t forget to keep the order of variables.

Also we added a method to map through the result set with our previous chapter’s from function:

Let’s check our improvements with a new test. Let’s say we want to get all records with names that are longer than some value and contain "@".

In this case, we’ll have only one name as a result.

Conclusion

From now on, we are prepared for huge queries that we couldn’t express with exposed:

SQL injection safe
the result can be easily converted to our types

As a final result, we have two conversion versions. One uses the reflection. You are required only to specify your type, it does all conversion by itself, but not type-safe. The second uses specified columns, it is type-safe, but all conversion descriptions are on you.

Please, let me know which option you would prefer to use!

SQL Story of unbroken chains of events (daily/weekly streaks)

keyridan — Mon, 03 Feb 2020 23:11:37 +0000

Recently I decided to write my own habit tracker.
I'm planning to have 2 kinds of habits: everyday habits and every-week habits. Plus in order to accomplish some of them you must check them several times per period. To track it and prevent me from quitting my new habits we'll calculate unbroken chains of events in 3 parts:

Everyday habit streak
Every week habit streak
With postgreSQL queries

1. Everyday habit streak

For a start let's calculate consecutive series of events that should happen every day.
For the demonstration purpose let's use data dumps on Stack Exchange Data Explorer. We will use a post table and try to find streaks of post creation. You can find the schema description here. It uses SQL Server, but next, we'll rewrite it for PostgresSQL.
First, let's check what do we have for user 1.

SELECT 
  ROW_NUMBER() OVER (ORDER BY CreationDate) rowNumber,
  CAST(CreationDate AS DATE) date
FROM Posts
WHERE OwnerUserId = ##UserId##
ORDER BY CreationDate

(you can run this snippet here and check all 144 rows of data)
We can see lots of posts created during the first days. And we can find 5 day streak here:

rowNumber   date
-----------------------
1   2008-07-31 00:00:00
2   2008-07-31 00:00:00
3   2008-07-31 00:00:00
4   2008-08-04 00:00:00
5   2008-08-04 00:00:00
6   2008-08-04 00:00:00

7   2008-08-10 00:00:00 ----
8   2008-08-11 00:00:00     |
9   2008-08-12 00:00:00     |
10  2008-08-12 00:00:00     |
11  2008-08-12 00:00:00     |
12  2008-08-12 00:00:00     |5 day
13  2008-08-12 00:00:00     |streak
14  2008-08-13 00:00:00     |found  
15  2008-08-13 00:00:00     | 
16  2008-08-13 00:00:00     |
17  2008-08-14 00:00:00     |
18  2008-08-14 00:00:00     |
19  2008-08-14 00:00:00     |
20  2008-08-14 00:00:00 ----

21  2008-08-17 00:00:00
...

What is ROW_NUMBER() function in the snippet above? It's called window function, it allows to make calculations across all rows of the current query.

Before using it in our calculations let's resolve another problem.
In our 5 days streak we can see a lot of repeating days, which gives us nothing new, let's group it and calculate the number of posts for these specific days - column amount.

SELECT 
  ROW_NUMBER() OVER (ORDER BY MIN(CreationDate)) rowNumber,
  COUNT(*) amount,
  CAST(MIN(CreationDate)AS DATE) date
FROM Posts
WHERE OwnerUserId = ##UserId##
  AND CreationDate BETWEEN '2008-08-10' and '2008-08-17'
GROUP BY CAST(CreationDate AS DATE)
ORDER BY MIN(CreationDate)

(run it!)

r amount  date
---------------------------
1   1   2008-08-10 00:00:00 ----
2   1   2008-08-11 00:00:00     |our
3   5   2008-08-12 00:00:00     |5 days
4   3   2008-08-13 00:00:00     |streak
5   4   2008-08-14 00:00:00 ----

Now we can see a pattern: day 5 of our streak here is also the 5th row.
Let's use this sweet ROW_NUMBER() function and subtract row number from our date.

SELECT 
  ROW_NUMBER() OVER (ORDER BY MIN(CreationDate)) row,
  COUNT(*) amount,
  CAST(MIN(CreationDate)AS DATE) date,
  CAST(MIN(CreationDate) - ROW_NUMBER() OVER (ORDER BY MIN(CreationDate)) as DATE) dateMinusRow
FROM Posts
WHERE OwnerUserId = ##UserId##
  AND CreationDate BETWEEN '2008-08-10' and '2008-08-17'
GROUP BY CAST(CreationDate AS DATE)
ORDER BY MIN(CreationDate)

(run it here)

r   amount          date                dateMinusRow
------------------------------------------------------------
1   1       2008-08-10 00:00:00     2008-08-09 00:00:00 --
2   1       2008-08-11 00:00:00     2008-08-09 00:00:00    |same value
3   5       2008-08-12 00:00:00     2008-08-09 00:00:00    |because of
4   3       2008-08-13 00:00:00     2008-08-09 00:00:00    |(date - row_number)
5   4       2008-08-14 00:00:00     2008-08-09 00:00:00 --

And apply this logic to the whole set:

SELECT 
  ROW_NUMBER() OVER (ORDER BY MIN(CreationDate)) as DATE) row,
  COUNT(*) amount,
  CAST(MIN(CreationDate)AS DATE) date,
  CAST(MIN(CreationDate) - ROW_NUMBER() OVER (ORDER BY MIN(CreationDate)) as DATE) dateMinusRow
FROM Posts
WHERE OwnerUserId = ##UserId##
GROUP BY CAST(CreationDate AS DATE)
ORDER BY MIN(CreationDate)

(run me)

r   amount      date             dateMinusRow
-----------------------------------------------------------
1    3       2008-07-31 00:00:00   2008-07-30 00:00:00
2    3       2008-08-04 00:00:00   2008-08-02 00:00:00

3    1       2008-08-10 00:00:00   2008-08-07 00:00:00-- 
4    1       2008-08-11 00:00:00   2008-08-07 00:00:00  |same value
5    5       2008-08-12 00:00:00   2008-08-07 00:00:00  |because of
6    3       2008-08-13 00:00:00   2008-08-07 00:00:00  |(date - row_number)
7    4       2008-08-14 00:00:00   2008-08-07 00:00:00--

8    2       2008-08-17 00:00:00   2008-08-09 00:00:00

As we can see it works perfectly, column dateMinusRow has the same value for all streak days.
All we need to do now is group the values and enjoy the result.

SELECT
  COUNT(*) streak,
  SUM(amount) streakAmount,
  MIN(date) startDate,
  MAX(date) endDate,
  dateMinusRow dateMinusRow
FROM (
  SELECT 
    COUNT(*) amount,
    CAST(MIN(CreationDate)AS DATE) date,
    CAST(MIN(CreationDate) - ROW_NUMBER() OVER (ORDER BY MIN(CreationDate)) as DATE) dateMinusRow
  FROM Posts
  WHERE OwnerUserId = ##UserId##
  GROUP BY CAST(CreationDate AS DATE)
) groupedDays
GROUP BY dateMinusRow

And result was as we expected (run it here and check all records)

streak          start                  endDate
--------------------------------------------------------
1       2008-07-31 00:00:00     2008-07-31 00:00:00 
1       2008-08-04 00:00:00     2008-08-04 00:00:00 
5       2008-08-10 00:00:00     2008-08-14 00:00:00 - our 5 days streak
1       2008-08-17 00:00:00     2008-08-17 00:00:00

We can work with this query further and find out what is our user's max daily streak (wasn't it 5?). But now let's talk about weeks and use this ROW_NUMBER() method there.

2. Every week habit streak

To use the same calculation method we need to understand what can represent all events during the same week.

First, we can use DATEPART() to get a week number and year from the date.
In this case, we'll have to get our head around calculations when streak goes from one year to another.

Another way to solve the week streak case is to use the difference in weeks between our CreationDate and some start date.
We'll use 0 as a start date. Sql Server interprets it like 1900-01-01 00:00:00.000.
Also by default Sql Server uses Sunday as the first day of the week:

SELECT 
  COUNT(*) anount,
  MIN(p.CreationDate) minDate,
  MAX(p.CreationDate) maxDate,
  DATEDIFF(wk, 0, CreationDate) weeks,
  DATEDIFF(wk, 0, CreationDate) - ROW_NUMBER() OVER (ORDER BY MIN(CreationDate)) weekMinusRow
FROM Posts p
WHERE OwnerUserId = ##UserId##
GROUP BY DATEDIFF(wk, 0, CreationDate)

In my case, I prefer to use Monday as the first day. Even though DATEDIFF ignores SET DATEFIRST there is a hint from here.

(run version with Monday as a first day)

So our final query:

SET DATEFIRST 1
SELECT
  COUNT(*) streak,
  MIN(minDate) startDate,
  MAX(maxDate) endDate
FROM (
  SELECT 
    COUNT(*) counts,
    MIN(p.CreationDate) minDate,
    MAX(p.CreationDate) maxDate,
    DATEDIFF(wk, DATEADD(dd,-@@datefirst,0), DATEADD(dd,-@@datefirst,CreationDate)) weeks,
    DATEDIFF(wk, DATEADD(dd,-@@datefirst,0), DATEADD(dd,-@@datefirst,CreationDate)) - ROW_NUMBER() OVER (ORDER BY MIN(CreationDate)) weekMinusRow
  FROM Posts p
  WHERE OwnerUserId = ##UserId##
  GROUP BY DATEDIFF(wk, DATEADD(dd,-@@datefirst,0), DATEADD(dd,-@@datefirst,CreationDate))
  HAVING COUNT(*) >= ##numberOfRepetitions##
) groupedRows
GROUP BY weekMinusRow
ORDER BY endDate DESC

(run it)

streak      startDate                  endDate
-----------------------------------------------------------
1        2009-03-30 08:00:18       2009-04-03 10:35:03
1        2009-03-10 01:05:12       2009-03-14 17:45:04
1        2008-12-01 04:56:38       2008-12-05 00:34:40
3        2008-10-07 08:01:40       2008-10-24 15:45:15
1        2008-08-25 00:11:43       2008-08-28 13:26:27
2        2008-08-04 02:45:07       2008-08-17 03:00:34

By the way, I also added HAVING COUNT(*) >= ##numberOfRepetitions## to filter by the number of actions that were performed per week.
From now on for streak week we should post harder, at least 4 times a week ;-)

3. With PostgreSQL queries

Data dumps on Stack Exchange Data Explorer are amazing and a lot of fun to play with. But in my current project, I use PostgreSQL. Let's rewrite our queries. For better readability, we will use with clause and extract subquery.

Sql Server	PostgreSQL
CAST(date AS DATE)	date_trunc('day', date)
CAST(MIN(CreationDate)AS DATE) - ROW_NUMBER() OVER (ORDER BY MIN(CreationDate))	date_trunc('day', CreationDate) - INTERVAL '1' DAY * ROW_NUMBER() OVER (ORDER BY MIN(CreationDate))
DATEDIFF(wk, start, end)	(DATE_PART('day', end - start)/7)::int

The final query for day streaks:

WITH
  groups(date, dateMinusRow) AS (
    SELECT 
      date_trunc('day', CreationDate) date,
      date_trunc('day', CreationDate) - INTERVAL '1' DAY * DENSE_RANK() OVER (ORDER BY date_trunc('day', CreationDate)) dateMinusRow
    FROM Posts
    GROUP BY date_trunc('day', CreationDate)
    HAVING COUNT(*) >= 1
  )
SELECT
  COUNT(*) AS streak,
  MIN(date) AS startDate,
  MAX(date) AS endDate
FROM groups
GROUP BY dateMinusRow
ORDER BY endDate DESC

run it here

The final query for week streaks:

WITH
  groups(minDate, maxDate, weekMinusRow) AS (
    SELECT
      MIN(CreationDate) minDate,
      MAX(CreationDate) maxDate,
      (DATE_PART('day', CreationDate - to_timestamp(0))/7)::int - ROW_NUMBER() OVER (ORDER BY MIN(CreationDate)) weekMinusRow
    FROM Posts
    GROUP BY (DATE_PART('day', CreationDate - to_timestamp(0))/7)::int
    HAVING COUNT(*) >= 2
  )
SELECT
  COUNT(*) streak,
  MIN(minDate) startDate,
  MAX(maxDate) endDate
FROM groups
GROUP BY weekMinusRow
ORDER BY endDate DESC