123xiaomi/NotesDatabaseHelper.java

// 创建笔记表的SQL语句
private static final String CREATE_NOTE_TABLE_SQL = "CREATE TABLE " + TABLE.NOTE + " ("
        + NoteColumns.ID + " TEXT PRIMARY KEY, "
        + NoteColumns.TITLE + " TEXT NOT NULL, "
        + NoteColumns.CONTENT + " TEXT NOT NULL, "
        + NoteColumns.CREATED_DATE + " INTEGER NOT NULL, "
        + NoteColumns.MODIFIED_DATE + " INTEGER NOT NULL, "
        + NoteColumns.TYPE + " INTEGER NOT NULL, "
        + NoteColumns.PARENT_ID + " TEXT NOT NULL, "
        + NoteColumns.GTASK_ID + " TEXT NOT NULL DEFAULT '', "
        + NoteColumns.VERSION + " INTEGER NOT NULL DEFAULT 0"
        + ");";

// 创建与笔记表相关的触发器的SQL语句
private static final String NOTE_INCREASE_FOLDER_COUNT_ON_UPDATE_TRIGGER = "CREATE TRIGGER increase_folder_count_on_update AFTER UPDATE ON " + TABLE.NOTE
        + " BEGIN"
        + " UPDATE " + TABLE.NOTE + " SET " + NoteColumns.COUNT + " = " + NoteColumns.COUNT + " + 1"
        + " WHERE " + NoteColumns.ID + " = new." + NoteColumns.PARENT_ID + ";"
        + " END;";
// 类似的触发器SQL语句省略...

// 创建数据表的SQL语句
private static final String CREATE_DATA_TABLE_SQL = "CREATE TABLE " + TABLE.DATA + " ("
        + DataColumns.ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
        + DataColumns.Note_ID + " TEXT NOT NULL, "
        + DataColumns.TYPE + " INTEGER NOT NULL, "
        + DataColumns.CONTENT + " TEXT NOT NULL, "
        + DataColumns.CREATED_DATE + " INTEGER NOT NULL"
        + ");";

// 创建与数据表相关的触发器的SQL语句
private static final String DATA_UPDATE_NOTE_CONTENT_ON_INSERT_TRIGGER = "CREATE TRIGGER update_note_content_on_insert AFTER INSERT ON " + TABLE.DATA
        + " BEGIN"
        + " UPDATE " + TABLE.NOTE + " SET " + NoteColumns.CONTENT + " = new." + DataColumns.CONTENT
        + " WHERE " + NoteColumns.ID + " = new." + DataColumns.Note_ID + ";"
        + " END;";
// 类似的触发器SQL语句省略...

// 在数据表上创建索引的SQL语句
private static final String CREATE_DATA_NOTE_ID_INDEX_SQL = "CREATE INDEX " + TABLE.DATA + "_note_id_index ON "
        + TABLE.DATA + " (" + DataColumns.Note_ID + ");";

// 删除触发器的SQL语句
private static final String DROP_TRIGGER_SQL = "DROP TRIGGER IF EXISTS %s;";

// 插入系统文件夹的SQL语句
private static final String INSERT_SYSTEM_FOLDER_SQL = "INSERT INTO " + TABLE.NOTE + " ("
        + NoteColumns.ID + ", "
        + NoteColumns.TYPE + ", "
        + NoteColumns.TITLE + ", "
        + NoteColumns.CONTENT + ", "
        + NoteColumns.CREATED_DATE + ", "
        + NoteColumns.MODIFIED_DATE + ", "
        + NoteColumns.PARENT_ID + ")"
        + " VALUES (?, ?, ?, ?, ?, ?, ?);";

// 更新笔记表的SQL语句
private static final String UPDATE_NOTE_TABLE_SQL = "UPDATE " + TABLE.NOTE + " SET "
        + NoteColumns.TITLE + " = ?, "
        + NoteColumns.CONTENT + " = ?, "
        + NoteColumns.MODIFIED_DATE + " = ?, "
        + NoteColumns.PARENT_ID + " = ?"
        + " WHERE " + NoteColumns.ID + " = ?;";

// 删除笔记的SQL语句
private static final String DELETE_NOTE_SQL = "DELETE FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.ID + " = ?;";

// 查询笔记的SQL语句
private static final String QUERY_NOTES_SQL = "SELECT * FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.PARENT_ID + " = ?"
        + " ORDER BY " + NoteColumns.MODIFIED_DATE + " DESC;";

// 按ID查询笔记的SQL语句
private static final String QUERY_NOTE_BY_ID_SQL = "SELECT * FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.ID + " = ?;";

// 查询笔记的最大版本的SQL语句
private static final String QUERY_MAX_VERSION_SQL = "SELECT MAX(" + NoteColumns.VERSION + ") FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.ID + " = ?;";

