Skrypt do czyszczenia bazy przed uruchomieniem produkcyjnym

Często, po zakończeniu wdrożenia, a szczególnie po wykonaniu wielu testów, w AMODIT pozostają sprawy testowe. W momencie produkcyjnego uruchomienia nie są one potrzebne i baza powinna zostać wyczyszczona. Jednakże nie chcemy usunąć nic innego np słowników, definicji procedur, użytkowników itp. Przy niewielkiej liczbie spraw można taką operację przeprowadzić ręcznie kasując pojedyncze sprawy i usuwając je ostatecznie z zakładki USUNIĘTE. Przy dużej liczbie spraw, operacja ta może być długotrwała.

Można ją wykonać bezpośrednio z poziomu bazy danych wspierając się poniższym skryptem. Podana tu wersja dotyczy AMODIT w wersji 2016.0006. Dla wcześniejszych wersji może on być inny ze względu na inną strukturę bazy danych. Przed jego zastosowaniem należy upewnić się do której wersji AMODIT’a go stosujemy.

W drugiej linii skryptu nalezy wpisać ID procedury, której sprawy chcemy usunąć. Jeżeli mamy klika procedur to operację powtarzamy dla każdej procedury odrębnie.

AMODIT wersja 2016.0006

declare @procId int; --MSSQL only
set @procId=XXXXXX
DELETE FROM caseacceptance WHERE accCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casesignature WHERE sigCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM caseattachmenthistory WHERE attCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM caseattachmentcontent WHERE atcAttId in (select attId FROM caseattachment WHERE attCaseId in (select caseId from casedefinition where caseProcedureId=@procId));
DELETE FROM caseattachment WHERE attCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casecomment WHERE comCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casehistorychanges WHERE chcCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casehistorychanges WHERE chcCaseId in (select caseId from casedefinition where caseMainCaseId in (select caseId from casedefinition where caseProcedureId=@procId));
DELETE FROM casehistorydetails WHERE caseid in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casehistory WHERE caseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casepeople WHERE peopCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casereaded WHERE crdCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM caserights WHERE crsCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM reminder WHERE caseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM ftsearchdata WHERE ftscaseid in (select caseId from casedefinition where caseProcedureId=@procId);
update casedefinition set caseConnectedToCaseId=null where caseConnectedToCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
Delete from casedefinition where caseMainCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casedefinition WHERE caseProcedureId=@procId

AMODIT Wersja 2016.0005 i wcześniejsze

declare @procId int; --MSSQL only
set @procId=XXXXX
DELETE FROM caseacceptance WHERE accCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casesignature WHERE sigCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM caseattachmenthistory WHERE attCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM caseattachment WHERE attCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casecomment WHERE comCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casehistorychanges WHERE chcCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casehistorychanges WHERE chcCaseId in (select caseId from casedefinition where caseMainCaseId in (select caseId from casedefinition where caseProcedureId=@procId));
DELETE FROM casehistorydetails WHERE caseid in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casehistory WHERE caseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casepeople WHERE peopCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casereaded WHERE crdCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM caserights WHERE crsCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM reminder WHERE caseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM ftsearchdata WHERE ftscaseid in (select caseId from casedefinition where caseProcedureId=@procId);
update casedefinition set caseConnectedToCaseId=null where caseConnectedToCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
Delete from casedefinition where caseMainCaseId in (select caseId from casedefinition where caseProcedureId=@procId);
DELETE FROM casedefinition WHERE caseProcedureId=@procId