### Install SQLlin Dependencies via Gradle

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Configures the necessary sqllin-dsl, sqllin-driver, and sqllin-processor dependencies within a Kotlin Multiplatform build.gradle.kts file.

```kotlin
plugins {
    kotlin("multiplatform")
    kotlin("plugin.serialization")
    id("com.android.library")
    id("com.google.devtools.ksp")
}

val sqllinVersion = "x.x.x"

kotlin {
    sourceSets {
        val commonMain by getting {
            kotlin.srcDir("build/generated/ksp/metadata/commonMain/kotlin")
            dependencies {
                implementation("com.ctrip.kotlin:sqllin-dsl:$sqllinVersion")
                implementation("com.ctrip.kotlin:sqllin-driver:$sqllinVersion")
                implementation("org.jetbrains.kotlinx:kotlinx-serialization-core:1.9.0")
                implementation("org.jetbrains.kotlinx:kotlinx-coroutines-core:1.10.2")
            }
        }
    }
}

dependencies {
    add("kspCommonMainMetadata", "com.ctrip.kotlin:sqllin-processor:$sqllinVersion")
}
```

--------------------------------

### Initialize SQLlin Database Instance

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Demonstrates how to instantiate a Database object and configure platform-specific database paths using expect/actual patterns.

```kotlin
import com.ctrip.sqllin.dsl.Database

val database = Database(name = "Person.db", path = getGlobalPath(), version = 1)
```

```kotlin
import com.ctrip.sqllin.driver.DatabasePath

expect fun getGlobalDatabasePath(): DatabasePath
```

```kotlin
import android.content.Context
import com.ctrip.sqllin.driver.DatabasePath
import com.ctrip.sqllin.driver.toDatabasePath

actual fun getGlobalDatabasePath(): DatabasePath = applicationContext.toDatabasePath()
```

```kotlin
import com.ctrip.sqllin.driver.DatabasePath
import com.ctrip.sqllin.driver.toDatabasePath

actual fun getGlobalDatabasePath(): DatabasePath = (NSSearchPathForDirectoriesInDomains(
    NSDocumentDirectory, 
    NSUserDomainMask, true).firstOrNull() as? String ?: "
").toDatabasePath()
```

--------------------------------

### Configure Advanced Database Settings

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Provides an example of using DatabaseConfiguration to set advanced SQLite parameters like journal mode, synchronous mode, and custom schema creation.

```kotlin
import com.ctrip.sqllin.driver.DatabaseConfiguration
import com.ctrip.sqllin.dsl.Database

val database = Database(
    DatabaseConfiguration(
        name = "Person.db",
        path = getGlobalDatabasePath(),
        version = 1,
        isReadOnly = false,
        inMemory = false,
        journalMode = JournalMode.WAL,
        synchronousMode = SynchronousMode.NORMAL,
        busyTimeout = 5000,
        create = {
            it.execSQL("create table person (id integer primary key autoincrement, name text, age integer)")
        },
        upgrade = { databaseConnection, oldVersion, newVersion -> }
    )
)
```

--------------------------------

### Define Database Schema and Foreign Key Relationships in Kotlin

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Demonstrates how to define parent and child tables using @DBRow annotations and @References to enforce relational integrity. It includes examples of ON_DELETE_CASCADE and ON_DELETE_SET_NULL triggers.

```kotlin
import com.ctrip.sqllin.dsl.Database
import com.ctrip.sqllin.dsl.annotation.*
import kotlinx.serialization.Serializable

@DBRow
@Serializable
data class User(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @Unique val email: String,
    val name: String,
)

@DBRow
@Serializable
data class Order(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @References(tableName = "User", foreignKeys = ["id"], trigger = Trigger.ON_DELETE_CASCADE)
    val userId: Long,
    val amount: Double,
    val orderDate: String,
)

@DBRow
@Serializable
data class Post(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @References(tableName = "User", foreignKeys = ["id"], trigger = Trigger.ON_DELETE_SET_NULL)
    val authorId: Long?,
    val title: String,
    val content: String,
)
```

--------------------------------

### Implement Multiple Foreign Keys

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Shows how to define multiple foreign key constraints on a single table using either @ForeignKeyGroup or multiple @References annotations.

```kotlin
@DBRow
@Serializable
@ForeignKeyGroup(group = 0, tableName = "User", trigger = Trigger.ON_DELETE_CASCADE)
@ForeignKeyGroup(group = 1, tableName = "Product", trigger = Trigger.ON_DELETE_RESTRICT)
data class OrderItem(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @ForeignKey(group = 0, reference = "id") val userId: Long,
    @ForeignKey(group = 1, reference = "id") val productId: Long,
    val quantity: Int,
)

// Alternative using @References
@DBRow
@Serializable
data class OrderItemReferences(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @References(tableName = "User", foreignKeys = ["id"], trigger = Trigger.ON_DELETE_CASCADE)
    val userId: Long,
    @References(tableName = "Product", foreignKeys = ["id"], trigger = Trigger.ON_DELETE_RESTRICT)
    val productId: Long,
    val quantity: Int,
)
```

--------------------------------

### Configuring Column Default Values

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Shows the usage of the @Default annotation to set SQLite column defaults. Includes examples for strings, numbers, booleans, and SQL functions, as well as integration with foreign key triggers.

```kotlin
@DBRow
@Serializable
data class User(
	@PrimaryKey(isAutoincrement = true) val id: Long?,
	val name: String,
	@Default("'active'") val status: String,
	@Default("0") val loginCount: Int,
	@Default("1") val isEnabled: Boolean,
	@Default("CURRENT_TIMESTAMP") val createdAt: String,
)
```

```kotlin
@DBRow
@Serializable
data class Order(
	@PrimaryKey(isAutoincrement = true) val id: Long?,
	@References(
		tableName = "User",
		foreignKeys = ["id"],
		trigger = Trigger.ON_DELETE_SET_DEFAULT
	)
	@Default("0")
	val userId: Long,
	val amount: Double,
)
```

