Кладр

 CREATE DOMAIN D$NAME AS VARCHAR(120);


CREATE DOMAIN D$POSTALCODE AS VARCHAR(6) CHECK ((char_length(Value) = 6) or (Value is Null));


CREATE DOMAIN D$PREFIX_NAME AS VARCHAR(10);


CREATE DOMAIN D$SMALLINT AS SMALLINT;


CREATE DOMAIN D$ADDRESS AS VARCHAR(1000);


CREATE DOMAIN D$VC3 AS VARCHAR(3);


CREATE DOMAIN D$GUID AS CHAR(16) CHARACTER SET OCTETS;


CREATE DOMAIN D$OKATO AS VARCHAR(11) CHECK ((char_length(Value) = 11) or (Value is Null));


CREATE DOMAIN D$OKTMO AS VARCHAR(11) CHECK ((char_length(Value) IN (8, 11)) or (Value is Null));


CREATE DOMAIN D$KLADR_ID AS VARCHAR(17);


/* ---------------------------------------------------------------------- */


/* Add table "FIAS$ADDRESSES" */


/* ---------------------------------------------------------------------- */


CREATE TABLE FIAS$ADDRESSES (


OBJECT_ID D$GUID NOT NULL,


PARENT_FK D$GUID,


LEVEL D$SMALLINT NOT NULL,


REGION_CODE D$SMALLINT NOT NULL,


NAME D$NAME NOT NULL,


FORMAL_NAME D$NAME NOT NULL,


PREFIX_NAME D$PREFIX_NAME NOT NULL,


POSTALCODE D$POSTALCODE,


OKATO D$OKATO,


OKTMO D$OKTMO,


KLADR_ID D$KLADR_ID NOT NULL,


CONSTRAINT FIAS$ADDRESSES$PK PRIMARY KEY (OBJECT_ID),


CONSTRAINT FIAS$ADDRESSES$UC_KLADR_ID UNIQUE (KLADR_ID)


);


CREATE INDEX FIAS$ADDRESSES$IDX_PFK_LVL ON FIAS$ADDRESSES (PARENT_FK,LEVEL);


CREATE INDEX FIAS$ADDRESSES$IDX_RCODE_LVL ON FIAS$ADDRESSES (REGION_CODE,LEVEL);


CREATE INDEX FIAS$ADDRESSES$IDX_POSTALCODE ON FIAS$ADDRESSES (POSTALCODE);


CREATE INDEX FIAS$ADDRESSES$IDX_UPPER_NAME ON FIAS$ADDRESSES (NAME);


ALTER TABLE FIAS$ADDRESSES ADD CONSTRAINT FIAS$ADDRESSES$CC_LEVEL 


CHECK ([LEVEL] BETWEEN 1 AND 7);


UPDATE RDB$RELATIONS SET RDB$DESCRIPTION = 'ФИАС: Справочник адресных объектов' WHERE RDB$RELATION_NAME='FIAS$ADDRESSES';


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Уникальный идентификатор' WHERE (RDB$RELATION_NAME = 'FIAS$ADDRESSES') AND (RDB$FIELD_NAME = 'OBJECT_ID');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Ссылка на родителя' WHERE (RDB$RELATION_NAME = 'FIAS$ADDRESSES') AND (RDB$FIELD_NAME = 'PARENT_FK');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Уровень объекта' WHERE (RDB$RELATION_NAME = 'FIAS$ADDRESSES') AND (RDB$FIELD_NAME = 'LEVEL');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Код региона' WHERE (RDB$RELATION_NAME = 'FIAS$ADDRESSES') AND (RDB$FIELD_NAME = 'REGION_CODE');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Наименование объекта' WHERE (RDB$RELATION_NAME = 'FIAS$ADDRESSES') AND (RDB$FIELD_NAME = 'NAME');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Формальное наименование объекта' WHERE (RDB$RELATION_NAME = 'FIAS$ADDRESSES') AND (RDB$FIELD_NAME = 'FORMAL_NAME');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Префикс' WHERE (RDB$RELATION_NAME = 'FIAS$ADDRESSES') AND (RDB$FIELD_NAME = 'PREFIX_NAME');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Индекс' WHERE (RDB$RELATION_NAME = 'FIAS$ADDRESSES') AND (RDB$FIELD_NAME = 'POSTALCODE');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'ОКАТО' WHERE (RDB$RELATION_NAME = 'FIAS$ADDRESSES') AND (RDB$FIELD_NAME = 'OKATO');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'ОКТМО' WHERE (RDB$RELATION_NAME = 'FIAS$ADDRESSES') AND (RDB$FIELD_NAME = 'OKTMO');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Идентификатор в КЛАДР' WHERE (RDB$RELATION_NAME = 'FIAS$ADDRESSES') AND (RDB$FIELD_NAME = 'KLADR_ID');


