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