SYSDBA: Создана новая страница размером CREATE TABLE fin_place ( placekey dintkey, /* уникальный идентификатор */ parent ...

2009-09-04T10:23:27Z

Создана новая страница размером <source lang="SQL"> CREATE TABLE fin_place ( placekey dintkey, - ‎уникальный идентификатор: parent ...

Новая страница

<source lang="SQL">
CREATE TABLE fin_place
(
placekey dintkey, /* уникальный идентификатор */
parent INTEGER, /* ссылка на родителя */
placekind dintkey, /* тип места */
name dname, /* наименование */
comment dtext, /* комментарий */
disabled dboolean, /* признак: не используется */
security INTEGER, /* дескриптор безопасности */
icon INTEGER, /* иконка */
reserved INTEGER /* зарезервировано */
);

ALTER TABLE fin_place ADD CONSTRAINT fin_pk_place
PRIMARY KEY (placekey);

ALTER TABLE fin_place ADD CONSTRAINT fin_fk_place_parent
FOREIGN KEY (parent) REFERENCES fin_place (placekey);

ALTER TABLE fin_place ADD CONSTRAINT fin_fk_place_placekind
FOREIGN KEY (placekind) REFERENCES fin_placekind (placekindkey);

CREATE ASC INDEX fin_x_place_name
ON fin_place (name);

CREATE ASC INDEX fin_x_place_disabled
ON fin_place (disabled);

CREATE ASC INDEX fin_x_place_security
ON fin_place (security);

CREATE GENERATOR fin_g_placekey;
SET GENERATOR fin_g_placekey TO 2000;

SET TERM ^ ;

CREATE EXCEPTION fin_e_invalidplace "Неверный адрес (Не соблюдается иерархия)"^
CREATE EXCEPTION fin_e_duplicateplace "Адрес дублируется"^

CREATE TRIGGER fin_insert_place FOR fin_place
BEFORE INSERT
POSITION 0
AS
DECLARE VARIABLE G1 SMALLINT;
DECLARE VARIABLE G2 SMALLINT;
DECLARE VARIABLE C INTEGER;
BEGIN
/* соблюдаем иерархию */
SELECT grade FROM fin_placekind
WHERE placekindkey = NEW.placekind
INTO :G1;

IF (NEW.parent IS NULL) THEN
BEGIN
IF (:G1 <> 200) THEN
EXCEPTION fin_e_invalidplace;
END ELSE
BEGIN
SELECT grade FROM fin_placekind
WHERE placekindkey = (SELECT placekind FROM fin_place WHERE placekey = NEW.parent)
INTO :G2;

IF (:G1 <= :G2) THEN
EXCEPTION fin_e_invalidplace;
END

SELECT COUNT(*) FROM fin_place
WHERE parent = NEW.parent AND g_s_ansicomparetext(name, NEW.name) = 0
INTO :C;

/*IF (:C > 0) THEN
EXCEPTION fin_e_duplicateplace; */

/* присваиваем ключ */
IF (NEW.placekey IS NULL) THEN
NEW.placekey = GEN_ID(fin_g_placekey, 1);

/* значения по умолчанию */
IF (NEW.disabled IS NULL) THEN
NEW.disabled = 0;
END;
^

CREATE PROCEDURE cst_p_placekey
RETURNS (placekey INTEGER)
AS
BEGIN
placekey = GEN_ID(fin_g_placekey, 1);
END;
^

CREATE PROCEDURE fin_p_plc_saveplace(Place INTEGER, Building VARCHAR(8),
Part VARCHAR(8), Office VARCHAR(8))
RETURNS (
PlaceKey INTEGER
)
AS
DECLARE VARIABLE C INTEGER;
DECLARE VARIABLE BK INTEGER;
DECLARE VARIABLE PK INTEGER;
DECLARE VARIABLE OK INTEGER;
BEGIN
/* если такого строения нет, то добавляем его */
SELECT COUNT(*) FROM fin_place
WHERE parent = :Place AND name = :Building
INTO :C;

IF (:C = 0) THEN
BEGIN
INSERT INTO fin_place(parent, placekind, name)
VALUES (:Place, 120, :Building);
END

/* берем ключ строения */
SELECT placekey FROM fin_place
WHERE parent = :Place AND name = :Building
INTO :BK;

/* корпуса может и не быть */
IF (:Part <> "") THEN
BEGIN
SELECT COUNT(*) FROM fin_place
WHERE parent = :BK AND name = :Part
INTO :C;

IF (:C = 0) THEN
BEGIN
INSERT INTO fin_place(parent, placekind, name)
VALUES (:BK, 130, :Part);
END

SELECT placekey FROM fin_place
WHERE parent = :BK AND name = :Part
INTO :PK;
END ELSE
BEGIN
PK = :BK;
END

/* квартиры может и не быть */
IF (:Office <> "") THEN
BEGIN
SELECT COUNT(*) FROM fin_place
WHERE parent = :PK AND name = :Office
INTO :C;

IF (:C = 0) THEN
BEGIN
INSERT INTO fin_place(parent, placekind, name)
VALUES (:PK, 140, :Office);
END

SELECT placekey FROM fin_place
WHERE parent = :PK AND name = :Office
INTO :OK;
END ELSE
BEGIN
OK = :PK;
END

PlaceKey = :OK;
END;
^

CREATE PROCEDURE fin_p_plc_saveplace_newstreet(Place INTEGER, Street VARCHAR(60),
Building VARCHAR(8), Part VARCHAR(8), Office VARCHAR(8))
RETURNS (
PlaceKey INTEGER
)
AS
DECLARE VARIABLE C INTEGER;
DECLARE VARIABLE SK INTEGER;
BEGIN
/* если такого города нет, то добавляем его */
SELECT COUNT(*) FROM fin_place
WHERE parent = :Place AND name = :Street
INTO :C;

IF (:C = 0) THEN
BEGIN
INSERT INTO fin_place(parent, placekind, name)
VALUES (:Place, 70, :Street);
END

/* берем ключ города */
SELECT placekey FROM fin_place
WHERE parent = :Place AND name = :Street
INTO :SK;

EXECUTE PROCEDURE fin_p_plc_saveplace (:SK, Building, Part, Office)
RETURNING_VALUES :PlaceKey;
END;
^

CREATE PROCEDURE fin_p_plc_place2str(PlaceKey INTEGER)
RETURNS (
FullAddress VARCHAR(255)
)
AS
DECLARE VARIABLE Parent INTEGER;
DECLARE VARIABLE Name VARCHAR(255);
DECLARE VARIABLE PlaceKind INTEGER;
BEGIN
SELECT parent, name, placekind
FROM fin_place WHERE placekey = :PlaceKey
INTO :Parent, :Name, :PlaceKind;

IF (:Parent IS NULL) THEN
BEGIN
FullAddress = :Name;
END ELSE
BEGIN
EXECUTE PROCEDURE fin_p_plc_place2str (:Parent)
RETURNING_VALUES :FullAddress;

FullAddress = :FullAddress || ", " || :Name;
END
END;
^

CREATE PROCEDURE fin_p_plc_addrlist(PlaceKey INTEGER, DetailKey INTEGER)
RETURNS (
FullAddress VARCHAR(255)
)
AS
DECLARE VARIABLE K INTEGER;
DECLARE VARIABLE PK INTEGER;
BEGIN
FOR
SELECT placekey, placekind FROM fin_place
WHERE parent = :PlaceKey
INTO :K, :PK
DO BEGIN
EXECUTE PROCEDURE fin_p_plc_place2str (:K)
RETURNING_VALUES :FullAddress;

SUSPEND;
END
END;
^

CREATE PROCEDURE fin_p_plc_deleteplace(PlaceKey INTEGER)
RETURNS (
Result INTEGER
)
AS
DECLARE VARIABLE PK INTEGER;
BEGIN
FOR
SELECT placekey FROM fin_place
WHERE parent = :PlaceKey
INTO :PK
DO
BEGIN
EXECUTE PROCEDURE fin_p_plc_deleteplace(:PK)
RETURNING_VALUES :Result;
END

DELETE FROM fin_place WHERE placekey = :PlaceKey;

Result = 1;
END;
^

CREATE PROCEDURE fin_p_plc_expandplckey(PlaceKey INTEGER)
RETURNS (
CountryKey INTEGER,
CountryName VARCHAR(255),
ProvinceKey INTEGER,
ProvinceName VARCHAR(255),
DistrictKey INTEGER,
DistrictName VARCHAR(255),
TownKey INTEGER,
TownName VARCHAR(255),
StreetKey INTEGER,
StreetName VARCHAR(255),
BuildingKey INTEGER,
BuildingName VARCHAR(8),
PartKey INTEGER,
PartName VARCHAR(8),
OfficeKey INTEGER,
OfficeName VARCHAR(8)
)
AS
DECLARE VARIABLE PK INTEGER;
DECLARE VARIABLE Name VARCHAR(255);
BEGIN
CountryKey = NULL;
CountryName = "";
ProvinceKey = NULL;
ProvinceName = "";
DistrictKey = NULL;
DistrictName = "";
TownKey = NULL;
TownName = "";
StreetKey = NULL;
StreetName = "";
BuildingKey = NULL;
BuildingName = "";
PartKey = NULL;
PartName = "";
OfficeKey = NULL;
OfficeName = "";

WHILE (NOT :PlaceKey IS NULL) DO
BEGIN
SELECT placekind, name FROM fin_place WHERE placekey = :PlaceKey
INTO :PK, :Name;

IF (:PK >= 140 AND :PK <= 170) THEN
BEGIN
OfficeKey = :PlaceKey;
OfficeName = :Name;
END

IF (:PK = 130) THEN
BEGIN
PartKey = :PlaceKey;
PartName = :Name;
END

IF (:PK = 120) THEN
BEGIN
BuildingKey = :PlaceKey;
BuildingName = :Name;
END

IF (:PK >= 70 AND :PK <= 110) THEN
BEGIN
StreetKey = :PlaceKey;
StreetName = :Name;
END

IF (:PK >= 40 AND :PK <= 60) THEN
BEGIN
TownKey = :PlaceKey;
TownName = :Name;
END

IF (:PK = 30) THEN
BEGIN
DistrictKey = :PlaceKey;
DistrictName = :Name;
END

IF (:PK = 20) THEN
BEGIN
ProvinceKey = :PlaceKey;
ProvinceName = :Name;
END

IF (:PK = 10) THEN
BEGIN
CountryKey = :PlaceKey;
CountryName = :Name;
END

SELECT parent FROM fin_place WHERE placekey = :PlaceKey
INTO :PlaceKey;
END
END;
^

CREATE PROCEDURE fin_p_plc_subplacelist(ParentKey INTEGER)
RETURNS (
PlaceKey INTEGER
)
AS
DECLARE VARIABLE PK INTEGER;
BEGIN
FOR
SELECT placekey
FROM fin_place
WHERE placekey = :ParentKey
INTO :PlaceKey
DO
SUSPEND;

FOR
SELECT placekey
FROM fin_place
WHERE parent = :ParentKey
INTO :PK
DO
FOR
SELECT placekey
FROM fin_p_plc_subplacelist(:PK)
INTO :PlaceKey
DO
SUSPEND;
END;
^

SET TERM ; ^
</source>

[[Category:Архив]]
[[Category:Проект InfoRegistr]]

FIN PLACE - История изменений

SYSDBA: Создана новая страница размером CREATE TABLE fin_place ( placekey dintkey, /* уникальный идентификатор */ parent ...