Baze podataka – MySQL, Oracle, PostgreSQL, MS SQL Server, Access, modelovanje podataka …

Logiranje izmjene strukture baze – DDL LOGGER

Modeliranje baze podataka često nije jednokratan posao, uvijek se nađe potreba da se naprave izmjene na dijelovima koda ili strukturi tablica, doda ili oduzme neka kolona u nekoj tablici, izmijeni tip podatka neke kolone, doda ili obriše neki ključ ili index…

Kada se takve izmjene rade nepredviđeno, a ne dokumentiraju se, ili u okolini gdje takve stvari radi više ljudi (namjerno ili slučajno), i kad se to radi ručno kroz komandnu liniju a bez modeliranja u nekom alatu za modeliranje (čiji bi se modeli spremali pod sistemom verzioniranja), mogu dovesti do vrlo nezgodnih situacija u kojima ne znamo tok događaja – tko je, kada i što napravio (i zašto ?).

Čak sam i sam bio u situacijama gdje neke promjene radim na razvoju, pa nakon nekog vremena ne znam šta sam napravio i kojim redoslijedom, ili zaključim da sam otišao pogrešnim putem i da se trebam vratiti nekoliko koraka unatrag, a ne znam točno kako…

Rješenje je vrlo jednostavno – database trigger koji logira sistemske događaje u tablicu DDLOG sa sljedećim kolonama:

• OWNER (vlasnik objekta)
• OPERATION (CREATE, ALTER ili DROP)
• OBJECT_TYPE (procedure, table, trigger,package…)
• OBJECT_NAME (ime objekta)
• SQL_TEXT (prvih 1000 znakova sql izraza)
• SQL_FULLTEXT (kompletni kod izraza)
• CREATED (vrijeme upisa)
• CREATED_BY (user koji je pokrenuo operaciju)
• TERMINAL_NAME (ime računala s kojeg je operacija pokrenuta)
• TERMINAL_IP (IP adresa računala s kojeg je operacija pokrenuta)
• SESSIONID (ID sesije u kojoj je operacija izvršena).

Iz drugih korisničkih shema se može raditi select na ovu tablicu uz posjedovanje SELECT ANY TABLE privilegije.

Druga tablica ERRLOG služi za logiranje eventualnih grešaka u samom triggeru.

Priložena je skripta koju je potrebno pokrenuti pod SYS userom, čime se kreira user DDLOGGER, sa tablicom DDLOG:

/********************************************************************************************************************************************
Desc: User for logging changes on database objects: CREATE, ALTER and DROP operations.
Author: Mihael Radovan
Last change: 28.03.2013
********************************************************************************************************************************************/
/*  user with privileges */
CREATE user ddlogger IDENTIFIED BY DDL;
GRANT UNLIMITED TABLESPACE TO ddlogger;

/* error log table */
CREATE TABLE ddlogger.errlog
(tstamp TIMESTAMP DEFAULT systimestamp,
errcode number,
errtext varchar2(500))
NOLOGGING, COMPRESS FOR oltp, NOCACHE, NOMONITORING;

/* err log procedure */

CREATE OR REPLACE PROCEDURE ddlogger.plog(errcode number, errtext varchar2)
IS
  PRAGMA autonomous_transaction;
BEGIN
  INSERT /*+ append */ INTO ddlogger.errlog(errcode, errtext) 
  VALUES (errcode, errtext);
  
  COMMIT;

END plog;
/
/* ddl log table */

CREATE TABLE ddlogger.ddlog (
owner   VARCHAR2(30),
operation   VARCHAR2(30),
object_type VARCHAR2(30),
object_name VARCHAR2(30),
sql_text    VARCHAR2(1000),
sql_fulltext clob,
created TIMESTAMP,
created_by  VARCHAR2(30),
terminal_name VARCHAR2(255),
terminal_ip VARCHAR2(30),
sessionid number)
NOLOGGING, COMPRESS FOR oltp, NOCACHE, NOMONITORING;

/* database trigger */

CREATE OR REPLACE TRIGGER ddlogger.DDL_TRIGGER BEFORE DDL ON DATABASE
DECLARE
  oper varchar2(30);
  l_sql_text ora_name_list_t;
  l_count NUMBER;
  l_sql varchar2(1000);
  errcode number;
  errtext varchar2(500);
BEGIN
  SELECT ora_sysevent
    INTO oper
   FROM DUAL;

   l_count := ora_sql_txt(l_sql_text);

  FOR i IN 1..l_count

  LOOP
      l_sql := l_sql||l_sql_text(i);
  END LOOP;

  IF (oper IN ('CREATE', 'ALTER', 'DROP')) AND (ora_dict_obj_name != ' ddlog') THEN NULL;

   INSERT /*+ append */ INTO ddlogger.ddlog (owner, operation, object_type, object_name, sql_text, sql_fulltext, created, created_by, terminal_name, terminal_ip, sessionid)
   VALUES (ora_dict_obj_owner, oper, ora_dict_obj_type, ora_dict_obj_name, substr(l_sql, 1, 1000), l_sql, systimestamp, user, SYS_CONTEXT ('userenv', 'host'),  SYS_CONTEXT ('userenv', 'ip_address'), SYS_CONTEXT ('userenv', 'sessionid'));
  END IF;

EXCEPTION
  WHEN others THEN
     errcode:= SQLCODE;
     errtext:= SQLERRM;
     plog(errcode, errtext);
END;
/