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