1-- STEP 2: Create Schema
2-- sudo -u postgres psql pingtraceroutedb <schema.sql
3--
4-- =================================================================
5--          #     #                 #     #
6--          ##    #   ####   #####  ##    #  ######   #####
7--          # #   #  #    #  #    # # #   #  #          #
8--          #  #  #  #    #  #    # #  #  #  #####      #
9--          #   # #  #    #  #####  #   # #  #          #
10--          #    ##  #    #  #   #  #    ##  #          #
11--          #     #   ####   #    # #     #  ######     #
12--
13--       ---   The NorNet Testbed for Multi-Homed Systems  ---
14--                       https://www.nntb.no
15-- =================================================================
16--
17-- High-Performance Connectivity Tracer (HiPerConTracer)
18-- Copyright (C) 2015-2020 by Thomas Dreibholz
19--
20-- This program is free software: you can redistribute it and/or modify
21-- it under the terms of the GNU General Public License as published by
22-- the Free Software Foundation, either version 3 of the License, or
23-- (at your option) any later version.
24--
25-- This program is distributed in the hope that it will be useful,
26-- but WITHOUT ANY WARRANTY; without even the implied warranty of
27-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
28-- GNU General Public License for more details.
29--
30-- You should have received a copy of the GNU General Public License
31-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
32--
33-- Contact: dreibh@simula.no
34
35
36-- ###### NorNet Information ################################################
37DROP TABLE IF EXISTS SiteInfo;
38CREATE TABLE SiteInfo (
39   SiteIndex    SMALLINT NOT NULL,                   -- NorNet Site Index
40   TimeStamp    TIMESTAMP WITHOUT TIME ZONE NOT NULL,-- Time stamp for information
41
42   -- ------ Name -----------------------------------------------------------
43   FullName      CHAR(64),                           -- Full name
44   ShortName     CHAR(8),                            -- Short name, e.g. SRL
45
46   -- ------ Location -------------------------------------------------------
47   Latitude      FLOAT,                              -- Latitude
48   Longitude     FLOAT,                              -- Longitude
49   CountryCode   CHAR(2),                            -- Country code, e.g. DE
50   Country       VARCHAR(30),                        -- Country name
51   Region        VARCHAR(30),                        -- Region name
52   City          VARCHAR(30),                        -- City name
53
54   PRIMARY KEY (SiteIndex)
55);
56
57DROP TABLE IF EXISTS ProviderInfo;
58CREATE TABLE ProviderInfo (
59   ProviderIndex SMALLINT NOT NULL,                   -- NorNet Provider Index
60   TimeStamp     TIMESTAMP WITHOUT TIME ZONE NOT NULL,-- Time stamp for information
61
62   -- ------ Name -----------------------------------------------------------
63   FullName      CHAR(64),                           -- Full name
64   ShortName     CHAR(16),                           -- Short name, e.g. BKK
65
66   PRIMARY KEY (ProviderIndex)
67);
68
69
70-- ###### Ping ##############################################################
71DROP TABLE IF EXISTS Ping;
72CREATE TABLE Ping (
73   TimeStamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,   -- Time stamp (always UTC!)
74   FromIP    INET     NOT NULL,                      -- Source IP address
75   ToIP      INET     NOT NULL,                      -- Destination IP address
76   PktSize   INTEGER  NOT NULL DEFAULT 0,            -- Packet size
77   TC        SMALLINT NOT NULL DEFAULT 0,            -- Traffic Class
78   Status    SMALLINT NOT NULL,                      -- Status
79   RTT       INTEGER  NOT NULL,                      -- microseconds (max. 2147s)
80   PRIMARY KEY (FromIP, ToIP, TC, TimeStamp)
81);
82
83CREATE INDEX PingTimeStampIndex ON Ping (TimeStamp ASC);
84-- CREATE INDEX PingFromIPIndex ON Ping (FromIP ASC);
85-- CREATE INDEX PingToIPIndex ON Ping (ToIP ASC);
86-- CREATE INDEX PingStatusIndex ON Ping (Status ASC);
87
88
89-- ###### Traceroute ########################################################
90DROP TABLE IF EXISTS Traceroute;
91CREATE TABLE Traceroute (
92   TimeStamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,   -- Time stamp (always UTC!)
93   FromIP    INET     NOT NULL,                      -- Source IP address
94   ToIP      INET     NOT NULL,                      -- Destination IP address
95   PktSize   INTEGER  NOT NULL DEFAULT 0,            -- Packet size
96   TC        SMALLINT NOT NULL DEFAULT 0,            -- Traffic Class
97   HopNumber SMALLINT NOT NULL,                      -- Current hop number
98   TotalHops SMALLINT NOT NULL,                      -- Total number of hops
99   Status    SMALLINT NOT NULL,                      -- Status
100   RTT       INTEGER  NOT NULL,                      -- microseconds (max. 2147s)
101   HopIP     INET     NOT NULL,                      -- Router or Destination IP address
102   PathHash  BIGINT   NOT NULL,                      -- Hash over full path
103   Round     INTEGER  NOT NULL DEFAULT 0,            -- Round number
104   PRIMARY KEY (FromIP,ToIP,TC,TimeStamp,Round,HopNumber)
105);
106
107CREATE INDEX TracerouteTimeStampIndex ON Traceroute (TimeStamp ASC);
108-- CREATE INDEX TraceroutePathHashIndex ON Traceroute (PathHash ASC);
109-- CREATE INDEX TracerouteFromIPIndex ON Traceroute (FromIP ASC);
110-- CREATE INDEX TracerouteToIPIndex ON Traceroute (ToIP ASC);
111-- CREATE INDEX TracerouteHopIPIndex ON Traceroute (HopIP ASC);
112
113
114-- ###### Address Information ###############################################
115DROP TABLE IF EXISTS AddressInfo;
116CREATE TABLE AddressInfo (
117   IP           INET NOT NULL,                       -- IP address
118   TimeStamp    TIMESTAMP WITHOUT TIME ZONE NOT NULL,-- Time stamp for information
119
120   -- ------ NorNet ---------------------------------------------------------
121   SiteIndex     SMALLINT REFERENCES SiteInfo(SiteIndex),         -- NorNet Site ID
122   ProviderIndex SMALLINT REFERENCES ProviderInfo(ProviderIndex), -- NorNet Provider ID
123
124   -- ----- Autonomous System -----------------------------------------------
125   ASNumber      INTEGER,                            -- Autonomous System number
126
127   -- ------ GeoIP ----------------------------------------------------------
128   Latitude      FLOAT,                              -- Latitude
129   Longitude     FLOAT,                              -- Longitude
130   CountryCode   CHAR(2),                            -- Country code, e.g. DE
131   PostalCode    CHAR(8),                            -- Postal code, e.g. 45326
132   Country       VARCHAR(30),                        -- Country name
133   Region        VARCHAR(30),                        -- Region name
134   City          VARCHAR(48),                        -- City name
135   Organisation  VARCHAR(80),                        -- Organisation name
136
137   -- ------ DNS ------------------------------------------------------------
138   FQDN         VARCHAR(253),                        -- Fully-qualified domain name
139   PRIMARY KEY (IP)
140);
141
142CREATE INDEX AddressInfoASNumberIndex ON AddressInfo (ASNumber ASC NULLS LAST);
143