[Из песочницы] Проблемы слияния записей в сложносвязанной таблице Oracle

Предисловие

По роду своей деятельности, сопровождаю инфраструктуру небольшого парк-отеля, которая включает необходимые и не очень элементы.

Для любопытных людей которым интересны детали, осторожно, много текста

С декабря прошлого года, нам наконец внедрили дорогущую умную систему управления отелем.

Каждый элемент имеет свой собственный сервер и соответственно свою базу данных, функциональные части могут обмениваться между собой данными о клиентах и продажах.

Система состоит из трех модулей:

• Fidelio V8 — модуль управления гостиницей, вполне состоявшееся приложение. Имеет систему бронирования через сайт и от стойки. занимает приоритетное место слегка капризен, кучу отчетов, ведет статистику. В качестве базы данных используется Oracle 11gR2


• Micros 3700-модуль управления рестораном(работа на местах ведется через типовые POS моноблоки), интерактивная рассадка гостей, разделение чеков и много разных полезных вещей которые облегчают жизнь. Работает на Sybase.


• TNG- довольно молодой модуль для продажи услуг медицинского центра и спортивно-развлекательного комплекса, своя собственная система бронирования, клиентская база, регистрация клиентов etc. Работает на Oracle 11gR2. Весь пост, собственно и связан с этим модулем.

Однажды на пятничном совещании начальство сказало решить проблему тормозов базы данных модуля управления развлекательного комплекса и медицинского центра, «плавающих отчетов». Мы системные администраторы, наше дело маленькое — решать подобные проблемы.

Что там было

После проверки всех частей системы управления, все внимание обратилось на святая святых системы — базу данных, спасибо тому интегратору, который поставил галочку «Save Password» в SQL Developer.

Проблемы 1,2

1. Таблица CARDS(клиентские профайлы)- содержит много дублирующих друг друга записей, иногда количество дубликатов доходит до 8-10


2. в той же таблице отсутствуют дополнительные сведения о пользователях, некритичные, но облегчающие их персональную идентификацию, для начисления бонусов в дальнейшем

Каждая запись в таблице CARDS, тянет за собой записи в дочерних таблицах с предельно жестким обеспечением целостности данных сплошные Restrict и никаких Cascade, финансовая информация все же.

Замечу, что TNG, предоставляет свой инструмент для объединения клиентских профайлов, вот такой:



Тут есть одно большое «НО», или даже два:

1. За одно действие, нельзя объединить больше чем два профайла


2. Профайлы базы находятся не в «нормальном состоянии».

Решаем проблему 2

Для начала необходимо расставить «гендерные» признаки людям, ибо по дефолту в таблице это свойство не стояло как обязательное

пишем относительно простой SQL скрипт отдельно для мужчин, для женщин и юридических лиц, сначала тестируем скрипт на выборке

SELECT card_id,first_name,last_name,created_person,card_type_id,card_status_id,creation_date FROM cards
--card_id -id профайла
--first_name - имя
--last_name - фамилия
--created_person - кто создал
--card_type_id - тип 
--card_status_id -статус профайла(активна/заблокирована)
--creation_date -дата создания
where 
last_name like '%в' or
last_name like'%ий' or
last_name like '%о' or
last_name like'%ый' 
order by last_name;

Фамилий кончающихся на «о» меньшинство, и автоматически разделить людей нельзя и обновлять данные в базе для этой категории придется вручную, для остальных категорий пишем простой скрипт на обновление

update cards 
set gender = "м"
where
last_name like '%в' or
last_name like'%ий' or
last_name like '%о' or
last_name like'%ый' 
commit;
-- не забываем коммитить изменения в таблице, иначе клиентское приложение постоянно будет вылетать с ошибкой

Аналогично поступаем с профайлами женщин, предварительно тестируя скрипт через выборку

Скрипт аналогичный по содержанию предыдущему

SELECT card_id,first_name,last_name,created_person,card_type_id,card_status_id,creation_date FROM cards
where 
last_name like '%ва' or
last_name like'%ая' or
last_name like '%а' 
order by last_name;

