jpo-cvmanager/resources/sql_scripts/CVManager_SampleData.sql at develop · neaeraconsulting/jpo-cvmanager · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
-- Sample data to test CV Manager locally or in a new deployment
-- This is only recommended to be used for testing, do not use in a deployed environment
INSERT INTO public.manufacturers(name)
  VALUES ('Commsignia'), ('Yunex');

INSERT INTO public.rsu_models(
	name, supported_radio, manufacturer)
	VALUES ('ITS-RS4-M', 'DSRC,C-V2X', 1), ('RSU2X US', 'DSRC,C-V2X', 2);

INSERT INTO public.firmware_images(
	name, model, install_package, version)
	VALUES ('y20.0.0', 1, 'install_y20_0_0.tar', 'y20.0.0'), ('y20.1.0', 1, 'install_y20_1_0.tar', 'y20.1.0');

INSERT INTO public.firmware_upgrade_rules(
	from_id, to_id)
	VALUES (1, 2);

INSERT INTO public.organizations(
    name)
VALUES ('Test Org'), ('Test Org 2');

INSERT INTO public.rsu_credentials(
	username, password, nickname, owner_organization_id)
	VALUES ('username', 'password', 'cred1', 1);

INSERT INTO public.snmp_credentials(
	username, password, encrypt_password, nickname, owner_organization_id)
	VALUES ('username', 'password', 'encryption-pw', 'snmp1', 1);

INSERT INTO public.snmp_protocols(
	protocol_code, nickname)
	VALUES ('41', 'RSU 4.1');
INSERT INTO public.snmp_protocols(
	protocol_code, nickname)
	VALUES ('1218', 'NTCIP 1218');

INSERT INTO public.rsus(
	geography, milepost, ipv4_address, serial_number, iss_scms_id, primary_route, model, credential_id, snmp_credential_id, snmp_protocol_id, firmware_version, target_firmware_version)
	VALUES (ST_GeomFromText('POINT(-105.0135030 39.7405654)'), 1, '10.0.0.180', 'E5672', 'E5672', 'I999', 1, 1, 1, 1, 1, 1),
	(ST_GeomFromText('POINT(-104.987775 39.981805)'), 2, '10.0.0.78', 'E5321', 'E5321', 'I999', 1, 1, 1, 2, 2, 2);

INSERT INTO public.rsu_options(
	rsu_id, tim_deposit, snmp_monitoring)
	VALUES (1, TRUE, TRUE), (2, FALSE, TRUE);


INSERT INTO public.roles(
	name)
	VALUES ('admin'), ('operator'), ('user');

INSERT INTO public.rsu_organization(
	rsu_id, organization_id)
	VALUES (1, 1), (2, 1);

-- Replace user with a real gmail to test GCP OAuth2.0 support
INSERT INTO public.users(
	keycloak_id, email, first_name, last_name, created_timestamp, super_user)
	VALUES ('fc3d8729-8526-4aaa-805b-d64bf3b93860'::UUID, 'test@gmail.com', 'Test', 'User', (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) * 1000), '1');

INSERT INTO public.user_organization(
	user_id, organization_id, role_id)
	VALUES (1, 1, 1), (1, 2, 3);

INSERT INTO public.snmp_msgfwd_type(
	name)
	VALUES ('rsuDsrcFwd'), ('rsuReceivedMsg'), ('rsuXmitMsgFwding');

INSERT INTO public.snmp_msgfwd_config(
	rsu_id, msgfwd_type, snmp_index, message_type, dest_ipv4, dest_port, start_datetime, end_datetime, active, security)
	VALUES (1, 1, 1, 'BSM', '10.0.0.80', 46800, '2024/04/01T00:00:00', '2034/04/01T00:00:00', '1', '0'),
	(1, 1, 2, 'BSM', '10.0.0.81', 46800, '2024/04/01T00:00:00', '2034/04/01T00:00:00', '1', '0'),
	(1, 1, 3, 'BSM', '10.0.0.82', 46800, '2024/04/01T00:00:00', '2034/04/01T00:00:00', '1', '1'),
	(2, 2, 1, 'BSM', '10.0.0.80', 46800, '2024/04/01T00:00:00', '2034/04/01T00:00:00', '1', '1'),
	(2, 2, 2, 'BSM', '10.0.0.81', 46800, '2024/04/01T00:00:00', '2034/04/01T00:00:00', '1', '1'),
	(2, 3, 1, 'MAP', '10.0.0.80', 44920, '2024/04/01T00:00:00', '2034/04/01T00:00:00', '1', '1'),
	(2, 3, 2, 'SPAT', '10.0.0.80', 44910, '2024/04/01T00:00:00', '2034/04/01T00:00:00', '1', '0');

INSERT INTO public.email_type(
	email_type, supports_immediate, supports_hourly, supports_daily, supports_weekly, supports_monthly)
	VALUES ('Support Requests', true, false, false, false, false),
    ('Firmware Upgrade Failures', true, false, false, false, false),
    ('Daily Message Counts', true, false, false, false, false),
    ('Access Requests', true, false, false, false, false),
    ('Intersection Notification Summary', true, true, true, true, true),
    ('Critical Error Messages', true, false, false, false, false);

INSERT INTO public.user_email_notification(
	user_email_notification_id, user_id, email_type_id, immediate, hourly, daily, weekly, monthly)
	VALUES (1, 1, 1, true, false, false, false, false),
    (2, 1, 2, true, false, false, false, false),
    (3, 1, 3, true, false, false, false, false),
    (4, 1, 4, true, false, false, false, false),
    (5, 1, 5, true, true, true, true, true),
    (6, 1, 6, true, false, false, false, false);


INSERT INTO public.intersections(
	intersection_number, ref_pt, intersection_name)
	VALUES (12109, ST_GeomFromText('POINT(-105.0908854 39.5880413)'), 'S Wadsworth & W Columbine Dr');

INSERT INTO public.intersection_organization(
	intersection_id, organization_id)
	VALUES (1, 1);

INSERT INTO public.rsu_intersection(
	rsu_id, intersection_id)
	VALUES (1, 1);