代码之家 › 专栏 › 技术社区 › Tivie

查询已创建的dbView(非表)时出错!

view sqlite database android

Tivie · 技术社区 · 14 年前

有关上下文,请参见 post

根据yock提供的awsome建议,我将数据库设计更改为使用外键。您可以在此检查整个数据库设计邮递 .

所以我有两张桌子。

              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

我无数次地重读代码,但没有发现任何错误。我错过了什么?

这是sqlite_master中SELECT*的输出

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”)

2 回复 | 直到 7 年前

Doug Currie 14 年前

可能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>

所以,如果有什么东西是amis,它必须在Android实现中。

Kaern Stone 12 年前

啊终于找到了!

填充图标表的方法有误。奇怪的是,没有抛出错误。因此,当视图访问该表时,错误就会传播。

实际上,我在阅读图标表的内容时,通过adb控制台发现了这个bug。

纠正了,现在工作像一个魅力。