После теста выборки обновляем, поле gender

update cards 
set gender = "ж"
where
last_name like '%ва' or
last_name like'%ая' or
last_name like '%а' ;
commit;

Разбираемся с фамилиями на букву «о», тестируем выборку

SELECT card_id,first_name,last_name,created_person,card_type_id,card_status_id,creation_date FROM cards
where 
last_name like '%o' and first_name like '%й' or first_name like '%р'
order by last_name;

обновляем гендерный признак для этих записей

update cards 
set gender = 'м' 
where 
last_name like '%o' and first_name like '%й' or first_name like '%р';
commit;

Получается что профайлы женщин на «о» остались без гендера поэтому обновление для них выглядит просто

update cards
set gender  = 'ж'
where 
last_name like '%o' and gender is null;
commit;

Получается что юридические лица остались единственными, кто имеет «null» gender,

обновляем их следующим образом

update cards
set gender = 'ю'
where gender is null;
commit;

Со второй проблемой разобрались.

Решаем проблему объединения профайлов

Так как таблица CARDS имеет множественные связи с другими таблицами то возникают некоторые сложности с объединением профайлов, результатом двухнедельных исследований базы(база создавалась не мной и как вы понимаете, понимание принципов ееработы потребовало некоторого времени) стала

следующая процедура

Карты объединяются по критерию:

UPPER(last_name), UPPER(first_name), birth_date, UPPER(second_name)

create or replace
PROCEDURE cards_merge AS 

vLast VARCHAR2(40);
vFirst VARCHAR2(40);
vSecond VARCHAR2(40);
vBirth DATE;
vCardId NUMBER;
vOrdr NUMBER;
vTargetCardId NUMBER;

-- ACC
vAcDefId NUMBER(6,0);
vLoanFlg CHAR(1);
vAmount NUMBER(19,4);
vCrdTtl NUMBER(19,4);
vLoanTtl NUMBER(19,4);
cntAccBallanceTargetCard NUMBER;
vBallance NUMBER(19,4);
vCredit NUMBER(19,4);
vLoan NUMBER(19,4);
--
cntBS NUMBER;

CURSOR curCards  IS 
select UPPER(last_name), UPPER(first_name), birth_date, UPPER(second_name) from cards group by UPPER(last_name), UPPER(first_name), birth_date, UPPER(second_name) having count(*) >1 order by UPPER(last_name);
CURSOR curMerge(pLast IN VARCHAR2, pFirst IN VARCHAR2, pSecond IN VARCHAR2, pBirth IN DATE)  IS  
select card_id, ROW_NUMBER() OVER (PARTITION BY '' ORDER BY card_id DESC) as ordr from CARDS
WHERE 
(UPPER(last_name)=pLast or (pLast is NULL and last_name is NULL)) 
and (UPPER(first_name)=pFirst or (pFirst is NULL and first_name is NULL)) 
and (birth_date = pBirth or (pBirth is NULL and birth_date is NULL)) 
and (UPPER(second_name)=pSecond or (pSecond is NULL and second_name is NULL)) 
ORDER BY card_id desc;

CURSOR curAcc(pSourceCardId IN NUMBER) IS  
select ac.ACCOUNT_DEF_ID, ac.loan_flag, ac.amount from ACCOUNTING ac, DOCS dc WHERE ac.doc_id=dc.doc_id 
and dc.COFIRMATION='T' and ac.CARD_ID=pSourceCardId;                                                                                                                             

BEGIN
OPEN curCards;
LOOP
FETCH curCards INTO VLAST, VFIRST, VBIRTH, VSECOND;
EXIT WHEN NOT curCards%FOUND;
 