// 更新笔记版本的SQL语句
private static final String UPDATE_NOTE_VERSION_SQL = "UPDATE " + TABLE.NOTE + " SET "
        + NoteColumns.VERSION + " = ?"
        + " WHERE " + NoteColumns.ID + " = ?;";

// 删除旧笔记的SQL语句
private static final String DELETE_OLD_NOTES_SQL = "DELETE FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.VERSION + " < ?;";

// 创建新版本笔记的SQL语句
private static final String CREATE_NEW_VERSION_SQL = "INSERT INTO " + TABLE.NOTE + " ("
        + NoteColumns.ID + ", "
        + NoteColumns.TITLE + ", "
        + NoteColumns.CONTENT + ", "
        + NoteColumns.CREATED_DATE + ", "
        + NoteColumns.MODIFIED_DATE + ", "
        + NoteColumns.TYPE + ", "
        + NoteColumns.PARENT_ID + ", "
        + NoteColumns.GTASK_ID + ", "
        + NoteColumns.VERSION + ")"
        + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);";

// 按类型查询笔记的SQL语句
private static final String QUERY_NOTES_BY_TYPE_SQL = "SELECT * FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.TYPE + " = ?"
        + " ORDER BY " + NoteColumns.MODIFIED_DATE + " DESC;";

// 按标题查询笔记的SQL语句
private static final String QUERY_NOTES_BY_TITLE_SQL = "SELECT * FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.TITLE + " LIKE ?"
        + " ORDER BY " + NoteColumns.MODIFIED_DATE + " DESC;";

// 按内容查询笔记的SQL语句
private static final String QUERY_NOTES_BY_CONTENT_SQL = "SELECT * FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.CONTENT + " LIKE ?"
        + " ORDER BY " + NoteColumns.MODIFIED_DATE + " DESC;";

// 按日期范围查询笔记的SQL语句
private static final String QUERY_NOTES_BY_DATE_SQL = "SELECT * FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.CREATED_DATE + " BETWEEN ? AND ?"
        + " ORDER BY " + NoteColumns.MODIFIED_DATE + " DESC;";

// 按GTASK ID查询笔记的SQL语句
private static final String QUERY_NOTES_BY_GTASK_ID_SQL = "SELECT * FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.GTASK_ID + " = ?;";

// 更新笔记的GTASK ID的SQL语句
private static final String UPDATE_NOTE_GTASK_ID_SQL = "UPDATE " + TABLE.NOTE + " SET "
        + NoteColumns.GTASK_ID + " = ?"
        + " WHERE " + NoteColumns.ID + " = ?;";

// 查询回收站中的笔记的SQL语句
private static final String QUERY_TRASH_NOTES_SQL = "SELECT * FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.PARENT_ID + " = " + Notes.ID_TRASH_FOLER
        + " ORDER BY " + NoteColumns.MODIFIED_DATE + " DESC;";

// 从回收站恢复笔记的SQL语句
private static final String RESTORE_NOTE_FROM_TRASH_SQL = "UPDATE " + TABLE.NOTE + " SET "
        + NoteColumns.PARENT_ID + " = ?"
        + " WHERE " + NoteColumns.ID + " = ?;";

// 永久删除笔记的SQL语句
private static final String PERMANENTLY_DELETE_NOTE_SQL = "DELETE FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.ID + " = ?;";

// 查询文件夹中笔记数量的SQL语句
private static final String QUERY_FOLDER_COUNT_SQL = "SELECT COUNT(*) FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.PARENT_ID + " = ?;";

// 更新文件夹中笔记数量的SQL语句
private static final String UPDATE_FOLDER_COUNT_SQL = "UPDATE " + TABLE.NOTE + " SET "
        + NoteColumns.COUNT + " = ?"
        + " WHERE " + NoteColumns.ID + " = ?;";

// 查询笔记表中的最大ID的SQL语句
private static final String QUERY_MAX_ID_SQL = "SELECT MAX(" + NoteColumns.ID + ") FROM " + TABLE.NOTE;

// 插入新笔记的SQL语句
private static final String INSERT_NOTE_SQL = "INSERT INTO " + TABLE.NOTE + " ("
        + NoteColumns.ID + ", "
        + NoteColumns.TITLE + ", "
        + NoteColumns.CONTENT + ", "
        + NoteColumns.CREATED_DATE + ", "
        + NoteColumns.MODIFIED_DATE + ", "
        + NoteColumns.TYPE + ", "
        + NoteColumns.PARENT_ID + ")"
        + " VALUES (?, ?, ?, ?, ?, ?, ?);";

// 更新笔记标题的SQL语句
private static final String UPDATE_NOTE_TITLE_SQL = "UPDATE " + TABLE.NOTE + " SET "
        + NoteColumns.TITLE + " = ?"
        + " WHERE " + NoteColumns.ID + " = ?;";

