public class DBAdaptor { //완벽한 싱글톤은 아니지만 비슷하게 만듬
public static final String WORD_KEY_COLUMN1 = "spelling";
public static final String WORD_KEY_COLUMN2 = "wordclass";
public static final String WORD_KEY_COLUMN3 = "meaning";
public static final String PRIMARY_KEY_COLUMN = "_id";
public static final String MAPPING_KEY_COLUMN1 = "tablename";
public static final String MAPPING_KEY_COLUMN2 = "name";
public static final String MYTABLE_KEY_COLUMN = "wordID";
public static final String CURRENT_KEY_COLUMN1 = "mappingID";
public static final String CURRENT_KEY_COLUMN2 = "interval";
public static final int MAX_CURRENT_KEY_COLUMN2 = 5;
public static final int MIN_CURRENT_KEY_COLUMN2 = 3;
private static final String TAG = "DbAdapter";
private static DatabaseHelper mDbHelper;
private static SQLiteDatabase mDb; // 데이터베이스를 저장
private static final String DATABASE_CREATE =
"create table if not exists word(_id integer primary key autoincrement,"+
"spelling text not null, wordclass text not null, meaning text not null);";
private static final String DATABASE_CREATE2 =
"create table if not exists mapping(_id integer primary key autoincrement, tablename text not null, name text not null);";
private static final String DATABASE_CREATE3 =
"create table if not exists current(_id integer primary key autoincrement, mappingID integer not null, interval interger not null);";
private static final String DATABASE_NAME = "voca.db";
private static final String DATABASE_TABLE = "word";
private static final String DATABASE_TABLE2 = "mapping";
private static final String DATABASE_TABLE3 = "current";
private static final int DATABASE_VERSION = 1;
private final Context mCtx;
private class DatabaseHelper extends SQLiteOpenHelper{
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db){
db.execSQL(DATABASE_CREATE);
db.execSQL(DATABASE_CREATE2);
db.execSQL(DATABASE_CREATE3);
ContentValues initialValues = new ContentValues();
initialValues.put(CURRENT_KEY_COLUMN1, 1);
initialValues.put(CURRENT_KEY_COLUMN2, 3);
db.insert(DATABASE_TABLE3, null, initialValues);
String createTrigger = "create trigger if not exists fk_delete_mapping_current" +
" before delete on mapping for each row begin update current" +
" set mappingID = 1 where mappingID = old._id; end;";
db.execSQL(createTrigger);
initialValues.clear();
initialValues.put(MAPPING_KEY_COLUMN1, "word");
initialValues.put(MAPPING_KEY_COLUMN2, "All Word");
db.insert(DATABASE_TABLE2, null, initialValues);
}
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
Log.w(TAG, "Upgrading db from version" + oldVersion + " to" +
newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS data");
onCreate(db);
}
}
public DBAdaptor(Context ctx){
this.mCtx = ctx;
}
public DBAdaptor open() throws SQLException{
mDbHelper = new DatabaseHelper(mCtx);
mDb = mDbHelper.getWritableDatabase();
return this;
}
public void close(){
mDbHelper.close();
mDb.close();
}
public static long insertWord(String spelling, String wordclass, String meaning)
{
ContentValues initialValues = new ContentValues();
initialValues.put(WORD_KEY_COLUMN1, spelling);
initialValues.put(WORD_KEY_COLUMN2, wordclass);
initialValues.put(WORD_KEY_COLUMN3, meaning);
return mDb.insert(DATABASE_TABLE, null, initialValues);
}
public static long insertMapping(String tablename, String name)
{
ContentValues initialValues = new ContentValues();
initialValues.put(MAPPING_KEY_COLUMN1, tablename);
initialValues.put(MAPPING_KEY_COLUMN2, name);
return mDb.insert(DATABASE_TABLE2, null, initialValues);
}
public static long insertMyTable(long wordID, String tablename)
{
ContentValues initialValues = new ContentValues();
initialValues.put(MYTABLE_KEY_COLUMN, wordID);
return mDb.insert(tablename, null, initialValues);
}
public static boolean deleteWord(String spelling, String wordclass) throws SQLException
{
String[] whereArgs = new String[]{spelling, wordclass};
return mDb.delete(DATABASE_TABLE, WORD_KEY_COLUMN1 + "=? AND " + WORD_KEY_COLUMN2 + "=?", whereArgs) > 0;
}
public static boolean deleteMapping(String tablename) throws SQLException
{
return mDb.delete(DATABASE_TABLE2, MAPPING_KEY_COLUMN1 + "='" + tablename +"'", null) > 0;
}
public static Cursor fetchAllWords(){
Cursor mCursor = null;
mCursor =
mDb.query(DATABASE_TABLE, new String[]{PRIMARY_KEY_COLUMN, WORD_KEY_COLUMN1, WORD_KEY_COLUMN2, WORD_KEY_COLUMN3}, null, null, null, null, WORD_KEY_COLUMN1);
if(mCursor != null)
mCursor.moveToFirst();
return mCursor;
}
public static Cursor fetchWord(String spelling, String wordclass) throws SQLException{
String[] selectArgs;
String[] col;
col = new String[]{PRIMARY_KEY_COLUMN, WORD_KEY_COLUMN1, WORD_KEY_COLUMN2, WORD_KEY_COLUMN3};
selectArgs = new String[]{spelling, wordclass};
Cursor mCursor = null;
mCursor =
mDb.query(true, DATABASE_TABLE, col, WORD_KEY_COLUMN1 + "=? AND " + WORD_KEY_COLUMN2 + "=?", selectArgs, null, null, null, null);
if(mCursor != null)
mCursor.moveToFirst();
return mCursor;
}
public static Cursor fetchWordAtSpell(String spelling) throws SQLException{
String[] col;
col = new String[]{PRIMARY_KEY_COLUMN, WORD_KEY_COLUMN2, WORD_KEY_COLUMN3};
Cursor mCursor = null;
mCursor =
mDb.query(true, DATABASE_TABLE, col, WORD_KEY_COLUMN1 + "='" + spelling + "'", null, null, null, null, null);
if(mCursor != null)
mCursor.moveToFirst();
return mCursor;
}
public static Cursor fetchAllWordsJoin(String tablename) throws SQLException{
String sql = "Select * from word," + tablename +" where word._id=" + tablename + ".wordID order by spelling";
Cursor mCursor = null;
mCursor =
mDb.rawQuery(sql, null);
if(mCursor != null)
mCursor.moveToFirst();
return mCursor;
}
public static Cursor fetchAllMapping(){
Cursor mCursor = null;
mCursor =
mDb.query(DATABASE_TABLE2, new String[]{PRIMARY_KEY_COLUMN, MAPPING_KEY_COLUMN1, MAPPING_KEY_COLUMN2}, null, null, null, null, null);
if(mCursor != null)
mCursor.moveToFirst();
return mCursor;
}
public static Cursor fetchMappingAtTableName(String tablename) throws SQLException{
String[] col;
col = new String[]{PRIMARY_KEY_COLUMN, MAPPING_KEY_COLUMN1, MAPPING_KEY_COLUMN2};
Cursor mCursor = null;
mCursor =
mDb.query(true, DATABASE_TABLE2, col, MAPPING_KEY_COLUMN1 + "='" + tablename + "'", null, null, null, null, null);
if(mCursor != null)
mCursor.moveToFirst();
return mCursor;
}
public static Cursor fetchMappingAtName(String name) throws SQLException{
String[] col;
col = new String[]{PRIMARY_KEY_COLUMN, MAPPING_KEY_COLUMN1, MAPPING_KEY_COLUMN2};
Cursor mCursor = null;
mCursor =
mDb.query(true, DATABASE_TABLE2, col, MAPPING_KEY_COLUMN2 + "='" + name + "'", null, null, null, null, null);
if(mCursor != null)
mCursor.moveToFirst();
return mCursor;
}
public static Cursor fetchMappingAtID(int id) throws SQLException{
String[] col;
col = new String[]{PRIMARY_KEY_COLUMN, MAPPING_KEY_COLUMN1, MAPPING_KEY_COLUMN2};
Cursor mCursor = null;
mCursor =
mDb.query(true, DATABASE_TABLE2, col, PRIMARY_KEY_COLUMN + "=" + id, null, null, null, null, null);
if(mCursor != null)
mCursor.moveToFirst();
return mCursor;
}
public static Cursor fetchCurrent(){
Cursor mCursor = null;
mCursor =
mDb.query(DATABASE_TABLE3, new String[]{PRIMARY_KEY_COLUMN, CURRENT_KEY_COLUMN1, CURRENT_KEY_COLUMN2}, null, null, null, null, null);
if(mCursor != null)
mCursor.moveToFirst();
return mCursor;
}
public static boolean updateWord(long rowID, String spelling, String wordclass){
ContentValues args = new ContentValues();
args.put(WORD_KEY_COLUMN1, spelling);
args.put(WORD_KEY_COLUMN2, wordclass);
return mDb.update(DATABASE_TABLE, args, PRIMARY_KEY_COLUMN + "=" + rowID, null) > 0;
}
public static boolean updateCurrent(int mappingID){
ContentValues args = new ContentValues();
args.put(CURRENT_KEY_COLUMN1, mappingID);
return mDb.update(DATABASE_TABLE3, args, PRIMARY_KEY_COLUMN + "=1", null) > 0;
}
public static boolean updateInterval(int interval){
ContentValues args = new ContentValues();
args.put(CURRENT_KEY_COLUMN2, interval);
return mDb.update(DATABASE_TABLE3, args, PRIMARY_KEY_COLUMN + "=1", null) > 0;
}
public static void beginTransaction()
{
mDb.beginTransaction();
}
public static void setTransactionSuccessful()
{
mDb.setTransactionSuccessful();
}
public static void endTransaction()
{
mDb.endTransaction();
}
public static void execSQLFunc(String sql)
{
mDb.execSQL(sql);
}
} |