--------------------------------

### Define Column-Level Foreign Keys with @References

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Illustrates the recommended approach for defining simple single-column foreign keys using the @References annotation with cascade triggers.

```kotlin
@DBRow
@Serializable
data class Order(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @References(
        tableName = "User",
        foreignKeys = ["id"],
        trigger = Trigger.ON_DELETE_CASCADE
    )
    val userId: Long,
    val amount: Double,
    val orderDate: String,
)
```

--------------------------------

### Initialize Database and Enforce Foreign Keys

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Shows the initialization process for a SQLlin database, specifically highlighting the requirement to call PRAGMA_FOREIGN_KEYS(true) and the correct order of table creation.

```kotlin
fun setupDatabase() {
    database {
        PRAGMA_FOREIGN_KEYS(true)

        CREATE(UserTable)
        CREATE(OrderTable)
        CREATE(PostTable)

        val user = User(id = null, email = "alice@example.com", name = "Alice")
        UserTable INSERT user

        val order = Order(id = null, userId = 1L, amount = 99.99, orderDate = "2025-01-15")
        OrderTable INSERT order

        UserTable DELETE WHERE(UserTable.id EQ 1L)
    }
}
```

--------------------------------

### Define Named Foreign Key Constraints

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Illustrates how to provide a custom constraint name for foreign keys to improve error reporting and schema introspection.

```kotlin
@DBRow
@Serializable
data class Order(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @References(
        tableName = "User",
        foreignKeys = ["id"],
        trigger = Trigger.ON_DELETE_CASCADE,
        constraintName = "fk_order_user"
    )
    val userId: Long,
)
```

--------------------------------

### Configure Database with DSLDBConfiguration (Kotlin)

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Configures a database instance using DSLDBConfiguration, enabling type-safe SQL DSL for create and upgrade operations. It specifies database name, path, version, and various modes. Dependencies include sqllin-driver and sqllin-dsl.

```kotlin
import com.ctrip.sqllin.driver.DSLDBConfiguration
import com.ctrip.sqllin.dsl.Database
import com.ctrip.sqllin.dsl.JournalMode
import com.ctrip.sqllin.dsl.SynchronousMode

val database = Database(
    DSLDBConfiguration(
        name = "Person.db",
        path = getGlobalDatabasePath(),
        version = 1,
        isReadOnly = false,
        inMemory = false,
        journalMode = JournalMode.WAL,
        synchronousMode = SynchronousMode.NORMAL,
        busyTimeout = 5000,
        lookasideSlotSize = 0,
        lookasideSlotCount = 0,
        create = {
            // Use type-safe DSL instead of raw SQL
            CREATE(PersonTable)
        },
        upgrade = {
            oldVersion, newVersion ->
            when (oldVersion) {
                1 -> {
                    // Example: Add a new column in version 2
                    PersonTable ALERT_ADD_COLUMN PersonTable.email
                }
            }
        }
    )
)
```

--------------------------------

### Define Composite Foreign Keys with @ForeignKeyGroup

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Demonstrates how to handle complex relationships involving multiple columns by grouping foreign keys with @ForeignKeyGroup and @ForeignKey annotations.

```kotlin
@DBRow
@Serializable
@ForeignKeyGroup(
    group = 0,
    tableName = "Product",
    trigger = Trigger.ON_DELETE_CASCADE,
    constraintName = "fk_product"
)
data class OrderItem(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @ForeignKey(group = 0, reference = "categoryId")
    val productCategory: Int,
    @ForeignKey(group = 0, reference = "productCode")
    val productCode: String,
    val quantity: Int,
)
```

--------------------------------

### Using Typealiases in Database Models

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Illustrates how SQLlin resolves recursive typealiases for supported Kotlin types within @DBRow data classes.

```kotlin
typealias UserId = Long
typealias Price = Double
typealias AccountId = UserId

@DBRow
@Serializable
data class Product(
	@PrimaryKey val id: UserId,
	val name: String,
	val price: Price,
	val ownerId: AccountId,
)
```

--------------------------------

### Combining Multiple Constraints in Kotlin

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Demonstrates how to apply multiple constraint annotations such as @PrimaryKey, @Unique, and @CollateNoCase to a single property within a @DBRow data class.

```kotlin
@DBRow
@Serializable
data class Product(
	@PrimaryKey(isAutoincrement = true) val id: Long?,
	@Unique @CollateNoCase val code: String,
	val name: String,
	val price: Double,
)
```

--------------------------------

### Close Database Instance (Kotlin)

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Demonstrates how to close a database instance when its lifecycle ends, typically in an onDestroy method. This is crucial for releasing resources associated with the database connection.

```kotlin
override fun onDestroy() {
    database.close()
}
```

--------------------------------

### Enable Foreign Key Enforcement in SQLlin

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Shows how to enable SQLite foreign key constraints, which are disabled by default, using the PRAGMA_FOREIGN_KEYS command within a database configuration block.

```kotlin
database {
    PRAGMA_FOREIGN_KEYS(true)

    CREATE(UserTable)
    CREATE(OrderTable)
}
```

--------------------------------

### Execute UNION and UNION ALL Queries in Kotlin

Source: https://context7.com/ctripcorp/sqllin/llms.txt

Shows how to combine multiple SELECT statements using UNION and UNION ALL operators. Includes examples of nested queries and result retrieval.

```kotlin
import com.ctrip.sqllin.dsl.sql.statement.SelectStatement

fun unionExample() {
    lateinit var selectStatement: SelectStatement<Person>
    database {
        PersonTable { table ->
            selectStatement = UNION {
                table SELECT WHERE (age GTE 5)
                table SELECT WHERE (length(name) LTE 8)
            }
            selectStatement = UNION_ALL {
                table SELECT WHERE (age LTE 3)
                table SELECT WHERE (name EQ "Tom")
            }
        }
    }
    selectStatement.getResults().forEach { person ->
        println(person)
    }
}
```

--------------------------------

