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