/* ---------------------------------------------------------------------- */


/* Add table "FIAS$HOUSES" */


/* ---------------------------------------------------------------------- */


CREATE TABLE FIAS$HOUSES (


OBJECT_ID D$GUID NOT NULL,


OBJECT_FK D$GUID NOT NULL,


NAME D$PREFIX_NAME,


DSTART D$SMALLINT,


DEND D$SMALLINT,


DTYPE D$SMALLINT NOT NULL,


POSTALCODE D$POSTALCODE,


OKATO D$OKATO,


OKTMO D$OKTMO,


CONSTRAINT FIAS$HOUSES$PK PRIMARY KEY (OBJECT_ID)


);


ALTER TABLE FIAS$HOUSES ADD CONSTRAINT FIAS$HOUSES$CC_DEND 


CHECK ((DEND is not Null and Name is Null and DEND >= DStart) or (DEND is Null and Name is not Null));


ALTER TABLE FIAS$HOUSES ADD CONSTRAINT FIAS$HOUSES$CC_DSTART 


CHECK ((DSTART is not Null and Name is Null) or (DSTART is Null and Name is not Null));


ALTER TABLE FIAS$HOUSES ADD CONSTRAINT FIAS$HOUSES$CC_DTYPE 


CHECK (DTYPE BETWEEN 0 AND 3);


UPDATE RDB$RELATIONS SET RDB$DESCRIPTION = 'ФИАС: Справочник элементов адреса, идентифицирующих адресуемые объекты' WHERE RDB$RELATION_NAME='FIAS$HOUSES';


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Уникальный идентификатор' WHERE (RDB$RELATION_NAME = 'FIAS$HOUSES') AND (RDB$FIELD_NAME = 'OBJECT_ID');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Ссылка на родителя' WHERE (RDB$RELATION_NAME = 'FIAS$HOUSES') AND (RDB$FIELD_NAME = 'OBJECT_FK');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Наименование объекта' WHERE (RDB$RELATION_NAME = 'FIAS$HOUSES') AND (RDB$FIELD_NAME = 'NAME');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Значение начала интервала' WHERE (RDB$RELATION_NAME = 'FIAS$HOUSES') AND (RDB$FIELD_NAME = 'DSTART');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Значение окончания интервала' WHERE (RDB$RELATION_NAME = 'FIAS$HOUSES') AND (RDB$FIELD_NAME = 'DEND');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Тип интервала 0 - дом 1 - обычный 2 - чётный 3 - нечётный' WHERE (RDB$RELATION_NAME = 'FIAS$HOUSES') AND (RDB$FIELD_NAME = 'DTYPE');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Индекс' WHERE (RDB$RELATION_NAME = 'FIAS$HOUSES') AND (RDB$FIELD_NAME = 'POSTALCODE');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'ОКАТО' WHERE (RDB$RELATION_NAME = 'FIAS$HOUSES') AND (RDB$FIELD_NAME = 'OKATO');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'ОКТМО' WHERE (RDB$RELATION_NAME = 'FIAS$HOUSES') AND (RDB$FIELD_NAME = 'OKTMO');


