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 android.content.ContentValues;
12import android.content.Context;
13import android.database.Cursor;
14import android.database.sqlite.SQLiteDatabase;
15import android.database.sqlite.SQLiteOpenHelper;
16import android.os.Bundle;
17import android.util.Log;
18
19public class DatabaseBackend extends SQLiteOpenHelper {
20
21 private static DatabaseBackend instance = null;
22
23 private static final String DATABASE_NAME = "history";
24 private static final int DATABASE_VERSION = 1;
25
26 public DatabaseBackend(Context context) {
27 super(context, DATABASE_NAME, null, DATABASE_VERSION);
28 }
29
30 @Override
31 public void onCreate(SQLiteDatabase db) {
32 db.execSQL("PRAGMA foreign_keys=ON;");
33 db.execSQL("create table " + Account.TABLENAME + "(" + Account.UUID
34 + " TEXT PRIMARY KEY," + Account.USERNAME + " TEXT,"
35 + Account.SERVER + " TEXT," + Account.PASSWORD + " TEXT,"
36 + Account.ROSTERVERSION + " TEXT," + Account.OPTIONS
37 + " NUMBER)");
38 db.execSQL("create table " + Conversation.TABLENAME + " ("
39 + Conversation.UUID + " TEXT PRIMARY KEY, " + Conversation.NAME
40 + " TEXT, " + Conversation.PHOTO_URI + " TEXT, "
41 + Conversation.ACCOUNT + " TEXT, " + Conversation.CONTACT
42 + " TEXT, " + Conversation.CREATED + " NUMBER, "
43 + Conversation.STATUS + " NUMBER," + Conversation.MODE
44 + " NUMBER," + "FOREIGN KEY(" + Conversation.ACCOUNT
45 + ") REFERENCES " + Account.TABLENAME + "(" + Account.UUID
46 + ") ON DELETE CASCADE);");
47 db.execSQL("create table " + Message.TABLENAME + "( " + Message.UUID
48 + " TEXT PRIMARY KEY, " + Message.CONVERSATION + " TEXT, "
49 + Message.TIME_SENT + " NUMBER, " + Message.COUNTERPART
50 + " TEXT, " + Message.BODY + " TEXT, " + Message.ENCRYPTION
51 + " NUMBER, " + Message.STATUS + " NUMBER," + "FOREIGN KEY("
52 + Message.CONVERSATION + ") REFERENCES "
53 + Conversation.TABLENAME + "(" + Conversation.UUID
54 + ") ON DELETE CASCADE);");
55 db.execSQL("create table " + Contact.TABLENAME + "(" + Contact.UUID
56 + " TEXT PRIMARY KEY, " + Contact.ACCOUNT + " TEXT, "
57 + Contact.DISPLAYNAME + " TEXT," + Contact.JID + " TEXT,"
58 + Contact.PRESENCES + " TEXT, " + Contact.OPENPGPKEY
59 + " TEXT," + Contact.PHOTOURI + " TEXT," + Contact.SUBSCRIPTION
60 + " TEXT," + Contact.SYSTEMACCOUNT + " NUMBER, "
61 + "FOREIGN KEY(" + Contact.ACCOUNT + ") REFERENCES "
62 + Account.TABLENAME + "(" + Account.UUID
63 + ") ON DELETE CASCADE);");
64 }
65
66 @Override
67 public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {
68 // TODO Auto-generated method stub
69
70 }
71
72 public static synchronized DatabaseBackend getInstance(Context context) {
73 if (instance == null) {
74 instance = new DatabaseBackend(context);
75 }
76 return instance;
77 }
78
79 public void createConversation(Conversation conversation) {
80 SQLiteDatabase db = this.getWritableDatabase();
81 db.insert(Conversation.TABLENAME, null, conversation.getContentValues());
82 }
83
84 public void createMessage(Message message) {
85 SQLiteDatabase db = this.getWritableDatabase();
86 db.insert(Message.TABLENAME, null, message.getContentValues());
87 }
88
89 public void createAccount(Account account) {
90 SQLiteDatabase db = this.getWritableDatabase();
91 db.insert(Account.TABLENAME, null, account.getContentValues());
92 }
93
94 public void createContact(Contact contact) {
95 SQLiteDatabase db = this.getWritableDatabase();
96 db.insert(Contact.TABLENAME, null, contact.getContentValues());
97 }
98
99 public int getConversationCount() {
100 SQLiteDatabase db = this.getReadableDatabase();
101 Cursor cursor = db.rawQuery("select count(uuid) as count from "
102 + Conversation.TABLENAME + " where " + Conversation.STATUS
103 + "=" + Conversation.STATUS_AVAILABLE, null);
104 cursor.moveToFirst();
105 return cursor.getInt(0);
106 }
107
108 public List<Conversation> getConversations(int status) {
109 List<Conversation> list = new ArrayList<Conversation>();
110 SQLiteDatabase db = this.getReadableDatabase();
111 String[] selectionArgs = { "" + status };
112 Cursor cursor = db.rawQuery("select * from " + Conversation.TABLENAME
113 + " where " + Conversation.STATUS + " = ? order by "
114 + Conversation.CREATED + " desc", selectionArgs);
115 while (cursor.moveToNext()) {
116 list.add(Conversation.fromCursor(cursor));
117 }
118 return list;
119 }
120
121 public List<Message> getMessages(Conversation conversation, int limit) {
122 List<Message> list = new ArrayList<Message>();
123 SQLiteDatabase db = this.getReadableDatabase();
124 String[] selectionArgs = { conversation.getUuid() };
125 Cursor cursor = db.query(Message.TABLENAME, null, Message.CONVERSATION
126 + "=?", selectionArgs, null, null, Message.TIME_SENT + " DESC",
127 String.valueOf(limit));
128 if (cursor.getCount() > 0) {
129 cursor.moveToLast();
130 do {
131 list.add(Message.fromCursor(cursor));
132 } while (cursor.moveToPrevious());
133 }
134 return list;
135 }
136
137 public Conversation findConversation(Account account, String contactJid) {
138 SQLiteDatabase db = this.getReadableDatabase();
139 String[] selectionArgs = { account.getUuid(), contactJid };
140 Cursor cursor = db.query(Conversation.TABLENAME, null,
141 Conversation.ACCOUNT + "=? AND " + Conversation.CONTACT + "=?",
142 selectionArgs, null, null, null);
143 if (cursor.getCount() == 0)
144 return null;
145 cursor.moveToFirst();
146 return Conversation.fromCursor(cursor);
147 }
148
149 public void updateConversation(Conversation conversation) {
150 SQLiteDatabase db = this.getWritableDatabase();
151 String[] args = { conversation.getUuid() };
152 db.update(Conversation.TABLENAME, conversation.getContentValues(),
153 Conversation.UUID + "=?", args);
154 }
155
156 public List<Account> getAccounts() {
157 List<Account> list = new ArrayList<Account>();
158 SQLiteDatabase db = this.getReadableDatabase();
159 Cursor cursor = db.query(Account.TABLENAME, null, null, null, null,
160 null, null);
161 Log.d("gultsch", "found " + cursor.getCount() + " accounts");
162 while (cursor.moveToNext()) {
163 list.add(Account.fromCursor(cursor));
164 }
165 return list;
166 }
167
168 public void updateAccount(Account account) {
169 SQLiteDatabase db = this.getWritableDatabase();
170 String[] args = { account.getUuid() };
171 db.update(Account.TABLENAME, account.getContentValues(), Account.UUID
172 + "=?", args);
173 }
174
175 public void deleteAccount(Account account) {
176 SQLiteDatabase db = this.getWritableDatabase();
177 String[] args = { account.getUuid() };
178 Log.d("gultsch", "backend trying to delete account with uuid:"
179 + 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};
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 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 String args[] = {account.getUuid()};
235 Cursor cursor = db.query(Contact.TABLENAME, null, Contact.ACCOUNT+"=?", args, null,
236 null, null);
237 while (cursor.moveToNext()) {
238 list.add(Contact.fromCursor(cursor));
239 }
240 return list;
241 }
242
243 public Contact findContact(Account account, String jid) {
244 SQLiteDatabase db = this.getReadableDatabase();
245 String[] selectionArgs = { account.getUuid(), jid };
246 Cursor cursor = db.query(Contact.TABLENAME, null,
247 Contact.ACCOUNT + "=? AND " + Contact.JID + "=?",
248 selectionArgs, null, null, null);
249 if (cursor.getCount() == 0)
250 return null;
251 cursor.moveToFirst();
252 return Contact.fromCursor(cursor);
253 }
254}