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