# $Id: SCHEMA,v 1.2 2002/12/16 09:04:58 jeroen Exp $

# comparison_info
#
# An entry is added every time a comparison between the RIS data and the
# RPSL database is made.  This may be used to determine what data was
# used, and when the comparison was made.
#
CREATE TABLE comparison_info (
    # unique identifier
    # ex: 1, 2, 113
    cmp_id      INT NOT NULL AUTO_INCREMENT,     # unique identifier
    
    # time comparison ran
    # ex: '2000-09-19 12:44:34'
    run_time    DATETIME NOT NULL,               

    # time of RIS dump
    # ex: '2000-09-18 18:02:11'
    ris_time    DATETIME NOT NULL,               

    # type of comparison 
    # ex: 'route_not_advertised', 'route_not_registered'
    cmp_type    ENUM ('route_not_advertised', 'route_not_registered', 'impossible_as_path') NOT NULL,

    # flag whether comparison is complete or not (set to 'false' when
    # a comparison is started, and 'true' when all records related to it
    # have been created)
    # ex: 'false', 'true'
    cmp_done    ENUM ('false', 'true') NOT NULL,

    # keys
    PRIMARY KEY (cmp_id),
    INDEX (cmp_type)
);

# route_not_advertised
#
# When a route is discovered to be missing from the RIS data, an entry is
# added to this table.
#
CREATE TABLE route_not_advertised (
    # prefix and length of the route
    # ex: '192.168.1.0/24' becomes 3232235776, 24
    prefix               INT UNSIGNED NOT NULL,
    prefix_length        TINYINT(2) UNSIGNED NOT NULL,
    # end of the range. Simply prefix_end=(prefix+POW(2,(32-prefix_length)))-1
    # but required for less spec/more spec queries
    prefix_end           INT UNSIGNED NOT NULL,

    # the origin AS of this route (i.e. last hop)
    # ex: 'AS3333', 'AS1713'
    origin               CHAR(7) NOT NULL,

    # foreign key: comparison_info of last time advertised
    # ex: NULL, 17
    cmp_id_advertised    INT,

    # foreign key: comparison_info of last check
    # ex: 5, 17
    cmp_id_when_checked  INT NOT NULL,

    # keys
    PRIMARY KEY (prefix, prefix_length, origin),
    INDEX (origin),
    INDEX (cmp_id_when_checked)
);

# route_not_registered
#
# When a route is in the RIS data but not registered, an entry is added to
# this table.
#
CREATE TABLE route_not_registered (
    # prefix and length of the route
    # ex: '192.168.1.0/24' becomes 3232235776, 24
    prefix               INT UNSIGNED NOT NULL,
    prefix_length        TINYINT(2) UNSIGNED NOT NULL,
    # end of the range. Simply prefix_end=(prefix+POW(2,(32-prefix_length)))-1
    # but required for less spec/more spec queries
    prefix_end           INT UNSIGNED NOT NULL,

    # the origin AS of this route (i.e. last hop)
    # ex: 'AS3333', 'AS1713'
    origin               CHAR(7) NOT NULL,

    # foreign key: comparison_info when discovered
    # ex: 5, 17
    cmp_id_when_checked  INT NOT NULL,

    # keys
    PRIMARY KEY (prefix, prefix_length, origin, cmp_id_when_checked),
    INDEX (origin),
    INDEX (cmp_id_when_checked),
    INDEX (prefix),
    INDEX (prefix_end)
);


# TABLE peers is to record peerings in the RR. Filled in using
# aut-num objects
CREATE TABLE peers (
  # then first peer AS
  peer1 INT(8) unsigned NOT NULL,
  # the second peer AS (the order should not matter)
  peer2 INT(8) unsigned NOT NULL,
  
  # foreign key: comparison info of the last check
  cmp_id_when_checked INT NOT NULL,

  # keys
  PRIMARY KEY(peer1, peer2, cmp_id_when_checked),
  INDEX(peer1, peer2, cmp_id_when_checked)
);

# TABLE impossible_as_paths is to record impossible AS paths
# in RIS data (BGP routing table) according to the policies
# recorded in RR (which are in 'peers' table).
CREATE TABLE impossible_as_paths (
  # prefix & length of the route
  prefix INT unsigned NOT NULL,
  prefix_length TINYINT(2) unsigned NOT NULL,
  # origin of the prefix 
  origin CHAR(7) NOT NULL,
  # the as path
  as_path BLOB NOT NULL,
  # and the offending pair of ASs
  offending_pair_entry1 INT(10) UNSIGNED,
  offending_pair_entry2 INT(10) UNSIGNED,

  # foreign key: comparison info of the last check
  cmp_id_when_checked INT NOT NULL,
  # foreign key: comparison info of the last time advertised
  cmp_id_advertised INT,

  # keys
  INDEX(origin), 
  INDEX(offending_pair_entry1),
  INDEX(offending_pair_entry2), 
  INDEX(cmp_id_when_checked)
);