Using Subquery Columns on Android with SQLite?

Take a look at this (contrived) query, as might be passed to SQLiteDatabase.rawQuery():

SELECT *
FROM t1 A, (SELECT T2.id FROM t2 T2) B
WHERE A.id = B.id

On Android 2.1 and below, this query will cause your app to crash. You'll get an error like this:

Caused by: android.database.sqlite.SQLiteException: no such column: B.id: , while compiling: SELECT * FROM t1 A, (SELECT T2.id FROM t2 T2) B WHERE A.id = B.id
    at android.database.sqlite.SQLiteProgram.native_compile(Native Method)
    at android.database.sqlite.SQLiteProgram.compile(SQLiteProgram.java:110)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:49)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1220)
    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1193)
    at com.mycompany.myapp.MyActivity.onCreate(QuickTestActivity.java:22)
    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2459)
    ... 11 more

It turns out that SQLite throws this error if two conditions are met:

1. You use a subquery (in this case, the select subquery from table t2).
2. Your subquery has a table alias (in this case, referencing table t2 as "T2").

The solution is simple: alias the selected field names. This query will work:

SELECT *
FROM t1 A, (SELECT T2.id AS id FROM t2 T2) B
WHERE A.id = B.id

I'm hardly an expert on SQL or SQLite, so I don't know whether this is a bug or just me running into undefined behavior. I ran into this problem when doing JOINs on multiple tables, so as contrived as my example is, it can happen. Regardless, it is easy to work around.

Reference Integrity Example in Android Sqlite?

Declaring Referential Integrity (Foreign Key) Constraints

Foreign key constraints are used to check referential integrity between tables in a database. Consider for example the following two tables:

create table Residence (

name VARCHAR PRIMARY KEY,

capacity INT

);

create table Student (

id INT PRIMARY KEY,

firstName VARCHAR,

lastName VARCHAR,

residence VARCHAR

);

We can enforce the constraint that a Student's residence actually exists by making Student.residence a foreign key that refers to Residence.name. SQLite lets you specify this relationship in several different ways:

create table Residence (

name VARCHAR PRIMARY KEY,

capacity INT

);

create table Student (

id INT PRIMARY KEY,

firstName VARCHAR,

lastName VARCHAR,

residence VARCHAR,

FOREIGN KEY(residence) REFERENCES Residence(name)

);

or

create table Residence (

name VARCHAR PRIMARY KEY,

capacity INT

);

create table Student (

id INT PRIMARY KEY,

firstName VARCHAR,

lastName VARCHAR,

residence VARCHAR REFERENCES Residence(name)

);

or

create table Residence (

name VARCHAR PRIMARY KEY,

capacity INT

);

create table Student (

id INT PRIMARY KEY,

firstName VARCHAR,

lastName VARCHAR,

residence VARCHAR REFERENCES     Residence -- Implicitly references the primary key of the Residence table.

);

All three forms are valid syntax for specifying the same constraint. More details can be found in the documentation for SQLite Foreign Key Support .

Constraint Enforcement

There are a number of important things about how referential integrity and foreign keys are handled in SQLite:

• The attribute(s) referenced by a foreign key constraint (i.e. Residence.name in the example above) must be declared unique or as a primary key within their table, but this requirement is checked at run-time, not when constraints are declared. For example, if Residence.name had not been declared as the primary key (or as unique), the FOREIGN KEY declarations above would still be permitteed, but inserting into the Student table would always yield an error.
• Foreign key constraints are not checked by default. If you want SQLite to check any foreign key constraints specified on your tables, you must enable them with the command:
  PRAGMA foreign_keys = ON;
  once per database session (i.e. once per invocation of /usr/class/cs145/bin/sqlite). Even if you have previously enabled foreign key constraint checking while using a particular database, new sessions with that database will not check foreign key constraints unless you issue this PRAGMA command. If you do not issue this command, foreign key constraints are permitted to become violated, and it will happen in complete silence.
