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