我正在为android制作一个应用程序，允许用户将.db文件备份到他的设备中。这些.db文件是sqlite从我的应用程序生成的数据库。当用户恢复这些文件时，我想检查他是否没有编辑它，并且它仍然是我的应用程序数据库格式(就像下面的代码创建的(。如需澄清，请发表评论。
String SQL_CREATE_FLIGHT_TABLE = "CREATE TABLE " + flightsEntery.TABLE_NAME + "("
+ flightsEntery.COLUMN_id + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ flightsEntery.COLUMN_mat + " TEXT NOT NULL, "
+ flightsEntery.COLUMN_ori + " TEXT NOT NULL, "
+ flightsEntery.COLUMN_des + " TEXT NOT NULL, "
+ flightsEntery.COLUMN_data + " TEXT NOT NULL, "
+ flightsEntery.COLUMN_mil + " INTEGER NOT NULL DEFAULT 0, "
+ flightsEntery.COLUMN_Npou + " INTEGER NOT NULL DEFAULT 1, "
+ flightsEntery.COLUMN_IFRapp + " INTEGER NOT NULL, "
+ flightsEntery.COLUMN_Func + " INTEGER NOT NULL, "
+ flightsEntery.COLUMN_OBS + " TEXT, "
+ flightsEntery.COLUMN_Hdec + " TEXT NOT NULL, "
+ flightsEntery.COLUMN_Hpou + " TEXT NOT NULL, "
+ flightsEntery.COLUMN_Hsol + " TEXT NOT NULL, "
+ flightsEntery.COLUMN_Hhora + " TEXT NOT NULL, "
+ flightsEntery.COLUMN_Hdiu + " TEXT NOT NULL, "
+ flightsEntery.COLUMN_Hnot + " TEXT NOT NULL, "
+ flightsEntery.COLUMN_Hifr + " INTEGER NOT NULL DEFAULT 0, "
+ flightsEntery.COLUMN_Hvfr + " TEXT NOT NULL, "
+ flightsEntery.COLUMN_Hnav + " TEXT NOT NULL, "
+ flightsEntery.COLUMN_Sobcapota + " TEXT NOT NULL DEFAULT '00:00', "
+ flightsEntery.COLUMN_Hcorte + " TEXT NOT NULL, "
+ flightsEntery.COLUMN_Hacc + " TEXT NOT NULL, "
+ flightsEntery.COLUMN_VooPara + " INTEGER NOT NULL DEFAULT 0, "
+ flightsEntery.COLUMN_VooReb + " INTEGER NOT NULL DEFAULT 0, "
+ flightsEntery.COLUMN_Trab + " INTEGER NOT NULL DEFAULT 0, "
+ flightsEntery.COLUMN_OrdyData + " INTERGER NOT NULL, "
+ flightsEntery.COLUMN_CivF + " INTEGER NOT NULL DEFAULT 0, "
+ flightsEntery.COLUMN_CivD + " INTEGER NOT NULL DEFAULT 0, "
+ flightsEntery.COLUMN_DiB + " INTEGER NOT NULL DEFAULT 0);";
我很乐意尝试任何想法，谢谢！
PS.：我不想比较数据库在每个细节上是否相等，我只想知道列是否相同。
我相信您可以使用以下方法，该方法获取两个文件，并根据查询比较两者之间的架构(sqlite_master(，该查询通过名称、类型和sql列在两个组之间建立联合(如果它们相同，则每个组将有2个(，并输出没有2行的行(即不匹配(。如果返回的Cursor没有行，则Schema匹配。
public class CompareDBSchemas {
public static boolean areDBSchemasEqual(File db1File, File db2File, boolean compareSizes) {
boolean rv = true;
if (!(db1File.exists() && db2File.exists())) return false;
if (compareSizes) {
if (db1File.length() != db2File.length()) return false;
}
SQLiteDatabase db1 = SQLiteDatabase.openDatabase(db1File.getPath(),null,SQLiteDatabase.OPEN_READWRITE);
db1.execSQL("ATTACH '" +
db2File.getPath() + "' AS other");
/*
WITH cte AS (SELECT * FROM main.sqlite_master UNION ALL SELECT * FROM other.sqlite_master)
SELECT * FROM cte GROUP BY type,name,sql HAVING count() <> 2
*/
Cursor csr = db1.rawQuery("WITH cte AS (" +
"SELECT * FROM main.sqlite_master UNION ALL SELECT * FROM other.sqlite_master" +
") " +
"SELECT * FROM cte GROUP BY type,name,sql HAVING count() <> 2 ",
null
);
if (csr.getCount() > 0) {
rv = false;
}
csr.close();
db1.close();
return rv;
}
public static boolean areDBSchemasEqual(File db1File, File db2File) {
return areDBSchemasEqual(db1File,db2File,false);
}
}
- 请注意，上面有检测数据库大小差异的选项，这可能会检测更改的数据，而不仅仅是模式更改
示例用法
下面演示了在创建3个数据库时使用的上述内容，第一个和第二个数据库具有相同的模式，第三个不同(添加了额外的列(。
数据库hlpr.java
public class DatabaseHlpr extends SQLiteOpenHelper {
public static final String TABLE_NAME = "mytable";
public static final String MYTABLE_COL_ID = BaseColumns._ID;
public static final String MYTABLE_COL_COMMON = "common";
public static final String MYTABLE_COL_SCHEMA1ONLY = "schema1only";
private int mSchema;
private SQLiteDatabase mDB;
public DatabaseHlpr(@Nullable Context context, @Nullable String name, int schema) {
super(context, name, null,1);
this.mSchema = schema;
mDB = this.getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
switch (mSchema) {
case 1:
useSchema1(db);
break;
default:
useSchema0(db);
}
}
private void useSchema1(SQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "(" +
MYTABLE_COL_ID + " INTEGER PRIMARY KEY, " +
MYTABLE_COL_COMMON + " TEXT, " +
MYTABLE_COL_SCHEMA1ONLY + " TEXT " +
")");
}
private void useSchema0(SQLiteDatabase db) {
db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "(" +
MYTABLE_COL_ID + " INTEGER PRIMARY KEY, " +
MYTABLE_COL_COMMON + " TEXT" +
")");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
主活动.java
public class MainActivity extends AppCompatActivity {
static final String DB1NAME = "db1", DB2NAME = "db2", DB3NAME = "db3";
DatabaseHlpr mDB1Hlpr, mDB2Hlpr, mDB3Hlpr;
File db1File, db2File,db3File;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDB1Hlpr = new DatabaseHlpr(this,DB1NAME,0);
mDB2Hlpr = new DatabaseHlpr(this,DB2NAME,0);
mDB3Hlpr = new DatabaseHlpr(this,DB3NAME,1);
mDB1Hlpr.close();
mDB2Hlpr.close();
mDB3Hlpr.close();
db1File = new File(this.getDatabasePath(DB1NAME).getPath());
db2File = new File(this.getDatabasePath(DB2NAME).getPath());
db3File = new File(this.getDatabasePath(DB3NAME).getPath());
String result = " the same ";
if (!CompareDBSchemas.areDBSchemasEqual(db1File,db2File,false)) {
result = " NOT the same ";
}
Log.d("RESULTINFO",
"Database Schemas are " + result +
" for " +
"nt" +db1File.getPath() +
"n and nt" + db2File.getPath()
);
result = " the same ";
if (!CompareDBSchemas.areDBSchemasEqual(db1File,db3File)) {
result = " NOT the same ";
}
Log.d("RESULTINFO",
"Database Schemas are " + result +
" for " +
"nt" +db1File.getPath() +
"n and nt" + db3File.getPath()
);
}
}
- 请注意，上面的内容是为了演示比较过程，因此文件检索简单方便。它并不打算反映如何检索文件
结果：-
2019-10-27 07:19:23.688 28976-28976/aso.so58566618dbcompareschema D/RESULTINFO: Database Schemas are the same for
/data/user/0/aso.so58566618dbcompareschema/databases/db1
and
/data/user/0/aso.so58566618dbcompareschema/databases/db2
2019-10-27 07:19:23.693 28976-28976/aso.so58566618dbcompareschema D/RESULTINFO: Database Schemas are NOT the same for
/data/user/0/aso.so58566618dbcompareschema/databases/db1
and
/data/user/0/aso.so58566618dbcompareschema/databases/db3
附加
如果您希望能够检查行计数(插入或删除的数据(和实际数据(通过更新更改的数据(，则可以使用CompareDBSchemas类的以下自适应：-
public class CompareDBSchemas {
private static final String
SQLITE_MASTER = "sqlite_master",
SQLITE_MASTER_TYPE_COLUMN = "type",
SQLITE_MASTER_NAME_COLUMN = "name",
SQLITE_MASTER_SQL_COLUMN = "sql",
SQLITE_MASTER_TABLE_TYPE = "table",
SQLITE_SYSTEMTABLES = "sqlite_",
ANDROID_METADATA = "android_metadata",
CTE_NAME = "cte", MAIN_SCHEMA = "main", OTHER_SCHEMA = "other"
;
public static boolean areDBSchemasEqual(File db1File, File db2File, boolean compareSizes, boolean compareRowCounts, boolean compareData) {
boolean rv = true;
if (!(db1File.exists() && db2File.exists())) return false;
if (compareSizes) {
if (db1File.length() != db2File.length()) return false;
}
SQLiteDatabase db1 = SQLiteDatabase.openDatabase(db1File.getPath(),null,SQLiteDatabase.OPEN_READWRITE);
db1.beginTransaction();
db1.execSQL("ATTACH '" +
db2File.getPath() + "' AS " + OTHER_SCHEMA);
/*
WITH cte AS (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_master)
SELECT * FROM cte GROUP BY type,name,sql HAVING count() <> 2
*/
Cursor csr = db1.rawQuery("WITH " + CTE_NAME + " AS (" +
"SELECT * FROM " + MAIN_SCHEMA + "." + SQLITE_MASTER +
" UNION ALL " +
"SELECT * FROM " + OTHER_SCHEMA + "." + SQLITE_MASTER +
") " +
"SELECT * FROM " + CTE_NAME +
" GROUP BY " +
SQLITE_MASTER_TYPE_COLUMN + "," +
SQLITE_MASTER_NAME_COLUMN + "," +
SQLITE_MASTER_SQL_COLUMN +
" HAVING count() <> 2 ",
null
);
if (csr.getCount() > 0) {
rv = false;
}
if (compareRowCounts && rv) {
csr = db1.rawQuery("SELECT * FROM main." + SQLITE_MASTER +
" WHERE " + SQLITE_MASTER_TYPE_COLUMN +
" = '" + SQLITE_MASTER_TABLE_TYPE +
"' AND (" + SQLITE_MASTER_NAME_COLUMN +
" NOT LIKE '" + SQLITE_SYSTEMTABLES +
"%' AND " + SQLITE_MASTER_NAME_COLUMN +
" <> '" + ANDROID_METADATA + "')",null);
while(csr.moveToNext()) {
if (
DatabaseUtils.queryNumEntries(db1,MAIN_SCHEMA +"." + csr.getString(csr.getColumnIndex(SQLITE_MASTER_NAME_COLUMN))) ==
DatabaseUtils.queryNumEntries(db1,OTHER_SCHEMA + "." + csr.getString(csr.getColumnIndex(SQLITE_MASTER_NAME_COLUMN)))
) continue;
rv = false;
break;
}
}
if (compareData && rv) {
csr.moveToPosition(-1);
while (csr.moveToNext()) {
if (
isTableDataTheSame(db1,csr.getString(csr.getColumnIndex(SQLITE_MASTER_NAME_COLUMN)))
) continue;
rv = false;
break;
}
}
db1.endTransaction();
csr.close();
db1.close();
return rv;
}
private static boolean isTableDataTheSame(SQLiteDatabase db, String table) {
boolean rv = true;
Cursor csr = db.rawQuery("PRAGMA table_info("+ table +")",null);
StringBuilder columnConcat = new StringBuilder();
while (csr.moveToNext()) {
if (columnConcat.length() > 0) columnConcat.append("||");
columnConcat.append(csr.getString(csr.getColumnIndex(SQLITE_MASTER_NAME_COLUMN)));
}
csr = db.rawQuery("WITH " + CTE_NAME +
" AS (" +
" SELECT " + columnConcat.toString() +
" AS comparison FROM " + MAIN_SCHEMA + "." + table +
" UNION ALL SELECT " + columnConcat.toString() + " FROM " + OTHER_SCHEMA + "." + table +
") SELECT * FROM " + CTE_NAME +
" GROUP BY comparison HAVING count() <> 2",
null
);
if (csr.getCount() > 0) {
rv = false;
}
csr.close();
return rv;
}
public static boolean areDBSchemasEqual(File db1File, File db2File) {
return areDBSchemasEqual(db1File,db2File,false,false,false);
}
}