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
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 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	-- 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	INSERT INTO meta_fields VALUES ('trust', 'trust_comment', 'widget', 'area');
164
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	-- 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	-- DNS on location
200
201	create table dns (
202	dns_id serial not null,
203	dns_on_location_id int references location(location_id),
204	dns_type int not null references dns_type(dns_type_id),
205	dns_domain varchar(255) not null,
206	primary key (dns_on_location_id, dns_type, dns_domain)
207	);
208
209	-- this has to go on table, not on view!
210	insert into meta_fields values ('dns', 'dns_domain', 'widget', 'text(size=25)') ;
211
212	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	GRANT ALL ON dns TO PUBLIC;
228