My app similar to todo application where list of todos are associated with a tag Ex: tag1: "Sunday" todo list: Task: "wake up late" info: "task1" Task: "break fast" info: "task2"
tag2 : "Monday" todo list: Task: "wake up at 6" info: "to early wakeup" Task: "Read news paper" info: "task2" Task: "eat break fast" info: "task3"
When user select a tag (say Sunday) I am making a list of todos of that tag and showing it as list view in my fragment. Each entry in list view shows Task and info details.
Now I am implementing long press on each list entry and show options "delete entry" and "update info".
For "delete" operation, I have delete the entry from database and show list updated list view.
For "update info", read input from user and update "info" details and refresh the list view.
I am able to achieve everything except database operations such as deleting respective row if user selects deletion and updating a particular row with given info.
I am taking help from http://ift.tt/1iCzjxB
with some modification in names (not logic)
But some how delete and update are not working with the KEY_ID.
Below is code that create tables:
public class DatabaseHelper extends SQLiteOpenHelper {
private static final String CREATE_TABLE_ROWDATA = "CREATE TABLE IF NOT EXISTS "
+ TABLE_ROWDATA + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_ROWDATA
+ " TEXT," + KEY_INFOPATH + " TEXT," + KEY_STATUS + " INTEGER," + KEY_CREATED_AT
+ " DATETIME" + ")";
// CategoryTag table create statement
private static final String CREATE_TABLE_TAG = "CREATE TABLE IF NOT EXISTS " + TABLE_TAG
+ "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TAG_NAME + " TEXT,"
+ KEY_CREATED_AT + " DATETIME" + ")";
// rowdata_tag table create statement
private static final String CREATE_TABLE_ROWDATA_TAG = "CREATE TABLE IF NOT EXISTS "
+ TABLE_ROWDATA_TAG + "(" + KEY_ID + " INTEGER PRIMARY KEY,"
+ KEY_ROWDATA_ID + " INTEGER," + KEY_TAG_ID + " INTEGER,"
+ KEY_CREATED_AT + " DATETIME" + ")";
public static DatabaseHelper sInstance;
public static synchronized DatabaseHelper getInstance(Context context) {
if (sInstance == null) {
sInstance = new DatabaseHelper(context.getApplicationContext());
}
return sInstance;
}
@Override
public void onCreate(SQLiteDatabase db) {
// creating required tables
db.execSQL(CREATE_TABLE_ROWDATA);
db.execSQL(CREATE_TABLE_TAG);
db.execSQL(CREATE_TABLE_ROWDATA_TAG);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// on upgrade drop older tables
db.execSQL("DROP TABLE IF EXISTS " + TABLE_ROWDATA);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_TAG);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_ROWDATA_TAG);
// create new tables
onCreate(db);
}
// ------------------------ "rowdatas" table methods ----------------//
/**
* Creating a rowdata
*/
public long createRowData(RowData rowdata, long[] tag_ids) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_ROWDATA, rowdata.getPath());
values.put(KEY_INFOPATH, rowdata.getInfoPath());
values.put(KEY_STATUS, rowdata.getStatus());
values.put(KEY_CREATED_AT, getDateTime());
// insert row
long rowdata_id = db.insert(TABLE_ROWDATA, null, values);
Log.e(LOG, "New rowdata id for: " + rowdata.getPath() + "is: " + rowdata_id + "tag id: " + tag_ids[0] + " to table: " + TABLE_ROWDATA);
// insert tag_ids
for (long tag_id : tag_ids) {
createRowDataTag(rowdata_id, tag_id);
}
return rowdata_id;
}
/**
* get single rowdata
*/
public RowData getRowData(long rowdata_id) {
SQLiteDatabase db = this.getReadableDatabase();
String selectQuery = "SELECT * FROM " + TABLE_ROWDATA + " WHERE "
+ KEY_ID + " = " + rowdata_id;
Log.e(LOG, selectQuery);
Cursor c = db.rawQuery(selectQuery, null);
if (c != null)
c.moveToFirst();
RowData td = new RowData();
td.setId(c.getInt(c.getColumnIndex(KEY_ID)));
td.setPath((c.getString(c.getColumnIndex(KEY_ROWDATA))));
td.setInfoNote((c.getString(c.getColumnIndex(KEY_INFOPATH))));
td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));
return td;
}
/**
* getting all rowdatas
* */
public List<RowData> getAllRowDatas() {
List<RowData> rowdatas = new ArrayList<RowData>();
String selectQuery = "SELECT * FROM " + TABLE_ROWDATA;
Log.e(LOG, selectQuery);
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (c.moveToFirst()) {
do {
RowData td = new RowData();
td.setId(c.getInt((c.getColumnIndex(KEY_ID))));
td.setPath((c.getString(c.getColumnIndex(KEY_ROWDATA))));
td.setInfoNote((c.getString(c.getColumnIndex(KEY_INFOPATH))));
td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));
// adding to rowdata list
rowdatas.add(td);
} while (c.moveToNext());
}
return rowdatas;
}
/**
* getting all rowdatas under single tag
* */
public List<RowData> getAllRowDatasByCategoryTag(String tag_name) {
List<RowData> rowdatas = new ArrayList<RowData>();
String selectQuery = "SELECT * FROM " + TABLE_ROWDATA + " td, "
+ TABLE_TAG + " tg, " + TABLE_ROWDATA_TAG + " tt WHERE tg."
+ KEY_TAG_NAME + " = '" + tag_name + "'" + " AND tg." + KEY_ID
+ " = " + "tt." + KEY_TAG_ID + " AND td." + KEY_ID + " = "
+ "tt." + KEY_ROWDATA_ID;
Log.e(LOG, selectQuery);
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (c.moveToFirst()) {
do {
RowData td = new RowData();
td.setId(c.getInt((c.getColumnIndex(KEY_ID))));
td.setPath((c.getString(c.getColumnIndex(KEY_ROWDATA))));
td.setInfoNote((c.getString(c.getColumnIndex(KEY_INFOPATH))));
td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));
// adding to rowdata list
rowdatas.add(td);
} while (c.moveToNext());
}
return rowdatas;
}
public RowData getRowDataId(String tag_name, RowData rowdata) {
String selectQuery = "SELECT * FROM " + TABLE_ROWDATA + " td, "
+ TABLE_TAG + " tg, " + TABLE_ROWDATA_TAG + " tt WHERE tg."
+ KEY_TAG_NAME + " = '" + tag_name + "'" + " AND tg." + KEY_ID
+ " = " + "tt." + KEY_TAG_ID + " AND td." + KEY_ID + " = "
+ "tt." + KEY_ROWDATA_ID;
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (c.moveToFirst()) {
do {
if(rowdata.getPath().equals(c.getString(c.getColumnIndex(KEY_ROWDATA)))) {
rowdata.setId(c.getColumnIndex(KEY_ID));
rowdata.setInfoNote(c.getString(c.getColumnIndex(KEY_INFOPATH)));
rowdata.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));
break;
}
} while (c.moveToNext());
}
Log.e(LOG, rowdata.getPath() + " got id: " + rowdata.getId());
return rowdata;
}
/**
* getting rowdata count
*/
public int getRowDataCount() {
String countQuery = "SELECT * FROM " + TABLE_ROWDATA;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
int count = cursor.getCount();
cursor.close();
// return count
return count;
}
/**
* Updating a rowdata
*/
public int updateRowData(RowData rowdata) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_ROWDATA, rowdata.getPath());
values.put(KEY_INFOPATH, rowdata.getInfoPath());
values.put(KEY_STATUS, rowdata.getStatus());
// updating row
return db.update(TABLE_ROWDATA, values, KEY_ID + " = ?",
new String[] { String.valueOf(rowdata.getId()) });
}
public int getRowDataCountByCategoryTag(String tag_name) {
String selectQuery = "SELECT * FROM " + TABLE_ROWDATA + " td, "
+ TABLE_TAG + " tg, " + TABLE_ROWDATA_TAG + " tt WHERE tg."
+ KEY_TAG_NAME + " = '" + tag_name + "'" + " AND tg." + KEY_ID
+ " = " + "tt." + KEY_TAG_ID + " AND td." + KEY_ID + " = "
+ "tt." + KEY_ROWDATA_ID;
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery(selectQuery, null);
int count = c.getCount();
return count;
}
/**
* Deleting a rowdata
*/
public void deleteRowData(long todo_id) {
SQLiteDatabase db = this.getWritableDatabase();
Log.e(LOG, "Deleting rowdata id: " + todo_id + " from table: " + TABLE_ROWDATA);
int ret = db.delete(TABLE_ROWDATA, KEY_ID + " = ?",
new String[] { String.valueOf(todo_id) });
}
// ------------------------ "tags" table methods ----------------//
/**
* Creating tag
*/
public long createCategoryTag(CategoryTag tag) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_TAG_NAME, tag.getTagName());
values.put(KEY_CREATED_AT, getDateTime());
// insert row
long tag_id = db.insert(TABLE_TAG, null, values);
return tag_id;
}
public String[] getAllCategoryTagNames() {
String[] tagNames = null;
int count = 0;
String selectQuery = "SELECT * FROM " + TABLE_TAG;
Log.e(LOG, selectQuery);
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery(selectQuery, null);
tagNames = new String[c.getCount()];
// looping through all rows and adding to list
if (c.moveToFirst()) {
do {
tagNames[count] = c.getString(c.getColumnIndex(KEY_TAG_NAME));
count++;
} while (c.moveToNext());
}
return tagNames;
}
/**
* getting all tags
* */
public List<CategoryTag> getAllCategoryTags() {
List<CategoryTag> tags = new ArrayList<CategoryTag>();
String selectQuery = "SELECT * FROM " + TABLE_TAG;
Log.e(LOG, selectQuery);
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (c.moveToFirst()) {
do {
CategoryTag t = new CategoryTag();
t.setId(c.getInt((c.getColumnIndex(KEY_ID))));
t.setTagName(c.getString(c.getColumnIndex(KEY_TAG_NAME)));
// adding to tags list
tags.add(t);
} while (c.moveToNext());
}
return tags;
}
public CategoryTag getCategoryTagId(CategoryTag tag) {
String selectQuery = "SELECT * FROM " + TABLE_TAG;
boolean found = false;
Log.e(LOG, selectQuery);
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (c.moveToFirst()) {
do {
if(c.getString(c.getColumnIndex(KEY_TAG_NAME)).equals(tag.getTagName())) {
tag.setId(c.getInt((c.getColumnIndex(KEY_ID))));
found = true;
break;
}
} while (c.moveToNext());
}
if(found) {
return tag;
} else {
tag.setId(-1);
return tag;
}
}
/**
* Updating a tag
*/
public int updateCategoryTag(CategoryTag tag) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_TAG_NAME, tag.getTagName());
// updating row
return db.update(TABLE_TAG, values, KEY_ID + " = ?",
new String[] { String.valueOf(tag.getId()) });
}
/**
* Deleting a tag
*/
public void deleteCategoryTag(CategoryTag tag, boolean should_delete_all_tag_rowdatas) {
SQLiteDatabase db = this.getWritableDatabase();
// before deleting tag
// check if rowdatas under this tag should also be deleted
if (should_delete_all_tag_rowdatas) {
// get all rowdatas under this tag
List<RowData> allTagToDos = getAllRowDatasByCategoryTag(tag.getTagName());
// delete all rowdatas
for (RowData rowdata : allTagToDos) {
// delete rowdata
deleteRowData(rowdata.getId());
}
}
// now delete the tag
db.delete(TABLE_TAG, KEY_ID + " = ?",
new String[] { String.valueOf(tag.getId()) });
}
// ------------------------ "rowdata_tags" table methods ----------------//
/**
* Creating rowdata_tag
*/
public long createRowDataTag(long rowdata_id, long tag_id) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_ROWDATA_ID, rowdata_id);
values.put(KEY_TAG_ID, tag_id);
values.put(KEY_CREATED_AT, getDateTime());
Log.e(LOG, "Inserting rowdata_id: " + rowdata_id + " tag id: " + tag_id + " to table: " + TABLE_ROWDATA_TAG);
long id = db.insert(TABLE_ROWDATA_TAG, null, values);
Log.e(LOG, "inserted id: " + id);
return id;
}
public void printRowData_Tag() {
String selectQuery = "SELECT * FROM " + TABLE_ROWDATA_TAG;
Log.e(LOG, selectQuery);
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (c.moveToFirst()) {
do {
Log.e(LOG, TABLE_ROWDATA_TAG + "tag id: " + c.getInt(c.getColumnIndex(KEY_TAG_ID)) + "rowdata id: " + c.getInt(c.getColumnIndex(KEY_ROWDATA_ID)));
}while (c.moveToNext());
}
}
/**
* Updating a rowdata tag
*/
public int updateRowData_Tag(long id, long tag_id) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_TAG_ID, tag_id);
// updating row
return db.update(TABLE_ROWDATA_TAG, values, KEY_ID + " = ?",
new String[] { String.valueOf(id) });
}
/**
* Deleting a rowdata tag
*/
public void deleteRowData_Tag(long id) {
SQLiteDatabase db = this.getWritableDatabase();
Log.e(LOG, "Deleting id: " + id + " from : " + TABLE_ROWDATA_TAG);
db.delete(TABLE_ROWDATA_TAG, KEY_ID + " = ?",
new String[] { String.valueOf(id) });
}
// closing database
public void closeDB() {
SQLiteDatabase db = this.getReadableDatabase();
if (db != null && db.isOpen())
db.close();
}
/**
* get datetime
* */
private String getDateTime() {
SimpleDateFormat dateFormat = new SimpleDateFormat(
"yyyy-MM-dd HH:mm:ss", Locale.getDefault());
Date date = new Date();
return dateFormat.format(date);
}
}
I am able to create entries in TABLE_ROWDATA. I am not able to delete entry from TABLE_ROWDATA. Below code which calls to delete entry and update listview.
public void deleteListEntry(int position) {
CategoryTag tag = new CategoryTag(title);
tag = db.getCategoryTagId(tag);
RowData rowdata = new RowData(pathList[position], 0);
List<RowData> rowdatas = db.getAllRowDatasByCategoryTag(title);
for (RowData rd : rowdatas) {
if (rd.getPath().equals(pathList[position])) {
db.deleteRowData(rd.getId());
break;
}
}
Log.e("tagIt", "Count: " + db.getRowDataCountByCategoryTag(title));
}
public void updateRowDataList(){
List<RowData> rowdatas = db.getAllRowDatasByCategoryTag(title);
int i = 0;
if(rowdatas.size() == 0) {
Log.e("tagIt", "No list found ");
}
pathList = new String[rowdatas.size()];
pathInfoList = new String[rowdatas.size()];
for (RowData rowdata : rowdatas) {
pathList[i] = rowdata.getPath();
pathInfoList[i] = rowdata.getInfoPath();
Log.e("tagIt", "rowdata-id: " + rowdata.getId());
i++;
}
}
public View updateFragmentWithData(){
updateRowDataList();
lpadapter = new ListPathsAdapter(getActivity(), pathList, pathInfoList);
lv = (ListView) rootView.findViewById(R.id.pathsList);
lv.setAdapter(lpadapter);
lv.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> parent, View view, int position,
long id) {
openFile(position);
return;
}
});
registerForContextMenu(lv);
return rootView;
}
public void updateInfo(final int position) {
AlertDialog.Builder builder = new AlertDialog.Builder(getActivity());
builder.setTitle("Update info");
final EditText input = new EditText(getActivity());
builder.setView(input);
builder.setPositiveButton("YES", new DialogInterface.OnClickListener() {
@Override
public void onClick(DialogInterface dialog, int which) {
dialog.dismiss();
String value = input.getText().toString();
CategoryTag tag = new CategoryTag(value);
List<RowData> rowdatas = db.getAllRowDatasByCategoryTag(title);
int i = 0;
for(RowData rd : rowdatas){
if (i == position){
rd.setInfoNote(value);
db.updateRowData(rd);
break;
}
}
updateFragmentWithData();
}
});
Above delete call is from below selection in onContextItemSelected(MenuItem item);. where info.position is position in the of list entries of public String[] list pathList.
@Override
public boolean onContextItemSelected(MenuItem item) {
AdapterView.AdapterContextMenuInfo info = (AdapterView.AdapterContextMenuInfo) item.getMenuInfo();
switch (item.getItemId()) {
case R.id.send:
openFile(info.position);
return true;
case R.id.delete:
deleteListEntry(info.position);
updateFragmentWithData();
return true;
case R.id.updateinfo:
updateInfo(info.position);
return true;
default:
return super.onContextItemSelected(item);
}
}
I am not sure where is the issue in DB. db.delete returns 0 But after deletion recreation of list shows same list again.
Aucun commentaire:
Enregistrer un commentaire