IT

Why Audit? [1]

1. Ensure accountability for all actions -  in particular server, database, schema, and table operations.
2. Discourage users from inappropriate actions due to their accountability.
3. Assist the investigation of  suspicious activity.
4. Notify an auditor of the actions of an unauthorized user.
5. Monitor and gather data about specific database activities.
6. Detect problems with an authorization or access control implementation.
7. Ensure compliance with legal requirements, such as: Sarbanes-Oxley Act; Health Insurance Portability and Accountability Act (HIPAA); International Convergence of Capital Measurement and Standards: a Revised Frame work; Japan Privacy Law; European Union Directive of Privacy and Electronic Communications; Data Protect Act (UK);

Using a UNIQUE CONSTRAINT:

CREATE TABLE Book
(
P_Id int NOT NULL AUTO_INCREMENT,
Title varchar(255) NOT NULL,
ISBN varchar(255),
PRIMARY KEY (P_Id),
UNIQUE(ISBN)
)

CREATE TABLE Book
(
P_Id int NOT NULL AUTO_INCREMENT,
Title varchar(255) NOT NULL,
ISBN varchar(255),
PRIMARY KEY (P_Id),
CONSTRAINT uc_Book UNIQUE(P_Id,ISBN)
)

Using a UNIQUE INDEX:

CREATE TABLE Book
(
P_Id int NOT NULL AUTO_INCREMENT,
Title varchar(255) NOT NULL,
ISBN varchar(255),
PRIMARY KEY (P_Id),
UNIQUE INDEX (ISBN)
)

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...);

DECLARE 
      Variable declaration 
BEGIN 
     Program Execution 
EXCEPTION 
      Exception handling
END;

• variable_name datatype [NOT NULL := value ];

• Number (n,m);
• Char (n);
• Varchar2 (n)
• Date;
• Long;
• Long raw;
• Raw;
• Blob;
• Clob;
• Nclob;
• Bfile;

salary number(4);

dept varchar2(10) NOT NULL := “HR Dept”; 

DECLARE
  myGlobalVar number; 
 BEGIN 
  myGlobalVar := 100;  
  DECLARE 
   myLocalVar number; 
...

• constant_name CONSTANT datatype := VALUE;  

• salary_increase CONSTANT number (3) := 10; 

IF condition 1 
THEN 
 statement 1; 
 statement 2; 
ELSIF condtion2 THEN 
 statement 3; 
ELSE 
 statement 4; 
END IF;

• Simple Loop (similar to Java Do While)
• While Loop (similar to Java While)
• For Loop  (similar to Java For)

LOOP 
     statements; 
      EXIT; 
       {or EXIT WHEN condition;}
END LOOP;

WHILE <condition>
LOOP statements;
END LOOP;

WHILE monthly_value <= 4000
LOOP
     monthly_value := daily_value * 31;
END LOOP;

FOR counter IN [reverse] lowest_val..highest_val
  LOOP statements; 
END LOOP; 

for i in 1..1000 loop 
     insert into a values(i,i*2);
end loop;

for i in reverse 1..1000 loop 
    insert into a values(i,i*2);
end loop; 

• TYPE record_type_name IS RECORD
  (first_col_name column_datatype,
  second_col_name column_datatype, ...);
  NOTE: If a field is based on a column from database table, you can define the field_type as follows:
  col_name table_name.column_name%type;
• If all the fields of a record are based on the columns of a table, we can declare the record as follows:
  record_name table_name%ROWTYPE;

CREATE [OR REPLACE ] TRIGGER trigger_name 
 {BEFORE | AFTER | INSTEAD OF } 
 {INSERT [OR] | UPDATE [OR] | DELETE} 
 [OF col_name] 
 ON table_name 
 [REFERENCING OLD AS o NEW AS n] 
 [FOR EACH ROW] 
 WHEN (condition)  
 BEGIN 
   --- sql statements  
 END; 

