VROOM schema for PostgreSQL

vroom_db.sql

--
-- Setup some tables for VROOM
--
CREATE EXTENSION IF NOT EXISTS postgis;

DO $$ BEGIN
  CREATE TYPE step_types AS ENUM ('start', 'job', 'pickup', 'delivery', 'break', 'end');
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
  CREATE TYPE vehicle_types AS ENUM ('car', 'bicycle', 'foot');
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
  CREATE TYPE time_window_type AS (
    open TIMESTAMP,
    close TIMESTAMP
  );
EXCEPTION
  WHEN duplicate_object THEN null;
END $$;

CREATE TABLE IF NOT EXISTS locations (
  id BIGSERIAL PRIMARY KEY,
  description TEXT,
  geom geometry('PointZ',4326)
);

CREATE TABLE IF NOT EXISTS jobs (
  id BIGSERIAL PRIMARY KEY,
  description TEXT,
  location_id BIGINT NOT NULL,
  service INTEGER DEFAULT 0,
  pickup INTEGER[],
  delivery INTEGER[],
  skills INTEGER[],
  priority INTEGER DEFAULT 0, 
  time_windows TIME_WINDOW_TYPE[],
  CHECK (priority BETWEEN 1 AND 100),
  FOREIGN KEY(location_id) REFERENCES locations(id)
);

CREATE TABLE IF NOT EXISTS shipment_steps (
  id BIGSERIAL PRIMARY KEY,
  description TEXT,
  location_id BIGINT NOT NULL,
  service INTEGER DEFAULT 0, 
  time_windows TIME_WINDOW_TYPE[],
  FOREIGN KEY(location_id) REFERENCES locations(id)
);

CREATE TABLE IF NOT EXISTS shipments (
  id BIGSERIAL PRIMARY KEY,
  pickup BIGINT NOT NULL,
  delivery BIGINT NOT NULL,
  amount INTEGER[],
  skills INTEGER[],
  priority INTEGER DEFAULT 0,
  CHECK (priority BETWEEN 1 AND 100),
  FOREIGN KEY(pickup) REFERENCES shipment_steps(id),
  FOREIGN KEY(delivery) REFERENCES shipment_steps(id)
);

CREATE TABLE IF NOT EXISTS vehicles (
  id BIGSERIAL PRIMARY KEY,
  profile vehicle_types DEFAULT 'car',
  description TEXT,
  start_location_id BIGINT NOT NULL,
  final_location_id BIGINT NOT NULL,
  capacity INTEGER[],
  skills INTEGER[],
  time_window TIME_WINDOW_TYPE,
  speed_factor FLOAT DEFAULT 1.0,
  FOREIGN KEY(start_location_id) REFERENCES locations(id),
  FOREIGN KEY(final_location_id) REFERENCES locations(id)
);

CREATE TABLE IF NOT EXISTS breaks (
  id BIGSERIAL PRIMARY KEY,
  vehicle_id BIGINT NOT NULL,
  service INTEGER DEFAULT 0,
  description TEXT, 
  time_windows TIME_WINDOW_TYPE[],
  FOREIGN KEY(vehicle_id) REFERENCES vehicles(id)
);

CREATE TABLE IF NOT EXISTS vehicle_steps (
  id BIGSERIAL PRIMARY KEY,
  vehicle_id BIGINT NOT NULL,
  step_type STEP_TYPES,
  step_position INTEGER,
  service_at TIMESTAMP,
  service_after TIMESTAMP,
  service_before TIMESTAMP,
  FOREIGN KEY(vehicle_id) REFERENCES vehicles(id)
);

-- To store a traveltime 'distance) matrix
CREATE TABLE IF NOT EXISTS traveltimes (
  start_id BIGINT NOT NULL,
  final_id BIGINT NOT NULL,
  agg_cost FLOAT NOT NULL,
  agg_distance FLOAT NOT NULL,
  FOREIGN KEY(start_id) REFERENCES locations(id),
  FOREIGN KEY(final_id) REFERENCES locations(id),
  PRIMARY KEY (start_id, final_id)
);