数据库升级笔记

http://jinnianshilongnian.iteye.com/blog/2022468 Realm数据库系列教程

http://www.easydone.cn/2015/11/13数据库greendao升级笔记

packagexcxin.filexpert.orm.dao.base;

importandroid.content.ContentValues;

importandroid.content.Context;

importandroid.database.Cursor;

importandroid.database.sqlite.SQLiteDatabase;

importandroid.provider.MediaStore;

importandroid.text.TextUtils;

importandroid.util.Log;

importjava.io.File;

importjava.util.ArrayList;

importjava.util.Arrays;

importjava.util.List;

importde.greenrobot.dao.AbstractDao;

importde.greenrobot.dao.internal.DaoConfig;

importde.greenrobot.dao.query.QueryBuilder;

importxcxin.filexpert.assistant.constant.DataConstant;

importxcxin.filexpert.assistant.constant.SettingConstant;

importxcxin.filexpert.assistant.utils.FileUtils;

importxcxin.filexpert.assistant.utils.PreferenceUtils;

importxcxin.filexpert.assistant.utils.TimeUtils;

importxcxin.filexpert.assistant.utils.TmpFolderUtils;

importxcxin.filexpert.model.implement.local.tool.recyclebin.RecycleUtils;

importxcxin.filexpert.orm.helper.implement.SafeBoxHelper;

