/**
*
*/
package com.sina.util.dnscache.cache;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.sina.util.dnscache.model.DomainModel;
import com.sina.util.dnscache.model.IpModel;
import java.util.ArrayList;
import java.util.List;
/**
*
* 项目名称: DNSCache <br>
* 类名称: DNSCacheDatabaseHelper <br>
* 类描述: 缓存数据库 创建、更新、删除、增删改查相关操作 <br>
* 创建人: fenglei <br>
* 创建时间: 2015-3-26 下午4:04:23 <br>
*
* 修改人: <br>
* 修改时间: <br>
* 修改备注: <br>
*
* @version V1.0
*/
public class DNSCacheDatabaseHelper extends SQLiteOpenHelper implements DBConstants{
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/**
* 资源锁
*/
private final static byte synLock[] = new byte[1];
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/**
* 构造函数
* @param context
*/
public DNSCacheDatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
// TODO Auto-generated constructor stub
}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/**
* 创建数据库
*
* 残酷的现实告诉我们，创建多个表时，要分开多次执行db.execSQL方法！！
*/
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
//Log.d("DB", "onCreate") ;
db.execSQL(CREATE_DOMAIN_TABLE_SQL);
db.execSQL(CREATE_IP_TEBLE_SQL);
db.execSQL(CREATE_CONNECT_FAIL_TABLE_SQL);
}
/**
* 数据库版本更新策略（直接放弃旧表）
*/
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
//Log.d("DB", "onUpgrade") ;
if (oldVersion != newVersion) {
// 其它情况，直接放弃旧表.
db.beginTransaction();
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_DOMAIN + ";");
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_IP + ";");
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_CONNECT_FAIL + ";");
db.setTransactionSuccessful();
db.endTransaction();
onCreate(db);
}
}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/**
* 添加一条新的记录 。如果域名重复删除旧数据。
*
* @param url
* @param sp
* @param model
* @return
*/
public DomainModel addDomainModel(String url, String sp, DomainModel model) {
synchronized (synLock) {
// 过滤重复数据
ArrayList<DomainModel> domainList = (ArrayList<DomainModel>) QueryDomainInfo(model.domain, model.sp);
if (domainList != null && domainList.size() > 0) {
//之所以删除该记录是为了保证 与过期ip数据断开关联关系。
//比如：第一次拉下来的是1、2、3ip数据。第二次拉下来是4、5、6ip数据。那么1、2、3关联的did就会失效。 即：1、2、3记录成为无效数据
deleteDomainInfo(domainList);
}
SQLiteDatabase db = getWritableDatabase();
ContentValues cv = new ContentValues();
try {
db.beginTransaction();
cv.put(DOMAIN_COLUMN_DOMAIN, model.domain);
cv.put(DOMAIN_COLUMN_SP, model.sp);
cv.put(DOMAIN_COLUMN_TTL, model.ttl);
cv.put(DOMAIN_COLUMN_TIME, model.time);
model.id = db.insert(TABLE_NAME_DOMAIN, null, cv);
for (int i = 0; i < model.ipModelArr.size(); i++) {
IpModel temp = model.ipModelArr.get(i);
// 更新内存中IP表中的d_id字段
IpModel oldModel = getIpModel(temp.ip, sp);
IpModel ipModel = null;
// 若数据库中无此条数据，则插入一条
if (oldModel == null) {
ipModel = temp;
ipModel.d_id = model.id;
ipModel.id = addIpModel(ipModel);
} else {
ipModel = oldModel;
ipModel.d_id = model.id;
// 若数据库中存在此条数据，则更新对应的domainId即可
updateIpInfo(ipModel);
}
model.ipModelArr.remove(i);
model.ipModelArr.add(i, ipModel);
}
db.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
// 上报错误
} finally {
if(db.isOpen())
db.endTransaction();
db.close();
}
return model;
}
}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/**
* 根据url 获取缓存domain
*
* @param domain
* @param sp
* @return
*/
public List<DomainModel> QueryDomainInfo(String domain, String sp){
synchronized (synLock) {
List<DomainModel> list = new ArrayList<DomainModel>() ;
StringBuilder sql = new StringBuilder();
sql.append("SELECT * FROM ");
sql.append(TABLE_NAME_DOMAIN);
sql.append(" WHERE ");
sql.append(DOMAIN_COLUMN_DOMAIN);
sql.append(" =? ");
sql.append(" AND ");
sql.append(DOMAIN_COLUMN_SP);
sql.append(" =? ;");
SQLiteDatabase db = getReadableDatabase();
Cursor cursor = null;
try {
cursor = db.rawQuery(sql.toString(), new String[] { domain, sp });
if (cursor != null && cursor.getCount() > 0) {
cursor.moveToFirst();
do {
DomainModel model = new DomainModel() ;
model.id = cursor.getInt(cursor.getColumnIndex(DOMAIN_COLUMN_ID));
model.domain = cursor.getString(cursor.getColumnIndex(DOMAIN_COLUMN_DOMAIN));
model.sp = cursor.getString(cursor.getColumnIndex(DOMAIN_COLUMN_SP));
model.ttl = cursor.getString(cursor.getColumnIndex(DOMAIN_COLUMN_TTL));
model.time = cursor.getString(cursor.getColumnIndex(DOMAIN_COLUMN_TIME));
model.ipModelArr = (ArrayList<IpModel>) QueryIpModelInfo(model) ;
list.add(model) ;
} while (cursor.moveToNext());
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
cursor.close();
db.close() ;
}
return list ;
}
}
/**
* 通过url获取服务器ip信息
* 根据 domainModel 获取Ipmodel 对象。
* @param domainModel
* @return
*/
private List<IpModel> QueryIpModelInfo( DomainModel domainModel ){
// 内部方法 不需要加锁
List<IpModel> list = new ArrayList<IpModel>() ;
StringBuilder sql = new StringBuilder();
sql.append("SELECT * FROM ");
sql.append(TABLE_NAME_IP);
sql.append(" WHERE ");
sql.append(IP_COLUMN_DOMAIN_ID);
sql.append(" =? ;");
SQLiteDatabase db = getReadableDatabase();
Cursor cursor = null;
try {
cursor = db.rawQuery(sql.toString(), new String[] { String.valueOf( domainModel.id ) });
if (cursor != null && cursor.getCount() > 0) {
cursor.moveToFirst();
do{
IpModel ip = new IpModel() ;
ip.id = cursor.getInt(cursor.getColumnIndex(IP_COLUMN_ID));
ip.d_id = cursor.getInt(cursor.getColumnIndex(IP_COLUMN_DOMAIN_ID));
ip.ip = cursor.getString(cursor.getColumnIndex(IP_COLUMN_IP));
// ip.ip = String.valueOf( Tools.longToIP( Long.parseLong( ip.ip ) ) );
ip.port = cursor.getInt(cursor.getColumnIndex(IP_COLUMN_PORT));
ip.sp = cursor.getString(cursor.getColumnIndex(IP_COLUMN_SP));
ip.ttl = cursor.getString(cursor.getColumnIndex(IP_COLUMN_TTL));
ip.priority = cursor.getString(cursor.getColumnIndex(IP_COLUMN_PRIORITY));
ip.rtt = cursor.getString(cursor.getColumnIndex(IP_COLUMN_RTT));
ip.success_num = cursor.getString(cursor.getColumnIndex(IP_COLUMN_SUCCESS_NUM));
ip.err_num = cursor.getString(cursor.getColumnIndex(IP_COLUMN_ERR_NUM));
ip.finally_success_time = cursor.getString(cursor.getColumnIndex(IP_COLUMN_FINALLY_SUCCESS_TIME));
ip.finally_fail_time = cursor.getString(cursor.getColumnIndex(IP_COLUMN_FINALLY_FAIL_TIME));
list.add(ip) ;
}while(cursor.moveToNext());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
cursor.close();
db.close() ;
}
return list ;
}
/**
* 根据 服务器 ip 获取数据库的数据集
*
* @param serverIp
* @return
*/
private IpModel getIpModel(String serverIp, String sp){
ArrayList<IpModel> list = new ArrayList<IpModel>();
synchronized (synLock) {
StringBuilder sql = new StringBuilder();
sql.append("SELECT * FROM ");
sql.append(TABLE_NAME_IP);
sql.append(" WHERE ");
sql.append(IP_COLUMN_IP);
sql.append(" =? ");
sql.append(" AND ");
sql.append(DOMAIN_COLUMN_SP);
sql.append(" =? ;");
SQLiteDatabase db = getWritableDatabase();
Cursor cursor = null;
try {
cursor = db.rawQuery(sql.toString(), new String[]{serverIp, sp});
if (cursor != null && cursor.getCount() > 0) {
cursor.moveToFirst();
do {
IpModel ip = new IpModel();
ip.id = cursor.getInt(cursor.getColumnIndex(IP_COLUMN_ID));
ip.d_id = cursor.getInt(cursor.getColumnIndex(IP_COLUMN_DOMAIN_ID));
ip.ip = cursor.getString(cursor.getColumnIndex(IP_COLUMN_IP));
ip.port = cursor.getInt(cursor.getColumnIndex(IP_COLUMN_PORT));
ip.sp = cursor.getString(cursor.getColumnIndex(IP_COLUMN_SP));
ip.ttl = cursor.getString(cursor.getColumnIndex(IP_COLUMN_TTL));
ip.priority = cursor.getString(cursor.getColumnIndex(IP_COLUMN_PRIORITY));
ip.rtt = cursor.getString(cursor.getColumnIndex(IP_COLUMN_RTT));
ip.success_num = cursor.getString(cursor.getColumnIndex(IP_COLUMN_SUCCESS_NUM));
ip.err_num = cursor.getString(cursor.getColumnIndex(IP_COLUMN_ERR_NUM));
ip.finally_success_time = cursor.getString(cursor.getColumnIndex(IP_COLUMN_FINALLY_SUCCESS_TIME));
ip.finally_fail_time = cursor.getString(cursor.getColumnIndex(IP_COLUMN_FINALLY_FAIL_TIME));
list.add(ip);
} while (cursor.moveToNext());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
cursor.close();
//db.close();
}
}
// 排除下重复的IP 理论上是不会出现重复IP的， 多线程同时写数据库有锁。
if( list.size() > 1 ){
for( int i = 0 ; i < list.size() - 1 ; i++ ){
IpModel ipModel = list.get(i);
deleteIpServer(ipModel.id) ;
}
}
return list.size() > 0 ? list.get(list.size() - 1) : null ;
}
/**
* 根据域名id 删除域名相关信息
*/
private void deleteDomainInfo(long domain_id){
synchronized (synLock) {
SQLiteDatabase db = getWritableDatabase();
try {
db.delete(TABLE_NAME_DOMAIN, DOMAIN_COLUMN_ID + " = ?", new String[]{String.valueOf(domain_id)} ) ;
} catch (Exception e) {
e.printStackTrace();
} finally {
db.close();
}
}
}
// /**
// * 根据域名id 删除服务器相关信息
// * @param domain_id
// */
// private void deleteIpInfo(long domain_id){
//
// synchronized (synLock) {
// SQLiteDatabase db = getWritableDatabase();
// try {
// db.delete(TABLE_NAME_IP, IP_COLUMN_DOMAIN_ID + " = ?", new String[]{String.valueOf(domain_id)} ) ;
// } catch (Exception e) {
// e.printStackTrace();
// } finally {
// db.close();
// }
// }
// }
/**
* 根据 ID 删除服务器信息
* @param ip
*/
private void deleteIpServer(long id){
synchronized (synLock) {
SQLiteDatabase db = getWritableDatabase();
try {
db.delete(TABLE_NAME_IP, IP_COLUMN_ID + " = ?", new String[]{String.valueOf(id)} ) ;
} catch (Exception e) {
e.printStackTrace();
} finally {
db.close();
}
}
}
/**
* 删除域名相关信息
*/
public void deleteDomainInfo(DomainModel domainModel){
deleteDomainInfo( domainModel.id) ;
}
/**
* 删除域名相关信息
*/
public void deleteDomainInfo(ArrayList<DomainModel> domainModelArr){
for( DomainModel temp : domainModelArr )
deleteDomainInfo( temp.id) ;
}
/**
* 清除缓存数据
*/
public void clear() {
synchronized (synLock) {
SQLiteDatabase db = getWritableDatabase();
try {
db.delete(TABLE_NAME_DOMAIN, null, null);
db.delete(TABLE_NAME_IP, null, null);
db.delete(TABLE_NAME_CONNECT_FAIL, null, null);
} catch (Exception e) {
e.printStackTrace();
} finally {
db.close();
}
}
}
/**
* 返回 domain 表信息
*/
public ArrayList<DomainModel> getAllTableDomain(boolean appendIpInfo) {
ArrayList<DomainModel> list = new ArrayList<DomainModel>();
synchronized (synLock) {
StringBuilder sql = new StringBuilder();
sql.append("SELECT * FROM ");
sql.append(TABLE_NAME_DOMAIN);
sql.append(" ; ");
SQLiteDatabase db = getReadableDatabase();
Cursor cursor = null;
try {
cursor = db.rawQuery(sql.toString(), null);
if (cursor != null && cursor.getCount() > 0) {
cursor.moveToFirst();
do {
DomainModel model = new DomainModel();
model.id = cursor.getInt(cursor.getColumnIndex(DOMAIN_COLUMN_ID));
model.domain = cursor.getString(cursor.getColumnIndex(DOMAIN_COLUMN_DOMAIN));
model.sp = cursor.getString(cursor.getColumnIndex(DOMAIN_COLUMN_SP));
model.ttl = cursor.getString(cursor.getColumnIndex(DOMAIN_COLUMN_TTL));
model.time = cursor.getString(cursor.getColumnIndex(DOMAIN_COLUMN_TIME));
if (appendIpInfo) {
model.ipModelArr = (ArrayList<IpModel>) QueryIpModelInfo(model);
}
list.add(model);
} while (cursor.moveToNext());
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
} finally {
cursor.close();
db.close();
}
}
return list;
}
/**
* 返回 domain 表信息
*/
public ArrayList<DomainModel> getAllTableDomain() {
return getAllTableDomain(false);
}
/**
* 返回 ip 表信息
*/
public ArrayList<IpModel> getTableIP(){
synchronized (synLock) {
ArrayList<IpModel> list = new ArrayList<IpModel>();
StringBuilder sql = new StringBuilder();
sql.append("SELECT * FROM ");
sql.append(TABLE_NAME_IP);
sql.append(" ; ");
SQLiteDatabase db = getReadableDatabase();
Cursor cursor = null;
try {
cursor = db.rawQuery(sql.toString(), null);
if (cursor != null && cursor.getCount() > 0) {
cursor.moveToFirst();
do {
IpModel ip = new IpModel();
ip.id = cursor.getInt(cursor.getColumnIndex(IP_COLUMN_ID));
ip.d_id = cursor.getInt(cursor.getColumnIndex(IP_COLUMN_DOMAIN_ID));
ip.ip = cursor.getString(cursor.getColumnIndex(IP_COLUMN_IP));
ip.port = cursor.getInt(cursor.getColumnIndex(IP_COLUMN_PORT));
ip.sp = cursor.getString(cursor.getColumnIndex(IP_COLUMN_SP));
ip.ttl = cursor.getString(cursor.getColumnIndex(IP_COLUMN_TTL));
ip.priority = cursor.getString(cursor.getColumnIndex(IP_COLUMN_PRIORITY));
ip.rtt = cursor.getString(cursor.getColumnIndex(IP_COLUMN_RTT));
ip.success_num = cursor.getString(cursor.getColumnIndex(IP_COLUMN_SUCCESS_NUM));
ip.err_num = cursor.getString(cursor.getColumnIndex(IP_COLUMN_ERR_NUM));
ip.finally_success_time = cursor.getString(cursor.getColumnIndex(IP_COLUMN_FINALLY_SUCCESS_TIME));
ip.finally_fail_time = cursor.getString(cursor.getColumnIndex(IP_COLUMN_FINALLY_FAIL_TIME));
list.add(ip);
} while (cursor.moveToNext());
}
} catch (Exception e) {
e.printStackTrace();
} finally {
cursor.close();
db.close();
}
return list ;
}
}
/**
* 向数据库中新增一条ip记录
* @param model
* @return
*/
public long addIpModel(IpModel model) {
synchronized (synLock) {
SQLiteDatabase db = getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put(IP_COLUMN_DOMAIN_ID, model.d_id);
cv.put(IP_COLUMN_IP, model.ip);
cv.put(IP_COLUMN_PORT, model.port);
cv.put(IP_COLUMN_PRIORITY, model.priority);
cv.put(IP_COLUMN_SP, model.sp);
cv.put(IP_COLUMN_RTT, model.rtt);
cv.put(IP_COLUMN_FINALLY_FAIL_TIME, model.finally_fail_time);
cv.put(IP_COLUMN_FINALLY_SUCCESS_TIME, model.finally_success_time);
cv.put(IP_COLUMN_SUCCESS_NUM, model.success_num);
cv.put(IP_COLUMN_ERR_NUM, model.err_num);
cv.put(IP_COLUMN_TTL, model.ttl);
return db.insert(TABLE_NAME_IP, null, cv);
}
}
/**
* 更新数据库中的一条ip记录
* @param model
* @return
*/
private void updateIpInfo(IpModel model) {
synchronized (synLock) {
SQLiteDatabase db = getWritableDatabase();
ContentValues cv = new ContentValues();
StringBuilder where = new StringBuilder();
where.append(IP_COLUMN_ID);
where.append(" = ? ");
cv.put(IP_COLUMN_DOMAIN_ID, model.d_id);
cv.put(IP_COLUMN_IP, model.ip);
cv.put(IP_COLUMN_PORT, model.port);
cv.put(IP_COLUMN_PRIORITY, model.priority);
cv.put(IP_COLUMN_SP, model.sp);
cv.put(IP_COLUMN_RTT, model.rtt);
cv.put(IP_COLUMN_FINALLY_FAIL_TIME, model.finally_fail_time);
cv.put(IP_COLUMN_FINALLY_SUCCESS_TIME, model.finally_success_time);
cv.put(IP_COLUMN_SUCCESS_NUM, model.success_num);
cv.put(IP_COLUMN_ERR_NUM, model.err_num);
cv.put(IP_COLUMN_TTL, model.ttl);
String[] args = new String[] { String.valueOf(model.id) };
db.update(TABLE_NAME_IP, cv, where.toString(), args);
}
}
/**
* 批量更新ip表数据
* @param model
* @return
*/
public void updateIpInfo(List<IpModel> ipModels) {
synchronized (synLock) {
SQLiteDatabase db = getWritableDatabase();
db.beginTransaction();
try {
for (IpModel model : ipModels) {
ContentValues cv = new ContentValues();
StringBuilder where = new StringBuilder();
where.append(IP_COLUMN_ID);
where.append(" = ? ");
cv.put(IP_COLUMN_DOMAIN_ID, model.d_id);
cv.put(IP_COLUMN_IP, model.ip);
cv.put(IP_COLUMN_PORT, model.port);
cv.put(IP_COLUMN_PRIORITY, model.priority);
cv.put(IP_COLUMN_SP, model.sp);
cv.put(IP_COLUMN_RTT, model.rtt);
cv.put(IP_COLUMN_FINALLY_FAIL_TIME, model.finally_fail_time);
cv.put(IP_COLUMN_FINALLY_SUCCESS_TIME, model.finally_success_time);
cv.put(IP_COLUMN_SUCCESS_NUM, model.success_num);
cv.put(IP_COLUMN_ERR_NUM, model.err_num);
cv.put(IP_COLUMN_TTL, model.ttl);
String[] args = new String[] { String.valueOf(model.id) };
db.update(TABLE_NAME_IP, cv, where.toString(), args);
}
db.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
} finally{
db.endTransaction();
db.close();
}
}
}
}