### Define Delete Triggers in SQLlin

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Demonstrates the use of @References with various trigger types like CASCADE, SET_NULL, RESTRICT, and SET_DEFAULT to manage relational integrity during delete operations.

```kotlin
@DBRow
@Serializable
data class Order(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @References(tableName = "User", foreignKeys = ["id"], trigger = Trigger.ON_DELETE_CASCADE)
    val userId: Long,
    val amount: Double,
)

@DBRow
@Serializable
data class Post(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @References(tableName = "User", foreignKeys = ["id"], trigger = Trigger.ON_DELETE_SET_NULL)
    val authorId: Long?,
    val content: String,
)

@DBRow
@Serializable
data class OrderItem(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @References(tableName = "Order", foreignKeys = ["id"], trigger = Trigger.ON_DELETE_RESTRICT)
    val orderId: Long,
    val productId: Long,
)

@DBRow
@Serializable
data class Comment(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @References(tableName = "User", foreignKeys = ["id"], trigger = Trigger.ON_DELETE_SET_DEFAULT)
    val userId: Long = 0L,
    val content: String,
)
```

--------------------------------

### Define Database Schema with Enums in Kotlin

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Demonstrates how to use the @DBRow annotation to define a database table and map Kotlin enums, which are stored as integer ordinals by default.

```kotlin
enum class UserStatus {
    ACTIVE, INACTIVE, SUSPENDED, BANNED
}

@DBRow
@Serializable
data class User(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    val username: String,
    val status: UserStatus,
    val priority: Priority?,
)
```

--------------------------------

### Install sqllin-driver via Maven in Gradle

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-driver/README.md

This snippet shows how to add the sqllin-driver dependency to your Gradle build file for Kotlin projects. It specifies the dependency for the common main source set.

```kotlin
kotlin {
    // ......
    sourceSets {
        val commonMain by getting {
            dependencies {
                // sqllin-driver
                implementation("com.ctrip.kotlin:sqllin-driver:$sqllinVersion")
            }
        }
        // ......
    }
}
```

--------------------------------

### Kotlin Multiplatform Project Setup with SQLlin

Source: https://context7.com/ctripcorp/sqllin/llms.txt

Configures a Kotlin Multiplatform project to use SQLlin by adding necessary plugins and dependencies, including the KSP processor for code generation. Ensure you have the correct versions for sqllin and kotlinx.serialization.

```kotlin
plugins {
    kotlin("multiplatform")
    kotlin("plugin.serialization")
    id("com.android.library")
    id("com.google.devtools.ksp")
}

val sqllinVersion = "2.2.0"

kotlin {
    sourceSets {
        val commonMain by getting {
            kotlin.srcDir("build/generated/ksp/metadata/commonMain/kotlin")
            dependencies {
                implementation("com.ctrip.kotlin:sqllin-dsl:$sqllinVersion")
                implementation("com.ctrip.kotlin:sqllin-driver:$sqllinVersion")
                implementation("org.jetbrains.kotlinx:kotlinx-serialization-core:1.9.0")
                implementation("org.jetbrains.kotlinx:kotlinx-coroutines-core:1.10.2")
            }
        }
    }
}

dependencies {
    add("kspCommonMainMetadata", "com.ctrip.kotlin:sqllin-processor:$sqllinVersion")
}
```

--------------------------------

### Manage Table Structure with DDL Operations in Kotlin

Source: https://context7.com/ctripcorp/sqllin/llms.txt

Provides examples for creating, dropping, and altering database tables, including adding columns, renaming entities, and dropping columns using type-safe DSL syntax.

```kotlin
fun tableOperationsExample() {
    database {
        CREATE(PersonTable)
        DROP(PersonTable)
        PersonTable ALERT_ADD_COLUMN PersonTable.email
        PersonTable ALERT_RENAME_TABLE_TO NewPersonTable
        PersonTable.RENAME_COLUMN(PersonTable.age, PersonTable.yearsOld)
        PersonTable DROP_COLUMN PersonTable.email
    }
}
```

--------------------------------

### Add Column to Table with SQLlin DSL

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/modify-database-and-transaction.md

Shows how to add a new column to an existing table using SQLlin's ALERT_ADD_COLUMN operation. The example defines a data class with an additional nullable email column and applies the change.

```kotlin
@DBRow
@Serializable
data class Person(
    val name: String,
    val age: Int,
    val email: String? = null,  // New column
)

fun sample() {
    database {
        PersonTable ALERT_ADD_COLUMN PersonTable.email
    }
}
```

--------------------------------

### Complex SELECT Statement with Multiple Clauses in Kotlin

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/query.md

Shows an example of a complex SELECT statement in SQLlin that combines multiple clauses, including WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, and OFFSET. SQLlin enforces the correct order of clause application.

```kotlin
fun sample() {
    lateinit var selectStatement: SelectStatement<Person>
    database {
        PersonTable { table ->
             selectStatement = table SELECT WHERE (age LTE 5) GROUP_BY age HAVING (upper(name) EQ "TOM") ORDER_BY (age to DESC) LIMIT 2 OFFSET 1
        }
    }
    selectStatement.getResult().forEach { person ->
        println(person)
    }
}
```

--------------------------------

### Define Database Entity with @DBRow and @Serializable (Kotlin)

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Defines a data class 'Person' as a database entity using @DBRow and @Serializable annotations. The @DBRow annotation specifies the table name, and @Serializable is required for kotlinx.serialization integration. Property names must match column names.

```kotlin
import com.ctrip.sqllin.dsl.annotation.DBRow
import kotlinx.serialization.Serializable

@DBRow(tableName = "person")
@Serializable
data class Person(
    val name: String,
    val age: Int,
)
```

--------------------------------

### Post-Query Aggregation with Kotlin Collections API

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/sql-functions.md

Provides an alternative approach for aggregating query results when SQL functions cannot be used directly in SELECT statements. This example utilizes Kotlin's Collections API to perform operations like max, min, and count on the fetched data. It's a workaround for the current limitation of SQLlin not supporting functions after the SELECT keyword.

