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
| | 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 ,
| | 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 (
| | id INT NOT NULL auto_increment,
| | license_type_id INT NOT NULL ,
| | key VARCHAR(100) NOT NULL ,
| | value VARCHAR(200) NULL ,
| | PRIMARY KEY (id));
| |
| | 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 ,
| | 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 (
| | id INT NOT NULL auto_increment,
| | pack_id INT NOT NULL ,
| | key VARCHAR(100) NOT NULL ,
| | value VARCHAR(200) NULL ,
| | readonly BOOlEAN NOT NULL default false,
| | PRIMARY KEY (id));
| |
| |
| | 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));
| |
| |
| |
|
|
