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;
12import android.util.Pair;
13
14import org.json.JSONObject;
15import org.whispersystems.libaxolotl.AxolotlAddress;
16import org.whispersystems.libaxolotl.IdentityKey;
17import org.whispersystems.libaxolotl.IdentityKeyPair;
18import org.whispersystems.libaxolotl.InvalidKeyException;
19import org.whispersystems.libaxolotl.state.PreKeyRecord;
20import org.whispersystems.libaxolotl.state.SessionRecord;
21import org.whispersystems.libaxolotl.state.SignedPreKeyRecord;
22
23import java.io.ByteArrayInputStream;
24import java.io.IOException;
25import java.security.cert.CertificateEncodingException;
26import java.security.cert.CertificateException;
27import java.security.cert.CertificateFactory;
28import java.security.cert.X509Certificate;
29import java.util.ArrayList;
30import java.util.HashSet;
31import java.util.Iterator;
32import java.util.List;
33import java.util.Set;
34import java.util.concurrent.CopyOnWriteArrayList;
35import org.json.JSONException;
36
37import eu.siacs.conversations.Config;
38import eu.siacs.conversations.crypto.axolotl.AxolotlService;
39import eu.siacs.conversations.crypto.axolotl.SQLiteAxolotlStore;
40import eu.siacs.conversations.crypto.axolotl.XmppAxolotlSession;
41import eu.siacs.conversations.entities.Account;
42import eu.siacs.conversations.entities.Contact;
43import eu.siacs.conversations.entities.Conversation;
44import eu.siacs.conversations.entities.Message;
45import eu.siacs.conversations.entities.PresenceTemplate;
46import eu.siacs.conversations.entities.Roster;
47import eu.siacs.conversations.entities.ServiceDiscoveryResult;
48import eu.siacs.conversations.generator.AbstractGenerator;
49import eu.siacs.conversations.xmpp.jid.InvalidJidException;
50import eu.siacs.conversations.xmpp.jid.Jid;
51
52public class DatabaseBackend extends SQLiteOpenHelper {
53
54 private static DatabaseBackend instance = null;
55
56 private static final String DATABASE_NAME = "history";
57 private static final int DATABASE_VERSION = 28;
58
59 private static String CREATE_CONTATCS_STATEMENT = "create table "
60 + Contact.TABLENAME + "(" + Contact.ACCOUNT + " TEXT, "
61 + Contact.SERVERNAME + " TEXT, " + Contact.SYSTEMNAME + " TEXT,"
62 + Contact.JID + " TEXT," + Contact.KEYS + " TEXT,"
63 + Contact.PHOTOURI + " TEXT," + Contact.OPTIONS + " NUMBER,"
64 + Contact.SYSTEMACCOUNT + " NUMBER, " + Contact.AVATAR + " TEXT, "
65 + Contact.LAST_PRESENCE + " TEXT, " + Contact.LAST_TIME + " NUMBER, "
66 + Contact.GROUPS + " TEXT, FOREIGN KEY(" + Contact.ACCOUNT + ") REFERENCES "
67 + Account.TABLENAME + "(" + Account.UUID
68 + ") ON DELETE CASCADE, UNIQUE(" + Contact.ACCOUNT + ", "
69 + Contact.JID + ") ON CONFLICT REPLACE);";
70
71 private static String CREATE_DISCOVERY_RESULTS_STATEMENT = "create table "
72 + ServiceDiscoveryResult.TABLENAME + "("
73 + ServiceDiscoveryResult.HASH + " TEXT, "
74 + ServiceDiscoveryResult.VER + " TEXT, "
75 + ServiceDiscoveryResult.RESULT + " TEXT, "
76 + "UNIQUE(" + ServiceDiscoveryResult.HASH + ", "
77 + ServiceDiscoveryResult.VER + ") ON CONFLICT REPLACE);";
78
79 private static String CREATE_PRESENCE_TEMPLATES_STATEMENT = "CREATE TABLE "
80 + PresenceTemplate.TABELNAME + "("
81 + PresenceTemplate.UUID + " TEXT, "
82 + PresenceTemplate.LAST_USED + " NUMBER,"
83 + PresenceTemplate.MESSAGE + " TEXT,"
84 + PresenceTemplate.STATUS + " TEXT,"
85 + "UNIQUE("+PresenceTemplate.MESSAGE + "," +PresenceTemplate.STATUS+") ON CONFLICT REPLACE);";
86
87 private static String CREATE_PREKEYS_STATEMENT = "CREATE TABLE "
88 + SQLiteAxolotlStore.PREKEY_TABLENAME + "("
89 + SQLiteAxolotlStore.ACCOUNT + " TEXT, "
90 + SQLiteAxolotlStore.ID + " INTEGER, "
91 + SQLiteAxolotlStore.KEY + " TEXT, FOREIGN KEY("
92 + SQLiteAxolotlStore.ACCOUNT
93 + ") REFERENCES " + Account.TABLENAME + "(" + Account.UUID + ") ON DELETE CASCADE, "
94 + "UNIQUE( " + SQLiteAxolotlStore.ACCOUNT + ", "
95 + SQLiteAxolotlStore.ID
96 + ") ON CONFLICT REPLACE"
97 + ");";
98
99 private static String CREATE_SIGNED_PREKEYS_STATEMENT = "CREATE TABLE "
100 + SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME + "("
101 + SQLiteAxolotlStore.ACCOUNT + " TEXT, "
102 + SQLiteAxolotlStore.ID + " INTEGER, "
103 + SQLiteAxolotlStore.KEY + " TEXT, FOREIGN KEY("
104 + SQLiteAxolotlStore.ACCOUNT
105 + ") REFERENCES " + Account.TABLENAME + "(" + Account.UUID + ") ON DELETE CASCADE, "
106 + "UNIQUE( " + SQLiteAxolotlStore.ACCOUNT + ", "
107 + SQLiteAxolotlStore.ID
108 + ") ON CONFLICT REPLACE" +
109 ");";
110
111 private static String CREATE_SESSIONS_STATEMENT = "CREATE TABLE "
112 + SQLiteAxolotlStore.SESSION_TABLENAME + "("
113 + SQLiteAxolotlStore.ACCOUNT + " TEXT, "
114 + SQLiteAxolotlStore.NAME + " TEXT, "
115 + SQLiteAxolotlStore.DEVICE_ID + " INTEGER, "
116 + SQLiteAxolotlStore.KEY + " TEXT, FOREIGN KEY("
117 + SQLiteAxolotlStore.ACCOUNT
118 + ") REFERENCES " + Account.TABLENAME + "(" + Account.UUID + ") ON DELETE CASCADE, "
119 + "UNIQUE( " + SQLiteAxolotlStore.ACCOUNT + ", "
120 + SQLiteAxolotlStore.NAME + ", "
121 + SQLiteAxolotlStore.DEVICE_ID
122 + ") ON CONFLICT REPLACE"
123 + ");";
124
125 private static String CREATE_IDENTITIES_STATEMENT = "CREATE TABLE "
126 + SQLiteAxolotlStore.IDENTITIES_TABLENAME + "("
127 + SQLiteAxolotlStore.ACCOUNT + " TEXT, "
128 + SQLiteAxolotlStore.NAME + " TEXT, "
129 + SQLiteAxolotlStore.OWN + " INTEGER, "
130 + SQLiteAxolotlStore.FINGERPRINT + " TEXT, "
131 + SQLiteAxolotlStore.CERTIFICATE + " BLOB, "
132 + SQLiteAxolotlStore.TRUSTED + " INTEGER, "
133 + SQLiteAxolotlStore.KEY + " TEXT, FOREIGN KEY("
134 + SQLiteAxolotlStore.ACCOUNT
135 + ") REFERENCES " + Account.TABLENAME + "(" + Account.UUID + ") ON DELETE CASCADE, "
136 + "UNIQUE( " + SQLiteAxolotlStore.ACCOUNT + ", "
137 + SQLiteAxolotlStore.NAME + ", "
138 + SQLiteAxolotlStore.FINGERPRINT
139 + ") ON CONFLICT IGNORE"
140 + ");";
141
142 private DatabaseBackend(Context context) {
143 super(context, DATABASE_NAME, null, DATABASE_VERSION);
144 }
145
146 @Override
147 public void onCreate(SQLiteDatabase db) {
148 db.execSQL("PRAGMA foreign_keys=ON;");
149 db.execSQL("create table " + Account.TABLENAME + "(" + Account.UUID+ " TEXT PRIMARY KEY,"
150 + Account.USERNAME + " TEXT,"
151 + Account.SERVER + " TEXT,"
152 + Account.PASSWORD + " TEXT,"
153 + Account.DISPLAY_NAME + " TEXT, "
154 + Account.STATUS + " TEXT,"
155 + Account.STATUS_MESSAGE + " TEXT,"
156 + Account.ROSTERVERSION + " TEXT,"
157 + Account.OPTIONS + " NUMBER, "
158 + Account.AVATAR + " TEXT, "
159 + Account.KEYS + " TEXT, "
160 + Account.HOSTNAME + " TEXT, "
161 + Account.PORT + " NUMBER DEFAULT 5222)");
162 db.execSQL("create table " + Conversation.TABLENAME + " ("
163 + Conversation.UUID + " TEXT PRIMARY KEY, " + Conversation.NAME
164 + " TEXT, " + Conversation.CONTACT + " TEXT, "
165 + Conversation.ACCOUNT + " TEXT, " + Conversation.CONTACTJID
166 + " TEXT, " + Conversation.CREATED + " NUMBER, "
167 + Conversation.STATUS + " NUMBER, " + Conversation.MODE
168 + " NUMBER, " + Conversation.ATTRIBUTES + " TEXT, FOREIGN KEY("
169 + Conversation.ACCOUNT + ") REFERENCES " + Account.TABLENAME
170 + "(" + Account.UUID + ") ON DELETE CASCADE);");
171 db.execSQL("create table " + Message.TABLENAME + "( " + Message.UUID
172 + " TEXT PRIMARY KEY, " + Message.CONVERSATION + " TEXT, "
173 + Message.TIME_SENT + " NUMBER, " + Message.COUNTERPART
174 + " TEXT, " + Message.TRUE_COUNTERPART + " TEXT,"
175 + Message.BODY + " TEXT, " + Message.ENCRYPTION + " NUMBER, "
176 + Message.STATUS + " NUMBER," + Message.TYPE + " NUMBER, "
177 + Message.RELATIVE_FILE_PATH + " TEXT, "
178 + Message.SERVER_MSG_ID + " TEXT, "
179 + Message.FINGERPRINT + " TEXT, "
180 + Message.CARBON + " INTEGER, "
181 + Message.EDITED + " TEXT, "
182 + Message.READ + " NUMBER DEFAULT 1, "
183 + Message.OOB + " INTEGER, "
184 + Message.REMOTE_MSG_ID + " TEXT, FOREIGN KEY("
185 + Message.CONVERSATION + ") REFERENCES "
186 + Conversation.TABLENAME + "(" + Conversation.UUID
187 + ") ON DELETE CASCADE);");
188
189 db.execSQL(CREATE_CONTATCS_STATEMENT);
190 db.execSQL(CREATE_DISCOVERY_RESULTS_STATEMENT);
191 db.execSQL(CREATE_SESSIONS_STATEMENT);
192 db.execSQL(CREATE_PREKEYS_STATEMENT);
193 db.execSQL(CREATE_SIGNED_PREKEYS_STATEMENT);
194 db.execSQL(CREATE_IDENTITIES_STATEMENT);
195 db.execSQL(CREATE_PRESENCE_TEMPLATES_STATEMENT);
196 }
197
198 @Override
199 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
200 if (oldVersion < 2 && newVersion >= 2) {
201 db.execSQL("update " + Account.TABLENAME + " set "
202 + Account.OPTIONS + " = " + Account.OPTIONS + " | 8");
203 }
204 if (oldVersion < 3 && newVersion >= 3) {
205 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
206 + Message.TYPE + " NUMBER");
207 }
208 if (oldVersion < 5 && newVersion >= 5) {
209 db.execSQL("DROP TABLE " + Contact.TABLENAME);
210 db.execSQL(CREATE_CONTATCS_STATEMENT);
211 db.execSQL("UPDATE " + Account.TABLENAME + " SET "
212 + Account.ROSTERVERSION + " = NULL");
213 }
214 if (oldVersion < 6 && newVersion >= 6) {
215 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
216 + Message.TRUE_COUNTERPART + " TEXT");
217 }
218 if (oldVersion < 7 && newVersion >= 7) {
219 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
220 + Message.REMOTE_MSG_ID + " TEXT");
221 db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
222 + Contact.AVATAR + " TEXT");
223 db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN "
224 + Account.AVATAR + " TEXT");
225 }
226 if (oldVersion < 8 && newVersion >= 8) {
227 db.execSQL("ALTER TABLE " + Conversation.TABLENAME + " ADD COLUMN "
228 + Conversation.ATTRIBUTES + " TEXT");
229 }
230 if (oldVersion < 9 && newVersion >= 9) {
231 db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
232 + Contact.LAST_TIME + " NUMBER");
233 db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
234 + Contact.LAST_PRESENCE + " TEXT");
235 }
236 if (oldVersion < 10 && newVersion >= 10) {
237 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
238 + Message.RELATIVE_FILE_PATH + " TEXT");
239 }
240 if (oldVersion < 11 && newVersion >= 11) {
241 db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
242 + Contact.GROUPS + " TEXT");
243 db.execSQL("delete from " + Contact.TABLENAME);
244 db.execSQL("update " + Account.TABLENAME + " set " + Account.ROSTERVERSION + " = NULL");
245 }
246 if (oldVersion < 12 && newVersion >= 12) {
247 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
248 + Message.SERVER_MSG_ID + " TEXT");
249 }
250 if (oldVersion < 13 && newVersion >= 13) {
251 db.execSQL("delete from " + Contact.TABLENAME);
252 db.execSQL("update " + Account.TABLENAME + " set " + Account.ROSTERVERSION + " = NULL");
253 }
254 if (oldVersion < 14 && newVersion >= 14) {
255 canonicalizeJids(db);
256 }
257 if (oldVersion < 15 && newVersion >= 15) {
258 recreateAxolotlDb(db);
259 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
260 + Message.FINGERPRINT + " TEXT");
261 } else if (oldVersion < 22 && newVersion >= 22) {
262 db.execSQL("ALTER TABLE " + SQLiteAxolotlStore.IDENTITIES_TABLENAME + " ADD COLUMN " + SQLiteAxolotlStore.CERTIFICATE);
263 }
264 if (oldVersion < 16 && newVersion >= 16) {
265 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
266 + Message.CARBON + " INTEGER");
267 }
268 if (oldVersion < 19 && newVersion >= 19) {
269 db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN " + Account.DISPLAY_NAME + " TEXT");
270 }
271 if (oldVersion < 20 && newVersion >= 20) {
272 db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN " + Account.HOSTNAME + " TEXT");
273 db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN " + Account.PORT + " NUMBER DEFAULT 5222");
274 }
275 if (oldVersion < 26 && newVersion >= 26) {
276 db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN " + Account.STATUS + " TEXT");
277 db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN " + Account.STATUS_MESSAGE + " TEXT");
278 }
279 /* Any migrations that alter the Account table need to happen BEFORE this migration, as it
280 * depends on account de-serialization.
281 */
282 if (oldVersion < 17 && newVersion >= 17) {
283 List<Account> accounts = getAccounts(db);
284 for (Account account : accounts) {
285 String ownDeviceIdString = account.getKey(SQLiteAxolotlStore.JSONKEY_REGISTRATION_ID);
286 if (ownDeviceIdString == null) {
287 continue;
288 }
289 int ownDeviceId = Integer.valueOf(ownDeviceIdString);
290 AxolotlAddress ownAddress = new AxolotlAddress(account.getJid().toBareJid().toString(), ownDeviceId);
291 deleteSession(db, account, ownAddress);
292 IdentityKeyPair identityKeyPair = loadOwnIdentityKeyPair(db, account);
293 if (identityKeyPair != null) {
294 setIdentityKeyTrust(db, account, identityKeyPair.getPublicKey().getFingerprint().replaceAll("\\s", ""), XmppAxolotlSession.Trust.TRUSTED);
295 } else {
296 Log.d(Config.LOGTAG, account.getJid().toBareJid() + ": could not load own identity key pair");
297 }
298 }
299 }
300 if (oldVersion < 18 && newVersion >= 18) {
301 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN " + Message.READ + " NUMBER DEFAULT 1");
302 }
303
304 if (oldVersion < 21 && newVersion >= 21) {
305 List<Account> accounts = getAccounts(db);
306 for (Account account : accounts) {
307 account.unsetPgpSignature();
308 db.update(Account.TABLENAME, account.getContentValues(), Account.UUID
309 + "=?", new String[]{account.getUuid()});
310 }
311 }
312
313 if (oldVersion < 23 && newVersion >= 23) {
314 db.execSQL(CREATE_DISCOVERY_RESULTS_STATEMENT);
315 }
316
317 if (oldVersion < 24 && newVersion >= 24) {
318 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN " + Message.EDITED + " TEXT");
319 }
320
321 if (oldVersion < 25 && newVersion >= 25) {
322 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN " + Message.OOB + " INTEGER");
323 }
324
325 if (oldVersion < 26 && newVersion >= 26) {
326 db.execSQL(CREATE_PRESENCE_TEMPLATES_STATEMENT);
327 }
328
329 if (oldVersion < 27 && newVersion >= 27) {
330 db.execSQL("DELETE FROM "+ServiceDiscoveryResult.TABLENAME);
331 }
332
333 if (oldVersion < 28 && newVersion >= 28) {
334 canonicalizeJids(db);
335 }
336 }
337
338 private void canonicalizeJids(SQLiteDatabase db) {
339 // migrate db to new, canonicalized JID domainpart representation
340
341 // Conversation table
342 Cursor cursor = db.rawQuery("select * from " + Conversation.TABLENAME, new String[0]);
343 while (cursor.moveToNext()) {
344 String newJid;
345 try {
346 newJid = Jid.fromString(
347 cursor.getString(cursor.getColumnIndex(Conversation.CONTACTJID))
348 ).toString();
349 } catch (InvalidJidException ignored) {
350 Log.e(Config.LOGTAG, "Failed to migrate Conversation CONTACTJID "
351 + cursor.getString(cursor.getColumnIndex(Conversation.CONTACTJID))
352 + ": " + ignored + ". Skipping...");
353 continue;
354 }
355
356 String updateArgs[] = {
357 newJid,
358 cursor.getString(cursor.getColumnIndex(Conversation.UUID)),
359 };
360 db.execSQL("update " + Conversation.TABLENAME
361 + " set " + Conversation.CONTACTJID + " = ? "
362 + " where " + Conversation.UUID + " = ?", updateArgs);
363 }
364 cursor.close();
365
366 // Contact table
367 cursor = db.rawQuery("select * from " + Contact.TABLENAME, new String[0]);
368 while (cursor.moveToNext()) {
369 String newJid;
370 try {
371 newJid = Jid.fromString(
372 cursor.getString(cursor.getColumnIndex(Contact.JID))
373 ).toString();
374 } catch (InvalidJidException ignored) {
375 Log.e(Config.LOGTAG, "Failed to migrate Contact JID "
376 + cursor.getString(cursor.getColumnIndex(Contact.JID))
377 + ": " + ignored + ". Skipping...");
378 continue;
379 }
380
381 String updateArgs[] = {
382 newJid,
383 cursor.getString(cursor.getColumnIndex(Contact.ACCOUNT)),
384 cursor.getString(cursor.getColumnIndex(Contact.JID)),
385 };
386 db.execSQL("update " + Contact.TABLENAME
387 + " set " + Contact.JID + " = ? "
388 + " where " + Contact.ACCOUNT + " = ? "
389 + " AND " + Contact.JID + " = ?", updateArgs);
390 }
391 cursor.close();
392
393 // Account table
394 cursor = db.rawQuery("select * from " + Account.TABLENAME, new String[0]);
395 while (cursor.moveToNext()) {
396 String newServer;
397 try {
398 newServer = Jid.fromParts(
399 cursor.getString(cursor.getColumnIndex(Account.USERNAME)),
400 cursor.getString(cursor.getColumnIndex(Account.SERVER)),
401 "mobile"
402 ).getDomainpart();
403 } catch (InvalidJidException ignored) {
404 Log.e(Config.LOGTAG, "Failed to migrate Account SERVER "
405 + cursor.getString(cursor.getColumnIndex(Account.SERVER))
406 + ": " + ignored + ". Skipping...");
407 continue;
408 }
409
410 String updateArgs[] = {
411 newServer,
412 cursor.getString(cursor.getColumnIndex(Account.UUID)),
413 };
414 db.execSQL("update " + Account.TABLENAME
415 + " set " + Account.SERVER + " = ? "
416 + " where " + Account.UUID + " = ?", updateArgs);
417 }
418 cursor.close();
419 }
420
421 public static synchronized DatabaseBackend getInstance(Context context) {
422 if (instance == null) {
423 instance = new DatabaseBackend(context);
424 }
425 return instance;
426 }
427
428 public void createConversation(Conversation conversation) {
429 SQLiteDatabase db = this.getWritableDatabase();
430 db.insert(Conversation.TABLENAME, null, conversation.getContentValues());
431 }
432
433 public void createMessage(Message message) {
434 SQLiteDatabase db = this.getWritableDatabase();
435 db.insert(Message.TABLENAME, null, message.getContentValues());
436 }
437
438 public void createAccount(Account account) {
439 SQLiteDatabase db = this.getWritableDatabase();
440 db.insert(Account.TABLENAME, null, account.getContentValues());
441 }
442
443 public void insertDiscoveryResult(ServiceDiscoveryResult result) {
444 SQLiteDatabase db = this.getWritableDatabase();
445 db.insert(ServiceDiscoveryResult.TABLENAME, null, result.getContentValues());
446 }
447
448 public ServiceDiscoveryResult findDiscoveryResult(final String hash, final String ver) {
449 SQLiteDatabase db = this.getReadableDatabase();
450 String[] selectionArgs = {hash, ver};
451 Cursor cursor = db.query(ServiceDiscoveryResult.TABLENAME, null,
452 ServiceDiscoveryResult.HASH + "=? AND " + ServiceDiscoveryResult.VER + "=?",
453 selectionArgs, null, null, null);
454 if (cursor.getCount() == 0) {
455 cursor.close();
456 return null;
457 }
458 cursor.moveToFirst();
459
460 ServiceDiscoveryResult result = null;
461 try {
462 result = new ServiceDiscoveryResult(cursor);
463 } catch (JSONException e) { /* result is still null */ }
464
465 cursor.close();
466 return result;
467 }
468
469 public void insertPresenceTemplate(PresenceTemplate template) {
470 SQLiteDatabase db = this.getWritableDatabase();
471 db.insert(PresenceTemplate.TABELNAME, null, template.getContentValues());
472 }
473
474 public List<PresenceTemplate> getPresenceTemplates() {
475 ArrayList<PresenceTemplate> templates = new ArrayList<>();
476 SQLiteDatabase db = this.getReadableDatabase();
477 Cursor cursor = db.query(PresenceTemplate.TABELNAME,null,null,null,null,null,PresenceTemplate.LAST_USED+" desc");
478 while (cursor.moveToNext()) {
479 templates.add(PresenceTemplate.fromCursor(cursor));
480 }
481 cursor.close();
482 return templates;
483 }
484
485 public void deletePresenceTemplate(PresenceTemplate template) {
486 Log.d(Config.LOGTAG,"deleting presence template with uuid "+template.getUuid());
487 SQLiteDatabase db = this.getWritableDatabase();
488 String where = PresenceTemplate.UUID+"=?";
489 String[] whereArgs = {template.getUuid()};
490 db.delete(PresenceTemplate.TABELNAME,where,whereArgs);
491 }
492
493 public CopyOnWriteArrayList<Conversation> getConversations(int status) {
494 CopyOnWriteArrayList<Conversation> list = new CopyOnWriteArrayList<>();
495 SQLiteDatabase db = this.getReadableDatabase();
496 String[] selectionArgs = {Integer.toString(status)};
497 Cursor cursor = db.rawQuery("select * from " + Conversation.TABLENAME
498 + " where " + Conversation.STATUS + " = ? order by "
499 + Conversation.CREATED + " desc", selectionArgs);
500 while (cursor.moveToNext()) {
501 list.add(Conversation.fromCursor(cursor));
502 }
503 cursor.close();
504 return list;
505 }
506
507 public ArrayList<Message> getMessages(Conversation conversations, int limit) {
508 return getMessages(conversations, limit, -1);
509 }
510
511 public ArrayList<Message> getMessages(Conversation conversation, int limit,
512 long timestamp) {
513 ArrayList<Message> list = new ArrayList<>();
514 SQLiteDatabase db = this.getReadableDatabase();
515 Cursor cursor;
516 if (timestamp == -1) {
517 String[] selectionArgs = {conversation.getUuid()};
518 cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
519 + "=?", selectionArgs, null, null, Message.TIME_SENT
520 + " DESC", String.valueOf(limit));
521 } else {
522 String[] selectionArgs = {conversation.getUuid(),
523 Long.toString(timestamp)};
524 cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
525 + "=? and " + Message.TIME_SENT + "<?", selectionArgs,
526 null, null, Message.TIME_SENT + " DESC",
527 String.valueOf(limit));
528 }
529 if (cursor.getCount() > 0) {
530 cursor.moveToLast();
531 do {
532 Message message = Message.fromCursor(cursor);
533 message.setConversation(conversation);
534 list.add(message);
535 } while (cursor.moveToPrevious());
536 }
537 cursor.close();
538 return list;
539 }
540
541 public Iterable<Message> getMessagesIterable(final Conversation conversation) {
542 return new Iterable<Message>() {
543 @Override
544 public Iterator<Message> iterator() {
545 class MessageIterator implements Iterator<Message> {
546 SQLiteDatabase db = getReadableDatabase();
547 String[] selectionArgs = {conversation.getUuid()};
548 Cursor cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
549 + "=?", selectionArgs, null, null, Message.TIME_SENT
550 + " ASC", null);
551
552 public MessageIterator() {
553 cursor.moveToFirst();
554 }
555
556 @Override
557 public boolean hasNext() {
558 return !cursor.isAfterLast();
559 }
560
561 @Override
562 public Message next() {
563 Message message = Message.fromCursor(cursor);
564 cursor.moveToNext();
565 return message;
566 }
567
568 @Override
569 public void remove() {
570 throw new UnsupportedOperationException();
571 }
572 }
573 return new MessageIterator();
574 }
575 };
576 }
577
578 public Conversation findConversation(final Account account, final Jid contactJid) {
579 SQLiteDatabase db = this.getReadableDatabase();
580 String[] selectionArgs = {account.getUuid(),
581 contactJid.toBareJid().toString() + "/%",
582 contactJid.toBareJid().toString()
583 };
584 Cursor cursor = db.query(Conversation.TABLENAME, null,
585 Conversation.ACCOUNT + "=? AND (" + Conversation.CONTACTJID
586 + " like ? OR " + Conversation.CONTACTJID + "=?)", selectionArgs, null, null, null);
587 if (cursor.getCount() == 0) {
588 cursor.close();
589 return null;
590 }
591 cursor.moveToFirst();
592 Conversation conversation = Conversation.fromCursor(cursor);
593 cursor.close();
594 return conversation;
595 }
596
597 public void updateConversation(final Conversation conversation) {
598 final SQLiteDatabase db = this.getWritableDatabase();
599 final String[] args = {conversation.getUuid()};
600 db.update(Conversation.TABLENAME, conversation.getContentValues(),
601 Conversation.UUID + "=?", args);
602 }
603
604 public List<Account> getAccounts() {
605 SQLiteDatabase db = this.getReadableDatabase();
606 return getAccounts(db);
607 }
608
609 private List<Account> getAccounts(SQLiteDatabase db) {
610 List<Account> list = new ArrayList<>();
611 Cursor cursor = db.query(Account.TABLENAME, null, null, null, null,
612 null, null);
613 while (cursor.moveToNext()) {
614 list.add(Account.fromCursor(cursor));
615 }
616 cursor.close();
617 return list;
618 }
619
620 public boolean updateAccount(Account account) {
621 SQLiteDatabase db = this.getWritableDatabase();
622 String[] args = {account.getUuid()};
623 final int rows = db.update(Account.TABLENAME, account.getContentValues(), Account.UUID + "=?", args);
624 return rows == 1;
625 }
626
627 public boolean deleteAccount(Account account) {
628 SQLiteDatabase db = this.getWritableDatabase();
629 String[] args = {account.getUuid()};
630 final int rows = db.delete(Account.TABLENAME, Account.UUID + "=?", args);
631 return rows == 1;
632 }
633
634 public boolean hasEnabledAccounts() {
635 SQLiteDatabase db = this.getReadableDatabase();
636 Cursor cursor = db.rawQuery("select count(" + Account.UUID + ") from "
637 + Account.TABLENAME + " where not options & (1 <<1)", null);
638 try {
639 cursor.moveToFirst();
640 int count = cursor.getInt(0);
641 return (count > 0);
642 } catch (SQLiteCantOpenDatabaseException e) {
643 return true; // better safe than sorry
644 } catch (RuntimeException e) {
645 return true; // better safe than sorry
646 } finally {
647 if (cursor != null) {
648 cursor.close();
649 }
650 }
651 }
652
653 @Override
654 public SQLiteDatabase getWritableDatabase() {
655 SQLiteDatabase db = super.getWritableDatabase();
656 db.execSQL("PRAGMA foreign_keys=ON;");
657 return db;
658 }
659
660 public void updateMessage(Message message) {
661 SQLiteDatabase db = this.getWritableDatabase();
662 String[] args = {message.getUuid()};
663 db.update(Message.TABLENAME, message.getContentValues(), Message.UUID
664 + "=?", args);
665 }
666
667 public void updateMessage(Message message, String uuid) {
668 SQLiteDatabase db = this.getWritableDatabase();
669 String[] args = {uuid};
670 db.update(Message.TABLENAME, message.getContentValues(), Message.UUID
671 + "=?", args);
672 }
673
674 public void readRoster(Roster roster) {
675 SQLiteDatabase db = this.getReadableDatabase();
676 Cursor cursor;
677 String args[] = {roster.getAccount().getUuid()};
678 cursor = db.query(Contact.TABLENAME, null, Contact.ACCOUNT + "=?", args, null, null, null);
679 while (cursor.moveToNext()) {
680 roster.initContact(Contact.fromCursor(cursor));
681 }
682 cursor.close();
683 }
684
685 public void writeRoster(final Roster roster) {
686 final Account account = roster.getAccount();
687 final SQLiteDatabase db = this.getWritableDatabase();
688 db.beginTransaction();
689 for (Contact contact : roster.getContacts()) {
690 if (contact.getOption(Contact.Options.IN_ROSTER)) {
691 db.insert(Contact.TABLENAME, null, contact.getContentValues());
692 } else {
693 String where = Contact.ACCOUNT + "=? AND " + Contact.JID + "=?";
694 String[] whereArgs = {account.getUuid(), contact.getJid().toString()};
695 db.delete(Contact.TABLENAME, where, whereArgs);
696 }
697 }
698 db.setTransactionSuccessful();
699 db.endTransaction();
700 account.setRosterVersion(roster.getVersion());
701 updateAccount(account);
702 }
703
704 public void deleteMessagesInConversation(Conversation conversation) {
705 SQLiteDatabase db = this.getWritableDatabase();
706 String[] args = {conversation.getUuid()};
707 db.delete(Message.TABLENAME, Message.CONVERSATION + "=?", args);
708 }
709
710 public Pair<Long, String> getLastMessageReceived(Account account) {
711 Cursor cursor = null;
712 try {
713 SQLiteDatabase db = this.getReadableDatabase();
714 String sql = "select messages.timeSent,messages.serverMsgId from accounts join conversations on accounts.uuid=conversations.accountUuid join messages on conversations.uuid=messages.conversationUuid where accounts.uuid=? and (messages.status=0 or messages.carbon=1 or messages.serverMsgId not null) order by messages.timesent desc limit 1";
715 String[] args = {account.getUuid()};
716 cursor = db.rawQuery(sql, args);
717 if (cursor.getCount() == 0) {
718 return null;
719 } else {
720 cursor.moveToFirst();
721 return new Pair<>(cursor.getLong(0), cursor.getString(1));
722 }
723 } catch (Exception e) {
724 return null;
725 } finally {
726 if (cursor != null) {
727 cursor.close();
728 }
729 }
730 }
731
732 public Pair<Long,String> getLastClearDate(Account account) {
733 SQLiteDatabase db = this.getReadableDatabase();
734 String[] columns = {Conversation.ATTRIBUTES};
735 String selection = Conversation.ACCOUNT + "=?";
736 String[] args = {account.getUuid()};
737 Cursor cursor = db.query(Conversation.TABLENAME,columns,selection,args,null,null,null);
738 long maxClearDate = 0;
739 while (cursor.moveToNext()) {
740 try {
741 final JSONObject jsonObject = new JSONObject(cursor.getString(0));
742 maxClearDate = Math.max(maxClearDate, jsonObject.getLong(Conversation.ATTRIBUTE_LAST_CLEAR_HISTORY));
743 } catch (Exception e) {
744 //ignored
745 }
746 }
747 cursor.close();
748 return new Pair<>(maxClearDate,null);
749 }
750
751 private Cursor getCursorForSession(Account account, AxolotlAddress contact) {
752 final SQLiteDatabase db = this.getReadableDatabase();
753 String[] selectionArgs = {account.getUuid(),
754 contact.getName(),
755 Integer.toString(contact.getDeviceId())};
756 return db.query(SQLiteAxolotlStore.SESSION_TABLENAME,
757 null,
758 SQLiteAxolotlStore.ACCOUNT + " = ? AND "
759 + SQLiteAxolotlStore.NAME + " = ? AND "
760 + SQLiteAxolotlStore.DEVICE_ID + " = ? ",
761 selectionArgs,
762 null, null, null);
763 }
764
765 public SessionRecord loadSession(Account account, AxolotlAddress contact) {
766 SessionRecord session = null;
767 Cursor cursor = getCursorForSession(account, contact);
768 if (cursor.getCount() != 0) {
769 cursor.moveToFirst();
770 try {
771 session = new SessionRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
772 } catch (IOException e) {
773 cursor.close();
774 throw new AssertionError(e);
775 }
776 }
777 cursor.close();
778 return session;
779 }
780
781 public List<Integer> getSubDeviceSessions(Account account, AxolotlAddress contact) {
782 final SQLiteDatabase db = this.getReadableDatabase();
783 return getSubDeviceSessions(db, account, contact);
784 }
785
786 private List<Integer> getSubDeviceSessions(SQLiteDatabase db, Account account, AxolotlAddress contact) {
787 List<Integer> devices = new ArrayList<>();
788 String[] columns = {SQLiteAxolotlStore.DEVICE_ID};
789 String[] selectionArgs = {account.getUuid(),
790 contact.getName()};
791 Cursor cursor = db.query(SQLiteAxolotlStore.SESSION_TABLENAME,
792 columns,
793 SQLiteAxolotlStore.ACCOUNT + " = ? AND "
794 + SQLiteAxolotlStore.NAME + " = ?",
795 selectionArgs,
796 null, null, null);
797
798 while (cursor.moveToNext()) {
799 devices.add(cursor.getInt(
800 cursor.getColumnIndex(SQLiteAxolotlStore.DEVICE_ID)));
801 }
802
803 cursor.close();
804 return devices;
805 }
806
807 public boolean containsSession(Account account, AxolotlAddress contact) {
808 Cursor cursor = getCursorForSession(account, contact);
809 int count = cursor.getCount();
810 cursor.close();
811 return count != 0;
812 }
813
814 public void storeSession(Account account, AxolotlAddress contact, SessionRecord session) {
815 SQLiteDatabase db = this.getWritableDatabase();
816 ContentValues values = new ContentValues();
817 values.put(SQLiteAxolotlStore.NAME, contact.getName());
818 values.put(SQLiteAxolotlStore.DEVICE_ID, contact.getDeviceId());
819 values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(session.serialize(), Base64.DEFAULT));
820 values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
821 db.insert(SQLiteAxolotlStore.SESSION_TABLENAME, null, values);
822 }
823
824 public void deleteSession(Account account, AxolotlAddress contact) {
825 SQLiteDatabase db = this.getWritableDatabase();
826 deleteSession(db, account, contact);
827 }
828
829 private void deleteSession(SQLiteDatabase db, Account account, AxolotlAddress contact) {
830 String[] args = {account.getUuid(),
831 contact.getName(),
832 Integer.toString(contact.getDeviceId())};
833 db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
834 SQLiteAxolotlStore.ACCOUNT + " = ? AND "
835 + SQLiteAxolotlStore.NAME + " = ? AND "
836 + SQLiteAxolotlStore.DEVICE_ID + " = ? ",
837 args);
838 }
839
840 public void deleteAllSessions(Account account, AxolotlAddress contact) {
841 SQLiteDatabase db = this.getWritableDatabase();
842 String[] args = {account.getUuid(), contact.getName()};
843 db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
844 SQLiteAxolotlStore.ACCOUNT + "=? AND "
845 + SQLiteAxolotlStore.NAME + " = ?",
846 args);
847 }
848
849 private Cursor getCursorForPreKey(Account account, int preKeyId) {
850 SQLiteDatabase db = this.getReadableDatabase();
851 String[] columns = {SQLiteAxolotlStore.KEY};
852 String[] selectionArgs = {account.getUuid(), Integer.toString(preKeyId)};
853 Cursor cursor = db.query(SQLiteAxolotlStore.PREKEY_TABLENAME,
854 columns,
855 SQLiteAxolotlStore.ACCOUNT + "=? AND "
856 + SQLiteAxolotlStore.ID + "=?",
857 selectionArgs,
858 null, null, null);
859
860 return cursor;
861 }
862
863 public PreKeyRecord loadPreKey(Account account, int preKeyId) {
864 PreKeyRecord record = null;
865 Cursor cursor = getCursorForPreKey(account, preKeyId);
866 if (cursor.getCount() != 0) {
867 cursor.moveToFirst();
868 try {
869 record = new PreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
870 } catch (IOException e) {
871 throw new AssertionError(e);
872 }
873 }
874 cursor.close();
875 return record;
876 }
877
878 public boolean containsPreKey(Account account, int preKeyId) {
879 Cursor cursor = getCursorForPreKey(account, preKeyId);
880 int count = cursor.getCount();
881 cursor.close();
882 return count != 0;
883 }
884
885 public void storePreKey(Account account, PreKeyRecord record) {
886 SQLiteDatabase db = this.getWritableDatabase();
887 ContentValues values = new ContentValues();
888 values.put(SQLiteAxolotlStore.ID, record.getId());
889 values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(record.serialize(), Base64.DEFAULT));
890 values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
891 db.insert(SQLiteAxolotlStore.PREKEY_TABLENAME, null, values);
892 }
893
894 public void deletePreKey(Account account, int preKeyId) {
895 SQLiteDatabase db = this.getWritableDatabase();
896 String[] args = {account.getUuid(), Integer.toString(preKeyId)};
897 db.delete(SQLiteAxolotlStore.PREKEY_TABLENAME,
898 SQLiteAxolotlStore.ACCOUNT + "=? AND "
899 + SQLiteAxolotlStore.ID + "=?",
900 args);
901 }
902
903 private Cursor getCursorForSignedPreKey(Account account, int signedPreKeyId) {
904 SQLiteDatabase db = this.getReadableDatabase();
905 String[] columns = {SQLiteAxolotlStore.KEY};
906 String[] selectionArgs = {account.getUuid(), Integer.toString(signedPreKeyId)};
907 Cursor cursor = db.query(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
908 columns,
909 SQLiteAxolotlStore.ACCOUNT + "=? AND " + SQLiteAxolotlStore.ID + "=?",
910 selectionArgs,
911 null, null, null);
912
913 return cursor;
914 }
915
916 public SignedPreKeyRecord loadSignedPreKey(Account account, int signedPreKeyId) {
917 SignedPreKeyRecord record = null;
918 Cursor cursor = getCursorForSignedPreKey(account, signedPreKeyId);
919 if (cursor.getCount() != 0) {
920 cursor.moveToFirst();
921 try {
922 record = new SignedPreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
923 } catch (IOException e) {
924 throw new AssertionError(e);
925 }
926 }
927 cursor.close();
928 return record;
929 }
930
931 public List<SignedPreKeyRecord> loadSignedPreKeys(Account account) {
932 List<SignedPreKeyRecord> prekeys = new ArrayList<>();
933 SQLiteDatabase db = this.getReadableDatabase();
934 String[] columns = {SQLiteAxolotlStore.KEY};
935 String[] selectionArgs = {account.getUuid()};
936 Cursor cursor = db.query(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
937 columns,
938 SQLiteAxolotlStore.ACCOUNT + "=?",
939 selectionArgs,
940 null, null, null);
941
942 while (cursor.moveToNext()) {
943 try {
944 prekeys.add(new SignedPreKeyRecord(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT)));
945 } catch (IOException ignored) {
946 }
947 }
948 cursor.close();
949 return prekeys;
950 }
951
952 public boolean containsSignedPreKey(Account account, int signedPreKeyId) {
953 Cursor cursor = getCursorForPreKey(account, signedPreKeyId);
954 int count = cursor.getCount();
955 cursor.close();
956 return count != 0;
957 }
958
959 public void storeSignedPreKey(Account account, SignedPreKeyRecord record) {
960 SQLiteDatabase db = this.getWritableDatabase();
961 ContentValues values = new ContentValues();
962 values.put(SQLiteAxolotlStore.ID, record.getId());
963 values.put(SQLiteAxolotlStore.KEY, Base64.encodeToString(record.serialize(), Base64.DEFAULT));
964 values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
965 db.insert(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME, null, values);
966 }
967
968 public void deleteSignedPreKey(Account account, int signedPreKeyId) {
969 SQLiteDatabase db = this.getWritableDatabase();
970 String[] args = {account.getUuid(), Integer.toString(signedPreKeyId)};
971 db.delete(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
972 SQLiteAxolotlStore.ACCOUNT + "=? AND "
973 + SQLiteAxolotlStore.ID + "=?",
974 args);
975 }
976
977 private Cursor getIdentityKeyCursor(Account account, String name, boolean own) {
978 final SQLiteDatabase db = this.getReadableDatabase();
979 return getIdentityKeyCursor(db, account, name, own);
980 }
981
982 private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String name, boolean own) {
983 return getIdentityKeyCursor(db, account, name, own, null);
984 }
985
986 private Cursor getIdentityKeyCursor(Account account, String fingerprint) {
987 final SQLiteDatabase db = this.getReadableDatabase();
988 return getIdentityKeyCursor(db, account, fingerprint);
989 }
990
991 private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String fingerprint) {
992 return getIdentityKeyCursor(db, account, null, null, fingerprint);
993 }
994
995 private Cursor getIdentityKeyCursor(SQLiteDatabase db, Account account, String name, Boolean own, String fingerprint) {
996 String[] columns = {SQLiteAxolotlStore.TRUSTED,
997 SQLiteAxolotlStore.KEY};
998 ArrayList<String> selectionArgs = new ArrayList<>(4);
999 selectionArgs.add(account.getUuid());
1000 String selectionString = SQLiteAxolotlStore.ACCOUNT + " = ?";
1001 if (name != null) {
1002 selectionArgs.add(name);
1003 selectionString += " AND " + SQLiteAxolotlStore.NAME + " = ?";
1004 }
1005 if (fingerprint != null) {
1006 selectionArgs.add(fingerprint);
1007 selectionString += " AND " + SQLiteAxolotlStore.FINGERPRINT + " = ?";
1008 }
1009 if (own != null) {
1010 selectionArgs.add(own ? "1" : "0");
1011 selectionString += " AND " + SQLiteAxolotlStore.OWN + " = ?";
1012 }
1013 Cursor cursor = db.query(SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1014 columns,
1015 selectionString,
1016 selectionArgs.toArray(new String[selectionArgs.size()]),
1017 null, null, null);
1018
1019 return cursor;
1020 }
1021
1022 public IdentityKeyPair loadOwnIdentityKeyPair(Account account) {
1023 SQLiteDatabase db = getReadableDatabase();
1024 return loadOwnIdentityKeyPair(db, account);
1025 }
1026
1027 private IdentityKeyPair loadOwnIdentityKeyPair(SQLiteDatabase db, Account account) {
1028 String name = account.getJid().toBareJid().toString();
1029 IdentityKeyPair identityKeyPair = null;
1030 Cursor cursor = getIdentityKeyCursor(db, account, name, true);
1031 if (cursor.getCount() != 0) {
1032 cursor.moveToFirst();
1033 try {
1034 identityKeyPair = new IdentityKeyPair(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT));
1035 } catch (InvalidKeyException e) {
1036 Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Encountered invalid IdentityKey in database for account" + account.getJid().toBareJid() + ", address: " + name);
1037 }
1038 }
1039 cursor.close();
1040
1041 return identityKeyPair;
1042 }
1043
1044 public Set<IdentityKey> loadIdentityKeys(Account account, String name) {
1045 return loadIdentityKeys(account, name, null);
1046 }
1047
1048 public Set<IdentityKey> loadIdentityKeys(Account account, String name, XmppAxolotlSession.Trust trust) {
1049 Set<IdentityKey> identityKeys = new HashSet<>();
1050 Cursor cursor = getIdentityKeyCursor(account, name, false);
1051
1052 while (cursor.moveToNext()) {
1053 if (trust != null &&
1054 cursor.getInt(cursor.getColumnIndex(SQLiteAxolotlStore.TRUSTED))
1055 != trust.getCode()) {
1056 continue;
1057 }
1058 try {
1059 identityKeys.add(new IdentityKey(Base64.decode(cursor.getString(cursor.getColumnIndex(SQLiteAxolotlStore.KEY)), Base64.DEFAULT), 0));
1060 } catch (InvalidKeyException e) {
1061 Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + "Encountered invalid IdentityKey in database for account" + account.getJid().toBareJid() + ", address: " + name);
1062 }
1063 }
1064 cursor.close();
1065
1066 return identityKeys;
1067 }
1068
1069 public long numTrustedKeys(Account account, String name) {
1070 SQLiteDatabase db = getReadableDatabase();
1071 String[] args = {
1072 account.getUuid(),
1073 name,
1074 String.valueOf(XmppAxolotlSession.Trust.TRUSTED.getCode()),
1075 String.valueOf(XmppAxolotlSession.Trust.TRUSTED_X509.getCode())
1076 };
1077 return DatabaseUtils.queryNumEntries(db, SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1078 SQLiteAxolotlStore.ACCOUNT + " = ?"
1079 + " AND " + SQLiteAxolotlStore.NAME + " = ?"
1080 + " AND (" + SQLiteAxolotlStore.TRUSTED + " = ? OR " + SQLiteAxolotlStore.TRUSTED + " = ?)",
1081 args
1082 );
1083 }
1084
1085 private void storeIdentityKey(Account account, String name, boolean own, String fingerprint, String base64Serialized) {
1086 storeIdentityKey(account, name, own, fingerprint, base64Serialized, XmppAxolotlSession.Trust.UNDECIDED);
1087 }
1088
1089 private void storeIdentityKey(Account account, String name, boolean own, String fingerprint, String base64Serialized, XmppAxolotlSession.Trust trusted) {
1090 SQLiteDatabase db = this.getWritableDatabase();
1091 ContentValues values = new ContentValues();
1092 values.put(SQLiteAxolotlStore.ACCOUNT, account.getUuid());
1093 values.put(SQLiteAxolotlStore.NAME, name);
1094 values.put(SQLiteAxolotlStore.OWN, own ? 1 : 0);
1095 values.put(SQLiteAxolotlStore.FINGERPRINT, fingerprint);
1096 values.put(SQLiteAxolotlStore.KEY, base64Serialized);
1097 values.put(SQLiteAxolotlStore.TRUSTED, trusted.getCode());
1098 db.insert(SQLiteAxolotlStore.IDENTITIES_TABLENAME, null, values);
1099 }
1100
1101 public XmppAxolotlSession.Trust isIdentityKeyTrusted(Account account, String fingerprint) {
1102 Cursor cursor = getIdentityKeyCursor(account, fingerprint);
1103 XmppAxolotlSession.Trust trust = null;
1104 if (cursor.getCount() > 0) {
1105 cursor.moveToFirst();
1106 int trustValue = cursor.getInt(cursor.getColumnIndex(SQLiteAxolotlStore.TRUSTED));
1107 trust = XmppAxolotlSession.Trust.fromCode(trustValue);
1108 }
1109 cursor.close();
1110 return trust;
1111 }
1112
1113 public boolean setIdentityKeyTrust(Account account, String fingerprint, XmppAxolotlSession.Trust trust) {
1114 SQLiteDatabase db = this.getWritableDatabase();
1115 return setIdentityKeyTrust(db, account, fingerprint, trust);
1116 }
1117
1118 private boolean setIdentityKeyTrust(SQLiteDatabase db, Account account, String fingerprint, XmppAxolotlSession.Trust trust) {
1119 String[] selectionArgs = {
1120 account.getUuid(),
1121 fingerprint
1122 };
1123 ContentValues values = new ContentValues();
1124 values.put(SQLiteAxolotlStore.TRUSTED, trust.getCode());
1125 int rows = db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME, values,
1126 SQLiteAxolotlStore.ACCOUNT + " = ? AND "
1127 + SQLiteAxolotlStore.FINGERPRINT + " = ? ",
1128 selectionArgs);
1129 return rows == 1;
1130 }
1131
1132 public boolean setIdentityKeyCertificate(Account account, String fingerprint, X509Certificate x509Certificate) {
1133 SQLiteDatabase db = this.getWritableDatabase();
1134 String[] selectionArgs = {
1135 account.getUuid(),
1136 fingerprint
1137 };
1138 try {
1139 ContentValues values = new ContentValues();
1140 values.put(SQLiteAxolotlStore.CERTIFICATE, x509Certificate.getEncoded());
1141 return db.update(SQLiteAxolotlStore.IDENTITIES_TABLENAME, values,
1142 SQLiteAxolotlStore.ACCOUNT + " = ? AND "
1143 + SQLiteAxolotlStore.FINGERPRINT + " = ? ",
1144 selectionArgs) == 1;
1145 } catch (CertificateEncodingException e) {
1146 Log.d(Config.LOGTAG, "could not encode certificate");
1147 return false;
1148 }
1149 }
1150
1151 public X509Certificate getIdentityKeyCertifcate(Account account, String fingerprint) {
1152 SQLiteDatabase db = this.getReadableDatabase();
1153 String[] selectionArgs = {
1154 account.getUuid(),
1155 fingerprint
1156 };
1157 String[] colums = {SQLiteAxolotlStore.CERTIFICATE};
1158 String selection = SQLiteAxolotlStore.ACCOUNT + " = ? AND " + SQLiteAxolotlStore.FINGERPRINT + " = ? ";
1159 Cursor cursor = db.query(SQLiteAxolotlStore.IDENTITIES_TABLENAME, colums, selection, selectionArgs, null, null, null);
1160 if (cursor.getCount() < 1) {
1161 return null;
1162 } else {
1163 cursor.moveToFirst();
1164 byte[] certificate = cursor.getBlob(cursor.getColumnIndex(SQLiteAxolotlStore.CERTIFICATE));
1165 cursor.close();
1166 if (certificate == null || certificate.length == 0) {
1167 return null;
1168 }
1169 try {
1170 CertificateFactory certificateFactory = CertificateFactory.getInstance("X.509");
1171 return (X509Certificate) certificateFactory.generateCertificate(new ByteArrayInputStream(certificate));
1172 } catch (CertificateException e) {
1173 Log.d(Config.LOGTAG,"certificate exception "+e.getMessage());
1174 return null;
1175 }
1176 }
1177 }
1178
1179 public void storeIdentityKey(Account account, String name, IdentityKey identityKey) {
1180 storeIdentityKey(account, name, false, identityKey.getFingerprint().replaceAll("\\s", ""), Base64.encodeToString(identityKey.serialize(), Base64.DEFAULT));
1181 }
1182
1183 public void storeOwnIdentityKeyPair(Account account, IdentityKeyPair identityKeyPair) {
1184 storeIdentityKey(account, account.getJid().toBareJid().toString(), true, identityKeyPair.getPublicKey().getFingerprint().replaceAll("\\s", ""), Base64.encodeToString(identityKeyPair.serialize(), Base64.DEFAULT), XmppAxolotlSession.Trust.TRUSTED);
1185 }
1186
1187 public void recreateAxolotlDb() {
1188 recreateAxolotlDb(getWritableDatabase());
1189 }
1190
1191 public void recreateAxolotlDb(SQLiteDatabase db) {
1192 Log.d(Config.LOGTAG, AxolotlService.LOGPREFIX + " : " + ">>> (RE)CREATING AXOLOTL DATABASE <<<");
1193 db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.SESSION_TABLENAME);
1194 db.execSQL(CREATE_SESSIONS_STATEMENT);
1195 db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.PREKEY_TABLENAME);
1196 db.execSQL(CREATE_PREKEYS_STATEMENT);
1197 db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME);
1198 db.execSQL(CREATE_SIGNED_PREKEYS_STATEMENT);
1199 db.execSQL("DROP TABLE IF EXISTS " + SQLiteAxolotlStore.IDENTITIES_TABLENAME);
1200 db.execSQL(CREATE_IDENTITIES_STATEMENT);
1201 }
1202
1203 public void wipeAxolotlDb(Account account) {
1204 String accountName = account.getUuid();
1205 Log.d(Config.LOGTAG, AxolotlService.getLogprefix(account) + ">>> WIPING AXOLOTL DATABASE FOR ACCOUNT " + accountName + " <<<");
1206 SQLiteDatabase db = this.getWritableDatabase();
1207 String[] deleteArgs = {
1208 accountName
1209 };
1210 db.delete(SQLiteAxolotlStore.SESSION_TABLENAME,
1211 SQLiteAxolotlStore.ACCOUNT + " = ?",
1212 deleteArgs);
1213 db.delete(SQLiteAxolotlStore.PREKEY_TABLENAME,
1214 SQLiteAxolotlStore.ACCOUNT + " = ?",
1215 deleteArgs);
1216 db.delete(SQLiteAxolotlStore.SIGNED_PREKEY_TABLENAME,
1217 SQLiteAxolotlStore.ACCOUNT + " = ?",
1218 deleteArgs);
1219 db.delete(SQLiteAxolotlStore.IDENTITIES_TABLENAME,
1220 SQLiteAxolotlStore.ACCOUNT + " = ?",
1221 deleteArgs);
1222 }
1223}