|
|
|
CREATE TABLE notification_destinations (
|
|
destination_id int NOT NULL,
|
|
address varchar(250) NOT NULL,
|
|
type_id varchar(50) NOT NULL,
|
|
pirate_id varchar(250) NOT NULL,
|
|
PRIMARY KEY (destination_id)
|
|
);
|
|
|
|
|
|
INSERT INTO notification_destinations ( destination_id , address , type_id , pirate_id ) VALUES
|
|
(1, '33333333333', 'fb', 'marek.krejpsky'),
|
|
(2, 'marek.fffwefwer', 'sms', 'marek.krejpsky'),
|
|
(3, '434444444444', 'email', 'marek.krejpsky'),
|
|
(6, '222222222222222222', 'zulip', 'marek.krejpsky'),
|
|
(7, 'fferer.krejpsky', 'signal', 'marek.krejpsky');
|
|
|
|
|
|
CREATE TABLE notification_sources (
|
|
source_id int NOT NULL,
|
|
name varchar(100) NOT NULL,
|
|
sender_id varchar(250) NOT NULL,
|
|
group_id varchar(250) NOT NULL,
|
|
PRIMARY KEY (source_id)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX sender_to_group_idx ON notification_sources (sender_id,group_id);
|
|
|
|
--
|
|
|
|
INSERT INTO notification_sources ( source_id , name , sender_id , group_id ) VALUES
|
|
(1, 'Zprávy od Helios pro PKS Vysocina', 'HELIOS_VOTING_MODULE', 'deadbeef-babe-f002-000000000112'),
|
|
(2, 'Zprávy Forum pro PKS Vysocina', 'FORUM_PHPBB_NEWTHREAD_MODULE', 'deadbeef-babe-f002-000000000112'),
|
|
(3, 'Zprávy Piratsky Kalendar pro PKS Vysocina', 'EVENT_SCHEDULER_PICALENDAR', 'deadbeef-babe-f002-000000000112'),
|
|
(4, 'Zpravy Helios pro KS Vysocina', 'HELIOS_VOTING_MODULE', 'deadbeef-babe-f002-000000000037'),
|
|
(5, 'Zpravy Forum pro KS Vysocina', 'FORUM_PHPBB_NEWTHREAD_MODULE', 'deadbeef-babe-f002-000000000037');
|
|
|
|
|
|
CREATE TABLE notification_types (
|
|
type_id varchar(50) NOT NULL,
|
|
script varchar(250) NOT NULL,
|
|
name varchar(250) NOT NULL,
|
|
PRIMARY KEY (type_id)
|
|
);
|
|
|
|
INSERT INTO notification_types ( type_id , script , name ) VALUES
|
|
('email', '/usr/local/pinotice/pinotice_send_mail.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'Email zpráva'),
|
|
('fb', '/usr/local/pinotice/pinotice_send_FB.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'zprava na Facebook '),
|
|
('signal', '/usr/local/pinotice/pinotice_send_SIGNAL.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'SIGNAL zprava'),
|
|
('sms', '/usr/local/pinotice/pinotice_send_SMS.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'SMS zpráva'),
|
|
('zulip', '/usr/local/pinotice/pinotice_send_ZULIP.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'Zulip oznámení');
|
|
|
|
|
|
CREATE TABLE pirates (
|
|
pirate_id varchar(250) NOT NULL,
|
|
name varchar(250) NOT NULL,
|
|
comment text NOT NULL,
|
|
email varchar(250) NOT NULL,
|
|
PRIMARY KEY (pirate_id)
|
|
);
|
|
|
|
|
|
CREATE TABLE pirate_in_group (
|
|
pirate_id varchar(250) NOT NULL,
|
|
group_id varchar(250) NOT NULL,
|
|
PRIMARY KEY (pirate_id,group_id)
|
|
);
|
|
|
|
|
|
|
|
CREATE TABLE pirate_receives (
|
|
destination_id int NOT NULL,
|
|
source_id int NOT NULL,
|
|
subject_pattern varchar(250) NOT NULL
|
|
);
|
|
|
|
CREATE INDEX src_idx ON pirate_receives (source_id);
|
|
CREATE INDEX dst_idx ON pirate_receives (destination_id);
|
|
|
|
|
|
CREATE TABLE target_groups (
|
|
group_id varchar(250) NOT NULL,
|
|
email_alias varchar(250) NOT NULL,
|
|
name text NOT NULL,
|
|
about text NOT NULL,
|
|
PRIMARY KEY (group_id)
|
|
);
|
|
|