Предисловие
По роду своей деятельности, сопровождаю инфраструктуру небольшого парк-отеля, которая включает необходимые и не очень элементы.
Каждый элемент имеет свой собственный сервер и соответственно свою базу данных, функциональные части могут обмениваться между собой данными о клиентах и продажах.
Система состоит из трех модулей:
- Fidelio V8 — модуль управления гостиницей, вполне состоявшееся приложение. Имеет систему бронирования через сайт и от стойки. занимает приоритетное место
слегка капризен, кучу отчетов, ведет статистику. В качестве базы данных используется Oracle 11gR2 - Micros 3700-модуль управления рестораном(работа на местах ведется через типовые POS моноблоки), интерактивная рассадка гостей, разделение чеков и много разных полезных вещей которые облегчают жизнь. Работает на Sybase.
- TNG- довольно молодой модуль для продажи услуг медицинского центра и спортивно-развлекательного комплекса, своя собственная система бронирования, клиентская база, регистрация клиентов etc. Работает на Oracle 11gR2. Весь пост, собственно и связан с этим модулем.
Однажды на пятничном совещании начальство сказало решить проблему тормозов базы данных модуля управления развлекательного комплекса и медицинского центра, «плавающих отчетов». Мы системные администраторы, наше дело маленькое — решать подобные проблемы.
Что там было
После проверки всех частей системы управления, все внимание обратилось на святая святых системы — базу данных, спасибо тому интегратору, который поставил галочку «Save Password» в SQL Developer.
Проблемы 1,2
- Таблица CARDS(клиентские профайлы)- содержит много дублирующих друг друга записей, иногда количество дубликатов доходит до 8-10
- в той же таблице отсутствуют дополнительные сведения о пользователях, некритичные, но облегчающие их персональную идентификацию, для начисления бонусов в дальнейшем
Каждая запись в таблице CARDS, тянет за собой записи в дочерних таблицах с предельно жестким обеспечением целостности данных сплошные Restrict и никаких Cascade, финансовая информация все же.
Замечу, что TNG, предоставляет свой инструмент для объединения клиентских профайлов, вот такой:
Тут есть одно большое «НО», или даже два:
- За одно действие, нельзя объединить больше чем два профайла
- Профайлы базы находятся не в «нормальном состоянии».
Решаем проблему 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, что послужит темой для следующего поста.
ссылка на оригинал статьи http://habrahabr.ru/post/188084/
Добавить комментарий