Приложение б Листинг программного кода

Создание таблиц:

create table country ( id serial primary key, name varchar(64) not null unique ); create table series ( id int primary key, name varchar(64) not null ); create table color ( id serial primary key, name varchar(64) not null unique ); create table theme ( id serial primary key, name varchar(96) not null, is_closed bool default false not null ); create table section ( id serial primary key, theme_id int references theme on delete restrict, country_id int references country on delete restrict, unique (theme_id, country_id) ); create table stamp ( id serial primary key, id_number int not null, series_id int not null references series on delete restrict, color_id int references color on delete restrict, section_id int not null references section on delete restrict, width int check (width > 0), height int check (height > 0), price numeric(11, 2) check (price >= 0), release_year smallint, unique (id_number, series_id) ); create table stamp_position ( stamp_id int primary key references stamp on delete cascade, volume int not null check (volume > 0), page int not null check (page > 0), x int not null check (x >= 0), y int not null check (y >= 0) );

Создание функций манипулирования данными:

create function add_stamp( id_number_ int, series_id_ int, series_name_ varchar(64), color_ varchar(64), country_ varchar(64), theme_ varchar(96), width_ int, height_ int, price_ numeric(11,2), release_year_ int ) returns void language plpgsql as $$ declare color_id_ int; country_id_ int; theme_id_ int; section_id_ int; begin select id from color where name = color_ into color_id_; if color_id_ is null then insert into color (name) values (color_) returning id into color_id_; end if; select id from country where name = country_ into country_id_; if country_id_ is null then insert into country (name) values (country_) returning id into country_id_; end if; select id from theme where name = theme_ and is_closed = false into theme_id_; if theme_id_ is null then insert into theme (name) values (theme_) returning id into theme_id_; end if; select id from section where country_id = country_id_ and theme_id = theme_id_ into section_id_; if section_id_ is null then insert into section (country_id, theme_id) values (country_id_, theme_id_) returning id into section_id_; end if; insert into series (id, name) values (series_id_, series_name_) on conflict do nothing; insert into stamp (id_number, series_id, color_id, section_id, width, height, price, release_year) values (id_number_, series_id_, color_id_, section_id_, width_, height_, price_, release_year_); end $$; create function remove_stamps_by_theme( theme_ varchar(96), is_closed_ bool ) returns void language plpgsql as $$ begin delete from stamp using section join theme on theme.id = section.theme_id where stamp.section_id = section.id and theme.name = theme_ and theme.is_closed = is_closed_; end $$; create function set_stamp_position( id_number_ int, serial_id_ int, volume_ int, page_ int, x_ int, y_ int ) returns void language plpgsql as $$ declare stamp_id_ int; begin select id from stamp where id_number = id_number_ and series_id = serial_id_ into stamp_id_; if stamp_id_ is not null then insert into stamp_position (stamp_id, volume, page, x, y) values (stamp_id_, volume_, page_, x_, y_) on conflict (stamp_id) do update set volume = volume_, page = page_, x = x_, y = y_; end if; end $$;

Создание функций поисковых запросов:

create function get_stamp_country_from_section( section_id_ int ) returns table(country varchar) language plpgsql as $$ begin return query select country.name from section join country on country.id = section.country_id where section.id = section_id_; end $$; create function get_volumes_from_series( series_ varchar(64) ) returns table(volume int) language plpgsql as $$ begin return query select stamp_position.volume from stamp_position join stamp on stamp_position.stamp_id = stamp.id join series on series.id = stamp.series_id where series.name = series_ group by stamp_position.volume; end $$; create function get_positions_from_theme( theme_ varchar(64) ) returns table(volume int, page int, x int, y int) language plpgsql as $$ begin return query select stamp_position.volume, stamp_position.page, stamp_position.x, stamp_position.y from stamp_position join stamp on stamp_position.stamp_id = stamp.id join section on stamp.section_id = section.id join theme on theme.id = section.theme_id where theme.name = theme_; end $$; create function get_series_themes_from_stamp_size( width_ int, height_ int ) returns table(series_name varchar(64)) language plpgsql as $$ begin return query select series.name from series join stamp on series.id = stamp.series_id where stamp.width = width_ and stamp.height = height_ group by series.name; end $$; create function get_stamp_country_from_position( volume_ int, page_ int, x_ int, y_ int ) returns table(country varchar(64)) language plpgsql as $$ begin return query select country.name from country join section on country.id = section.country_id join stamp on section.id = stamp.section_id join stamp_position on stamp.id = stamp_position.stamp_id where stamp_position.volume = volume_ and stamp_position.page = page_ and stamp_position.x = x_ and stamp_position.y = y_; end $$;

Создание функций справок и отчётов:

create function report_stamp_countries_from_theme( theme_ varchar(64) ) returns table(country varchar(64)) language plpgsql as $$ begin return query select country.name from country join section on country.id = section.country_id join theme on theme.id = section.theme_id where theme.name = theme_; end $$; create function report_collection_summary() returns void language plpgsql as $$ declare items record; theme_items record; country_items record; begin raise notice 'Country Count: %', (select count(*) from (select 1 from section join country on country.id = section.country_id group by country.id) as sc); raise notice 'Country List:'; for items in select country.name from section join country on country.id = section.country_id group by country.id loop raise notice '- %', items.name; end loop; raise notice ' '; raise notice 'Theme Count: %', (select count(*) from (select 1 from section join theme on theme.id = section.theme_id group by theme.id) as sc); raise notice 'Theme List:'; for items in select theme.name from section join theme on theme.id = section.theme_id group by theme.id loop raise notice '- %', items.name; end loop; raise notice ' '; raise notice 'Stamp Count by Themes and Countries:'; for theme_items in select theme.id, theme.name from section join theme on theme.id = section.theme_id group by theme.id loop for country_items in select country.id, country.name from section join country on country.id = section.country_id where section.theme_id = theme_items.id group by country.id loop raise notice '- Theme: %, Country: %, Stamp Count: %', theme_items.name, country_items.name, (select count(stamp.id) from section join stamp on section.id = stamp.section_id where section.country_id = country_items.id and section.theme_id = theme_items.id); end loop; end loop; raise notice ' '; raise notice 'Page Count: %', (select sum(spsc.c) from (select count(stamp_position.page) as c from stamp_position join stamp on stamp.id = stamp_position.stamp_id group by stamp.section_id, stamp_position.volume, stamp_position.page) as spsc); end $$;

Создание и настройка пользователей:

create user collector superuser password 'q56fd9wj'; create user guest; grant execute on function report_collection_summary() to guest;