Error when querying a created dbView (not table)!

For context see post

Following the awsome sugestion provided by yock, I changed my database design to use foreign keys. You can check the overall database design in this post.

So I have 2 tables.

              TABLE(1)                         TABLE (2)
+--------------------------------------+     +-----------+
|            SURVEYS TABLE             |     |   ICONS   |
+----+------+-------------+------------+     +----+------+
| ID | name | description |   iconID   |     | ID | ICON |
+----+------+-------------+------------+     +----+------+
|    |      |             | FOREIGN KEY|     
+--------------------------------------+     

I constructed a VIEW using the two tables.

public static final String VIEW_SURVEYS = "surveysview";

      public static final String VIEW_SURVEYS_CREATE =
       "CREATE VIEW " + VIEW_SURVEYS +
       " AS SELECT " + TABLE_SURVEYS + "." + KEY_ROWID + " AS _id, " + 
       TABLE_SURVEYS + "." + KEY_NAME + ", " +
       TABLE_SURVEYS + "." + KEY_DESCRIPTION + ", " +
       TABLE_ICONS + "." + KEY_ICON + " " +
       "FROM " + TABLE_SURVEYS + " JOIN " + TABLE_ICONS +
       " ON " + TABLE_SURVEYS + "." + KEY_ICONID + " = " +
                TABLE_ICONS + "." + KEY_ROWID;

The tables and views are called in my Adapter constructor.

private static class DatabaseHelper extends SQLiteOpenHelper 
{
    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    // TABLES
    db.execSQL(TABLE_ICONS_CREATE);
    db.execSQL(TABLE_SURVEYS_CREATE);

    // VIEWS
    db.execSQL(VIEW_SURVEYS_CREATE);
    }

And this is the method used to query the view.

    public Cursor getSurveys() {
return db.query(VIEW_SURVEYS, new String[] {
        KEY_ROWID, KEY_NAME, KEY_DESCRIPTION, KEY_ICON},
        null, null, null, null, null);
}

Which is called in my activity through a cursor

    DBAdapter db = new DBAdapter(this);
    db.open(); // Opens db session 
    Cursor survey_cursor = db.getSurveys();
    startManagingCursor(survey_cursor);

The problem is when I try run the above code, my program crashes. The line at it crashes is Cursor survey_cursor = db.getSurveys();

And Catlog show

11-25 16:19:42.324: ERROR/AndroidRuntime(6146): Caused by: android.database.sqlite.SQLiteException: no such table: surveysview: , while compiling: SELECT _id, name, description, icon FROM surveysview

I reread the code countless times and can't find anything开发者_Python百科 wrong. What am I missing?

Here's the output for SELECT * FROM sqlite_master

table|android_metadata|android_metadata|3|CREATE TABLE android_metadata (locale TEXT)

table|fieldtype|fieldtype|4|CREATE TABLE fieldtype (_id integer primary key autoincrement, type text not null)

table|sqlite_sequence|sqlite_sequence|5|CREATE TABLE sqlite_sequence(name,seq)

table|editicons|editicons|6|CREATE TABLE editicons (_id integer primary key autoincrement, icon text not null)

table|surveys|surveys|7|CREATE TABLE surveys (_id integer primary key autoincrement, name text not null, description text, editableid integer not null, FOREIGN KEY(editableid) REFERENCES editicons(_id))

table|questions|questions|8|CREATE TABLE questions (_id integer primary key autoincrement, field text not null, typeid text not null, survey_id integer not null, FOREIGN KEY(typeid) REFERENCES fieldtype(_id)FOREIGN KEY(survey_id) REFERENCES surveys(_id))

table|choices|choices|9|CREATE TABLE choices (choice text, question_id integer not null, FOREIGN KEY(question_id) REFERENCES questions(_id))

table|answers|answers|10|CREATE TABLE answers (choice_id integer not null, FOREIGN KEY(choice_id) REFERENCES choices(_id))

view|surveysview|surveysview|0|CREATE VIEW surveysview AS SELECT surveys._id AS _id, surveys.name AS name, surveys.description AS description, editicons.icon AS icon FROM surveys JOIN editicons ON surveys.editableid = editicons._id

(this output reflects some changes I made to the code since I added " AS " after each column)

Perhaps android.database.sqlite.SQLiteDatabase doesn't support views in calls to query; try using rawQuery such as:

return db.rawQuery("select * from " + VIEW_SURVEYS);

If this works, replace * with your column names.

Here's what I get in the sqlite3 shell using your schema:

e$ sqlite3
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE editicons (_id integer primary key autoincrement, icon text not null);
sqlite> CREATE TABLE surveys (_id integer primary key autoincrement, name text not null, description text, editableid integer not null, FOREIGN KEY(editableid) REFERENCES editicons(_id));
sqlite> CREATE VIEW surveysview AS SELECT surveys._id AS _id, surveys.name AS name, surveys.description AS description, editicons.icon AS icon FROM surveys JOIN editicons ON surveys.editableid = editicons._id;
sqlite> select * from surveysview;
sqlite> insert into editicons values (null,"icon1");
sqlite> select * from editicons;
1|icon1
sqlite> insert into surveys values (NULL, 'survey1', 'survey1 desc', 1);
sqlite> select * from surveysview;
1|survey1|survey1 desc|icon1
sqlite> 

So, if something is amis it must be in the Android implementation.

AH Finally found it!

It was an error in the method that populated ICONS table. Strangely, no error was thrown. So, when the View accessed that table, the error propagated.

I actually found the bug through the adb console, when reading contents of the ICONS table.

Corrected that, now works like a charm.