Организация удаленного доступа к распределенным базам данных

SQL-скрипт для генерации базы данных

CREATE GENERATOR genUslPropsKeys;

CREATE GENERATOR genUslProps;

CREATE GENERATOR genPhonesRegions;

CREATE GENERATOR genPhonesStations;

CREATE GENERATOR genPhonesStreets;

CREATE GENERATOR genPhonesBanks;

CREATE GENERATOR genTalksPay;

CREATE GENERATOR genTalks;

CREATE GENERATOR genNach;

CREATE GENERATOR genNachBillings;

CREATE GENERATOR genNachBillDates;

CREATE GENERATOR genNachConstUsl;

CREATE GENERATOR genUslDivisions;

CREATE GENERATOR genUslLgots;

CREATE GENERATOR genUslsKeys;

CREATE GENERATOR genUsls;

CREATE GENERATOR genUslCatKeys;

CREATE GENERATOR genUslCat;

CREATE GENERATOR genPhones;

CREATE GENERATOR genPhonesOwnersKeys;

CREATE GENERATOR genPhonesOwners;

CREATE GENERATOR genSysSettings;

CREATE GENERATOR genPhonesKeys;

CREATE GENERATOR genPlat;

CREATE GENERATOR genPhonesPostStations;

CREATE GENERATOR genSysLog;

CREATE GENERATOR genUslTypes;

CREATE GENERATOR genUslDivisionsKeys;

CREATE DOMAIN CALLTIME_TYPE INTEGER NOT NULL;

CREATE DOMAIN CURR_TYPE FLOAT DEFAULT 0 NOT NULL;

CREATE DOMAIN DATE_TYPE DATE NOT NULL;

CREATE DOMAIN DESCR_TYPE CHAR(32);

CREATE DOMAIN PHONE_TYPE CHAR(7) NOT NULL;

CREATE DOMAIN PROCENT_TYPE FLOAT DEFAULT 100 NOT NULL

CHECK (VALUE BETWEEN 0 AND 300);

CREATE TABLE Nach (

Code INTEGER NOT NULL,

Owner INTEGER NOT NULL,

Usl INTEGER NOT NULL,

Phone INTEGER,

UslSum CURR_TYPE,

NachDate DATE_TYPE,

BillDate DATE_TYPE

);

ALTER TABLE Nach

ADD CONSTRAINT XPKNach PRIMARY KEY (Code);

CREATE TABLE NachBillDates (

Code INTEGER NOT NULL,

BillingDate INTEGER NOT NULL

);

ALTER TABLE NachBillDates

ADD CONSTRAINT XPKBillDates PRIMARY KEY (Code);

CREATE TABLE NachBillings (

Code INTEGER NOT NULL,

Division INTEGER NOT NULL,

Owner INTEGER NOT NULL,

BillDateCode INTEGER NOT NULL

);

ALTER TABLE NachBillings

ADD CONSTRAINT XPKNachBillings PRIMARY KEY (Code);

CREATE TABLE NachConstUsl (

Code INTEGER NOT NULL,

Owner INTEGER NOT NULL,

Usl INTEGER NOT NULL,

Phone INTEGER NOT NULL,

UslSum CURR_TYPE,

BegDate DATE_TYPE,

EndDate DATE_TYPE

);

ALTER TABLE NachConstUsl

ADD CONSTRAINT XPKNachConstUsl PRIMARY KEY (Code);

CREATE TABLE Phones (

Code INTEGER NOT NULL,

Street INTEGER NOT NULL,

Owner INTEGER NOT NULL,

PKey INTEGER NOT NULL,

Comment DESCR_TYPE,

PhoneNmb PHONE_TYPE,

InstallDate DATE_TYPE,

RemoveDate DATE_TYPE,

BegDate DATE_TYPE,

EndDate DATE_TYPE

);

ALTER TABLE Phones

ADD CONSTRAINT XPKPhones PRIMARY KEY (Code);

