--- trunk/network_topology/network_topology.sql	2005/02/14 20:58:20	3
+++ trunk/network_topology/network_topology.sql	2005/02/16 12:41:02	8
@@ -43,19 +43,45 @@
 	select
 		link_id as id,
 		link_name as text
-	from link;
+	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;
 
@@ -64,9 +90,16 @@
 		location_id as id,
 		location_name as text
 	from location;
-	
 
--- VPN connection
+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,
@@ -81,7 +114,7 @@
 );
 create unique index vpn_index on vpn(vpn_from_location_id, vpn_to_location_id, vpn_link);
 
-comment on table vpn is 'VPN connection';
+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';
@@ -89,7 +122,7 @@
 comment on column vpn.vpn_remote_gw is 'Remote gateway';
 comment on column vpn.vpn_comment is 'Comment';
 
-INSERT INTO meta_fields VALUES ('vpn', 'comment', 'widget', 'area');
+INSERT INTO meta_fields VALUES ('vpn', 'vpn_comment', 'widget', 'area');
 
 GRANT ALL ON vpn TO PUBLIC;
 
@@ -107,6 +140,28 @@
 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 (
@@ -126,7 +181,7 @@
 comment on column trust.trust_mutual is 'two-way trust';
 comment on column trust.trust_comment is 'Comment';
 
-INSERT INTO meta_fields VALUES ('trust', 'comment', 'widget', 'area');
+INSERT INTO meta_fields VALUES ('trust', 'trust_comment', 'widget', 'area');
 
 GRANT ALL ON trust TO PUBLIC;
 
@@ -142,18 +197,53 @@
 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 varchar(1) not null check (dns_type like 'p' or dns_type like 's' or dns_type like 'f'),
+	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)
 );
 
-comment on table dns is 'Location DNS';
-comment on column dns.dns_on_location_id is 'Location';
-comment on column dns.dns_type is 'DNS type';
-comment on column dns.dns_domain is '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;
+