perfsonar development work

[ulisses <>]

Hi Jason

finally, here is the SQL to XML conversion I propised to send:

The corrently exported information from the topology service is:

- nodes
- interfaces 
- networks that relate to previous infterfaces

The source SQL file is CNM's schema, which I beautified and attached 
(perfsonar-schema-byme.sql)

The format of this file is:

the information being exported, currently: node, interface and network, in
three sections and within each section:

- a subsection for the source information -- the SQL schema (marked as @SQL) 
- a subsection for the target format -- the XML schema (marked as @XML)

Some comments between David (CNM) and I are included, I think they can help
understanding some conversions done.

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@ node
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

@SQL 

CREATE TABLE network_node_base (
    ip_management_address text,     # Doesn't has an equivalent
    network_node_type_id integer,   # Doesn't has an equivalent, this field 
implies
                                    #
                                    # CREATE TABLE product_type (
                                    #    id serial NOT NULL,
                                    #    vendor_name text,
                                    #    type_name text,
                                    #    description text
                                    # );
                                    #
    location_id integer,            # Dooesn't has a real equivalent, this 
field implies a lot of
                                    # fields (see below) and it should match 
a single
                                    # a nmwgtopo3:location { xsd:string } 
element
                                    #
                                    # CREATE TABLE "location" (
                                    #    id serial NOT NULL,
                                    #    description text, @ converted to 
nmwgtopo3:location + "," + nmwgtopo3:city
                                    #    utm text,
                                    #    in_domain_id text,
                                    #    domain_id integer
                                    # );
                                    #
                                    # CREATE TABLE "domain" (
                                    #    domain_id serial NOT NULL,
                                    #    domain_name text, @ converted to 
nmwgtopo3:institution
                                    #    domain_description text,   
                                    #    domain_external_id text,
                                    #    domain_networks_name text,
                                    #    country_iso3166_code text  @ 
converted to element nmwgtopo3:country
                                    # );
                                    #
                                    #
    dns_name1 text,                 # @ converted to Hostname
    dns_name2 text,                 # @ Doesn't has an equivalent
    usage_type information_schema.cardinal_number, # converted to attribute 
type of nmwgtopo3:name
    name text                       # @ converted to element nmwgtopo3:name
);


@ XML: nmtopo_ver3.rnc

Node =
        element nmwgtopo3:node { NodeContent }

NodeContent =
        Identifier? &
        BaseNodeIdRef? &
        BaseRole? &
        element nmwgtopo3:name {
                attribute type { xsd:string }? &
                xsd:string
        }? &
        element nmwgtopo3:type { xsd:string }? &
        element nmwgtopo3:hostName { xsd:string }? &
        element nmwgtopo3:description { xsd:string }? &
        element nmwgtopo3:cpu { xsd:string }? &
        element nmwgtopo3:operSys { xsd:string }? &
        element nmwgtopo3:location { xsd:string }? &
        element nmwgtopo3:country { xsd:string }? &
        element nmwgtopo3:city { xsd:string }? &
        element nmwgtopo3:latitude { xsd:string }? &
        element nmwgtopo3:longitude { xsd:string }? &
        element nmwgtopo3:institution { xsd:string }? &
        (
                BaseInterface |
                L2Interface |
                L3Interface
        )*


@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@ interface
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

(11:29:00) David Schmitz (CNM): yes, L3Interface (nmwg) == 
network_interface_base 

@ SQL:

CREATE TABLE network_interface_base (
    if_highspeed bigint,                         # @ converted to 
nmtl3:capacity
    if_mtu text,                                 # Doesn't has an equivalent
    if_type information_schema.cardinal_number,  # @ converted to nmtl3:type
    if_index integer,                            # @ converted to 
nmtl3:ifIndex
    parent_interface_object_id integer,          # Doesn't has an equivalent
                                                 #
    layer integer,                               # Doesn't has an equivalent
                                                 #
    default_ip_interface_address text,           # @ converted to 
nmtl3:ifAddress which is of type L3Address
                                                 # L3Address =
                                                 #        (
                                                 #                attribute 
value { xsd:string } |
                                                 #                text
                                                 #        ) &
                                                 #
    default_ip_network_subnet_prefix text,       # @ converted to 
nmtl3:netmask
    if_alias text,                               # Doesn't has an equivalent 
and we don't want to convert it to name
    if_description text,                         # @ converted to 
nmtl3:ifDescription
    if_speed bigint,                             # @ converted to 
nmtl3:capacity
    ospf_link_type text,                         # Doesn't has an equivalent
    speed bigint                                 # @ converted to 
nmtl3:capacity
);

Some words about speed, if_highspeed, and if_speed:

