PGCONTRIB=/local/apps/postgresql/share/contrib

psql -U postgres <<EOSQL
CREATE ROLE iplog LOGIN PASSWORD 'wreckless';
CREATE DATABASE iplog WITH OWNER iplog;
EOSQL

psql -U postgres iplog < $PGCONTRIB/y_octet_t.sql
psql -U postgres iplog < $PGCONTRIB/y_uuid.sql

export PGPASSWORD=wreckless
psql -U iplog -d iplog -h localhost <<EOSQL
CREATE TABLE iplog (
  logged
    TIMESTAMP WITH TIME ZONE
    NOT NULL
    DEFAULT CURRENT_TIMESTAMP,
  source_ip
    CIDR
    NOT NULL,
  destination_ip
    CIDR
    NOT NULL,
  ip_protocol
    VARCHAR(4)
    NOT NULL,
  destination_port
    INTEGER
    NOT NULL,
  timespan
    INTERVAL
    NOT NULL,
  packet_count
    INTEGER
    NOT NULL,
  ethernet_caplen
    INTEGER
    NOT NULL,
  ethernet_len
    INTEGER
    NOT NULL,
  ip_total_size
    INTEGER
    NOT NULL,
  ip_data_size
    INTEGER
    NOT NULL,
  payload_size
    INTEGER,
  id
    y_octet_16
    NOT NULL
    DEFAULT encode( y_uuid_generate_random(), 'hex' )::y_octet_16
    PRIMARY KEY
);

CREATE UNIQUE INDEX
  iplog_key_idx
ON
  iplog( logged, source_ip, destination_ip, ip_protocol, destination_port );

CREATE INDEX
  iplog_count_idx
ON
  iplog( packet_count );

CREATE INDEX
  iplog_payload_idx
ON
  iplog( payload_size );
EOSQL

CREATE INDEX
  iplog_ip_total_size_idx
ON
  iplog( ip_total_size );
EOSQL