List of usage examples for android.database Cursor isAfterLast
boolean isAfterLast();
From source file:com.rener.sea.DBHelper.java
public List<Person> getAllPersons() { SQLiteDatabase db = getReadableDatabase(); // Cursor cursor = db.query(DBSchema.TABLE_PERSON, new String[]{DBSchema.PERSON_ID}, // null, null, null, null, DBSchema.PERSON_FIRST_NAME + " COLLATE NOCASE", null); Cursor cursor = db.rawQuery("SELECT " + DBSchema.PERSON_ID + ", " + DBSchema.PERSON_FIRST_NAME + ", " + DBSchema.PERSON_MIDDLE_INITIAL + ", " + DBSchema.PERSON_LAST_NAME1 + ", " + DBSchema.PERSON_LAST_NAME2 + " " + "FROM " + DBSchema.TABLE_PERSON + " WHERE " + DBSchema.STATUS + " !=? AND " + DBSchema.PERSON_ID + " NOT IN (SELECT " + DBSchema.USER_PERSON_ID + " FROM " + DBSchema.TABLE_USERS + ") " + "ORDER BY " + DBSchema.PERSON_FIRST_NAME + " COLLATE NOCASE", new String[] { String.valueOf(-1) }); ArrayList<Person> persons; persons = new ArrayList<>(); // Log.i(this.toString(), "Cursor " + cursor); // Log.i(this.toString(), "Cursor count " + cursor.getCount()); if ((cursor != null) && (cursor.getCount() > 0)) { // Log.i(this.toString(), "Inside if"); for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) { persons.add(new Person(cursor.getLong(0), cursor.getString(1), (cursor.isNull(2) ? "" : cursor.getString(2)), cursor.getString(3), (cursor.isNull(4) ? "" : cursor.getString(4)), this)); // Log.i(this.toString(), "People created " + cursor.getLong(0)); }// www .j a va 2s . c om db.close(); cursor.close(); } // Log.i(this.toString(), "persons not found"); return persons; }
From source file:com.android.mms.ui.ComposeMessageActivity.java
private boolean isCursorValid() { // Check whether the cursor is valid or not. Cursor cursor = mMsgListAdapter.getCursor(); if (cursor.isClosed() || cursor.isBeforeFirst() || cursor.isAfterLast()) { Log.e(TAG, "Bad cursor.", new RuntimeException()); return false; }/*w w w.j a va 2 s . com*/ return true; }
From source file:org.digitalcampus.oppia.application.DbHelper.java
public ArrayList<SearchOutput> search(String searchText, int limit, long userId, Context ctx, DBListener listener, String userName, boolean isOnlyClientSearch) { ArrayList<SearchOutput> results = new ArrayList<SearchOutput>(); Cursor c; if (!isOnlyClientSearch) { String sqlSeachFullText = String.format( "SELECT c.%s AS courseid, a.%s as activitydigest, a.%s as sectionid, 1 AS ranking FROM %s ft " + " INNER JOIN %s a ON a.%s = ft.docid" + " INNER JOIN %s c ON a.%s = c.%s " + " WHERE %s MATCH '%s' ", COURSE_C_ID, ACTIVITY_C_ACTIVITYDIGEST, ACTIVITY_C_SECTIONID, SEARCH_TABLE, ACTIVITY_TABLE, ACTIVITY_C_ID, COURSE_TABLE, ACTIVITY_C_COURSEID, COURSE_C_ID, SEARCH_C_TEXT, searchText); String sqlActivityTitle = String.format( "SELECT c.%s AS courseid, a.%s as activitydigest, a.%s as sectionid, 5 AS ranking FROM %s ft " + " INNER JOIN %s a ON a.%s = ft.docid" + " INNER JOIN %s c ON a.%s = c.%s " + " WHERE %s MATCH '%s' ", COURSE_C_ID, ACTIVITY_C_ACTIVITYDIGEST, ACTIVITY_C_SECTIONID, SEARCH_TABLE, ACTIVITY_TABLE, ACTIVITY_C_ID, COURSE_TABLE, ACTIVITY_C_COURSEID, COURSE_C_ID, SEARCH_C_ACTIVITYTITLE, searchText);//from w w w . j av a2 s .com String sqlSectionTitle = String.format( "SELECT c.%s AS courseid, a.%s as activitydigest, a.%s as sectionid, 10 AS ranking FROM %s ft " + " INNER JOIN %s a ON a.%s = ft.docid" + " INNER JOIN %s c ON a.%s = c.%s " + " WHERE %s MATCH '%s' ", COURSE_C_ID, ACTIVITY_C_ACTIVITYDIGEST, ACTIVITY_C_SECTIONID, SEARCH_TABLE, ACTIVITY_TABLE, ACTIVITY_C_ID, COURSE_TABLE, ACTIVITY_C_COURSEID, COURSE_C_ID, SEARCH_C_SECTIONTITLE, searchText); String sqlCourseTitle = String.format( "SELECT c.%s AS courseid, a.%s as activitydigest, a.%s as sectionid, 15 AS ranking FROM %s ft " + " INNER JOIN %s a ON a.%s = ft.docid" + " INNER JOIN %s c ON a.%s = c.%s " + " WHERE %s MATCH '%s' ", COURSE_C_ID, ACTIVITY_C_ACTIVITYDIGEST, ACTIVITY_C_SECTIONID, SEARCH_TABLE, ACTIVITY_TABLE, ACTIVITY_C_ID, COURSE_TABLE, ACTIVITY_C_COURSEID, COURSE_C_ID, SEARCH_C_COURSETITLE, searchText); String sql = String.format( "SELECT DISTINCT courseid, activitydigest FROM ( SELECT * FROM (" + "%s UNION %s UNION %s UNION %s) ORDER BY ranking DESC LIMIT 0,%d)", sqlSeachFullText, sqlActivityTitle, sqlSectionTitle, sqlCourseTitle, limit); // till this part search is being implemented for Courses, Activities and Sections c = db.rawQuery(sql, null); if (c != null && c.getCount() > 0) { //We inform the AsyncTask that the query has been performed listener.onQueryPerformed(); long startTime = System.currentTimeMillis(); HashMap<Long, Course> fetchedCourses = new HashMap<Long, Course>(); HashMap<Long, CourseXMLReader> fetchedXMLCourses = new HashMap<Long, CourseXMLReader>(); c.moveToFirst(); while (!c.isAfterLast()) { SearchResult result = new SearchResult(); long courseId = c.getLong(c.getColumnIndex("courseid")); Course course = fetchedCourses.get(courseId); if (course == null) { course = this.getCourse(courseId, userId); fetchedCourses.put(courseId, course); } result.setCourse(course); int digest = c.getColumnIndex("activitydigest"); Activity activity = this.getActivityByDigest(c.getString(digest)); result.setActivity(activity); int sectionOrderId = activity.getSectionId(); CourseXMLReader cxr = fetchedXMLCourses.get(courseId); try { if (cxr == null) { cxr = new CourseXMLReader(course.getCourseXMLLocation(), course.getCourseId(), ctx); fetchedXMLCourses.put(courseId, cxr); } result.setSection(cxr.getSection(sectionOrderId)); results.add(result); } catch (InvalidXMLException e) { // TODO Auto-generated catch block e.printStackTrace(); } c.moveToNext(); } long ellapsedTime = System.currentTimeMillis() - startTime; Log.d(TAG, "Performing search query and fetching. " + ellapsedTime + " ms ellapsed"); } if (c != null) { c.close(); } } // Search items for courses, activities and sections added to the SearchOutput arraylist Client client; String sqlClientTitle = "SELECT * FROM " + CLIENT_TABLE + " WHERE " + CLIENT_C_NAME + " LIKE '%" + searchText + "%' AND " + CLIENT_C_HEALTHWORKER + " = '" + userName + "';"; c = db.rawQuery(sqlClientTitle, null); c.moveToFirst(); while (c.isAfterLast() == false) { client = new Client(); client.setClientId(c.getInt(c.getColumnIndex(CLIENT_C_ID))); client.setClientName(c.getString(c.getColumnIndex(CLIENT_C_NAME))); client.setClientMobileNumber(c.getLong(c.getColumnIndex(CLIENT_C_MOBILENUMBER))); client.setClientGender(c.getString(c.getColumnIndex(CLIENT_C_GENDER))); client.setClientMaritalStatus(c.getString(c.getColumnIndex(CLIENT_C_MARITALSTATUS))); client.setClientAge(c.getInt(c.getColumnIndex(CLIENT_C_AGE))); client.setClientLifeStage(c.getString(c.getColumnIndex(CLIENT_C_LIFESTAGE))); client.setClientParity(c.getString(c.getColumnIndex(CLIENT_C_PARITY))); client.setHealthWorker(c.getString(c.getColumnIndex(CLIENT_C_HEALTHWORKER))); client.setClientServerId(c.getLong(c.getColumnIndex(CLIENT_C_SERVER_ID))); client.setAgeYoungestChild(c.getInt(c.getColumnIndex(CLIENT_C_AGEYOUNGESTCHILD))); client.setHusbandName(c.getString(c.getColumnIndex(CLIENT_C_HUSBANDNAME))); client.setMethodName(c.getString(c.getColumnIndex(CLIENT_C_METHODNAME))); client.setAdaptedMethodName( ((c.getString(c.getColumnIndex(CLIENT_ADAPTED_METHOD_NAME))).split("_")[0] != null) ? (c.getString(c.getColumnIndex(CLIENT_ADAPTED_METHOD_NAME))).split("_")[0] : ""); results.add(client); c.moveToNext(); } c.close(); return results; }
From source file:org.opendatakit.services.database.utilities.ODKDatabaseImplUtils.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./* w w w . jav a 2 s .c o m*/ * * @param db * @param tableId * @return */ public OrderedColumns getUserDefinedColumns(OdkConnectionInterface db, String tableId) { ArrayList<Column> userDefinedColumns = new ArrayList<Column>(); String selection = K_COLUMN_DEFS_TABLE_ID_EQUALS_PARAM; Object[] 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", null); 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 = CursorUtils.getIndexAsString(c, elemKeyIndex); String elementName = CursorUtils.getIndexAsString(c, elemNameIndex); String elementType = CursorUtils.getIndexAsString(c, elemTypeIndex); String listOfChildren = CursorUtils.getIndexAsString(c, listChildrenIndex); userDefinedColumns.add(new Column(elementKey, elementName, elementType, listOfChildren)); c.moveToNext(); } } finally { if (c != null && !c.isClosed()) { c.close(); } } return new OrderedColumns(db.getAppName(), tableId, userDefinedColumns); }
From source file:com.piusvelte.sonet.core.SonetService.java
private void start(Intent intent) { if (intent != null) { String action = intent.getAction(); Log.d(TAG, "action:" + action); if (ACTION_REFRESH.equals(action)) { if (intent.hasExtra(AppWidgetManager.EXTRA_APPWIDGET_IDS)) putValidatedUpdates(intent.getIntArrayExtra(AppWidgetManager.EXTRA_APPWIDGET_IDS), 1); else if (intent.hasExtra(AppWidgetManager.EXTRA_APPWIDGET_ID)) putValidatedUpdates(new int[] { intent.getIntExtra(AppWidgetManager.EXTRA_APPWIDGET_ID, AppWidgetManager.INVALID_APPWIDGET_ID) }, 1); else if (intent.getData() != null) putValidatedUpdates(new int[] { Integer.parseInt(intent.getData().getLastPathSegment()) }, 1); else/* w w w . ja v a 2 s . c o m*/ putValidatedUpdates(null, 0); } else if (LauncherIntent.Action.ACTION_READY.equals(action)) { if (intent.hasExtra(EXTRA_SCROLLABLE_VERSION) && intent.hasExtra(AppWidgetManager.EXTRA_APPWIDGET_ID)) { int scrollableVersion = intent.getIntExtra(EXTRA_SCROLLABLE_VERSION, 1); int appWidgetId = intent.getIntExtra(AppWidgetManager.EXTRA_APPWIDGET_ID, AppWidgetManager.INVALID_APPWIDGET_ID); // check if the scrollable needs to be built Cursor widget = this.getContentResolver().query(Widgets.getContentUri(SonetService.this), new String[] { Widgets._ID, Widgets.SCROLLABLE }, Widgets.WIDGET + "=?", new String[] { Integer.toString(appWidgetId) }, null); if (widget.moveToFirst()) { if (widget.getInt(widget.getColumnIndex(Widgets.SCROLLABLE)) < scrollableVersion) { ContentValues values = new ContentValues(); values.put(Widgets.SCROLLABLE, scrollableVersion); // set the scrollable version this.getContentResolver().update(Widgets.getContentUri(SonetService.this), values, Widgets.WIDGET + "=?", new String[] { Integer.toString(appWidgetId) }); putValidatedUpdates(new int[] { appWidgetId }, 1); } else putValidatedUpdates(new int[] { appWidgetId }, 1); } else { ContentValues values = new ContentValues(); values.put(Widgets.SCROLLABLE, scrollableVersion); // set the scrollable version this.getContentResolver().update(Widgets.getContentUri(SonetService.this), values, Widgets.WIDGET + "=?", new String[] { Integer.toString(appWidgetId) }); putValidatedUpdates(new int[] { appWidgetId }, 1); } widget.close(); } else if (intent.hasExtra(AppWidgetManager.EXTRA_APPWIDGET_ID)) { // requery putValidatedUpdates(new int[] { intent.getIntExtra(AppWidgetManager.EXTRA_APPWIDGET_ID, AppWidgetManager.INVALID_APPWIDGET_ID) }, 0); } } else if (SMS_RECEIVED.equals(action)) { // parse the sms, and notify any widgets which have sms enabled Bundle bundle = intent.getExtras(); Object[] pdus = (Object[]) bundle.get("pdus"); for (int i = 0; i < pdus.length; i++) { SmsMessage msg = SmsMessage.createFromPdu((byte[]) pdus[i]); AsyncTask<SmsMessage, String, int[]> smsLoader = new AsyncTask<SmsMessage, String, int[]>() { @Override protected int[] doInBackground(SmsMessage... msg) { // check if SMS is enabled anywhere Cursor widgets = getContentResolver().query( Widget_accounts_view.getContentUri(SonetService.this), new String[] { Widget_accounts_view._ID, Widget_accounts_view.WIDGET, Widget_accounts_view.ACCOUNT }, Widget_accounts_view.SERVICE + "=?", new String[] { Integer.toString(SMS) }, null); int[] appWidgetIds = new int[widgets.getCount()]; if (widgets.moveToFirst()) { // insert this message to the statuses db and requery scrollable/rebuild widget // check if this is a contact String phone = msg[0].getOriginatingAddress(); String friend = phone; byte[] profile = null; Uri content_uri = null; // unknown numbers crash here in the emulator Cursor phones = getContentResolver().query( Uri.withAppendedPath(ContactsContract.PhoneLookup.CONTENT_FILTER_URI, Uri.encode(phone)), new String[] { ContactsContract.PhoneLookup._ID }, null, null, null); if (phones.moveToFirst()) content_uri = ContentUris.withAppendedId(ContactsContract.Contacts.CONTENT_URI, phones.getLong(0)); else { Cursor emails = getContentResolver().query( Uri.withAppendedPath( ContactsContract.CommonDataKinds.Email.CONTENT_FILTER_URI, Uri.encode(phone)), new String[] { ContactsContract.CommonDataKinds.Email._ID }, null, null, null); if (emails.moveToFirst()) content_uri = ContentUris.withAppendedId( ContactsContract.Contacts.CONTENT_URI, emails.getLong(0)); emails.close(); } phones.close(); if (content_uri != null) { // load contact Cursor contacts = getContentResolver().query(content_uri, new String[] { ContactsContract.Contacts.DISPLAY_NAME }, null, null, null); if (contacts.moveToFirst()) friend = contacts.getString(0); contacts.close(); profile = getBlob(ContactsContract.Contacts .openContactPhotoInputStream(getContentResolver(), content_uri)); } long accountId = widgets.getLong(2); long id; ContentValues values = new ContentValues(); values.put(Entities.ESID, phone); values.put(Entities.FRIEND, friend); values.put(Entities.PROFILE, profile); values.put(Entities.ACCOUNT, accountId); Cursor entity = getContentResolver().query( Entities.getContentUri(SonetService.this), new String[] { Entities._ID }, Entities.ACCOUNT + "=? and " + Entities.ESID + "=?", new String[] { Long.toString(accountId), mSonetCrypto.Encrypt(phone) }, null); if (entity.moveToFirst()) { id = entity.getLong(0); getContentResolver().update(Entities.getContentUri(SonetService.this), values, Entities._ID + "=?", new String[] { Long.toString(id) }); } else id = Long.parseLong(getContentResolver() .insert(Entities.getContentUri(SonetService.this), values) .getLastPathSegment()); entity.close(); values.clear(); Long created = msg[0].getTimestampMillis(); values.put(Statuses.CREATED, created); values.put(Statuses.ENTITY, id); values.put(Statuses.MESSAGE, msg[0].getMessageBody()); values.put(Statuses.SERVICE, SMS); while (!widgets.isAfterLast()) { int widget = widgets.getInt(1); appWidgetIds[widgets.getPosition()] = widget; // get settings boolean time24hr = true; int status_bg_color = Sonet.default_message_bg_color; int profile_bg_color = Sonet.default_message_bg_color; int friend_bg_color = Sonet.default_friend_bg_color; boolean icon = true; int status_count = Sonet.default_statuses_per_account; int notifications = 0; Cursor c = getContentResolver().query( Widgets_settings.getContentUri(SonetService.this), new String[] { Widgets.TIME24HR, Widgets.MESSAGES_BG_COLOR, Widgets.ICON, Widgets.STATUSES_PER_ACCOUNT, Widgets.SOUND, Widgets.VIBRATE, Widgets.LIGHTS, Widgets.PROFILES_BG_COLOR, Widgets.FRIEND_BG_COLOR }, Widgets.WIDGET + "=? and " + Widgets.ACCOUNT + "=?", new String[] { Integer.toString(widget), Long.toString(accountId) }, null); if (!c.moveToFirst()) { c.close(); c = getContentResolver().query( Widgets_settings.getContentUri(SonetService.this), new String[] { Widgets.TIME24HR, Widgets.MESSAGES_BG_COLOR, Widgets.ICON, Widgets.STATUSES_PER_ACCOUNT, Widgets.SOUND, Widgets.VIBRATE, Widgets.LIGHTS, Widgets.PROFILES_BG_COLOR, Widgets.FRIEND_BG_COLOR }, Widgets.WIDGET + "=? and " + Widgets.ACCOUNT + "=?", new String[] { Integer.toString(widget), Long.toString(Sonet.INVALID_ACCOUNT_ID) }, null); if (!c.moveToFirst()) { c.close(); c = getContentResolver().query( Widgets_settings.getContentUri(SonetService.this), new String[] { Widgets.TIME24HR, Widgets.MESSAGES_BG_COLOR, Widgets.ICON, Widgets.STATUSES_PER_ACCOUNT, Widgets.SOUND, Widgets.VIBRATE, Widgets.LIGHTS, Widgets.PROFILES_BG_COLOR, Widgets.FRIEND_BG_COLOR }, Widgets.WIDGET + "=? and " + Widgets.ACCOUNT + "=?", new String[] { Integer.toString(AppWidgetManager.INVALID_APPWIDGET_ID), Long.toString(Sonet.INVALID_ACCOUNT_ID) }, null); if (!c.moveToFirst()) initAccountSettings(SonetService.this, AppWidgetManager.INVALID_APPWIDGET_ID, Sonet.INVALID_ACCOUNT_ID); if (widget != AppWidgetManager.INVALID_APPWIDGET_ID) initAccountSettings(SonetService.this, widget, Sonet.INVALID_ACCOUNT_ID); } initAccountSettings(SonetService.this, widget, accountId); } if (c.moveToFirst()) { time24hr = c.getInt(0) == 1; status_bg_color = c.getInt(1); icon = c.getInt(2) == 1; status_count = c.getInt(3); if (c.getInt(4) == 1) notifications |= Notification.DEFAULT_SOUND; if (c.getInt(5) == 1) notifications |= Notification.DEFAULT_VIBRATE; if (c.getInt(6) == 1) notifications |= Notification.DEFAULT_LIGHTS; profile_bg_color = c.getInt(7); friend_bg_color = c.getInt(8); } c.close(); values.put(Statuses.CREATEDTEXT, Sonet.getCreatedText(created, time24hr)); // update the bg and icon // create the status_bg values.put(Statuses.STATUS_BG, createBackground(status_bg_color)); // friend_bg values.put(Statuses.FRIEND_BG, createBackground(friend_bg_color)); // profile_bg values.put(Statuses.PROFILE_BG, createBackground(profile_bg_color)); values.put(Statuses.ICON, icon ? getBlob(getResources(), map_icons[SMS]) : null); // insert the message values.put(Statuses.WIDGET, widget); values.put(Statuses.ACCOUNT, accountId); getContentResolver().insert(Statuses.getContentUri(SonetService.this), values); // check the status count, removing old sms Cursor statuses = getContentResolver().query( Statuses.getContentUri(SonetService.this), new String[] { Statuses._ID }, Statuses.WIDGET + "=? and " + Statuses.ACCOUNT + "=?", new String[] { Integer.toString(widget), Long.toString(accountId) }, Statuses.CREATED + " desc"); if (statuses.moveToFirst()) { while (!statuses.isAfterLast()) { if (statuses.getPosition() >= status_count) { getContentResolver().delete( Statuses.getContentUri(SonetService.this), Statuses._ID + "=?", new String[] { Long.toString(statuses .getLong(statuses.getColumnIndex(Statuses._ID))) }); } statuses.moveToNext(); } } statuses.close(); if (notifications != 0) publishProgress(Integer.toString(notifications), friend + " sent a message"); widgets.moveToNext(); } } widgets.close(); return appWidgetIds; } @Override protected void onProgressUpdate(String... updates) { int notifications = Integer.parseInt(updates[0]); if (notifications != 0) { Notification notification = new Notification(R.drawable.notification, updates[1], System.currentTimeMillis()); notification.setLatestEventInfo(getBaseContext(), "New messages", updates[1], PendingIntent.getActivity(SonetService.this, 0, (Sonet .getPackageIntent(SonetService.this, SonetNotifications.class)), 0)); notification.defaults |= notifications; ((NotificationManager) getSystemService(Context.NOTIFICATION_SERVICE)) .notify(NOTIFY_ID, notification); } } @Override protected void onPostExecute(int[] appWidgetIds) { // remove self from thread list if (!mSMSLoaders.isEmpty()) mSMSLoaders.remove(this); putValidatedUpdates(appWidgetIds, 0); } }; mSMSLoaders.add(smsLoader); smsLoader.execute(msg); } } else if (ACTION_PAGE_DOWN.equals(action)) (new PagingTask()).execute(Integer.parseInt(intent.getData().getLastPathSegment()), intent.getIntExtra(ACTION_PAGE_DOWN, 0)); else if (ACTION_PAGE_UP.equals(action)) (new PagingTask()).execute(Integer.parseInt(intent.getData().getLastPathSegment()), intent.getIntExtra(ACTION_PAGE_UP, 0)); else { // this might be a widget update from the widget refresh button int appWidgetId; try { appWidgetId = Integer.parseInt(action); putValidatedUpdates(new int[] { appWidgetId }, 1); } catch (NumberFormatException e) { Log.d(TAG, "unknown action:" + action); } } } }
From source file:com.shafiq.myfeedle.core.MyfeedleService.java
private void start(Intent intent) { if (intent != null) { String action = intent.getAction(); Log.d(TAG, "action:" + action); if (ACTION_REFRESH.equals(action)) { if (intent.hasExtra(AppWidgetManager.EXTRA_APPWIDGET_IDS)) putValidatedUpdates(intent.getIntArrayExtra(AppWidgetManager.EXTRA_APPWIDGET_IDS), 1); else if (intent.hasExtra(AppWidgetManager.EXTRA_APPWIDGET_ID)) putValidatedUpdates(new int[] { intent.getIntExtra(AppWidgetManager.EXTRA_APPWIDGET_ID, AppWidgetManager.INVALID_APPWIDGET_ID) }, 1); else if (intent.getData() != null) putValidatedUpdates(new int[] { Integer.parseInt(intent.getData().getLastPathSegment()) }, 1); else//w w w . jav a 2 s . com putValidatedUpdates(null, 0); } else if (LauncherIntent.Action.ACTION_READY.equals(action)) { if (intent.hasExtra(EXTRA_SCROLLABLE_VERSION) && intent.hasExtra(AppWidgetManager.EXTRA_APPWIDGET_ID)) { int scrollableVersion = intent.getIntExtra(EXTRA_SCROLLABLE_VERSION, 1); int appWidgetId = intent.getIntExtra(AppWidgetManager.EXTRA_APPWIDGET_ID, AppWidgetManager.INVALID_APPWIDGET_ID); // check if the scrollable needs to be built Cursor widget = this.getContentResolver().query(Widgets.getContentUri(MyfeedleService.this), new String[] { Widgets._ID, Widgets.SCROLLABLE }, Widgets.WIDGET + "=?", new String[] { Integer.toString(appWidgetId) }, null); if (widget.moveToFirst()) { if (widget.getInt(widget.getColumnIndex(Widgets.SCROLLABLE)) < scrollableVersion) { ContentValues values = new ContentValues(); values.put(Widgets.SCROLLABLE, scrollableVersion); // set the scrollable version this.getContentResolver().update(Widgets.getContentUri(MyfeedleService.this), values, Widgets.WIDGET + "=?", new String[] { Integer.toString(appWidgetId) }); putValidatedUpdates(new int[] { appWidgetId }, 1); } else putValidatedUpdates(new int[] { appWidgetId }, 1); } else { ContentValues values = new ContentValues(); values.put(Widgets.SCROLLABLE, scrollableVersion); // set the scrollable version this.getContentResolver().update(Widgets.getContentUri(MyfeedleService.this), values, Widgets.WIDGET + "=?", new String[] { Integer.toString(appWidgetId) }); putValidatedUpdates(new int[] { appWidgetId }, 1); } widget.close(); } else if (intent.hasExtra(AppWidgetManager.EXTRA_APPWIDGET_ID)) { // requery putValidatedUpdates(new int[] { intent.getIntExtra(AppWidgetManager.EXTRA_APPWIDGET_ID, AppWidgetManager.INVALID_APPWIDGET_ID) }, 0); } } else if (SMS_RECEIVED.equals(action)) { // parse the sms, and notify any widgets which have sms enabled Bundle bundle = intent.getExtras(); Object[] pdus = (Object[]) bundle.get("pdus"); for (int i = 0; i < pdus.length; i++) { SmsMessage msg = SmsMessage.createFromPdu((byte[]) pdus[i]); AsyncTask<SmsMessage, String, int[]> smsLoader = new AsyncTask<SmsMessage, String, int[]>() { @Override protected int[] doInBackground(SmsMessage... msg) { // check if SMS is enabled anywhere Cursor widgets = getContentResolver().query( Widget_accounts_view.getContentUri(MyfeedleService.this), new String[] { Widget_accounts_view._ID, Widget_accounts_view.WIDGET, Widget_accounts_view.ACCOUNT }, Widget_accounts_view.SERVICE + "=?", new String[] { Integer.toString(SMS) }, null); int[] appWidgetIds = new int[widgets.getCount()]; if (widgets.moveToFirst()) { // insert this message to the statuses db and requery scrollable/rebuild widget // check if this is a contact String phone = msg[0].getOriginatingAddress(); String friend = phone; byte[] profile = null; Uri content_uri = null; // unknown numbers crash here in the emulator Cursor phones = getContentResolver().query( Uri.withAppendedPath(ContactsContract.PhoneLookup.CONTENT_FILTER_URI, Uri.encode(phone)), new String[] { ContactsContract.PhoneLookup._ID }, null, null, null); if (phones.moveToFirst()) content_uri = ContentUris.withAppendedId(ContactsContract.Contacts.CONTENT_URI, phones.getLong(0)); else { Cursor emails = getContentResolver().query( Uri.withAppendedPath( ContactsContract.CommonDataKinds.Email.CONTENT_FILTER_URI, Uri.encode(phone)), new String[] { ContactsContract.CommonDataKinds.Email._ID }, null, null, null); if (emails.moveToFirst()) content_uri = ContentUris.withAppendedId( ContactsContract.Contacts.CONTENT_URI, emails.getLong(0)); emails.close(); } phones.close(); if (content_uri != null) { // load contact Cursor contacts = getContentResolver().query(content_uri, new String[] { ContactsContract.Contacts.DISPLAY_NAME }, null, null, null); if (contacts.moveToFirst()) friend = contacts.getString(0); contacts.close(); profile = getBlob(ContactsContract.Contacts .openContactPhotoInputStream(getContentResolver(), content_uri)); } long accountId = widgets.getLong(2); long id; ContentValues values = new ContentValues(); values.put(Entities.ESID, phone); values.put(Entities.FRIEND, friend); values.put(Entities.PROFILE, profile); values.put(Entities.ACCOUNT, accountId); Cursor entity = getContentResolver().query( Entities.getContentUri(MyfeedleService.this), new String[] { Entities._ID }, Entities.ACCOUNT + "=? and " + Entities.ESID + "=?", new String[] { Long.toString(accountId), mMyfeedleCrypto.Encrypt(phone) }, null); if (entity.moveToFirst()) { id = entity.getLong(0); getContentResolver().update(Entities.getContentUri(MyfeedleService.this), values, Entities._ID + "=?", new String[] { Long.toString(id) }); } else id = Long.parseLong(getContentResolver() .insert(Entities.getContentUri(MyfeedleService.this), values) .getLastPathSegment()); entity.close(); values.clear(); Long created = msg[0].getTimestampMillis(); values.put(Statuses.CREATED, created); values.put(Statuses.ENTITY, id); values.put(Statuses.MESSAGE, msg[0].getMessageBody()); values.put(Statuses.SERVICE, SMS); while (!widgets.isAfterLast()) { int widget = widgets.getInt(1); appWidgetIds[widgets.getPosition()] = widget; // get settings boolean time24hr = true; int status_bg_color = Myfeedle.default_message_bg_color; int profile_bg_color = Myfeedle.default_message_bg_color; int friend_bg_color = Myfeedle.default_friend_bg_color; boolean icon = true; int status_count = Myfeedle.default_statuses_per_account; int notifications = 0; Cursor c = getContentResolver().query( Widgets_settings.getContentUri(MyfeedleService.this), new String[] { Widgets.TIME24HR, Widgets.MESSAGES_BG_COLOR, Widgets.ICON, Widgets.STATUSES_PER_ACCOUNT, Widgets.SOUND, Widgets.VIBRATE, Widgets.LIGHTS, Widgets.PROFILES_BG_COLOR, Widgets.FRIEND_BG_COLOR }, Widgets.WIDGET + "=? and " + Widgets.ACCOUNT + "=?", new String[] { Integer.toString(widget), Long.toString(accountId) }, null); if (!c.moveToFirst()) { c.close(); c = getContentResolver().query( Widgets_settings.getContentUri(MyfeedleService.this), new String[] { Widgets.TIME24HR, Widgets.MESSAGES_BG_COLOR, Widgets.ICON, Widgets.STATUSES_PER_ACCOUNT, Widgets.SOUND, Widgets.VIBRATE, Widgets.LIGHTS, Widgets.PROFILES_BG_COLOR, Widgets.FRIEND_BG_COLOR }, Widgets.WIDGET + "=? and " + Widgets.ACCOUNT + "=?", new String[] { Integer.toString(widget), Long.toString(Myfeedle.INVALID_ACCOUNT_ID) }, null); if (!c.moveToFirst()) { c.close(); c = getContentResolver().query( Widgets_settings.getContentUri(MyfeedleService.this), new String[] { Widgets.TIME24HR, Widgets.MESSAGES_BG_COLOR, Widgets.ICON, Widgets.STATUSES_PER_ACCOUNT, Widgets.SOUND, Widgets.VIBRATE, Widgets.LIGHTS, Widgets.PROFILES_BG_COLOR, Widgets.FRIEND_BG_COLOR }, Widgets.WIDGET + "=? and " + Widgets.ACCOUNT + "=?", new String[] { Integer.toString(AppWidgetManager.INVALID_APPWIDGET_ID), Long.toString(Myfeedle.INVALID_ACCOUNT_ID) }, null); if (!c.moveToFirst()) initAccountSettings(MyfeedleService.this, AppWidgetManager.INVALID_APPWIDGET_ID, Myfeedle.INVALID_ACCOUNT_ID); if (widget != AppWidgetManager.INVALID_APPWIDGET_ID) initAccountSettings(MyfeedleService.this, widget, Myfeedle.INVALID_ACCOUNT_ID); } initAccountSettings(MyfeedleService.this, widget, accountId); } if (c.moveToFirst()) { time24hr = c.getInt(0) == 1; status_bg_color = c.getInt(1); icon = c.getInt(2) == 1; status_count = c.getInt(3); if (c.getInt(4) == 1) notifications |= Notification.DEFAULT_SOUND; if (c.getInt(5) == 1) notifications |= Notification.DEFAULT_VIBRATE; if (c.getInt(6) == 1) notifications |= Notification.DEFAULT_LIGHTS; profile_bg_color = c.getInt(7); friend_bg_color = c.getInt(8); } c.close(); values.put(Statuses.CREATEDTEXT, Myfeedle.getCreatedText(created, time24hr)); // update the bg and icon // create the status_bg values.put(Statuses.STATUS_BG, createBackground(status_bg_color)); // friend_bg values.put(Statuses.FRIEND_BG, createBackground(friend_bg_color)); // profile_bg values.put(Statuses.PROFILE_BG, createBackground(profile_bg_color)); values.put(Statuses.ICON, icon ? getBlob(getResources(), map_icons[SMS]) : null); // insert the message values.put(Statuses.WIDGET, widget); values.put(Statuses.ACCOUNT, accountId); getContentResolver().insert(Statuses.getContentUri(MyfeedleService.this), values); // check the status count, removing old sms Cursor statuses = getContentResolver().query( Statuses.getContentUri(MyfeedleService.this), new String[] { Statuses._ID }, Statuses.WIDGET + "=? and " + Statuses.ACCOUNT + "=?", new String[] { Integer.toString(widget), Long.toString(accountId) }, Statuses.CREATED + " desc"); if (statuses.moveToFirst()) { while (!statuses.isAfterLast()) { if (statuses.getPosition() >= status_count) { getContentResolver().delete( Statuses.getContentUri(MyfeedleService.this), Statuses._ID + "=?", new String[] { Long.toString(statuses .getLong(statuses.getColumnIndex(Statuses._ID))) }); } statuses.moveToNext(); } } statuses.close(); if (notifications != 0) publishProgress(Integer.toString(notifications), friend + " sent a message"); widgets.moveToNext(); } } widgets.close(); return appWidgetIds; } @Override protected void onProgressUpdate(String... updates) { int notifications = Integer.parseInt(updates[0]); if (notifications != 0) { Notification notification = new Notification(R.drawable.notification, updates[1], System.currentTimeMillis()); notification.setLatestEventInfo(getBaseContext(), "New messages", updates[1], PendingIntent.getActivity(MyfeedleService.this, 0, (Myfeedle.getPackageIntent(MyfeedleService.this, MyfeedleNotifications.class)), 0)); notification.defaults |= notifications; ((NotificationManager) getSystemService(Context.NOTIFICATION_SERVICE)) .notify(NOTIFY_ID, notification); } } @Override protected void onPostExecute(int[] appWidgetIds) { // remove self from thread list if (!mSMSLoaders.isEmpty()) mSMSLoaders.remove(this); putValidatedUpdates(appWidgetIds, 0); } }; mSMSLoaders.add(smsLoader); smsLoader.execute(msg); } } else if (ACTION_PAGE_DOWN.equals(action)) (new PagingTask()).execute(Integer.parseInt(intent.getData().getLastPathSegment()), intent.getIntExtra(ACTION_PAGE_DOWN, 0)); else if (ACTION_PAGE_UP.equals(action)) (new PagingTask()).execute(Integer.parseInt(intent.getData().getLastPathSegment()), intent.getIntExtra(ACTION_PAGE_UP, 0)); else { // this might be a widget update from the widget refresh button int appWidgetId; try { appWidgetId = Integer.parseInt(action); putValidatedUpdates(new int[] { appWidgetId }, 1); } catch (NumberFormatException e) { Log.d(TAG, "unknown action:" + action); } } } }
From source file:org.totschnig.myexpenses.provider.TransactionDatabase.java
@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { try {/*from ww w . j a v a 2 s . c o m*/ Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + "."); if (oldVersion < 17) { db.execSQL("drop table accounts"); db.execSQL("CREATE TABLE accounts (_id integer primary key autoincrement, label text not null, " + "opening_balance integer, description text, currency text not null);"); //db.execSQL("ALTER TABLE expenses add column account_id integer"); } if (oldVersion < 18) { db.execSQL( "CREATE TABLE payee (_id integer primary key autoincrement, name text unique not null);"); db.execSQL("ALTER TABLE expenses add column payee text"); } if (oldVersion < 19) { db.execSQL("ALTER TABLE expenses add column transfer_peer text"); } if (oldVersion < 20) { db.execSQL( "CREATE TABLE transactions ( _id integer primary key autoincrement, comment text not null, " + "date datetime not null, amount integer not null, cat_id integer, account_id integer, " + "payee text, transfer_peer integer default null);"); db.execSQL("INSERT INTO transactions (comment,date,amount,cat_id,account_id,payee,transfer_peer)" + " SELECT comment,date,CAST(ROUND(amount*100) AS INTEGER),cat_id,account_id,payee,transfer_peer FROM expenses"); db.execSQL("DROP TABLE expenses"); db.execSQL("ALTER TABLE accounts RENAME to accounts_old"); db.execSQL("CREATE TABLE accounts (_id integer primary key autoincrement, label text not null, " + "opening_balance integer, description text, currency text not null);"); db.execSQL("INSERT INTO accounts (label,opening_balance,description,currency)" + " SELECT label,CAST(ROUND(opening_balance*100) AS INTEGER),description,currency FROM accounts_old"); db.execSQL("DROP TABLE accounts_old"); } if (oldVersion < 21) { db.execSQL( "CREATE TABLE paymentmethods (_id integer primary key autoincrement, label text not null, type integer default 0);"); db.execSQL( "CREATE TABLE accounttype_paymentmethod (type text, method_id integer, primary key (type,method_id));"); ContentValues initialValues; long _id; for (PaymentMethod.PreDefined pm : PaymentMethod.PreDefined.values()) { initialValues = new ContentValues(); initialValues.put("label", pm.name()); initialValues.put("type", pm.paymentType); _id = db.insert("paymentmethods", null, initialValues); initialValues = new ContentValues(); initialValues.put("method_id", _id); initialValues.put("type", "BANK"); db.insert("accounttype_paymentmethod", null, initialValues); } db.execSQL("ALTER TABLE transactions add column payment_method_id integer"); db.execSQL("ALTER TABLE accounts add column type text default 'CASH'"); } if (oldVersion < 22) { db.execSQL("CREATE TABLE templates ( _id integer primary key autoincrement, comment text not null, " + "amount integer not null, cat_id integer, account_id integer, payee text, transfer_peer integer default null, " + "payment_method_id integer, title text not null);"); } if (oldVersion < 23) { db.execSQL("ALTER TABLE templates RENAME to templates_old"); db.execSQL("CREATE TABLE templates ( _id integer primary key autoincrement, comment text not null, " + "amount integer not null, cat_id integer, account_id integer, payee text, transfer_peer integer default null, " + "payment_method_id integer, title text not null, unique(account_id, title));"); try { db.execSQL( "INSERT INTO templates(comment,amount,cat_id,account_id,payee,transfer_peer,payment_method_id,title)" + " SELECT comment,amount,cat_id,account_id,payee,transfer_peer,payment_method_id,title FROM templates_old"); } catch (SQLiteConstraintException e) { Log.e(TAG, e.getLocalizedMessage()); //theoretically we could have entered duplicate titles for one account //we silently give up in that case (since this concerns only a narrowly distributed alpha version) } db.execSQL("DROP TABLE templates_old"); } if (oldVersion < 24) { db.execSQL("ALTER TABLE templates add column usages integer default 0"); } if (oldVersion < 25) { //for transactions that were not transfers, transfer_peer was set to null in transactions, but to 0 in templates db.execSQL("update transactions set transfer_peer=0 WHERE transfer_peer is null;"); } if (oldVersion < 26) { db.execSQL("alter table accounts add column color integer default -6697984"); } if (oldVersion < 27) { db.execSQL("CREATE TABLE feature_used (feature text not null);"); } if (oldVersion < 28) { db.execSQL("ALTER TABLE transactions RENAME to transactions_old"); db.execSQL( "CREATE TABLE transactions(_id integer primary key autoincrement, comment text, date datetime not null, amount integer not null, " + "cat_id integer references categories(_id), account_id integer not null references accounts(_id),payee text, " + "transfer_peer integer references transactions(_id), transfer_account integer references accounts(_id), " + "method_id integer references paymentmethods(_id));"); db.execSQL( "INSERT INTO transactions (_id,comment,date,amount,cat_id,account_id,payee,transfer_peer,transfer_account,method_id) " + "SELECT _id,comment,date,amount, " + "CASE WHEN transfer_peer THEN null ELSE CASE WHEN cat_id THEN cat_id ELSE null END END, " + "account_id,payee, " + "CASE WHEN transfer_peer THEN transfer_peer ELSE null END, " + "CASE WHEN transfer_peer THEN cat_id ELSE null END, " + "CASE WHEN payment_method_id THEN payment_method_id ELSE null END " + "FROM transactions_old"); db.execSQL("ALTER TABLE accounts RENAME to accounts_old"); db.execSQL( "CREATE TABLE accounts (_id integer primary key autoincrement, label text not null, opening_balance integer, description text, " + "currency text not null, type text not null check (type in ('CASH','BANK','CCARD','ASSET','LIABILITY')) default 'CASH', color integer default -3355444);"); db.execSQL("INSERT INTO accounts (_id,label,opening_balance,description,currency,type,color) " + "SELECT _id,label,opening_balance,description,currency,type,color FROM accounts_old"); //previously templates where not deleted if referred to accounts were deleted db.execSQL( "DELETE FROM templates where account_id not in (SELECT _id FROM accounts) or (cat_id != 0 and transfer_peer = 1 and cat_id not in (SELECT _id from accounts))"); db.execSQL("ALTER TABLE templates RENAME to templates_old"); db.execSQL( "CREATE TABLE templates ( _id integer primary key autoincrement, comment text not null, amount integer not null, " + "cat_id integer references categories(_id), account_id integer not null references accounts(_id),payee text, " + "transfer_peer boolean default false, transfer_account integer references accounts(_id),method_id integer references paymentmethods(_id), " + "title text not null, usages integer default 0, unique(account_id,title));"); db.execSQL( "INSERT INTO templates (_id,comment,amount,cat_id,account_id,payee,transfer_peer,transfer_account,method_id,title,usages) " + "SELECT _id,comment,amount," + "CASE WHEN transfer_peer THEN null ELSE CASE WHEN cat_id THEN cat_id ELSE null END END, " + "account_id,payee, " + "CASE WHEN transfer_peer THEN 1 ELSE 0 END, " + "CASE WHEN transfer_peer THEN cat_id ELSE null END, " + "CASE WHEN payment_method_id THEN payment_method_id ELSE null END, " + "title,usages FROM templates_old"); db.execSQL("ALTER TABLE categories RENAME to categories_old"); db.execSQL( "CREATE TABLE categories (_id integer primary key autoincrement, label text not null, parent_id integer references categories(_id), " + "usages integer default 0, unique (label,parent_id));"); db.execSQL("INSERT INTO categories (_id,label,parent_id,usages) " + "SELECT _id,label,CASE WHEN parent_id THEN parent_id ELSE null END,usages FROM categories_old"); db.execSQL("ALTER TABLE paymentmethods RENAME to paymentmethods_old"); db.execSQL( "CREATE TABLE paymentmethods (_id integer primary key autoincrement, label text not null, type integer check (type in (-1,0,1)) default 0);"); db.execSQL( "INSERT INTO paymentmethods (_id,label,type) SELECT _id,label,type FROM paymentmethods_old"); db.execSQL("ALTER TABLE accounttype_paymentmethod RENAME to accounttype_paymentmethod_old"); db.execSQL( "CREATE TABLE accounttype_paymentmethod (type text not null check (type in ('CASH','BANK','CCARD','ASSET','LIABILITY')), method_id integer references paymentmethods (_id), primary key (type,method_id));"); db.execSQL( "INSERT INTO accounttype_paymentmethod (type,method_id) SELECT type,method_id FROM accounttype_paymentmethod_old"); db.execSQL("DROP TABLE transactions_old"); db.execSQL("DROP TABLE accounts_old"); db.execSQL("DROP TABLE templates_old"); db.execSQL("DROP TABLE categories_old"); db.execSQL("DROP TABLE paymentmethods_old"); db.execSQL("DROP TABLE accounttype_paymentmethod_old"); //Changes to handle //1) Transfer account no longer stored as cat_id but in transfer_account (in transactions and templates) //2) parent_id for categories uses foreign key on itself, hence root categories have null instead of 0 as parent_id //3) catId etc now need to be null instead of 0 //4) transactions payment_method_id renamed to method_id } if (oldVersion < 29) { db.execSQL("ALTER TABLE transactions add column status integer default 0"); } if (oldVersion < 30) { db.execSQL("ALTER TABLE transactions add column parent_id integer references transactions (_id)"); // db.execSQL("CREATE VIEW committed AS SELECT * FROM transactions WHERE status != 2;"); // db.execSQL("CREATE VIEW uncommitted AS SELECT * FROM transactions WHERE status = 2;"); ContentValues initialValues = new ContentValues(); initialValues.put("_id", 0); initialValues.put("parent_id", 0); initialValues.put("label", "__SPLIT_TRANSACTION__"); db.insert("categories", null, initialValues); } if (oldVersion < 31) { //in an alpha version distributed on Google Play, we had SPLIT_CATID as -1 ContentValues initialValues = new ContentValues(); initialValues.put("_id", 0); initialValues.put("parent_id", 0); db.update("categories", initialValues, "_id=-1", null); } if (oldVersion < 32) { db.execSQL("ALTER TABLE accounts add column grouping text not null check (grouping in " + "('NONE','DAY','WEEK','MONTH','YEAR')) default 'NONE'"); } if (oldVersion < 33) { db.execSQL("ALTER TABLE accounts add column usages integer default 0"); db.execSQL( "UPDATE accounts SET usages = (SELECT count(*) FROM transactions WHERE account_id = accounts._id AND parent_id IS null)"); } if (oldVersion < 34) { //fix for https://github.com/mtotschnig/MyExpenses/issues/69 db.execSQL( "UPDATE transactions set date = (SELECT date from transactions parent WHERE parent._id = transactions.parent_id) WHERE parent_id IS NOT null"); } if (oldVersion < 35) { db.execSQL( "ALTER TABLE transactions add column cr_status text not null check (cr_status in ('UNRECONCILED','CLEARED','RECONCILED')) default 'UNRECONCILED'"); } if (oldVersion < 36) { //move payee field in transactions from text to foreign key db.execSQL("ALTER TABLE transactions RENAME to transactions_old"); db.execSQL("CREATE TABLE transactions (" + " _id integer primary key autoincrement," + " comment text, date datetime not null," + " amount integer not null," + " cat_id integer references categories(_id)," + " account_id integer not null references accounts(_id)," + " payee_id integer references payee(_id)," + " transfer_peer integer references transactions(_id)," + " transfer_account integer references accounts(_id)," + " method_id integer references paymentmethods(_id)," + " parent_id integer references transactions(_id)," + " status integer default 0," + " cr_status text not null check (cr_status in ('UNRECONCILED','CLEARED','RECONCILED')) default 'RECONCILED')"); //insert all payees that are stored in transactions, but are not in payee db.execSQL( "INSERT INTO payee (name) SELECT DISTINCT payee FROM transactions_old WHERE payee != '' AND NOT exists (SELECT 1 FROM payee WHERE name=transactions_old.payee)"); db.execSQL("INSERT INTO transactions " + "(_id,comment,date,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,parent_id,status,cr_status) " + "SELECT " + "_id, " + "comment, " + "date, " + "amount, " + "cat_id, " + "account_id, " + "(SELECT _id from payee WHERE name = payee), " + "transfer_peer, " + "transfer_account, " + "method_id," + "parent_id," + "status," + "cr_status " + "FROM transactions_old"); db.execSQL("DROP TABLE transactions_old"); //move payee field in templates from text to foreign key db.execSQL("ALTER TABLE templates RENAME to templates_old"); db.execSQL("CREATE TABLE templates (" + " _id integer primary key autoincrement," + " comment text," + " amount integer not null," + " cat_id integer references categories(_id)," + " account_id integer not null references accounts(_id)," + " payee_id integer references payee(_id)," + " transfer_peer boolean default false," + " transfer_account integer references accounts(_id)," + " method_id integer references paymentmethods(_id)," + " title text not null," + " usages integer default 0," + " unique(account_id,title));"); //insert all payees that are stored in templates, but are not in payee db.execSQL( "INSERT INTO payee (name) SELECT DISTINCT payee FROM templates_old WHERE payee != '' AND NOT exists (SELECT 1 FROM payee WHERE name=templates_old.payee)"); db.execSQL("INSERT INTO templates " + "(_id,comment,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,title,usages) " + "SELECT " + "_id, " + "comment, " + "amount, " + "cat_id, " + "account_id, " + "(SELECT _id from payee WHERE name = payee), " + "transfer_peer, " + "transfer_account, " + "method_id," + "title," + "usages " + "FROM templates_old"); db.execSQL("DROP TABLE templates_old"); db.execSQL("DROP VIEW IF EXISTS committed"); db.execSQL("DROP VIEW IF EXISTS uncommitted"); //for the definition of the view, it is safe to rely on the constants, //since we will not alter the view, but drop it, and recreate it, if needed // String viewTransactions = VIEW_DEFINITION(TABLE_TRANSACTIONS); // db.execSQL("CREATE VIEW transactions_committed " + viewTransactions + " WHERE " + KEY_STATUS + " != " + STATUS_UNCOMMITTED + ";"); // db.execSQL("CREATE VIEW transactions_uncommitted" + viewTransactions + " WHERE " + KEY_STATUS + " = " + STATUS_UNCOMMITTED + ";"); // db.execSQL("CREATE VIEW transactions_all" + viewTransactions); // db.execSQL("CREATE VIEW templates_all" + VIEW_DEFINITION(TABLE_TEMPLATES)); } if (oldVersion < 37) { db.execSQL("ALTER TABLE transactions add column number text"); db.execSQL("ALTER TABLE paymentmethods add column is_numbered boolean default 0"); ContentValues initialValues = new ContentValues(); initialValues.put("is_numbered", true); db.update("paymentmethods", initialValues, "label = ?", new String[] { "CHEQUE" }); } if (oldVersion < 38) { db.execSQL("ALTER TABLE templates add column plan_id integer"); db.execSQL("ALTER TABLE templates add column plan_execution boolean default 0"); } if (oldVersion < 39) { // db.execSQL("CREATE VIEW transactions_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TRANSACTIONS) + " WHERE " + KEY_STATUS + " != " + STATUS_UNCOMMITTED + ";"); // db.execSQL("CREATE VIEW templates_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TEMPLATES)); db.execSQL( "CREATE TABLE currency (_id integer primary key autoincrement, code text unique not null);"); insertCurrencies(db); } if (oldVersion < 40) { //added currency to extended view db.execSQL("DROP VIEW IF EXISTS transactions_extended"); db.execSQL("DROP VIEW IF EXISTS templates_extended"); // db.execSQL("CREATE VIEW transactions_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TRANSACTIONS) + " WHERE " + KEY_STATUS + " != " + STATUS_UNCOMMITTED + ";"); // db.execSQL("CREATE VIEW templates_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TEMPLATES)); } if (oldVersion < 41) { db.execSQL("CREATE TABLE planinstance_transaction " + "(template_id integer references templates(_id), " + "instance_id integer, " + "transaction_id integer references transactions(_id), " + "primary key (instance_id,transaction_id));"); } if (oldVersion < 42) { //migrate date field to unix time stamp (UTC) db.execSQL("ALTER TABLE transactions RENAME to transactions_old"); db.execSQL("CREATE TABLE transactions (" + " _id integer primary key autoincrement," + " comment text, date datetime not null," + " amount integer not null," + " cat_id integer references categories(_id)," + " account_id integer not null references accounts(_id)," + " payee_id integer references payee(_id)," + " transfer_peer integer references transactions(_id)," + " transfer_account integer references accounts(_id)," + " method_id integer references paymentmethods(_id)," + " parent_id integer references transactions(_id)," + " status integer default 0," + " cr_status text not null check (cr_status in ('UNRECONCILED','CLEARED','RECONCILED')) default 'RECONCILED'," + " number text)"); db.execSQL("INSERT INTO transactions " + "(_id,comment,date,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,parent_id,status,cr_status,number) " + "SELECT " + "_id, " + "comment, " + "strftime('%s',date,'utc'), " + "amount, " + "cat_id, " + "account_id, " + "payee_id, " + "transfer_peer, " + "transfer_account, " + "method_id," + "parent_id," + "status," + "cr_status, " + "number " + "FROM transactions_old"); db.execSQL("DROP TABLE transactions_old"); } if (oldVersion < 43) { db.execSQL("UPDATE accounts set currency = 'ZMW' WHERE currency = 'ZMK'"); db.execSQL("UPDATE currency set code = 'ZMW' WHERE code = 'ZMK'"); } if (oldVersion < 44) { //add ON DELETE CASCADE //accounts table sort_key column db.execSQL("ALTER TABLE planinstance_transaction RENAME to planinstance_transaction_old"); db.execSQL("CREATE TABLE planinstance_transaction " + "(template_id integer references templates(_id) ON DELETE CASCADE, " + "instance_id integer, " + "transaction_id integer references transactions(_id) ON DELETE CASCADE, " + "primary key (instance_id,transaction_id));"); db.execSQL("INSERT INTO planinstance_transaction " + "(template_id,instance_id,transaction_id)" + "SELECT " + "template_id,instance_id,transaction_id FROM planinstance_transaction_old"); db.execSQL("DROP TABLE planinstance_transaction_old"); db.execSQL("ALTER TABLE transactions RENAME to transactions_old"); db.execSQL("CREATE TABLE transactions (" + " _id integer primary key autoincrement," + " comment text, date datetime not null," + " amount integer not null," + " cat_id integer references categories(_id)," + " account_id integer not null references accounts(_id) ON DELETE CASCADE," + " payee_id integer references payee(_id)," + " transfer_peer integer references transactions(_id)," + " transfer_account integer references accounts(_id)," + " method_id integer references paymentmethods(_id)," + " parent_id integer references transactions(_id) ON DELETE CASCADE," + " status integer default 0," + " cr_status text not null check (cr_status in ('UNRECONCILED','CLEARED','RECONCILED')) default 'RECONCILED'," + " number text)"); db.execSQL("INSERT INTO transactions " + "(_id,comment,date,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,parent_id,status,cr_status,number) " + "SELECT " + "_id, " + "comment, " + "date, " + "amount, " + "cat_id, " + "account_id, " + "payee_id, " + "transfer_peer, " + "transfer_account, " + "method_id," + "parent_id," + "status," + "cr_status, " + "number " + "FROM transactions_old"); db.execSQL("DROP TABLE transactions_old"); db.execSQL("ALTER TABLE templates RENAME to templates_old"); db.execSQL("CREATE TABLE templates (" + " _id integer primary key autoincrement," + " comment text," + " amount integer not null," + " cat_id integer references categories(_id)," + " account_id integer not null references accounts(_id) ON DELETE CASCADE," + " payee_id integer references payee(_id)," + " transfer_peer boolean default 0," + " transfer_account integer references accounts(_id) ON DELETE CASCADE," + " method_id integer references paymentmethods(_id)," + " title text not null," + " usages integer default 0," + " plan_id integer, " + " plan_execution boolean default 0, " + " unique(account_id,title));"); db.execSQL("INSERT INTO templates " + "(_id,comment,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,title,usages,plan_id,plan_execution) " + "SELECT " + "_id, " + "comment, " + "amount, " + "cat_id, " + "account_id, " + "payee_id, " + "transfer_peer, " + "transfer_account, " + "method_id," + "title," + "usages, " + "plan_id, " + "plan_execution " + "FROM templates_old"); db.execSQL("ALTER TABLE accounts add column sort_key integer"); } if (oldVersion < 45) { db.execSQL("ALTER TABLE accounts add column exclude_from_totals boolean default 0"); //added to extended view db.execSQL("DROP VIEW IF EXISTS transactions_extended"); db.execSQL("DROP VIEW IF EXISTS templates_extended"); // db.execSQL("CREATE VIEW transactions_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TRANSACTIONS) + " WHERE " + KEY_STATUS + " != " + STATUS_UNCOMMITTED + ";"); // db.execSQL("CREATE VIEW templates_extended" + VIEW_DEFINITION_EXTENDED(TABLE_TEMPLATES)); } if (oldVersion < 46) { db.execSQL("ALTER TABLE payee add column name_normalized text"); Cursor c = db.query("payee", new String[] { "_id", "name" }, null, null, null, null, null); if (c != null) { if (c.moveToFirst()) { ContentValues v = new ContentValues(); while (c.getPosition() < c.getCount()) { v.put("name_normalized", Utils.normalize(c.getString(1))); db.update("payee", v, "_id = " + c.getLong(0), null); c.moveToNext(); } } c.close(); } } if (oldVersion < 47) { db.execSQL("ALTER TABLE templates add column uuid text"); db.execSQL(EVENT_CACHE_CREATE); } if (oldVersion < 48) { //added method_label to extended view //do not comment out, since it is needed by the uuid update refreshViews(db); //need to inline to protect against later renames if (oldVersion < 47) { String[] projection = new String[] { "templates._id", "amount", "comment", "cat_id", "CASE WHEN " + " " + "transfer_peer" + " " + " THEN " + " (SELECT " + "label" + " FROM " + "accounts" + " WHERE " + "_id" + " = " + "transfer_account" + ") " + " ELSE " + " CASE WHEN " + " (SELECT " + "parent_id" + " FROM " + "categories" + " WHERE " + "_id" + " = " + "cat_id" + ") " + " THEN " + " (SELECT " + "label" + " FROM " + "categories" + " WHERE " + "_id" + " = " + " (SELECT " + "parent_id" + " FROM " + "categories" + " WHERE " + "_id" + " = " + "cat_id" + ")) " + " || ' : ' || " + " (SELECT " + "label" + " FROM " + "categories" + " WHERE " + "_id" + " = " + "cat_id" + ") " + " ELSE" + " (SELECT " + "label" + " FROM " + "categories" + " WHERE " + "_id" + " = " + "cat_id" + ") " + " END " + " END AS " + "label", "name", "transfer_peer", "transfer_account", "account_id", "method_id", "paymentmethods.label AS method_label", "title", "plan_id", "plan_execution", "uuid", "currency" }; SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); qb.setTables("templates LEFT JOIN payee ON payee_id = payee._id" + " LEFT JOIN accounts ON account_id = accounts._id" + " LEFT JOIN paymentmethods ON method_id = paymentmethods._id"); Cursor c = qb.query(db, projection, null, null, null, null, null); if (c != null) { if (c.moveToFirst()) { ContentValues templateValues = new ContentValues(), eventValues = new ContentValues(); String planCalendarId = MyApplication.getInstance().checkPlanner(); while (c.getPosition() < c.getCount()) { Template t = new Template(c); templateValues.put(DatabaseConstants.KEY_UUID, t.getUuid()); long templateId = c.getLong(c.getColumnIndex("_id")); long planId = c.getLong(c.getColumnIndex("plan_id")); eventValues.put(Events.DESCRIPTION, t.compileDescription(mCtx)); db.update("templates", templateValues, "_id = " + templateId, null); if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.ICE_CREAM_SANDWICH) { try { mCtx.getContentResolver().update(Events.CONTENT_URI, eventValues, Events._ID + "= ? AND " + Events.CALENDAR_ID + " = ?", new String[] { String.valueOf(planId), planCalendarId }); } catch (Exception e) { //fails with IllegalArgumentException on 2.x devices, //since the same uri works for inserting and querying //but also on HUAWEI Y530-U00 with 4.3 //probably SecurityException could arise here } } c.moveToNext(); } } c.close(); } } } if (oldVersion < 49) { //forgotten to drop in previous upgrade db.execSQL("DROP TABLE IF EXISTS templates_old"); } if (oldVersion < 50) { db.execSQL("ALTER TABLE transactions add column picture_id text"); db.execSQL("DROP TABLE IF EXISTS feature_used"); } if (oldVersion < 51) { File pictureDir = Utils.getPictureDir(false); //fallback if not mounted if (pictureDir == null) { pictureDir = new File( Environment.getExternalStorageDirectory().getPath() + "/Android/data/" + MyApplication.getInstance().getPackageName() + "/files", Environment.DIRECTORY_PICTURES); } if (!pictureDir.exists()) { AcraHelper.report(new Exception("Unable to calculate pictureDir during upgrade")); } //if pictureDir does not exist, we use its URI nonetheless, in order to have the data around //for potential trouble handling String prefix = Uri.fromFile(pictureDir).toString() + "/"; String postfix = ".jpg"; //if picture_id concat expression will also be null db.execSQL("UPDATE transactions set picture_id = '" + prefix + "'||picture_id||'" + postfix + "'"); db.execSQL("CREATE TABLE stale_uris ( picture_id text);"); db.execSQL( "CREATE TRIGGER cache_stale_uri BEFORE DELETE ON transactions WHEN old.picture_id NOT NULL " + " BEGIN INSERT INTO stale_uris VALUES (old.picture_id); END"); } if (oldVersion < 52) { db.execSQL("CREATE INDEX transactions_cat_id_index on transactions(cat_id)"); db.execSQL("CREATE INDEX templates_cat_id_index on templates(cat_id)"); } if (oldVersion < 53) { //add VOID status db.execSQL("ALTER TABLE transactions RENAME to transactions_old"); db.execSQL("CREATE TABLE " + "transactions" + "( " + "_id" + " integer primary key autoincrement, " + "comment" + " text, " + "date" + " datetime not null, " + "amount" + " integer not null, " + "cat_id" + " integer references " + "categories" + "(" + "_id" + "), " + "account_id" + " integer not null references " + "accounts" + "(" + "_id" + ") ON DELETE CASCADE," + "payee_id" + " integer references " + "payee" + "(" + "_id" + "), " + "transfer_peer" + " integer references " + "transactions" + "(" + "_id" + "), " + "transfer_account" + " integer references " + "accounts" + "(" + "_id" + ")," + "method_id" + " integer references " + "paymentmethods" + "(" + "_id" + ")," + "parent_id" + " integer references " + "transactions" + "(" + "_id" + ") ON DELETE CASCADE, " + "status" + " integer default 0, " + "cr_status" + " text not null check (" + "cr_status" + " in ('UNRECONCILED','CLEARED','RECONCILED','VOID')) default 'RECONCILED', " + "number" + " text, " + "picture_id" + " text);"); db.execSQL("INSERT INTO transactions " + "(_id,comment,date,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,parent_id,status,cr_status,number,picture_id) " + "SELECT " + "_id, " + "comment, " + "date, " + "amount, " + "cat_id, " + "account_id, " + "payee_id, " + "transfer_peer, " + "transfer_account, " + "method_id," + "parent_id," + "status," + "cr_status, " + "number, " + "picture_id " + "FROM transactions_old"); db.execSQL("DROP TABLE transactions_old"); db.execSQL( "CREATE TRIGGER cache_stale_uri BEFORE DELETE ON transactions WHEN old.picture_id NOT NULL " + " BEGIN INSERT INTO stale_uris VALUES (old.picture_id); END"); db.execSQL("CREATE INDEX transactions_cat_id_index on transactions(cat_id)"); } if (oldVersion < 54) { db.execSQL("DROP TRIGGER cache_stale_uri"); db.execSQL("CREATE TRIGGER cache_stale_uri " + "AFTER DELETE ON " + "transactions" + " " + "WHEN old." + "picture_id" + " NOT NULL " + "AND NOT EXISTS " + "(SELECT 1 FROM " + "transactions" + " " + "WHERE " + "picture_id" + " = old." + "picture_id" + ") " + "BEGIN INSERT INTO " + "stale_uris" + " VALUES (old." + "picture_id" + "); END"); //all Accounts with old default color are updated to the new one db.execSQL(String.format(Locale.US, "UPDATE accounts set color = %d WHERE color = %d", 0xff009688, 0xff99CC00)); } if (oldVersion < 55) { db.execSQL("ALTER TABLE categories add column label_normalized text"); Cursor c = db.query("categories", new String[] { "_id", "label" }, null, null, null, null, null); if (c != null) { if (c.moveToFirst()) { ContentValues v = new ContentValues(); while (c.getPosition() < c.getCount()) { v.put("label_normalized", Utils.normalize(c.getString(1))); db.update("categories", v, "_id = " + c.getLong(0), null); c.moveToNext(); } } c.close(); } } if (oldVersion < 56) { db.execSQL("ALTER TABLE templates add column last_used datetime"); db.execSQL("ALTER TABLE categories add column last_used datetime"); db.execSQL("ALTER TABLE accounts add column last_used datetime"); db.execSQL("CREATE TRIGGER sort_key_default AFTER INSERT ON accounts " + "BEGIN UPDATE accounts SET sort_key = (SELECT coalesce(max(sort_key),0) FROM accounts) + 1 " + "WHERE _id = NEW._id; END"); //The sort key could be set by user in previous versions, now it is handled internally Cursor c = db.query("accounts", new String[] { "_id", "sort_key" }, null, null, null, null, "sort_key ASC"); boolean hasAccountSortKeySet = false; if (c != null) { if (c.moveToFirst()) { ContentValues v = new ContentValues(); while (c.getPosition() < c.getCount()) { v.put("sort_key", c.getPosition() + 1); db.update("accounts", v, "_id = ?", new String[] { c.getString(0) }); if (c.getInt(1) != 0) hasAccountSortKeySet = true; c.moveToNext(); } } c.close(); } String legacy = PrefKey.SORT_ORDER_LEGACY.getString("USAGES"); PrefKey.SORT_ORDER_TEMPLATES.putString(legacy); PrefKey.SORT_ORDER_CATEGORIES.putString(legacy); PrefKey.SORT_ORDER_ACCOUNTS.putString(hasAccountSortKeySet ? "CUSTOM" : legacy); PrefKey.SORT_ORDER_LEGACY.remove(); } } catch (SQLException e) { throw Utils.hasApiLevel(Build.VERSION_CODES.JELLY_BEAN) ? new SQLiteUpgradeFailedException("Database upgrade failed", e) : e; } if (oldVersion < 57) { //fix custom app uris if (ContextCompat.checkSelfPermission(mCtx, Manifest.permission.WRITE_CALENDAR) == PackageManager.PERMISSION_GRANTED) { Cursor c = db.query("templates", new String[] { "_id", "plan_id" }, "plan_id IS NOT null", null, null, null, null); if (c != null) { if (c.moveToFirst()) { while (!c.isAfterLast()) { Plan.updateCustomAppUri(c.getLong(1), Template.buildCustomAppUri(c.getLong(0))); c.moveToNext(); } } c.close(); } } //Drop unique constraint on templates db.execSQL("ALTER TABLE templates RENAME to templates_old"); db.execSQL("CREATE TABLE templates (" + " _id integer primary key autoincrement," + " comment text," + " amount integer not null," + " cat_id integer references categories(_id)," + " account_id integer not null references accounts(_id) ON DELETE CASCADE," + " payee_id integer references payee(_id)," + " transfer_peer boolean default 0," + " transfer_account integer references accounts(_id) ON DELETE CASCADE," + " method_id integer references paymentmethods(_id)," + " title text not null," + " usages integer default 0," + " plan_id integer, " + " plan_execution boolean default 0, " + " uuid text, " + " last_used datetime);"); db.execSQL("INSERT INTO templates " + "(_id,comment,amount,cat_id,account_id,payee_id,transfer_peer,transfer_account,method_id,title,usages,plan_id,plan_execution,uuid,last_used) " + "SELECT " + "_id, " + "comment, " + "amount, " + "cat_id, " + "account_id, " + "payee_id, " + "transfer_peer, " + "transfer_account, " + "method_id," + "title," + "usages, " + "plan_id, " + "plan_execution, uuid, last_used " + "FROM templates_old"); db.execSQL("DROP TABLE templates_old"); //Recreate changed views refreshViews(db); } if (oldVersion < 58) { //cache fraction digits Cursor c = db.rawQuery("SELECT distinct currency from accounts", null); if (c != null) { if (c.moveToFirst()) { while (!c.isAfterLast()) { Money.ensureFractionDigitsAreCached(Utils.getSaveInstance(c.getString(0))); c.moveToNext(); } } c.close(); } } }