Skip to content
This repository has been archived by the owner on May 1, 2020. It is now read-only.

Model specs for SQL views

Jump to bottom
Sam Bosley edited this page Sep 22, 2015 · 9 revisions

In SQLite, a view is like a pre-packaged select statement that acts as a virtual table you can query.

Defining a ViewModel

Just as table schemas are represented in SquiDB using subclasses of TableModel (which are generated by @TableModelSpec annotated classes), so too can view schemas be represented using subclasses of ViewModel. ViewModels are also generated from model spec classes, although the spec will look a little different. Because views are composed of columns from other tables, instead of listing column names and types in your spec, you will instead list references to those columns in other tables.

@ViewModelSpec(className="PersonNameAndImage", viewName="names_and_images")
public class PersonNameAndImageSpec {

    @ViewQuery
    public static final Query QUERY = Query.select().from(Person.TABLE)
            .leftJoin(ProfileImage.TABLE, Person.ID.eq(ProfileImage.PERSON_ID));

    public static final LongProperty PERSON_ID = Person.ID;

    public static final LongProperty PROFILE_ID = ProfileImage.ID;

    public static final StringProperty PERSON_FIRST_NAME = Person.FIRST_NAME;

    public static final StringProperty PERSON_LAST_NAME = Person.LAST_NAME;

    public static final StringProperty PROFILE_IMAGE = ProfileImage.URL;

}

The generated ViewModel will have versions of all the properties declared, only now associated with the view rather than their original tables.

There is also a constant Query object annotated with @ViewQuery. This lets the code generator know that this is the query you want to use to define the view. Note that the Query object uses Query.select() with no args--that's important! The code generator is responsible for making sure the properties will be selected.

Aliasing

An issue that can come up sometimes when defining views is name clashes. What if for example your view is based on a query that selects an _id column from two different tables, like the above spec does? The code generator has tools that can help you out with this problem.

Automatic aliasing

If you don't really care what the aliased column names are, you don't need to do anything else. The code generator will automatically detect naming collisions in the view properties you listed and alias them before adding them to the @ViewQuery Query object. You won't need to worry about what the names are under the hood, since you'll just be referring to them using the names you gave them in your view's model spec anyway, e.g. PersonNameAndImage.PERSON_ID.

Manual aliasing

On the other hand, you might have strong feelings about what the aliases should be, such as if you're implementing a ContentProvider and need to meet a specific contract. In that case, you can manually specify what alias you'd like to use for a given column using the @Alias annotation:

@ViewModelSpec(className="PersonNameAndImage", viewName="names_and_images")
public class PersonNameAndImageSpec {

    @Alias("person_id")
    public static final LongProperty PERSON_ID = Person.ID;

    @Alias("profile_img_id")
    public static final LongProperty PROFILE_ID = ProfileImage.ID;

}

This will let the code generator know that you'd like to use the given aliases for those columns. Just make sure you don't accidentally introduce any naming conflicts! The code generator will override you if you manually specify an alias that's a name already in use by some other column.

Working with ViewModels

Creating the view in your database

Each generated ViewModel will have a View object constant named VIEW (assuming you specified a @ViewQuery). You can make sure this view exists in your database by overriding getViews() in your subclass of SquidDatabase (see Setting up a database for your app).

Querying the view

You can construct a query against a view in exactly the same way you do for a table model:

Query fromView = Query.select(PersonNameAndImage.PROPERTIES)
    .from(PersonNameAndImage.VIEW)
    .where(PersonNameAndImage.PERSON_FIRST_NAME.eq("Sam"));

Features of ViewModel objects

You can get and set properties on ViewModel subclasses just like with table models. SquiDB generates the same kinds of getters and setters for you for each property as for regular Table models:

PersonNameAndImage nameAndImage = ...;
String name = nameAndImage.getPersonFirstName();
long personId = nameAndImage.getPersonId();

Perhaps the most powerful feature of ViewModel objects though is that they know what other models they were constructed from, and which fields correspond to which source model. This allows you to "split" them out into objects corresponding to their original sources using the special mapToModel() method:

// Imagine this view model has person_first_name = "Sam",
// person_id = 1, profile_image_id = 2, and url = "http://some.url"
PersonNameAndImage nameAndImage = ...;

Person person = nameAndImage.mapToModel(new Person());
long personId = person.getId(); // Returns 1
String personName = person.getFirstName(); // Returns "Sam"

ProfileImage profileImage = nameAndImage.mapToModel(new ProfileImage());
long imageId = profileImage.getId(); // Returns 2
String url = profileImage.getUrl(); // Returns "http://some.url"

This can be a very powerful tool--you can split a view model into its source table models and persist the table models, or map to a source model to examine only part of a view model using the source's getter methods.

Views vs Subqueries

A view in SQLite is essentially a named query that can be used in other statements, much like a named constant (e.g. Math.PI) could be used wherever that value is needed. If you don't need that convenience, you could instead substitute the query itself wherever you would use the view name.

--these produce the same results
--example 1
CREATE VIEW sample_view AS
  SELECT a.col1, b.col2, c.col3
  FROM a JOIN b ON (a._id = b.a_id) JOIN c ON (b._id = c.b_id)
  GROUP BY a.col4
  ORDER BY b.col1 ASC;

SELECT sample_view.col1, sample_view.col3 FROM sample_view WHERE col1 > 1000;

--example 2
SELECT subquery.col1, subquery.col3 FROM (
  SELECT a.col1, b.col2, c.col3
  FROM a JOIN b ON (a._id = b.a_id) JOIN c ON (b._id = c.b_id)
  GROUP BY a.col4
  ORDER BY b.col1 ASC
) AS subquery
WHERE col1 > 1000;

With @ViewModelSpec, you can specify that the generated model have a SubqueryTable (named SUBQUERY) instead of a View by specifying isSubquery = true in the @ViewModelSpec annotation. In that case you would not have to add anything to your database, but otherwise you still get all the benefits of a ViewModel. When writing your queries, you would use SUBQUERY the same way you would have used VIEW:

// if the model has a View, i.e. isSubquery = false
Query.select(Model.COL_1, Model.COL_3)
	.from(Model.VIEW)
	.where(Model.COL_1.gt(1000));

// if the model has a SubqueryTable, i.e. isSubquery = true
Query.select(Model.COL_1, Model.COL_3)
	.from(Model.SUBQUERY) // <-- the only difference
	.where(Model.COL_1.gt(1000));

See also:

Clone this wiki locally