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 |
|
|
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; |
160 |
dpavlin |
5 |
|
161 |
|
|
create table dns_type ( id varchar(1) not null, text text, primary key(id) ) ; |
162 |
|
|
|