CREATE TRIGGER Phones_BUH FOR Phones

BEFORE UPDATE POSITION 0

AS

BEGIN

/* Изменение BegDate */

IF (new.BegDate <> old.BegDate) THEN

BEGIN

IF (new.BegDate < old.BegDate) THEN

BEGIN

/* Расширение BegDate */

UPDATE Phones

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение BegDate */

UPDATE Phones

SET EndDate = new.BegDate

WHERE ((EndDate = old.BegDate) AND (PKey = new.PKey));

END

END

/* Изменение EndDate */

IF (new.EndDate <> old.EndDate) THEN

BEGIN

IF (new.EndDate > old.EndDate) THEN

BEGIN

/* Расширение EndDate */

UPDATE Phones

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение EndDate */

UPDATE Phones

SET BegDate = new.EndDate

WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));

END

END

/* Сборка мусора */

DELETE FROM Phones

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));

END ^

CREATE TRIGGER Phones_BIH FOR Phones

BEFORE INSERT POSITION 0

AS

BEGIN

DELETE FROM Phones

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey));

UPDATE Phones

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

UPDATE Phones

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END ^

CREATE TRIGGER Phones_BDH FOR Phones

BEFORE DELETE POSITION 0

AS

BEGIN

UPDATE Phones

SET EndDate = old.EndDate

WHERE ((EndDate = old.BegDate) AND (PKey = old.PKey));

END ^

CREATE TABLE PhonesBanks (

Code INTEGER NOT NULL,

Name1 DESCR_TYPE,

PMFO CHAR(12) NOT NULL,

Name2 DESCR_TYPE,

ELMFO CHAR(12) NOT NULL,

PlatCount SMALLINT NOT NULL,

Acc1 CHAR(12) NOT NULL,

Acc2 CHAR(12) NOT NULL

);

CREATE INDEX XIEPhonesBanksName ON PhonesBanks

(

Name1,

Name2

);

ALTER TABLE PhonesBanks

ADD CONSTRAINT XPKPhonesBanks PRIMARY KEY (Code);

CREATE TABLE PhonesKeys (

Code INTEGER NOT NULL

);

ALTER TABLE PhonesKeys

ADD CONSTRAINT XPKPhonesKeys PRIMARY KEY (Code);

CREATE TABLE PhonesOwners (

Code INTEGER NOT NULL,

PKey INTEGER NOT NULL,

Name1 DESCR_TYPE,

Name2 DESCR_TYPE,

Category INTEGER NOT NULL,

Bank INTEGER,

Street INTEGER NOT NULL,

PostStation INTEGER,

House CHAR(5),

Corpus CHAR(3),

Flat CHAR(3),

Account CHAR(5),

RS CHAR(9),

INN CHAR(13),

Nmb_Dogov CHAR(6),

Date_Dogov DATE,

BegDate DATE_TYPE,

EndDate DATE_TYPE

);

ALTER TABLE PhonesOwners

ADD CONSTRAINT XPKPhonesOwners PRIMARY KEY (Code);

CREATE TRIGGER PhonesOwners_BUH FOR PhonesOwners

BEFORE UPDATE POSITION 0

AS

BEGIN

/* Изменение BegDate */

IF (new.BegDate <> old.BegDate) THEN

BEGIN

IF (new.BegDate < old.BegDate) THEN

BEGIN

/* Расширение BegDate */

UPDATE PhonesOwners

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение BegDate */

UPDATE PhonesOwners

SET EndDate = new.BegDate

WHERE ((EndDate = old.BegDate) AND (PKey = new.PKey));

END

END

/* Изменение EndDate */

IF (new.EndDate <> old.EndDate) THEN

BEGIN

IF (new.EndDate > old.EndDate) THEN

BEGIN

/* Расширение EndDate */

UPDATE PhonesOwners

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение EndDate */

UPDATE PhonesOwners

SET BegDate = new.EndDate

WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));

END

END

/* Сборка мусора */

