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				final File[] files = oldFilesDirectory.listFiles();
 415				if (files == null) {
 416					return;
 417				}
 418				for(File file : files) {
 419					if (file.getName().equals(".nomedia")) {
 420						if (file.delete()) {
 421							Log.d(Config.LOGTAG,"deleted nomedia file in "+oldFilesDirectory.getAbsolutePath());
 422						}
 423					} else if (file.isFile()) {
 424						final String name = file.getName();
 425						boolean isVideo = false;
 426						int start = name.lastIndexOf('.') + 1;
 427						if (start < name.length()) {
 428							String mime=  MimeUtils.guessMimeTypeFromExtension(name.substring(start));
 429							isVideo = mime != null && mime.startsWith("video/");
 430						}
 431						File dst = new File((isVideo ? newVideosDirectory : newFilesDirectory).getAbsolutePath()+"/"+file.getName());
 432						if (file.renameTo(dst)) {
 433							Log.d(Config.LOGTAG, "moved " + file + " to " + dst);
 434						}
 435					}
 436				}
 437			}
 438		}
 439	}
 440
 441	private static ContentValues createFingerprintStatusContentValues(FingerprintStatus.Trust trust, boolean active) {
 442		ContentValues values = new ContentValues();
 443		values.put(SQLiteAxolotlStore.TRUST,trust.toString());
 444		values.put(SQLiteAxolotlStore.ACTIVE,active ? 1 : 0);
 445		return values;
 446	}
 447
 448	private void canonicalizeJids(SQLiteDatabase db) {
 449		// migrate db to new, canonicalized JID domainpart representation
 450
 451		// Conversation table
 452		Cursor cursor = db.rawQuery("select * from " + Conversation.TABLENAME, new String[0]);
 453		while (cursor.moveToNext()) {
 454			String newJid;
 455			try {
 456				newJid = Jid.fromString(
 457						cursor.getString(cursor.getColumnIndex(Conversation.CONTACTJID))
 458				).toPreppedString();
 459			} catch (InvalidJidException ignored) {
 460				Log.e(Config.LOGTAG, "Failed to migrate Conversation CONTACTJID "
 461						+ cursor.getString(cursor.getColumnIndex(Conversation.CONTACTJID))
 462						+ ": " + ignored + ". Skipping...");
 463				continue;
 464			}
 465
 466			String updateArgs[] = {
 467					newJid,
 468					cursor.getString(cursor.getColumnIndex(Conversation.UUID)),
 469			};
 470			db.execSQL("update " + Conversation.TABLENAME
 471					+ " set " + Conversation.CONTACTJID + " = ? "
 472					+ " where " + Conversation.UUID + " = ?", updateArgs);
 473		}
 474		cursor.close();
 475
 476		// Contact table
 477		cursor = db.rawQuery("select * from " + Contact.TABLENAME, new String[0]);
 478		while (cursor.moveToNext()) {
 479			String newJid;
 480			try {
 481				newJid = Jid.fromString(
 482						cursor.getString(cursor.getColumnIndex(Contact.JID))
 483				).toPreppedString();
 484			} catch (InvalidJidException ignored) {
 485				Log.e(Config.LOGTAG, "Failed to migrate Contact JID "
 486						+ cursor.getString(cursor.getColumnIndex(Contact.JID))
 487						+ ": " + ignored + ". Skipping...");
 488				continue;
 489			}
 490
 491			String updateArgs[] = {
 492					newJid,
 493					cursor.getString(cursor.getColumnIndex(Contact.ACCOUNT)),
 494					cursor.getString(cursor.getColumnIndex(Contact.JID)),
 495			};
 496			db.execSQL("update " + Contact.TABLENAME
 497					+ " set " + Contact.JID + " = ? "
 498					+ " where " + Contact.ACCOUNT + " = ? "
 499					+ " AND " + Contact.JID + " = ?", updateArgs);
 500		}
 501		cursor.close();
 502
 503		// Account table
 504		cursor = db.rawQuery("select * from " + Account.TABLENAME, new String[0]);
 505		while (cursor.moveToNext()) {
 506			String newServer;
 507			try {
 508				newServer = Jid.fromParts(
 509						cursor.getString(cursor.getColumnIndex(Account.USERNAME)),
 510						cursor.getString(cursor.getColumnIndex(Account.SERVER)),
 511						"mobile"
 512				).getDomainpart();
 513			} catch (InvalidJidException ignored) {
 514				Log.e(Config.LOGTAG, "Failed to migrate Account SERVER "
 515						+ cursor.getString(cursor.getColumnIndex(Account.SERVER))
 516						+ ": " + ignored + ". Skipping...");
 517				continue;
 518			}
 519
 520			String updateArgs[] = {
 521					newServer,
 522					cursor.getString(cursor.getColumnIndex(Account.UUID)),
 523			};
 524			db.execSQL("update " + Account.TABLENAME
 525					+ " set " + Account.SERVER + " = ? "
 526					+ " where " + Account.UUID + " = ?", updateArgs);
 527		}
 528		cursor.close();
 529	}
 530
 531	public static synchronized DatabaseBackend getInstance(Context context) {
 532		if (instance == null) {
 533			instance = new DatabaseBackend(context);
 534		}
 535		return instance;
 536	}
 537
 538	public void createConversation(Conversation conversation) {
 539		SQLiteDatabase db = this.getWritableDatabase();
 540		db.insert(Conversation.TABLENAME, null, conversation.getContentValues());
 541	}
 542
 543	public void createMessage(Message message) {
 544		SQLiteDatabase db = this.getWritableDatabase();
 545		db.insert(Message.TABLENAME, null, message.getContentValues());
 546	}
 547
 548	public void createAccount(Account account) {
 549		SQLiteDatabase db = this.getWritableDatabase();
 550		db.insert(Account.TABLENAME, null, account.getContentValues());
 551	}
 552
 553	public void insertDiscoveryResult(ServiceDiscoveryResult result) {
 554		SQLiteDatabase db = this.getWritableDatabase();
 555		db.insert(ServiceDiscoveryResult.TABLENAME, null, result.getContentValues());
 556	}
 557
 558	public ServiceDiscoveryResult findDiscoveryResult(final String hash, final String ver) {
 559		SQLiteDatabase db = this.getReadableDatabase();
 560		String[] selectionArgs = {hash, ver};
 561		Cursor cursor = db.query(ServiceDiscoveryResult.TABLENAME, null,
 562				ServiceDiscoveryResult.HASH + "=? AND " + ServiceDiscoveryResult.VER + "=?",
 563				selectionArgs, null, null, null);
 564		if (cursor.getCount() == 0) {
 565			cursor.close();
 566			return null;
 567		}
 568		cursor.moveToFirst();
 569
 570		ServiceDiscoveryResult result = null;
 571		try {
 572			result = new ServiceDiscoveryResult(cursor);
 573		} catch (JSONException e) { /* result is still null */ }
 574
 575		cursor.close();
 576		return result;
 577	}
 578
 579	public void insertPresenceTemplate(PresenceTemplate template) {
 580		SQLiteDatabase db = this.getWritableDatabase();
 581		db.insert(PresenceTemplate.TABELNAME, null, template.getContentValues());
 582	}
 583
 584	public List<PresenceTemplate> getPresenceTemplates() {
 585		ArrayList<PresenceTemplate> templates = new ArrayList<>();
 586		SQLiteDatabase db = this.getReadableDatabase();
 587		Cursor cursor = db.query(PresenceTemplate.TABELNAME,null,null,null,null,null,PresenceTemplate.LAST_USED+" desc");
 588		while (cursor.moveToNext()) {
 589			templates.add(PresenceTemplate.fromCursor(cursor));
 590		}
 591		cursor.close();
 592		return templates;
 593	}
 594
 595	public void deletePresenceTemplate(PresenceTemplate template) {
 596		Log.d(Config.LOGTAG,"deleting presence template with uuid "+template.getUuid());
 597		SQLiteDatabase db = this.getWritableDatabase();
 598		String where = PresenceTemplate.UUID+"=?";
 599		String[] whereArgs = {template.getUuid()};
 600		db.delete(PresenceTemplate.TABELNAME,where,whereArgs);
 601	}
 602
 603	public CopyOnWriteArrayList<Conversation> getConversations(int status) {
 604		CopyOnWriteArrayList<Conversation> list = new CopyOnWriteArrayList<>();
 605		SQLiteDatabase db = this.getReadableDatabase();
 606		String[] selectionArgs = {Integer.toString(status)};
 607		Cursor cursor = db.rawQuery("select * from " + Conversation.TABLENAME
 608				+ " where " + Conversation.STATUS + " = ? order by "
 609				+ Conversation.CREATED + " desc", selectionArgs);
 610		while (cursor.moveToNext()) {
 611			list.add(Conversation.fromCursor(cursor));
 612		}
 613		cursor.close();
 614		return list;
 615	}
 616
 617	public ArrayList<Message> getMessages(Conversation conversations, int limit) {
 618		return getMessages(conversations, limit, -1);
 619	}
 620
 621	public ArrayList<Message> getMessages(Conversation conversation, int limit,
 622										  long timestamp) {
 623		ArrayList<Message> list = new ArrayList<>();
 624		SQLiteDatabase db = this.getReadableDatabase();
 625		Cursor cursor;
 626		if (timestamp == -1) {
 627			String[] selectionArgs = {conversation.getUuid()};
 628			cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 629					+ "=?", selectionArgs, null, null, Message.TIME_SENT
 630					+ " DESC", String.valueOf(limit));
 631		} else {
 632			String[] selectionArgs = {conversation.getUuid(),
 633					Long.toString(timestamp)};
 634			cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 635							+ "=? and " + Message.TIME_SENT + "<?", selectionArgs,
 636					null, null, Message.TIME_SENT + " DESC",
 637					String.valueOf(limit));
 638		}
 639		if (cursor.getCount() > 0) {
 640			cursor.moveToLast();
 641			do {
 642				Message message = Message.fromCursor(cursor);
 643				if (message != null) {
 644					message.setConversation(conversation);
 645					list.add(message);
 646				}
 647			} while (cursor.moveToPrevious());
 648		}
 649		cursor.close();
 650		return list;
 651	}
 652
 653	public Iterable<Message> getMessagesIterable(final Conversation conversation) {
 654		return new Iterable<Message>() {
 655			@Override
 656			public Iterator<Message> iterator() {
 657				class MessageIterator implements Iterator<Message> {
 658					SQLiteDatabase db = getReadableDatabase();
 659					String[] selectionArgs = {conversation.getUuid()};
 660					Cursor cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 661							+ "=?", selectionArgs, null, null, Message.TIME_SENT
 662							+ " ASC", null);
 663
 664					public MessageIterator() {
 665						cursor.moveToFirst();
 666					}
 667
 668					@Override
 669					public boolean hasNext() {
 670						return !cursor.isAfterLast();
 671					}
 672
 673					@Override
 674					public Message next() {
 675						Message message = Message.fromCursor(cursor);
 676						cursor.moveToNext();
 677						return message;
 678					}
 679
 680					@Override
 681					public void remove() {
 682						throw new UnsupportedOperationException();
 683					}
 684				}
 685				return new MessageIterator();
 686			}
 687		};
 688	}
 689
 690	public Conversation findConversation(final Account account, final Jid contactJid) {
 691		SQLiteDatabase db = this.getReadableDatabase();
 692		String[] selectionArgs = {account.getUuid(),
 693				contactJid.toBareJid().toPreppedString() + "/%",
 694				contactJid.toBareJid().toPreppedString()
 695		};
 696		Cursor cursor = db.query(Conversation.TABLENAME, null,
 697				Conversation.ACCOUNT + "=? AND (" + Conversation.CONTACTJID
 698						+ " like ? OR " + Conversation.CONTACTJID + "=?)", selectionArgs, null, null, null);
 699		if (cursor.getCount() == 0) {
 700			cursor.close();
 701			return null;
 702		}
 703		cursor.moveToFirst();
 704		Conversation conversation = Conversation.fromCursor(cursor);
 705		cursor.close();
 706		return conversation;
 707	}
 708
 709	public void updateConversation(final Conversation conversation) {
 710		final SQLiteDatabase db = this.getWritableDatabase();
 711		final String[] args = {conversation.getUuid()};
 712		db.update(Conversation.TABLENAME, conversation.getContentValues(),
 713				Conversation.UUID + "=?", args);
 714	}
 715
 716	public List<Account> getAccounts() {
 717		SQLiteDatabase db = this.getReadableDatabase();
 718		return getAccounts(db);
 719	}
 720
 721	private List<Account> getAccounts(SQLiteDatabase db) {
 722		List<Account> list = new ArrayList<>();
 723		Cursor cursor = db.query(Account.TABLENAME, null, null, null, null,
 724				null, null);
 725		while (cursor.moveToNext()) {
 726			list.add(Account.fromCursor(cursor));
 727		}
 728		cursor.close();
 729		return list;
 730	}
 731
 732	public boolean updateAccount(Account account) {
 733		SQLiteDatabase db = this.getWritableDatabase();
 734		String[] args = {account.getUuid()};
 735		final int rows = db.update(Account.TABLENAME, account.getContentValues(), Account.UUID + "=?", args);
 736		return rows == 1;
 737	}
 738
 739	public boolean deleteAccount(Account account) {
 740		SQLiteDatabase db = this.getWritableDatabase();
 741		String[] args = {account.getUuid()};
 742		final int rows = db.delete(Account.TABLENAME, Account.UUID + "=?", args);
 743		return rows == 1;
 744	}
 745
 746	public boolean hasEnabledAccounts() {
 747		SQLiteDatabase db = this.getReadableDatabase();
 748		Cursor cursor = db.rawQuery("select count(" + Account.UUID + ")  from "
 749				+ Account.TABLENAME + " where not options & (1 <<1)", null);
 750		try {
 751			cursor.moveToFirst();
 752			int count = cursor.getInt(0);
 753			return (count > 0);
 754		} catch (SQLiteCantOpenDatabaseException e) {
 755			return true; // better safe than sorry
 756		} catch (RuntimeException e) {
 757			return true; // better safe than sorry
 758		} finally {
 759			if (cursor != null) {
 760				cursor.close();
 761			}
 762		}
 763	}
 764
 765	@Override
 766	public SQLiteDatabase getWritableDatabase() {
 767		SQLiteDatabase db = super.getWritableDatabase();
 768		db.execSQL("PRAGMA foreign_keys=ON;");
 769		return db;
 770	}
 771
 772	public void updateMessage(Message message) {
 773		SQLiteDatabase db = this.getWritableDatabase();
 774		String[] args = {message.getUuid()};
 775		db.update(Message.TABLENAME, message.getContentValues(), Message.UUID
 776				+ "=?", args);
 777	}
 778
 779	public void updateMessage(Message message, String uuid) {
 780		SQLiteDatabase db = this.getWritableDatabase();
 781		String[] args = {uuid};
 782		db.update(Message.TABLENAME, message.getContentValues(), Message.UUID
 783				+ "=?", args);
 784	}
 785
 786	public void readRoster(Roster roster) {
 787		SQLiteDatabase db = this.getReadableDatabase();
 788		Cursor cursor;
 789		String args[] = {roster.getAccount().getUuid()};
 790		cursor = db.query(Contact.TABLENAME, null, Contact.ACCOUNT + "=?", args, null, null, null);
 791		while (cursor.moveToNext()) {
 792			roster.initContact(Contact.fromCursor(cursor));
 793		}
 794		cursor.close();
 795	}
 796
 797	public void writeRoster(final Roster roster) {
 798		final Account account = roster.getAccount();
 799		final SQLiteDatabase db = this.getWritableDatabase();
 800		db.beginTransaction();
 801		for (Contact contact : roster.getContacts()) {
 802			if (contact.getOption(Contact.Options.IN_ROSTER)) {
 803				db.insert(Contact.TABLENAME, null, contact.getContentValues());
 804			} else {
 805				String where = Contact.ACCOUNT + "=? AND " + Contact.JID + "=?";
 806				String[] whereArgs = {account.getUuid(), contact.getJid().toPreppedString()};
 807				db.delete(Contact.TABLENAME, where, whereArgs);
 808			}
 809		}
 810		db.setTransactionSuccessful();
 811		db.endTransaction();
 812		account.setRosterVersion(roster.getVersion());
 813		updateAccount(account);
 814	}
 815
 816	public void deleteMessagesInConversation(Conversation conversation) {
 817		SQLiteDatabase db = this.getWritableDatabase();
 818		String[] args = {conversation.getUuid()};
 819		db.delete(Message.TABLENAME, Message.CONVERSATION + "=?", args);
 820	}
 821
 822	public boolean expireOldMessages(long timestamp) {
 823		String where = Message.TIME_SENT+"<?";
 824		String[] whereArgs = {String.valueOf(timestamp)};
 825		SQLiteDatabase db = this.getReadableDatabase();
 826		return db.delete(Message.TABLENAME,where,whereArgs) > 0;
 827	}
 828
 829	public Pair<Long, String> getLastMessageReceived(Account account) {
 830		Cursor cursor = null;
 831		try {
 832			SQLiteDatabase db = this.getReadableDatabase();
 833			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";
 834			String[] args = {account.getUuid()};
 835			cursor = db.rawQuery(sql, args);
 836			if (cursor.getCount() == 0) {
 837				return null;
 838			} else {
 839				cursor.moveToFirst();
 840				return new Pair<>(cursor.getLong(0), cursor.getString(1));
 841			}
 842		} catch (Exception e) {
 843			return null;
 844		} finally {
 845			if (cursor != null) {
 846				cursor.close();
 847			}
 848		}
 849	}
 850
 851	public long getLastTimeFingerprintUsed(Account account, String fingerprint) {
 852		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";
 853		String[] args = {account.getUuid(), fingerprint};
 854		Cursor cursor = getReadableDatabase().rawQuery(SQL,args);
 855		long time;
 856		if (cursor.moveToFirst()) {
 857			time = cursor.getLong(0);
 858		} else {
 859			time = 0;
 860		}
 861		cursor.close();
 862		return time;
 863	}
 864
 865	public Pair<Long,String> getLastClearDate(Account account) {
 866		SQLiteDatabase db = this.getReadableDatabase();
 867		String[] columns = {Conversation.ATTRIBUTES};
 868		String selection = Conversation.ACCOUNT + "=?";
 869		String[] args = {account.getUuid()};
 870		Cursor cursor = db.query(Conversation.TABLENAME,columns,selection,args,null,null,null);
 871		long maxClearDate = 0;
 872		while (cursor.moveToNext()) {
 873			try {
 874				final JSONObject jsonObject = new JSONObject(cursor.getString(0));
 875				maxClearDate = Math.max(maxClearDate, jsonObject.getLong(Conversation.ATTRIBUTE_LAST_CLEAR_HISTORY));
 876			} catch (Exception e) {
 877				//ignored
 878			}
 879		}
 880		cursor.close();
 881		return new Pair<>(maxClearDate,null);
 882	}
 883
 884	private Cursor getCursorForSession(Account account, AxolotlAddress contact) {
 885		final SQLiteDatabase db = this.getReadableDatabase();
 886		String[] selectionArgs = {account.getUuid(),
 887				contact.getName(),
 888				Integer.toString(contact.getDeviceId())};
 889		return db.query(SQLiteAxolotlStore.SESSION_TABLENAME,
 890				null,
 891				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 892						+ SQLiteAxolotlStore.NAME + " = ? AND "
 893						+ SQLiteAxolotlStore.DEVICE_ID + " = ? ",
 894				selectionArgs,
 895				null, null, null);
 896	}
 897
 898	public SessionRecord loadSession(Account account, AxolotlAddress contact) {
 899		SessionRecord session = null;
 900		Cursor cursor = getCursorForSession(account, contact);
 901		if (cursor.getCount() != 0) {
 902			cursor.moveToFirst();
 903			try {
 904				session = new SessionRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
 905			} catch (IOException e) {
 906				cursor.close();
 907				throw new AssertionError(e);
 908			}
 909		}
 910		cursor.close();
 911		return session;
 912	}
 913
 914	public List<Integer> getSubDeviceSessions(Account account, AxolotlAddress contact) {
 915		final SQLiteDatabase db = this.getReadableDatabase();
 916		return getSubDeviceSessions(db, account, contact);
 917	}
 918
 919	private List<Integer> getSubDeviceSessions(SQLiteDatabase db, Account account, AxolotlAddress contact) {
 920		List<Integer> devices = new ArrayList<>();
 921		String[] columns = {SQLiteAxolotlStore.DEVICE_ID};
 922		String[] selectionArgs = {account.getUuid(),
 923				contact.getName()};
 924		Cursor cursor = db.query(SQLiteAxolotlStore.SESSION_TABLENAME,
 925				columns,
 926				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 927						+ SQLiteAxolotlStore.NAME + " = ?",
 928				selectionArgs,
 929				null, null, null);
 930
 931		while (cursor.moveToNext()) {
 932			devices.add(cursor.getInt(
 933					cursor.getColumnIndex(SQLiteAxolotlStore.DEVICE_ID)));
 934		}
 935
 936		cursor.close();
 937		return devices;
 938	}
 939
 940	public boolean containsSession(Account account, AxolotlAddress contact) {
 941		Cursor cursor = getCursorForSession(account, contact);
 942		int count = cursor.getCount();
 943		cursor.close();
 944		return count != 0;
 945	}
 946
 947	public void storeSession(Account account, AxolotlAddress contact, SessionRecord session) {
 948		SQLiteDatabase db = this.getWritableDatabase();
 949		ContentValues values = new ContentValues();
 950		values.put(SQLiteAxolotlStore.NAME, contact.getName());
 951		values.put(SQLiteAxolotlStore.DEVICE_ID, contact.getDeviceId());
 952		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(session.serialize(), Base64.DEFAULT));
 953		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
 954		db.insert(SQLiteAxolotlStore.SESSION_TABLENAME, null, values);
 955	}
 956
 957	public void deleteSession(Account account, AxolotlAddress contact) {
 958		SQLiteDatabase db = this.getWritableDatabase();
 959		deleteSession(db, account, contact);
 960	}
 961
 962	private void deleteSession(SQLiteDatabase db, Account account, AxolotlAddress contact) {
 963		String[] args = {account.getUuid(),
 964				contact.getName(),
 965				Integer.toString(contact.getDeviceId())};
 966		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
 967				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 968						+ SQLiteAxolotlStore.NAME + " = ? AND "
 969						+ SQLiteAxolotlStore.DEVICE_ID + " = ? ",
 970				args);
 971	}
 972
 973	public void deleteAllSessions(Account account, AxolotlAddress contact) {
 974		SQLiteDatabase db = this.getWritableDatabase();
 975		String[] args = {account.getUuid(), contact.getName()};
 976		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
 977				SQLiteAxolotlStore.ACCOUNT + "=? AND "
 978						+ SQLiteAxolotlStore.NAME + " = ?",
 979				args);
 980	}
 981
 982	private Cursor getCursorForPreKey(Account account, int preKeyId) {
 983		SQLiteDatabase db = this.getReadableDatabase();
 984		String[] columns = {SQLiteAxolotlStore.KEY};
 985		String[] selectionArgs = {account.getUuid(), Integer.toString(preKeyId)};
 986		Cursor cursor = db.query(SQLiteAxolotlStore.PREKEY_TABLENAME,
 987				columns,
 988				SQLiteAxolotlStore.ACCOUNT + "=? AND "
 989						+ SQLiteAxolotlStore.ID + "=?",
 990				selectionArgs,
 991				null, null, null);
 992
 993		return cursor;
 994	}
 995
 996	public PreKeyRecord loadPreKey(Account account, int preKeyId) {
 997		PreKeyRecord record = null;
 998		Cursor cursor = getCursorForPreKey(account, preKeyId);
 999		if (cursor.getCount() != 0) {
1000			cursor.moveToFirst();
1001			try {
1002				record = new PreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
1003			} catch (IOException e) {
1004				throw new AssertionError(e);
1005			}
1006		}
1007		cursor.close();
1008		return record;
1009	}
1010
1011	public boolean containsPreKey(Account account, int preKeyId) {
1012		Cursor cursor = getCursorForPreKey(account, preKeyId);
1013		int count = cursor.getCount();
1014		cursor.close();
1015		return count != 0;
1016	}
1017
1018	public void storePreKey(Account account, PreKeyRecord record) {
1019		SQLiteDatabase db = this.getWritableDatabase();
1020		ContentValues values = new ContentValues();
1021		values.put(SQLiteAxolotlStore.ID, record.getId());
1022		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(record.serialize(), Base64.DEFAULT));
1023		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1024		db.insert(SQLiteAxolotlStore.PREKEY_TABLENAME, null, values);
1025	}
1026
1027	public void deletePreKey(Account account, int preKeyId) {
1028		SQLiteDatabase db = this.getWritableDatabase();
1029		String[] args = {account.getUuid(), Integer.toString(preKeyId)};
1030		db.delete(SQLiteAxolotlStore.PREKEY_TABLENAME,
1031				SQLiteAxolotlStore.ACCOUNT + "=? AND "
1032						+ SQLiteAxolotlStore.ID + "=?",
1033				args);
1034	}
1035
1036	private Cursor getCursorForSignedPreKey(Account account, int signedPreKeyId) {
1037		SQLiteDatabase db = this.getReadableDatabase();
1038		String[] columns = {SQLiteAxolotlStore.KEY};
1039		String[] selectionArgs = {account.getUuid(), Integer.toString(signedPreKeyId)};
1040		Cursor cursor = db.query(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1041				columns,
1042				SQLiteAxolotlStore.ACCOUNT + "=? AND " + SQLiteAxolotlStore.ID + "=?",
1043				selectionArgs,
1044				null, null, null);
1045
1046		return cursor;
1047	}
1048
1049	public SignedPreKeyRecord loadSignedPreKey(Account account, int signedPreKeyId) {
1050		SignedPreKeyRecord record = null;
1051		Cursor cursor = getCursorForSignedPreKey(account, signedPreKeyId);
1052		if (cursor.getCount() != 0) {
1053			cursor.moveToFirst();
1054			try {
1055				record = new SignedPreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
1056			} catch (IOException e) {
1057				throw new AssertionError(e);
1058			}
1059		}
1060		cursor.close();
1061		return record;
1062	}
1063
1064	public List<SignedPreKeyRecord> loadSignedPreKeys(Account account) {
1065		List<SignedPreKeyRecord> prekeys = new ArrayList<>();
1066		SQLiteDatabase db = this.getReadableDatabase();
1067		String[] columns = {SQLiteAxolotlStore.KEY};
1068		String[] selectionArgs = {account.getUuid()};
1069		Cursor cursor = db.query(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1070				columns,
1071				SQLiteAxolotlStore.ACCOUNT + "=?",
1072				selectionArgs,
1073				null, null, null);
1074
1075		while (cursor.moveToNext()) {
1076			try {
1077				prekeys.add(new SignedPreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT)));
1078			} catch (IOException ignored) {
1079			}
1080		}
1081		cursor.close();
1082		return prekeys;
1083	}
1084
1085	public boolean containsSignedPreKey(Account account, int signedPreKeyId) {
1086		Cursor cursor = getCursorForPreKey(account, signedPreKeyId);
1087		int count = cursor.getCount();
1088		cursor.close();
1089		return count != 0;
1090	}
1091
1092	public void storeSignedPreKey(Account account, SignedPreKeyRecord record) {
1093		SQLiteDatabase db = this.getWritableDatabase();
1094		ContentValues values = new ContentValues();
1095		values.put(SQLiteAxolotlStore.ID, record.getId());
1096		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(record.serialize(), Base64.DEFAULT));
1097		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1098		db.insert(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME, null, values);
1099	}
1100
1101	public void deleteSignedPreKey(Account account, int signedPreKeyId) {
1102		SQLiteDatabase db = this.getWritableDatabase();
1103		String[] args = {account.getUuid(), Integer.toString(signedPreKeyId)};
1104		db.delete(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1105				SQLiteAxolotlStore.ACCOUNT + "=? AND "
1106						+ SQLiteAxolotlStore.ID + "=?",
1107				args);
1108	}
1109
1110	private Cursor getIdentityKeyCursor(Account account, String name, boolean own) {
1111		final SQLiteDatabase db = this.getReadableDatabase();
1112		return getIdentityKeyCursor(db, account, name, own);
1113	}
1114
1115	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String name, boolean own) {
1116		return getIdentityKeyCursor(db, account, name, own, null);
1117	}
1118
1119	private Cursor getIdentityKeyCursor(Account account, String fingerprint) {
1120		final SQLiteDatabase db = this.getReadableDatabase();
1121		return getIdentityKeyCursor(db, account, fingerprint);
1122	}
1123
1124	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String fingerprint) {
1125		return getIdentityKeyCursor(db, account, null, null, fingerprint);
1126	}
1127
1128	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String name, Boolean own, String fingerprint) {
1129		String[] columns = {SQLiteAxolotlStore.TRUST,
1130				SQLiteAxolotlStore.ACTIVE,
1131				SQLiteAxolotlStore.LAST_ACTIVATION,
1132				SQLiteAxolotlStore.KEY};
1133		ArrayList<String> selectionArgs = new ArrayList<>(4);
1134		selectionArgs.add(account.getUuid());
1135		String selectionString = SQLiteAxolotlStore.ACCOUNT + " = ?";
1136		if (name != null) {
1137			selectionArgs.add(name);
1138			selectionString += " AND " + SQLiteAxolotlStore.NAME + " = ?";
1139		}
1140		if (fingerprint != null) {
1141			selectionArgs.add(fingerprint);
1142			selectionString += " AND " + SQLiteAxolotlStore.FINGERPRINT + " = ?";
1143		}
1144		if (own != null) {
1145			selectionArgs.add(own ? "1" : "0");
1146			selectionString += " AND " + SQLiteAxolotlStore.OWN + " = ?";
1147		}
1148		Cursor cursor = db.query(SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1149				columns,
1150				selectionString,
1151				selectionArgs.toArray(new String[selectionArgs.size()]),
1152				null, null, null);
1153
1154		return cursor;
1155	}
1156
1157	public IdentityKeyPair loadOwnIdentityKeyPair(Account account) {
1158		SQLiteDatabase db = getReadableDatabase();
1159		return loadOwnIdentityKeyPair(db, account);
1160	}
1161
1162	private IdentityKeyPair loadOwnIdentityKeyPair(SQLiteDatabase db, Account account) {
1163		String name = account.getJid().toBareJid().toPreppedString();
1164		IdentityKeyPair identityKeyPair = null;
1165		Cursor cursor = getIdentityKeyCursor(db, account, name, true);
1166		if (cursor.getCount() != 0) {
1167			cursor.moveToFirst();
1168			try {
1169				identityKeyPair = new IdentityKeyPair(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
1170			} catch (InvalidKeyException e) {
1171				Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Encountered invalid IdentityKey in database for account" + account.getJid().toBareJid() + ", address: " + name);
1172			}
1173		}
1174		cursor.close();
1175
1176		return identityKeyPair;
1177	}
1178
1179	public Set<IdentityKey> loadIdentityKeys(Account account, String name) {
1180		return loadIdentityKeys(account, name, null);
1181	}
1182
1183	public Set<IdentityKey> loadIdentityKeys(Account account, String name, FingerprintStatus status) {
1184		Set<IdentityKey> identityKeys = new HashSet<>();
1185		Cursor cursor = getIdentityKeyCursor(account, name, false);
1186
1187		while (cursor.moveToNext()) {
1188			if (status != null && !FingerprintStatus.fromCursor(cursor).equals(status)) {
1189				continue;
1190			}
1191			try {
1192				String key = cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY));
1193				if (key != null) {
1194					identityKeys.add(new IdentityKey(Base64.decode(key, Base64.DEFAULT), 0));
1195				} else {
1196					Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Missing key (possibly preverified) in database for account" + account.getJid().toBareJid() + ", address: " + name);
1197				}
1198			} catch (InvalidKeyException e) {
1199				Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Encountered invalid IdentityKey in database for account" + account.getJid().toBareJid() + ", address: " + name);
1200			}
1201		}
1202		cursor.close();
1203
1204		return identityKeys;
1205	}
1206
1207	public long numTrustedKeys(Account account, String name) {
1208		SQLiteDatabase db = getReadableDatabase();
1209		String[] args = {
1210				account.getUuid(),
1211				name,
1212				FingerprintStatus.Trust.TRUSTED.toString(),
1213				FingerprintStatus.Trust.VERIFIED.toString(),
1214				FingerprintStatus.Trust.VERIFIED_X509.toString()
1215		};
1216		return DatabaseUtils.queryNumEntries(db, SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1217				SQLiteAxolotlStore.ACCOUNT + " = ?"
1218						+ " AND " + SQLiteAxolotlStore.NAME + " = ?"
1219						+ " AND (" + SQLiteAxolotlStore.TRUST + " = ? OR " + SQLiteAxolotlStore.TRUST + " = ? OR " +SQLiteAxolotlStore.TRUST +" = ?)"
1220						+ " AND " +SQLiteAxolotlStore.ACTIVE + " > 0",
1221				args
1222		);
1223	}
1224
1225	private void storeIdentityKey(Account account, String name, boolean own, String fingerprint, String base64Serialized, FingerprintStatus status) {
1226		SQLiteDatabase db = this.getWritableDatabase();
1227		ContentValues values = new ContentValues();
1228		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1229		values.put(SQLiteAxolotlStore.NAME, name);
1230		values.put(SQLiteAxolotlStore.OWN, own ? 1 : 0);
1231		values.put(SQLiteAxolotlStore.FINGERPRINT, fingerprint);
1232		values.put(SQLiteAxolotlStore.KEY, base64Serialized);
1233		values.putAll(status.toContentValues());
1234		String where = SQLiteAxolotlStore.ACCOUNT+"=? AND "+SQLiteAxolotlStore.NAME+"=? AND "+SQLiteAxolotlStore.FINGERPRINT+" =?";
1235		String[] whereArgs = {account.getUuid(),name,fingerprint};
1236		int rows = db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME,values,where,whereArgs);
1237		if (rows == 0) {
1238			db.insert(SQLiteAxolotlStore.IDENTITIES_TABLENAME, null, values);
1239		}
1240	}
1241
1242	public void storePreVerification(Account account, String name, String fingerprint, FingerprintStatus status) {
1243		SQLiteDatabase db = this.getWritableDatabase();
1244		ContentValues values = new ContentValues();
1245		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1246		values.put(SQLiteAxolotlStore.NAME, name);
1247		values.put(SQLiteAxolotlStore.OWN, 0);
1248		values.put(SQLiteAxolotlStore.FINGERPRINT, fingerprint);
1249		values.putAll(status.toContentValues());
1250		db.insert(SQLiteAxolotlStore.IDENTITIES_TABLENAME, null, values);
1251	}
1252
1253	public FingerprintStatus getFingerprintStatus(Account account, String fingerprint) {
1254		Cursor cursor = getIdentityKeyCursor(account, fingerprint);
1255		final FingerprintStatus status;
1256		if (cursor.getCount() > 0) {
1257			cursor.moveToFirst();
1258			status = FingerprintStatus.fromCursor(cursor);
1259		} else {
1260			status = null;
1261		}
1262		cursor.close();
1263		return status;
1264	}
1265
1266	public boolean setIdentityKeyTrust(Account account, String fingerprint, FingerprintStatus fingerprintStatus) {
1267		SQLiteDatabase db = this.getWritableDatabase();
1268		return setIdentityKeyTrust(db, account, fingerprint, fingerprintStatus);
1269	}
1270
1271	private boolean setIdentityKeyTrust(SQLiteDatabase db, Account account, String fingerprint, FingerprintStatus status) {
1272		String[] selectionArgs = {
1273				account.getUuid(),
1274				fingerprint
1275		};
1276		int rows = db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME, status.toContentValues(),
1277				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
1278						+ SQLiteAxolotlStore.FINGERPRINT + " = ? ",
1279				selectionArgs);
1280		return rows == 1;
1281	}
1282
1283	public boolean setIdentityKeyCertificate(Account account, String fingerprint, X509Certificate x509Certificate) {
1284		SQLiteDatabase db = this.getWritableDatabase();
1285		String[] selectionArgs = {
1286				account.getUuid(),
1287				fingerprint
1288		};
1289		try {
1290			ContentValues values = new ContentValues();
1291			values.put(SQLiteAxolotlStore.CERTIFICATE, x509Certificate.getEncoded());
1292			return db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME, values,
1293					SQLiteAxolotlStore.ACCOUNT + " = ? AND "
1294							+ SQLiteAxolotlStore.FINGERPRINT + " = ? ",
1295					selectionArgs) == 1;
1296		} catch (CertificateEncodingException e) {
1297			Log.d(Config.LOGTAG, "could not encode certificate");
1298			return false;
1299		}
1300	}
1301
1302	public X509Certificate getIdentityKeyCertifcate(Account account, String fingerprint) {
1303		SQLiteDatabase db = this.getReadableDatabase();
1304		String[] selectionArgs = {
1305				account.getUuid(),
1306				fingerprint
1307		};
1308		String[] colums = {SQLiteAxolotlStore.CERTIFICATE};
1309		String selection = SQLiteAxolotlStore.ACCOUNT + " = ? AND " + SQLiteAxolotlStore.FINGERPRINT + " = ? ";
1310		Cursor cursor = db.query(SQLiteAxolotlStore.IDENTITIES_TABLENAME, colums, selection, selectionArgs, null, null, null);
1311		if (cursor.getCount() < 1) {
1312			return null;
1313		} else {
1314			cursor.moveToFirst();
1315			byte[] certificate = cursor.getBlob(cursor.getColumnIndex(SQLiteAxolotlStore.CERTIFICATE));
1316			cursor.close();
1317			if (certificate == null || certificate.length == 0) {
1318				return null;
1319			}
1320			try {
1321				CertificateFactory certificateFactory = CertificateFactory.getInstance("X.509");
1322				return (X509Certificate) certificateFactory.generateCertificate(new ByteArrayInputStream(certificate));
1323			} catch (CertificateException e) {
1324				Log.d(Config.LOGTAG,"certificate exception "+e.getMessage());
1325				return null;
1326			}
1327		}
1328	}
1329
1330	public void storeIdentityKey(Account account, String name, IdentityKey identityKey, FingerprintStatus status) {
1331		storeIdentityKey(account, name, false, identityKey.getFingerprint().replaceAll("\\s", ""), Base64.encodeToString(identityKey.serialize(), Base64.DEFAULT), status);
1332	}
1333
1334	public void storeOwnIdentityKeyPair(Account account, IdentityKeyPair identityKeyPair) {
1335		storeIdentityKey(account, account.getJid().toBareJid().toPreppedString(), true, identityKeyPair.getPublicKey().getFingerprint().replaceAll("\\s", ""), Base64.encodeToString(identityKeyPair.serialize(), Base64.DEFAULT), FingerprintStatus.createActiveVerified(false));
1336	}
1337
1338
1339	public void recreateAxolotlDb(SQLiteDatabase db) {
1340		Log.d(Config.LOGTAG, AxolotlService.LOGPREFIX + " : " + ">>> (RE)CREATING AXOLOTL DATABASE <<<");
1341		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.SESSION_TABLENAME);
1342		db.execSQL(CREATE_SESSIONS_STATEMENT);
1343		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.PREKEY_TABLENAME);
1344		db.execSQL(CREATE_PREKEYS_STATEMENT);
1345		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME);
1346		db.execSQL(CREATE_SIGNED_PREKEYS_STATEMENT);
1347		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.IDENTITIES_TABLENAME);
1348		db.execSQL(CREATE_IDENTITIES_STATEMENT);
1349	}
1350
1351	public void wipeAxolotlDb(Account account) {
1352		String accountName = account.getUuid();
1353		Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + ">>> WIPING AXOLOTL DATABASE FOR ACCOUNT " + accountName + " <<<");
1354		SQLiteDatabase db = this.getWritableDatabase();
1355		String[] deleteArgs = {
1356				accountName
1357		};
1358		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
1359				SQLiteAxolotlStore.ACCOUNT + " = ?",
1360				deleteArgs);
1361		db.delete(SQLiteAxolotlStore.PREKEY_TABLENAME,
1362				SQLiteAxolotlStore.ACCOUNT + " = ?",
1363				deleteArgs);
1364		db.delete(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1365				SQLiteAxolotlStore.ACCOUNT + " = ?",
1366				deleteArgs);
1367		db.delete(SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1368				SQLiteAxolotlStore.ACCOUNT + " = ?",
1369				deleteArgs);
1370	}
1371
1372	public boolean startTimeCountExceedsThreshold() {
1373		SQLiteDatabase db = this.getWritableDatabase();
1374		long cleanBeforeTimestamp = System.currentTimeMillis() - Config.FREQUENT_RESTARTS_DETECTION_WINDOW;
1375		db.execSQL("delete from "+START_TIMES_TABLE+" where timestamp < "+cleanBeforeTimestamp);
1376		ContentValues values = new ContentValues();
1377		values.put("timestamp",System.currentTimeMillis());
1378		db.insert(START_TIMES_TABLE,null,values);
1379		String[] columns = new String[]{"count(timestamp)"};
1380		Cursor cursor = db.query(START_TIMES_TABLE,columns,null,null,null,null,null);
1381		int count;
1382		if (cursor.moveToFirst()) {
1383			count = cursor.getInt(0);
1384		} else {
1385			count = 0;
1386		}
1387		cursor.close();
1388		Log.d(Config.LOGTAG,"start time counter reached "+count);
1389		return count >= Config.FREQUENT_RESTARTS_THRESHOLD;
1390	}
1391
1392	public void clearStartTimeCounter(boolean justOne) {
1393		SQLiteDatabase db = this.getWritableDatabase();
1394		if (justOne) {
1395			db.execSQL("delete from "+START_TIMES_TABLE+" where timestamp in (select timestamp from "+START_TIMES_TABLE+" order by timestamp desc limit 1)");
1396			Log.d(Config.LOGTAG,"do not count start up after being swiped away");
1397		} else {
1398			Log.d(Config.LOGTAG,"resetting start time counter");
1399			db.execSQL("delete from " + START_TIMES_TABLE);
1400		}
1401	}
1402}