schema.sql

DROP DATABASE IF EXISTS hdbpp_test;

-- Create the hdb database and use it
CREATE DATABASE hdbpp_test;
\c hdbpp_test

-- Add the timescaledb extension (Important)
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

-------------------------------------------------------------------------------
CREATE DOMAIN uchar AS numeric(3) -- ALT smallint
    CHECK(VALUE >= 0 AND VALUE <= 255);

CREATE DOMAIN ushort AS numeric(5)  -- ALT integer
    CHECK(VALUE >= 0 AND VALUE <= 65535);

CREATE DOMAIN ulong AS numeric(10) -- ALT bigint
    CHECK(VALUE >= 0 AND VALUE <= 4294967295);

CREATE DOMAIN ulong64 AS numeric(20)
    CHECK(VALUE >= 0 AND VALUE <= 18446744073709551615);

-------------------------------------------------------------------------------
DROP TABLE IF EXISTS att_conf_type;

-- Mappings for ths Tango Data Type (used in att_conf)
CREATE TABLE att_conf_type (
    att_conf_type_id serial NOT NULL,
    type text NOT NULL,
    type_num smallint NOT NULL,
    PRIMARY KEY (att_conf_type_id)
);

COMMENT ON TABLE att_conf_type is 'Attribute data type';

INSERT INTO att_conf_type (type, type_num) VALUES
('DEV_BOOLEAN', 1),('DEV_SHORT', 2),('DEV_LONG', 3),('DEV_FLOAT', 4),
('DEV_DOUBLE', 5),('DEV_USHORT', 6),('DEV_ULONG', 7),('DEV_STRING', 8),
('DEV_STATE', 19),('DEV_UCHAR',22),('DEV_LONG64', 23),('DEV_ULONG64', 24),
('DEV_ENCODED', 28),('DEV_ENUM', 29);

DROP TABLE IF EXISTS att_conf_format;

-- Mappings for ths Tango Data Format Type (used in att_conf)
CREATE TABLE att_conf_format (
    att_conf_format_id serial NOT NULL,
    format text NOT NULL,
    format_num smallint NOT NULL,
    PRIMARY KEY (att_conf_format_id)
);

COMMENT ON TABLE att_conf_format is 'Attribute format type';

INSERT INTO att_conf_format (format, format_num) VALUES
('SCALAR', 0),('SPECTRUM', 1),('IMAGE', 2);

DROP TABLE IF EXISTS att_conf_write;

-- Mappings for the Tango Data Write Type (used in att_conf)
CREATE TABLE att_conf_write (
    att_conf_write_id serial NOT NULL,
    write text NOT NULL,
    write_num smallint NOT NULL,
    PRIMARY KEY (att_conf_write_id)
);

COMMENT ON TABLE att_conf_write is 'Attribute write type';

INSERT INTO att_conf_write (write, write_num) VALUES
('READ', 0),('READ_WITH_WRITE', 1),('WRITE', 2),('READ_WRITE', 3);