```kotlin
fun sample() {
    lateinit var selectStatement: SelectStatement<Person>
    database {
        PersonTable { table ->
             selectStatement = table SELECT X
        }
    }
    // Get the max value
    selectStatement.getResult().maxOrNull()
    // Get the min value
    selectStatement.getResult().minOrNull()
    // Get the count of query results
    selectStatement.getResult().count()
    // ......
```

--------------------------------

### Kotlin: Use Built-in SQL Functions in Conditions

Source: https://context7.com/ctripcorp/sqllin/llms.txt

Demonstrates the use of built-in SQL functions within conditions for various operations such as aggregate, numeric, and string manipulations. Examples include count, abs, upper, and length.

```kotlin
import com.ctrip.sqllin.dsl.sql.clause.*

fun sqlFunctionsExample() {
    database {
        PersonTable { table ->
            // Aggregate functions: count, max, min, avg, sum, group_concat
            table SELECT GROUP_BY (name) HAVING (count(X) > 2)

            // Numeric functions: abs, round, random, sign
            table SELECT WHERE (abs(age) LTE 5)

            // String functions: upper, lower, length, substr, trim, ltrim, rtrim, replace, instr, printf
            table SELECT WHERE (upper(name) EQ "TOM")
            table SELECT WHERE (length(name) GTE 3)
            table SELECT WHERE (lower(name) LIKE "%tom%")
        }
    }
}
```

--------------------------------

### Kotlin: Perform DELETE Operations with WHERE Clause

Source: https://context7.com/ctripcorp/sqllin/llms.txt

Shows how to delete rows from a table using the WHERE clause with various SQL operators for condition specification. It includes examples for single, complex conditions, and deleting all rows.

```kotlin
import com.ctrip.sqllin.dsl.sql.clause.*

fun deleteExample() {
    database {
        PersonTable { table ->
            // Delete with WHERE condition
            table DELETE WHERE (age GTE 10)

            // Delete with complex conditions
            table DELETE WHERE ((age GTE 10) OR (name NEQ "Jerry"))

            // Delete all rows
            table DELETE X
        }
    }
}
```

--------------------------------

### Kotlin: Utilize SQL Operators for WHERE Conditions

Source: https://context7.com/ctripcorp/sqllin/llms.txt

Provides a reference for SQLlin's type-safe operators used in constructing WHERE conditions, which directly map to SQL operators. Examples include equality, comparison, BETWEEN, IN, LIKE, GLOB, and logical operators.

```kotlin
import com.ctrip.sqllin.dsl.sql.clause.*

fun operatorsExample() {
    database {
        PersonTable { table ->
            // Equality: EQ (=), NEQ (!=)
            table SELECT WHERE (name EQ "Tom")
            table SELECT WHERE (name NEQ "Jerry")

            // Comparison: LT (<), LTE (<=), GT (>), GTE (>=)
            table SELECT WHERE (age LT 10)
            table SELECT WHERE (age LTE 10)
            table SELECT WHERE (age GT 5)
            table SELECT WHERE (age GTE 5)

            // BETWEEN
            table SELECT WHERE (age BETWEEN (5 to 15))

            // IN
            table SELECT WHERE (name IN listOf("Tom", "Jerry", "Jack"))

            // LIKE and GLOB
            table SELECT WHERE (name LIKE "%om")
            table SELECT WHERE (name GLOB "*om")

            // Logical: AND, OR
            table SELECT WHERE ((age GTE 5) AND (name EQ "Tom"))
            table SELECT WHERE ((age LTE 3) OR (name EQ "Jack"))
        }
    }
}
```

--------------------------------

### Rename Column with SQLlin DSL

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/modify-database-and-transaction.md

Explains how to rename a column within a table using SQLlin's RENAME_COLUMN operation. Examples show renaming using type-safe ClauseElement references and using the old column name as a String.

```kotlin
fun sample() {
    database {
        // Using ClauseElement references (type-safe)
        PersonTable.RENAME_COLUMN(PersonTable.age, PersonTable.yearsOld)

        // Or using String for old column name
        PersonTable.RENAME_COLUMN("age", PersonTable.yearsOld)
    }
}
```

--------------------------------

### Initialize Database and Table Access

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/modify-database-and-transaction.md

Demonstrates how to initialize a Database instance and access a generated table within a DatabaseScope.

```kotlin
private val database = Database(name = "Person.db", path = getGlobalPath(), version = 1)

fun sample() {
    database {
        PersonTable { table ->
            // Write your SQL statements...
        }
    }
}
```

--------------------------------

### Open and Close SQLite Database with sqllin-driver

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-driver/README.md

Demonstrates the basic usage of opening and closing an SQLite database using sqllin-driver. It includes configuration options for database creation, versioning, and operational parameters.

```kotlin
// Open SQLite
val databaseConnection = openDatabase(
    DatabaseConfiguration(
        name = "Person.db",
        path = getGlobalDatabasePath(),
        version = 1,
        isReadOnly = false,
        inMemory = false,
        journalMode = JournalMode.WAL,
        synchronousMode = SynchronousMode.NORMAL,
        busyTimeout = 5000,
        lookasideSlotSize = 0,
        lookasideSlotCount = 0,
        create = {
            it.execSQL("create table person (id integer primary key autoincrement, name text, age integer)")
        },
        upgrade = { databaseConnection, oldVersion, newVersion -> } 
    )
)
// Close SQLite
databaseConnection.close()
```

--------------------------------

### Sqllin CRUD Operations in Kotlin

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-driver/README.md

Demonstrates INSERT, DELETE, UPDATE, and SELECT statements using Sqllin. Supports parameter binding with Array<Any?>. Includes table creation and iterating through query results.

