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.util.Base64;
  11import android.util.Log;
  12import android.util.Pair;
  13
  14import org.whispersystems.libaxolotl.AxolotlAddress;
  15import org.whispersystems.libaxolotl.IdentityKey;
  16import org.whispersystems.libaxolotl.IdentityKeyPair;
  17import org.whispersystems.libaxolotl.InvalidKeyException;
  18import org.whispersystems.libaxolotl.state.PreKeyRecord;
  19import org.whispersystems.libaxolotl.state.SessionRecord;
  20import org.whispersystems.libaxolotl.state.SignedPreKeyRecord;
  21
  22import java.io.IOException;
  23import java.util.ArrayList;
  24import java.util.HashSet;
  25import java.util.Iterator;
  26import java.util.List;
  27import java.util.Set;
  28import java.util.concurrent.CopyOnWriteArrayList;
  29
  30import eu.siacs.conversations.Config;
  31import eu.siacs.conversations.crypto.axolotl.AxolotlService;
  32import eu.siacs.conversations.crypto.axolotl.SQLiteAxolotlStore;
  33import eu.siacs.conversations.crypto.axolotl.XmppAxolotlSession;
  34import eu.siacs.conversations.entities.Account;
  35import eu.siacs.conversations.entities.Contact;
  36import eu.siacs.conversations.entities.Conversation;
  37import eu.siacs.conversations.entities.Message;
  38import eu.siacs.conversations.entities.Roster;
  39import eu.siacs.conversations.xmpp.jid.InvalidJidException;
  40import eu.siacs.conversations.xmpp.jid.Jid;
  41
  42public class DatabaseBackend extends SQLiteOpenHelper {
  43
  44	private static DatabaseBackend instance = null;
  45
  46	private static final String DATABASE_NAME = "history";
  47	private static final int DATABASE_VERSION = 21;
  48
  49	private static String CREATE_CONTATCS_STATEMENT = "create table "
  50			+ Contact.TABLENAME + "(" + Contact.ACCOUNT + " TEXT, "
  51			+ Contact.SERVERNAME + " TEXT, " + Contact.SYSTEMNAME + " TEXT,"
  52			+ Contact.JID + " TEXT," + Contact.KEYS + " TEXT,"
  53			+ Contact.PHOTOURI + " TEXT," + Contact.OPTIONS + " NUMBER,"
  54			+ Contact.SYSTEMACCOUNT + " NUMBER, " + Contact.AVATAR + " TEXT, "
  55			+ Contact.LAST_PRESENCE + " TEXT, " + Contact.LAST_TIME + " NUMBER, "
  56			+ Contact.GROUPS + " TEXT, FOREIGN KEY(" + Contact.ACCOUNT + ") REFERENCES "
  57			+ Account.TABLENAME + "(" + Account.UUID
  58			+ ") ON DELETE CASCADE, UNIQUE(" + Contact.ACCOUNT + ", "
  59			+ Contact.JID + ") ON CONFLICT REPLACE);";
  60
  61	private static String CREATE_PREKEYS_STATEMENT = "CREATE TABLE "
  62			+ SQLiteAxolotlStore.PREKEY_TABLENAME + "("
  63			+ SQLiteAxolotlStore.ACCOUNT + " TEXT,  "
  64			+ SQLiteAxolotlStore.ID + " INTEGER, "
  65			+ SQLiteAxolotlStore.KEY + " TEXT, FOREIGN KEY("
  66			+ SQLiteAxolotlStore.ACCOUNT
  67			+ ") REFERENCES " + Account.TABLENAME + "(" + Account.UUID + ") ON DELETE CASCADE, "
  68			+ "UNIQUE( " + SQLiteAxolotlStore.ACCOUNT + ", "
  69			+ SQLiteAxolotlStore.ID
  70			+ ") ON CONFLICT REPLACE"
  71			+ ");";
  72
  73	private static String CREATE_SIGNED_PREKEYS_STATEMENT = "CREATE TABLE "
  74			+ SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME + "("
  75			+ SQLiteAxolotlStore.ACCOUNT + " TEXT,  "
  76			+ SQLiteAxolotlStore.ID + " INTEGER, "
  77			+ SQLiteAxolotlStore.KEY + " TEXT, FOREIGN KEY("
  78			+ SQLiteAxolotlStore.ACCOUNT
  79			+ ") REFERENCES " + Account.TABLENAME + "(" + Account.UUID + ") ON DELETE CASCADE, "
  80			+ "UNIQUE( " + SQLiteAxolotlStore.ACCOUNT + ", "
  81			+ SQLiteAxolotlStore.ID
  82			+ ") ON CONFLICT REPLACE" +
  83			");";
  84
  85	private static String CREATE_SESSIONS_STATEMENT = "CREATE TABLE "
  86			+ SQLiteAxolotlStore.SESSION_TABLENAME + "("
  87			+ SQLiteAxolotlStore.ACCOUNT + " TEXT,  "
  88			+ SQLiteAxolotlStore.NAME + " TEXT, "
  89			+ SQLiteAxolotlStore.DEVICE_ID + " INTEGER, "
  90			+ SQLiteAxolotlStore.KEY + " TEXT, FOREIGN KEY("
  91			+ SQLiteAxolotlStore.ACCOUNT
  92			+ ") REFERENCES " + Account.TABLENAME + "(" + Account.UUID + ") ON DELETE CASCADE, "
  93			+ "UNIQUE( " + SQLiteAxolotlStore.ACCOUNT + ", "
  94			+ SQLiteAxolotlStore.NAME + ", "
  95			+ SQLiteAxolotlStore.DEVICE_ID
  96			+ ") ON CONFLICT REPLACE"
  97			+ ");";
  98
  99	private static String CREATE_IDENTITIES_STATEMENT = "CREATE TABLE "
 100			+ SQLiteAxolotlStore.IDENTITIES_TABLENAME + "("
 101			+ SQLiteAxolotlStore.ACCOUNT + " TEXT,  "
 102			+ SQLiteAxolotlStore.NAME + " TEXT, "
 103			+ SQLiteAxolotlStore.OWN + " INTEGER, "
 104			+ SQLiteAxolotlStore.FINGERPRINT + " TEXT, "
 105			+ SQLiteAxolotlStore.TRUSTED + " INTEGER, "
 106			+ SQLiteAxolotlStore.KEY + " TEXT, FOREIGN KEY("
 107			+ SQLiteAxolotlStore.ACCOUNT
 108			+ ") REFERENCES " + Account.TABLENAME + "(" + Account.UUID + ") ON DELETE CASCADE, "
 109			+ "UNIQUE( " + SQLiteAxolotlStore.ACCOUNT + ", "
 110			+ SQLiteAxolotlStore.NAME + ", "
 111			+ SQLiteAxolotlStore.FINGERPRINT
 112			+ ") ON CONFLICT IGNORE"
 113			+ ");";
 114
 115	private DatabaseBackend(Context context) {
 116		super(context, DATABASE_NAME, null, DATABASE_VERSION);
 117	}
 118
 119	@Override
 120	public void onCreate(SQLiteDatabase db) {
 121		db.execSQL("PRAGMA foreign_keys=ON;");
 122		db.execSQL("create table " + Account.TABLENAME + "(" + Account.UUID
 123				+ " TEXT PRIMARY KEY," + Account.USERNAME + " TEXT,"
 124				+ Account.SERVER + " TEXT," + Account.PASSWORD + " TEXT,"
 125				+ Account.DISPLAY_NAME + " TEXT, "
 126				+ Account.ROSTERVERSION + " TEXT," + Account.OPTIONS
 127				+ " NUMBER, " + Account.AVATAR + " TEXT, " + Account.KEYS
 128				+ " TEXT, " + Account.HOSTNAME + " TEXT, " + Account.PORT + " NUMBER DEFAULT 5222)");
 129		db.execSQL("create table " + Conversation.TABLENAME + " ("
 130				+ Conversation.UUID + " TEXT PRIMARY KEY, " + Conversation.NAME
 131				+ " TEXT, " + Conversation.CONTACT + " TEXT, "
 132				+ Conversation.ACCOUNT + " TEXT, " + Conversation.CONTACTJID
 133				+ " TEXT, " + Conversation.CREATED + " NUMBER, "
 134				+ Conversation.STATUS + " NUMBER, " + Conversation.MODE
 135				+ " NUMBER, " + Conversation.ATTRIBUTES + " TEXT, FOREIGN KEY("
 136				+ Conversation.ACCOUNT + ") REFERENCES " + Account.TABLENAME
 137				+ "(" + Account.UUID + ") ON DELETE CASCADE);");
 138		db.execSQL("create table " + Message.TABLENAME + "( " + Message.UUID
 139				+ " TEXT PRIMARY KEY, " + Message.CONVERSATION + " TEXT, "
 140				+ Message.TIME_SENT + " NUMBER, " + Message.COUNTERPART
 141				+ " TEXT, " + Message.TRUE_COUNTERPART + " TEXT,"
 142				+ Message.BODY + " TEXT, " + Message.ENCRYPTION + " NUMBER, "
 143				+ Message.STATUS + " NUMBER," + Message.TYPE + " NUMBER, "
 144				+ Message.RELATIVE_FILE_PATH + " TEXT, "
 145				+ Message.SERVER_MSG_ID + " TEXT, "
 146				+ Message.FINGERPRINT + " TEXT, "
 147				+ Message.CARBON + " INTEGER, "
 148				+ Message.READ + " NUMBER DEFAULT 1, "
 149				+ Message.REMOTE_MSG_ID + " TEXT, FOREIGN KEY("
 150				+ Message.CONVERSATION + ") REFERENCES "
 151				+ Conversation.TABLENAME + "(" + Conversation.UUID
 152				+ ") ON DELETE CASCADE);");
 153
 154		db.execSQL(CREATE_CONTATCS_STATEMENT);
 155		db.execSQL(CREATE_SESSIONS_STATEMENT);
 156		db.execSQL(CREATE_PREKEYS_STATEMENT);
 157		db.execSQL(CREATE_SIGNED_PREKEYS_STATEMENT);
 158		db.execSQL(CREATE_IDENTITIES_STATEMENT);
 159	}
 160
 161	@Override
 162	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 163		if (oldVersion < 2 && newVersion >= 2) {
 164			db.execSQL("update " + Account.TABLENAME + " set "
 165					+ Account.OPTIONS + " = " + Account.OPTIONS + " | 8");
 166		}
 167		if (oldVersion < 3 && newVersion >= 3) {
 168			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
 169					+ Message.TYPE + " NUMBER");
 170		}
 171		if (oldVersion < 5 && newVersion >= 5) {
 172			db.execSQL("DROP TABLE " + Contact.TABLENAME);
 173			db.execSQL(CREATE_CONTATCS_STATEMENT);
 174			db.execSQL("UPDATE " + Account.TABLENAME + " SET "
 175					+ Account.ROSTERVERSION + " = NULL");
 176		}
 177		if (oldVersion < 6 && newVersion >= 6) {
 178			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
 179					+ Message.TRUE_COUNTERPART + " TEXT");
 180		}
 181		if (oldVersion < 7 && newVersion >= 7) {
 182			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
 183					+ Message.REMOTE_MSG_ID + " TEXT");
 184			db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
 185					+ Contact.AVATAR + " TEXT");
 186			db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN "
 187					+ Account.AVATAR + " TEXT");
 188		}
 189		if (oldVersion < 8 && newVersion >= 8) {
 190			db.execSQL("ALTER TABLE " + Conversation.TABLENAME + " ADD COLUMN "
 191					+ Conversation.ATTRIBUTES + " TEXT");
 192		}
 193		if (oldVersion < 9 && newVersion >= 9) {
 194			db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
 195					+ Contact.LAST_TIME + " NUMBER");
 196			db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
 197					+ Contact.LAST_PRESENCE + " TEXT");
 198		}
 199		if (oldVersion < 10 && newVersion >= 10) {
 200			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
 201					+ Message.RELATIVE_FILE_PATH + " TEXT");
 202		}
 203		if (oldVersion < 11 && newVersion >= 11) {
 204			db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
 205					+ Contact.GROUPS + " TEXT");
 206			db.execSQL("delete from " + Contact.TABLENAME);
 207			db.execSQL("update " + Account.TABLENAME + " set " + Account.ROSTERVERSION + " = NULL");
 208		}
 209		if (oldVersion < 12 && newVersion >= 12) {
 210			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
 211					+ Message.SERVER_MSG_ID + " TEXT");
 212		}
 213		if (oldVersion < 13 && newVersion >= 13) {
 214			db.execSQL("delete from " + Contact.TABLENAME);
 215			db.execSQL("update " + Account.TABLENAME + " set " + Account.ROSTERVERSION + " = NULL");
 216		}
 217		if (oldVersion < 14 && newVersion >= 14) {
 218			// migrate db to new, canonicalized JID domainpart representation
 219
 220			// Conversation table
 221			Cursor cursor = db.rawQuery("select * from " + Conversation.TABLENAME, new String[0]);
 222			while (cursor.moveToNext()) {
 223				String newJid;
 224				try {
 225					newJid = Jid.fromString(
 226							cursor.getString(cursor.getColumnIndex(Conversation.CONTACTJID))
 227					).toString();
 228				} catch (InvalidJidException ignored) {
 229					Log.e(Config.LOGTAG, "Failed to migrate Conversation CONTACTJID "
 230							+ cursor.getString(cursor.getColumnIndex(Conversation.CONTACTJID))
 231							+ ": " + ignored + ". Skipping...");
 232					continue;
 233				}
 234
 235				String updateArgs[] = {
 236						newJid,
 237						cursor.getString(cursor.getColumnIndex(Conversation.UUID)),
 238				};
 239				db.execSQL("update " + Conversation.TABLENAME
 240						+ " set " + Conversation.CONTACTJID + " = ? "
 241						+ " where " + Conversation.UUID + " = ?", updateArgs);
 242			}
 243			cursor.close();
 244
 245			// Contact table
 246			cursor = db.rawQuery("select * from " + Contact.TABLENAME, new String[0]);
 247			while (cursor.moveToNext()) {
 248				String newJid;
 249				try {
 250					newJid = Jid.fromString(
 251							cursor.getString(cursor.getColumnIndex(Contact.JID))
 252					).toString();
 253				} catch (InvalidJidException ignored) {
 254					Log.e(Config.LOGTAG, "Failed to migrate Contact JID "
 255							+ cursor.getString(cursor.getColumnIndex(Contact.JID))
 256							+ ": " + ignored + ". Skipping...");
 257					continue;
 258				}
 259
 260				String updateArgs[] = {
 261						newJid,
 262						cursor.getString(cursor.getColumnIndex(Contact.ACCOUNT)),
 263						cursor.getString(cursor.getColumnIndex(Contact.JID)),
 264				};
 265				db.execSQL("update " + Contact.TABLENAME
 266						+ " set " + Contact.JID + " = ? "
 267						+ " where " + Contact.ACCOUNT + " = ? "
 268						+ " AND " + Contact.JID + " = ?", updateArgs);
 269			}
 270			cursor.close();
 271
 272			// Account table
 273			cursor = db.rawQuery("select * from " + Account.TABLENAME, new String[0]);
 274			while (cursor.moveToNext()) {
 275				String newServer;
 276				try {
 277					newServer = Jid.fromParts(
 278							cursor.getString(cursor.getColumnIndex(Account.USERNAME)),
 279							cursor.getString(cursor.getColumnIndex(Account.SERVER)),
 280							"mobile"
 281					).getDomainpart();
 282				} catch (InvalidJidException ignored) {
 283					Log.e(Config.LOGTAG, "Failed to migrate Account SERVER "
 284							+ cursor.getString(cursor.getColumnIndex(Account.SERVER))
 285							+ ": " + ignored + ". Skipping...");
 286					continue;
 287				}
 288
 289				String updateArgs[] = {
 290						newServer,
 291						cursor.getString(cursor.getColumnIndex(Account.UUID)),
 292				};
 293				db.execSQL("update " + Account.TABLENAME
 294						+ " set " + Account.SERVER + " = ? "
 295						+ " where " + Account.UUID + " = ?", updateArgs);
 296			}
 297			cursor.close();
 298		}
 299		if (oldVersion < 15 && newVersion >= 15) {
 300			recreateAxolotlDb(db);
 301			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
 302					+ Message.FINGERPRINT + " TEXT");
 303		}
 304		if (oldVersion < 16 && newVersion >= 16) {
 305			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
 306					+ Message.CARBON + " INTEGER");
 307		}
 308		if (oldVersion < 19 && newVersion >= 19) {
 309			db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN " + Account.DISPLAY_NAME + " TEXT");
 310		}
 311		if (oldVersion < 20 && newVersion >= 20) {
 312			db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN " + Account.HOSTNAME + " TEXT");
 313			db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN " + Account.PORT + " NUMBER DEFAULT 5222");
 314		}
 315		/* Any migrations that alter the Account table need to happen BEFORE this migration, as it
 316		 * depends on account de-serialization.
 317		 */
 318		if (oldVersion < 17 && newVersion >= 17) {
 319			List<Account> accounts = getAccounts(db);
 320			for (Account account : accounts) {
 321				String ownDeviceIdString = account.getKey(SQLiteAxolotlStore.JSONKEY_REGISTRATION_ID);
 322				if (ownDeviceIdString == null) {
 323					continue;
 324				}
 325				int ownDeviceId = Integer.valueOf(ownDeviceIdString);
 326				AxolotlAddress ownAddress = new AxolotlAddress(account.getJid().toBareJid().toString(), ownDeviceId);
 327				deleteSession(db, account, ownAddress);
 328				IdentityKeyPair identityKeyPair = loadOwnIdentityKeyPair(db, account);
 329				if (identityKeyPair != null) {
 330					setIdentityKeyTrust(db, account, identityKeyPair.getPublicKey().getFingerprint().replaceAll("\\s", ""), XmppAxolotlSession.Trust.TRUSTED);
 331				} else {
 332					Log.d(Config.LOGTAG, account.getJid().toBareJid() + ": could not load own identity key pair");
 333				}
 334			}
 335		}
 336		if (oldVersion < 18 && newVersion >= 18) {
 337			db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN " + Message.READ + " NUMBER DEFAULT 1");
 338		}
 339
 340		if (oldVersion < 21 && newVersion >= 21) {
 341			List<Account> accounts = getAccounts(db);
 342			for (Account account : accounts) {
 343				account.unsetPgpSignature();
 344				db.update(Account.TABLENAME, account.getContentValues(), Account.UUID
 345						+ "=?", new String[]{account.getUuid()});
 346			}
 347		}
 348	}
 349
 350	public static synchronized DatabaseBackend getInstance(Context context) {
 351		if (instance == null) {
 352			instance = new DatabaseBackend(context);
 353		}
 354		return instance;
 355	}
 356
 357	public void createConversation(Conversation conversation) {
 358		SQLiteDatabase db = this.getWritableDatabase();
 359		db.insert(Conversation.TABLENAME, null, conversation.getContentValues());
 360	}
 361
 362	public void createMessage(Message message) {
 363		SQLiteDatabase db = this.getWritableDatabase();
 364		db.insert(Message.TABLENAME, null, message.getContentValues());
 365	}
 366
 367	public void createAccount(Account account) {
 368		SQLiteDatabase db = this.getWritableDatabase();
 369		db.insert(Account.TABLENAME, null, account.getContentValues());
 370	}
 371
 372	public void createContact(Contact contact) {
 373		SQLiteDatabase db = this.getWritableDatabase();
 374		db.insert(Contact.TABLENAME, null, contact.getContentValues());
 375	}
 376
 377	public int getConversationCount() {
 378		SQLiteDatabase db = this.getReadableDatabase();
 379		Cursor cursor = db.rawQuery("select count(uuid) as count from "
 380				+ Conversation.TABLENAME + " where " + Conversation.STATUS
 381				+ "=" + Conversation.STATUS_AVAILABLE, null);
 382		cursor.moveToFirst();
 383		int count = cursor.getInt(0);
 384		cursor.close();
 385		return count;
 386	}
 387
 388	public CopyOnWriteArrayList<Conversation> getConversations(int status) {
 389		CopyOnWriteArrayList<Conversation> list = new CopyOnWriteArrayList<>();
 390		SQLiteDatabase db = this.getReadableDatabase();
 391		String[] selectionArgs = {Integer.toString(status)};
 392		Cursor cursor = db.rawQuery("select * from " + Conversation.TABLENAME
 393				+ " where " + Conversation.STATUS + " = ? order by "
 394				+ Conversation.CREATED + " desc", selectionArgs);
 395		while (cursor.moveToNext()) {
 396			list.add(Conversation.fromCursor(cursor));
 397		}
 398		cursor.close();
 399		return list;
 400	}
 401
 402	public ArrayList<Message> getMessages(Conversation conversations, int limit) {
 403		return getMessages(conversations, limit, -1);
 404	}
 405
 406	public ArrayList<Message> getMessages(Conversation conversation, int limit,
 407										  long timestamp) {
 408		ArrayList<Message> list = new ArrayList<>();
 409		SQLiteDatabase db = this.getReadableDatabase();
 410		Cursor cursor;
 411		if (timestamp == -1) {
 412			String[] selectionArgs = {conversation.getUuid()};
 413			cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 414					+ "=?", selectionArgs, null, null, Message.TIME_SENT
 415					+ " DESC", String.valueOf(limit));
 416		} else {
 417			String[] selectionArgs = {conversation.getUuid(),
 418					Long.toString(timestamp)};
 419			cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 420							+ "=? and " + Message.TIME_SENT + "<?", selectionArgs,
 421					null, null, Message.TIME_SENT + " DESC",
 422					String.valueOf(limit));
 423		}
 424		if (cursor.getCount() > 0) {
 425			cursor.moveToLast();
 426			do {
 427				Message message = Message.fromCursor(cursor);
 428				message.setConversation(conversation);
 429				list.add(message);
 430			} while (cursor.moveToPrevious());
 431		}
 432		cursor.close();
 433		return list;
 434	}
 435
 436	public Iterable<Message> getMessagesIterable(final Conversation conversation) {
 437		return new Iterable<Message>() {
 438			@Override
 439			public Iterator<Message> iterator() {
 440				class MessageIterator implements Iterator<Message> {
 441					SQLiteDatabase db = getReadableDatabase();
 442					String[] selectionArgs = {conversation.getUuid()};
 443					Cursor cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 444							+ "=?", selectionArgs, null, null, Message.TIME_SENT
 445							+ " ASC", null);
 446
 447					public MessageIterator() {
 448						cursor.moveToFirst();
 449					}
 450
 451					@Override
 452					public boolean hasNext() {
 453						return !cursor.isAfterLast();
 454					}
 455
 456					@Override
 457					public Message next() {
 458						Message message = Message.fromCursor(cursor);
 459						cursor.moveToNext();
 460						return message;
 461					}
 462
 463					@Override
 464					public void remove() {
 465						throw new UnsupportedOperationException();
 466					}
 467				}
 468				return new MessageIterator();
 469			}
 470		};
 471	}
 472
 473	public Conversation findConversation(final Account account, final Jid contactJid) {
 474		SQLiteDatabase db = this.getReadableDatabase();
 475		String[] selectionArgs = {account.getUuid(),
 476				contactJid.toBareJid().toString() + "/%",
 477				contactJid.toBareJid().toString()
 478		};
 479		Cursor cursor = db.query(Conversation.TABLENAME, null,
 480				Conversation.ACCOUNT + "=? AND (" + Conversation.CONTACTJID
 481						+ " like ? OR " + Conversation.CONTACTJID + "=?)", selectionArgs, null, null, null);
 482		if (cursor.getCount() == 0)
 483			return null;
 484		cursor.moveToFirst();
 485		Conversation conversation = Conversation.fromCursor(cursor);
 486		cursor.close();
 487		return conversation;
 488	}
 489
 490	public void updateConversation(final Conversation conversation) {
 491		final SQLiteDatabase db = this.getWritableDatabase();
 492		final String[] args = {conversation.getUuid()};
 493		db.update(Conversation.TABLENAME, conversation.getContentValues(),
 494				Conversation.UUID + "=?", args);
 495	}
 496
 497	public List<Account> getAccounts() {
 498		SQLiteDatabase db = this.getReadableDatabase();
 499		return getAccounts(db);
 500	}
 501
 502	private List<Account> getAccounts(SQLiteDatabase db) {
 503		List<Account> list = new ArrayList<>();
 504		Cursor cursor = db.query(Account.TABLENAME, null, null, null, null,
 505				null, null);
 506		while (cursor.moveToNext()) {
 507			list.add(Account.fromCursor(cursor));
 508		}
 509		cursor.close();
 510		return list;
 511	}
 512
 513	public void updateAccount(Account account) {
 514		SQLiteDatabase db = this.getWritableDatabase();
 515		String[] args = {account.getUuid()};
 516		db.update(Account.TABLENAME, account.getContentValues(), Account.UUID
 517				+ "=?", args);
 518	}
 519
 520	public void deleteAccount(Account account) {
 521		SQLiteDatabase db = this.getWritableDatabase();
 522		String[] args = {account.getUuid()};
 523		db.delete(Account.TABLENAME, Account.UUID + "=?", args);
 524	}
 525
 526	public boolean hasEnabledAccounts() {
 527		SQLiteDatabase db = this.getReadableDatabase();
 528		Cursor cursor = db.rawQuery("select count(" + Account.UUID + ")  from "
 529				+ Account.TABLENAME + " where not options & (1 <<1)", null);
 530		try {
 531			cursor.moveToFirst();
 532			int count = cursor.getInt(0);
 533			cursor.close();
 534			return (count > 0);
 535		} catch (SQLiteCantOpenDatabaseException e) {
 536			return true; // better safe than sorry
 537		} catch (RuntimeException e) {
 538			return true; // better safe than sorry
 539		}
 540	}
 541
 542	@Override
 543	public SQLiteDatabase getWritableDatabase() {
 544		SQLiteDatabase db = super.getWritableDatabase();
 545		db.execSQL("PRAGMA foreign_keys=ON;");
 546		return db;
 547	}
 548
 549	public void updateMessage(Message message) {
 550		SQLiteDatabase db = this.getWritableDatabase();
 551		String[] args = {message.getUuid()};
 552		db.update(Message.TABLENAME, message.getContentValues(), Message.UUID
 553				+ "=?", args);
 554	}
 555
 556	public void readRoster(Roster roster) {
 557		SQLiteDatabase db = this.getReadableDatabase();
 558		Cursor cursor;
 559		String args[] = {roster.getAccount().getUuid()};
 560		cursor = db.query(Contact.TABLENAME, null, Contact.ACCOUNT + "=?", args, null, null, null);
 561		while (cursor.moveToNext()) {
 562			roster.initContact(Contact.fromCursor(cursor));
 563		}
 564		cursor.close();
 565	}
 566
 567	public void writeRoster(final Roster roster) {
 568		final Account account = roster.getAccount();
 569		final SQLiteDatabase db = this.getWritableDatabase();
 570		for (Contact contact : roster.getContacts()) {
 571			if (contact.getOption(Contact.Options.IN_ROSTER)) {
 572				db.insert(Contact.TABLENAME, null, contact.getContentValues());
 573			} else {
 574				String where = Contact.ACCOUNT + "=? AND " + Contact.JID + "=?";
 575				String[] whereArgs = {account.getUuid(), contact.getJid().toString()};
 576				db.delete(Contact.TABLENAME, where, whereArgs);
 577			}
 578		}
 579		account.setRosterVersion(roster.getVersion());
 580		updateAccount(account);
 581	}
 582
 583	public void deleteMessage(Message message) {
 584		SQLiteDatabase db = this.getWritableDatabase();
 585		String[] args = {message.getUuid()};
 586		db.delete(Message.TABLENAME, Message.UUID + "=?", args);
 587	}
 588
 589	public void deleteMessagesInConversation(Conversation conversation) {
 590		SQLiteDatabase db = this.getWritableDatabase();
 591		String[] args = {conversation.getUuid()};
 592		db.delete(Message.TABLENAME, Message.CONVERSATION + "=?", args);
 593	}
 594
 595	public Pair<Long,String> getLastMessageReceived(Account account) {
 596		SQLiteDatabase db = this.getReadableDatabase();
 597		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";
 598		String[] args = {account.getUuid()};
 599		Cursor cursor = db.rawQuery(sql,args);
 600		if (cursor.getCount() ==0) {
 601			return null;
 602		} else {
 603			cursor.moveToFirst();
 604			return new Pair<>(cursor.getLong(0),cursor.getString(1));
 605		}
 606	}
 607
 608	public Conversation findConversationByUuid(String conversationUuid) {
 609		SQLiteDatabase db = this.getReadableDatabase();
 610		String[] selectionArgs = {conversationUuid};
 611		Cursor cursor = db.query(Conversation.TABLENAME, null,
 612				Conversation.UUID + "=?", selectionArgs, null, null, null);
 613		if (cursor.getCount() == 0) {
 614			return null;
 615		}
 616		cursor.moveToFirst();
 617		Conversation conversation = Conversation.fromCursor(cursor);
 618		cursor.close();
 619		return conversation;
 620	}
 621
 622	public Message findMessageByUuid(String messageUuid) {
 623		SQLiteDatabase db = this.getReadableDatabase();
 624		String[] selectionArgs = {messageUuid};
 625		Cursor cursor = db.query(Message.TABLENAME, null, Message.UUID + "=?",
 626				selectionArgs, null, null, null);
 627		if (cursor.getCount() == 0) {
 628			return null;
 629		}
 630		cursor.moveToFirst();
 631		Message message = Message.fromCursor(cursor);
 632		cursor.close();
 633		return message;
 634	}
 635
 636	public Account findAccountByUuid(String accountUuid) {
 637		SQLiteDatabase db = this.getReadableDatabase();
 638		String[] selectionArgs = {accountUuid};
 639		Cursor cursor = db.query(Account.TABLENAME, null, Account.UUID + "=?",
 640				selectionArgs, null, null, null);
 641		if (cursor.getCount() == 0) {
 642			return null;
 643		}
 644		cursor.moveToFirst();
 645		Account account = Account.fromCursor(cursor);
 646		cursor.close();
 647		return account;
 648	}
 649
 650	public List<Message> getImageMessages(Conversation conversation) {
 651		ArrayList<Message> list = new ArrayList<>();
 652		SQLiteDatabase db = this.getReadableDatabase();
 653		Cursor cursor;
 654		String[] selectionArgs = {conversation.getUuid(), String.valueOf(Message.TYPE_IMAGE)};
 655		cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 656				+ "=? AND " + Message.TYPE + "=?", selectionArgs, null, null, null);
 657		if (cursor.getCount() > 0) {
 658			cursor.moveToLast();
 659			do {
 660				Message message = Message.fromCursor(cursor);
 661				message.setConversation(conversation);
 662				list.add(message);
 663			} while (cursor.moveToPrevious());
 664		}
 665		cursor.close();
 666		return list;
 667	}
 668
 669	private Cursor getCursorForSession(Account account, AxolotlAddress contact) {
 670		final SQLiteDatabase db = this.getReadableDatabase();
 671		String[] columns = null;
 672		String[] selectionArgs = {account.getUuid(),
 673				contact.getName(),
 674				Integer.toString(contact.getDeviceId())};
 675		Cursor cursor = db.query(SQLiteAxolotlStore.SESSION_TABLENAME,
 676				columns,
 677				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 678						+ SQLiteAxolotlStore.NAME + " = ? AND "
 679						+ SQLiteAxolotlStore.DEVICE_ID + " = ? ",
 680				selectionArgs,
 681				null, null, null);
 682
 683		return cursor;
 684	}
 685
 686	public SessionRecord loadSession(Account account, AxolotlAddress contact) {
 687		SessionRecord session = null;
 688		Cursor cursor = getCursorForSession(account, contact);
 689		if (cursor.getCount() != 0) {
 690			cursor.moveToFirst();
 691			try {
 692				session = new SessionRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
 693			} catch (IOException e) {
 694				cursor.close();
 695				throw new AssertionError(e);
 696			}
 697		}
 698		cursor.close();
 699		return session;
 700	}
 701
 702	public List<Integer> getSubDeviceSessions(Account account, AxolotlAddress contact) {
 703		final SQLiteDatabase db = this.getReadableDatabase();
 704		return getSubDeviceSessions(db, account, contact);
 705	}
 706
 707	private List<Integer> getSubDeviceSessions(SQLiteDatabase db, Account account, AxolotlAddress contact) {
 708		List<Integer> devices = new ArrayList<>();
 709		String[] columns = {SQLiteAxolotlStore.DEVICE_ID};
 710		String[] selectionArgs = {account.getUuid(),
 711				contact.getName()};
 712		Cursor cursor = db.query(SQLiteAxolotlStore.SESSION_TABLENAME,
 713				columns,
 714				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 715						+ SQLiteAxolotlStore.NAME + " = ?",
 716				selectionArgs,
 717				null, null, null);
 718
 719		while (cursor.moveToNext()) {
 720			devices.add(cursor.getInt(
 721					cursor.getColumnIndex(SQLiteAxolotlStore.DEVICE_ID)));
 722		}
 723
 724		cursor.close();
 725		return devices;
 726	}
 727
 728	public boolean containsSession(Account account, AxolotlAddress contact) {
 729		Cursor cursor = getCursorForSession(account, contact);
 730		int count = cursor.getCount();
 731		cursor.close();
 732		return count != 0;
 733	}
 734
 735	public void storeSession(Account account, AxolotlAddress contact, SessionRecord session) {
 736		SQLiteDatabase db = this.getWritableDatabase();
 737		ContentValues values = new ContentValues();
 738		values.put(SQLiteAxolotlStore.NAME, contact.getName());
 739		values.put(SQLiteAxolotlStore.DEVICE_ID, contact.getDeviceId());
 740		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(session.serialize(), Base64.DEFAULT));
 741		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
 742		db.insert(SQLiteAxolotlStore.SESSION_TABLENAME, null, values);
 743	}
 744
 745	public void deleteSession(Account account, AxolotlAddress contact) {
 746		SQLiteDatabase db = this.getWritableDatabase();
 747		deleteSession(db, account, contact);
 748	}
 749
 750	private void deleteSession(SQLiteDatabase db, Account account, AxolotlAddress contact) {
 751		String[] args = {account.getUuid(),
 752				contact.getName(),
 753				Integer.toString(contact.getDeviceId())};
 754		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
 755				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 756						+ SQLiteAxolotlStore.NAME + " = ? AND "
 757						+ SQLiteAxolotlStore.DEVICE_ID + " = ? ",
 758				args);
 759	}
 760
 761	public void deleteAllSessions(Account account, AxolotlAddress contact) {
 762		SQLiteDatabase db = this.getWritableDatabase();
 763		String[] args = {account.getUuid(), contact.getName()};
 764		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
 765				SQLiteAxolotlStore.ACCOUNT + "=? AND "
 766						+ SQLiteAxolotlStore.NAME + " = ?",
 767				args);
 768	}
 769
 770	private Cursor getCursorForPreKey(Account account, int preKeyId) {
 771		SQLiteDatabase db = this.getReadableDatabase();
 772		String[] columns = {SQLiteAxolotlStore.KEY};
 773		String[] selectionArgs = {account.getUuid(), Integer.toString(preKeyId)};
 774		Cursor cursor = db.query(SQLiteAxolotlStore.PREKEY_TABLENAME,
 775				columns,
 776				SQLiteAxolotlStore.ACCOUNT + "=? AND "
 777						+ SQLiteAxolotlStore.ID + "=?",
 778				selectionArgs,
 779				null, null, null);
 780
 781		return cursor;
 782	}
 783
 784	public PreKeyRecord loadPreKey(Account account, int preKeyId) {
 785		PreKeyRecord record = null;
 786		Cursor cursor = getCursorForPreKey(account, preKeyId);
 787		if (cursor.getCount() != 0) {
 788			cursor.moveToFirst();
 789			try {
 790				record = new PreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
 791			} catch (IOException e) {
 792				throw new AssertionError(e);
 793			}
 794		}
 795		cursor.close();
 796		return record;
 797	}
 798
 799	public boolean containsPreKey(Account account, int preKeyId) {
 800		Cursor cursor = getCursorForPreKey(account, preKeyId);
 801		int count = cursor.getCount();
 802		cursor.close();
 803		return count != 0;
 804	}
 805
 806	public void storePreKey(Account account, PreKeyRecord record) {
 807		SQLiteDatabase db = this.getWritableDatabase();
 808		ContentValues values = new ContentValues();
 809		values.put(SQLiteAxolotlStore.ID, record.getId());
 810		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(record.serialize(), Base64.DEFAULT));
 811		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
 812		db.insert(SQLiteAxolotlStore.PREKEY_TABLENAME, null, values);
 813	}
 814
 815	public void deletePreKey(Account account, int preKeyId) {
 816		SQLiteDatabase db = this.getWritableDatabase();
 817		String[] args = {account.getUuid(), Integer.toString(preKeyId)};
 818		db.delete(SQLiteAxolotlStore.PREKEY_TABLENAME,
 819				SQLiteAxolotlStore.ACCOUNT + "=? AND "
 820						+ SQLiteAxolotlStore.ID + "=?",
 821				args);
 822	}
 823
 824	private Cursor getCursorForSignedPreKey(Account account, int signedPreKeyId) {
 825		SQLiteDatabase db = this.getReadableDatabase();
 826		String[] columns = {SQLiteAxolotlStore.KEY};
 827		String[] selectionArgs = {account.getUuid(), Integer.toString(signedPreKeyId)};
 828		Cursor cursor = db.query(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
 829				columns,
 830				SQLiteAxolotlStore.ACCOUNT + "=? AND " + SQLiteAxolotlStore.ID + "=?",
 831				selectionArgs,
 832				null, null, null);
 833
 834		return cursor;
 835	}
 836
 837	public SignedPreKeyRecord loadSignedPreKey(Account account, int signedPreKeyId) {
 838		SignedPreKeyRecord record = null;
 839		Cursor cursor = getCursorForSignedPreKey(account, signedPreKeyId);
 840		if (cursor.getCount() != 0) {
 841			cursor.moveToFirst();
 842			try {
 843				record = new SignedPreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
 844			} catch (IOException e) {
 845				throw new AssertionError(e);
 846			}
 847		}
 848		cursor.close();
 849		return record;
 850	}
 851
 852	public List<SignedPreKeyRecord> loadSignedPreKeys(Account account) {
 853		List<SignedPreKeyRecord> prekeys = new ArrayList<>();
 854		SQLiteDatabase db = this.getReadableDatabase();
 855		String[] columns = {SQLiteAxolotlStore.KEY};
 856		String[] selectionArgs = {account.getUuid()};
 857		Cursor cursor = db.query(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
 858				columns,
 859				SQLiteAxolotlStore.ACCOUNT + "=?",
 860				selectionArgs,
 861				null, null, null);
 862
 863		while (cursor.moveToNext()) {
 864			try {
 865				prekeys.add(new SignedPreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT)));
 866			} catch (IOException ignored) {
 867			}
 868		}
 869		cursor.close();
 870		return prekeys;
 871	}
 872
 873	public boolean containsSignedPreKey(Account account, int signedPreKeyId) {
 874		Cursor cursor = getCursorForPreKey(account, signedPreKeyId);
 875		int count = cursor.getCount();
 876		cursor.close();
 877		return count != 0;
 878	}
 879
 880	public void storeSignedPreKey(Account account, SignedPreKeyRecord record) {
 881		SQLiteDatabase db = this.getWritableDatabase();
 882		ContentValues values = new ContentValues();
 883		values.put(SQLiteAxolotlStore.ID, record.getId());
 884		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(record.serialize(), Base64.DEFAULT));
 885		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
 886		db.insert(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME, null, values);
 887	}
 888
 889	public void deleteSignedPreKey(Account account, int signedPreKeyId) {
 890		SQLiteDatabase db = this.getWritableDatabase();
 891		String[] args = {account.getUuid(), Integer.toString(signedPreKeyId)};
 892		db.delete(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
 893				SQLiteAxolotlStore.ACCOUNT + "=? AND "
 894						+ SQLiteAxolotlStore.ID + "=?",
 895				args);
 896	}
 897
 898	private Cursor getIdentityKeyCursor(Account account, String name, boolean own) {
 899		final SQLiteDatabase db = this.getReadableDatabase();
 900		return getIdentityKeyCursor(db, account, name, own);
 901	}
 902
 903	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String name, boolean own) {
 904		return getIdentityKeyCursor(db, account, name, own, null);
 905	}
 906
 907	private Cursor getIdentityKeyCursor(Account account, String fingerprint) {
 908		final SQLiteDatabase db = this.getReadableDatabase();
 909		return getIdentityKeyCursor(db, account, fingerprint);
 910	}
 911
 912	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String fingerprint) {
 913		return getIdentityKeyCursor(db, account, null, null, fingerprint);
 914	}
 915
 916	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String name, Boolean own, String fingerprint) {
 917		String[] columns = {SQLiteAxolotlStore.TRUSTED,
 918				SQLiteAxolotlStore.KEY};
 919		ArrayList<String> selectionArgs = new ArrayList<>(4);
 920		selectionArgs.add(account.getUuid());
 921		String selectionString = SQLiteAxolotlStore.ACCOUNT + " = ?";
 922		if (name != null) {
 923			selectionArgs.add(name);
 924			selectionString += " AND " + SQLiteAxolotlStore.NAME + " = ?";
 925		}
 926		if (fingerprint != null) {
 927			selectionArgs.add(fingerprint);
 928			selectionString += " AND " + SQLiteAxolotlStore.FINGERPRINT + " = ?";
 929		}
 930		if (own != null) {
 931			selectionArgs.add(own ? "1" : "0");
 932			selectionString += " AND " + SQLiteAxolotlStore.OWN + " = ?";
 933		}
 934		Cursor cursor = db.query(SQLiteAxolotlStore.IDENTITIES_TABLENAME,
 935				columns,
 936				selectionString,
 937				selectionArgs.toArray(new String[selectionArgs.size()]),
 938				null, null, null);
 939
 940		return cursor;
 941	}
 942
 943	public IdentityKeyPair loadOwnIdentityKeyPair(Account account) {
 944		SQLiteDatabase db = getReadableDatabase();
 945		return loadOwnIdentityKeyPair(db, account);
 946	}
 947
 948	private IdentityKeyPair loadOwnIdentityKeyPair(SQLiteDatabase db, Account account) {
 949		String name = account.getJid().toBareJid().toString();
 950		IdentityKeyPair identityKeyPair = null;
 951		Cursor cursor = getIdentityKeyCursor(db, account, name, true);
 952		if (cursor.getCount() != 0) {
 953			cursor.moveToFirst();
 954			try {
 955				identityKeyPair = new IdentityKeyPair(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
 956			} catch (InvalidKeyException e) {
 957				Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Encountered invalid IdentityKey in database for account" + account.getJid().toBareJid() + ", address: " + name);
 958			}
 959		}
 960		cursor.close();
 961
 962		return identityKeyPair;
 963	}
 964
 965	public Set<IdentityKey> loadIdentityKeys(Account account, String name) {
 966		return loadIdentityKeys(account, name, null);
 967	}
 968
 969	public Set<IdentityKey> loadIdentityKeys(Account account, String name, XmppAxolotlSession.Trust trust) {
 970		Set<IdentityKey> identityKeys = new HashSet<>();
 971		Cursor cursor = getIdentityKeyCursor(account, name, false);
 972
 973		while (cursor.moveToNext()) {
 974			if (trust != null &&
 975					cursor.getInt(cursor.getColumnIndex(SQLiteAxolotlStore.TRUSTED))
 976							!= trust.getCode()) {
 977				continue;
 978			}
 979			try {
 980				identityKeys.add(new IdentityKey(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT), 0));
 981			} catch (InvalidKeyException e) {
 982				Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Encountered invalid IdentityKey in database for account" + account.getJid().toBareJid() + ", address: " + name);
 983			}
 984		}
 985		cursor.close();
 986
 987		return identityKeys;
 988	}
 989
 990	public long numTrustedKeys(Account account, String name) {
 991		SQLiteDatabase db = getReadableDatabase();
 992		String[] args = {
 993				account.getUuid(),
 994				name,
 995				String.valueOf(XmppAxolotlSession.Trust.TRUSTED.getCode()),
 996				String.valueOf(XmppAxolotlSession.Trust.TRUSTED_X509.getCode())
 997		};
 998		return DatabaseUtils.queryNumEntries(db, SQLiteAxolotlStore.IDENTITIES_TABLENAME,
 999				SQLiteAxolotlStore.ACCOUNT + " = ?"
1000						+ " AND " + SQLiteAxolotlStore.NAME + " = ?"
1001						+ " AND (" + SQLiteAxolotlStore.TRUSTED + " = ? OR " + SQLiteAxolotlStore.TRUSTED + " = ?)",
1002				args
1003		);
1004	}
1005
1006	private void storeIdentityKey(Account account, String name, boolean own, String fingerprint, String base64Serialized) {
1007		storeIdentityKey(account, name, own, fingerprint, base64Serialized, XmppAxolotlSession.Trust.UNDECIDED);
1008	}
1009
1010	private void storeIdentityKey(Account account, String name, boolean own, String fingerprint, String base64Serialized, XmppAxolotlSession.Trust trusted) {
1011		SQLiteDatabase db = this.getWritableDatabase();
1012		ContentValues values = new ContentValues();
1013		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1014		values.put(SQLiteAxolotlStore.NAME, name);
1015		values.put(SQLiteAxolotlStore.OWN, own ? 1 : 0);
1016		values.put(SQLiteAxolotlStore.FINGERPRINT, fingerprint);
1017		values.put(SQLiteAxolotlStore.KEY, base64Serialized);
1018		values.put(SQLiteAxolotlStore.TRUSTED, trusted.getCode());
1019		db.insert(SQLiteAxolotlStore.IDENTITIES_TABLENAME, null, values);
1020	}
1021
1022	public XmppAxolotlSession.Trust isIdentityKeyTrusted(Account account, String fingerprint) {
1023		Cursor cursor = getIdentityKeyCursor(account, fingerprint);
1024		XmppAxolotlSession.Trust trust = null;
1025		if (cursor.getCount() > 0) {
1026			cursor.moveToFirst();
1027			int trustValue = cursor.getInt(cursor.getColumnIndex(SQLiteAxolotlStore.TRUSTED));
1028			trust = XmppAxolotlSession.Trust.fromCode(trustValue);
1029		}
1030		cursor.close();
1031		return trust;
1032	}
1033
1034	public boolean setIdentityKeyTrust(Account account, String fingerprint, XmppAxolotlSession.Trust trust) {
1035		SQLiteDatabase db = this.getWritableDatabase();
1036		return setIdentityKeyTrust(db, account, fingerprint, trust);
1037	}
1038
1039	private boolean setIdentityKeyTrust(SQLiteDatabase db, Account account, String fingerprint, XmppAxolotlSession.Trust trust) {
1040		String[] selectionArgs = {
1041				account.getUuid(),
1042				fingerprint
1043		};
1044		ContentValues values = new ContentValues();
1045		values.put(SQLiteAxolotlStore.TRUSTED, trust.getCode());
1046		int rows = db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME, values,
1047				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
1048						+ SQLiteAxolotlStore.FINGERPRINT + " = ? ",
1049				selectionArgs);
1050		return rows == 1;
1051	}
1052
1053	public void storeIdentityKey(Account account, String name, IdentityKey identityKey) {
1054		storeIdentityKey(account, name, false, identityKey.getFingerprint().replaceAll("\\s", ""), Base64.encodeToString(identityKey.serialize(), Base64.DEFAULT));
1055	}
1056
1057	public void storeOwnIdentityKeyPair(Account account, IdentityKeyPair identityKeyPair) {
1058		storeIdentityKey(account, account.getJid().toBareJid().toString(), true, identityKeyPair.getPublicKey().getFingerprint().replaceAll("\\s", ""), Base64.encodeToString(identityKeyPair.serialize(), Base64.DEFAULT), XmppAxolotlSession.Trust.TRUSTED);
1059	}
1060
1061	public void recreateAxolotlDb() {
1062		recreateAxolotlDb(getWritableDatabase());
1063	}
1064
1065	public void recreateAxolotlDb(SQLiteDatabase db) {
1066		Log.d(Config.LOGTAG, AxolotlService.LOGPREFIX + " : " + ">>> (RE)CREATING AXOLOTL DATABASE <<<");
1067		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.SESSION_TABLENAME);
1068		db.execSQL(CREATE_SESSIONS_STATEMENT);
1069		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.PREKEY_TABLENAME);
1070		db.execSQL(CREATE_PREKEYS_STATEMENT);
1071		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME);
1072		db.execSQL(CREATE_SIGNED_PREKEYS_STATEMENT);
1073		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.IDENTITIES_TABLENAME);
1074		db.execSQL(CREATE_IDENTITIES_STATEMENT);
1075	}
1076
1077	public void wipeAxolotlDb(Account account) {
1078		String accountName = account.getUuid();
1079		Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + ">>> WIPING AXOLOTL DATABASE FOR ACCOUNT " + accountName + " <<<");
1080		SQLiteDatabase db = this.getWritableDatabase();
1081		String[] deleteArgs = {
1082				accountName
1083		};
1084		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
1085				SQLiteAxolotlStore.ACCOUNT + " = ?",
1086				deleteArgs);
1087		db.delete(SQLiteAxolotlStore.PREKEY_TABLENAME,
1088				SQLiteAxolotlStore.ACCOUNT + " = ?",
1089				deleteArgs);
1090		db.delete(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1091				SQLiteAxolotlStore.ACCOUNT + " = ?",
1092				deleteArgs);
1093		db.delete(SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1094				SQLiteAxolotlStore.ACCOUNT + " = ?",
1095				deleteArgs);
1096	}
1097}