查询创建的 dbView（不是表）时出错！

Question

有关上下文，请参阅 post

根据 yock 提供的很棒的建议，我更改了数据库设计以使用外键。您可以在此检查总体数据库设计 帖子。

所以我有2张桌子。

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

我使用这两个表构建了一个视图。

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;

表和视图在我的适配器构造函数中调用。

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);
    }

这就是用来查询视图的方法。

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

这是通过游标在我的活动中调用的

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

问题是当我尝试运行上面的代码时，我的程序崩溃了。崩溃的那一行是 Cursor Survey_cursor = db.getSurveys();

Catlog 显示

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

我重新阅读了代码无数次，但找不到任何错误。我缺少什么？

这是 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

（此输出反映了我对代码所做的一些更改，因为我在每列后添加了“ AS ”）

Answer 1

也许 android.database.sqlite.SQLiteDatabase 不支持调用 query 中的视图；尝试使用 rawQuery，例如：

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

如果有效，请将 * 替换为您的列名称。

这是我使用您的模式在 sqlite3 shell 中得到的结果：

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> 

因此，如果有什么问题，它一定是在 Android 实现中。

Answer 2

啊终于找到了！

这是填充 ICONS 表的方法中的错误。奇怪的是，没有抛出任何错误。因此，当视图访问该表时，错误就会传播。

实际上，我在阅读 ICONS 表的内容时通过 adb 控制台发现了该错误。

更正了这一点，现在就像魅力一样。