List of usage examples for android.database.sqlite SQLiteDatabase beginTransaction
public void beginTransaction()
From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java
/** * Update the ETag and SyncState of a given rowId. There should be exactly one * record for this rowId in thed database (i.e., no conflicts or checkpoints). * //from w w w . java 2 s .c om * @param db * @param tableId * @param rowId * @param rowETag * @param state */ public void updateRowETagAndSyncState(SQLiteDatabase db, String tableId, String rowId, String rowETag, SyncState state) { String whereClause = DataTableColumns.ID + " = ?"; String[] whereArgs = { rowId }; ContentValues cvDataTableVal = new ContentValues(); String sel = "SELECT * FROM " + tableId + " WHERE " + whereClause; String[] selArgs = whereArgs; Cursor cursor = rawQuery(db, sel, selArgs); // There must be only one row in the db if (cursor.getCount() != 1) { throw new IllegalArgumentException( t + ": row id " + rowId + " does not have exactly 1 row in table " + tableId); } cvDataTableVal.put(DataTableColumns.ROW_ETAG, rowETag); cvDataTableVal.put(DataTableColumns.SYNC_STATE, state.name()); boolean dbWithinTransaction = db.inTransaction(); try { if (!dbWithinTransaction) { db.beginTransaction(); } db.update(tableId, cvDataTableVal, whereClause, whereArgs); if (!dbWithinTransaction) { db.setTransactionSuccessful(); } } finally { if (!dbWithinTransaction) { db.endTransaction(); } } }
From source file:com.grazerss.EntryManager.java
private void deleteArticlesFromDb(final SyncJob job, final List<String> articleIdsToDeleteInDatabase) { if (articleIdsToDeleteInDatabase.isEmpty()) { return;//from w w w. j av a 2 s.c om } Timing t2 = new Timing("Delete Articles From Db", ctx); job.setJobDescription("Cleaning up database"); job.target = articleIdsToDeleteInDatabase.size(); job.actual = 0; fireStatusUpdated(); SQLiteDatabase db = databaseHelper.getDb(); final String sql1 = "DELETE FROM " + Entries.TABLE_NAME + " WHERE " + Entries.__ID + "=?;"; final String sql2 = "DELETE FROM " + EntryLabelAssociations.TABLE_NAME + " WHERE " + EntryLabelAssociations.ENTRY_ID + "=?;"; final SQLiteStatement stmt1 = db.compileStatement(sql1); final SQLiteStatement stmt2 = db.compileStatement(sql2); try { // outter loop does the chunking and holds the transaction context while (!articleIdsToDeleteInDatabase.isEmpty()) { db.beginTransaction(); while (!articleIdsToDeleteInDatabase.isEmpty()) { String id = articleIdsToDeleteInDatabase.remove(0); stmt1.bindString(1, id); stmt1.execute(); stmt2.bindString(1, id); stmt2.execute(); job.actual++; if ((job.actual % 10) == 0) { fireStatusUpdated(); } // commit every 35 articles if (job.actual >= 35) { break; } } db.setTransactionSuccessful(); db.endTransaction(); } } finally { stmt1.close(); stmt2.close(); } fireStatusUpdated(); t2.stop(); }
From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java
/** * The deletion filter includes all non-null arguments. If all arguments * (except the db) are null, then all properties are removed. * //from w w w.jav a 2 s .com * @param db * @param tableId * @param partition * @param aspect * @param key */ public void deleteDBTableMetadata(SQLiteDatabase db, String tableId, String partition, String aspect, String key) { 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); } boolean dbWithinTransaction = db.inTransaction(); try { if (!dbWithinTransaction) { db.beginTransaction(); } db.delete(DatabaseConstants.KEY_VALUE_STORE_ACTIVE_TABLE_NAME, b.toString(), selArgs.toArray(new String[selArgs.size()])); if (!dbWithinTransaction) { db.setTransactionSuccessful(); } } finally { if (!dbWithinTransaction) { db.endTransaction(); } } }
From source file:mobisocial.musubi.service.AddressBookUpdateHandler.java
@Override public void onChange(boolean selfChange) { final DatabaseManager dbManager = new DatabaseManager(mContext); if (!dbManager.getIdentitiesManager().hasConnectedAccounts()) { Log.w(TAG, "no connected accounts, skipping friend import"); return;//from w ww .j a v a2 s .c o m } //a new meta contact appears (and the previous ones disappear) if the user merges //or if a new entry is added, we can detect the ones that have changed by //this condition long highestContactIdAlreadySeen = dbManager.getContactDataVersionManager().getMaxContactIdSeen(); //a new data item corresponds with a new contact, but its possible //that a users just adds a new contact method to an existing contact //and we need to detect that long highestDataIdAlreadySeen = dbManager.getContactDataVersionManager().getMaxDataIdSeen(); // BJD -- this didn't end up being faster once all import features were added. /*if (highestContactIdAlreadySeen == -1) { importFullAddressBook(mContext); return; }*/ long now = System.currentTimeMillis(); if (mLastRun + ONCE_PER_PERIOD > now) { //wake up when the period expires if (!mScheduled) { new Handler(mThread.getLooper()).postDelayed(new Runnable() { @Override public void run() { mScheduled = false; dispatchChange(false); } }, ONCE_PER_PERIOD - (now - mLastRun) + 1); } mScheduled = true; //skip this update return; } Log.i(TAG, "waking up to handle contact changes..."); boolean identityAdded = false, profileDataChanged = false; Date start = new Date(); assert (SYNC_EMAIL); String account_type_selection = getAccountSelectionString(); Cursor c = mContext.getContentResolver().query( ContactsContract.Data.CONTENT_URI, new String[] { ContactsContract.Data._ID, ContactsContract.Data.DATA_VERSION, ContactsContract.Data.CONTACT_ID }, "(" + ContactsContract.Data.DATA_VERSION + ">0 OR " + //maybe updated ContactsContract.Data.CONTACT_ID + ">? OR " + //definitely new or merged ContactsContract.Data._ID + ">? " + //definitely added a data item ") AND (" + ContactsContract.RawContacts.ACCOUNT_TYPE + "<>'" + mAccountType + "'" + ") AND (" + NAME_OR_OTHER_SELECTION + account_type_selection + ")", // All known contacts. new String[] { String.valueOf(highestContactIdAlreadySeen), String.valueOf(highestDataIdAlreadySeen) }, null); if (c == null) { Log.e(TAG, "no valid cursor", new Throwable()); mContext.getContentResolver().notifyChange(MusubiService.ADDRESS_BOOK_SCANNED, this); return; } HashMap<Pair<String, String>, MMyAccount> account_mapping = new HashMap<Pair<String, String>, MMyAccount>(); int max_changes = c.getCount(); TLongArrayList raw_data_ids = new TLongArrayList(max_changes); TLongArrayList versions = new TLongArrayList(max_changes); long new_max_data_id = highestDataIdAlreadySeen; long new_max_contact_id = highestContactIdAlreadySeen; TLongHashSet potentially_changed = new TLongHashSet(); try { //the cursor points to a list of raw contact data items that may have changed //the items will include a type specific field that we are interested in updating //it is possible that multiple data item entries mention the same identifier //so we build a list of contacts to update and then perform synchronization //by refreshing given that we know the top level contact id. if (DBG) Log.d(TAG, "Scanning " + c.getCount() + " contacts..."); while (c.moveToNext()) { if (DBG) Log.v(TAG, "check for updates of contact " + c.getLong(0)); long raw_data_id = c.getLong(0); long version = c.getLong(1); long contact_id = c.getLong(2); //if the contact was split or merged, then we get a higher contact id //so if we have a higher id, data version doesnt really matter if (contact_id <= highestContactIdAlreadySeen) { //the data associated with this contact may not be dirty //we just can't do the join against our table because thise //api is implmented over the content provider if (dbManager.getContactDataVersionManager().getVersion(raw_data_id) == version) continue; } else { new_max_contact_id = Math.max(new_max_contact_id, contact_id); } raw_data_ids.add(raw_data_id); versions.add(version); potentially_changed.add(contact_id); new_max_data_id = Math.max(new_max_data_id, raw_data_id); } if (DBG) Log.d(TAG, "Finished iterating over " + c.getCount() + " contacts for " + potentially_changed.size() + " candidates."); } finally { c.close(); } if (potentially_changed.size() == 0) { Log.w(TAG, "possible bug, woke up to update contacts, but no change was detected; there are extra wakes so it could be ok"); } final SQLiteDatabase db = dbManager.getDatabase(); Pattern emailPattern = getEmailPattern(); Pattern numberPattern = getNumberPattern(); //slice it up so we don't use too much system resource on keeping a lot of state in memory int total = potentially_changed.size(); sAddressBookTotal = total; sAddressBookPosition = 0; final TLongArrayList slice_of_changed = new TLongArrayList(BATCH_SIZE); final StringBuilder to_fetch = new StringBuilder(); final HashMap<Pair<String, String>, TLongHashSet> ids_for_account = new HashMap<Pair<String, String>, TLongHashSet>(); final TLongObjectHashMap<String> names = new TLongObjectHashMap<String>(); TLongIterator it = potentially_changed.iterator(); for (int i = 0; i < total && it.hasNext();) { sAddressBookPosition = i; if (BootstrapActivity.isBootstrapped()) { try { Thread.sleep(mSleepTime * SLEEP_SCALE); } catch (InterruptedException e) { } } slice_of_changed.clear(); ids_for_account.clear(); names.clear(); int max = i + BATCH_SIZE; for (; i < max && it.hasNext(); ++i) { slice_of_changed.add(it.next()); } if (DBG) Log.v(TAG, "looking up names "); to_fetch.setLength(0); to_fetch.append(ContactsContract.Contacts._ID + " IN "); SQLClauseHelper.appendArray(to_fetch, slice_of_changed.iterator()); //lookup the fields we care about from a user profile perspective c = mContext.getContentResolver().query(ContactsContract.Contacts.CONTENT_URI, new String[] { ContactsContract.Contacts._ID, ContactsContract.Contacts.DISPLAY_NAME, }, to_fetch.toString(), null, null); try { while (c.moveToNext()) { long id = c.getLong(0); String name = c.getString(1); if (name == null) continue; //reject names that are just the email address or are just a number //the default for android is just to propagate this as the name //if there is no name if (emailPattern.matcher(name).matches() || numberPattern.matcher(name).matches()) continue; names.put(id, name); } } finally { c.close(); } if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.HONEYCOMB) { db.beginTransactionNonExclusive(); } else { db.beginTransaction(); } long before = SystemClock.elapsedRealtime(); SliceUpdater updater = new SliceUpdater(dbManager, slice_of_changed, ids_for_account, names, account_type_selection); long after = SystemClock.elapsedRealtime(); mSleepTime = (mSleepTime + after - before) / 2; slice_of_changed.forEach(updater); profileDataChanged |= updater.profileDataChanged; identityAdded |= updater.identityAdded; db.setTransactionSuccessful(); db.endTransaction(); if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.HONEYCOMB) { db.beginTransactionNonExclusive(); } else { db.beginTransaction(); } //add all detected members to account feed for (Entry<Pair<String, String>, TLongHashSet> e : ids_for_account.entrySet()) { Pair<String, String> k = e.getKey(); TLongHashSet v = e.getValue(); MMyAccount cached_account = account_mapping.get(k); if (cached_account == null) { cached_account = lookupOrCreateAccount(dbManager, k.getValue0(), k.getValue1()); prepareAccountWhitelistFeed(dbManager.getMyAccountManager(), dbManager.getFeedManager(), cached_account); account_mapping.put(k, cached_account); } final MMyAccount account = cached_account; v.forEach(new TLongProcedure() { @Override public boolean execute(long id) { dbManager.getFeedManager().ensureFeedMember(account.feedId_, id); db.yieldIfContendedSafely(75); return true; } }); } db.setTransactionSuccessful(); db.endTransaction(); } sAddressBookTotal = sAddressBookPosition = 0; //TODO: handle deleted //for all android data ids in our table, check if they still exist in the //contacts table, probably in batches of 100 or something. if they don't //null them out. this is annoyingly non-differential. //TODO: adding friend should update accepted feed status, however, //if a crashe happens for whatever reason, then its possible that this may need to //be run for identities which actually exist in the db. so this update code //needs to do the feed accepted status change for all users that were touched //by the profile update process //update the version ids so we can be faster on subsequent runs if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.HONEYCOMB) { db.beginTransactionNonExclusive(); } else { db.beginTransaction(); } int changed_data_rows = raw_data_ids.size(); for (int i = 0; i < changed_data_rows; ++i) { dbManager.getContactDataVersionManager().setVersion(raw_data_ids.get(i), versions.get(i)); } db.setTransactionSuccessful(); db.endTransaction(); dbManager.getContactDataVersionManager().setMaxDataIdSeen(new_max_data_id); dbManager.getContactDataVersionManager().setMaxContactIdSeen(new_max_contact_id); ContentResolver resolver = mContext.getContentResolver(); Date end = new Date(); double time = end.getTime() - start.getTime(); time /= 1000; Log.w(TAG, "update address book " + mChangeCount++ + " took " + time + " seconds"); if (identityAdded) { //wake up the profile push resolver.notifyChange(MusubiService.WHITELIST_APPENDED, this); } if (profileDataChanged) { //refresh the ui... resolver.notifyChange(MusubiService.PRIMARY_CONTENT_CHANGED, this); } if (identityAdded || profileDataChanged) { //update the our musubi address book as needed. String accountName = mContext.getString(R.string.account_name); String accountType = mContext.getString(R.string.account_type); Account account = new Account(accountName, accountType); ContentResolver.requestSync(account, ContactsContract.AUTHORITY, new Bundle()); } dbManager.close(); mLastRun = new Date().getTime(); resolver.notifyChange(MusubiService.ADDRESS_BOOK_SCANNED, this); }
From source file:edu.stanford.mobisocial.dungbeetle.DungBeetleContentProvider.java
/** * Inserts a message locally that has been received from some agent, * typically from a remote device.//from www. ja va 2 s . c om */ @Override public Uri insert(Uri uri, ContentValues values) { ContentResolver resolver = getContext().getContentResolver(); if (DBG) Log.i(TAG, "Inserting at uri: " + uri + ", " + values); final String appId = getCallingActivityId(); if (appId == null) { Log.d(TAG, "No AppId for calling activity. Ignoring query."); return null; } List<String> segs = uri.getPathSegments(); if (match(uri, "feeds", "me")) { if (!appId.equals(SUPER_APP_ID)) { return null; } long objId = mHelper.addToFeed(appId, "friend", values); Uri objUri = DbObject.uriForObj(objId); resolver.notifyChange(Feed.uriForName("me"), null); resolver.notifyChange(Feed.uriForName("friend"), null); resolver.notifyChange(objUri, null); return objUri; } else if (match(uri, "feeds", ".+")) { String feedName = segs.get(1); String type = values.getAsString(DbObject.TYPE); try { JSONObject json = new JSONObject(values.getAsString(DbObject.JSON)); String objHash = null; if (feedName.contains(":")) { String[] parts = feedName.split(":"); feedName = parts[0]; objHash = parts[1]; } if (objHash != null) { json.put(DbObjects.TARGET_HASH, Long.parseLong(objHash)); json.put(DbObjects.TARGET_RELATION, DbRelation.RELATION_PARENT); values.put(DbObject.JSON, json.toString()); } String appAuthority = appId; if (SUPER_APP_ID.equals(appId)) { if (AppObj.TYPE.equals(type)) { if (json.has(AppObj.ANDROID_PACKAGE_NAME)) { appAuthority = json.getString(AppObj.ANDROID_PACKAGE_NAME); } } } long objId = mHelper.addToFeed(appAuthority, feedName, values); Uri objUri = DbObject.uriForObj(objId); resolver.notifyChange(objUri, null); notifyDependencies(mHelper, resolver, segs.get(1)); if (DBG) Log.d(TAG, "just inserted " + values.getAsString(DbObject.JSON)); return objUri; } catch (JSONException e) { return null; } } else if (match(uri, "out")) { try { JSONObject obj = new JSONObject(values.getAsString("json")); long objId = mHelper.addToOutgoing(appId, values.getAsString(DbObject.DESTINATION), values.getAsString(DbObject.TYPE), obj); resolver.notifyChange(Uri.parse(CONTENT_URI + "/out"), null); return DbObject.uriForObj(objId); } catch (JSONException e) { return null; } } else if (match(uri, "contacts")) { if (!appId.equals(SUPER_APP_ID)) { return null; } long id = mHelper.insertContact(values); resolver.notifyChange(Uri.parse(CONTENT_URI + "/contacts"), null); return uriWithId(uri, id); } else if (match(uri, "subscribers")) { // Question: Should this be restricted? // if(!appId.equals(SUPER_APP_ID)) return null; long id = mHelper.insertSubscriber(values); resolver.notifyChange(Uri.parse(CONTENT_URI + "/subscribers"), null); return uriWithId(uri, id); } else if (match(uri, "groups")) { if (!appId.equals(SUPER_APP_ID)) return null; long id = mHelper.insertGroup(values); getContext().getContentResolver().notifyChange(Uri.parse(CONTENT_URI + "/groups"), null); return uriWithId(uri, id); } else if (match(uri, "group_members")) { if (!appId.equals(SUPER_APP_ID)) { return null; } long id = mHelper.insertGroupMember(values); getContext().getContentResolver().notifyChange(Uri.parse(CONTENT_URI + "/group_members"), null); getContext().getContentResolver().notifyChange(Uri.parse(CONTENT_URI + "/group_contacts"), null); return uriWithId(uri, id); } else if (match(uri, "group_invitations")) { if (!appId.equals(SUPER_APP_ID)) { return null; } String groupName = values.getAsString(InviteToGroupObj.GROUP_NAME); Uri dynUpdateUri = Uri.parse(values.getAsString(InviteToGroupObj.DYN_UPDATE_URI)); long gid = values.getAsLong("groupId"); SQLiteDatabase db = mHelper.getWritableDatabase(); mHelper.addToOutgoing(db, appId, values.getAsString(InviteToGroupObj.PARTICIPANTS), InviteToGroupObj.TYPE, InviteToGroupObj.json(groupName, dynUpdateUri)); getContext().getContentResolver().notifyChange(Uri.parse(CONTENT_URI + "/out"), null); return uriWithId(uri, gid); } else if (match(uri, "dynamic_groups")) { if (!appId.equals(SUPER_APP_ID)) { return null; } Uri gUri = Uri.parse(values.getAsString("uri")); GroupProviders.GroupProvider gp = GroupProviders.forUri(gUri); String feedName = gp.feedName(gUri); Maybe<Group> mg = mHelper.groupByFeedName(feedName); long id = -1; try { Group g = mg.get(); id = g.id; } catch (Maybe.NoValError e) { ContentValues cv = new ContentValues(); cv.put(Group.NAME, gp.groupName(gUri)); cv.put(Group.FEED_NAME, feedName); cv.put(Group.DYN_UPDATE_URI, gUri.toString()); String table = DbObject.TABLE; String[] columns = new String[] { DbObject.FEED_NAME }; String selection = DbObject.CHILD_FEED_NAME + " = ?"; String[] selectionArgs = new String[] { feedName }; Cursor parent = mHelper.getReadableDatabase().query(table, columns, selection, selectionArgs, null, null, null); try { if (parent.moveToFirst()) { String parentName = parent.getString(0); table = Group.TABLE; columns = new String[] { Group._ID }; selection = Group.FEED_NAME + " = ?"; selectionArgs = new String[] { parentName }; Cursor parent2 = mHelper.getReadableDatabase().query(table, columns, selection, selectionArgs, null, null, null); try { if (parent2.moveToFirst()) { cv.put(Group.PARENT_FEED_ID, parent2.getLong(0)); } else { Log.e(TAG, "Parent feed found but no id for " + parentName); } } finally { parent2.close(); } } else { Log.w(TAG, "No parent feed for " + feedName); } } finally { parent.close(); } id = mHelper.insertGroup(cv); getContext().getContentResolver().notifyChange(Uri.parse(CONTENT_URI + "/dynamic_groups"), null); getContext().getContentResolver().notifyChange(Uri.parse(CONTENT_URI + "/groups"), null); } return uriWithId(uri, id); } else if (match(uri, "dynamic_group_member")) { if (!appId.equals(SUPER_APP_ID)) { return null; } SQLiteDatabase db = mHelper.getWritableDatabase(); db.beginTransaction(); try { ContentValues cv = new ContentValues(); String pubKeyStr = values.getAsString(Contact.PUBLIC_KEY); RSAPublicKey k = RSACrypto.publicKeyFromString(pubKeyStr); String personId = mIdent.personIdForPublicKey(k); if (!personId.equals(mIdent.userPersonId())) { cv.put(Contact.PUBLIC_KEY, values.getAsString(Contact.PUBLIC_KEY)); cv.put(Contact.NAME, values.getAsString(Contact.NAME)); cv.put(Contact.EMAIL, values.getAsString(Contact.EMAIL)); if (values.getAsString(Contact.PICTURE) != null) { cv.put(Contact.PICTURE, values.getAsByteArray(Contact.PICTURE)); } long cid = -1; Contact contact = mHelper.contactForPersonId(personId).otherwise(Contact.NA()); if (contact.id > -1) { cid = contact.id; } else { cid = mHelper.insertContact(db, cv); } if (cid > -1) { ContentValues gv = new ContentValues(); gv.put(GroupMember.GLOBAL_CONTACT_ID, values.getAsString(GroupMember.GLOBAL_CONTACT_ID)); gv.put(GroupMember.GROUP_ID, values.getAsLong(GroupMember.GROUP_ID)); gv.put(GroupMember.CONTACT_ID, cid); mHelper.insertGroupMember(db, gv); getContext().getContentResolver().notifyChange(Uri.parse(CONTENT_URI + "/group_members"), null); getContext().getContentResolver().notifyChange(Uri.parse(CONTENT_URI + "/contacts"), null); getContext().getContentResolver().notifyChange(Uri.parse(CONTENT_URI + "/group_contacts"), null); // Add subscription to this private group feed ContentValues sv = new ContentValues(); sv = new ContentValues(); sv.put(Subscriber.CONTACT_ID, cid); sv.put(Subscriber.FEED_NAME, values.getAsString(Group.FEED_NAME)); mHelper.insertSubscriber(db, sv); ContentValues xv = new ContentValues(); xv.put(Subscriber.CONTACT_ID, cid); xv.put(Subscriber.FEED_NAME, "friend"); mHelper.insertSubscriber(db, xv); getContext().getContentResolver().notifyChange(Uri.parse(CONTENT_URI + "/subscribers"), null); db.setTransactionSuccessful(); } return uriWithId(uri, cid); } else { Log.i(TAG, "Omitting self."); return uriWithId(uri, Contact.MY_ID); } } finally { db.endTransaction(); } } else { Log.e(TAG, "Failed to insert into " + uri); return null; } }
From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java
/** * Call this when the schema on the server has changed w.r.t. the schema on * the device. In this case, we do not know whether the rows on the device * match those on the server.//from w w w . j a v a 2 s.c o m * * <ul> * <li>Reset all 'in_conflict' rows to their original local state (changed or * deleted).</li> * <li>Leave all 'deleted' rows in 'deleted' state.</li> * <li>Leave all 'changed' rows in 'changed' state.</li> * <li>Reset all 'synced' rows to 'new_row' to ensure they are sync'd to the * server.</li> * <li>Reset all 'synced_pending_files' rows to 'new_row' to ensure they are * sync'd to the server.</li> * </ul> * * @param db * @param tableId */ public void changeDataRowsToNewRowState(SQLiteDatabase db, String tableId) { StringBuilder b = new StringBuilder(); // remove server conflicting rows b.setLength(0); b.append("DELETE FROM \"").append(tableId).append("\" WHERE ").append(DataTableColumns.SYNC_STATE) .append(" =? AND ").append(DataTableColumns.CONFLICT_TYPE).append(" IN (?, ?)"); String sqlConflictingServer = b.toString(); //@formatter:off String argsConflictingServer[] = { SyncState.in_conflict.name(), Integer.toString(ConflictType.SERVER_DELETED_OLD_VALUES), Integer.toString(ConflictType.SERVER_UPDATED_UPDATED_VALUES) }; //@formatter:on // update local delete conflicts to deletes b.setLength(0); //@formatter:off b.append("UPDATE \"").append(tableId).append("\" SET ").append(DataTableColumns.SYNC_STATE).append(" =?, ") .append(DataTableColumns.CONFLICT_TYPE).append(" = null WHERE ") .append(DataTableColumns.CONFLICT_TYPE).append(" = ?"); //@formatter:on String sqlConflictingLocalDeleting = b.toString(); //@formatter:off String argsConflictingLocalDeleting[] = { SyncState.deleted.name(), Integer.toString(ConflictType.LOCAL_DELETED_OLD_VALUES) }; //@formatter:on // update local update conflicts to updates String sqlConflictingLocalUpdating = sqlConflictingLocalDeleting; //@formatter:off String argsConflictingLocalUpdating[] = { SyncState.changed.name(), Integer.toString(ConflictType.LOCAL_UPDATED_UPDATED_VALUES) }; //@formatter:on // reset all 'rest' rows to 'insert' b.setLength(0); //@formatter:off b.append("UPDATE \"").append(tableId).append("\" SET ").append(DataTableColumns.SYNC_STATE) .append(" =? WHERE ").append(DataTableColumns.SYNC_STATE).append(" =?"); //@formatter:on String sqlRest = b.toString(); //@formatter:off String argsRest[] = { SyncState.new_row.name(), SyncState.synced.name() }; //@formatter:on String sqlRestPendingFiles = sqlRest; //@formatter:off String argsRestPendingFiles[] = { SyncState.new_row.name(), SyncState.synced_pending_files.name() }; //@formatter:on boolean dbWithinTransaction = db.inTransaction(); try { if (!dbWithinTransaction) { db.beginTransaction(); } db.execSQL(sqlConflictingServer, argsConflictingServer); db.execSQL(sqlConflictingLocalDeleting, argsConflictingLocalDeleting); db.execSQL(sqlConflictingLocalUpdating, argsConflictingLocalUpdating); db.execSQL(sqlRest, argsRest); db.execSQL(sqlRestPendingFiles, argsRestPendingFiles); if (!dbWithinTransaction) { db.setTransactionSuccessful(); } } finally { if (!dbWithinTransaction) { db.endTransaction(); } } }
From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java
private void upsertDataIntoExistingDBTable(SQLiteDatabase db, String tableId, ArrayList<ColumnDefinition> orderedColumns, ContentValues cvValues, boolean shouldUpdate) { String rowId = null;//from w ww.j a va2 s. c o m String whereClause = null; boolean specifiesConflictType = cvValues.containsKey(DataTableColumns.CONFLICT_TYPE); boolean nullConflictType = specifiesConflictType && (cvValues.get(DataTableColumns.CONFLICT_TYPE) == null); String[] whereArgs = new String[specifiesConflictType ? (1 + (nullConflictType ? 0 : 1)) : 1]; boolean update = false; if (cvValues.size() <= 0) { throw new IllegalArgumentException(t + ": No values to add into table " + tableId); } ContentValues cvDataTableVal = new ContentValues(); cvDataTableVal.putAll(cvValues); if (cvDataTableVal.containsKey(DataTableColumns.ID)) { // The user specified a row id; we need to determine whether to // insert or update the record, or to reject the action because // there are either checkpoint records for this row id, or, if // a server conflict is associated with this row, that the // _conflict_type to update was not specified. // // i.e., the tuple (_id, _conflict_type) should be unique. If // we find that there are more than 0 or 1 records matching this // tuple, then we should reject the update request. // // TODO: perhaps we want to allow updates to the local conflict // row if there are no checkpoints on it? I.e., change the // tri-state conflict type to a pair of states (local / remote). // and all local changes are flagged local. Remote only exists // if the server is in conflict. rowId = cvDataTableVal.getAsString(DataTableColumns.ID); if (rowId == null) { throw new IllegalArgumentException(DataTableColumns.ID + ", if specified, cannot be null"); } if (specifiesConflictType) { if (nullConflictType) { whereClause = DataTableColumns.ID + " = ?" + " AND " + DataTableColumns.CONFLICT_TYPE + " IS NULL"; whereArgs[0] = rowId; } else { whereClause = DataTableColumns.ID + " = ?" + " AND " + DataTableColumns.CONFLICT_TYPE + " = ?"; whereArgs[0] = rowId; whereArgs[1] = cvValues.getAsString(DataTableColumns.CONFLICT_TYPE); } } else { whereClause = DataTableColumns.ID + " = ?"; whereArgs[0] = rowId; } String sel = "SELECT * FROM " + tableId + " WHERE " + whereClause; String[] selArgs = whereArgs; Cursor cursor = rawQuery(db, sel, selArgs); // There must be only one row in the db for the update to work if (shouldUpdate) { if (cursor.getCount() == 1) { update = true; } else if (cursor.getCount() > 1) { throw new IllegalArgumentException( t + ": row id " + rowId + " has more than 1 row in table " + tableId); } } else { if (cursor.getCount() > 0) { throw new IllegalArgumentException( t + ": id " + rowId + " is already present in table " + tableId); } } } else { rowId = "uuid:" + UUID.randomUUID().toString(); } // TODO: This is broken w.r.t. updates of partial fields // TODO: This is broken w.r.t. updates of partial fields // TODO: This is broken w.r.t. updates of partial fields // TODO: This is broken w.r.t. updates of partial fields if (!cvDataTableVal.containsKey(DataTableColumns.ID)) { cvDataTableVal.put(DataTableColumns.ID, rowId); } if (update) { if (!cvDataTableVal.containsKey(DataTableColumns.SYNC_STATE) || (cvDataTableVal.get(DataTableColumns.SYNC_STATE) == null)) { cvDataTableVal.put(DataTableColumns.SYNC_STATE, SyncState.changed.name()); } if (cvDataTableVal.containsKey(DataTableColumns.LOCALE) && (cvDataTableVal.get(DataTableColumns.LOCALE) == null)) { cvDataTableVal.put(DataTableColumns.LOCALE, DataTableColumns.DEFAULT_LOCALE); } if (cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_TYPE) && (cvDataTableVal.get(DataTableColumns.SAVEPOINT_TYPE) == null)) { cvDataTableVal.put(DataTableColumns.SAVEPOINT_TYPE, SavepointTypeManipulator.complete()); } if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_TIMESTAMP) || cvDataTableVal.get(DataTableColumns.SAVEPOINT_TIMESTAMP) == null) { String timeStamp = TableConstants.nanoSecondsFromMillis(System.currentTimeMillis()); cvDataTableVal.put(DataTableColumns.SAVEPOINT_TIMESTAMP, timeStamp); } if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_CREATOR) || (cvDataTableVal.get(DataTableColumns.SAVEPOINT_CREATOR) == null)) { cvDataTableVal.put(DataTableColumns.SAVEPOINT_CREATOR, DataTableColumns.DEFAULT_SAVEPOINT_CREATOR); } } else { if (!cvDataTableVal.containsKey(DataTableColumns.ROW_ETAG) || cvDataTableVal.get(DataTableColumns.ROW_ETAG) == null) { cvDataTableVal.put(DataTableColumns.ROW_ETAG, DataTableColumns.DEFAULT_ROW_ETAG); } if (!cvDataTableVal.containsKey(DataTableColumns.SYNC_STATE) || (cvDataTableVal.get(DataTableColumns.SYNC_STATE) == null)) { cvDataTableVal.put(DataTableColumns.SYNC_STATE, SyncState.new_row.name()); } if (!cvDataTableVal.containsKey(DataTableColumns.CONFLICT_TYPE)) { cvDataTableVal.putNull(DataTableColumns.CONFLICT_TYPE); } if (!cvDataTableVal.containsKey(DataTableColumns.FILTER_TYPE) || (cvDataTableVal.get(DataTableColumns.FILTER_TYPE) == null)) { cvDataTableVal.put(DataTableColumns.FILTER_TYPE, DataTableColumns.DEFAULT_FILTER_TYPE); } if (!cvDataTableVal.containsKey(DataTableColumns.FILTER_VALUE) || (cvDataTableVal.get(DataTableColumns.FILTER_VALUE) == null)) { cvDataTableVal.put(DataTableColumns.FILTER_VALUE, DataTableColumns.DEFAULT_FILTER_VALUE); } if (!cvDataTableVal.containsKey(DataTableColumns.FORM_ID)) { cvDataTableVal.putNull(DataTableColumns.FORM_ID); } if (!cvDataTableVal.containsKey(DataTableColumns.LOCALE) || (cvDataTableVal.get(DataTableColumns.LOCALE) == null)) { cvDataTableVal.put(DataTableColumns.LOCALE, DataTableColumns.DEFAULT_LOCALE); } if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_TYPE) || (cvDataTableVal.get(DataTableColumns.SAVEPOINT_TYPE) == null)) { cvDataTableVal.put(DataTableColumns.SAVEPOINT_TYPE, SavepointTypeManipulator.complete()); } if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_TIMESTAMP) || cvDataTableVal.get(DataTableColumns.SAVEPOINT_TIMESTAMP) == null) { String timeStamp = TableConstants.nanoSecondsFromMillis(System.currentTimeMillis()); cvDataTableVal.put(DataTableColumns.SAVEPOINT_TIMESTAMP, timeStamp); } if (!cvDataTableVal.containsKey(DataTableColumns.SAVEPOINT_CREATOR) || (cvDataTableVal.get(DataTableColumns.SAVEPOINT_CREATOR) == null)) { cvDataTableVal.put(DataTableColumns.SAVEPOINT_CREATOR, DataTableColumns.DEFAULT_SAVEPOINT_CREATOR); } } cleanUpValuesMap(orderedColumns, cvDataTableVal); boolean dbWithinTransaction = db.inTransaction(); try { if (!dbWithinTransaction) { db.beginTransaction(); } if (update) { db.update(tableId, cvDataTableVal, whereClause, whereArgs); } else { db.insertOrThrow(tableId, null, cvDataTableVal); } if (!dbWithinTransaction) { db.setTransactionSuccessful(); } } finally { if (!dbWithinTransaction) { db.endTransaction(); } } }
From source file:org.opendatakit.common.android.utilities.CsvUtil.java
/** * Update tableId from/*from ww w. ja v a2 s .co m*/ * <ul> * <li>tables/tableId/properties.csv</li> * <li>tables/tableId/definition.csv</li> * </ul> * * This will either create a table, or verify that the table structure matches * that defined in the csv. It will then override all the KVS entries with * those present in the file. * * @param importListener * @param tableId * @throws IOException */ public void updateTablePropertiesFromCsv(ImportListener importListener, String tableId) throws IOException { WebLogger.getLogger(appName).i(TAG, "updateTablePropertiesFromCsv: tableId: " + tableId); SQLiteDatabase db = null; try { db = DatabaseFactory.get().getDatabase(context, appName); List<Column> columns = new ArrayList<Column>(); // reading data File file = null; FileInputStream in = null; InputStreamReader input = null; RFC4180CsvReader cr = null; try { file = new File(ODKFileUtils.getTableDefinitionCsvFile(appName, tableId)); in = new FileInputStream(file); input = new InputStreamReader(in, CharEncoding.UTF_8); cr = new RFC4180CsvReader(input); String[] row; // Read ColumnDefinitions // get the column headers String[] colHeaders = cr.readNext(); int colHeadersLength = countUpToLastNonNullElement(colHeaders); // get the first row row = cr.readNext(); while (row != null && countUpToLastNonNullElement(row) != 0) { String elementKeyStr = null; String elementNameStr = null; String elementTypeStr = null; String listChildElementKeysStr = null; int rowLength = countUpToLastNonNullElement(row); for (int i = 0; i < rowLength; ++i) { if (i >= colHeadersLength) { throw new IllegalStateException("data beyond header row of ColumnDefinitions table"); } if (ColumnDefinitionsColumns.ELEMENT_KEY.equals(colHeaders[i])) { elementKeyStr = row[i]; } if (ColumnDefinitionsColumns.ELEMENT_NAME.equals(colHeaders[i])) { elementNameStr = row[i]; } if (ColumnDefinitionsColumns.ELEMENT_TYPE.equals(colHeaders[i])) { elementTypeStr = row[i]; } if (ColumnDefinitionsColumns.LIST_CHILD_ELEMENT_KEYS.equals(colHeaders[i])) { listChildElementKeysStr = row[i]; } } if (elementKeyStr == null || elementTypeStr == null) { throw new IllegalStateException("ElementKey and ElementType must be specified"); } columns.add(new Column(elementKeyStr, elementNameStr, elementTypeStr, listChildElementKeysStr)); // get next row or blank to end... row = cr.readNext(); } cr.close(); try { input.close(); } catch (IOException e) { } try { in.close(); } catch (IOException e) { } ArrayList<ColumnDefinition> colDefns = ColumnDefinition.buildColumnDefinitions(appName, tableId, columns); Map<String, List<KeyValueStoreEntry>> colEntries = new TreeMap<String, List<KeyValueStoreEntry>>(); file = new File(ODKFileUtils.getTablePropertiesCsvFile(appName, tableId)); in = new FileInputStream(file); input = new InputStreamReader(in, CharEncoding.UTF_8); cr = new RFC4180CsvReader(input); // Read KeyValueStore // read the column headers String[] kvsHeaders = cr.readNext(); int kvsHeadersLength = countUpToLastNonNullElement(kvsHeaders); String displayName = null; List<KeyValueStoreEntry> kvsEntries = new ArrayList<KeyValueStoreEntry>(); // read the first row row = cr.readNext(); while (row != null && countUpToLastNonNullElement(row) != 0) { KeyValueStoreEntry kvsEntry = new KeyValueStoreEntry(); kvsEntry.tableId = tableId; int rowLength = countUpToLastNonNullElement(row); for (int i = 0; i < rowLength; ++i) { if (KeyValueStoreColumns.PARTITION.equals(kvsHeaders[i])) { kvsEntry.partition = row[i]; } if (KeyValueStoreColumns.ASPECT.equals(kvsHeaders[i])) { kvsEntry.aspect = row[i]; } if (KeyValueStoreColumns.KEY.equals(kvsHeaders[i])) { kvsEntry.key = row[i]; } if (KeyValueStoreColumns.VALUE_TYPE.equals(kvsHeaders[i])) { kvsEntry.type = row[i]; } if (KeyValueStoreColumns.VALUE.equals(kvsHeaders[i])) { kvsEntry.value = row[i]; } } if (KeyValueStoreConstants.PARTITION_COLUMN.equals(kvsEntry.partition)) { // column-specific String column = kvsEntry.aspect; List<KeyValueStoreEntry> kvList = colEntries.get(column); if (kvList == null) { kvList = new ArrayList<KeyValueStoreEntry>(); colEntries.put(column, kvList); } try { ColumnDefinition.find(colDefns, column); } catch (IllegalArgumentException e) { throw new IllegalStateException( "Reference to non-existent column: " + column + " of tableId: " + tableId); } kvList.add(kvsEntry); } else { // not column-specific // see if we can find the displayName if (KeyValueStoreConstants.PARTITION_TABLE.equals(kvsEntry.partition) && KeyValueStoreConstants.ASPECT_DEFAULT.equals(kvsEntry.aspect) && KeyValueStoreConstants.TABLE_DISPLAY_NAME.equals(kvsEntry.key)) { displayName = kvsEntry.value; } // still put it in the kvsEntries -- displayName is not stored??? kvsEntries.add(kvsEntry); } // get next row or blank to end... row = cr.readNext(); } cr.close(); try { input.close(); } catch (IOException e) { } try { in.close(); } catch (IOException e) { } if (ODKDatabaseUtils.get().hasTableId(db, tableId)) { ArrayList<ColumnDefinition> existingDefns = TableUtil.get().getColumnDefinitions(db, appName, tableId); // confirm that the column definitions are unchanged... if (existingDefns.size() != colDefns.size()) { throw new IllegalStateException( "Unexpectedly found tableId with different column definitions that already exists!"); } for (ColumnDefinition ci : colDefns) { ColumnDefinition existingDefn; try { existingDefn = ColumnDefinition.find(existingDefns, ci.getElementKey()); } catch (IllegalArgumentException e) { throw new IllegalStateException( "Unexpectedly failed to match elementKey: " + ci.getElementKey()); } if (!existingDefn.getElementName().equals(ci.getElementName())) { throw new IllegalStateException( "Unexpected mis-match of elementName for elementKey: " + ci.getElementKey()); } List<ColumnDefinition> refList = existingDefn.getChildren(); List<ColumnDefinition> ciList = ci.getChildren(); if (refList.size() != ciList.size()) { throw new IllegalStateException( "Unexpected mis-match of listOfStringElementKeys for elementKey: " + ci.getElementKey()); } for (int i = 0; i < ciList.size(); ++i) { if (!refList.contains(ciList.get(i))) { throw new IllegalStateException("Unexpected mis-match of listOfStringElementKeys[" + i + "] for elementKey: " + ci.getElementKey()); } } ElementType type = ci.getType(); ElementType existingType = existingDefn.getType(); if (!existingType.equals(type)) { throw new IllegalStateException( "Unexpected mis-match of elementType for elementKey: " + ci.getElementKey()); } } // OK -- we have matching table definition // now just clear and update the properties... try { db.beginTransaction(); ODKDatabaseUtils.get().replaceDBTableMetadata(db, tableId, kvsEntries, true); for (ColumnDefinition ci : colDefns) { // put the displayName into the KVS List<KeyValueStoreEntry> kvsList = colEntries.get(ci.getElementKey()); if (kvsList == null) { kvsList = new ArrayList<KeyValueStoreEntry>(); colEntries.put(ci.getElementKey(), kvsList); } KeyValueStoreEntry entry = null; for (KeyValueStoreEntry e : kvsList) { if (e.partition.equals(KeyValueStoreConstants.PARTITION_COLUMN) && e.aspect.equals(ci.getElementKey()) && e.key.equals(KeyValueStoreConstants.COLUMN_DISPLAY_NAME)) { entry = e; break; } } if (entry != null && (entry.value == null || entry.value.trim().length() == 0)) { kvsList.remove(entry); entry = null; } if (entry == null) { entry = new KeyValueStoreEntry(); entry.tableId = tableId; entry.partition = KeyValueStoreConstants.PARTITION_COLUMN; entry.aspect = ci.getElementKey(); entry.key = KeyValueStoreConstants.COLUMN_DISPLAY_NAME; entry.type = ElementDataType.object.name(); entry.value = ODKFileUtils.mapper.writeValueAsString(ci.getElementKey()); kvsList.add(entry); } ODKDatabaseUtils.get().replaceDBTableMetadata(db, tableId, kvsList, false); } db.setTransactionSuccessful(); } finally { db.endTransaction(); } } else { for (ColumnDefinition ci : colDefns) { // put the displayName into the KVS if not supplied List<KeyValueStoreEntry> kvsList = colEntries.get(ci.getElementKey()); if (kvsList == null) { kvsList = new ArrayList<KeyValueStoreEntry>(); colEntries.put(ci.getElementKey(), kvsList); } KeyValueStoreEntry entry = null; for (KeyValueStoreEntry e : kvsList) { if (e.partition.equals(KeyValueStoreConstants.PARTITION_COLUMN) && e.aspect.equals(ci.getElementKey()) && e.key.equals(KeyValueStoreConstants.COLUMN_DISPLAY_NAME)) { entry = e; break; } } if (entry != null && (entry.value == null || entry.value.trim().length() == 0)) { kvsList.remove(entry); entry = null; } if (entry == null) { entry = new KeyValueStoreEntry(); entry.tableId = tableId; entry.partition = KeyValueStoreConstants.PARTITION_COLUMN; entry.aspect = ci.getElementKey(); entry.key = KeyValueStoreConstants.COLUMN_DISPLAY_NAME; entry.type = ElementDataType.object.name(); entry.value = ODKFileUtils.mapper.writeValueAsString(ci.getElementKey()); kvsList.add(entry); } } // ensure there is a display name for the table... KeyValueStoreEntry e = new KeyValueStoreEntry(); e.tableId = tableId; e.partition = KeyValueStoreConstants.PARTITION_TABLE; e.aspect = KeyValueStoreConstants.ASPECT_DEFAULT; e.key = KeyValueStoreConstants.TABLE_DISPLAY_NAME; e.type = ElementDataType.object.name(); e.value = NameUtil.normalizeDisplayName( (displayName == null ? NameUtil.constructSimpleDisplayName(tableId) : displayName)); kvsEntries.add(e); try { db.beginTransaction(); ODKDatabaseUtils.get().createOrOpenDBTableWithColumns(db, appName, tableId, columns); ODKDatabaseUtils.get().replaceDBTableMetadata(db, tableId, kvsEntries, false); // we have created the table... for (ColumnDefinition ci : colDefns) { List<KeyValueStoreEntry> kvsList = colEntries.get(ci.getElementKey()); ODKDatabaseUtils.get().replaceDBTableMetadata(db, tableId, kvsList, false); } db.setTransactionSuccessful(); } finally { db.endTransaction(); } } } finally { try { if (input != null) { input.close(); } } catch (IOException e) { } } // And update the inserted properties so that // the known entries have their expected types. try { db.beginTransaction(); ODKDatabaseUtils.get().enforceTypesDBTableMetadata(db, tableId); db.setTransactionSuccessful(); } finally { db.endTransaction(); } } finally { if (db != null) { db.close(); } } }