1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
| | drop table IF EXISTS settings;
| | CREATE TABLE IF NOT EXISTS settings (
| | key VARCHAR(100) NOT NULL ,
| | value VARCHAR(2000) NULL ,
| | timestamp DATETIME NOT NULL DEFAULT now() ,
| | PRIMARY KEY (key) );
| |
| | drop table IF EXISTS user;
| | CREATE TABLE IF NOT EXISTS user (
| | username VARCHAR(45) NOT NULL ,
| | password VARCHAR(100) NULL ,
| | roles INT NOT NULL default 0,
| | first_name VARCHAR(100) NULL ,
| | last_name VARCHAR(100) NULL ,
| | last_login DATETIME NULL ,
| | lang VARCHAR(10) NULL ,
| | creation_timestamp DATETIME NULL ,
| | modification_timestamp DATETIME NULL ,
| | PRIMARY KEY (username));
| |
| | drop table IF EXISTS application;
| | CREATE TABLE IF NOT EXISTS application (
| | id INT NOT NULL auto_increment,
| | name VARCHAR(45) NOT NULL ,
| | license_filename VARCHAR(100) NOT NULL ,
| | description VARCHAR(500) NULL ,
| | creation_timestamp DATETIME NULL ,
| | PRIMARY KEY (id));
| |
| | drop table IF EXISTS application_metadata;
| | CREATE TABLE IF NOT EXISTS application_metadata (
| | application_id INT NOT NULL ,
| | key VARCHAR(100) NOT NULL ,
| | value VARCHAR(200) NULL ,
| | mandatory BOOLEAN NOT NULL default true,
| | creation_timestamp DATETIME NOT NULL ,
| | PRIMARY KEY (application_id, key));
| |
| |
| | drop table IF EXISTS license_type;
| | CREATE TABLE IF NOT EXISTS license_type (
| | id INT NOT NULL auto_increment,
| | code VARCHAR(10) NOT NULL ,
| | name VARCHAR(45) NOT NULL ,
| | description VARCHAR(100) NULL ,
| | application_id INT NULL ,
| | creation_timestamp DATETIME NULL ,
| | PRIMARY KEY (id));
| |
| | drop table IF EXISTS licensetype_metadata;
| | CREATE TABLE IF NOT EXISTS licensetype_metadata (
| | license_type_id INT NOT NULL ,
| | key VARCHAR(100) NOT NULL ,
| | value VARCHAR(200) NULL ,
| | mandatory BOOLEAN NOT NULL default true,
| | PRIMARY KEY (license_type_id, key));
| |
| | drop table IF EXISTS organization;
| | CREATE TABLE IF NOT EXISTS organization (
| | id INT NOT NULL auto_increment,
| | code VARCHAR(10) NOT NULL ,
| | name VARCHAR(45) NOT NULL ,
| | description VARCHAR(100) NULL ,
| | org_parent_id INT NULL ,
| | creation_timestamp DATETIME NULL ,
| | PRIMARY KEY (id));
| |
| | drop table IF EXISTS user_organization;
| | CREATE TABLE IF NOT EXISTS user_organization (
| | username VARCHAR(45) NOT NULL,
| | organization_id INT NOT NULL,
| | PRIMARY KEY (username, organization_id));
| |
| | drop table IF EXISTS pack;
| | CREATE TABLE IF NOT EXISTS pack (
| | id INT NOT NULL auto_increment,
| | code VARCHAR(50) NOT NULL ,
| | num_licenses INT NOT NULL ,
| | init_valid_date DATE NOT NULL default today(),
| | end_valid_date DATE NOT NULL default today(),
| | status VARCHAR(2) NOT NULL default 'PE',
| | comments VARCHAR(1024) NULL ,
| | license_type_id INT NOT NULL,
| | organization_id INT NOT NULL,
| | license_preactivation BOOLEAN NOT NULL DEFAULT true,
| | created_by varchar(45) NULL ,
| | creation_timestamp DATETIME NOT NULL ,
| | PRIMARY KEY (id));
| |
| | drop table IF EXISTS pack_metadata;
| | CREATE TABLE IF NOT EXISTS pack_metadata (
| | pack_id INT NOT NULL ,
| | key VARCHAR(100) NOT NULL ,
| | value VARCHAR(200) NULL ,
| | readonly BOOlEAN NOT NULL default false,
| | mandatory BOOLEAN NOT NULL default true,
| | PRIMARY KEY (pack_id, key));
| |
| |
| | drop table IF EXISTS license;
| | CREATE TABLE IF NOT EXISTS license (
| | id INT NOT NULL auto_increment,
| | code VARCHAR(100) NOT NULL ,
| | request_data VARCHAR(1024) NULL ,
| | license_data VARCHAR(1024) NULL ,
| | pack_id INT NOT NULL,
| | full_name VARCHAR(150) NULL,
| | email VARCHAR(100) NOT NULL,
| | comments VARCHAR(1024) NULL ,
| | creation_timestamp DATETIME NOT NULL ,
| | modification_timestamp DATETIME NULL ,
| | last_access_timestamp DATETIME NULL ,
| | expiration_date DATETIME NULL ,
| | canceled_by varchar(45) NULL ,
| | created_by varchar(45) NULL ,
| | status INT NOT NULL default 0,
| | PRIMARY KEY (id));
| |
| |
| | drop table IF EXISTS license_history;
| | CREATE TABLE IF NOT EXISTS license_history (
| | id INT NOT NULL auto_increment,
| | license_id INT NOT NULL,
| | username VARCHAR(45) NOT NULL,
| | timestamp DATETIME NOT NULL ,
| | action VARCHAR(40) ,
| | comments VARCHAR(512) ,
| | PRIMARY KEY (id));
| |
| |
| |
|
|
