List of usage examples for android.database.sqlite SQLiteDatabase endTransaction
public void endTransaction()
From source file:org.opendatakit.common.android.utilities.CsvUtil.java
/** * Update tableId from//from w ww . ja v a 2 s . c o m * <ul> * <li>tables/tableId/properties.csv</li> * <li>tables/tableId/definition.csv</li> * </ul> * * This will either create a table, or verify that the table structure matches * that defined in the csv. It will then override all the KVS entries with * those present in the file. * * @param importListener * @param tableId * @throws IOException */ public void updateTablePropertiesFromCsv(ImportListener importListener, String tableId) throws IOException { WebLogger.getLogger(appName).i(TAG, "updateTablePropertiesFromCsv: tableId: " + tableId); SQLiteDatabase db = null; try { db = DatabaseFactory.get().getDatabase(context, appName); List<Column> columns = new ArrayList<Column>(); // reading data File file = null; FileInputStream in = null; InputStreamReader input = null; RFC4180CsvReader cr = null; try { file = new File(ODKFileUtils.getTableDefinitionCsvFile(appName, tableId)); in = new FileInputStream(file); input = new InputStreamReader(in, CharEncoding.UTF_8); cr = new RFC4180CsvReader(input); String[] row; // Read ColumnDefinitions // get the column headers String[] colHeaders = cr.readNext(); int colHeadersLength = countUpToLastNonNullElement(colHeaders); // get the first row row = cr.readNext(); while (row != null && countUpToLastNonNullElement(row) != 0) { String elementKeyStr = null; String elementNameStr = null; String elementTypeStr = null; String listChildElementKeysStr = null; int rowLength = countUpToLastNonNullElement(row); for (int i = 0; i < rowLength; ++i) { if (i >= colHeadersLength) { throw new IllegalStateException("data beyond header row of ColumnDefinitions table"); } if (ColumnDefinitionsColumns.ELEMENT_KEY.equals(colHeaders[i])) { elementKeyStr = row[i]; } if (ColumnDefinitionsColumns.ELEMENT_NAME.equals(colHeaders[i])) { elementNameStr = row[i]; } if (ColumnDefinitionsColumns.ELEMENT_TYPE.equals(colHeaders[i])) { elementTypeStr = row[i]; } if (ColumnDefinitionsColumns.LIST_CHILD_ELEMENT_KEYS.equals(colHeaders[i])) { listChildElementKeysStr = row[i]; } } if (elementKeyStr == null || elementTypeStr == null) { throw new IllegalStateException("ElementKey and ElementType must be specified"); } columns.add(new Column(elementKeyStr, elementNameStr, elementTypeStr, listChildElementKeysStr)); // get next row or blank to end... row = cr.readNext(); } cr.close(); try { input.close(); } catch (IOException e) { } try { in.close(); } catch (IOException e) { } ArrayList<ColumnDefinition> colDefns = ColumnDefinition.buildColumnDefinitions(appName, tableId, columns); Map<String, List<KeyValueStoreEntry>> colEntries = new TreeMap<String, List<KeyValueStoreEntry>>(); file = new File(ODKFileUtils.getTablePropertiesCsvFile(appName, tableId)); in = new FileInputStream(file); input = new InputStreamReader(in, CharEncoding.UTF_8); cr = new RFC4180CsvReader(input); // Read KeyValueStore // read the column headers String[] kvsHeaders = cr.readNext(); int kvsHeadersLength = countUpToLastNonNullElement(kvsHeaders); String displayName = null; List<KeyValueStoreEntry> kvsEntries = new ArrayList<KeyValueStoreEntry>(); // read the first row row = cr.readNext(); while (row != null && countUpToLastNonNullElement(row) != 0) { KeyValueStoreEntry kvsEntry = new KeyValueStoreEntry(); kvsEntry.tableId = tableId; int rowLength = countUpToLastNonNullElement(row); for (int i = 0; i < rowLength; ++i) { if (KeyValueStoreColumns.PARTITION.equals(kvsHeaders[i])) { kvsEntry.partition = row[i]; } if (KeyValueStoreColumns.ASPECT.equals(kvsHeaders[i])) { kvsEntry.aspect = row[i]; } if (KeyValueStoreColumns.KEY.equals(kvsHeaders[i])) { kvsEntry.key = row[i]; } if (KeyValueStoreColumns.VALUE_TYPE.equals(kvsHeaders[i])) { kvsEntry.type = row[i]; } if (KeyValueStoreColumns.VALUE.equals(kvsHeaders[i])) { kvsEntry.value = row[i]; } } if (KeyValueStoreConstants.PARTITION_COLUMN.equals(kvsEntry.partition)) { // column-specific String column = kvsEntry.aspect; List<KeyValueStoreEntry> kvList = colEntries.get(column); if (kvList == null) { kvList = new ArrayList<KeyValueStoreEntry>(); colEntries.put(column, kvList); } try { ColumnDefinition.find(colDefns, column); } catch (IllegalArgumentException e) { throw new IllegalStateException( "Reference to non-existent column: " + column + " of tableId: " + tableId); } kvList.add(kvsEntry); } else { // not column-specific // see if we can find the displayName if (KeyValueStoreConstants.PARTITION_TABLE.equals(kvsEntry.partition) && KeyValueStoreConstants.ASPECT_DEFAULT.equals(kvsEntry.aspect) && KeyValueStoreConstants.TABLE_DISPLAY_NAME.equals(kvsEntry.key)) { displayName = kvsEntry.value; } // still put it in the kvsEntries -- displayName is not stored??? kvsEntries.add(kvsEntry); } // get next row or blank to end... row = cr.readNext(); } cr.close(); try { input.close(); } catch (IOException e) { } try { in.close(); } catch (IOException e) { } if (ODKDatabaseUtils.get().hasTableId(db, tableId)) { ArrayList<ColumnDefinition> existingDefns = TableUtil.get().getColumnDefinitions(db, appName, tableId); // confirm that the column definitions are unchanged... if (existingDefns.size() != colDefns.size()) { throw new IllegalStateException( "Unexpectedly found tableId with different column definitions that already exists!"); } for (ColumnDefinition ci : colDefns) { ColumnDefinition existingDefn; try { existingDefn = ColumnDefinition.find(existingDefns, ci.getElementKey()); } catch (IllegalArgumentException e) { throw new IllegalStateException( "Unexpectedly failed to match elementKey: " + ci.getElementKey()); } if (!existingDefn.getElementName().equals(ci.getElementName())) { throw new IllegalStateException( "Unexpected mis-match of elementName for elementKey: " + ci.getElementKey()); } List<ColumnDefinition> refList = existingDefn.getChildren(); List<ColumnDefinition> ciList = ci.getChildren(); if (refList.size() != ciList.size()) { throw new IllegalStateException( "Unexpected mis-match of listOfStringElementKeys for elementKey: " + ci.getElementKey()); } for (int i = 0; i < ciList.size(); ++i) { if (!refList.contains(ciList.get(i))) { throw new IllegalStateException("Unexpected mis-match of listOfStringElementKeys[" + i + "] for elementKey: " + ci.getElementKey()); } } ElementType type = ci.getType(); ElementType existingType = existingDefn.getType(); if (!existingType.equals(type)) { throw new IllegalStateException( "Unexpected mis-match of elementType for elementKey: " + ci.getElementKey()); } } // OK -- we have matching table definition // now just clear and update the properties... try { db.beginTransaction(); ODKDatabaseUtils.get().replaceDBTableMetadata(db, tableId, kvsEntries, true); for (ColumnDefinition ci : colDefns) { // put the displayName into the KVS List<KeyValueStoreEntry> kvsList = colEntries.get(ci.getElementKey()); if (kvsList == null) { kvsList = new ArrayList<KeyValueStoreEntry>(); colEntries.put(ci.getElementKey(), kvsList); } KeyValueStoreEntry entry = null; for (KeyValueStoreEntry e : kvsList) { if (e.partition.equals(KeyValueStoreConstants.PARTITION_COLUMN) && e.aspect.equals(ci.getElementKey()) && e.key.equals(KeyValueStoreConstants.COLUMN_DISPLAY_NAME)) { entry = e; break; } } if (entry != null && (entry.value == null || entry.value.trim().length() == 0)) { kvsList.remove(entry); entry = null; } if (entry == null) { entry = new KeyValueStoreEntry(); entry.tableId = tableId; entry.partition = KeyValueStoreConstants.PARTITION_COLUMN; entry.aspect = ci.getElementKey(); entry.key = KeyValueStoreConstants.COLUMN_DISPLAY_NAME; entry.type = ElementDataType.object.name(); entry.value = ODKFileUtils.mapper.writeValueAsString(ci.getElementKey()); kvsList.add(entry); } ODKDatabaseUtils.get().replaceDBTableMetadata(db, tableId, kvsList, false); } db.setTransactionSuccessful(); } finally { db.endTransaction(); } } else { for (ColumnDefinition ci : colDefns) { // put the displayName into the KVS if not supplied List<KeyValueStoreEntry> kvsList = colEntries.get(ci.getElementKey()); if (kvsList == null) { kvsList = new ArrayList<KeyValueStoreEntry>(); colEntries.put(ci.getElementKey(), kvsList); } KeyValueStoreEntry entry = null; for (KeyValueStoreEntry e : kvsList) { if (e.partition.equals(KeyValueStoreConstants.PARTITION_COLUMN) && e.aspect.equals(ci.getElementKey()) && e.key.equals(KeyValueStoreConstants.COLUMN_DISPLAY_NAME)) { entry = e; break; } } if (entry != null && (entry.value == null || entry.value.trim().length() == 0)) { kvsList.remove(entry); entry = null; } if (entry == null) { entry = new KeyValueStoreEntry(); entry.tableId = tableId; entry.partition = KeyValueStoreConstants.PARTITION_COLUMN; entry.aspect = ci.getElementKey(); entry.key = KeyValueStoreConstants.COLUMN_DISPLAY_NAME; entry.type = ElementDataType.object.name(); entry.value = ODKFileUtils.mapper.writeValueAsString(ci.getElementKey()); kvsList.add(entry); } } // ensure there is a display name for the table... KeyValueStoreEntry e = new KeyValueStoreEntry(); e.tableId = tableId; e.partition = KeyValueStoreConstants.PARTITION_TABLE; e.aspect = KeyValueStoreConstants.ASPECT_DEFAULT; e.key = KeyValueStoreConstants.TABLE_DISPLAY_NAME; e.type = ElementDataType.object.name(); e.value = NameUtil.normalizeDisplayName( (displayName == null ? NameUtil.constructSimpleDisplayName(tableId) : displayName)); kvsEntries.add(e); try { db.beginTransaction(); ODKDatabaseUtils.get().createOrOpenDBTableWithColumns(db, appName, tableId, columns); ODKDatabaseUtils.get().replaceDBTableMetadata(db, tableId, kvsEntries, false); // we have created the table... for (ColumnDefinition ci : colDefns) { List<KeyValueStoreEntry> kvsList = colEntries.get(ci.getElementKey()); ODKDatabaseUtils.get().replaceDBTableMetadata(db, tableId, kvsList, false); } db.setTransactionSuccessful(); } finally { db.endTransaction(); } } } finally { try { if (input != null) { input.close(); } } catch (IOException e) { } } // And update the inserted properties so that // the known entries have their expected types. try { db.beginTransaction(); ODKDatabaseUtils.get().enforceTypesDBTableMetadata(db, tableId); db.setTransactionSuccessful(); } finally { db.endTransaction(); } } finally { if (db != null) { db.close(); } } }
From source file:mobile.tiis.appv2.base.BackboneApplication.java
public boolean addChildVaccinationEventVaccinationAppointment(ChildCollector2 childCollector) { Log.d("coze", "saving data to db"); boolean containsData = false; List<Child> children = childCollector.getChildList(); List<VaccinationEvent> vaccinationEvents = childCollector.getVeList(); List<VaccinationAppointment> vaccinationAppointments = childCollector.getVaList(); DatabaseHandler db = getDatabaseInstance(); SQLiteDatabase db1 = db.getWritableDatabase(); db1.beginTransactionNonExclusive();/*from w w w . j a va 2 s .co m*/ try { if (children != null) { String sql0 = "INSERT OR REPLACE INTO " + SQLHandler.Tables.CHILD + " ( " + SQLHandler.SyncColumns.UPDATED + ", " + SQLHandler.ChildColumns.ID + "," + SQLHandler.ChildColumns.BARCODE_ID + "," + SQLHandler.ChildColumns.FIRSTNAME1 + "," + SQLHandler.ChildColumns.FIRSTNAME2 + "," + SQLHandler.ChildColumns.LASTNAME1 + "," + SQLHandler.ChildColumns.BIRTHDATE + "," + SQLHandler.ChildColumns.GENDER + "," + SQLHandler.ChildColumns.TEMP_ID + "," + SQLHandler.ChildColumns.HEALTH_FACILITY + "," + SQLHandler.ChildColumns.DOMICILE + "," + SQLHandler.ChildColumns.DOMICILE_ID + "," + SQLHandler.ChildColumns.HEALTH_FACILITY_ID + "," + SQLHandler.ChildColumns.STATUS_ID + "," + SQLHandler.ChildColumns.BIRTHPLACE_ID + "," + SQLHandler.ChildColumns.NOTES + "," + SQLHandler.ChildColumns.STATUS + "," + SQLHandler.ChildColumns.MOTHER_FIRSTNAME + "," + SQLHandler.ChildColumns.MOTHER_LASTNAME + "," + SQLHandler.ChildColumns.PHONE + "," + SQLHandler.ChildColumns.CUMULATIVE_SERIAL_NUMBER + "," + SQLHandler.ChildColumns.CHILD_REGISTRY_YEAR + "," + SQLHandler.ChildColumns.MOTHER_VVU_STS + "," + SQLHandler.ChildColumns.MOTHER_TT2_STS + "," + SQLHandler.ChildColumns.MODIFIED_ON + " ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?)"; SQLiteStatement stmt0 = db1.compileStatement(sql0); for (Child child : children) { containsData = true; stmt0.bindString(1, "1"); stmt0.bindString(2, child.getId() == null ? "" : child.getId()); stmt0.bindString(3, child.getBarcodeID() == null ? "" : child.getBarcodeID()); stmt0.bindString(4, child.getFirstname1() == null ? "" : child.getFirstname1()); stmt0.bindString(5, child.getFirstname2() == null ? "" : child.getFirstname2()); stmt0.bindString(6, child.getLastname1() == null ? "" : child.getLastname1()); stmt0.bindString(7, child.getBirthdate() == null ? "" : child.getBirthdate()); stmt0.bindString(8, child.getGender() == null ? "" : child.getGender()); stmt0.bindString(9, child.getTempId() == null ? "" : child.getTempId()); stmt0.bindString(10, child.getHealthcenter() == null ? "" : child.getHealthcenter()); stmt0.bindString(11, child.getDomicile() == null ? "" : child.getDomicile()); stmt0.bindString(12, child.getDomicileId() == null ? "" : child.getDomicileId()); stmt0.bindString(13, child.getHealthcenterId() == null ? "" : child.getHealthcenterId()); stmt0.bindString(14, child.getStatusId() == null ? "" : child.getStatusId()); stmt0.bindString(15, child.getBirthplaceId() == null ? "" : child.getBirthplaceId()); stmt0.bindString(16, child.getNotes() == null ? "" : child.getNotes()); stmt0.bindString(17, child.getDomicile() == null ? "" : child.getDomicile()); stmt0.bindString(18, child.getMotherFirstname() == null ? "" : child.getMotherFirstname()); stmt0.bindString(19, child.getMotherLastname() == null ? "" : child.getMotherLastname()); stmt0.bindString(20, child.getPhone() == null ? "" : child.getPhone()); stmt0.bindString(21, child.getChildCumulativeSn() == null ? "" : child.getChildCumulativeSn()); stmt0.bindString(22, child.getChildRegistryYear() == null ? "" : child.getChildRegistryYear()); stmt0.bindString(23, child.getMotherHivStatus() == null ? "" : child.getMotherHivStatus()); stmt0.bindString(24, child.getMotherTT2Status() == null ? "" : child.getMotherTT2Status()); stmt0.bindString(25, child.getModifiedOn() == null ? "" : child.getModifiedOn()); stmt0.execute(); stmt0.clearBindings(); } } if (vaccinationEvents != null) { String sql = "INSERT OR REPLACE INTO " + SQLHandler.Tables.VACCINATION_EVENT + " ( " + SQLHandler.SyncColumns.UPDATED + ", " + SQLHandler.VaccinationEventColumns.APPOINTMENT_ID + "," + SQLHandler.VaccinationEventColumns.CHILD_ID + "," + SQLHandler.VaccinationEventColumns.DOSE_ID + "," + SQLHandler.VaccinationEventColumns.HEALTH_FACILITY_ID + "," + SQLHandler.VaccinationEventColumns.ID + "," + SQLHandler.VaccinationEventColumns.IS_ACTIVE + "," + SQLHandler.VaccinationEventColumns.MODIFIED_BY + "," + SQLHandler.VaccinationEventColumns.MODIFIED_ON + "," + SQLHandler.VaccinationEventColumns.NONVACCINATION_REASON_ID + "," + SQLHandler.VaccinationEventColumns.SCHEDULED_DATE + "," + SQLHandler.VaccinationEventColumns.VACCINATION_DATE + "," + SQLHandler.VaccinationEventColumns.VACCINATION_STATUS + "," + SQLHandler.VaccinationEventColumns.VACCINE_LOT_ID + " ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; SQLiteStatement stmt = db1.compileStatement(sql); for (VaccinationEvent vaccinationEvent : vaccinationEvents) { containsData = true; stmt.bindString(1, "1"); stmt.bindString(2, vaccinationEvent.getAppointmentId()); stmt.bindString(3, vaccinationEvent.getChildId()); stmt.bindString(4, vaccinationEvent.getDoseId()); stmt.bindString(5, vaccinationEvent.getHealthFacilityId()); stmt.bindString(6, vaccinationEvent.getId()); stmt.bindString(7, vaccinationEvent.getIsActive()); stmt.bindString(8, vaccinationEvent.getModifiedBy()); stmt.bindString(9, vaccinationEvent.getModifiedOn()); stmt.bindString(10, vaccinationEvent.getNonvaccinationReasonId()); stmt.bindString(11, vaccinationEvent.getScheduledDate()); stmt.bindString(12, vaccinationEvent.getVaccinationDate()); stmt.bindString(13, vaccinationEvent.getVaccinationStatus()); stmt.bindString(14, vaccinationEvent.getVaccineLotId()); stmt.execute(); stmt.clearBindings(); } } if (vaccinationAppointments != null) { String sql1 = "INSERT OR REPLACE INTO " + SQLHandler.Tables.VACCINATION_APPOINTMENT + " ( " + SQLHandler.SyncColumns.UPDATED + ", " + SQLHandler.VaccinationAppointmentColumns.CHILD_ID + "," + SQLHandler.VaccinationAppointmentColumns.ID + "," + SQLHandler.VaccinationAppointmentColumns.IS_ACTIVE + "," + SQLHandler.VaccinationAppointmentColumns.MODIFIED_BY + "," + SQLHandler.VaccinationAppointmentColumns.MODIFIED_ON + "," + SQLHandler.VaccinationAppointmentColumns.NOTES + "," + SQLHandler.VaccinationAppointmentColumns.OUTREACH + "," + SQLHandler.VaccinationAppointmentColumns.SCHEDULED_DATE + "," + SQLHandler.VaccinationAppointmentColumns.SCHEDULED_FACILITY_ID + " ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?,?)"; SQLiteStatement stmt1 = db1.compileStatement(sql1); for (VaccinationAppointment vaccinationAppointment : vaccinationAppointments) { containsData = true; stmt1.bindString(1, "1"); stmt1.bindString(2, vaccinationAppointment.getChildId()); stmt1.bindString(3, vaccinationAppointment.getId()); stmt1.bindString(4, vaccinationAppointment.getIsActive()); stmt1.bindString(5, vaccinationAppointment.getModifiedBy()); stmt1.bindString(6, vaccinationAppointment.getModifiedOn()); stmt1.bindString(7, vaccinationAppointment.getNotes()); stmt1.bindString(8, vaccinationAppointment.getOutreach()); stmt1.bindString(9, vaccinationAppointment.getScheduledDate()); stmt1.bindString(10, vaccinationAppointment.getScheduledFacilityId()); Log.d("day20", "Out Reach for " + vaccinationAppointment.getChildId() + " is : " + vaccinationAppointment.getOutreach()); stmt1.execute(); stmt1.clearBindings(); } } db1.setTransactionSuccessful(); db1.endTransaction(); } catch (Exception e) { try { db1.endTransaction(); } catch (Exception e1) { e1.printStackTrace(); } e.printStackTrace(); } Log.d("coze", "saving data to db returning = " + containsData); return containsData; }