# (09:52:51) David Schmitz (CNM): so, the easiest way at the moment would be 
to map only this one with our attribute speed
# (09:53:11) Ulisses: so, if I have to choose between speed, if_speed and 
if_high_speed, I will do speed > if_high_speed > if_speed, where '>' means 
higher preference
# (09:53:55) David Schmitz (CNM): yes, in fact if_high_speed is only 
meaningfull if if_speed has all its 32 bits set to 1 (=4GB)
# (09:54:07) David Schmitz (CNM): this is SNMP specific from MIB2 and its 
extension

@ XML: 

L3Interface =
        element nmtl3:interface { L3InterfaceContent }


                                # We want to have an L2InterfaceIdRef
                                # but the current tooling doesnt support
                                # having two attributes of the same name
                                # (even in different namespaces)
L3InterfaceContent =
        Identifier? &
        L3InterfaceIdRef? &
        element nmtl3:ipAddress { L3Address }? &
        element nmtl3:netmask { xsd:string }? &
        element nmtl3:ifName { xsd:string }? &
        element nmtl3:ifDescription { xsd:string }? &
        element nmtl3:ifAddress { L3Address }? &
        element nmtl3:ifHostName { xsd:string }? &
        element nmtl3:ifIndex { xsd:string }? &
        element nmtl3:type { xsd:string }? &
        element nmtl3:capacity { xsd:string }?

L3Address =
        (
                attribute value { xsd:string } |
                text
        ) &
        attribute type { xsd:string }


# Comments on how to find all network_interfaces of a node
#
# (10:26:20) David Schmitz (CNM): look at network_interface_key
# (10:27:21) David Schmitz (CNM): the primary key (from 
network_interface_key_base) consists of
# (10:27:27) David Schmitz (CNM): object_id_of_network_node + object_id
# (10:27:35) Ulisses: ok
# (10:27:37) Ulisses: object_id_of_network_node
# (10:27:50) David Schmitz (CNM): it this is also reflected by its external 
name
# (10:28:19) David Schmitz (CNM): which is (for interfaces) 
domain_id:unique_node_name:unique_interface_name
# (10:29:06) David Schmitz (CNM): of course unqiue always mean in the given 
level of this hierarchy of concatenated strings
# (10:29:34) David Schmitz (CNM): so unique_node_name is unique within the 
given domain_id
# (10:29:56) David Schmitz (CNM): and unique_interface_id is unique for the 
given unique_node_id + domain_id

# (10:33:54) David Schmitz (CNM): yes, this is a bit inconsistent what I said
# (10:34:07) David Schmitz (CNM): primary keys aren't inherited
# (10:34:35) David Schmitz (CNM): the truth is, that the object_id is the 
primary key
# (10:34:44) David Schmitz (CNM): in network_interface_key
# (10:36:18) David Schmitz (CNM): so, network_interface_key relates 
(internal) object_id to object_id_of_network_node + in_domain_id (of the 
interface unqiue within the node)
# (10:36:36) Ulisses: now, I can relate nodes and network_interface_key and 
nodes, but how to relate and network_interface_key and network_interface?
# (10:36:52) David Schmitz (CNM): by the object_id 
# (10:36:58) Ulisses: ???
# (10:37:12) David Schmitz (CNM): (only one integer key interal to the db for 
references
# (10:37:34) Ulisses: the object id of network_interface_key is the same id 
as in network_interface?
# (10:37:41) David Schmitz (CNM): keys
# (10:37:42) David Schmitz (CNM): yes
# (10:37:46) Ulisses: ok!

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@ network
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

@ SQL: CNM

CREATE TABLE network_base (
    network_type text,          # @ converted to nmtl3:type
    bandwidth bigint,           # Doesn't has an equivalent
    description text,           # @ converted to attribute of nmtl3:name
    network_name text,          # @ converted to nmtl3:name
    ospf_network_type text      # Doesn't has an equivalent
);

@ XML: L3Link (best for me):

# [day 1]
# (11:32:39) Ulisses: and network (cnm) seems to be L3Link (nmwg) and not 
L3Network (nmwg)
# (11:41:15) Ulisses: l3 network has ip addressing
# (11:41:27) Ulisses: while l3 link doesn't
#
# [day 2]
# (12:21:59) Ulisses: l3network seems more like ip_subnetwork because it 
implies l3 addresses
# (12:22:03) Ulisses: while l3link 
# (12:22:26) Ulisses: has no need for having l3 or l2 addresses, but it can
#
# [...]
#
# (12:31:04) Ulisses: for me l3network is = ip_subnetwork, whre the l3 
network can be composed on > 2 l3/l2 interfaces and l3/l2 links, but always 
only one prefix
# (12:31:08) David Schmitz (CNM): of course such a (more complex) (IP) 
network consists of multiple of subnetworks which are realized each on a 
physical l2 network in our sense
# (12:32:07) Ulisses: in anycase, my point was in the first place that 
network_base is l3link
# (12:32:49) David Schmitz (CNM): this seems so
# (12:32:51) David Schmitz (CNM): yes

