List of usage examples for android.database.sqlite SQLiteDatabase rawQuery
public Cursor rawQuery(String sql, String[] selectionArgs)
From source file:com.xplink.android.carchecklist.CarCheckListActivity.java
private List<Map> restoreCheckList() { // check getListMem List<Map> tmpList = null; SharedPreferences shared = getSharedPreferences("mysettings", Context.MODE_PRIVATE); String check = "empty"; boolean stateGetList = shared.getBoolean("stateGetList", false); int indexList = shared.getInt("indexList", -1); if (stateGetList) { // Log.i("inCheck_satetGetList", "inCheck_stateGetList"); DBCarCheckList dbList = new DBCarCheckList(this); SQLiteDatabase sqliteList = dbList.getReadableDatabase(); String sqlList = "SELECT * FROM " + dbList.TABLE_NAME; Cursor cursor = sqliteList.rawQuery(sqlList, null); cursor.moveToFirst();// w ww .j ava2 s. com int numRow = cursor.getCount(); String[] idList = new String[numRow]; int i = 0; int id = -1; String username = "empty"; String data = "empty"; while (cursor != null) { idList[i] = cursor.getString(0); i++; if (cursor.isLast()) break; cursor.moveToNext(); } // restore from save list String iD = idList[indexList]; sqlList = "SELECT * FROM " + dbList.TABLE_NAME + " WHERE id=" + iD; Cursor cursor2 = sqliteList.rawQuery(sqlList, null); cursor2.moveToFirst(); ExpandData ex = new ExpandData(getApplicationContext()); tmpList = ex.filterData(cursor2.getString(2)); ex.displayMap(tmpList.get(0), tmpList.get(1)); check = " , data : " + cursor2.getString(2); cursor2.close(); sqliteList.close(); dbList.close(); int[] expand = ex.getPercentAllList(); // IT'S WORK FOR TEST // **************************************************************** PercenPower = expand[0]; PercenEngine = expand[1]; PercenExterior = expand[2]; PercenInterior = expand[3]; PercenDocument = expand[4]; /*CheckPowerTotal = shared.getInt("CheckPowerTotal", 0); CheckEngineTotal = shared.getInt("CheckEngineTotal", 0); CheckExteriorTotal = shared.getInt("CheckExteriorTotal", 0); CheckInteriorTotal = shared.getInt("CheckInteriorTotal", 0); CheckDocumentTotal = shared.getInt("CheckDocumentTotal", 0);*/ // IT'S WORK FOR TEST // **************************************************************** } else { Log.i("inCheck_satetGetList", "out stateGetList"); } // Log.i("stateGetList2", "stateGetList >--->>" + stateGetList + check); // Log.i("indexList", "getIndexList >>>>>>> " + indexList); if (stateGetList) { Editor edit = shared.edit(); edit.clear(); edit.commit(); Checknumcheckbox(); edit.putInt("checknum", Checknum); edit.commit(); } return tmpList; }
From source file:de.vanita5.twittnuker.util.Utils.java
public static boolean isFiltered(final SQLiteDatabase database, final long user_id, final String text_plain, final String text_html, final String source, final long retweeted_by_id, final boolean filter_rts) { if (database == null) return false; if (text_plain == null && text_html == null && user_id <= 0 && source == null) return false; final StringBuilder builder = new StringBuilder(); final List<String> selection_args = new ArrayList<String>(); builder.append("SELECT NULL WHERE"); if (text_plain != null) { selection_args.add(text_plain);//from w w w . j a va2s. com builder.append("(SELECT 1 IN (SELECT ? LIKE '%'||" + Filters.Keywords.TABLE_NAME + "." + Filters.VALUE + "||'%' FROM " + Filters.Keywords.TABLE_NAME + "))"); } if (text_html != null) { if (!selection_args.isEmpty()) { builder.append(" OR "); } selection_args.add(text_html); builder.append("(SELECT 1 IN (SELECT ? LIKE '%<a href=\"%'||" + Filters.Links.TABLE_NAME + "." + Filters.VALUE + "||'%\">%' FROM " + Filters.Links.TABLE_NAME + "))"); } if (user_id > 0) { if (!selection_args.isEmpty()) { builder.append(" OR "); } builder.append("(SELECT " + user_id + " IN (SELECT " + Filters.Users.USER_ID + " FROM " + Filters.Users.TABLE_NAME + "))"); } if (retweeted_by_id > 0) { if (!selection_args.isEmpty()) { builder.append(" OR "); } builder.append("(SELECT " + retweeted_by_id + " IN (SELECT " + Filters.Users.USER_ID + " FROM " + Filters.Users.TABLE_NAME + "))"); } if (source != null) { if (!selection_args.isEmpty()) { builder.append(" OR "); } selection_args.add(source); builder.append("(SELECT 1 IN (SELECT ? LIKE '%>'||" + Filters.Sources.TABLE_NAME + "." + Filters.VALUE + "||'</a>%' FROM " + Filters.Sources.TABLE_NAME + "))"); } final Cursor cur = database.rawQuery(builder.toString(), selection_args.toArray(new String[selection_args.size()])); if (cur == null) return false; try { return cur.getCount() > 0; } finally { cur.close(); } }
From source file:org.getlantern.firetweet.util.Utils.java
public static boolean isFiltered(final SQLiteDatabase database, final long user_id, final String text_plain, final String text_html, final String source, final long retweeted_by_id, final boolean filter_rts) { if (database == null) return false; if (text_plain == null && text_html == null && user_id <= 0 && source == null) return false; final StringBuilder builder = new StringBuilder(); final List<String> selection_args = new ArrayList<>(); builder.append("SELECT NULL WHERE"); if (text_plain != null) { selection_args.add(text_plain);//ww w. j a v a2s. c o m builder.append("(SELECT 1 IN (SELECT ? LIKE '%'||" + Filters.Keywords.TABLE_NAME + "." + Filters.VALUE + "||'%' FROM " + Filters.Keywords.TABLE_NAME + "))"); } if (text_html != null) { if (!selection_args.isEmpty()) { builder.append(" OR "); } selection_args.add(text_html); builder.append("(SELECT 1 IN (SELECT ? LIKE '%<a href=\"%'||" + Filters.Links.TABLE_NAME + "." + Filters.VALUE + "||'%\">%' FROM " + Filters.Links.TABLE_NAME + "))"); } if (user_id > 0) { if (!selection_args.isEmpty()) { builder.append(" OR "); } builder.append("(SELECT ").append(user_id).append(" IN (SELECT ").append(Users.USER_ID).append(" FROM ") .append(Users.TABLE_NAME).append("))"); } if (retweeted_by_id > 0) { if (!selection_args.isEmpty()) { builder.append(" OR "); } builder.append("(SELECT ").append(retweeted_by_id).append(" IN (SELECT ").append(Users.USER_ID) .append(" FROM ").append(Users.TABLE_NAME).append("))"); } if (source != null) { if (!selection_args.isEmpty()) { builder.append(" OR "); } selection_args.add(source); builder.append("(SELECT 1 IN (SELECT ? LIKE '%>'||" + Filters.Sources.TABLE_NAME + "." + Filters.VALUE + "||'</a>%' FROM " + Filters.Sources.TABLE_NAME + "))"); } final Cursor cur = database.rawQuery(builder.toString(), selection_args.toArray(new String[selection_args.size()])); if (cur == null) return false; try { return cur.getCount() > 0; } finally { cur.close(); } }
From source file:org.opendatakit.survey.android.provider.SubmissionProvider.java
/** * The incoming URI is of the form:/* w w w . ja va 2 s .c o m*/ * ..../appName/tableId/instanceId?formId=&formVersion= * * where instanceId is the DataTableColumns._ID */ @SuppressWarnings("unchecked") @Override public ParcelFileDescriptor openFile(Uri uri, String mode) throws FileNotFoundException { final boolean asXml = uri.getAuthority().equalsIgnoreCase(XML_SUBMISSION_AUTHORITY); if (mode != null && !mode.equals("r")) { throw new IllegalArgumentException("Only read access is supported"); } // URI == ..../appName/tableId/instanceId?formId=&formVersion= List<String> segments = uri.getPathSegments(); if (segments.size() != 4) { throw new IllegalArgumentException("Unknown URI (incorrect number of path segments!) " + uri); } final String appName = segments.get(0); ODKFileUtils.verifyExternalStorageAvailability(); ODKFileUtils.assertDirectoryStructure(appName); WebLogger log = WebLogger.getLogger(appName); final String tableId = segments.get(1); final String instanceId = segments.get(2); final String submissionInstanceId = segments.get(3); SQLiteDatabase db = null; try { db = DatabaseFactory.get().getDatabase(getContext(), appName); 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); } final String dbTableName = "\"" + tableId + "\""; // Get the table properties specific to XML submissions String xmlInstanceName = null; String xmlRootElementName = null; String xmlDeviceIdPropertyName = null; String xmlUserIdPropertyName = null; String xmlBase64RsaPublicKey = null; try { Cursor c = null; try { c = db.query(DatabaseConstants.KEY_VALUE_STORE_ACTIVE_TABLE_NAME, new String[] { KeyValueStoreColumns.KEY, KeyValueStoreColumns.VALUE }, KeyValueStoreColumns.TABLE_ID + "=? AND " + KeyValueStoreColumns.PARTITION + "=? AND " + KeyValueStoreColumns.ASPECT + "=? AND " + KeyValueStoreColumns.KEY + " IN (?,?,?,?,?)", new String[] { tableId, KeyValueStoreConstants.PARTITION_TABLE, KeyValueStoreConstants.ASPECT_DEFAULT, KeyValueStoreConstants.XML_INSTANCE_NAME, KeyValueStoreConstants.XML_ROOT_ELEMENT_NAME, KeyValueStoreConstants.XML_DEVICE_ID_PROPERTY_NAME, KeyValueStoreConstants.XML_USER_ID_PROPERTY_NAME, KeyValueStoreConstants.XML_BASE64_RSA_PUBLIC_KEY }, null, null, null); if (c.getCount() > 0) { c.moveToFirst(); int idxKey = c.getColumnIndex(KeyValueStoreColumns.KEY); int idxValue = c.getColumnIndex(KeyValueStoreColumns.VALUE); do { String key = c.getString(idxKey); String value = c.getString(idxValue); if (KeyValueStoreConstants.XML_INSTANCE_NAME.equals(key)) { xmlInstanceName = value; } else if (KeyValueStoreConstants.XML_ROOT_ELEMENT_NAME.equals(key)) { xmlRootElementName = value; } else if (KeyValueStoreConstants.XML_DEVICE_ID_PROPERTY_NAME.equals(key)) { xmlDeviceIdPropertyName = value; } else if (KeyValueStoreConstants.XML_USER_ID_PROPERTY_NAME.equals(key)) { xmlUserIdPropertyName = value; } else if (KeyValueStoreConstants.XML_BASE64_RSA_PUBLIC_KEY.equals(key)) { xmlBase64RsaPublicKey = value; } } while (c.moveToNext()); } } finally { c.close(); c = null; } ArrayList<ColumnDefinition> orderedDefns = TableUtil.get().getColumnDefinitions(db, appName, tableId); // Retrieve the values of the record to be emitted... HashMap<String, Object> values = new HashMap<String, Object>(); // issue query to retrieve the most recent non-checkpoint data record // for the instanceId StringBuilder b = new StringBuilder(); b.append("SELECT * FROM ").append(dbTableName).append(" as T WHERE ").append(DataTableColumns.ID) .append("=?").append(" AND ").append(DataTableColumns.SAVEPOINT_TYPE) .append(" IS NOT NULL AND ").append(DataTableColumns.SAVEPOINT_TIMESTAMP) .append("=(SELECT max(V.").append(DataTableColumns.SAVEPOINT_TIMESTAMP).append(") FROM ") .append(dbTableName).append(" as V WHERE V.").append(DataTableColumns.ID).append("=T.") .append(DataTableColumns.ID).append(" AND V.").append(DataTableColumns.SAVEPOINT_TYPE) .append(" IS NOT NULL").append(")"); String[] selectionArgs = new String[] { instanceId }; FileSet freturn = new FileSet(appName); String datestamp = null; try { c = db.rawQuery(b.toString(), selectionArgs); b.setLength(0); if (c.moveToFirst() && c.getCount() == 1) { String rowETag = null; String filterType = null; String filterValue = null; String formId = null; String locale = null; String savepointType = null; String savepointCreator = null; String savepointTimestamp = null; String instanceName = null; // OK. we have the record -- work through all the terms for (int i = 0; i < c.getColumnCount(); ++i) { ColumnDefinition defn = null; String columnName = c.getColumnName(i); try { defn = ColumnDefinition.find(orderedDefns, columnName); } catch (IllegalArgumentException e) { // ignore... } if (defn != null && !c.isNull(i)) { if (xmlInstanceName != null && defn.getElementName().equals(xmlInstanceName)) { instanceName = ODKDatabaseUtils.get().getIndexAsString(c, i); } // user-defined column ElementType type = defn.getType(); ElementDataType dataType = type.getDataType(); log.i(t, "element type: " + defn.getElementType()); if (dataType == ElementDataType.integer) { Integer value = ODKDatabaseUtils.get().getIndexAsType(c, Integer.class, i); putElementValue(values, defn, value); } else if (dataType == ElementDataType.number) { Double value = ODKDatabaseUtils.get().getIndexAsType(c, Double.class, i); putElementValue(values, defn, value); } else if (dataType == ElementDataType.bool) { Integer tmp = ODKDatabaseUtils.get().getIndexAsType(c, Integer.class, i); Boolean value = tmp == null ? null : (tmp != 0); putElementValue(values, defn, value); } else if (type.getElementType().equals("date")) { String value = ODKDatabaseUtils.get().getIndexAsString(c, i); String jrDatestamp = (value == null) ? null : (new SimpleDateFormat(ISO8601_DATE_ONLY_FORMAT, Locale.ENGLISH)) .format(new Date(TableConstants.milliSecondsFromNanos(value))); putElementValue(values, defn, jrDatestamp); } else if (type.getElementType().equals("dateTime")) { String value = ODKDatabaseUtils.get().getIndexAsString(c, i); String jrDatestamp = (value == null) ? null : (new SimpleDateFormat(ISO8601_DATE_FORMAT, Locale.ENGLISH)) .format(new Date(TableConstants.milliSecondsFromNanos(value))); putElementValue(values, defn, jrDatestamp); } else if (type.getElementType().equals("time")) { String value = ODKDatabaseUtils.get().getIndexAsString(c, i); putElementValue(values, defn, value); } else if (dataType == ElementDataType.array) { ArrayList<Object> al = ODKDatabaseUtils.get().getIndexAsType(c, ArrayList.class, i); putElementValue(values, defn, al); } else if (dataType == ElementDataType.string) { String value = ODKDatabaseUtils.get().getIndexAsString(c, i); putElementValue(values, defn, value); } else /* unrecognized */ { throw new IllegalStateException( "unrecognized data type: " + defn.getElementType()); } } else if (columnName.equals(DataTableColumns.SAVEPOINT_TIMESTAMP)) { savepointTimestamp = ODKDatabaseUtils.get().getIndexAsString(c, i); } else if (columnName.equals(DataTableColumns.ROW_ETAG)) { rowETag = ODKDatabaseUtils.get().getIndexAsString(c, i); } else if (columnName.equals(DataTableColumns.FILTER_TYPE)) { filterType = ODKDatabaseUtils.get().getIndexAsString(c, i); } else if (columnName.equals(DataTableColumns.FILTER_VALUE)) { filterValue = ODKDatabaseUtils.get().getIndexAsString(c, i); } else if (columnName.equals(DataTableColumns.FORM_ID)) { formId = ODKDatabaseUtils.get().getIndexAsString(c, i); } else if (columnName.equals(DataTableColumns.LOCALE)) { locale = ODKDatabaseUtils.get().getIndexAsString(c, i); } else if (columnName.equals(DataTableColumns.FORM_ID)) { formId = ODKDatabaseUtils.get().getIndexAsString(c, i); } else if (columnName.equals(DataTableColumns.SAVEPOINT_TYPE)) { savepointType = ODKDatabaseUtils.get().getIndexAsString(c, i); } else if (columnName.equals(DataTableColumns.SAVEPOINT_CREATOR)) { savepointCreator = ODKDatabaseUtils.get().getIndexAsString(c, i); } } // OK got all the values into the values map -- emit // contents b.setLength(0); File submissionXml = new File(ODKFileUtils.getInstanceFolder(appName, tableId, instanceId), (asXml ? "submission.xml" : "submission.json")); File manifest = new File(ODKFileUtils.getInstanceFolder(appName, tableId, instanceId), "manifest.json"); submissionXml.delete(); manifest.delete(); freturn.instanceFile = submissionXml; if (asXml) { // Pre-processing -- collapse all geopoints into a // string-valued representation for (ColumnDefinition defn : orderedDefns) { ElementType type = defn.getType(); ElementDataType dataType = type.getDataType(); if (dataType == ElementDataType.object && (type.getElementType().equals("geopoint") || type.getElementType().equals("mimeUri"))) { Map<String, Object> parent = null; List<ColumnDefinition> parents = new ArrayList<ColumnDefinition>(); ColumnDefinition d = defn.getParent(); while (d != null) { parents.add(d); d = d.getParent(); } parent = values; for (int i = parents.size() - 1; i >= 0; --i) { Object o = parent.get(parents.get(i).getElementName()); if (o == null) { parent = null; break; } parent = (Map<String, Object>) o; } if (parent != null) { Object o = parent.get(defn.getElementName()); if (o != null) { if (type.getElementType().equals("geopoint")) { Map<String, Object> geopoint = (Map<String, Object>) o; // OK. we have geopoint -- get the // lat, long, alt, etc. Double latitude = (Double) geopoint.get("latitude"); Double longitude = (Double) geopoint.get("longitude"); Double altitude = (Double) geopoint.get("altitude"); Double accuracy = (Double) geopoint.get("accuracy"); String gpt = "" + latitude + " " + longitude + " " + altitude + " " + accuracy; parent.put(defn.getElementName(), gpt); } else if (type.getElementType().equals("mimeUri")) { Map<String, Object> mimeuri = (Map<String, Object>) o; String uriFragment = (String) mimeuri.get("uriFragment"); String contentType = (String) mimeuri.get("contentType"); if (uriFragment != null) { File f = ODKFileUtils.getAsFile(appName, uriFragment); if (f.equals(manifest)) { throw new IllegalStateException( "Unexpected collision with manifest.json"); } freturn.addAttachmentFile(f, contentType); parent.put(defn.getElementName(), f.getName()); } } else { throw new IllegalStateException("Unhandled transform case"); } } } } } datestamp = (new SimpleDateFormat(ISO8601_DATE_FORMAT, Locale.ENGLISH)) .format(new Date(TableConstants.milliSecondsFromNanos(savepointTimestamp))); // For XML, we traverse the map to serialize it Document d = new Document(); d.setStandalone(true); d.setEncoding(CharEncoding.UTF_8); Element e = d.createElement(XML_DEFAULT_NAMESPACE, (xmlRootElementName == null) ? "data" : xmlRootElementName); e.setPrefix("jr", XML_OPENROSA_NAMESPACE); e.setPrefix("", XML_DEFAULT_NAMESPACE); d.addChild(0, Node.ELEMENT, e); e.setAttribute("", "id", tableId); DynamicPropertiesCallback cb = new DynamicPropertiesCallback(getContext(), appName, tableId, instanceId); int idx = 0; Element meta = d.createElement(XML_OPENROSA_NAMESPACE, "meta"); Element v = d.createElement(XML_OPENROSA_NAMESPACE, "instanceID"); v.addChild(0, Node.TEXT, submissionInstanceId); meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); if (xmlDeviceIdPropertyName != null) { String deviceId = propertyManager.getSingularProperty(xmlDeviceIdPropertyName, cb); if (deviceId != null) { v = d.createElement(XML_OPENROSA_NAMESPACE, "deviceID"); v.addChild(0, Node.TEXT, deviceId); meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); } } if (xmlUserIdPropertyName != null) { String userId = propertyManager.getSingularProperty(xmlUserIdPropertyName, cb); if (userId != null) { v = d.createElement(XML_OPENROSA_NAMESPACE, "userID"); v.addChild(0, Node.TEXT, userId); meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); } } v = d.createElement(XML_OPENROSA_NAMESPACE, "timeEnd"); v.addChild(0, Node.TEXT, datestamp); meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); // these are extra metadata tags... if (instanceName != null) { v = d.createElement(XML_DEFAULT_NAMESPACE, "instanceName"); v.addChild(0, Node.TEXT, instanceName); meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); } else { v = d.createElement(XML_DEFAULT_NAMESPACE, "instanceName"); v.addChild(0, Node.TEXT, savepointTimestamp); meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); } // these are extra metadata tags... // rowID v = d.createElement(XML_DEFAULT_NAMESPACE, "rowID"); v.addChild(0, Node.TEXT, instanceId); meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); // rowETag v = d.createElement(XML_DEFAULT_NAMESPACE, "rowETag"); if (rowETag != null) { v.addChild(0, Node.TEXT, rowETag); } meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); // filterType v = d.createElement(XML_DEFAULT_NAMESPACE, "filterType"); if (filterType != null) { v.addChild(0, Node.TEXT, filterType); } meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); // filterValue v = d.createElement(XML_DEFAULT_NAMESPACE, "filterValue"); if (filterValue != null) { v.addChild(0, Node.TEXT, filterValue); } meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); // formID v = d.createElement(XML_DEFAULT_NAMESPACE, "formID"); v.addChild(0, Node.TEXT, formId); meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); // locale v = d.createElement(XML_DEFAULT_NAMESPACE, "locale"); v.addChild(0, Node.TEXT, locale); meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); // savepointType v = d.createElement(XML_DEFAULT_NAMESPACE, "savepointType"); v.addChild(0, Node.TEXT, savepointType); meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); // savepointCreator v = d.createElement(XML_DEFAULT_NAMESPACE, "savepointCreator"); if (savepointCreator != null) { v.addChild(0, Node.TEXT, savepointCreator); } meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); // savepointTimestamp v = d.createElement(XML_DEFAULT_NAMESPACE, "savepointTimestamp"); v.addChild(0, Node.TEXT, savepointTimestamp); meta.addChild(idx++, Node.ELEMENT, v); meta.addChild(idx++, Node.IGNORABLE_WHITESPACE, NEW_LINE); // and insert the meta block into the XML e.addChild(0, Node.IGNORABLE_WHITESPACE, NEW_LINE); e.addChild(1, Node.ELEMENT, meta); e.addChild(2, Node.IGNORABLE_WHITESPACE, NEW_LINE); idx = 3; ArrayList<String> entryNames = new ArrayList<String>(); entryNames.addAll(values.keySet()); Collections.sort(entryNames); for (String name : entryNames) { idx = generateXmlHelper(d, e, idx, name, values, log); } KXmlSerializer serializer = new KXmlSerializer(); ByteArrayOutputStream bo = new ByteArrayOutputStream(); serializer.setOutput(bo, CharEncoding.UTF_8); // setting the response content type emits the // xml header. // just write the body here... d.writeChildren(serializer); serializer.flush(); bo.close(); b.append(bo.toString(CharEncoding.UTF_8)); // OK we have the document in the builder (b). String doc = b.toString(); freturn.instanceFile = submissionXml; // see if the form is encrypted and we can // encrypt it... EncryptedFormInformation formInfo = EncryptionUtils.getEncryptedFormInformation(appName, tableId, xmlBase64RsaPublicKey, instanceId); if (formInfo != null) { File submissionXmlEnc = new File(submissionXml.getParentFile(), submissionXml.getName() + ".enc"); submissionXmlEnc.delete(); // if we are encrypting, the form cannot be // reopened afterward // and encrypt the submission (this is a // one-way operation)... if (!EncryptionUtils.generateEncryptedSubmission(freturn, doc, submissionXml, submissionXmlEnc, formInfo)) { return null; } // at this point, the freturn object has // been re-written with the encrypted media // and xml files. } else { exportFile(doc, submissionXml, log); } } else { // Pre-processing -- collapse all mimeUri into filename for (ColumnDefinition defn : orderedDefns) { ElementType type = defn.getType(); ElementDataType dataType = type.getDataType(); if (dataType == ElementDataType.object && type.getElementType().equals("mimeUri")) { Map<String, Object> parent = null; List<ColumnDefinition> parents = new ArrayList<ColumnDefinition>(); ColumnDefinition d = defn.getParent(); while (d != null) { parents.add(d); d = d.getParent(); } parent = values; for (int i = parents.size() - 1; i >= 0; --i) { Object o = parent.get(parents.get(i).getElementName()); if (o == null) { parent = null; break; } parent = (Map<String, Object>) o; } if (parent != null) { Object o = parent.get(defn.getElementName()); if (o != null) { if (dataType == ElementDataType.object && type.getElementType().equals("mimeUri")) { Map<String, Object> mimeuri = (Map<String, Object>) o; String uriFragment = (String) mimeuri.get("uriFragment"); String contentType = (String) mimeuri.get("contentType"); File f = ODKFileUtils.getAsFile(appName, uriFragment); if (f.equals(manifest)) { throw new IllegalStateException( "Unexpected collision with manifest.json"); } freturn.addAttachmentFile(f, contentType); parent.put(defn.getElementName(), f.getName()); } else { throw new IllegalStateException("Unhandled transform case"); } } } } } // For JSON, we construct the model, then emit model + // meta + data HashMap<String, Object> wrapper = new HashMap<String, Object>(); wrapper.put("tableId", tableId); wrapper.put("instanceId", instanceId); HashMap<String, Object> formDef = new HashMap<String, Object>(); formDef.put("table_id", tableId); formDef.put("model", ColumnDefinition.getDataModel(orderedDefns)); wrapper.put("formDef", formDef); wrapper.put("data", values); wrapper.put("metadata", new HashMap<String, Object>()); HashMap<String, Object> elem = (HashMap<String, Object>) wrapper.get("metadata"); if (instanceName != null) { elem.put("instanceName", instanceName); } elem.put("saved", "COMPLETE"); elem.put("timestamp", datestamp); b.append(ODKFileUtils.mapper.writeValueAsString(wrapper)); // OK we have the document in the builder (b). String doc = b.toString(); exportFile(doc, submissionXml, log); } exportFile(freturn.serializeUriFragmentList(getContext()), manifest, log); return ParcelFileDescriptor.open(manifest, ParcelFileDescriptor.MODE_READ_ONLY); } } finally { if (c != null && !c.isClosed()) { c.close(); } } } catch (JsonParseException e) { e.printStackTrace(); } catch (JsonMappingException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } finally { if (db != null) { db.close(); } } return null; }
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); }/*ww w . j a va 2s . c om*/ 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:edu.stanford.mobisocial.dungbeetle.DBHelper.java
@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion); if (oldVersion <= 23) { Log.w(TAG, "Schema too old to migrate, dropping all."); dropAll(db);/*w w w .j a va 2s . c o m*/ onCreate(db); return; } if (oldVersion <= 24) { Log.w(TAG, "Adding columns 'presence' and 'status' to contact table."); db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.STATUS + " TEXT"); db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.PRESENCE + " INTEGER DEFAULT " + Presence.AVAILABLE); } if (oldVersion <= 25) { Log.w(TAG, "Adding columns 'presence' and 'status' to contact table."); db.execSQL("ALTER TABLE " + Group.TABLE + " ADD COLUMN " + Group.FEED_NAME + " TEXT"); } if (oldVersion <= 26) { Log.w(TAG, "Adding column 'picture' to contact table."); db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.PICTURE + " BLOB"); } if (oldVersion <= 27) { Log.w(TAG, "Adding column 'last_presence_time' to contact table."); db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.LAST_PRESENCE_TIME + " INTEGER DEFAULT 0"); } if (oldVersion <= 28) { Log.w(TAG, "Adding column 'picture' to my_info table."); db.execSQL("ALTER TABLE " + MyInfo.TABLE + " ADD COLUMN " + MyInfo.PICTURE + " BLOB"); } if (oldVersion <= 29) { Log.w(TAG, "Adding column 'version' to group table."); db.execSQL("ALTER TABLE " + Group.TABLE + " ADD COLUMN " + Group.VERSION + " INTEGER DEFAULT -1"); } if (oldVersion <= 30) { Log.w(TAG, "Adding column 'E' to object table."); db.execSQL("ALTER TABLE " + DbObject.TABLE + " ADD COLUMN " + DbObject.ENCODED + " BLOB"); createIndex(db, "INDEX", "objects_by_encoded", DbObject.TABLE, DbObject.ENCODED); } if (oldVersion <= 31) { Log.w(TAG, "Adding column 'child_feed' to object table."); db.execSQL("ALTER TABLE " + DbObject.TABLE + " ADD COLUMN " + DbObject.CHILD_FEED_NAME + " TEXT"); createIndex(db, "INDEX", "child_feeds", DbObject.TABLE, DbObject.CHILD_FEED_NAME); } if (oldVersion <= 32) { // Bug fix. Log.w(TAG, "Updating app state objects."); db.execSQL("UPDATE " + DbObject.TABLE + " SET " + DbObject.CHILD_FEED_NAME + " = NULL WHERE " + DbObject.CHILD_FEED_NAME + " = " + DbObject.FEED_NAME); } if (oldVersion <= 33) { Log.w(TAG, "Adding column 'nearby' to contact table."); db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.NEARBY + " INTEGER DEFAULT 0"); } if (oldVersion <= 34) { Log.w(TAG, "Adding column 'secret' to contact table."); db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.SHARED_SECRET + " BLOB"); } if (oldVersion <= 35) { Log.w(TAG, "Adding column 'last_updated' to group table."); db.execSQL("ALTER TABLE " + Group.TABLE + " ADD COLUMN " + Group.LAST_UPDATED + " INTEGER"); } if (oldVersion <= 36) { // Can't easily drop columns, but 'update_id' and 'is_child_feed' are dead columns. Log.w(TAG, "Adding column 'parent_feed_id' to group table."); db.execSQL( "ALTER TABLE " + Group.TABLE + " ADD COLUMN " + Group.PARENT_FEED_ID + " INTEGER DEFAULT -1"); Log.w(TAG, "Adding column 'last_object_id' to group table."); db.execSQL( "ALTER TABLE " + Group.TABLE + " ADD COLUMN " + Group.LAST_OBJECT_ID + " INTEGER DEFAULT -1"); } if (oldVersion <= 37) { // Can't easily drop columns, but 'update_id' and 'is_child_feed' are dead columns. Log.w(TAG, "Adding column 'num_unread' to group table."); db.execSQL("ALTER TABLE " + Group.TABLE + " ADD COLUMN " + Group.NUM_UNREAD + " INTEGER DEFAULT 0"); } if (oldVersion <= 38) { Log.w(TAG, "Adding column 'raw' to object table."); db.execSQL("ALTER TABLE " + DbObject.TABLE + " ADD COLUMN " + DbObject.RAW + " BLOB"); } // sadly, we have to do this again because incoming voice obj's were not being split! if (oldVersion <= 50) { Log.w(TAG, "Converting voice and picture objs to raw."); Log.w(TAG, "Converting objs to raw."); Cursor c = db.query(DbObject.TABLE, new String[] { DbObject._ID }, DbObject.TYPE + " = ? AND " + DbObject.RAW + " IS NULL", new String[] { PictureObj.TYPE }, null, null, null); ArrayList<Long> ids = new ArrayList<Long>(); if (c.moveToFirst()) do { ids.add(c.getLong(0)); } while (c.moveToNext()); c.close(); DbEntryHandler dbh = DbObjects.forType(PictureObj.TYPE); for (Long id : ids) { c = db.query(DbObject.TABLE, new String[] { DbObject.JSON, DbObject.RAW }, DbObject._ID + " = ? ", new String[] { String.valueOf(id.longValue()) }, null, null, null); if (c.moveToFirst()) try { String json = c.getString(0); byte[] raw = c.getBlob(1); c.close(); if (raw == null) { Pair<JSONObject, byte[]> p = dbh.splitRaw(new JSONObject(json)); if (p != null) { json = p.first.toString(); raw = p.second; updateJsonAndRaw(db, id, json, raw); } } } catch (JSONException e) { } c.close(); } c = db.query(DbObject.TABLE, new String[] { DbObject._ID }, DbObject.TYPE + " = ? AND " + DbObject.RAW + " IS NULL", new String[] { VoiceObj.TYPE }, null, null, null); ids = new ArrayList<Long>(); if (c.moveToFirst()) do { ids.add(c.getLong(0)); } while (c.moveToNext()); c.close(); dbh = DbObjects.forType(VoiceObj.TYPE); for (Long id : ids) { c = db.query(DbObject.TABLE, new String[] { DbObject.JSON, DbObject.RAW }, DbObject._ID + " = ? ", new String[] { String.valueOf(id.longValue()) }, null, null, null); if (c.moveToFirst()) try { String json = c.getString(0); byte[] raw = c.getBlob(1); c.close(); if (raw == null) { Pair<JSONObject, byte[]> p = dbh.splitRaw(new JSONObject(json)); if (p != null) { json = p.first.toString(); raw = p.second; updateJsonAndRaw(db, id, json, raw); } } } catch (JSONException e) { } c.close(); } } if (oldVersion <= 40) { Log.w(TAG, "Adding column 'E' to object table."); db.execSQL("ALTER TABLE " + DbObject.TABLE + " ADD COLUMN " + DbObject.HASH + " INTEGER"); createIndex(db, "INDEX", "objects_by_hash", DbObject.TABLE, DbObject.HASH); db.execSQL("DROP INDEX objects_by_encoded"); db.delete(DbObject.TABLE, DbObject.TYPE + " = ?", new String[] { "profile" }); db.delete(DbObject.TABLE, DbObject.TYPE + " = ?", new String[] { "profilepicture" }); ContentValues cv = new ContentValues(); cv.putNull(DbObject.ENCODED); db.update(DbObject.TABLE, cv, null, null); } if (oldVersion <= 41) { db.execSQL("DROP INDEX objects_by_sequence_id"); db.execSQL("CREATE INDEX objects_by_sequence_id ON " + DbObject.TABLE + "(" + DbObject.CONTACT_ID + ", " + DbObject.FEED_NAME + ", " + DbObject.SEQUENCE_ID + ")"); } //secret to life, etc if (oldVersion <= 42) { db.execSQL("DROP INDEX objects_by_creator_id"); db.execSQL("CREATE INDEX objects_by_creator_id ON " + DbObject.TABLE + "(" + DbObject.CONTACT_ID + ", " + DbObject.SENT + ")"); } if (oldVersion <= 44) { // oops. db.execSQL("DROP TABLE IF EXISTS " + DbRelation.TABLE); createRelationBaseTable(db); } if (oldVersion <= 45) { db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.LAST_OBJECT_ID + " INTEGER"); db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.LAST_UPDATED + " INTEGER"); db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.NUM_UNREAD + " INTEGER DEFAULT 0"); } if (oldVersion <= 46) { db.execSQL("ALTER TABLE " + DbObject.TABLE + " ADD COLUMN " + DbObject.DELETED + " INTEGER DEFAULT 0"); } if (oldVersion <= 47) { addRelationIndexes(db); } if (oldVersion <= 44) { createUserAttributesTable(db); } if (oldVersion <= 49) { if (oldVersion > 44) { db.execSQL("ALTER TABLE " + DbRelation.TABLE + " ADD COLUMN " + DbRelation.RELATION_TYPE + " TEXT"); createIndex(db, "INDEX", "relations_by_type", DbRelation.TABLE, DbRelation.RELATION_TYPE); } db.execSQL("UPDATE " + DbRelation.TABLE + " SET " + DbRelation.RELATION_TYPE + " = 'parent'"); } if (oldVersion <= 52) { Log.w(TAG, "Adding column 'about' to my_info table."); try { db.execSQL("ALTER TABLE " + MyInfo.TABLE + " ADD COLUMN " + MyInfo.ABOUT + " TEXT DEFAULT ''"); } catch (Exception e) { // because of bad update, we just ignore the duplicate column error } } if (oldVersion <= 53) { db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.HIDDEN + " INTEGER DEFAULT 0"); } if (oldVersion <= 55) { db.execSQL("ALTER TABLE " + DbObj.TABLE + " ADD COLUMN " + DbObj.COL_KEY_INT + " INTEGER"); } if (oldVersion <= 56) { db.execSQL("DROP INDEX attrs_by_contact_id"); createIndex(db, "INDEX", "attrs_by_contact_id", DbContactAttributes.TABLE, DbContactAttributes.CONTACT_ID); } if (oldVersion <= 57) { db.execSQL("ALTER TABLE " + DbObject.TABLE + " ADD COLUMN " + DbObject.LAST_MODIFIED_TIMESTAMP + " INTEGER"); db.execSQL("UPDATE " + DbObject.TABLE + " SET " + DbObject.LAST_MODIFIED_TIMESTAMP + " = " + DbObject.TIMESTAMP); } if (oldVersion <= 58) { db.execSQL("ALTER TABLE " + Group.TABLE + " ADD COLUMN " + Group.GROUP_TYPE + " TEXT DEFAULT 'group'"); db.execSQL("UPDATE " + Group.TABLE + " SET " + Group.GROUP_TYPE + " = 'group'"); } if (oldVersion <= 59) { createIndex(db, "INDEX", "objects_last_modified", DbObject.TABLE, DbObject.LAST_MODIFIED_TIMESTAMP); } if (oldVersion <= 60) { db.execSQL("ALTER TABLE " + Contact.TABLE + " ADD COLUMN " + Contact.PUBLIC_KEY_HASH_64 + " INTEGER DEFAULT 0"); createIndex(db, "INDEX", "contacts_by_pkp", Contact.TABLE, Contact.PUBLIC_KEY_HASH_64); Cursor peeps = db .rawQuery("SELECT " + Contact._ID + "," + Contact.PUBLIC_KEY + " FROM " + Contact.TABLE, null); peeps.moveToFirst(); while (!peeps.isAfterLast()) { db.execSQL("UPDATE " + Contact.TABLE + " SET " + Contact.PUBLIC_KEY_HASH_64 + " = " + hashPublicKey(peeps.getBlob(1)) + " WHERE " + Contact._ID + " = " + peeps.getLong(0)); peeps.moveToNext(); } peeps.close(); } db.setVersion(VERSION); }
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 va 2s .c om*/ 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(); } } }