/[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

Diff of /trunk/network_topology/network_topology.sql

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 6 by dpavlin, Tue Feb 15 01:04:08 2005 UTC revision 7 by dpavlin, Wed Feb 16 12:21:09 2005 UTC
# Line 43  create view link_combo as Line 43  create view link_combo as
43          select          select
44                  link_id as id,                  link_id as id,
45                  link_name as text                  link_name as text
46          from link;          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  -- location
69    
70  create table location (  create table location (
71          location_id serial not null,          location_id serial not null,
72          location_name text check (location_name != ''),          location_name text check (location_name != ''),
73            location_country_id int references country(country_id),
74            location_comment text,
75          primary key(location_id)          primary key(location_id)
76  );  );
77    
78  comment on table location is 'Locations';  comment on table location is 'Locations';
79  comment on column location.location_id is 'id';  comment on column location.location_id is 'id';
80  comment on column location.location_name is 'Location name';  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;  GRANT ALL ON location TO PUBLIC;
87    
# Line 64  create view location_combo as Line 90  create view location_combo as
90                  location_id as id,                  location_id as id,
91                  location_name as text                  location_name as text
92          from location;          from location;
           
93    
94  -- VPN connection  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 (  create table vpn (
105          vpn_id serial not null,          vpn_id serial not null,
# Line 81  create table vpn ( Line 114  create table vpn (
114  );  );
115  create unique index vpn_index on vpn(vpn_from_location_id, vpn_to_location_id, vpn_link);  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 connection';  comment on table vpn is 'VPN connections';
118  comment on column vpn.vpn_from_location_id is 'From location';  comment on column vpn.vpn_from_location_id is 'From location';
119  comment on column vpn.vpn_to_location_id is 'To location';  comment on column vpn.vpn_to_location_id is 'To location';
120  comment on column vpn.vpn_link is 'Using link';  comment on column vpn.vpn_link is 'Using link';
# Line 89  comment on column vpn.vpn_local_gw is 'L Line 122  comment on column vpn.vpn_local_gw is 'L
122  comment on column vpn.vpn_remote_gw is 'Remote gateway';  comment on column vpn.vpn_remote_gw is 'Remote gateway';
123  comment on column vpn.vpn_comment is 'Comment';  comment on column vpn.vpn_comment is 'Comment';
124    
125  INSERT INTO meta_fields VALUES ('vpn', 'comment', 'widget', 'area');  INSERT INTO meta_fields VALUES ('vpn', 'vpn_comment', 'widget', 'area');
126    
127  GRANT ALL ON vpn TO PUBLIC;  GRANT ALL ON vpn TO PUBLIC;
128    
# Line 107  create view vpn_list as Line 140  create view vpn_list as
140  comment on column vpn_list.from_location is 'From location';  comment on column vpn_list.from_location is 'From location';
141  comment on column vpn_list.to_location is 'To location';  comment on column vpn_list.to_location is 'To location';
142    
143    
144  -- location trust  -- location trust
145    
146  create table trust (  create table trust (
# Line 126  comment on column trust.trust_to_locatio Line 160  comment on column trust.trust_to_locatio
160  comment on column trust.trust_mutual is 'two-way trust';  comment on column trust.trust_mutual is 'two-way trust';
161  comment on column trust.trust_comment is 'Comment';  comment on column trust.trust_comment is 'Comment';
162    
163  INSERT INTO meta_fields VALUES ('trust', 'comment', 'widget', 'area');  INSERT INTO meta_fields VALUES ('trust', 'trust_comment', 'widget', 'area');
164    
165  GRANT ALL ON trust TO PUBLIC;  GRANT ALL ON trust TO PUBLIC;
166    
# Line 142  create view trust_list as Line 176  create view trust_list as
176  comment on column trust_list.from_location is 'From location';  comment on column trust_list.from_location is 'From location';
177  comment on column trust_list.to_location is 'To location';  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  -- DNS on location
200    
201  create table dns (  create table dns (
202            dns_id serial not null,
203          dns_on_location_id int references location(location_id),          dns_on_location_id int references location(location_id),
204          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),
205          dns_domain varchar(255) not null,          dns_domain varchar(255) not null,
206          primary key (dns_on_location_id, dns_type, dns_domain)          primary key (dns_on_location_id, dns_type, dns_domain)
207  );  );
208    
209  comment on table dns is 'Location DNS';  -- this has to go on table, not on view!
210  comment on column dns.dns_on_location_id is 'Location';  insert into meta_fields values ('dns', 'dns_domain', 'widget', 'text(size=25)') ;
 comment on column dns.dns_type is 'DNS type';  
 comment on column dns.dns_domain is 'DNS domain';  
211    
212  GRANT ALL ON dns TO PUBLIC;  create unique index dns_id_ind on dns(dns_id);
213    
214  create table dns_type ( id varchar(1) not null, text text, primary key(id) ) ;  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    

Legend:
Removed from v.6  
changed lines
  Added in v.7

  ViewVC Help
Powered by ViewVC 1.1.26