gt:         DotConnect dc = new DotConnect();
context: package com.dotmarketing.startup.runonce;
  
  import java.sql.Connection;
  import java.sql.Date;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Types;
  import java.util.List;
  import java.util.Map;
  
  import com.dotmarketing.common.db.DotConnect;
  import com.dotmarketing.db.DbConnectionFactory;
  import com.dotmarketing.exception.DotDataException;
  import com.dotmarketing.exception.DotRuntimeException;
  import com.dotmarketing.startup.StartupTask;
  import com.dotmarketing.util.Logger;
  import com.dotmarketing.util.UtilMethods;
  
  /**
   * http://jira.dotmarketing.net/browse/DOTCMS-6383
   *
   * In this task we need to
   *
   * - Drop live,working,deleted,locked attributes
   * from contentlet,container,template,links,htmlpage,file_assets.
   * With associated indexs
   *
   * - Create new records for version_info tables associated
   * with identifiers linking working and live inodes.
   *
   * - Drop old triggers that enforced rules with live, working versions
   * not needed anymore
   *
   * @author Jorge Urdaneta
   */
  public class Task00795LiveWorkingToIdentifier implements StartupTask {
  
      protected void dropOldTriggers() throws DotDataException, SQLException {
          if(DbConnectionFactory.isPostgres()) {
              DotConnect dc = new DotConnect();
              dc.executeStatement(
                  "drop trigger if exists content_work_version_trigger ON contentlet");
              dc.executeStatement(
                  "drop function if exists content_work_version_check()");
              dc.executeStatement(
                  "drop trigger if exists file_asset_live_version_trigger ON file_asset");
              dc.executeStatement(
                  "drop function if exists file_asset_live_version_check()");
              dc.executeStatement(
                  "drop trigger if exists content_live_version_trigger ON contentlet");
              dc.executeStatement(
                  "drop function if exists content_live_version_check()");
          }
      }
  
      protected void createNewTables() throws DotDataException, SQLException {
          DotConnect dc = new DotConnect();
  
          dc.executeStatement(
          "create table contentlet_version_info (\n"+
          "   identifier varchar(36) not null,\n"+
          "   locked_by varchar(36),\n"+
          "   locked_on date,\n"+
          "   deleted bool not null,\n"+
          "   lang int8 not null,\n"+
          "   working_inode char(36) not null,\n"+
          "   live_inode varchar(36),\n"+
          "   primary key (identifier, lang)\n"+
          ")");
          dc.executeStatement(
          "create table container_version_info (\n"+
          "   identifier varchar(36) not null,\n"+
          "   working_inode varchar(36) not null,\n"+
          "   live_inode varchar(36),\n"+
          "   locked_by varchar(36),\n"+
          "   locked_on date,\n"+
          "   deleted bool not null,\n"+
          "   primary key (identifier)\n"+
          ")");
          dc.executeStatement(
          "create table template_version_info (\n"+
          "   identifier varchar(36) not null,\n"+
          "   working_inode varchar(36) not null,\n"+
          "   live_inode varchar(36),\n"+
          "   locked_by varchar(36),\n"+
          "   locked_on date,\n"+
          "   deleted bool not null,\n"+
          "   primary key (identifier)\n"+
          ")");
          dc.executeStatement(
          "create table htmlpage_version_info (\n"+
          "   identifier varchar(36) not null,\n"+
          "   working_inode varchar(36) not null,\n"+
          "   live_inode varchar(36),\n"+
          "   locked_by varchar(36),\n"+
          "   locked_on date,\n"+
          "   deleted bool not null,\n"+
          "   primary key (identifier)\n"+
          ")");
          dc.executeStatement(
          "create table fileasset_version_info (\n"+
          "   identifier varchar(36) not null,\n"+
          "   working_inode varchar(36) not null,\n"+
          "   live_inode varchar(36),\n"+
          "   locked_by varchar(36),\n"+
          "   locked_on date,\n"+
          "   deleted bool not null,\n"+
          "   primary key (identifier)\n"+
          ")");
          dc.executeStatement(
          "create table link_version_info (\n"+
          "   identifier varchar(36) not null,\n"+
          "   working_inode varchar(36) not null,\n"+
          "   live_inode varchar(36),\n"+
          "   locked_by varchar(36),\n"+
          "   locked_on date,\n"+
          "   deleted bool not null,\n"+
          "   primary key (identifier)\n"+
          ")");
      }
      protected void createNewTablesForOracle() throws DotDataException,SQLException{
      	DotConnect dc = new DotConnect();
          
          dc.executeStatement("create table contentlet_version_info (\n"
                  + "    identifier varchar2(36) not null,\n"
                  + "    deleted number(1,0) not null,\n"
                  + "    locked_by varchar2(100),\n" 
                  + "    locked_on date,\n"
                  + "    lang number(19,0) not null,\n"
                  + "    working_inode varchar2(36) not null,\n"
                  + "    live_inode varchar2(36),\n"
                  + "    primary key (identifier, lang)\n" + ")");
  
      	dc.executeStatement(
      			"create table container_version_info (\n" +
      			"      identifier varchar2(36) not null,\n" +
      			"	   working_inode varchar2(36) not null,\n" +
      			"	   live_inode varchar2(36),\n" +
   		        "      deleted number(1,0) not null,\n" +
      			"	   locked_by varchar2(100),\n" +
      			"	   locked_on date,\n" +
      			"	   primary key (identifier)\n" +
      			")");
  
      	dc.executeStatement(
      			"create table htmlpage_version_info (\n" +
      			"     identifier varchar2(36) not null,\n" +
      			"     working_inode varchar2(36) not null,\n" +
      			"     live_inode varchar2(36),\n" +
      			"     deleted number(1,0) not null,\n" +
      			"     locked_by varchar2(100),\n" +
      			"     locked_on date,\n" +
      			"     primary key (identifier)\n" +
      			")");
  
      	dc.executeStatement(
      			"create table fileasset_version_info (\n" +
      			"     identifier varchar2(36) not null,\n" +
      			"     working_inode varchar2(36) not null,\n" +
      			"     live_inode varchar2(36),\n" +
      			"     deleted number(1,0) not null,\n" +
                  "     locked_by varchar2(100),\n" +
                  "     locked_on date,\n" +
                  "     primary key (identifier)\n" +
                  ")");
  
      	dc.executeStatement(
      			"create table template_version_info (\n" +
      			"     identifier varchar2(36) not null,\n" +
      			"     working_inode varchar2(36) not null,\n" +
      			"     live_inode varchar2(36),\n" +
      			"     deleted number(1,0) not null,\n" +
      			"     locked_by varchar2(100),\n" +
      			"     locked_on date,\n" +
      			"     primary key (identifier)\n" +
      			")");
  
      	dc.executeStatement(
      			"create table link_version_info (\n" +
      			"	 identifier varchar2(36) not null,\n" +
      			"    working_inode varchar2(36) not null,\n" +
      			"    live_inode varchar2(36),\n" +
                  "    deleted number(1,0) not null,\n" +
                  "    locked_by varchar2(100),\n" +
                  "    locked_on date,\n" +
                  "    primary key (identifier)\n" +
      			")");
      }
      protected void createNewTablesForSQLServer() throws DotDataException, SQLException {
          DotConnect dc = new DotConnect();
          
          dc.executeStatement("create table contentlet_version_info (\n"
                              + "identifier varchar(36) not null,\n"
                              + "deleted tinyint not null,\n"
                              + "locked_by varchar(100) null,\n"
                              + "locked_on datetime null,\n"
                              + "lang numeric(19,0) not null,\n"
                              + "working_inode varchar(36) not null,\n"
                              + "live_inode varchar(36) null,\n"
                              + "primary key (identifier, lang)\n" 
                              + ")");
  
      	dc.executeStatement("create table container_version_info (\n" +
      			   			"identifier varchar(36) not null,\n" +
      			   			"working_inode varchar(36) not null,\n" +
      			   			"live_inode varchar(36) null,\n" +
      			   			"deleted tinyint not null,\n" +
      			   			"locked_by varchar(100) null,\n" +
      			   			"locked_on datetime null,\n" +
      			   			"primary key (identifier)\n" +
      						")");
      	dc.executeStatement("create table template_version_info (\n" +
      			   			"identifier varchar(36) not null,\n" +
      			   			"working_inode varchar(36) not null,\n" +
      			   			"live_inode varchar(36) null,\n" +
      			   			"deleted tinyint not null,\n" +
      			   			"locked_by varchar(100) null,\n" +
      			   			"locked_on datetime null,\n" +
      			   			"primary key (identifier)\n" +
      						")");
  
  
      	dc.executeStatement("create table htmlpage_version_info (\n" +
      			   			"identifier varchar(36) not null,\n" +
      			   			"working_inode varchar(36) not null,\n" +
      			   			"live_inode varchar(36) null,\n" +
      			   			"deleted tinyint not null,\n" +
      			   			"locked_by varchar(100) null,\n" +
      			   			"locked_on datetime null,\n" +
      			   			"primary key (identifier)\n" +
      						")");
  
      	dc.executeStatement("create table link_version_info (\n" +
      			   			"identifier varchar(36) not null,\n" +
      			   			"working_inode varchar(36) not null,\n" +
      			   			"live_inode varchar(36) null,\n" +
      			   			"deleted tinyint not null,\n" +
      			   			"locked_by varchar(100) null,\n" +
      			   			"locked_on datetime null,\n" +
      			   			"primary key (identifier)\n" +
      						")");
      	dc.executeStatement("create table fileasset_version_info (\n" +
      			   			"identifier varchar(36) not null,\n" +
      			   			"working_inode varchar(36) not null,\n" +
      			   			"live_inode varchar(36) null,\n" +
      			   			"deleted tinyint not null,\n" +
      			   			"locked_by varchar(100) null,\n" +
      			   			"locked_on datetime not null,\n" +
      			   			"primary key (identifier)\n" +
      			            ")");
  
      }
  
      protected void addNewForeignKeys() throws DotDataException, SQLException {
          DotConnect dc = new DotConnect();
          dc.executeStatement(
            "alter table contentlet_version_info add constraint fk_con_ver_info_ident         foreign key (identifier) references identifier(id) on delete cascade");
          dc.executeStatement(
            "alter table container_version_info  add constraint fk_container_ver_info_ident   foreign key (identifier) references identifier(id)");
          dc.executeStatement(
            "alter table template_version_info   add constraint fk_template_ver_info_ident    foreign key (identifier) references identifier(id)");
          dc.executeStatement(
            "alter table htmlpage_version_info   add constraint fk_htmlpage_ver_info_ident    foreign key (identifier) references identifier(id)");
          dc.executeStatement(
            "alter table fileasset_version_info  add constraint fk_fileasset_ver_info_ident   foreign key (identifier) references identifier(id)");
          dc.executeStatement(
            "alter table link_version_info       add constraint fk_link_ver_info_ident        foreign key (identifier) references identifier(id)");
          dc.executeStatement(
            "alter table container_version_info  add constraint fk_contain_ver_info_working   foreign key (working_inode) references containers(inode)");
          dc.executeStatement(
            "alter table template_version_info   add constraint fk_temp_ver_info_working      foreign key (working_inode) references template(inode)");
          dc.executeStatement(
            "alter table htmlpage_version_info   add constraint fk_htmlpage_ver_info_working  foreign key (working_inode) references htmlpage(inode)");
          dc.executeStatement(
            "alter table fileasset_version_info  add constraint fk_fileasset_ver_info_working foreign key (working_inode) references file_asset(inode)");
          dc.executeStatement(
            "alter table link_version_info       add constraint fk_link_version_info_working  foreign key (working_inode) references links(inode)");
          dc.executeStatement(
            "alter table contentlet_version_info add constraint fk_cont_version_info_working  foreign key (working_inode) references contentlet(inode)");
          dc.executeStatement(
            "alter table container_version_info  add constraint fk_container_ver_info_live    foreign key (live_inode) references containers(inode)");
          dc.executeStatement(
            "alter table template_version_info   add constraint fk_template_ver_info_live     foreign key (live_inode) references template(inode)");
          dc.executeStatement(
            "alter table htmlpage_version_info   add constraint fk_htmlpage_ver_info_live     foreign key (live_inode) references htmlpage(inode)");
          dc.executeStatement(
            "alter table fileasset_version_info  add constraint fk_fileasset_ver_info_live    foreign key (live_inode) references file_asset(inode)");
          dc.executeStatement(
            "alter table link_version_info       add constraint fk_link_version_info_live     foreign key (live_inode) references links(inode)");
          dc.executeStatement(
            "alter table contentlet_version_info add constraint fk_cont_version_info_live     foreign key (live_inode) references contentlet(inode)");
          dc.executeStatement(
            "alter table contentlet_version_info add constraint fk_cont_ver_info_lang         foreign key (lang) references language(id)");
          dc.executeStatement(
            "alter table contentlet              add constraint fk_contentlet_lang            foreign key (language_id) references language(id)");
          dc.executeStatement(
            "alter table contentlet_version_info add constraint FK_con_ver_lockedby           foreign key (locked_by) references user_(userid)");
          dc.executeStatement(
            "alter table container_version_info  add constraint FK_tainer_ver_info_lockedby   foreign key (locked_by) references user_(userid)");
          dc.executeStatement(
            "alter table template_version_info   add constraint FK_temp_ver_info_lockedby     foreign key (locked_by) references user_(userid)");
          dc.executeStatement(
            "alter table htmlpage_version_info   add constraint FK_page_ver_info_lockedby     foreign key (locked_by) references user_(userid)");
          dc.executeStatement(
            "alter table fileasset_version_info  add constraint FK_fil_ver_info_lockedby      foreign key (locked_by) references user_(userid)");
          dc.executeStatement(
            "alter table link_version_info       add constraint FK_link_ver_info_lockedby     foreign key (locked_by) references user_(userid)");
      }
  
      protected void associateWorking(String table) throws DotDataException, SQLException {
          Connection con = DbConnectionFactory.getConnection();
          String versionTable=UtilMethods.getVersionInfoTableName(table);
          PreparedStatement selectContentlets = null;
          final int limit=1000;
          
          Logger.info(this, "creating (working) version info records for "+table+" on "+versionTable);
          
          if(DbConnectionFactory.isOracle()){
          	  selectContentlets = con.prepareStatement(
          	          "select * from ( "+
          	          "   select identifier,inode,live,locked,mod_date,mod_user,deleted, row_number() over (order by inode) rn "+ 
          	          "   from "+table+" where working = 1 "+ 
          	          ") where rn >= ? and rn < ? order by identifier asc, mod_date desc");
          }else if(DbConnectionFactory.isMsSql()){
          	 selectContentlets = con.prepareStatement(
          			 " SELECT TOP " + limit  + "  * FROM (SELECT identifier,inode,live,locked,mod_date,mod_user,deleted,ROW_NUMBER() "
  	    		   + " OVER (order by mod_date) AS RowNumber FROM "+ table +" where working = "+DbConnectionFactory.getDBTrue()+") temp "
  	    		   + " WHERE RowNumber > ? order by RowNumber, identifier asc, mod_date desc");
  
          }else{
          	 selectContentlets = con.prepareStatement(
                       "select identifier,inode,live,locked,mod_date,mod_user,deleted from "+ table +
                       " where working="+DbConnectionFactory.getDBTrue()+" order by identifier asc, mod_date desc limit ? offset ? ");
          }
  
          PreparedStatement insertVersionInfo=con.prepareStatement(
                  "insert into "+versionTable+"(identifier,working_inode,locked_on,locked_by,deleted,live_inode) "+
                  " values (?,?,?,?,?,?)");
  
  
          int offset=0;
          boolean notDone=true;
          String lastId="";
          do {
          	if(DbConnectionFactory.isMsSql()){
          		selectContentlets.setInt(1, offset);
          	} else if(DbConnectionFactory.isOracle()) {
          	    selectContentlets.setInt(1, offset);
          	    selectContentlets.setInt(2, offset+limit);
          	} else{
          		selectContentlets.setInt(1, limit);
                  selectContentlets.setInt(2, offset);
          	}
              offset=offset+limit;
  
              notDone=false;
              ResultSet rs=selectContentlets.executeQuery();
              while(rs.next()) {
                  notDone=true;
                  final String inode=rs.getString("inode");
                  final String identifier=rs.getString("identifier");
                  if(UtilMethods.isSet(identifier) && !identifier.equals(lastId)) {
                      lastId=identifier;
                      insertVersionInfo.setString(1, identifier);
                      insertVersionInfo.setString(2, inode);
                      boolean locked=rs.getBoolean("locked");
                      if(locked) {
                          insertVersionInfo.setDate(3, rs.getDate("mod_date"));
                          insertVersionInfo.setString(4, rs.getString("mod_user"));
                      }
                      else {
                          insertVersionInfo.setDate(3, new Date(System.currentTimeMillis()));
                          insertVersionInfo.setNull(4, Types.VARCHAR);
                      }
      
                      insertVersionInfo.setBoolean(5, rs.getBoolean("deleted"));
      
                      boolean live=rs.getBoolean("live");
                      if(live)
                          insertVersionInfo.setString(6, inode);
                      else
                          insertVersionInfo.setNull(6, Types.VARCHAR);
      
                      insertVersionInfo.executeUpdate();
                  }
              }
              rs.close();
              //insertVersionInfo.executeBatch();
          } while(notDone);
  
          selectContentlets.close();
          insertVersionInfo.close();
      }
  
      protected void associateLiveNotWorking(String table) throws DotDataException {
  