Audit of changes and history

Principle of operation

For each table with audit enabled, are created:
- a trigger that captures changes to common transaction tables and records;
- view for receiving the changed information;
- class-history with the properties of the main ORM-class.

Advantages of this approach

- fixes changes made both through ORM and SQL;
- privileges are granted only on the view, not on the common table;
- the class-history has the properties of the main ORM-class, which allows you to access in general form via metainformation.

Video



Example of using

Create table Service in the test schema with the column Price, change the data in the table, and then use the mapping mechanism to get the story.

1. Create table test.service
create table test.service(
  id serial primary key,
  importance integer, 
  code char(3) not null unique,
  name varchar(32) not null,  
  price numeric(16,2),
  is_vip boolean,
  description text,
  image bytea
);

create index i_service$importance on test.service(importance);

insert into test.service(code,name,description,price) values ('DLV','Delivery','Delivery of goods to the customer',10);
update test.service set price=20;

/*
select * from test.service;
select * from test.v_audit$service where code='DLV'

// access by superuser only
select * from jv$audit.audit_transaction
select * from jv$audit.audit_table

drop table test.service;
drop view test.v_audit$service
*/

2. Generate the Service class, in the History section for the table enable keeping history and the creation of a trigger
Generate test ORM-class Service

3. Create the object Service, change its property (column of the table), get information about changes via ORM and SQL-query
package org.jarovit.example.audit;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import javafx.application.Application;
import javafx.stage.Stage;

import org.jarovit.database.adapter.DatabaseAdapter;
import org.jarovit.database.adapter.DatabaseAdapter.TraceLevel;
import org.jarovit.database.adapter.PostgreSQLDatabaseAdapter;
import org.jarovit.example.orm.database.test.orm.record.Service;
import org.jarovit.javafx.util.ApplicationUtil;
import org.jarovit.orm.javafx.stage.RecordAuditViewer;

public class ExampleAudit {
	
    public static class JavaFxApp extends Application {
    	
    	static Service service;
    	
	    public void start(Stage stage) throws Exception {
	    	ApplicationUtil.DATABASE_ADAPTER_DEFAULT = service.getDatabaseAdapter(); 
	    	RecordAuditViewer.show(stage, service);
	    }
		
	};
	
	public static void main(String[] args) {
		
    	try {
    		
    		// Connect to database and create ORM-adapter 
    		DatabaseAdapter databaseAdapter = new PostgreSQLDatabaseAdapter();
    		databaseAdapter.connect("jdbc:postgresql://127.0.0.1:5432/exampledb", "postgres", "");
    		databaseAdapter.setTraceLevel(TraceLevel.FULL);    		
    		
    		// Create, change and delete a service    		 
    		Service service = new Service();
    		service.setCode("PCK");
    		service.setName("Packing");
    		service.setDescription("Packing of goods for the customer");
    		service.setPrice(new BigDecimal(5));
    		service.setDatabaseAdapter(databaseAdapter);
    		service.save();
    		service.delete();
    		
    		// Load audit of service by code with using index
    		// ORM
    		List<Service.Audit> serviceAuditList = Service.Audit.loadList(databaseAdapter, "code=?", "PCK"); 
    		for(Service.Audit serviceAudit : serviceAuditList) {
    			System.out.println("==============================================");
    			System.out.println("Timestamp: "+serviceAudit.getAuditTimestamp());
    			System.out.println("User:      "+serviceAudit.getAuditUser());
    			System.out.println("Operation: "+serviceAudit.getAuditOperation());
    			System.out.println("State:     "+serviceAudit.getAuditState());
    			System.out.println("Price:     "+serviceAudit.getPrice());
    		}
    		// SQL
    		PreparedStatement sqlStatement = databaseAdapter.getConnection().prepareStatement("select * from test.v_audit$service where code=? order by audit_id");
    		sqlStatement.setString(1,"PCK");
            ResultSet sqlResultSet = sqlStatement.executeQuery();            
            while(sqlResultSet.next()) {
            	System.out.println("==============================================");
            	System.out.println("Timestamp: "+sqlResultSet.getTimestamp("audit_timestamp"));
    			System.out.println("User:      "+sqlResultSet.getString("audit_user"));
    			System.out.println("Operation: "+sqlResultSet.getString("audit_operation"));
    			System.out.println("State:     "+sqlResultSet.getString("audit_state"));    			
    			System.out.println("Price:     "+sqlResultSet.getBigDecimal(Service.METAINFO_PROPERTY$PRICE.getTableColumnName() /* "price" */ ));
            }
            sqlStatement.close();

            // Show history of changes in JavaFX Application
            JavaFxApp.service = service;
            Application.launch(JavaFxApp.class);
            
		} catch(Exception exception) {
			exception.printStackTrace();
		}
	}

}