FreeRDP
BookmarkDB.java
1 /*
2  Android Bookmark Database
3 
4  Copyright 2013 Thincast Technologies GmbH, Author: Martin Fleisz
5 
6  This Source Code Form is subject to the terms of the Mozilla Public License, v. 2.0.
7  If a copy of the MPL was not distributed with this file, You can obtain one at
8  http://mozilla.org/MPL/2.0/.
9 */
10 
11 package com.freerdp.freerdpcore.services;
12 
13 import android.content.ContentValues;
14 import android.content.Context;
15 import android.database.Cursor;
16 import android.database.sqlite.SQLiteDatabase;
17 import android.database.sqlite.SQLiteOpenHelper;
18 import android.provider.BaseColumns;
19 import android.util.Log;
20 
21 import java.util.ArrayList;
22 import java.util.Arrays;
23 import java.util.List;
24 
25 public class BookmarkDB extends SQLiteOpenHelper
26 {
27  public static final String ID = BaseColumns._ID;
28  private static final int DB_VERSION = 10;
29  private static final String DB_BACKUP_PREFIX = "temp_";
30  private static final String DB_NAME = "bookmarks.db";
31  static final String DB_TABLE_BOOKMARK = "tbl_manual_bookmarks";
32  static final String DB_TABLE_SCREEN = "tbl_screen_settings";
33  static final String DB_TABLE_PERFORMANCE = "tbl_performance_flags";
34  private static final String[] DB_TABLES = { DB_TABLE_BOOKMARK, DB_TABLE_SCREEN,
35  DB_TABLE_PERFORMANCE };
36 
37  static final String DB_KEY_SCREEN_COLORS = "colors";
38  static final String DB_KEY_SCREEN_RESOLUTION = "resolution";
39  static final String DB_KEY_SCREEN_WIDTH = "width";
40  static final String DB_KEY_SCREEN_HEIGHT = "height";
41 
42  static final String DB_KEY_SCREEN_SETTINGS = "screen_settings";
43  static final String DB_KEY_SCREEN_SETTINGS_3G = "screen_3g";
44  static final String DB_KEY_PERFORMANCE_FLAGS = "performance_flags";
45  static final String DB_KEY_PERFORMANCE_FLAGS_3G = "performance_3g";
46 
47  static final String DB_KEY_PERFORMANCE_RFX = "perf_remotefx";
48  static final String DB_KEY_PERFORMANCE_GFX = "perf_gfx";
49  static final String DB_KEY_PERFORMANCE_H264 = "perf_gfx_h264";
50  static final String DB_KEY_PERFORMANCE_WALLPAPER = "perf_wallpaper";
51  static final String DB_KEY_PERFORMANCE_THEME = "perf_theming";
52  static final String DB_KEY_PERFORMANCE_DRAG = "perf_full_window_drag";
53  static final String DB_KEY_PERFORMANCE_MENU_ANIMATIONS = "perf_menu_animations";
54  static final String DB_KEY_PERFORMANCE_FONTS = "perf_font_smoothing";
55  static final String DB_KEY_PERFORMANCE_COMPOSITION = "perf_desktop_composition";
56 
57  static final String DB_KEY_BOOKMARK_LABEL = "label";
58  static final String DB_KEY_BOOKMARK_HOSTNAME = "hostname";
59  static final String DB_KEY_BOOKMARK_USERNAME = "username";
60  static final String DB_KEY_BOOKMARK_PASSWORD = "password";
61  static final String DB_KEY_BOOKMARK_DOMAIN = "domain";
62  static final String DB_KEY_BOOKMARK_PORT = "port";
63 
64  static final String DB_KEY_BOOKMARK_REDIRECT_SDCARD = "redirect_sdcard";
65  static final String DB_KEY_BOOKMARK_REDIRECT_SOUND = "redirect_sound";
66  static final String DB_KEY_BOOKMARK_REDIRECT_MICROPHONE = "redirect_microphone";
67  static final String DB_KEY_BOOKMARK_SECURITY = "security";
68  static final String DB_KEY_BOOKMARK_REMOTE_PROGRAM = "remote_program";
69  static final String DB_KEY_BOOKMARK_WORK_DIR = "work_dir";
70  static final String DB_KEY_BOOKMARK_ASYNC_CHANNEL = "async_channel";
71  static final String DB_KEY_BOOKMARK_ASYNC_UPDATE = "async_update";
72  static final String DB_KEY_BOOKMARK_CONSOLE_MODE = "console_mode";
73  static final String DB_KEY_BOOKMARK_DEBUG_LEVEL = "debug_level";
74 
75  static final String DB_KEY_BOOKMARK_GW_ENABLE = "enable_gateway_settings";
76  static final String DB_KEY_BOOKMARK_GW_HOSTNAME = "gateway_hostname";
77  static final String DB_KEY_BOOKMARK_GW_PORT = "gateway_port";
78  static final String DB_KEY_BOOKMARK_GW_USERNAME = "gateway_username";
79  static final String DB_KEY_BOOKMARK_GW_PASSWORD = "gateway_password";
80  static final String DB_KEY_BOOKMARK_GW_DOMAIN = "gateway_domain";
81  static final String DB_KEY_BOOKMARK_3G_ENABLE = "enable_3g_settings";
82 
83  public BookmarkDB(Context context)
84  {
85  super(context, DB_NAME, null, DB_VERSION);
86  }
87 
88  private static List<String> GetColumns(SQLiteDatabase db, String tableName)
89  {
90  List<String> ar = null;
91  try (Cursor c = db.rawQuery("SELECT * FROM " + tableName + " LIMIT 1", null))
92  {
93  if (c != null)
94  {
95  ar = new ArrayList<>(Arrays.asList(c.getColumnNames()));
96  }
97  }
98  catch (Exception e)
99  {
100  Log.v(tableName, e.getMessage(), e);
101  e.printStackTrace();
102  }
103  return ar;
104  }
105 
106  private static String joinStrings(List<String> list, String delim)
107  {
108  StringBuilder buf = new StringBuilder();
109  int num = list.size();
110  for (int i = 0; i < num; i++)
111  {
112  if (i != 0)
113  buf.append(delim);
114  buf.append((String)list.get(i));
115  }
116  return buf.toString();
117  }
118 
119  private void backupTables(SQLiteDatabase db)
120  {
121  for (String table : DB_TABLES)
122  {
123  final String tmpTable = DB_BACKUP_PREFIX + table;
124  final String query = "ALTER TABLE '" + table + "' RENAME TO '" + tmpTable + "'";
125  try
126  {
127  db.execSQL(query);
128  }
129  catch (Exception e)
130  {
131  /* Ignore errors if table does not exist. */
132  }
133  }
134  }
135 
136  private void dropOldTables(SQLiteDatabase db)
137  {
138  for (String table : DB_TABLES)
139  {
140  final String tmpTable = DB_BACKUP_PREFIX + table;
141  final String query = "DROP TABLE IF EXISTS '" + tmpTable + "'";
142  db.execSQL(query);
143  }
144  }
145 
146  private void createDB(SQLiteDatabase db)
147  {
148  final String sqlScreenSettings =
149  "CREATE TABLE IF NOT EXISTS " + DB_TABLE_SCREEN + " (" + ID + " INTEGER PRIMARY KEY, " +
150  DB_KEY_SCREEN_COLORS + " INTEGER DEFAULT 16, " + DB_KEY_SCREEN_RESOLUTION +
151  " INTEGER DEFAULT 0, " + DB_KEY_SCREEN_WIDTH + ", " + DB_KEY_SCREEN_HEIGHT + ");";
152 
153  db.execSQL(sqlScreenSettings);
154 
155  final String sqlPerformanceFlags =
156  "CREATE TABLE IF NOT EXISTS " + DB_TABLE_PERFORMANCE + " (" + ID +
157  " INTEGER PRIMARY KEY, " + DB_KEY_PERFORMANCE_RFX + " INTEGER, " +
158  DB_KEY_PERFORMANCE_GFX + " INTEGER, " + DB_KEY_PERFORMANCE_H264 + " INTEGER, " +
159  DB_KEY_PERFORMANCE_WALLPAPER + " INTEGER, " + DB_KEY_PERFORMANCE_THEME + " INTEGER, " +
160  DB_KEY_PERFORMANCE_DRAG + " INTEGER, " + DB_KEY_PERFORMANCE_MENU_ANIMATIONS +
161  " INTEGER, " + DB_KEY_PERFORMANCE_FONTS + " INTEGER, " +
162  DB_KEY_PERFORMANCE_COMPOSITION + " INTEGER);";
163 
164  db.execSQL(sqlPerformanceFlags);
165 
166  final String sqlManualBookmarks = getManualBookmarksCreationString();
167  db.execSQL(sqlManualBookmarks);
168  }
169 
170  private void upgradeTables(SQLiteDatabase db)
171  {
172  for (String table : DB_TABLES)
173  {
174  final String tmpTable = DB_BACKUP_PREFIX + table;
175 
176  final List<String> newColumns = GetColumns(db, table);
177  List<String> columns = GetColumns(db, tmpTable);
178 
179  if (columns != null)
180  {
181  columns.retainAll(newColumns);
182 
183  // restore data
184  final String cols = joinStrings(columns, ",");
185  final String query = String.format("INSERT INTO %s (%s) SELECT %s from '%s'", table,
186  cols, cols, tmpTable);
187  db.execSQL(query);
188  }
189  }
190  }
191 
192  private void downgradeTables(SQLiteDatabase db)
193  {
194  for (String table : DB_TABLES)
195  {
196  final String tmpTable = DB_BACKUP_PREFIX + table;
197 
198  List<String> oldColumns = GetColumns(db, table);
199  final List<String> columns = GetColumns(db, tmpTable);
200 
201  if (oldColumns != null)
202  {
203  oldColumns.retainAll(columns);
204 
205  // restore data
206  final String cols = joinStrings(oldColumns, ",");
207  final String query = String.format("INSERT INTO %s (%s) SELECT %s from '%s'", table,
208  cols, cols, tmpTable);
209  db.execSQL(query);
210  }
211  }
212  }
213 
214  private List<String> getTableNames(SQLiteDatabase db)
215  {
216  final String query = "SELECT name FROM sqlite_master WHERE type='table'";
217  Cursor cursor = db.rawQuery(query, null);
218  List<String> list = new ArrayList<>();
219  try
220  {
221  if (cursor.moveToFirst() && (cursor.getCount() > 0))
222  {
223  while (!cursor.isAfterLast())
224  {
225  final String name = cursor.getString(cursor.getColumnIndex("name"));
226  list.add(name);
227  cursor.moveToNext();
228  }
229  }
230  }
231  finally
232  {
233  cursor.close();
234  }
235 
236  return list;
237  }
238 
239  private void insertDefault(SQLiteDatabase db)
240  {
241  ContentValues screenValues = new ContentValues();
242  screenValues.put(DB_KEY_SCREEN_COLORS, 32);
243  screenValues.put(DB_KEY_SCREEN_RESOLUTION, 1);
244  screenValues.put(DB_KEY_SCREEN_WIDTH, 1024);
245  screenValues.put(DB_KEY_SCREEN_HEIGHT, 768);
246 
247  final long idScreen = db.insert(DB_TABLE_SCREEN, null, screenValues);
248  final long idScreen3g = db.insert(DB_TABLE_SCREEN, null, screenValues);
249 
250  ContentValues performanceValues = new ContentValues();
251  performanceValues.put(DB_KEY_PERFORMANCE_RFX, 1);
252  performanceValues.put(DB_KEY_PERFORMANCE_GFX, 1);
253  performanceValues.put(DB_KEY_PERFORMANCE_H264, 0);
254  performanceValues.put(DB_KEY_PERFORMANCE_WALLPAPER, 0);
255  performanceValues.put(DB_KEY_PERFORMANCE_THEME, 0);
256  performanceValues.put(DB_KEY_PERFORMANCE_DRAG, 0);
257  performanceValues.put(DB_KEY_PERFORMANCE_MENU_ANIMATIONS, 0);
258  performanceValues.put(DB_KEY_PERFORMANCE_FONTS, 0);
259  performanceValues.put(DB_KEY_PERFORMANCE_COMPOSITION, 0);
260 
261  final long idPerformance = db.insert(DB_TABLE_PERFORMANCE, null, performanceValues);
262  final long idPerformance3g = db.insert(DB_TABLE_PERFORMANCE, null, performanceValues);
263 
264  ContentValues bookmarkValues = new ContentValues();
265  bookmarkValues.put(DB_KEY_BOOKMARK_LABEL, "Test Server");
266  bookmarkValues.put(DB_KEY_BOOKMARK_HOSTNAME, "testservice.afreerdp.com");
267  bookmarkValues.put(DB_KEY_BOOKMARK_USERNAME, "");
268  bookmarkValues.put(DB_KEY_BOOKMARK_PASSWORD, "");
269  bookmarkValues.put(DB_KEY_BOOKMARK_DOMAIN, "");
270  bookmarkValues.put(DB_KEY_BOOKMARK_PORT, "3389");
271 
272  bookmarkValues.put(DB_KEY_SCREEN_SETTINGS, idScreen);
273  bookmarkValues.put(DB_KEY_SCREEN_SETTINGS_3G, idScreen3g);
274  bookmarkValues.put(DB_KEY_PERFORMANCE_FLAGS, idPerformance);
275  bookmarkValues.put(DB_KEY_PERFORMANCE_FLAGS_3G, idPerformance3g);
276 
277  bookmarkValues.put(DB_KEY_BOOKMARK_REDIRECT_SDCARD, 0);
278  bookmarkValues.put(DB_KEY_BOOKMARK_REDIRECT_SOUND, 0);
279  bookmarkValues.put(DB_KEY_BOOKMARK_REDIRECT_MICROPHONE, 0);
280  bookmarkValues.put(DB_KEY_BOOKMARK_SECURITY, 0);
281  bookmarkValues.put(DB_KEY_BOOKMARK_REMOTE_PROGRAM, "");
282  bookmarkValues.put(DB_KEY_BOOKMARK_WORK_DIR, "");
283  bookmarkValues.put(DB_KEY_BOOKMARK_ASYNC_CHANNEL, 1);
284  bookmarkValues.put(DB_KEY_BOOKMARK_ASYNC_UPDATE, 1);
285  bookmarkValues.put(DB_KEY_BOOKMARK_CONSOLE_MODE, 0);
286  bookmarkValues.put(DB_KEY_BOOKMARK_DEBUG_LEVEL, "INFO");
287 
288  db.insert(DB_TABLE_BOOKMARK, null, bookmarkValues);
289  }
290 
291  @Override public void onCreate(SQLiteDatabase db)
292  {
293  createDB(db);
294  insertDefault(db);
295  }
296 
297  private String getManualBookmarksCreationString()
298  {
299  return ("CREATE TABLE IF NOT EXISTS " + DB_TABLE_BOOKMARK + " (" + ID +
300  " INTEGER PRIMARY KEY, " + DB_KEY_BOOKMARK_LABEL + " TEXT NOT NULL, " +
301  DB_KEY_BOOKMARK_HOSTNAME + " TEXT NOT NULL, " + DB_KEY_BOOKMARK_USERNAME +
302  " TEXT NOT NULL, " + DB_KEY_BOOKMARK_PASSWORD + " TEXT, " + DB_KEY_BOOKMARK_DOMAIN +
303  " TEXT, " + DB_KEY_BOOKMARK_PORT + " TEXT, " + DB_KEY_SCREEN_SETTINGS +
304  " INTEGER NOT NULL, " + DB_KEY_PERFORMANCE_FLAGS + " INTEGER NOT NULL, "
305 
306  + DB_KEY_BOOKMARK_GW_ENABLE + " INTEGER DEFAULT 0, " + DB_KEY_BOOKMARK_GW_HOSTNAME +
307  " TEXT, " + DB_KEY_BOOKMARK_GW_PORT + " INTEGER DEFAULT 443, " +
308  DB_KEY_BOOKMARK_GW_USERNAME + " TEXT, " + DB_KEY_BOOKMARK_GW_PASSWORD + " TEXT, " +
309  DB_KEY_BOOKMARK_GW_DOMAIN + " TEXT, "
310 
311  + DB_KEY_BOOKMARK_3G_ENABLE + " INTEGER DEFAULT 0, " + DB_KEY_SCREEN_SETTINGS_3G +
312  " INTEGER NOT NULL, " + DB_KEY_PERFORMANCE_FLAGS_3G + " INTEGER NOT NULL, " +
313  DB_KEY_BOOKMARK_REDIRECT_SDCARD + " INTEGER DEFAULT 0, " +
314  DB_KEY_BOOKMARK_REDIRECT_SOUND + " INTEGER DEFAULT 0, " +
315  DB_KEY_BOOKMARK_REDIRECT_MICROPHONE + " INTEGER DEFAULT 0, " +
316  DB_KEY_BOOKMARK_SECURITY + " INTEGER, " + DB_KEY_BOOKMARK_REMOTE_PROGRAM +
317  " TEXT, " + DB_KEY_BOOKMARK_WORK_DIR + " TEXT, " + DB_KEY_BOOKMARK_ASYNC_CHANNEL +
318  " INTEGER DEFAULT 0, " + DB_KEY_BOOKMARK_ASYNC_UPDATE + " INTEGER DEFAULT 0, " +
319  DB_KEY_BOOKMARK_CONSOLE_MODE + " INTEGER, " + DB_KEY_BOOKMARK_DEBUG_LEVEL +
320  " TEXT DEFAULT 'INFO', "
321 
322  + "FOREIGN KEY(" + DB_KEY_SCREEN_SETTINGS + ") REFERENCES " + DB_TABLE_SCREEN +
323  "(" + ID + "), "
324  + "FOREIGN KEY(" + DB_KEY_PERFORMANCE_FLAGS + ") REFERENCES " +
325  DB_TABLE_PERFORMANCE + "(" + ID + "), "
326  + "FOREIGN KEY(" + DB_KEY_SCREEN_SETTINGS_3G + ") REFERENCES " + DB_TABLE_SCREEN +
327  "(" + ID + "), "
328  + "FOREIGN KEY(" + DB_KEY_PERFORMANCE_FLAGS_3G + ") REFERENCES " +
329  DB_TABLE_PERFORMANCE + "(" + ID + ") "
330 
331  + ");");
332  }
333 
334  private void recreateDB(SQLiteDatabase db)
335  {
336  for (String table : DB_TABLES)
337  {
338  final String query = "DROP TABLE IF EXISTS '" + table + "'";
339  db.execSQL(query);
340  }
341  onCreate(db);
342  }
343 
344  private void upgradeDB(SQLiteDatabase db)
345  {
346  db.beginTransaction();
347  try
348  {
349  /* Back up old tables. */
350  dropOldTables(db);
351  backupTables(db);
352  createDB(db);
353  upgradeTables(db);
354 
355  db.setTransactionSuccessful();
356  }
357  finally
358  {
359  db.endTransaction();
360  dropOldTables(db);
361  }
362  }
363 
364  private void downgradeDB(SQLiteDatabase db)
365  {
366  db.beginTransaction();
367  try
368  {
369  /* Back up old tables. */
370  dropOldTables(db);
371  backupTables(db);
372  createDB(db);
373  downgradeTables(db);
374 
375  db.setTransactionSuccessful();
376  }
377  finally
378  {
379  db.endTransaction();
380  dropOldTables(db);
381  }
382  }
383 
384  // from
385  // http://stackoverflow.com/questions/3424156/upgrade-sqlite-database-from-one-version-to-another
386  @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
387  {
388  switch (oldVersion)
389  {
390  case 0:
391  case 1:
392  case 2:
393  case 3:
394  case 4:
395  case 5:
396  case 6:
397  case 7:
398  case 8:
399  case 9:
400  upgradeDB(db);
401  break;
402  default:
403  recreateDB(db);
404  break;
405  }
406  }
407 
408  @Override public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)
409  {
410  downgradeDB(db);
411  }
412 }