Adds a new ArrayColumnType that accepts and returns elements of a List:
object TestTable : Table() {
val amounts: Column<List<Int>?> = array<Int>("amounts", IntegerColumnType()).nullable()
val items: Column<List<String?>> = array<String?>("amounts", TextColumnType()).default(emptyList())
val prices: Column<List<Double>> = array<Double>("prices", DoubleColumnType())
}
Note:
The ARRAY data type is supported by H2 and PostgreSQL (and their variants). See below for other possible supporting DB.
Even though it is an array() column, the expected input/output is List. Type casting with List and Java ArrayList is more simple when retrieving java.sql.Array from the DB. Avoiding class cast exceptions when converting back to a kotlin.Array is possible but would require iterating over contents of the java.sql.Array.resultSet instead of using the array directly. Since Array and List don't share a type, there would also need to be adequate differences to prevent clashing overrides:
And all functions would need to be duplicated or given less restrictive type parameters. If there are any opinions about whether `Array` should be used instead (or add both), please let me know.
- To ensure that each element in the array is processed properly, the user has to supply a delegate column type (on column declaration and in some column functions). This could be seen as too verbose, so functions could be altered to use reflection and assign a column type for primitives, so that the user could choose to omit this argument.
---
Allows maximum array size to be set on column declaration.
Allows array column getter (via index reference) and slicing:
```kt
val somePrices = TestTable.prices.slice(3, 7)
TestTable.select(somePrices).where { TestTable.items[2] eq "Item B" }
Features not supported:
Update using index reference: UPDATE ... SET array_column[2] = new_value. This would require changes to the behavior of UpdateBuilder class.
Multi-dimensional support (PostgreSQL): INTEGER[][].
Oracle & SQL Server: While they don't support ARRAY data type, there are ways to simulate array storage by creating a custom SQL type (using VARRAY or table-value parameters). Support could be investigated if requested, but there might be driver-specific issues with how arrays are created and provided to PreparedStatement (for Oracle at least).
Next steps:
[x] Add support for new column type with ANY and ALL operators.
[x] Refactor existing ANY and ALL operators to use ArrayColumnType.
[x] Consider using reflection and reified versions of column functions to resolve an appropriate column type, so users don't need to manually provide this value.
[x] The new column works with datetime types as long as a default array is not used on table creation. Tests for these modules can be added once the datetime default issue is resolved.
Adds a new
ArrayColumnType
that accepts and returns elements of a List:Note:
ARRAY
data type is supported by H2 and PostgreSQL (and their variants). See below for other possible supporting DB.array()
column, the expected input/output isList
. Type casting with List and JavaArrayList
is more simple when retrievingjava.sql.Array
from the DB. Avoiding class cast exceptions when converting back to akotlin.Array
is possible but would require iterating over contents of thejava.sql.Array.resultSet
instead of using the array directly. SinceArray
andList
don't share a type, there would also need to be adequate differences to prevent clashing overrides:fun arrayList(name: String, columnType: ColumnType, length: Int? = null): Column<List> =
registerColumn(name, ArrayColumnType(columnType.apply { nullable = true }, true, length))
Features not supported:
UPDATE ... SET array_column[2] = new_value
. This would require changes to the behavior ofUpdateBuilder
class.INTEGER[][]
.PreparedStatement
(for Oracle at least).Next steps:
ArrayColumnType
.