Организация удаленного доступа к распределенным базам данных. Содержание Введение 3 Основные подходы к проектированию распределенных баз данных 5
 Скачать 0.97 Mb.
|
ЗаключениеЗа время работы над дипломным проектом по теме «Организация удаленного доступа к распределенным базам данных» были изучены теоретические основы построения распределенных информационных систем с возможностью оперативного удаленного доступа к данным. Результатом дипломного проектирования является информационная система для автоматизации расчетов с абонентами АО «Связьинформ» РМ. В ходе работы было проведено информационное моделирование объекта, построена структура баз данных, отвечающая предъявляемым требованиям, а также разработана архитектура информационной системы. Кроме того, было разработано программное обеспечение для автоматизации администрирования и решения задач удаленного доступа, удаленного управления и репликации данных. Отдельная глава посвящена технико-экономическому обоснованию данного дипломного проекта. Список литературы
Приложение 1SQL-скрипт для генерации базы данных 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)); UPDATE UslProps SET BegDate = new.EndDate WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey)); UPDATE UslProps SET EndDate = new.BegDate WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey)); END ^ CREATE TRIGGER UslProps_BDH FOR UslProps BEFORE DELETE POSITION 0 AS BEGIN UPDATE UslProps SET EndDate = old.EndDate WHERE ((EndDate = old.BegDate) AND (PKey = old.PKey)); END ^ CREATE TABLE UslPropsKeys ( Code INTEGER NOT NULL ); ALTER TABLE UslPropsKeys ADD CONSTRAINT XPKUslPropsKeys PRIMARY KEY (Code); CREATE TABLE Usls ( Code INTEGER NOT NULL, PKey INTEGER NOT NULL, Division INTEGER NOT NULL, UslType INTEGER NOT NULL, Name CHAR(64) NOT NULL, BegDate DATE_TYPE, EndDate DATE_TYPE ); CREATE INDEX XIEUslsName ON Usls ( Name ); ALTER TABLE Usls ADD CONSTRAINT XPKUsls PRIMARY KEY (Code); CREATE TRIGGER Usls_BUH FOR Usls BEFORE UPDATE POSITION 0 AS BEGIN /* Изменение BegDate */ IF (new.BegDate <> old.BegDate) THEN BEGIN IF (new.BegDate < old.BegDate) THEN BEGIN /* Расширение BegDate */ UPDATE Usls SET EndDate = new.BegDate WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey)); END ELSE BEGIN /* Сужение BegDate */ UPDATE Usls 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 Usls SET BegDate = new.EndDate WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey)); END ELSE BEGIN /* Сужение EndDate */ UPDATE Usls SET BegDate = new.EndDate WHERE ((BegDate = old.EndDate) AND (PKey = new.PKey)); END END /* Сборка мусора */ DELETE FROM Usls WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey) AND (Code <> new.Code)); END ^ CREATE TRIGGER Usls_BIH FOR Usls BEFORE INSERT POSITION 0 AS BEGIN DELETE FROM Usls WHERE ((BegDate >= new.BegDate) AND (EndDate <= new.EndDate) AND (PKey = new.PKey)); UPDATE Usls SET BegDate = new.EndDate WHERE ((new.EndDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey)); UPDATE Usls SET EndDate = new.BegDate WHERE ((new.BegDate BETWEEN BegDate AND EndDate) AND (PKey = new.PKey)); END ^ CREATE TRIGGER Usls_BDH FOR Usls BEFORE DELETE POSITION 0 AS BEGIN UPDATE Usls SET EndDate = old.EndDate WHERE ((EndDate = old.BegDate) AND (PKey = old.PKey)); END ^ CREATE TABLE UslsKeys ( Code INTEGER NOT NULL ); ALTER TABLE UslsKeys ADD CONSTRAINT XPKUslsKeys PRIMARY KEY (Code); CREATE TABLE UslTypes ( Code INTEGER NOT NULL, Name DESCR_TYPE ); ALTER TABLE UslTypes ADD CONSTRAINT XPKUslTypes PRIMARY KEY (Code); ALTER TABLE Nach ADD CONSTRAINT R_59 FOREIGN KEY (Usl) REFERENCES UslsKeys; ALTER TABLE Nach ADD CONSTRAINT R_57 FOREIGN KEY (Phone) REFERENCES PhonesKeys; ALTER TABLE Nach ADD FOREIGN KEY (Owner) REFERENCES PhonesOwnersKeys; ALTER TABLE NachBillings ADD CONSTRAINT R_65 FOREIGN KEY (Division) REFERENCES UslDivisionsKeys; ALTER TABLE NachBillings ADD FOREIGN KEY (BillDateCode) REFERENCES NachBillDates; ALTER TABLE NachBillings ADD FOREIGN KEY (Owner) REFERENCES PhonesOwnersKeys; ALTER TABLE NachConstUsl ADD CONSTRAINT R_60 FOREIGN KEY (Usl) REFERENCES UslsKeys; ALTER TABLE NachConstUsl ADD CONSTRAINT R_58 FOREIGN KEY (Phone) REFERENCES PhonesKeys; ALTER TABLE NachConstUsl ADD FOREIGN KEY (Owner) REFERENCES PhonesOwnersKeys; ALTER TABLE Phones ADD FOREIGN KEY (Owner) REFERENCES PhonesOwnersKeys; ALTER TABLE Phones ADD FOREIGN KEY (PKey) REFERENCES PhonesKeys; ALTER TABLE Phones ADD FOREIGN KEY (Street) REFERENCES PhonesStreets; ALTER TABLE PhonesOwners ADD FOREIGN KEY (PostStation) REFERENCES PhonesPostStations; ALTER TABLE PhonesOwners ADD FOREIGN KEY (Street) REFERENCES PhonesStreets; ALTER TABLE PhonesOwners ADD FOREIGN KEY (Bank) REFERENCES PhonesBanks; ALTER TABLE PhonesOwners ADD FOREIGN KEY (Category) REFERENCES UslCatKeys; ALTER TABLE PhonesOwners ADD FOREIGN KEY (PKey) REFERENCES PhonesOwnersKeys; ALTER TABLE PhonesPostStations ADD CONSTRAINT R_62 FOREIGN KEY (Region) REFERENCES PhonesRegions; ALTER TABLE PhonesStations ADD FOREIGN KEY (Region) REFERENCES PhonesRegions; ALTER TABLE PhonesStreets ADD FOREIGN KEY (Region) REFERENCES PhonesRegions; ALTER TABLE PhonesStreets ADD FOREIGN KEY (Station) REFERENCES PhonesStations; ALTER TABLE Plat ADD CONSTRAINT R_61 FOREIGN KEY (ToUsl) REFERENCES UslsKeys; ALTER TABLE Plat ADD FOREIGN KEY (Owner) REFERENCES PhonesOwnersKeys; ALTER TABLE SysSettings ADD FOREIGN KEY (NullOwner) REFERENCES PhonesOwnersKeys; ALTER TABLE SysSettings ADD FOREIGN KEY (TimeTalksUsl) REFERENCES UslsKeys; ALTER TABLE Talks ADD FOREIGN KEY (ToPhone) REFERENCES PhonesKeys; ALTER TABLE Talks ADD FOREIGN KEY (Phone) REFERENCES PhonesKeys; ALTER TABLE Talks ADD FOREIGN KEY (DayCode) REFERENCES TalksPay; ALTER TABLE TalksPay ADD FOREIGN KEY (Phone) REFERENCES PhonesKeys; ALTER TABLE UslCat ADD FOREIGN KEY (PKey) REFERENCES UslCatKeys; ALTER TABLE UslDivisions ADD CONSTRAINT R_63 FOREIGN KEY (PKey) REFERENCES UslDivisionsKeys; ALTER TABLE UslLgots ADD CONSTRAINT R_50 FOREIGN KEY (Property) REFERENCES UslPropsKeys; ALTER TABLE UslLgots ADD FOREIGN KEY (Usl) REFERENCES UslsKeys; ALTER TABLE UslLgots ADD FOREIGN KEY (Category) REFERENCES UslCatKeys; ALTER TABLE UslProps ADD CONSTRAINT R_51 FOREIGN KEY (PKey) REFERENCES UslPropsKeys; ALTER TABLE Usls ADD CONSTRAINT R_64 FOREIGN KEY (Division) REFERENCES UslDivisionsKeys; ALTER TABLE Usls ADD FOREIGN KEY (UslType) REFERENCES UslTypes; ALTER TABLE Usls ADD FOREIGN KEY (PKey) REFERENCES UslsKeys; CREATE PROCEDURE PrGenUslPropsKeys RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genUslPropsKeys, 1); END ^ CREATE PROCEDURE PrGenUslProps RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genUslProps, 1); END ^ CREATE PROCEDURE PrGenPhonesRegions RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genPhonesRegions, 1); END ^ CREATE PROCEDURE PrGenPhonesStations RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genPhonesStations, 1); END ^ CREATE PROCEDURE PrGenPhonesStreets RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genPhonesStreets, 1); END ^ CREATE PROCEDURE PrGenPhonesBanks RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genPhonesBanks, 1); END ^ CREATE PROCEDURE PrGenTalksPay RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genTalksPay, 1); END ^ CREATE PROCEDURE PrGenTalks RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genTalks, 1); END ^ CREATE PROCEDURE PrGenNach RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genNach, 1); END ^ CREATE PROCEDURE PrGenNachBillings RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genNachBillings, 1); END ^ CREATE PROCEDURE PrGenNachBillDates RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genNachBillDates, 1); END ^ CREATE PROCEDURE PrGenNachConstUsl RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genNachConstUsl, 1); END ^ CREATE PROCEDURE PrGenUslDivisions RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genUslDivisions, 1); END ^ CREATE PROCEDURE PrGenUslLgots RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genUslLgots, 1); END ^ CREATE PROCEDURE PrGenUslsKeys RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genUslsKeys, 1); END ^ CREATE PROCEDURE PrGenUsls RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genUsls, 1); END ^ CREATE PROCEDURE PrGenUslCatKeys RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genUslCatKeys, 1); END ^ CREATE PROCEDURE PrGenUslCat RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genUslCat, 1); END ^ CREATE PROCEDURE PrGenPhones RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genPhones, 1); END ^ CREATE PROCEDURE PrGenPhonesOwnersKeys RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genPhonesOwnersKeys, 1); END ^ CREATE PROCEDURE PrGenPhonesOwners RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genPhonesOwners, 1); END ^ CREATE PROCEDURE PrGenSysSettings RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genSysSettings, 1); END ^ CREATE PROCEDURE PrGenPhonesKeys RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genPhonesKeys, 1); END ^ CREATE PROCEDURE PrGenPlat RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genPlat, 1); END ^ CREATE PROCEDURE PrGenPhonesPostStations RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genPhonesPostStations, 1); END ^ CREATE PROCEDURE PrGenSysLog RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genSysLog, 1); END ^ CREATE PROCEDURE PrGenUslTypes RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genUslTypes, 1); END ^ CREATE PROCEDURE PrGenUslDivisionsKeys RETURNS (ACode INTEGER) AS BEGIN ACode = GEN_ID(genUslDivisionsKeys, 1); END ^ ^ CREATE PROCEDURE TalksExamineOwner (APhoneNmb CHAR(7), ADate DATE) RETURNS (APhone INTEGER) AS DECLARE VARIABLE AStreet INTEGER; DECLARE VARIABLE NOwner INTEGER; DECLARE VARIABLE APhoneCode INTEGER; BEGIN SELECT PKey FROM Phones WHERE (PhoneNmb = :APhoneNmb) AND (:ADate BETWEEN BegDate AND EndDate) INTO :APhone; IF (:APhone IS NULL) THEN BEGIN SELECT NullOwner FROM SysSettings INTO :NOwner; SELECT Street FROM PhonesOwners WHERE (PKey = :NOwner) AND (:ADate BETWEEN PhonesOwners.BegDate AND PhonesOwners.EndDate) INTO :AStreet; IF (:AStreet IS NOT NULL) THEN BEGIN EXECUTE PROCEDURE PrGenPhonesKeys RETURNING_VALUES :APhone; INSERT INTO PhonesKeys (Code) VALUES (:APhone); INSERT INTO Phones(Owner, PKey, PhoneNmb, Street, InstallDate, RemoveDate, BegDate, EndDate) VALUES (:NOwner, :APhone, :APhoneNmb, :AStreet, :ADate, "12.12.2222", :ADate, "12.12.2222"); END END END ^ CREATE PROCEDURE TalksGetTax AS BEGIN EXIT; END ^ CREATE PROCEDURE TalksGetPay (APhone INTEGER, ADay DATE, ACallTime INTEGER, AHowLong INTEGER) RETURNS (APay FLOAT, ACalculated SMALLINT, IsLgot SMALLINT) AS DECLARE VARIABLE ATax FLOAT; DECLARE VARIABLE AProcNach FLOAT; DECLARE VARIABLE ATalksUsl INTEGER; DECLARE VARIABLE AOwner INTEGER; DECLARE VARIABLE ANalog FLOAT; BEGIN ACalculated = 0; SELECT TimeTalksUsl FROM SysSettings INTO :ATalksUsl; IF (:ATalksUsl IS NULL) THEN EXIT; SELECT Owner FROM Phones WHERE (PKey = :APhone) AND (:ADay BETWEEN BegDate AND EndDate) INTO :AOwner; IF (:AOwner IS NULL) THEN EXIT; EXECUTE PROCEDURE UslGetOwnerTax(:AOwner, :ATalksUsl, :ADay) RETURNING_VALUES :ATax, :AProcNach, :ANalog; IF (:ATax IS NULL) THEN EXIT; APay = ATax*AHowLong*AProcNach/3000; ACalculated = 1; END ^ CREATE PROCEDURE TalksCallBilling (APhone INTEGER, ACallDate DATE, ACallTime INTEGER, AHowLong INTEGER) RETURNS (ATalksPayCode INTEGER, ACalculated SMALLINT) AS DECLARE VARIABLE APay FLOAT; DECLARE VARIABLE PayCode INTEGER; DECLARE VARIABLE IsLgot SMALLINT; DECLARE VARIABLE TTime INTEGER; DECLARE VARIABLE LTime INTEGER; BEGIN EXECUTE PROCEDURE TalksGetPay(APhone, ACallDate, ACallTime, AHowLong) RETURNING_VALUES :APay, :ACalculated, :IsLgot; SELECT Code FROM TalksPay WHERE (Phone = :APhone) AND (CallDate = :ACallDate) INTO PayCode; IF (:ACalculated = 0) THEN EXIT; IF (:IsLgot = 0) THEN BEGIN TTime = AHowLong; LTime = 0; END ELSE BEGIN LTime = AHowLong; TTime = 0; END IF (:PayCode IS NULL) THEN BEGIN EXECUTE PROCEDURE PrGenTalksPay RETURNING_VALUES :PayCode; INSERT INTO TalksPay (Code, Phone, CallDate, TotalSum, TotalFullTime, TotalLgotTime) VALUES (:PayCode, :APhone, :ACallDate, :APay, :TTime, :LTime); END ELSE BEGIN UPDATE TalksPay SET TotalSum = TotalSum+:APay, TotalFullTime = TotalFullTime+:TTime, TotalLgotTime = TotalLgotTime+:LTime WHERE Code = :PayCode; END END ^ CREATE PROCEDURE UslGetOwnerTax(AOwner INTEGER, AUsl INTEGER, ADate DATE) RETURNS (ATax FLOAT, AProcNach FLOAT, ANalog FLOAT) AS DECLARE VARIABLE ACategory INTEGER; BEGIN SELECT Category FROM PhonesOwners WHERE (PKey = :AOwner) AND (:ADate BETWEEN BegDate AND EndDate) INTO :ACategory; SELECT Tax, NachCoeff, Nalog FROM UslLgots WHERE (Usl = :AUsl) AND (:ADate BETWEEN BegDate AND EndDate) INTO :ATax, :AProcNach, :ANalog; EXIT; END |