1package eu.siacs.conversations.persistance;
   2
   3import android.content.ContentValues;
   4import android.content.Context;
   5import android.database.Cursor;
   6import android.database.DatabaseUtils;
   7import android.database.sqlite.SQLiteCantOpenDatabaseException;
   8import android.database.sqlite.SQLiteDatabase;
   9import android.database.sqlite.SQLiteOpenHelper;
  10import android.os.Environment;
  11import android.util.Base64;
  12import android.util.Log;
  13import android.util.Pair;
  14
  15import org.json.JSONObject;
  16import org.whispersystems.libaxolotl.AxolotlAddress;
  17import org.whispersystems.libaxolotl.IdentityKey;
  18import org.whispersystems.libaxolotl.IdentityKeyPair;
  19import org.whispersystems.libaxolotl.InvalidKeyException;
  20import org.whispersystems.libaxolotl.state.PreKeyRecord;
  21import org.whispersystems.libaxolotl.state.SessionRecord;
  22import org.whispersystems.libaxolotl.state.SignedPreKeyRecord;
  23
  24import java.io.ByteArrayInputStream;
  25import java.io.File;
  26import java.io.IOException;
  27import java.security.cert.CertificateEncodingException;
  28import java.security.cert.CertificateException;
  29import java.security.cert.CertificateFactory;
  30import java.security.cert.X509Certificate;
  31import java.util.ArrayList;
  32import java.util.HashMap;
  33import java.util.HashSet;
  34import java.util.Iterator;
  35import java.util.List;
  36import java.util.Map;
  37import java.util.Set;
  38import java.util.concurrent.CopyOnWriteArrayList;
  39import org.json.JSONException;
  40
  41import eu.siacs.conversations.Config;
  42import eu.siacs.conversations.crypto.axolotl.AxolotlService;
  43import eu.siacs.conversations.crypto.axolotl.FingerprintStatus;
  44import eu.siacs.conversations.crypto.axolotl.SQLiteAxolotlStore;
  45import eu.siacs.conversations.entities.Account;
  46import eu.siacs.conversations.entities.Contact;
  47import eu.siacs.conversations.entities.Conversation;
  48import eu.siacs.conversations.entities.Message;
  49import eu.siacs.conversations.entities.PresenceTemplate;
  50import eu.siacs.conversations.entities.Roster;
  51import eu.siacs.conversations.entities.ServiceDiscoveryResult;
  52import eu.siacs.conversations.utils.MimeUtils;
  53import eu.siacs.conversations.xmpp.jid.InvalidJidException;
  54import eu.siacs.conversations.xmpp.jid.Jid;
  55
  56public class DatabaseBackend extends SQLiteOpenHelper {
  57
  58	private static DatabaseBackend instance = null;
  59
  60	private static final String DATABASE_NAME = "history";
  61	private static final int DATABASE_VERSION = 34;
  62
  63	private static String CREATE_CONTATCS_STATEMENT = "create table "
  64			+ Contact.TABLENAME + "(" + Contact.ACCOUNT + " TEXT, "
  65			+ Contact.SERVERNAME + " TEXT, " + Contact.SYSTEMNAME + " TEXT,"
  66			+ Contact.JID + " TEXT," + Contact.KEYS + " TEXT,"
  67			+ Contact.PHOTOURI + " TEXT," + Contact.OPTIONS + " NUMBER,"
  68			+ Contact.SYSTEMACCOUNT + " NUMBER, " + Contact.AVATAR + " TEXT, "
  69			+ Contact.LAST_PRESENCE + " TEXT, " + Contact.LAST_TIME + " NUMBER, "
  70			+ Contact.GROUPS + " TEXT, FOREIGN KEY(" + Contact.ACCOUNT + ") REFERENCES "
  71			+ Account.TABLENAME + "(" + Account.UUID
  72			+ ") ON DELETE CASCADE, UNIQUE(" + Contact.ACCOUNT + ", "
  73			+ Contact.JID + ") ON CONFLICT REPLACE);";
  74
  75	private static String CREATE_DISCOVERY_RESULTS_STATEMENT = "create table "
  76			+ ServiceDiscoveryResult.TABLENAME + "("
  77			+ ServiceDiscoveryResult.HASH + " TEXT, "
  78			+ ServiceDiscoveryResult.VER + " TEXT, "
  79			+ ServiceDiscoveryResult.RESULT + " TEXT, "
  80			+ "UNIQUE(" + ServiceDiscoveryResult.HASH + ", "
  81			+ ServiceDiscoveryResult.VER + ") ON CONFLICT REPLACE);";
  82
  83	private static String CREATE_PRESENCE_TEMPLATES_STATEMENT = "CREATE TABLE "
  84			+ PresenceTemplate.TABELNAME + "("
  85			+ PresenceTemplate.UUID + " TEXT, "
  86			+ PresenceTemplate.LAST_USED + " NUMBER,"
  87			+ PresenceTemplate.MESSAGE + " TEXT,"
  88			+ PresenceTemplate.STATUS + " TEXT,"
  89			+ "UNIQUE("+PresenceTemplate.MESSAGE + "," +PresenceTemplate.STATUS+") ON CONFLICT REPLACE);";
  90
  91	private static String CREATE_PREKEYS_STATEMENT = "CREATE TABLE "
  92			+ SQLiteAxolotlStore.PREKEY_TABLENAME + "("
  93			+ SQLiteAxolotlStore.ACCOUNT + " TEXT,  "
  94			+ SQLiteAxolotlStore.ID + " INTEGER, "
  95			+ SQLiteAxolotlStore.KEY + " TEXT, FOREIGN KEY("
  96			+ SQLiteAxolotlStore.ACCOUNT
  97			+ ") REFERENCES " + Account.TABLENAME + "(" + Account.UUID + ") ON DELETE CASCADE, "
  98			+ "UNIQUE( " + SQLiteAxolotlStore.ACCOUNT + ", "
  99			+ SQLiteAxolotlStore.ID
 100			+ ") ON CONFLICT REPLACE"
 101			+ ");";
 102
 103	private static String CREATE_SIGNED_PREKEYS_STATEMENT = "CREATE TABLE "
 104			+ SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME + "("
 105			+ SQLiteAxolotlStore.ACCOUNT + " TEXT,  "
 106			+ SQLiteAxolotlStore.ID + " INTEGER, "
 107			+ SQLiteAxolotlStore.KEY + " TEXT, FOREIGN KEY("
 108			+ SQLiteAxolotlStore.ACCOUNT
 109			+ ") REFERENCES " + Account.TABLENAME + "(" + Account.UUID + ") ON DELETE CASCADE, "
 110			+ "UNIQUE( " + SQLiteAxolotlStore.ACCOUNT + ", "
 111			+ SQLiteAxolotlStore.ID
 112			+ ") ON CONFLICT REPLACE" +
 113			");";
 114
 115	private static String CREATE_SESSIONS_STATEMENT = "CREATE TABLE "
 116			+ SQLiteAxolotlStore.SESSION_TABLENAME + "("
 117			+ SQLiteAxolotlStore.ACCOUNT + " TEXT,  "
 118			+ SQLiteAxolotlStore.NAME + " TEXT, "
 119			+ SQLiteAxolotlStore.DEVICE_ID + " INTEGER, "
 120			+ SQLiteAxolotlStore.KEY + " TEXT, FOREIGN KEY("
 121			+ SQLiteAxolotlStore.ACCOUNT
 122			+ ") REFERENCES " + Account.TABLENAME + "(" + Account.UUID + ") ON DELETE CASCADE, "
 123			+ "UNIQUE( " + SQLiteAxolotlStore.ACCOUNT + ", "
 124			+ SQLiteAxolotlStore.NAME + ", "
 125			+ SQLiteAxolotlStore.DEVICE_ID
 126			+ ") ON CONFLICT REPLACE"
 127			+ ");";
 128
 129	private static String CREATE_IDENTITIES_STATEMENT = "CREATE TABLE "
 130			+ SQLiteAxolotlStore.IDENTITIES_TABLENAME + "("
 131			+ SQLiteAxolotlStore.ACCOUNT + " TEXT,  "
 132			+ SQLiteAxolotlStore.NAME + " TEXT, "
 133			+ SQLiteAxolotlStore.OWN + " INTEGER, "
 134			+ SQLiteAxolotlStore.FINGERPRINT + " TEXT, "
 135			+ SQLiteAxolotlStore.CERTIFICATE + " BLOB, "
 136			+ SQLiteAxolotlStore.TRUST + " TEXT, "
 137			+ SQLiteAxolotlStore.ACTIVE + " NUMBER, "
 138			+ SQLiteAxolotlStore.LAST_ACTIVATION + " NUMBER,"
 139			+ SQLiteAxolotlStore.KEY + " TEXT, FOREIGN KEY("
 140			+ SQLiteAxolotlStore.ACCOUNT
 141			+ ") REFERENCES " + Account.TABLENAME + "(" + Account.UUID + ") ON DELETE CASCADE, "
 142			+ "UNIQUE( " + SQLiteAxolotlStore.ACCOUNT + ", "
 143			+ SQLiteAxolotlStore.NAME + ", "
 144			+ SQLiteAxolotlStore.FINGERPRINT
 145			+ ") ON CONFLICT IGNORE"
 146			+ ");";
 147
 148	private static String START_TIMES_TABLE = "start_times";
 149
 150	private static String CREATE_START_TIMES_TABLE = "create table "+START_TIMES_TABLE+" (timestamp NUMBER);";
 151
 152	private static String CREATE_MESSAGE_TIME_INDEX = "create INDEX message_time_index ON "+Message.TABLENAME+"("+Message.TIME_SENT+")";
 153
 154	private DatabaseBackend(Context context) {
 155		super(context, DATABASE_NAME, null, DATABASE_VERSION);
 156	}
 157
 158	@Override
 159	public void onCreate(SQLiteDatabase db) {
 160		db.execSQL("PRAGMA foreign_keys=ON;");
 161		db.execSQL("create table " + Account.TABLENAME + "(" + Account.UUID+ " TEXT PRIMARY KEY,"
 162				+ Account.USERNAME + " TEXT,"
 163				+ Account.SERVER + " TEXT,"
 164				+ Account.PASSWORD + " TEXT,"
 165				+ Account.DISPLAY_NAME + " TEXT, "
 166				+ Account.STATUS + " TEXT,"
 167				+ Account.STATUS_MESSAGE + " TEXT,"
 168				+ Account.ROSTERVERSION + " TEXT,"
 169				+ Account.OPTIONS + " NUMBER, "
 170				+ Account.AVATAR + " TEXT, "
 171				+ Account.KEYS + " TEXT, "
 172				+ Account.HOSTNAME + " TEXT, "
 173				+ Account.PORT + " NUMBER DEFAULT 5222)");
 174		db.execSQL("create table " + Conversation.TABLENAME + " ("
 175				+ Conversation.UUID + " TEXT PRIMARY KEY, " + Conversation.NAME
 176				+ " TEXT, " + Conversation.CONTACT + " TEXT, "
 177				+ Conversation.ACCOUNT + " TEXT, " + Conversation.CONTACTJID
 178				+ " TEXT, " + Conversation.CREATED + " NUMBER, "
 179				+ Conversation.STATUS + " NUMBER, " + Conversation.MODE
 180				+ " NUMBER, " + Conversation.ATTRIBUTES + " TEXT, FOREIGN KEY("
 181				+ Conversation.ACCOUNT + ") REFERENCES " + Account.TABLENAME
 182				+ "(" + Account.UUID + ") ON DELETE CASCADE);");
 183		db.execSQL("create table " + Message.TABLENAME + "( " + Message.UUID
 184				+ " TEXT PRIMARY KEY, " + Message.CONVERSATION + " TEXT, "
 185				+ Message.TIME_SENT + " NUMBER, " + Message.COUNTERPART
 186				+ " TEXT, " + Message.TRUE_COUNTERPART + " TEXT,"
 187				+ Message.BODY + " TEXT, " + Message.ENCRYPTION + " NUMBER, "
 188				+ Message.STATUS + " NUMBER," + Message.TYPE + " NUMBER, "
 189				+ Message.RELATIVE_FILE_PATH + " TEXT, "
 190				+ Message.SERVER_MSG_ID + " TEXT, "
 191				+ Message.FINGERPRINT + " TEXT, "
 192				+ Message.CARBON + " INTEGER, "
 193				+ Message.EDITED + " TEXT, "
 194				+ Message.READ + " NUMBER DEFAULT 1, "
 195				+ Message.OOB + " INTEGER, "
 196				+ Message.ERROR_MESSAGE + " TEXT,"
 197				+ Message.REMOTE_MSG_ID + " TEXT, FOREIGN KEY("
 198				+ Message.CONVERSATION + ") REFERENCES "
 199				+ Conversation.TABLENAME + "(" + Conversation.UUID
 200				+ ") ON DELETE CASCADE);");
 201		db.execSQL(CREATE_MESSAGE_TIME_INDEX);
 202		db.execSQL(CREATE_CONTATCS_STATEMENT);
 203		db.execSQL(CREATE_DISCOVERY_RESULTS_STATEMENT);
 204		db.execSQL(CREATE_SESSIONS_STATEMENT);
 205		db.execSQL(CREATE_PREKEYS_STATEMENT);
 206		db.execSQL(CREATE_SIGNED_PREKEYS_STATEMENT);
 207		db.execSQL(CREATE_IDENTITIES_STATEMENT);
 208		db.execSQL(CREATE_PRESENCE_TEMPLATES_STATEMENT);
 209		db.execSQL(CREATE_START_TIMES_TABLE);
 210	}
 211
 212	@Override
 213	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 214		if (oldVersion < 2 && newVersion >= 2) {
 215			db.execSQL("update " + Account.TABLENAME + " set "
 216					+ Account.OPTIONS + " = " + Account.OPTIONS + " | 8");
 217		}
 218		if (oldVersion < 3 && newVersion >= 3) {
 219			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
 220					+ Message.TYPE + " NUMBER");
 221		}
 222		if (oldVersion < 5 && newVersion >= 5) {
 223			db.execSQL("DROP TABLE " + Contact.TABLENAME);
 224			db.execSQL(CREATE_CONTATCS_STATEMENT);
 225			db.execSQL("UPDATE " + Account.TABLENAME + " SET "
 226					+ Account.ROSTERVERSION + " = NULL");
 227		}
 228		if (oldVersion < 6 && newVersion >= 6) {
 229			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
 230					+ Message.TRUE_COUNTERPART + " TEXT");
 231		}
 232		if (oldVersion < 7 && newVersion >= 7) {
 233			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
 234					+ Message.REMOTE_MSG_ID + " TEXT");
 235			db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
 236					+ Contact.AVATAR + " TEXT");
 237			db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN "
 238					+ Account.AVATAR + " TEXT");
 239		}
 240		if (oldVersion < 8 && newVersion >= 8) {
 241			db.execSQL("ALTER TABLE " + Conversation.TABLENAME + " ADD COLUMN "
 242					+ Conversation.ATTRIBUTES + " TEXT");
 243		}
 244		if (oldVersion < 9 && newVersion >= 9) {
 245			db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
 246					+ Contact.LAST_TIME + " NUMBER");
 247			db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
 248					+ Contact.LAST_PRESENCE + " TEXT");
 249		}
 250		if (oldVersion < 10 && newVersion >= 10) {
 251			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
 252					+ Message.RELATIVE_FILE_PATH + " TEXT");
 253		}
 254		if (oldVersion < 11 && newVersion >= 11) {
 255			db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
 256					+ Contact.GROUPS + " TEXT");
 257			db.execSQL("delete from " + Contact.TABLENAME);
 258			db.execSQL("update " + Account.TABLENAME + " set " + Account.ROSTERVERSION + " = NULL");
 259		}
 260		if (oldVersion < 12 && newVersion >= 12) {
 261			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
 262					+ Message.SERVER_MSG_ID + " TEXT");
 263		}
 264		if (oldVersion < 13 && newVersion >= 13) {
 265			db.execSQL("delete from " + Contact.TABLENAME);
 266			db.execSQL("update " + Account.TABLENAME + " set " + Account.ROSTERVERSION + " = NULL");
 267		}
 268		if (oldVersion < 14 && newVersion >= 14) {
 269			canonicalizeJids(db);
 270		}
 271		if (oldVersion < 15 && newVersion >= 15) {
 272			recreateAxolotlDb(db);
 273			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
 274					+ Message.FINGERPRINT + " TEXT");
 275		} else if (oldVersion < 22 && newVersion >= 22) {
 276			db.execSQL("ALTER TABLE " + SQLiteAxolotlStore.IDENTITIES_TABLENAME + " ADD COLUMN " + SQLiteAxolotlStore.CERTIFICATE);
 277		}
 278		if (oldVersion < 16 && newVersion >= 16) {
 279			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
 280					+ Message.CARBON + " INTEGER");
 281		}
 282		if (oldVersion < 19 && newVersion >= 19) {
 283			db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN " + Account.DISPLAY_NAME + " TEXT");
 284		}
 285		if (oldVersion < 20 && newVersion >= 20) {
 286			db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN " + Account.HOSTNAME + " TEXT");
 287			db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN " + Account.PORT + " NUMBER DEFAULT 5222");
 288		}
 289		if (oldVersion < 26 && newVersion >= 26) {
 290			db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN " + Account.STATUS + " TEXT");
 291			db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN " + Account.STATUS_MESSAGE + " TEXT");
 292		}
 293		/* Any migrations that alter the Account table need to happen BEFORE this migration, as it
 294		 * depends on account de-serialization.
 295		 */
 296		if (oldVersion < 17 && newVersion >= 17) {
 297			List<Account> accounts = getAccounts(db);
 298			for (Account account : accounts) {
 299				String ownDeviceIdString = account.getKey(SQLiteAxolotlStore.JSONKEY_REGISTRATION_ID);
 300				if (ownDeviceIdString == null) {
 301					continue;
 302				}
 303				int ownDeviceId = Integer.valueOf(ownDeviceIdString);
 304				AxolotlAddress ownAddress = new AxolotlAddress(account.getJid().toBareJid().toPreppedString(), ownDeviceId);
 305				deleteSession(db, account, ownAddress);
 306				IdentityKeyPair identityKeyPair = loadOwnIdentityKeyPair(db, account);
 307				if (identityKeyPair != null) {
 308					String[] selectionArgs = {
 309							account.getUuid(),
 310							identityKeyPair.getPublicKey().getFingerprint().replaceAll("\\s", "")
 311					};
 312					ContentValues values = new ContentValues();
 313					values.put(SQLiteAxolotlStore.TRUSTED, 2);
 314					db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME, values,
 315							SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 316									+ SQLiteAxolotlStore.FINGERPRINT + " = ? ",
 317							selectionArgs);
 318				} else {
 319					Log.d(Config.LOGTAG, account.getJid().toBareJid() + ": could not load own identity key pair");
 320				}
 321			}
 322		}
 323		if (oldVersion < 18 && newVersion >= 18) {
 324			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN " + Message.READ + " NUMBER DEFAULT 1");
 325		}
 326
 327		if (oldVersion < 21 && newVersion >= 21) {
 328			List<Account> accounts = getAccounts(db);
 329			for (Account account : accounts) {
 330				account.unsetPgpSignature();
 331				db.update(Account.TABLENAME, account.getContentValues(), Account.UUID
 332						+ "=?", new String[]{account.getUuid()});
 333			}
 334		}
 335
 336		if (oldVersion < 23 && newVersion >= 23) {
 337			db.execSQL(CREATE_DISCOVERY_RESULTS_STATEMENT);
 338		}
 339
 340		if (oldVersion < 24 && newVersion >= 24) {
 341			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN " + Message.EDITED + " TEXT");
 342		}
 343
 344		if (oldVersion < 25 && newVersion >= 25) {
 345			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN " + Message.OOB + " INTEGER");
 346		}
 347
 348		if (oldVersion <  26 && newVersion >= 26) {
 349			db.execSQL(CREATE_PRESENCE_TEMPLATES_STATEMENT);
 350		}
 351
 352		if (oldVersion < 27 && newVersion >= 27) {
 353			db.execSQL("DELETE FROM "+ServiceDiscoveryResult.TABLENAME);
 354		}
 355
 356		if (oldVersion < 28 && newVersion >= 28) {
 357			canonicalizeJids(db);
 358		}
 359
 360		if (oldVersion < 29 && newVersion >= 29) {
 361			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN " + Message.ERROR_MESSAGE + " TEXT");
 362		}
 363		if (oldVersion < 30 && newVersion >= 30) {
 364			db.execSQL(CREATE_START_TIMES_TABLE);
 365		}
 366		if (oldVersion < 31 && newVersion >= 31) {
 367			db.execSQL("ALTER TABLE "+ SQLiteAxolotlStore.IDENTITIES_TABLENAME + " ADD COLUMN "+SQLiteAxolotlStore.TRUST + " TEXT");
 368			db.execSQL("ALTER TABLE "+ SQLiteAxolotlStore.IDENTITIES_TABLENAME + " ADD COLUMN "+SQLiteAxolotlStore.ACTIVE + " NUMBER");
 369			HashMap<Integer,ContentValues> migration = new HashMap<>();
 370			migration.put(0,createFingerprintStatusContentValues(FingerprintStatus.Trust.TRUSTED,true));
 371			migration.put(1,createFingerprintStatusContentValues(FingerprintStatus.Trust.TRUSTED, true));
 372			migration.put(2,createFingerprintStatusContentValues(FingerprintStatus.Trust.UNTRUSTED, true));
 373			migration.put(3,createFingerprintStatusContentValues(FingerprintStatus.Trust.COMPROMISED, false));
 374			migration.put(4,createFingerprintStatusContentValues(FingerprintStatus.Trust.TRUSTED, false));
 375			migration.put(5,createFingerprintStatusContentValues(FingerprintStatus.Trust.TRUSTED, false));
 376			migration.put(6,createFingerprintStatusContentValues(FingerprintStatus.Trust.UNTRUSTED, false));
 377			migration.put(7,createFingerprintStatusContentValues(FingerprintStatus.Trust.VERIFIED_X509, true));
 378			migration.put(8,createFingerprintStatusContentValues(FingerprintStatus.Trust.VERIFIED_X509, false));
 379			for(Map.Entry<Integer,ContentValues> entry : migration.entrySet()) {
 380				String whereClause = SQLiteAxolotlStore.TRUSTED+"=?";
 381				String[] where = {String.valueOf(entry.getKey())};
 382				db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME,entry.getValue(),whereClause,where);
 383			}
 384
 385		}
 386		if (oldVersion < 32 && newVersion >= 32) {
 387			db.execSQL("ALTER TABLE "+ SQLiteAxolotlStore.IDENTITIES_TABLENAME + " ADD COLUMN "+SQLiteAxolotlStore.LAST_ACTIVATION + " NUMBER");
 388			ContentValues defaults = new ContentValues();
 389			defaults.put(SQLiteAxolotlStore.LAST_ACTIVATION,System.currentTimeMillis());
 390			db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME,defaults,null,null);
 391		}
 392		if (oldVersion < 33 && newVersion >= 33) {
 393			String whereClause = SQLiteAxolotlStore.OWN+"=1";
 394			db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME,createFingerprintStatusContentValues(FingerprintStatus.Trust.VERIFIED,true),whereClause,null);
 395		}
 396
 397		if (oldVersion < 34 && newVersion >= 34) {
 398			db.execSQL(CREATE_MESSAGE_TIME_INDEX);
 399
 400			final File oldPicturesDirectory = new File(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_PICTURES)+"/Conversations/");
 401			final File oldFilesDirectory = new File(Environment.getExternalStorageDirectory() + "/Conversations/");
 402			final File newFilesDirectory = new File(Environment.getExternalStorageDirectory() + "/Conversations/Media/Conversations Files/");
 403			final File newVideosDirectory = new File(Environment.getExternalStorageDirectory() + "/Conversations/Media/Conversations Videos/");
 404			if (oldPicturesDirectory.exists() && oldPicturesDirectory.isDirectory()) {
 405				final File newPicturesDirectory = new File(Environment.getExternalStorageDirectory() + "/Conversations/Media/Conversations Images/");
 406				newPicturesDirectory.getParentFile().mkdirs();
 407				if (oldPicturesDirectory.renameTo(newPicturesDirectory)) {
 408					Log.d(Config.LOGTAG,"moved "+oldPicturesDirectory.getAbsolutePath()+" to "+newPicturesDirectory.getAbsolutePath());
 409				}
 410			}
 411			if (oldFilesDirectory.exists() && oldFilesDirectory.isDirectory()) {
 412				newFilesDirectory.mkdirs();
 413				newVideosDirectory.mkdirs();
 414				for(File file : oldFilesDirectory.listFiles()) {
 415					if (file.getName().equals(".nomedia")) {
 416						if (file.delete()) {
 417							Log.d(Config.LOGTAG,"deleted nomedia file in "+oldFilesDirectory.getAbsolutePath());
 418						}
 419					} else if (file.isFile()) {
 420						final String name = file.getName();
 421						boolean isVideo = false;
 422						int start = name.lastIndexOf('.') + 1;
 423						if (start < name.length()) {
 424							String mime=  MimeUtils.guessMimeTypeFromExtension(name.substring(start));
 425							isVideo = mime != null && mime.startsWith("video/");
 426						}
 427						File dst = new File((isVideo ? newVideosDirectory : newFilesDirectory).getAbsolutePath()+"/"+file.getName());
 428						if (file.renameTo(dst)) {
 429							Log.d(Config.LOGTAG, "moved " + file + " to " + dst);
 430						}
 431					}
 432				}
 433			}
 434		}
 435	}
 436
 437	private static ContentValues createFingerprintStatusContentValues(FingerprintStatus.Trust trust, boolean active) {
 438		ContentValues values = new ContentValues();
 439		values.put(SQLiteAxolotlStore.TRUST,trust.toString());
 440		values.put(SQLiteAxolotlStore.ACTIVE,active ? 1 : 0);
 441		return values;
 442	}
 443
 444	private void canonicalizeJids(SQLiteDatabase db) {
 445		// migrate db to new, canonicalized JID domainpart representation
 446
 447		// Conversation table
 448		Cursor cursor = db.rawQuery("select * from " + Conversation.TABLENAME, new String[0]);
 449		while (cursor.moveToNext()) {
 450			String newJid;
 451			try {
 452				newJid = Jid.fromString(
 453						cursor.getString(cursor.getColumnIndex(Conversation.CONTACTJID))
 454				).toPreppedString();
 455			} catch (InvalidJidException ignored) {
 456				Log.e(Config.LOGTAG, "Failed to migrate Conversation CONTACTJID "
 457						+ cursor.getString(cursor.getColumnIndex(Conversation.CONTACTJID))
 458						+ ": " + ignored + ". Skipping...");
 459				continue;
 460			}
 461
 462			String updateArgs[] = {
 463					newJid,
 464					cursor.getString(cursor.getColumnIndex(Conversation.UUID)),
 465			};
 466			db.execSQL("update " + Conversation.TABLENAME
 467					+ " set " + Conversation.CONTACTJID + " = ? "
 468					+ " where " + Conversation.UUID + " = ?", updateArgs);
 469		}
 470		cursor.close();
 471
 472		// Contact table
 473		cursor = db.rawQuery("select * from " + Contact.TABLENAME, new String[0]);
 474		while (cursor.moveToNext()) {
 475			String newJid;
 476			try {
 477				newJid = Jid.fromString(
 478						cursor.getString(cursor.getColumnIndex(Contact.JID))
 479				).toPreppedString();
 480			} catch (InvalidJidException ignored) {
 481				Log.e(Config.LOGTAG, "Failed to migrate Contact JID "
 482						+ cursor.getString(cursor.getColumnIndex(Contact.JID))
 483						+ ": " + ignored + ". Skipping...");
 484				continue;
 485			}
 486
 487			String updateArgs[] = {
 488					newJid,
 489					cursor.getString(cursor.getColumnIndex(Contact.ACCOUNT)),
 490					cursor.getString(cursor.getColumnIndex(Contact.JID)),
 491			};
 492			db.execSQL("update " + Contact.TABLENAME
 493					+ " set " + Contact.JID + " = ? "
 494					+ " where " + Contact.ACCOUNT + " = ? "
 495					+ " AND " + Contact.JID + " = ?", updateArgs);
 496		}
 497		cursor.close();
 498
 499		// Account table
 500		cursor = db.rawQuery("select * from " + Account.TABLENAME, new String[0]);
 501		while (cursor.moveToNext()) {
 502			String newServer;
 503			try {
 504				newServer = Jid.fromParts(
 505						cursor.getString(cursor.getColumnIndex(Account.USERNAME)),
 506						cursor.getString(cursor.getColumnIndex(Account.SERVER)),
 507						"mobile"
 508				).getDomainpart();
 509			} catch (InvalidJidException ignored) {
 510				Log.e(Config.LOGTAG, "Failed to migrate Account SERVER "
 511						+ cursor.getString(cursor.getColumnIndex(Account.SERVER))
 512						+ ": " + ignored + ". Skipping...");
 513				continue;
 514			}
 515
 516			String updateArgs[] = {
 517					newServer,
 518					cursor.getString(cursor.getColumnIndex(Account.UUID)),
 519			};
 520			db.execSQL("update " + Account.TABLENAME
 521					+ " set " + Account.SERVER + " = ? "
 522					+ " where " + Account.UUID + " = ?", updateArgs);
 523		}
 524		cursor.close();
 525	}
 526
 527	public static synchronized DatabaseBackend getInstance(Context context) {
 528		if (instance == null) {
 529			instance = new DatabaseBackend(context);
 530		}
 531		return instance;
 532	}
 533
 534	public void createConversation(Conversation conversation) {
 535		SQLiteDatabase db = this.getWritableDatabase();
 536		db.insert(Conversation.TABLENAME, null, conversation.getContentValues());
 537	}
 538
 539	public void createMessage(Message message) {
 540		SQLiteDatabase db = this.getWritableDatabase();
 541		db.insert(Message.TABLENAME, null, message.getContentValues());
 542	}
 543
 544	public void createAccount(Account account) {
 545		SQLiteDatabase db = this.getWritableDatabase();
 546		db.insert(Account.TABLENAME, null, account.getContentValues());
 547	}
 548
 549	public void insertDiscoveryResult(ServiceDiscoveryResult result) {
 550		SQLiteDatabase db = this.getWritableDatabase();
 551		db.insert(ServiceDiscoveryResult.TABLENAME, null, result.getContentValues());
 552	}
 553
 554	public ServiceDiscoveryResult findDiscoveryResult(final String hash, final String ver) {
 555		SQLiteDatabase db = this.getReadableDatabase();
 556		String[] selectionArgs = {hash, ver};
 557		Cursor cursor = db.query(ServiceDiscoveryResult.TABLENAME, null,
 558				ServiceDiscoveryResult.HASH + "=? AND " + ServiceDiscoveryResult.VER + "=?",
 559				selectionArgs, null, null, null);
 560		if (cursor.getCount() == 0) {
 561			cursor.close();
 562			return null;
 563		}
 564		cursor.moveToFirst();
 565
 566		ServiceDiscoveryResult result = null;
 567		try {
 568			result = new ServiceDiscoveryResult(cursor);
 569		} catch (JSONException e) { /* result is still null */ }
 570
 571		cursor.close();
 572		return result;
 573	}
 574
 575	public void insertPresenceTemplate(PresenceTemplate template) {
 576		SQLiteDatabase db = this.getWritableDatabase();
 577		db.insert(PresenceTemplate.TABELNAME, null, template.getContentValues());
 578	}
 579
 580	public List<PresenceTemplate> getPresenceTemplates() {
 581		ArrayList<PresenceTemplate> templates = new ArrayList<>();
 582		SQLiteDatabase db = this.getReadableDatabase();
 583		Cursor cursor = db.query(PresenceTemplate.TABELNAME,null,null,null,null,null,PresenceTemplate.LAST_USED+" desc");
 584		while (cursor.moveToNext()) {
 585			templates.add(PresenceTemplate.fromCursor(cursor));
 586		}
 587		cursor.close();
 588		return templates;
 589	}
 590
 591	public void deletePresenceTemplate(PresenceTemplate template) {
 592		Log.d(Config.LOGTAG,"deleting presence template with uuid "+template.getUuid());
 593		SQLiteDatabase db = this.getWritableDatabase();
 594		String where = PresenceTemplate.UUID+"=?";
 595		String[] whereArgs = {template.getUuid()};
 596		db.delete(PresenceTemplate.TABELNAME,where,whereArgs);
 597	}
 598
 599	public CopyOnWriteArrayList<Conversation> getConversations(int status) {
 600		CopyOnWriteArrayList<Conversation> list = new CopyOnWriteArrayList<>();
 601		SQLiteDatabase db = this.getReadableDatabase();
 602		String[] selectionArgs = {Integer.toString(status)};
 603		Cursor cursor = db.rawQuery("select * from " + Conversation.TABLENAME
 604				+ " where " + Conversation.STATUS + " = ? order by "
 605				+ Conversation.CREATED + " desc", selectionArgs);
 606		while (cursor.moveToNext()) {
 607			list.add(Conversation.fromCursor(cursor));
 608		}
 609		cursor.close();
 610		return list;
 611	}
 612
 613	public ArrayList<Message> getMessages(Conversation conversations, int limit) {
 614		return getMessages(conversations, limit, -1);
 615	}
 616
 617	public ArrayList<Message> getMessages(Conversation conversation, int limit,
 618										  long timestamp) {
 619		ArrayList<Message> list = new ArrayList<>();
 620		SQLiteDatabase db = this.getReadableDatabase();
 621		Cursor cursor;
 622		if (timestamp == -1) {
 623			String[] selectionArgs = {conversation.getUuid()};
 624			cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 625					+ "=?", selectionArgs, null, null, Message.TIME_SENT
 626					+ " DESC", String.valueOf(limit));
 627		} else {
 628			String[] selectionArgs = {conversation.getUuid(),
 629					Long.toString(timestamp)};
 630			cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 631							+ "=? and " + Message.TIME_SENT + "<?", selectionArgs,
 632					null, null, Message.TIME_SENT + " DESC",
 633					String.valueOf(limit));
 634		}
 635		if (cursor.getCount() > 0) {
 636			cursor.moveToLast();
 637			do {
 638				Message message = Message.fromCursor(cursor);
 639				message.setConversation(conversation);
 640				list.add(message);
 641			} while (cursor.moveToPrevious());
 642		}
 643		cursor.close();
 644		return list;
 645	}
 646
 647	public Iterable<Message> getMessagesIterable(final Conversation conversation) {
 648		return new Iterable<Message>() {
 649			@Override
 650			public Iterator<Message> iterator() {
 651				class MessageIterator implements Iterator<Message> {
 652					SQLiteDatabase db = getReadableDatabase();
 653					String[] selectionArgs = {conversation.getUuid()};
 654					Cursor cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 655							+ "=?", selectionArgs, null, null, Message.TIME_SENT
 656							+ " ASC", null);
 657
 658					public MessageIterator() {
 659						cursor.moveToFirst();
 660					}
 661
 662					@Override
 663					public boolean hasNext() {
 664						return !cursor.isAfterLast();
 665					}
 666
 667					@Override
 668					public Message next() {
 669						Message message = Message.fromCursor(cursor);
 670						cursor.moveToNext();
 671						return message;
 672					}
 673
 674					@Override
 675					public void remove() {
 676						throw new UnsupportedOperationException();
 677					}
 678				}
 679				return new MessageIterator();
 680			}
 681		};
 682	}
 683
 684	public Conversation findConversation(final Account account, final Jid contactJid) {
 685		SQLiteDatabase db = this.getReadableDatabase();
 686		String[] selectionArgs = {account.getUuid(),
 687				contactJid.toBareJid().toPreppedString() + "/%",
 688				contactJid.toBareJid().toPreppedString()
 689		};
 690		Cursor cursor = db.query(Conversation.TABLENAME, null,
 691				Conversation.ACCOUNT + "=? AND (" + Conversation.CONTACTJID
 692						+ " like ? OR " + Conversation.CONTACTJID + "=?)", selectionArgs, null, null, null);
 693		if (cursor.getCount() == 0) {
 694			cursor.close();
 695			return null;
 696		}
 697		cursor.moveToFirst();
 698		Conversation conversation = Conversation.fromCursor(cursor);
 699		cursor.close();
 700		return conversation;
 701	}
 702
 703	public void updateConversation(final Conversation conversation) {
 704		final SQLiteDatabase db = this.getWritableDatabase();
 705		final String[] args = {conversation.getUuid()};
 706		db.update(Conversation.TABLENAME, conversation.getContentValues(),
 707				Conversation.UUID + "=?", args);
 708	}
 709
 710	public List<Account> getAccounts() {
 711		SQLiteDatabase db = this.getReadableDatabase();
 712		return getAccounts(db);
 713	}
 714
 715	private List<Account> getAccounts(SQLiteDatabase db) {
 716		List<Account> list = new ArrayList<>();
 717		Cursor cursor = db.query(Account.TABLENAME, null, null, null, null,
 718				null, null);
 719		while (cursor.moveToNext()) {
 720			list.add(Account.fromCursor(cursor));
 721		}
 722		cursor.close();
 723		return list;
 724	}
 725
 726	public boolean updateAccount(Account account) {
 727		SQLiteDatabase db = this.getWritableDatabase();
 728		String[] args = {account.getUuid()};
 729		final int rows = db.update(Account.TABLENAME, account.getContentValues(), Account.UUID + "=?", args);
 730		return rows == 1;
 731	}
 732
 733	public boolean deleteAccount(Account account) {
 734		SQLiteDatabase db = this.getWritableDatabase();
 735		String[] args = {account.getUuid()};
 736		final int rows = db.delete(Account.TABLENAME, Account.UUID + "=?", args);
 737		return rows == 1;
 738	}
 739
 740	public boolean hasEnabledAccounts() {
 741		SQLiteDatabase db = this.getReadableDatabase();
 742		Cursor cursor = db.rawQuery("select count(" + Account.UUID + ")  from "
 743				+ Account.TABLENAME + " where not options & (1 <<1)", null);
 744		try {
 745			cursor.moveToFirst();
 746			int count = cursor.getInt(0);
 747			return (count > 0);
 748		} catch (SQLiteCantOpenDatabaseException e) {
 749			return true; // better safe than sorry
 750		} catch (RuntimeException e) {
 751			return true; // better safe than sorry
 752		} finally {
 753			if (cursor != null) {
 754				cursor.close();
 755			}
 756		}
 757	}
 758
 759	@Override
 760	public SQLiteDatabase getWritableDatabase() {
 761		SQLiteDatabase db = super.getWritableDatabase();
 762		db.execSQL("PRAGMA foreign_keys=ON;");
 763		return db;
 764	}
 765
 766	public void updateMessage(Message message) {
 767		SQLiteDatabase db = this.getWritableDatabase();
 768		String[] args = {message.getUuid()};
 769		db.update(Message.TABLENAME, message.getContentValues(), Message.UUID
 770				+ "=?", args);
 771	}
 772
 773	public void updateMessage(Message message, String uuid) {
 774		SQLiteDatabase db = this.getWritableDatabase();
 775		String[] args = {uuid};
 776		db.update(Message.TABLENAME, message.getContentValues(), Message.UUID
 777				+ "=?", args);
 778	}
 779
 780	public void readRoster(Roster roster) {
 781		SQLiteDatabase db = this.getReadableDatabase();
 782		Cursor cursor;
 783		String args[] = {roster.getAccount().getUuid()};
 784		cursor = db.query(Contact.TABLENAME, null, Contact.ACCOUNT + "=?", args, null, null, null);
 785		while (cursor.moveToNext()) {
 786			roster.initContact(Contact.fromCursor(cursor));
 787		}
 788		cursor.close();
 789	}
 790
 791	public void writeRoster(final Roster roster) {
 792		final Account account = roster.getAccount();
 793		final SQLiteDatabase db = this.getWritableDatabase();
 794		db.beginTransaction();
 795		for (Contact contact : roster.getContacts()) {
 796			if (contact.getOption(Contact.Options.IN_ROSTER)) {
 797				db.insert(Contact.TABLENAME, null, contact.getContentValues());
 798			} else {
 799				String where = Contact.ACCOUNT + "=? AND " + Contact.JID + "=?";
 800				String[] whereArgs = {account.getUuid(), contact.getJid().toPreppedString()};
 801				db.delete(Contact.TABLENAME, where, whereArgs);
 802			}
 803		}
 804		db.setTransactionSuccessful();
 805		db.endTransaction();
 806		account.setRosterVersion(roster.getVersion());
 807		updateAccount(account);
 808	}
 809
 810	public void deleteMessagesInConversation(Conversation conversation) {
 811		SQLiteDatabase db = this.getWritableDatabase();
 812		String[] args = {conversation.getUuid()};
 813		db.delete(Message.TABLENAME, Message.CONVERSATION + "=?", args);
 814	}
 815
 816	public boolean expireOldMessages(long timestamp) {
 817		String where = Message.TIME_SENT+"<?";
 818		String[] whereArgs = {String.valueOf(timestamp)};
 819		SQLiteDatabase db = this.getReadableDatabase();
 820		return db.delete(Message.TABLENAME,where,whereArgs) > 0;
 821	}
 822
 823	public Pair<Long, String> getLastMessageReceived(Account account) {
 824		Cursor cursor = null;
 825		try {
 826			SQLiteDatabase db = this.getReadableDatabase();
 827			String sql = "select messages.timeSent,messages.serverMsgId from accounts join conversations on accounts.uuid=conversations.accountUuid join messages on conversations.uuid=messages.conversationUuid where accounts.uuid=? and (messages.status=0 or messages.carbon=1 or messages.serverMsgId not null) order by messages.timesent desc limit 1";
 828			String[] args = {account.getUuid()};
 829			cursor = db.rawQuery(sql, args);
 830			if (cursor.getCount() == 0) {
 831				return null;
 832			} else {
 833				cursor.moveToFirst();
 834				return new Pair<>(cursor.getLong(0), cursor.getString(1));
 835			}
 836		} catch (Exception e) {
 837			return null;
 838		} finally {
 839			if (cursor != null) {
 840				cursor.close();
 841			}
 842		}
 843	}
 844
 845	public long getLastTimeFingerprintUsed(Account account, String fingerprint) {
 846		String SQL = "select messages.timeSent from accounts join conversations on accounts.uuid=conversations.accountUuid join messages on conversations.uuid=messages.conversationUuid where accounts.uuid=? and messages.axolotl_fingerprint=? order by messages.timesent desc limit 1";
 847		String[] args = {account.getUuid(), fingerprint};
 848		Cursor cursor = getReadableDatabase().rawQuery(SQL,args);
 849		long time;
 850		if (cursor.moveToFirst()) {
 851			time = cursor.getLong(0);
 852		} else {
 853			time = 0;
 854		}
 855		cursor.close();
 856		return time;
 857	}
 858
 859	public Pair<Long,String> getLastClearDate(Account account) {
 860		SQLiteDatabase db = this.getReadableDatabase();
 861		String[] columns = {Conversation.ATTRIBUTES};
 862		String selection = Conversation.ACCOUNT + "=?";
 863		String[] args = {account.getUuid()};
 864		Cursor cursor = db.query(Conversation.TABLENAME,columns,selection,args,null,null,null);
 865		long maxClearDate = 0;
 866		while (cursor.moveToNext()) {
 867			try {
 868				final JSONObject jsonObject = new JSONObject(cursor.getString(0));
 869				maxClearDate = Math.max(maxClearDate, jsonObject.getLong(Conversation.ATTRIBUTE_LAST_CLEAR_HISTORY));
 870			} catch (Exception e) {
 871				//ignored
 872			}
 873		}
 874		cursor.close();
 875		return new Pair<>(maxClearDate,null);
 876	}
 877
 878	private Cursor getCursorForSession(Account account, AxolotlAddress contact) {
 879		final SQLiteDatabase db = this.getReadableDatabase();
 880		String[] selectionArgs = {account.getUuid(),
 881				contact.getName(),
 882				Integer.toString(contact.getDeviceId())};
 883		return db.query(SQLiteAxolotlStore.SESSION_TABLENAME,
 884				null,
 885				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 886						+ SQLiteAxolotlStore.NAME + " = ? AND "
 887						+ SQLiteAxolotlStore.DEVICE_ID + " = ? ",
 888				selectionArgs,
 889				null, null, null);
 890	}
 891
 892	public SessionRecord loadSession(Account account, AxolotlAddress contact) {
 893		SessionRecord session = null;
 894		Cursor cursor = getCursorForSession(account, contact);
 895		if (cursor.getCount() != 0) {
 896			cursor.moveToFirst();
 897			try {
 898				session = new SessionRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
 899			} catch (IOException e) {
 900				cursor.close();
 901				throw new AssertionError(e);
 902			}
 903		}
 904		cursor.close();
 905		return session;
 906	}
 907
 908	public List<Integer> getSubDeviceSessions(Account account, AxolotlAddress contact) {
 909		final SQLiteDatabase db = this.getReadableDatabase();
 910		return getSubDeviceSessions(db, account, contact);
 911	}
 912
 913	private List<Integer> getSubDeviceSessions(SQLiteDatabase db, Account account, AxolotlAddress contact) {
 914		List<Integer> devices = new ArrayList<>();
 915		String[] columns = {SQLiteAxolotlStore.DEVICE_ID};
 916		String[] selectionArgs = {account.getUuid(),
 917				contact.getName()};
 918		Cursor cursor = db.query(SQLiteAxolotlStore.SESSION_TABLENAME,
 919				columns,
 920				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 921						+ SQLiteAxolotlStore.NAME + " = ?",
 922				selectionArgs,
 923				null, null, null);
 924
 925		while (cursor.moveToNext()) {
 926			devices.add(cursor.getInt(
 927					cursor.getColumnIndex(SQLiteAxolotlStore.DEVICE_ID)));
 928		}
 929
 930		cursor.close();
 931		return devices;
 932	}
 933
 934	public boolean containsSession(Account account, AxolotlAddress contact) {
 935		Cursor cursor = getCursorForSession(account, contact);
 936		int count = cursor.getCount();
 937		cursor.close();
 938		return count != 0;
 939	}
 940
 941	public void storeSession(Account account, AxolotlAddress contact, SessionRecord session) {
 942		SQLiteDatabase db = this.getWritableDatabase();
 943		ContentValues values = new ContentValues();
 944		values.put(SQLiteAxolotlStore.NAME, contact.getName());
 945		values.put(SQLiteAxolotlStore.DEVICE_ID, contact.getDeviceId());
 946		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(session.serialize(), Base64.DEFAULT));
 947		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
 948		db.insert(SQLiteAxolotlStore.SESSION_TABLENAME, null, values);
 949	}
 950
 951	public void deleteSession(Account account, AxolotlAddress contact) {
 952		SQLiteDatabase db = this.getWritableDatabase();
 953		deleteSession(db, account, contact);
 954	}
 955
 956	private void deleteSession(SQLiteDatabase db, Account account, AxolotlAddress contact) {
 957		String[] args = {account.getUuid(),
 958				contact.getName(),
 959				Integer.toString(contact.getDeviceId())};
 960		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
 961				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 962						+ SQLiteAxolotlStore.NAME + " = ? AND "
 963						+ SQLiteAxolotlStore.DEVICE_ID + " = ? ",
 964				args);
 965	}
 966
 967	public void deleteAllSessions(Account account, AxolotlAddress contact) {
 968		SQLiteDatabase db = this.getWritableDatabase();
 969		String[] args = {account.getUuid(), contact.getName()};
 970		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
 971				SQLiteAxolotlStore.ACCOUNT + "=? AND "
 972						+ SQLiteAxolotlStore.NAME + " = ?",
 973				args);
 974	}
 975
 976	private Cursor getCursorForPreKey(Account account, int preKeyId) {
 977		SQLiteDatabase db = this.getReadableDatabase();
 978		String[] columns = {SQLiteAxolotlStore.KEY};
 979		String[] selectionArgs = {account.getUuid(), Integer.toString(preKeyId)};
 980		Cursor cursor = db.query(SQLiteAxolotlStore.PREKEY_TABLENAME,
 981				columns,
 982				SQLiteAxolotlStore.ACCOUNT + "=? AND "
 983						+ SQLiteAxolotlStore.ID + "=?",
 984				selectionArgs,
 985				null, null, null);
 986
 987		return cursor;
 988	}
 989
 990	public PreKeyRecord loadPreKey(Account account, int preKeyId) {
 991		PreKeyRecord record = null;
 992		Cursor cursor = getCursorForPreKey(account, preKeyId);
 993		if (cursor.getCount() != 0) {
 994			cursor.moveToFirst();
 995			try {
 996				record = new PreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
 997			} catch (IOException e) {
 998				throw new AssertionError(e);
 999			}
1000		}
1001		cursor.close();
1002		return record;
1003	}
1004
1005	public boolean containsPreKey(Account account, int preKeyId) {
1006		Cursor cursor = getCursorForPreKey(account, preKeyId);
1007		int count = cursor.getCount();
1008		cursor.close();
1009		return count != 0;
1010	}
1011
1012	public void storePreKey(Account account, PreKeyRecord record) {
1013		SQLiteDatabase db = this.getWritableDatabase();
1014		ContentValues values = new ContentValues();
1015		values.put(SQLiteAxolotlStore.ID, record.getId());
1016		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(record.serialize(), Base64.DEFAULT));
1017		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1018		db.insert(SQLiteAxolotlStore.PREKEY_TABLENAME, null, values);
1019	}
1020
1021	public void deletePreKey(Account account, int preKeyId) {
1022		SQLiteDatabase db = this.getWritableDatabase();
1023		String[] args = {account.getUuid(), Integer.toString(preKeyId)};
1024		db.delete(SQLiteAxolotlStore.PREKEY_TABLENAME,
1025				SQLiteAxolotlStore.ACCOUNT + "=? AND "
1026						+ SQLiteAxolotlStore.ID + "=?",
1027				args);
1028	}
1029
1030	private Cursor getCursorForSignedPreKey(Account account, int signedPreKeyId) {
1031		SQLiteDatabase db = this.getReadableDatabase();
1032		String[] columns = {SQLiteAxolotlStore.KEY};
1033		String[] selectionArgs = {account.getUuid(), Integer.toString(signedPreKeyId)};
1034		Cursor cursor = db.query(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1035				columns,
1036				SQLiteAxolotlStore.ACCOUNT + "=? AND " + SQLiteAxolotlStore.ID + "=?",
1037				selectionArgs,
1038				null, null, null);
1039
1040		return cursor;
1041	}
1042
1043	public SignedPreKeyRecord loadSignedPreKey(Account account, int signedPreKeyId) {
1044		SignedPreKeyRecord record = null;
1045		Cursor cursor = getCursorForSignedPreKey(account, signedPreKeyId);
1046		if (cursor.getCount() != 0) {
1047			cursor.moveToFirst();
1048			try {
1049				record = new SignedPreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
1050			} catch (IOException e) {
1051				throw new AssertionError(e);
1052			}
1053		}
1054		cursor.close();
1055		return record;
1056	}
1057
1058	public List<SignedPreKeyRecord> loadSignedPreKeys(Account account) {
1059		List<SignedPreKeyRecord> prekeys = new ArrayList<>();
1060		SQLiteDatabase db = this.getReadableDatabase();
1061		String[] columns = {SQLiteAxolotlStore.KEY};
1062		String[] selectionArgs = {account.getUuid()};
1063		Cursor cursor = db.query(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1064				columns,
1065				SQLiteAxolotlStore.ACCOUNT + "=?",
1066				selectionArgs,
1067				null, null, null);
1068
1069		while (cursor.moveToNext()) {
1070			try {
1071				prekeys.add(new SignedPreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT)));
1072			} catch (IOException ignored) {
1073			}
1074		}
1075		cursor.close();
1076		return prekeys;
1077	}
1078
1079	public boolean containsSignedPreKey(Account account, int signedPreKeyId) {
1080		Cursor cursor = getCursorForPreKey(account, signedPreKeyId);
1081		int count = cursor.getCount();
1082		cursor.close();
1083		return count != 0;
1084	}
1085
1086	public void storeSignedPreKey(Account account, SignedPreKeyRecord record) {
1087		SQLiteDatabase db = this.getWritableDatabase();
1088		ContentValues values = new ContentValues();
1089		values.put(SQLiteAxolotlStore.ID, record.getId());
1090		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(record.serialize(), Base64.DEFAULT));
1091		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1092		db.insert(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME, null, values);
1093	}
1094
1095	public void deleteSignedPreKey(Account account, int signedPreKeyId) {
1096		SQLiteDatabase db = this.getWritableDatabase();
1097		String[] args = {account.getUuid(), Integer.toString(signedPreKeyId)};
1098		db.delete(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1099				SQLiteAxolotlStore.ACCOUNT + "=? AND "
1100						+ SQLiteAxolotlStore.ID + "=?",
1101				args);
1102	}
1103
1104	private Cursor getIdentityKeyCursor(Account account, String name, boolean own) {
1105		final SQLiteDatabase db = this.getReadableDatabase();
1106		return getIdentityKeyCursor(db, account, name, own);
1107	}
1108
1109	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String name, boolean own) {
1110		return getIdentityKeyCursor(db, account, name, own, null);
1111	}
1112
1113	private Cursor getIdentityKeyCursor(Account account, String fingerprint) {
1114		final SQLiteDatabase db = this.getReadableDatabase();
1115		return getIdentityKeyCursor(db, account, fingerprint);
1116	}
1117
1118	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String fingerprint) {
1119		return getIdentityKeyCursor(db, account, null, null, fingerprint);
1120	}
1121
1122	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String name, Boolean own, String fingerprint) {
1123		String[] columns = {SQLiteAxolotlStore.TRUST,
1124				SQLiteAxolotlStore.ACTIVE,
1125				SQLiteAxolotlStore.LAST_ACTIVATION,
1126				SQLiteAxolotlStore.KEY};
1127		ArrayList<String> selectionArgs = new ArrayList<>(4);
1128		selectionArgs.add(account.getUuid());
1129		String selectionString = SQLiteAxolotlStore.ACCOUNT + " = ?";
1130		if (name != null) {
1131			selectionArgs.add(name);
1132			selectionString += " AND " + SQLiteAxolotlStore.NAME + " = ?";
1133		}
1134		if (fingerprint != null) {
1135			selectionArgs.add(fingerprint);
1136			selectionString += " AND " + SQLiteAxolotlStore.FINGERPRINT + " = ?";
1137		}
1138		if (own != null) {
1139			selectionArgs.add(own ? "1" : "0");
1140			selectionString += " AND " + SQLiteAxolotlStore.OWN + " = ?";
1141		}
1142		Cursor cursor = db.query(SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1143				columns,
1144				selectionString,
1145				selectionArgs.toArray(new String[selectionArgs.size()]),
1146				null, null, null);
1147
1148		return cursor;
1149	}
1150
1151	public IdentityKeyPair loadOwnIdentityKeyPair(Account account) {
1152		SQLiteDatabase db = getReadableDatabase();
1153		return loadOwnIdentityKeyPair(db, account);
1154	}
1155
1156	private IdentityKeyPair loadOwnIdentityKeyPair(SQLiteDatabase db, Account account) {
1157		String name = account.getJid().toBareJid().toPreppedString();
1158		IdentityKeyPair identityKeyPair = null;
1159		Cursor cursor = getIdentityKeyCursor(db, account, name, true);
1160		if (cursor.getCount() != 0) {
1161			cursor.moveToFirst();
1162			try {
1163				identityKeyPair = new IdentityKeyPair(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
1164			} catch (InvalidKeyException e) {
1165				Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Encountered invalid IdentityKey in database for account" + account.getJid().toBareJid() + ", address: " + name);
1166			}
1167		}
1168		cursor.close();
1169
1170		return identityKeyPair;
1171	}
1172
1173	public Set<IdentityKey> loadIdentityKeys(Account account, String name) {
1174		return loadIdentityKeys(account, name, null);
1175	}
1176
1177	public Set<IdentityKey> loadIdentityKeys(Account account, String name, FingerprintStatus status) {
1178		Set<IdentityKey> identityKeys = new HashSet<>();
1179		Cursor cursor = getIdentityKeyCursor(account, name, false);
1180
1181		while (cursor.moveToNext()) {
1182			if (status != null && !FingerprintStatus.fromCursor(cursor).equals(status)) {
1183				continue;
1184			}
1185			try {
1186				String key = cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY));
1187				if (key != null) {
1188					identityKeys.add(new IdentityKey(Base64.decode(key, Base64.DEFAULT), 0));
1189				} else {
1190					Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Missing key (possibly preverified) in database for account" + account.getJid().toBareJid() + ", address: " + name);
1191				}
1192			} catch (InvalidKeyException e) {
1193				Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Encountered invalid IdentityKey in database for account" + account.getJid().toBareJid() + ", address: " + name);
1194			}
1195		}
1196		cursor.close();
1197
1198		return identityKeys;
1199	}
1200
1201	public long numTrustedKeys(Account account, String name) {
1202		SQLiteDatabase db = getReadableDatabase();
1203		String[] args = {
1204				account.getUuid(),
1205				name,
1206				FingerprintStatus.Trust.TRUSTED.toString(),
1207				FingerprintStatus.Trust.VERIFIED.toString(),
1208				FingerprintStatus.Trust.VERIFIED_X509.toString()
1209		};
1210		return DatabaseUtils.queryNumEntries(db, SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1211				SQLiteAxolotlStore.ACCOUNT + " = ?"
1212						+ " AND " + SQLiteAxolotlStore.NAME + " = ?"
1213						+ " AND (" + SQLiteAxolotlStore.TRUST + " = ? OR " + SQLiteAxolotlStore.TRUST + " = ? OR " +SQLiteAxolotlStore.TRUST +" = ?)"
1214						+ " AND " +SQLiteAxolotlStore.ACTIVE + " > 0",
1215				args
1216		);
1217	}
1218
1219	private void storeIdentityKey(Account account, String name, boolean own, String fingerprint, String base64Serialized, FingerprintStatus status) {
1220		SQLiteDatabase db = this.getWritableDatabase();
1221		ContentValues values = new ContentValues();
1222		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1223		values.put(SQLiteAxolotlStore.NAME, name);
1224		values.put(SQLiteAxolotlStore.OWN, own ? 1 : 0);
1225		values.put(SQLiteAxolotlStore.FINGERPRINT, fingerprint);
1226		values.put(SQLiteAxolotlStore.KEY, base64Serialized);
1227		values.putAll(status.toContentValues());
1228		String where = SQLiteAxolotlStore.ACCOUNT+"=? AND "+SQLiteAxolotlStore.NAME+"=? AND "+SQLiteAxolotlStore.FINGERPRINT+" =?";
1229		String[] whereArgs = {account.getUuid(),name,fingerprint};
1230		int rows = db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME,values,where,whereArgs);
1231		if (rows == 0) {
1232			db.insert(SQLiteAxolotlStore.IDENTITIES_TABLENAME, null, values);
1233		}
1234	}
1235
1236	public void storePreVerification(Account account, String name, String fingerprint, FingerprintStatus status) {
1237		SQLiteDatabase db = this.getWritableDatabase();
1238		ContentValues values = new ContentValues();
1239		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1240		values.put(SQLiteAxolotlStore.NAME, name);
1241		values.put(SQLiteAxolotlStore.OWN, 0);
1242		values.put(SQLiteAxolotlStore.FINGERPRINT, fingerprint);
1243		values.putAll(status.toContentValues());
1244		db.insert(SQLiteAxolotlStore.IDENTITIES_TABLENAME, null, values);
1245	}
1246
1247	public FingerprintStatus getFingerprintStatus(Account account, String fingerprint) {
1248		Cursor cursor = getIdentityKeyCursor(account, fingerprint);
1249		final FingerprintStatus status;
1250		if (cursor.getCount() > 0) {
1251			cursor.moveToFirst();
1252			status = FingerprintStatus.fromCursor(cursor);
1253		} else {
1254			status = null;
1255		}
1256		cursor.close();
1257		return status;
1258	}
1259
1260	public boolean setIdentityKeyTrust(Account account, String fingerprint, FingerprintStatus fingerprintStatus) {
1261		SQLiteDatabase db = this.getWritableDatabase();
1262		return setIdentityKeyTrust(db, account, fingerprint, fingerprintStatus);
1263	}
1264
1265	private boolean setIdentityKeyTrust(SQLiteDatabase db, Account account, String fingerprint, FingerprintStatus status) {
1266		String[] selectionArgs = {
1267				account.getUuid(),
1268				fingerprint
1269		};
1270		int rows = db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME, status.toContentValues(),
1271				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
1272						+ SQLiteAxolotlStore.FINGERPRINT + " = ? ",
1273				selectionArgs);
1274		return rows == 1;
1275	}
1276
1277	public boolean setIdentityKeyCertificate(Account account, String fingerprint, X509Certificate x509Certificate) {
1278		SQLiteDatabase db = this.getWritableDatabase();
1279		String[] selectionArgs = {
1280				account.getUuid(),
1281				fingerprint
1282		};
1283		try {
1284			ContentValues values = new ContentValues();
1285			values.put(SQLiteAxolotlStore.CERTIFICATE, x509Certificate.getEncoded());
1286			return db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME, values,
1287					SQLiteAxolotlStore.ACCOUNT + " = ? AND "
1288							+ SQLiteAxolotlStore.FINGERPRINT + " = ? ",
1289					selectionArgs) == 1;
1290		} catch (CertificateEncodingException e) {
1291			Log.d(Config.LOGTAG, "could not encode certificate");
1292			return false;
1293		}
1294	}
1295
1296	public X509Certificate getIdentityKeyCertifcate(Account account, String fingerprint) {
1297		SQLiteDatabase db = this.getReadableDatabase();
1298		String[] selectionArgs = {
1299				account.getUuid(),
1300				fingerprint
1301		};
1302		String[] colums = {SQLiteAxolotlStore.CERTIFICATE};
1303		String selection = SQLiteAxolotlStore.ACCOUNT + " = ? AND " + SQLiteAxolotlStore.FINGERPRINT + " = ? ";
1304		Cursor cursor = db.query(SQLiteAxolotlStore.IDENTITIES_TABLENAME, colums, selection, selectionArgs, null, null, null);
1305		if (cursor.getCount() < 1) {
1306			return null;
1307		} else {
1308			cursor.moveToFirst();
1309			byte[] certificate = cursor.getBlob(cursor.getColumnIndex(SQLiteAxolotlStore.CERTIFICATE));
1310			cursor.close();
1311			if (certificate == null || certificate.length == 0) {
1312				return null;
1313			}
1314			try {
1315				CertificateFactory certificateFactory = CertificateFactory.getInstance("X.509");
1316				return (X509Certificate) certificateFactory.generateCertificate(new ByteArrayInputStream(certificate));
1317			} catch (CertificateException e) {
1318				Log.d(Config.LOGTAG,"certificate exception "+e.getMessage());
1319				return null;
1320			}
1321		}
1322	}
1323
1324	public void storeIdentityKey(Account account, String name, IdentityKey identityKey, FingerprintStatus status) {
1325		storeIdentityKey(account, name, false, identityKey.getFingerprint().replaceAll("\\s", ""), Base64.encodeToString(identityKey.serialize(), Base64.DEFAULT), status);
1326	}
1327
1328	public void storeOwnIdentityKeyPair(Account account, IdentityKeyPair identityKeyPair) {
1329		storeIdentityKey(account, account.getJid().toBareJid().toPreppedString(), true, identityKeyPair.getPublicKey().getFingerprint().replaceAll("\\s", ""), Base64.encodeToString(identityKeyPair.serialize(), Base64.DEFAULT), FingerprintStatus.createActiveVerified(false));
1330	}
1331
1332
1333	public void recreateAxolotlDb(SQLiteDatabase db) {
1334		Log.d(Config.LOGTAG, AxolotlService.LOGPREFIX + " : " + ">>> (RE)CREATING AXOLOTL DATABASE <<<");
1335		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.SESSION_TABLENAME);
1336		db.execSQL(CREATE_SESSIONS_STATEMENT);
1337		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.PREKEY_TABLENAME);
1338		db.execSQL(CREATE_PREKEYS_STATEMENT);
1339		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME);
1340		db.execSQL(CREATE_SIGNED_PREKEYS_STATEMENT);
1341		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.IDENTITIES_TABLENAME);
1342		db.execSQL(CREATE_IDENTITIES_STATEMENT);
1343	}
1344
1345	public void wipeAxolotlDb(Account account) {
1346		String accountName = account.getUuid();
1347		Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + ">>> WIPING AXOLOTL DATABASE FOR ACCOUNT " + accountName + " <<<");
1348		SQLiteDatabase db = this.getWritableDatabase();
1349		String[] deleteArgs = {
1350				accountName
1351		};
1352		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
1353				SQLiteAxolotlStore.ACCOUNT + " = ?",
1354				deleteArgs);
1355		db.delete(SQLiteAxolotlStore.PREKEY_TABLENAME,
1356				SQLiteAxolotlStore.ACCOUNT + " = ?",
1357				deleteArgs);
1358		db.delete(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1359				SQLiteAxolotlStore.ACCOUNT + " = ?",
1360				deleteArgs);
1361		db.delete(SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1362				SQLiteAxolotlStore.ACCOUNT + " = ?",
1363				deleteArgs);
1364	}
1365
1366	public boolean startTimeCountExceedsThreshold() {
1367		SQLiteDatabase db = this.getWritableDatabase();
1368		long cleanBeforeTimestamp = System.currentTimeMillis() - Config.FREQUENT_RESTARTS_DETECTION_WINDOW;
1369		db.execSQL("delete from "+START_TIMES_TABLE+" where timestamp < "+cleanBeforeTimestamp);
1370		ContentValues values = new ContentValues();
1371		values.put("timestamp",System.currentTimeMillis());
1372		db.insert(START_TIMES_TABLE,null,values);
1373		String[] columns = new String[]{"count(timestamp)"};
1374		Cursor cursor = db.query(START_TIMES_TABLE,columns,null,null,null,null,null);
1375		int count;
1376		if (cursor.moveToFirst()) {
1377			count = cursor.getInt(0);
1378		} else {
1379			count = 0;
1380		}
1381		cursor.close();
1382		Log.d(Config.LOGTAG,"start time counter reached "+count);
1383		return count >= Config.FREQUENT_RESTARTS_THRESHOLD;
1384	}
1385
1386	public void clearStartTimeCounter(boolean justOne) {
1387		SQLiteDatabase db = this.getWritableDatabase();
1388		if (justOne) {
1389			db.execSQL("delete from "+START_TIMES_TABLE+" where timestamp in (select timestamp from "+START_TIMES_TABLE+" order by timestamp desc limit 1)");
1390			Log.d(Config.LOGTAG,"do not count start up after being swiped away");
1391		} else {
1392			Log.d(Config.LOGTAG,"resetting start time counter");
1393			db.execSQL("delete from " + START_TIMES_TABLE);
1394		}
1395	}
1396}