Friday, October 3, 2014

Postgre SQL Commands

Following are some of the commands I though very useful for newbie developers.
  • Connect to the DB
/usr/local/pgsql/bin/psql -U postgres [DB]
  • Execute postgres functions
\i /test/query/run.sql
  • How to get differecne between two schems
             Here I have have used apgdiff    
/usr/java/[JDK Verssion]/bin/java -jar apgdiff-xxxxx.jar target_db.sql source_db.sql > diff.sql
  • How to get the dump
/usr/local/pgsql/bin/pg_dump --file=/home/uploads/schema_backup/[file name].sql --schema=[schema name] -U [user name] [DB Name]
  • How to restore a schema 
-U [user name] --pset pager -f /home/uploads/pgdump/[file anme].sql [DB Name]
  • How to connect to the Database
/usr/local/pgsql/bin/psql  -U [user name]  [schema name]
  • How to backup only the selected table
pg_dump --file=/rezsystem/backup/[file name].sql --host=[connect IP] --table=[Table name] --username=[user name] --schema=[schema name] [DB Name]
  • How to backup only the table structure
pg_dump --file=$1 --host=$2 -U $3 --schema=$4 --schema-only $5 > $6 2>&1
           ex. pg_dump --file=/rezsystem/rm_upload/[file name].sql --host=localhost -U [user name] --schema=[schema name] --schema-only [DB Name] > /rezsystem/rm_upload/[file name].sql 2>&1
  • How to update bulk DDL statement in a one SQL file
psql --file=/rezrelmgmt/uploads/Lsi/[file name].sql --host=localhost --username=postgres --quiet --tuples-only [DB Name]
  • How to check check the running datbase processors with the status
ps auxww | grep ^postgres
  • How to kill the exact process
kill -INT prodessId
  • How to check the total connections
ps ax |grep post |wc -l

Monday, June 30, 2014

How to Implement Audit Functionality In PostgreSQL

As I can remember 6 years back in 2008 Dec. we were researching on a better audit module to audit all transactions (Create, Modify, Delete) in PostgreSQL 8.0.3. It was a Friday which I started to implement this and I spent couple of days including Saturday and Sunday to get this completed. By Monday Morning I was able to implement the audit module to the existing product. Since then the time I'm writing this Blog still the audit functionality is working perfectly without any issues even with upgrade of the PostgreSQL versions.

So I though share this experience with you'll as when we eager to do something and also when we have the determination and courage, we are very much keen on to get things done whether it is weekend or 1 - 2 am in the morning. I know most of the Software Development enthusiastic would agree with me.

While I was researching on this, I found a way to implement the audit module with PL/Tcl language.
PL/Tcl is a loadable procedural language for the PostgreSQL database system that enables the Tcl language to be used to write functions and trigger procedures.

Please allow me to explain how we can get this implemented easily. Steps 1 - 3 has to be implemented only once. So thereafter if you need to implement audit then follow steps 4 - 5 (Create the Audit Table and Trigger). Pretty easy ha. Let's start...

  • Step 1 - In order to implement the audit module install the PL/TCL language.
createlang -U postgres pltcl <DB-Name>
  • Step 2 - Create the Function -  log_to_audit_table
Function          Arguments     Returns   Programming language
log_to_audit_table             "trigger"   pltcl
#This function crated to audit the data for all the modules
#Created By : Kosala De Silva
#Created On : 12 - Dec - 2008

spi_exec "SELECT CURRENT_USER AS tguser"
spi_exec "SELECT c.relname AS tgname,n.nspname AS schema_name
           FROM pg_class c , pg_namespace n
           WHERE n.oid = c.relnamespace
           AND relfilenode = $TG_relid"

if {[string equal -nocase $tgname audit_table]} { return OK }

set pk_name ""

 spi_exec "SELECT a.attname AS pk_name FROM pg_class c, pg_attribute a, pg_index i
 WHERE c.relname = '$tgname'
 AND c.oid=i.indrelid
 AND a.attnum > 0
 AND a.attrelid = i.indexrelid
 AND i.indisprimary='t'"

  spi_exec " select substring('$schema_name',0,instr('$schema_name','_', 1))||'_admin'
  AS main_schema"

 spi_exec "SELECT audit_table_name AS m_aud_tbl_name
 FROM $main_schema.audit_table_mapping
 where schema_name = '$schema_name'
 and tabel_name = '$tgname'"

