modify-database-and-transaction.md

Modify Database and Transaction

中文版请见这里

In Getting Start, we have learned how to create the Database instance and define your database entities. Now, we start to learn how to write SQL statements with SQLlin.

Insert

The class Database has overloaded function operator that type is <T> Database.(Database.() -> T) -> T. When you invoke the operator function, it will produce a DatabaseScope. Yeah, that is an operator function's lambda parameter. Any SQL statement must be written in DatabaseScope. And, the inner SQL statements only will be executed when the DatabaseScope ended.

You already know, the INSERT, DELETE, UPDATE and SELECT SQL statements are used for table operation. So, before you write your SQL statements, you also need to get a Table instance, like this:

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

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

The PersonTable is generated by sqllin-processor, because Person is annotated the @DBRow annotation. Any class that is annotated the @DBRow will be generated a Table object, its name is class name + 'Table'.

Now, let's do the real _INSERT_s:

fun sample() {
    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"),
            )
        }
    }
}

The INSERT statements could insert objects directly. You can insert one or multiple objects once.

Delete

The DELETE statements will be slightly more complex than INSERT. SQLlin doesn't delete objects like Jetpack Room, we use the WHERE clause:

fun sample() {
    database {
        PersonTable { table ->
            table DELETE WHERE(age GTE 10 OR (name NEQ "Jerry"))
        }
    }
}

Let's understand the WHERE clause. WHERE function receives a ClauseCondiction as a parameter. The age and name in the example are used for representing columns' names, they are extension property with Table, their type are ClauseElement, and they are generated by sqllin-processor(KSP).

The ClauseElement has a series of operators that used for representing the SQL operators like: =, >, <, LIKE, IN, IS etc. When a ClauseElement invoke a operator, we will get a ClauseCondiction. Multiple ClauseCondictions would use the AND or OR operator to link and produce a new ClauseCondiction.

The chart of between SQL operators and SQLlin operators like this:

SQL	SQLlin
=	EQ
!=	NEQ
<	LT
<=	LTE
>	GT
>=	GTE
BETWEEN	BETWEEN
IN	IN
LIKE	LIKE
GLOB	GLOB
OR	OR
AND	AND

Sometimes, we want to delete all data in the table. At this time, the DELETE statement doesn't have WHERE clause:

DELETE FROM person

In SQLlin we can write this to achieve the same effect:

fun sample() {
    database {
        PersonTable { table ->
            table DELETE X
        }
    }
}

The X is a Kotlin object (singleton).

Update

The UPDATE is similar with DELETE, it also use a WHERE clause to limit update conditions. But, the difference is the UPDATE statement owns a particular SET clause:

fun sample() {
    database {
        PersonTable { table ->
            table UPDATE SET { age = 5 } WHERE (name NEQ "Tom")
        }
    }
}

The SET clause is different from other clauses, it receives a lambda as parameter, you can set the new value to column in the lambda. The age in the set lambda is a writable property that also be generated by KSP, and it is only available in SET clauses, it is different with readonly property age in WHERE clauses.

You also could write the UPDATE statements without the WHERE clause that used for update all rows, but you should use it with caution.

Transaction

Using transaction is simple in SQLlin, you just need to use a transaction {...} wrap your SQL statements:

fun sample() {
    database {
        transaction {
            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"),
                )
                table UPDATE SET { age = 5 } WHERE (name NEQ "Tom")
            }
        }
    }
}

The transaction {...} is a member function in Database, it is inside or outside of TABLE(databaseName) {...} doesn't matter.

Next Step

You have learned how to use INSERT, DELETE and UPDATE statements. Next step you will learn SELECT statements. The SELECT statement is more complex than other statements, be ready :).

• Query
• Concurrency Safety
• SQL Functions
• Advanced Query