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