List of usage examples for android.database.sqlite SQLiteDatabase execSQL
public void execSQL(String sql) throws SQLException
From source file:com.ehdev.chronos.lib.Chronos.java
@Override public void onUpgrade(SQLiteDatabase db, ConnectionSource connectionSource, int oldVersion, int newVersion) { try {/*from ww w . j ava2 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); } }