/[gedafe]/trunk/network_topology/network_topology.sql
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /trunk/network_topology/network_topology.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 11 - (show annotations)
Wed Feb 16 14:53:37 2005 UTC (14 years, 9 months ago) by dpavlin
File size: 6882 byte(s)
small edits

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

  ViewVC Help
Powered by ViewVC 1.1.26