1package eu.siacs.conversations.persistance;
2
3import android.content.ContentValues;
4import android.database.sqlite.SQLiteBlobTooBigException;
5import android.database.sqlite.SQLiteDatabase;
6import android.util.Log;
7import androidx.test.core.app.ApplicationProvider;
8import androidx.test.ext.junit.runners.AndroidJUnit4;
9
10import org.json.JSONException;
11import org.json.JSONObject;
12import org.junit.After;
13import org.junit.Assert;
14import org.junit.Before;
15import org.junit.BeforeClass;
16import org.junit.Test;
17import org.junit.runner.RunWith;
18
19import java.util.HashMap;
20import java.util.HashSet;
21import java.util.Set;
22import java.util.UUID;
23
24import eu.siacs.conversations.entities.Account;
25import eu.siacs.conversations.entities.Conversation;
26import eu.siacs.conversations.entities.MucOptions;
27import eu.siacs.conversations.xml.Namespace;
28import im.conversations.android.xmpp.model.disco.info.Feature;
29import im.conversations.android.xmpp.model.disco.info.InfoQuery;
30
31@RunWith(AndroidJUnit4.class)
32public class DatabaseBackendTest {
33 private record AccountFixture(String uuid, String username, String server) {
34 void write(DatabaseBackend db) {
35 final var cv = new ContentValues();
36 cv.put("uuid", uuid);
37 cv.put("username", username);
38 cv.put("server", server);
39 cv.put("password", "test");
40 cv.put("options", 0);
41 db.getWritableDatabase().insertWithOnConflict(
42 "accounts", null, cv, SQLiteDatabase.CONFLICT_REPLACE);
43 }
44 }
45
46 private record ConversationFixture(
47 String conversationUuid,
48 AccountFixture account,
49 String name,
50 String contactJid,
51 String attributes,
52 HashMap<MucOptions.User.OccupantId, MucOptions.User.CacheEntry> occupantCache
53 ) {
54 void writeConversation(DatabaseBackend db) {
55 final var cv = new ContentValues();
56 cv.put("uuid", conversationUuid);
57 cv.put("name", name);
58 cv.put("contactUuid", "");
59 cv.put("accountUuid", account.uuid());
60 cv.put("contactJid", contactJid);
61 cv.put("created", System.currentTimeMillis());
62 cv.put("status", Conversation.STATUS_AVAILABLE);
63 cv.put("mode", Conversation.MODE_MULTI);
64 cv.put("attributes", attributes);
65 db.getWritableDatabase().insert("conversations", null, cv);
66 }
67
68 void writeOccupants(DatabaseBackend db) {
69 for (final var entry : occupantCache.entrySet()) {
70 final var cv = new ContentValues();
71 cv.put(MucOptions.User.CacheEntry.OCCUPANT_ID, entry.getKey().inner());
72 cv.put(MucOptions.User.CacheEntry.CONVERSATION_UUID, conversationUuid);
73 cv.put(MucOptions.User.CacheEntry.AVATAR, entry.getValue().avatar());
74 cv.put(MucOptions.User.CacheEntry.NICK, entry.getValue().nick());
75 db.getWritableDatabase().insert(
76 MucOptions.User.CacheEntry.TABLENAME, null, cv);
77 }
78 }
79
80 void writeAll(DatabaseBackend db) {
81 writeConversation(db);
82 writeOccupants(db);
83 }
84
85 Conversation extractAndConfigure(DatabaseBackend db)
86 {
87 final var conversations = db.getConversations(Conversation.STATUS_AVAILABLE);
88 Assert.assertNotNull("getConversations should not return null", conversations);
89
90 Conversation match = null;
91 for (final var c : conversations) {
92 if (conversationUuid.equals(c.getUuid())) {
93 match = c;
94 break;
95 }
96 }
97 Assert.assertNotNull(
98 "Fixture conversation " + conversationUuid + " not found", match);
99
100 match.setAccount(db.getAccounts().get(0));
101 match.getMucOptions().updateConfiguration(INFO_QUERY_WITH_OCCUPANT_ID);
102 match.putAllInMucOccupantCache(db.getMucUsersForConversation(match));
103 return match;
104 }
105 }
106
107 private DatabaseBackend db;
108 private static final InfoQuery INFO_QUERY_WITH_OCCUPANT_ID = new InfoQuery();
109 private static final int MANY_USERS = 20_000;
110
111 private static AccountFixture ACCOUNT;
112 private static ConversationFixture ROW_TOO_BIG;
113 private static ConversationFixture CONFORMING;
114 private static ConversationFixture NO_CACHED_MUC_USERS;
115 private static ConversationFixture[] FIXTURES;
116
117 private Set<String> getCheogramSchema(SQLiteDatabase db) {
118 var schema = new HashSet<String>();
119
120 var cursor = db.rawQuery(
121 "SELECT name FROM cheogram.sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'",
122 null
123 );
124 final var tables = new java.util.ArrayList<String>();
125 while (cursor.moveToNext()) {
126 tables.add(cursor.getString(0));
127 }
128 cursor.close();
129
130 for (final var table : tables) {
131 cursor = db.rawQuery("PRAGMA cheogram.table_info(" + table + ")", null);
132 while (cursor.moveToNext()) {
133 final var colName = cursor.getString(cursor.getColumnIndexOrThrow("name"));
134 final var colType = cursor.getString(cursor.getColumnIndexOrThrow("type"));
135 schema.add("table:" + table + ":col:" + colName + ":" + colType);
136 }
137 cursor.close();
138 }
139
140 cursor = db.rawQuery(
141 "SELECT name, tbl_name, sql FROM cheogram.sqlite_master WHERE type='index' AND sql IS NOT NULL",
142 null
143 );
144 while (cursor.moveToNext()) {
145 final var name = cursor.getString(0);
146 final var tbl = cursor.getString(1);
147 final var sql = cursor.getString(2);
148 schema.add("index:" + name + ":" + tbl + ":" + sql);
149 }
150 cursor.close();
151
152 return schema;
153 }
154
155 private int getCheogramVersion(SQLiteDatabase db) {
156 final var cursor = db.rawQuery("PRAGMA cheogram.user_version", null);
157 var version = -1;
158 try {
159 cursor.moveToNext();
160 version = cursor.getInt(0);
161 } finally {
162 cursor.close();
163 }
164 return version;
165 }
166
167 @BeforeClass
168 public static void setupClass() throws JSONException {
169 final var occupantIdFeature = new Feature();
170 occupantIdFeature.setVar(Namespace.OCCUPANT_ID);
171 INFO_QUERY_WITH_OCCUPANT_ID.addChild(occupantIdFeature);
172
173 ACCOUNT = new AccountFixture(
174 UUID.randomUUID().toString(), "test", "example.com");
175
176 final var rowTooBigAttrs = new JSONObject();
177 for (int i = 0; i < MANY_USERS; i++) {
178 final var occupantId = UUID.randomUUID().toString();
179 rowTooBigAttrs.put("occupantNick/" + occupantId, "User" + i);
180 rowTooBigAttrs.put("occupantAvatar/" + occupantId,
181 UUID.randomUUID().toString().repeat(5));
182 }
183 rowTooBigAttrs.put("mucNick", "testMucNick");
184
185 final var rowTooBigCache =
186 new HashMap<MucOptions.User.OccupantId, MucOptions.User.CacheEntry>();
187 rowTooBigCache.put(
188 new MucOptions.User.OccupantId(UUID.randomUUID().toString()),
189 new MucOptions.User.CacheEntry(UUID.randomUUID().toString(), "RowTooBigUser"));
190
191 ROW_TOO_BIG = new ConversationFixture(
192 UUID.randomUUID().toString(),
193 ACCOUNT,
194 "Big MUC",
195 "room@conference.example.com",
196 rowTooBigAttrs.toString(),
197 rowTooBigCache
198 );
199
200 final var conformingCache =
201 new HashMap<MucOptions.User.OccupantId, MucOptions.User.CacheEntry>();
202 conformingCache.put(
203 new MucOptions.User.OccupantId(UUID.randomUUID().toString()),
204 new MucOptions.User.CacheEntry(UUID.randomUUID().toString(), "ConformingUser"));
205
206 CONFORMING = new ConversationFixture(
207 UUID.randomUUID().toString(),
208 ACCOUNT,
209 "Normal MUC",
210 "normalroom@conference.example.com",
211 new JSONObject().put("mucNick", "testMucNick").toString(),
212 conformingCache
213 );
214
215 NO_CACHED_MUC_USERS = new ConversationFixture(
216 UUID.randomUUID().toString(),
217 ACCOUNT,
218 "Empty Cache MUC",
219 "emptycache@conference.example.com",
220 new JSONObject().put("mucNick", "testMucNick").toString(),
221 new HashMap<>()
222 );
223
224 FIXTURES = new ConversationFixture[] {
225 ROW_TOO_BIG, CONFORMING, NO_CACHED_MUC_USERS };
226 }
227
228 @Before
229 public void setUp() throws Exception {
230 db = DatabaseBackend.getInstance(
231 ApplicationProvider.getApplicationContext());
232 ACCOUNT.write(db);
233 for (final var fixture : FIXTURES) {
234 fixture.writeAll(db);
235 }
236 }
237
238 @After
239 public void tearDown() {
240 SQLiteDatabase sqDb = db.getWritableDatabase();
241 sqDb.delete(Conversation.TABLENAME, null, null);
242 sqDb.delete(Account.TABLENAME, null, null);
243 sqDb.delete(MucOptions.User.CacheEntry.TABLENAME, null, null);
244 }
245
246 @Test
247 public void getConversationsCorrectlyReadsMucUsers() throws Exception {
248 Assert.assertTrue(
249 "Occupant cache should be empty when no occupants are written",
250 NO_CACHED_MUC_USERS
251 .extractAndConfigure(db)
252 .getMucOccupantCache()
253 .isEmpty()
254 );
255
256 Assert.assertEquals(
257 "Cached entries should match fixture",
258 CONFORMING
259 .extractAndConfigure(db)
260 .getMucOccupantCache(),
261 CONFORMING.occupantCache()
262 );
263 }
264
265 @Test
266 public void getConversationsTruncatesTooBigRow() throws Exception {
267 final var conversation = ROW_TOO_BIG.extractAndConfigure(db);
268 java.lang.reflect.Field attributesField =
269 conversation.getClass().getDeclaredField("attributes");
270
271 attributesField.setAccessible(true);
272 org.json.JSONObject attributes =
273 (org.json.JSONObject) attributesField.get(conversation);
274
275 final var expected = new JSONObject();
276 expected.put("members_only", "false");
277 expected.put("moderated", "false");
278 expected.put("non_anonymous", "false");
279
280 Assert.assertEquals(
281 "Attributes should not contain occupant cache after truncation:\n" + attributes.toString(4),
282 expected.toString(),
283 attributes.toString()
284 );
285 }
286
287 @Test
288 public void cheogramMigrateIsIdempotent() throws Exception {
289 final var sqDb = db.getWritableDatabase();
290
291 final var schemaAfterFirst = getCheogramSchema(sqDb);
292 int versionAfterFirst = getCheogramVersion(sqDb);
293
294 Assert.assertTrue("Version should be > 0 after migration", versionAfterFirst > 0);
295
296 final var migrateMethod = DatabaseBackend.class.getDeclaredMethod("cheogramMigrate", SQLiteDatabase.class);
297 migrateMethod.setAccessible(true);
298 migrateMethod.invoke(db, sqDb);
299
300 final var schemaAfterSecond = getCheogramSchema(sqDb);
301 var versionAfterSecond = getCheogramVersion(sqDb);
302
303 Assert.assertEquals("Schema should be identical after re-running migration",
304 schemaAfterFirst, schemaAfterSecond);
305 Assert.assertEquals("Version should be unchanged after re-running migration",
306 versionAfterFirst, versionAfterSecond);
307 }
308
309 @Test
310 public void updateConversationWritesMucOccupantsCache() throws Exception {
311 final var conversation = NO_CACHED_MUC_USERS.extractAndConfigure(db);
312 conversation.putAllInMucOccupantCache(CONFORMING.occupantCache());
313 db.updateConversation(conversation);
314
315 final var readBackCache = db.getMucUsersForConversation(conversation);
316 Assert.assertEquals(
317 "Cache should match after updateConversation",
318 CONFORMING.occupantCache(),
319 readBackCache
320 );
321 }
322}