L3Link =
        element nmtl3:link { L3LinkContent }

                                # We want to have an L2LinkIdRef
                                # but the current tooling doesnt support
                                # having two attributes of the same name
                                # (even in different namespaces)
L3LinkContent =
        Identifier? &
        L3LinkIdRef? &
        element nmtl3:index { xsd:string }? &
        element nmtl3:type { xsd:string }? &
        element nmtl3:name {
                attribute type { xsd:string }? &
                xsd:string
        }? &
        element nmtl3:globalName {
                attribute type { xsd:string }? &
                xsd:string
        }? &
        element nmtl3:authRealm {
                xsd:string
        }? &
        (
                L3Interface |
                L2Interface
        )* &
        (
                L3Link |
                L2Link
        )* &
        Node*

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@@@ network to interface
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

@ SQL: 

CREATE TABLE network_to_interface (
    network_object_id integer,
    network_interface_object_id integer,
    interface_order integer
)
INHERITS (object_with_historyinfo);

ALTER TABLE ONLY network_to_interface ADD CONSTRAINT 
network_to_interface_network_interface_object_id_fkey FOREIGN KEY 
(network_interface_object_id) REFERENCES network_interface_key(object_id) ON 
UPDATE RESTRICT ON DELETE RESTRICT;

ALTER TABLE ONLY network_to_interface ADD CONSTRAINT 
network_to_interface_network_object_id_fkey FOREIGN KEY (network_object_id) 
REFERENCES network_key(object_id) ON UPDATE RESTRICT ON DELETE RESTRICT;

@ XML: 
  
The way to relate network to elements with the XML schema is to use l3links 
for the
networks because it contains l3 interfaces.





--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


SET search_path = public, pg_catalog;

--
-- Name: myrowtype; Type: TYPE; Schema: public; Owner: a2824al
--

CREATE TYPE myrowtype AS (
        f1 integer,
        f2 text,
        f3 numeric
);


ALTER TYPE public.myrowtype OWNER TO a2824al;

SET default_tablespace = '';

SET default_with_oids = true;

--
-- Name: object_with_historyinfo; Type: TABLE; Schema: public; Owner: 
a2824al; Tablespace: 
--

CREATE TABLE object_with_historyinfo (
    valid_from timestamp without time zone,
    valid_to timestamp without time zone
);


ALTER TABLE public.object_with_historyinfo OWNER TO a2824al;

--
-- Name: generic_versioned_object; Type: TABLE; Schema: public; Owner: 
a2824al; Tablespace: 
--

CREATE TABLE generic_versioned_object (
    object_id integer NOT NULL,
    object_version integer NOT NULL
)
INHERITS (object_with_historyinfo);


ALTER TABLE public.generic_versioned_object OWNER TO a2824al;

--
-- Name: snmp_access_for_monitoring_object; Type: TABLE; Schema: public; 
Owner: a2824al; Tablespace: 
--

CREATE TABLE snmp_access_for_monitoring_object (
    snmp_community text,
    snmp_status_aggregation boolean,
    snmp_access boolean
)
INHERITS (generic_versioned_object);


ALTER TABLE public.snmp_access_for_monitoring_object OWNER TO a2824al;

--
-- Name: cdp_info_for_monitoring_object; Type: TABLE; Schema: public; Owner: 
a2824al; Tablespace: 
--

CREATE TABLE cdp_info_for_monitoring_object (
    cdp_discovered boolean,
    cdp_valid boolean,
    cdp_first_discovery timestamp without time zone,
    cdp_last_discovery timestamp without time zone
)
INHERITS (snmp_access_for_monitoring_object);


ALTER TABLE public.cdp_info_for_monitoring_object OWNER TO a2824al;

--
-- Name: current_cdp_info_for_monitoring_object; Type: VIEW; Schema: public; 
Owner: a2824al
--

CREATE VIEW current_cdp_info_for_monitoring_object AS
    SELECT m.valid_from, m.valid_to, m.object_id, m.object_version, 
m.snmp_community, m.snmp_status_aggregation, m.snmp_access, m.cdp_discovered, 
m.cdp_valid, m.cdp_first_discovery, m.cdp_last_discovery FROM 
cdp_info_for_monitoring_object m WHERE (((m.valid_from IS NULL) OR 
(m.valid_from < ('now'::text)::timestamp(6) with time zone)) AND ((m.valid_to 
IS NULL) OR (m.valid_to > ('now'::text)::timestamp(6) with time zone)));


ALTER TABLE public.current_cdp_info_for_monitoring_object OWNER TO a2824al;

SET default_with_oids = false;

