trunk/network_topology/network_topology.sql


comment on database network_topology is 'Network Topology';

CREATE TABLE meta_tables (
        -- Table Name
        meta_tables_table       NAME    NOT NULL PRIMARY KEY,
        -- Attribute
        meta_tables_attribute   TEXT    NOT NULL,
        -- Value
        meta_tables_value       TEXT
);
-- standard attributes: filterfirst, hide

CREATE TABLE meta_fields (
        -- Table Name
        meta_fields_table       NAME    NOT NULL,
        -- Field Name
        meta_fields_field       NAME    NOT NULL,
        -- Attribute
        meta_fields_attribute   TEXT    NOT NULL,
        -- Value
        meta_fields_value       TEXT
);
-- standard attributes: widget, copy, sortfunc

GRANT SELECT ON meta_fields, meta_tables TO PUBLIC;

-- link

create table link (
        link_id serial not null,
        link_name text check (link_name != ''),
        primary key(link_id)
);

comment on table link is 'Link providers';
comment on column link.link_id is 'id';
comment on column link.link_name is 'Link name';

GRANT ALL ON link TO PUBLIC;

create view link_combo as
        select
                link_id as id,
                link_name as text
        from link order by link_name;

-- countries

create table country (
        country_id serial not null,
        country_name text not null,
        primary key (country_id)
);

comment on table country is 'Countries';
comment on column country.country_id is 'id';
comment on column country.country_name is 'Country';

grant all on country to public ;

create view country_combo as
        select
                country_id as id,
                country_name as text
        from country order by country_name;

-- location

create table location (
        location_id serial not null,
        location_name text check (location_name != ''),
        location_country_id int references country(country_id),
        location_comment text,
        primary key(location_id)
);

comment on table location is 'Locations';
comment on column location.location_id is 'id';
comment on column location.location_name is 'Location name';
comment on column location.location_comment is 'Comment';
comment on column location.location_country_id is 'Country';

INSERT INTO meta_fields VALUES ('location', 'location_comment', 'widget', 'area');

GRANT ALL ON location TO PUBLIC;

create view location_combo as
        select
                location_id as id,
                location_name as text
        from location;

create view location_list as
        select location_id,
                location_name,
                country_name,
                location_comment
        from location, country
        where location_country_id = country_id;

-- VPN connections

create table vpn (
        vpn_id serial not null,
        vpn_from_location_id int not null references location(location_id),
        vpn_to_location_id int not null references location(location_id),
        vpn_link int not null references link(link_id),
        vpn_local_gw inet not null,
        vpn_remote_gw inet not null,
        vpn_comment text,
        primary key(vpn_id)
        
);
create unique index vpn_index on vpn(vpn_from_location_id, vpn_to_location_id, vpn_link);

comment on table vpn is 'VPN connections';
comment on column vpn.vpn_from_location_id is 'From location';
comment on column vpn.vpn_to_location_id is 'To location';
comment on column vpn.vpn_link is 'Using link';
comment on column vpn.vpn_local_gw is 'Local gateway';
comment on column vpn.vpn_remote_gw is 'Remote gateway';
comment on column vpn.vpn_comment is 'Comment';

INSERT INTO meta_fields VALUES ('vpn', 'vpn_comment', 'widget', 'area');

GRANT ALL ON vpn TO PUBLIC;

create view vpn_list as
        select vpn_id,
                f.location_name as from_location,
                t.location_name as to_location,
                link_name, vpn_local_gw, vpn_remote_gw,
                vpn_comment
        from vpn, location f, location t, link
        where f.location_id = vpn_from_location_id
                and t.location_id = vpn_to_location_id
                and link_id = vpn_link ;

comment on column vpn_list.from_location is 'From location';
comment on column vpn_list.to_location is 'To location';


create view vpn_rep as        
        select
                fl.location_name as from_location,
                fc.country_name as from_country,
                vpn_local_gw,
                tl.location_name as to_location,
                tc.country_name as to_country,
                vpn_remote_gw,
                link_name,
                vpn_comment
        from vpn, location fl, location tl, link, country fc, country tc
        where fl.location_id = vpn_from_location_id
                and tl.location_id = vpn_to_location_id
                and fl.location_country_id = fc.country_id
                and tl.location_country_id = tc.country_id
                and link_id = vpn_link ;

