/[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 3 - (show annotations)
Mon Feb 14 20:58:20 2005 UTC (14 years, 8 months ago) by dpavlin
File size: 4427 byte(s)
my own application about Network Topology

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;
47
48 -- location
49
50 create table location (
51 location_id serial not null,
52 location_name text check (location_name != ''),
53 primary key(location_id)
54 );
55
56 comment on table location is 'Locations';
57 comment on column location.location_id is 'id';
58 comment on column location.location_name is 'Location name';
59
60 GRANT ALL ON location TO PUBLIC;
61
62 create view location_combo as
63 select
64 location_id as id,
65 location_name as text
66 from location;
67
68
69 -- VPN connection
70
71 create table vpn (
72 vpn_id serial not null,
73 vpn_from_location_id int not null references location(location_id),
74 vpn_to_location_id int not null references location(location_id),
75 vpn_link int not null references link(link_id),
76 vpn_local_gw inet not null,
77 vpn_remote_gw inet not null,
78 vpn_comment text,
79 primary key(vpn_id)
80
81 );
82 create unique index vpn_index on vpn(vpn_from_location_id, vpn_to_location_id, vpn_link);
83
84 comment on table vpn is 'VPN connection';
85 comment on column vpn.vpn_from_location_id is 'From location';
86 comment on column vpn.vpn_to_location_id is 'To location';
87 comment on column vpn.vpn_link is 'Using link';
88 comment on column vpn.vpn_local_gw is 'Local gateway';
89 comment on column vpn.vpn_remote_gw is 'Remote gateway';
90 comment on column vpn.vpn_comment is 'Comment';
91
92 INSERT INTO meta_fields VALUES ('vpn', 'comment', 'widget', 'area');
93
94 GRANT ALL ON vpn TO PUBLIC;
95
96 create view vpn_list as
97 select vpn_id,
98 f.location_name as from_location,
99 t.location_name as to_location,
100 link_name, vpn_local_gw, vpn_remote_gw,
101 vpn_comment
102 from vpn, location f, location t, link
103 where f.location_id = vpn_from_location_id
104 and t.location_id = vpn_to_location_id
105 and link_id = vpn_link ;
106
107 comment on column vpn_list.from_location is 'From location';
108 comment on column vpn_list.to_location is 'To location';
109
110 -- location trust
111
112 create table trust (
113 trust_id serial not null,
114 trust_from_location_id int references location(location_id),
115 trust_to_location_id int references location(location_id),
116 trust_mutual boolean default false,
117 trust_comment text,
118 primary key(trust_id)
119 );
120
121 create unique index trust_ind on trust(trust_from_location_id, trust_to_location_id,trust_mutual);
122
123 comment on table trust is 'Inter-location trust';
124 comment on column trust.trust_from_location_id is 'From location';
125 comment on column trust.trust_to_location_id is 'To location';
126 comment on column trust.trust_mutual is 'two-way trust';
127 comment on column trust.trust_comment is 'Comment';
128
129 INSERT INTO meta_fields VALUES ('trust', 'comment', 'widget', 'area');
130
131 GRANT ALL ON trust TO PUBLIC;
132
133 create view trust_list as
134 select trust_id,
135 f.location_name as from_location,
136 t.location_name as to_location,
137 trust_mutual, trust_comment
138 from trust, location f, location t
139 where f.location_id = trust_from_location_id
140 and t.location_id = trust_to_location_id ;
141
142 comment on column trust_list.from_location is 'From location';
143 comment on column trust_list.to_location is 'To location';
144
145 -- DNS on location
146
147 create table dns (
148 dns_on_location_id int references location(location_id),
149 dns_type varchar(1) not null check (dns_type like 'p' or dns_type like 's' or dns_type like 'f'),
150 dns_domain varchar(255) not null,
151 primary key (dns_on_location_id, dns_type, dns_domain)
152 );
153
154 comment on table dns is 'Location DNS';
155 comment on column dns.dns_on_location_id is 'Location';
156 comment on column dns.dns_type is 'DNS type';
157 comment on column dns.dns_domain is 'DNS domain';
158
159 GRANT ALL ON dns TO PUBLIC;

  ViewVC Help
Powered by ViewVC 1.1.26