2 * Copyright 2016 Soren Stoutner <soren@stoutner.com>.
4 * This file is part of Privacy Browser <https://www.stoutner.com/privacy-browser>.
6 * Privacy Browser is free software: you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation, either version 3 of the License, or
9 * (at your option) any later version.
11 * Privacy Browser is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
16 * You should have received a copy of the GNU General Public License
17 * along with Privacy Browser. If not, see <http://www.gnu.org/licenses/>.
20 package com.stoutner.privacybrowser;
22 import android.content.ContentValues;
23 import android.content.Context;
24 import android.database.Cursor;
25 import android.database.DatabaseUtils;
26 import android.database.sqlite.SQLiteDatabase;
27 import android.database.sqlite.SQLiteOpenHelper;
29 public class BookmarksDatabaseHandler extends SQLiteOpenHelper {
30 private static final int SCHEMA_VERSION = 1;
31 private static final String BOOKMARKS_DATABASE = "bookmarks.db";
32 private static final String BOOKMARKS_TABLE = "bookmarks";
34 public static final String _ID = "_id";
35 public static final String DISPLAY_ORDER = "displayorder";
36 public static final String BOOKMARK_NAME = "bookmarkname";
37 public static final String BOOKMARK_URL = "bookmarkurl";
38 public static final String PARENT_FOLDER = "parentfolder";
39 public static final String IS_FOLDER = "isfolder";
40 public static final String FAVORITE_ICON = "favoriteicon";
42 public BookmarksDatabaseHandler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
43 super(context, BOOKMARKS_DATABASE, factory, SCHEMA_VERSION);
47 public void onCreate(SQLiteDatabase bookmarksDatabase) {
48 // Create the database if it doesn't exist.
49 String CREATE_BOOKMARKS_TABLE = "CREATE TABLE " + BOOKMARKS_TABLE + " (" +
50 _ID + " integer primary key, " +
51 DISPLAY_ORDER + " integer, " +
52 BOOKMARK_NAME + " text, " +
53 BOOKMARK_URL + " text, " +
54 PARENT_FOLDER + " text, " +
55 IS_FOLDER + " boolean, " +
56 FAVORITE_ICON + " blob);";
58 bookmarksDatabase.execSQL(CREATE_BOOKMARKS_TABLE);
62 public void onUpgrade(SQLiteDatabase bookmarksDatabase, int oldVersion, int newVersion) {
63 // Code for upgrading the database will be added here when the schema version > 1.
66 public void createBookmark(String bookmarkName, String bookmarkURL, int displayOrder, String parentFolder, byte[] favoriteIcon) {
67 ContentValues bookmarkContentValues = new ContentValues();
69 // ID is created automatically.
70 bookmarkContentValues.put(DISPLAY_ORDER, displayOrder);
71 bookmarkContentValues.put(BOOKMARK_NAME, bookmarkName);
72 bookmarkContentValues.put(BOOKMARK_URL, bookmarkURL);
73 bookmarkContentValues.put(PARENT_FOLDER, parentFolder);
74 bookmarkContentValues.put(IS_FOLDER, false);
75 bookmarkContentValues.put(FAVORITE_ICON, favoriteIcon);
77 // Get a writable database handle.
78 SQLiteDatabase bookmarksDatabase = this.getWritableDatabase();
80 // The second argument is `null`, which makes it so that completely null rows cannot be created. Not a problem in our case.
81 bookmarksDatabase.insert(BOOKMARKS_TABLE, null, bookmarkContentValues);
83 // Close the database handle.
84 bookmarksDatabase.close();
87 public void createFolder(String folderName, int displayOrder, String parentFolder, byte[] favoriteIcon) {
88 ContentValues bookmarkContentValues = new ContentValues();
90 // ID is created automatically.
91 bookmarkContentValues.put(DISPLAY_ORDER, displayOrder);
92 bookmarkContentValues.put(BOOKMARK_NAME, folderName);
93 bookmarkContentValues.put(PARENT_FOLDER, parentFolder);
94 bookmarkContentValues.put(IS_FOLDER, true);
95 bookmarkContentValues.put(FAVORITE_ICON, favoriteIcon);
97 // Get a writable database handle.
98 SQLiteDatabase bookmarksDatabase = this.getWritableDatabase();
100 // The second argument is `null`, which makes it so that completely null rows cannot be created. Not a problem in our case.
101 bookmarksDatabase.insert(BOOKMARKS_TABLE, null, bookmarkContentValues);
103 // Close the database handle.
104 bookmarksDatabase.close();
107 public Cursor getBookmarkCursor(int databaseId) {
108 // Get a readable database handle.
109 SQLiteDatabase bookmarksDatabase = this.getReadableDatabase();
111 // Prepare the SQL statement to get the `Cursor` for `databaseId`
112 final String GET_ONE_BOOKMARK = "Select * FROM " + BOOKMARKS_TABLE +
113 " WHERE " + _ID + " = " + databaseId;
115 // Return the results as a `Cursor`. The second argument is `null` because there are no `selectionArgs`.
116 // We can't close the `Cursor` because we need to use it in the parent activity.
117 return bookmarksDatabase.rawQuery(GET_ONE_BOOKMARK, null);
120 public String getFolderName (int databaseId) {
121 // Get a readable database handle.
122 SQLiteDatabase bookmarksDatabase = this.getReadableDatabase();
124 // Prepare the SQL statement to get the `Cursor` for the folder.
125 final String GET_FOLDER = "Select * FROM " + BOOKMARKS_TABLE +
126 " WHERE " + _ID + " = " + databaseId;
128 // Get `folderCursor`. The second argument is `null` because there are no `selectionArgs`.
129 Cursor folderCursor = bookmarksDatabase.rawQuery(GET_FOLDER, null);
132 folderCursor.moveToFirst();
133 String folderName = folderCursor.getString(folderCursor.getColumnIndex(BOOKMARK_NAME));
135 // Close the cursor and the database handle.
136 folderCursor.close();
137 bookmarksDatabase.close();
139 // Return the folder name.
143 public Cursor getFolderCursor(String folderName) {
144 // Get a readable database handle.
145 SQLiteDatabase bookmarksDatabase = this.getReadableDatabase();
147 // SQL escape `folderName`.
148 folderName = DatabaseUtils.sqlEscapeString(folderName);
150 // Prepare the SQL statement to get the `Cursor` for the folder.
151 final String GET_FOLDER = "Select * FROM " + BOOKMARKS_TABLE +
152 " WHERE " + BOOKMARK_NAME + " = " + folderName +
153 " AND " + IS_FOLDER + " = " + 1;
155 // Return the results as a `Cursor`. The second argument is `null` because there are no `selectionArgs`.
156 // We can't close the `Cursor` because we need to use it in the parent activity.
157 return bookmarksDatabase.rawQuery(GET_FOLDER, null);
160 public Cursor getFoldersCursorExcept(String exceptFolders) {
161 // Get a readable database handle.
162 SQLiteDatabase bookmarksDatabase = this.getReadableDatabase();
164 // Prepare the SQL statement to get the `Cursor` for the folders.
165 final String GET_FOLDERS_EXCEPT = "Select * FROM " + BOOKMARKS_TABLE +
166 " WHERE " + IS_FOLDER + " = " + 1 +
167 " AND " + BOOKMARK_NAME + " NOT IN (" + exceptFolders +
168 ") ORDER BY " + BOOKMARK_NAME + " ASC";
170 // Return the results as a `Cursor`. The second argument is `null` because there are no `selectionArgs`.
171 // We can't close the `Cursor` because we need to use it in the parent activity.
172 return bookmarksDatabase.rawQuery(GET_FOLDERS_EXCEPT, null);
175 public Cursor getSubfoldersCursor(String currentFolder) {
176 // Get a readable database handle.
177 SQLiteDatabase bookmarksDatabase = this.getReadableDatabase();
179 // SQL escape `currentFolder.
180 currentFolder = DatabaseUtils.sqlEscapeString(currentFolder);
182 // Prepare the SQL statement to get the `Cursor` for the subfolders.
183 final String GET_SUBFOLDERS = "Select * FROM " + BOOKMARKS_TABLE +
184 " WHERE " + PARENT_FOLDER + " = " + currentFolder +
185 " AND " + IS_FOLDER + " = " + 1;
187 // Return the results as a `Cursor`. The second argument is `null` because there are no `selectionArgs`.
188 // We can't close the `Cursor` because we need to use it in the parent activity.
189 return bookmarksDatabase.rawQuery(GET_SUBFOLDERS, null);
192 public String getParentFolder(String currentFolder) {
193 // Get a readable database handle.
194 SQLiteDatabase bookmarksDatabase = this.getReadableDatabase();
196 // SQL escape `currentFolder`.
197 currentFolder = DatabaseUtils.sqlEscapeString(currentFolder);
199 // Prepare the SQL statement to get the parent folder.
200 final String GET_PARENT_FOLDER = "Select * FROM " + BOOKMARKS_TABLE +
201 " WHERE " + IS_FOLDER + " = " + 1 +
202 " AND " + BOOKMARK_NAME + " = " + currentFolder;
204 // The second argument is `null` because there are no `selectionArgs`.
205 Cursor bookmarkCursor = bookmarksDatabase.rawQuery(GET_PARENT_FOLDER, null);
206 bookmarkCursor.moveToFirst();
208 // Store the name of the parent folder.
209 String parentFolder = bookmarkCursor.getString(bookmarkCursor.getColumnIndex(PARENT_FOLDER));
211 // Close the `Cursor`.
212 bookmarkCursor.close();
217 public Cursor getAllBookmarksCursor() {
218 // Get a readable database handle.
219 SQLiteDatabase bookmarksDatabase = this.getReadableDatabase();
221 // Get everything in the BOOKMARKS_TABLE.
222 final String GET_ALL_BOOKMARKS = "Select * FROM " + BOOKMARKS_TABLE;
224 // Return the results as a Cursor. The second argument is `null` because there are no selectionArgs.
225 // We can't close the Cursor because we need to use it in the parent activity.
226 return bookmarksDatabase.rawQuery(GET_ALL_BOOKMARKS, null);
229 public Cursor getAllBookmarksCursorByDisplayOrder(String folderName) {
230 // Get a readable database handle.
231 SQLiteDatabase bookmarksDatabase = this.getReadableDatabase();
233 // SQL escape `folderName`.
234 folderName = DatabaseUtils.sqlEscapeString(folderName);
236 // Get everything in the BOOKMARKS_TABLE.
237 final String GET_ALL_BOOKMARKS = "Select * FROM " + BOOKMARKS_TABLE +
238 " WHERE " + PARENT_FOLDER + " = " + folderName +
239 " ORDER BY " + DISPLAY_ORDER + " ASC";
241 // Return the results as a Cursor. The second argument is `null` because there are no selectionArgs.
242 // We can't close the Cursor because we need to use it in the parent activity.
243 return bookmarksDatabase.rawQuery(GET_ALL_BOOKMARKS, null);
246 public Cursor getBookmarksCursorExcept(long[] exceptIdLongArray, String folderName) {
247 // Get a readable database handle.
248 SQLiteDatabase bookmarksDatabase = this.getReadableDatabase();
250 // Prepare a string that contains the comma-separated list of IDs not to get.
251 String doNotGetIdsString = "";
252 // Extract the array to `doNotGetIdsString`.
253 for (long databaseIdLong : exceptIdLongArray) {
254 // If this is the first number, only add the number.
255 if (doNotGetIdsString.isEmpty()) {
256 doNotGetIdsString = String.valueOf(databaseIdLong);
257 } else { // If there already is a number in the string, place a `,` before the number.
258 doNotGetIdsString = doNotGetIdsString + "," + databaseIdLong;
262 // SQL escape `folderName`.
263 folderName = DatabaseUtils.sqlEscapeString(folderName);
265 // Prepare the SQL statement to select all items except those with the specified IDs.
266 final String GET_All_BOOKMARKS_EXCEPT_SPECIFIED = "Select * FROM " + BOOKMARKS_TABLE +
267 " WHERE " + PARENT_FOLDER + " = " + folderName +
268 " AND " + _ID + " NOT IN (" + doNotGetIdsString +
269 ") ORDER BY " + DISPLAY_ORDER + " ASC";
271 // Return the results as a `Cursor`. The second argument is `null` because there are no `selectionArgs`.
272 // We can't close the `Cursor` because we need to use it in the parent activity.
273 return bookmarksDatabase.rawQuery(GET_All_BOOKMARKS_EXCEPT_SPECIFIED, null);
276 public boolean isFolder(int databaseId) {
277 // Get a readable database handle.
278 SQLiteDatabase bookmarksDatabase = this.getReadableDatabase();
280 // Prepare the SQL statement to determine if `databaseId` is a folder.
281 final String CHECK_IF_FOLDER = "Select * FROM " + BOOKMARKS_TABLE +
282 " WHERE " + _ID + " = " + databaseId;
284 // Populate folderCursor. The second argument is `null` because there are no `selectionArgs`.
285 Cursor folderCursor = bookmarksDatabase.rawQuery(CHECK_IF_FOLDER, null);
287 // Ascertain if this database ID is a folder.
288 folderCursor.moveToFirst();
289 boolean isFolder = (folderCursor.getInt(folderCursor.getColumnIndex(IS_FOLDER)) == 1);
291 // Close the `Cursor` and the database handle.
292 folderCursor.close();
293 bookmarksDatabase.close();
298 public void updateBookmark(int databaseId, String bookmarkName, String bookmarkUrl) {
299 // Store the updated values in `bookmarkContentValues`.
300 ContentValues bookmarkContentValues = new ContentValues();
302 bookmarkContentValues.put(BOOKMARK_NAME, bookmarkName);
303 bookmarkContentValues.put(BOOKMARK_URL, bookmarkUrl);
305 // Get a writable database handle.
306 SQLiteDatabase bookmarksDatabase = this.getWritableDatabase();
308 // Update the bookmark. The last argument is `null` because there are no `whereArgs`.
309 bookmarksDatabase.update(BOOKMARKS_TABLE, bookmarkContentValues, _ID + " = " + databaseId, null);
311 // Close the database handle.
312 bookmarksDatabase.close();
315 public void updateBookmark(int databaseId, String bookmarkName, String bookmarkUrl, byte[] favoriteIcon) {
316 // Store the updated values in `bookmarkContentValues`.
317 ContentValues bookmarkContentValues = new ContentValues();
319 bookmarkContentValues.put(BOOKMARK_NAME, bookmarkName);
320 bookmarkContentValues.put(BOOKMARK_URL, bookmarkUrl);
321 bookmarkContentValues.put(FAVORITE_ICON, favoriteIcon);
323 // Get a writable database handle.
324 SQLiteDatabase bookmarksDatabase = this.getWritableDatabase();
326 // Update the bookmark. The last argument is `null` because there are no `whereArgs`.
327 bookmarksDatabase.update(BOOKMARKS_TABLE, bookmarkContentValues, _ID + " = " + databaseId, null);
329 // Close the database handle.
330 bookmarksDatabase.close();
333 public void updateFolder(int databaseId, String oldFolderName, String newFolderName) {
334 // Get a writable database handle.
335 SQLiteDatabase bookmarksDatabase = this.getWritableDatabase();
337 // Update the folder first. Store the updated values in `folderContentValues`.
338 ContentValues folderContentValues = new ContentValues();
340 folderContentValues.put(BOOKMARK_NAME, newFolderName);
342 // Run the update on the folder. The last argument is `null` because there are no `whereArgs`.
343 bookmarksDatabase.update(BOOKMARKS_TABLE, folderContentValues, _ID + " = " + databaseId, null);
346 // Update the bookmarks inside the folder with the new parent folder name.
347 ContentValues bookmarkContentValues = new ContentValues();
349 bookmarkContentValues.put(PARENT_FOLDER, newFolderName);
351 // SQL escape `oldFolderName`.
352 oldFolderName = DatabaseUtils.sqlEscapeString(oldFolderName);
354 // Run the update on the bookmarks. The last argument is `null` because there are no `whereArgs`.
355 bookmarksDatabase.update(BOOKMARKS_TABLE, bookmarkContentValues, PARENT_FOLDER + " = " + oldFolderName, null);
357 // Close the database handle.
358 bookmarksDatabase.close();
361 public void updateFolder(int databaseId, String oldFolderName, String newFolderName, byte[] folderIcon) {
362 // Get a writable database handle.
363 SQLiteDatabase bookmarksDatabase = this.getWritableDatabase();
365 // Update the folder first. Store the updated values in `folderContentValues`.
366 ContentValues folderContentValues = new ContentValues();
368 folderContentValues.put(BOOKMARK_NAME, newFolderName);
369 folderContentValues.put(FAVORITE_ICON, folderIcon);
371 // Run the update on the folder. The last argument is `null` because there are no `whereArgs`.
372 bookmarksDatabase.update(BOOKMARKS_TABLE, folderContentValues, _ID + " = " + databaseId, null);
375 // Update the bookmarks inside the folder with the new parent folder name.
376 ContentValues bookmarkContentValues = new ContentValues();
378 bookmarkContentValues.put(PARENT_FOLDER, newFolderName);
380 // SQL escape `oldFolderName`.
381 oldFolderName = DatabaseUtils.sqlEscapeString(oldFolderName);
383 // Run the update on the bookmarks. The last argument is `null` because there are no `whereArgs`.
384 bookmarksDatabase.update(BOOKMARKS_TABLE, bookmarkContentValues, PARENT_FOLDER + " = " + oldFolderName, null);
386 // Close the database handle.
387 bookmarksDatabase.close();
390 public void updateBookmarkDisplayOrder(int databaseId, int displayOrder) {
391 // Get a writable database handle.
392 SQLiteDatabase bookmarksDatabase = this.getWritableDatabase();
394 // Store the new display order in `bookmarkContentValues`.
395 ContentValues bookmarkContentValues = new ContentValues();
396 bookmarkContentValues.put(DISPLAY_ORDER, displayOrder);
398 // Update the database. The last argument is `null` because there are no `whereArgs`.
399 bookmarksDatabase.update(BOOKMARKS_TABLE, bookmarkContentValues, _ID + " = " + databaseId, null);
401 // Close the database handle.
402 bookmarksDatabase.close();
405 public void moveToFolder(int databaseId, String newFolder) {
406 // Get a writable database handle.
407 SQLiteDatabase bookmarksDatabase = this.getWritableDatabase();
409 // Get the highest `DISPLAY_ORDER` in the new folder
410 String newFolderSqlEscaped = DatabaseUtils.sqlEscapeString(newFolder);
411 final String NEW_FOLDER = "Select * FROM " + BOOKMARKS_TABLE +
412 " WHERE " + PARENT_FOLDER + " = " + newFolderSqlEscaped +
413 " ORDER BY " + DISPLAY_ORDER + " ASC";
414 // The second argument is `null` because there are no `selectionArgs`.
415 Cursor newFolderCursor = bookmarksDatabase.rawQuery(NEW_FOLDER, null);
417 if (newFolderCursor.getCount() > 0) {
418 newFolderCursor.moveToLast();
419 displayOrder = newFolderCursor.getInt(newFolderCursor.getColumnIndex(DISPLAY_ORDER)) + 1;
423 newFolderCursor.close();
425 // Store the new values in `bookmarkContentValues`.
426 ContentValues bookmarkContentValues = new ContentValues();
427 bookmarkContentValues.put(DISPLAY_ORDER, displayOrder);
428 bookmarkContentValues.put(PARENT_FOLDER, newFolder);
430 // Update the database. The last argument is 'null' because there are no 'whereArgs'.
431 bookmarksDatabase.update(BOOKMARKS_TABLE, bookmarkContentValues, _ID + " = " + databaseId, null);
433 // Close the database handle.
434 bookmarksDatabase.close();
437 public void deleteBookmark(int databaseId) {
438 // Get a writable database handle.
439 SQLiteDatabase bookmarksDatabase = this.getWritableDatabase();
441 // Deletes the row with the given databaseId. The last argument is null because we don't need additional parameters.
442 bookmarksDatabase.delete(BOOKMARKS_TABLE, _ID + " = " + databaseId, null);
444 // Close the database handle.
445 bookmarksDatabase.close();