```kotlin
import me.sqllin.common.CommonCursor

// Assuming databaseConnection and SQL object are defined elsewhere

// INSERT
databaseConnection.executeInsert(SQL.INSERT, arrayOf(20, "Tom"))

// DELETE
databaseConnection.executeUpdateDelete(SQL.DELETE, arrayOf(20, "Tom"))

// UPDATE
databaseConnection.executeUpdateDelete(SQL.UPDATE, arrayOf(20, "Tom"))

// SELECT
val cursor: CommonCursor = databaseConnection.query(SQL.QUERY, arrayOf(20, "Tom"))
cursor.forEachRow { index -> // Index of rows
    val age: Int = cursor.getInt("age")
    val name: String = cursor.getString("name")
    // Process age and name
}

// Create table and others
databaseConnection.execSQL(SQL.CREATE_TABLE)
```

--------------------------------

### Create and Configure SQLlin Database Instance

Source: https://context7.com/ctripcorp/sqllin/llms.txt

Creates a SQLlin Database instance with basic or advanced configurations using DSLDBConfiguration. Supports options like journal mode, synchronous mode, schema creation, and upgrade callbacks. Remember to close the database when done.

```kotlin
import com.ctrip.sqllin.dsl.Database
import com.ctrip.sqllin.dsl.DSLDBConfiguration
import com.ctrip.sqllin.driver.DatabasePath
import com.ctrip.sqllin.driver.JournalMode
import com.ctrip.sqllin.driver.SynchronousMode

// Simple database creation
val database = Database(name = "Person.db", path = getGlobalDatabasePath(), version = 1)

// Advanced configuration with DSLDBConfiguration
val database = Database(
    DSLDBConfiguration(
        name = "Person.db",
        path = getGlobalDatabasePath(),
        version = 2,
        isReadOnly = false,
        inMemory = false,
        journalMode = JournalMode.WAL,
        synchronousMode = SynchronousMode.NORMAL,
        busyTimeout = 5000,
        create = {
            CREATE(PersonTable)
            CREATE(TranscriptTable)
        },
        upgrade = { oldVersion, newVersion ->
            when (oldVersion) {
                1 -> {
                    PersonTable ALERT_ADD_COLUMN PersonTable.email
                }
            }
        }
    ),
    enableSimpleSQLLog = true
)

// Close database when done
database.close()
```

--------------------------------

### Kotlin: Construct SELECT Queries with Full Clause Support

Source: https://context7.com/ctripcorp/sqllin/llms.txt

Illustrates how to build SELECT queries with comprehensive clause support, including WHERE, ORDER BY, LIMIT, GROUP BY, HAVING, and OFFSET. It demonstrates selecting all, with conditions, ordering, limiting, and complex combined clauses.

```kotlin
import com.ctrip.sqllin.dsl.sql.clause.*
import com.ctrip.sqllin.dsl.sql.clause.OrderByWay.DESC
import com.ctrip.sqllin.dsl.sql.statement.SelectStatement

fun selectExample() {
    lateinit var selectStatement: SelectStatement<Person>

    database {
        PersonTable { table ->
            // Select all
            selectStatement = table SELECT X

            // Select with WHERE
            selectStatement = table SELECT WHERE (age LTE 5)

            // Select with ORDER BY
            selectStatement = table SELECT ORDER_BY (age to DESC)

            // Select with LIMIT
            selectStatement = table SELECT LIMIT 10

            // Complex query with multiple clauses
            selectStatement = table SELECT WHERE (age LTE 5) GROUP_BY age HAVING (upper(name) EQ "TOM") ORDER_BY (age to DESC) LIMIT 2 OFFSET 1
        }
    }

    // Get results after database block
    selectStatement.getResults().forEach { person ->
        println("Name: ${person.name}, Age: ${person.age}")
    }
}
```

--------------------------------

### Define Entity with Non-Nullable Primary Key (Kotlin)

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Defines a 'User' data class with a non-nullable String primary key 'username'. The @PrimaryKey annotation is used without autoIncrement. The property must be provided a unique value upon insertion.

```kotlin
import com.ctrip.sqllin.dsl.annotation.DBRow
import com.ctrip.sqllin.dsl.annotation.PrimaryKey
import kotlinx.serialization.Serializable

@DBRow
@Serializable
data class User(
    @PrimaryKey
    val username: String,  // Non-nullable, user-provided primary key
    val email: String,
)
```

--------------------------------

### Define Entity with Single Auto-Increment Primary Key (Kotlin)

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Defines a 'Person' data class with a single auto-incrementing primary key 'id' of type Long?. The @PrimaryKey annotation with autoIncrement = true is used. The property must be nullable Long? for auto-increment.

```kotlin
import com.ctrip.sqllin.dsl.annotation.DBRow
import com.ctrip.sqllin.dsl.annotation.PrimaryKey
import kotlinx.serialization.Serializable

@DBRow
@Serializable
data class Person(
    @PrimaryKey(autoIncrement = true)
    val id: Long? = null,  // Auto-incrementing primary key
    val name: String,
    val age: Int,
)
```

--------------------------------

### Define Composite Primary Key with @CompositePrimaryKey (Kotlin)

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Use the @CompositePrimaryKey annotation to define a primary key composed of multiple columns in a table. All annotated properties must be non-nullable, and you cannot mix @PrimaryKey and @CompositePrimaryKey in the same class. The combination of all @CompositePrimaryKey properties forms the table's composite primary key.

```kotlin
import com.ctrip.sqllin.dsl.annotation.DBRow
import com.ctrip.sqllin.dsl.annotation.CompositePrimaryKey
import kotlinx.serialization.Serializable

@DBRow
@Serializable
data class Enrollment(
    @CompositePrimaryKey
    val studentId: Long,
    @CompositePrimaryKey
    val courseId: Long,
    val enrollmentDate: String,
)
```

--------------------------------

### Enable Case-Insensitive Text Comparison with @CollateNoCase (Kotlin)

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

The @CollateNoCase annotation modifies string comparisons to be case-insensitive, meaning 'ABC' will be considered equal to 'abc'. It also affects ORDER BY clauses, sorting text case-insensitively, and indexes on the column will also be case-insensitive. This annotation can only be applied to String or Char properties.

