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 '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 |
from link; |
47 |
|
48 |
-- location |
49 |
|
50 |
create table location ( |
51 |
location_id serial not null, |
52 |
location_name text check (location_name != ''), |
53 |
primary key(location_id) |
54 |
); |
55 |
|
56 |
comment on table location is 'Locations'; |
57 |
comment on column location.location_id is 'id'; |
58 |
comment on column location.location_name is 'Location name'; |
59 |
|
60 |
GRANT ALL ON location TO PUBLIC; |
61 |
|
62 |
create view location_combo as |
63 |
select |
64 |
location_id as id, |
65 |
location_name as text |
66 |
from location; |
67 |
|
68 |
|
69 |
-- VPN connection |
70 |
|
71 |
create table vpn ( |
72 |
vpn_id serial not null, |
73 |
vpn_from_location_id int not null references location(location_id), |
74 |
vpn_to_location_id int not null references location(location_id), |
75 |
vpn_link int not null references link(link_id), |
76 |
vpn_local_gw inet not null, |
77 |
vpn_remote_gw inet not null, |
78 |
vpn_comment text, |
79 |
primary key(vpn_id) |
80 |
|
81 |
); |
82 |
create unique index vpn_index on vpn(vpn_from_location_id, vpn_to_location_id, vpn_link); |
83 |
|
84 |
comment on table vpn is 'VPN connection'; |
85 |
comment on column vpn.vpn_from_location_id is 'From location'; |
86 |
comment on column vpn.vpn_to_location_id is 'To location'; |
87 |
comment on column vpn.vpn_link is 'Using link'; |
88 |
comment on column vpn.vpn_local_gw is 'Local gateway'; |
89 |
comment on column vpn.vpn_remote_gw is 'Remote gateway'; |
90 |
comment on column vpn.vpn_comment is 'Comment'; |
91 |
|
92 |
INSERT INTO meta_fields VALUES ('vpn', 'comment', 'widget', 'area'); |
93 |
|
94 |
GRANT ALL ON vpn TO PUBLIC; |
95 |
|
96 |
create view vpn_list as |
97 |
select vpn_id, |
98 |
f.location_name as from_location, |
99 |
t.location_name as to_location, |
100 |
link_name, vpn_local_gw, vpn_remote_gw, |
101 |
vpn_comment |
102 |
from vpn, location f, location t, link |
103 |
where f.location_id = vpn_from_location_id |
104 |
and t.location_id = vpn_to_location_id |
105 |
and link_id = vpn_link ; |
106 |
|
107 |
comment on column vpn_list.from_location is 'From location'; |
108 |
comment on column vpn_list.to_location is 'To location'; |
109 |
|
110 |
-- location trust |
111 |
|
112 |
create table trust ( |
113 |
trust_id serial not null, |
114 |
trust_from_location_id int references location(location_id), |
115 |
trust_to_location_id int references location(location_id), |
116 |
trust_mutual boolean default false, |
117 |
trust_comment text, |
118 |
primary key(trust_id) |
119 |
); |
120 |
|
121 |
create unique index trust_ind on trust(trust_from_location_id, trust_to_location_id,trust_mutual); |
122 |
|
123 |
comment on table trust is 'Inter-location trust'; |
124 |
comment on column trust.trust_from_location_id is 'From location'; |
125 |
comment on column trust.trust_to_location_id is 'To location'; |
126 |
comment on column trust.trust_mutual is 'two-way trust'; |
127 |
comment on column trust.trust_comment is 'Comment'; |
128 |
|
129 |
INSERT INTO meta_fields VALUES ('trust', 'comment', 'widget', 'area'); |
130 |
|
131 |
GRANT ALL ON trust TO PUBLIC; |
132 |
|
133 |
create view trust_list as |
134 |
select trust_id, |
135 |
f.location_name as from_location, |
136 |
t.location_name as to_location, |
137 |
trust_mutual, trust_comment |
138 |
from trust, location f, location t |
139 |
where f.location_id = trust_from_location_id |
140 |
and t.location_id = trust_to_location_id ; |
141 |
|
142 |
comment on column trust_list.from_location is 'From location'; |
143 |
comment on column trust_list.to_location is 'To location'; |
144 |
|
145 |
-- DNS on location |
146 |
|
147 |
create table dns ( |
148 |
dns_on_location_id int references location(location_id), |
149 |
dns_type varchar(1) not null check (dns_type like 'p' or dns_type like 's' or dns_type like 'f'), |
150 |
dns_domain varchar(255) not null, |
151 |
primary key (dns_on_location_id, dns_type, dns_domain) |
152 |
); |
153 |
|
154 |
comment on table dns is 'Location DNS'; |
155 |
comment on column dns.dns_on_location_id is 'Location'; |
156 |
comment on column dns.dns_type is 'DNS type'; |
157 |
comment on column dns.dns_domain is 'DNS domain'; |
158 |
|
159 |
GRANT ALL ON dns TO PUBLIC; |