import android.content.Context;
import android.database.sqlite.sqliteDatabase;
import android.database.sqlite.sqliteOpenHelper;

public class DatabaseHelper extends sqliteOpenHelper {

private static final String DATABASE_NAME = "iblueye.db";
private static final int DATABASE_VERSION = 1;

public DatabaseHelper(Context context) {
// CursorFactory设置为null,使用默认值
super(context,DATABASE_NAME,null,DATABASE_VERSION);
}

// 数据库第一次被创建时onCreate会被调用 INTEGER PRIMARY KEY AUTOINCREMENT
@Override
public void onCreate(sqliteDatabase db) {
/* 智能家居创建的数据库 */
db.execsql("CREATE TABLE IF NOT EXISTS profile"
+ "(profile_id VARCHAR PRIMARY KEY,profile_name VARCHAR,profile_enabled BOOL NOT NULL DEFAULT 0)");
db.execsql("CREATE TABLE IF NOT EXISTS mode"
+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT,profile_id VARCHAR,device_id VARCHAR,device_state VARCHAR)");

/* 智慧交通 */
db.execsql("CREATE TABLE IF NOT EXISTS traffic"
+ "(traffic_id VARCHAR PRIMARY KEY,traffic_name VARCHAR,description VARCHAR,city VARCHAR,video_id VARCHAR)");

}

// 如果DATABASE_VERSION值被改为2,系统发现现有数据库版本不同,即会调用onUpgrade
@Override
public void onUpgrade(sqliteDatabase db,int oldVersion,int newVersion) {
db.execsql("ALTER TABLE profile ADD COLUMN other STRING");
db.execsql("ALTER TABLE mode ADD COLUMN other STRING");
db.execsql("ALTER TABLE traffic ADD COLUMN other STRING");
}
}

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.sqliteDatabase;

import com.wlx.iblueyeclient.model.home.Mode;
import com.wlx.iblueyeclient.model.home.Profile;

public class HomeDBManager {
private DatabaseHelper helper;
private sqliteDatabase db;

public HomeDBManager(Context context) {
helper = new DatabaseHelper(context);
// 因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName,// mFactory);
// 所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
db = helper.getWritableDatabase();
}

/**
* add profile
* 
* @param profile
*/
public void add(Profile profile) {
db.beginTransaction(); // 开始事务
try {
db.execsql("INSERT INTO profile VALUES(?,?,?)",new Object[] { profile.profileId,profile.profileName,profile.isEnabled });
for (Mode mode : profile.modeList) {
db.execsql("INSERT INTO mode VALUES(null,mode.deviceId,mode.deviceState });
}
db.setTransactionSuccessful(); // 设置事务成功完成
} finally {
db.endTransaction(); // 结束事务
}
}

/**
* update profile's name
* 
* @param profile
*/
public void updateProfileName(Profile profile) {
ContentValues cv = new ContentValues();
cv.put("profile_name",profile.profileName);
db.update("profile",cv,"profile_id = ?",new String[] { profile.profileId });
}

/**
* delete old profile
* 
* @param profile
*/
public void deleteOldProfile(Profile profile) {
db.delete("profile","profile_id == ?",new String[] { String.valueOf(profile.profileId) });
}

/**
* query all profiles,return list
* 
* @return List<Profile>
*/
public List<Profile> query() {
ArrayList<Profile> profileList = new ArrayList<Profile>();
Cursor c = queryProfileCursor();
while (c.moveToNext()) {
Profile profile = new Profile();
profile.profileId = c.getString(c.getColumnIndex("profile_id"));
profile.profileName = c.getString(c.getColumnIndex("profile_name"));
profile.isEnabled = Boolean.parseBoolean(c.getString(c
.getColumnIndex("profile_enabled")));

String modeId = c.getString(c.getColumnIndex("mode_id"));
Cursor r = queryModeCursor(new String[] { "mode_id='" + modeId
+ "'" });
List<Mode> modeList = new ArrayList<Mode>();
while (r.moveToNext()) {
Mode mode = new Mode();
mode.deviceId = r.getString(r.getColumnIndex("device_id"));
mode.deviceState = r
.getString(r.getColumnIndex("device_state"));
modeList.add(mode);
}
profile.modeList = modeList;
profileList.add(profile);
}
c.close();
return profileList;
}

/**
* query all profiles,return cursor
* 
* @return Cursor
*/
public Cursor queryProfileCursor() {
Cursor c = db.rawQuery("SELECT * FROM profile",null);
return c;
}

/**
* query all mode,return cursor
* 
* @return Cursor
*/
public Cursor queryModeCursor(String[] selectionArgs) {
Cursor c = db.rawQuery("SELECT * FROM profile",selectionArgs);
return c;
}

/**
* close database
*/
public void closeDB() {
db.close();
}
}