```kotlin
import com.ctrip.sqllin.dsl.annotation.DBRow
import com.ctrip.sqllin.dsl.annotation.PrimaryKey
import com.ctrip.sqllin.dsl.annotation.NoCase
import com.ctrip.sqllin.dsl.annotation.Unique
import kotlinx.serialization.Serializable

@DBRow
@Serializable
data class User(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @CollateNoCase @Unique val email: String,  // Case-insensitive unique email
    @CollateNoCase val username: String,        // Case-insensitive username
    val bio: String,
)
// Generated SQL: CREATE TABLE User(
//   id INTEGER PRIMARY KEY AUTOINCREMENT,
//   email TEXT COLLATE NOCASE UNIQUE,
//   username TEXT COLLATE NOCASE,
//   bio TEXT
// )
```

--------------------------------

### Enforce Multi-Column Uniqueness with @CompositeUnique (Kotlin)

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

Use @CompositeUnique to enforce uniqueness on the combination of multiple columns. Properties can be grouped into different unique constraint groups by specifying group numbers. If no group number is specified, it defaults to group 0. All properties within the same group number are combined into a single composite unique constraint.

```kotlin
import com.ctrip.sqllin.dsl.annotation.DBRow
import com.ctrip.sqllin.dsl.annotation.PrimaryKey
import com.ctrip.sqllin.dsl.annotation.CompositeUnique
import kotlinx.serialization.Serializable

@DBRow
@Serializable
data class Enrollment(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @CompositeUnique(0) val studentId: Int,
    @CompositeUnique(0) val courseId: Int,
    val enrollmentDate: String,
)
// Generated SQL: CREATE TABLE Enrollment(
//   id INTEGER PRIMARY KEY AUTOINCREMENT,
//   studentId INT,
//   courseId INT,
//   enrollmentDate TEXT,
//   UNIQUE(studentId, courseId)
// )
// A student cannot enroll in the same course twice

@DBRow
@Serializable
data class Event(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @CompositeUnique(0, 1) val userId: Int,     // Part of groups 0 and 1
    @CompositeUnique(0) val eventType: String,  // Part of group 0
    @CompositeUnique(1) val timestamp: Long,    // Part of group 1
)
// Generated SQL: CREATE TABLE Event(
//   id INTEGER PRIMARY KEY AUTOINCREMENT,
//   userId INT,
//   eventType TEXT,
//   timestamp BIGINT,
//   UNIQUE(userId, eventType),    // Group 0: userId + eventType
//   UNIQUE(userId, timestamp)     // Group 1: userId + timestamp
// )
```

--------------------------------

### Basic SELECT Statement in Kotlin

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/query.md

Demonstrates the simplest form of a SELECT statement in SQLlin, retrieving all data from a table. The 'X' placeholder signifies no additional clauses. The result is a SelectStatement<T> which is executed outside the database block to deserialize results.

```kotlin
fun sample() {
    lateinit var selectStatement: SelectStatement<Person>
    database {
        PersonTable { table ->
             selectStatement = table SELECT X
        }
    }
    selectStatement.getResult().forEach { person ->
        println(person)
    }
}
```

--------------------------------

### Enforce Single Column Uniqueness with @Unique (Kotlin)

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/getting-start.md

The @Unique annotation ensures that all values in a specific column are unique across all rows in the table. It can be applied to multiple columns within the same class. Note that multiple NULL values are permitted in a UNIQUE column, as NULL is not considered equal to NULL in SQL. To prevent NULLs, use a non-nullable type.

```kotlin
import com.ctrip.sqllin.dsl.annotation.DBRow
import com.ctrip.sqllin.dsl.annotation.PrimaryKey
import com.ctrip.sqllin.dsl.annotation.Unique
import kotlinx.serialization.Serializable

@DBRow
@Serializable
data class User(
    @PrimaryKey(isAutoincrement = true) val id: Long?,
    @Unique val email: String,        // Each email must be unique
    @Unique val username: String,     // Each username must be unique
    val displayName: String,
)
// Generated SQL: CREATE TABLE User(
//   id INTEGER PRIMARY KEY AUTOINCREMENT,
//   email TEXT UNIQUE,
//   username TEXT UNIQUE,
//   displayName TEXT
// )
```

--------------------------------

### Create Table with SQLlin DSL

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/modify-database-and-transaction.md

Demonstrates how to create a database table from a data class definition using SQLlin's type-safe DSL. It supports automatic mapping of data types, NOT NULL constraints, PRIMARY KEY, and AUTOINCREMENT.

```kotlin
import com.ctrip.sqllin.dsl.annotation.DBRow
import com.ctrip.sqllin.dsl.annotation.PrimaryKey
import kotlinx.serialization.Serializable

@DBRow
@Serializable
data class Person(
    @PrimaryKey(autoIncrement = true)
    val id: Long = 0,
    val name: String,
    val age: Int,
)

fun sample() {
    database {
        // Create table using infix notation
        CREATE(PersonTable)

        // Or using extension function
        PersonTable.CREATE()
    }
}
```

--------------------------------

### Database Operations with DSLDBConfiguration

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/modify-database-and-transaction.md

Illustrates using SQLlin's table structure operations (CREATE, ALERT_ADD_COLUMN) within the `create` and `upgrade` callbacks of `DSLDBConfiguration`. This is useful for initializing and updating the database schema.

```kotlin
import com.ctrip.sqllin.dsl.DSLDBConfiguration

val database = Database(
    DSLDBConfiguration(
        name = "Person.db",
        path = getGlobalDatabasePath(),
        version = 2,
        create = {
            CREATE(PersonTable)
            CREATE(AddressTable)
        },
        upgrade = { oldVersion, newVersion ->
            when (oldVersion) {
                1 -> {
                    // Upgrade from version 1 to 2
                    PersonTable ALERT_ADD_COLUMN PersonTable.email
                    CREATE(AddressTable)
                }
            }
        }
    )
)
```

--------------------------------

### SQLlin DSL for Database Operations in Kotlin

Source: https://github.com/ctripcorp/sqllin/blob/main/README.md

