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 entity. Now, we start 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 will only 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 be annotated the @DBRow will be generated a Table object, its name is class name + 'Table'.

Now, let's do the real INSERT:

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 statement could insert objects directly. You can insert one or multiple objects once.

Delete

The DELETE statement 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 is used for representing columns' name, they are extension property with Table, their type are ClauseElement, and be 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 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 statement is similar to DELETE, it also use a WHERE clause to limit update conditions. But, the difference is 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 only available in SET clause, it different with age readonly property in WHERE clause.

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

Transaction

Use transaction is simple in SQLlin, you just need to use the 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 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 statement. The SELECT statement is more complex than other statements, be ready :).

• Query
• Concurrency Safety
• SQL Functions
• Advanced Query