-- Не осуждайте за использование зарезервированных слов в именах доменов :)
CREATE DOMAIN SMALL_ID AS SMALLINT;
CREATE DOMAIN UNITAG AS VARCHAR(31) CHARACTER SET UTF8;
CREATE DOMAIN NAME AS VARCHAR(200) CHARACTER SET UTF8;

/*
 * --= Справочник размерностей =--
 * "id"       - Идентификатор размерности;
 * "main_id"  - Внешний ключ к идентификатору базовой размерности
 *              мастер-таблицы;
 * "base"     - Множитель базовой размерности;
 * "power"    - Степень множителя базовой размерности;
 * "mark"     - Метка размерности;
 * "name"     - Наименование размерности;
 */
CREATE TABLE "dims" (
    "id"      SMALL_ID NOT NULL,
    "main_id" SMALL_ID DEFAULT 0,
    "base"    INTEGER DEFAULT 10 NOT NULL,
    "power"   SMALLINT DEFAULT 0 NOT NULL,
    "mark"    UNITAG NOT NULL,
    "name"    NAME NOT NULL,
  CONSTRAINT "dims__pk" PRIMARY KEY ("id"),
  CONSTRAINT "dims__fk__self" FOREIGN KEY ("main_id")
    REFERENCES "dims" ("id") ON UPDATE CASCADE ON DELETE SET DEFAULT,
  CONSTRAINT "dims__uq" UNIQUE ("main_id", "base", "power", "mark")
);
COMMENT ON TABLE  "dims" IS 'Справочник размерностей величин';
COMMENT ON COLUMN "dims"."id" IS 'Идентификатор размерности';
COMMENT ON COLUMN "dims"."main_id"
  IS 'Ключ мастер-таблицы идентификатора базовой размерности';
COMMENT ON COLUMN "dims"."base"
  IS 'Множитель базовой размерности';
COMMENT ON COLUMN "dims"."power" IS 'Степень множителя базовой размерности';
COMMENT ON COLUMN "dims"."mark" IS 'Метка размерности';
COMMENT ON COLUMN "dims"."name" IS 'Наименование размерности';

REVOKE ALL ON TABLE "dims" FROM PUBLIC, ROLE USERS;
GRANT SELECT, REFERENCES("id") ON TABLE "dims" TO ROLE USERS;


CREATE GENERATOR "dims__GEN";
COMMIT WORK;


/*
 * Триггер получения идентификатора размерности величины
 * для новой записи
 */
SET TERM ^;
CREATE OR ALTER TRIGGER "dims__TR_BI"
  FOR "dims"
  ACTIVE
  BEFORE INSERT
  POSITION 0
AS
BEGIN
  IF (NOT(NEW."id" IS NOT NULL
      AND NEW."id" <= GEN_ID("dims__GEN", 0))) THEN
    NEW."id" = GEN_ID("dims__GEN", 1);
END^
SET TERM ;^
COMMIT WORK;

/*
SET TERM ^;
CREATE OR ALTER PROCEDURE oneRow
  RETURNS ("tmstmp" TIMESTAMP)
AS
BEGIN
  "tmstmp" = CURRENT_TIMESTAMP;
  SUSPEND;
END^
SET TERM ;^
GRANT EXECUTE ON PROCEDURE oneRow TO PUBLIC;
COMMIT WORK;
*/