Demonstrates how to use SQLlin's DSL to perform INSERT, UPDATE, DELETE, and SELECT operations on a SQLite database within Kotlin Multiplatform code. It shows object-to-database and database-to-object mapping.

```kotlin
private val db by lazy { Database(name = "person.db", path = path, version = 1) }

fun sample() {
    val tom = Person(age = 4, name = "Tom")
    val jerry = Person(age = 3, name = "Jerry")
    val jack = Person(age = 8, name = "Jack")
    val selectStatement: SelectStatement<Person> = db {
        PersonTable { table ->
            table INSERT listOf(tom, jerry, jack)
            table UPDATE SET { age = 5; name = "Tom" } WHERE ((age LTE 5) AND (name NEQ "Tom"))
            table DELETE WHERE ((age GTE 10) OR (name NEQ "Jerry"))
            table SELECT WHERE (age LTE 5) GROUP_BY age HAVING (upper(name) EQ "TOM") ORDER_BY (age to DESC) LIMIT 2 OFFSET 1
        }
    }
    selectStatement.getResult().forEach { person ->
        println(person.name)
    }
}
```

--------------------------------

### Perform UNION and UNION ALL operations in SQLlin

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/advanced-query.md

Demonstrates how to use the UNION and UNION_ALL higher-order functions to merge multiple SELECT statements. Requires at least two statements within the block and supports nesting for complex query logic.

```kotlin
fun sample() {
    lateinit var selectStatement: SelectStatement<Person>
    database {
        PersonTable { table ->
            selectStatement = UNION {
                table SELECT WHERE (age GTE 5)
                UNION_ALL {
                    table SELECT WHERE (length(name) LTE 8)
                    table SELECT WHERE (name EQ "Tom")
                }
            }
        }
    }
}
```

--------------------------------

### SELECT Statements with Single Clauses in Kotlin

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/query.md

Illustrates how to use individual SQL clauses like WHERE, ORDER BY, LIMIT, and GROUP BY with SELECT statements in SQLlin. Each clause modifies the query to filter, sort, limit, or group the results.

```kotlin
fun sample() {
    database {
        PersonTable { table ->
            table SELECT WHERE(age LTE 5)
            table SELECT ORDER_BY(age to DESC)
            table SELECT ORDER_BY(age)
            table SELECT LIMIT(3)
            table SELECT GROUP_BY(name)
        }
    }
}
```

--------------------------------

### Execute Inner Join operations

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/advanced-query.md

Demonstrates various INNER_JOIN configurations including USING, ON clauses, and NATURAL_INNER_JOIN. These operations allow for relational data retrieval between defined tables.

```kotlin
fun joinSample() {
    db {
        PersonTable { table ->
            table SELECT INNER_JOIN<Student>(TranscriptTable) USING name
            table SELECT NATURAL_INNER_JOIN<Student>(TranscriptTable)
            table SELECT INNER_JOIN<CrossJoinStudent>(TranscriptTable) ON (name EQ TranscriptTable.name)
        }
    }
}
```

--------------------------------

### Low-Level SQLite Operations with sqllin-driver in Kotlin

Source: https://context7.com/ctripcorp/sqllin/llms.txt

Shows how to use the `sqllin-driver` for direct, low-level SQLite operations in Kotlin. This is useful for scenarios requiring fine-grained control over database connections and SQL execution. It involves opening a database connection with specific configurations and then executing raw SQL statements for insert, update, delete, and select operations.

```kotlin
import com.ctrip.sqllin.driver.*

fun driverExample() {
    val databaseConnection = openDatabase(
        DatabaseConfiguration(
            name = "Person.db",
            path = getGlobalDatabasePath(),
            version = 1,
            isReadOnly = false,
            journalMode = JournalMode.WAL,
            synchronousMode = SynchronousMode.NORMAL,
            busyTimeout = 5000,
            create = {
                it.execSQL("CREATE TABLE person (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER)")
            },
            upgrade = { connection, oldVersion, newVersion -> } // Placeholder for upgrade logic
        )
    )

    // INSERT
    databaseConnection.executeInsert("INSERT INTO person (name, age) VALUES (?, ?)", arrayOf("Tom", 20))

    // UPDATE
    databaseConnection.executeUpdateDelete("UPDATE person SET age = ? WHERE name = ?", arrayOf(25, "Tom"))

    // DELETE
    databaseConnection.executeUpdateDelete("DELETE FROM person WHERE age > ?", arrayOf(30))

    // SELECT
    val cursor: CommonCursor = databaseConnection.query("SELECT * FROM person WHERE age < ?", arrayOf(30))
    cursor.forEachRow { index ->
        val name: String = cursor.getString("name")
        val age: Int = cursor.getInt("age")
        println("Row $index: $name, $age")
    }

    // Execute arbitrary SQL
    databaseConnection.execSQL("CREATE INDEX idx_person_name ON person(name)")

    databaseConnection.close()
}
```

--------------------------------

### Execute Cross Join operations

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/advanced-query.md

Shows how to perform a CROSS_JOIN between tables. The result type is specified via a generic type parameter, and it requires that no column names overlap between the joined tables.

```kotlin
fun joinSample() {
    db {
        PersonTable { table ->
            table SELECT CROSS_JOIN<CrossJoinStudent>(TranscriptTable)
        }
    }
}
```

--------------------------------

### Define Foreign Key Constraints with Referential Actions in Kotlin

Source: https://context7.com/ctripcorp/sqllin/llms.txt

Demonstrates how to define foreign key relationships with referential actions (e.g., ON_DELETE_CASCADE) using SQLlin's DSL. This ensures data integrity by specifying behavior when referenced rows are deleted or updated. It requires the `sqllin-dsl` dependency.

