Решение любой задачи, связанной с автоматизацией производственных процессов, безусловно, начинается с анализа реальных действий работников данной сферы и изучения общепринятой практики в той же области. Эти наблюдения позволяют сформировать основные представления о том, что нам нужно и чего не может быть, ну прям совсем. Однако, эта идиллическая картинка, как правило, грубо разбивается об разгильдяйство проектировщика (зачастую просто не хватает кругозора или времени на изучение подробностей) и "дикость рынка", заставляющая заказчика менять правила игры в зависимости от сиюсекундных обстоятельств. Кроме того, иногда возникает необходимость "растянуть" решение на смежные области.
Как правило, все эти неприятности выливаются в создание "костылей" на уже работающем проекте. С увеличением их количества, растут и "глюки" системы. Начинаются непредвиденные "тормоза", связанные с изменениями основной структуры данных, не имеющих четких логических связей. Возникает необходимость в регулярном обновлении клиентского программного обеспечения и логика на одной рабочей станции может в корне отличаться от соседней, где не успели обновить софт. Заказчик начинает банально терять деньги на простоях (особенно, во "фронтофисном" приложении, напрямую связанном с производственным процессом) и прямых затратах на наращивании серверных мощностей. В результате, наша система, из помощника, превращается в не сильно нужный придаток.
Оставим разборки на тему "кто виноват?" философам и обратим свой пылающий взор в направлении "что делать?".
Структура и платформа проекта
Начнем с основного... По возможности, не размещайте бизнес логику внутри клиентского приложения. Эпоха десктопных систем давно канула в лету и в современном мире рулят серверные СУБД. Абсолютное большинство из них имеют возможность использования процедур и функций, как на встроенном PSQL, так и с привязкой внешних библиотек. Такой подход избавит вас от необходимости обновлять программу на клиентских машинах, при каждом изменении в процессах. Ваше приложение становится банальным пользовательским интерфейсом к Базе Данных.
При выборе СУБД, если она явно не указана заказчиком, помните, что "стрельба из пушек по воробьям" чаще всего приводит к сложностям в развертывании и обслуживании, при мизерном выигрыше в производительности. Оцените свои реальные требования к внутреннему функционалу платформы, не забывая учесть, что механизмы управления транзакциями и репликацией данных (буде таковая понадобится) самые сложные и, как следствие, относительно ненадежные в абсолютно любом движке БД. Возвращаясь к нашему клиентскому приложению, сделайте так, чтобы операции чтения и редактирования данных проходили с минимальными блокировками или вообще без оных. При оценке потенциальной производительности, больше ориентируйтесь на возможность разнесения своих наборов данных на разные дисковые подсистемы, а не на рекламу от производителя (чисто для примера, фирма Oracle пробилась на рынок благодаря голословному утверждению о высокой надежности своей СУБД). И снова - не стреляйте в воробьев!
Структура данных
Еще немного прописных истин, или второе лирическое отступление...
Делите данные по таблицам исходя из следующих простых критериев:
Основные данные должны быть максимально компактны по структуре и содержать лишь необходимый минимум информации
Где бы ни была размещена ваша таблица, чем больше она содержит полей, тем большее время требуется серверу, чтобы банально прочитать ее с диска. При построении отчетов, она может дополняться данными дополнительных таблиц, функций и представлений, часть из которых будут черпать информацию из других физических источников.
Это общее правило, но оно не противоречит нашим "костыльным" задачам - данный подход позволяет сохранить нетронутыми базовые структуры, сохраняя (в этой части) качество работы системы.
Создавайте компактные первичные ключи
На мой непритязательный вкус, банальное автоинкрементное поле для всех записей, не имеющих других компактных критериев уникальности - самый оптимальный метод. Если вы связываете две таблицы по какому-нибудь строковому (да еще и часто изменяемому полю), и при связывании разных таблиц, и при модификации ключевой записи, вы получите весьма ощутимую нагрузку и на диск, и на процессор многострадального сервера.
Вспоминаем про костыли... Этот пункт дополняет предыдущий, позволяя легко и эффективно привязывать новые дополнительные наборы данных.
Промежуточное "итого"
Выполнение двух предыдущих пунктов уже сильно упрощает вопрос поддержки и развития системы - если нам необходимо добавить дополнительной информации, мы просто вводим вспомогательные связанные таблицы, данные из которых будут фигурировать в новых отчетах, не затрагивая оригинальную аналитику; а размещение бизнес логики внутри СУБД позволяет изменить ее поведение за одну транзакцию.
"Произвольная" логика
Но что если логика работы системы зависит от дополнительных факторов и не может быть описана простой функцией или условием?
Давайте определимся - где нам нужны дополнительные "костыли"? По всей вероятности это произойдет в момент ввода данных или во время рутинных расчетов. Ведь отчеты, как правило, содержат агрегированную информацию и при их формировании изменения в выводимых данных потребуют значительного времени. И происходить это будет каждый раз, когда пользователь пожелает глянуть на аналитику. И это только отчеты! А ведь может быть и другая активность системы, не связанная напрямую с хранением данных. В то же время, процесс добавления или изменения одной записи достаточно быстротечен и добавив немного вычислений в его тело, мы не сильно потеряем в "отзывчивости" системы.
Что до технологий, возможность выполнять произвольные действия заложена в выполнении текстового кода на уровне PSQL. То есть, нам достаточно прописать свой набор "процедур" в отдельной таблице и вызывать их в соответствующих процедурах и триггерах при достижении условий, заявленных в таблицах "фильтрах".
Практика
Рассмотрим вариант реализации данной технологии на самом "лобовом" примере - бухгалтерской "главной книге". Заодно, снимем некоторые вопросы о "мощностях и производительности" - будем делать ее на прабабушке современных СУБД - Interbase, а точнее на ее клоне - Firebird.
Чем привлекательна эта задача? Самой занудной темой на постсоветском пространстве - "планом счетов", который никак не эволюционирует от "совкового" бух.учета во что-нибудь "рыночное" и свободное. Таким образом, мы имеем напаханное поле условностей уже в базовой задаче.
Когда господа средневековые итальянцы придумывали этот способ учета финансов, им и в страшном сне не снились всяческие "Налоговые Инспекции" и количества счетов, сильно превышающие возможности расчета на "калькуляторе" того времени - счетах. Мир был прост и прекрасен... Но именно они заложили две базовых структуры - оборотно-сальдовую ведомость и атомарные транзакции, влияющие на ее содержимое. По сути, в этой гениальной простоте и кроется успех всей концепции.
Мы тоже не будем мудрить и в самой упрощенной форме повторим их творение.
Из чего состоит главная книга? Упрощенно:
- балансовый счет, показывающий знающему человеку - о какой статье доходов/расходов идет речь;
- дата актуальности данной записи;
- входящий остаток на этот день;
- сумма денег, которые в этот день легли на данный счет;
- сумма денег, снятая с этого счета в этот день;
- исходящий остаток;
- и, чисто для приличия, дата предыдущих манипуляций с этим счетом.
Сразу обращаем внимание на "балансовый счет". По крайней мере, в современных условиях он имеет два представления - собственно номер счета, отвечающий за глобальную финансовую статью и субсчета, разбивающие его на мелкие подробности (поступление денег = сумма(поступления от Вани, Клавы и Азраила)). Таким образом, мы имеем два уровня отчетности, которые будем называть балансом (суммы движений по глобальной статье) и оборотно-сальдовой ведомостью (детальная разбивка по субсчетам с теми же данными). В реальной жизни, ситуация с большим количеством субсчетов, при относительно малом количестве счетов верхнего (первого порядка) - норма, но мы сейчас не будем вдаваться в эти сложности и не станем агрегировать детальную таблицу в дополнительную - пусть топ менеджмент немного подождет расчета агрегатных значений.
Позволим себе и еще одно упрощение - дело в том, что сальдо считается не только по деньгам, но и по "внебалансовым" и валютным счетам, на которых обороты и остатки считаются в других величинах. Мы просто добавим четыре дополнительных "денежных" поля в основную таблицу.
Итак, структура справочника счетов будет выглядеть следующим образом:
CREATE TABLE ACCOUNTS(
ID DOM_SMALL_ID NOT NULL,
NUM DOM_ACCOUNT NOT NULL,
DESCRIPTION DOM_TITLE NOT NULL,
CONSTRAINT PK_ACCOUNTS PRIMARY KEY (NUM)
);
CREATE TABLE SUBACCOUNTS(
ID DOM_ID NOT NULL,
ACCOUNT DOM_ACCOUNT NOT NULL,
NUM DOM_ACCOUNT NOT NULL,
DESCRIPTION DOM_TITLE NOT NULL,
MEASURE DOM_SMALL_ID NOT NULL,
CLIENT DOM_ID, REPRESENTATION DOM_SHORT_TITLE NOT NULL,
CONSTRAINT PK_SUBACCOUNTS PRIMARY KEY (ID)
);
ALTER TABLE SUBACCOUNTS ADD CONSTRAINT FK_SUBACCOUNTS_A FOREIGN KEY (ACCOUNT) REFERENCES ACCOUNTS (NUM) ON UPDATE CASCADE;
Структуру сальдовки упростим до:
CREATE TABLE BALANCE(
ACCOUNT DOM_ID NOT NULL,
ON_DATE DOM_DATE NOT NULL,
INBOUND DOM_MONEY NOT NULL,
DEBIT DOM_MONEY NOT NULL,
CREDIT DOM_MONEY NOT NULL,
BALANCE DOM_MONEY NOT NULL,
M_INBOUND DOM_MONEY NOT NULL,
M_DEBIT DOM_MONEY NOT NULL,
M_CREDIT DOM_MONEY NOT NULL,
M_BALANCE DOM_MONEY NOT NULL,
LAST_OPERATION DOM_DATE,
CONSTRAINT PK_BALANCE PRIMARY KEY (ACCOUNT,ON_DATE)
);
ALTER TABLE BALANCE ADD CONSTRAINT FK_BALANCE_A FOREIGN KEY (ACCOUNT) REFERENCES SUBACCOUNTS (ID) ON UPDATE CASCADE ON DELETE CASCADE;Мы не будем разбивать входящий и исходящий остатки на дебет и кредит. Просто воспользуемся знаком. Первичный ключ сформируем по двум основным критериям субсчет и дата. При этом, балансовые отчеты добываются из:
create view BALANCE_A (ACCOUNT, ON_DATE, INBOUND, DEBIT, CREDIT, BALANCE,
M_INBOUND, M_DEBIT, M_CREDIT, M_BALANCE, LAST_OPERATION, SPLIT_BALANCE)
as
select s.ACCOUNT, b.ON_DATE,
sum(b.INBOUND) as INBOUND,
sum(b.DEBIT) as DEBIT,
sum(b.CREDIT) as CREDIT,
sum(b.BALANCE) as BALANCE,
sum(b.M_INBOUND) as M_INBOUND,
sum(b.M_DEBIT) as M_DEBIT,
sum(b.M_CREDIT) as M_CREDIT,
sum(b.M_BALANCE) as M_BALANCE,
max(b.LAST_OPERATION) as LAST_OPERATION,
max(m.SPLIT_BALANCE) as SPLIT_BALANCE
from SUBACCOUNTS as s
inner join MEASURES as m on m.ID = s.MEASURE
inner join BALANCE as b on b.ACCOUNT = s.ID
group by s.ACCOUNT, b.ON_DATE;
create view BALANCE_S (ID, REPRESENTATION, ACCOUNT, NUM, ON_DATE, INBOUND, DEBIT, CREDIT, BALANCE,
M_INBOUND, M_DEBIT, M_CREDIT, M_BALANCE, LAST_OPERATION, DESCRIPTION, MEASURE, SPLIT_BALANCE, CLIENT_ID, CLIENT, TAX_ID)
as
select s.ID, s.REPRESENTATION, s.ACCOUNT, s.NUM, b.ON_DATE,
b.INBOUND, b.DEBIT, b.CREDIT, b.BALANCE,
b.M_INBOUND, b.M_DEBIT, b.M_CREDIT, b.M_BALANCE,
b.LAST_OPERATION, s.DESCRIPTION, m.CODE as MEASURE, m.SPLIT_BALANCE,
s.CLIENT as CLIENT_ID, c.TITLE as CLIENT, c.TAX_ID
from SUBACCOUNTS as s
left outer join CLIENTS as c on c.ID = s.CLIENT
inner join MEASURES as m on m.ID = s.MEASURE
inner join BALANCE as b on b.ACCOUNT = s.ID;
Обратите внимание! Мы пересчитываем сальдо на каждый божий день. Это позволяет существенно ускорить процесс формирования отчетов, хоть и сильно раздувает размер БД. В принципе, это вопрос вкуса и технологий - для некоторых реализаций СУБД вполне приемлемым вариантом будет построение отчета на базе таблицы дневных оборотов, или сокращение количества записей в оборотно-сальдовой ведомости до значимых, без нулевых оборотов. К слову о таблице дневных оборотов...
CREATE TABLE CASH_FLOW
(
ACCOUNT DOM_ID NOT NULL,
ON_DATE DOM_DATE NOT NULL,
DEBIT DOM_MONEY NOT NULL,
CREDIT DOM_MONEY NOT NULL,
BALANCE DOM_MONEY NOT NULL,
M_DEBIT DOM_MONEY NOT NULL,
M_CREDIT DOM_MONEY NOT NULL,
M_BALANCE DOM_MONEY NOT NULL,
CONSTRAINT PK_CASH_FLOW PRIMARY KEY (ACCOUNT,ON_DATE)
);
ALTER TABLE CASH_FLOW ADD CONSTRAINT FK_CASH_FLOW_A
FOREIGN KEY (ACCOUNT) REFERENCES SUBACCOUNTS (ID) ON UPDATE CASCADE ON DELETE CASCADE;
По сути, она является буферной и данные в ней меняются тригерами обслуживания таблицы транзакций. Агрегированные значения оборотов по счетам позволяют пересчитывать главную книгу значительно быстрее, нежели на основании первичных записей. В том числе, это касается и дат предыдущих операций, фигурирующих в отчете.
От лирики - к делу
Оставим в стороне непосредственно транзакции и дополнительные справочные таблицы и перейдем к сути нашего повествования.
Если вы внимательно смотрели на структуру таблиц счетов, вы наверное уже успели покрыть меня матом и загаром за отсутствие критериев проверки состояния счета - является ли он активным или пассивным. Вот тут мы и блестнем "кастомной логикой".
В нашем глобальном бардаке, каждый "счетовод", мнящий себя "бухгалтером" желает быть "умнее системы". Иногда это выливается в "а давайте я пересчитаю ДРАФТ баланса, пока еще не все документы введены в систему". "Ваша система - море фекалий, если она считает, что расходы не могут превышать все имеющиеся средства!". Ой!
А мы сделаем "финт ушами" дадим страдальцу самому выбирать вариант действий в таких ситуациях. Мало того! У него будет возможность делать это на любом уровне и с любыми счетами в отдельности! Дядя (или тетя) сможет почувствовать себя "великим программистом", "повелителем виндоффс" и просто "гигантом мысли".
CREATE TABLE PROCS
(
ID DOM_SMALL_ID NOT NULL,
DESCRIPTION DOM_SHORT_TITLE NOT NULL,
BODY blob sub_type 1 NOT NULL,
CONSTRAINT PK_PROCS PRIMARY KEY (ID)
);
CREATE TABLE ACC_BC
(
ID DOM_ID NOT NULL,
ACCOUNT DOM_ACCOUNT NOT NULL,
INBOUND_F DOM_MONEY,
INBOUND_T DOM_MONEY,
DEBIT_F DOM_MONEY,
DEBIT_T DOM_MONEY,
CREDIT_F DOM_MONEY,
CREDIT_T DOM_MONEY,
BALANCE_F DOM_MONEY,
BALANCE_T DOM_MONEY,
M_INBOUND_F DOM_MONEY,
M_INBOUND_T DOM_MONEY,
M_DEBIT_F DOM_MONEY,
M_DEBIT_T DOM_MONEY,
M_CREDIT_F DOM_MONEY,
M_CREDIT_T DOM_MONEY,
M_BALANCE_F DOM_MONEY,
M_BALANCE_T DOM_MONEY,
E_ORDER DOM_ORDER NOT NULL,
PROC_ID DOM_SMALL_ID NOT NULL,
CONSTRAINT PK_ACC_BC PRIMARY KEY (ID)
);
ALTER TABLE ACC_BC ADD CONSTRAINT FK_ACC_BC_A
FOREIGN KEY (ACCOUNT) REFERENCES ACCOUNTS (NUM) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE ACC_BC ADD CONSTRAINT FK_ACC_BC_P
FOREIGN KEY (PROC_ID) REFERENCES PROCS (ID) ON UPDATE CASCADE;
CREATE TABLE SUBACC_BC
(
ID DOM_ID NOT NULL,
ACCOUNT DOM_ID NOT NULL,
INBOUND_F DOM_MONEY,
INBOUND_T DOM_MONEY,
DEBIT_F DOM_MONEY,
DEBIT_T DOM_MONEY,
CREDIT_F DOM_MONEY,
CREDIT_T DOM_MONEY,
BALANCE_F DOM_MONEY,
BALANCE_T DOM_MONEY,
M_INBOUND_F DOM_MONEY,
M_INBOUND_T DOM_MONEY,
M_DEBIT_F DOM_MONEY,
M_DEBIT_T DOM_MONEY,
M_CREDIT_F DOM_MONEY,
M_CREDIT_T DOM_MONEY,
M_BALANCE_F DOM_MONEY,
M_BALANCE_T DOM_MONEY,
E_ORDER DOM_ORDER NOT NULL,
PROC_ID DOM_SMALL_ID NOT NULL,
CONSTRAINT PK_SUBACC_BC PRIMARY KEY (ID)
);
ALTER TABLE SUBACC_BC ADD CONSTRAINT FK_SUBACC_BC_A
FOREIGN KEY (ACCOUNT) REFERENCES SUBACCOUNTS (ID) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE SUBACC_BC ADD CONSTRAINT FK_SUBACC_BC_P
FOREIGN KEY (PROC_ID) REFERENCES PROCS (ID) ON UPDATE CASCADE;
Ведь как все просто - собственная библиотека процедур обработки и два набора фильтров (по уровню счета) их применения. Да и обработка не так уж сложна...
create procedure SP_CALC_BALANCE_D (
ON_DATE DOM_DATE
)
as
declare ID DOM_ID;
declare ACCOUNT DOM_ACCOUNT;
declare NUM DOM_ACCOUNT;
declare INBOUND DOM_MONEY;
declare DEBIT DOM_MONEY;
declare CREDIT DOM_MONEY;
declare BALANCE_ DOM_MONEY;
declare M_DEBIT DOM_MONEY;
declare M_CREDIT DOM_MONEY;
declare M_INBOUND DOM_MONEY;
declare M_BALANCE DOM_MONEY;
declare LAST_OPERATION DOM_DATE;
declare R_NUM DOM_ORDER;
begin
delete from RECALC_BALANCE;
R_NUM = 0;
for select s.ID, s.ACCOUNT, s.NUM,
coalesce(b.BALANCE, 0) as INBOUND,
coalesce(c.DEBIT, 0) as DEBIT, coalesce(c.CREDIT, 0) as CREDIT,
coalesce(c.BALANCE, 0) + coalesce(b.BALANCE, 0) as BALANCE,
coalesce(c.M_DEBIT, 0) as M_DEBIT, coalesce(c.M_CREDIT, 0) as M_CREDIT,
coalesce(b.M_BALANCE, 0) as M_INBOUND,
coalesce(c.M_BALANCE, 0) + coalesce(b.M_BALANCE, 0) as M_BALANCE,
case when b.DEBIT = 0 and b.CREDIT = 0 and b.M_DEBIT = 0 and b.M_CREDIT = 0
then b.LAST_OPERATION
else dateadd(-1 day to :ON_DATE) end as LAST_OPERATION
from SUBACCOUNTS as s
left outer join CASH_FLOW as c on c.ACCOUNT = s.ID and c.ON_DATE = :ON_DATE
left outer join BALANCE as b on b.ACCOUNT = s.ID
and b.ON_DATE = dateadd(-1 day to :ON_DATE)
into ID, ACCOUNT, NUM, INBOUND, DEBIT, CREDIT, BALANCE_,
M_DEBIT, M_CREDIT, M_INBOUND, M_BALANCE, LAST_OPERATION
do begin
select R_DEBIT, R_CREDIT, R_BALANCE_,
R_M_DEBIT, R_M_CREDIT, R_M_BALANCE, R_LAST_OPERATION
from SP_CALC_BALANCE_DP(:ID, :ACCOUNT, :NUM, :ON_DATE,
:INBOUND, :DEBIT, :CREDIT, :BALANCE_,
:M_INBOUND, :M_DEBIT, :M_CREDIT, :M_BALANCE,
:LAST_OPERATION, :R_NUM)
into DEBIT, CREDIT, BALANCE_, M_DEBIT, M_CREDIT, M_BALANCE, LAST_OPERATION;
update or insert into BALANCE (ACCOUNT, ON_DATE, INBOUND, DEBIT, CREDIT, BALANCE,
M_INBOUND, M_DEBIT, M_CREDIT, M_BALANCE, LAST_OPERATION)
values (:ID, :ON_DATE, :INBOUND, :DEBIT, :CREDIT, :BALANCE_,
:M_INBOUND, :M_DEBIT, :M_CREDIT, :M_BALANCE, :LAST_OPERATION)
matching (ACCOUNT, ON_DATE);
end
while (exists(select * from RECALC_BALANCE)) do begin
R_NUM = R_NUM + 1;
delete from RECALC_BUFF;
if (R_NUM < 10) then begin
insert into RECALC_BUFF (ACCOUNT)
select distinct ACCOUNT from RECALC_BALANCE;
delete from RECALC_BALANCE;
for select s.ACCOUNT, a.ACCOUNT, a.NUM,
coalesce(b.BALANCE, 0) as INBOUND,
coalesce(c.DEBIT, 0) as DEBIT, coalesce(c.CREDIT, 0) as CREDIT,
coalesce(c.BALANCE, 0) + coalesce(b.BALANCE, 0) as BALANCE,
coalesce(c.M_DEBIT, 0) as M_DEBIT, coalesce(c.M_CREDIT, 0) as M_CREDIT,
coalesce(b.M_BALANCE, 0) as M_INBOUND,
coalesce(c.M_BALANCE, 0) + coalesce(b.M_BALANCE, 0) as M_BALANCE,
case when b.DEBIT = 0 and b.CREDIT = 0 and b.M_DEBIT = 0 and b.M_CREDIT = 0
then b.LAST_OPERATION
else dateadd(-1 day to :ON_DATE) end as LAST_OPERATION
from RECALC_BUFF as s
inner join SUBACCOUNTS as a on a.ID = s.ACCOUNT
left outer join CASH_FLOW as c on c.ACCOUNT = s.ACCOUNT and c.ON_DATE = :ON_DATE
left outer join BALANCE as b on b.ACCOUNT = s.ACCOUNT
and b.ON_DATE = dateadd(-1 day to :ON_DATE)
into ID, ACCOUNT, NUM, INBOUND, DEBIT, CREDIT, BALANCE_,
M_DEBIT, M_CREDIT, M_INBOUND, M_BALANCE, LAST_OPERATION
do begin
select R_DEBIT, R_CREDIT, R_BALANCE_, R_M_DEBIT, R_M_CREDIT, R_M_BALANCE,
R_LAST_OPERATION
from SP_CALC_BALANCE_DP(:ID, :ACCOUNT, :NUM, :ON_DATE,
:INBOUND, :DEBIT, :CREDIT, :BALANCE_,
:M_INBOUND, :M_DEBIT, :M_CREDIT, :M_BALANCE,
:LAST_OPERATION, :R_NUM)
into DEBIT, CREDIT, BALANCE_, M_DEBIT, M_CREDIT, M_BALANCE, LAST_OPERATION;
update or insert into BALANCE (ACCOUNT, ON_DATE,
INBOUND, DEBIT, CREDIT, BALANCE,
M_INBOUND, M_DEBIT, M_CREDIT, M_BALANCE, LAST_OPERATION)
values (:ID, :ON_DATE,
:INBOUND, :DEBIT, :CREDIT, :BALANCE_,
:M_INBOUND, :M_DEBIT, :M_CREDIT, :M_BALANCE, :LAST_OPERATION)
matching (ACCOUNT, ON_DATE);
end
end else exception exc_recursion;
end
end
Тут мы пересчитываем весь отчет за день, но, для каждой строки, выполняем проверку или модификацию через набор собственных обработок. Возврашаемые обработчиком значения, либо копируют исходные данные, либо отражают произведенные процедурой изменения (например, при переоценке валютных счетов). Для возможного изменения баланса на других счетах, обработка может забросить ID требуемого счета в глобальную временную таблицу. При этом, главная процедура расчета прогонит до десятка псевдорекурсивных обработок.
И, собственно, волшебная процедура...
create procedure SP_CALC_BALANCE_DP (
ID DOM_ID,
ACCOUNT DOM_ACCOUNT,
NUM DOM_ACCOUNT,
ON_DATE DOM_DATE,
INBOUND DOM_MONEY,
DEBIT DOM_MONEY,
CREDIT DOM_MONEY,
BALANCE_ DOM_MONEY,
M_INBOUND DOM_MONEY,
M_DEBIT DOM_MONEY,
M_CREDIT DOM_MONEY,
M_BALANCE DOM_MONEY,
LAST_OPERATION DOM_DATE,
R_NUM DOM_ORDER
)
returns (
R_DEBIT DOM_MONEY,
R_CREDIT DOM_MONEY,
R_BALANCE_ DOM_MONEY,
R_M_DEBIT DOM_MONEY,
R_M_CREDIT DOM_MONEY,
R_M_BALANCE DOM_MONEY,
R_LAST_OPERATION DOM_DATE
)
as
declare P DOM_SMALL_ID;
declare E DOM_ORDER;
declare PB blob sub_type text;
begin
R_DEBIT = DEBIT;
R_CREDIT = CREDIT;
R_BALANCE_ = BALANCE_;
R_M_DEBIT = M_DEBIT;
R_M_CREDIT = M_CREDIT;
R_M_BALANCE = M_BALANCE;
R_LAST_OPERATION = LAST_OPERATION;
delete from RECALC_BALANCE_PROCS;
insert into RECALC_BALANCE_PROCS (PROC_ID, E_ORDER)
select PROC_ID, min(E_ORDER)
from SUBACC_BC
where ACCOUNT = :ID
and :INBOUND between coalesce(INBOUND_F, -1e15) and coalesce(INBOUND_T, 1e15)
and :DEBIT between coalesce(DEBIT_F, -1e15) and coalesce(DEBIT_T, 1e15)
and :CREDIT between coalesce(CREDIT_F, -1e15) and coalesce(CREDIT_T, 1e15)
and :BALANCE_ between coalesce(BALANCE_F, -1e15) and coalesce(BALANCE_T, 1e15)
group by PROC_ID;
insert into RECALC_BALANCE_PROCS (PROC_ID, E_ORDER)
select PROC_ID, min(E_ORDER)
from ACC_BC
where ACCOUNT = :ACCOUNT and PROC_ID not in (select PROC_ID from RECALC_BALANCE_PROCS)
and :INBOUND between coalesce(INBOUND_F, -1e15) and coalesce(INBOUND_T, 1e15)
and :DEBIT between coalesce(DEBIT_F, -1e15) and coalesce(DEBIT_T, 1e15)
and :CREDIT between coalesce(CREDIT_F, -1e15) and coalesce(CREDIT_T, 1e15)
and :BALANCE_ between coalesce(BALANCE_F, -1e15) and coalesce(BALANCE_T, 1e15)
group by PROC_ID;
while (exists(select * from RECALC_BALANCE_PROCS)) do begin
select first 1 PROC_ID, E_ORDER
from RECALC_BALANCE_PROCS
order by E_ORDER
into P, E;
select BODY from PROCS where ID = :P into PB;
execute statement ('execute block (
ACCOUNT DOM_ACCOUNT = ?,
NUM DOM_ACCOUNT = ?,
ID DOM_ID = ?,
ON_DATE DOM_DATE = ?,
INBOUND DOM_MONEY = ?,
DEBIT DOM_MONEY = ?,
CREDIT DOM_MONEY = ?,
BALANCE_ DOM_MONEY = ?,
M_INBOUND DOM_MONEY = ?,
M_DEBIT DOM_MONEY = ?,
M_CREDIT DOM_MONEY = ?,
M_BALANCE DOM_MONEY = ?,
LAST_OPERATION DOM_DATE = ?,
PROC_ID DOM_SMALL_ID = ?,
E_ORDER DOM_ORDER = ?
R_NUM DOM_ORDER = ?
)
returns (
R_DEBIT DOM_MONEY,
R_CREDIT DOM_MONEY,
R_BALANCE_ DOM_MONEY,
R_M_DEBIT DOM_MONEY,
R_M_CREDIT DOM_MONEY,
R_M_BALANCE DOM_MONEY,
R_LAST_OPERATION DOM_DATE
)
as
' || :PB) (:ACCOUNT, :NUM, :ID, :ON_DATE,
:INBOUND, :R_DEBIT, :R_CREDIT, :R_BALANCE_,
:M_INBOUND, :R_M_DEBIT, :R_M_CREDIT, :R_M_BALANCE,
:R_LAST_OPERATION, :P, :E, R_NUM)
with caller privileges
into :DEBIT, :CREDIT, :BALANCE_, :M_DEBIT, :M_CREDIT, :M_BALANCE, :LAST_OPERATION;
R_DEBIT = coalesce(DEBIT, :R_DEBIT);
R_CREDIT = coalesce(CREDIT, :R_CREDIT);
R_BALANCE_ = coalesce(BALANCE_, :R_BALANCE_);
R_M_DEBIT = coalesce(M_DEBIT, :R_M_DEBIT);
R_M_CREDIT = coalesce(M_CREDIT, :R_M_CREDIT);
R_M_BALANCE = coalesce(M_BALANCE, :R_M_BALANCE);
R_LAST_OPERATION = coalesce(LAST_OPERATION, :R_LAST_OPERATION);
delete from RECALC_BALANCE_PROCS where PROC_ID = :P and E_ORDER = :E;
end
suspend;
end
Сначала подбираем подходящие условия по фильтру для правил конкретного субсчета, потом добавляем в них правила для балансового счета и радостно выполняем инструкции счетовода, которые могут быть описаны так:
insert into PROCS (DESCRIPTION, BODY)
values ('не менее нуля жестко', 'begin if (:BALANCE_ < 0) then exception ''negative balance''; end');
insert into PROCS (DESCRIPTION, BODY)
values ('не менее нуля, предупреждение', 'begin if (:BALANCE_ < 0) then post_event(''negative balance''); end');
Примеры кода заплаток конечно примитивны (да и проверка условий - липа, ибо она производится на уровне таблицы-фильтра), но из тела процедуры обработки (SP_CALC_BALANCE_DP) видно, что они могут содержать вполне серьезный код, вплоть до декларации собственных, внутренних переменных, курсоров, циклов и т.п., а так же дополнительные проверки условий выполнения, описаные уже не цифрами, но кодом.
Глобальное "итого"
Таким образом, наша БД, "костылябельная" на классическом уровне логики хранимых процедур, превращается в "суперкостылябельную", с минимальным привлечением программиста к изменению условий в применении заплаток.
Данная методика применима и в более широком смысле - в системах, априори, ориентированых на произвольную обработку информации (наподобии телеметрии).
Из минусов этой технологии - каждая обработка работает в режиме интерпретации текстового блока, что немного больше грузит процессор относительно классических хранимых процедур. В то же время, таблицы условий могут ссылаться и на названия "железных" обработок, созданых в БД по единому стандарту (входных и выходных переметров). Вопрос вкуса и технологических возможностей СУБД...