Java tutorial
/** * Apow - a mobile EHR Management System for low-resource environments * in developing countries, exemplified by rural Ghana * Copyright (C) 2014 Martin Landua * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see http://www.gnu.org/licenses/. */ package de.uni_koblenz_landau.apow.db; import java.util.ArrayList; import java.util.List; import org.json.JSONException; import org.json.JSONObject; import de.uni_koblenz_landau.apow.helper.Constants; import de.uni_koblenz_landau.apow.helper.CustomApplication; import net.sqlcipher.SQLException; import net.sqlcipher.database.SQLiteDatabase; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; /** * Implements database access for synchronizing tables and helper functions. * Source: http://havrl.blogspot.de/2013/08/synchronization-algorithm-for.html * * @author Martin Landua * */ public class SyncDBHelper { private SQLiteDatabase mDatabase; private final DBHelper mDbHelper; public SyncDBHelper(Context context) { DBHelper.initializeInstance(context); mDbHelper = DBHelper.getInstance(); } public void open(Context context) throws SQLException { String password = ((CustomApplication) context.getApplicationContext()).getPassword(); mDatabase = mDbHelper.openDatabase(password); } public void close() { mDbHelper.closeDatabase(); } /** * Finds the last modified date of a table. * @param table Table * @return Last modified date */ public String lastUpdate(String table) { String query; String lastupdate = ""; if (table.equals(Constants.TABLES.OBS.getName())) { query = "SELECT MAX(IFNULL(date_created, 0)) FROM obs;"; } else { query = "SELECT MAX(MAX(IFNULL(date_changed, 0)), MAX(IFNULL(date_created, 0))) FROM " + table; } Cursor cursor = mDatabase.rawQuery(query, new String[] {}); cursor.moveToFirst(); if (!cursor.isAfterLast()) { lastupdate = cursor.getString(0); } cursor.close(); return lastupdate; } /** * Updates the persons table. * @param values Values * @return Affected rows */ public int updatePersons(List<ContentValues> values) { int affectedRows = 0; for (ContentValues item : values) { item.remove("person_id"); // update table affectedRows += updateTable(item, "person"); } return affectedRows; } /** * Updates the patients table. * @param values Values * @return Affected rows */ public int updatePatients(List<ContentValues> values) { int affectedRows = 0; for (ContentValues item : values) { // prepare relations to locale IDs item.remove("patient_id"); String uuid = item.getAsString("uuid"); item.remove("uuid"); int id = getPersonIDByUUID(uuid); item.put("patient_id", id); // update table affectedRows += updatePatientTable(item); } return affectedRows; } /** * Updates the tables with relation to the person table. * @param values Values * @param table Table * @param relationColumn Name of relation column * @return Affected rows */ public int updatePersonChildren(List<ContentValues> values, String table, String relationColumn) { int affectedRows = 0; for (ContentValues item : values) { // prepare relations to locale IDs String relationUUID = item.getAsString(relationColumn); item.remove(relationColumn); item.put(relationColumn, getPersonIDByUUID(relationUUID)); // update table affectedRows += updateTable(item, table); } return affectedRows; } /** * Updates the obs table. * @param values Values * @return Affected rows */ public int updateObs(List<ContentValues> values) { int affectedRows = 0; for (ContentValues item : values) { // prepare relations to locale IDs item.remove("obs_id"); String personUUID = item.getAsString("person_id"); String encounterUUID = item.getAsString("encounter_id"); String obsGroupUUID = item.getAsString("obs_group_id"); item.remove("person_id"); item.remove("encounter_id"); item.remove("obs_group_id"); item.put("person_id", getPersonIDByUUID(personUUID)); item.put("encounter_id", getEncounterIDByUUID(encounterUUID)); if (obsGroupUUID != null) { item.put("obs_group_id", getObsIDByUUID(obsGroupUUID)); } affectedRows += updateTable(item, "obs"); } return affectedRows; } /** * Inserts big numbers of rows into fact tables. * @param values Values * @param tablename Table name * @return Affected rows */ public int bulkInsert(List<ContentValues> values, String tablename) { int numInserted = 0; // Bundle in one transaction for better performance mDatabase.beginTransaction(); try { for (ContentValues item : values) { mDatabase.insertWithOnConflict(tablename, null, item, SQLiteDatabase.CONFLICT_REPLACE); numInserted++; } mDatabase.setTransactionSuccessful(); } catch (Exception e) { e.printStackTrace(); } finally { mDatabase.endTransaction(); } return numInserted; } /** * Gets modified rows of a table. * @param table Table * @return List of rows as JSONObject * @throws JSONException */ public List<JSONObject> getDirtyRows(String table) throws JSONException { String query = null; switch (table) { case "patient": query = "SELECT pa.tribe, pa.creator, pa.date_created, pa.changed_by, pa.date_changed," + " pa.voided, pa.voided_by, pa.date_voided, pa.void_reason, pe.uuid FROM patient" + " pa INNER JOIN person pe ON pe.person_id = pa.patient_id " + " WHERE pa.dirty = 1"; break; case "person": query = "SELECT pe.gender, pe.birthdate, pe.birthdate_estimated, pe.dead, pe.death_date," + " pe.cause_of_death, pe.creator, pe.date_created, pe.changed_by, pe.date_changed," + " pe.voided, pe.voided_by, pe.date_voided, pe.void_reason, pe.uuid FROM person pe" + " WHERE pe.dirty = 1"; break; case "patient_identifier": query = "SELECT pe.uuid AS patient_id, pi.identifier, pi.identifier_type, pi.preferred," + " pi.location_id, pi.creator, pi.date_created, pi.date_changed, pi.changed_by, pi.voided," + " pi.voided_by, pi.date_voided, pi.void_reason, pi.uuid FROM patient_identifier pi" + " INNER JOIN person pe ON pi.patient_id = pe.person_id" + " WHERE pi.dirty = 1"; break; case "person_address": query = "SELECT pe.uuid AS person_id, pa.preferred, pa.address1, pa.address2, pa.city_village," + " pa.state_province, pa.postal_code, pa.country, pa.latitude, pa.longitude, pa.start_date," + " pa.end_date, pa.creator, pa.date_created, pa.voided, pa.voided_by, pa.date_voided," + " pa.void_reason, pa.county_district, pa.address3, pa.address4, pa.address5, pa.address6," + " pa.date_changed, pa.changed_by, pa.uuid FROM person_address pa" + " INNER JOIN person pe ON pa.person_id = pe.person_id" + " WHERE pa.dirty = 1"; break; case "person_name": query = "SELECT pn.preferred, pe.uuid AS person_id, pn.prefix, pn.given_name, pn.middle_name," + " pn.family_name_prefix, pn.family_name, pn.family_name2, pn.family_name_suffix, pn.degree," + " pn.creator, pn.date_created, pn.voided, pn.voided_by, pn.date_voided, pn.void_reason," + " pn.changed_by, pn.date_changed, pn.uuid FROM person_name pn" + " INNER JOIN person pe ON pn.person_id = pe.person_id" + " WHERE pn.dirty = 1"; break; case "encounter": query = "SELECT en.encounter_type, pe.uuid AS patient_id, en.location_id, en.form_id," + " en.encounter_datetime, en.creator, en.date_created, en.voided, en.voided_by," + " en.date_voided, en.void_reason, en.changed_by, en.date_changed, en.visit_id," + " en.uuid FROM encounter en INNER JOIN person pe ON en.patient_id = pe.person_id" + " WHERE en.dirty = 1"; break; case "obs": query = "SELECT pe.uuid AS person_id, o.concept_id, en.uuid AS encounter_id, o.order_id," + " o.obs_datetime, o.location_id, o2.uuid AS obs_group_id, o.accession_number," + " o.value_group_id, o.value_boolean, o.value_coded, o.value_coded_name_id, o.value_drug," + " o.value_datetime, o.value_numeric, o.value_modifier, o.value_text, o.value_complex," + " o.comments, o.creator, o.date_created, o.voided, o.voided_by, o.date_voided, o.void_reason," + " o.uuid, o.previous_version FROM obs o INNER JOIN person pe ON o.person_id = pe.person_id" + " INNER JOIN encounter en ON en.encounter_id = o.encounter_id" + " LEFT JOIN obs o2 ON o2.obs_id = o.obs_group_id" + " WHERE o.dirty = 1 ORDER BY o.obs_group_id"; break; } Cursor cursor = mDatabase.rawQuery(query, new String[] {}); cursor.moveToFirst(); List<JSONObject> items = new ArrayList<>(); JSONObject root; JSONObject obj; // For every row while (!cursor.isAfterLast()) { root = new JSONObject(); obj = new JSONObject(); // For every column for (int i = 0; i < cursor.getColumnCount(); i++) { String key = cursor.getColumnName(i); // Convert value to JSON type if (cursor.isNull(i)) { obj.put(key, JSONObject.NULL); } else { obj.put(key, cursor.getString(i)); } } root.put("table", table); root.put("values", obj); items.add(root); cursor.moveToNext(); } cursor.close(); return items; } /** * Resets dirty flag of synchronized modified rows and updates change dates. * @param table Table * @param uuid UUID * @param datechanged Date changed * @param datecreated Date created */ public void cleanPerson(String table, String uuid, String datechanged, String datecreated) { String where; ContentValues values = new ContentValues(); values.put("date_created", datecreated); values.put("dirty", false); if (table.equals(Constants.TABLES.PATIENT.getName())) { int id = getPersonIDByUUID(uuid); where = "patient_id = " + id; values.put("date_changed", datechanged); } else { where = "uuid = '" + uuid + "'"; if (!table.equals(Constants.TABLES.OBS.getName())) { values.put("date_changed", datechanged); } } mDatabase.update(table, values, where, null); } /** * Is row modified? * @param table Table * @param uuid UUID * @return -1 if new, 0 if not modified, 1 if modified */ private int isDirty(String table, String uuid) { int dirty = -1; String query = "SELECT dirty FROM " + table + " WHERE uuid = ?;"; Cursor cursor = mDatabase.rawQuery(query, new String[] { uuid }); cursor.moveToFirst(); if (!cursor.isAfterLast()) { dirty = cursor.getInt(0); } cursor.close(); return dirty; } /** * Is patient row modified? * @param id ID * @return -1 if new, 0 if not modified, 1 if modified */ private int isPatientDirty(int id) { int dirty = -1; String query = "SELECT dirty FROM patient WHERE patient_id = ?;"; Cursor cursor = mDatabase.rawQuery(query, new String[] { "" + id }); cursor.moveToFirst(); if (!cursor.isAfterLast()) { dirty = cursor.getInt(0); } cursor.close(); return dirty; } /** * Changes modified flag for row * @param table Table * @param uuid UUID * @param dirty Dirty */ private void setDirty(String table, String uuid, Boolean dirty) { ContentValues values = new ContentValues(); values.put("dirty", dirty); mDatabase.update(table, values, "uuid = '" + uuid + "'", null); } /** * Changes modified flag for patient row * @param id ID * @param dirty Dirty */ private void setPatientDirty(int id, Boolean dirty) { ContentValues values = new ContentValues(); values.put("dirty", dirty); mDatabase.update("patient", values, "patient_id = " + id, null); } /** * Gets ID of a person by UUID. * @param uuid UUID * @return Person ID */ private int getPersonIDByUUID(String uuid) { int result = -1; String query = "SELECT pe.person_id FROM person pe WHERE pe.uuid = ?"; Cursor cursor = mDatabase.rawQuery(query, new String[] { uuid }); cursor.moveToFirst(); if (!cursor.isAfterLast()) { result = cursor.getInt(0); } cursor.close(); return result; } /** * Gets ID of an encounter by UUID. * @param uuid UUID * @return Encounter ID */ private int getEncounterIDByUUID(String uuid) { int result = -1; String query = "SELECT en.encounter_id FROM encounter en WHERE en.uuid = ?"; Cursor cursor = mDatabase.rawQuery(query, new String[] { uuid }); cursor.moveToFirst(); if (!cursor.isAfterLast()) { result = cursor.getInt(0); } cursor.close(); return result; } /** * Gets ID of an observation by UUID. * @param uuid UUID * @return Observation ID */ private int getObsIDByUUID(String uuid) { int result = -1; String query = "SELECT o.obs_id FROM obs o WHERE o.uuid = ?"; Cursor cursor = mDatabase.rawQuery(query, new String[] { uuid }); cursor.moveToFirst(); if (!cursor.isAfterLast()) { result = cursor.getInt(0); } cursor.close(); return result; } /** * Updates a table with given values. * @param values Values * @param table Table * @return Affected rows */ private int updateTable(ContentValues values, String table) { // check if uuid exists in table int affectedRows = 0; String uuid = values.getAsString("uuid"); int dirty = isDirty(table, uuid); // row not in table if (dirty == -1) { // insert mDatabase.insert(table, null, values); affectedRows++; } else if (dirty == 0) { // replace mDatabase.update(table, values, "uuid = '" + uuid + "'", null); affectedRows++; } else if (dirty == 1) { // replace mDatabase.update(table, values, "uuid = '" + uuid + "'", null); setDirty(table, uuid, false); } return affectedRows; } /** * Updates patient table with given values. * @param values Values * @return Affected rows */ private int updatePatientTable(ContentValues values) { // check if uuid exists in table int affectedRows = 0; int id = values.getAsInteger("patient_id"); int dirty = isPatientDirty(id); // row not in table if (dirty == -1) { // insert mDatabase.insert("patient", null, values); affectedRows++; } else if (dirty == 0) { // replace mDatabase.update("patient", values, "patient_id = " + id, null); affectedRows++; } else if (dirty == 1) { // replace mDatabase.update("patient", values, "patient_id = " + id, null); setPatientDirty(id, false); } return affectedRows; } }