CREATE or REPLACE TRIGGER price_history_trigger 
BEFORE UPDATE OF unit_price 
ON product 
FOR EACH ROW 
BEGIN 
INSERT INTO product_price_history 
VALUES 
(:old.product_id, 
 :old.product_name, 
 :old.supplier_name, 
 :old.unit_price); 
END; 

 CREATE or REPLACE TRIGGER After_Update_Row_product 
 AFTER  
 insert On product 
 FOR EACH ROW 
 BEGIN 
 INSERT INTO product_check 
 Values('After update, Row level',sysdate); 
 END; 

• Implicit cursors:
  These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.
  Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
• Explicit cursors:They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
  Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

DECLARE var_rows number(5);
BEGIN
 UPDATE employee 
 SET salary = salary + 1000;
 IF SQL%NOTFOUND THEN
 dbms_output.put_line('None of the salaries where updated');
 ELSIF SQL%FOUND THEN
 var_rows := SQL%ROWCOUNT;
 dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
 END IF; 
END; 

DECLARE 
    /* DECLARANDO O CURSOR DE PRODUTOS */
    CURSOR C_PRODUTOS IS 
        SELECT * FROM TB_PRODUTOS;
        
    /* DECLARANDO UMA VARIAVEL QUE SERA O REGISTRO DA TABELA */
    REG_PRODUTO C_PRODUTOS%ROWTYPE;
    
BEGIN
    /* ABRE CURSOR */
    OPEN C_PRODUTOS
    
    LOOP
        /* LÊ UM REGISTRO DO CURSOR */
        FETCH C_PRODUTOS INTO REG_PRODUTO;
        
        /* ABANDONA O LOOP CASO SEJA O FINAL DO CURSOR */
        EXIT WHEN C_PRODUTOS%NOTFOUND;
        
        /* 
        AQUI SERA INSERIDO O CODIGO QUE IRA MANIPULAR OS DADOS COMO: 
            - INSERIR EM OUTRA TABELA, 
            - FAZER ALGUM CALCULO, 
            - ETC. 
        */
    
    END LOOP;
    
    /* FECHA O CURSOR */
    CLOSE C_PRODUTOS
END;

What is XSLT?

• XSLT stands for XSL Transformations
• XSLT is the most important part of XSL
• XSLT transforms an XML document into another XML document (or even to another type of XML doc like XHTML);
• XSLT uses XPath to navigate in XML documents
• XSLT is a W3C Recommendation

XSLT 2.0

XSLT support

• XSLT 1.0 is supported by all major browsers;
• XSLT 2.0, has of 2012, is still not supported by any browser (Wikipedia, When will xslt 2.0 be finished, which browsers support XSLT 2.0 already?);

• Apache’s Xalan (XSLT1.0 only);
• Saxon XSLT (supports XSLT 2.0 as well);
• 
  

• XSLT Debugger for NetBeans (official documentation with GUI tutorial on how to use it) - seems to use the Xalan XSLT processor so only has support for XSLT 1.0;
• Altova's XMLSpy, a fast XML Editor with support for XSLT 2.0 - includes a powerful XSLT debugger for troubleshooting and perfecting XSLT 1.0 and 2.0 stylesheets.

XSLT examples

• JAVA: check the javadoc of the Package javax.xml.transform this package defines the generic APIs for processing transformation instructions, and performing a transformation from source to result.
  Check this example: xml to xml transformation

XSL-FO ("Extensible Stylesheet Language Formatting Objects")

What is XSL-FO?

• XSL-FO is about formatting XML data for output to screen, paper or other media.
• XSL-FO stands for Extensible Stylesheet Language Formatting Objects
• XSL-FO is based on XML
• XSL-FO is a W3C Recommendation
• XSL-FO is now formally named XSL (or you can think of it has a part of XSL)

XSL-FO examples

• create a PDF document using XSLFO and Apache FOP. (with source code example);