1package eu.siacs.conversations.persistance;
2
3import java.util.ArrayList;
4import java.util.List;
5import java.util.UUID;
6import java.util.concurrent.CopyOnWriteArrayList;
7
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.Presences;
13import android.content.ContentValues;
14import android.content.Context;
15import android.database.Cursor;
16import android.database.sqlite.SQLiteDatabase;
17import android.database.sqlite.SQLiteOpenHelper;
18import android.os.Bundle;
19import android.util.Log;
20
21public class DatabaseBackend extends SQLiteOpenHelper {
22
23 private static DatabaseBackend instance = null;
24
25 private static final String DATABASE_NAME = "history";
26 private static final int DATABASE_VERSION = 1;
27
28 public DatabaseBackend(Context context) {
29 super(context, DATABASE_NAME, null, DATABASE_VERSION);
30 }
31
32 @Override
33 public void onCreate(SQLiteDatabase db) {
34 db.execSQL("PRAGMA foreign_keys=ON;");
35 db.execSQL("create table " + Account.TABLENAME + "(" + Account.UUID
36 + " TEXT PRIMARY KEY," + Account.USERNAME + " TEXT,"
37 + Account.SERVER + " TEXT," + Account.PASSWORD + " TEXT,"
38 + Account.ROSTERVERSION + " TEXT," + Account.OPTIONS
39 + " NUMBER, "+Account.KEYS+" TEXT)");
40 db.execSQL("create table " + Conversation.TABLENAME + " ("
41 + Conversation.UUID + " TEXT PRIMARY KEY, " + Conversation.NAME
42 + " TEXT, " + Conversation.CONTACT + " TEXT, "
43 + Conversation.ACCOUNT + " TEXT, " + Conversation.CONTACTJID
44 + " TEXT, " + Conversation.CREATED + " NUMBER, "
45 + Conversation.STATUS + " NUMBER," + Conversation.MODE
46 + " NUMBER," + "FOREIGN KEY(" + Conversation.ACCOUNT
47 + ") REFERENCES " + Account.TABLENAME + "(" + Account.UUID
48 + ") ON DELETE CASCADE);");
49 db.execSQL("create table " + Message.TABLENAME + "( " + Message.UUID
50 + " TEXT PRIMARY KEY, " + Message.CONVERSATION + " TEXT, "
51 + Message.TIME_SENT + " NUMBER, " + Message.COUNTERPART
52 + " TEXT, " + Message.BODY + " TEXT, " + Message.ENCRYPTION
53 + " NUMBER, " + Message.STATUS + " NUMBER," + "FOREIGN KEY("
54 + Message.CONVERSATION + ") REFERENCES "
55 + Conversation.TABLENAME + "(" + Conversation.UUID
56 + ") ON DELETE CASCADE);");
57 db.execSQL("create table " + Contact.TABLENAME + "(" + Contact.UUID
58 + " TEXT PRIMARY KEY, " + Contact.ACCOUNT + " TEXT, "
59 + Contact.DISPLAYNAME + " TEXT," + Contact.JID + " TEXT,"
60 + Contact.PRESENCES + " TEXT, " + Contact.KEYS
61 + " TEXT," + Contact.PHOTOURI + " TEXT," + Contact.SUBSCRIPTION
62 + " NUMBER," + Contact.SYSTEMACCOUNT + " NUMBER, "
63 + "FOREIGN KEY(" + Contact.ACCOUNT + ") REFERENCES "
64 + Account.TABLENAME + "(" + Account.UUID
65 + ") ON DELETE CASCADE);");
66 }
67
68 @Override
69 public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
70 // TODO Auto-generated method stub
71
72 }
73
74 public static synchronized DatabaseBackend getInstance(Context context) {
75 if (instance == null) {
76 instance = new DatabaseBackend(context);
77 }
78 return instance;
79 }
80
81 public void createConversation(Conversation conversation) {
82 SQLiteDatabase db = this.getWritableDatabase();
83 db.insert(Conversation.TABLENAME, null, conversation.getContentValues());
84 }
85
86 public void createMessage(Message message) {
87 SQLiteDatabase db = this.getWritableDatabase();
88 db.insert(Message.TABLENAME, null, message.getContentValues());
89 }
90
91 public void createAccount(Account account) {
92 SQLiteDatabase db = this.getWritableDatabase();
93 db.insert(Account.TABLENAME, null, account.getContentValues());
94 }
95
96 public void createContact(Contact contact) {
97 SQLiteDatabase db = this.getWritableDatabase();
98 db.insert(Contact.TABLENAME, null, contact.getContentValues());
99 }
100
101 public int getConversationCount() {
102 SQLiteDatabase db = this.getReadableDatabase();
103 Cursor cursor = db.rawQuery("select count(uuid) as count from "
104 + Conversation.TABLENAME + " where " + Conversation.STATUS
105 + "=" + Conversation.STATUS_AVAILABLE, null);
106 cursor.moveToFirst();
107 return cursor.getInt(0);
108 }
109
110 public List<Conversation> getConversations(int status) {
111 List<Conversation> list = new ArrayList<Conversation>();
112 SQLiteDatabase db = this.getReadableDatabase();
113 String[] selectionArgs = { "" + status };
114 Cursor cursor = db.rawQuery("select * from " + Conversation.TABLENAME
115 + " where " + Conversation.STATUS + " = ? order by "
116 + Conversation.CREATED + " desc", selectionArgs);
117 while (cursor.moveToNext()) {
118 list.add(Conversation.fromCursor(cursor));
119 }
120 return list;
121 }
122
123 public List<Message> getMessages(Conversation conversation, int limit) {
124 List<Message> list = new CopyOnWriteArrayList<Message>();
125 SQLiteDatabase db = this.getReadableDatabase();
126 String[] selectionArgs = { conversation.getUuid() };
127 Cursor cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
128 + "=?", selectionArgs, null, null, Message.TIME_SENT + " DESC",
129 String.valueOf(limit));
130 if (cursor.getCount() > 0) {
131 cursor.moveToLast();
132 do {
133 list.add(Message.fromCursor(cursor));
134 } while (cursor.moveToPrevious());
135 }
136 return list;
137 }
138
139 public Conversation findConversation(Account account, String contactJid) {
140 SQLiteDatabase db = this.getReadableDatabase();
141 String[] selectionArgs = { account.getUuid(), contactJid+"%" };
142 Cursor cursor = db.query(Conversation.TABLENAME, null,
143 Conversation.ACCOUNT + "=? AND " + Conversation.CONTACTJID + " like ?",
144 selectionArgs, null, null, null);
145 if (cursor.getCount() == 0)
146 return null;
147 cursor.moveToFirst();
148 return Conversation.fromCursor(cursor);
149 }
150
151 public void updateConversation(Conversation conversation) {
152 SQLiteDatabase db = this.getWritableDatabase();
153 String[] args = { conversation.getUuid() };
154 db.update(Conversation.TABLENAME, conversation.getContentValues(),
155 Conversation.UUID + "=?", args);
156 }
157
158 public List<Account> getAccounts() {
159 List<Account> list = new ArrayList<Account>();
160 SQLiteDatabase db = this.getReadableDatabase();
161 Cursor cursor = db.query(Account.TABLENAME, null, null, null, null,
162 null, null);
163 Log.d("gultsch", "found " + cursor.getCount() + " accounts");
164 while (cursor.moveToNext()) {
165 list.add(Account.fromCursor(cursor));
166 }
167 return list;
168 }
169
170 public void updateAccount(Account account) {
171 SQLiteDatabase db = this.getWritableDatabase();
172 String[] args = { account.getUuid() };
173 db.update(Account.TABLENAME, account.getContentValues(), Account.UUID
174 + "=?", args);
175 }
176
177 public void deleteAccount(Account account) {
178 SQLiteDatabase db = this.getWritableDatabase();
179 String[] args = { account.getUuid() };
180 db.delete(Account.TABLENAME, Account.UUID + "=?", args);
181 }
182
183 @Override
184 public SQLiteDatabase getWritableDatabase() {
185 SQLiteDatabase db = super.getWritableDatabase();
186 db.execSQL("PRAGMA foreign_keys=ON;");
187 return db;
188 }
189
190 public void updateMessage(Message message) {
191 SQLiteDatabase db = this.getWritableDatabase();
192 String[] args = { message.getUuid() };
193 db.update(Message.TABLENAME, message.getContentValues(), Message.UUID
194 + "=?", args);
195 }
196
197 public void updateContact(Contact contact) {
198 SQLiteDatabase db = this.getWritableDatabase();
199 String[] args = { contact.getUuid() };
200 db.update(Contact.TABLENAME, contact.getContentValues(), Contact.UUID
201 + "=?", args);
202 }
203
204 public void clearPresences(Account account) {
205 SQLiteDatabase db = this.getWritableDatabase();
206 String[] args = { account.getUuid() };
207 ContentValues values = new ContentValues();
208 values.put(Contact.PRESENCES,"[]");
209 db.update(Contact.TABLENAME, values, Contact.ACCOUNT
210 + "=?", args);
211 }
212
213 public void mergeContacts(List<Contact> contacts) {
214 SQLiteDatabase db = this.getWritableDatabase();
215 for (int i = 0; i < contacts.size(); i++) {
216 Contact contact = contacts.get(i);
217 String[] columns = {Contact.UUID, Contact.PRESENCES};
218 String[] args = {contact.getAccount().getUuid(), contact.getJid()};
219 Cursor cursor = db.query(Contact.TABLENAME, columns,Contact.ACCOUNT+"=? AND "+Contact.JID+"=?", args, null, null, null);
220 if (cursor.getCount()>=1) {
221 cursor.moveToFirst();
222 contact.setUuid(cursor.getString(0));
223 contact.setPresences(Presences.fromJsonString(cursor.getString(1)));
224 updateContact(contact);
225 } else {
226 contact.setUuid(UUID.randomUUID().toString());
227 createContact(contact);
228 }
229 }
230 }
231
232 public List<Contact> getContactsByAccount(Account account) {
233 List<Contact> list = new ArrayList<Contact>();
234 SQLiteDatabase db = this.getReadableDatabase();
235 Cursor cursor;
236 if (account==null) {
237 cursor = db.query(Contact.TABLENAME, null, null, null, null,
238 null, null);
239 } else {
240 String args[] = {account.getUuid()};
241 cursor = db.query(Contact.TABLENAME, null, Contact.ACCOUNT+"=?", args, null,
242 null, null);
243 }
244 while (cursor.moveToNext()) {
245 list.add(Contact.fromCursor(cursor));
246 }
247 return list;
248 }
249
250 public List<Contact> getContacts(String where) {
251 List<Contact> list = new ArrayList<Contact>();
252 SQLiteDatabase db = this.getReadableDatabase();
253 Cursor cursor = db.query(Contact.TABLENAME, null, where, null, null, null, null);
254 while (cursor.moveToNext()) {
255 list.add(Contact.fromCursor(cursor));
256 }
257 return list;
258 }
259
260 public Contact findContact(Account account, String jid) {
261 SQLiteDatabase db = this.getReadableDatabase();
262 String[] selectionArgs = { account.getUuid(), jid };
263 Cursor cursor = db.query(Contact.TABLENAME, null,
264 Contact.ACCOUNT + "=? AND " + Contact.JID + "=?",
265 selectionArgs, null, null, null);
266 if (cursor.getCount() == 0)
267 return null;
268 cursor.moveToFirst();
269 return Contact.fromCursor(cursor);
270 }
271
272 public void deleteMessage(Message message) {
273 SQLiteDatabase db = this.getWritableDatabase();
274 String[] args = { message.getUuid() };
275 db.delete(Message.TABLENAME, Message.UUID + "=?", args);
276 }
277
278 public void deleteContact(Contact contact) {
279 SQLiteDatabase db = this.getWritableDatabase();
280 String[] args = { contact.getUuid() };
281 db.delete(Contact.TABLENAME, Contact.UUID + "=?", args);
282 }
283
284 public Contact getContact(String uuid) {
285 SQLiteDatabase db = this.getWritableDatabase();
286 String[] args = { uuid };
287 Cursor cursor = db.query(Contact.TABLENAME, null, Contact.UUID + "=?", args, null, null, null);
288 if (cursor.getCount() == 0)
289 return null;
290 cursor.moveToFirst();
291 return Contact.fromCursor(cursor);
292 }
293
294
295}