```kotlin
import com.ctrip.sqllin.dsl.annotation.*

@DBRow
@Serializable
data class User(
    @PrimaryKey(autoIncrement = true) val id: Long?,
    @Unique val email: String,
    val name: String,
)

@DBRow
@Serializable
data class Order(
    @PrimaryKey(autoIncrement = true) val id: Long?,
    @References(
        tableName = "User",
        foreignKeys = ["id"],
        trigger = Trigger.ON_DELETE_CASCADE
    )
    val userId: Long,
    val amount: Double,
)

fun foreignKeyExample() {
    database {
        // Enable foreign key enforcement (required)
        PRAGMA_FOREIGN_KEYS(true)

        // Create parent table first
        CREATE(UserTable)

        // Then create child table with foreign key
        CREATE(OrderTable)

        // Insert data
        UserTable INSERT User(id = null, email = "alice@example.com", name = "Alice")
        OrderTable INSERT Order(id = null, userId = 1L, amount = 99.99)

        // Deleting user will cascade delete their orders
        UserTable DELETE WHERE (UserTable.id EQ 1L)
    }
}
```

--------------------------------

### Perform INSERT Operations

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/modify-database-and-transaction.md

Shows how to insert single objects or lists of objects into a database table using the INSERT command.

```kotlin
database {
    PersonTable { table ->
        table INSERT Person(age = 4, name = "Tom")
        table INSERT listOf(
            Person(age = 10, name = "Nick"),
            Person(age = 3, name = "Jerry"),
            Person(age = 8, name = "Jack"),
        )
    }
}
```

--------------------------------

### Drop Table with SQLlin DSL

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/modify-database-and-transaction.md

Illustrates how to permanently remove a table and all its data from the database using SQLlin's DROP operation. This is a destructive operation and should be used with caution.

```kotlin
fun sample() {
    database {
        // Drop table using infix notation
        DROP(PersonTable)

        // Or using extension function
        PersonTable.DROP()
    }
}
```

--------------------------------

### Kotlin: Perform UPDATE Operations with SET and WHERE Clauses

Source: https://context7.com/ctripcorp/sqllin/llms.txt

Demonstrates how to update rows in a table using the SET clause for specifying new values and the WHERE clause for targeted modifications. It covers single and multiple conditions, as well as updating all rows.

```kotlin
import com.ctrip.sqllin.dsl.sql.clause.*

fun updateExample() {
    database {
        PersonTable { table ->
            // Update with WHERE condition
            table UPDATE SET { age = 5; name = "Tommy" } WHERE (name EQ "Tom")

            // Update with multiple conditions
            table UPDATE SET { status = "inactive" } WHERE ((age LTE 5) AND (name NEQ "Jerry"))

            // Update all rows (use with caution)
            table UPDATE SET { status = "pending" }
        }
    }
}
```

--------------------------------

### Using SQL Functions in Conditions (Kotlin)

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/sql-functions.md

Demonstrates how to use SQL functions like 'abs' and 'count' within WHERE and HAVING clauses in SQLlin. This showcases the library's ability to translate these function calls into SQL conditions. It assumes the 'sqllin-processor' is used for generating ClauseElements.

```kotlin
fun sample() {
    database {
        PersonTable { table ->
             table SELECT WHERE(abs(age) LTE 5)
             table SELECT GROUP_BY(name) HAVING (count(X) > 2)
        }
    }
}
```

--------------------------------

### Perform Relational JOIN Operations in Kotlin

Source: https://context7.com/ctripcorp/sqllin/llms.txt

Illustrates various JOIN types including CROSS, INNER, and LEFT OUTER joins using the SQLLin DSL. It supports both USING and ON clauses for flexible relational mapping between tables.

```kotlin
@DBRow("transcript")
@Serializable
data class Transcript(
    @PrimaryKey val id: Long?,
    val name: String?,
    val math: Int,
    val english: Int,
)

@Serializable
data class Student(
    val name: String?,
    val age: Int?,
    val math: Int,
    val english: Int,
)

fun joinExample() {
    database {
        PersonTable { table ->
            table SELECT CROSS_JOIN<Student>(TranscriptTable)
            table SELECT INNER_JOIN<Student>(TranscriptTable) USING name
            table SELECT NATURAL_JOIN<Student>(TranscriptTable)
            table SELECT INNER_JOIN<Student>(TranscriptTable) ON (name EQ TranscriptTable.name)
            table SELECT LEFT_OUTER_JOIN<Student>(TranscriptTable) USING name
            table SELECT NATURAL_LEFT_OUTER_JOIN<Student>(TranscriptTable)
        }
    }
}
```

--------------------------------

### Execute Database Transactions

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/modify-database-and-transaction.md

Demonstrates wrapping multiple SQL operations within a transaction block to ensure atomicity.

```kotlin
database {
    transaction {
        PersonTable { table ->
            table INSERT Person(age = 4, name = "Tom")
            table UPDATE SET { age = 5 } WHERE (name NEQ "Tom")
        }
    }
}
```

--------------------------------

### Rename Table with SQLlin DSL

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/modify-database-and-transaction.md

Demonstrates renaming an existing table to a new name using SQLlin's ALERT_RENAME_TABLE_TO operation. It can be performed using a Table object or the old table name as a String.

```kotlin
fun sample() {
    database {
        // Rename using Table object
        PersonTable ALERT_RENAME_TABLE_TO NewPersonTable

        // Or rename using old table name as String
        "old_person" ALERT_RENAME_TABLE_TO NewPersonTable
    }
}
```

--------------------------------

### Execute Left Outer Join operations

Source: https://github.com/ctripcorp/sqllin/blob/main/sqllin-dsl/doc/advanced-query.md

Illustrates the use of LEFT_OUTER_JOIN and its natural variant. These functions mirror the INNER_JOIN API but perform a left outer join on the target table.

```kotlin
fun joinSample() {
    db {
        PersonTable { table ->
            table SELECT LEFT_OUTER_JOIN<Student>(TranscriptTable) USING name
            table SELECT NATURAL_LEFT_OUTER_JOIN<Student>(TranscriptTable)
            table SELECT LEFT_OUTER_JOIN<CrossJoinStudent>(TranscriptTable) ON (name EQ TranscriptTable.name)
        }
    }
}
```