comment on view vpn_rep is 'All VPN connections';
comment on column vpn_rep.from_country is 'From country';
comment on column vpn_rep.to_country is 'To country';

-- location trust

create table trust (
        trust_id serial not null,
        trust_from_location_id int references location(location_id),
        trust_to_location_id int references location(location_id),
        trust_mutual boolean default false,
        trust_comment text,
        primary key(trust_id)
);

create unique index trust_ind on trust(trust_from_location_id, trust_to_location_id,trust_mutual);

comment on table trust is 'Inter-location trust';
comment on column trust.trust_from_location_id is 'From location';
comment on column trust.trust_to_location_id is 'To location';
comment on column trust.trust_mutual is 'two-way trust';
comment on column trust.trust_comment is 'Comment';

INSERT INTO meta_fields VALUES ('trust', 'trust_comment', 'widget', 'area');

GRANT ALL ON trust TO PUBLIC;

create view trust_list as
        select trust_id,
                f.location_name as from_location,
                t.location_name as to_location,
                trust_mutual, trust_comment
        from trust, location f, location t
        where f.location_id = trust_from_location_id
                and t.location_id = trust_to_location_id ; 

comment on column trust_list.from_location is 'From location';
comment on column trust_list.to_location is 'To location';

-- combo for DNS type
create table dns_type (
        dns_type_id serial not null,
        dns_type_name text not null,
        primary key(dns_type_id)
) ;
insert into dns_type values(1, 'primary');
insert into dns_type values(2, 'secondary');
insert into dns_type values(3, 'forwarder');

grant all on dns_type to public;

create view dns_type_combo as
        select dns_type_id as id, dns_type_name as text
        from dns_type order by dns_type_id;


-- hide this table
INSERT INTO meta_tables VALUES ('dns_type', 'hide', 1);

-- DNS on location

create table dns (
        dns_id serial not null,
        dns_on_location_id int references location(location_id),
        dns_type int not null references dns_type(dns_type_id),
        dns_domain varchar(255) not null,
        primary key (dns_on_location_id, dns_type, dns_domain)
);

-- this has to go on table, not on view!
insert into meta_fields values ('dns', 'dns_domain', 'widget', 'text(size=25)') ;

create unique index dns_id_ind on dns(dns_id);

create view dns_list as
        select dns_id,
                l.location_name as location,
                t.dns_type_name as type,
                dns_domain
        from dns, location l, dns_type t
        where location_id = dns_on_location_id and dns_type_id = dns_type ;

comment on view dns_list is 'DNS on locations';
comment on column dns_list.location is 'Location';
comment on column dns_list.type is 'server type';
comment on column dns_list.dns_domain is 'DNS domain';

GRANT ALL ON dns TO PUBLIC;

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	dpavlin	11	comment on table link is 'Link providers';
37	dpavlin	3	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	8	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	dpavlin	3	-- 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	dpavlin	7	INSERT INTO meta_fields VALUES ('trust', 'trust_comment', 'widget', 'area');
185	dpavlin	3
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	dpavlin	7	-- 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	dpavlin	8	INSERT INTO meta_tables VALUES ('dns_type', 'hide', 1);
219	dpavlin	7
220	dpavlin	3	-- DNS on location
221
222			create table dns (
223	dpavlin	7	dns_id serial not null,
224	dpavlin	3	dns_on_location_id int references location(location_id),
225	dpavlin	7	dns_type int not null references dns_type(dns_type_id),
226	dpavlin	3	dns_domain varchar(255) not null,
227			primary key (dns_on_location_id, dns_type, dns_domain)
228			);
229
230	dpavlin	7	-- this has to go on table, not on view!
231			insert into meta_fields values ('dns', 'dns_domain', 'widget', 'text(size=25)') ;
232	dpavlin	3
233	dpavlin	7	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	dpavlin	3	GRANT ALL ON dns TO PUBLIC;
249	dpavlin	5