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

/*
 * Copyright (C) 2012-2017 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";

	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 = 13;

	public static final String DATABASE_CREATE =
							"CREATE TABLE " + TABLE_VPNPROFILE + " (" +
								KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
								KEY_UUID + " TEXT UNIQUE," +
								KEY_NAME + " TEXT NOT NULL," +
								KEY_GATEWAY + " TEXT NOT NULL," +
								KEY_VPN_TYPE + " TEXT NOT NULL," +
								KEY_USERNAME + " TEXT," +
								KEY_PASSWORD + " TEXT," +
								KEY_CERTIFICATE + " TEXT," +
								KEY_USER_CERTIFICATE + " TEXT," +
								KEY_MTU + " INTEGER," +
								KEY_PORT + " INTEGER," +
								KEY_SPLIT_TUNNELING + " INTEGER," +
								KEY_LOCAL_ID + " TEXT," +
								KEY_REMOTE_ID + " TEXT," +
								KEY_EXCLUDED_SUBNETS + " TEXT," +
								KEY_INCLUDED_SUBNETS + " TEXT," +
								KEY_SELECTED_APPS + " INTEGER," +
								KEY_SELECTED_APPS_LIST + " TEXT," +
								KEY_NAT_KEEPALIVE + " INTEGER" +
							");";
	private static final String[] ALL_COLUMNS = new String[] {
								KEY_ID,
								KEY_UUID,
								KEY_NAME,
								KEY_GATEWAY,
								KEY_VPN_TYPE,
								KEY_USERNAME,
								KEY_PASSWORD,
								KEY_CERTIFICATE,
								KEY_USER_CERTIFICATE,
								KEY_MTU,
								KEY_PORT,
								KEY_SPLIT_TUNNELING,
								KEY_LOCAL_ID,
								KEY_REMOTE_ID,
								KEY_EXCLUDED_SUBNETS,
								KEY_INCLUDED_SUBNETS,
								KEY_SELECTED_APPS,
								KEY_SELECTED_APPS_LIST,
								KEY_NAT_KEEPALIVE,
							};

	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(DATABASE_CREATE);
		}

		@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);
			}
			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);
			}
			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;");
			}
		}

		private void updateColumns(SQLiteDatabase db)
		{
			db.beginTransaction();
			try
			{
				db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " RENAME TO tmp_" + TABLE_VPNPROFILE + ";");
				db.execSQL(DATABASE_CREATE);
				StringBuilder insert = new StringBuilder("INSERT INTO " + TABLE_VPNPROFILE + " SELECT ");
				SQLiteQueryBuilder.appendColumns(insert, ALL_COLUMNS);
				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 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(getUUID(cursor, 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)));
		return profile;
	}

	private ContentValues ContentValuesFromVpnProfile(VpnProfile profile)
	{
		ContentValues values = new ContentValues();
		values.put(KEY_UUID, profile.getUUID() != null ? profile.getUUID().toString() : null);
		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());
		return values;
	}

	private Integer getInt(Cursor cursor, int columnIndex)
	{
		return cursor.isNull(columnIndex) ? null : cursor.getInt(columnIndex);
	}

	private UUID getUUID(Cursor cursor, int columnIndex)
	{
		try
		{
			return cursor.isNull(columnIndex) ? null : UUID.fromString(cursor.getString(columnIndex));
		}
		catch (Exception e)
		{
			return null;
		}
	}
}
Commit	Line	Data
d799cbf6	1	/*
13ead876	2	* Copyright (C) 2012-2017 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";
d799cbf6 TB	55
	56	private DatabaseHelper mDbHelper;
	57	private SQLiteDatabase mDatabase;
	58	private final Context mContext;
	59
	60	private static final String DATABASE_NAME = "strongswan.db";
	61	private static final String TABLE_VPNPROFILE = "vpnprofile";
	62
a2830231	63	private static final int DATABASE_VERSION = 13;
d799cbf6 TB	64
	65	public static final String DATABASE_CREATE =
	66	"CREATE TABLE " + TABLE_VPNPROFILE + " (" +
	67	KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
c4ab9af7	68	KEY_UUID + " TEXT UNIQUE," +
d799cbf6 TB	69	KEY_NAME + " TEXT NOT NULL," +
d799cbf6 TB	70	KEY_GATEWAY + " TEXT NOT NULL," +
48f51d94	71	KEY_VPN_TYPE + " TEXT NOT NULL," +
3f9e90f6	72	KEY_USERNAME + " TEXT," +
d799cbf6	73	KEY_PASSWORD + " TEXT," +
e09f4120	74	KEY_CERTIFICATE + " TEXT," +
7e2a6c4a	75	KEY_USER_CERTIFICATE + " TEXT," +
5b11855f	76	KEY_MTU + " INTEGER," +
f3d8da76	77	KEY_PORT + " INTEGER," +
89149dbb TB	78	KEY_SPLIT_TUNNELING + " INTEGER," +
89149dbb TB	79	KEY_LOCAL_ID + " TEXT," +
13ead876	80	KEY_REMOTE_ID + " TEXT," +
a9875259	81	KEY_EXCLUDED_SUBNETS + " TEXT," +
43b33f07 TB	82	KEY_INCLUDED_SUBNETS + " TEXT," +
43b33f07 TB	83	KEY_SELECTED_APPS + " INTEGER," +
a2830231 TB	84	KEY_SELECTED_APPS_LIST + " TEXT," +
a2830231 TB	85	KEY_NAT_KEEPALIVE + " INTEGER" +
d799cbf6	86	");";
3f9e90f6	87	private static final String[] ALL_COLUMNS = new String[] {
d799cbf6	88	KEY_ID,
c4ab9af7	89	KEY_UUID,
d799cbf6 TB	90	KEY_NAME,
d799cbf6 TB	91	KEY_GATEWAY,
48f51d94	92	KEY_VPN_TYPE,
d799cbf6 TB	93	KEY_USERNAME,
d799cbf6 TB	94	KEY_PASSWORD,
e09f4120 TB	95	KEY_CERTIFICATE,
e09f4120 TB	96	KEY_USER_CERTIFICATE,
7e2a6c4a	97	KEY_MTU,
5b11855f	98	KEY_PORT,
f3d8da76	99	KEY_SPLIT_TUNNELING,
89149dbb TB	100	KEY_LOCAL_ID,
89149dbb TB	101	KEY_REMOTE_ID,
13ead876	102	KEY_EXCLUDED_SUBNETS,
a9875259	103	KEY_INCLUDED_SUBNETS,
43b33f07 TB	104	KEY_SELECTED_APPS,
43b33f07 TB	105	KEY_SELECTED_APPS_LIST,
a2830231	106	KEY_NAT_KEEPALIVE,
d799cbf6 TB	107	};
	108
	109	private static class DatabaseHelper extends SQLiteOpenHelper
	110	{
	111	public DatabaseHelper(Context context)
	112	{
	113	super(context, DATABASE_NAME, null, DATABASE_VERSION);
	114	}
	115
	116	@Override
	117	public void onCreate(SQLiteDatabase database)
	118	{
	119	database.execSQL(DATABASE_CREATE);
	120	}
	121
	122	@Override
	123	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
	124	{
	125	Log.w(TAG, "Upgrading database from version " + oldVersion +
e09f4120 TB	126	" to " + newVersion);
	127	if (oldVersion < 2)
	128	{
	129	db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_USER_CERTIFICATE +
	130	" TEXT;");
	131	}
48f51d94 TB	132	if (oldVersion < 3)
	133	{
	134	db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_VPN_TYPE +
	135	" TEXT DEFAULT '';");
	136	}
3f9e90f6 TB	137	if (oldVersion < 4)
	138	{ /* remove NOT NULL constraint from username column */
	139	updateColumns(db);
	140	}
