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

Annotation of /trunk/network_topology/network_topology.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 3 - (hide annotations)
Mon Feb 14 20:58:20 2005 UTC (19 years, 1 month ago) by dpavlin
File size: 4427 byte(s)
my own application about Network Topology

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