/**

* Created by liucheng on 2015/10/16

public classDBCore {

private static finalStringDEFAULT_DB_NAME="FileExpert.db";

private staticDaoMasterdaoMaster;

private staticDaoSessiondaoSession;

private staticContextmContext;

private staticStringDB_NAME;

//Befor Table Columns

public final staticStringDATA= MediaStore.Files.FileColumns.DATA;

public final staticStringTITLE= MediaStore.Files.FileColumns.TITLE;

public final staticStringSIZE= MediaStore.Files.FileColumns.SIZE;

//Befor Tables

public static finalStringSEARCH_HISTORY="search_history";

public static finalStringROOT_FILE="root_file";

public static finalStringTABLE_MUSIC_PLAY="music_play";

public static finalStringNAVIGATION="navigation";

public static finalStringSETTING="setting";

public static finalStringAPPS="apps";

public static finalStringAPKS="apks";

public static finalStringGCLOUD_ACCOUNT="gcloud_account";

public static finalStringGCLOUD_FILE="gcloud_file";

public static finalStringWIFI_DEVICE="wifi_device";

public static finalStringTASK_DOWNLOAD="task_download";

public static finalStringTHREAD_DOWNLOAD="thread_download";

public static finalStringSTATISTICS="statistics";

public static finalStringSPLASH="splash";

public static finalStringSYNC_SETTING="sync_setting";

public static finalStringFILEANALYSIS="file_analysis";

public static finalStringRECOMMEND="recommend";

public static finalStringMAIN_CUSTOMER="main_customer";

public static finalStringFAVORITE="favorite";

public static finalStringLABELFIlE="label_file";

public static finalStringLABEL="label";

public static finalStringRecycle_BIN="recycle";

public static finalStringSYNC_DATA="sync_data";

public static finalStringSYNC_HISTORY="sync_history";

public static finalStringSEARCH_FILE="search";

public static finalStringTABLE_SETTING="setting";

public static finalStringLOCAL_FILE="local_file";

public static voidinitDB(Context context) {

init(context,DEFAULT_DB_NAME);

}

public static voidinit(Context context,String dbName) {

if(context ==null) {

throw newIllegalArgumentException("context can't be null");

}

enableQueryBuilderLog();

mContext= context.getApplicationContext();

DB_NAME= dbName;

}

public staticDaoMastergetDaoMaster() {

if(daoMaster==null) {

DaoMaster.OpenHelper helper =newFeOpenHelper(mContext,DB_NAME, null);

daoMaster=newDaoMaster(helper.getWritableDatabase());

}

returndaoMaster;

}

public staticDaoSessiongetDaoSession() {

if(daoSession==null) {

if(daoMaster==null) {

daoMaster=getDaoMaster();

}

daoSession=daoMaster.newSession();

}

returndaoSession;

}

public static voidenableQueryBuilderLog() {

QueryBuilder.LOG_SQL=false;

QueryBuilder.LOG_VALUES=false;

}

private static classFeOpenHelperextendsDaoMaster.OpenHelper {

publicFeOpenHelper(Context context,String name,SQLiteDatabase.CursorFactory factory) {

super(context,name,factory);

}

@Override

public voidonUpgrade(SQLiteDatabase db, intoldVersion, intnewVersion) {

Log.e("nizi","==== oldVersion "+ oldVersion +"newVersion "+ newVersion);

try{

if(oldVersion <=10) {

dropAllTable(db);

}

if(oldVersion >=11&& oldVersion <=17) {

//delete unavailable table

dropV7UnavailableTable(db);

//table：Label

upgradeLableTable(db);

//table：labelFile

upgradeLableFileTable(db);

//table:favorate

restoreFavorateToLableFile(db);

RecycleUtils.updateRecycleData(db);

}

}catch(Exception e) {

e.printStackTrace();

}finally{

//version 17 need's newTables

DaoMaster.createAllTables(db, true);

}

if(PreferenceUtils.getPrefBoolean(SettingConstant.FIRST_USE_SAFEBOX,SettingConstant.FIRST_USE_SAFEBOX_DEFAULT_VALUE)) {

SafeBoxHelper.getHelper();

}

private staticArrayListneedDropBasicTables() {

ArrayList tableList =newArrayList<>();

tableList.add(APKS);

tableList.add(APPS);

tableList.add(FILEANALYSIS);

tableList.add(GCLOUD_ACCOUNT);

tableList.add(GCLOUD_FILE);

tableList.add(TABLE_MUSIC_PLAY);

tableList.add(MAIN_CUSTOMER);

tableList.add(NAVIGATION);

tableList.add(RECOMMEND);

tableList.add(ROOT_FILE);

tableList.add(SEARCH_HISTORY);

tableList.add(SETTING);

tableList.add(SPLASH);

tableList.add(STATISTICS);

tableList.add(SYNC_SETTING);

tableList.add(TASK_DOWNLOAD);

tableList.add(THREAD_DOWNLOAD);

tableList.add(WIFI_DEVICE);

returntableList;

}

private static voiddropV7UnavailableTable(SQLiteDatabase db) {

ArrayList tableList =needDropBasicTables();

for(inti =0,z = tableList.size();i < z;i++) {

String tableName = tableList.get(i);

db.execSQL("DROP TABLE IF EXISTS "+ tableName);

Log.e("nizi","dropV7UnavailableTable "+ tableName +" success");

}

private static voiddropAllTable(SQLiteDatabase db) {

ArrayList tableList =needDropBasicTables();

tableList.add(WIFI_DEVICE);

tableList.add(LABELFIlE);

tableList.add(LABEL);

tableList.add(Recycle_BIN);

tableList.add(SYNC_DATA);

tableList.add(SYNC_HISTORY);

tableList.add(SEARCH_FILE);

tableList.add(TABLE_SETTING);

tableList.add(LOCAL_FILE);

for(inti =0,z = tableList.size();i < z;i++) {

String tableName = tableList.get(i);

db.execSQL("DROP TABLE IF EXISTS "+ tableName);

Log.e("nizi","dropAllTable "+ tableName +" success");

}

private static voidupgradeLableTable(SQLiteDatabase db) {

try{

db.beginTransaction();

String tempTableName = LabelDao.TABLENAME+"_temp";

String sqlAlter ="ALTER TABLE "+ LabelDao.TABLENAME+" RENAME TO "+ tempTableName;

db.execSQL(sqlAlter);

// 2,创建新表.

LabelDao.createTable(db, true);

// 3,迁移数据

String sql ="INSERT INTO "+ LabelDao.TABLENAME

+" SELECT * FROM "+ tempTableName;

db.execSQL(sql);

// 4,删除旧表

sql ="DROP TABLE IF EXISTS "+ tempTableName;

db.execSQL(sql);

db.setTransactionSuccessful();

Log.d("nizi","upgradeLableTable success------");

}catch(Exception e) {

e.printStackTrace();

}finally{

db.endTransaction();

}

private static voidupgradeLableFileTable(SQLiteDatabase db) {

try{

db.beginTransaction();

String tempTableName = LabelFileDao.TABLENAME+"_temp";

String sqlAlter ="ALTER TABLE "+ LabelFileDao.TABLENAME+" RENAME TO "+ tempTableName;

db.execSQL(sqlAlter);

// 2,创建新表.

LabelFileDao.createTable(db, true);

// 3,迁移数据

String sql ="INSERT INTO "+ LabelFileDao.TABLENAME

+"("+ LabelFileDao.Properties.Name.columnName+","

+ LabelFileDao.Properties.Path.columnName+","

+ LabelFileDao.Properties.Size.columnName+","

+ LabelFileDao.Properties.Date_modified.columnName+","

+ LabelFileDao.Properties.LabelId.columnName+","

+ LabelFileDao.Properties.Mime_type.columnName+","

+ LabelFileDao.Properties.Is_file.columnName+")"

+" SELECT "+TITLE+","+DATA+","+SIZE+","

+ LabelFileDao.Properties.Date_modified.name+","

+ LabelFileDao.Properties.LabelId.name+","

+ LabelFileDao.Properties.Mime_type.name+","

+ LabelFileDao.Properties.Is_file.name+

" FROM "+ tempTableName;

db.execSQL(sql);

// 4,删除旧表

sql ="DROP TABLE IF EXISTS "+ tempTableName;

db.execSQL(sql);

db.setTransactionSuccessful();

Log.d("nizi","upgradeLableFile Table success------");

}catch(Exception e) {

e.printStackTrace();

}finally{

db.endTransaction();

}

private static voidrestoreFavorateToLableFile(SQLiteDatabase db) {

List files =newArrayList<>();

Cursor beforFiles = db.query(FAVORITE, newString[]{MediaStore.Files.FileColumns.DATA}, null, null, null, null, null);

if(beforFiles !=null&& beforFiles.getCount() >0) {

while(beforFiles.moveToNext()) {

String data = beforFiles.getString(0);

if(data !=null) {

files.add(newFile(data));

}

beforFiles.close();

booleanresult =false;

try{

ContentValues cv =newContentValues();

cv.put(LabelDao.Properties.LabelType.columnName,DataConstant.LABEL_DEFAUL_FAV_NAME);

cv.put(LabelDao.Properties.LabelColor.columnName,DataConstant.LABEL_DEFAUL_FAV_COLOR);

cv.put(LabelDao.Properties.Date_modified.columnName,TimeUtils.getCurrentTime());

if(db.insert(LabelDao.TABLENAME, null,cv) != -1) {

Log.d("nizi"," insert "+ LabelDao.TABLENAME+" SUCCESS ");

result =true;

}else{

Log.d("nizi"," insert "+ LabelDao.TABLENAME+" fail ");

}

}catch(Exception e) {

e.printStackTrace();

}finally{

if(result) {

String selectSql ="select * from "+ LabelDao.TABLENAME+" where "+ LabelDao.Properties.LabelType.columnName+" = '"

+ DataConstant.LABEL_DEFAUL_FAV_NAME+"' and "+ LabelDao.Properties.LabelColor.columnName+" = "+ DataConstant.LABEL_DEFAUL_FAV_COLOR;

Cursor cur = db.rawQuery(selectSql, null);

if(cur !=null&& cur.getCount() ==1) {

cur.moveToFirst();

intlableId = cur.getInt(cur.getColumnIndex(LabelDao.Properties.Id.columnName));

Log.d("nizi"," select "+ LabelDao.TABLENAME+" curId "+ lableId);

cur.close();

try{

if(files.size() >0) {

db.beginTransaction();

for(File file : files) {

ContentValues cv =newContentValues();

cv.put(LabelFileDao.Properties.LabelId.columnName,lableId);

cv.put(LabelFileDao.Properties.Path.columnName,file.getPath());

cv.put(LabelFileDao.Properties.Name.columnName,file.getName());

cv.put(LabelFileDao.Properties.Size.columnName,file.length());

cv.put(LabelFileDao.Properties.Date_modified.columnName,file.lastModified());

cv.put(LabelFileDao.Properties.Is_file.columnName,!file.isDirectory());

cv.put(LabelFileDao.Properties.Mime_type.columnName,FileUtils.getMiMeType(file.getName()));

db.insert(LabelFileDao.TABLENAME, null,cv);

}

db.execSQL("DROP TABLE IF EXISTS "+FAVORITE);

Log.d("nizi"," generate favorate data ok ");

db.setTransactionSuccessful();

}

}catch(Exception e) {

e.printStackTrace();

}finally{

db.endTransaction();

}

}else{

Log.d("nizi"," select "+ LabelDao.TABLENAME+" curId null");

}

}else{

db.execSQL("DROP TABLE IF EXISTS "+FAVORITE);

Log.d("nizi"," DROP TABLE IF EXISTS FAVORITE OK ");

return;

}

private staticStringgetTypeByClass(Class type) {

if(type.equals(String.class)) {

return"TEXT";

}

if(type.equals(Long.class) || type.equals(Integer.class) || type.equals(long.class)) {

return"INTEGER";

}

if(type.equals(Boolean.class)) {

return"BOOLEAN";

}

return null;

}

private staticListgetColumns(SQLiteDatabase db,String tableName) {

List columns =newArrayList<>();

Cursor cursor =null;

try{

cursor = db.rawQuery("SELECT * FROM "+ tableName +" limit 1", null);

if(cursor !=null) {

columns =newArrayList<>(Arrays.asList(cursor.getColumnNames()));

}

}catch(Exception e) {

Log.v(tableName,e.getMessage(),e);

e.printStackTrace();

}finally{

if(cursor !=null)

cursor.close();

}

returncolumns;

}

private static booleantableIsExist(SQLiteDatabase db,String tableName) {

booleanresult =false;

if(tableName ==null) {

return false;

}

Cursor cursor =null;

try{

String sql ="SELECT COUNT(*) FROM sqlite_master where type ='table' and name ='"+ tableName.trim() +"' ";

cursor = db.rawQuery(sql, null);

if(cursor.moveToNext()) {

intcount = cursor.getInt(0);

if(count >0) {

result =true;

}

}catch(Exception e) {

e.printStackTrace();

}finally{

if(cursor !=null) {

cursor.close();

}

returnresult;

}

private static voidgenerateTempTables(SQLiteDatabase db,Class>... daoClasses) {

for(Class> daoClass : daoClasses) {

DaoConfig daoConfig =newDaoConfig(db,daoClass);

String divider ="";

String tableName = daoConfig.tablename;

String tempTableName = daoConfig.tablename.concat("_TEMP");

ArrayList properties =newArrayList<>();

StringBuilder createTableStringBuilder =newStringBuilder();

createTableStringBuilder.append("CREATE TABLE ").append(tempTableName).append(" (");

for(intj =0;j < daoConfig.properties.length;j++) {

String columnName = daoConfig.properties[j].columnName;

if(getColumns(db,tableName).contains(columnName)) {

properties.add(columnName);

String type =null;

try{

type =getTypeByClass(daoConfig.properties[j].type);

}catch(Exception exception) {

exception.printStackTrace();

}

createTableStringBuilder.append(divider).append(columnName).append(" ").append(type);

if(daoConfig.properties[j].primaryKey) {

createTableStringBuilder.append(" PRIMARY KEY");

}

divider =",";

}

createTableStringBuilder.append(");");

db.execSQL(createTableStringBuilder.toString());

StringBuilder insertTableStringBuilder =newStringBuilder();

insertTableStringBuilder.append("INSERT INTO ").append(tempTableName).append(" (");

insertTableStringBuilder.append(TextUtils.join(",",properties));

insertTableStringBuilder.append(") SELECT ");

insertTableStringBuilder.append(TextUtils.join(",",properties));

insertTableStringBuilder.append(" FROM ").append(tableName).append(";");

db.execSQL(insertTableStringBuilder.toString());

}

private static voidrestoreData(SQLiteDatabase db,Class>... daoClasses) {

for(Class> daoClass : daoClasses) {

DaoConfig daoConfig =newDaoConfig(db,daoClass);

String tableName = daoConfig.tablename;

String tempTableName = daoConfig.tablename.concat("_TEMP");

ArrayList properties =newArrayList<>();

for(intj =0;j < daoConfig.properties.length;j++) {

String columnName = daoConfig.properties[j].columnName;

if(getColumns(db,tempTableName).contains(columnName)) {

properties.add(columnName);

}

StringBuilder insertTableStringBuilder =newStringBuilder();

insertTableStringBuilder.append("INSERT INTO ").append(tableName).append(" (");

insertTableStringBuilder.append(TextUtils.join(",",properties));

insertTableStringBuilder.append(") SELECT ");

insertTableStringBuilder.append(TextUtils.join(",",properties));

insertTableStringBuilder.append(" FROM ").append(tempTableName).append(";");

StringBuilder dropTableStringBuilder =newStringBuilder();

dropTableStringBuilder.append("DROP TABLE ").append(tempTableName);

db.execSQL(insertTableStringBuilder.toString());

db.execSQL(dropTableStringBuilder.toString());

}

最后编辑于：2017.12.03 04:45:12

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,456评论 5赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,370评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,337评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,583评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,596评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,572评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,936评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,595评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,850评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,601评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,685评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,371评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,951评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,934评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,167评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 43,636评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,411评论 2赞 342

数据库升级笔记

推荐阅读更多精彩内容