java - SQLite 比较 Android 中的表

Question

是否有任何方法可以比较表(当然具有相同的属性)并在它们相同时返回 true，如果不同则返回 false？谢谢。

Answer 1

不是直接的，但它确实很容易。

即 sqlite 存储用于定义表 sqlite_master(架构)中的 SQL。

为了最简单地比较 SQL 与 SQL(对于列定义)，您可以使用以下 SQL:-

WITH tablenames(table1,table2) AS (SELECT 'table1' /*<<<<< change accordingly */,'table2' /*<<<<< change accordingly*/)
SELECT 
    COALESCE(
        (SELECT substr(sql,instr(sql,'(')) FROM sqlite_master WHERE name = (SELECT table1 FROM tablenames)) =
        (SELECT substr(sql,instr(sql,'(')) FROM sqlite_master WHERE name = (SELECT table2 FROM tablenames))
    ,0)
;

在 Android 中，这可以使用(在数据库助手中)来完成:-

public boolean compareTable(String table1, String table2) {
    SQLiteDatabase db = this.getWritableDatabase();
    String result_column = "result";
    boolean rv = false;
    Cursor csr = db.rawQuery("WITH tablenames(table1,table2) AS (SELECT ? ,? )" +
            "SELECT " +
            "COALESCE(" +
            "(SELECT substr(sql,instr(sql,'(')) FROM sqlite_master WHERE name = (SELECT table1 FROM tablenames)) =" +
            "(SELECT substr(sql,instr(sql,'(')) FROM sqlite_master WHERE name = (SELECT table2 FROM tablenames))" +
            ",0) " +
            "AS " + result_column +
            ";",new String[]{table1,table2});
    if (csr.moveToFirst()) {
        rv = (csr.getInt(csr.getColumnIndex(result_column)) > 0);
    }
    csr.close();
    return rv;
}

• 请注意，这会将 SQL 中最细微的差异视为比较失败

示例

数据库助手DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DBNAME = "mydb";
    public static final int DBVERSION = 1;

    public static final String TABLE = "htmlstore";
    public static final String IDCOLUMN = BaseColumns._ID;
    public static final String HTMLCOLUMN = "html";

    public static final String TABLE2 = "otherstore";
    public static final String TABLE3 = "storeother";

    SQLiteDatabase db;

    public DatabaseHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
        db = this.getWritableDatabase();

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String crt_htmlstore_table = "CREATE TABLE IF NOT EXISTS " + TABLE + "(" +
                IDCOLUMN + " INTEGER PRIMARY KEY," +
                HTMLCOLUMN + " TEXT" +
                ")";
        db.execSQL(crt_htmlstore_table);
        String crt_otherstore_table = "CREATE TABLE IF NOT EXISTS " + TABLE2 + "(" +
                IDCOLUMN + " INTEGER PRIMARY KEY," +
                HTMLCOLUMN + " TEXT" +
                ")";
        db.execSQL(crt_otherstore_table);
        String crt_storeother_table = "CREATE TABLE IF NOT EXISTS " + TABLE3 + "(" +
                HTMLCOLUMN + " TEXT, " +
                IDCOLUMN + " INTEGER, " +
                " PRIMARY KEY(" + IDCOLUMN + ")" +
                ")";
        db.execSQL(crt_storeother_table);
    }


    public long insert(String html) {
        ContentValues cv = new ContentValues();
        cv.put(HTMLCOLUMN,html);
        return db.insert(TABLE,null,cv);
    }

    public String getHTML(long id) {
        String rv = "";
        Cursor csr = db.query(TABLE,new String[]{HTMLCOLUMN},IDCOLUMN+"=?",new String[]{String.valueOf(id)},null,null,null);
        if (csr.moveToFirst()) {
            rv = csr.getString(csr.getColumnIndex(HTMLCOLUMN));
        }
        csr.close();
        return rv;
    }

    public boolean compareTable(String table1, String table2) {
        SQLiteDatabase db = this.getWritableDatabase();
        String result_column = "result";
        boolean rv = false;
        Cursor csr = db.rawQuery("WITH tablenames(table1,table2) AS (SELECT ? ,? )" +
                "SELECT " +
                "COALESCE(" +
                "(SELECT substr(sql,instr(sql,'(')) FROM sqlite_master WHERE name = (SELECT table1 FROM tablenames)) =" +
                "(SELECT substr(sql,instr(sql,'(')) FROM sqlite_master WHERE name = (SELECT table2 FROM tablenames))" +
                ",0) " +
                "AS " + result_column +
                ";",new String[]{table1,table2});
        if (csr.moveToFirst()) {
            rv = (csr.getInt(csr.getColumnIndex(result_column)) > 0);
        }
        csr.close();
        return rv;
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

• 请注意，实际上这 3 个表都是相同的(但第三个表在用于生成列定义的 SQL 中不同，因此将被视为不同)

• COALESCE 用于将 null(sqlite_master 中没有这样的表)转换为 0 (false)

调用代码MainActivity.java

public class MainActivity extends AppCompatActivity {

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        DatabaseHelper databaseHelper = new DatabaseHelper(this);

        Log.d("TABLECOMPARE"," Result = " + String.valueOf(databaseHelper.compareTable(DatabaseHelper.TABLE,DatabaseHelper.TABLE2)));
        Log.d("TABLECOMPARE"," Result = " + String.valueOf(databaseHelper.compareTable(DatabaseHelper.TABLE2,DatabaseHelper.TABLE3)));
        Log.d("TABLECOMPARE"," Result = " + String.valueOf(databaseHelper.compareTable(DatabaseHelper.TABLE,DatabaseHelper.TABLE3)));
        Log.d("TABLECOMPARE"," Result = " + String.valueOf(databaseHelper.compareTable(DatabaseHelper.TABLE,DatabaseHelper.TABLE)));
    }
}

结果:-

2020-01-14 07:23:29.624 D/TABLECOMPARE:  Result = true
2020-01-14 07:23:29.625 D/TABLECOMPARE:  Result = false
2020-01-14 07:23:29.625 D/TABLECOMPARE:  Result = false
2020-01-14 07:23:29.626 D/TABLECOMPARE:  Result = true

即

• 表 1 和表 2 被视为相同
• 表 2 和表 3 被视为不同
• 表 1 和表 3 被视为不同
• 表 1 被视为与表 1 相同。