/*
 * Типы объектов
 */
CREATE TABLE "obj_types"
(
    "id"   ID NOT NULL,
    "name" NAME NOT NULL,
  CONSTRAINT "obj_types__pk" PRIMARY KEY ("id"),
  CONSTRAINT "obj_types__uq" UNIQUE ("name")
);

/*
 * Атрибуты типа объекта
 */
CREATE TABLE "type_attrs"
(
    "type_id" ID NOT NULL
    "id"      ID NOT NULL,
    "name"    NAME NOT NULL,
  CONSTRAINT "type_attrs__pk" PRIMARY KEY ("type_id", "id"),
  CONSTRAINT "type_attrs__uq" UNIQUE ("type_id", "name"),
  CONSTRAINT "type_attrs__fk__obj_types" FOREIGN KEY ("type_id")
    REFERENCES "obj_types" ("id") ON UPDATE CASCADE ON DELETE CASCADE
);

/*
 * Объекты
 */
CREATE TABLE "obj"
(
    "type_id" ID NOT NULL,
    "id"      ID NOT NULL,
    "name"    ID NOT NULL,
  CONSTRAINT "obj__pk" PRIMARY KEY ("type_id", "id"),
  CONSTRAINT "obj__uq" UNIQUE ("type_id", "name"),
  CONSTRAINT "obj__fk__obj_types" FOREIGN KEY ("type_id")
    REFERENCES "obj_types" ("id") ON UPDATE CASCADE ON DELETE CASCADE
);

/*
 * Значения атрибутов объектов
 */
CREATE TABLE "obj_value"
(
    "type_id" ID NOT NULL,
    "attr_id" ID NOT NULL,
    "obj_id"  ID NOT NULL,
    "id"      ID NOT NULL,
    "value"   DOUBLE PRECISION DEFAULT NULL,
  CONSTRAINT "obj_value__pk" PRIMARY KEY ("id"),
  CONSTRAINT "obj_value__fk__type_attrs" FOREIGN KEY ("type_id", "attr_id")
    REFERENCES "type_attrs" ("type_id", "id") ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT "obj_value__fk__obj" FOREIGN KEY ("type_id", "obj_id")
    REFERENCES "obj" ("type_id", "id") ON UPDATE CASCADE ON DELETE CASCADE
);

/*
 * Типы объектов
 */
CREATE TABLE "obj_types"
(
    "id"   ID NOT NULL,
    "name" NAME NOT NULL,
  CONSTRAINT "obj_types__pk" PRIMARY KEY ("id"),
  CONSTRAINT "obj_types__uq" UNIQUE ("name")
);

/*
 * Атрибуты типа объекта
 */
CREATE TABLE "type_attrs"
(
    "type_id" ID NOT NULL
    "id"      ID NOT NULL,
    "name"    NAME NOT NULL,
  CONSTRAINT "type_attrs__pk" PRIMARY KEY ("id"),
  CONSTRAINT "type_attrs__uq" UNIQUE ("type_id", "name"),
  CONSTRAINT "type_attrs__fk__obj_types" FOREIGN KEY ("type_id")
    REFERENCES "obj_types" ("id") ON UPDATE CASCADE ON DELETE CASCADE
);

REVOKE ALL ON TABLE "type_attrs" FROM ROLE USERS;

CREATE GENERATOR "type_attrs__GEN";

SET TERM ^;
CREATE OR ALTER TRIGGER "type_attrs__TR_BIU"
  FOR "type_attrs"
  ACTIVE
  BEFORE INSERT OR UPDATE
  POSITION 0
