← All tasks Task 12 of 12 unsolved

12. Database Dialect Type Mapping

Cross-DB Sealed Classes ~25 min

Each database has its own native type names (PostgreSQL int4, MySQL INT, both = INTEGER). Map every dialect's native type into a single CanonicalType so the rest of the engine can stay dialect-agnostic. Round-trip the same canonical type from PostgreSQL to MySQL or vice-versa.

Bonus: identifier quoting — PostgreSQL "users", MySQL `users`.

Tests this task must pass

PostgreSQL types — int4, int8, bool, timestamptz, jsonb, bytea, uuid map to their canonical equivalents.
MySQL types — INT, BIGINT, TINYINT(1) (size = 1 → BOOLEAN), DATETIME, JSON, LONGBLOB.
Unknown types — anything unrecognized returns CanonicalType.UNKNOWN(nativeType).
Identifier quoting — PostgreSQL produces "users", MySQL produces `users`.
Cross-DB equivalence — pg.mapType("jsonb") == my.mapType("JSON"); pg.mapType("int4") == my.mapType("INT").

//sampleStart sealed class CanonicalType(val name: String) { object INTEGER : CanonicalType("INTEGER") object LONG : CanonicalType("LONG") object BOOLEAN : CanonicalType("BOOLEAN") object DOUBLE : CanonicalType("DOUBLE") object TEXT : CanonicalType("TEXT") object TIMESTAMP : CanonicalType("TIMESTAMP") object TIMESTAMP_TZ : CanonicalType("TIMESTAMP_TZ") object UUID : CanonicalType("UUID") object JSON : CanonicalType("JSON") object BINARY : CanonicalType("BINARY") data class UNKNOWN(val nativeType: String) : CanonicalType("UNKNOWN:$nativeType") } interface DatabaseDialect { fun mapType(typeName: String, size: Int = 0): CanonicalType fun quoteIdentifier(name: String): String } class PostgresDialect : DatabaseDialect { override fun mapType(typeName: String, size: Int): CanonicalType { TODO() } override fun quoteIdentifier(name: String): String { TODO() } } class MySqlDialect : DatabaseDialect { override fun mapType(typeName: String, size: Int): CanonicalType { TODO() } override fun quoteIdentifier(name: String): String { TODO() } } //sampleEnd fun main() { val pg = PostgresDialect() val my = MySqlDialect() // Test 1: PostgreSQL types check(pg.mapType("int4") == CanonicalType.INTEGER) { "FAIL: pg int4" } check(pg.mapType("int8") == CanonicalType.LONG) { "FAIL: pg int8" } check(pg.mapType("bool") == CanonicalType.BOOLEAN) { "FAIL: pg bool" } check(pg.mapType("timestamptz") == CanonicalType.TIMESTAMP_TZ) { "FAIL: pg timestamptz" } check(pg.mapType("jsonb") == CanonicalType.JSON) { "FAIL: pg jsonb" } check(pg.mapType("bytea") == CanonicalType.BINARY) { "FAIL: pg bytea" } check(pg.mapType("uuid") == CanonicalType.UUID) { "FAIL: pg uuid" } println("✅ Test 1: PostgreSQL types") // Test 2: MySQL types check(my.mapType("INT") == CanonicalType.INTEGER) { "FAIL: mysql INT" } check(my.mapType("BIGINT") == CanonicalType.LONG) { "FAIL: mysql BIGINT" } check(my.mapType("TINYINT", 1) == CanonicalType.BOOLEAN) { "FAIL: mysql TINYINT(1)" } check(my.mapType("DATETIME") == CanonicalType.TIMESTAMP) { "FAIL: mysql DATETIME" } check(my.mapType("JSON") == CanonicalType.JSON) { "FAIL: mysql JSON" } check(my.mapType("LONGBLOB") == CanonicalType.BINARY) { "FAIL: mysql LONGBLOB" } println("✅ Test 2: MySQL types") // Test 3: Unknown types val pgUnk = pg.mapType("geometry") check(pgUnk is CanonicalType.UNKNOWN) { "FAIL: unknown type" } println("✅ Test 3: Unknown type — ${pgUnk.name}") // Test 4: Quoting check(pg.quoteIdentifier("users") == "\"users\"") { "FAIL: pg quoting" } check(my.quoteIdentifier("users") == "`users`") { "FAIL: mysql quoting" } println("✅ Test 4: Identifier quoting") // Test 5: Same canonical type from different DBs check(pg.mapType("jsonb") == my.mapType("JSON")) { "FAIL: same canonical for JSON" } check(pg.mapType("int4") == my.mapType("INT")) { "FAIL: same canonical for INTEGER" } println("✅ Test 5: Cross-DB canonical types match") println("\n🎉 ALL TESTS PASSED!") }

Solution

class PostgresDialect : DatabaseDialect {
    override fun mapType(typeName: String, size: Int): CanonicalType =
        when (typeName.lowercase()) {
            "int4", "integer", "int", "serial" -> CanonicalType.INTEGER
            "int8", "bigint", "bigserial" -> CanonicalType.LONG
            "bool", "boolean" -> CanonicalType.BOOLEAN
            "float4", "float8", "real", "double precision", "numeric" -> CanonicalType.DOUBLE
            "text", "varchar", "char", "citext" -> CanonicalType.TEXT
            "timestamp" -> CanonicalType.TIMESTAMP
            "timestamptz", "timestamp with time zone" -> CanonicalType.TIMESTAMP_TZ
            "uuid" -> CanonicalType.UUID
            "json", "jsonb" -> CanonicalType.JSON
            "bytea" -> CanonicalType.BINARY
            else -> CanonicalType.UNKNOWN(typeName)
        }

    override fun quoteIdentifier(name: String): String = "\"$name\""
}

class MySqlDialect : DatabaseDialect {
    override fun mapType(typeName: String, size: Int): CanonicalType =
        when (typeName.uppercase()) {
            "INT", "INTEGER", "MEDIUMINT" -> CanonicalType.INTEGER
            "BIGINT" -> CanonicalType.LONG
            "TINYINT" -> if (size == 1) CanonicalType.BOOLEAN else CanonicalType.INTEGER
            "BOOLEAN", "BOOL" -> CanonicalType.BOOLEAN
            "FLOAT", "DOUBLE", "REAL", "DECIMAL" -> CanonicalType.DOUBLE
            "TEXT", "VARCHAR", "CHAR", "LONGTEXT", "MEDIUMTEXT" -> CanonicalType.TEXT
            "DATETIME", "TIMESTAMP" -> CanonicalType.TIMESTAMP
            "JSON" -> CanonicalType.JSON
            "BLOB", "LONGBLOB", "MEDIUMBLOB", "BINARY", "VARBINARY" -> CanonicalType.BINARY
            else -> CanonicalType.UNKNOWN(typeName)
        }

    override fun quoteIdentifier(name: String): String = "`$name`"
}

← Previous Next →