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 recreateAxolotlDb();
287 }
288 }
289
290 public static synchronized DatabaseBackend getInstance(Context context) {
291 if (instance == null) {
292 instance = new DatabaseBackend(context);
293 }
294 return instance;
295 }
296
297 public void createConversation(Conversation conversation) {
298 SQLiteDatabase db = this.getWritableDatabase();
299 db.insert(Conversation.TABLENAME, null, conversation.getContentValues());
300 }
301
302 public void createMessage(Message message) {
303 SQLiteDatabase db = this.getWritableDatabase();
304 db.insert(Message.TABLENAME, null, message.getContentValues());
305 }
306
307 public void createAccount(Account account) {
308 SQLiteDatabase db = this.getWritableDatabase();
309 db.insert(Account.TABLENAME, null, account.getContentValues());
310 }
311
312 public void createContact(Contact contact) {
313 SQLiteDatabase db = this.getWritableDatabase();
314 db.insert(Contact.TABLENAME, null, contact.getContentValues());
315 }
316
317 public int getConversationCount() {
318 SQLiteDatabase db = this.getReadableDatabase();
319 Cursor cursor = db.rawQuery("select count(uuid) as count from "
320 + Conversation.TABLENAME + " where " + Conversation.STATUS
321 + "=" + Conversation.STATUS_AVAILABLE, null);
322 cursor.moveToFirst();
323 int count = cursor.getInt(0);
324 cursor.close();
325 return count;
326 }
327
328 public CopyOnWriteArrayList<Conversation> getConversations(int status) {
329 CopyOnWriteArrayList<Conversation> list = new CopyOnWriteArrayList<>();
330 SQLiteDatabase db = this.getReadableDatabase();
331 String[] selectionArgs = { Integer.toString(status) };
332 Cursor cursor = db.rawQuery("select * from " + Conversation.TABLENAME
333 + " where " + Conversation.STATUS + " = ? order by "
334 + Conversation.CREATED + " desc", selectionArgs);
335 while (cursor.moveToNext()) {
336 list.add(Conversation.fromCursor(cursor));
337 }
338 cursor.close();
339 return list;
340 }
341
342 public ArrayList<Message> getMessages(Conversation conversations, int limit) {
343 return getMessages(conversations, limit, -1);
344 }
345
346 public ArrayList<Message> getMessages(Conversation conversation, int limit,
347 long timestamp) {
348 ArrayList<Message> list = new ArrayList<>();
349 SQLiteDatabase db = this.getReadableDatabase();
350 Cursor cursor;
351 if (timestamp == -1) {
352 String[] selectionArgs = { conversation.getUuid() };
353 cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
354 + "=?", selectionArgs, null, null, Message.TIME_SENT
355 + " DESC", String.valueOf(limit));
356 } else {
357 String[] selectionArgs = { conversation.getUuid(),
358 Long.toString(timestamp) };
359 cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
360 + "=? and " + Message.TIME_SENT + "<?", selectionArgs,
361 null, null, Message.TIME_SENT + " DESC",
362 String.valueOf(limit));
363 }
364 if (cursor.getCount() > 0) {
365 cursor.moveToLast();
366 do {
367 Message message = Message.fromCursor(cursor);
368 message.setConversation(conversation);
369 list.add(message);
370 } while (cursor.moveToPrevious());
371 }
372 cursor.close();
373 return list;
374 }
375
376 public Conversation findConversation(final Account account, final Jid contactJid) {
377 SQLiteDatabase db = this.getReadableDatabase();
378 String[] selectionArgs = { account.getUuid(),
379 contactJid.toBareJid().toString() + "/%",
380 contactJid.toBareJid().toString()
381 };
382 Cursor cursor = db.query(Conversation.TABLENAME, null,
383 Conversation.ACCOUNT + "=? AND (" + Conversation.CONTACTJID
384 + " like ? OR " + Conversation.CONTACTJID + "=?)", selectionArgs, null, null, null);
385 if (cursor.getCount() == 0)
386 return null;
387 cursor.moveToFirst();
388 Conversation conversation = Conversation.fromCursor(cursor);
389 cursor.close();
390 return conversation;
391 }
392
393 public void updateConversation(final Conversation conversation) {
394 final SQLiteDatabase db = this.getWritableDatabase();
395 final String[] args = { conversation.getUuid() };
396 db.update(Conversation.TABLENAME, conversation.getContentValues(),
397 Conversation.UUID + "=?", args);
398 }
399
400 public List<Account> getAccounts() {
401 List<Account> list = new ArrayList<>();
402 SQLiteDatabase db = this.getReadableDatabase();
403 Cursor cursor = db.query(Account.TABLENAME, null, null, null, null,
404 null, null);
405 while (cursor.moveToNext()) {
406 list.add(Account.fromCursor(cursor));
407 }
408 cursor.close();
409 return list;
410 }
411
412 public void updateAccount(Account account) {
413 SQLiteDatabase db = this.getWritableDatabase();
414 String[] args = { account.getUuid() };
415 db.update(Account.TABLENAME, account.getContentValues(), Account.UUID
416 + "=?", args);
417 }
418
419 public void deleteAccount(Account account) {
420 SQLiteDatabase db = this.getWritableDatabase();
421 String[] args = { account.getUuid() };
422 db.delete(Account.TABLENAME, Account.UUID + "=?", args);
423 }
424
425 public boolean hasEnabledAccounts() {
426 SQLiteDatabase db = this.getReadableDatabase();
427 Cursor cursor = db.rawQuery("select count(" + Account.UUID + ") from "
428 + Account.TABLENAME + " where not options & (1 <<1)", null);
429 try {
430 cursor.moveToFirst();
431 int count = cursor.getInt(0);
432 cursor.close();
433 return (count > 0);
434 } catch (SQLiteCantOpenDatabaseException e) {
435 return true; // better safe than sorry
436 } catch (RuntimeException e) {
437 return true; // better safe than sorry
438 }
439 }
440
441 @Override
442 public SQLiteDatabase getWritableDatabase() {
443 SQLiteDatabase db = super.getWritableDatabase();
444 db.execSQL("PRAGMA foreign_keys=ON;");
445 return db;
446 }
447
448 public void updateMessage(Message message) {
449 SQLiteDatabase db = this.getWritableDatabase();
450 String[] args = { message.getUuid() };
451 db.update(Message.TABLENAME, message.getContentValues(), Message.UUID
452 + "=?", args);
453 }
454
455 public void readRoster(Roster roster) {
456 SQLiteDatabase db = this.getReadableDatabase();
457 Cursor cursor;
458 String args[] = { roster.getAccount().getUuid() };
459 cursor = db.query(Contact.TABLENAME, null, Contact.ACCOUNT + "=?", args, null, null, null);
460 while (cursor.moveToNext()) {
461 roster.initContact(Contact.fromCursor(cursor));
462 }
463 cursor.close();
464 }
465
466 public void writeRoster(final Roster roster) {
467 final Account account = roster.getAccount();
468 final SQLiteDatabase db = this.getWritableDatabase();
469 for (Contact contact : roster.getContacts()) {
470 if (contact.getOption(Contact.Options.IN_ROSTER)) {
471 db.insert(Contact.TABLENAME, null, contact.getContentValues());
472 } else {
473 String where = Contact.ACCOUNT + "=? AND " + Contact.JID + "=?";
474 String[] whereArgs = { account.getUuid(), contact.getJid().toString() };
475 db.delete(Contact.TABLENAME, where, whereArgs);
476 }
477 }
478 account.setRosterVersion(roster.getVersion());
479 updateAccount(account);
480 }
481
482 public void deleteMessage(Message message) {
483 SQLiteDatabase db = this.getWritableDatabase();
484 String[] args = { message.getUuid() };
485 db.delete(Message.TABLENAME, Message.UUID + "=?", args);
486 }
487
488 public void deleteMessagesInConversation(Conversation conversation) {
489 SQLiteDatabase db = this.getWritableDatabase();
490 String[] args = { conversation.getUuid() };
491 db.delete(Message.TABLENAME, Message.CONVERSATION + "=?", args);
492 }
493
494 public Conversation findConversationByUuid(String conversationUuid) {
495 SQLiteDatabase db = this.getReadableDatabase();
496 String[] selectionArgs = { conversationUuid };
497 Cursor cursor = db.query(Conversation.TABLENAME, null,
498 Conversation.UUID + "=?", selectionArgs, null, null, null);
499 if (cursor.getCount() == 0) {
500 return null;
501 }
502 cursor.moveToFirst();
503 Conversation conversation = Conversation.fromCursor(cursor);
504 cursor.close();
505 return conversation;
506 }
507
508 public Message findMessageByUuid(String messageUuid) {
509 SQLiteDatabase db = this.getReadableDatabase();
510 String[] selectionArgs = { messageUuid };
511 Cursor cursor = db.query(Message.TABLENAME, null, Message.UUID + "=?",
512 selectionArgs, null, null, null);
513 if (cursor.getCount() == 0) {
514 return null;
515 }
516 cursor.moveToFirst();
517 Message message = Message.fromCursor(cursor);
518 cursor.close();
519 return message;
520 }
521
522 public Account findAccountByUuid(String accountUuid) {
523 SQLiteDatabase db = this.getReadableDatabase();
524 String[] selectionArgs = { accountUuid };
525 Cursor cursor = db.query(Account.TABLENAME, null, Account.UUID + "=?",
526 selectionArgs, null, null, null);
527 if (cursor.getCount() == 0) {
528 return null;
529 }
530 cursor.moveToFirst();
531 Account account = Account.fromCursor(cursor);
532 cursor.close();
533 return account;
534 }
535
536 public List<Message> getImageMessages(Conversation conversation) {
537 ArrayList<Message> list = new ArrayList<>();
538 SQLiteDatabase db = this.getReadableDatabase();
539 Cursor cursor;
540 String[] selectionArgs = { conversation.getUuid(), String.valueOf(Message.TYPE_IMAGE) };
541 cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
542 + "=? AND "+Message.TYPE+"=?", selectionArgs, null, null,null);
543 if (cursor.getCount() > 0) {
544 cursor.moveToLast();
545 do {
546 Message message = Message.fromCursor(cursor);
547 message.setConversation(conversation);
548 list.add(message);
549 } while (cursor.moveToPrevious());
550 }
551 cursor.close();
552 return list;
553 }
554
555 private Cursor getCursorForSession(Account account, AxolotlAddress contact) {
556 final SQLiteDatabase db = this.getReadableDatabase();
557 String[] columns = null;
558 String[] selectionArgs = {account.getUuid(),
559 contact.getName(),
560 Integer.toString(contact.getDeviceId())};
561 Cursor cursor = db.query(AxolotlService.SQLiteAxolotlStore.SESSION_TABLENAME,
562 columns,
563 AxolotlService.SQLiteAxolotlStore.ACCOUNT + " = ? AND "
564 + AxolotlService.SQLiteAxolotlStore.NAME + " = ? AND "
565 + AxolotlService.SQLiteAxolotlStore.DEVICE_ID + " = ? ",
566 selectionArgs,
567 null, null, null);
568
569 return cursor;
570 }
571
572 public SessionRecord loadSession(Account account, AxolotlAddress contact) {
573 SessionRecord session = null;
574 Cursor cursor = getCursorForSession(account, contact);
575 if(cursor.getCount() != 0) {
576 cursor.moveToFirst();
577 try {
578 session = new SessionRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(AxolotlService.SQLiteAxolotlStore.KEY)),Base64.DEFAULT));
579 } catch (IOException e) {
580 cursor.close();
581 throw new AssertionError(e);
582 }
583 }
584 cursor.close();
585 return session;
586 }
587
588 public List<Integer> getSubDeviceSessions(Account account, AxolotlAddress contact) {
589 List<Integer> devices = new ArrayList<>();
590 final SQLiteDatabase db = this.getReadableDatabase();
591 String[] columns = {AxolotlService.SQLiteAxolotlStore.DEVICE_ID};
592 String[] selectionArgs = {account.getUuid(),
593 contact.getName()};
594 Cursor cursor = db.query(AxolotlService.SQLiteAxolotlStore.SESSION_TABLENAME,
595 columns,
596 AxolotlService.SQLiteAxolotlStore.ACCOUNT + " = ? AND "
597 + AxolotlService.SQLiteAxolotlStore.NAME + " = ? AND ",
598 selectionArgs,
599 null, null, null);
600
601 while(cursor.moveToNext()) {
602 devices.add(cursor.getInt(
603 cursor.getColumnIndex(AxolotlService.SQLiteAxolotlStore.DEVICE_ID)));
604 }
605
606 cursor.close();
607 return devices;
608 }
609
610 public boolean containsSession(Account account, AxolotlAddress contact) {
611 Cursor cursor = getCursorForSession(account, contact);
612 int count = cursor.getCount();
613 cursor.close();
614 return count != 0;
615 }
616
617 public void storeSession(Account account, AxolotlAddress contact, SessionRecord session) {
618 SQLiteDatabase db = this.getWritableDatabase();
619 ContentValues values = new ContentValues();
620 values.put(AxolotlService.SQLiteAxolotlStore.NAME, contact.getName());
621 values.put(AxolotlService.SQLiteAxolotlStore.DEVICE_ID, contact.getDeviceId());
622 values.put(AxolotlService.SQLiteAxolotlStore.KEY, Base64.encodeToString(session.serialize(),Base64.DEFAULT));
623 values.put(AxolotlService.SQLiteAxolotlStore.ACCOUNT, account.getUuid());
624 db.insert(AxolotlService.SQLiteAxolotlStore.SESSION_TABLENAME, null, values);
625 }
626
627 public void deleteSession(Account account, AxolotlAddress contact) {
628 SQLiteDatabase db = this.getWritableDatabase();
629 String[] args = {account.getUuid(),
630 contact.getName(),
631 Integer.toString(contact.getDeviceId())};
632 db.delete(AxolotlService.SQLiteAxolotlStore.SESSION_TABLENAME,
633 AxolotlService.SQLiteAxolotlStore.ACCOUNT + " = ? AND "
634 + AxolotlService.SQLiteAxolotlStore.NAME + " = ? AND "
635 + AxolotlService.SQLiteAxolotlStore.DEVICE_ID + " = ? ",
636 args);
637 }
638
639 public void deleteAllSessions(Account account, AxolotlAddress contact) {
640 SQLiteDatabase db = this.getWritableDatabase();
641 String[] args = {account.getUuid(), contact.getName()};
642 db.delete(AxolotlService.SQLiteAxolotlStore.SESSION_TABLENAME,
643 AxolotlService.SQLiteAxolotlStore.ACCOUNT + "=? AND "
644 + AxolotlService.SQLiteAxolotlStore.NAME + " = ?",
645 args);
646 }
647
648 public boolean isTrustedSession(Account account, AxolotlAddress contact) {
649 boolean trusted = false;
650 Cursor cursor = getCursorForSession(account, contact);
651 if(cursor.getCount() != 0) {
652 cursor.moveToFirst();
653 trusted = cursor.getInt(cursor.getColumnIndex(
654 AxolotlService.SQLiteAxolotlStore.TRUSTED)) > 0;
655 }
656 cursor.close();
657 return trusted;
658 }
659
660 public void setTrustedSession(Account account, AxolotlAddress contact, boolean trusted) {
661 SQLiteDatabase db = this.getWritableDatabase();
662 ContentValues values = new ContentValues();
663 values.put(AxolotlService.SQLiteAxolotlStore.NAME, contact.getName());
664 values.put(AxolotlService.SQLiteAxolotlStore.DEVICE_ID, contact.getDeviceId());
665 values.put(AxolotlService.SQLiteAxolotlStore.ACCOUNT, account.getUuid());
666 values.put(AxolotlService.SQLiteAxolotlStore.TRUSTED, trusted?1:0);
667 db.insert(AxolotlService.SQLiteAxolotlStore.SESSION_TABLENAME, null, values);
668 }
669
670 private Cursor getCursorForPreKey(Account account, int preKeyId) {
671 SQLiteDatabase db = this.getReadableDatabase();
672 String[] columns = {AxolotlService.SQLiteAxolotlStore.KEY};
673 String[] selectionArgs = {account.getUuid(), Integer.toString(preKeyId)};
674 Cursor cursor = db.query(AxolotlService.SQLiteAxolotlStore.PREKEY_TABLENAME,
675 columns,
676 AxolotlService.SQLiteAxolotlStore.ACCOUNT + "=? AND "
677 + AxolotlService.SQLiteAxolotlStore.ID + "=?",
678 selectionArgs,
679 null, null, null);
680
681 return cursor;
682 }
683
684 public PreKeyRecord loadPreKey(Account account, int preKeyId) {
685 PreKeyRecord record = null;
686 Cursor cursor = getCursorForPreKey(account, preKeyId);
687 if(cursor.getCount() != 0) {
688 cursor.moveToFirst();
689 try {
690 record = new PreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(AxolotlService.SQLiteAxolotlStore.KEY)),Base64.DEFAULT));
691 } catch (IOException e ) {
692 throw new AssertionError(e);
693 }
694 }
695 cursor.close();
696 return record;
697 }
698
699 public boolean containsPreKey(Account account, int preKeyId) {
700 Cursor cursor = getCursorForPreKey(account, preKeyId);
701 int count = cursor.getCount();
702 cursor.close();
703 return count != 0;
704 }
705
706 public void storePreKey(Account account, PreKeyRecord record) {
707 SQLiteDatabase db = this.getWritableDatabase();
708 ContentValues values = new ContentValues();
709 values.put(AxolotlService.SQLiteAxolotlStore.ID, record.getId());
710 values.put(AxolotlService.SQLiteAxolotlStore.KEY, Base64.encodeToString(record.serialize(),Base64.DEFAULT));
711 values.put(AxolotlService.SQLiteAxolotlStore.ACCOUNT, account.getUuid());
712 db.insert(AxolotlService.SQLiteAxolotlStore.PREKEY_TABLENAME, null, values);
713 }
714
715 public void deletePreKey(Account account, int preKeyId) {
716 SQLiteDatabase db = this.getWritableDatabase();
717 String[] args = {account.getUuid(), Integer.toString(preKeyId)};
718 db.delete(AxolotlService.SQLiteAxolotlStore.PREKEY_TABLENAME,
719 AxolotlService.SQLiteAxolotlStore.ACCOUNT + "=? AND "
720 + AxolotlService.SQLiteAxolotlStore.ID + "=?",
721 args);
722 }
723
724 private Cursor getCursorForSignedPreKey(Account account, int signedPreKeyId) {
725 SQLiteDatabase db = this.getReadableDatabase();
726 String[] columns = {AxolotlService.SQLiteAxolotlStore.KEY};
727 String[] selectionArgs = {account.getUuid(), Integer.toString(signedPreKeyId)};
728 Cursor cursor = db.query(AxolotlService.SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
729 columns,
730 AxolotlService.SQLiteAxolotlStore.ACCOUNT + "=? AND " + AxolotlService.SQLiteAxolotlStore.ID + "=?",
731 selectionArgs,
732 null, null, null);
733
734 return cursor;
735 }
736
737 public SignedPreKeyRecord loadSignedPreKey(Account account, int signedPreKeyId) {
738 SignedPreKeyRecord record = null;
739 Cursor cursor = getCursorForSignedPreKey(account, signedPreKeyId);
740 if(cursor.getCount() != 0) {
741 cursor.moveToFirst();
742 try {
743 record = new SignedPreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(AxolotlService.SQLiteAxolotlStore.KEY)),Base64.DEFAULT));
744 } catch (IOException e ) {
745 throw new AssertionError(e);
746 }
747 }
748 cursor.close();
749 return record;
750 }
751
752 public List<SignedPreKeyRecord> loadSignedPreKeys(Account account) {
753 List<SignedPreKeyRecord> prekeys = new ArrayList<>();
754 SQLiteDatabase db = this.getReadableDatabase();
755 String[] columns = {AxolotlService.SQLiteAxolotlStore.KEY};
756 String[] selectionArgs = {account.getUuid()};
757 Cursor cursor = db.query(AxolotlService.SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
758 columns,
759 AxolotlService.SQLiteAxolotlStore.ACCOUNT + "=?",
760 selectionArgs,
761 null, null, null);
762
763 while(cursor.moveToNext()) {
764 try {
765 prekeys.add(new SignedPreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(AxolotlService.SQLiteAxolotlStore.KEY)), Base64.DEFAULT)));
766 } catch (IOException ignored) {
767 }
768 }
769 cursor.close();
770 return prekeys;
771 }
772
773 public boolean containsSignedPreKey(Account account, int signedPreKeyId) {
774 Cursor cursor = getCursorForPreKey(account, signedPreKeyId);
775 int count = cursor.getCount();
776 cursor.close();
777 return count != 0;
778 }
779
780 public void storeSignedPreKey(Account account, SignedPreKeyRecord record) {
781 SQLiteDatabase db = this.getWritableDatabase();
782 ContentValues values = new ContentValues();
783 values.put(AxolotlService.SQLiteAxolotlStore.ID, record.getId());
784 values.put(AxolotlService.SQLiteAxolotlStore.KEY, Base64.encodeToString(record.serialize(),Base64.DEFAULT));
785 values.put(AxolotlService.SQLiteAxolotlStore.ACCOUNT, account.getUuid());
786 db.insert(AxolotlService.SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME, null, values);
787 }
788
789 public void deleteSignedPreKey(Account account, int signedPreKeyId) {
790 SQLiteDatabase db = this.getWritableDatabase();
791 String[] args = {account.getUuid(), Integer.toString(signedPreKeyId)};
792 db.delete(AxolotlService.SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
793 AxolotlService.SQLiteAxolotlStore.ACCOUNT + "=? AND "
794 + AxolotlService.SQLiteAxolotlStore.ID + "=?",
795 args);
796 }
797
798 private Cursor getIdentityKeyCursor(Account account, String name, boolean own) {
799 final SQLiteDatabase db = this.getReadableDatabase();
800 String[] columns = {AxolotlService.SQLiteAxolotlStore.KEY};
801 String[] selectionArgs = {account.getUuid(),
802 name,
803 own?"1":"0"};
804 Cursor cursor = db.query(AxolotlService.SQLiteAxolotlStore.IDENTITIES_TABLENAME,
805 columns,
806 AxolotlService.SQLiteAxolotlStore.ACCOUNT + " = ? AND "
807 + AxolotlService.SQLiteAxolotlStore.NAME + " = ? AND "
808 + AxolotlService.SQLiteAxolotlStore.OWN + " = ? ",
809 selectionArgs,
810 null, null, null);
811
812 return cursor;
813 }
814
815 public IdentityKeyPair loadOwnIdentityKeyPair(Account account, String name) {
816 IdentityKeyPair identityKeyPair = null;
817 Cursor cursor = getIdentityKeyCursor(account, name, true);
818 if(cursor.getCount() != 0) {
819 cursor.moveToFirst();
820 try {
821 identityKeyPair = new IdentityKeyPair(Base64.decode(cursor.getString(cursor.getColumnIndex(AxolotlService.SQLiteAxolotlStore.KEY)),Base64.DEFAULT));
822 } catch (InvalidKeyException e) {
823 Log.d(Config.LOGTAG, "Encountered invalid IdentityKey in database for account" + account.getJid().toBareJid() + ", address: " + name);
824 }
825 }
826 cursor.close();
827
828 return identityKeyPair;
829 }
830
831 public Set<IdentityKey> loadIdentityKeys(Account account, String name) {
832 Set<IdentityKey> identityKeys = new HashSet<>();
833 Cursor cursor = getIdentityKeyCursor(account, name, false);
834
835 while(cursor.moveToNext()) {
836 try {
837 identityKeys.add(new IdentityKey(Base64.decode(cursor.getString(cursor.getColumnIndex(AxolotlService.SQLiteAxolotlStore.KEY)),Base64.DEFAULT),0));
838 } catch (InvalidKeyException e) {
839 Log.d(Config.LOGTAG, "Encountered invalid IdentityKey in database for account"+account.getJid().toBareJid()+", address: "+name);
840 }
841 }
842 cursor.close();
843
844 return identityKeys;
845 }
846
847 private void storeIdentityKey(Account account, String name, boolean own, String base64Serialized) {
848 SQLiteDatabase db = this.getWritableDatabase();
849 ContentValues values = new ContentValues();
850 values.put(AxolotlService.SQLiteAxolotlStore.ACCOUNT, account.getUuid());
851 values.put(AxolotlService.SQLiteAxolotlStore.NAME, name);
852 values.put(AxolotlService.SQLiteAxolotlStore.OWN, own?1:0);
853 values.put(AxolotlService.SQLiteAxolotlStore.KEY, base64Serialized);
854 db.insert(AxolotlService.SQLiteAxolotlStore.IDENTITIES_TABLENAME, null, values);
855 }
856
857 public void storeIdentityKey(Account account, String name, IdentityKey identityKey) {
858 storeIdentityKey(account, name, false, Base64.encodeToString(identityKey.serialize(), Base64.DEFAULT));
859 }
860
861 public void storeOwnIdentityKeyPair(Account account, String name, IdentityKeyPair identityKeyPair) {
862 storeIdentityKey(account, name, true, Base64.encodeToString(identityKeyPair.serialize(),Base64.DEFAULT));
863 }
864
865 public void recreateAxolotlDb() {
866 Log.d(Config.LOGTAG, ">>> (RE)CREATING AXOLOTL DATABASE <<<");
867 SQLiteDatabase db = this.getWritableDatabase();
868 db.execSQL("DROP TABLE IF EXISTS " + AxolotlService.SQLiteAxolotlStore.SESSION_TABLENAME);
869 db.execSQL(CREATE_SESSIONS_STATEMENT);
870 db.execSQL("DROP TABLE IF EXISTS " + AxolotlService.SQLiteAxolotlStore.PREKEY_TABLENAME);
871 db.execSQL(CREATE_PREKEYS_STATEMENT);
872 db.execSQL("DROP TABLE IF EXISTS " + AxolotlService.SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME);
873 db.execSQL(CREATE_SIGNED_PREKEYS_STATEMENT);
874 db.execSQL("DROP TABLE IF EXISTS " + AxolotlService.SQLiteAxolotlStore.IDENTITIES_TABLENAME);
875 db.execSQL(CREATE_IDENTITIES_STATEMENT);
876 }
877}