164 lines
6.9 KiB
SQL
164 lines
6.9 KiB
SQL
create database if not exists FPDAM2023 character set utf8mb4 collate utf8mb4_general_ci;
|
|
use FPDAM2023;
|
|
|
|
delimiter ;^
|
|
|
|
drop procedure if exists tables_remove;^
|
|
create procedure tables_remove() begin
|
|
|
|
-- Level 1.
|
|
drop table if exists Enrollments;
|
|
|
|
-- Level 0.
|
|
drop table if exists Partners;
|
|
drop table if exists Courses;
|
|
drop table if exists Centers;
|
|
|
|
end;^
|
|
|
|
drop procedure if exists tables_create;^
|
|
create procedure tables_create() begin
|
|
|
|
-- Level 0.
|
|
create table if not exists Partners(
|
|
id integer not null auto_increment,
|
|
`session` integer not null,
|
|
`procedure` integer not null,
|
|
code char(10) not null,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint partners_id primary key(id),
|
|
constraint partners_session foreign key(`session`) references AnP.Sessions(id),
|
|
constraint partners_procedure foreign key(`procedure`) references AnP.Procedures(id)
|
|
);
|
|
|
|
create table if not exists Courses(
|
|
id integer not null auto_increment,
|
|
`session` integer not null,
|
|
`procedure` integer not null,
|
|
code char(7) not null,
|
|
name varchar(256) not null,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint courses_id primary key(id),
|
|
constraint courses_session foreign key(`session`) references AnP.Sessions(id),
|
|
constraint courses_procedure foreign key(`procedure`) references AnP.Procedures(id)
|
|
);
|
|
|
|
create table if not exists Centers(
|
|
id integer not null auto_increment,
|
|
`session` integer not null,
|
|
`procedure` integer not null,
|
|
code integer not null,
|
|
name varchar(256) not null,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint centers_id primary key(id),
|
|
constraint centers_session foreign key(`session`) references AnP.Sessions(id),
|
|
constraint centers_procedure foreign key(`procedure`) references AnP.Procedures(id)
|
|
);
|
|
|
|
create table if not exists EnrollmentsTypes(
|
|
id integer not null auto_increment,
|
|
`session` integer not null,
|
|
`procedure` integer not null,
|
|
name varchar(32) not null,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint enrollments_types_id primary key(id),
|
|
constraint enrollments_types_session foreign key(`session`) references AnP.Sessions(id),
|
|
constraint enrollments_types_procedure foreign key(`procedure`) references AnP.Procedures(id)
|
|
);
|
|
|
|
-- Level 1.
|
|
create table if not exists Enrollments(
|
|
id integer not null auto_increment,
|
|
`session` integer not null,
|
|
`procedure` integer not null,
|
|
`year` smallint not null,
|
|
`type` integer not null,
|
|
partner integer not null,
|
|
center integer not null,
|
|
course integer not null,
|
|
score tinyint not null,
|
|
date_in datetime not null default now(),
|
|
date_out datetime,
|
|
constraint enrollments_id primary key(id),
|
|
constraint enrollments_session foreign key(`session`) references AnP.Sessions(id),
|
|
constraint enrollments_procedure foreign key(`procedure`) references AnP.Procedures(id),
|
|
constraint enrollments_type foreign key(`type`) references EnrollmentsTypes(id),
|
|
constraint enrollments_partner foreign key(partner) references Partners(id),
|
|
constraint enrollments_center foreign key(center) references Centers(id),
|
|
constraint enrollments_course foreign key(course) references Courses(id)
|
|
);
|
|
|
|
end;^
|
|
|
|
drop procedure if exists tables_update;^
|
|
create procedure tables_update() begin
|
|
|
|
declare `$database` varchar(64) default AnP.settings_get('database_fpdam2023', false);
|
|
|
|
-- Partners.
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Partners' && column_name = 'code' limit 1) is null then
|
|
alter table Partners add column code char(10) not null;
|
|
end if;
|
|
|
|
-- Courses.
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Courses' && column_name = 'code' limit 1) is null then
|
|
alter table Courses add column code char(7) not null;
|
|
end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Courses' && column_name = 'name' limit 1) is null then
|
|
alter table Courses add column name varchar(256) not null;
|
|
end if;
|
|
|
|
-- Centers.
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Centers' && column_name = 'code' limit 1) is null then
|
|
alter table Centers add column code integer not null;
|
|
end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Centers' && column_name = 'name' limit 1) is null then
|
|
alter table Centers add column name varchar(256) not null;
|
|
end if;
|
|
|
|
-- EnrollmentsTypes.
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'EnrollmentsTypes' && column_name = 'name' limit 1) is null then
|
|
alter table EnrollmentsTypes add column name varchar(32) not null;
|
|
end if;
|
|
|
|
-- Enrollments.
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Enrollments' && column_name = 'year' limit 1) is null then
|
|
alter table Enrollments add column `year` smallint not null;
|
|
end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Enrollments' && column_name = 'type' limit 1) is null then begin
|
|
alter table Enrollments add column `type` integer not null;
|
|
alter table Enrollments add constraint enrollments_type foreign key(`type`) references EnrollmentsTypes(id);
|
|
end;end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Enrollments' && column_name = 'partner' limit 1) is null then begin
|
|
alter table Enrollments add column partner integer not null;
|
|
alter table Enrollments add constraint enrollments_partner foreign key(partner) references Partners(id);
|
|
end;end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Enrollments' && column_name = 'center' limit 1) is null then begin
|
|
alter table Enrollments add column center integer not null;
|
|
alter table Enrollments add constraint enrollments_center foreign key(center) references Centers(id);
|
|
end;end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Enrollments' && column_name = 'course' limit 1) is null then begin
|
|
alter table Enrollments add column course integer not null;
|
|
alter table Enrollments add constraint enrollments_course foreign key(course) references Courses(id);
|
|
end;end if;
|
|
if (select 1 from information_schema.columns where table_schema = `$database` && table_name = 'Enrollments' && column_name = 'score' limit 1) is null then
|
|
alter table Enrollments add column score tinyint not null;
|
|
end if;
|
|
|
|
end;^
|
|
|
|
drop procedure if exists tables_fill;^
|
|
create procedure tables_fill() begin
|
|
|
|
end;^
|
|
|
|
call tables_remove();^
|
|
call tables_create();^
|
|
call tables_update();^
|
|
call tables_fill();^
|
|
|
|
delimiter ; |