--
-- Name: ip_subnetwork_base; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE ip_subnetwork_base (
    ip_prefix text,
    description text,
    object_id_of_used_network integer
);


ALTER TABLE public.ip_subnetwork_base OWNER TO a2824al;

--
-- Name: monitoring_object_base; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE monitoring_object_base (
    display_count smallint
);


ALTER TABLE public.monitoring_object_base OWNER TO a2824al;

--
-- Name: COLUMN monitoring_object_base.display_count; Type: COMMENT; Schema: 
public; Owner: a2824al
--

COMMENT ON COLUMN monitoring_object_base.display_count IS 'counts appearance 
of this object
in the maps
to know wheter it can be deleted
safely

could also be handeled
by referential integrity';


SET default_with_oids = true;

--
-- Name: monitoring_object; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE monitoring_object (
    "valid" boolean
)
INHERITS (generic_versioned_object, monitoring_object_base);


ALTER TABLE public.monitoring_object OWNER TO a2824al;

--
-- Name: ip_subnetwork; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE ip_subnetwork (
)
INHERITS (monitoring_object, ip_subnetwork_base);


ALTER TABLE public.ip_subnetwork OWNER TO a2824al;

--
-- Name: current_ip_network; Type: VIEW; Schema: public; Owner: a2824al
--

CREATE VIEW current_ip_network AS
    SELECT m.valid_from, m.valid_to, m.object_id, m.object_version, 
m.display_count, m."valid", m.ip_prefix, m.description, 
m.object_id_of_used_network FROM ip_subnetwork m WHERE (((m.valid_from IS 
NULL) OR (m.valid_from < ('now'::text)::timestamp(6) with time zone)) AND 
((m.valid_to IS NULL) OR (m.valid_to > ('now'::text)::timestamp(6) with time 
zone)));


ALTER TABLE public.current_ip_network OWNER TO a2824al;

SET default_with_oids = false;

--
-- Name: ip_subnetwork_interface_base; Type: TABLE; Schema: public; Owner: 
a2824al; Tablespace: 
--

CREATE TABLE ip_subnetwork_interface_base (
    ip_address text,
    description text,
    object_id_of_used_network_interface integer
);


ALTER TABLE public.ip_subnetwork_interface_base OWNER TO a2824al;

SET default_with_oids = true;

--
-- Name: ip_subnetwork_interface; Type: TABLE; Schema: public; Owner: 
a2824al; Tablespace: 
--

CREATE TABLE ip_subnetwork_interface (
)
INHERITS (monitoring_object, ip_subnetwork_interface_base);


ALTER TABLE public.ip_subnetwork_interface OWNER TO a2824al;

--
-- Name: current_ip_network_interface; Type: VIEW; Schema: public; Owner: 
a2824al
--

CREATE VIEW current_ip_network_interface AS
    SELECT m.valid_from, m.valid_to, m.object_id, m.object_version, 
m.display_count, m."valid", m.ip_address, m.description, 
m.object_id_of_used_network_interface FROM ip_subnetwork_interface m WHERE 
(((m.valid_from IS NULL) OR (m.valid_from < ('now'::text)::timestamp(6) with 
time zone)) AND ((m.valid_to IS NULL) OR (m.valid_to > 
('now'::text)::timestamp(6) with time zone)));


ALTER TABLE public.current_ip_network_interface OWNER TO a2824al;

--
-- Name: current_monitoring_object; Type: VIEW; Schema: public; Owner: a2824al
--

CREATE VIEW current_monitoring_object AS
    SELECT m.valid_from, m.valid_to, m.object_id, m.object_version, 
m.display_count, m."valid" FROM monitoring_object m WHERE (((m.valid_from IS 
NULL) OR (m.valid_from < ('now'::text)::timestamp(6) with time zone)) AND 
((m.valid_to IS NULL) OR (m.valid_to > ('now'::text)::timestamp(6) with time 
zone)));


ALTER TABLE public.current_monitoring_object OWNER TO a2824al;

SET default_with_oids = false;

--
-- Name: network_base; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE network_base (
    network_type text,
    bandwidth bigint,
    description text,
    network_name text,
    ospf_network_type text
);


ALTER TABLE public.network_base OWNER TO a2824al;

--
-- Name: COLUMN network_base.network_type; Type: COMMENT; Schema: public; 
Owner: a2824al
--

COMMENT ON COLUMN network_base.network_type IS 'to distinguish between 
technologies
like MPLS, VLAN, ...

of course relates to ifType of
associated interfaces in some way';


--
-- Name: COLUMN network_base.bandwidth; Type: COMMENT; Schema: public; Owner: 
a2824al
--

COMMENT ON COLUMN network_base.bandwidth IS 'bandwidth of the link (in kbit/s)

should relate to ifSpeed/ifHighSpeed
of associated interfaces';