DELETE FROM PhonesOwners

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));

END ^

CREATE TRIGGER PhonesOwners_BIH FOR PhonesOwners

BEFORE INSERT POSITION 0

AS

BEGIN

DELETE FROM PhonesOwners

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey));

UPDATE PhonesOwners

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

UPDATE PhonesOwners

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END ^

CREATE TRIGGER PhonesOwners_BDH FOR PhonesOwners

BEFORE DELETE POSITION 0

AS

BEGIN

UPDATE PhonesOwners

SET EndDate = old.EndDate

WHERE ((EndDate = old.BegDate) AND (PKey = old.PKey));

END ^

CREATE TABLE PhonesOwnersKeys (

Code INTEGER NOT NULL,

InRest CURR_TYPE,

OutRest CURR_TYPE,

NDolg INTEGER NOT NULL

);

ALTER TABLE PhonesOwnersKeys

ADD CONSTRAINT XPKPhonesOwnersKeys PRIMARY KEY (Code);

CREATE TABLE PhonesPostStations (

Code INTEGER NOT NULL,

Name DESCR_TYPE,

Region INTEGER NOT NULL,

PostIndex CHAR(6) NOT NULL,

PostNmb CHAR(6) NOT NULL

);

CREATE UNIQUE INDEX XAKPhonesPostStationsIndex ON PhonesPostStations

(

PostIndex

);

CREATE UNIQUE INDEX XAKPhonesPostStationsPostNmb ON PhonesPostStations

(

PostNmb

);

CREATE INDEX XIEPhonesPostStationsName ON PhonesPostStations

(

Name

);

ALTER TABLE PhonesPostStations

ADD CONSTRAINT XPKPhonesPostStations PRIMARY KEY (Code);

CREATE TABLE PhonesRegions (

Code INTEGER NOT NULL,

Name DESCR_TYPE NOT NULL

);

CREATE INDEX XIEPhonesRegionsName ON PhonesRegions

(

Name

);

ALTER TABLE PhonesRegions

ADD CONSTRAINT XPKPhonesRegions PRIMARY KEY (Code);

CREATE TABLE PhonesStations (

Code INTEGER NOT NULL,

Region INTEGER NOT NULL,

Name DESCR_TYPE NOT NULL

);

CREATE INDEX XIEPhonesStationsName ON PhonesStations

(

Name

);

ALTER TABLE PhonesStations

ADD CONSTRAINT XPKPhonesStations PRIMARY KEY (Code);

CREATE TABLE PhonesStreets (

Code INTEGER NOT NULL,

Station INTEGER NOT NULL,

Region INTEGER NOT NULL,

Name DESCR_TYPE

);

CREATE INDEX XIEPhonesStreetsName ON PhonesStreets

(

Name

);

ALTER TABLE PhonesStreets

ADD CONSTRAINT XPKPhonesStreets PRIMARY KEY (Code);

CREATE TABLE Plat (

Code INTEGER NOT NULL,

Owner INTEGER NOT NULL,

ToUsl INTEGER,

PlatDate DATE_TYPE,

PlatType INTEGER NOT NULL,

DocNmb CHAR(12) NOT NULL

);

ALTER TABLE Plat

ADD CONSTRAINT XPKPlat PRIMARY KEY (Code);

CREATE TABLE SysLog (

Code INTEGER NOT NULL,

TableName CHAR(16) NOT NULL,

OpType INTEGER NOT NULL,

NewData CHAR(64) NOT NULL,

OpDate DATE NOT NULL

);

ALTER TABLE SysLog

ADD CONSTRAINT XPKSysLog PRIMARY KEY (Code);

CREATE TABLE SysSettings (

Code INTEGER NOT NULL,

TimeTalksUsl INTEGER NOT NULL,

NullOwner INTEGER NOT NULL

);

ALTER TABLE SysSettings

ADD CONSTRAINT XPKSysSettings PRIMARY KEY (Code);

