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