iBase.ru Forum

Код: Выделить всё

CREATE GENERATOR GEN_DOMAINS_ID;

CREATE TABLE DOMAINS (
    ID           INTEGER NOT NULL,
    NAME         NAME_PART /* NAME_PART = VARCHAR(16) */,
    PARENT       INTEGER,
    DNS_RECORD   COMPUTED BY ("NAME"||'.'||(select "FULL_NAME" from "GET_FQDN"("DOMAINS"."ID"))),
    FQDN         COMPUTED BY (SUBSTR("DNS_RECORD",1,STRLEN("DNS_RECORD")-1)),
    DNS          BOOLEAN NOT NULL /* BOOLEAN = SMALLINT DEFAULT 0 NOT NULL CHECK (value in (0,1)) */,
    PROJECT      BOOLEAN NOT NULL /* BOOLEAN = SMALLINT DEFAULT 0 NOT NULL CHECK (value in (0,1)) */,
    RECORD_TYPE  DNS_TYPES /* DNS_TYPES = VARCHAR(2) DEFAULT 'A' NOT NULL CHECK (value in ('A', 'MX')) */
);
ALTER TABLE DOMAINS ADD CONSTRAINT UNQ1_DOMAINS UNIQUE (NAME, PARENT);
ALTER TABLE DOMAINS ADD CONSTRAINT PK_DOMAINS PRIMARY KEY (ID);
ALTER TABLE DOMAINS ADD CONSTRAINT FK_DOMAINS_PARENT FOREIGN KEY (PARENT) REFERENCES DOMAINS (ID) ON DELETE SET NULL ON UPDATE SET NULL;

есть процедура
CREATE PROCEDURE GET_FQDN (
    INPUT_ID INTEGER)
RETURNS (
    FULL_NAME VARCHAR(128))
AS
DECLARE VARIABLE PARENT_DOMAIN INTEGER;
begin
  "FULL_NAME" = '**db_error**';
  select "PARENT" from "DOMAINS" where "DOMAINS"."ID" = :"INPUT_ID" into :"PARENT_DOMAIN";
  if("PARENT_DOMAIN" is not null) then
    begin
    select "DOMAINS"."DNS_RECORD" from "DOMAINS" where "DOMAINS"."ID" = :"PARENT_DOMAIN" into :"FULL_NAME";
    end
   else
    begin
     "FULL_NAME" = '';
    end
  suspend;
end