1package eu.siacs.conversations.persistance;
2
3import java.util.ArrayList;
4import java.util.List;
5import java.util.concurrent.CopyOnWriteArrayList;
6
7import eu.siacs.conversations.Config;
8import eu.siacs.conversations.entities.Account;
9import eu.siacs.conversations.entities.Contact;
10import eu.siacs.conversations.entities.Conversation;
11import eu.siacs.conversations.entities.Message;
12import eu.siacs.conversations.entities.Roster;
13import eu.siacs.conversations.xmpp.jid.InvalidJidException;
14import eu.siacs.conversations.xmpp.jid.Jid;
15
16import android.content.Context;
17import android.database.Cursor;
18import android.database.sqlite.SQLiteCantOpenDatabaseException;
19import android.database.sqlite.SQLiteDatabase;
20import android.database.sqlite.SQLiteOpenHelper;
21import android.util.Log;
22
23public class DatabaseBackend extends SQLiteOpenHelper {
24
25 private static DatabaseBackend instance = null;
26
27 private static final String DATABASE_NAME = "history";
28 private static final int DATABASE_VERSION = 14;
29
30 private static String CREATE_CONTATCS_STATEMENT = "create table "
31 + Contact.TABLENAME + "(" + Contact.ACCOUNT + " TEXT, "
32 + Contact.SERVERNAME + " TEXT, " + Contact.SYSTEMNAME + " TEXT,"
33 + Contact.JID + " TEXT," + Contact.KEYS + " TEXT,"
34 + Contact.PHOTOURI + " TEXT," + Contact.OPTIONS + " NUMBER,"
35 + Contact.SYSTEMACCOUNT + " NUMBER, " + Contact.AVATAR + " TEXT, "
36 + Contact.LAST_PRESENCE + " TEXT, " + Contact.LAST_TIME + " NUMBER, "
37 + Contact.GROUPS + " TEXT, FOREIGN KEY(" + Contact.ACCOUNT + ") REFERENCES "
38 + Account.TABLENAME + "(" + Account.UUID
39 + ") ON DELETE CASCADE, UNIQUE(" + Contact.ACCOUNT + ", "
40 + Contact.JID + ") ON CONFLICT REPLACE);";
41
42 private DatabaseBackend(Context context) {
43 super(context, DATABASE_NAME, null, DATABASE_VERSION);
44 }
45
46 @Override
47 public void onCreate(SQLiteDatabase db) {
48 db.execSQL("PRAGMA foreign_keys=ON;");
49 db.execSQL("create table " + Account.TABLENAME + "(" + Account.UUID
50 + " TEXT PRIMARY KEY," + Account.USERNAME + " TEXT,"
51 + Account.SERVER + " TEXT," + Account.PASSWORD + " TEXT,"
52 + Account.ROSTERVERSION + " TEXT," + Account.OPTIONS
53 + " NUMBER, " + Account.AVATAR + " TEXT, " + Account.KEYS
54 + " TEXT)");
55 db.execSQL("create table " + Conversation.TABLENAME + " ("
56 + Conversation.UUID + " TEXT PRIMARY KEY, " + Conversation.NAME
57 + " TEXT, " + Conversation.CONTACT + " TEXT, "
58 + Conversation.ACCOUNT + " TEXT, " + Conversation.CONTACTJID
59 + " TEXT, " + Conversation.CREATED + " NUMBER, "
60 + Conversation.STATUS + " NUMBER, " + Conversation.MODE
61 + " NUMBER, " + Conversation.ATTRIBUTES + " TEXT, FOREIGN KEY("
62 + Conversation.ACCOUNT + ") REFERENCES " + Account.TABLENAME
63 + "(" + Account.UUID + ") ON DELETE CASCADE);");
64 db.execSQL("create table " + Message.TABLENAME + "( " + Message.UUID
65 + " TEXT PRIMARY KEY, " + Message.CONVERSATION + " TEXT, "
66 + Message.TIME_SENT + " NUMBER, " + Message.COUNTERPART
67 + " TEXT, " + Message.TRUE_COUNTERPART + " TEXT,"
68 + Message.BODY + " TEXT, " + Message.ENCRYPTION + " NUMBER, "
69 + Message.STATUS + " NUMBER," + Message.TYPE + " NUMBER, "
70 + Message.RELATIVE_FILE_PATH + " TEXT, "
71 + Message.SERVER_MSG_ID + " TEXT, "
72 + Message.REMOTE_MSG_ID + " TEXT, FOREIGN KEY("
73 + Message.CONVERSATION + ") REFERENCES "
74 + Conversation.TABLENAME + "(" + Conversation.UUID
75 + ") ON DELETE CASCADE);");
76
77 db.execSQL(CREATE_CONTATCS_STATEMENT);
78 }
79
80 @Override
81 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
82 if (oldVersion < 2 && newVersion >= 2) {
83 db.execSQL("update " + Account.TABLENAME + " set "
84 + Account.OPTIONS + " = " + Account.OPTIONS + " | 8");
85 }
86 if (oldVersion < 3 && newVersion >= 3) {
87 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
88 + Message.TYPE + " NUMBER");
89 }
90 if (oldVersion < 5 && newVersion >= 5) {
91 db.execSQL("DROP TABLE " + Contact.TABLENAME);
92 db.execSQL(CREATE_CONTATCS_STATEMENT);
93 db.execSQL("UPDATE " + Account.TABLENAME + " SET "
94 + Account.ROSTERVERSION + " = NULL");
95 }
96 if (oldVersion < 6 && newVersion >= 6) {
97 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
98 + Message.TRUE_COUNTERPART + " TEXT");
99 }
100 if (oldVersion < 7 && newVersion >= 7) {
101 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
102 + Message.REMOTE_MSG_ID + " TEXT");
103 db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
104 + Contact.AVATAR + " TEXT");
105 db.execSQL("ALTER TABLE " + Account.TABLENAME + " ADD COLUMN "
106 + Account.AVATAR + " TEXT");
107 }
108 if (oldVersion < 8 && newVersion >= 8) {
109 db.execSQL("ALTER TABLE " + Conversation.TABLENAME + " ADD COLUMN "
110 + Conversation.ATTRIBUTES + " TEXT");
111 }
112 if (oldVersion < 9 && newVersion >= 9) {
113 db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
114 + Contact.LAST_TIME + " NUMBER");
115 db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
116 + Contact.LAST_PRESENCE + " TEXT");
117 }
118 if (oldVersion < 10 && newVersion >= 10) {
119 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
120 + Message.RELATIVE_FILE_PATH + " TEXT");
121 }
122 if (oldVersion < 11 && newVersion >= 11) {
123 db.execSQL("ALTER TABLE " + Contact.TABLENAME + " ADD COLUMN "
124 + Contact.GROUPS + " TEXT");
125 db.execSQL("delete from "+Contact.TABLENAME);
126 db.execSQL("update "+Account.TABLENAME+" set "+Account.ROSTERVERSION+" = NULL");
127 }
128 if (oldVersion < 12 && newVersion >= 12) {
129 db.execSQL("ALTER TABLE " + Message.TABLENAME + " ADD COLUMN "
130 + Message.SERVER_MSG_ID + " TEXT");
131 }
132 if (oldVersion < 13 && newVersion >= 13) {
133 db.execSQL("delete from "+Contact.TABLENAME);
134 db.execSQL("update "+Account.TABLENAME+" set "+Account.ROSTERVERSION+" = NULL");
135 }
136 if (oldVersion < 14 && newVersion >= 14) {
137 // migrate db to new, canonicalized JID domainpart representation
138
139 // Conversation table
140 Cursor cursor = db.rawQuery("select * from " + Conversation.TABLENAME, new String[0]);
141 while(cursor.moveToNext()) {
142 String newJid;
143 try {
144 newJid = Jid.fromString(
145 cursor.getString(cursor.getColumnIndex(Conversation.CONTACTJID))
146 ).toString();
147 } catch (InvalidJidException ignored) {
148 Log.e(Config.LOGTAG, "Failed to migrate Conversation CONTACTJID "
149 +cursor.getString(cursor.getColumnIndex(Conversation.CONTACTJID))
150 +": " + ignored +". Skipping...");
151 continue;
152 }
153
154 String updateArgs[] = {
155 newJid,
156 cursor.getString(cursor.getColumnIndex(Conversation.UUID)),
157 };
158 db.execSQL("update " + Conversation.TABLENAME
159 + " set " + Conversation.CONTACTJID + " = ? "
160 + " where " + Conversation.UUID + " = ?", updateArgs);
161 }
162 cursor.close();
163
164 // Contact table
165 cursor = db.rawQuery("select * from " + Contact.TABLENAME, new String[0]);
166 while(cursor.moveToNext()) {
167 String newJid;
168 try {
169 newJid = Jid.fromString(
170 cursor.getString(cursor.getColumnIndex(Contact.JID))
171 ).toString();
172 } catch (InvalidJidException ignored) {
173 Log.e(Config.LOGTAG, "Failed to migrate Contact JID "
174 +cursor.getString(cursor.getColumnIndex(Contact.JID))
175 +": " + ignored +". Skipping...");
176 continue;
177 }
178
179 String updateArgs[] = {
180 newJid,
181 cursor.getString(cursor.getColumnIndex(Contact.ACCOUNT)),
182 cursor.getString(cursor.getColumnIndex(Contact.JID)),
183 };
184 db.execSQL("update " + Contact.TABLENAME
185 + " set " + Contact.JID + " = ? "
186 + " where " + Contact.ACCOUNT + " = ? "
187 + " AND " + Contact.JID + " = ?", updateArgs);
188 }
189 cursor.close();
190
191 // Account table
192 cursor = db.rawQuery("select * from " + Account.TABLENAME, new String[0]);
193 while(cursor.moveToNext()) {
194 String newServer;
195 try {
196 newServer = Jid.fromParts(
197 cursor.getString(cursor.getColumnIndex(Account.USERNAME)),
198 cursor.getString(cursor.getColumnIndex(Account.SERVER)),
199 "mobile"
200 ).getDomainpart();
201 } catch (InvalidJidException ignored) {
202 Log.e(Config.LOGTAG, "Failed to migrate Account SERVER "
203 +cursor.getString(cursor.getColumnIndex(Account.SERVER))
204 +": " + ignored +". Skipping...");
205 continue;
206 }
207
208 String updateArgs[] = {
209 newServer,
210 cursor.getString(cursor.getColumnIndex(Account.UUID)),
211 };
212 db.execSQL("update " + Account.TABLENAME
213 + " set " + Account.SERVER + " = ? "
214 + " where " + Account.UUID + " = ?", updateArgs);
215 }
216 cursor.close();
217 }
218 }
219
220 public static synchronized DatabaseBackend getInstance(Context context) {
221 if (instance == null) {
222 instance = new DatabaseBackend(context);
223 }
224 return instance;
225 }
226
227 public void createConversation(Conversation conversation) {
228 SQLiteDatabase db = this.getWritableDatabase();
229 db.insert(Conversation.TABLENAME, null, conversation.getContentValues());
230 }
231
232 public void createMessage(Message message) {
233 SQLiteDatabase db = this.getWritableDatabase();
234 db.insert(Message.TABLENAME, null, message.getContentValues());
235 }
236
237 public void createAccount(Account account) {
238 SQLiteDatabase db = this.getWritableDatabase();
239 db.insert(Account.TABLENAME, null, account.getContentValues());
240 }
241
242 public void createContact(Contact contact) {
243 SQLiteDatabase db = this.getWritableDatabase();
244 db.insert(Contact.TABLENAME, null, contact.getContentValues());
245 }
246
247 public int getConversationCount() {
248 SQLiteDatabase db = this.getReadableDatabase();
249 Cursor cursor = db.rawQuery("select count(uuid) as count from "
250 + Conversation.TABLENAME + " where " + Conversation.STATUS
251 + "=" + Conversation.STATUS_AVAILABLE, null);
252 cursor.moveToFirst();
253 int count = cursor.getInt(0);
254 cursor.close();
255 return count;
256 }
257
258 public CopyOnWriteArrayList<Conversation> getConversations(int status) {
259 CopyOnWriteArrayList<Conversation> list = new CopyOnWriteArrayList<>();
260 SQLiteDatabase db = this.getReadableDatabase();
261 String[] selectionArgs = { Integer.toString(status) };
262 Cursor cursor = db.rawQuery("select * from " + Conversation.TABLENAME
263 + " where " + Conversation.STATUS + " = ? order by "
264 + Conversation.CREATED + " desc", selectionArgs);
265 while (cursor.moveToNext()) {
266 list.add(Conversation.fromCursor(cursor));
267 }
268 cursor.close();
269 return list;
270 }
271
272 public ArrayList<Message> getMessages(Conversation conversations, int limit) {
273 return getMessages(conversations, limit, -1);
274 }
275
276 public ArrayList<Message> getMessages(Conversation conversation, int limit,
277 long timestamp) {
278 ArrayList<Message> list = new ArrayList<>();
279 SQLiteDatabase db = this.getReadableDatabase();
280 Cursor cursor;
281 if (timestamp == -1) {
282 String[] selectionArgs = { conversation.getUuid() };
283 cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
284 + "=?", selectionArgs, null, null, Message.TIME_SENT
285 + " DESC", String.valueOf(limit));
286 } else {
287 String[] selectionArgs = { conversation.getUuid(),
288 Long.toString(timestamp) };
289 cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
290 + "=? and " + Message.TIME_SENT + "<?", selectionArgs,
291 null, null, Message.TIME_SENT + " DESC",
292 String.valueOf(limit));
293 }
294 if (cursor.getCount() > 0) {
295 cursor.moveToLast();
296 do {
297 Message message = Message.fromCursor(cursor);
298 message.setConversation(conversation);
299 list.add(message);
300 } while (cursor.moveToPrevious());
301 }
302 cursor.close();
303 return list;
304 }
305
306 public Conversation findConversation(final Account account, final Jid contactJid) {
307 SQLiteDatabase db = this.getReadableDatabase();
308 String[] selectionArgs = { account.getUuid(),
309 contactJid.toBareJid().toString() + "/%",
310 contactJid.toBareJid().toString()
311 };
312 Cursor cursor = db.query(Conversation.TABLENAME, null,
313 Conversation.ACCOUNT + "=? AND (" + Conversation.CONTACTJID
314 + " like ? OR "+Conversation.CONTACTJID+"=?)", selectionArgs, null, null, null);
315 if (cursor.getCount() == 0)
316 return null;
317 cursor.moveToFirst();
318 Conversation conversation = Conversation.fromCursor(cursor);
319 cursor.close();
320 return conversation;
321 }
322
323 public void updateConversation(final Conversation conversation) {
324 final SQLiteDatabase db = this.getWritableDatabase();
325 final String[] args = { conversation.getUuid() };
326 db.update(Conversation.TABLENAME, conversation.getContentValues(),
327 Conversation.UUID + "=?", args);
328 }
329
330 public List<Account> getAccounts() {
331 List<Account> list = new ArrayList<>();
332 SQLiteDatabase db = this.getReadableDatabase();
333 Cursor cursor = db.query(Account.TABLENAME, null, null, null, null,
334 null, null);
335 while (cursor.moveToNext()) {
336 list.add(Account.fromCursor(cursor));
337 }
338 cursor.close();
339 return list;
340 }
341
342 public void updateAccount(Account account) {
343 SQLiteDatabase db = this.getWritableDatabase();
344 String[] args = { account.getUuid() };
345 db.update(Account.TABLENAME, account.getContentValues(), Account.UUID
346 + "=?", args);
347 }
348
349 public void deleteAccount(Account account) {
350 SQLiteDatabase db = this.getWritableDatabase();
351 String[] args = { account.getUuid() };
352 db.delete(Account.TABLENAME, Account.UUID + "=?", args);
353 }
354
355 public boolean hasEnabledAccounts() {
356 SQLiteDatabase db = this.getReadableDatabase();
357 Cursor cursor = db.rawQuery("select count(" + Account.UUID + ") from "
358 + Account.TABLENAME + " where not options & (1 <<1)", null);
359 try {
360 cursor.moveToFirst();
361 int count = cursor.getInt(0);
362 cursor.close();
363 return (count > 0);
364 } catch (SQLiteCantOpenDatabaseException e) {
365 return true; // better safe than sorry
366 } catch (RuntimeException e) {
367 return true; // better safe than sorry
368 }
369 }
370
371 @Override
372 public SQLiteDatabase getWritableDatabase() {
373 SQLiteDatabase db = super.getWritableDatabase();
374 db.execSQL("PRAGMA foreign_keys=ON;");
375 return db;
376 }
377
378 public void updateMessage(Message message) {
379 SQLiteDatabase db = this.getWritableDatabase();
380 String[] args = { message.getUuid() };
381 db.update(Message.TABLENAME, message.getContentValues(), Message.UUID
382 + "=?", args);
383 }
384
385 public void readRoster(Roster roster) {
386 SQLiteDatabase db = this.getReadableDatabase();
387 Cursor cursor;
388 String args[] = { roster.getAccount().getUuid() };
389 cursor = db.query(Contact.TABLENAME, null, Contact.ACCOUNT + "=?", args, null, null, null);
390 while (cursor.moveToNext()) {
391 roster.initContact(Contact.fromCursor(cursor));
392 }
393 cursor.close();
394 }
395
396 public void writeRoster(final Roster roster) {
397 final Account account = roster.getAccount();
398 final SQLiteDatabase db = this.getWritableDatabase();
399 for (Contact contact : roster.getContacts()) {
400 if (contact.getOption(Contact.Options.IN_ROSTER)) {
401 db.insert(Contact.TABLENAME, null, contact.getContentValues());
402 } else {
403 String where = Contact.ACCOUNT + "=? AND " + Contact.JID + "=?";
404 String[] whereArgs = { account.getUuid(), contact.getJid().toString() };
405 db.delete(Contact.TABLENAME, where, whereArgs);
406 }
407 }
408 account.setRosterVersion(roster.getVersion());
409 updateAccount(account);
410 }
411
412 public void deleteMessage(Message message) {
413 SQLiteDatabase db = this.getWritableDatabase();
414 String[] args = { message.getUuid() };
415 db.delete(Message.TABLENAME, Message.UUID + "=?", args);
416 }
417
418 public void deleteMessagesInConversation(Conversation conversation) {
419 SQLiteDatabase db = this.getWritableDatabase();
420 String[] args = { conversation.getUuid() };
421 db.delete(Message.TABLENAME, Message.CONVERSATION + "=?", args);
422 }
423
424 public Conversation findConversationByUuid(String conversationUuid) {
425 SQLiteDatabase db = this.getReadableDatabase();
426 String[] selectionArgs = { conversationUuid };
427 Cursor cursor = db.query(Conversation.TABLENAME, null,
428 Conversation.UUID + "=?", selectionArgs, null, null, null);
429 if (cursor.getCount() == 0) {
430 return null;
431 }
432 cursor.moveToFirst();
433 Conversation conversation = Conversation.fromCursor(cursor);
434 cursor.close();
435 return conversation;
436 }
437
438 public Message findMessageByUuid(String messageUuid) {
439 SQLiteDatabase db = this.getReadableDatabase();
440 String[] selectionArgs = { messageUuid };
441 Cursor cursor = db.query(Message.TABLENAME, null, Message.UUID + "=?",
442 selectionArgs, null, null, null);
443 if (cursor.getCount() == 0) {
444 return null;
445 }
446 cursor.moveToFirst();
447 Message message = Message.fromCursor(cursor);
448 cursor.close();
449 return message;
450 }
451
452 public Account findAccountByUuid(String accountUuid) {
453 SQLiteDatabase db = this.getReadableDatabase();
454 String[] selectionArgs = { accountUuid };
455 Cursor cursor = db.query(Account.TABLENAME, null, Account.UUID + "=?",
456 selectionArgs, null, null, null);
457 if (cursor.getCount() == 0) {
458 return null;
459 }
460 cursor.moveToFirst();
461 Account account = Account.fromCursor(cursor);
462 cursor.close();
463 return account;
464 }
465
466 public List<Message> getImageMessages(Conversation conversation) {
467 ArrayList<Message> list = new ArrayList<>();
468 SQLiteDatabase db = this.getReadableDatabase();
469 Cursor cursor;
470 String[] selectionArgs = { conversation.getUuid(), String.valueOf(Message.TYPE_IMAGE) };
471 cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
472 + "=? AND "+Message.TYPE+"=?", selectionArgs, null, null,null);
473 if (cursor.getCount() > 0) {
474 cursor.moveToLast();
475 do {
476 Message message = Message.fromCursor(cursor);
477 message.setConversation(conversation);
478 list.add(message);
479 } while (cursor.moveToPrevious());
480 }
481 cursor.close();
482 return list;
483 }
484}