OPEN curMerge(VLAST, VFIRST, VSECOND, VBIRTH);
LOOP
FETCH curMerge INTO vCardId, vOrdr;
EXIT WHEN NOT curMerge%FOUND;
              
 if vOrdr=1 then
 -- target card;
    vTargetCardId := vCardId;
    else
                  
  -- ACCOUNTING
     OPEN curAcc(vCardId);
     LOOP
     FETCH curAcc INTO vAcDefId, vLoanFlg, vAmount;
     EXIT WHEN NOT curAcc%FOUND;
                               
     if vAmount < 0 then
     vCrdTtl:=vAmount;
     else
     vCrdTtl:=0;
     end if;
                               
     if vLoanFlg='T' then
        vLoanTtl:=vAmount;
    else
        vLoanTtl:=0;
    end if;
 
select count(*) into cntAccBallanceTargetCard FROM accounting_balance ab 
  WHERE ab.card_id=vTargetCardId and ab.account_def_id=vAcDefId;
 if cntAccBallanceTargetCard =0 then
 INSERT INTO accounting_balance( ID, ACCOUNT_DEF_ID, CARD_ID, AMOUNT, CRD_TTL, LAST_EXPIRY, LOAN )
 values 
(ACCOUNTING_BALANCE_SEQUENCE.nextval, vAcDefId, vTargetCardId, vAmount, vCrdTtl, NULL, vLoanTtl);
 else
 select ab.amount, ab.crd_ttl, ab.loan into vBallance, vCredit, vLoan  FROM accounting_balance ab 
    WHERE ab.card_id=vTargetCardId and ab.account_def_id=vAcDefId;
    
UPDATE accounting_balance SET AMOUNT= vBallance+ vAmount, CRD_TTL=vCredit + vCrdTtl, LOAN=vLoan+ vLoanTtl 
     WHERE card_id=vTargetCardId and account_def_id=vAcDefId;
     end if;
     end loop;
     CLOSE curAcc;
                    
 UPDATE ACCOUNTING SET CARD_ID=vTargetCardId WHERE CARD_ID=vCardId;
 DELETE ACCOUNTING_BALANCE
 WHERE CARD_ID=vCardId;
 -- END ACCOUNTING 
 -- BONUS_TRIGGER_HISTORY
 UPDATE BONUS_TRIGGER_HISTORY SET CARD_ID=vTargetCardId 
WHERE CARD_ID=vCardId;
 -- END BONUS_TRIGGER_HISTORY
 -- BOOKING_RESOURCES
UPDATE BOOKING_RESOURCES SET RESOURCE_ID=vTargetCardId 
WHERE RESOURCE_ID=vCardId and RESOURCE_TYPE='P';
 -- END BOOKING_RESOURCES
-- CARD_FOREIGN_IDS

DELETE FROM CARD_FOREIGN_IDS WHERE CARD_ID=vCardId;
-- END CARD_FOREIGN_IDS
-- DOCS
UPDATE DOCS SET DEF_CARD_ID=vTargetCardId WHERE DEF_CARD_ID=vCardId;
 -- END DOCS
-- DSC_HISTORY
UPDATE DSC_HISTORY SET CARD_ID=vTargetCardId WHERE CARD_ID=vCardId;
-- END DSC_HISTORY
-- ITEM_ACCOUNTING
UPDATE ITEM_ACCOUNTING SET CARD_ID=vTargetCardId WHERE CARD_ID=vCardId;
update item_balance ib set ib.count= nvl(( select sum(ia.mi_count) from item_accounting ia, docs d 
where ia.doc_id = d.doc_id and ia.subscription_item_id=ib.subscription_item_id
and ia.subscription_accounting_id=ib.subscription_accounting_id  and d.cofirmation='T' and ia.card_id=vTargetCardId),0);
DELETE  item_balance WHERE CARD_ID=vCardId;            
                               
