drop trigger info_cache_trg;
drop table info_cache;
drop sequence info_cache_seq;

drop trigger info_table_columns_trg;
drop sequence into_table_columns_seq;
drop table info_table_columns;

drop trigger info_objects_trg;
drop sequence info_objects_seq;
drop table info_objects;

drop trigger info_tables_trg;
drop sequence info_tables_seq;
drop table info_tables;

drop trigger info_schemes_trg;
drop sequence info_schemes_seq;
drop table info_schemes;

drop trigger info_session_trg;
drop table info_session;
drop sequence info_session_seq;

create table info_schemes(
scheme_id int not null primary key,
scheme_name varchar(100)
);
create sequence info_schemes_seq start with 1 increment by 1;

create trigger info_schemes_trg
before insert on info_schemes
for each row
begin
    if :new.scheme_id is null then
        select info_schemes_seq.nextval into :new.scheme_id from dual;
    end if;
end;

commit;
create table info_tables(
table_id int not null primary key,
table_name varchar(100),
scheme_id int not null,
primary_column_name varchar(100),
foreign key(scheme_id) references info_schemes(scheme_id)
);

create sequence info_tables_seq start with 1 increment by 1;

create trigger info_tables_trg
before insert on info_tables
for each row
begin
    if :new.table_id is null then
        select info_tables_seq.nextval into :new.table_id from dual;
    end if;
end;
commit;

create table info_objects(
object_id int not null primary key,
table_id int not null,
table_object_id int not null,
foreign key(table_id) references info_tables(table_id)
);

create sequence info_objects_seq start with 1 increment by 1;

create trigger info_objects_trg
before insert on info_objects
for each row
begin
    if :new.object_id is null then
        select info_objects_seq.nextval into :new.object_id from dual;
    end if;
end;
commit;

create table info_table_columns(
column_id int not null primary key,
column_name varchar(100),
table_id int not null,
data_type varchar(20),
foreign key(table_id) references info_tables(table_id)
);

create sequence info_table_columns_seq start with 1 increment by 1;

create trigger info_table_columns_trg
before insert on info_table_columns
for each row
begin
    if :new.column_id is null then
        select info_table_columns_seq.nextval into :new.column_id from dual;
    end if;
end;
commit;

create sequence info_session_seq start with 1 increment by 1 cache 2;

create table info_session(
session_id int not null primary key,
creation_date date not null);

create trigger info_session_trg
before insert on info_session
for each row
begin
    if :new.session_id is null then
        select info_session_seq.nextval into :new.session_id from dual;
    end if;
end;

create sequence info_cache_seq start with 1 increment by 1 cache 2;

create table info_cache(
cache_id int not null primary key,
session_id int not null,
column_id int not null,
object_id int not null,
str_val varchar2(2000),
int_val int,
float_val float,
date_val date,
foreign key(session_id) references info_session(session_id),
foreign key(column_id) references info_table_columns(column_id),
foreign key(object_id) references info_objects(object_id));

create trigger info_cache_trg
before insert on info_cache
for each row
begin
    if :new.cache_id is null then
        select info_cache_seq.nextval into :new.cache_id from dual;
    end if;
end;

insert into info_schemes(scheme_name)
values('domodedovo_uag');
commit;

insert into info_tables(table_name, scheme_id, primary_column_name)
values('buildings', 1, 'building_id');
commit;

insert into info_objects(table_id, table_object_id)
values(1, 17501);
insert into info_objects(table_id, table_object_id)
values(1, 17567);
commit;

insert into info_table_columns(column_name, table_id)
values('floors', 1);
insert into info_table_columns(column_name, table_id)
values('total_area', 1);
insert into info_Table_columns(column_name, table_id)
values('build_name', 1);
commit;