1 |
|
2 |
comment on database network_topology is 'Network Topology'; |
3 |
|
4 |
CREATE TABLE meta_tables ( |
5 |
-- Table Name |
6 |
meta_tables_table NAME NOT NULL PRIMARY KEY, |
7 |
-- Attribute |
8 |
meta_tables_attribute TEXT NOT NULL, |
9 |
-- Value |
10 |
meta_tables_value TEXT |
11 |
); |
12 |
-- standard attributes: filterfirst, hide |
13 |
|
14 |
CREATE TABLE meta_fields ( |
15 |
-- Table Name |
16 |
meta_fields_table NAME NOT NULL, |
17 |
-- Field Name |
18 |
meta_fields_field NAME NOT NULL, |
19 |
-- Attribute |
20 |
meta_fields_attribute TEXT NOT NULL, |
21 |
-- Value |
22 |
meta_fields_value TEXT |
23 |
); |
24 |
-- standard attributes: widget, copy, sortfunc |
25 |
|
26 |
GRANT SELECT ON meta_fields, meta_tables TO PUBLIC; |
27 |
|
28 |
-- link |
29 |
|
30 |
create table link ( |
31 |
link_id serial not null, |
32 |
link_name text check (link_name != ''), |
33 |
primary key(link_id) |
34 |
); |
35 |
|
36 |
comment on table link is 'Link providers'; |
37 |
comment on column link.link_id is 'id'; |
38 |
comment on column link.link_name is 'Link name'; |
39 |
|
40 |
GRANT ALL ON link TO PUBLIC; |
41 |
|
42 |
create view link_combo as |
43 |
select |
44 |
link_id as id, |
45 |
link_name as text |
46 |
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 |
69 |
|
70 |
create table location ( |
71 |
location_id serial not null, |
72 |
location_name text check (location_name != ''), |
73 |
location_country_id int references country(country_id), |
74 |
location_comment text, |
75 |
primary key(location_id) |
76 |
); |
77 |
|
78 |
comment on table location is 'Locations'; |
79 |
comment on column location.location_id is 'id'; |
80 |
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; |
87 |
|
88 |
create view location_combo as |
89 |
select |
90 |
location_id as id, |
91 |
location_name as text |
92 |
from location; |
93 |
|
94 |
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 ( |
105 |
vpn_id serial not null, |
106 |
vpn_from_location_id int not null references location(location_id), |
107 |
vpn_to_location_id int not null references location(location_id), |
108 |
vpn_link int not null references link(link_id), |
109 |
vpn_local_gw inet not null, |
110 |
vpn_remote_gw inet not null, |
111 |
vpn_comment text, |
112 |
primary key(vpn_id) |
113 |
|
114 |
); |
115 |
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 connections'; |
118 |
comment on column vpn.vpn_from_location_id is 'From location'; |
119 |
comment on column vpn.vpn_to_location_id is 'To location'; |
120 |
comment on column vpn.vpn_link is 'Using link'; |
121 |
comment on column vpn.vpn_local_gw is 'Local gateway'; |
122 |
comment on column vpn.vpn_remote_gw is 'Remote gateway'; |
123 |
comment on column vpn.vpn_comment is 'Comment'; |
124 |
|
125 |
INSERT INTO meta_fields VALUES ('vpn', 'vpn_comment', 'widget', 'area'); |
126 |
|
127 |
GRANT ALL ON vpn TO PUBLIC; |
128 |
|
129 |
create view vpn_list as |
130 |
select vpn_id, |
131 |
f.location_name as from_location, |
132 |
t.location_name as to_location, |
133 |
link_name, vpn_local_gw, vpn_remote_gw, |
134 |
vpn_comment |
135 |
from vpn, location f, location t, link |
136 |
where f.location_id = vpn_from_location_id |
137 |
and t.location_id = vpn_to_location_id |
138 |
and link_id = vpn_link ; |
139 |
|
140 |
comment on column vpn_list.from_location is 'From location'; |
141 |
comment on column vpn_list.to_location is 'To location'; |
142 |
|
143 |
|
144 |
create view vpn_rep as |
145 |
select |
146 |
fl.location_name as from_location, |
147 |
fc.country_name as from_country, |
148 |
vpn_local_gw, |
149 |
tl.location_name as to_location, |
150 |
tc.country_name as to_country, |
151 |
vpn_remote_gw, |
152 |
link_name, |
153 |
vpn_comment |
154 |
from vpn, location fl, location tl, link, country fc, country tc |
155 |
where fl.location_id = vpn_from_location_id |
156 |
and tl.location_id = vpn_to_location_id |
157 |
and fl.location_country_id = fc.country_id |
158 |
and tl.location_country_id = tc.country_id |
159 |
and link_id = vpn_link ; |
160 |
|
161 |
comment on view vpn_rep is 'All VPN connections'; |
162 |
comment on column vpn_rep.from_country is 'From country'; |
163 |
comment on column vpn_rep.to_country is 'To country'; |
164 |
|
165 |
-- location trust |
166 |
|
167 |
create table trust ( |
168 |
trust_id serial not null, |
169 |
trust_from_location_id int references location(location_id), |
170 |
trust_to_location_id int references location(location_id), |
171 |
trust_mutual boolean default false, |
172 |
trust_comment text, |
173 |
primary key(trust_id) |
174 |
); |
175 |
|
176 |
create unique index trust_ind on trust(trust_from_location_id, trust_to_location_id,trust_mutual); |
177 |
|
178 |
comment on table trust is 'Inter-location trust'; |
179 |
comment on column trust.trust_from_location_id is 'From location'; |
180 |
comment on column trust.trust_to_location_id is 'To location'; |
181 |
comment on column trust.trust_mutual is 'two-way trust'; |
182 |
comment on column trust.trust_comment is 'Comment'; |
183 |
|
184 |
INSERT INTO meta_fields VALUES ('trust', 'trust_comment', 'widget', 'area'); |
185 |
|
186 |
GRANT ALL ON trust TO PUBLIC; |
187 |
|
188 |
create view trust_list as |
189 |
select trust_id, |
190 |
f.location_name as from_location, |
191 |
t.location_name as to_location, |
192 |
trust_mutual, trust_comment |
193 |
from trust, location f, location t |
194 |
where f.location_id = trust_from_location_id |
195 |
and t.location_id = trust_to_location_id ; |
196 |
|
197 |
comment on column trust_list.from_location is 'From location'; |
198 |
comment on column trust_list.to_location is 'To location'; |
199 |
|
200 |
-- combo for DNS type |
201 |
create table dns_type ( |
202 |
dns_type_id serial not null, |
203 |
dns_type_name text not null, |
204 |
primary key(dns_type_id) |
205 |
) ; |
206 |
insert into dns_type values(1, 'primary'); |
207 |
insert into dns_type values(2, 'secondary'); |
208 |
insert into dns_type values(3, 'forwarder'); |
209 |
|
210 |
grant all on dns_type to public; |
211 |
|
212 |
create view dns_type_combo as |
213 |
select dns_type_id as id, dns_type_name as text |
214 |
from dns_type order by dns_type_id; |
215 |
|
216 |
|
217 |
-- hide this table |
218 |
INSERT INTO meta_tables VALUES ('dns_type', 'hide', 1); |
219 |
|
220 |
-- DNS on location |
221 |
|
222 |
create table dns ( |
223 |
dns_id serial not null, |
224 |
dns_on_location_id int references location(location_id), |
225 |
dns_type int not null references dns_type(dns_type_id), |
226 |
dns_domain varchar(255) not null, |
227 |
primary key (dns_on_location_id, dns_type, dns_domain) |
228 |
); |
229 |
|
230 |
-- this has to go on table, not on view! |
231 |
insert into meta_fields values ('dns', 'dns_domain', 'widget', 'text(size=25)') ; |
232 |
|
233 |
create unique index dns_id_ind on dns(dns_id); |
234 |
|
235 |
create view dns_list as |
236 |
select dns_id, |
237 |
l.location_name as location, |
238 |
t.dns_type_name as type, |
239 |
dns_domain |
240 |
from dns, location l, dns_type t |
241 |
where location_id = dns_on_location_id and dns_type_id = dns_type ; |
242 |
|
243 |
comment on view dns_list is 'DNS on locations'; |
244 |
comment on column dns_list.location is 'Location'; |
245 |
comment on column dns_list.type is 'server type'; |
246 |
comment on column dns_list.dns_domain is 'DNS domain'; |
247 |
|
248 |
GRANT ALL ON dns TO PUBLIC; |
249 |
|