android: Fix database update from older versions
[strongswan.git] / src / frontends / android / app / src / main / java / org / strongswan / android / data / VpnProfileDataSource.java
1 /*
2 * Copyright (C) 2012-2017 Tobias Brunner
3 * Copyright (C) 2012 Giuliano Grassi
4 * Copyright (C) 2012 Ralf Sager
5 * HSR Hochschule fuer Technik Rapperswil
6 *
7 * This program is free software; you can redistribute it and/or modify it
8 * under the terms of the GNU General Public License as published by the
9 * Free Software Foundation; either version 2 of the License, or (at your
10 * option) any later version. See <http://www.fsf.org/copyleft/gpl.txt>.
11 *
12 * This program is distributed in the hope that it will be useful, but
13 * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
14 * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
15 * for more details.
16 */
17
18 package org.strongswan.android.data;
19
20 import android.content.ContentValues;
21 import android.content.Context;
22 import android.database.Cursor;
23 import android.database.SQLException;
24 import android.database.sqlite.SQLiteDatabase;
25 import android.database.sqlite.SQLiteOpenHelper;
26 import android.database.sqlite.SQLiteQueryBuilder;
27 import android.util.Log;
28
29 import java.util.ArrayList;
30 import java.util.List;
31 import java.util.UUID;
32
33 public class VpnProfileDataSource
34 {
35 private static final String TAG = VpnProfileDataSource.class.getSimpleName();
36 public static final String KEY_ID = "_id";
37 public static final String KEY_UUID = "_uuid";
38 public static final String KEY_NAME = "name";
39 public static final String KEY_GATEWAY = "gateway";
40 public static final String KEY_VPN_TYPE = "vpn_type";
41 public static final String KEY_USERNAME = "username";
42 public static final String KEY_PASSWORD = "password";
43 public static final String KEY_CERTIFICATE = "certificate";
44 public static final String KEY_USER_CERTIFICATE = "user_certificate";
45 public static final String KEY_MTU = "mtu";
46 public static final String KEY_PORT = "port";
47 public static final String KEY_SPLIT_TUNNELING = "split_tunneling";
48 public static final String KEY_LOCAL_ID = "local_id";
49 public static final String KEY_REMOTE_ID = "remote_id";
50 public static final String KEY_EXCLUDED_SUBNETS = "excluded_subnets";
51 public static final String KEY_INCLUDED_SUBNETS = "included_subnets";
52 public static final String KEY_SELECTED_APPS = "selected_apps";
53 public static final String KEY_SELECTED_APPS_LIST = "selected_apps_list";
54 public static final String KEY_NAT_KEEPALIVE = "nat_keepalive";
55 public static final String KEY_FLAGS = "flags";
56
57 private DatabaseHelper mDbHelper;
58 private SQLiteDatabase mDatabase;
59 private final Context mContext;
60
61 private static final String DATABASE_NAME = "strongswan.db";
62 private static final String TABLE_VPNPROFILE = "vpnprofile";
63
64 private static final int DATABASE_VERSION = 14;
65
66 public static final DbColumn[] COLUMNS = new DbColumn[] {
67 new DbColumn(KEY_ID, "INTEGER PRIMARY KEY AUTOINCREMENT", 1),
68 new DbColumn(KEY_UUID, "TEXT UNIQUE", 9),
69 new DbColumn(KEY_NAME, "TEXT NOT NULL", 1),
70 new DbColumn(KEY_GATEWAY, "TEXT NOT NULL", 1),
71 new DbColumn(KEY_VPN_TYPE, "TEXT NOT NULL", 3),
72 new DbColumn(KEY_USERNAME, "TEXT", 1),
73 new DbColumn(KEY_PASSWORD, "TEXT", 1),
74 new DbColumn(KEY_CERTIFICATE, "TEXT", 1),
75 new DbColumn(KEY_USER_CERTIFICATE, "TEXT", 2),
76 new DbColumn(KEY_MTU, "INTEGER", 5),
77 new DbColumn(KEY_PORT, "INTEGER", 5),
78 new DbColumn(KEY_SPLIT_TUNNELING, "INTEGER", 7),
79 new DbColumn(KEY_LOCAL_ID, "TEXT", 8),
80 new DbColumn(KEY_REMOTE_ID, "TEXT", 8),
81 new DbColumn(KEY_EXCLUDED_SUBNETS, "TEXT", 10),
82 new DbColumn(KEY_INCLUDED_SUBNETS, "TEXT", 11),
83 new DbColumn(KEY_SELECTED_APPS, "INTEGER", 12),
84 new DbColumn(KEY_SELECTED_APPS_LIST, "TEXT", 12),
85 new DbColumn(KEY_NAT_KEEPALIVE, "INTEGER", 13),
86 new DbColumn(KEY_FLAGS, "INTEGER", 14),
87 };
88
89 private static final String[] ALL_COLUMNS = getColumns(DATABASE_VERSION);
90
91 private static String getDatabaseCreate(int version)
92 {
93 boolean first = true;
94 StringBuilder create = new StringBuilder("CREATE TABLE ");
95 create.append(TABLE_VPNPROFILE);
96 create.append(" (");
97 for (DbColumn column : COLUMNS)
98 {
99 if (column.Since <= version)
100 {
101 if (!first)
102 {
103 create.append(",");
104 }
105 first = false;
106 create.append(column.Name);
107 create.append(" ");
108 create.append(column.Type);
109 }
110 }
111 create.append(");");
112 return create.toString();
113 }
114
115 private static String[] getColumns(int version)
116 {
117 ArrayList<String> columns = new ArrayList<>();
118 for (DbColumn column : COLUMNS)
119 {
120 if (column.Since <= version)
121 {
122 columns.add(column.Name);
123 }
124 }
125 return columns.toArray(new String[0]);
126 }
127
128 private static class DatabaseHelper extends SQLiteOpenHelper
129 {
130 public DatabaseHelper(Context context)
131 {
132 super(context, DATABASE_NAME, null, DATABASE_VERSION);
133 }
134
135 @Override
136 public void onCreate(SQLiteDatabase database)
137 {
138 database.execSQL(getDatabaseCreate(DATABASE_VERSION));
139 }
140
141 @Override
142 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
143 {
144 Log.w(TAG, "Upgrading database from version " + oldVersion +
145 " to " + newVersion);
146 if (oldVersion < 2)
147 {
148 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_USER_CERTIFICATE +
149 " TEXT;");
150 }
151 if (oldVersion < 3)
152 {
153 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_VPN_TYPE +
154 " TEXT DEFAULT '';");
155 }
156 if (oldVersion < 4)
157 { /* remove NOT NULL constraint from username column */
158 updateColumns(db, 4);
159 }
160 if (oldVersion < 5)
161 {
162 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_MTU +
163 " INTEGER;");
164 }
165 if (oldVersion < 6)
166 {
167 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_PORT +
168 " INTEGER;");
169 }
170 if (oldVersion < 7)
171 {
172 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_SPLIT_TUNNELING +
173 " INTEGER;");
174 }
175 if (oldVersion < 8)
176 {
177 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_LOCAL_ID +
178 " TEXT;");
179 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_REMOTE_ID +
180 " TEXT;");
181 }
182 if (oldVersion < 9)
183 {
184 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_UUID +
185 " TEXT;");
186 updateColumns(db, 9);
187 }
188 if (oldVersion < 10)
189 {
190 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_EXCLUDED_SUBNETS +
191 " TEXT;");
192 }
193 if (oldVersion < 11)
194 {
195 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_INCLUDED_SUBNETS +
196 " TEXT;");
197 }
198 if (oldVersion < 12)
199 {
200 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_SELECTED_APPS +
201 " INTEGER;");
202 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_SELECTED_APPS_LIST +
203 " TEXT;");
204 }
205 if (oldVersion < 13)
206 {
207 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_NAT_KEEPALIVE +
208 " INTEGER;");
209 }
210 if (oldVersion < 14)
211 {
212 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_FLAGS +
213 " INTEGER;");
214 }
215 }
216
217 private void updateColumns(SQLiteDatabase db, int version)
218 {
219 db.beginTransaction();
220 try
221 {
222 db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " RENAME TO tmp_" + TABLE_VPNPROFILE + ";");
223 db.execSQL(getDatabaseCreate(version));
224 StringBuilder insert = new StringBuilder("INSERT INTO " + TABLE_VPNPROFILE + " SELECT ");
225 SQLiteQueryBuilder.appendColumns(insert, getColumns(version));
226 db.execSQL(insert.append(" FROM tmp_" + TABLE_VPNPROFILE + ";").toString());
227 db.execSQL("DROP TABLE tmp_" + TABLE_VPNPROFILE + ";");
228 db.setTransactionSuccessful();
229 }
230 finally
231 {
232 db.endTransaction();
233 }
234 }
235 }
236
237 /**
238 * Construct a new VPN profile data source. The context is used to
239 * open/create the database.
240 * @param context context used to access the database
241 */
242 public VpnProfileDataSource(Context context)
243 {
244 this.mContext = context;
245 }
246
247 /**
248 * Open the VPN profile data source. The database is automatically created
249 * if it does not yet exist. If that fails an exception is thrown.
250 * @return itself (allows to chain initialization calls)
251 * @throws SQLException if the database could not be opened or created
252 */
253 public VpnProfileDataSource open() throws SQLException
254 {
255 if (mDbHelper == null)
256 {
257 mDbHelper = new DatabaseHelper(mContext);
258 mDatabase = mDbHelper.getWritableDatabase();
259 }
260 return this;
261 }
262
263 /**
264 * Close the data source.
265 */
266 public void close()
267 {
268 if (mDbHelper != null)
269 {
270 mDbHelper.close();
271 mDbHelper = null;
272 }
273 }
274
275 /**
276 * Insert the given VPN profile into the database. On success the Id of
277 * the object is updated and the object returned.
278 *
279 * @param profile the profile to add
280 * @return the added VPN profile or null, if failed
281 */
282 public VpnProfile insertProfile(VpnProfile profile)
283 {
284 ContentValues values = ContentValuesFromVpnProfile(profile);
285 long insertId = mDatabase.insert(TABLE_VPNPROFILE, null, values);
286 if (insertId == -1)
287 {
288 return null;
289 }
290 profile.setId(insertId);
291 return profile;
292 }
293
294 /**
295 * Updates the given VPN profile in the database.
296 * @param profile the profile to update
297 * @return true if update succeeded, false otherwise
298 */
299 public boolean updateVpnProfile(VpnProfile profile)
300 {
301 long id = profile.getId();
302 ContentValues values = ContentValuesFromVpnProfile(profile);
303 return mDatabase.update(TABLE_VPNPROFILE, values, KEY_ID + " = " + id, null) > 0;
304 }
305
306 /**
307 * Delete the given VPN profile from the database.
308 * @param profile the profile to delete
309 * @return true if deleted, false otherwise
310 */
311 public boolean deleteVpnProfile(VpnProfile profile)
312 {
313 long id = profile.getId();
314 return mDatabase.delete(TABLE_VPNPROFILE, KEY_ID + " = " + id, null) > 0;
315 }
316
317 /**
318 * Get a single VPN profile from the database.
319 * @param id the ID of the VPN profile
320 * @return the profile or null, if not found
321 */
322 public VpnProfile getVpnProfile(long id)
323 {
324 VpnProfile profile = null;
325 Cursor cursor = mDatabase.query(TABLE_VPNPROFILE, ALL_COLUMNS,
326 KEY_ID + "=" + id, null, null, null, null);
327 if (cursor.moveToFirst())
328 {
329 profile = VpnProfileFromCursor(cursor);
330 }
331 cursor.close();
332 return profile;
333 }
334
335 /**
336 * Get a single VPN profile from the database by its UUID.
337 * @param uuid the UUID of the VPN profile
338 * @return the profile or null, if not found
339 */
340 public VpnProfile getVpnProfile(UUID uuid)
341 {
342 VpnProfile profile = null;
343 Cursor cursor = mDatabase.query(TABLE_VPNPROFILE, ALL_COLUMNS,
344 KEY_UUID + "='" + uuid.toString() + "'", null, null, null, null);
345 if (cursor.moveToFirst())
346 {
347 profile = VpnProfileFromCursor(cursor);
348 }
349 cursor.close();
350 return profile;
351 }
352
353 /**
354 * Get a list of all VPN profiles stored in the database.
355 * @return list of VPN profiles
356 */
357 public List<VpnProfile> getAllVpnProfiles()
358 {
359 List<VpnProfile> vpnProfiles = new ArrayList<VpnProfile>();
360
361 Cursor cursor = mDatabase.query(TABLE_VPNPROFILE, ALL_COLUMNS, null, null, null, null, null);
362 cursor.moveToFirst();
363 while (!cursor.isAfterLast())
364 {
365 VpnProfile vpnProfile = VpnProfileFromCursor(cursor);
366 vpnProfiles.add(vpnProfile);
367 cursor.moveToNext();
368 }
369 cursor.close();
370 return vpnProfiles;
371 }
372
373 private VpnProfile VpnProfileFromCursor(Cursor cursor)
374 {
375 VpnProfile profile = new VpnProfile();
376 profile.setId(cursor.getLong(cursor.getColumnIndex(KEY_ID)));
377 profile.setUUID(getUUID(cursor, cursor.getColumnIndex(KEY_UUID)));
378 profile.setName(cursor.getString(cursor.getColumnIndex(KEY_NAME)));
379 profile.setGateway(cursor.getString(cursor.getColumnIndex(KEY_GATEWAY)));
380 profile.setVpnType(VpnType.fromIdentifier(cursor.getString(cursor.getColumnIndex(KEY_VPN_TYPE))));
381 profile.setUsername(cursor.getString(cursor.getColumnIndex(KEY_USERNAME)));
382 profile.setPassword(cursor.getString(cursor.getColumnIndex(KEY_PASSWORD)));
383 profile.setCertificateAlias(cursor.getString(cursor.getColumnIndex(KEY_CERTIFICATE)));
384 profile.setUserCertificateAlias(cursor.getString(cursor.getColumnIndex(KEY_USER_CERTIFICATE)));
385 profile.setMTU(getInt(cursor, cursor.getColumnIndex(KEY_MTU)));
386 profile.setPort(getInt(cursor, cursor.getColumnIndex(KEY_PORT)));
387 profile.setSplitTunneling(getInt(cursor, cursor.getColumnIndex(KEY_SPLIT_TUNNELING)));
388 profile.setLocalId(cursor.getString(cursor.getColumnIndex(KEY_LOCAL_ID)));
389 profile.setRemoteId(cursor.getString(cursor.getColumnIndex(KEY_REMOTE_ID)));
390 profile.setExcludedSubnets(cursor.getString(cursor.getColumnIndex(KEY_EXCLUDED_SUBNETS)));
391 profile.setIncludedSubnets(cursor.getString(cursor.getColumnIndex(KEY_INCLUDED_SUBNETS)));
392 profile.setSelectedAppsHandling(getInt(cursor, cursor.getColumnIndex(KEY_SELECTED_APPS)));
393 profile.setSelectedApps(cursor.getString(cursor.getColumnIndex(KEY_SELECTED_APPS_LIST)));
394 profile.setNATKeepAlive(getInt(cursor, cursor.getColumnIndex(KEY_NAT_KEEPALIVE)));
395 profile.setFlags(getInt(cursor, cursor.getColumnIndex(KEY_FLAGS)));
396 return profile;
397 }
398
399 private ContentValues ContentValuesFromVpnProfile(VpnProfile profile)
400 {
401 ContentValues values = new ContentValues();
402 values.put(KEY_UUID, profile.getUUID() != null ? profile.getUUID().toString() : null);
403 values.put(KEY_NAME, profile.getName());
404 values.put(KEY_GATEWAY, profile.getGateway());
405 values.put(KEY_VPN_TYPE, profile.getVpnType().getIdentifier());
406 values.put(KEY_USERNAME, profile.getUsername());
407 values.put(KEY_PASSWORD, profile.getPassword());
408 values.put(KEY_CERTIFICATE, profile.getCertificateAlias());
409 values.put(KEY_USER_CERTIFICATE, profile.getUserCertificateAlias());
410 values.put(KEY_MTU, profile.getMTU());
411 values.put(KEY_PORT, profile.getPort());
412 values.put(KEY_SPLIT_TUNNELING, profile.getSplitTunneling());
413 values.put(KEY_LOCAL_ID, profile.getLocalId());
414 values.put(KEY_REMOTE_ID, profile.getRemoteId());
415 values.put(KEY_EXCLUDED_SUBNETS, profile.getExcludedSubnets());
416 values.put(KEY_INCLUDED_SUBNETS, profile.getIncludedSubnets());
417 values.put(KEY_SELECTED_APPS, profile.getSelectedAppsHandling().getValue());
418 values.put(KEY_SELECTED_APPS_LIST, profile.getSelectedApps());
419 values.put(KEY_NAT_KEEPALIVE, profile.getNATKeepAlive());
420 values.put(KEY_FLAGS, profile.getFlags());
421 return values;
422 }
423
424 private Integer getInt(Cursor cursor, int columnIndex)
425 {
426 return cursor.isNull(columnIndex) ? null : cursor.getInt(columnIndex);
427 }
428
429 private UUID getUUID(Cursor cursor, int columnIndex)
430 {
431 try
432 {
433 return cursor.isNull(columnIndex) ? null : UUID.fromString(cursor.getString(columnIndex));
434 }
435 catch (Exception e)
436 {
437 return null;
438 }
439 }
440
441 private static class DbColumn
442 {
443 public final String Name;
444 public final String Type;
445 public final Integer Since;
446
447 public DbColumn(String name, String type, Integer since)
448 {
449 Name = name;
450 Type = type;
451 Since = since;
452 }
453 }
454 }