类型

MySQL 类型

SQLDelight 列定义与常规 H2 列定义相同，但支持一个 额外的列约束，该约束指定了生成接口中列的 Kotlin 类型。

CREATE TABLE some_types (
  some_tiny_int TINYINT,                           -- Retrieved as Byte
  some_small_int SMALLINT,                         -- Retrieved as Short
  some_integer INTEGER,                            -- Retrieved as Int
  some_int INT,                                    -- Retrieved as Int
  some_big_int BIGINT,                             -- Retrieved as Long
  some_decimal DECIMAL(6,5),                       -- Retrieved as Int
  some_dec DEC(6,5),                               -- Retrieved as Int
  some_numeric NUMERIC(6,5),                       -- Retrieved as Int
  some_float FLOAT(6),                             -- Retrieved as Double
  some_real REAL,                                  -- Retrieved as Double
  some_double DOUBLE,                              -- Retrieved as Double
  some_double_precision DOUBLE PRECISION,          -- Retrieved as Double
  some_boolean BOOLEAN,                            -- Retrieved as Boolean
  some_date DATE,                                  -- Retrieved as String
  some_time TIME,                                  -- Retrieved as String
  some_timestamp2 TIMESTAMP(6),                    -- Retrieved as String
  some_char CHAR,                                  -- Retrieved as String
  some_character CHARACTER(6),                     -- Retrieved as String
  some_char_varying CHAR VARYING(6),               -- Retrieved as String
  some_longvarchar LONGVARCHAR,                    -- Retrieved as String
  some_character_varying CHARACTER VARYING(6),     -- Retrieved as String
  some_varchar VARCHAR(16),                        -- Retrieved as String
  some_clo CHARACTER LARGE OBJECT(16),             -- Retrieved as String
  some_clob clob(16 M CHARACTERS),                 -- Retrieved as String
  some_binary BINARY,                              -- Retrieved as ByteArray
  some_binary2 BINARY(6),                          -- Retrieved as ByteArray
  some_longvarbinary LONGVARBINARY,                -- Retrieved as ByteArray
  some_longvarbinary2 LONGVARBINARY(6),            -- Retrieved as ByteArray
  some_binary_varying BINARY VARYING(6),           -- Retrieved as ByteArray
  some_varbinary VARBINARY(8),                     -- Retrieved as ByteArray
  some_uuid UUID,                                  -- Retrieved as ByteArray
  some_blob BLOB,                                  -- Retrieved as ByteArray
  some_blo BINARY LARGE OBJECT(6),                 -- Retrieved as ByteArray
  some_bit BIT,                                    -- Retrieved as ByteArray
  some_bit2 BIT(6),                                -- Retrieved as ByteArray
  some_bit_varying BIT VARYING(6),                 -- Retrieved as ByteArray
  some_interval INTERVAL YEAR TO MONTH,            -- Retrieved as ByteArray
  some_interval2 INTERVAL YEAR(3),                 -- Retrieved as ByteArray
  some_interval3 INTERVAL DAY(4) TO HOUR,          -- Retrieved as ByteArray
  some_interval4 INTERVAL MINUTE(4) TO SECOND(6),  -- Retrieved as ByteArray
  some_interval5 INTERVAL SECOND(4,6)              -- Retrieved as ByteArray
);

自定义列类型

如果你想将列作为自定义类型检索，可以指定一个 Kotlin 类型：

import kotlin.String;
import kotlin.collections.List;

CREATE TABLE hockeyPlayer (
  cup_wins TEXT AS List<String> NOT NULL
);

然而，创建 Database 需要你提供一个 ColumnAdapter，它知道如何在数据库类型和你的自定义类型之间进行映射：

val listOfStringsAdapter = object : ColumnAdapter<List<String>, String> {
  override fun decode(databaseValue: String) =
    if (databaseValue.isEmpty()) {
      listOf()
    } else {
      databaseValue.split(",")
    }
  override fun encode(value: List<String>) = value.joinToString(separator = ",")
}

val queryWrapper: Database = Database(
  driver = driver,
  hockeyPlayerAdapter = hockeyPlayer.Adapter(
    cup_winsAdapter = listOfStringsAdapter
  )
)

枚举

为了方便，SQLDelight 运行时包含一个 ColumnAdapter，用于将枚举存储为字符串数据。

import com.example.hockey.HockeyPlayer;

CREATE TABLE hockeyPlayer (
  position TEXT AS HockeyPlayer.Position
)

val queryWrapper: Database = Database(
  driver = driver,
  hockeyPlayerAdapter = HockeyPlayer.Adapter(
    positionAdapter = EnumColumnAdapter()
  )
)

值类型

如果需要，SQLDelight 可以为列生成一个值类型，该值类型会包装底层数据库类型：

CREATE TABLE hockeyPlayer (
  id INT AS VALUE
);

## 乐观锁

如果将某个列指定为 `LOCK`，它将为其生成一个值类型，并要求 `UPDATE` 语句正确使用该锁来执行更新。

```sql
CREATE TABLE hockeyPlayer(
  id INT AS VALUE,
  version_number INT AS LOCK,
  name VARCHAR(8)
);

-- This will fail (and the IDE plugin will suggest rewriting to the below)
updateName:
UPDATE hockeyPlayer
SET name = ?;

-- This will pass compilation
updateNamePassing:
UPDATE hockeyPlayer
SET name = ?
    version_number = :version_number + 1
WHERE version_number = :version_number;

迁移中的自定义类型

如果迁移是模式的事实来源，那么在修改表时，您也可以指定暴露的 Kotlin 类型：

import kotlin.String;
import kotlin.collection.List;

ALTER TABLE my_table
  ADD COLUMN new_column VARCHAR(8) AS List<String>;