Defining database tables for spans, poles, and devices between poles

-- ------------------------------------------
-- ------------ Table devicestypes
-- ------------------------------------------
DROP SEQUENCE devicestypes_id_seq;

CREATE TABLE devicestypes (
devicestypes_id SERIAL NOT NULL PRIMARY KEY,
devicestypes_name TEXT,
devicestypes_description TEXT
);
GRANT ALL ON devicestypes TO PUBLIC;
DROP VIEW devicestypes_combo;
CREATE OR REPLACE VIEW devicestypes_combo AS
SELECT devicestypes_id AS id,
devicestypes_name AS TEXT
FROM devicestypes;
GRANT SELECT ON devicestypes_combo TO PUBLIC;

COMMENT ON TABLE devicestypes IS 'Device Types';
COMMENT ON COLUMN devicestypes.devicestypes_id IS 'ID';
COMMENT ON COLUMN devicestypes.devicestypes_name IS 'Name';
COMMENT ON COLUMN devicestypes.devicestypes_description IS 'Description';

CREATE UNIQUE INDEX devicestypes_index ON devicestypes ( devicestypes_name );

-- ------------------------------------------
-- ------------ Table devices
-- ------------------------------------------
DROP SEQUENCE devices_id_seq;

CREATE TABLE devices (
devices_id SERIAL NOT NULL PRIMARY KEY,
devices_datecreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
devices_datemodified TIMESTAMP,
devices_usercreated TEXT NOT NULL DEFAULT current_user,
devices_usermodified TEXT NOT NULL DEFAULT current_user,
devices_devicestypes INT4 REFERENCES devicestypes NOT NULL,
devices_name TEXT NOT NULL,
devices_description TEXT
);
GRANT ALL ON devices TO PUBLIC;
DROP VIEW devices_combo;
CREATE OR REPLACE VIEW devices_combo AS
SELECT devices_id AS id,
devices_name AS TEXT
FROM devices;
GRANT SELECT ON devices_combo TO PUBLIC;

-- ------------------------------------------
-- ------------ Table poles
-- ------------------------------------------
DROP SEQUENCE poles_id_seq;

CREATE TABLE poles (
poles_id SERIAL NOT NULL PRIMARY KEY,
poles_datecreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
poles_datemodified TIMESTAMP,
poles_usercreated TEXT NOT NULL DEFAULT current_user,
poles_usermodified TEXT NOT NULL DEFAULT current_user,
poles_name TEXT,
poles_description TEXT
);
GRANT ALL ON poles TO PUBLIC;
DROP VIEW poles_combo;
CREATE OR REPLACE VIEW poles_combo AS
SELECT poles_id AS id,
poles_name AS TEXT
FROM poles;
GRANT SELECT ON poles_combo TO PUBLIC;

COMMENT ON TABLE poles IS 'Poles';
COMMENT ON COLUMN poles.poles_id IS 'ID';
COMMENT ON COLUMN poles.poles_datecreated IS 'Date created';
COMMENT ON COLUMN poles.poles_datemodified IS 'Date modified';
COMMENT ON COLUMN poles.poles_usercreated IS 'User created';
COMMENT ON COLUMN poles.poles_usermodified IS 'User modified';
COMMENT ON COLUMN poles.poles_name IS 'Name';
COMMENT ON COLUMN poles.poles_description IS 'Description';

-- ------------------------------------------
-- ------------ Table poles
-- ------------------------------------------
DROP SEQUENCE poles_id_seq;

CREATE TABLE poles (
poles_id SERIAL NOT NULL PRIMARY KEY,
poles_datecreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
poles_datemodified TIMESTAMP,
poles_usercreated TEXT NOT NULL DEFAULT current_user,
poles_usermodified TEXT NOT NULL DEFAULT current_user,
poles_name TEXT,
poles_description TEXT
);
GRANT ALL ON poles TO PUBLIC;
DROP VIEW poles_combo;
CREATE OR REPLACE VIEW poles_combo AS
SELECT poles_id AS id,
poles_name AS TEXT
FROM poles;
GRANT SELECT ON poles_combo TO PUBLIC;

-- ------------------------------------------
-- ------------ Table spans
-- ------------------------------------------
DROP SEQUENCE spans_id_seq;

CREATE TABLE spans (
spans_id SERIAL NOT NULL PRIMARY KEY,
spans_datecreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
spans_datemodified TIMESTAMP,
spans_usercreated TEXT NOT NULL DEFAULT current_user,
spans_usermodified TEXT NOT NULL DEFAULT current_user,
spans_name TEXT,
spans_pole1 INT4 REFERENCES poles NOT NULL,
spans_pole2 INT4 REFERENCES poles NOT NULL,
spans_description TEXT
);
GRANT ALL ON spans TO PUBLIC;
DROP VIEW spans_combo;
CREATE OR REPLACE VIEW spans_combo AS
SELECT spans_id AS id,
spans_name AS TEXT
FROM spans;
GRANT SELECT ON spans_combo TO PUBLIC;

COMMENT ON TABLE spans IS 'Spans';
COMMENT ON COLUMN spans.spans_id IS 'ID';
COMMENT ON COLUMN spans.spans_datecreated IS 'Date created';
COMMENT ON COLUMN spans.spans_datemodified IS 'Date modified';
COMMENT ON COLUMN spans.spans_usercreated IS 'User created';
COMMENT ON COLUMN spans.spans_usermodified IS 'User modified';
COMMENT ON COLUMN spans.spans_name IS 'Name';
COMMENT ON COLUMN spans.spans_description IS 'Description';

CREATE UNIQUE INDEX spans_index ON spans ( spans_pole1, spans_pole2 );

-- ------------------------------------------
-- ------------ Table spandevices
-- ------------------------------------------
DROP SEQUENCE spandevices_id_seq;

CREATE TABLE spandevices (
spandevices_id SERIAL NOT NULL PRIMARY KEY,
spandevices_datecreated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
spandevices_datemodified TIMESTAMP,
spandevices_usercreated TEXT NOT NULL DEFAULT current_user,
spandevices_usermodified TEXT NOT NULL DEFAULT current_user,
spandevices_name TEXT NOT NULL,
spandevices_devices INT4 REFERENCES devices NOT NULL,
spandevices_spans INT4 REFERENCES spans NOT NULL,
spandevices_description TEXT
);
GRANT ALL ON spandevices TO PUBLIC;
DROP VIEW spandevices_combo;
CREATE OR REPLACE VIEW spandevices_combo AS
SELECT spandevices_id AS id,
spandevices_name AS TEXT
FROM spandevices;
GRANT SELECT ON spandevices_combo TO PUBLIC;

COMMENT ON TABLE spandevices IS 'Span Devices';
COMMENT ON COLUMN spandevices.spandevices_id IS 'ID';
COMMENT ON COLUMN spandevices.spandevices_datecreated IS 'Date created';
COMMENT ON COLUMN spandevices.spandevices_datemodified IS 'Date modified';
COMMENT ON COLUMN spandevices.spandevices_usercreated IS 'User created';
COMMENT ON COLUMN spandevices.spandevices_usermodified IS 'User modified';
COMMENT ON COLUMN spandevices.spandevices_name IS 'Name';
COMMENT ON COLUMN spandevices.spandevices_description IS 'Description';