267 lines
12 KiB
SQL
267 lines
12 KiB
SQL
create database if not exists DPTW character set utf8mb4 collate utf8mb4_general_ci;
|
|
use DPTW;
|
|
|
|
delimiter ;^
|
|
|
|
drop procedure if exists tables_remove;^
|
|
create procedure tables_remove() begin
|
|
|
|
-- Level 2.
|
|
drop table if exists Scores;
|
|
drop table if exists Exceptions;
|
|
drop table if exists Logs;
|
|
|
|
-- Level 1.
|
|
drop table if exists Sessions;
|
|
drop table if exists Procedures;
|
|
|
|
-- Level 0.
|
|
drop table if exists Modes;
|
|
drop table if exists Nicks;
|
|
drop table if exists Parameters;
|
|
drop table if exists Messages;
|
|
drop table if exists `Databases`;
|
|
drop table if exists UserAgents;
|
|
drop table if exists Ips;
|
|
|
|
end;^
|
|
|
|
drop procedure if exists tables_create;^
|
|
create procedure tables_create() begin
|
|
|
|
-- Level 0.
|
|
create table if not exists Ips(
|
|
id integer not null auto_increment,
|
|
address varchar(39) not null,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint ips_id primary key(id)
|
|
);
|
|
|
|
create table if not exists UserAgents(
|
|
id integer not null auto_increment,
|
|
`data` text not null,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint user_agents_id primary key(id)
|
|
);
|
|
|
|
create table if not exists `Databases`(
|
|
id integer not null auto_increment,
|
|
name varchar(64) not null,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint databases_id primary key(id)
|
|
);
|
|
|
|
create table if not exists Parameters(
|
|
id integer not null auto_increment,
|
|
`data` text,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint parameters_id primary key(id)
|
|
);
|
|
|
|
create table if not exists Messages(
|
|
id integer not null auto_increment,
|
|
`text` text,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint messages_id primary key(id)
|
|
);
|
|
|
|
create table if not exists Nicks(
|
|
id integer not null auto_increment,
|
|
name varchar(32) not null,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint nicks_id primary key(id)
|
|
);
|
|
|
|
create table if not exists Modes( -- For different game settings.
|
|
id integer not null auto_increment,
|
|
name varchar(32) not null,
|
|
description text,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint nicks_id primary key(id)
|
|
);
|
|
|
|
-- Level 1.
|
|
create table if not exists Sessions(
|
|
id integer not null auto_increment,
|
|
ip integer not null,
|
|
user_agent integer not null,
|
|
date_in datetime not null default now(),
|
|
date_last datetime not null default now(),
|
|
date_out datetime,
|
|
constraint sessions_id primary key(id),
|
|
constraint sessions_ip foreign key(ip) references Ips(id),
|
|
constraint sessions_user_agent foreign key(user_agent) references UserAgents(id)
|
|
);
|
|
|
|
create table if not exists Procedures(
|
|
id integer not null auto_increment,
|
|
`database` integer not null,
|
|
name varchar(64) not null,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint procedures_id primary key(id),
|
|
constraint procedures_database foreign key(`database`) references `Databases`(id)
|
|
);
|
|
|
|
-- Level 2.
|
|
create table if not exists Logs(
|
|
id integer not null auto_increment,
|
|
`session` integer,
|
|
`procedure` integer not null,
|
|
parameters integer not null,
|
|
error bigint,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint logs_id primary key(id),
|
|
constraint logs_session foreign key(`session`) references Sessions(id),
|
|
constraint logs_procedure foreign key(`procedure`) references Procedures(id),
|
|
constraint logs_parameters foreign key(parameters) references Parameters(id)
|
|
);
|
|
|
|
create table if not exists Exceptions(
|
|
id integer not null auto_increment,
|
|
`session` integer,
|
|
`procedure` integer not null,
|
|
parameters integer not null,
|
|
error bigint,
|
|
message integer not null,
|
|
status varchar(16),
|
|
code integer,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint exceptions_id primary key(id),
|
|
constraint exceptions_session foreign key(`session`) references Sessions(id),
|
|
constraint exceptions_procedure foreign key(`procedure`) references Procedures(id),
|
|
constraint exceptions_parameters foreign key(parameters) references Parameters(id),
|
|
constraint exceptions_message foreign key(message) references Messages(id)
|
|
);
|
|
|
|
create table if not exists Scores(
|
|
id integer not null auto_increment,
|
|
`session` integer not null,
|
|
`procedure` integer not null,
|
|
mode integer not null,
|
|
nick integer not null,
|
|
score integer not null,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint scores_id primary key(id),
|
|
constraint scores_session foreign key(`session`) references Sessions(id),
|
|
constraint scores_procedure foreign key(`procedure`) references Procedures(id),
|
|
constraint scores_mode foreign key(mode) references Modes(id),
|
|
constraint scores_nick foreign key(nick) references Nicks(id)
|
|
);
|
|
|
|
end;^
|
|
|
|
drop procedure if exists tables_update;^
|
|
create procedure tables_update() begin
|
|
|
|
declare `$database` varchar(64) default 'DPTW';
|
|
|
|
-- Level 0.
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Ips' && column_name = 'address' limit 1) is null then
|
|
alter table Ips add column address varchar(39) not null after id;
|
|
end if;
|
|
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'UserAgents' && column_name = 'data' limit 1) is null then
|
|
alter table UserAgents add column `data` text not null after id;
|
|
end if;
|
|
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Databases' && column_name = 'name' limit 1) is null then
|
|
alter table `Databases` add column name varchar(64) not null after id;
|
|
end if;
|
|
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Parameters' && column_name = 'data' limit 1) is null then
|
|
alter table Parameters add column `data` text not null after id;
|
|
end if;
|
|
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Messages' && column_name = 'text' limit 1) is null then
|
|
alter table Parameters add column `text` text not null after id;
|
|
end if;
|
|
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Nicks' && column_name = 'name' limit 1) is null then
|
|
alter table Nicks add column name varchar(32) not null after id;
|
|
end if;
|
|
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Modes' && column_name = 'name' limit 1) is null then
|
|
alter table Modes add column name varchar(32) not null after id;
|
|
end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Modes' && column_name = 'description' limit 1) is null then
|
|
alter table Modes add column description text not null after name;
|
|
end if;
|
|
|
|
-- Level 1.
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Sessions' && column_name = 'ip' limit 1) is null then begin
|
|
alter table Sessions add column ip integer not null after id;
|
|
alter table Sessions add constraint sessions_ip foreign key(ip) references Ips(id);
|
|
end;end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Sessions' && column_name = 'user_agent' limit 1) is null then begin
|
|
alter table Sessions add column ip integer not null after ip;
|
|
alter table Sessions add constraint sessions_user_agent foreign key(user_agent) references UserAgents(id);
|
|
end;end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Sessions' && column_name = 'date_last' limit 1) is null then
|
|
alter table Sessions add column date_last datetime not null default now() after date_in;
|
|
end if;
|
|
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Procedures' && column_name = 'database' limit 1) is null then begin
|
|
alter table Procedures add column `database` integer not null after id;
|
|
alter table Procedures add constraint procedures_database foreign key(`database`) references `Databases`(id);
|
|
end;end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Procedures' && column_name = 'name' limit 1) is null then
|
|
alter table Procedures add column name varchar(64) not null after `database`;
|
|
end if;
|
|
|
|
-- Level 2.
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Logs' && column_name = 'parameters' limit 1) is null then begin
|
|
alter table Logs add column parameters integer not null after `procedure`;
|
|
alter table Logs add constraint logs_parameters foreign key(parameters) references Parameters(id);
|
|
end;end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Logs' && column_name = 'error' limit 1) is null then
|
|
alter table Logs add column error bigint not null after parameters;
|
|
end if;
|
|
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Exceptions' && column_name = 'parameters' limit 1) is null then begin
|
|
alter table Exceptions add column parameters integer not null after `procedure`;
|
|
alter table Exceptions add constraint exception_parameters foreign key(parameters) references Parameters(id);
|
|
end;end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Exceptions' && column_name = 'error' limit 1) is null then
|
|
alter table Exceptions add column error bigint not null after parameters;
|
|
end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Exceptions' && column_name = 'message' limit 1) is null then begin
|
|
alter table Exceptions add column message integer not null after error;
|
|
alter table Exceptions add constraint exception_message foreign key(message) references Messages(id);
|
|
end;end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Exceptions' && column_name = 'status' limit 1) is null then
|
|
alter table Exceptions add column status varchar(16) after message;
|
|
end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Exceptions' && column_name = 'code' limit 1) is null then
|
|
alter table Exceptions add column code integer after message;
|
|
end if;
|
|
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Scores' && column_name = 'mode' limit 1) is null then begin
|
|
alter table Scores add column mode integer not null after `procedure`;
|
|
alter table Scores add constraint exception_mode foreign key(mode) references Modes(id);
|
|
end;end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Scores' && column_name = 'nick' limit 1) is null then begin
|
|
alter table Scores add column nick integer not null after mode;
|
|
alter table Scores add constraint exception_nick foreign key(nick) references Nicks(id);
|
|
end;end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Scores' && column_name = 'score' limit 1) is null then
|
|
alter table Scores add column score integer not null after nick;
|
|
end if;
|
|
|
|
end;^
|
|
|
|
call tables_remove();^
|
|
call tables_create();^
|
|
call tables_update();^
|
|
|
|
delimiter ; |