Exposed 数据库 CustomColumnType

PostgreSQL 数据库有一种列类型用于存储各种网络地址（IPv4、IPv6 和 MAC 地址），文档： 8.9. Network Address Types。

本文定义了一个名为 switches 的数据表，用于存储交换机 MAC 地址 （列名 mac_address）和地址 （列名 address）的对应关系，配有 id、created_at 列用于其它可能的用途。 其中 mac_address 列的数据类型为 macaddr 。

CREATE TABLE public.switches (
	id uuid DEFAULT uuid_generate_v4() NOT NULL,
	mac_address macaddr NOT NULL,
	address text NULL,
	created_at timestamp DEFAULT now() NOT NULL,
	CONSTRAINT sccn_swithes_pkey PRIMARY KEY (id)
);

直接通过数据库管理工具与数据库交互时，这种列类型与 text 没有明显的区别。但是我们在项目中需要与后端服务结合，本文采用 Ktor 作为后端服务，对应的数据类定义如下。其中 MAC 地址在前端直接定义为 String 类型。

@Serializable
data class SwitchRecord(
    @Contextual val id: UUID?,
    val macAddress: String,
    val address: String?,
    @Contextual val createdAt: Instant?
)

用于这个数据类的序列化代码如下。

object UUIDSerializer : KSerializer<UUID> {
    override val descriptor: SerialDescriptor = PrimitiveSerialDescriptor("UUID", PrimitiveKind.STRING)

    override fun serialize(encoder: Encoder, value: UUID) {
        encoder.encodeString(value.toString())
    }

    override fun deserialize(decoder: Decoder): UUID {
        return UUID.fromString(decoder.decodeString())
    }
}

object InstantSerializer : KSerializer<Instant> {
    override val descriptor: SerialDescriptor = PrimitiveSerialDescriptor("Instant", PrimitiveKind.STRING)

    override fun serialize(encoder: Encoder, value: Instant) {
        encoder.encodeString(value.toString())
    }

    override fun deserialize(decoder: Decoder): Instant {
        return Instant.parse(decoder.decodeString())
    }
}

在 Ktor 项目中使用 Exposed 库对接 PostgreSQL 数据库时，Exposed 提供的列类型不能直接读写 macaddr 类型的列，需要自定义转换。因为 MAC 地址实际上可以理解为字符串的存储，所以这里定义一个 StringColumnType 类型转换。

class PostgresMacaddrColumnType: StringColumnType() {
    override fun sqlType(): String = "macaddr"
    
    override fun setParameter(stmt: PreparedStatementApi, index: Int, value: Any?) {
        val pgObject = PGobject()
        pgObject.type = "macaddr"
        pgObject.value = value as String?
        stmt.set(index, pgObject, this)
    }
    
    override fun valueFromDB(value: Any): String {
        return when (value) {
            is PGobject -> value.value ?: ""
            is String -> value
            else -> value.toString()
        }
    }
}

在 Exposed 的表定义中，用 registerColumn(name = "<column_name>", type = <上面创建的类型>()) 定义网络地址列。

object SwitchRecords: Table("database_table_name") {
    val id = uuid("id").autoGenerate()
    val macAddress = registerColumn(name = "mac_address", type = PostgresMacaddrColumnType())
    // other columns
    
    override val primaryKey = PrimaryKey(id)
}

经过以上的定义后，就可以使用在 Ktor 项目中使用 String 类型的数据构造 SwitchRecord 对象并存储到数据库中，或者从数据库中读取并转换为 SwitchRecord 对象使用。

经过测试以下只要符合 MAC 地址要求的数据均可插入，不符合的数据会报错。

• ✅ 12ab34cd56ef 、12:ab:34:cd:56:ef 、12AB34CD56EF、12:AB:34:CD:56:EF(字母大小写、是否含有冒号均可)

• ❌ AB12CD34EF5G（不是正确的 MAC 地址），报错：Caused by: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type macaddr: "AB12CD34EF5G