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				message.setConversation(conversation);
 644				list.add(message);
 645			} while (cursor.moveToPrevious());
 646		}
 647		cursor.close();
 648		return list;
 649	}
 650
 651	public Iterable<Message> getMessagesIterable(final Conversation conversation) {
 652		return new Iterable<Message>() {
 653			@Override
 654			public Iterator<Message> iterator() {
 655				class MessageIterator implements Iterator<Message> {
 656					SQLiteDatabase db = getReadableDatabase();
 657					String[] selectionArgs = {conversation.getUuid()};
 658					Cursor cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 659							+ "=?", selectionArgs, null, null, Message.TIME_SENT
 660							+ " ASC", null);
 661
 662					public MessageIterator() {
 663						cursor.moveToFirst();
 664					}
 665
 666					@Override
 667					public boolean hasNext() {
 668						return !cursor.isAfterLast();
 669					}
 670
 671					@Override
 672					public Message next() {
 673						Message message = Message.fromCursor(cursor);
 674						cursor.moveToNext();
 675						return message;
 676					}
 677
 678					@Override
 679					public void remove() {
 680						throw new UnsupportedOperationException();
 681					}
 682				}
 683				return new MessageIterator();
 684			}
 685		};
 686	}
 687
 688	public Conversation findConversation(final Account account, final Jid contactJid) {
 689		SQLiteDatabase db = this.getReadableDatabase();
 690		String[] selectionArgs = {account.getUuid(),
 691				contactJid.toBareJid().toPreppedString() + "/%",
 692				contactJid.toBareJid().toPreppedString()
 693		};
 694		Cursor cursor = db.query(Conversation.TABLENAME, null,
 695				Conversation.ACCOUNT + "=? AND (" + Conversation.CONTACTJID
 696						+ " like ? OR " + Conversation.CONTACTJID + "=?)", selectionArgs, null, null, null);
 697		if (cursor.getCount() == 0) {
 698			cursor.close();
 699			return null;
 700		}
 701		cursor.moveToFirst();
 702		Conversation conversation = Conversation.fromCursor(cursor);
 703		cursor.close();
 704		return conversation;
 705	}
 706
 707	public void updateConversation(final Conversation conversation) {
 708		final SQLiteDatabase db = this.getWritableDatabase();
 709		final String[] args = {conversation.getUuid()};
 710		db.update(Conversation.TABLENAME, conversation.getContentValues(),
 711				Conversation.UUID + "=?", args);
 712	}
 713
 714	public List<Account> getAccounts() {
 715		SQLiteDatabase db = this.getReadableDatabase();
 716		return getAccounts(db);
 717	}
 718
 719	private List<Account> getAccounts(SQLiteDatabase db) {
 720		List<Account> list = new ArrayList<>();
 721		Cursor cursor = db.query(Account.TABLENAME, null, null, null, null,
 722				null, null);
 723		while (cursor.moveToNext()) {
 724			list.add(Account.fromCursor(cursor));
 725		}
 726		cursor.close();
 727		return list;
 728	}
 729
 730	public boolean updateAccount(Account account) {
 731		SQLiteDatabase db = this.getWritableDatabase();
 732		String[] args = {account.getUuid()};
 733		final int rows = db.update(Account.TABLENAME, account.getContentValues(), Account.UUID + "=?", args);
 734		return rows == 1;
 735	}
 736
 737	public boolean deleteAccount(Account account) {
 738		SQLiteDatabase db = this.getWritableDatabase();
 739		String[] args = {account.getUuid()};
 740		final int rows = db.delete(Account.TABLENAME, Account.UUID + "=?", args);
 741		return rows == 1;
 742	}
 743
 744	public boolean hasEnabledAccounts() {
 745		SQLiteDatabase db = this.getReadableDatabase();
 746		Cursor cursor = db.rawQuery("select count(" + Account.UUID + ")  from "
 747				+ Account.TABLENAME + " where not options & (1 <<1)", null);
 748		try {
 749			cursor.moveToFirst();
 750			int count = cursor.getInt(0);
 751			return (count > 0);
 752		} catch (SQLiteCantOpenDatabaseException e) {
 753			return true; // better safe than sorry
 754		} catch (RuntimeException e) {
 755			return true; // better safe than sorry
 756		} finally {
 757			if (cursor != null) {
 758				cursor.close();
 759			}
 760		}
 761	}
 762
 763	@Override
 764	public SQLiteDatabase getWritableDatabase() {
 765		SQLiteDatabase db = super.getWritableDatabase();
 766		db.execSQL("PRAGMA foreign_keys=ON;");
 767		return db;
 768	}
 769
 770	public void updateMessage(Message message) {
 771		SQLiteDatabase db = this.getWritableDatabase();
 772		String[] args = {message.getUuid()};
 773		db.update(Message.TABLENAME, message.getContentValues(), Message.UUID
 774				+ "=?", args);
 775	}
 776
 777	public void updateMessage(Message message, String uuid) {
 778		SQLiteDatabase db = this.getWritableDatabase();
 779		String[] args = {uuid};
 780		db.update(Message.TABLENAME, message.getContentValues(), Message.UUID
 781				+ "=?", args);
 782	}
 783
 784	public void readRoster(Roster roster) {
 785		SQLiteDatabase db = this.getReadableDatabase();
 786		Cursor cursor;
 787		String args[] = {roster.getAccount().getUuid()};
 788		cursor = db.query(Contact.TABLENAME, null, Contact.ACCOUNT + "=?", args, null, null, null);
 789		while (cursor.moveToNext()) {
 790			roster.initContact(Contact.fromCursor(cursor));
 791		}
 792		cursor.close();
 793	}
 794
 795	public void writeRoster(final Roster roster) {
 796		final Account account = roster.getAccount();
 797		final SQLiteDatabase db = this.getWritableDatabase();
 798		db.beginTransaction();
 799		for (Contact contact : roster.getContacts()) {
 800			if (contact.getOption(Contact.Options.IN_ROSTER)) {
 801				db.insert(Contact.TABLENAME, null, contact.getContentValues());
 802			} else {
 803				String where = Contact.ACCOUNT + "=? AND " + Contact.JID + "=?";
 804				String[] whereArgs = {account.getUuid(), contact.getJid().toPreppedString()};
 805				db.delete(Contact.TABLENAME, where, whereArgs);
 806			}
 807		}
 808		db.setTransactionSuccessful();
 809		db.endTransaction();
 810		account.setRosterVersion(roster.getVersion());
 811		updateAccount(account);
 812	}
 813
 814	public void deleteMessagesInConversation(Conversation conversation) {
 815		SQLiteDatabase db = this.getWritableDatabase();
 816		String[] args = {conversation.getUuid()};
 817		db.delete(Message.TABLENAME, Message.CONVERSATION + "=?", args);
 818	}
 819
 820	public boolean expireOldMessages(long timestamp) {
 821		String where = Message.TIME_SENT+"<?";
 822		String[] whereArgs = {String.valueOf(timestamp)};
 823		SQLiteDatabase db = this.getReadableDatabase();
 824		return db.delete(Message.TABLENAME,where,whereArgs) > 0;
 825	}
 826
 827	public Pair<Long, String> getLastMessageReceived(Account account) {
 828		Cursor cursor = null;
 829		try {
 830			SQLiteDatabase db = this.getReadableDatabase();
 831			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";
 832			String[] args = {account.getUuid()};
 833			cursor = db.rawQuery(sql, args);
 834			if (cursor.getCount() == 0) {
 835				return null;
 836			} else {
 837				cursor.moveToFirst();
 838				return new Pair<>(cursor.getLong(0), cursor.getString(1));
 839			}
 840		} catch (Exception e) {
 841			return null;
 842		} finally {
 843			if (cursor != null) {
 844				cursor.close();
 845			}
 846		}
 847	}
 848
 849	public long getLastTimeFingerprintUsed(Account account, String fingerprint) {
 850		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";
 851		String[] args = {account.getUuid(), fingerprint};
 852		Cursor cursor = getReadableDatabase().rawQuery(SQL,args);
 853		long time;
 854		if (cursor.moveToFirst()) {
 855			time = cursor.getLong(0);
 856		} else {
 857			time = 0;
 858		}
 859		cursor.close();
 860		return time;
 861	}
 862
 863	public Pair<Long,String> getLastClearDate(Account account) {
 864		SQLiteDatabase db = this.getReadableDatabase();
 865		String[] columns = {Conversation.ATTRIBUTES};
 866		String selection = Conversation.ACCOUNT + "=?";
 867		String[] args = {account.getUuid()};
 868		Cursor cursor = db.query(Conversation.TABLENAME,columns,selection,args,null,null,null);
 869		long maxClearDate = 0;
 870		while (cursor.moveToNext()) {
 871			try {
 872				final JSONObject jsonObject = new JSONObject(cursor.getString(0));
 873				maxClearDate = Math.max(maxClearDate, jsonObject.getLong(Conversation.ATTRIBUTE_LAST_CLEAR_HISTORY));
 874			} catch (Exception e) {
 875				//ignored
 876			}
 877		}
 878		cursor.close();
 879		return new Pair<>(maxClearDate,null);
 880	}
 881
 882	private Cursor getCursorForSession(Account account, AxolotlAddress contact) {
 883		final SQLiteDatabase db = this.getReadableDatabase();
 884		String[] selectionArgs = {account.getUuid(),
 885				contact.getName(),
 886				Integer.toString(contact.getDeviceId())};
 887		return db.query(SQLiteAxolotlStore.SESSION_TABLENAME,
 888				null,
 889				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 890						+ SQLiteAxolotlStore.NAME + " = ? AND "
 891						+ SQLiteAxolotlStore.DEVICE_ID + " = ? ",
 892				selectionArgs,
 893				null, null, null);
 894	}
 895
 896	public SessionRecord loadSession(Account account, AxolotlAddress contact) {
 897		SessionRecord session = null;
 898		Cursor cursor = getCursorForSession(account, contact);
 899		if (cursor.getCount() != 0) {
 900			cursor.moveToFirst();
 901			try {
 902				session = new SessionRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
 903			} catch (IOException e) {
 904				cursor.close();
 905				throw new AssertionError(e);
 906			}
 907		}
 908		cursor.close();
 909		return session;
 910	}
 911
 912	public List<Integer> getSubDeviceSessions(Account account, AxolotlAddress contact) {
 913		final SQLiteDatabase db = this.getReadableDatabase();
 914		return getSubDeviceSessions(db, account, contact);
 915	}
 916
 917	private List<Integer> getSubDeviceSessions(SQLiteDatabase db, Account account, AxolotlAddress contact) {
 918		List<Integer> devices = new ArrayList<>();
 919		String[] columns = {SQLiteAxolotlStore.DEVICE_ID};
 920		String[] selectionArgs = {account.getUuid(),
 921				contact.getName()};
 922		Cursor cursor = db.query(SQLiteAxolotlStore.SESSION_TABLENAME,
 923				columns,
 924				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 925						+ SQLiteAxolotlStore.NAME + " = ?",
 926				selectionArgs,
 927				null, null, null);
 928
 929		while (cursor.moveToNext()) {
 930			devices.add(cursor.getInt(
 931					cursor.getColumnIndex(SQLiteAxolotlStore.DEVICE_ID)));
 932		}
 933
 934		cursor.close();
 935		return devices;
 936	}
 937
 938	public boolean containsSession(Account account, AxolotlAddress contact) {
 939		Cursor cursor = getCursorForSession(account, contact);
 940		int count = cursor.getCount();
 941		cursor.close();
 942		return count != 0;
 943	}
 944
 945	public void storeSession(Account account, AxolotlAddress contact, SessionRecord session) {
 946		SQLiteDatabase db = this.getWritableDatabase();
 947		ContentValues values = new ContentValues();
 948		values.put(SQLiteAxolotlStore.NAME, contact.getName());
 949		values.put(SQLiteAxolotlStore.DEVICE_ID, contact.getDeviceId());
 950		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(session.serialize(), Base64.DEFAULT));
 951		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
 952		db.insert(SQLiteAxolotlStore.SESSION_TABLENAME, null, values);
 953	}
 954
 955	public void deleteSession(Account account, AxolotlAddress contact) {
 956		SQLiteDatabase db = this.getWritableDatabase();
 957		deleteSession(db, account, contact);
 958	}
 959
 960	private void deleteSession(SQLiteDatabase db, Account account, AxolotlAddress contact) {
 961		String[] args = {account.getUuid(),
 962				contact.getName(),
 963				Integer.toString(contact.getDeviceId())};
 964		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
 965				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 966						+ SQLiteAxolotlStore.NAME + " = ? AND "
 967						+ SQLiteAxolotlStore.DEVICE_ID + " = ? ",
 968				args);
 969	}
 970
 971	public void deleteAllSessions(Account account, AxolotlAddress contact) {
 972		SQLiteDatabase db = this.getWritableDatabase();
 973		String[] args = {account.getUuid(), contact.getName()};
 974		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
 975				SQLiteAxolotlStore.ACCOUNT + "=? AND "
 976						+ SQLiteAxolotlStore.NAME + " = ?",
 977				args);
 978	}
 979
 980	private Cursor getCursorForPreKey(Account account, int preKeyId) {
 981		SQLiteDatabase db = this.getReadableDatabase();
 982		String[] columns = {SQLiteAxolotlStore.KEY};
 983		String[] selectionArgs = {account.getUuid(), Integer.toString(preKeyId)};
 984		Cursor cursor = db.query(SQLiteAxolotlStore.PREKEY_TABLENAME,
 985				columns,
 986				SQLiteAxolotlStore.ACCOUNT + "=? AND "
 987						+ SQLiteAxolotlStore.ID + "=?",
 988				selectionArgs,
 989				null, null, null);
 990
 991		return cursor;
 992	}
 993
 994	public PreKeyRecord loadPreKey(Account account, int preKeyId) {
 995		PreKeyRecord record = null;
 996		Cursor cursor = getCursorForPreKey(account, preKeyId);
 997		if (cursor.getCount() != 0) {
 998			cursor.moveToFirst();
 999			try {
1000				record = new PreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
1001			} catch (IOException e) {
1002				throw new AssertionError(e);
1003			}
1004		}
1005		cursor.close();
1006		return record;
1007	}
1008
1009	public boolean containsPreKey(Account account, int preKeyId) {
1010		Cursor cursor = getCursorForPreKey(account, preKeyId);
1011		int count = cursor.getCount();
1012		cursor.close();
1013		return count != 0;
1014	}
1015
1016	public void storePreKey(Account account, PreKeyRecord record) {
1017		SQLiteDatabase db = this.getWritableDatabase();
1018		ContentValues values = new ContentValues();
1019		values.put(SQLiteAxolotlStore.ID, record.getId());
1020		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(record.serialize(), Base64.DEFAULT));
1021		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1022		db.insert(SQLiteAxolotlStore.PREKEY_TABLENAME, null, values);
1023	}
1024
1025	public void deletePreKey(Account account, int preKeyId) {
1026		SQLiteDatabase db = this.getWritableDatabase();
1027		String[] args = {account.getUuid(), Integer.toString(preKeyId)};
1028		db.delete(SQLiteAxolotlStore.PREKEY_TABLENAME,
1029				SQLiteAxolotlStore.ACCOUNT + "=? AND "
1030						+ SQLiteAxolotlStore.ID + "=?",
1031				args);
1032	}
1033
1034	private Cursor getCursorForSignedPreKey(Account account, int signedPreKeyId) {
1035		SQLiteDatabase db = this.getReadableDatabase();
1036		String[] columns = {SQLiteAxolotlStore.KEY};
1037		String[] selectionArgs = {account.getUuid(), Integer.toString(signedPreKeyId)};
1038		Cursor cursor = db.query(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1039				columns,
1040				SQLiteAxolotlStore.ACCOUNT + "=? AND " + SQLiteAxolotlStore.ID + "=?",
1041				selectionArgs,
1042				null, null, null);
1043
1044		return cursor;
1045	}
1046
1047	public SignedPreKeyRecord loadSignedPreKey(Account account, int signedPreKeyId) {
1048		SignedPreKeyRecord record = null;
1049		Cursor cursor = getCursorForSignedPreKey(account, signedPreKeyId);
1050		if (cursor.getCount() != 0) {
1051			cursor.moveToFirst();
1052			try {
1053				record = new SignedPreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
1054			} catch (IOException e) {
1055				throw new AssertionError(e);
1056			}
1057		}
1058		cursor.close();
1059		return record;
1060	}
1061
1062	public List<SignedPreKeyRecord> loadSignedPreKeys(Account account) {
1063		List<SignedPreKeyRecord> prekeys = new ArrayList<>();
1064		SQLiteDatabase db = this.getReadableDatabase();
1065		String[] columns = {SQLiteAxolotlStore.KEY};
1066		String[] selectionArgs = {account.getUuid()};
1067		Cursor cursor = db.query(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1068				columns,
1069				SQLiteAxolotlStore.ACCOUNT + "=?",
1070				selectionArgs,
1071				null, null, null);
1072
1073		while (cursor.moveToNext()) {
1074			try {
1075				prekeys.add(new SignedPreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT)));
1076			} catch (IOException ignored) {
1077			}
1078		}
1079		cursor.close();
1080		return prekeys;
1081	}
1082
1083	public boolean containsSignedPreKey(Account account, int signedPreKeyId) {
1084		Cursor cursor = getCursorForPreKey(account, signedPreKeyId);
1085		int count = cursor.getCount();
1086		cursor.close();
1087		return count != 0;
1088	}
1089
1090	public void storeSignedPreKey(Account account, SignedPreKeyRecord record) {
1091		SQLiteDatabase db = this.getWritableDatabase();
1092		ContentValues values = new ContentValues();
1093		values.put(SQLiteAxolotlStore.ID, record.getId());
1094		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(record.serialize(), Base64.DEFAULT));
1095		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1096		db.insert(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME, null, values);
1097	}
1098
1099	public void deleteSignedPreKey(Account account, int signedPreKeyId) {
1100		SQLiteDatabase db = this.getWritableDatabase();
1101		String[] args = {account.getUuid(), Integer.toString(signedPreKeyId)};
1102		db.delete(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1103				SQLiteAxolotlStore.ACCOUNT + "=? AND "
1104						+ SQLiteAxolotlStore.ID + "=?",
1105				args);
1106	}
1107
1108	private Cursor getIdentityKeyCursor(Account account, String name, boolean own) {
1109		final SQLiteDatabase db = this.getReadableDatabase();
1110		return getIdentityKeyCursor(db, account, name, own);
1111	}
1112
1113	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String name, boolean own) {
1114		return getIdentityKeyCursor(db, account, name, own, null);
1115	}
1116
1117	private Cursor getIdentityKeyCursor(Account account, String fingerprint) {
1118		final SQLiteDatabase db = this.getReadableDatabase();
1119		return getIdentityKeyCursor(db, account, fingerprint);
1120	}
1121
1122	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String fingerprint) {
1123		return getIdentityKeyCursor(db, account, null, null, fingerprint);
1124	}
1125
1126	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String name, Boolean own, String fingerprint) {
1127		String[] columns = {SQLiteAxolotlStore.TRUST,
1128				SQLiteAxolotlStore.ACTIVE,
1129				SQLiteAxolotlStore.LAST_ACTIVATION,
1130				SQLiteAxolotlStore.KEY};
1131		ArrayList<String> selectionArgs = new ArrayList<>(4);
1132		selectionArgs.add(account.getUuid());
1133		String selectionString = SQLiteAxolotlStore.ACCOUNT + " = ?";
1134		if (name != null) {
1135			selectionArgs.add(name);
1136			selectionString += " AND " + SQLiteAxolotlStore.NAME + " = ?";
1137		}
1138		if (fingerprint != null) {
1139			selectionArgs.add(fingerprint);
1140			selectionString += " AND " + SQLiteAxolotlStore.FINGERPRINT + " = ?";
1141		}
1142		if (own != null) {
1143			selectionArgs.add(own ? "1" : "0");
1144			selectionString += " AND " + SQLiteAxolotlStore.OWN + " = ?";
1145		}
1146		Cursor cursor = db.query(SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1147				columns,
1148				selectionString,
1149				selectionArgs.toArray(new String[selectionArgs.size()]),
1150				null, null, null);
1151
1152		return cursor;
1153	}
1154
1155	public IdentityKeyPair loadOwnIdentityKeyPair(Account account) {
1156		SQLiteDatabase db = getReadableDatabase();
1157		return loadOwnIdentityKeyPair(db, account);
1158	}
1159
1160	private IdentityKeyPair loadOwnIdentityKeyPair(SQLiteDatabase db, Account account) {
1161		String name = account.getJid().toBareJid().toPreppedString();
1162		IdentityKeyPair identityKeyPair = null;
1163		Cursor cursor = getIdentityKeyCursor(db, account, name, true);
1164		if (cursor.getCount() != 0) {
1165			cursor.moveToFirst();
1166			try {
1167				identityKeyPair = new IdentityKeyPair(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
1168			} catch (InvalidKeyException e) {
1169				Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Encountered invalid IdentityKey in database for account" + account.getJid().toBareJid() + ", address: " + name);
1170			}
1171		}
1172		cursor.close();
1173
1174		return identityKeyPair;
1175	}
1176
1177	public Set<IdentityKey> loadIdentityKeys(Account account, String name) {
1178		return loadIdentityKeys(account, name, null);
1179	}
1180
1181	public Set<IdentityKey> loadIdentityKeys(Account account, String name, FingerprintStatus status) {
1182		Set<IdentityKey> identityKeys = new HashSet<>();
1183		Cursor cursor = getIdentityKeyCursor(account, name, false);
1184
1185		while (cursor.moveToNext()) {
1186			if (status != null && !FingerprintStatus.fromCursor(cursor).equals(status)) {
1187				continue;
1188			}
1189			try {
1190				String key = cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY));
1191				if (key != null) {
1192					identityKeys.add(new IdentityKey(Base64.decode(key, Base64.DEFAULT), 0));
1193				} else {
1194					Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Missing key (possibly preverified) in database for account" + account.getJid().toBareJid() + ", address: " + name);
1195				}
1196			} catch (InvalidKeyException e) {
1197				Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Encountered invalid IdentityKey in database for account" + account.getJid().toBareJid() + ", address: " + name);
1198			}
1199		}
1200		cursor.close();
1201
1202		return identityKeys;
1203	}
1204
1205	public long numTrustedKeys(Account account, String name) {
1206		SQLiteDatabase db = getReadableDatabase();
1207		String[] args = {
1208				account.getUuid(),
1209				name,
1210				FingerprintStatus.Trust.TRUSTED.toString(),
1211				FingerprintStatus.Trust.VERIFIED.toString(),
1212				FingerprintStatus.Trust.VERIFIED_X509.toString()
1213		};
1214		return DatabaseUtils.queryNumEntries(db, SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1215				SQLiteAxolotlStore.ACCOUNT + " = ?"
1216						+ " AND " + SQLiteAxolotlStore.NAME + " = ?"
1217						+ " AND (" + SQLiteAxolotlStore.TRUST + " = ? OR " + SQLiteAxolotlStore.TRUST + " = ? OR " +SQLiteAxolotlStore.TRUST +" = ?)"
1218						+ " AND " +SQLiteAxolotlStore.ACTIVE + " > 0",
1219				args
1220		);
1221	}
1222
1223	private void storeIdentityKey(Account account, String name, boolean own, String fingerprint, String base64Serialized, FingerprintStatus status) {
1224		SQLiteDatabase db = this.getWritableDatabase();
1225		ContentValues values = new ContentValues();
1226		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1227		values.put(SQLiteAxolotlStore.NAME, name);
1228		values.put(SQLiteAxolotlStore.OWN, own ? 1 : 0);
1229		values.put(SQLiteAxolotlStore.FINGERPRINT, fingerprint);
1230		values.put(SQLiteAxolotlStore.KEY, base64Serialized);
1231		values.putAll(status.toContentValues());
1232		String where = SQLiteAxolotlStore.ACCOUNT+"=? AND "+SQLiteAxolotlStore.NAME+"=? AND "+SQLiteAxolotlStore.FINGERPRINT+" =?";
1233		String[] whereArgs = {account.getUuid(),name,fingerprint};
1234		int rows = db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME,values,where,whereArgs);
1235		if (rows == 0) {
1236			db.insert(SQLiteAxolotlStore.IDENTITIES_TABLENAME, null, values);
1237		}
1238	}
1239
1240	public void storePreVerification(Account account, String name, String fingerprint, FingerprintStatus status) {
1241		SQLiteDatabase db = this.getWritableDatabase();
1242		ContentValues values = new ContentValues();
1243		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1244		values.put(SQLiteAxolotlStore.NAME, name);
1245		values.put(SQLiteAxolotlStore.OWN, 0);
1246		values.put(SQLiteAxolotlStore.FINGERPRINT, fingerprint);
1247		values.putAll(status.toContentValues());
1248		db.insert(SQLiteAxolotlStore.IDENTITIES_TABLENAME, null, values);
1249	}
1250
1251	public FingerprintStatus getFingerprintStatus(Account account, String fingerprint) {
1252		Cursor cursor = getIdentityKeyCursor(account, fingerprint);
1253		final FingerprintStatus status;
1254		if (cursor.getCount() > 0) {
1255			cursor.moveToFirst();
1256			status = FingerprintStatus.fromCursor(cursor);
1257		} else {
1258			status = null;
1259		}
1260		cursor.close();
1261		return status;
1262	}
1263
1264	public boolean setIdentityKeyTrust(Account account, String fingerprint, FingerprintStatus fingerprintStatus) {
1265		SQLiteDatabase db = this.getWritableDatabase();
1266		return setIdentityKeyTrust(db, account, fingerprint, fingerprintStatus);
1267	}
1268
1269	private boolean setIdentityKeyTrust(SQLiteDatabase db, Account account, String fingerprint, FingerprintStatus status) {
1270		String[] selectionArgs = {
1271				account.getUuid(),
1272				fingerprint
1273		};
1274		int rows = db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME, status.toContentValues(),
1275				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
1276						+ SQLiteAxolotlStore.FINGERPRINT + " = ? ",
1277				selectionArgs);
1278		return rows == 1;
1279	}
1280
1281	public boolean setIdentityKeyCertificate(Account account, String fingerprint, X509Certificate x509Certificate) {
1282		SQLiteDatabase db = this.getWritableDatabase();
1283		String[] selectionArgs = {
1284				account.getUuid(),
1285				fingerprint
1286		};
1287		try {
1288			ContentValues values = new ContentValues();
1289			values.put(SQLiteAxolotlStore.CERTIFICATE, x509Certificate.getEncoded());
1290			return db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME, values,
1291					SQLiteAxolotlStore.ACCOUNT + " = ? AND "
1292							+ SQLiteAxolotlStore.FINGERPRINT + " = ? ",
1293					selectionArgs) == 1;
1294		} catch (CertificateEncodingException e) {
1295			Log.d(Config.LOGTAG, "could not encode certificate");
1296			return false;
1297		}
1298	}
1299
1300	public X509Certificate getIdentityKeyCertifcate(Account account, String fingerprint) {
1301		SQLiteDatabase db = this.getReadableDatabase();
1302		String[] selectionArgs = {
1303				account.getUuid(),
1304				fingerprint
1305		};
1306		String[] colums = {SQLiteAxolotlStore.CERTIFICATE};
1307		String selection = SQLiteAxolotlStore.ACCOUNT + " = ? AND " + SQLiteAxolotlStore.FINGERPRINT + " = ? ";
1308		Cursor cursor = db.query(SQLiteAxolotlStore.IDENTITIES_TABLENAME, colums, selection, selectionArgs, null, null, null);
1309		if (cursor.getCount() < 1) {
1310			return null;
1311		} else {
1312			cursor.moveToFirst();
1313			byte[] certificate = cursor.getBlob(cursor.getColumnIndex(SQLiteAxolotlStore.CERTIFICATE));
1314			cursor.close();
1315			if (certificate == null || certificate.length == 0) {
1316				return null;
1317			}
1318			try {
1319				CertificateFactory certificateFactory = CertificateFactory.getInstance("X.509");
1320				return (X509Certificate) certificateFactory.generateCertificate(new ByteArrayInputStream(certificate));
1321			} catch (CertificateException e) {
1322				Log.d(Config.LOGTAG,"certificate exception "+e.getMessage());
1323				return null;
1324			}
1325		}
1326	}
1327
1328	public void storeIdentityKey(Account account, String name, IdentityKey identityKey, FingerprintStatus status) {
1329		storeIdentityKey(account, name, false, identityKey.getFingerprint().replaceAll("\\s", ""), Base64.encodeToString(identityKey.serialize(), Base64.DEFAULT), status);
1330	}
1331
1332	public void storeOwnIdentityKeyPair(Account account, IdentityKeyPair identityKeyPair) {
1333		storeIdentityKey(account, account.getJid().toBareJid().toPreppedString(), true, identityKeyPair.getPublicKey().getFingerprint().replaceAll("\\s", ""), Base64.encodeToString(identityKeyPair.serialize(), Base64.DEFAULT), FingerprintStatus.createActiveVerified(false));
1334	}
1335
1336
1337	public void recreateAxolotlDb(SQLiteDatabase db) {
1338		Log.d(Config.LOGTAG, AxolotlService.LOGPREFIX + " : " + ">>> (RE)CREATING AXOLOTL DATABASE <<<");
1339		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.SESSION_TABLENAME);
1340		db.execSQL(CREATE_SESSIONS_STATEMENT);
1341		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.PREKEY_TABLENAME);
1342		db.execSQL(CREATE_PREKEYS_STATEMENT);
1343		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME);
1344		db.execSQL(CREATE_SIGNED_PREKEYS_STATEMENT);
1345		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.IDENTITIES_TABLENAME);
1346		db.execSQL(CREATE_IDENTITIES_STATEMENT);
1347	}
1348
1349	public void wipeAxolotlDb(Account account) {
1350		String accountName = account.getUuid();
1351		Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + ">>> WIPING AXOLOTL DATABASE FOR ACCOUNT " + accountName + " <<<");
1352		SQLiteDatabase db = this.getWritableDatabase();
1353		String[] deleteArgs = {
1354				accountName
1355		};
1356		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
1357				SQLiteAxolotlStore.ACCOUNT + " = ?",
1358				deleteArgs);
1359		db.delete(SQLiteAxolotlStore.PREKEY_TABLENAME,
1360				SQLiteAxolotlStore.ACCOUNT + " = ?",
1361				deleteArgs);
1362		db.delete(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1363				SQLiteAxolotlStore.ACCOUNT + " = ?",
1364				deleteArgs);
1365		db.delete(SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1366				SQLiteAxolotlStore.ACCOUNT + " = ?",
1367				deleteArgs);
1368	}
1369
1370	public boolean startTimeCountExceedsThreshold() {
1371		SQLiteDatabase db = this.getWritableDatabase();
1372		long cleanBeforeTimestamp = System.currentTimeMillis() - Config.FREQUENT_RESTARTS_DETECTION_WINDOW;
1373		db.execSQL("delete from "+START_TIMES_TABLE+" where timestamp < "+cleanBeforeTimestamp);
1374		ContentValues values = new ContentValues();
1375		values.put("timestamp",System.currentTimeMillis());
1376		db.insert(START_TIMES_TABLE,null,values);
1377		String[] columns = new String[]{"count(timestamp)"};
1378		Cursor cursor = db.query(START_TIMES_TABLE,columns,null,null,null,null,null);
1379		int count;
1380		if (cursor.moveToFirst()) {
1381			count = cursor.getInt(0);
1382		} else {
1383			count = 0;
1384		}
1385		cursor.close();
1386		Log.d(Config.LOGTAG,"start time counter reached "+count);
1387		return count >= Config.FREQUENT_RESTARTS_THRESHOLD;
1388	}
1389
1390	public void clearStartTimeCounter(boolean justOne) {
1391		SQLiteDatabase db = this.getWritableDatabase();
1392		if (justOne) {
1393			db.execSQL("delete from "+START_TIMES_TABLE+" where timestamp in (select timestamp from "+START_TIMES_TABLE+" order by timestamp desc limit 1)");
1394			Log.d(Config.LOGTAG,"do not count start up after being swiped away");
1395		} else {
1396			Log.d(Config.LOGTAG,"resetting start time counter");
1397			db.execSQL("delete from " + START_TIMES_TABLE);
1398		}
1399	}
1400}