105 lines
14 KiB
SQL
Executable File
105 lines
14 KiB
SQL
Executable File
-- # $Id: create_excon_db.py 48761 2019-10-29 14:03:07Z ehrlicher $
|
|
-- # $HeadURL: svn://slssvn/TDS/branches/product/devel/trunk/Lts/Tests/DatabaseInitialisation/create_excon_db.py $
|
|
-- # $Author: ehrlicher $
|
|
|
|
-- **************************
|
|
-- * Create Operator Groups *
|
|
-- **************************
|
|
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1011, 'Christian', 'User', 2, false);
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1012, 'Hanna', 'User', 2, false);
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1013, 'Niklas', 'User', 2, false);
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1014, 'Alex', 'User', 2, false);
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1015, 'Malte', 'User', 2, false);
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1016, 'Mike', 'User', 2, false);
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1017, 'Norbert', 'User', 2, false);
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1018, 'Patric', 'User', 2, false);
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1019, 'Patrick', 'User', 2, false);
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1020, 'Gerlinde', 'User', 2, false);
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1021, 'Hannes', 'User', 2, false);
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1022, 'Steve', 'User', 2, false);
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1023, 'Maik', 'User', 2, false);
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1024, 'Veronica', 'User', 2, false);
|
|
|
|
-- ********************
|
|
-- * Create Operators *
|
|
-- ********************
|
|
|
|
--INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (10, 'Chris', md5('ctc'), 'User', 1010, 1, 1, '{0,1,2,3,4,5}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (11, 'Christian', md5('ctc'), 'User', 1011, 1, 1, '{0,1,2,3,4,5}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (12, 'Hanna', md5('ctc'), 'User', 1012, 1, 1, '{0,1,2,3,4,5}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (13, 'Niklas', md5('ctc'), 'User', 1013, 1, 1, '{0,1,2,3,4,5}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (14, 'Alex', md5('ctc'), 'User', 1014, 1, 1, '{0,1,2,3,4,5}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (15, 'Malte', md5('ctc'), 'User', 1015, 1, 1, '{0,1,2,3,4,5}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (16, 'Mike', md5('ctc'), 'User', 1016, 1, 1, '{0,1,2,3,4,5}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (17, 'Norbert', md5('ctc'), 'User', 1017, 1, 1, '{0,1,2,3,4,5}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (18, 'Patric', md5('ctc'), 'User', 1018, 1, 1, '{0,1,2,3,4,5}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (19, 'Patrick', md5('ctc'), 'User', 1019, 1, 1, '{0,1,2,3,4,5}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (20, 'Gerlinde', md5('ctc'), 'User', 1020, 1, 1, '{0,1,2,3,4,5}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (21, 'Hannes', md5('ctc'), 'User', 1021, 1, 1, '{0,1,2,3,4,5}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (22, 'Steve', md5('ctc'), 'User', 1022, 1, 1, '{0,1,2,3,4,5}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (23, 'Maik', md5('ctc'), 'User', 1023, 1, 1, '{0,1,2,3,4,5}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties) VALUES (24, 'Veronica', md5('ctc'), 'User', 1024, 1, 1, '{0,1,2,3,4,5}');
|
|
|
|
-- *****************************************************
|
|
-- * Put all Operators in "Organisator" groups (1001)
|
|
-- *****************************************************
|
|
INSERT INTO opm.opm_group_group (group_id, is_member_of_group_id)
|
|
SELECT opm.opm_group.id, is_member_of_group_id FROM opm.opm_group_group,opm.opm_group
|
|
WHERE group_id = 1001 AND opm.opm_group.id > 1010 AND opm.opm_group.id <= 1024;
|
|
|
|
UPDATE opm.opm_operator SET role_preset_id = 1001 WHERE id > 10 AND id <= 24;
|
|
|
|
-- ***************************************
|
|
-- * Put all Operators in all exercises *
|
|
-- ***************************************
|
|
|
|
INSERT INTO opm.opm_operator_exercise (operator_id, exercise_id, visible_parties) SELECT opm.opm_operator.id, exm.exercise.id, '{0,1,2,3,4}' FROM opm.opm_operator, exm.exercise WHERE opm.opm_operator.id > 10 and opm.opm_operator.id <= 24 and exm.exercise.id IN (35, 42);
|
|
|
|
ALTER SEQUENCE opm.opm_operator_id_seq RESTART WITH 101;
|
|
--ALTER SEQUENCE opm.opm_group_group_id_seq RESTART WITH 100; -- No need, ids were always given automatically.
|
|
|
|
-- Iuliana in group Organisator
|
|
-- INSERT INTO opm.opm_group_group (group_id, is_member_of_group_id) VALUES (1014, 1001);
|
|
-- UPDATE opm.opm_operator SET role_preset_id = 1001 WHERE id = 14;
|
|
|
|
-- ****************************************************
|
|
-- * Guez PWS operators with role preset Organisator
|
|
-- ****************************************************
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1025, 'DVAP41', 'Schulung AP41', 2, false);
|
|
INSERT INTO opm.opm_group (id, name, description, type_id, is_hidden) VALUES (1026, 'DVAP42', 'Schulung AP42', 2, false);
|
|
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties)
|
|
VALUES (25, '_ECAWPWS01', md5('ctc'), 'Projektions-WS Benutzer', 1025, 1, 1, '{0,1,2,3,4,5,6,7,8,9,10,11,12,13}');
|
|
INSERT INTO opm.opm_operator (id, name, password, description, primary_group_id, projection_priority, audio_channel_priority, visible_parties)
|
|
VALUES (26, '_ECLTPWS01', md5('ctc'), 'Projektions-WS Benutzer', 1026, 1, 1, '{0,1,2,3,4,5,6,7,8,9,10,11,12,13}');
|
|
|
|
INSERT INTO opm.opm_group_group (group_id, is_member_of_group_id)
|
|
SELECT opm.opm_group.id, is_member_of_group_id FROM opm.opm_group_group,opm.opm_group
|
|
WHERE group_id = 1001 AND opm.opm_group.id in (1025, 1026);
|
|
|
|
UPDATE opm.opm_operator SET role_preset_id = 1001 WHERE id in (25, 26);
|
|
|
|
INSERT INTO opm.opm_operator_exercise (operator_id, exercise_id) SELECT opm.opm_operator.id, exm.exercise.id FROM opm.opm_operator, exm.exercise
|
|
WHERE opm.opm_operator.id in (25, 26) and exm.exercise.id IN (35, 42);
|
|
|
|
UPDATE opm.opm_operator_exercise SET properties = JSONB_BUILD_OBJECT('adlerAllowed', visible_parties) WHERE visible_parties is not null;
|
|
|
|
UPDATE opm.opm_operator SET properties = ' {"Exercises": [{"exerciseId": 42, "suppressNotifications": [{"Address": 2, "partyId": 2, "AddressType": 0, "NotificationType": 0}, {"Address": 2, "partyId": 4, "AddressType": 0, "NotificationType": 0}, {"Address": 2, "partyId": 3, "AddressType": 0, "NotificationType": 0}]}]}';
|
|
|
|
-- Settings (Zeitoffset 00:10)
|
|
INSERT INTO opm.opm_settings (id, operator_id, settings) VALUES (1, 11, '\x0000018e789c8d903b0bc24010843f836063e16f1051104965612b846021a4482ac5229807011f219742ffbd934b0c56c15df698db9d9ddd3b60022c08b9525150526370390827a4043cf05489c9c51bc9614da4ea5dd55051cb0bb172dbf7ad9c782a97d2eacb36034dbef04bd9a4e7c0981fdb0d347bc2467bdfb4e3bbe745c2a51572e8d4e622947297a39091c8dede43a158912a07d386ee9c2f3ab77f4ff5f5da4acc46a69bd87cd472402020931b9b6f5b56b30f72043d4b');
|
|
INSERT INTO opm.opm_settings (id, operator_id, settings) VALUES (2, 12, '\x0000018e789c8d903b0bc24010843f836063e16f1051104965612b846021a4482ac5229807011f219742ffbd934b0c56c15df698db9d9ddd3b60022c08b9525150526370390827a4043cf05489c9c51bc9614da4ea5dd55051cb0bb172dbf7ad9c782a97d2eacb36034dbef04bd9a4e7c0981fdb0d347bc2467bdfb4e3bbe745c2a51572e8d4e622947297a39091c8dede43a158912a07d386ee9c2f3ab77f4ff5f5da4acc46a69bd87cd472402020931b9b6f5b56b30f72043d4b');
|
|
INSERT INTO opm.opm_settings (id, operator_id, settings) VALUES (3, 13, '\x0000018e789c8d903b0bc24010843f836063e16f1051104965612b846021a4482ac5229807011f219742ffbd934b0c56c15df698db9d9ddd3b60022c08b9525150526370390827a4043cf05489c9c51bc9614da4ea5dd55051cb0bb172dbf7ad9c782a97d2eacb36034dbef04bd9a4e7c0981fdb0d347bc2467bdfb4e3bbe745c2a51572e8d4e622947297a39091c8dede43a158912a07d386ee9c2f3ab77f4ff5f5da4acc46a69bd87cd472402020931b9b6f5b56b30f72043d4b');
|
|
INSERT INTO opm.opm_settings (id, operator_id, settings) VALUES (4, 15, '\x0000018e789c8d903b0bc24010843f836063e16f1051104965612b846021a4482ac5229807011f219742ffbd934b0c56c15df698db9d9ddd3b60022c08b9525150526370390827a4043cf05489c9c51bc9614da4ea5dd55051cb0bb172dbf7ad9c782a97d2eacb36034dbef04bd9a4e7c0981fdb0d347bc2467bdfb4e3bbe745c2a51572e8d4e622947297a39091c8dede43a158912a07d386ee9c2f3ab77f4ff5f5da4acc46a69bd87cd472402020931b9b6f5b56b30f72043d4b');
|
|
INSERT INTO opm.opm_settings (id, operator_id, settings) VALUES (5, 16, '\x0000018e789c8d903b0bc24010843f836063e16f1051104965612b846021a4482ac5229807011f219742ffbd934b0c56c15df698db9d9ddd3b60022c08b9525150526370390827a4043cf05489c9c51bc9614da4ea5dd55051cb0bb172dbf7ad9c782a97d2eacb36034dbef04bd9a4e7c0981fdb0d347bc2467bdfb4e3bbe745c2a51572e8d4e622947297a39091c8dede43a158912a07d386ee9c2f3ab77f4ff5f5da4acc46a69bd87cd472402020931b9b6f5b56b30f72043d4b');
|
|
INSERT INTO opm.opm_settings (id, operator_id, settings) VALUES (6, 17, '\x0000018e789c8d903b0bc24010843f836063e16f1051104965612b846021a4482ac5229807011f219742ffbd934b0c56c15df698db9d9ddd3b60022c08b9525150526370390827a4043cf05489c9c51bc9614da4ea5dd55051cb0bb172dbf7ad9c782a97d2eacb36034dbef04bd9a4e7c0981fdb0d347bc2467bdfb4e3bbe745c2a51572e8d4e622947297a39091c8dede43a158912a07d386ee9c2f3ab77f4ff5f5da4acc46a69bd87cd472402020931b9b6f5b56b30f72043d4b');
|
|
INSERT INTO opm.opm_settings (id, operator_id, settings) VALUES (7, 18, '\x0000018e789c8d903b0bc24010843f836063e16f1051104965612b846021a4482ac5229807011f219742ffbd934b0c56c15df698db9d9ddd3b60022c08b9525150526370390827a4043cf05489c9c51bc9614da4ea5dd55051cb0bb172dbf7ad9c782a97d2eacb36034dbef04bd9a4e7c0981fdb0d347bc2467bdfb4e3bbe745c2a51572e8d4e622947297a39091c8dede43a158912a07d386ee9c2f3ab77f4ff5f5da4acc46a69bd87cd472402020931b9b6f5b56b30f72043d4b');
|
|
INSERT INTO opm.opm_settings (id, operator_id, settings) VALUES (8, 19, '\x0000018e789c8d903b0bc24010843f836063e16f1051104965612b846021a4482ac5229807011f219742ffbd934b0c56c15df698db9d9ddd3b60022c08b9525150526370390827a4043cf05489c9c51bc9614da4ea5dd55051cb0bb172dbf7ad9c782a97d2eacb36034dbef04bd9a4e7c0981fdb0d347bc2467bdfb4e3bbe745c2a51572e8d4e622947297a39091c8dede43a158912a07d386ee9c2f3ab77f4ff5f5da4acc46a69bd87cd472402020931b9b6f5b56b30f72043d4b');
|
|
INSERT INTO opm.opm_settings (id, operator_id, settings) VALUES (9, 20, '\x0000018e789c8d903b0bc24010843f836063e16f1051104965612b846021a4482ac5229807011f219742ffbd934b0c56c15df698db9d9ddd3b60022c08b9525150526370390827a4043cf05489c9c51bc9614da4ea5dd55051cb0bb172dbf7ad9c782a97d2eacb36034dbef04bd9a4e7c0981fdb0d347bc2467bdfb4e3bbe745c2a51572e8d4e622947297a39091c8dede43a158912a07d386ee9c2f3ab77f4ff5f5da4acc46a69bd87cd472402020931b9b6f5b56b30f72043d4b');
|
|
INSERT INTO opm.opm_settings (id, operator_id, settings) VALUES (10, 21, '\x0000018e789c8d903b0bc24010843f836063e16f1051104965612b846021a4482ac5229807011f219742ffbd934b0c56c15df698db9d9ddd3b60022c08b9525150526370390827a4043cf05489c9c51bc9614da4ea5dd55051cb0bb172dbf7ad9c782a97d2eacb36034dbef04bd9a4e7c0981fdb0d347bc2467bdfb4e3bbe745c2a51572e8d4e622947297a39091c8dede43a158912a07d386ee9c2f3ab77f4ff5f5da4acc46a69bd87cd472402020931b9b6f5b56b30f72043d4b');
|
|
INSERT INTO opm.opm_settings (id, operator_id, settings) VALUES (11, 22, '\x0000018e789c8d903b0bc24010843f836063e16f1051104965612b846021a4482ac5229807011f219742ffbd934b0c56c15df698db9d9ddd3b60022c08b9525150526370390827a4043cf05489c9c51bc9614da4ea5dd55051cb0bb172dbf7ad9c782a97d2eacb36034dbef04bd9a4e7c0981fdb0d347bc2467bdfb4e3bbe745c2a51572e8d4e622947297a39091c8dede43a158912a07d386ee9c2f3ab77f4ff5f5da4acc46a69bd87cd472402020931b9b6f5b56b30f72043d4b');
|
|
INSERT INTO opm.opm_settings (id, operator_id, settings) VALUES (12, 23, '\x0000018e789c8d903b0bc24010843f836063e16f1051104965612b846021a4482ac5229807011f219742ffbd934b0c56c15df698db9d9ddd3b60022c08b9525150526370390827a4043cf05489c9c51bc9614da4ea5dd55051cb0bb172dbf7ad9c782a97d2eacb36034dbef04bd9a4e7c0981fdb0d347bc2467bdfb4e3bbe745c2a51572e8d4e622947297a39091c8dede43a158912a07d386ee9c2f3ab77f4ff5f5da4acc46a69bd87cd472402020931b9b6f5b56b30f72043d4b');
|
|
ALTER SEQUENCE opm.opm_settings_id_seq RESTART WITH 20;
|
|
|