[thirdparty/strongswan.git] / src / frontends / android / app / src / main / java / org / strongswan / android / data / VpnProfileDataSource.java

/*
 * Copyright (C) 2012-2019 Tobias Brunner
 * Copyright (C) 2012 Giuliano Grassi
 * Copyright (C) 2012 Ralf Sager
 * HSR Hochschule fuer Technik Rapperswil
 *
 * 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 <http://www.fsf.org/copyleft/gpl.txt>.
 *
 * 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.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;
import java.util.UUID;

public class VpnProfileDataSource
{
	private static final String TAG = VpnProfileDataSource.class.getSimpleName();
	public static final String KEY_ID = "_id";
	public static final String KEY_UUID = "_uuid";
	public static final String KEY_NAME = "name";
	public static final String KEY_GATEWAY = "gateway";
	public static final String KEY_VPN_TYPE = "vpn_type";
	public static final String KEY_USERNAME = "username";
	public static final String KEY_PASSWORD = "password";
	public static final String KEY_CERTIFICATE = "certificate";
	public static final String KEY_USER_CERTIFICATE = "user_certificate";
	public static final String KEY_MTU = "mtu";
	public static final String KEY_PORT = "port";
	public static final String KEY_SPLIT_TUNNELING = "split_tunneling";
	public static final String KEY_LOCAL_ID = "local_id";
	public static final String KEY_REMOTE_ID = "remote_id";
	public static final String KEY_EXCLUDED_SUBNETS = "excluded_subnets";
	public static final String KEY_INCLUDED_SUBNETS = "included_subnets";
	public static final String KEY_SELECTED_APPS = "selected_apps";
	public static final String KEY_SELECTED_APPS_LIST = "selected_apps_list";
	public static final String KEY_NAT_KEEPALIVE = "nat_keepalive";
	public static final String KEY_FLAGS = "flags";
	public static final String KEY_IKE_PROPOSAL = "ike_proposal";
	public static final String KEY_ESP_PROPOSAL = "esp_proposal";
	public static final String KEY_DNS_SERVERS = "dns_servers";

	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;

	public static final DbColumn[] COLUMNS = new DbColumn[] {
								new DbColumn(KEY_ID, "INTEGER PRIMARY KEY AUTOINCREMENT", 1),
								new DbColumn(KEY_UUID, "TEXT UNIQUE", 9),
								new DbColumn(KEY_NAME, "TEXT NOT NULL", 1),
								new DbColumn(KEY_GATEWAY, "TEXT NOT NULL", 1),
								new DbColumn(KEY_VPN_TYPE, "TEXT NOT NULL", 3),
								new DbColumn(KEY_USERNAME, "TEXT", 1),
								new DbColumn(KEY_PASSWORD, "TEXT", 1),
								new DbColumn(KEY_CERTIFICATE, "TEXT", 1),
								new DbColumn(KEY_USER_CERTIFICATE, "TEXT", 2),
								new DbColumn(KEY_MTU, "INTEGER", 5),
								new DbColumn(KEY_PORT, "INTEGER", 5),
								new DbColumn(KEY_SPLIT_TUNNELING, "INTEGER", 7),
								new DbColumn(KEY_LOCAL_ID, "TEXT", 8),
								new DbColumn(KEY_REMOTE_ID, "TEXT", 8),
								new DbColumn(KEY_EXCLUDED_SUBNETS, "TEXT", 10),
								new DbColumn(KEY_INCLUDED_SUBNETS, "TEXT", 11),
								new DbColumn(KEY_SELECTED_APPS, "INTEGER", 12),
								new DbColumn(KEY_SELECTED_APPS_LIST, "TEXT", 12),
								new DbColumn(KEY_NAT_KEEPALIVE, "INTEGER", 13),
								new DbColumn(KEY_FLAGS, "INTEGER", 14),
								new DbColumn(KEY_IKE_PROPOSAL, "TEXT", 15),
								new DbColumn(KEY_ESP_PROPOSAL, "TEXT", 15),
								new DbColumn(KEY_DNS_SERVERS, "TEXT", 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<String> 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.getColumnIndex(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.
	 * @param context context used to access the database
	 */
	public VpnProfileDataSource(Context context)
	{
		this.mContext = context;
	}

	/**
	 * Open the VPN profile data source. The database is automatically created
	 * if it does not yet exist. If that fails an exception is thrown.
	 * @return itself (allows to chain initialization calls)
	 * @throws SQLException if the database could not be opened or created
	 */
	public VpnProfileDataSource open() throws SQLException
	{
		if (mDbHelper == null)
		{
			mDbHelper = new DatabaseHelper(mContext);
			mDatabase = mDbHelper.getWritableDatabase();
		}
		return this;
	}

	/**
	 * Close the data source.
	 */
	public void close()
	{
		if (mDbHelper != null)
		{
			mDbHelper.close();
			mDbHelper = null;
		}
	}

	/**
	 * Insert the given VPN profile into the database.  On success the Id of
	 * the object is updated and the object returned.
	 *
	 * @param profile the profile to add
	 * @return the added VPN profile or null, if failed
	 */
	public VpnProfile insertProfile(VpnProfile profile)
	{
		ContentValues values = ContentValuesFromVpnProfile(profile);
		long insertId = mDatabase.insert(TABLE_VPNPROFILE, null, values);
		if (insertId == -1)
		{
			return null;
		}
		profile.setId(insertId);
		return profile;
	}

	/**
	 * Updates the given VPN profile in the database.
	 * @param profile the profile to update
	 * @return true if update succeeded, false otherwise
	 */
	public boolean updateVpnProfile(VpnProfile profile)
	{
		long id = profile.getId();
		ContentValues values = ContentValuesFromVpnProfile(profile);
		return mDatabase.update(TABLE_VPNPROFILE, values, KEY_ID + " = " + id, null) > 0;
	}

	/**
	 * Delete the given VPN profile from the database.
	 * @param profile the profile to delete
	 * @return true if deleted, false otherwise
	 */
	public boolean deleteVpnProfile(VpnProfile profile)
	{
		long id = profile.getId();
		return mDatabase.delete(TABLE_VPNPROFILE, KEY_ID + " = " + id, null) > 0;
	}

	/**
	 * Get a single VPN profile from the database.
	 * @param id the ID of the VPN profile
	 * @return the profile or null, if not found
	 */
	public VpnProfile getVpnProfile(long id)
	{
		VpnProfile profile = null;
		Cursor cursor = mDatabase.query(TABLE_VPNPROFILE, ALL_COLUMNS,
										KEY_ID + "=" + id, null, null, null, null);
		if (cursor.moveToFirst())
		{
			profile = VpnProfileFromCursor(cursor);
		}
		cursor.close();
		return profile;
	}

	/**
	 * Get a single VPN profile from the database by its UUID.
	 * @param uuid the UUID of the VPN profile
	 * @return the profile or null, if not found
	 */
	public VpnProfile getVpnProfile(UUID uuid)
	{
		VpnProfile profile = null;
		Cursor cursor = mDatabase.query(TABLE_VPNPROFILE, ALL_COLUMNS,
										KEY_UUID + "='" + uuid.toString() + "'", null, null, null, null);
		if (cursor.moveToFirst())
		{
			profile = VpnProfileFromCursor(cursor);
		}
		cursor.close();
		return profile;
	}

	/**
	 * Get a single VPN profile from the database by its UUID as String.
	 * @param uuid the UUID of the VPN profile as String
	 * @return the profile or null, if not found
	 */
	public VpnProfile getVpnProfile(String uuid)
	{
		try
		{
			if (uuid != null)
			{
				return getVpnProfile(UUID.fromString(uuid));
			}
			return null;
		}
		catch (IllegalArgumentException e)
		{
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Get a list of all VPN profiles stored in the database.
	 * @return list of VPN profiles
	 */
	public List<VpnProfile> getAllVpnProfiles()
	{
		List<VpnProfile> vpnProfiles = new ArrayList<VpnProfile>();

		Cursor cursor = mDatabase.query(TABLE_VPNPROFILE, ALL_COLUMNS, null, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast())
		{
			VpnProfile vpnProfile = VpnProfileFromCursor(cursor);
			vpnProfiles.add(vpnProfile);
			cursor.moveToNext();
		}
		cursor.close();
		return vpnProfiles;
	}

	private VpnProfile VpnProfileFromCursor(Cursor cursor)
	{
		VpnProfile profile = new VpnProfile();
		profile.setId(cursor.getLong(cursor.getColumnIndex(KEY_ID)));
		profile.setUUID(UUID.fromString(cursor.getString(cursor.getColumnIndex(KEY_UUID))));
		profile.setName(cursor.getString(cursor.getColumnIndex(KEY_NAME)));
		profile.setGateway(cursor.getString(cursor.getColumnIndex(KEY_GATEWAY)));
		profile.setVpnType(VpnType.fromIdentifier(cursor.getString(cursor.getColumnIndex(KEY_VPN_TYPE))));
		profile.setUsername(cursor.getString(cursor.getColumnIndex(KEY_USERNAME)));
		profile.setPassword(cursor.getString(cursor.getColumnIndex(KEY_PASSWORD)));
		profile.setCertificateAlias(cursor.getString(cursor.getColumnIndex(KEY_CERTIFICATE)));
		profile.setUserCertificateAlias(cursor.getString(cursor.getColumnIndex(KEY_USER_CERTIFICATE)));
		profile.setMTU(getInt(cursor, cursor.getColumnIndex(KEY_MTU)));
		profile.setPort(getInt(cursor, cursor.getColumnIndex(KEY_PORT)));
		profile.setSplitTunneling(getInt(cursor, cursor.getColumnIndex(KEY_SPLIT_TUNNELING)));
		profile.setLocalId(cursor.getString(cursor.getColumnIndex(KEY_LOCAL_ID)));
		profile.setRemoteId(cursor.getString(cursor.getColumnIndex(KEY_REMOTE_ID)));
		profile.setExcludedSubnets(cursor.getString(cursor.getColumnIndex(KEY_EXCLUDED_SUBNETS)));
		profile.setIncludedSubnets(cursor.getString(cursor.getColumnIndex(KEY_INCLUDED_SUBNETS)));
		profile.setSelectedAppsHandling(getInt(cursor, cursor.getColumnIndex(KEY_SELECTED_APPS)));
		profile.setSelectedApps(cursor.getString(cursor.getColumnIndex(KEY_SELECTED_APPS_LIST)));
		profile.setNATKeepAlive(getInt(cursor, cursor.getColumnIndex(KEY_NAT_KEEPALIVE)));
		profile.setFlags(getInt(cursor, cursor.getColumnIndex(KEY_FLAGS)));
		profile.setIkeProposal(cursor.getString(cursor.getColumnIndex(KEY_IKE_PROPOSAL)));
		profile.setEspProposal(cursor.getString(cursor.getColumnIndex(KEY_ESP_PROPOSAL)));
		profile.setDnsServers(cursor.getString(cursor.getColumnIndex(KEY_DNS_SERVERS)));
		return profile;
	}

	private ContentValues ContentValuesFromVpnProfile(VpnProfile profile)
	{
		ContentValues values = new ContentValues();
		values.put(KEY_UUID, profile.getUUID().toString());
		values.put(KEY_NAME, profile.getName());
		values.put(KEY_GATEWAY, profile.getGateway());
		values.put(KEY_VPN_TYPE, profile.getVpnType().getIdentifier());
		values.put(KEY_USERNAME, profile.getUsername());
		values.put(KEY_PASSWORD, profile.getPassword());
		values.put(KEY_CERTIFICATE, profile.getCertificateAlias());
		values.put(KEY_USER_CERTIFICATE, profile.getUserCertificateAlias());
		values.put(KEY_MTU, profile.getMTU());
		values.put(KEY_PORT, profile.getPort());
		values.put(KEY_SPLIT_TUNNELING, profile.getSplitTunneling());
		values.put(KEY_LOCAL_ID, profile.getLocalId());
		values.put(KEY_REMOTE_ID, profile.getRemoteId());
		values.put(KEY_EXCLUDED_SUBNETS, profile.getExcludedSubnets());
		values.put(KEY_INCLUDED_SUBNETS, profile.getIncludedSubnets());
		values.put(KEY_SELECTED_APPS, profile.getSelectedAppsHandling().getValue());
		values.put(KEY_SELECTED_APPS_LIST, profile.getSelectedApps());
		values.put(KEY_NAT_KEEPALIVE, profile.getNATKeepAlive());
		values.put(KEY_FLAGS, profile.getFlags());
		values.put(KEY_IKE_PROPOSAL, profile.getIkeProposal());
		values.put(KEY_ESP_PROPOSAL, profile.getEspProposal());
		values.put(KEY_DNS_SERVERS, profile.getDnsServers());
		return values;
	}

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