-- END ITEM_ACCOUNTING
-- ITEM_PERIOD_ACC
UPDATE ITEM_PERIOD_ACC SET CARD_ID =vTargetCardId WHERE CARD_ID=vCardId;
-- END ITEM_PERIOD_ACC
-- REGISTRATION
UPDATE REGISTRATION SET card_id=vTargetCardId WHERE CARD_ID=vCardId;
-- END REGISTRATION
-- SUBSCRIPTION_ACCOUNTING
UPDATE SUBSCRIPTION_ACCOUNTING SET card_id=vTargetCardId WHERE CARD_ID=vCardId;
-- END SUBSCRIPTION_ACCOUNTING
 -- TIMER
                  UPDATE TIMER SET card_id=vTargetCardId WHERE CARD_ID=vCardId;
                  -- END TIMER
                  -- RULESET_PERIOD_ACC
                  UPDATE RULESET_PERIOD_ACC SET card_id=vTargetCardId WHERE CARD_ID=vCardId;
                  -- END RULESET_PERIOD_ACC
                  -- CARD_XTRA
                  DELETE CARD_XTRA WHERE CARD_ID=vCardId;
                  -- END CARD_XTRA
                  -- CLIENT_RELATIONS
                  UPDATE CLIENT_RELATIONS SET CARD_ID_1=vTargetCardId WHERE CARD_ID_1=vCardId;
                  UPDATE CLIENT_RELATIONS SET CARD_ID_2=vTargetCardId WHERE CARD_ID_2=vCardId;
                  DELETE CLIENT_RELATIONS WHERE CARD_ID_1=vTargetCardId and CARD_ID_2=vTargetCardId;
                  -- END CLIENT_RELATIONS
                  -- CLIENT_PROFILE_DATA
                  DELETE CLIENT_PROFILE_DATA WHERE card_id=vCardId;
                  -- END CLIENT_PROFILE_DATA
                  -- BS_TASK
                  UPDATE BS_TASK SET CARD_ID=vTargetCardId WHERE CARD_ID=vCardId;
                  -- END BS_TASK
                  -- BS_XXX
                  SELECT COUNT(*) into cntBS FROM ALL_TABLES WHERE TABLE_NAME = 'BS_BOOKING';
                  if cntBS >0 then
                      UPDATE BS_BOOKING SET CARD_ID=vTargetCardId WHERE CARD_ID=vCardId;
                  end if;
                  -- END BS_XXX
                  -- CARDS
                  UPDATE CARDS SET MASTER_CARD_ID=vTargetCardId WHERE MASTER_CARD_ID=vCardId;
                  DELETE CARDS WHERE card_id=vCardId;
                  -- END CARDS
 -- CARDS_MERGE_HISTORY
INSERT INTO CARDS_MERGE_HISTORY 
(CARDS_MERGE_HISTORY_ID, empl_id, 
operator, source_card_id, source_name, target_card_id, target_name, tm) 
values
 (CARDS_MERGE_HISTORY_SEQUENCE.nextval, NULL, 'UNKNOWN', 
vCardId, VLAST ||' '|| VFIRST, vTargetCardId, VLAST ||' '|| VFIRST,sysdate);
                  -- END CARDS_MERGE_HISTORY
                  
               end if;

end loop;
 CLOSE curMerge;

end loop;
CLOSE curCards;
 
END cards_merge;
/
quit;

Для контроля используется следующий

скрипт

select UPPER(last_name), UPPER(first_name), birth_date, UPPER(second_name), count(*)
from cards group by UPPER(last_name), UPPER(first_name), birth_date, UPPER(second_name) having count(*) >1
 order by UPPER(last_name);

Процедуру ставим в задачи, на 02.00 am понедельника, чтобы исключить влияние пользователей на выполнение скрипта.

Happy end

На данный момент проблема с «плавающими» отчетами решена, и у бухгалтерии больше нет вопросов с финансовыми отчетами, бонусы клиентам тикают. Все довольны, а с понедельника приступаем к внедрению на территории роумингового Wi-Fi, что послужит темой для следующего поста.

This entry passed through the Full-Text RSS service — if this is your content and you're reading it on someone else's site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers. Five Filters recommends: 'You Say What You Like, Because They Like What You Say' - http://www.medialens.org/index.php/alerts/alert-archive/alerts-2013/731-you-say-what-you-like-because-they-like-what-you-say.html