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