List of usage examples for android.database.sqlite SQLiteDatabase execSQL
public void execSQL(String sql, Object[] bindArgs) throws SQLException
From source file:ru.orangesoftware.financisto2.db.DatabaseAdapter.java
public void purgeAccountAtDate(Account account, long date) { long nearestTransactionId = findNearestOlderTransactionId(account, date); if (nearestTransactionId > 0) { SQLiteDatabase db = db(); db.beginTransaction();//from ww w .j av a 2 s . c om try { Transaction newTransaction = createTransactionFromNearest(account, nearestTransactionId); breakSplitTransactions(account, date); deleteOldTransactions(account, date); insertWithoutUpdatingBalance(newTransaction); db.execSQL(INSERT_RUNNING_BALANCE, new Object[] { account.id, newTransaction.id, newTransaction.dateTime, newTransaction.fromAmount }); db.setTransactionSuccessful(); } finally { db.endTransaction(); } } }
From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java
/** * Update all rows for the given rowId to SavepointType 'INCOMPLETE' and * remove all but the most recent row. When used with a rowId that has * checkpoints, this updates to the most recent checkpoint and removes any * earlier checkpoints, incomplete or complete savepoints. Otherwise, it has * the general effect of resetting the rowId to an INCOMPLETE state. * //from ww w . j ava 2s.c o m * @param db * @param tableId * @param rowId */ public void saveAsIncompleteMostRecentCheckpointDataInDBTableWithId(SQLiteDatabase db, String tableId, String rowId) { boolean dbWithinTransaction = db.inTransaction(); try { if (!dbWithinTransaction) { db.beginTransaction(); } db.execSQL( "UPDATE \"" + tableId + "\" SET " + DataTableColumns.SAVEPOINT_TYPE + "= ? WHERE " + DataTableColumns.ID + "=?", new String[] { SavepointTypeManipulator.incomplete(), rowId }); db.delete(tableId, DataTableColumns.ID + "=? AND " + DataTableColumns.SAVEPOINT_TIMESTAMP + " NOT IN (SELECT MAX(" + DataTableColumns.SAVEPOINT_TIMESTAMP + ") FROM \"" + tableId + "\" WHERE " + DataTableColumns.ID + "=?)", new String[] { rowId, rowId }); if (!dbWithinTransaction) { db.setTransactionSuccessful(); } } finally { if (!dbWithinTransaction) { db.endTransaction(); } } }
From source file:ru.orangesoftware.financisto2.db.DatabaseAdapter.java
public long[] storeMissedSchedules(List<RestoredTransaction> restored, long now) { SQLiteDatabase db = db(); db.beginTransaction();// ww w. ja v a2 s . c o m try { int count = restored.size(); long[] restoredIds = new long[count]; HashMap<Long, Transaction> transactions = new HashMap<Long, Transaction>(); for (int i = 0; i < count; i++) { RestoredTransaction rt = restored.get(i); long transactionId = rt.transactionId; Transaction t = transactions.get(transactionId); if (t == null) { t = getTransaction(transactionId); transactions.put(transactionId, t); } t.id = -1; t.dateTime = rt.dateTime.getTime(); t.status = TransactionStatus.RS; t.isTemplate = 0; restoredIds[i] = insertOrUpdate(t); t.id = transactionId; } for (Transaction t : transactions.values()) { db.execSQL(UPDATE_LAST_RECURRENCE, new Object[] { now, t.id }); } db.setTransactionSuccessful(); return restoredIds; } finally { db.endTransaction(); } }
From source file:org.opendatakit.common.android.utilities.ODKDatabaseUtils.java
/** * Clean up the KVS row data types. This simplifies the migration process by * enforcing the proper data types regardless of what the values are in the * imported CSV files./*from ww w . ja v a 2 s . c om*/ * * @param db * @param tableId */ public void enforceTypesDBTableMetadata(SQLiteDatabase db, String tableId) { boolean dbWithinTransaction = db.inTransaction(); try { if (!dbWithinTransaction) { db.beginTransaction(); } StringBuilder b = new StringBuilder(); b.setLength(0); //@formatter:off b.append("UPDATE \"").append(DatabaseConstants.KEY_VALUE_STORE_ACTIVE_TABLE_NAME).append("\" SET ") .append(KeyValueStoreColumns.VALUE_TYPE).append("=? WHERE ") .append(KeyValueStoreColumns.PARTITION).append("=? AND ").append(KeyValueStoreColumns.KEY) .append("=?"); //@formatter:on String sql = b.toString(); String[] fields = new String[3]; // for columns fields[0] = ElementDataType.array.name(); fields[1] = KeyValueStoreConstants.PARTITION_COLUMN; fields[2] = KeyValueStoreConstants.COLUMN_DISPLAY_CHOICES_LIST; db.execSQL(sql, fields); fields[0] = ElementDataType.string.name(); fields[1] = KeyValueStoreConstants.PARTITION_COLUMN; fields[2] = KeyValueStoreConstants.COLUMN_DISPLAY_FORMAT; db.execSQL(sql, fields); fields[0] = ElementDataType.object.name(); fields[1] = KeyValueStoreConstants.PARTITION_COLUMN; fields[2] = KeyValueStoreConstants.COLUMN_DISPLAY_NAME; db.execSQL(sql, fields); fields[0] = ElementDataType.bool.name(); fields[1] = KeyValueStoreConstants.PARTITION_COLUMN; fields[2] = KeyValueStoreConstants.COLUMN_DISPLAY_VISIBLE; db.execSQL(sql, fields); fields[0] = ElementDataType.array.name(); fields[1] = KeyValueStoreConstants.PARTITION_COLUMN; fields[2] = KeyValueStoreConstants.COLUMN_JOINS; db.execSQL(sql, fields); // and for the table... fields[0] = ElementDataType.array.name(); fields[1] = KeyValueStoreConstants.PARTITION_TABLE; fields[2] = KeyValueStoreConstants.TABLE_COL_ORDER; db.execSQL(sql, fields); fields[0] = ElementDataType.object.name(); fields[1] = KeyValueStoreConstants.PARTITION_TABLE; fields[2] = KeyValueStoreConstants.TABLE_DISPLAY_NAME; db.execSQL(sql, fields); fields[0] = ElementDataType.array.name(); fields[1] = KeyValueStoreConstants.PARTITION_TABLE; fields[2] = KeyValueStoreConstants.TABLE_GROUP_BY_COLS; db.execSQL(sql, fields); fields[0] = ElementDataType.string.name(); fields[1] = KeyValueStoreConstants.PARTITION_TABLE; fields[2] = KeyValueStoreConstants.TABLE_INDEX_COL; db.execSQL(sql, fields); fields[0] = ElementDataType.object.name(); fields[1] = KeyValueStoreConstants.PARTITION_TABLE; fields[2] = KeyValueStoreConstants.TABLE_SORT_COL; db.execSQL(sql, fields); fields[0] = ElementDataType.object.name(); fields[1] = KeyValueStoreConstants.PARTITION_TABLE; fields[2] = KeyValueStoreConstants.TABLE_SORT_ORDER; db.execSQL(sql, fields); // TODO: color rule groups if (!dbWithinTransaction) { db.setTransactionSuccessful(); } } finally { if (!dbWithinTransaction) { db.endTransaction(); } } }
From source file:org.opendatakit.common.android.provider.impl.InstanceProviderImpl.java
@Override public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) { List<String> segments = uri.getPathSegments(); if (segments.size() < 2 || segments.size() > 3) { throw new SQLException("Unknown URI (too many segments!) " + uri); }//from w ww . j a va2s . c o m String appName = segments.get(0); ODKFileUtils.verifyExternalStorageAvailability(); ODKFileUtils.assertDirectoryStructure(appName); String tableId = segments.get(1); String instanceName = null; // _ID in UPLOADS_TABLE_NAME String instanceId = (segments.size() == 3 ? segments.get(2) : null); SQLiteDatabase db = null; String fullQuery; String filterArgs[]; Cursor c = null; String dbTableName; List<ColumnDefinition> orderedDefns; StringBuilder b = new StringBuilder(); try { db = DatabaseFactory.get().getDatabase(getContext(), appName); db.beginTransaction(); boolean success = false; try { success = ODKDatabaseUtils.get().hasTableId(db, tableId); } catch (Exception e) { e.printStackTrace(); throw new SQLException("Unknown URI (exception testing for tableId) " + uri); } if (!success) { throw new SQLException("Unknown URI (missing data table for tableId) " + uri); } dbTableName = "\"" + tableId + "\""; try { c = db.query(DatabaseConstants.KEY_VALUE_STORE_ACTIVE_TABLE_NAME, new String[] { KeyValueStoreColumns.VALUE }, KeyValueStoreColumns.TABLE_ID + "=? AND " + KeyValueStoreColumns.PARTITION + "=? AND " + KeyValueStoreColumns.ASPECT + "=? AND " + KeyValueStoreColumns.KEY + "=?", new String[] { tableId, KeyValueStoreConstants.PARTITION_TABLE, KeyValueStoreConstants.ASPECT_DEFAULT, KeyValueStoreConstants.XML_INSTANCE_NAME }, null, null, null); if (c.getCount() == 1) { c.moveToFirst(); int idxInstanceName = c.getColumnIndex(KeyValueStoreColumns.VALUE); instanceName = c.getString(idxInstanceName); } } finally { c.close(); } // ARGH! we must ensure that we have records in our UPLOADS_TABLE_NAME // for every distinct instance in the data table. b.setLength(0); //@formatter:off b.append("INSERT INTO ").append(DatabaseConstants.UPLOADS_TABLE_NAME).append("(") .append(InstanceColumns.DATA_INSTANCE_ID).append(",") .append(InstanceColumns.DATA_TABLE_TABLE_ID).append(") ").append("SELECT ") .append(InstanceColumns.DATA_INSTANCE_ID).append(",") .append(InstanceColumns.DATA_TABLE_TABLE_ID).append(" FROM (").append("SELECT DISTINCT ") .append(DATA_TABLE_ID_COLUMN).append(" as ").append(InstanceColumns.DATA_INSTANCE_ID) .append(",").append("? as ").append(InstanceColumns.DATA_TABLE_TABLE_ID).append(" FROM ") .append(dbTableName).append(" EXCEPT SELECT DISTINCT ").append(InstanceColumns.DATA_INSTANCE_ID) .append(",").append(InstanceColumns.DATA_TABLE_TABLE_ID).append(" FROM ") .append(DatabaseConstants.UPLOADS_TABLE_NAME).append(")"); //@formatter:on // TODO: should we collapse across FORM_ID or leave it this way? String[] args = { tableId }; db.execSQL(b.toString(), args); // Can't get away with dataTable.* because of collision with _ID column // get map of (elementKey -> ColumnDefinition) try { orderedDefns = TableUtil.get().getColumnDefinitions(db, appName, tableId); } catch (IllegalArgumentException e) { e.printStackTrace(); throw new SQLException("Unable to retrieve column definitions for tableId " + tableId); } db.setTransactionSuccessful(); } finally { if (db != null) { db.endTransaction(); db.close(); } } //////////////////////////////////////////////////////////////// // OK we have the info we need -- now build the query we want... // We can now join through and access the data table rows b.setLength(0); // @formatter:off b.append("SELECT "); b.append(DatabaseConstants.UPLOADS_TABLE_NAME).append(".").append(InstanceColumns._ID).append(" as ") .append(InstanceColumns._ID).append(",").append(DatabaseConstants.UPLOADS_TABLE_NAME).append(".") .append(InstanceColumns.DATA_INSTANCE_ID).append(" as ").append(InstanceColumns.DATA_INSTANCE_ID) .append(",").append(DatabaseConstants.UPLOADS_TABLE_NAME).append(".") .append(InstanceColumns.SUBMISSION_INSTANCE_ID).append(" as ") .append(InstanceColumns.SUBMISSION_INSTANCE_ID).append(","); // add the dataTable metadata except for _ID (which conflicts with InstanceColumns._ID) b.append(dbTableName).append(".").append(DataTableColumns.ROW_ETAG).append(" as ") .append(DataTableColumns.ROW_ETAG).append(",").append(dbTableName).append(".") .append(DataTableColumns.SYNC_STATE).append(" as ").append(DataTableColumns.SYNC_STATE).append(",") .append(dbTableName).append(".").append(DataTableColumns.CONFLICT_TYPE).append(" as ") .append(DataTableColumns.CONFLICT_TYPE).append(",").append(dbTableName).append(".") .append(DataTableColumns.FILTER_TYPE).append(" as ").append(DataTableColumns.FILTER_TYPE) .append(",").append(dbTableName).append(".").append(DataTableColumns.FILTER_VALUE).append(" as ") .append(DataTableColumns.FILTER_VALUE).append(",").append(dbTableName).append(".") .append(DataTableColumns.FORM_ID).append(" as ").append(DataTableColumns.FORM_ID).append(",") .append(dbTableName).append(".").append(DataTableColumns.LOCALE).append(" as ") .append(DataTableColumns.LOCALE).append(",").append(dbTableName).append(".") .append(DataTableColumns.SAVEPOINT_TYPE).append(" as ").append(DataTableColumns.SAVEPOINT_TYPE) .append(",").append(dbTableName).append(".").append(DataTableColumns.SAVEPOINT_TIMESTAMP) .append(" as ").append(DataTableColumns.SAVEPOINT_TIMESTAMP).append(",").append(dbTableName) .append(".").append(DataTableColumns.SAVEPOINT_CREATOR).append(" as ") .append(DataTableColumns.SAVEPOINT_CREATOR).append(","); // add the user-specified data fields in this dataTable for (ColumnDefinition cd : orderedDefns) { if (cd.isUnitOfRetention()) { b.append(dbTableName).append(".").append(cd.getElementKey()).append(" as ") .append(cd.getElementKey()).append(","); } } b.append("CASE WHEN ").append(DATA_TABLE_SAVEPOINT_TIMESTAMP_COLUMN).append(" IS NULL THEN null") .append(" WHEN ").append(InstanceColumns.XML_PUBLISH_TIMESTAMP).append(" IS NULL THEN null") .append(" WHEN ").append(DATA_TABLE_SAVEPOINT_TIMESTAMP_COLUMN).append(" > ") .append(InstanceColumns.XML_PUBLISH_TIMESTAMP).append(" THEN null").append(" ELSE ") .append(InstanceColumns.XML_PUBLISH_TIMESTAMP).append(" END as ") .append(InstanceColumns.XML_PUBLISH_TIMESTAMP).append(","); b.append("CASE WHEN ").append(DATA_TABLE_SAVEPOINT_TIMESTAMP_COLUMN).append(" IS NULL THEN null") .append(" WHEN ").append(InstanceColumns.XML_PUBLISH_TIMESTAMP).append(" IS NULL THEN null") .append(" WHEN ").append(DATA_TABLE_SAVEPOINT_TIMESTAMP_COLUMN).append(" > ") .append(InstanceColumns.XML_PUBLISH_TIMESTAMP).append(" THEN null").append(" ELSE ") .append(InstanceColumns.XML_PUBLISH_STATUS).append(" END as ") .append(InstanceColumns.XML_PUBLISH_STATUS).append(","); b.append("CASE WHEN ").append(DATA_TABLE_SAVEPOINT_TIMESTAMP_COLUMN).append(" IS NULL THEN null") .append(" WHEN ").append(InstanceColumns.XML_PUBLISH_TIMESTAMP).append(" IS NULL THEN null") .append(" WHEN ").append(DATA_TABLE_SAVEPOINT_TIMESTAMP_COLUMN).append(" > ") .append(InstanceColumns.XML_PUBLISH_TIMESTAMP).append(" THEN null").append(" ELSE ") .append(InstanceColumns.DISPLAY_SUBTEXT).append(" END as ").append(InstanceColumns.DISPLAY_SUBTEXT) .append(","); if (instanceName == null) { b.append(DataTableColumns.SAVEPOINT_TIMESTAMP); } else { b.append(instanceName); } b.append(" as ").append(InstanceColumns.DISPLAY_NAME); b.append(" FROM "); b.append("( SELECT * FROM ").append(dbTableName).append(" AS T WHERE T.") .append(DATA_TABLE_SAVEPOINT_TIMESTAMP_COLUMN).append("=(SELECT MAX(V.") .append(DATA_TABLE_SAVEPOINT_TIMESTAMP_COLUMN).append(") FROM ").append(dbTableName) .append(" AS V WHERE V.").append(DATA_TABLE_ID_COLUMN).append("=T.").append(DATA_TABLE_ID_COLUMN) .append(" AND V.").append(DATA_TABLE_SAVEPOINT_TYPE_COLUMN).append(" IS NOT NULL").append(")") .append(") as ").append(dbTableName); b.append(" JOIN ").append(DatabaseConstants.UPLOADS_TABLE_NAME).append(" ON ").append(dbTableName) .append(".").append(DATA_TABLE_ID_COLUMN).append("=").append(DatabaseConstants.UPLOADS_TABLE_NAME) .append(".").append(InstanceColumns.DATA_INSTANCE_ID).append(" AND ").append("? =") .append(DatabaseConstants.UPLOADS_TABLE_NAME).append(".") .append(InstanceColumns.DATA_TABLE_TABLE_ID); b.append(" WHERE ").append(DATA_TABLE_SAVEPOINT_TYPE_COLUMN).append("=?"); // @formatter:on if (instanceId != null) { b.append(" AND ").append(DatabaseConstants.UPLOADS_TABLE_NAME).append(".").append(InstanceColumns._ID) .append("=?"); String tempArgs[] = { tableId, InstanceColumns.STATUS_COMPLETE, instanceId }; filterArgs = tempArgs; } else { String tempArgs[] = { tableId, InstanceColumns.STATUS_COMPLETE }; filterArgs = tempArgs; } if (selection != null) { b.append(" AND (").append(selection).append(")"); } if (selectionArgs != null) { String[] tempArgs = new String[filterArgs.length + selectionArgs.length]; for (int i = 0; i < filterArgs.length; ++i) { tempArgs[i] = filterArgs[i]; } for (int i = 0; i < selectionArgs.length; ++i) { tempArgs[filterArgs.length + i] = selectionArgs[i]; } filterArgs = tempArgs; } if (sortOrder != null) { b.append(" ORDER BY ").append(sortOrder); } fullQuery = b.toString(); db = null; boolean success = false; try { db = DatabaseFactory.get().getDatabase(getContext(), appName); c = db.rawQuery(fullQuery, filterArgs); // Tell the cursor what uri to watch, so it knows when its source data // changes c.setNotificationUri(getContext().getContentResolver(), uri); success = true; return c; } finally { if (db != null && !success) { // leave database open for cursor... db.close(); } } }
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 www. j a v a2 s .c om*/ * * <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(); } } }