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