SET default_with_oids = true;

--
-- Name: network; Type: TABLE; Schema: public; Owner: a2824al; Tablespace: 
--

CREATE TABLE network (
)
INHERITS (network_base, monitoring_object);


ALTER TABLE public.network OWNER TO a2824al;

--
-- Name: current_network; Type: VIEW; Schema: public; Owner: a2824al
--

CREATE VIEW current_network AS
    SELECT m.valid_from, m.valid_to, m.object_id, m.object_version, 
m.network_type, m.bandwidth, m.display_count, m."valid", m.description, 
m.network_name, m.ospf_network_type FROM network m WHERE (((m.valid_from IS 
NULL) OR (m.valid_from < ('now'::text)::timestamp(6) with time zone)) AND 
((m.valid_to IS NULL) OR (m.valid_to > ('now'::text)::timestamp(6) with time 
zone)));


ALTER TABLE public.current_network OWNER TO a2824al;

SET default_with_oids = false;

--
-- Name: network_interface_base; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE network_interface_base (
    if_highspeed bigint,
    if_mtu text,
    if_type information_schema.cardinal_number,
    if_index integer,
    parent_interface_object_id integer,
    layer integer,
    default_ip_interface_address text,
    default_ip_network_subnet_prefix text,
    if_alias text,
    if_description text,
    if_speed bigint,
    ospf_link_type text,
    speed bigint
);


ALTER TABLE public.network_interface_base OWNER TO a2824al;

--
-- Name: COLUMN network_interface_base.if_type; Type: COMMENT; Schema: 
public; Owner: a2824al
--

COMMENT ON COLUMN network_interface_base.if_type IS 'ifType of MIB2';


--
-- Name: COLUMN network_interface_base.if_index; Type: COMMENT; Schema: 
public; Owner: a2824al
--

COMMENT ON COLUMN network_interface_base.if_index IS 'ifIndex of MIB2';


--
-- Name: COLUMN network_interface_base.layer; Type: COMMENT; Schema: public; 
Owner: a2824al
--

COMMENT ON COLUMN network_interface_base.layer IS '1/2 or 3';


--
-- Name: COLUMN network_interface_base.default_ip_network_subnet_prefix; 
Type: COMMENT; Schema: public; Owner: a2824al
--

COMMENT ON COLUMN network_interface_base.default_ip_network_subnet_prefix IS 
'should match default_ip_interface_address,

for simplicity at the moment:

later different layers of
interfaces (1/2 or 3 ...)
will have separate tables';


--
-- Name: COLUMN network_interface_base.if_alias; Type: COMMENT; Schema: 
public; Owner: a2824al
--

COMMENT ON COLUMN network_interface_base.if_alias IS 'ifAlias from MIB2';


--
-- Name: COLUMN network_interface_base.if_description; Type: COMMENT; Schema: 
public; Owner: a2824al
--

COMMENT ON COLUMN network_interface_base.if_description IS 'ifDescription of 
MIB2';


--
-- Name: COLUMN network_interface_base.if_speed; Type: COMMENT; Schema: 
public; Owner: a2824al
--

COMMENT ON COLUMN network_interface_base.if_speed IS 'if_speed of MIB2';


SET default_with_oids = true;

--
-- Name: network_interface; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE network_interface (
)
INHERITS (monitoring_object, network_interface_base);


ALTER TABLE public.network_interface OWNER TO a2824al;

--
-- Name: current_network_interface; Type: VIEW; Schema: public; Owner: a2824al
--

CREATE VIEW current_network_interface AS
    SELECT m.valid_from, m.valid_to, m.object_id, m.object_version, 
m.if_highspeed, m.if_mtu, m.if_type, m.if_index, 
m.parent_interface_object_id, m.layer, m.default_ip_interface_address, 
m.default_ip_network_subnet_prefix, m.if_alias, m.if_description, 
m.display_count, m."valid", m.if_speed FROM network_interface m WHERE 
(((m.valid_from IS NULL) OR (m.valid_from < ('now'::text)::timestamp(6) with 
time zone)) AND ((m.valid_to IS NULL) OR (m.valid_to > 
('now'::text)::timestamp(6) with time zone)));


ALTER TABLE public.current_network_interface OWNER TO a2824al;

SET default_with_oids = false;

--
-- Name: network_node_base; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE network_node_base (
    ip_management_address text,
    network_node_type_id integer,
    location_id integer,
    dns_name1 text,
    dns_name2 text,
    usage_type information_schema.cardinal_number,
    name text
);


ALTER TABLE public.network_node_base OWNER TO a2824al;

--
-- Name: COLUMN network_node_base.usage_type; Type: COMMENT; Schema: public; 
Owner: a2824al
--

