logging to SQL database
[strongswan.git] / src / charon / plugins / sql / mysql.sql
1
2
3 DROP TABLE IF EXISTS `child_configs`;
4 CREATE TABLE `child_configs` (
5 `id` int(10) unsigned NOT NULL auto_increment,
6 `name` varchar(32) collate utf8_unicode_ci NOT NULL,
7 `lifetime` mediumint(8) unsigned NOT NULL default '1500',
8 `rekeytime` mediumint(8) unsigned NOT NULL default '1200',
9 `jitter` mediumint(8) unsigned NOT NULL default '60',
10 `updown` varchar(128) collate utf8_unicode_ci default NULL,
11 `hostaccess` tinyint(1) unsigned NOT NULL default '1',
12 `mode` tinyint(4) unsigned NOT NULL default '1',
13 PRIMARY KEY (`id`)
14 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
15
16
17 DROP TABLE IF EXISTS `child_config_traffic_selector`;
18 CREATE TABLE `child_config_traffic_selector` (
19 `child_cfg` int(10) unsigned NOT NULL,
20 `traffic_selector` int(10) unsigned NOT NULL,
21 `kind` tinyint(3) unsigned NOT NULL
22 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
23
24
25 DROP TABLE IF EXISTS `ike_configs`;
26 CREATE TABLE `ike_configs` (
27 `id` int(10) unsigned NOT NULL auto_increment,
28 `certreq` tinyint(3) unsigned NOT NULL default '1',
29 `force_encap` tinyint(1) NOT NULL default '0',
30 `local` varchar(64) collate utf8_unicode_ci NOT NULL,
31 `remote` varchar(64) collate utf8_unicode_ci NOT NULL,
32 PRIMARY KEY (`id`)
33 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
34
35
36 DROP TABLE IF EXISTS `peer_configs`;
37 CREATE TABLE `peer_configs` (
38 `id` int(11) NOT NULL auto_increment,
39 `name` varchar(32) collate utf8_unicode_ci NOT NULL,
40 `ike_version` tinyint(3) unsigned NOT NULL default '2',
41 `ike_cfg` int(10) unsigned NOT NULL,
42 `local_id` varchar(64) collate utf8_unicode_ci NOT NULL,
43 `remote_id` varchar(64) collate utf8_unicode_ci NOT NULL,
44 `cert_policy` tinyint(3) unsigned NOT NULL default '1',
45 `auth_method` tinyint(3) unsigned NOT NULL default '1',
46 `eap_type` tinyint(3) unsigned NOT NULL default '0',
47 `eap_vendor` smallint(5) unsigned NOT NULL default '0',
48 `keyingtries` tinyint(3) unsigned NOT NULL default '3',
49 `rekeytime` mediumint(8) unsigned NOT NULL default '7200',
50 `reauthtime` mediumint(8) unsigned NOT NULL default '0',
51 `jitter` mediumint(8) unsigned NOT NULL default '180',
52 `overtime` mediumint(8) unsigned NOT NULL default '300',
53 `mobike` tinyint(1) NOT NULL default '1',
54 `dpd_delay` mediumint(8) unsigned NOT NULL default '120',
55 `dpd_action` tinyint(3) unsigned NOT NULL default '1',
56 `local_vip` varchar(128) collate utf8_unicode_ci default NULL,
57 `remote_vip` varchar(128) collate utf8_unicode_ci default NULL,
58 `mediation` tinyint(1) NOT NULL default '0',
59 `mediated_by` int(11) NOT NULL default '0',
60 `peer_id` varchar(64) collate utf8_unicode_ci default NULL,
61 PRIMARY KEY (`id`)
62 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
63
64
65 DROP TABLE IF EXISTS `peer_config_child_config`;
66 CREATE TABLE `peer_config_child_config` (
67 `peer_cfg` int(10) unsigned NOT NULL,
68 `child_cfg` int(10) unsigned NOT NULL
69 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
70
71
72 DROP TABLE IF EXISTS `traffic_selectors`;
73 CREATE TABLE `traffic_selectors` (
74 `id` int(10) unsigned NOT NULL auto_increment,
75 `type` tinyint(3) unsigned NOT NULL default '7',
76 `protocol` smallint(5) unsigned NOT NULL default '0',
77 `start_addr` varchar(40) collate utf8_unicode_ci default NULL,
78 `end_addr` varchar(40) collate utf8_unicode_ci default NULL,
79 `start_port` smallint(5) unsigned NOT NULL default '0',
80 `end_port` smallint(5) unsigned NOT NULL default '65535',
81 PRIMARY KEY (`id`)
82 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
83
84
85 DROP TABLE IF EXISTS shared_secrets;
86 CREATE TABLE shared_secrets (
87 `id` int(10) unsigned NOT NULL auto_increment,
88 `type` tinyint(3) unsigned NOT NULL,
89 `local` varchar(64) default NULL,
90 `remote` varchar(64) default NULL,
91 `data` BLOB NOT NULL,
92 PRIMARY KEY (`id`)
93 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
94
95
96 DROP TABLE IF EXISTS certificates;
97 CREATE TABLE certificates (
98 `id` int(10) unsigned NOT NULL auto_increment,
99 `type` tinyint(3) unsigned NOT NULL,
100 `keytype` tinyint(3) unsigned NOT NULL,
101 `keyid` BLOB NOT NULL,
102 `subject` varchar(64) default NULL,
103 `data` BLOB NOT NULL,
104 PRIMARY KEY (`id`)
105 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
106
107
108 DROP TABLE IF EXISTS private_keys;
109 CREATE TABLE private_keys (
110 `id` int(10) unsigned NOT NULL auto_increment,
111 `type` tinyint(3) unsigned NOT NULL,
112 `keyid` tinyblob NOT NULL,
113 `data` BLOB NOT NULL,
114 PRIMARY KEY (`id`)
115 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
116
117
118 DROP TABLE IF EXISTS ike_sas;
119 CREATE TABLE ike_sas (
120 `local_spi` BLOB(8) NOT NULL,
121 `remote_spi` BLOB(8) NOT NULL,
122 `id` int(10) unsigned NOT NULL,
123 `initiator` tinyint(1) NOT NULL,
124 `local_id` varchar(64) collate utf8_unicode_ci default NULL,
125 `remote_id` varchar(64) collate utf8_unicode_ci default NULL,
126 `local` varchar(64) collate utf8_unicode_ci NOT NULL,
127 `remote` varchar(64) collate utf8_unicode_ci NOT NULL,
128 PRIMARY KEY (local_spi(8))
129 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
130
131 DROP TABLE IF EXISTS logs;
132 CREATE TABLE logs (
133 `id` int(10) unsigned NOT NULL auto_increment,
134 `local_spi` BLOB(8) NOT NULL,
135 `signal` tinyint(3) NOT NULL,
136 `level` tinyint(3) NOT NULL,
137 `msg`varchar(256) NOT NULL,
138 PRIMARY KEY (`id`)
139 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
140
141