DELETE FROM "dims";
SET GENERATOR "dims__GEN" TO 56;
INSERT INTO "dims"
    ("id", "main_id", "base", "power", "mark", "name")
        SELECT  0, NULL,   0,  0, '',    '' FROM oneRow
  UNION SELECT  1,    0,  10,  0, 'В',   'Вольт' FROM oneRow
  UNION SELECT  2,    1,  10,  3, 'кВ',  'Киловольт' FROM oneRow
  UNION SELECT  3,    1,  10,  6, 'МВ',  'Мегавольт' FROM oneRow
  UNION SELECT  4,    0,  10,  0, 'Ом',  'Ом' FROM oneRow
  UNION SELECT  5,    4,  10,  3, 'кОм', 'Килоом' FROM oneRow
  UNION SELECT  6,    0,  10,  0, 'А',   'Ампер' FROM oneRow
  UNION SELECT  7,    6,  10,  3, 'кА',  'Килоампер' FROM oneRow
  UNION SELECT  8,    0,  10,  0, 'Вт',  'Ватт' FROM oneRow
  UNION SELECT  9,    8,  10,  3, 'кВт', 'Киловатт' FROM oneRow
  UNION SELECT 10,    8,  10,  6, 'МВт', 'Мегаватт' FROM oneRow
  UNION SELECT 11,    8,  10,  9, 'ГВт', 'Гигаватт' FROM oneRow
  UNION SELECT 12,    0,  10,  0, 'Дж',  'Джоуль' FROM oneRow
  UNION SELECT 13,   12,  10,  3, 'кДж', 'Килоджоуль' FROM oneRow
  UNION SELECT 14,   12,  10,  6, 'МДж', 'Мегаджоуль' FROM oneRow
  UNION SELECT 15,    0,  10,  0, 'м',   'Метр' FROM oneRow
  UNION SELECT 16,   15,  10, -3, 'мм',  'Миллиметр' FROM oneRow
  UNION SELECT 17,   15,  10, -6, 'мкм', 'Микрометр' FROM oneRow
  UNION SELECT 18,   15,  10,  3, 'км',  'Километр' FROM oneRow
  UNION SELECT 19,    0,  10, -3, 'г',   'Грамм' FROM oneRow
  UNION SELECT 20,   19,  10, -6, 'мг',  'Миллиграмм' FROM oneRow
  UNION SELECT 21,   19,  10,  0, 'кг',  'Килограмм' FROM oneRow
  UNION SELECT 22,   19,  10,  3, 'т',   'Тонна' FROM oneRow
  UNION SELECT 23,    0,  60,  0, 'с',   'Секунда' FROM oneRow
  UNION SELECT 24,   23,  60,  1, 'мин', 'Минута' FROM oneRow
  UNION SELECT 25,   23,  60,  2, 'ч',   'Час' FROM oneRow
  UNION SELECT 26,   23, 86400,1, 'сут', 'Сутки' FROM oneRow
  UNION SELECT 27,   23,  10, -3, 'мс',  'Миллисекунда' FROM oneRow
  UNION SELECT 28,   23,  10, -6, 'мкс', 'Микросекунда' FROM oneRow
  UNION SELECT 29,    0,  10,  0, 'Гц',  'Герц' FROM oneRow
  UNION SELECT 30,   29,  10,  3, 'кГц', 'Килогерц' FROM oneRow
  UNION SELECT 31,   29,  10,  6, 'МГц', 'Мегагерц' FROM oneRow
  UNION SELECT 32,   29,  10,  9, 'ГГц', 'Гигагерц' FROM oneRow
  UNION SELECT 33,    0,   1,  0, '°C',  'Градус Цельсия' FROM oneRow
  UNION SELECT 34,    0,   1,  0, '°',   'Градус' FROM oneRow
  UNION SELECT 35,   34,  60, -1, '''',  'Угловая минута' FROM oneRow
  UNION SELECT 36,   34,  60, -2, '"',   'Угловая секунда' FROM oneRow
  UNION SELECT 37,    0,  10,  0, '%',   'Процент' FROM oneRow
  UNION SELECT 38,    0,  10,  0, 'Вар',  'Вар' FROM oneRow
  UNION SELECT 39,   38,  10,  3, 'кВар', 'Киловар' FROM oneRow
  UNION SELECT 40,   38,  10,  6, 'МВар', 'Мегавар' FROM oneRow
  UNION SELECT 41,   38,  10, -3, 'мВар', 'Милливар' FROM oneRow
  UNION SELECT 42,    0,  10,  0, 'ВА',   'Вольт-Ампер' FROM oneRow
  UNION SELECT 43,   42,  10,  3, 'кВА',  'Киловольт-Ампер' FROM oneRow
  UNION SELECT 44,   42,  10,  6, 'МВА',  'Мегавольт-Ампер' FROM oneRow
  UNION SELECT 45,   42,  10, -3, 'мВА',  'Милливольт-Ампер' FROM oneRow
  UNION SELECT 46,    0,  10,  0, 'раз',      'Раз' FROM oneRow
  UNION SELECT 47,   46,  10,  3, 'тыс.раз',  'Тысяч раз' FROM oneRow
  UNION SELECT 48,   46,  10,  6, 'млн.раз',  'Миллион раз' FROM oneRow
  UNION SELECT 49,   46,  10,  9, 'млрд.раз', 'Миллиард раз' FROM oneRow
  UNION SELECT 50,    0,  10,  0, 'шт.',      'Штук' FROM oneRow
  UNION SELECT 51,   50,  10,  3, 'тыс.шт.',  'Тысяч штук' FROM oneRow
  UNION SELECT 52,   50,  10,  6, 'млн.шт.',  'Миллион штук' FROM oneRow
  UNION SELECT 53,    0,  10,  0, 'бит/с',    'Бит в секунду' FROM oneRow
  UNION SELECT 54,   53,  10,  3, 'кбит/с',   'Килобит в секунду' FROM oneRow
  UNION SELECT 55,   53,  10,  6, 'Мбит/с',   'Мегабит в секунду' FROM oneRow
  UNION SELECT 56,    0,  10,  0, 'м/с',      'Метр в секунду' FROM oneRow;
COMMIT WORK;



/*
 * Представление долей размерностей по базовой размерности
 *
 * для отображения выбора долей величин измерений
 * и упрощения преобразований долей размерностей
 */
CREATE OR ALTER VIEW "dims__quotList"
AS
WITH RECURSIVE dims AS
(
  SELECT "main_id", "id", "base", "power",
      "mark" AS "main_mark", "mark", "name"
    FROM "dims"
    WHERE "main_id" = 0
  UNION ALL
  SELECT b."main_id", b."id", b."base", b."power",
      a."main_mark", b."mark", b."name"
    FROM "dims" b
      INNER JOIN dims a ON b."main_id" = a."id"
)
SELECT "main_id", "id", "base", "power", "main_mark", "mark", "name"
  FROM dims;


/*
 * Возвращает список долей величин по базовой размерности, отсортированный
 * по имени базовой размерности, базовому коэффициенту и его степени
 * относительно базовой размерности
 */
SET TERM ^;
CREATE OR ALTER PROCEDURE "dims__getQuotList"
  RETURNS
  (
    "id"      SMALL_ID,
    "name"    NAME
  )
AS
BEGIN
  FOR
      SELECT "id", "name"
        FROM "dims__quotList"
        ORDER BY "main_mark", "base", "power"
        INTO: "id", "name"
    DO
      SUSPEND;
END^
SET TERM ;^

GRANT SELECT ON "dims__quotList" TO PROCEDURE "dims__getList";
GRANT SELECT, REFERENCES ("id") ON TABLE "dims"
  TO PROCEDURE "dims__quotList";
COMMIT WORK;
/* ----------------------------------------------------------------- */