43 |
select |
select |
44 |
link_id as id, |
link_id as id, |
45 |
link_name as text |
link_name as text |
46 |
from link; |
from link order by link_name; |
47 |
|
|
48 |
|
-- countries |
49 |
|
|
50 |
|
create table country ( |
51 |
|
country_id serial not null, |
52 |
|
country_name text not null, |
53 |
|
primary key (country_id) |
54 |
|
); |
55 |
|
|
56 |
|
comment on table country is 'Countries'; |
57 |
|
comment on column country.country_id is 'id'; |
58 |
|
comment on column country.country_name is 'Country'; |
59 |
|
|
60 |
|
grant all on country to public ; |
61 |
|
|
62 |
|
create view country_combo as |
63 |
|
select |
64 |
|
country_id as id, |
65 |
|
country_name as text |
66 |
|
from country order by country_name; |
67 |
|
|
68 |
-- location |
-- location |
69 |
|
|
70 |
create table location ( |
create table location ( |
71 |
location_id serial not null, |
location_id serial not null, |
72 |
location_name text check (location_name != ''), |
location_name text check (location_name != ''), |
73 |
|
location_country_id int references country(country_id), |
74 |
|
location_comment text, |
75 |
primary key(location_id) |
primary key(location_id) |
76 |
); |
); |
77 |
|
|
78 |
comment on table location is 'Locations'; |
comment on table location is 'Locations'; |
79 |
comment on column location.location_id is 'id'; |
comment on column location.location_id is 'id'; |
80 |
comment on column location.location_name is 'Location name'; |
comment on column location.location_name is 'Location name'; |
81 |
|
comment on column location.location_comment is 'Comment'; |
82 |
|
comment on column location.location_country_id is 'Country'; |
83 |
|
|
84 |
|
INSERT INTO meta_fields VALUES ('location', 'location_comment', 'widget', 'area'); |
85 |
|
|
86 |
GRANT ALL ON location TO PUBLIC; |
GRANT ALL ON location TO PUBLIC; |
87 |
|
|
90 |
location_id as id, |
location_id as id, |
91 |
location_name as text |
location_name as text |
92 |
from location; |
from location; |
|
|
|
93 |
|
|
94 |
-- VPN connection |
create view location_list as |
95 |
|
select location_id, |
96 |
|
location_name, |
97 |
|
country_name, |
98 |
|
location_comment |
99 |
|
from location, country |
100 |
|
where location_country_id = country_id; |
101 |
|
|
102 |
|
-- VPN connections |
103 |
|
|
104 |
create table vpn ( |
create table vpn ( |
105 |
vpn_id serial not null, |
vpn_id serial not null, |
114 |
); |
); |
115 |
create unique index vpn_index on vpn(vpn_from_location_id, vpn_to_location_id, vpn_link); |
create unique index vpn_index on vpn(vpn_from_location_id, vpn_to_location_id, vpn_link); |
116 |
|
|
117 |
comment on table vpn is 'VPN connection'; |
comment on table vpn is 'VPN connections'; |
118 |
comment on column vpn.vpn_from_location_id is 'From location'; |
comment on column vpn.vpn_from_location_id is 'From location'; |
119 |
comment on column vpn.vpn_to_location_id is 'To location'; |
comment on column vpn.vpn_to_location_id is 'To location'; |
120 |
comment on column vpn.vpn_link is 'Using link'; |
comment on column vpn.vpn_link is 'Using link'; |
122 |
comment on column vpn.vpn_remote_gw is 'Remote gateway'; |
comment on column vpn.vpn_remote_gw is 'Remote gateway'; |
123 |
comment on column vpn.vpn_comment is 'Comment'; |
comment on column vpn.vpn_comment is 'Comment'; |
124 |
|
|
125 |
INSERT INTO meta_fields VALUES ('vpn', 'comment', 'widget', 'area'); |
INSERT INTO meta_fields VALUES ('vpn', 'vpn_comment', 'widget', 'area'); |
126 |
|
|
127 |
GRANT ALL ON vpn TO PUBLIC; |
GRANT ALL ON vpn TO PUBLIC; |
128 |
|
|
140 |
comment on column vpn_list.from_location is 'From location'; |
comment on column vpn_list.from_location is 'From location'; |
141 |
comment on column vpn_list.to_location is 'To location'; |
comment on column vpn_list.to_location is 'To location'; |
142 |
|
|
143 |
|
|
144 |
-- location trust |
-- location trust |
145 |
|
|
146 |
create table trust ( |
create table trust ( |
160 |
comment on column trust.trust_mutual is 'two-way trust'; |
comment on column trust.trust_mutual is 'two-way trust'; |
161 |
comment on column trust.trust_comment is 'Comment'; |
comment on column trust.trust_comment is 'Comment'; |
162 |
|
|
163 |
INSERT INTO meta_fields VALUES ('trust', 'comment', 'widget', 'area'); |
INSERT INTO meta_fields VALUES ('trust', 'trust_comment', 'widget', 'area'); |
164 |
|
|
165 |
GRANT ALL ON trust TO PUBLIC; |
GRANT ALL ON trust TO PUBLIC; |
166 |
|
|
176 |
comment on column trust_list.from_location is 'From location'; |
comment on column trust_list.from_location is 'From location'; |
177 |
comment on column trust_list.to_location is 'To location'; |
comment on column trust_list.to_location is 'To location'; |
178 |
|
|
179 |
|
-- combo for DNS type |
180 |
|
create table dns_type ( |
181 |
|
dns_type_id serial not null, |
182 |
|
dns_type_name text not null, |
183 |
|
primary key(dns_type_id) |
184 |
|
) ; |
185 |
|
insert into dns_type values(1, 'primary'); |
186 |
|
insert into dns_type values(2, 'secondary'); |
187 |
|
insert into dns_type values(3, 'forwarder'); |
188 |
|
|
189 |
|
grant all on dns_type to public; |
190 |
|
|
191 |
|
create view dns_type_combo as |
192 |
|
select dns_type_id as id, dns_type_name as text |
193 |
|
from dns_type order by dns_type_id; |
194 |
|
|
195 |
|
|
196 |
|
-- hide this table |
197 |
|
-- INSERT INTO meta_tables VALUES ('dns_type_combo', 'hide', 1); |
198 |
|
|
199 |
-- DNS on location |
-- DNS on location |
200 |
|
|
201 |
create table dns ( |
create table dns ( |
202 |
|
dns_id serial not null, |
203 |
dns_on_location_id int references location(location_id), |
dns_on_location_id int references location(location_id), |
204 |
dns_type varchar(1) not null check (dns_type like 'p' or dns_type like 's' or dns_type like 'f'), |
dns_type int not null references dns_type(dns_type_id), |
205 |
dns_domain varchar(255) not null, |
dns_domain varchar(255) not null, |
206 |
primary key (dns_on_location_id, dns_type, dns_domain) |
primary key (dns_on_location_id, dns_type, dns_domain) |
207 |
); |
); |
208 |
|
|
209 |
comment on table dns is 'Location DNS'; |
-- this has to go on table, not on view! |
210 |
comment on column dns.dns_on_location_id is 'Location'; |
insert into meta_fields values ('dns', 'dns_domain', 'widget', 'text(size=25)') ; |
|
comment on column dns.dns_type is 'DNS type'; |
|
|
comment on column dns.dns_domain is 'DNS domain'; |
|
211 |
|
|
212 |
GRANT ALL ON dns TO PUBLIC; |
create unique index dns_id_ind on dns(dns_id); |
213 |
|
|
214 |
create table dns_type ( id varchar(1) not null, text text, primary key(id) ) ; |
create view dns_list as |
215 |
|
select dns_id, |
216 |
|
l.location_name as location, |
217 |
|
t.dns_type_name as type, |
218 |
|
dns_domain |
219 |
|
from dns, location l, dns_type t |
220 |
|
where location_id = dns_on_location_id and dns_type_id = dns_type ; |
221 |
|
|
222 |
|
comment on view dns_list is 'DNS on locations'; |
223 |
|
comment on column dns_list.location is 'Location'; |
224 |
|
comment on column dns_list.type is 'server type'; |
225 |
|
comment on column dns_list.dns_domain is 'DNS domain'; |
226 |
|
|
227 |
|
GRANT ALL ON dns TO PUBLIC; |
228 |
|
|