package ml.puredark.hviewer.dataholders;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import ml.puredark.hviewer.helpers.Logger;
public class DBHelper {
private final static String dbName = "hviewer.db";
private SQLiteHelper mSqliteHelper = null;
public DBHelper() {
}
public synchronized void open(Context context) {
close();
mSqliteHelper = new SQLiteHelper(context, dbName, null, 7);
}
public synchronized void insert(String sql) {
if (mSqliteHelper == null) {
return;
}
mSqliteHelper.getWritableDatabase().execSQL(sql);
}
public synchronized long insert(String table, ContentValues values) {
if (mSqliteHelper == null) {
return -1;
}
return mSqliteHelper.getWritableDatabase().insert(table, null, values);
}
public synchronized int update(String table, ContentValues values,
String whereClause, String... whereArgs) {
if (mSqliteHelper == null) {
return -1;
}
return mSqliteHelper.getWritableDatabase().update(table, values,
whereClause, whereArgs);
}
public synchronized Cursor query(String table, String[] columns,
String selection, String[] selectionArgs, String groupBy,
String having, String orderBy) {
if (mSqliteHelper == null) {
return null;
}
return mSqliteHelper.getReadableDatabase().query(table, columns,
selection, selectionArgs, groupBy, having, orderBy);
}
public synchronized Cursor query(String sql) {
if (mSqliteHelper == null) {
return null;
}
return mSqliteHelper.getReadableDatabase().rawQuery(sql, null);
}
public synchronized Cursor query(String sql, String... args) {
if (mSqliteHelper == null) {
return null;
}
return mSqliteHelper.getReadableDatabase().rawQuery(sql, args);
}
public synchronized void nonQuery(String sql) {
if (mSqliteHelper == null) {
return;
}
mSqliteHelper.getWritableDatabase().execSQL(sql);
}
public synchronized void nonQuery(String sql, String... args) {
if (mSqliteHelper == null) {
return;
}
mSqliteHelper.getWritableDatabase().execSQL(sql, args);
}
public synchronized int delete(String table, String whereClause,
String[] whereArgs) {
if (mSqliteHelper == null) {
return -1;
}
return mSqliteHelper.getWritableDatabase().delete(table, whereClause,
whereArgs);
}
public synchronized void close() {
if (mSqliteHelper != null) {
mSqliteHelper.close();
mSqliteHelper = null;
}
}
public class SQLiteHelper extends SQLiteOpenHelper {
public SQLiteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,
int version) {
super(context, name, factory, version);
}
//创建SQLiteOpenHelper的子类，并重写onCreate及onUpgrade方法。
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE `sites`(`sid` integer primary key autoincrement, `title`, `indexUrl`, `galleryUrl`, `json` text, `index` integer, `gid` integer)");
db.execSQL("CREATE TABLE `siteGroups`(`gid` integer primary key autoincrement, `title`, `index` integer)");
db.execSQL("CREATE TABLE `histories`(`hid` integer primary key autoincrement, `idCode`, `title`, `referer`, `json` text, `index` integer)");
db.execSQL("CREATE TABLE `favourites`(`fid` integer primary key autoincrement, `idCode`, `title`, `referer`, `json` text, `index` integer, `gid` integer)");
db.execSQL("CREATE TABLE `favGroups`(`gid` integer primary key autoincrement, `title`, `index` integer)");
db.execSQL("CREATE TABLE `downloads`(`did` integer primary key autoincrement, `idCode`, `title`, `referer`, `json` text, `index` integer, `gid` integer)");
db.execSQL("CREATE TABLE `dlGroups`(`gid` integer primary key autoincrement, `title`, `index` integer)");
db.execSQL("CREATE TABLE `searchSuggestions`(`title` text primary key)");
db.execSQL("CREATE TABLE `siteTags`(`sid` integer, `title` text, `url` text, FOREIGN KEY(`sid`) REFERENCES `sites`(`sid`), PRIMARY KEY(`sid`,`title`))");
db.execSQL("CREATE TABLE `favorSiteTags`(`tid` integer primary key autoincrement, `title` text, `url` text, `index` integer)");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Logger.d("SQLiteHelper", "onUpgrade: oldVersion=" + oldVersion + " newVersion=" + newVersion);
for (int currVer = oldVersion; currVer < newVersion; currVer++) {
upgrade(db, currVer, currVer + 1);
}
}
private void upgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (oldVersion == 1 && newVersion == 2) {
db.execSQL("DROP TABLE `downloads`;");
db.execSQL("CREATE TABLE `downloads`(`did` integer primary key autoincrement, `idCode`, `title`, `referer`, `json` text)");
} else if (oldVersion == 2 && newVersion == 3) {
db.execSQL("ALTER TABLE `sites` RENAME TO `_temp_sites`;");
db.execSQL("CREATE TABLE `sites`(`sid` integer primary key autoincrement, `title`, `indexUrl`, `galleryUrl`, `json` text, `index` integer)");
db.execSQL("INSERT INTO `sites` SELECT `sid`, `title`, `indexUrl`, `galleryUrl`, `json`,`sid` AS `index` FROM `_temp_sites`;");
db.execSQL("DROP TABLE `_temp_sites`;");
} else if (oldVersion == 3 && newVersion == 4) {
db.execSQL("ALTER TABLE `sites` RENAME TO `_temp_sites`;");
db.execSQL("CREATE TABLE `sites`(`sid` integer primary key autoincrement, `title`, `indexUrl`, `galleryUrl`, `json` text, `index` integer, `gid` integer)");
db.execSQL("CREATE TABLE `siteGroups`(`gid` integer primary key autoincrement, `title`, `index` integer)");
db.execSQL("INSERT INTO `siteGroups` VALUES(1, \"未分类\", 1);");
db.execSQL("INSERT INTO `sites` SELECT `sid`, `title`, `indexUrl`, `galleryUrl`, `json`, `index`, 1 AS `gid` FROM `_temp_sites`;");
db.execSQL("DROP TABLE `_temp_sites`;");
} else if (oldVersion == 4 && newVersion == 5) {
db.execSQL("CREATE TABLE `siteTags`(`sid` integer, `title` text, `url` text, FOREIGN KEY(`sid`) REFERENCES `sites`(`sid`), PRIMARY KEY(`sid`,`title`))");
db.execSQL("CREATE TABLE `favorSiteTags`(`tid` integer primary key autoincrement, `title` text, `url` text, `index` integer)");
} else if (oldVersion == 5 && newVersion == 6) {
db.execSQL("ALTER TABLE `favourites` RENAME TO `_temp_favourites`;");
db.execSQL("CREATE TABLE `favourites`(`fid` integer primary key autoincrement, `idCode`, `title`, `referer`, `json` text, `index` integer, `gid` integer)");
db.execSQL("CREATE TABLE `favGroups`(`gid` integer primary key autoincrement, `title`, `index` integer)");
db.execSQL("INSERT INTO `favGroups` VALUES(1, \"未分类\", 1);");
db.execSQL("INSERT INTO `favourites` SELECT `fid`, `idCode`, `title`, `referer`, `json`, 0 AS `index`, 1 AS `gid` FROM `_temp_favourites`;");
db.execSQL("DROP TABLE `_temp_favourites`;");
} else if (oldVersion == 6 && newVersion == 7) {
db.execSQL("ALTER TABLE `downloads` RENAME TO `_temp_downloads`;");
db.execSQL("CREATE TABLE `downloads`(`did` integer primary key autoincrement, `idCode`, `title`, `referer`, `json` text, `index` integer, `gid` integer)");
db.execSQL("CREATE TABLE `dlGroups`(`gid` integer primary key autoincrement, `title`, `index` integer)");
db.execSQL("INSERT INTO `dlGroups` VALUES(1, \"未分类\", 1);");
db.execSQL("INSERT INTO `downloads` SELECT `did`, `idCode`, `title`, `referer`, `json`, 0 AS `index`, 1 AS `gid` FROM `_temp_downloads`;");
db.execSQL("DROP TABLE `_temp_downloads`;");
}
}
}
}