Czyszczenie bazy danych przed uruchomieniem

Po okresie testowania klient życzy sobie aby wyczyścić bazę. Można to zrobić poprzez wywołanie odpowiednich komend w bazie danych.

-- wyczyszczenie tabel z rekordami dziennikow
delete from regofpapers ;
delete from regofvatnotes;
delete from regofmagazins;
delete from regofresolutions;
delete from regofnotes;

-- wyczyszczenie tabel dokumentow
delete from documents;
delete from documents_history;
delete from attachments;

-- wyzerowanie liczników
update counters set cntval = 0;
update briefcases set countr = 0;
update doss_extract_list set countr = 0;

-- wyczyszczenie zdarzen
delete from rcp_cards;
delete from events;
delete from process_comments;
delete from process_participants;
delete from processes;
delete from proc_link_users;

-- czyczczenie etapów, procedur
delete from stages;
delete from procedures;

-- czyszczenie usuniętych wpisów
DELETE from fk_elements WHERE doc_id IN (SELECT doc_id FROM documents where adduid IN (SELECT usr_id FROM users where is_del is true));

delete from documents where adduid IN (SELECT usr_id FROM users where is_del is true);

delete from rcp_cards WHERE emp_id IN(SELECT usr_id FROM users where is_del is true);

delete from readed_comments where usr_id IN (SELECT usr_id FROM users where is_del is true);

delete from contacts_caretakers where usr_id IN (SELECT usr_id FROM users where is_del is true);

delete from vindication.vind_proc where vindyc IN (SELECT usr_id FROM users where is_del is true);

DELETE from fk_elements WHERE adduid IN (SELECT usr_id FROM users where is_del is true);

delete from users where is_del is true;

delete from rcp_cards WHERE prc_id IN(select prc_id from processes where is_del is true);

delete from fk_elements WHERE prc_id IN(select prc_id from processes where is_del is true);

delete from process_participants WHERE prc_id IN(select prc_id from processes where is_del is true);

delete from processes where is_del is true;

delete FROM organization_units WHERE is_del IS TRUE;

delete from fk_elements WHERE doc_id IN(SELECT doc_id FROM documents where is_del is true);

delete from documents where is_del is true;

delete from log;

Po testach procedur również można wyczyścić instancje procedur :

DELETE FROM procedures WHERE NOT EXISTS (SELECT d.procid FROM documents d WHERE p.procid = d.procid AND d.procid IS NOT NULL) AND
NOT EXISTS (SELECT d.procid FROM processes d WHERE p.procid = d.procid AND d.procid IS NOT NULL) AND
NOT EXISTS (SELECT d.procid FROM rcp_cards d WHERE p.procid = d.procid AND d.procid IS NOT NULL) 

Policzyć można je za pomocą:

SELECT count(procid) FROM procedures p WHERE NOT EXISTS (SELECT d.procid FROM documents d WHERE p.procid = d.procid AND d.procid IS NOT NULL) AND
NOT EXISTS (SELECT d.procid FROM processes d WHERE p.procid = d.procid AND d.procid IS NOT NULL) AND
NOT EXISTS (SELECT d.procid FROM rcp_cards d WHERE p.procid = d.procid AND d.procid IS NOT NULL)