{ MigrationUtils.statementsRequiredForDatabaseMigration(ActorTable, MovieTable, ActorInMovieTable) .forEach { exec(it) } } Path: 10-multitenant/01-multitenant-spring-web Source: DatabaseInitializer.kt CREATE SCHEMA IF NOT EXISTS korean CREATE SCHEMA IF NOT EXISTS English
"Amount of testers" val tester = object: Table("tester") { val amount = integer("amount") .withDefinition("COMMENT", stringLiteral(comment)) // ஸۢী ࢳ ୶о } -- MySQL 8 CREATE TABLE IF NOT EXISTS tester ( amount INT COMMENT 'Amount of testers' NOT NULL )
"Amount of testers" val tester = object: Table("tester") { val amount = integer("amount") .withDefinition("COMMENT", stringLiteral(comment)) // ஸۢী ࢳ ୶о } val tester = object: Table("tester") { val amount = integer("amount") val active = bool("active").nullable() .withDefinition("INVISIBLE") // Implicit ઑഥ द (select * from tester), ஸۢਸ ऀӣ } -- MySQL 8 CREATE TABLE IF NOT EXISTS tester ( amount INT COMMENT 'Amount of testers' NOT NULL )
"Amount of testers" val tester = object: Table("tester") { val amount = integer("amount") .withDefinition("COMMENT", stringLiteral(comment)) // ஸۢী ࢳ ୶о } val tester = object: Table("tester") { val amount = integer("amount") val active = bool("active").nullable() .withDefinition("INVISIBLE") // Implicit ઑഥ द (select * from tester), ஸۢਸ ऀӣ } -- MySQL 8 CREATE TABLE IF NOT EXISTS tester ( amount INT COMMENT 'Amount of testers' NOT NULL ) -- MySQL 8 CREATE TABLE IF NOT EXISTS tester ( amount INT NOT NULL, active BOOLEAN INVISIBLE NULL -- INVISIBLE ஸۢ )
{ val id = integer("id").autoIncrement() val name = varchar("name", 255) override val primaryKey = PrimaryKey(id) // ੋؙझ val byName = index("tester_by_name", isUnique = false, name) }
{ val id = integer("id").autoIncrement() val name = varchar("name", 255) override val primaryKey = PrimaryKey(id) // ੋؙझ val byName = index("tester_by_name", isUnique = false, name) } -- Postgres CREATE TABLE IF NOT EXISTS tester ( id SERIAL PRIMARY KEY, "name" VARCHAR(255) NOT NULL ); CREATE INDEX tester_by_name ON tester ("name");
{ val id = integer("id").autoIncrement() val name = varchar("name", 255) override val primaryKey = PrimaryKey(id) // ੋؙझ val byName = index("tester_by_name", isUnique = false, name) } -- Postgres CREATE TABLE IF NOT EXISTS tester ( id SERIAL PRIMARY KEY, "name" VARCHAR(255) NOT NULL ); CREATE INDEX tester_by_name ON tester ("name"); val tester = object: Table("tester") { val id = integer("id").autoIncrement() val name = varchar("name", 255) override val primaryKey = PrimaryKey(id) // Hash index val byName = index("tester_by_name", isUnique = false, name, indexType = "HASH") }
{ val id = integer("id").autoIncrement() val name = varchar("name", 255) override val primaryKey = PrimaryKey(id) // ੋؙझ val byName = index("tester_by_name", isUnique = false, name) } -- Postgres CREATE TABLE IF NOT EXISTS tester ( id SERIAL PRIMARY KEY, "name" VARCHAR(255) NOT NULL ); CREATE INDEX tester_by_name ON tester ("name"); val tester = object: Table("tester") { val id = integer("id").autoIncrement() val name = varchar("name", 255) override val primaryKey = PrimaryKey(id) // Hash index val byName = index("tester_by_name", isUnique = false, name, indexType = "HASH") } -- Postgres CREATE TABLE IF NOT EXISTS tester ( id SERIAL PRIMARY KEY, "name" VARCHAR(255) NOT NULL ); CREATE INDEX tester_by_name ON tester USING HASH ("name");
val id3 = idTable.insert { it[name] = "name-3" } get idTable.id Path: 05-exposed-dml/01-dml Source: Ex02_Insert.kt val cityNames = listOf("Paris", "Moscow", "Helsinki") val allCitiesIDs: List<ResultRow> = cities.batchInsert(cityNames) { name -> this[cities.name] = name } val idTable = object: IntIdTable("tmp") { val name = varchar("foo", 10).uniqueIndex() }
64).uniqueIndex() val count = integer("count").default(1) } Path: 05-exposed-dml/01-dml Source: Ex04_Upsert.kt -- Postgres CREATE TABLE IF NOT EXISTS words ( "name" VARCHAR(64) NOT NULL, "count" INT DEFAULT 1 NOT NULL ); ALTER TABLE words ADD CONSTRAINT words_name_unique UNIQUE ("name"); val wordA = Words.upsert { it[word] = "A" it[count] = 10 } get Words.word Words.upsert { it[word] = wordA // "A" it[count] = 9 }
64).uniqueIndex() val count = integer("count").default(1) } Path: 05-exposed-dml/01-dml Source: Ex04_Upsert.kt -- Postgres CREATE TABLE IF NOT EXISTS words ( "name" VARCHAR(64) NOT NULL, "count" INT DEFAULT 1 NOT NULL ); ALTER TABLE words ADD CONSTRAINT words_name_unique UNIQUE ("name"); val wordA = Words.upsert { it[word] = "A" it[count] = 10 } get Words.word Words.upsert { it[word] = wordA // "A" it[count] = 9 } -- Postgres INSERT INTO words ("name", "count") VALUES ('A', 10) ON CONFLICT ("name") DO UPDATE SET "count"=EXCLUDED."count"
64).uniqueIndex() val count = integer("count").default(1) } Path: 05-exposed-dml/01-dml Source: Ex04_Upsert.kt -- Postgres CREATE TABLE IF NOT EXISTS words ( "name" VARCHAR(64) NOT NULL, "count" INT DEFAULT 1 NOT NULL ); ALTER TABLE words ADD CONSTRAINT words_name_unique UNIQUE ("name"); val wordA = Words.upsert { it[word] = "A" it[count] = 10 } get Words.word Words.upsert { it[word] = wordA // "A" it[count] = 9 } -- Postgres INSERT INTO words ("name", "count") VALUES ('A', 10) ON CONFLICT ("name") DO UPDATE SET "count"=EXCLUDED."count" -- Postgres INSERT INTO words ("name", "count") VALUES ('A', 9) ON CONFLICT ("name") DO UPDATE SET "count"=EXCLUDED."count"
EntityID<Long>): LongEntity(id) { companion object: LongEntityClass<City>(Cities) var name: String by Cities.name // one-to-many ҙ҅ val towns: SizedIterable<Town> by Town referrersOn Towns.cityId } class Town(id: EntityID<Long>): LongEntity(id) { companion object: LongEntityClass<Town>(Towns) // many-to-one ҙ҅ var city: City by City referencedOn Towns.cityId } class Person(id: EntityID<Long>): LongEntity(id) { companion object: LongEntityClass<Person>(People) var name: String by People.name // many-to-one ҙ҅ var city: City by City referencedOn People.cityId }
EntityID<Long>): LongEntity(id) { companion object: LongEntityClass<City>(Cities) var name: String by Cities.name // one-to-many ҙ҅ val towns: SizedIterable<Town> by Town referrersOn Towns.cityId } class Town(id: EntityID<Long>): LongEntity(id) { companion object: LongEntityClass<Town>(Towns) // many-to-one ҙ҅ var city: City by City referencedOn Towns.cityId } class Person(id: EntityID<Long>): LongEntity(id) { companion object: LongEntityClass<Person>(People) var name: String by People.name // many-to-one ҙ҅ var city: City by City referencedOn People.cityId } @OneToMany -> ࠗݽী referrersOn @ManyToOne -> धী referencedOn
City(id: EntityID<UUID>): UUIDEntity(id) { companion object: UUIDEntityClass<City>(Cities) var name: String by Cities.name val towns: SizedIterable<Town> by Town referrersOn Towns.cityId // one-to-many } class Person(id: EntityID<UUID>): UUIDEntity(id) { companion object: UUIDEntityClass<Person>(People) var name: String by People.name var city: City by City referencedOn People.cityId } class Town(id: EntityID<UUID>): UUIDEntity(id) { companion object: UUIDEntityClass<Town>(Towns) var city: City by City referencedOn Towns.cityId // many-to-one } class Address(id: EntityID<UUID>): UUIDEntity(id) { companion object: UUIDEntityClass<Address>(Addresses) var person: Person by Person referencedOn Addresses.personId // many-to-one var city: City by City referencedOn Addresses.cityId // many-to-one var address: String by Addresses.address }
varchar("name", 50) val lz4Fory = binarySerializedBlob<Embeddable>("lz4_fory", BinarySerializers.LZ4Fory).nullable() val lz4Kryo = binarySerializedBlob<Embeddable>("lz4_kryo", BinarySerializers.LZ4Kryo).nullable() val zstdFory = binarySerializedBlob<Embeddable2>("zstd_fory", BinarySerializers.ZstdFory).nullable() val zstdKryo = binarySerializedBlob<Embeddable2>("zstd_kryo", BinarySerializers.ZstdKryo).nullable() } class E1(id: EntityID<Int>): IntEntity(id) { companion object: IntEntityClass<E1>(T1) var name by T1.name var lz4Fory by T1.lz4Fory var lz4Kryo by T1.lz4Kryo var zstdFory by T1.zstdFory var zstdKryo by T1.zstdKryo override fun equals(other: Any?): Boolean = idEquals(other) override fun hashCode(): Int = idHashCode() override fun toString(): String = entityToStringBuilder() .add("name", name) .toString() } data class Embeddable( val name: String, val age: Int, val address: String, ): Serializable Path: 06-advanced/06-custom-columns Source: BinarySerializedBlobColumnTypeTest.kt data class Embeddable2( val name: String, val age: Int, val address: String, val zipcode: String, ): Serializable
columnName: String = "id"): IdTable<UUID>(name) { /** * UUID v7 ਸ Client ীࢲ ࢤࢿפ. */ final override val id: Column<EntityID<UUID>> = javaUUID(columnName).timebasedGenerated().entityId() /** ప࠶ ӝࠄః ੑפ. */ final override val primaryKey = PrimaryKey(id) } Path: 06-advanced/07-custom-entities Source: TimebasedUUIDTableTest.kt object T1: TimebasedUUIDTable("t_timebased_uuid") { val name = varchar("name", 255) val age = integer("age") } class E1(id: TimebasedUUIDEntityID): TimebasedUUIDEntity(id) { companion object: TimebasedUUIDEntityClass<E1>(T1) var name by T1.name var age by T1.age } fun Column<UUID>.timebasedGenerated(): Column<UUID> = clientDefault { Uuid.V7.nextId() }
number = varchar("number", 255).uniqueIndex() } Path: 07-jpa/01-convert-jpa-basic Source: ex05_relations/BankSchema.kt object AccountOwnerTable: IntIdTable("account_owner") { val ssn = varchar("ssn", 255).uniqueIndex() } object OwnerAccountMapTable: Table("owner_account_map") { val ownerId = reference("owner_id", AccountOwnerTable) val accountId = reference("account_id", BankAccountTable) init { uniqueIndex(ownerId, accountId) } } class BankAccount(id: EntityID<Int>): IntEntity(id) { companion object: IntEntityClass<BankAccount>(BankAccountTable) var number: String by BankAccountTable.number // many to many with via val owners: SizedIterable<AccountOwner> by AccountOwner via OwnerAccountMapTable } class AccountOwner(id: EntityID<Int>): IntEntity(id) { companion object: IntEntityClass<AccountOwner>(AccountOwnerTable) var ssn: String by AccountOwnerTable.ssn // many to many with via val accounts: SizedIterable<BankAccount> by BankAccount via OwnerAccountMapTable }
Source: ex04_tree/TreeNodeSchema buildTreeNodes() val sub = TreeNodeTable.alias("sub") val subQuery = sub .select(sub[TreeNodeTable.parentId]) .where { sub[TreeNodeTable.title] like "grand%" } val query = TreeNodeTable .selectAll() .where { TreeNodeTable.id inSubQuery subQuery } val nodes = TreeNode.wrapRows(query).toList() nodes shouldHaveSize 1 nodes.single().title shouldBeEqualTo "child1" SELECT tree_nodes.id, tree_nodes.title, tree_nodes.description, tree_nodes."depth", tree_nodes.parent_id FROM tree_nodes WHERE tree_nodes.id IN (SELECT sub.parent_id FROM tree_nodes sub WHERE sub.title LIKE 'grand%')
and ORM alike Database Access with Kotlin Exposed • Guide to the Kotlin Exposed • Kotlin Exposed: From Setup to Advanced Usage • Kotlin Exposed Book (Exposed 1.1.1) • Github: Exposed Workshop
Access • 7+/-2 thins you didn’t know about Exposed by Alexey Soshin • Using PostgreSQL as an Append-only Datastore with Kotlin and Exposed • PostgreSQL and Exposed - Kotlin database access
debop
takuyay0ne
matsuo_atsushi
akkeylab
higaki_program
manfredsteyer
ikezoemakoto
masuda220
fadis
yorifuji
.jpeg){kind=avatar}
arthur1
%20zoom.jpeg){kind=avatar}
avosalmon
k0kubun
philnash
mfonobong
tammyeverts
smashingmag
akademiya2063
moore
sstephenson
keithpitt
jonyablonski
elsirapls
trishagee
letsgokoyo
![INSERT val id1 = idTable.insertAndGetId { it[name] = "name-1" }](https://files.speakerdeck.com/presentations/b26f9d308ac54922b5d5804d044d7568/slide_40.jpg){kind=link}
![INSERT val id1 = idTable.insertAndGetId { it[name] = "name-1" }](https://files.speakerdeck.com/presentations/b26f9d308ac54922b5d5804d044d7568/slide_41.jpg){kind=link}
![INSERT val id1 = idTable.insertAndGetId { it[name] = "name-1" }](https://files.speakerdeck.com/presentations/b26f9d308ac54922b5d5804d044d7568/slide_42.jpg){kind=link}