// 更新笔记内容的SQL语句
private static final String UPDATE_NOTE_CONTENT_SQL = "UPDATE " + TABLE.NOTE + " SET "
        + NoteColumns.CONTENT + " = ?"
        + " WHERE " + NoteColumns.ID + " = ?;";

// 更新笔记修改日期的SQL语句
private static final String UPDATE_NOTE_MODIFIED_DATE_SQL = "UPDATE " + TABLE.NOTE + " SET "
        + NoteColumns.MODIFIED_DATE + " = ?"
        + " WHERE " + NoteColumns.ID + " = ?;";

// 更新笔记父ID的SQL语句
private static final String UPDATE_NOTE_PARENT_ID_SQL = "UPDATE " + TABLE.NOTE + " SET "
        + NoteColumns.PARENT_ID + " = ?"
        + " WHERE " + NoteColumns.ID + " = ?;";

// 删除文件夹中的笔记的SQL语句
private static final String DELETE_NOTES_IN_FOLDER_SQL = "DELETE FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.PARENT_ID + " = ?;";

// 按父ID查询笔记的SQL语句
private static final String QUERY_NOTES_BY_PARENT_ID_SQL = "SELECT * FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.PARENT_ID + " = ?;";

// 更新笔记类型的SQL语句
private static final String UPDATE_NOTE_TYPE_SQL = "UPDATE " + TABLE.NOTE + " SET "
        + NoteColumns.TYPE + " = ?"
        + " WHERE " + NoteColumns.ID + " = ?;";

// 按类型和父ID查询笔记的SQL语句
private static final String QUERY_NOTES_BY_TYPE_AND_PARENT_ID_SQL = "SELECT * FROM " + TABLE.NOTE
        + " WHERE " + NoteColumns.TYPE + " = ? AND "
        + NoteColumns.PARENT_ID + " = ?;"
        + " ORDER BY " + NoteColumns.MODIFIED_DATE + " DESC;";

/**
 * 构造函数，初始化数据库帮助器。
 *
 * @param context 上下文对象
 */
public NotesDatabaseHelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
}

/**
 * 创建笔记表。
 *
 * @param db 数据库对象
 */
public void createNoteTable(SQLiteDatabase db) {
    db.execSQL(CREATE_NOTE_TABLE_SQL);
    reCreateNoteTableTriggers(db);
    createSystemFolder(db);
    Log.d(TAG, "note table has been created");
}

/**
 * 重新创建笔记表的触发器。
 *
 * @param db 数据库对象
 */
private void reCreateNoteTableTriggers(SQLiteDatabase db) {
    db.execSQL("DROP TRIGGER IF EXISTS increase_folder_count_on_update");
    db.execSQL("DROP TRIGGER IF EXISTS decrease_folder_count_on_update");
    db.execSQL("DROP TRIGGER IF EXISTS decrease_folder_count_on_delete");
    db.execSQL("DROP TRIGGER IF EXISTS delete_data_on_delete");
    db.execSQL("DROP TRIGGER IF EXISTS increase_folder_count_on_insert");
    db.execSQL("DROP TRIGGER IF EXISTS folder_delete_notes_on_delete");
    db.execSQL("DROP TRIGGER IF EXISTS folder_move_notes_on_trash");

    db.execSQL(NOTE_INCREASE_FOLDER_COUNT_ON_UPDATE_TRIGGER);
    db.execSQL(NOTE_DECREASE_FOLDER_COUNT_ON_UPDATE_TRIGGER);
    db.execSQL(NOTE_DECREASE_FOLDER_COUNT_ON_DELETE_TRIGGER);
    db.execSQL(NOTE_DELETE_DATA_ON_DELETE_TRIGGER);
    db.execSQL(NOTE_INCREASE_FOLDER_COUNT_ON_INSERT_TRIGGER);
    db.execSQL(FOLDER_DELETE_NOTES_ON_DELETE_TRIGGER);
    db.execSQL(FOLDER_MOVE_NOTES_ON_TRASH_TRIGGER);
}

/**
 * 创建系统文件夹。
 *
 * @param db 数据库对象
 */
private void createSystemFolder(SQLiteDatabase db) {
    ContentValues values = new ContentValues();

    /**
     * 通话记录文件夹，用于存储通话笔记
     */
    values.put(NoteColumns.ID, Notes.ID_CALL_RECORD_FOLDER);
    values.put(NoteColumns.TYPE, Notes.TYPE_SYSTEM);
    db.insert(TABLE.NOTE, null, values);

    /**
     * 根文件夹，作为默认文件夹
     */
    values.clear();
    values.put(NoteColumns.ID, Notes.ID