7e2a6c4a TB	141	if (oldVersion < 5)
	142	{
	143	db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_MTU +
	144	" INTEGER;");
	145	}
5b11855f TB	146	if (oldVersion < 6)
	147	{
	148	db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_PORT +
	149	" INTEGER;");
	150	}
f3d8da76 TB	151	if (oldVersion < 7)
	152	{
	153	db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_SPLIT_TUNNELING +
	154	" INTEGER;");
	155	}
89149dbb TB	156	if (oldVersion < 8)
	157	{
	158	db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_LOCAL_ID +
	159	" TEXT;");
	160	db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_REMOTE_ID +
	161	" TEXT;");
	162	}
c4ab9af7 TB	163	if (oldVersion < 9)
	164	{
	165	db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_UUID +
	166	" TEXT;");
	167	updateColumns(db);
	168	}
13ead876 TB	169	if (oldVersion < 10)
	170	{
	171	db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_EXCLUDED_SUBNETS +
	172	" TEXT;");
	173	}
a9875259 TB	174	if (oldVersion < 11)
	175	{
	176	db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_INCLUDED_SUBNETS +
	177	" TEXT;");
	178	}
43b33f07 TB	179	if (oldVersion < 12)
	180	{
	181	db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_SELECTED_APPS +
	182	" INTEGER;");
	183	db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_SELECTED_APPS_LIST +
	184	" TEXT;");
	185	}
