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 void createContact(Contact contact) {
 383		SQLiteDatabase db = this.getWritableDatabase();
 384		db.insert(Contact.TABLENAME, null, contact.getContentValues());
 385	}
 386
 387	public int getConversationCount() {
 388		SQLiteDatabase db = this.getReadableDatabase();
 389		Cursor cursor = db.rawQuery("select count(uuid) as count from "
 390				+ Conversation.TABLENAME + " where " + Conversation.STATUS
 391				+ "=" + Conversation.STATUS_AVAILABLE, null);
 392		cursor.moveToFirst();
 393		int count = cursor.getInt(0);
 394		cursor.close();
 395		return count;
 396	}
 397
 398	public CopyOnWriteArrayList<Conversation> getConversations(int status) {
 399		CopyOnWriteArrayList<Conversation> list = new CopyOnWriteArrayList<>();
 400		SQLiteDatabase db = this.getReadableDatabase();
 401		String[] selectionArgs = {Integer.toString(status)};
 402		Cursor cursor = db.rawQuery("select * from " + Conversation.TABLENAME
 403				+ " where " + Conversation.STATUS + " = ? order by "
 404				+ Conversation.CREATED + " desc", selectionArgs);
 405		while (cursor.moveToNext()) {
 406			list.add(Conversation.fromCursor(cursor));
 407		}
 408		cursor.close();
 409		return list;
 410	}
 411
 412	public ArrayList<Message> getMessages(Conversation conversations, int limit) {
 413		return getMessages(conversations, limit, -1);
 414	}
 415
 416	public ArrayList<Message> getMessages(Conversation conversation, int limit,
 417										  long timestamp) {
 418		ArrayList<Message> list = new ArrayList<>();
 419		SQLiteDatabase db = this.getReadableDatabase();
 420		Cursor cursor;
 421		if (timestamp == -1) {
 422			String[] selectionArgs = {conversation.getUuid()};
 423			cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 424					+ "=?", selectionArgs, null, null, Message.TIME_SENT
 425					+ " DESC", String.valueOf(limit));
 426		} else {
 427			String[] selectionArgs = {conversation.getUuid(),
 428					Long.toString(timestamp)};
 429			cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 430							+ "=? and " + Message.TIME_SENT + "<?", selectionArgs,
 431					null, null, Message.TIME_SENT + " DESC",
 432					String.valueOf(limit));
 433		}
 434		if (cursor.getCount() > 0) {
 435			cursor.moveToLast();
 436			do {
 437				Message message = Message.fromCursor(cursor);
 438				message.setConversation(conversation);
 439				list.add(message);
 440			} while (cursor.moveToPrevious());
 441		}
 442		cursor.close();
 443		return list;
 444	}
 445
 446	public Iterable<Message> getMessagesIterable(final Conversation conversation) {
 447		return new Iterable<Message>() {
 448			@Override
 449			public Iterator<Message> iterator() {
 450				class MessageIterator implements Iterator<Message> {
 451					SQLiteDatabase db = getReadableDatabase();
 452					String[] selectionArgs = {conversation.getUuid()};
 453					Cursor cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 454							+ "=?", selectionArgs, null, null, Message.TIME_SENT
 455							+ " ASC", null);
 456
 457					public MessageIterator() {
 458						cursor.moveToFirst();
 459					}
 460
 461					@Override
 462					public boolean hasNext() {
 463						return !cursor.isAfterLast();
 464					}
 465
 466					@Override
 467					public Message next() {
 468						Message message = Message.fromCursor(cursor);
 469						cursor.moveToNext();
 470						return message;
 471					}
 472
 473					@Override
 474					public void remove() {
 475						throw new UnsupportedOperationException();
 476					}
 477				}
 478				return new MessageIterator();
 479			}
 480		};
 481	}
 482
 483	public Conversation findConversation(final Account account, final Jid contactJid) {
 484		SQLiteDatabase db = this.getReadableDatabase();
 485		String[] selectionArgs = {account.getUuid(),
 486				contactJid.toBareJid().toString() + "/%",
 487				contactJid.toBareJid().toString()
 488		};
 489		Cursor cursor = db.query(Conversation.TABLENAME, null,
 490				Conversation.ACCOUNT + "=? AND (" + Conversation.CONTACTJID
 491						+ " like ? OR " + Conversation.CONTACTJID + "=?)", selectionArgs, null, null, null);
 492		if (cursor.getCount() == 0)
 493			return null;
 494		cursor.moveToFirst();
 495		Conversation conversation = Conversation.fromCursor(cursor);
 496		cursor.close();
 497		return conversation;
 498	}
 499
 500	public void updateConversation(final Conversation conversation) {
 501		final SQLiteDatabase db = this.getWritableDatabase();
 502		final String[] args = {conversation.getUuid()};
 503		db.update(Conversation.TABLENAME, conversation.getContentValues(),
 504				Conversation.UUID + "=?", args);
 505	}
 506
 507	public List<Account> getAccounts() {
 508		SQLiteDatabase db = this.getReadableDatabase();
 509		return getAccounts(db);
 510	}
 511
 512	private List<Account> getAccounts(SQLiteDatabase db) {
 513		List<Account> list = new ArrayList<>();
 514		Cursor cursor = db.query(Account.TABLENAME, null, null, null, null,
 515				null, null);
 516		while (cursor.moveToNext()) {
 517			list.add(Account.fromCursor(cursor));
 518		}
 519		cursor.close();
 520		return list;
 521	}
 522
 523	public void updateAccount(Account account) {
 524		SQLiteDatabase db = this.getWritableDatabase();
 525		String[] args = {account.getUuid()};
 526		db.update(Account.TABLENAME, account.getContentValues(), Account.UUID
 527				+ "=?", args);
 528	}
 529
 530	public void deleteAccount(Account account) {
 531		SQLiteDatabase db = this.getWritableDatabase();
 532		String[] args = {account.getUuid()};
 533		db.delete(Account.TABLENAME, Account.UUID + "=?", args);
 534	}
 535
 536	public boolean hasEnabledAccounts() {
 537		SQLiteDatabase db = this.getReadableDatabase();
 538		Cursor cursor = db.rawQuery("select count(" + Account.UUID + ")  from "
 539				+ Account.TABLENAME + " where not options & (1 <<1)", null);
 540		try {
 541			cursor.moveToFirst();
 542			int count = cursor.getInt(0);
 543			cursor.close();
 544			return (count > 0);
 545		} catch (SQLiteCantOpenDatabaseException e) {
 546			return true; // better safe than sorry
 547		} catch (RuntimeException e) {
 548			return true; // better safe than sorry
 549		}
 550	}
 551
 552	@Override
 553	public SQLiteDatabase getWritableDatabase() {
 554		SQLiteDatabase db = super.getWritableDatabase();
 555		db.execSQL("PRAGMA foreign_keys=ON;");
 556		return db;
 557	}
 558
 559	public void updateMessage(Message message) {
 560		SQLiteDatabase db = this.getWritableDatabase();
 561		String[] args = {message.getUuid()};
 562		db.update(Message.TABLENAME, message.getContentValues(), Message.UUID
 563				+ "=?", args);
 564	}
 565
 566	public void readRoster(Roster roster) {
 567		SQLiteDatabase db = this.getReadableDatabase();
 568		Cursor cursor;
 569		String args[] = {roster.getAccount().getUuid()};
 570		cursor = db.query(Contact.TABLENAME, null, Contact.ACCOUNT + "=?", args, null, null, null);
 571		while (cursor.moveToNext()) {
 572			roster.initContact(Contact.fromCursor(cursor));
 573		}
 574		cursor.close();
 575	}
 576
 577	public void writeRoster(final Roster roster) {
 578		final Account account = roster.getAccount();
 579		final SQLiteDatabase db = this.getWritableDatabase();
 580		for (Contact contact : roster.getContacts()) {
 581			if (contact.getOption(Contact.Options.IN_ROSTER)) {
 582				db.insert(Contact.TABLENAME, null, contact.getContentValues());
 583			} else {
 584				String where = Contact.ACCOUNT + "=? AND " + Contact.JID + "=?";
 585				String[] whereArgs = {account.getUuid(), contact.getJid().toString()};
 586				db.delete(Contact.TABLENAME, where, whereArgs);
 587			}
 588		}
 589		account.setRosterVersion(roster.getVersion());
 590		updateAccount(account);
 591	}
 592
 593	public void deleteMessage(Message message) {
 594		SQLiteDatabase db = this.getWritableDatabase();
 595		String[] args = {message.getUuid()};
 596		db.delete(Message.TABLENAME, Message.UUID + "=?", args);
 597	}
 598
 599	public void deleteMessagesInConversation(Conversation conversation) {
 600		SQLiteDatabase db = this.getWritableDatabase();
 601		String[] args = {conversation.getUuid()};
 602		db.delete(Message.TABLENAME, Message.CONVERSATION + "=?", args);
 603	}
 604
 605	public Pair<Long, String> getLastMessageReceived(Account account) {
 606		SQLiteDatabase db = this.getReadableDatabase();
 607		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";
 608		String[] args = {account.getUuid()};
 609		Cursor cursor = db.rawQuery(sql, args);
 610		if (cursor.getCount() == 0) {
 611			return null;
 612		} else {
 613			cursor.moveToFirst();
 614			return new Pair<>(cursor.getLong(0), cursor.getString(1));
 615		}
 616	}
 617
 618	public Conversation findConversationByUuid(String conversationUuid) {
 619		SQLiteDatabase db = this.getReadableDatabase();
 620		String[] selectionArgs = {conversationUuid};
 621		Cursor cursor = db.query(Conversation.TABLENAME, null,
 622				Conversation.UUID + "=?", selectionArgs, null, null, null);
 623		if (cursor.getCount() == 0) {
 624			return null;
 625		}
 626		cursor.moveToFirst();
 627		Conversation conversation = Conversation.fromCursor(cursor);
 628		cursor.close();
 629		return conversation;
 630	}
 631
 632	public Message findMessageByUuid(String messageUuid) {
 633		SQLiteDatabase db = this.getReadableDatabase();
 634		String[] selectionArgs = {messageUuid};
 635		Cursor cursor = db.query(Message.TABLENAME, null, Message.UUID + "=?",
 636				selectionArgs, null, null, null);
 637		if (cursor.getCount() == 0) {
 638			return null;
 639		}
 640		cursor.moveToFirst();
 641		Message message = Message.fromCursor(cursor);
 642		cursor.close();
 643		return message;
 644	}
 645
 646	public Account findAccountByUuid(String accountUuid) {
 647		SQLiteDatabase db = this.getReadableDatabase();
 648		String[] selectionArgs = {accountUuid};
 649		Cursor cursor = db.query(Account.TABLENAME, null, Account.UUID + "=?",
 650				selectionArgs, null, null, null);
 651		if (cursor.getCount() == 0) {
 652			return null;
 653		}
 654		cursor.moveToFirst();
 655		Account account = Account.fromCursor(cursor);
 656		cursor.close();
 657		return account;
 658	}
 659
 660	public List<Message> getImageMessages(Conversation conversation) {
 661		ArrayList<Message> list = new ArrayList<>();
 662		SQLiteDatabase db = this.getReadableDatabase();
 663		Cursor cursor;
 664		String[] selectionArgs = {conversation.getUuid(), String.valueOf(Message.TYPE_IMAGE)};
 665		cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
 666				+ "=? AND " + Message.TYPE + "=?", selectionArgs, null, null, null);
 667		if (cursor.getCount() > 0) {
 668			cursor.moveToLast();
 669			do {
 670				Message message = Message.fromCursor(cursor);
 671				message.setConversation(conversation);
 672				list.add(message);
 673			} while (cursor.moveToPrevious());
 674		}
 675		cursor.close();
 676		return list;
 677	}
 678
 679	private Cursor getCursorForSession(Account account, AxolotlAddress contact) {
 680		final SQLiteDatabase db = this.getReadableDatabase();
 681		String[] columns = null;
 682		String[] selectionArgs = {account.getUuid(),
 683				contact.getName(),
 684				Integer.toString(contact.getDeviceId())};
 685		Cursor cursor = db.query(SQLiteAxolotlStore.SESSION_TABLENAME,
 686				columns,
 687				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 688						+ SQLiteAxolotlStore.NAME + " = ? AND "
 689						+ SQLiteAxolotlStore.DEVICE_ID + " = ? ",
 690				selectionArgs,
 691				null, null, null);
 692
 693		return cursor;
 694	}
 695
 696	public SessionRecord loadSession(Account account, AxolotlAddress contact) {
 697		SessionRecord session = null;
 698		Cursor cursor = getCursorForSession(account, contact);
 699		if (cursor.getCount() != 0) {
 700			cursor.moveToFirst();
 701			try {
 702				session = new SessionRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
 703			} catch (IOException e) {
 704				cursor.close();
 705				throw new AssertionError(e);
 706			}
 707		}
 708		cursor.close();
 709		return session;
 710	}
 711
 712	public List<Integer> getSubDeviceSessions(Account account, AxolotlAddress contact) {
 713		final SQLiteDatabase db = this.getReadableDatabase();
 714		return getSubDeviceSessions(db, account, contact);
 715	}
 716
 717	private List<Integer> getSubDeviceSessions(SQLiteDatabase db, Account account, AxolotlAddress contact) {
 718		List<Integer> devices = new ArrayList<>();
 719		String[] columns = {SQLiteAxolotlStore.DEVICE_ID};
 720		String[] selectionArgs = {account.getUuid(),
 721				contact.getName()};
 722		Cursor cursor = db.query(SQLiteAxolotlStore.SESSION_TABLENAME,
 723				columns,
 724				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 725						+ SQLiteAxolotlStore.NAME + " = ?",
 726				selectionArgs,
 727				null, null, null);
 728
 729		while (cursor.moveToNext()) {
 730			devices.add(cursor.getInt(
 731					cursor.getColumnIndex(SQLiteAxolotlStore.DEVICE_ID)));
 732		}
 733
 734		cursor.close();
 735		return devices;
 736	}
 737
 738	public boolean containsSession(Account account, AxolotlAddress contact) {
 739		Cursor cursor = getCursorForSession(account, contact);
 740		int count = cursor.getCount();
 741		cursor.close();
 742		return count != 0;
 743	}
 744
 745	public void storeSession(Account account, AxolotlAddress contact, SessionRecord session) {
 746		SQLiteDatabase db = this.getWritableDatabase();
 747		ContentValues values = new ContentValues();
 748		values.put(SQLiteAxolotlStore.NAME, contact.getName());
 749		values.put(SQLiteAxolotlStore.DEVICE_ID, contact.getDeviceId());
 750		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(session.serialize(), Base64.DEFAULT));
 751		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
 752		db.insert(SQLiteAxolotlStore.SESSION_TABLENAME, null, values);
 753	}
 754
 755	public void deleteSession(Account account, AxolotlAddress contact) {
 756		SQLiteDatabase db = this.getWritableDatabase();
 757		deleteSession(db, account, contact);
 758	}
 759
 760	private void deleteSession(SQLiteDatabase db, Account account, AxolotlAddress contact) {
 761		String[] args = {account.getUuid(),
 762				contact.getName(),
 763				Integer.toString(contact.getDeviceId())};
 764		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
 765				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
 766						+ SQLiteAxolotlStore.NAME + " = ? AND "
 767						+ SQLiteAxolotlStore.DEVICE_ID + " = ? ",
 768				args);
 769	}
 770
 771	public void deleteAllSessions(Account account, AxolotlAddress contact) {
 772		SQLiteDatabase db = this.getWritableDatabase();
 773		String[] args = {account.getUuid(), contact.getName()};
 774		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
 775				SQLiteAxolotlStore.ACCOUNT + "=? AND "
 776						+ SQLiteAxolotlStore.NAME + " = ?",
 777				args);
 778	}
 779
 780	private Cursor getCursorForPreKey(Account account, int preKeyId) {
 781		SQLiteDatabase db = this.getReadableDatabase();
 782		String[] columns = {SQLiteAxolotlStore.KEY};
 783		String[] selectionArgs = {account.getUuid(), Integer.toString(preKeyId)};
 784		Cursor cursor = db.query(SQLiteAxolotlStore.PREKEY_TABLENAME,
 785				columns,
 786				SQLiteAxolotlStore.ACCOUNT + "=? AND "
 787						+ SQLiteAxolotlStore.ID + "=?",
 788				selectionArgs,
 789				null, null, null);
 790
 791		return cursor;
 792	}
 793
 794	public PreKeyRecord loadPreKey(Account account, int preKeyId) {
 795		PreKeyRecord record = null;
 796		Cursor cursor = getCursorForPreKey(account, preKeyId);
 797		if (cursor.getCount() != 0) {
 798			cursor.moveToFirst();
 799			try {
 800				record = new PreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
 801			} catch (IOException e) {
 802				throw new AssertionError(e);
 803			}
 804		}
 805		cursor.close();
 806		return record;
 807	}
 808
 809	public boolean containsPreKey(Account account, int preKeyId) {
 810		Cursor cursor = getCursorForPreKey(account, preKeyId);
 811		int count = cursor.getCount();
 812		cursor.close();
 813		return count != 0;
 814	}
 815
 816	public void storePreKey(Account account, PreKeyRecord record) {
 817		SQLiteDatabase db = this.getWritableDatabase();
 818		ContentValues values = new ContentValues();
 819		values.put(SQLiteAxolotlStore.ID, record.getId());
 820		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(record.serialize(), Base64.DEFAULT));
 821		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
 822		db.insert(SQLiteAxolotlStore.PREKEY_TABLENAME, null, values);
 823	}
 824
 825	public void deletePreKey(Account account, int preKeyId) {
 826		SQLiteDatabase db = this.getWritableDatabase();
 827		String[] args = {account.getUuid(), Integer.toString(preKeyId)};
 828		db.delete(SQLiteAxolotlStore.PREKEY_TABLENAME,
 829				SQLiteAxolotlStore.ACCOUNT + "=? AND "
 830						+ SQLiteAxolotlStore.ID + "=?",
 831				args);
 832	}
 833
 834	private Cursor getCursorForSignedPreKey(Account account, int signedPreKeyId) {
 835		SQLiteDatabase db = this.getReadableDatabase();
 836		String[] columns = {SQLiteAxolotlStore.KEY};
 837		String[] selectionArgs = {account.getUuid(), Integer.toString(signedPreKeyId)};
 838		Cursor cursor = db.query(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
 839				columns,
 840				SQLiteAxolotlStore.ACCOUNT + "=? AND " + SQLiteAxolotlStore.ID + "=?",
 841				selectionArgs,
 842				null, null, null);
 843
 844		return cursor;
 845	}
 846
 847	public SignedPreKeyRecord loadSignedPreKey(Account account, int signedPreKeyId) {
 848		SignedPreKeyRecord record = null;
 849		Cursor cursor = getCursorForSignedPreKey(account, signedPreKeyId);
 850		if (cursor.getCount() != 0) {
 851			cursor.moveToFirst();
 852			try {
 853				record = new SignedPreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
 854			} catch (IOException e) {
 855				throw new AssertionError(e);
 856			}
 857		}
 858		cursor.close();
 859		return record;
 860	}
 861
 862	public List<SignedPreKeyRecord> loadSignedPreKeys(Account account) {
 863		List<SignedPreKeyRecord> prekeys = new ArrayList<>();
 864		SQLiteDatabase db = this.getReadableDatabase();
 865		String[] columns = {SQLiteAxolotlStore.KEY};
 866		String[] selectionArgs = {account.getUuid()};
 867		Cursor cursor = db.query(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
 868				columns,
 869				SQLiteAxolotlStore.ACCOUNT + "=?",
 870				selectionArgs,
 871				null, null, null);
 872
 873		while (cursor.moveToNext()) {
 874			try {
 875				prekeys.add(new SignedPreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT)));
 876			} catch (IOException ignored) {
 877			}
 878		}
 879		cursor.close();
 880		return prekeys;
 881	}
 882
 883	public boolean containsSignedPreKey(Account account, int signedPreKeyId) {
 884		Cursor cursor = getCursorForPreKey(account, signedPreKeyId);
 885		int count = cursor.getCount();
 886		cursor.close();
 887		return count != 0;
 888	}
 889
 890	public void storeSignedPreKey(Account account, SignedPreKeyRecord record) {
 891		SQLiteDatabase db = this.getWritableDatabase();
 892		ContentValues values = new ContentValues();
 893		values.put(SQLiteAxolotlStore.ID, record.getId());
 894		values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(record.serialize(), Base64.DEFAULT));
 895		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
 896		db.insert(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME, null, values);
 897	}
 898
 899	public void deleteSignedPreKey(Account account, int signedPreKeyId) {
 900		SQLiteDatabase db = this.getWritableDatabase();
 901		String[] args = {account.getUuid(), Integer.toString(signedPreKeyId)};
 902		db.delete(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
 903				SQLiteAxolotlStore.ACCOUNT + "=? AND "
 904						+ SQLiteAxolotlStore.ID + "=?",
 905				args);
 906	}
 907
 908	private Cursor getIdentityKeyCursor(Account account, String name, boolean own) {
 909		final SQLiteDatabase db = this.getReadableDatabase();
 910		return getIdentityKeyCursor(db, account, name, own);
 911	}
 912
 913	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String name, boolean own) {
 914		return getIdentityKeyCursor(db, account, name, own, null);
 915	}
 916
 917	private Cursor getIdentityKeyCursor(Account account, String fingerprint) {
 918		final SQLiteDatabase db = this.getReadableDatabase();
 919		return getIdentityKeyCursor(db, account, fingerprint);
 920	}
 921
 922	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String fingerprint) {
 923		return getIdentityKeyCursor(db, account, null, null, fingerprint);
 924	}
 925
 926	private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String name, Boolean own, String fingerprint) {
 927		String[] columns = {SQLiteAxolotlStore.TRUSTED,
 928				SQLiteAxolotlStore.KEY};
 929		ArrayList<String> selectionArgs = new ArrayList<>(4);
 930		selectionArgs.add(account.getUuid());
 931		String selectionString = SQLiteAxolotlStore.ACCOUNT + " = ?";
 932		if (name != null) {
 933			selectionArgs.add(name);
 934			selectionString += " AND " + SQLiteAxolotlStore.NAME + " = ?";
 935		}
 936		if (fingerprint != null) {
 937			selectionArgs.add(fingerprint);
 938			selectionString += " AND " + SQLiteAxolotlStore.FINGERPRINT + " = ?";
 939		}
 940		if (own != null) {
 941			selectionArgs.add(own ? "1" : "0");
 942			selectionString += " AND " + SQLiteAxolotlStore.OWN + " = ?";
 943		}
 944		Cursor cursor = db.query(SQLiteAxolotlStore.IDENTITIES_TABLENAME,
 945				columns,
 946				selectionString,
 947				selectionArgs.toArray(new String[selectionArgs.size()]),
 948				null, null, null);
 949
 950		return cursor;
 951	}
 952
 953	public IdentityKeyPair loadOwnIdentityKeyPair(Account account) {
 954		SQLiteDatabase db = getReadableDatabase();
 955		return loadOwnIdentityKeyPair(db, account);
 956	}
 957
 958	private IdentityKeyPair loadOwnIdentityKeyPair(SQLiteDatabase db, Account account) {
 959		String name = account.getJid().toBareJid().toString();
 960		IdentityKeyPair identityKeyPair = null;
 961		Cursor cursor = getIdentityKeyCursor(db, account, name, true);
 962		if (cursor.getCount() != 0) {
 963			cursor.moveToFirst();
 964			try {
 965				identityKeyPair = new IdentityKeyPair(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
 966			} catch (InvalidKeyException e) {
 967				Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Encountered invalid IdentityKey in database for account" + account.getJid().toBareJid() + ", address: " + name);
 968			}
 969		}
 970		cursor.close();
 971
 972		return identityKeyPair;
 973	}
 974
 975	public Set<IdentityKey> loadIdentityKeys(Account account, String name) {
 976		return loadIdentityKeys(account, name, null);
 977	}
 978
 979	public Set<IdentityKey> loadIdentityKeys(Account account, String name, XmppAxolotlSession.Trust trust) {
 980		Set<IdentityKey> identityKeys = new HashSet<>();
 981		Cursor cursor = getIdentityKeyCursor(account, name, false);
 982
 983		while (cursor.moveToNext()) {
 984			if (trust != null &&
 985					cursor.getInt(cursor.getColumnIndex(SQLiteAxolotlStore.TRUSTED))
 986							!= trust.getCode()) {
 987				continue;
 988			}
 989			try {
 990				identityKeys.add(new IdentityKey(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT), 0));
 991			} catch (InvalidKeyException e) {
 992				Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Encountered invalid IdentityKey in database for account" + account.getJid().toBareJid() + ", address: " + name);
 993			}
 994		}
 995		cursor.close();
 996
 997		return identityKeys;
 998	}
 999