/* ---------------------------------------------------------------------- */


/* Add table "COUNTRIES" */


/* ---------------------------------------------------------------------- */


CREATE TABLE COUNTRIES (


COUNTRY_ID D$VC3 NOT NULL,


CODE D$VC3 NOT NULL,


NAME D$NAME NOT NULL,


FULL_NAME D$NAME NOT NULL,


CONSTRAINT COUNTRIES$PK PRIMARY KEY (COUNTRY_ID),


CONSTRAINT COUNTRIES$UN_CODE UNIQUE (CODE)


);


UPDATE RDB$RELATIONS SET RDB$DESCRIPTION = 'Справочник стран' WHERE RDB$RELATION_NAME='COUNTRIES';


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Уникальный идентификатор' WHERE (RDB$RELATION_NAME = 'COUNTRIES') AND (RDB$FIELD_NAME = 'COUNTRY_ID');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Код объекта' WHERE (RDB$RELATION_NAME = 'COUNTRIES') AND (RDB$FIELD_NAME = 'CODE');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Наименование объекта' WHERE (RDB$RELATION_NAME = 'COUNTRIES') AND (RDB$FIELD_NAME = 'NAME');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Полное наименование объекта' WHERE (RDB$RELATION_NAME = 'COUNTRIES') AND (RDB$FIELD_NAME = 'FULL_NAME');


/* ---------------------------------------------------------------------- */


/* Add table "FIAS$PREFIXES" */


/* ---------------------------------------------------------------------- */


CREATE TABLE FIAS$PREFIXES (


LEVEL D$SMALLINT NOT NULL,


PREFIX_NAME D$PREFIX_NAME NOT NULL,


NAME D$NAME NOT NULL,


CONSTRAINT FIAS$PREFIXES$PK PRIMARY KEY ([LEVEL], PREFIX_NAME)


);


ALTER TABLE FIAS$PREFIXES ADD CONSTRAINT FIAS$PREFIXES$CC_LEVEL 


CHECK ([LEVEL] BETWEEN 1 AND 8);


UPDATE RDB$RELATIONS SET RDB$DESCRIPTION = 'ФИАС: Справочник типов адресных элементов и уровней их классификации' WHERE RDB$RELATION_NAME='FIAS$PREFIXES';


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Уровень объекта' WHERE (RDB$RELATION_NAME = 'FIAS$PREFIXES') AND (RDB$FIELD_NAME = 'LEVEL');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Префикс' WHERE (RDB$RELATION_NAME = 'FIAS$PREFIXES') AND (RDB$FIELD_NAME = 'PREFIX_NAME');


UPDATE RDB$RELATION_FIELDS SET RDB$DESCRIPTION = 'Наименование объекта' WHERE (RDB$RELATION_NAME = 'FIAS$PREFIXES') AND (RDB$FIELD_NAME = 'NAME');


/* ---------------------------------------------------------------------- */


/* Foreign key constraints */


/* ---------------------------------------------------------------------- */


ALTER TABLE FIAS$ADDRESSES ADD CONSTRAINT FIAS$ADDRESSES_FIAS$ADDRESSES 


FOREIGN KEY (PARENT_FK) REFERENCES FIAS$ADDRESSES (OBJECT_ID);


ALTER TABLE FIAS$ADDRESSES ADD CONSTRAINT FIAS$PREFIXES_FIAS$ADDRESSES 


FOREIGN KEY ([LEVEL], PREFIX_NAME) REFERENCES FIAS$PREFIXES (LEVEL,PREFIX_NAME);


ALTER TABLE FIAS$HOUSES ADD CONSTRAINT FIAS$ADDRESSES_FIAS$HOUSES 


FOREIGN KEY (OBJECT_FK) REFERENCES FIAS$ADDRESSES (OBJECT_ID);