CREATE TABLE Talks (

Code INTEGER NOT NULL,

DayCode INTEGER NOT NULL,

Phone INTEGER NOT NULL,

ToPhone INTEGER NOT NULL,

CallTime CALLTIME_TYPE,

PhoneNmb PHONE_TYPE,

HowLong INTEGER NOT NULL,

ToPhoneNmb PHONE_TYPE,

Calculated SMALLINT NOT NULL,

CallDate DATE_TYPE

);

CREATE INDEX XAK1TalksCallDate ON Talks

(

CallDate

);

ALTER TABLE Talks

ADD CONSTRAINT XPKTalks PRIMARY KEY (Code);

CREATE TABLE TalksPay (

Code INTEGER NOT NULL,

Phone INTEGER NOT NULL,

TotalSum CURR_TYPE,

TotalLgotTime CALLTIME_TYPE,

TotalFullTime CALLTIME_TYPE,

TotalTime COMPUTED BY (TotalLgotTime+TotalFullTime),

CallDate DATE_TYPE

);

ALTER TABLE TalksPay

ADD CONSTRAINT XPKTalksPay PRIMARY KEY (Code);

CREATE TABLE UslCat (

Code INTEGER NOT NULL,

PKey INTEGER NOT NULL,

Name DESCR_TYPE,

Parent INTEGER NOT NULL,

BegDate DATE_TYPE,

EndDate DATE_TYPE

);

CREATE INDEX XIEUslCatName ON UslCat

(

Name

);

CREATE INDEX XIEUslCatParent ON UslCat

(

Parent

);

ALTER TABLE UslCat

ADD CONSTRAINT XPKUslCat PRIMARY KEY (Code);

CREATE TRIGGER UslCat_BUH FOR UslCat

BEFORE UPDATE POSITION 0

AS

BEGIN

/* Изменение BegDate */

IF (new.BegDate <> old.BegDate) THEN

BEGIN

IF (new.BegDate < old.BegDate) THEN

BEGIN

/* Расширение BegDate */

UPDATE UslCat

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение BegDate */

UPDATE UslCat

SET EndDate = new.BegDate

WHERE ((EndDate = old.BegDate) AND (PKey = new.PKey));

END

END

/* Изменение EndDate */

IF (new.EndDate <> old.EndDate) THEN

BEGIN

IF (new.EndDate > old.EndDate) THEN

BEGIN

/* Расширение EndDate */

UPDATE UslCat

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение EndDate */

UPDATE UslCat

SET BegDate = new.EndDate

WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));

END

END

/* Сборка мусора */

DELETE FROM UslCat

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));

END ^

CREATE TRIGGER UslCat_BIH FOR UslCat

BEFORE INSERT POSITION 0

AS

BEGIN

DELETE FROM UslCat

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey));

UPDATE UslCat

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

UPDATE UslCat

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END ^

CREATE TRIGGER UslCat_BDH FOR UslCat

BEFORE DELETE POSITION 0

AS

BEGIN

UPDATE UslCat

SET EndDate = old.EndDate

WHERE ((EndDate = old.BegDate) AND (PKey = old.PKey));

END ^

CREATE TABLE UslCatKeys (

Code INTEGER NOT NULL

);

ALTER TABLE UslCatKeys

ADD CONSTRAINT XPKUslCatKeys PRIMARY KEY (Code);

CREATE TABLE UslDivisions (

Code INTEGER NOT NULL,

Name DESCR_TYPE,

PKey INTEGER NOT NULL,

Parent INTEGER NOT NULL,

BegDate DATE_TYPE,

EndDate DATE_TYPE

);

CREATE INDEX XIEUslDivisionsname ON UslDivisions

(

Name

);

CREATE INDEX XIEUslDivisionsParent ON UslDivisions

(

Parent

);

ALTER TABLE UslDivisions

ADD CONSTRAINT XPKUslDivisions PRIMARY KEY (Code);

