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 'Network links';
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';


-- 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_combo', '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			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	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	3	-- location trust
145
146			create table trust (
147			trust_id serial not null,
148			trust_from_location_id int references location(location_id),
149			trust_to_location_id int references location(location_id),
150			trust_mutual boolean default false,
151			trust_comment text,
152			primary key(trust_id)
153			);
154
155			create unique index trust_ind on trust(trust_from_location_id, trust_to_location_id,trust_mutual);
156
157			comment on table trust is 'Inter-location trust';
158			comment on column trust.trust_from_location_id is 'From location';
159			comment on column trust.trust_to_location_id is 'To location';
160			comment on column trust.trust_mutual is 'two-way trust';
161			comment on column trust.trust_comment is 'Comment';
162
163	dpavlin	7	INSERT INTO meta_fields VALUES ('trust', 'trust_comment', 'widget', 'area');
164	dpavlin	3
165			GRANT ALL ON trust TO PUBLIC;
166
167			create view trust_list as
168			select trust_id,
169			f.location_name as from_location,
170			t.location_name as to_location,
171			trust_mutual, trust_comment
172			from trust, location f, location t
173			where f.location_id = trust_from_location_id
174			and t.location_id = trust_to_location_id ;
175
176			comment on column trust_list.from_location is 'From location';
177			comment on column trust_list.to_location is 'To location';
178
179	dpavlin	7	-- combo for DNS type
180			create table dns_type (
181			dns_type_id serial not null,
182			dns_type_name text not null,
183			primary key(dns_type_id)
184			) ;
185			insert into dns_type values(1, 'primary');
186			insert into dns_type values(2, 'secondary');
187			insert into dns_type values(3, 'forwarder');
188
189			grant all on dns_type to public;
190
191			create view dns_type_combo as
192			select dns_type_id as id, dns_type_name as text
193			from dns_type order by dns_type_id;
194
195
196			-- hide this table
197			-- INSERT INTO meta_tables VALUES ('dns_type_combo', 'hide', 1);
198
199	dpavlin	3	-- DNS on location
200
201			create table dns (
202	dpavlin	7	dns_id serial not null,
203	dpavlin	3	dns_on_location_id int references location(location_id),
204	dpavlin	7	dns_type int not null references dns_type(dns_type_id),
205	dpavlin	3	dns_domain varchar(255) not null,
206			primary key (dns_on_location_id, dns_type, dns_domain)
207			);
208
209	dpavlin	7	-- this has to go on table, not on view!
210			insert into meta_fields values ('dns', 'dns_domain', 'widget', 'text(size=25)') ;
211	dpavlin	3
212	dpavlin	7	create unique index dns_id_ind on dns(dns_id);
213
214			create view dns_list as
215			select dns_id,
216			l.location_name as location,
217			t.dns_type_name as type,
218			dns_domain
219			from dns, location l, dns_type t
220			where location_id = dns_on_location_id and dns_type_id = dns_type ;
221
222			comment on view dns_list is 'DNS on locations';
223			comment on column dns_list.location is 'Location';
224			comment on column dns_list.type is 'server type';
225			comment on column dns_list.dns_domain is 'DNS domain';
226
227	dpavlin	3	GRANT ALL ON dns TO PUBLIC;
228	dpavlin	5