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