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