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