1000	public long numTrustedKeys(Account account, String name) {
1001		SQLiteDatabase db = getReadableDatabase();
1002		String[] args = {
1003				account.getUuid(),
1004				name,
1005				String.valueOf(XmppAxolotlSession.Trust.TRUSTED.getCode()),
1006				String.valueOf(XmppAxolotlSession.Trust.TRUSTED_X509.getCode())
1007		};
1008		return DatabaseUtils.queryNumEntries(db, SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1009				SQLiteAxolotlStore.ACCOUNT + " = ?"
1010						+ " AND " + SQLiteAxolotlStore.NAME + " = ?"
1011						+ " AND (" + SQLiteAxolotlStore.TRUSTED + " = ? OR " + SQLiteAxolotlStore.TRUSTED + " = ?)",
1012				args
1013		);
1014	}
1015
1016	private void storeIdentityKey(Account account, String name, boolean own, String fingerprint, String base64Serialized) {
1017		storeIdentityKey(account, name, own, fingerprint, base64Serialized, XmppAxolotlSession.Trust.UNDECIDED);
1018	}
1019
1020	private void storeIdentityKey(Account account, String name, boolean own, String fingerprint, String base64Serialized, XmppAxolotlSession.Trust trusted) {
1021		SQLiteDatabase db = this.getWritableDatabase();
1022		ContentValues values = new ContentValues();
1023		values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1024		values.put(SQLiteAxolotlStore.NAME, name);
1025		values.put(SQLiteAxolotlStore.OWN, own ? 1 : 0);
1026		values.put(SQLiteAxolotlStore.FINGERPRINT, fingerprint);
1027		values.put(SQLiteAxolotlStore.KEY, base64Serialized);
1028		values.put(SQLiteAxolotlStore.TRUSTED, trusted.getCode());
1029		db.insert(SQLiteAxolotlStore.IDENTITIES_TABLENAME, null, values);
1030	}
1031
1032	public XmppAxolotlSession.Trust isIdentityKeyTrusted(Account account, String fingerprint) {
1033		Cursor cursor = getIdentityKeyCursor(account, fingerprint);
1034		XmppAxolotlSession.Trust trust = null;
1035		if (cursor.getCount() > 0) {
1036			cursor.moveToFirst();
1037			int trustValue = cursor.getInt(cursor.getColumnIndex(SQLiteAxolotlStore.TRUSTED));
1038			trust = XmppAxolotlSession.Trust.fromCode(trustValue);
1039		}
1040		cursor.close();
1041		return trust;
1042	}
1043
1044	public boolean setIdentityKeyTrust(Account account, String fingerprint, XmppAxolotlSession.Trust trust) {
1045		SQLiteDatabase db = this.getWritableDatabase();
1046		return setIdentityKeyTrust(db, account, fingerprint, trust);
1047	}
1048
1049	private boolean setIdentityKeyTrust(SQLiteDatabase db, Account account, String fingerprint, XmppAxolotlSession.Trust trust) {
1050		String[] selectionArgs = {
1051				account.getUuid(),
1052				fingerprint
1053		};
1054		ContentValues values = new ContentValues();
1055		values.put(SQLiteAxolotlStore.TRUSTED, trust.getCode());
1056		int rows = db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME, values,
1057				SQLiteAxolotlStore.ACCOUNT + " = ? AND "
1058						+ SQLiteAxolotlStore.FINGERPRINT + " = ? ",
1059				selectionArgs);
1060		return rows == 1;
1061	}
1062
1063	public boolean setIdentityKeyCertificate(Account account, String fingerprint, X509Certificate x509Certificate) {
1064		SQLiteDatabase db = this.getWritableDatabase();
1065		String[] selectionArgs = {
1066				account.getUuid(),
1067				fingerprint
1068		};
1069		try {
1070			ContentValues values = new ContentValues();
1071			values.put(SQLiteAxolotlStore.CERTIFICATE, x509Certificate.getEncoded());
1072			return db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME, values,
1073					SQLiteAxolotlStore.ACCOUNT + " = ? AND "
1074							+ SQLiteAxolotlStore.FINGERPRINT + " = ? ",
1075					selectionArgs) == 1;
1076		} catch (CertificateEncodingException e) {
1077			Log.d(Config.LOGTAG, "could not encode certificate");
1078			return false;
1079		}
1080	}
1081
1082	public X509Certificate getIdentityKeyCertifcate(Account account, String fingerprint) {
1083		SQLiteDatabase db = this.getReadableDatabase();
1084		String[] selectionArgs = {
1085				account.getUuid(),
1086				fingerprint
1087		};
1088		String[] colums = {SQLiteAxolotlStore.CERTIFICATE};
1089		String selection = SQLiteAxolotlStore.ACCOUNT + " = ? AND " + SQLiteAxolotlStore.FINGERPRINT + " = ? ";
1090		Cursor cursor = db.query(SQLiteAxolotlStore.IDENTITIES_TABLENAME, colums, selection, selectionArgs, null, null, null);
1091		if (cursor.getCount() < 1) {
1092			return null;
1093		} else {
1094			cursor.moveToFirst();
1095			byte[] certificate = cursor.getBlob(cursor.getColumnIndex(SQLiteAxolotlStore.CERTIFICATE));
1096			if (certificate == null || certificate.length == 0) {
1097				return null;
1098			}
1099			try {
1100				CertificateFactory certificateFactory = CertificateFactory.getInstance("X.509");
1101				return (X509Certificate) certificateFactory.generateCertificate(new ByteArrayInputStream(certificate));
1102			} catch (CertificateException e) {
1103				Log.d(Config.LOGTAG,"certificate exception "+e.getMessage());
1104				return null;
1105			}
1106		}
1107	}
1108
1109	public void storeIdentityKey(Account account, String name, IdentityKey identityKey) {
1110		storeIdentityKey(account, name, false, identityKey.getFingerprint().replaceAll("\\s", ""), Base64.encodeToString(identityKey.serialize(), Base64.DEFAULT));
1111	}
1112
1113	public void storeOwnIdentityKeyPair(Account account, IdentityKeyPair identityKeyPair) {
1114		storeIdentityKey(account, account.getJid().toBareJid().toString(), true, identityKeyPair.getPublicKey().getFingerprint().replaceAll("\\s", ""), Base64.encodeToString(identityKeyPair.serialize(), Base64.DEFAULT), XmppAxolotlSession.Trust.TRUSTED);
1115	}
1116
1117	public void recreateAxolotlDb() {
1118		recreateAxolotlDb(getWritableDatabase());
1119	}
1120
1121	public void recreateAxolotlDb(SQLiteDatabase db) {
1122		Log.d(Config.LOGTAG, AxolotlService.LOGPREFIX + " : " + ">>> (RE)CREATING AXOLOTL DATABASE <<<");
1123		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.SESSION_TABLENAME);
1124		db.execSQL(CREATE_SESSIONS_STATEMENT);
1125		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.PREKEY_TABLENAME);
1126		db.execSQL(CREATE_PREKEYS_STATEMENT);
1127		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME);
1128		db.execSQL(CREATE_SIGNED_PREKEYS_STATEMENT);
1129		db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.IDENTITIES_TABLENAME);
1130		db.execSQL(CREATE_IDENTITIES_STATEMENT);
1131	}
1132
1133	public void wipeAxolotlDb(Account account) {
1134		String accountName = account.getUuid();
1135		Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + ">>> WIPING AXOLOTL DATABASE FOR ACCOUNT " + accountName + " <<<");
1136		SQLiteDatabase db = this.getWritableDatabase();
1137		String[] deleteArgs = {
1138				accountName
1139		};
1140		db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
1141				SQLiteAxolotlStore.ACCOUNT + " = ?",
1142				deleteArgs);
1143		db.delete(SQLiteAxolotlStore.PREKEY_TABLENAME,
1144				SQLiteAxolotlStore.ACCOUNT + " = ?",
1145				deleteArgs);
1146		db.delete(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1147				SQLiteAxolotlStore.ACCOUNT + " = ?",
1148				deleteArgs);
1149		db.delete(SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1150				SQLiteAxolotlStore.ACCOUNT + " = ?",
1151				deleteArgs);
1152	}
1153}