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