查看在表create语句之前执行的create语句

Question

我有一个包含6个表的数据库。这些表是在内容提供商和数据库助手的帮助下创建的。当我使用这6个表运行应用程序时，应用程序工作，所有表都被创建，我可以查询表并插入数据。

但是，当我尝试将sqlite视图添加到我的数据库助手的onCreate方法时，应用程序会崩溃。

据我所知，视图的onCreate方法是在视图的联接中使用的表onCreate方法之前被调用的，无论出于什么原因。

我需要知道的是如何强制我的数据库助手首先创建表，然后创建视图。

由于这个问题的性质（“视图”一词用于指代其他事物），我很难找到解决方案。

以下是创建视图的代码：

public static final String TABLE_NAME = "recent_listens";

public static final String TIMESTAMP_ID = timestampTable.TIMESTAMP_ID;
public static final String TRACK_NAME = trackTable.TRACK_NAME;
public static final String TRACK_MBID = trackTable.TRACK_MBID;
public static final String ALBUM_NAME = albumTable.ALBUM_NAME;
public static final String ALBUM_MBID = albumTable.ALBUM_MBID;
public static final String ARTIST_NAME = artistTable.ARTIST_NAME;
public static final String ARTIST_MBID = artistTable.ARTIST_MBID;
public static final String TIMESTAMP_VALUE = timestampTable.TIMESTAMP_VALUE;
public static final String IMAGE_ID = albumTable.ALBUM_IMG_ID;

private static final String LISTEN_VIEW_CREATE = ""
        + "CREATE VIEW " + TABLE_NAME + " AS "
        + "SELECT "
            + timestampTable.TABLE_TIMESTAMP + "." + timestampTable.TIMESTAMP_ID + " AS " + TIMESTAMP_ID + ", "
            + trackTable.TRACK_NAME + " AS " + TRACK_NAME + ", "
            + trackTable.TRACK_MBID + " AS " + TRACK_MBID + ", "
            + albumTable.ALBUM_NAME + " AS " + ALBUM_NAME + ", "
            + albumTable.ALBUM_MBID + " AS " + ALBUM_MBID + ", "
            + artistTable.ARTIST_NAME + " AS " + ARTIST_NAME + ", "
            + artistTable.ARTIST_MBID + " AS " + ARTIST_MBID + ", "
            + timestampTable.TIMESTAMP_VALUE + " AS " + TIMESTAMP_VALUE + ", "
            + albumTable.ALBUM_IMG_ID + " AS " + IMAGE_ID
        + " FROM "
            + timestampTable.TABLE_TIMESTAMP
                + " INNER JOIN " + trackTable.TABLE_TRACK + " ON "
                    + timestampTable.TABLE_TIMESTAMP + "." + timestampTable.TIMESTAMP_CON_TRACK_ID + " = "
                    + trackTable.TABLE_TRACK + "." + trackTable.TRACK_ID
                + " INNER JOIN " + albumTable.TABLE_ALBUM + " ON "
                    + timestampTable.TABLE_TIMESTAMP + "." + timestampTable.TIMESTAMP_CON_ALBUM_ID + " = "
                    + albumTable.TABLE_ALBUM + "." + albumTable.ALBUM_ID;

public static synchronized void onCreate(SQLiteDatabase database) {
    database.execSQL(LISTEN_VIEW_CREATE);

这就是我得到的错误：

引起：android.database.sqlite.SQLiteExcture：no这样的列：artist_name（代码1）：，而编译：CREATE VIEWrecent_listensAS SELECTtimestamps_table_id_id，track_nametrack_name，track_mbidtrack_mbid，album_namealbum_name，album_mbidalbum_mbid，artist_nameartist_name，artist_mbidartist_mbid，时间戳作为时间戳，album_img_idalbum_img_id从timestamps_table内部连接轨道timestamps_table.track_id=轨道。_id内部连接相册timestamps_table.album_id=相册。_id

这是我的数据库助手类：

public class databaseHelper extends SQLiteOpenHelper {

private static final String DATABASE_NAME = "userData.db";
private static final int DATABASE_VERSION = 10;

public databaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
}

@Override
public void onCreate(SQLiteDatabase database) {
    artistTable.onCreate(database);
    albumTable.onCreate(database);
    trackTable.onCreate(database);
    tagTable.onCreate(database);
    similarTable.onCreate(database);
    timestampTable.onCreate(database);
    listenView.onCreate(database);
}

@Override
public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
    artistTable.onUpgrade(database, oldVersion, newVersion);
    albumTable.onUpgrade(database, oldVersion, newVersion);
    trackTable.onUpgrade(database, oldVersion, newVersion);
    tagTable.onUpgrade(database, oldVersion, newVersion);
    similarTable.onUpgrade(database, oldVersion, newVersion);
    timestampTable.onUpgrade(database, oldVersion, newVersion);
    listenView.onUpgrade(database, oldVersion, newVersion);
}

}

这是在我的自定义内容提供程序onCreate方法中调用的。

@Override
public boolean onCreate() {
    database = new databaseHelper(getContext());
    return false;
}

Answer 1

为了以防万一其他人会遇到同样的问题，我打算把这个留到一边。

事实证明，我在连接中没有包含select中提到的表。

生成的视图创建字符串如下：

private static final String LISTEN_VIEW_CREATE = ""
        + "CREATE VIEW " + TABLE_NAME + " AS "
        + "SELECT "
            + timestampTable.TABLE_TIMESTAMP + "." + timestampTable.TIMESTAMP_ID + " AS " + LISTEN_ID + ", "
            + trackTable.TRACK_NAME + " AS " + TRACK_NAME + ", "
            + trackTable.TRACK_MBID + " AS " + TRACK_MBID + ", "
            + albumTable.ALBUM_NAME + " AS " + ALBUM_NAME + ", "
            + albumTable.ALBUM_MBID + " AS " + ALBUM_MBID + ", "
            + artistTable.ARTIST_NAME + " AS " + ARTIST_NAME + ", "
            + artistTable.ARTIST_MBID + " AS " + ARTIST_MBID + ", "
            + timestampTable.TIMESTAMP_VALUE + " AS " + TIMESTAMP_VALUE + ", "
            + albumTable.ALBUM_IMG_ID + " AS " + IMAGE_ID
        + " FROM "
            + timestampTable.TABLE_TIMESTAMP
                + " INNER JOIN " + trackTable.TABLE_TRACK + " ON "
                    + timestampTable.TABLE_TIMESTAMP + "." + timestampTable.TIMESTAMP_CON_TRACK_ID + " = "
                    + trackTable.TABLE_TRACK + "." + trackTable.TRACK_ID
                + " INNER JOIN " + albumTable.TABLE_ALBUM + " ON "
                    + timestampTable.TABLE_TIMESTAMP + "." + timestampTable.TIMESTAMP_CON_ALBUM_ID + " = "
                    + albumTable.TABLE_ALBUM + "." + albumTable.ALBUM_ID
                + " INNER JOIN " + artistTable.TABLE_ARTIST + " ON "
                    + timestampTable.TABLE_TIMESTAMP + "." + timestampTable.TIMESTAMP_CON_ARTIST_ID + " = "
                    + artistTable.TABLE_ARTIST + "." + artistTable.ARTIST_ID;

Answer 2

android.database.sqlite.SQLiteException: no such column: artist_name

这表明视图试图访问该表中不存在的列（我认为timestamps_表）。很可能是因为您添加了artist_name表，忘记在onUpgrade（）中增加数据库的新版本
修复方法：升级db的版本或卸载应用程序并再次运行，或从设置中清除应用程序的数据