Rock-solid database schema

Building upon the first and simplest example of crafting a table from a Java class, we’ll demonstrate the following:

 Custom names for tables and columns  Handling relationships between classes

 Referential integrity for relationships (API Level 8 and above)  Cascading deletes (API Level 8 and above)

 Uniqueness constraints for cross references

Most real-world database instances will use these concepts and others. Even though we’re using an ORM tool to build our tables, we still have the expressive power to achieve a solid schema to enforce data consistency. For example, we might want to require that an article’s title and text must not be null. We also can ensure that if a cat- egory has a parent category, the parent must actually exist. Furthermore, we can spec- ify that if an article is deleted, then all of its comments and mappings to categories will be deleted automatically by SQLite.

The first recommendation when defining our schema is to use final variables to define names for tables and columns. This, in practice, will make maintaining our code much easier in the scenario where a member variable is refactored or removed. Doing so will help cause compile-time errors, rather than tricky-to-spot runtime mis- takes hidden away in SQL strings. Let’s define the Category class using this technique. We’ll declare public static final variables for the table and columns:

@DatabaseTable(tableName = Category.TABLE_NAME) _{Specifies name}

of our table

B

public class Category {

public static final String TABLE_NAME = "categories", ID_COLUMN = "_id",

NAME_COLUMN = "name", PARENT_COLUMN = "parent";

@DatabaseField(generatedId = true, columnName = ID_COLUMN)

C

Specifies names of columns in the DatabaseField

private int id;

@DatabaseField(canBeNull = false, columnName = NAME_COLUMN)

Name member must not be null

D

private String name;

@DatabaseField(foreign = true, columnName = PARENT_COLUMN)

E

Marked as foreign

private Category parent; public Category() { }

}

The additions here are many, and we’re not done yet. We now specify the name of our table in the DatabaseTable

B

annotation and names of columns in the Database- Field

C

annotations. We can use these public variables elsewhere in the host applica- tion for querying purposes.

Additionally, we require that the name member must not be null (columns can be null by default)

D

. Finally, consider the annotation on the parent member. Any

137

Building databases with ORMLite

member variable which is defined as a table in our relation must be marked as for- eign, using foreign = true

E

. This instructs ORMLite to only store the ID of the for- eign object in the current table. Taking this class one step further, we can ensure that a parent category must exist. The final member declaration of the parent looks like the following:

@DatabaseField(foreign = true, foreignAutoRefresh = true,

columnName = PARENT_COLUMN, columnDefinition = "integer references " + TABLE_NAME + "(" + ID_COLUMN + ") on delete cascade")

private Category parent;

We can fine-tune the exact SQL used to define this column using columnDefinition. Here we have specified that the parent column has a foreign key to the categories table (the same table on which it is defined). This states that values in the parent col- umn must either be null or exist in the categories table in the _id column. We also specify that records that refer to a parent category get deleted when the parent cate- gory is deleted. This is known as a cascading delete. This last technique is not required in a database, but for demonstration purposes we’ll include it. Our finished table for the Category class looks like the following:

CREATE TABLE 'categories' ('parent' integer references categories(_id) on delete cascade, 'name' VARCHAR NOT NULL ,

'_id' INTEGER PRIMARY KEY AUTOINCREMENT )

The last concept in this section is specifying uniqueness in a column or combination of columns. Implementing the many-to-many relationship between articles and cate- gories requires a cross-reference table. Put simply, a cross-reference table is used to match up entries from one table with entries from another. Therefore, we’ll define a two-column table to match IDs from articles to IDs from categories, logically storing which articles are in which categories. As an added sanity check, cross-reference tables usually include a constraint saying that the same combination of IDs can only appear in the table once. To express uniqueness, ORMLite uses two Boolean elements, unique and uniqueCombo. We’ll set uniqueCombo = true on the two member variables in the following class, ArticleCategory, which maps articles to categories:

@DatabaseTable(tableName = ArticleCategory.TABLE_NAME) public class ArticleCategory {

public static final String TABLE_NAME = "articlecategories",

Final variables for table and column names

B

ARTICLE_ID_COLUMN = "article_id", CATEGORY_ID_COLUMN = "category_id";

@DatabaseField(foreign = true, canBeNull = false, uniqueCombo = true, columnName = ARTICLE_ID_COLUMN,

columnDefinition = "integer references " +

C

Using the columnDefinition element

Article.TABLE_NAME +

"(" + Article.ID_COLUMN + ") on delete cascade") private Article article;

@DatabaseField(foreign = true, canBeNull = false, uniqueCombo = true,

D

Setting foreign = true for storing complex objects

columnDefinition = "integer references " + Category.TABLE_NAME +

"(" + Category.ID_COLUMN + ") on delete cascade") private Category category;

public ArticleCategory() { }

}

Notice the use of techniques described earlier, such as final variables for table and col- umn names

B

, referential integrity using the columnDefinition element

C

, and the requirement of setting foreign = true

D

when storing complex objects. The result- ing table is as follows:

CREATE TABLE 'articlecategories'

('article_id' integer references articles(_id) on delete cascade, 'category_id' integer references categories(_id) on delete cascade, UNIQUE ('article_id','category_id') );

Note the UNIQUE statement in the generated SQL.

In document 50 Android Hacks (Page 165-167)