COMMENT ON COLUMN network_node_base.usage_type IS 'from CNM GWIN:
core router
access router
other router';


SET default_with_oids = true;

--
-- Name: network_node; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE network_node (
)
INHERITS (monitoring_object, network_node_base);


ALTER TABLE public.network_node OWNER TO a2824al;

--
-- Name: current_network_node; Type: VIEW; Schema: public; Owner: a2824al
--

CREATE VIEW current_network_node AS
    SELECT m.valid_from, m.valid_to, m.object_id, m.object_version, 
m.ip_management_address, m.network_node_type_id, m.location_id, m.dns_name1, 
m.dns_name2, m.usage_type, m.display_count, m."valid", m.name FROM 
network_node m WHERE (((m.valid_from IS NULL) OR (m.valid_from < 
('now'::text)::timestamp(6) with time zone)) AND ((m.valid_to IS NULL) OR 
(m.valid_to > ('now'::text)::timestamp(6) with time zone)));


ALTER TABLE public.current_network_node OWNER TO a2824al;

--
-- Name: network_to_interface; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE network_to_interface (
    network_object_id integer,
    network_interface_object_id integer,
    interface_order integer
)
INHERITS (object_with_historyinfo);


ALTER TABLE public.network_to_interface OWNER TO a2824al;

--
-- Name: COLUMN network_to_interface.interface_order; Type: COMMENT; Schema: 
public; Owner: a2824al
--

COMMENT ON COLUMN network_to_interface.interface_order IS 'necessary for the 
main type of networks wich are p2p links,
to distinguish between 
to and from interface
(necessary for metrics which
distinguish both corresponding directions)';


--
-- Name: current_network_to_interface; Type: VIEW; Schema: public; Owner: 
a2824al
--

CREATE VIEW current_network_to_interface AS
    SELECT m.valid_from, m.valid_to, m.network_object_id, 
m.network_interface_object_id, m.interface_order FROM network_to_interface m 
WHERE (((m.valid_from IS NULL) OR (m.valid_from < ('now'::text)::timestamp(6) 
with time zone)) AND ((m.valid_to IS NULL) OR (m.valid_to > 
('now'::text)::timestamp(6) with time zone)));


ALTER TABLE public.current_network_to_interface OWNER TO a2824al;

--
-- Name: perfsonar_access_for_monitoring_object; Type: TABLE; Schema: public; 
Owner: a2824al; Tablespace: 
--

CREATE TABLE perfsonar_access_for_monitoring_object (
    measurement_archive_access boolean,
    measurement_archive_address text,
    metric_name information_schema.cardinal_number
)
INHERITS (generic_versioned_object);


ALTER TABLE public.perfsonar_access_for_monitoring_object OWNER TO a2824al;

--
-- Name: COLUMN perfsonar_access_for_monitoring_object.metric_name; Type: 
COMMENT; Schema: public; Owner: a2824al
--

COMMENT ON COLUMN perfsonar_access_for_monitoring_object.metric_name IS 
'later this should be in a extra associated table to support multiple metrics 
per monitoring object