• Bulk-Loading into a SQLite database while checking referential integrity is very, very slow -- we don't recommend it. It is faster to bulk-load your data with referential integrity checking turned off, then run SQL queries over your tables to verify that the constraints hold, then turn constraint-checking on.
• Referential integrity checking can be "deferred". This means that the constraint is not checked until the current transaction ends, or if there is no active transaction, when the current statement ends. This can be useful when adding tuples to multiple tables -- so that you don't need to worry about the order of inserts, or in the case of referential integrity between two tables in both directions.
  A foreign key constraint can be made deferrable with the keywords 
  DEFERRABLE INITIALLY DEFERRED:
  
  
  create table Residence (
  name VARCHAR PRIMARY KEY,
  capacity INT
  );
  
  
  create table Student (
  id INT PRIMARY KEY,
  firstName VARCHAR,
  lastName VARCHAR,
  residence VARCHAR REFERENCES Residence DEFERRABLE INITIALLY DEFERRED
  );
  
  
  By doing so, we can write:
  
  
  BEGIN;
  insert into Student values (123, 'Ben', 'Savage', 'Gavilan');
  insert into Residence values ('Gavilan', 50);
  COMMIT;
  
  
  and no error will be raised after the insertion into Student.
  
  
• Special action can be taken when the referenced tuple is updated or deleted. Let's say our Residence and Student tables contain the tuples inserted above:
  select * from Student;
  
  
  id      firstName      lastName     residence
  ------ --------- --------- ---------
  123        Ben             Savage       Gavilan
  
  
  select * from Residence;
  name     capacity
  -------      --------
  Gavilan      50
  
  
  By default, updating or removing the tuple in Residence is not permitted, since it would leave the tuple in Student "dangling" and the database in an inconsistent state. SQLite supports ON UPDATE and ONDELETE actions that will keep the database in a consistent state:
• RESTRICT: This is the default behavior -- it prohibits a change to the Residence tuple, as long as there are Student tuples that depend on it.
• CASCADE: Changes to the Residence tuple will be propagated to all Student tuples that depend on it.
• SET NULL: A change to the Residence tuple will set the referencing value in Student.residence to null.
  ON UPDATE and ON DELETE actions can be specified along with a foreign key constraint declaration, as in these examples:
• residence VARCHAR REFERENCES Residence ON UPDATE RESTRICT
• residence VARCHAR REFERENCES Residence ON DELETE CASCADE
• residence VARCHAR REFERENCES Residence ON UPDATE SET NULL ON DELETE RESTRICT

Again, more details on referential integrity in SQLite can be found in the SQLite Foreign Key Support documentation.

Sqlite Version List in different Android Version?

This is SQLite’s way of maintaining referential integrity. This was not a feature, however, until version 3.6.19.  Also, Android did not use a version of SQLite >= 3.6.19 until SDK 8 (2.2 Froyo). Well, actually prior versions of SQLite parsed foreign key constraints, but did not enforce them. Here is a list of SQLite versions that the different Android SDK’s use:

• 15-4.0.3-Ice Cream Sandwich: 3.7.4
• 14-4.0-Ice Cream Sandwich: 3.7.4
• 13-3.2-Honeycomb: 3.7.4
• 12-3.1-Honeycomb: 3.7.4
• 11-3.0-Honeycomb: 3.7.4
• 10-2.3.3-Gingerbread: 3.6.22
• 9-2.3.1-Gingerbread: 3.6.22
• 8-2.2-Froyo: 3.6.22
• 7-2.1-Eclair: 3.5.9
• 4-1.6-Donut: 3.5.9
• 3-1.5-Cupcake: 3.5.9

By default, foreign key support is turned off in the latest versions of SQLite in order to maintain backwards compatibility. This might change in the future, but as of now, it must be enabled before using foreign keys or else the constraints will not affect your commands.

