1 |
dpavlin |
3 |
|
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 'Network links'; |
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 |
dpavlin |
7 |
from link order by link_name; |
47 |
dpavlin |
3 |
|
48 |
dpavlin |
7 |
-- 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 |
dpavlin |
3 |
-- location |
69 |
|
|
|
70 |
|
|
create table location ( |
71 |
|
|
location_id serial not null, |
72 |
|
|
location_name text check (location_name != ''), |
73 |
dpavlin |
7 |
location_country_id int references country(country_id), |
74 |
|
|
location_comment text, |
75 |
dpavlin |
3 |
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 |
dpavlin |
7 |
comment on column location.location_comment is 'Comment'; |
82 |
|
|
comment on column location.location_country_id is 'Country'; |
83 |
dpavlin |
3 |
|
84 |
dpavlin |
7 |
INSERT INTO meta_fields VALUES ('location', 'location_comment', 'widget', 'area'); |
85 |
|
|
|
86 |
dpavlin |
3 |
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 |
dpavlin |
7 |
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 |
dpavlin |
3 |
|
102 |
dpavlin |
7 |
-- VPN connections |
103 |
|
|
|
104 |
dpavlin |
3 |
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 |
dpavlin |
7 |
comment on table vpn is 'VPN connections'; |
118 |
dpavlin |
3 |
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 |
dpavlin |
7 |
INSERT INTO meta_fields VALUES ('vpn', 'vpn_comment', 'widget', 'area'); |
126 |
dpavlin |
3 |
|
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 |
dpavlin |
7 |
|
144 |
dpavlin |
3 |
-- location trust |
145 |
|
|
|
146 |
|
|
create table trust ( |
147 |
|
|
trust_id serial not null, |
148 |
|
|
trust_from_location_id int references location(location_id), |
149 |
|
|
trust_to_location_id int references location(location_id), |
150 |
|
|
trust_mutual boolean default false, |
151 |
|
|
trust_comment text, |
152 |
|
|
primary key(trust_id) |
153 |
|
|
); |
154 |
|
|
|
155 |
|
|
create unique index trust_ind on trust(trust_from_location_id, trust_to_location_id,trust_mutual); |
156 |
|
|
|
157 |
|
|
comment on table trust is 'Inter-location trust'; |
158 |
|
|
comment on column trust.trust_from_location_id is 'From location'; |
159 |
|
|
comment on column trust.trust_to_location_id is 'To location'; |
160 |
|
|
comment on column trust.trust_mutual is 'two-way trust'; |
161 |
|
|
comment on column trust.trust_comment is 'Comment'; |
162 |
|
|
|
163 |
dpavlin |
7 |
INSERT INTO meta_fields VALUES ('trust', 'trust_comment', 'widget', 'area'); |
164 |
dpavlin |
3 |
|
165 |
|
|
GRANT ALL ON trust TO PUBLIC; |
166 |
|
|
|
167 |
|
|
create view trust_list as |
168 |
|
|
select trust_id, |
169 |
|
|
f.location_name as from_location, |
170 |
|
|
t.location_name as to_location, |
171 |
|
|
trust_mutual, trust_comment |
172 |
|
|
from trust, location f, location t |
173 |
|
|
where f.location_id = trust_from_location_id |
174 |
|
|
and t.location_id = trust_to_location_id ; |
175 |
|
|
|
176 |
|
|
comment on column trust_list.from_location is 'From location'; |
177 |
|
|
comment on column trust_list.to_location is 'To location'; |
178 |
|
|
|
179 |
dpavlin |
7 |
-- 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 |
dpavlin |
3 |
-- DNS on location |
200 |
|
|
|
201 |
|
|
create table dns ( |
202 |
dpavlin |
7 |
dns_id serial not null, |
203 |
dpavlin |
3 |
dns_on_location_id int references location(location_id), |
204 |
dpavlin |
7 |
dns_type int not null references dns_type(dns_type_id), |
205 |
dpavlin |
3 |
dns_domain varchar(255) not null, |
206 |
|
|
primary key (dns_on_location_id, dns_type, dns_domain) |
207 |
|
|
); |
208 |
|
|
|
209 |
dpavlin |
7 |
-- this has to go on table, not on view! |
210 |
|
|
insert into meta_fields values ('dns', 'dns_domain', 'widget', 'text(size=25)') ; |
211 |
dpavlin |
3 |
|
212 |
dpavlin |
7 |
create unique index dns_id_ind on dns(dns_id); |
213 |
|
|
|
214 |
|
|
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 |
dpavlin |
3 |
GRANT ALL ON dns TO PUBLIC; |
228 |
dpavlin |
5 |
|