From 802047cae8f66b59f806a574671a4dad18f25ed3 Mon Sep 17 00:00:00 2001 From: Markus Pfeiffer Date: Tue, 21 Nov 2023 15:37:22 +0100 Subject: [PATCH] android: Move database helper into separate class Reduce strong coupling between database helper and VPN profiles, to prepare for the addition of other tables. --- .../android/data/DatabaseHelper.java | 261 ++++++++++++++++++ .../android/data/VpnProfileSqlDataSource.java | 244 +--------------- 2 files changed, 268 insertions(+), 237 deletions(-) create mode 100644 src/frontends/android/app/src/main/java/org/strongswan/android/data/DatabaseHelper.java diff --git a/src/frontends/android/app/src/main/java/org/strongswan/android/data/DatabaseHelper.java b/src/frontends/android/app/src/main/java/org/strongswan/android/data/DatabaseHelper.java new file mode 100644 index 0000000000..c0416d8ff6 --- /dev/null +++ b/src/frontends/android/app/src/main/java/org/strongswan/android/data/DatabaseHelper.java @@ -0,0 +1,261 @@ +/* + * Copyright (C) 2023 Relution GmbH + * Copyright (C) 2012-2019 Tobias Brunner + * Copyright (C) 2012 Giuliano Grassi + * Copyright (C) 2012 Ralf Sager + * + * Copyright (C) secunet Security Networks AG + * + * This program is free software; you can redistribute it and/or modify it + * under the terms of the GNU General Public License as published by the + * Free Software Foundation; either version 2 of the License, or (at your + * option) any later version. See . + * + * This program is distributed in the hope that it will be useful, but + * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY + * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License + * for more details. + */ + +package org.strongswan.android.data; + +import android.content.ContentValues; +import android.content.Context; +import android.database.Cursor; +import android.database.sqlite.SQLiteDatabase; +import android.database.sqlite.SQLiteOpenHelper; +import android.database.sqlite.SQLiteQueryBuilder; +import android.util.Log; + +import java.util.ArrayList; +import java.util.UUID; + +public class DatabaseHelper extends SQLiteOpenHelper +{ + private static final String TAG = DatabaseHelper.class.getSimpleName(); + + private static final String DATABASE_NAME = "strongswan.db"; + static final String TABLE_VPNPROFILE = "vpnprofile"; + + private static final int DATABASE_VERSION = 17; + + private static final DbColumn[] COLUMNS = new DbColumn[]{ + new DbColumn(VpnProfileDataSource.KEY_ID, "INTEGER PRIMARY KEY AUTOINCREMENT", 1), + new DbColumn(VpnProfileDataSource.KEY_UUID, "TEXT UNIQUE", 9), + new DbColumn(VpnProfileDataSource.KEY_NAME, "TEXT NOT NULL", 1), + new DbColumn(VpnProfileDataSource.KEY_GATEWAY, "TEXT NOT NULL", 1), + new DbColumn(VpnProfileDataSource.KEY_VPN_TYPE, "TEXT NOT NULL", 3), + new DbColumn(VpnProfileDataSource.KEY_USERNAME, "TEXT", 1), + new DbColumn(VpnProfileDataSource.KEY_PASSWORD, "TEXT", 1), + new DbColumn(VpnProfileDataSource.KEY_CERTIFICATE, "TEXT", 1), + new DbColumn(VpnProfileDataSource.KEY_USER_CERTIFICATE, "TEXT", 2), + new DbColumn(VpnProfileDataSource.KEY_MTU, "INTEGER", 5), + new DbColumn(VpnProfileDataSource.KEY_PORT, "INTEGER", 6), + new DbColumn(VpnProfileDataSource.KEY_SPLIT_TUNNELING, "INTEGER", 7), + new DbColumn(VpnProfileDataSource.KEY_LOCAL_ID, "TEXT", 8), + new DbColumn(VpnProfileDataSource.KEY_REMOTE_ID, "TEXT", 8), + new DbColumn(VpnProfileDataSource.KEY_EXCLUDED_SUBNETS, "TEXT", 10), + new DbColumn(VpnProfileDataSource.KEY_INCLUDED_SUBNETS, "TEXT", 11), + new DbColumn(VpnProfileDataSource.KEY_SELECTED_APPS, "INTEGER", 12), + new DbColumn(VpnProfileDataSource.KEY_SELECTED_APPS_LIST, "TEXT", 12), + new DbColumn(VpnProfileDataSource.KEY_NAT_KEEPALIVE, "INTEGER", 13), + new DbColumn(VpnProfileDataSource.KEY_FLAGS, "INTEGER", 14), + new DbColumn(VpnProfileDataSource.KEY_IKE_PROPOSAL, "TEXT", 15), + new DbColumn(VpnProfileDataSource.KEY_ESP_PROPOSAL, "TEXT", 15), + new DbColumn(VpnProfileDataSource.KEY_DNS_SERVERS, "TEXT", 17), + }; + + DatabaseHelper(Context context) + { + super(context, DATABASE_NAME, null, DATABASE_VERSION); + } + + @Override + public void onCreate(SQLiteDatabase database) + { + database.execSQL(getDatabaseCreate(DATABASE_VERSION)); + } + + @Override + public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) + { + Log.w(TAG, "Upgrading database from version " + oldVersion + + " to " + newVersion); + if (oldVersion < 2) + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_USER_CERTIFICATE + + " TEXT;"); + } + if (oldVersion < 3) + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_VPN_TYPE + + " TEXT DEFAULT '';"); + } + if (oldVersion < 4) + { /* remove NOT NULL constraint from username column */ + updateColumns(db, 4); + } + if (oldVersion < 5) + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_MTU + + " INTEGER;"); + } + if (oldVersion < 6) + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_PORT + + " INTEGER;"); + } + if (oldVersion < 7) + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_SPLIT_TUNNELING + + " INTEGER;"); + } + if (oldVersion < 8) + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_LOCAL_ID + + " TEXT;"); + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_REMOTE_ID + + " TEXT;"); + } + if (oldVersion < 9) + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_UUID + + " TEXT;"); + updateColumns(db, 9); + } + if (oldVersion < 10) + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_EXCLUDED_SUBNETS + + " TEXT;"); + } + if (oldVersion < 11) + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_INCLUDED_SUBNETS + + " TEXT;"); + } + if (oldVersion < 12) + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_SELECTED_APPS + + " INTEGER;"); + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_SELECTED_APPS_LIST + + " TEXT;"); + } + if (oldVersion < 13) + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_NAT_KEEPALIVE + + " INTEGER;"); + } + if (oldVersion < 14) + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_FLAGS + + " INTEGER;"); + } + if (oldVersion < 15) + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_IKE_PROPOSAL + + " TEXT;"); + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_ESP_PROPOSAL + + " TEXT;"); + } + if (oldVersion < 16) + { /* add a UUID to all entries that haven't one yet */ + db.beginTransaction(); + try + { + Cursor cursor = db.query(TABLE_VPNPROFILE, getColumns(16), VpnProfileDataSource.KEY_UUID + " is NULL", null, null, null, null); + for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) + { + ContentValues values = new ContentValues(); + values.put(VpnProfileDataSource.KEY_UUID, UUID.randomUUID().toString()); + db.update(TABLE_VPNPROFILE, values, VpnProfileDataSource.KEY_ID + " = " + cursor.getLong(cursor.getColumnIndexOrThrow(VpnProfileDataSource.KEY_ID)), null); + } + cursor.close(); + db.setTransactionSuccessful(); + } + finally + { + db.endTransaction(); + } + } + if (oldVersion < 17) + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + VpnProfileDataSource.KEY_DNS_SERVERS + + " TEXT;"); + } + } + + public String[] getAllColumns() + { + return getColumns(DATABASE_VERSION); + } + + private void updateColumns(SQLiteDatabase db, int version) + { + db.beginTransaction(); + try + { + db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " RENAME TO tmp_" + TABLE_VPNPROFILE + ";"); + db.execSQL(getDatabaseCreate(version)); + StringBuilder insert = new StringBuilder("INSERT INTO " + TABLE_VPNPROFILE + " SELECT "); + SQLiteQueryBuilder.appendColumns(insert, getColumns(version)); + db.execSQL(insert.append(" FROM tmp_" + TABLE_VPNPROFILE + ";").toString()); + db.execSQL("DROP TABLE tmp_" + TABLE_VPNPROFILE + ";"); + db.setTransactionSuccessful(); + } + finally + { + db.endTransaction(); + } + } + + private String getDatabaseCreate(int version) + { + boolean first = true; + StringBuilder create = new StringBuilder("CREATE TABLE "); + create.append(TABLE_VPNPROFILE); + create.append(" ("); + for (DbColumn column : COLUMNS) + { + if (column.Since <= version) + { + if (!first) + { + create.append(","); + } + first = false; + create.append(column.Name); + create.append(" "); + create.append(column.Type); + } + } + create.append(");"); + return create.toString(); + } + + private String[] getColumns(int version) + { + ArrayList columns = new ArrayList<>(); + for (DbColumn column : COLUMNS) + { + if (column.Since <= version) + { + columns.add(column.Name); + } + } + return columns.toArray(new String[0]); + } + + private static class DbColumn + { + public final String Name; + public final String Type; + public final Integer Since; + + public DbColumn(String name, String type, Integer since) + { + Name = name; + Type = type; + Since = since; + } + } +} diff --git a/src/frontends/android/app/src/main/java/org/strongswan/android/data/VpnProfileSqlDataSource.java b/src/frontends/android/app/src/main/java/org/strongswan/android/data/VpnProfileSqlDataSource.java index 5b1f9ad269..008e7dd55b 100644 --- a/src/frontends/android/app/src/main/java/org/strongswan/android/data/VpnProfileSqlDataSource.java +++ b/src/frontends/android/app/src/main/java/org/strongswan/android/data/VpnProfileSqlDataSource.java @@ -23,9 +23,6 @@ import android.content.Context; import android.database.Cursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; -import android.database.sqlite.SQLiteOpenHelper; -import android.database.sqlite.SQLiteQueryBuilder; -import android.util.Log; import java.util.ArrayList; import java.util.List; @@ -33,223 +30,10 @@ import java.util.UUID; public class VpnProfileSqlDataSource implements VpnProfileDataSource { - private static final String TAG = VpnProfileSqlDataSource.class.getSimpleName(); - - private static final DbColumn[] COLUMNS = new VpnProfileSqlDataSource.DbColumn[]{ - new VpnProfileSqlDataSource.DbColumn(KEY_ID, "INTEGER PRIMARY KEY AUTOINCREMENT", 1), - new VpnProfileSqlDataSource.DbColumn(KEY_UUID, "TEXT UNIQUE", 9), - new VpnProfileSqlDataSource.DbColumn(KEY_NAME, "TEXT NOT NULL", 1), - new VpnProfileSqlDataSource.DbColumn(KEY_GATEWAY, "TEXT NOT NULL", 1), - new VpnProfileSqlDataSource.DbColumn(KEY_VPN_TYPE, "TEXT NOT NULL", 3), - new VpnProfileSqlDataSource.DbColumn(KEY_USERNAME, "TEXT", 1), - new VpnProfileSqlDataSource.DbColumn(KEY_PASSWORD, "TEXT", 1), - new VpnProfileSqlDataSource.DbColumn(KEY_CERTIFICATE, "TEXT", 1), - new VpnProfileSqlDataSource.DbColumn(KEY_USER_CERTIFICATE, "TEXT", 2), - new VpnProfileSqlDataSource.DbColumn(KEY_MTU, "INTEGER", 5), - new VpnProfileSqlDataSource.DbColumn(KEY_PORT, "INTEGER", 6), - new VpnProfileSqlDataSource.DbColumn(KEY_SPLIT_TUNNELING, "INTEGER", 7), - new VpnProfileSqlDataSource.DbColumn(KEY_LOCAL_ID, "TEXT", 8), - new VpnProfileSqlDataSource.DbColumn(KEY_REMOTE_ID, "TEXT", 8), - new VpnProfileSqlDataSource.DbColumn(KEY_EXCLUDED_SUBNETS, "TEXT", 10), - new VpnProfileSqlDataSource.DbColumn(KEY_INCLUDED_SUBNETS, "TEXT", 11), - new VpnProfileSqlDataSource.DbColumn(KEY_SELECTED_APPS, "INTEGER", 12), - new VpnProfileSqlDataSource.DbColumn(KEY_SELECTED_APPS_LIST, "TEXT", 12), - new VpnProfileSqlDataSource.DbColumn(KEY_NAT_KEEPALIVE, "INTEGER", 13), - new VpnProfileSqlDataSource.DbColumn(KEY_FLAGS, "INTEGER", 14), - new VpnProfileSqlDataSource.DbColumn(KEY_IKE_PROPOSAL, "TEXT", 15), - new VpnProfileSqlDataSource.DbColumn(KEY_ESP_PROPOSAL, "TEXT", 15), - new VpnProfileSqlDataSource.DbColumn(KEY_DNS_SERVERS, "TEXT", 17), - }; - private DatabaseHelper mDbHelper; private SQLiteDatabase mDatabase; private final Context mContext; - private static final String DATABASE_NAME = "strongswan.db"; - private static final String TABLE_VPNPROFILE = "vpnprofile"; - - private static final int DATABASE_VERSION = 17; - - private static final String[] ALL_COLUMNS = getColumns(DATABASE_VERSION); - - private static String getDatabaseCreate(int version) - { - boolean first = true; - StringBuilder create = new StringBuilder("CREATE TABLE "); - create.append(TABLE_VPNPROFILE); - create.append(" ("); - for (DbColumn column : COLUMNS) - { - if (column.Since <= version) - { - if (!first) - { - create.append(","); - } - first = false; - create.append(column.Name); - create.append(" "); - create.append(column.Type); - } - } - create.append(");"); - return create.toString(); - } - - private static String[] getColumns(int version) - { - ArrayList columns = new ArrayList<>(); - for (DbColumn column : COLUMNS) - { - if (column.Since <= version) - { - columns.add(column.Name); - } - } - return columns.toArray(new String[0]); - } - - private static class DatabaseHelper extends SQLiteOpenHelper - { - public DatabaseHelper(Context context) - { - super(context, DATABASE_NAME, null, DATABASE_VERSION); - } - - @Override - public void onCreate(SQLiteDatabase database) - { - database.execSQL(getDatabaseCreate(DATABASE_VERSION)); - } - - @Override - public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) - { - Log.w(TAG, "Upgrading database from version " + oldVersion + - " to " + newVersion); - if (oldVersion < 2) - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_USER_CERTIFICATE + - " TEXT;"); - } - if (oldVersion < 3) - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_VPN_TYPE + - " TEXT DEFAULT '';"); - } - if (oldVersion < 4) - { /* remove NOT NULL constraint from username column */ - updateColumns(db, 4); - } - if (oldVersion < 5) - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_MTU + - " INTEGER;"); - } - if (oldVersion < 6) - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_PORT + - " INTEGER;"); - } - if (oldVersion < 7) - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_SPLIT_TUNNELING + - " INTEGER;"); - } - if (oldVersion < 8) - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_LOCAL_ID + - " TEXT;"); - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_REMOTE_ID + - " TEXT;"); - } - if (oldVersion < 9) - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_UUID + - " TEXT;"); - updateColumns(db, 9); - } - if (oldVersion < 10) - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_EXCLUDED_SUBNETS + - " TEXT;"); - } - if (oldVersion < 11) - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_INCLUDED_SUBNETS + - " TEXT;"); - } - if (oldVersion < 12) - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_SELECTED_APPS + - " INTEGER;"); - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_SELECTED_APPS_LIST + - " TEXT;"); - } - if (oldVersion < 13) - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_NAT_KEEPALIVE + - " INTEGER;"); - } - if (oldVersion < 14) - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_FLAGS + - " INTEGER;"); - } - if (oldVersion < 15) - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_IKE_PROPOSAL + - " TEXT;"); - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_ESP_PROPOSAL + - " TEXT;"); - } - if (oldVersion < 16) - { /* add a UUID to all entries that haven't one yet */ - db.beginTransaction(); - try - { - Cursor cursor = db.query(TABLE_VPNPROFILE, getColumns(16), KEY_UUID + " is NULL", null, null, null, null); - for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) - { - ContentValues values = new ContentValues(); - values.put(KEY_UUID, UUID.randomUUID().toString()); - db.update(TABLE_VPNPROFILE, values, KEY_ID + " = " + cursor.getLong(cursor.getColumnIndexOrThrow(KEY_ID)), null); - } - cursor.close(); - db.setTransactionSuccessful(); - } - finally - { - db.endTransaction(); - } - } - if (oldVersion < 17) - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_DNS_SERVERS + - " TEXT;"); - } - } - - private void updateColumns(SQLiteDatabase db, int version) - { - db.beginTransaction(); - try - { - db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " RENAME TO tmp_" + TABLE_VPNPROFILE + ";"); - db.execSQL(getDatabaseCreate(version)); - StringBuilder insert = new StringBuilder("INSERT INTO " + TABLE_VPNPROFILE + " SELECT "); - SQLiteQueryBuilder.appendColumns(insert, getColumns(version)); - db.execSQL(insert.append(" FROM tmp_" + TABLE_VPNPROFILE + ";").toString()); - db.execSQL("DROP TABLE tmp_" + TABLE_VPNPROFILE + ";"); - db.setTransactionSuccessful(); - } - finally - { - db.endTransaction(); - } - } - } - /** * Construct a new VPN profile data source. The context is used to * open/create the database. @@ -286,7 +70,7 @@ public class VpnProfileSqlDataSource implements VpnProfileDataSource public VpnProfile insertProfile(VpnProfile profile) { ContentValues values = ContentValuesFromVpnProfile(profile); - long insertId = mDatabase.insert(TABLE_VPNPROFILE, null, values); + long insertId = mDatabase.insert(DatabaseHelper.TABLE_VPNPROFILE, null, values); if (insertId == -1) { return null; @@ -301,22 +85,22 @@ public class VpnProfileSqlDataSource implements VpnProfileDataSource { final UUID uuid = profile.getUUID(); ContentValues values = ContentValuesFromVpnProfile(profile); - return mDatabase.update(TABLE_VPNPROFILE, values, KEY_UUID + " = ?", new String[]{uuid.toString()}) > 0; + return mDatabase.update(DatabaseHelper.TABLE_VPNPROFILE, values, KEY_UUID + " = ?", new String[]{uuid.toString()}) > 0; } @Override public boolean deleteVpnProfile(VpnProfile profile) { final UUID uuid = profile.getUUID(); - return mDatabase.delete(TABLE_VPNPROFILE, KEY_UUID + " = ?", new String[]{uuid.toString()}) > 0; + return mDatabase.delete(DatabaseHelper.TABLE_VPNPROFILE, KEY_UUID + " = ?", new String[]{uuid.toString()}) > 0; } @Override public VpnProfile getVpnProfile(UUID uuid) { VpnProfile profile = null; - Cursor cursor = mDatabase.query(TABLE_VPNPROFILE, ALL_COLUMNS, - KEY_UUID + "='" + uuid.toString() + "'", null, null, null, null); + Cursor cursor = mDatabase.query(DatabaseHelper.TABLE_VPNPROFILE, mDbHelper.getAllColumns(), + KEY_UUID + " = ?", new String[]{uuid.toString()}, null, null, null); if (cursor.moveToFirst()) { profile = VpnProfileFromCursor(cursor); @@ -329,9 +113,9 @@ public class VpnProfileSqlDataSource implements VpnProfileDataSource @Override public List getAllVpnProfiles() { - List vpnProfiles = new ArrayList(); + List vpnProfiles = new ArrayList<>(); - Cursor cursor = mDatabase.query(TABLE_VPNPROFILE, ALL_COLUMNS, null, null, null, null, null); + Cursor cursor = mDatabase.query(DatabaseHelper.TABLE_VPNPROFILE, mDbHelper.getAllColumns(), null, null, null, null, null); cursor.moveToFirst(); while (!cursor.isAfterLast()) { @@ -404,18 +188,4 @@ public class VpnProfileSqlDataSource implements VpnProfileDataSource { return cursor.isNull(columnIndex) ? null : cursor.getInt(columnIndex); } - - private static class DbColumn - { - public final String Name; - public final String Type; - public final Integer Since; - - public DbColumn(String name, String type, Integer since) - { - Name = name; - Type = type; - Since = since; - } - } } -- 2.47.2