AS
BEGIN
  IF (NOT(NEW."id" IS NOT NULL
          AND NEW."id" <= GEN_ID("type_attrs__GEN", 0)
      ) THEN
    NEW."id" = GEN_ID("type_attrs__GEN", 1);
END^
SET TERM ;^


CREATE OR ALTER EXCEPTION "attr_value__xRowExists"
  'Существует ссылка внешнего ключа';

SET TERM ^;
CREATE OR ALTER PROCEDURE "type_attrs__set"
  (
    "oldId"   ID DEFAULT NULL,
    "typeId"  ID DEFAULT NULL,
    "name"    NAME DEFAULT NULL
  )
  RETURNS
  (
    "id" ID,
    "x"  BOOL
  )
AS
  DECLARE VARIABLE typeId ID DEFAULT NULL;
BEGIN
  IF (:"oldId" IS NULL) THEN
    BEGIN
      IF (:"typeId" IS NOT NULL AND :"name" IS NOT NULL) THEN
        INSERT INTO "type_attrs" ("type_id", "name")
          VALUES (:"typeId", :"name")
          RETURNING "id" INTO: "id";
    END
  ELSE
    IF (:"typeId" IS NOT NULL AND :"name" IS NOT NULL) THEN
      BEGIN
        SELECT "type_id", "id"
          FROM "type_attrs"
          WHERE "id" = :"oldId"
          FOR UPDATE WITH LOCK
          INTO: typeId, "id";
        IF (:"id" IS NULL) THEN EXIT;
        -- Для записей в "obj" и "type_attrs"
        -- блокируем общую запись в "obj_types"
        -- для предотвращения изменения соответствующих записей из других
        -- транзакций в таблицах "type_attr" и "attr_value"
        SELECT "id"
          FROM "obj_types"
          WHERE "id" = :typeId
          FOR UPDATE WITH LOCK;
          INTO: typeId
        SAVEPOINT "type_attrs__set__SVPNT";
        BEGIN
          "x" = 'N';
          -- определяем наличие реферального FK
          UPDATE "type_attrs"
            SET "id" = NULL
            WHERE "id" = :"oldId";
          WHEN EXCEPTION "attr_value__xRowExists" DO
            "x" = 'Y';
        END
        ROLLBACK TO SAVEPOINT "type_attrs__set__SVPNT";
        RELEASE SAVEPOINT "type_attrs__set__SVPNT";
        UPDATE "type_attrs"
          SET "type_id" = :"typeId",
            "name" = :"name"
          WHERE "id" = :"oldId"
          RETURNING "id" INTO: "id";
      END
    ELSE
      DELETE FROM "type_attrs"
        WHERE "id" = :"oldId";
  SUSPEND;
END^
SET TERM ;^

GRANT ALL ON TABLE "type_attr" TO PROCEDURE "type_attr__set";
GRANT EXECUTE ON PROCEDURE "type_attr__set" TO ROLE USERS;

/*
 * Объекты
 */
CREATE TABLE "obj"
(
    "type_id" ID NOT NULL,
    "id"      ID NOT NULL,
    "name"    NAME NOT NULL,
  CONSTRAINT "obj__pk" PRIMARY KEY ("id"),
  CONSTRAINT "obj__uq" UNIQUE ("name"),
  CONSTRAINT "obj__fk__obj_types" FOREIGN KEY ("type_id")
    REFERENCES "obj_types" ("id") ON UPDATE CASCADE ON DELETE CASCADE
);

REVOKE ALL ON TABLE "obj" FROM ROLE USERS;

CREATE GENERATOR "obj__GEN";

SET TERM ^;
CREATE OR ALTER TRIGGER "obj__TR_BIU"
  FOR "obj"
  ACTIVE
  BEFORE INSERT OR UPDATE
  POSITION 0
AS
BEGIN
  IF (NOT(NEW."id" IS NOT NULL
          AND NEW."id" <= GEN_ID("obj__GEN", 0)
      ) THEN
    NEW."id" = GEN_ID("obj__GEN", 1);
END^
SET TERM ;^


SET TERM ^;
CREATE OR ALTER PROCEDURE "obj__set"
  (
    "oldId"   ID DEFAULT NULL,
    "typeId"  ID DEFAULT NULL,
    "name"    NAME DEFAULT NULL
  )
  RETURNS
  (
    "id" ID,
    "x"  BOOL
  )
AS
  DECLARE VARIABLE typeId ID DEFAULT NULL;
BEGIN
  IF (:"oldId" IS NULL) THEN
    BEGIN
      IF (:"typeId" IS NOT NULL AND :"name" IS NOT NULL) THEN
        INSERT INTO "obj" ("type_id", "name")
          VALUES (:"typeId", :"name")
          RETURNING "id" INTO: "id";
    END
  ELSE
    IF (:"typeId" IS NOT NULL AND :"name" IS NOT NULL) THEN
      BEGIN
        SELECT "type_id", "id"
          FROM "obj"
          WHERE "id" = :"oldId"
          FOR UPDATE WITH LOCK
          INTO: typeId, "id";
        IF (:"id" IS NULL) THEN EXIT;
        -- Для записей в "obj" и "type_attrs"
        -- блокируем общую запись в "obj_types"
        -- для предотвращения изменения соответствующих записей из других
        -- транзакций в таблицах "type_attr" и "attr_value"
        SELECT "id"
          FROM "obj_types"
          WHERE "id" = :typeId
          FOR UPDATE WITH LOCK;
          INTO: typeId
        SAVEPOINT "obj__set__SVPNT";
        BEGIN
          "x" = 'N';
          -- определяем наличие реферального FK
          UPDATE "obj"
            SET "id" = NULL
            WHERE "id" = :"oldId";
          WHEN EXCEPTION "attr_value__xRowExists" DO
            "x" = 'Y';
        END
        ROLLBACK TO SAVEPOINT "obj__set__SVPNT";
        RELEASE SAVEPOINT "obj__set__SVPNT";
        UPDATE "obj"
          SET "type_id" = :"typeId",
            "name" = :"name"
          WHERE "id" = :"oldId"
          RETURNING "id" INTO: "id";
      END
    ELSE
      DELETE FROM "obj"
        WHERE "id" = :"oldId";
  SUSPEND;
END^
SET TERM ;^

GRANT ALL ON TABLE "obj" TO PROCEDURE "obj__set";
GRANT EXECUTE ON PROCEDURE "obj__set" TO ROLE USERS;


/*
 * Значения атрибутов объектов
 */
CREATE TABLE "attr_value"
(
    "attr_id" ID NOT NULL,
    "obj_id"  ID NOT NULL,
    "id"      ID NOT NULL,
    "value"   DOUBLE PRECISION DEFAULT NULL,
  CONSTRAINT "attr_value__pk" PRIMARY KEY ("id"),
  CONSTRAINT "attr_value__fk__type_attrs" FOREIGN KEY ("attr_id")
    REFERENCES "type_attrs" ("id") ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT "attr_value__fk__obj" FOREIGN KEY ("obj_id")
    REFERENCES "obj" ("id") ON UPDATE CASCADE ON DELETE CASCADE
);

SET TERM ^;
CREATE OR ALTER TRIGGER "attr_value__TR_BU"
AS
BEGIN
  IF (NEW."attr_id" IS NULL) THEN
    EXCEPTION "attr_value__xRowExists";
END^
SET TERM ;^