package org.openssf.openjournal.utils; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.DatabaseUtils; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import org.openssf.openjournal.R; import java.util.ArrayList; /** * DBHelper Class * Simple database helper class for retrieving notes data * @author zanedb on GitHub * @version 1.0 */ public class DBHelper extends SQLiteOpenHelper { private static final String DATABASE_NAME = "openJournalDB.db"; private static final String NOTES_TABLE_NAME = "notes"; private static final String NOTES_COLUMN_ID = "id"; private static final String NOTES_COLUMN_TITLE = "title"; private static final String NOTES_COLUMN_TEXT = "notetext"; private static final String NOTES_COLUMN_TIMESTAMP = "timestamp"; private Context mContext; public DBHelper(Context context) { super(context, DATABASE_NAME , null, 1); mContext = context; } @Override public void onCreate(SQLiteDatabase db) { db.execSQL( "create table "+NOTES_TABLE_NAME+" " + "("+NOTES_COLUMN_ID+" integer primary key, "+NOTES_COLUMN_TITLE+" text,"+NOTES_COLUMN_TEXT+" text,"+NOTES_COLUMN_TIMESTAMP+" text)" ); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS "+NOTES_TABLE_NAME+""); onCreate(db); } public boolean insertNote (String title, String notetext, String timestamp) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues contentValues = new ContentValues(); contentValues.put(NOTES_COLUMN_TITLE, title); contentValues.put(NOTES_COLUMN_TEXT, notetext); contentValues.put(NOTES_COLUMN_TIMESTAMP, timestamp); db.insert(NOTES_TABLE_NAME, null, contentValues); return true; } public String getData(int id) { SQLiteDatabase db = this.getReadableDatabase(); Cursor res = db.rawQuery( "select "+NOTES_COLUMN_TEXT+" from "+NOTES_TABLE_NAME+" where "+NOTES_COLUMN_ID+"="+id+"", null); if(res.moveToFirst()) { // Set variable to string String notetext = res.getString(0); // Close cursor res.close(); // Return variable return notetext; } else { res.close(); return mContext.getResources().getString(R.string.general_error); } } public int numberOfRows(){ SQLiteDatabase db = this.getReadableDatabase(); return (int) DatabaseUtils.queryNumEntries(db, NOTES_TABLE_NAME); } public boolean updateNote (Integer id, String title, String notetext, String timestamp) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues contentValues = new ContentValues(); contentValues.put(NOTES_COLUMN_TITLE, title); contentValues.put(NOTES_COLUMN_TEXT, notetext); contentValues.put(NOTES_COLUMN_TIMESTAMP, timestamp); db.update(NOTES_TABLE_NAME, contentValues, ""+NOTES_COLUMN_ID+" = ? ", new String[] { Integer.toString(id) }); return true; } public Integer deleteNote(Integer id) { SQLiteDatabase db = this.getWritableDatabase(); return db.delete(NOTES_TABLE_NAME, ""+NOTES_COLUMN_ID+" = ? ", new String[] { Integer.toString(id) }); } public ArrayList<String> getAllNotes() { ArrayList<String> array_list = new ArrayList<String>(); SQLiteDatabase db = this.getReadableDatabase(); Cursor res = db.rawQuery( "select * from "+NOTES_TABLE_NAME+"", null); res.moveToFirst(); while(!res.isAfterLast()){ array_list.add(res.getString(res.getColumnIndex(NOTES_COLUMN_TITLE))); res.moveToNext(); } res.close(); return array_list; } public Integer getNoteIdFromTitle(String title) { SQLiteDatabase db = this.getReadableDatabase(); Cursor res = db.rawQuery( "select "+NOTES_COLUMN_ID+" from "+NOTES_TABLE_NAME+" where "+NOTES_COLUMN_TITLE+"='"+title+"'", null); if(res.moveToFirst()) { int noteid = res.getInt(0); res.close(); return noteid; } else { res.close(); return -1; } } public boolean doesNoteExist(String title) { SQLiteDatabase db = this.getReadableDatabase(); Cursor res = db.rawQuery( "select * from "+NOTES_TABLE_NAME+" where "+NOTES_COLUMN_TITLE+"='"+title+"'", null); if(res.moveToFirst()) { res.close(); return true; } else { res.close(); return false; } } public String getTimestamp(int id) { SQLiteDatabase db = this.getReadableDatabase(); Cursor res = db.rawQuery( "select "+NOTES_COLUMN_TIMESTAMP+" from "+NOTES_TABLE_NAME+" where "+NOTES_COLUMN_ID+"="+id+"", null); if(res.moveToFirst()) { String timestamp = res.getString(0); res.close(); return timestamp; } else { res.close(); return mContext.getResources().getString(R.string.general_error); } } }