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)");
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.OPENPGPKEY
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 Log.d("gultsch", "backend trying to delete account with uuid:"
180 + account.getUuid());
181 db.delete(Account.TABLENAME, Account.UUID + "=?", args);
182 }
183
184 @Override
185 public SQLiteDatabase getWritableDatabase() {
186 SQLiteDatabase db = super.getWritableDatabase();
187 db.execSQL("PRAGMA foreign_keys=ON;");
188 return db;
189 }
190
191 public void updateMessage(Message message) {
192 SQLiteDatabase db = this.getWritableDatabase();
193 String[] args = { message.getUuid() };
194 db.update(Message.TABLENAME, message.getContentValues(), Message.UUID
195 + "=?", args);
196 }
197
198 public void updateContact(Contact contact) {
199 SQLiteDatabase db = this.getWritableDatabase();
200 String[] args = { contact.getUuid() };
201 db.update(Contact.TABLENAME, contact.getContentValues(), Contact.UUID
202 + "=?", args);
203 }
204
205 public void clearPresences(Account account) {
206 SQLiteDatabase db = this.getWritableDatabase();
207 String[] args = { account.getUuid() };
208 ContentValues values = new ContentValues();
209 values.put(Contact.PRESENCES,"[]");
210 db.update(Contact.TABLENAME, values, Contact.ACCOUNT
211 + "=?", args);
212 }
213
214 public void mergeContacts(List<Contact> contacts) {
215 SQLiteDatabase db = this.getWritableDatabase();
216 for (int i = 0; i < contacts.size(); i++) {
217 Contact contact = contacts.get(i);
218 String[] columns = {Contact.UUID, Contact.PRESENCES};
219 String[] args = {contact.getAccount().getUuid(), contact.getJid()};
220 Cursor cursor = db.query(Contact.TABLENAME, columns,Contact.ACCOUNT+"=? AND "+Contact.JID+"=?", args, null, null, null);
221 if (cursor.getCount()>=1) {
222 cursor.moveToFirst();
223 contact.setUuid(cursor.getString(0));
224 contact.setPresences(Presences.fromJsonString(cursor.getString(1)));
225 updateContact(contact);
226 } else {
227 contact.setUuid(UUID.randomUUID().toString());
228 createContact(contact);
229 }
230 }
231 }
232
233 public List<Contact> getContacts(Account account) {
234 List<Contact> list = new ArrayList<Contact>();
235 SQLiteDatabase db = this.getReadableDatabase();
236 Cursor cursor;
237 if (account==null) {
238 cursor = db.query(Contact.TABLENAME, null, null, null, null,
239 null, null);
240 } else {
241 String args[] = {account.getUuid()};
242 cursor = db.query(Contact.TABLENAME, null, Contact.ACCOUNT+"=?", args, null,
243 null, null);
244 }
245 while (cursor.moveToNext()) {
246 list.add(Contact.fromCursor(cursor));
247 }
248 return list;
249 }
250
251 public Contact findContact(Account account, String jid) {
252 SQLiteDatabase db = this.getReadableDatabase();
253 String[] selectionArgs = { account.getUuid(), jid };
254 Cursor cursor = db.query(Contact.TABLENAME, null,
255 Contact.ACCOUNT + "=? AND " + Contact.JID + "=?",
256 selectionArgs, null, null, null);
257 if (cursor.getCount() == 0)
258 return null;
259 cursor.moveToFirst();
260 return Contact.fromCursor(cursor);
261 }
262}