#396 fix - Changed DB init script to allow only localhost user access and c...

rsanchez

2014-11-28 fadd83e37dc7a2baaf8d5851e1d0813f6ac307d7

#396 fix - Changed DB init script to allow only localhost user access
and changed DB scheme to make it compatible with MySQL 5.1

 securis/src/main/resources/db/create_db.sql
..
..
@@ -1,7 +1,5 @@
1
1
 create database securis CHARACTER SET utf8 COLLATE utf8_bin;
2
-create user securis identified by 'securis';
3
2
 create user securis@localhost identified by 'securis';
4
-grant all on securis.* to securis;
5
3
 grant all on securis.* to securis@localhost;
6
4
 #Update passwords
7
5
 flush privileges;
 securis/src/main/resources/db/schema.sql
..
..
@@ -3,7 +3,7 @@
3
3
 CREATE  TABLE IF NOT EXISTS settings (
4
4
   `key` VARCHAR(100) NOT NULL ,
5
5
   value VARCHAR(2000) NULL ,
6
-  modification_timestamp DATETIME NOT NULL default now(),  
6
+  modification_timestamp TIMESTAMP NOT NULL default now(),  
7
7
   PRIMARY KEY (``key``) );
8
8
  
9
9
 drop table IF EXISTS user;
..
..
@@ -13,11 +13,11 @@
13
13
   roles INT NOT NULL default 0,
14
14
   first_name VARCHAR(100) NULL ,
15
15
   last_name VARCHAR(100) NULL ,
16
-  last_login DATETIME NULL ,
16
+  last_login TIMESTAMP NULL ,
17
17
   lang VARCHAR(10) NULL ,
18
18
   email VARCHAR(150) NULL ,
19
-  creation_timestamp DATETIME NOT NULL default now(),  
20
-  modification_timestamp DATETIME NULL ,  
19
+  creation_timestamp TIMESTAMP NOT NULL default now(),  
20
+  modification_timestamp TIMESTAMP NULL ,  
21
21
   PRIMARY KEY (username));
22
22
 
23
23
 drop table IF EXISTS application;
..
..
@@ -26,7 +26,7 @@
26
26
   name VARCHAR(45) NOT NULL ,
27
27
   license_filename VARCHAR(100) NOT NULL ,
28
28
   description VARCHAR(500) NULL ,
29
-  creation_timestamp DATETIME NOT NULL default now(),  
29
+  creation_timestamp TIMESTAMP NOT NULL default now(),  
30
30
   PRIMARY KEY (id));
31
31
 
32
32
 drop table IF EXISTS application_metadata;
..
..
@@ -35,7 +35,7 @@
35
35
   `key` VARCHAR(100) NOT NULL ,
36
36
   value VARCHAR(200) NULL ,
37
37
   mandatory BOOLEAN NOT NULL default true,
38
-  creation_timestamp DATETIME NULL default now(),  
38
+  creation_timestamp TIMESTAMP NULL default now(),  
39
39
   PRIMARY KEY (application_id, `key`));
40
40
 
41
41
 
..
..
@@ -46,7 +46,7 @@
46
46
   name VARCHAR(45) NOT NULL ,
47
47
   description VARCHAR(100) NULL ,
48
48
   application_id INT NULL ,
49
-  creation_timestamp DATETIME NOT NULL default now(),  
49
+  creation_timestamp TIMESTAMP NOT NULL default now(),  
50
50
   PRIMARY KEY (id));
51
51
 
52
52
 drop table IF EXISTS licensetype_metadata;
..
..
@@ -64,7 +64,7 @@
64
64
   name VARCHAR(45) NOT NULL ,
65
65
   description VARCHAR(100) NULL ,
66
66
   org_parent_id INT NULL ,
67
-  creation_timestamp DATETIME NOT NULL default now(),  
67
+  creation_timestamp TIMESTAMP NOT NULL default now(),  
68
68
   PRIMARY KEY (id));
69
69
   
70
70
 drop table IF EXISTS user_organization;
..
..
@@ -87,7 +87,7 @@
87
87
   license_preactivation BOOLEAN NOT NULL DEFAULT true,  
88
88
   default_valid_period INT NOT NULL DEFAULT 60,  
89
89
   created_by varchar(45) NULL ,  
90
-  creation_timestamp DATETIME NOT NULL default now(),
90
+  creation_timestamp TIMESTAMP NOT NULL default now(),
91
91
   PRIMARY KEY (id));
92
92
   
93
93
 drop table IF EXISTS pack_metadata;
..
..
@@ -111,10 +111,10 @@
111
111
   full_name VARCHAR(150) NULL,  
112
112
   email VARCHAR(100)  NOT NULL,  
113
113
   comments VARCHAR(1024) NULL ,
114
-  creation_timestamp DATETIME NOT NULL default now(),  
115
-  modification_timestamp DATETIME NULL ,  
116
-  last_access_timestamp DATETIME NULL ,  
117
-  expiration_date DATETIME NULL ,  
114
+  creation_timestamp TIMESTAMP NOT NULL default now(),  
115
+  modification_timestamp TIMESTAMP NULL ,  
116
+  last_access_timestamp TIMESTAMP NULL ,  
117
+  expiration_date TIMESTAMP NULL ,  
118
118
   cancelled_by varchar(45) NULL ,  
119
119
   created_by varchar(45) NULL ,  
120
120
   status VARCHAR(2) NOT NULL default 'CR',  
..
..
@@ -126,7 +126,7 @@
126
126
   id INT NOT NULL auto_increment,
127
127
   license_id INT NOT NULL,
128
128
   username VARCHAR(45) NOT NULL,
129
-  creation_timestamp DATETIME NOT NULL default now(),  
129
+  creation_timestamp TIMESTAMP NOT NULL default now(),  
130
130
   action VARCHAR(40) , 
131
131
   comments VARCHAR(512) ,
132
132
   PRIMARY KEY (id));
..
..
@@ -137,6 +137,6 @@
137
137
   hash VARCHAR(64) NOT NULL ,
138
138
   request_data VARCHAR(1024) NULL ,
139
139
   blocked_by varchar(45) NULL ,  
140
-  creation_timestamp DATETIME NOT NULL default now(),  
140
+  creation_timestamp TIMESTAMP NOT NULL default now(),  
141
141
   PRIMARY KEY (hash));
142
142