List of usage examples for android.database.sqlite SQLiteDatabase query
public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy,
String having, String orderBy)
From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java
/** * Verifies that the tableId exists in the database. * * @param db/*from w w w. j a va2 s .c o m*/ * @param tableId * @return true if table is listed in table definitions. */ public boolean hasTableId(SQLiteDatabase db, String tableId) { Cursor c = null; try { //@formatter:off c = db.query(DatabaseConstants.TABLE_DEFS_TABLE_NAME, null, TableDefinitionsColumns.TABLE_ID + "=?", new String[] { tableId }, null, null, null); //@formatter:on if (c.moveToFirst()) { // we know about the table... // tableId is the database table name... return true; } } finally { if (c != null && !c.isClosed()) { c.close(); } } return false; }
From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java
/** * //from www .ja v a 2 s . c o m * @param db * @param appName * @param tableId * @param rowId * @return the sync state of the row (see {@link SyncState}), or null if the * row does not exist. */ public SyncState getSyncState(SQLiteDatabase db, String appName, String tableId, String rowId) { Cursor c = null; try { c = db.query(tableId, new String[] { DataTableColumns.SYNC_STATE }, DataTableColumns.ID + " = ?", new String[] { rowId }, null, null, null); if (c.moveToFirst()) { int syncStateIndex = c.getColumnIndex(DataTableColumns.SYNC_STATE); if (!c.isNull(syncStateIndex)) { String val = getIndexAsString(c, syncStateIndex); return SyncState.valueOf(val); } } return null; } finally { if (c != null && !c.isClosed()) { c.close(); } } }
From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java
/** * Return all the tableIds in the database. * /*from w w w . j ava 2s . co m*/ * @param db * @return an ArrayList<String> of tableIds */ public ArrayList<String> getAllTableIds(SQLiteDatabase db) { ArrayList<String> tableIds = new ArrayList<String>(); Cursor c = null; try { c = db.query(DatabaseConstants.TABLE_DEFS_TABLE_NAME, new String[] { TableDefinitionsColumns.TABLE_ID }, null, null, null, null, TableDefinitionsColumns.TABLE_ID + " ASC"); if (c.moveToFirst()) { int idxId = c.getColumnIndex(TableDefinitionsColumns.TABLE_ID); do { String tableId = c.getString(idxId); if (tableId == null || tableId.length() == 0) { c.close(); throw new IllegalStateException("getAllTableIds: Unexpected tableId found!"); } tableIds.add(tableId); } while (c.moveToNext()); } } finally { if (c != null && !c.isClosed()) { c.close(); } } return tableIds; }
From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java
/** * Get the table definition entry for a tableId. This specifies the schema * ETag, the data-modification ETag, and the date-time of the last successful * sync of the table to the server.//w w w . j a va 2 s .c o m * * @param db * @param tableId * @return */ public TableDefinitionEntry getTableDefinitionEntry(SQLiteDatabase db, String tableId) { TableDefinitionEntry e = null; Cursor c = null; try { StringBuilder b = new StringBuilder(); ArrayList<String> selArgs = new ArrayList<String>(); b.append(KeyValueStoreColumns.TABLE_ID).append("=?"); selArgs.add(tableId); c = db.query(DatabaseConstants.TABLE_DEFS_TABLE_NAME, null, b.toString(), selArgs.toArray(new String[selArgs.size()]), null, null, null); if (c.moveToFirst()) { int idxSchemaETag = c.getColumnIndex(TableDefinitionsColumns.SCHEMA_ETAG); int idxLastDataETag = c.getColumnIndex(TableDefinitionsColumns.LAST_DATA_ETAG); int idxLastSyncTime = c.getColumnIndex(TableDefinitionsColumns.LAST_SYNC_TIME); if (c.getCount() != 1) { throw new IllegalStateException( "Two or more TableDefinitionEntry records found for tableId " + tableId); } e = new TableDefinitionEntry(tableId); e.setSchemaETag(c.getString(idxSchemaETag)); e.setLastDataETag(c.getString(idxLastDataETag)); e.setLastSyncTime(c.getString(idxLastSyncTime)); } } finally { if (c != null && !c.isClosed()) { c.close(); } } return e; }
From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java
/** * Retrieve the list of user-defined columns for a tableId using the metadata * for that table. Returns the unit-of-retention and non-unit-of-retention * (grouping) columns./*from w w w. j a v a 2s .c om*/ * * @param db * @param tableId * @return */ public ArrayList<Column> getUserDefinedColumns(SQLiteDatabase db, String tableId) { ArrayList<Column> userDefinedColumns = new ArrayList<Column>(); String selection = ColumnDefinitionsColumns.TABLE_ID + "=?"; String[] selectionArgs = { tableId }; //@formatter:off String[] cols = { ColumnDefinitionsColumns.ELEMENT_KEY, ColumnDefinitionsColumns.ELEMENT_NAME, ColumnDefinitionsColumns.ELEMENT_TYPE, ColumnDefinitionsColumns.LIST_CHILD_ELEMENT_KEYS }; //@formatter:on Cursor c = null; try { c = db.query(DatabaseConstants.COLUMN_DEFINITIONS_TABLE_NAME, cols, selection, selectionArgs, null, null, ColumnDefinitionsColumns.ELEMENT_KEY + " ASC"); int elemKeyIndex = c.getColumnIndexOrThrow(ColumnDefinitionsColumns.ELEMENT_KEY); int elemNameIndex = c.getColumnIndexOrThrow(ColumnDefinitionsColumns.ELEMENT_NAME); int elemTypeIndex = c.getColumnIndexOrThrow(ColumnDefinitionsColumns.ELEMENT_TYPE); int listChildrenIndex = c.getColumnIndexOrThrow(ColumnDefinitionsColumns.LIST_CHILD_ELEMENT_KEYS); c.moveToFirst(); while (!c.isAfterLast()) { String elementKey = getIndexAsString(c, elemKeyIndex); String elementName = getIndexAsString(c, elemNameIndex); String elementType = getIndexAsString(c, elemTypeIndex); String listOfChildren = getIndexAsString(c, listChildrenIndex); userDefinedColumns.add(new Column(elementKey, elementName, elementType, listOfChildren)); c.moveToNext(); } } finally { if (c != null && !c.isClosed()) { c.close(); } } return userDefinedColumns; }
From source file:br.liveo.ndrawer.ui.fragment.FragmentNotificationsfacebook.java
@Override public void onCompleted(GraphResponse graphResponse) { int postreversecounter = 1; ContentValues cv1 = null, cv2 = null, cv3 = null, cv4 = null; String abc1 = null, abc2 = null, abc3 = null; Log.i("response", graphResponse.toString()); JSONObject data1 = graphResponse.getJSONObject(); try {// w ww . j a v a2s.c o m if (data1 != null) { JSONArray friends = data1.getJSONArray("data"); // JSONObject pagingInfo = data1.getJSONObject("paging"); // Log.i("response-paging",pagingInfo.toString()); for (int i = 0; i < friends.length(); i++) { JSONObject currentFriend = friends.getJSONObject(i); Log.i("response1", currentFriend.toString()); String abc = currentFriend.getString("message"); String time = currentFriend.getString("created_time"); // Notification notimessage=new Notification(abc); // Notification timestamp=new Notification(time); // data2.add(timestamp); // adapter2.notifyDataSetChanged(); //DATE String year = time.substring(0, 4); String month = time.substring(5, 7); String dated = time.substring(8, 10); Integer imonth = Integer.parseInt(month); switch (imonth) { case 1: month = "Jan"; break; case 2: month = "Feb"; break; case 3: month = "Mar"; break; case 4: month = "Apr"; break; case 5: month = "May"; break; case 6: month = "June"; break; case 7: month = "July"; break; case 8: month = "Aug"; break; case 9: month = "Sep"; break; case 10: month = "Oct"; break; case 11: month = "Nov"; break; case 12: month = "Dec"; break; } String date = dated + " " + month + ", " + year; Log.i("date", date); Log.i("response year", year); Log.i("response year_m", month); Log.i("response year_d", date); //DATE END Log.i("response3", time); //TIme String meri = "PM"; String utchh = time.substring(11, 13); String utcmm = time.substring(14, 16); Log.i("hh_h", utchh); Log.i("hh_m", utcmm); Integer iutchh = Integer.parseInt(utchh); iutchh = iutchh + 5; if (iutchh > 12) { iutchh = iutchh - 12; meri = "PM"; } else { iutchh = iutchh; meri = "AM"; } Integer iutcmm = Integer.parseInt(utcmm); iutcmm = iutcmm + 30; if (iutcmm > 59) { iutcmm = iutcmm - 60; iutchh = iutchh + 1; } String isthh = String.valueOf(iutchh); String istmm = String.valueOf(iutcmm); Log.i("time", isthh + " : " + istmm); String ftime = isthh + ":" + istmm + " " + meri; //TIME Ends //Toast.makeText(this, time, Toast.LENGTH_SHORT); //check for duplicate String[] col = new String[1]; col[0] = NotificationSquliteOpenHelper.NOTIFICATION; // col[1]=NotificationSquliteOpenHelper.NOTIFICATION_DATE; // col[2]=NotificationSquliteOpenHelper.NOTIFICATION_TIME; String text = "text"; int flag = 0; Cursor c = db.query(NotificationSquliteOpenHelper.NOTIFICATION_TABLE, col, null, null, null, null, null); while (c.moveToNext()) { text = c.getString(c.getColumnIndex(NotificationSquliteOpenHelper.NOTIFICATION)); // String datee=c.getString(c.getColumnIndex(NotificationSquliteOpenHelper.NOTIFICATION_DATE)); // String timeee=c.getString(c.getColumnIndex(NotificationSquliteOpenHelper.NOTIFICATION_TIME)); // String // Notification n1=new Notification(text); //Log.i("text get", text); if (text != null && text.contentEquals(abc)) { flag = 1; } Log.i("text flag", String.valueOf(flag)); // postcount++; // Note nn1=new Note(text); // data2.add(n1); } if (postreversecounter == 2) { if (abc.contentEquals(abc1)) { flag = 1; } } else if (postreversecounter == 3) { if (abc.contentEquals(abc1) || abc.contentEquals(abc2)) { flag = 1; } } else if (postreversecounter == 4) { if (abc.contentEquals(abc1) || abc.contentEquals(abc2) || abc.contentEquals(abc3)) { flag = 1; } } if (flag != 1) // { // data2.add(notimessage); // adapter2.notifyDataSetChanged(); ContentValues cv = new ContentValues(); cv.put(NotificationSquliteOpenHelper.NOTIFICATION, abc); cv.put(NotificationSquliteOpenHelper.NOTIFICATION_DATE, date); cv.put(NotificationSquliteOpenHelper.NOTIFICATION_TIME, ftime); SQLiteDatabase db = helper.getWritableDatabase(); //getContentResolver().insert(Studentsquliteopenhelper.STUDENT_TABLE, cv); db.insert(NotificationSquliteOpenHelper.NOTIFICATION_TABLE, null, cv); if (postreversecounter == 1) { abc1 = abc; cv1 = new ContentValues(); cv1.put(NotificationSquliteOpenHelper.NOTIFICATION, abc); cv1.put(NotificationSquliteOpenHelper.NOTIFICATION_DATE, date); cv1.put(NotificationSquliteOpenHelper.NOTIFICATION_TIME, ftime); postreversecounter++; } // //for 1st object if (postreversecounter == 2) { abc2 = abc; cv2 = new ContentValues(); cv2.put(NotificationSquliteOpenHelper.NOTIFICATION, abc); cv2.put(NotificationSquliteOpenHelper.NOTIFICATION_DATE, date); cv2.put(NotificationSquliteOpenHelper.NOTIFICATION_TIME, ftime); postreversecounter++; } // //for 1st object if (postreversecounter == 3) { abc3 = abc; cv3 = new ContentValues(); cv3.put(NotificationSquliteOpenHelper.NOTIFICATION, abc); cv3.put(NotificationSquliteOpenHelper.NOTIFICATION_DATE, date); cv3.put(NotificationSquliteOpenHelper.NOTIFICATION_TIME, ftime); postreversecounter++; } // // ContentValues cv2 = new ContentValues(); // cv2.put(NotificationSquliteOpenHelper.NOTIFICATION_DATE, date); // db = helper.getWritableDatabase(); // //getContentResolver().insert(Studentsquliteopenhelper.STUDENT_TABLE, cv); // db.insert(NotificationSquliteOpenHelper.NOTIFICATION_TABLE, null, cv2); // // ContentValues cv3 = new ContentValues(); // cv3.put(NotificationSquliteOpenHelper.NOTIFICATION_TIME, ftime); // db = helper.getWritableDatabase(); // //getContentResolver().insert(Studentsquliteopenhelper.STUDENT_TABLE, cv); // db.insert(NotificationSquliteOpenHelper.NOTIFICATION_TABLE, null, cv3); Notification nn1 = new Notification(abc, date, ftime); data2.add(nn1); adapter2.notifyDataSetChanged(); // Note nnew=new Note(getnote); //data.add(nnew); //adapter2.notifyDataSetChanged(); } } } else { Toast.makeText(getActivity(), "No internet Connection found", Toast.LENGTH_SHORT); } if (postreversecounter == 4) { // db.insert(NotificationSquliteOpenHelper.NOTIFICATION_TABLE, null, cv3); // db.insert(NotificationSquliteOpenHelper.NOTIFICATION_TABLE, null, cv2); // db.insert(NotificationSquliteOpenHelper.NOTIFICATION_TABLE, null, cv1); } else if (postreversecounter == 3) { // db.insert(NotificationSquliteOpenHelper.NOTIFICATION_TABLE, null, cv2); // db.insert(NotificationSquliteOpenHelper.NOTIFICATION_TABLE, null, cv1); } else if (postreversecounter == 2) { //db.insert(NotificationSquliteOpenHelper.NOTIFICATION_TABLE, null, cv1); } } catch (JSONException e) { e.printStackTrace(); } }
From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java
/** * Filters results by all non-null field values. * /* ww w . ja v a2s.c om*/ * @param db * @param tableId * @param partition * @param aspect * @param key * @return */ public ArrayList<KeyValueStoreEntry> getDBTableMetadata(SQLiteDatabase db, String tableId, String partition, String aspect, String key) { ArrayList<KeyValueStoreEntry> entries = new ArrayList<KeyValueStoreEntry>(); Cursor c = null; try { StringBuilder b = new StringBuilder(); ArrayList<String> selArgs = new ArrayList<String>(); if (tableId != null) { b.append(KeyValueStoreColumns.TABLE_ID).append("=?"); selArgs.add(tableId); } if (partition != null) { if (b.length() != 0) { b.append(" AND "); } b.append(KeyValueStoreColumns.PARTITION).append("=?"); selArgs.add(partition); } if (aspect != null) { if (b.length() != 0) { b.append(" AND "); } b.append(KeyValueStoreColumns.ASPECT).append("=?"); selArgs.add(aspect); } if (key != null) { if (b.length() != 0) { b.append(" AND "); } b.append(KeyValueStoreColumns.KEY).append("=?"); selArgs.add(key); } c = db.query(DatabaseConstants.KEY_VALUE_STORE_ACTIVE_TABLE_NAME, null, b.toString(), selArgs.toArray(new String[selArgs.size()]), null, null, null); if (c.moveToFirst()) { int idxPartition = c.getColumnIndex(KeyValueStoreColumns.PARTITION); int idxAspect = c.getColumnIndex(KeyValueStoreColumns.ASPECT); int idxKey = c.getColumnIndex(KeyValueStoreColumns.KEY); int idxType = c.getColumnIndex(KeyValueStoreColumns.VALUE_TYPE); int idxValue = c.getColumnIndex(KeyValueStoreColumns.VALUE); do { KeyValueStoreEntry e = new KeyValueStoreEntry(); e.partition = c.getString(idxPartition); e.aspect = c.getString(idxAspect); e.key = c.getString(idxKey); e.type = c.getString(idxType); e.value = c.getString(idxValue); entries.add(e); } while (c.moveToNext()); } } finally { if (c != null && !c.isClosed()) { c.close(); } } return entries; }
From source file:com.example.blackberry.agoodandroidsample.SqlFragment.java
@Override public View onCreateView(LayoutInflater inflater, ViewGroup container, Bundle savedInstanceState) { // Inflate the layout for this fragment View view = inflater.inflate(R.layout.fragment_sql, container, false); final CheckBox blueBox = (CheckBox) view.findViewById(R.id.checkBlue); final CheckBox brownBox = (CheckBox) view.findViewById(R.id.checkBrown); final CheckBox greenBox = (CheckBox) view.findViewById(R.id.checkGreen); final CheckBox orangeBox = (CheckBox) view.findViewById(R.id.checkOrange); final CheckBox pinkBox = (CheckBox) view.findViewById(R.id.checkPink); final CheckBox purpleBox = (CheckBox) view.findViewById(R.id.checkPurple); final CheckBox redBox = (CheckBox) view.findViewById(R.id.checkRed); final CheckBox yellowBox = (CheckBox) view.findViewById(R.id.checkYellow); final Button clearButton = (Button) view.findViewById(R.id.clearButton); clearButton.setOnClickListener(new View.OnClickListener() { public void onClick(View v) { blueBox.setChecked(false);/*from ww w . j a va2s . c o m*/ brownBox.setChecked(false); greenBox.setChecked(false); orangeBox.setChecked(false); pinkBox.setChecked(false); purpleBox.setChecked(false); redBox.setChecked(false); yellowBox.setChecked(false); } }); final Button saveButton = (Button) view.findViewById(R.id.saveButton); saveButton.setOnClickListener(new View.OnClickListener() { public void onClick(View v) { ColorDbHelper dbHelper = new ColorDbHelper(getContext()); SQLiteDatabase db = dbHelper.getWritableDatabase(); int size = Constants.COLORS_ALL.length; //Update the database for each color. for (int count = 0; count < size; count++) { switch (Constants.COLORS_ALL[count]) { case Constants.COLOR_BLUE: updateDb(db, Constants.COLOR_BLUE, blueBox.isChecked()); break; case Constants.COLOR_BROWN: updateDb(db, Constants.COLOR_BROWN, brownBox.isChecked()); break; case Constants.COLOR_GREEN: updateDb(db, Constants.COLOR_GREEN, greenBox.isChecked()); break; case Constants.COLOR_ORANGE: updateDb(db, Constants.COLOR_ORANGE, orangeBox.isChecked()); break; case Constants.COLOR_PINK: updateDb(db, Constants.COLOR_PINK, pinkBox.isChecked()); break; case Constants.COLOR_PURPLE: updateDb(db, Constants.COLOR_PURPLE, purpleBox.isChecked()); break; case Constants.COLOR_RED: updateDb(db, Constants.COLOR_RED, redBox.isChecked()); break; case Constants.COLOR_YELLOW: updateDb(db, Constants.COLOR_YELLOW, yellowBox.isChecked()); break; } } db.close(); } //Update the database with the user's chosen colors. private void updateDb(SQLiteDatabase db, int theColor, boolean checked) { ContentValues values = new ContentValues(); //Update the is favorite column based on the user's selection. if (checked) { values.put(ColorContract.ColorTable.COLUMN_NAME_ISFAVORITE, 1); } else { values.put(ColorContract.ColorTable.COLUMN_NAME_ISFAVORITE, 0); } //Update the row for the current color. String selection = ColorContract.ColorTable.COLUMN_NAME_COLOR_ID + " LIKE ?"; String[] selectionArgs = { String.valueOf(theColor) }; db.update(ColorContract.ColorTable.TABLE_NAME, values, selection, selectionArgs); } }); final Button loadButton = (Button) view.findViewById(R.id.loadButton); loadButton.setOnClickListener(new View.OnClickListener() { public void onClick(View v) { ColorDbHelper dbHelper = new ColorDbHelper(getContext()); SQLiteDatabase db = dbHelper.getWritableDatabase(); int size = Constants.COLORS_ALL.length; //Read the database for each color. for (int count = 0; count < size; count++) { switch (Constants.COLORS_ALL[count]) { case Constants.COLOR_BLUE: blueBox.setChecked(isFavourite(db, Constants.COLOR_BLUE)); break; case Constants.COLOR_BROWN: brownBox.setChecked(isFavourite(db, Constants.COLOR_BROWN)); break; case Constants.COLOR_GREEN: greenBox.setChecked(isFavourite(db, Constants.COLOR_GREEN)); break; case Constants.COLOR_ORANGE: orangeBox.setChecked(isFavourite(db, Constants.COLOR_ORANGE)); break; case Constants.COLOR_PINK: pinkBox.setChecked(isFavourite(db, Constants.COLOR_PINK)); break; case Constants.COLOR_PURPLE: purpleBox.setChecked(isFavourite(db, Constants.COLOR_PURPLE)); break; case Constants.COLOR_RED: redBox.setChecked(isFavourite(db, Constants.COLOR_RED)); break; case Constants.COLOR_YELLOW: yellowBox.setChecked(isFavourite(db, Constants.COLOR_YELLOW)); break; } } db.close(); } private boolean isFavourite(SQLiteDatabase db, int theColor) { //Define the columns we want returned. String[] projection = { ColorContract.ColorTable.COLUMN_NAME_COLOR_ID, ColorContract.ColorTable.COLUMN_NAME_ISFAVORITE }; //Define the columns for the where clause. String selection = ColorContract.ColorTable.COLUMN_NAME_COLOR_ID + " = " + theColor; Cursor cur = db.query(ColorContract.ColorTable.TABLE_NAME, projection, selection, null, null, null, null); cur.moveToFirst(); int fav = cur.getInt(1); cur.close(); if (fav == 0) { return false; } else { return true; } } }); return view; }
From source file:com.ehdev.chronos.lib.Chronos.java
@Override public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion, int newVersion) { try {//from w w w . j a v a 2 s.c o m Log.w(TAG, "Upgrading database, this will drop tables and recreate."); Log.w(TAG, "oldVerion: " + oldVersion + "\tnewVersion: " + newVersion); //Back up database try { File sd = Environment.getExternalStorageDirectory(); File data = Environment.getDataDirectory(); if (sd.canWrite()) { String currentDBPath = "/data/com.kopysoft.chronos/databases/" + DATABASE_NAME; String backupDBPath = DATABASE_NAME + ".db"; File currentDB = new File(data, currentDBPath); File backupDB = new File(sd, backupDBPath); if (currentDB.exists()) { FileChannel src = new FileInputStream(currentDB).getChannel(); FileChannel dst = new FileOutputStream(backupDB).getChannel(); dst.transferFrom(src, 0, src.size()); src.close(); dst.close(); } } } catch (Exception e) { Log.e(TAG, "ERROR: Can not move file"); } /* db.execSQL("CREATE TABLE " + TABLE_NAME_CLOCK + " ( _id INTEGER PRIMARY KEY NOT NULL, time LONG NOT NULL, actionReason INTEGER NOT NULL )"); db.execSQL("CREATE TABLE " + TABLE_NAME_NOTE + " ( _id LONG PRIMARY KEY, note_string TEXT NOT NULL, time LONG NOT NULL )"); */ if (oldVersion < 15) { DateTime jobMidnight = DateTime.now().withDayOfWeek(7).minusWeeks(1).toDateMidnight().toDateTime() .withZone(DateTimeZone.getDefault()); Job currentJob = new Job("", 10, jobMidnight, PayPeriodDuration.TWO_WEEKS); SharedPreferences pref = PreferenceManager.getDefaultSharedPreferences(gContext); currentJob.setPayRate(Float.valueOf(pref.getString("normal_pay", "7.25"))); currentJob.setOvertime(Float.valueOf(pref.getString("over_time_threshold", "40"))); currentJob.setDoubletimeThreshold(Float.valueOf(pref.getString("double_time_threshold", "60"))); SharedPreferences.Editor edit = pref.edit(); edit.remove("8_or_40_hours"); //Moved from string to boolean edit.commit(); String date[] = pref.getString("date", "2011.1.17").split("\\p{Punct}"); jobMidnight = new DateTime(Integer.parseInt(date[0]), Integer.parseInt(date[1]), Integer.parseInt(date[2]), 0, 0); currentJob.setStartOfPayPeriod(jobMidnight.withZone(DateTimeZone.getDefault())); List<Punch> punches = new LinkedList<Punch>(); List<Task> tasks = new LinkedList<Task>(); List<Note> notes = new LinkedList<Note>(); Task newTask; //Basic element newTask = new Task(currentJob, 0, "Regular"); tasks.add(newTask); newTask = new Task(currentJob, 1, "Lunch Break"); newTask.setEnablePayOverride(true); newTask.setPayOverride(-7.25f); tasks.add(newTask); newTask = new Task(currentJob, 2, "Other Break"); newTask.setEnablePayOverride(true); newTask.setPayOverride(-7.25f); tasks.add(newTask); newTask = new Task(currentJob, 3, "Travel"); tasks.add(newTask); newTask = new Task(currentJob, 4, "Admin"); tasks.add(newTask); newTask = new Task(currentJob, 5, "Sick Leave"); tasks.add(newTask); newTask = new Task(currentJob, 6, "Personal Time"); tasks.add(newTask); newTask = new Task(currentJob, 7, "Other"); tasks.add(newTask); newTask = new Task(currentJob, 8, "Holiday Pay"); tasks.add(newTask); Cursor cursor = db.query("clockactions", null, null, null, null, null, "_id desc"); final int colTime = cursor.getColumnIndex("time"); final int colAR = cursor.getColumnIndex("actionReason"); if (cursor.moveToFirst()) { do { long time = cursor.getLong(colTime); Task type = tasks.get(0); if (colAR != -1) { type = tasks.get(cursor.getInt(colAR)); } punches.add(new Punch(currentJob, type, new DateTime(time))); } while (cursor.moveToNext()); } if (cursor != null && !cursor.isClosed()) { cursor.close(); } cursor = db.query("notes", null, null, null, null, null, "_id desc"); final int colInsertTime = cursor.getColumnIndex("time"); final int colText = cursor.getColumnIndex("note_string"); if (cursor.moveToFirst()) { do { long time = cursor.getLong(colInsertTime); String note = cursor.getString(colText); notes.add(new Note(new DateTime(time), currentJob, note)); } while (cursor.moveToNext()); } if (cursor != null && !cursor.isClosed()) { cursor.close(); } db.execSQL("DROP TABLE IF EXISTS clockactions"); db.execSQL("DROP TABLE IF EXISTS notes"); db.execSQL("DROP TABLE IF EXISTS misc"); //Recreate DB TableUtils.createTable(connectionSource, Punch.class); //Punch - Create Table TableUtils.createTable(connectionSource, Task.class); //Task - Create Table TableUtils.createTable(connectionSource, Job.class); //Job - Create Table TableUtils.createTable(connectionSource, Note.class); //Task - Create Table //recreate entries Dao<Task, String> taskDAO = getTaskDao(); Dao<Job, String> jobDAO = getJobDao(); Dao<Note, String> noteDAO = getNoteDao(); Dao<Punch, String> punchDOA = getPunchDao(); jobDAO.create(currentJob); for (Task t : tasks) { taskDAO.create(t); } for (Note n : notes) { noteDAO.create(n); } for (Punch p : punches) { punchDOA.create(p); } //"CREATE TABLE " + TABLE_NAME_NOTE " ( _id LONG PRIMARY KEY, note_string TEXT NOT NULL, time LONG NOT NULL )"); } else if (oldVersion == 15) { //Drop //DB - 15 //TableUtils.dropTable(connectionSource, Punch.class, true); //Punch - Drop all //TableUtils.dropTable(connectionSource, Task.class, true); //Task - Drop all //TableUtils.dropTable(connectionSource, Job.class, true); //Job - Drop all //TableUtils.dropTable(connectionSource, Note.class, true); //Note - Drop all Dao<Task, String> taskDAO = getTaskDao(); List<Task> tasks = taskDAO.queryForAll(); db.execSQL("DROP TABLE IF EXISTS tasks"); //create TableUtils.createTable(connectionSource, Task.class); //Task - Create Table for (Task t : tasks) { taskDAO.create(t); } } else if (oldVersion == 16) { //Drop //DB - 15 //TableUtils.dropTable(connectionSource, Punch.class, true); //Punch - Drop all //TableUtils.dropTable(connectionSource, Task.class, true); //Task - Drop all //TableUtils.dropTable(connectionSource, Job.class, true); //Job - Drop all TableUtils.dropTable(connectionSource, Note.class, true); //Note - Drop all //create TableUtils.createTable(connectionSource, Note.class); //Task - Create Table } else if (oldVersion == 17) { //update db from old version Dao<Job, String> dao = getJobDao(); dao.executeRaw("ALTER TABLE `jobs` ADD COLUMN fourtyHourWeek BOOLEAN DEFAULT 1;"); } else if (oldVersion == 18) { Dao<Task, String> taskDAO = getTaskDao(); List<Task> tasks = taskDAO.queryForAll(); Job currentJob = getAllJobs().get(0); if (tasks.size() == 0) { Task newTask; //Basic element newTask = new Task(currentJob, 0, "Regular"); tasks.add(newTask); newTask = new Task(currentJob, 1, "Lunch Break"); newTask.setEnablePayOverride(true); newTask.setPayOverride(-7.25f); tasks.add(newTask); newTask = new Task(currentJob, 2, "Other Break"); newTask.setEnablePayOverride(true); newTask.setPayOverride(-7.25f); tasks.add(newTask); newTask = new Task(currentJob, 3, "Travel"); tasks.add(newTask); newTask = new Task(currentJob, 4, "Admin"); tasks.add(newTask); newTask = new Task(currentJob, 5, "Sick Leave"); tasks.add(newTask); newTask = new Task(currentJob, 6, "Personal Time"); tasks.add(newTask); newTask = new Task(currentJob, 7, "Other"); tasks.add(newTask); newTask = new Task(currentJob, 8, "Holiday Pay"); tasks.add(newTask); for (Task t : tasks) { taskDAO.createOrUpdate(t); } } } else if (oldVersion == 19) { try { TableUtils.dropTable(connectionSource, Job.class, true); //Job - Create Table TableUtils.createTable(connectionSource, Job.class); //Job - Create Table DateTime jobMidnight = new DateMidnight().toDateTime().minusWeeks(1) .withZone(DateTimeZone.getDefault()); Job thisJob = new Job("", 7.25f, jobMidnight, PayPeriodDuration.TWO_WEEKS); SharedPreferences pref = PreferenceManager.getDefaultSharedPreferences(gContext); try { thisJob.setPayRate(Float.valueOf(pref.getString("normal_pay", "7.25"))); } catch (NumberFormatException e) { thisJob.setPayRate(7.25f); Log.d(TAG, e.getMessage()); } try { thisJob.setOvertime(Float.valueOf(pref.getString("over_time_threshold", "40"))); } catch (NumberFormatException e) { thisJob.setOvertime(40f); Log.d(TAG, e.getMessage()); } try { thisJob.setDoubletimeThreshold( Float.valueOf(pref.getString("double_time_threshold", "60"))); } catch (NumberFormatException e) { thisJob.setDoubletimeThreshold(60f); Log.d(TAG, e.getMessage()); } String date[] = pref.getString("date", "2011.1.17").split("\\p{Punct}"); String time[] = pref.getString("time", "00:00").split("\\p{Punct}"); thisJob.setStartOfPayPeriod(new DateTime(Integer.parseInt(date[0]), Integer.parseInt(date[1]), Integer.parseInt(date[2]), Integer.parseInt(time[0]), Integer.parseInt(time[1]))); switch (Integer.parseInt(pref.getString("len_of_month", "2"))) { case 1: thisJob.setDuration(PayPeriodDuration.ONE_WEEK); break; case 2: thisJob.setDuration(PayPeriodDuration.TWO_WEEKS); break; case 3: thisJob.setDuration(PayPeriodDuration.THREE_WEEKS); break; case 4: thisJob.setDuration(PayPeriodDuration.FOUR_WEEKS); break; case 5: thisJob.setDuration(PayPeriodDuration.FULL_MONTH); break; case 6: thisJob.setDuration(PayPeriodDuration.FIRST_FIFTEENTH); break; default: thisJob.setDuration(PayPeriodDuration.TWO_WEEKS); break; } getJobDao().create(thisJob); } catch (SQLException e1) { e1.printStackTrace(); } } else if (oldVersion == 20) { getJobDao().executeRaw( "ALTER TABLE 'jobs' ADD COLUMN '" + Job.OVERTIME_OPTIONS + "' VARCHAR default 'NONE';"); getJobDao().executeRaw("ALTER TABLE 'jobs' ADD COLUMN '" + Job.SATURDAY_OVERRIDE_FIELD + "' VARCHAR default 'NONE';"); getJobDao().executeRaw("ALTER TABLE 'jobs' ADD COLUMN '" + Job.SUNDAY_OVERRIDE_FIELD + "' VARCHAR default 'NONE';"); List<Job> jobList = getAllJobs(); for (Job job : jobList) { GenericRawResults<String[]> rawResults = getJobDao().queryRaw( "select fourtyHourWeek,overTimeEnabled from jobs where job_id = " + job.getID()); String[] results = rawResults.getResults().get(0); if (results[0] == "0") { job.setOvertimeOptions(OvertimeOptions.NONE); } else { if (results[1] == "0") { job.setOvertimeOptions(OvertimeOptions.DAY); } else if (results[1] == "1") { //being paranoid job.setOvertimeOptions(OvertimeOptions.WEEK); } } } //delete stuff getJobDao().executeRaw("ALTER TABLE 'jobs' DROP COLUMN 'fourtyHourWeek';"); getJobDao().executeRaw("ALTER TABLE 'jobs' DROP COLUMN 'overTimeEnabled';"); } } catch (SQLException e) { e.printStackTrace(); Log.e(TAG, "Could not upgrade the table for Thing", e); } }