|
1
|
|
|
2
|
CREATE TABLE notification_destinations (
|
|
3
|
destination_id int NOT NULL,
|
|
4
|
address varchar(250) NOT NULL,
|
|
5
|
type_id varchar(50) NOT NULL,
|
|
6
|
pirate_id varchar(250) NOT NULL,
|
|
7
|
PRIMARY KEY (destination_id)
|
|
8
|
);
|
|
9
|
|
|
10
|
|
|
11
|
INSERT INTO notification_destinations ( destination_id , address , type_id , pirate_id ) VALUES
|
|
12
|
(1, '33333333333', 'fb', 'marek.krejpsky'),
|
|
13
|
(2, 'marek.fffwefwer', 'sms', 'marek.krejpsky'),
|
|
14
|
(3, '434444444444', 'email', 'marek.krejpsky'),
|
|
15
|
(6, '222222222222222222', 'zulip', 'marek.krejpsky'),
|
|
16
|
(7, 'fferer.krejpsky', 'signal', 'marek.krejpsky');
|
|
17
|
|
|
18
|
|
|
19
|
CREATE TABLE notification_sources (
|
|
20
|
source_id int NOT NULL,
|
|
21
|
name varchar(100) NOT NULL,
|
|
22
|
sender_id varchar(250) NOT NULL,
|
|
23
|
group_id varchar(250) NOT NULL,
|
|
24
|
PRIMARY KEY (source_id)
|
|
25
|
);
|
|
26
|
|
|
27
|
CREATE UNIQUE INDEX sender_to_group_idx ON notification_sources (sender_id,group_id);
|
|
28
|
|
|
29
|
--
|
|
30
|
|
|
31
|
INSERT INTO notification_sources ( source_id , name , sender_id , group_id ) VALUES
|
|
32
|
(1, 'Zprávy od Helios pro PKS Vysocina', 'HELIOS_VOTING_MODULE', 'deadbeef-babe-f002-000000000112'),
|
|
33
|
(2, 'Zprávy Forum pro PKS Vysocina', 'FORUM_PHPBB_NEWTHREAD_MODULE', 'deadbeef-babe-f002-000000000112'),
|
|
34
|
(3, 'Zprávy Piratsky Kalendar pro PKS Vysocina', 'EVENT_SCHEDULER_PICALENDAR', 'deadbeef-babe-f002-000000000112'),
|
|
35
|
(4, 'Zpravy Helios pro KS Vysocina', 'HELIOS_VOTING_MODULE', 'deadbeef-babe-f002-000000000037'),
|
|
36
|
(5, 'Zpravy Forum pro KS Vysocina', 'FORUM_PHPBB_NEWTHREAD_MODULE', 'deadbeef-babe-f002-000000000037');
|
|
37
|
|
|
38
|
|
|
39
|
CREATE TABLE notification_types (
|
|
40
|
type_id varchar(50) NOT NULL,
|
|
41
|
script varchar(250) NOT NULL,
|
|
42
|
name varchar(250) NOT NULL,
|
|
43
|
PRIMARY KEY (type_id)
|
|
44
|
);
|
|
45
|
|
|
46
|
INSERT INTO notification_types ( type_id , script , name ) VALUES
|
|
47
|
('email', '/usr/local/pinotice/pinotice_send_mail.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'Email zpráva'),
|
|
48
|
('fb', '/usr/local/pinotice/pinotice_send_FB.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'zprava na Facebook '),
|
|
49
|
('signal', '/usr/local/pinotice/pinotice_send_SIGNAL.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'SIGNAL zprava'),
|
|
50
|
('sms', '/usr/local/pinotice/pinotice_send_SMS.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'SMS zpráva'),
|
|
51
|
('zulip', '/usr/local/pinotice/pinotice_send_ZULIP.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'Zulip oznámení');
|
|
52
|
|
|
53
|
|
|
54
|
CREATE TABLE pirates (
|
|
55
|
pirate_id varchar(250) NOT NULL,
|
|
56
|
name varchar(250) NOT NULL,
|
|
57
|
comment text NOT NULL,
|
|
58
|
email varchar(250) NOT NULL,
|
|
59
|
PRIMARY KEY (pirate_id)
|
|
60
|
);
|
|
61
|
|
|
62
|
|
|
63
|
CREATE TABLE pirate_in_group (
|
|
64
|
pirate_id varchar(250) NOT NULL,
|
|
65
|
group_id varchar(250) NOT NULL,
|
|
66
|
PRIMARY KEY (pirate_id,group_id)
|
|
67
|
);
|
|
68
|
|
|
69
|
|
|
70
|
|
|
71
|
CREATE TABLE pirate_receives (
|
|
72
|
destination_id int NOT NULL,
|
|
73
|
source_id int NOT NULL,
|
|
74
|
subject_pattern varchar(250) NOT NULL
|
|
75
|
);
|
|
76
|
|
|
77
|
CREATE INDEX src_idx ON pirate_receives (source_id);
|
|
78
|
CREATE INDEX dst_idx ON pirate_receives (destination_id);
|
|
79
|
|
|
80
|
|
|
81
|
CREATE TABLE target_groups (
|
|
82
|
group_id varchar(250) NOT NULL,
|
|
83
|
email_alias varchar(250) NOT NULL,
|
|
84
|
name text NOT NULL,
|
|
85
|
about text NOT NULL,
|
|
86
|
PRIMARY KEY (group_id)
|
|
87
|
);
|
|
88
|
|