a2830231 TB	186	if (oldVersion < 13)
	187	{
	188	db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " ADD " + KEY_NAT_KEEPALIVE +
	189	" INTEGER;");
	190	}
3f9e90f6 TB	191	}
	192
	193	private void updateColumns(SQLiteDatabase db)
	194	{
	195	db.beginTransaction();
	196	try
	197	{
	198	db.execSQL("ALTER TABLE " + TABLE_VPNPROFILE + " RENAME TO tmp_" + TABLE_VPNPROFILE + ";");
	199	db.execSQL(DATABASE_CREATE);
	200	StringBuilder insert = new StringBuilder("INSERT INTO " + TABLE_VPNPROFILE + " SELECT ");
	201	SQLiteQueryBuilder.appendColumns(insert, ALL_COLUMNS);
	202	db.execSQL(insert.append(" FROM tmp_" + TABLE_VPNPROFILE + ";").toString());
	203	db.execSQL("DROP TABLE tmp_" + TABLE_VPNPROFILE + ";");
	204	db.setTransactionSuccessful();
	205	}
	206	finally
	207	{
	208	db.endTransaction();
	209	}
d799cbf6 TB	210	}
	211	}
	212
	213	/**
	214	* Construct a new VPN profile data source. The context is used to
	215	* open/create the database.
	216	* @param context context used to access the database
	217	*/
	218	public VpnProfileDataSource(Context context)
	219	{
	220	this.mContext = context;
	221	}
	222
	223	/**
	224	* Open the VPN profile data source. The database is automatically created
	225	* if it does not yet exist. If that fails an exception is thrown.
	226	* @return itself (allows to chain initialization calls)
	227	* @throws SQLException if the database could not be opened or created
	228	*/
	229	public VpnProfileDataSource open() throws SQLException
	230	{
	231	if (mDbHelper == null)
	232	{
	233	mDbHelper = new DatabaseHelper(mContext);
	234	mDatabase = mDbHelper.getWritableDatabase();
	235	}
	236	return this;
	237	}
	238
	239	/**
	240	* Close the data source.
	241	*/
	242	public void close()
	243	{
	244	if (mDbHelper != null)
	245	{
	246	mDbHelper.close();
	247	mDbHelper = null;
	248	}
	249	}
	250
	251	/**
	252	* Insert the given VPN profile into the database. On success the Id of
	253	* the object is updated and the object returned.
	254	*
	255	* @param profile the profile to add
	256	* @return the added VPN profile or null, if failed
	257	*/
	258	public VpnProfile insertProfile(VpnProfile profile)
	259	{
	260	ContentValues values = ContentValuesFromVpnProfile(profile);
	261	long insertId = mDatabase.insert(TABLE_VPNPROFILE, null, values);
	262	if (insertId == -1)
	263	{
	264	return null;
	265	}
	266	profile.setId(insertId);
	267	return profile;
	268	}
	269
	270	/**
	271	* Updates the given VPN profile in the database.
	272	* @param profile the profile to update
	273	* @return true if update succeeded, false otherwise
274	*/
275	public boolean updateVpnProfile(VpnProfile profile)
276	{
277	long id = profile.getId();
278	ContentValues values = ContentValuesFromVpnProfile(profile);
279	return mDatabase.update(TABLE_VPNPROFILE, values, KEY_ID + " = " + id, null) > 0;
280	}
281
282	/**
283	* Delete the given VPN profile from the database.
284	* @param profile the profile to delete
285	* @return true if deleted, false otherwise
286	*/
287	public boolean deleteVpnProfile(VpnProfile profile)
288	{
289	long id = profile.getId();
290	return mDatabase.delete(TABLE_VPNPROFILE, KEY_ID + " = " + id, null) > 0;
291	}
292
293	/**
294	* Get a single VPN profile from the database.
295	* @param id the ID of the VPN profile
296	* @return the profile or null, if not found
297	*/
298	public VpnProfile getVpnProfile(long id)
299	{
300	VpnProfile profile = null;
301	Cursor cursor = mDatabase.query(TABLE_VPNPROFILE, ALL_COLUMNS,
302	KEY_ID + "=" + id, null, null, null, null);
303	if (cursor.moveToFirst())
304	{
305	profile = VpnProfileFromCursor(cursor);
306	}
307	cursor.close();
308	return profile;
309	}
310
c4ab9af7 TB	311	/**
	312	* Get a single VPN profile from the database by its UUID.
	313	* @param uuid the UUID of the VPN profile
	314	* @return the profile or null, if not found
	315	*/
	316	public VpnProfile getVpnProfile(UUID uuid)
	317	{
	318	VpnProfile profile = null;
	319	Cursor cursor = mDatabase.query(TABLE_VPNPROFILE, ALL_COLUMNS,
	320	KEY_UUID + "='" + uuid.toString() + "'", null, null, null, null);
	321	if (cursor.moveToFirst())
	322	{
	323	profile = VpnProfileFromCursor(cursor);
	324	}
	325	cursor.close();
	326	return profile;
	327	}
	328
d799cbf6 TB	329	/**
	330	* Get a list of all VPN profiles stored in the database.
	331	* @return list of VPN profiles
	332	*/
	333	public List<VpnProfile> getAllVpnProfiles()
	334	{
	335	List<VpnProfile> vpnProfiles = new ArrayList<VpnProfile>();
	336
	337	Cursor cursor = mDatabase.query(TABLE_VPNPROFILE, ALL_COLUMNS, null, null, null, null, null);
	338	cursor.moveToFirst();
	339	while (!cursor.isAfterLast())
	340	{
	341	VpnProfile vpnProfile = VpnProfileFromCursor(cursor);
	342	vpnProfiles.add(vpnProfile);
	343	cursor.moveToNext();
	344	}
	345	cursor.close();
	346	return vpnProfiles;
	347	}
	348
	349	private VpnProfile VpnProfileFromCursor(Cursor cursor)
	350	{
	351	VpnProfile profile = new VpnProfile();
	352	profile.setId(cursor.getLong(cursor.getColumnIndex(KEY_ID)));
c4ab9af7	353	profile.setUUID(getUUID(cursor, cursor.getColumnIndex(KEY_UUID)));
d799cbf6 TB	354	profile.setName(cursor.getString(cursor.getColumnIndex(KEY_NAME)));
d799cbf6 TB	355	profile.setGateway(cursor.getString(cursor.getColumnIndex(KEY_GATEWAY)));
48f51d94	356	profile.setVpnType(VpnType.fromIdentifier(cursor.getString(cursor.getColumnIndex(KEY_VPN_TYPE))));
d799cbf6 TB	357	profile.setUsername(cursor.getString(cursor.getColumnIndex(KEY_USERNAME)));
	358	profile.setPassword(cursor.getString(cursor.getColumnIndex(KEY_PASSWORD)));
	359	profile.setCertificateAlias(cursor.getString(cursor.getColumnIndex(KEY_CERTIFICATE)));
e09f4120	360	profile.setUserCertificateAlias(cursor.getString(cursor.getColumnIndex(KEY_USER_CERTIFICATE)));
7e2a6c4a	361	profile.setMTU(getInt(cursor, cursor.getColumnIndex(KEY_MTU)));
5b11855f	362	profile.setPort(getInt(cursor, cursor.getColumnIndex(KEY_PORT)));
f3d8da76	363	profile.setSplitTunneling(getInt(cursor, cursor.getColumnIndex(KEY_SPLIT_TUNNELING)));
89149dbb TB	364	profile.setLocalId(cursor.getString(cursor.getColumnIndex(KEY_LOCAL_ID)));
89149dbb TB	365	profile.setRemoteId(cursor.getString(cursor.getColumnIndex(KEY_REMOTE_ID)));
13ead876	366	profile.setExcludedSubnets(cursor.getString(cursor.getColumnIndex(KEY_EXCLUDED_SUBNETS)));
a9875259	367	profile.setIncludedSubnets(cursor.getString(cursor.getColumnIndex(KEY_INCLUDED_SUBNETS)));
43b33f07 TB	368	profile.setSelectedAppsHandling(getInt(cursor, cursor.getColumnIndex(KEY_SELECTED_APPS)));
43b33f07 TB	369	profile.setSelectedApps(cursor.getString(cursor.getColumnIndex(KEY_SELECTED_APPS_LIST)));
a2830231	370	profile.setNATKeepAlive(getInt(cursor, cursor.getColumnIndex(KEY_NAT_KEEPALIVE)));
d799cbf6 TB	371	return profile;
	372	}
	373
	374	private ContentValues ContentValuesFromVpnProfile(VpnProfile profile)
	375	{
	376	ContentValues values = new ContentValues();
c4ab9af7	377	values.put(KEY_UUID, profile.getUUID() != null ? profile.getUUID().toString() : null);
d799cbf6 TB	378	values.put(KEY_NAME, profile.getName());
d799cbf6 TB	379	values.put(KEY_GATEWAY, profile.getGateway());
48f51d94	380	values.put(KEY_VPN_TYPE, profile.getVpnType().getIdentifier());
d799cbf6 TB	381	values.put(KEY_USERNAME, profile.getUsername());
	382	values.put(KEY_PASSWORD, profile.getPassword());
	383	values.put(KEY_CERTIFICATE, profile.getCertificateAlias());
e09f4120	384	values.put(KEY_USER_CERTIFICATE, profile.getUserCertificateAlias());
7e2a6c4a	385	values.put(KEY_MTU, profile.getMTU());
5b11855f	386	values.put(KEY_PORT, profile.getPort());
f3d8da76	387	values.put(KEY_SPLIT_TUNNELING, profile.getSplitTunneling());
89149dbb TB	388	values.put(KEY_LOCAL_ID, profile.getLocalId());
89149dbb TB	389	values.put(KEY_REMOTE_ID, profile.getRemoteId());
13ead876	390	values.put(KEY_EXCLUDED_SUBNETS, profile.getExcludedSubnets());
a9875259	391	values.put(KEY_INCLUDED_SUBNETS, profile.getIncludedSubnets());
43b33f07 TB	392	values.put(KEY_SELECTED_APPS, profile.getSelectedAppsHandling().getValue());
43b33f07 TB	393	values.put(KEY_SELECTED_APPS_LIST, profile.getSelectedApps());
a2830231	394	values.put(KEY_NAT_KEEPALIVE, profile.getNATKeepAlive());
d799cbf6 TB	395	return values;
d799cbf6 TB	396	}
7e2a6c4a TB	397
	398	private Integer getInt(Cursor cursor, int columnIndex)
	399	{
	400	return cursor.isNull(columnIndex) ? null : cursor.getInt(columnIndex);
	401	}
c4ab9af7 TB	402
	403	private UUID getUUID(Cursor cursor, int columnIndex)
	404	{
	405	try
	406	{
	407	return cursor.isNull(columnIndex) ? null : UUID.fromString(cursor.getString(columnIndex));
	408	}
	409	catch (Exception e)
	410	{
	411	return null;
	412	}
	413	}
d799cbf6	414	}