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