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