private void createTable(SQLiteDatabase db) {
String CREATE_SCHOOL_DISTRICT_TABLE =
"CREATE TABLE IF NOT EXISTS "
+ SCHOOL_DISTRICT_TABLE
+ " ("
+ ID
+ " INTEGER,"
+ NAME
+ " TEXT,"
+ SCHOOL_KEY
+ " TEXT,"
+ POINT_ID
+ " INTEGER,"
+ PARENT_ID
+ " INTEGER,"
+ TYPE
+ " TEXT,"
+ TITLEURL
+ " TEXT,"
+ BOOKMARK
+ " BOOLEAN DEFAULT 0 NOT NULL,"
+ STATUS
+ " INTEGER"
+ ");";
db.execSQL(CREATE_SCHOOL_DISTRICT_TABLE);
String CREATE_SCHOOL_BOOKMARK_TABLE =
"CREATE TABLE IF NOT EXISTS "
+ SCHOOL_BOOKMARK_TABLE
+ " ("
+ ID
+ " INTEGER,"
+ POINT_ID
+ " INTEGER"
+ ");";
db.execSQL(CREATE_SCHOOL_BOOKMARK_TABLE);
String CREATE_NAVIGATION_TYPE_TABLE =
"CREATE TABLE IF NOT EXISTS "
+ NAVIGATION_TYPE_TABLE
+ " ("
+ ID
+ " INTEGER,"
+ TYPE
+ " TEXT,"
+ POINT_ID
+ " INTEGER"
+ ");";
db.execSQL(CREATE_NAVIGATION_TYPE_TABLE);
mNavigationDBHelper.updateTable(db, SCHOOL_DISTRICT_TABLE);
mNavigationDBHelper.updateTable(db, SCHOOL_BOOKMARK_TABLE);
mNavigationDBHelper.updateTable(db, NAVIGATION_TYPE_TABLE);
}
public Cursor getBookmarkCursor(String limit) {
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
String[] fields =
new String[] {
SCHOOL_DISTRICT_TABLE + "." + POINT_ID,
NAME,
SCHOOL_KEY,
PARENT_ID,
TYPE,
TITLEURL,
BOOKMARK,
STATUS
};
String queryBuilder =
SQLiteQueryBuilder.buildQueryString(
false,
SCHOOL_DISTRICT_TABLE + ", " + SCHOOL_BOOKMARK_TABLE,
fields,
SCHOOL_DISTRICT_TABLE + "." + POINT_ID + "=" + SCHOOL_BOOKMARK_TABLE + "." + POINT_ID,
null,
null,
NAME + " ASC",
limit);
return db.rawQuery(queryBuilder, null);
}
public void markAsBookmark(int point_id) {
SQLiteDatabase db = mNavigationDBHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(POINT_ID, point_id);
if (!pointExists(point_id)) {
db.insert(SCHOOL_BOOKMARK_TABLE, POINT_ID, values);
}
}
public Cursor getNavigationCursor(int parent_id, String limit) {
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
String queryBuilder =
SQLiteQueryBuilder.buildQueryString(
false,
SCHOOL_DISTRICT_TABLE,
null,
PARENT_ID + "=" + parent_id,
null,
null,
NAME + " ASC",
limit);
return db.rawQuery(queryBuilder, null);
}
public void saveNavigationListItem(NavigationListItem item) {
SQLiteDatabase db = mNavigationDBHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(TYPE, item.type);
values.put(POINT_ID, item.point_id);
if (!isNavigationListItemExists(item.point_id)) {
db.insert(NAVIGATION_TYPE_TABLE, POINT_ID, values);
} else {
db.update(
NAVIGATION_TYPE_TABLE,
values,
POINT_ID_WHERE,
new String[] {String.valueOf(item.point_id)});
}
}
private boolean pointExists(int point_id) {
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
Cursor cursor =
db.query(
SCHOOL_BOOKMARK_TABLE,
null,
POINT_ID_WHERE,
new String[] {String.valueOf(point_id)},
null,
null,
null);
int count = cursor.getCount();
cursor.close();
if (count > 0) {
return true;
} else {
return false;
}
}
private boolean isNavigationListItemExists(int point_id) {
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
Cursor cursor =
db.query(
NAVIGATION_TYPE_TABLE,
null,
POINT_ID_WHERE,
new String[] {point_id + ""},
null,
null,
null);
int count = cursor.getCount();
cursor.close();
if (count > 0) {
return true;
} else {
return false;
}
}
private boolean schoolDistrictExist(int point_id) {
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
Cursor cursor =
db.query(
SCHOOL_DISTRICT_TABLE,
null,
POINT_ID_WHERE,
new String[] {point_id + ""},
null,
null,
NAME + " ASC");
int count = cursor.getCount();
cursor.close();
if (count > 0) {
return true;
} else {
return false;
}
}
public NavigationItem getSchoolItem(String schoolkey) {
NavigationItem item = new NavigationItem();
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
String queryBuilder =
SQLiteQueryBuilder.buildQueryString(
false,
SCHOOL_DISTRICT_TABLE,
null,
SCHOOL_KEY + " = '" + schoolkey + "'",
null,
null,
null,
null);
Cursor result = db.rawQuery(queryBuilder, null);
if (null != result) {
if (result.moveToFirst()) {
item = retrieveNavigationItem(result);
}
result.close();
}
return item;
}
public String getNameByID(int point_id) {
String name = "";
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
String queryBuilder =
SQLiteQueryBuilder.buildQueryString(
false,
SCHOOL_DISTRICT_TABLE,
new String[] {NAME},
POINT_ID + "=" + point_id,
null,
null,
null,
null);
Cursor result = db.rawQuery(queryBuilder, null);
if (null != result) {
result.moveToFirst();
int name_index = result.getColumnIndex(NAME);
name = result.getString(name_index);
result.close();
}
return name;
}
public Cursor searchItemsByKey(String key, String limit) {
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
String queryString =
SQLiteQueryBuilder.buildQueryString(
false,
SCHOOL_DISTRICT_TABLE,
null,
NAME
+ " LIKE '%"
+ key
+ "%' AND "
+ STATUS
+ "="
+ "1"
+ " AND "
+ TYPE
+ "= 'school'",
null,
null,
POINT_ID + " DESC",
limit);
return db.rawQuery(queryString, null);
}
public boolean saveNavigationItem(NavigationItem item) {
boolean bFlags = false;
SQLiteDatabase db = mNavigationDBHelper.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(NAME, item.name);
values.put(TYPE, item.type);
values.put(POINT_ID, item.point_id);
values.put(PARENT_ID, item.parent_id);
values.put(SCHOOL_KEY, item.key);
values.put(TITLEURL, item.titleurl);
values.put(STATUS, item.status);
if (schoolDistrictExist(item.point_id)) {
db.update(
SCHOOL_DISTRICT_TABLE,
values,
POINT_ID_WHERE,
new String[] {String.valueOf(item.point_id)});
} else {
bFlags = true;
db.insert(SCHOOL_DISTRICT_TABLE, POINT_ID, values);
}
return bFlags;
}
public List<NavigationListItem> getNavigationLists() {
SQLiteDatabase db = mNavigationDBHelper.getReadableDatabase();
List<NavigationListItem> list = new ArrayList<NavigationListItem>();
String queryBuilder =
SQLiteQueryBuilder.buildQueryString(
false, NAVIGATION_TYPE_TABLE, null, null, null, null, POINT_ID + " ASC", null);
Cursor result = db.rawQuery(queryBuilder, null);
if (result != null) {
result.moveToFirst();
if (result.getCount() > 0) {
while (!result.isAfterLast()) {
NavigationListItem item = new NavigationListItem();
int type_index = result.getColumnIndex(TYPE);
int point_id_index = result.getColumnIndex(POINT_ID);
item.type = result.getString(type_index);
item.point_id = result.getInt(point_id_index);
list.add(item);
result.moveToNext();
}
}
result.close();
}
return list;
}
public void startTransaction() {
mNavigationDBHelper.getWritableDatabase().beginTransaction();
}
private NavigationDB(Context context) {
mNavigationDBHelper = NavigationDatabaseHelper.getInstance(context);
SQLiteDatabase db = mNavigationDBHelper.getWritableDatabase();
createTable(db);
db.close();
}
public void clearChildBySearchKey(String searchkey) {
SQLiteDatabase db = mNavigationDBHelper.getWritableDatabase();
db.delete(SCHOOL_DISTRICT_TABLE, NAME + " LIKE '%" + searchkey + "%'", null);
}
public void endTransaction() {
mNavigationDBHelper.getWritableDatabase().setTransactionSuccessful();
mNavigationDBHelper.getWritableDatabase().endTransaction();
}
public void clearAllNavigationList() {
SQLiteDatabase db = mNavigationDBHelper.getWritableDatabase();
db.delete(NAVIGATION_TYPE_TABLE, null, null);
}
public void deleteBookmarkFlag(int point_id) {
SQLiteDatabase db = mNavigationDBHelper.getWritableDatabase();
if (pointExists(point_id)) {
db.delete(SCHOOL_BOOKMARK_TABLE, POINT_ID_WHERE, new String[] {String.valueOf(point_id)});
}
}
public void clearChildID(String parentid) {
SQLiteDatabase db = mNavigationDBHelper.getWritableDatabase();
db.delete(SCHOOL_DISTRICT_TABLE, PARENT_ID + "=?", new String[] {parentid});
}
