Fields, Properties, and Functions

Fields

Fields are items that you can select in a SQLite query using the SquiDB Query object. The base class Field essentially wraps the expression of the item being selected, such as a column name or a literal value. Properties and Functions are extensions of Field that introduce special behaviors and usage patterns.

Properties

Property objects represent a column in a model object. They also enable SquiDB's type safety features. All instances of AbstractModel allow you to get and set properties on that model using the type corresponding to the given property. All property objects are one of the following types, each of which extends the abstract Property<?> class:

• LongProperty (getters and setters use Long)
• IntegerProperty (getters and setters use Integer)
• StringProperty (getters and setters use String)
• BooleanProperty (getters and setters use Boolean)
• DoubleProperty (getters and setters use Double)
• BlobProperty (getters and setters use byte[])

Aside from providing type safety, Properties are advantageous over Fields when using Query objects and SquidCursors obtained by running those queries. You can only use SquidCursor's get() method with a Property, and only Properties are read into model objects populated from a SquidCursor.

Field<String> stringLiteral = Field.selectLiteral("foo bar baz", "foo");
IntegerProperty intLiteral = IntegerProperty.literal(17, "seventeen");
// select people._id, 'foo bar baz' as foo, 17 as seventeen from people;
SquidCursor<Person> cursor = database.query(Query.select(Person.ID, stringLiteral, intLiteral));
cursor.moveToFirst();
Person person = new Person(cursor);
// both of these lines produce a compile time error
String foo1 = cursor.get(stringLiteral);
String foo2 = person.get(stringLiteral);
// both of these lines return 17
Integer a = cursor.get(intLiteral);
Integer b = person.get(intLiteral);

Functions

For times you want to use a SQLite function, you can create them using the Function class. For the most part, functions simply wrap SQLite expressions and keep track of arguments they may have. As a convenience, many common SQLite functions can be created with methods in the Function class, like max(), sum(), avg(), abs(), etc. If the function you want isn't among those, you can call Function.rawFunction() or Function.functionWithArguments() to build your own.

Functions can be used anywhere a Field can, such as the select list of a Query or to form Criterions.

// select ... from people where length(first_name) >= 7;
Function<Integer> nameLength = Function.length(Person.FIRST_NAME);
SquidCursor<Person> cursor = database.query(Person.class, Query.select(Person.PROPERTIES)
        .where(nameLength.gte(7)));

Given the advantages of using properties, if you are using functions in your queries, you will likely want to wrap them with the appropriate Property type. This allows you to read that value into models or directly from the cursor. Here's how to incorporate that into the example from above:

// select ..., length(people.first_name) as nameLength from people
//     where length(people.first_name) >= 7;
Function<Integer> nameLengthFunc = Function.length(Person.FIRST_NAME);
IntegerProperty nameLength = IntegerProperty.fromFunction(nameLengthFunc, "nameLength");
SquidCursor<Person> cursor = database.query(Person.class, Query.select(Person.PROPERTIES).selectMore(nameLength)
        .where(nameLength.gte(7)));
cursor.moveToFirst();
// reading from cursor
Integer length = cursor.get(nameLength);
// populating model and reading from it
Person person = new Person(cursor);
length = person.get(nameLength);

Note that the function expression is written twice in the query: once in the select clause, and again in the where clause. Normally this behaves just fine, but it can add unnecessary computations or, in rare cases, produce different results with each evaluation (e.g. if your function includes "random()"). If you prefer to reference the function by its alias elsewhere in the query, you can do the following:

// select ..., length(people.first_name) as nameLength from people where nameLength >= 7;
String nameLength = "nameLength";
Function<Integer> function = Function.length(Person.FIRST_NAME);
IntegerProperty nameLengthProp = IntegerProperty.fromFunction(function, nameLength);
Field<Integer> nameLengthField = Field.field(nameLength);
SquidCursor<Person> cursor = database.query(Person.class, Query.select(Person.PROPERTIES).selectMore(nameLengthProp)
        .where(nameLengthField.gte(7)));

Aliases

All of the above objects can be aliased using the as() method. In fact, creating a Property from a Function as a previous example illustrates does exactly this under the hood. The difference is apparent when you inspect the generated SQLite statement in both cases:

// select people.first_name as first_name from people
Query.select(Person.FIRST_NAME).from(Person.TABLE);
// vs
// select people.first_name as fname from people
Query.select(Person.FIRST_NAME.as("fname")).from(Person.TABLE);

In the latter case, you should declare the aliased property in a variable if you want to read it later. For example:

StringProperty fname = Person.FIRST_NAME.as("fname");
Query query = Query.select(fname).from(Person.TABLE);
SquidCursor<Person> cursor = database.query(Person.class, query);
if (cursor.moveToFirst()) {
    String fname = cursor.get(fname); // This will work
    String firstName = cursor.get(Person.FIRST_NAME); // This will throw a runtime exception
}