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