Referential Integirty with Android Sqlite?

If you look for documentation about sqlite, you will most quickly find all the excellent documentation on the website. And after a while you will find out that the documentation is for the latest version, which is currently 3.7.0.1. However when you play around with sqlite on the Android emulator or phone you will find that e.g. Android 1.5, 1.6 and 2.1 include SQLite 3.5.9 and only Android 2.2 includes the newer SQLite 3.6.22. Now looking up foreign key support on the documentation shows that it foreign key support was only added with version 3.6.19 and that it is disabled by default even with higher versions.

If you are  supporting Android 2.1 or below you are out of luck with this foreign key support and you will have to look for an alternative. Luckily there is one, so lets see. First we have our tables created like this:

CREATE TABLE ant (id INTEGER NOT NULL PRIMARY KEY, anthillid INTEGER, name TEXT, gender TEXT);

CREATE TABLE anthill (id INTEGER NOT NULL PRIMARY KEY, name TEXT, species TEXT, longitude TEXT, latitude TEXT);

Note that the creation statements above do not create any constraints. We could add that and SQLite supports the syntax, but it does not enforce it. So I leave it out to be more explicit.Now in order to be able to delete an anthill with a query like 

DELETE FROM anthill WHERE id ='5';

without having to delete the individual ants or even just issue a batch delete explicitly in Java code, we take advantage of triggers. It so happens that SQLite supports triggers and you can set up a trigger that will do the deletion automatically like this

CREATE TRIGGER delete_ants_with_anthill<br>

BEFORE DELETE ON anthill<br>

FOR EACH ROW BEGIN<br>

    DELETE FROM ant WHERE ant.anthillid = OLD.id;<br>

END;

In a similar manner you can enforce integrity upon insert and update allowing you to not worry about inconsistent data in the database and handling edge cases in your Java code. Overall this approach will allow you to reduce the complexity of your Java code as well as improve your overall performance. Not a bad deal I would say..

Don’t forget you will need to include turning them on in your DB Helper class:

@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
if (!db.isReadOnly())
     {
        db.execSQL(“PRAGMA foreign_keys=ON;”);
      }
}

Difference between rawQuery() and query() in android Sqlite?

Executing Queries

To execute queries, there are two methods:

1. Execute db.rawQuery method
2. Execute db.query method
   

To execute a raw query to retrieve all departments:

Cursor getAllDepts()
  {
   SQLiteDatabase db=this.getReadableDatabase();
   Cursor cur=db.rawQuery("SELECT "+colDeptID+" as _id, 
        "+colDeptName+" from "+deptTable,new String [] {});
   
   return cur;
  }

The rawQuery method has two parameters:

1. String query: The select statement
2. String[] selection args: The arguments if a WHERE clause is included in the select statement
   

Notes

1. The result of a query is returned in Cursor object.
2. In a select statement if the primary key column (the id column) of the table has a name other than _id, then you have to use an alias in the form SELECT [Column Name] as _id cause the Cursor object always expects that the primary key column has the name _id or it will throw an exception .
   

Another way to perform a query is to use a db.query method. A query to select all employees in a certain department from a view would be like this:

public Cursor getEmpByDept(String Dept) {
   SQLiteDatabase db=this.getReadableDatabase();
   String [] columns=new String[]{"_id",colName,colAge,colDeptName};
   Cursor c=db.query(viewEmps, columns, colDeptName+"=?", 
        new String[]{Dept}, null, null, null);
   return c;
  }

The db.query has the following parameters:

1. String Table Name: The name of the table to run the query against
2. String [ ] columns: The projection of the query, i.e., the columns to retrieve
3. String WHERE clause: where clause, if none pass null
4. String [ ] selection args: The parameters of the WHERE clause
5. String Group by: A string specifying group by clause
6. String Having: A string specifying HAVING clause
7. String Order By by: A string Order By by clause