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
132
133
134
135
136
137
138
139
140
141
142
143
144
| |
| | drop table IF EXISTS settings;
| | CREATE TABLE IF NOT EXISTS settings (
| | `key` VARCHAR(100) NOT NULL ,
| | value VARCHAR(2000) NULL ,
| | modification_timestamp TIMESTAMP 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(256) NULL ,
| | roles INT NOT NULL default 0,
| | first_name VARCHAR(100) NULL ,
| | last_name VARCHAR(100) NULL ,
| | last_login TIMESTAMP NULL ,
| | lang VARCHAR(10) NULL ,
| | email VARCHAR(150) NULL ,
| | creation_timestamp TIMESTAMP NOT NULL default now(),
| | modification_timestamp TIMESTAMP 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 TIMESTAMP NOT NULL default now(),
| | 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 TIMESTAMP NULL default now(),
| | 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 TIMESTAMP NOT NULL default now(),
| | 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 TIMESTAMP NOT NULL default now(),
| | 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,
| | end_valid_date DATE NOT NULL,
| | status VARCHAR(2) NOT NULL default 'CR',
| | comments VARCHAR(1024) NULL ,
| | license_type_id INT NOT NULL,
| | organization_id INT NOT NULL,
| | license_preactivation BOOLEAN NOT NULL DEFAULT true,
| | preactivation_valid_period INT NOT NULL DEFAULT 7,
| | renew_valid_period INT NOT NULL DEFAULT 30,
| | created_by varchar(45) NULL ,
| | creation_timestamp TIMESTAMP NOT NULL default now(),
| | 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 ,
| | code_suffix INT NULL ,
| | request_data VARCHAR(1024) NULL ,
| | request_data_hash VARCHAR(64) NULL ,
| | license_data VARCHAR(1024) NULL ,
| | pack_id INT NOT NULL,
| | full_name VARCHAR(150) NULL,
| | email VARCHAR(100) NULL,
| | comments VARCHAR(1024) NULL ,
| | creation_timestamp TIMESTAMP NOT NULL default now(),
| | modification_timestamp TIMESTAMP NULL ,
| | last_access_timestamp TIMESTAMP NULL ,
| | expiration_date TIMESTAMP NULL ,
| | cancelled_by varchar(45) NULL ,
| | created_by varchar(45) NULL ,
| | status VARCHAR(2) NOT NULL default 'CR',
| | PRIMARY KEY (id),
| | index(request_data_hash, pack_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,
| | creation_timestamp TIMESTAMP NOT NULL default now(),
| | action VARCHAR(40) ,
| | comments VARCHAR(512) ,
| | PRIMARY KEY (id));
| |
| |
| | drop table IF EXISTS blocked_request;
| | CREATE TABLE IF NOT EXISTS blocked_request (
| | hash VARCHAR(64) NOT NULL ,
| | request_data VARCHAR(1024) NULL ,
| | blocked_by varchar(45) NULL ,
| | creation_timestamp TIMESTAMP NOT NULL default now(),
| | PRIMARY KEY (hash));
| |
|
|