for now, we only have ''utilization''';


--
-- Name: current_perfsonar_access_for_monitoring_object; Type: VIEW; Schema: 
public; Owner: a2824al
--

CREATE VIEW current_perfsonar_access_for_monitoring_object AS
    SELECT m.valid_from, m.valid_to, m.object_id, m.object_version, 
m.measurement_archive_access, m.measurement_archive_address, m.metric_name 
FROM perfsonar_access_for_monitoring_object m WHERE (((m.valid_from IS NULL) 
OR (m.valid_from < ('now'::text)::timestamp(6) with time zone)) AND 
((m.valid_to IS NULL) OR (m.valid_to > ('now'::text)::timestamp(6) with time 
zone)));


ALTER TABLE public.current_perfsonar_access_for_monitoring_object OWNER TO 
a2824al;

--
-- Name: domain; Type: TABLE; Schema: public; Owner: a2824al; Tablespace: 
--

CREATE TABLE "domain" (
    domain_id serial NOT NULL,
    domain_name text,
    domain_description text,
    domain_external_id text,
    domain_networks_name text,
    country_iso3166_code text
);


ALTER TABLE public."domain" OWNER TO a2824al;

--
-- Name: COLUMN "domain".domain_name; Type: COMMENT; Schema: public; Owner: 
a2824al
--

COMMENT ON COLUMN "domain".domain_name IS 'name in english,
could be used as a label in
applications, human-readable';


--
-- Name: COLUMN "domain".domain_external_id; Type: COMMENT; Schema: public; 
Owner: a2824al
--

COMMENT ON COLUMN "domain".domain_external_id IS 'domain key name,
globally valid,
not only in this db,
not in any case good human-readable';


--
-- Name: domain_domain_id_seq; Type: SEQUENCE SET; Schema: public; Owner: 
a2824al
--

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('"domain"', 
'domain_id'), 13, true);


--
-- Name: domain_named_object; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE domain_named_object (
    domain_id integer,
    in_domain_id text
);


ALTER TABLE public.domain_named_object OWNER TO a2824al;

SET default_with_oids = false;

--
-- Name: generic_versioned_object_key; Type: TABLE; Schema: public; Owner: 
a2824al; Tablespace: 
--

CREATE TABLE generic_versioned_object_key (
    object_id serial NOT NULL
);


ALTER TABLE public.generic_versioned_object_key OWNER TO a2824al;

--
-- Name: generic_versioned_object_key_object_id_seq; Type: SEQUENCE SET; 
Schema: public; Owner: a2824al
--

SELECT 
pg_catalog.setval(pg_catalog.pg_get_serial_sequence('generic_versioned_object_key',
 'object_id'), 61096, true);


SET default_with_oids = true;

--
-- Name: product_type; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE product_type (
    id serial NOT NULL,
    vendor_name text,
    type_name text,
    description text
);


ALTER TABLE public.product_type OWNER TO a2824al;

--
-- Name: COLUMN product_type.id; Type: COMMENT; Schema: public; Owner: a2824al
--

COMMENT ON COLUMN product_type.id IS 'db internal id';


--
-- Name: hardware_type; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE hardware_type (
)
INHERITS (product_type);


ALTER TABLE public.hardware_type OWNER TO a2824al;

SET default_with_oids = false;

--
-- Name: ip_subnetwork_interface_key_base; Type: TABLE; Schema: public; 
Owner: a2824al; Tablespace: 
--

CREATE TABLE ip_subnetwork_interface_key_base (
    domain_id integer NOT NULL,
    in_domain_id text NOT NULL
);


ALTER TABLE public.ip_subnetwork_interface_key_base OWNER TO a2824al;

--
-- Name: TABLE ip_subnetwork_interface_key_base; Type: COMMENT; Schema: 
public; Owner: a2824al
--

COMMENT ON TABLE ip_subnetwork_interface_key_base IS 'in contrast to a 
network_interface 
the id of an ip_network_interface is not bound
to a specific router
and has its own in_domain_id bound only to domainid

the association to the router and 
physical (snmp mib-2 view) interface
can vary over time

ip address and prefix may also vary
but might also be used as a key

the name of a connected customer might also
be used as a key';


--
-- Name: monitoring_object_key_base; Type: TABLE; Schema: public; Owner: 
a2824al; Tablespace: 
--

CREATE TABLE monitoring_object_key_base (
);


ALTER TABLE public.monitoring_object_key_base OWNER TO a2824al;

--
-- Name: monitoring_object_key; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE monitoring_object_key (
)
INHERITS (generic_versioned_object_key, monitoring_object_key_base);


ALTER TABLE public.monitoring_object_key OWNER TO a2824al;

--
-- Name: ip_subnetwork_interface_key; Type: TABLE; Schema: public; Owner: 
a2824al; Tablespace: 
--

CREATE TABLE ip_subnetwork_interface_key (
)
INHERITS (monitoring_object_key, ip_subnetwork_interface_key_base);


ALTER TABLE public.ip_subnetwork_interface_key OWNER TO a2824al;

--
-- Name: ip_subnetwork_key_base; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE ip_subnetwork_key_base (
    domain_id integer NOT NULL,
    in_domain_id text NOT NULL
);


ALTER TABLE public.ip_subnetwork_key_base OWNER TO a2824al;

--
-- Name: ip_subnetwork_key; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE ip_subnetwork_key (
)
INHERITS (monitoring_object_key, ip_subnetwork_key_base);


ALTER TABLE public.ip_subnetwork_key OWNER TO a2824al;

SET default_with_oids = true;

--
-- Name: location; Type: TABLE; Schema: public; Owner: a2824al; Tablespace: 
--

CREATE TABLE "location" (
    id serial NOT NULL,
    description text,
    utm text,
    in_domain_id text,
    domain_id integer
);


ALTER TABLE public."location" OWNER TO a2824al;

--
-- Name: COLUMN "location".in_domain_id; Type: COMMENT; Schema: public; 
Owner: a2824al
--

COMMENT ON COLUMN "location".in_domain_id IS 'globally valid
also external to the db';


--
-- Name: location_id_seq; Type: SEQUENCE SET; Schema: public; Owner: a2824al
--

SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('"location"', 
'id'), 2257, true);


SET default_with_oids = false;

--
-- Name: network_interface_key_base; Type: TABLE; Schema: public; Owner: 
a2824al; Tablespace: 
--

CREATE TABLE network_interface_key_base (
    object_id_of_network_node integer NOT NULL,
    in_domain_id text
);


ALTER TABLE public.network_interface_key_base OWNER TO a2824al;

--
-- Name: network_interface_key; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE network_interface_key (
)
INHERITS (network_interface_key_base, monitoring_object_key);


ALTER TABLE public.network_interface_key OWNER TO a2824al;

--
-- Name: network_node_key_base; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE network_node_key_base (
    domain_id integer,
    in_domain_id text
)
INHERITS (monitoring_object_key_base);


ALTER TABLE public.network_node_key_base OWNER TO a2824al;

--
-- Name: network_node_key; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE network_node_key (
)
INHERITS (monitoring_object_key, network_node_key_base);


ALTER TABLE public.network_node_key OWNER TO a2824al;

--
-- Name: network_interface_joined_key; Type: VIEW; Schema: public; Owner: 
a2824al
--

CREATE VIEW network_interface_joined_key AS
    SELECT k.object_id, k.object_id_of_network_node, k.in_domain_id, 
r.domain_id, r.in_domain_id AS in_domain_id_of_network_node FROM 
(network_interface_key k JOIN network_node_key r ON ((r.object_id = 
k.object_id_of_network_node)));


ALTER TABLE public.network_interface_joined_key OWNER TO a2824al;

--
-- Name: network_interface_joined; Type: VIEW; Schema: public; Owner: a2824al
--

CREATE VIEW network_interface_joined AS
    SELECT k.object_id, k.object_id_of_network_node, k.in_domain_id, 
k.domain_id, k.in_domain_id_of_network_node, i.valid_from, i.valid_to, 
i.object_version, i.if_highspeed, i.if_mtu, i.if_type, i.if_index, 
i.parent_interface_object_id, i.layer, i.default_ip_interface_address, 
i.default_ip_network_subnet_prefix, i.if_alias, i.if_description, 
i.display_count, i."valid", i.if_speed, i.ospf_link_type FROM 
(network_interface_joined_key k JOIN network_interface i USING (object_id));


ALTER TABLE public.network_interface_joined OWNER TO a2824al;

--
-- Name: network_key_base; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE network_key_base (
    domain_id integer,
    in_domain_id text
);


ALTER TABLE public.network_key_base OWNER TO a2824al;

--
-- Name: COLUMN network_key_base.domain_id; Type: COMMENT; Schema: public; 
Owner: a2824al
--

COMMENT ON COLUMN network_key_base.domain_id IS 'there might be possibly
domain defined in the domain
table for organizational transits
e.g. "geant-<->-gwin"';


--
-- Name: COLUMN network_key_base.in_domain_id; Type: COMMENT; Schema: public; 
Owner: a2824al
--

COMMENT ON COLUMN network_key_base.in_domain_id IS 'external, globally (not 
only in this db) valid (together with domainid)
id';


--
-- Name: network_key; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE network_key (
)
INHERITS (network_key_base, monitoring_object_key);


ALTER TABLE public.network_key OWNER TO a2824al;

SET default_with_oids = true;

--
-- Name: network_node_type; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE network_node_type (
)
INHERITS (hardware_type);


ALTER TABLE public.network_node_type OWNER TO a2824al;

--
-- Name: pga_forms; Type: TABLE; Schema: public; Owner: a2824al; Tablespace: 
--

CREATE TABLE pga_forms (
    formname character varying(64),
    formsource text
);


ALTER TABLE public.pga_forms OWNER TO a2824al;

--
-- Name: pga_layout; Type: TABLE; Schema: public; Owner: a2824al; Tablespace: 
--

CREATE TABLE pga_layout (
    tablename character varying(64),
    nrcols smallint,
    colnames text,
    colwidth text
);


ALTER TABLE public.pga_layout OWNER TO a2824al;

--
-- Name: pga_queries; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE pga_queries (
    queryname character varying(64),
    querytype character(1),
    querycommand text,
    querytables text,
    querylinks text,
    queryresults text,
    querycomments text
);


ALTER TABLE public.pga_queries OWNER TO a2824al;

--
-- Name: pga_reports; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE pga_reports (
    reportname character varying(64),
    reportsource text,
    reportbody text,
    reportprocs text,
    reportoptions text
);


ALTER TABLE public.pga_reports OWNER TO a2824al;

--
-- Name: pga_schema; Type: TABLE; Schema: public; Owner: a2824al; Tablespace: 
--

CREATE TABLE pga_schema (
    schemaname character varying(64),
    schematables text,
    schemalinks text
);


ALTER TABLE public.pga_schema OWNER TO a2824al;

--
-- Name: pga_scripts; Type: TABLE; Schema: public; Owner: a2824al; 
Tablespace: 
--

CREATE TABLE pga_scripts (
    scriptname character varying(64),
    scriptsource text
);


ALTER TABLE public.pga_scripts OWNER TO a2824al;