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