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