CREATE TRIGGER UslDivisions_BUH FOR UslDivisions

BEFORE UPDATE POSITION 0

AS

BEGIN

/* Изменение BegDate */

IF (new.BegDate <> old.BegDate) THEN

BEGIN

IF (new.BegDate < old.BegDate) THEN

BEGIN

/* Расширение BegDate */

UPDATE UslDivisions

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение BegDate */

UPDATE UslDivisions

SET EndDate = new.BegDate

WHERE ((EndDate = old.BegDate) AND (PKey = new.PKey));

END

END

/* Изменение EndDate */

IF (new.EndDate <> old.EndDate) THEN

BEGIN

IF (new.EndDate > old.EndDate) THEN

BEGIN

/* Расширение EndDate */

UPDATE UslDivisions

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение EndDate */

UPDATE UslDivisions

SET BegDate = new.EndDate

WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));

END

END

/* Сборка мусора */

DELETE FROM UslDivisions

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));

END ^

CREATE TRIGGER UslDivisions_BIH FOR UslDivisions

BEFORE INSERT POSITION 0

AS

BEGIN

DELETE FROM UslDivisions

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey));

UPDATE UslDivisions

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

UPDATE UslDivisions

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END ^

CREATE TRIGGER UslDivisions_BDH FOR UslDivisions

BEFORE DELETE POSITION 0

AS

BEGIN

UPDATE UslDivisions

SET EndDate = old.EndDate

WHERE ((EndDate = old.BegDate) AND (PKey = old.PKey));

END ^

CREATE TABLE UslDivisionsKeys (

Code INTEGER NOT NULL

);

ALTER TABLE UslDivisionsKeys

ADD CONSTRAINT XPKUslDivisionsKeys PRIMARY KEY (Code);

CREATE TABLE UslLgots (

Code INTEGER NOT NULL,

Category INTEGER NOT NULL,

Property INTEGER,

Tax CURR_TYPE,

Usl INTEGER NOT NULL,

NachCoeff INTEGER NOT NULL,

Nalog INTEGER NOT NULL,

BegDate INTEGER NOT NULL,

Info INTEGER NOT NULL,

EndDate INTEGER NOT NULL

);

ALTER TABLE UslLgots

ADD CONSTRAINT XPKUslLgots PRIMARY KEY (Code);

CREATE TABLE UslProps (

Code INTEGER NOT NULL,

PKey INTEGER NOT NULL,

Tag INTEGER NOT NULL,

ValInteger INTEGER,

ValFloat FLOAT,

BegDate DATE_TYPE,

EndDate DATE_TYPE

);

ALTER TABLE UslProps

ADD CONSTRAINT XPKUslProps PRIMARY KEY (Code);

CREATE TRIGGER UslProps_BUH FOR UslProps

BEFORE UPDATE POSITION 0

AS

BEGIN

/* Изменение BegDate */

IF (new.BegDate <> old.BegDate) THEN

BEGIN

IF (new.BegDate < old.BegDate) THEN

BEGIN

/* Расширение BegDate */

UPDATE UslProps

SET EndDate = new.BegDate

WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение BegDate */

UPDATE UslProps

SET EndDate = new.BegDate

WHERE ((EndDate = old.BegDate) AND (PKey = new.PKey));

END

END

/* Изменение EndDate */

IF (new.EndDate <> old.EndDate) THEN

BEGIN

IF (new.EndDate > old.EndDate) THEN

BEGIN

/* Расширение EndDate */

UPDATE UslProps

SET BegDate = new.EndDate

WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey));

END

ELSE

BEGIN

/* Сужение EndDate */

UPDATE UslProps

SET BegDate = new.EndDate

WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey));

END

END

/* Сборка мусора */

DELETE FROM UslProps

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code));

END ^

CREATE TRIGGER UslProps_BIH FOR UslProps

BEFORE INSERT POSITION 0

AS

BEGIN

DELETE FROM UslProps

WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey));