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