-
Notifications
You must be signed in to change notification settings - Fork 132
Other SQLite grammar objects
SquiDB includes several classes for building statements other than queries. You can apply these statements to your database by using either SquidDatabase.tryExecStatement() or the appropriately named methods in SquidDatabase: insert(), update(), and delete().
Insert statements allow you to add new rows to a table with specific columns initialized to either some desired values, the values returned by a Query, or the column's default values. Inserts are typically useful for building or upgrading your database schema. The below example shows two different uses of Insert: the code in onTablesCreated() inserts some predetermined values to a table, while the code in onUpgrade() inserts data into a new table based on a Query.
public class MyDatabase extends SquidDatabase {
/* ... other methods ... */
@Override
protected int getVersion() {
return 2;
}
@Override
protected void onTablesCreated(SQLiteDatabaseWrapper db) {
// insert predetermined data
tryExecStatement(Insert.into(Department.TABLE)
.columns(Department.CODE, Department.NAME)
.values(10, "Finance")
.values(20, "Accounting")
.values(30, "Marketing")
.values(40, "Research & Development")
.values(50, "Human Resources"));
}
@Override
protected boolean onUpgrade(ISQLiteDatabase db, int oldVersion, int newVersion) {
switch(oldVersion) {
case 1:
// insert values based on a query
SqlTable<?> deptBackup = Department.TABLE.as("deptBackup");
tryCreateTable(deptBackup);
tryExecStatement(Insert.into(deptBackup).columns(Department.CODE, Department.NAME)
.select(Query.select(Department.CODE, Department.NAME).from(Department.TABLE)));
}
return true;
}
}Even though SQLite does not require a column list in an INSERT statement, the Insert object does require you to specify columns. This is because when given an empty column list, SQLite expects the values clause to specify values for every column. Since every table generated by SquiDB automatically includes an _id column that is autoincremented, you typically do not want to insert a value into this column directly. There is no way to inform SQLite that it should use the default behavior for a specific column, making it very difficult to properly handle the case where no columns are specified.
If you truly want this behavior, you can pass Model.PROPERTIES to the columns() method, but we advise against doing so--see the next section.
You may be inclined to use Model.PROPERTIES when specifying the column list of an Insert or the select list of a Query used in an Insert. The problem with doing so is that Model.PROPERTIES changes if you ever make changes to the spec file of your model. This means a user whose database is several versions behind can have different columns in their existing table than you expect. Consider the following example:
public class MyDatabase extends SquidDatabase {
/* ... other methods ... */
@Override
protected int getVersion() {
return 3;
}
@Override
protected boolean onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
switch(oldVersion) {
case 1:
SqlTable deptBackup = Department.TABLE.as("deptBackup");
tryCreateTable(deptBackup);
tryExecStatement(Insert.into(deptBackup).columns(Department.PROPERTIES)
.select(Query.select(Department.PROPERTIES).from(Department.TABLE)));
case 2:
tryAddColumn(Department.BUDGET);
}
return true;
}
}The upgrade to version 2 is similar to the previous example, but instead uses Department.PROPERTIES for both the column list of the Insert and the select list of the Query. The upgrade to version 3 adds a column that was introduced by adding a new field to the ModelSpec file from which the Department model is generated. If a user whose database version is 1 tries to upgrade to version 3, onUpgrade() will throw an exception at the tryExecStatement() call. To understand why, consider the SQL that is generated by the Insert statement:
INSERT INTO deptBackup (_id, code, name, budget) SELECT _id, code, name, budget FROM departments;The problem is that for a user with database version 1, the departments table only has the _id, code, and name columns; it does not have the budget column since that is added in a later version. As such, the SELECT ... part of the insert will fail to compile because budget is not recognized as a column.
Update statements allow you to update existing rows with new values for specified columns. Delete statements allow you to delete rows from a table. Both are typically useful when upgrading your database schema and are very straightforward to use. The following code implements a database upgrade in which the Accounting department is deleted and all employees in that department are moved to the Finance department, which will henceforth be known as "Finance & Accounting".
public class MyDatabase extends SquidDatabase {
/* ... other methods ... */
@Override
protected int getVersion() {
return 2;
}
@Override
protected boolean onUpgrade(ISQLiteDatabase db, int oldVersion, int newVersion) {
switch(oldVersion) {
case 1:
tryExecStatement(Update.table(Employee.TABLE).set(Employee.DEPT_CODE, 10)
.where(Employee.DEPT_CODE.eq(20)));
tryExecStatement(Update.table(Department.TABLE).set(Department.NAME, "Finance & Accounting")
.where(Department.CODE.eq(10)));
tryExecStatement(Delete.from(Department.TABLE).where(Department.CODE.eq(20)));
}
return true;
}
}Triggers allow you to execute predefined statements in response to certain database events. Triggers can be made to fire before or after an insert, update, or delete operation on a table, or instead of any of those operations on a view. Triggers are built on top of the other statement types that have been presented so far (Insert, Update, Delete, and Query). For each row affected by the operation, all the statements of the trigger will execute once.
You can supply Criterions to build a WHEN clause that restricts which affected rows actually cause the trigger statements to execute. The WHEN clause and any statements executed by the trigger can reference the old value (for updates and deletes) and new value (for inserts and updates) of a column in the affected row. To obtain properties for such references, use the static Trigger.oldValueOf() and Trigger.newValueOf() methods.
The following code sets up a trigger to fire whenever a user's score crosses a threshold value and inserts some data to an achievements table.
public class MyDatabase extends SquidDatabase {
/* ... other methods ... */
@Override
protected void onTablesCreated(ISQLiteDatabase db) {
// CREATE TRIGGER score_1000_get AFTER UPDATE OF score ON users
// WHEN OLD.score < 1000 AND NEW.score >= 1000 begin
// INSERT INTO achievements (userId, type) values (NEW._id, "score_1000", current_timestamp);
// END;
IntegerProperty oldScore = Trigger.oldValueOf(User.SCORE);
IntegerProperty newScore = Trigger.newValueOf(User.SCORE);
LongProperty userId = Trigger.newValueOf(User.ID);
Function now = Function.rawFunction("strftime('%s', 'now') * 1000");
Insert insert = Insert.into(Achievement.TABLE)
.columns(Achievement.USER_ID, Achievement.TYPE, Achievement.TIME)
.values(userId, "score_1000", now);
Trigger trigger = Trigger.after("score_1000_get")
.updateOf(User.TABLE, User.SCORE)
.when(oldScore.lt(1000).and(newScore.gte(1000)))
.perform(insert);
tryExecStatement(trigger);
}
}See also: