SQL schema for MySQL and SQLite, test data

4 files changed, 165 insertions, 89 deletions

diff --git a/src/charon/plugins/sql/config.sql b/src/charon/plugins/sql/config.sql
deleted file mode 100644
index 64aaea7d7..000000000
--- a/src/charon/plugins/sql/config.sql
+++ /dev/null
@@ -1,73 +0,0 @@
-
-DROP TABLE IF EXISTS ike_configs;
-CREATE TABLE ike_configs (
-	id INTEGER PRIMARY KEY AUTOINCREMENT,
-	certreq INTEGER,
-	force_encap INTEGER,
-	local TEXT, 
-	remote TEXT
-);
-
-DROP TABLE IF EXISTS child_configs;
-CREATE TABLE child_configs (
-	id INTEGER PRIMARY KEY AUTOINCREMENT,
-	name TEXT,
-	lifetime INTEGER,
-	rekeytime INTEGER,
-	jitter INTEGER,
-	updown TEXT,
-	hostaccess INTEGER,
-	mode INTEGER
-);
-
-DROP TABLE IF EXISTS peer_config_child_config;
-CREATE TABLE peer_config_child_config (
-	peer_cfg INTEGER,
-	child_cfg INTEGER
-);
-
-DROP TABLE IF EXISTS traffic_selectors;
-CREATE TABLE traffic_selectors (
-	id INTEGER PRIMARY KEY AUTOINCREMENT,
-	type INTEGER,
-	protocol INTEGER,
-	start_addr TEXT,
-	end_addr TEXT,
-	start_port INTEGER,
-	end_port INTEGER
-);
-
-DROP TABLE IF EXISTS child_config_traffic_selector;
-CREATE TABLE child_config_traffic_selector (
-	child_cfg INTEGER,
-	traffic_selector INTEGER,
-	kind INTEGER
-);
-
-DROP TABLE IF EXISTS peer_configs;
-CREATE TABLE peer_configs (
-	id INTEGER PRIMARY KEY AUTOINCREMENT,
-	name TEXT,
-	ike_version INTEGER,
-	ike_cfg INTEGER,
-	local_id TEXT,
-	remote_id TEXT,
-	cert_policy INTEGER,
-	auth_method INTEGER,
-	eap_type INTEGER,
-	eap_vendor INTEGER,
-	keyingtries INTEGER, 
-	rekeytime INTEGER, 
-	reauthtime INTEGER, 
-	jitter INTEGER, 
-	overtime INTEGER,
-	mobike INTEGER,
-	dpd_delay INTEGER, 
-	dpd_action INTEGER,
-	local_vip TEXT,
-	remote_vip TEXT,
-	mediation INTEGER,
-	mediated_by INTEGER,
-	peer_id TEXT
-);
-
diff --git a/src/charon/plugins/sql/mysql.sql b/src/charon/plugins/sql/mysql.sql
new file mode 100644
index 000000000..01b77df35
--- /dev/null
+++ b/src/charon/plugins/sql/mysql.sql
@@ -0,0 +1,83 @@
+
+
+DROP TABLE IF EXISTS `child_configs`;
+CREATE TABLE `child_configs` (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `name` varchar(32) collate utf8_unicode_ci NOT NULL,
+  `lifetime` mediumint(8) unsigned NOT NULL default '1500',
+  `rekeytime` mediumint(8) unsigned NOT NULL default '1200',
+  `jitter` mediumint(8) unsigned NOT NULL default '60',
+  `updown` varchar(128) collate utf8_unicode_ci default NULL,
+  `hostaccess` tinyint(1) unsigned NOT NULL default '1',
+  `mode` tinyint(4) unsigned NOT NULL default '1',
+  PRIMARY KEY  (`id`)
+) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `child_config_traffic_selector`;
+CREATE TABLE `child_config_traffic_selector` (
+  `child_cfg` int(10) unsigned NOT NULL,
+  `traffic_selector` int(10) unsigned NOT NULL,
+  `kind` tinyint(3) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `ike_configs`;
+CREATE TABLE `ike_configs` (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `certreq` tinyint(3) unsigned NOT NULL default '1',
+  `force_encap` tinyint(1) NOT NULL default '0',
+  `local` varchar(64) collate utf8_unicode_ci NOT NULL,
+  `remote` varchar(64) collate utf8_unicode_ci NOT NULL,
+  PRIMARY KEY  (`id`)
+) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `peer_configs`;
+CREATE TABLE `peer_configs` (
+  `id` int(11) NOT NULL auto_increment,
+  `name` varchar(32) collate utf8_unicode_ci NOT NULL,
+  `ike_version` tinyint(3) unsigned NOT NULL default '2',
+  `ike_cfg` int(10) unsigned NOT NULL,
+  `local_id` varchar(64) collate utf8_unicode_ci NOT NULL,
+  `remote_id` varchar(64) collate utf8_unicode_ci NOT NULL,
+  `cert_policy` tinyint(3) unsigned NOT NULL default '1',
+  `auth_method` tinyint(3) unsigned NOT NULL default '1',
+  `eap_type` tinyint(3) unsigned NOT NULL default '0',
+  `eap_vendor` smallint(5) unsigned NOT NULL default '0',
+  `keyingtries` tinyint(3) unsigned NOT NULL default '3',
+  `rekeytime` mediumint(8) unsigned NOT NULL default '7200',
+  `reauthtime` mediumint(8) unsigned NOT NULL default '0',
+  `jitter` mediumint(8) unsigned NOT NULL default '180',
+  `overtime` mediumint(8) unsigned NOT NULL default '300',
+  `mobike` tinyint(1) NOT NULL default '1',
+  `dpd_delay` mediumint(8) unsigned NOT NULL default '120',
+  `dpd_action` tinyint(3) unsigned NOT NULL default '1',
+  `local_vip` varchar(128) collate utf8_unicode_ci default NULL,
+  `remote_vip` varchar(128) collate utf8_unicode_ci default NULL,
+  `mediation` tinyint(1) NOT NULL default '0',
+  `mediated_by` int(11) NOT NULL default '0',
+  `peer_id` varchar(64) collate utf8_unicode_ci default NULL,
+  PRIMARY KEY  (`id`)
+) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `peer_config_child_config`;
+CREATE TABLE `peer_config_child_config` (
+  `peer_cfg` int(10) unsigned NOT NULL,
+  `child_cfg` int(10) unsigned NOT NULL
+) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+
+DROP TABLE IF EXISTS `traffic_selectors`;
+CREATE TABLE `traffic_selectors` (
+  `id` int(10) unsigned NOT NULL auto_increment,
+  `type` tinyint(3) unsigned NOT NULL default '7',
+  `protocol` smallint(5) unsigned NOT NULL default '0',
+  `start_addr` varchar(40) collate utf8_unicode_ci default NULL,
+  `end_addr` varchar(40) collate utf8_unicode_ci default NULL,
+  `start_port` smallint(5) unsigned NOT NULL default '0',
+  `end_port` smallint(5) unsigned NOT NULL default '65535',
+  PRIMARY KEY  (`id`)
+) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
diff --git a/src/charon/plugins/sql/sqlite.sql b/src/charon/plugins/sql/sqlite.sql
new file mode 100644
index 000000000..eeb1bb201
--- /dev/null
+++ b/src/charon/plugins/sql/sqlite.sql
@@ -0,0 +1,75 @@
+
+DROP TABLE IF EXISTS child_configs;
+CREATE TABLE child_configs (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  name TEXT NOT NULL,
+  lifetime INTEGER NOT NULL default '1500',
+  rekeytime INTEGER NOT NULL default '1200',
+  jitter INTEGER NOT NULL default '60',
+  updown TEXT default NULL,
+  hostaccess INTEGER NOT NULL default '1',
+  mode INTEGER NOT NULL default '1'
+);
+
+
+DROP TABLE IF EXISTS child_config_traffic_selector;
+CREATE TABLE child_config_traffic_selector (
+  child_cfg INTEGER NOT NULL,
+  traffic_selector INTEGER NOT NULL,
+  kind INTEGER NOT NULL
+);
+
+
+DROP TABLE IF EXISTS ike_configs;
+CREATE TABLE ike_configs (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  certreq INTEGER NOT NULL default '1',
+  force_encap INTEGER NOT NULL default '0',
+  local TEXT NOT NULL,
+  remote TEXT NOT NULL
+);
+
+DROP TABLE IF EXISTS peer_configs;
+CREATE TABLE peer_configs (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  name TEXT NOT NULL,
+  ike_version INTEGER NOT NULL default '2',
+  ike_cfg INTEGER NOT NULL,
+  local_id TEXT NOT NULL,
+  remote_id TEXT NOT NULL,
+  cert_policy INTEGER NOT NULL default '1',
+  auth_method INTEGER NOT NULL default '1',
+  eap_type INTEGER NOT NULL default '0',
+  eap_vendor INTEGER NOT NULL default '0',
+  keyingtries INTEGER NOT NULL default '3',
+  rekeytime INTEGER NOT NULL default '7200',
+  reauthtime INTEGER NOT NULL default '0',
+  jitter INTEGER NOT NULL default '180',
+  overtime INTEGER NOT NULL default '300',
+  mobike INTEGER NOT NULL default '1',
+  dpd_delay INTEGER NOT NULL default '120',
+  dpd_action INTEGER NOT NULL default '1',
+  local_vip TEXT default NULL,
+  remote_vip TEXT default NULL,
+  mediation INTEGER NOT NULL default '0',
+  mediated_by INTEGER NOT NULL default '0',
+  peer_id TEXT default NULL
+);
+
+DROP TABLE IF EXISTS peer_config_child_config;
+CREATE TABLE peer_config_child_config (
+  peer_cfg INTEGER NOT NULL,
+  child_cfg INTEGER NOT NULL
+);
+
+DROP TABLE IF EXISTS traffic_selectors;
+CREATE TABLE traffic_selectors (
+  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
+  type INTEGER NOT NULL default '7',
+  protocol INTEGER NOT NULL default '0',
+  start_addr TEXT default NULL,
+  end_addr TEXT default NULL,
+  start_port INTEGER NOT NULL default '0',
+  end_port INTEGER NOT NULL default '65535'
+);
+
diff --git a/src/charon/plugins/sql/test.sql b/src/charon/plugins/sql/test.sql
index ec5b401c5..495a2aa4c 100644
--- a/src/charon/plugins/sql/test.sql
+++ b/src/charon/plugins/sql/test.sql
@@ -1,14 +1,14 @@
 
 INSERT INTO ike_configs (
-	certreq, force_encap, local, remote
+	local, remote
 ) VALUES (
-	0, 0, '0.0.0.0', '152.96.52.150'
+	'0.0.0.0', '152.96.52.150'
 );
 
 INSERT INTO child_configs (
-	name, lifetime, rekeytime, jitter, updown, hostaccess, mode
+	name
 ) VALUES (
-	'sqltest', 500, 400, 50, NULL, 1, 1
+	'sqltest'
 );
 
 INSERT INTO peer_config_child_config (
@@ -17,11 +17,7 @@ INSERT INTO peer_config_child_config (
 	1, 1
 );
 
-INSERT INTO traffic_selectors (
-	type, protocol
-) values (
-	7, 0
-);
+INSERT INTO traffic_selectors (type) VALUES (7);
 
 INSERT INTO child_config_traffic_selector (
 	child_cfg, traffic_selector, kind
@@ -36,12 +32,7 @@ INSERT INTO child_config_traffic_selector (
 );
 
 INSERT INTO peer_configs (
-	name, ike_version, ike_cfg, local_id, remote_id, cert_policy, auth_method, 
-	eap_type, eap_vendor, keyingtries, rekeytime, reauthtime, jitter, overtime, 
-	mobike, dpd_delay, dpd_action, local_vip, remote_vip, 
-	mediation, mediated_by, peer_id
+	name, ike_cfg, local_id, remote_id
 ) VALUES (
-	'sqltest', 2, 1, 'C=CH, O=Linux strongSwan, CN=martin', 'sidv0150.hsr.ch', 0, 0, 
-	0, 0, 0, 500, 2000, 20, 20,
-	1, 120, 0, NULL, NULL, 0, 0, NULL
+	'sqltest', 1, 'C=CH, O=Linux strongSwan, CN=martin', 'sidv0150.hsr.ch'
 );