switch $TG_op {
  set pk_value ""

  #get PK value
  foreach field $TG_relatts {
    if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
      set pk_value [lindex [array get NEW $field] 1]
  #log inserted row values
  foreach field $TG_relatts {
    if {! [string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
      set modified_field [lindex [array get NEW $field] 0]
      set current_value [lindex [array get NEW $field] 1]
      spi_exec -array C "INSERT INTO $schema_name.$m_aud_tbl_name
        VALUES (CURRENT_TIMESTAMP, '$tguser', '$tgname', '$modified_field', '$pk_name', '$pk_value', '$TG_op', NULL, '$current_value')"
  set pk_value ""

  #get PK value
  foreach field $TG_relatts {
    if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
      set pk_value [lindex [array get NEW $field] 1]
  #log inserted row values
  foreach field $TG_relatts {
    #check changed fields
    if {[string equal -nocase [array get NEW $field] [array get OLD $field]] == 0} {
      set modified_field [lindex [array get OLD $field] 0]
      if {[string compare $modified_field ""] == 0} {
        set modified_field [lindex  [array get NEW $field] 0]
      set previous_value [lindex [array get OLD $field] 1]
      set current_value  [lindex [array get NEW $field] 1]
      spi_exec -array C "INSERT INTO $schema_name.$m_aud_tbl_name
        VALUES (CURRENT_TIMESTAMP, '$tguser', '$tgname', '$modified_field', '$pk_name', '$pk_value', '$TG_op', '$previous_value', '$current_value')"
  set pk_value ""

  #get PK value
  foreach field $TG_relatts {
    if {[string equal -nocase [lindex [array get OLD $field] 0] $pk_name]} {
      set pk_value [lindex [array get OLD $field] 1]
  #log inserted row values
  foreach field $TG_relatts {
    if {! [string equal -nocase [lindex [array get OLD $field] 0] $pk_name]} {
      set modified_field [lindex [array get OLD $field] 0]
      set previous_value [lindex [array get OLD $field] 1]
      spi_exec -array C "INSERT INTO $schema_name.$m_aud_tbl_name
        VALUES (CURRENT_TIMESTAMP, '$tguser', '$tgname', '$modified_field', '$pk_name', '$pk_value', '$TG_op', '$previous_value', NULL)"
return OK

Note : 
Main function should be created in the public schema. This function can be used for any system audit tracking. ex. "public"."log_to_audit_table" () 
This should be executed only once per DB server.

  • Step 3 - Create a table called audit_table_mapping
CREATE TABLE "<Schema Name>"."audit_table_mapping" (
  "schema_name" VARCHAR(100), 
  "tabel_name" VARCHAR(200), 
  "audit_table_name" VARCHAR(200), 
  CONSTRAINT "audit_table_mapping_pkey" PRIMARY KEY("id")
Note : 
When you implement the audit for each table there should be a mapping record in this table as one audit mapping table can be used to audit multiple tables.
DDL script should be executed only once.
A new record should be inserted for each audit table.

  • Step 4 - Create the Audit table which you need to audit data

  • CREATE TABLE "<Schema Name>"."<audit_Table Name>" (
      "time_stamp" TIMESTAMP WITH TIME ZONE,
      "pg_user" VARCHAR(30),
      "tbl_name" VARCHAR(30),
      "fld_name" VARCHAR(30),
      "primarykey_name" VARCHAR(30),
      "primarykey_value" VARCHAR(40),
      "modify_type" CHAR(6),
      "old_value" TEXT,
      "new_value" TEXT
    ) ;
    You can use the same DDL script to create other audit tables as well. What you have to do is just change only the audit table name. ex. audit_[table name]

    • Step 5 - Execute the Trigger
    CREATE TRIGGER "trig_audit_<audit_Table Name>"
    AFTER INSERT OR DELETE OR UPDATE ON "<Schema Name>"."<Table Name>"
    EXECUTE PROCEDURE "log_to_audit_table" ();
    Trigger name should be created as follows. trig_audit_[table name]

    Once above steps are done you are good to log data. So when you try to insert / modify / delete that will be audited in the audit table. The advantage of using this functionality is that the audit table will have only the modified data with the old value and the new value. 

    Tuesday, June 17, 2014

    CVS Commands

    Following are the basic CVS commands

  • Update the project - Go to the relevent folder and issue the following command
  •           Option 1 :
            cvs -d:pserver:user@cvsServer:/usr/local/cvsroot -Q update
              Option 2 :
            cvs -d:pserver:user@cvsServer:/usr/local/cvsroot update -d Project_V1
    • Update a file
          cvs -d:pserver:user@cvsServer:/usr/local/cvsroot update -d ""
    • Update a new folders
          cvs -d:pserver:user@cvsServer:/usr/local/cvsroot update -d
    • Commit a file
    cvs add 
    cvs -d:pserver:user@cvsServer:/usr/local/cvsroot add
      • go to the relevant folder and issue the following command
           cvs -d:pserver:user@cvsServer:/usr/local/cvsroot commit -m '[Message]'
    • Remove a branch 
    Option 1
         cvs -d:pserver:user@cvsServer:/usr/local/cvsroot rtag -d -B [TagName] [Project]
    Option 2
         cvs "-q" "checkout" "DocLibrary"
    • remove a tag
         cvs -d:pserver:user@cvsServer:/usr/local/cvsroot rtag -d Project_V1
    cvs rtag -d Project_V1 
    • chcekout a project - checkout HEAD
    cvs -d:pserver:user@cvsServer:/usr/local/cvsroot checkout Project
    • checkout a branch 
    1. got to the workspace
    cvs -d:pserver:user@cvsServer:/usr/local/cvsroot checkout -r Project_V1_QA Project
    2. then rename the foder
    checkout -d branch name Module name
    cvs -d:pserver:user@cvsServer:/usr/local/cvsroot checkout -r Project_V1 Project
    • check the status of the files by files or folder  - cvs status
    Summary of CVS commands are:
            add          Add a new file/directory to the repository
            admin        Administration front end for rcs
            annotate     Show last revision where each line was modified
            checkout     Checkout sources for editing
            commit       Check files into the repository
            diff         Show differences between revisions
            edit         Get ready to edit a watched file
            editors      See who is editing a watched file
            export       Export sources from CVS, similar to checkout
            history      Show repository access history
            import       Import sources into CVS, using vendor branches
            init         Create a CVS repository if it doesn't exist
            kserver      Kerberos server mode
            log          Print out history information for files
            login        Prompt for password for authenticating server
            logout       Removes entry in .cvspass for remote repository
            pserver      Password server mode
            rannotate    Show last revision where each line of module was modified
            rdiff        Create 'patch' format diffs between releases
            release      Indicate that a Module is no longer in use
            remove       Remove an entry from the repository
            rlog         Print out history information for a module
            rtag         Add a symbolic tag to a module
            server       Server mode
            status       Display status information on checked out files
            tag          Add a symbolic tag to checked out version of files
            unedit       Undo an edit command
            update       Bring work tree in sync with repository
            version      Show current CVS version(s)
            watch        Set watches
            watchers     See who is watching a file
    (Specify the --help option for a list of other help options)

    Friday, May 30, 2014

    Git - Resolve Merge conflicts

    When you pull changes from the Git server you might get conflict in your code. So we will see how to resolve those conflicts.

    You can use git mergetool to check the conflicts. This is the merge conflict resolution tools to resolve merge conflicts.

    Additionally you can resolve conflicts in your file by running below commands.

    git checkout --ours <your File name> 
    git checkout --theirs <your File name> 
    git add <your File name> 
    git commit -m "Conflicts Resolve"
    And then pull the changes with

    git pull origin master

    You will see the message similar to below

    From ssh://[git URL]/projectname
     * branch            master     -> FETCH_HEAD
    Already up-to-date

    Sunday, May 4, 2014

    Apache Camel - EIP Pattern Splitter

    The Splitter from the EIP patterns allows you split a message into a number of pieces and process them individually.

    You need to specify a Splitter as split(). In earlier versions of Camel, you need to use splitter()

    I have given samples how we can implement splitter with Java DSL

    Sample 1 - Split the Message Body with @ sign.

     CamelContext context = new DefaultCamelContext();
      ProducerTemplate camelTemplate = context.createProducerTemplate();
      context.addRoutes(new RouteBuilder() {
       public void configure() throws Exception {
        // this routes starts from the direct:start endpoint
        // the body is then splitted based on @ separator
        // the splitter in Camel supports InOut as well and for that we
        // need
        // to be able to aggregate what response we need to send back,
        // so we provide our
        // own strategy with the class AggregationStrategy.
          .split(body().tokenize("@"), new AggregationStrategy())
          // each splitted message is then send to this bean where
          // we can process it
          // this is important to end the splitter route as we do
          // not want to do more routing
          // on each splitted message
          // after we have splitted and handled each message we
          // want to send a single combined
          // response back to the original caller, so we let this
          // bean build it for us
          // this bean will receive the result of the aggregate
          // strategy: AggregationStrategy
      camelTemplate.sendBodyAndHeader("direct:start", "A@B@C",
        "searchCriteria", "headingValue");
    AggregationStrategy Class aggregate method.
     public Exchange aggregate(Exchange oldExchange, Exchange newExchange) {
      // put hotel search criteria together in old exchange 
      // by adding the search criteria from new exchange
      if (oldExchange == null) {
       // the first time we aggregate we only have the new exchange,
       // so we just return it
       return newExchange;
      String requests = oldExchange.getIn().getBody(String.class);
      String newLine = newExchange.getIn().getBody(String.class);
 "Aggregate old requests: " + requests);"Aggregate new requests: " + newLine);
      // put Request together separating by semi colon
      requests = requests + ";" + newLine;
      // put combined Request back on old to preserve it
      // return old as this is the one that has all the Request gathered until
      // now
      return oldExchange;
    Sample 2 - Split the message with split().method()
      CamelContext context = new DefaultCamelContext();
      ProducerTemplate camelTemplate = context.createProducerTemplate();
      context.addRoutes(new RouteBuilder() {
       public void configure() throws Exception {
          .split().method(MySplitterBean.class, "splitBody")
      camelTemplate.sendBodyAndHeader("direct:start", "msg1,msg2", "foo",

    Wednesday, April 9, 2014

    Apache Camel - Request / Reply Pattern with Java DSL

    Apache camel is a powerful tool yet  a lightweight integration framework. This implements all EIPs an  you can easily integrate different applications using the required patterns.  You can use Java, Spring XML, Scala or Groovy. Almost every technology you can imagine is available, for example HTTP, FTP, JMS, EJB, JPA, RMI, JMS, JMX, LDAP, Netty, and many, many more (of course most ESBs also offer support for them).

    You can get more information on Apache camel by going through the following link - Apache Camel

    There are many ways that we can use message routing and below article will illustrate one of the ways to use Request / Reply Pattern with Java DSL. Here I'm using Active MQ as my message Broker.

    Apache ActiveMQ is one of the the most popular and powerful open source messaging and Integration Patterns server. Apache ActiveMQ is fast, supports many Cross Language Clients and Protocols, comes with easy to use Enterprise Integration Patterns. I like this because it has lot of features and useful tools. Also the latest ActiveMQ (5.9.0) is bundle with the hawtio web console which you can monitor all your queues.

    Hawtio itself you can monitor all your camel Contexts, Routers, ActiveMQ Queues ect.

    You can get more information on Apache ActiveMQ by going through following link - Apache ActiveMQ

    Now lets check the Sample code.

    Producer Sample

     public void myMethod() throws Exception {
      // TODO Auto-generated method stub
      CamelContext context = null;
      ProducerTemplate camelTemplate = null;
      context = new DefaultCamelContext();
      context.getProperties().put(Exchange.LOG_DEBUG_BODY_STREAMS, "true");
      // Connect to embedded ActiveMQComponent JMS broker
      ConnectionFactory connectionFactory = new ActiveMQConnectionFactory(
          + "&priorityBackup=true&timeout=40000");
      //Create the Template
      camelTemplate = context.createProducerTemplate();
      Object response = camelTemplate
            + "&asyncConsumer=true&asyncStartListener=true&concurrentConsumers=10"
            + "&useMessageIDAsCorrelationID=true",
            "mBodyMsg", "HeaderString", "HeaderValue");

    If you are implementing InOut pattern you need ensure that processed data set back to the same message queue.
    In order to achieve this you need to ensure that there is a RouteBuilder implemented which listen to the
    above queue and process data. And also set the response back to the same Queue.

    Below given the RouteBuilder Configure method implementation.

     public void configure() throws Exception {
      CamelContext context = null;
      try {
       // create CamelContext
       context = new DefaultCamelContext();
       ConnectionFactory connectionFactory = new ActiveMQConnectionFactory(
       context.addRoutes(new RouteBuilder() {
        public void configure() {
         // Error Handler with deadLetterChannel
         int num = GenerateRandomNumber.randInt(1, 100000);
           .setHeader("JMSMessageID", constant("ID : " + num))
           .process(new RequestProcess());
      } catch (Exception e) {
      } finally {
    Data Processing will happen in the RequestProcess class which should be extend with the Apache camel Processor. Below given the process method implementation.
     public void process(Exchange exchange) throws Exception {
      String body = exchange.getIn().getBody(String.class);
       * Process data and get the response and set the resposen to the Exchage
       * body.
      exchange.getOut().setBody(body + 
        "response; ID : " + exchange.getExchangeId());

    Tuesday, March 4, 2014

    Maven - Deploy war in Tomcat 7 & 8

    This article will explain on how to deploy a war fine in to Tomcat 7 through maven build.

    Note : I have tested same settings for Tomcat 8 as well. So below settings can be apply for both versions.

    • Configure tomcat7-maven-plugin in pom.xml
       <!-- Tomcat plugin -->
         <url>http:// localhost:8080/manager/text</url>
    • Configure user rights in the tomcat-user.xml which is in the tomcat conf file.
       <role rolename="tomcat" />
       <role rolename="manager-gui" />
       <role rolename="manager-script" />
       <role rolename="admin-gui" />
       <user username="tomcat" password="tomcatuser"
        roles="tomcat,manager-gui,admin-gui,manager-script" />
    •  Once above steps are followed, You can deploy the war by running following maven goal
    mvn tomcat7:redeploy

    More details of the Apache Tomcat Maven Plugin refer maven-plugin-2.2