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