Package com.dotmarketing.common.db

Examples of com.dotmarketing.common.db.DotConnect


        dc.executeStatement(
            "alter table links drop column locked");
    }

    protected void associateContentlets() throws DotDataException {
        DotConnect dc = new DotConnect();
        final int limit=1000;

        Logger.info(this,"creating version_info records for contentlets");

        // first working and (optionally) live
        String contentlets;
        if(DbConnectionFactory.isOracle()){
            contentlets =
                  "select * from ( "+
                  "  select identifier,inode,live,locked,mod_user,mod_date,deleted,language_id,row_number() over (order by inode) rn "+
                  "  from contentlet where working = 1 "+
                  ") where rn >= ? and rn < ? order by identifier asc, language_id asc, mod_date desc";
        }else if(DbConnectionFactory.isMsSql()){
            contentlets =  " SELECT TOP "+ limit + " *  FROM (SELECT identifier,inode,live,locked,mod_user,mod_date,deleted,language_id,ROW_NUMBER() "
                         + " OVER (order by mod_date) AS RowNumber FROM contentlet where working="+DbConnectionFactory.getDBTrue()
                         + ") temp WHERE RowNumber > ? order by RowNumber, identifier asc, language_id asc, mod_date desc";
        }else{
            contentlets = "select identifier,inode,live,locked,mod_user,mod_date,deleted,language_id from contentlet "
                       " where working="+DbConnectionFactory.getDBTrue()+" order by identifier asc, language_id asc, mod_date desc limit ? offset ? ";
        }

        int offset=0;
        boolean notDone;
        String lastId="";
        long lastLang=-1;
        do {

            dc.setSQL(contentlets);
            if(DbConnectionFactory.isMsSql()){
                dc.addParam(offset);
            } else if(DbConnectionFactory.isOracle()) {
                dc.addParam(offset);
                dc.addParam(offset+limit);
            } else{
              dc.addParam(limit);
                dc.addParam(offset);
            }
            offset=offset+limit;

            List<Map<String,Object>> results=dc.loadObjectResults();
            notDone=results.size()>0;

            for(Map<String,Object> rr : results) {
                String identifier=(String)rr.get("identifier");
                String inode=(String)rr.get("inode");
                long langId=Long.parseLong(rr.get("language_id").toString());

                if(UtilMethods.isSet(identifier) && !(langId==lastLang && identifier.equals(lastId)) ) {
                    lastLang=langId;
                    lastId=identifier;

                    boolean live = false;
                    boolean locked = false;
                    boolean deleted = false;

                    String liveStr=rr.get("live").toString().trim();
                    String lockedStr=rr.get("locked").toString().trim();
                    String deletedStr=rr.get("deleted").toString().trim();

                    if(liveStr.equalsIgnoreCase("true") || liveStr.equalsIgnoreCase("false"))
                        live = Boolean.parseBoolean(liveStr);
                    else if(liveStr.equals("1") || liveStr.equals("0"))
                        live = Integer.parseInt(liveStr)==1;

                    if(lockedStr.equalsIgnoreCase("true") || lockedStr.equalsIgnoreCase("false"))
                        locked = Boolean.parseBoolean(lockedStr);
                    else if(lockedStr.equals("1") || lockedStr.equals("0"))
                        locked = Integer.parseInt(lockedStr)==1;

                    if(deletedStr.equalsIgnoreCase("true") || deletedStr.equalsIgnoreCase("false"))
                        deleted = Boolean.parseBoolean(deletedStr);
                    else if(deletedStr.equals("1") || deletedStr.equals("0"))
                        deleted = Integer.parseInt(deletedStr)==1;

                    String mod_user=(String)rr.get("mod_user");
                    java.util.Date mod_date=(java.util.Date)rr.get("mod_date");
                    String insert="";

                  insert="insert into contentlet_version_info(identifier,locked_on,locked_by,deleted,lang,working_inode"+(live?",live_inode":"")+") values " +
                                                             "(?,?,?,?,?,?"+(live?",?":"")+")";
                    dc.setSQL(insert);
                    dc.addParam(identifier.trim());
                    if(locked) {
                        dc.addParam(mod_date);
                        dc.addParam(mod_user);
                    }
                    else {
                        dc.addParam(new java.util.Date());
                        dc.addObject(null);
                    }
                    dc.addParam(deleted);
                    dc.addParam(langId);
                    dc.addParam(inode);
                    if(live)
                        dc.addParam(inode);

                    try {
                      dc.loadResult();
                    } catch (DotDataException e) {
                      e.printStackTrace();
                    }
                }
            }
        } while(notDone);

        // now live not working
        if(DbConnectionFactory.isOracle()){
            contentlets =
                  "select * from ( "+
                  "  select identifier,inode,language_id,mod_date,row_number() over (order by inode) rn "+
                  "  from contentlet where working = 0 and live = 1 "+
                  ") where rn >= ? and rn < ? order by identifier asc, language_id asc, mod_date desc";
        }else if(DbConnectionFactory.isMsSql()){
            contentlets =  " SELECT TOP " + limit + " * FROM (SELECT identifier,inode,language_id,mod_date,ROW_NUMBER() "
                         + " OVER (order by mod_date) AS RowNumber FROM contentlet where working = "+DbConnectionFactory.getDBFalse()
                         + " and live ="+ DbConnectionFactory.getDBTrue()+ ") temp WHERE RowNumber > ? order by RowNumber, identifier asc, language_id asc, mod_date desc";
        }else{
            contentlets = "select identifier,inode,language_id from contentlet "
                        + " where working="+DbConnectionFactory.getDBFalse()+" and live="+DbConnectionFactory.getDBTrue()
                        + " order by identifier asc, language_id asc, mod_date desc limit ? offset ? ";
        }
        lastId="";
        lastLang=-1;
        offset=0;
        do {

            dc.setSQL(contentlets);
            if(DbConnectionFactory.isMsSql()){
              dc.addParam(offset);
            }else if(DbConnectionFactory.isOracle()) {
                dc.addParam(offset);
                dc.addParam(offset+limit);
            }else{
                dc.addParam(limit);
                dc.addParam(offset);
            }
            offset=offset+limit;
            List<Map<String,Object>> results=dc.loadObjectResults();
            notDone=results.size()>0;
            for(Map<String,Object> rr : results) {
                String identifier=(String)rr.get("identifier");
                String inode=(String)rr.get("inode");
                long langId=Long.parseLong(rr.get("language_id").toString());
                if(UtilMethods.isSet(identifier) && !(langId==lastLang && identifier.equals(lastId))) {
                    lastId=identifier;
                    lastLang=langId;

                    dc.setSQL("update contentlet_version_info set live_inode=? where identifier=? and lang=?");
                    dc.addParam(inode);
                    dc.addParam(identifier);
                    dc.addParam(langId);
                    dc.loadResult();
                }
            }
        } while(notDone);
    }
View Full Code Here


public class Task01015AddPublishExpireDateToIdentifier extends AbstractJDBCStartupTask {
   
    @Override
    public boolean forceRun() {
        try {
            DotConnect dc=new DotConnect();
            dc.setSQL("select publish_date,expire_date from identifier where publish_date>"
                      +DbConnectionFactory.getDBDateTimeFunction());
            dc.loadResult();
        }
        catch(Exception ex) {
            return true;
        }
        return false;
View Full Code Here

public class Task00815WorkFlowTablesChanges implements StartupTask{
 
  private void workflowTaskChanges() throws SQLException, DotDataException{
    String dropInode = "";
    DotConnect dc = new DotConnect();
    dropWorkFlowTaskIndexes();
    if (DbConnectionFactory.isMySql()){
      dropInode = "ALTER TABLE workflow_task DROP FOREIGN KEY fk441116055fb51eb;" +
                    " drop index fk441116055fb51eb on workflow_task;"+
            "ALTER TABLE workflow_task change inode id varchar(36);";
    }else if(DbConnectionFactory.isOracle()){
      dropInode = "ALTER TABLE workflow_task DROP CONSTRAINT fk441116055fb51eb;" +
                  "ALTER TABLE workflow_task add id varchar2(36);" +
                  "UPDATE workflow_task set id = cast(inode as varchar2(36));" +
                  "ALTER TABLE workflow_task drop column inode;" +
                  "ALTER TABLE workflow_task MODIFY (id NOT NULL);" +
                  "ALTER TABLE workflow_task ADD CONSTRAINT workflow_task_pkey PRIMARY KEY(id);";
    }else if(DbConnectionFactory.isMsSql()){
      dropInode = "ALTER TABLE workflow_task DROP CONSTRAINT fk441116055fb51eb;" +
                  "ALTER TABLE workflow_task DROP CONSTRAINT pk_workflow_task;" +
                "ALTER TABLE workflow_task add new_inode varchar(36);" +
                  "UPDATE workflow_task set new_inode = cast(inode as varchar(36));" +
                  "ALTER TABLE workflow_task drop column inode;" +
                  "EXEC SP_RENAME 'dbo.workflow_task.new_inode','id','COLUMN';" +
                  "ALTER TABLE workflow_task ALTER column id varchar(36) not null;" +
                  "ALTER TABLE workflow_task ADD CONSTRAINT workflow_task_pkey PRIMARY KEY(id);";
    }else{
      dropInode = "ALTER TABLE workflow_task DROP CONSTRAINT fk441116055fb51eb;" +
                "ALTER TABLE workflow_task add id varchar(36);" +
                  "UPDATE workflow_task set id = cast(inode as varchar(36));" +
                  "ALTER TABLE workflow_task drop column inode;" +
                  "ALTER TABLE workflow_task ALTER COLUMN id SET NOT NULL;" +
                  "ALTER TABLE workflow_task ADD CONSTRAINT workflow_task_pkey PRIMARY KEY(id);";
    }
    String createTable= "Create table workflowtask_files" +
               "(id varchar(36) NOT NULL  primary key," +
               "workflowtask_id varchar(36) NOT NULL," +
               "file_inode varchar(36) NOT NULL);";
    if(DbConnectionFactory.isOracle())
        createTable=createTable.replaceAll("varchar\\(", "varchar2\\(");
       
    String addFKs = "alter table workflowtask_files add constraint FK_workflow_id foreign key (workflowtask_id) references workflow_task(id);"
                + "alter table workflowtask_files add constraint FK_task_file_inode foreign key (file_inode) references file_asset(inode);";
   
    String workflowtask_fileasset_relations = "Select child,parent from tree where parent in(select id from workflow_task) and child in(select inode from file_asset)";
   
    String delete_task_file_relations = "Delete from tree where parent in(select id from workflow_task) and child in(select inode from file_asset)";
   
    List<String> dropInodeQueries = SQLUtil.tokenize(dropInode + createTable+addFKs);
    for(String dropInodeQuery :dropInodeQueries){
        try {
            dc.executeStatement(dropInodeQuery);
        } catch(Exception ex) {
            Logger.warn(this, ex.getMessage());
        }
    }
    addWorkFlowTaskIndexes();
    dc.setSQL(workflowtask_fileasset_relations);
    List<Map<String, String>> relations = dc.loadResults();
      for(Map<String,String> relation : relations){
        String fileInode = relation.get("child");
        String workflowTaskId = relation.get("parent")
        String uuid = UUIDGenerator.generateUuid();
      dc.setSQL("insert into workflowtask_files(id,workflowtask_id,file_inode) values(?,?,?)");
      dc.addParam(uuid);
      dc.addParam(workflowTaskId);
      dc.addParam(fileInode);
      dc.loadResult();
      }
    dc.executeStatement(delete_task_file_relations);
  }
View Full Code Here

      dc.loadResult();
      }
    dc.executeStatement(delete_task_file_relations);
  }
    private void workflowCommentChanges() throws SQLException, DotDataException{
      DotConnect dc = new DotConnect();
      String dropInode = "";
      if (DbConnectionFactory.isMySql()){
      dropInode = "ALTER TABLE workflow_comment DROP FOREIGN KEY fk94993ddf5fb51eb;" +
                  "drop index fk94993ddf5fb51eb on workflow_comment;" +
            "ALTER TABLE workflow_comment change inode id varchar(36);";
    }else if(DbConnectionFactory.isOracle()){
      dropInode = "ALTER TABLE workflow_comment DROP CONSTRAINT fk94993ddf5fb51eb;" +
                  "ALTER TABLE workflow_comment add id varchar2(36);" +
                  "UPDATE workflow_comment set id = cast(inode as varchar2(36));" +
                  "ALTER TABLE workflow_comment drop column inode;" +
                  "ALTER TABLE workflow_comment MODIFY (id NOT NULL);" +
                  "ALTER TABLE workflow_comment ADD CONSTRAINT workflow_comment_pkey PRIMARY KEY(id);";
    }else if(DbConnectionFactory.isMsSql()){
      dropInode = "ALTER TABLE workflow_comment DROP CONSTRAINT fk94993ddf5fb51eb;" +
                  "ALTER TABLE workflow_comment DROP CONSTRAINT pk_workflow_comment;" +
                "ALTER TABLE workflow_comment add new_inode varchar(36);" +
                  "UPDATE workflow_comment set new_inode = cast(inode as varchar(36));" +
                  "ALTER TABLE workflow_comment drop column inode;" +
                  "EXEC SP_RENAME 'dbo.workflow_comment.new_inode','id','COLUMN';" +
                  "ALTER TABLE workflow_comment ALTER column id varchar(36) not null;" +
                  "ALTER TABLE workflow_comment ADD CONSTRAINT workflow_comment_pkey PRIMARY KEY(id);";
    }else{
      dropInode = "ALTER TABLE workflow_comment DROP CONSTRAINT fk94993ddf5fb51eb;" +
                "ALTER TABLE workflow_comment add id varchar(36);" +
                  "UPDATE workflow_comment set id = cast(inode as varchar(36));" +
                  "ALTER TABLE workflow_comment drop column inode;" +
                  "ALTER TABLE workflow_comment ALTER COLUMN id SET NOT NULL;" +
                  "ALTER TABLE workflow_comment ADD CONSTRAINT workflow_comment_pkey PRIMARY KEY(id);";
    }
    String addWorkFlowCommentFK = "alter table workflow_comment add workflowtask_id varchar(36);" +
                    "alter table workflow_comment add constraint wf_id_comment_FK foreign key (workflowtask_id) references workflow_task(id);";
    if(DbConnectionFactory.isOracle())
        addWorkFlowCommentFK=addWorkFlowCommentFK.replaceAll("varchar\\(", "varchar2\\(");
   
    String workflowtask_workflowcomment_relations = "Select child,parent from tree where parent in(select id from workflow_task) and child in(select id from workflow_comment)";
   
    String deleteFromTree = "Delete from tree where parent in(select id from workflow_task) and child in(select id from workflow_comment)";
   
    List<String> queries = SQLUtil.tokenize(dropInode+addWorkFlowCommentFK);
    for(String query :queries){
        try {
            dc.executeStatement(query);
        } catch(Exception ex) {
            Logger.warn(this, ex.getMessage());
        }
    }
    dc.setSQL(workflowtask_workflowcomment_relations);
    List<Map<String, String>> relations = dc.loadResults();
      for(Map<String,String> relation : relations){
        String workflowCommentId = relation.get("child");
        String workflowTaskId = relation.get("parent")
        dc.setSQL("UPDATE workflow_comment set workflowtask_id = ? where id = ?");
      dc.addParam(workflowTaskId);
      dc.addParam(workflowCommentId);
      dc.loadResult();
      }
      dc.executeStatement(deleteFromTree);
  }
View Full Code Here

      }
      dc.executeStatement(deleteFromTree);
  }
    private void workflowHistoryChanges() throws SQLException, DotDataException{
      String dropInode = "";
      DotConnect dc = new DotConnect();
      if (DbConnectionFactory.isMySql()){
      dropInode = "ALTER TABLE workflow_history DROP FOREIGN KEY fk933334145fb51eb;" +
                  "drop index fk933334145fb51eb on workflow_history;"+
            "ALTER TABLE workflow_history change inode id varchar(36);";
    }else if(DbConnectionFactory.isOracle()){
      dropInode = "ALTER TABLE workflow_history DROP CONSTRAINT fk933334145fb51eb;" +
                  "ALTER TABLE workflow_history add id varchar2(36);" +
                  "UPDATE workflow_history set id = cast(inode as varchar2(36));" +
                  "ALTER TABLE workflow_history drop column inode;" +
                  "ALTER TABLE workflow_history MODIFY (id NOT NULL);" +
                  "ALTER TABLE workflow_history ADD CONSTRAINT workflow_history_pkey PRIMARY KEY(id);";
    }else if(DbConnectionFactory.isMsSql()){
      dropInode = "ALTER TABLE workflow_history DROP CONSTRAINT fk933334145fb51eb;" +
                  "ALTER TABLE workflow_history DROP CONSTRAINT pk_workflow_history;" +
                "ALTER TABLE workflow_history add new_inode varchar(36);" +
                  "UPDATE workflow_history set new_inode = cast(inode as varchar(36));" +
                  "ALTER TABLE workflow_history drop column inode;" +
                  "EXEC SP_RENAME 'dbo.workflow_history.new_inode','id','COLUMN';" +
                  "ALTER TABLE workflow_history ALTER column id varchar(36) not null;" +
                  "ALTER TABLE workflow_history ADD CONSTRAINT workflow_history_pkey PRIMARY KEY(id);";
    }else{
      dropInode = "ALTER TABLE workflow_history DROP CONSTRAINT fk933334145fb51eb;" +
                "ALTER TABLE workflow_history add id varchar(36);" +
                  "UPDATE workflow_history set id = cast(inode as varchar(36));" +
                  "ALTER TABLE workflow_history drop column inode;" +
                  "ALTER TABLE workflow_history ALTER COLUMN id SET NOT NULL;" +
                  "ALTER TABLE workflow_history ADD CONSTRAINT workflow_history_pkey PRIMARY KEY(id);";
    }
    String addWorkFlowHistoryFK = "alter table workflow_history add workflowtask_id varchar(36);" +
                        "alter table workflow_history add constraint wf_id_history_FK foreign key (workflowtask_id) references workflow_task(id)";
    if(DbConnectionFactory.isOracle())
        addWorkFlowHistoryFK=addWorkFlowHistoryFK.replaceAll("varchar\\(", "varchar2\\(");
   
    String workflowtask_workflowhistory_relations = "Select child,parent from tree where parent in(select id from workflow_task) and child in(select id from workflow_history)";
   
    String deleteFromTree = "Delete from tree where parent in(select id from workflow_task) and child in(select id from workflow_history)";
   
    List<String> queries = SQLUtil.tokenize(dropInode+addWorkFlowHistoryFK);
    for(String query :queries){
        try {
            dc.executeStatement(query);
        } catch(Exception ex) {
            Logger.warn(this, ex.getMessage());
        }
    }
   
    dc.setSQL(workflowtask_workflowhistory_relations);
    List<Map<String, String>> relations = dc.loadResults();
      for(Map<String,String> relation : relations){
        String workflowHistoryInode = relation.get("child");
        String workflowTaskInode = relation.get("parent")
        dc.setSQL("UPDATE workflow_history set workflowtask_id = ? where id = ?");
      dc.addParam(workflowTaskInode);
      dc.addParam(workflowHistoryInode);
      dc.loadResult();
      }
     
      dc.executeStatement(deleteFromTree);
    }
View Full Code Here

     
      dc.executeStatement(deleteFromTree);
    }
    private void dropWorkFlowTaskIndexes() throws SQLException{
      String indexes = "";
      DotConnect dc = new DotConnect();
      if(DbConnectionFactory.isOracle()||
                DbConnectionFactory.isPostgres()){
        indexes = "drop index idx_workflow_1;" +
                  "drop index idx_workflow_2;" +
                  "drop index idx_workflow_3;" +
                  "drop index idx_workflow_4;" +
                  "drop index idx_workflow_5;";
      }else if(DbConnectionFactory.isMsSql()){
        indexes = "drop index workflow_task.idx_workflow_1;" +
                  "drop index workflow_task.idx_workflow_2;" +
                  "drop index workflow_task.idx_workflow_3;" +
                  "drop index workflow_task.idx_workflow_4;" +
                  "drop index workflow_task.idx_workflow_5;";
      }
      List<String> indexList = SQLUtil.tokenize(indexes);
      for(String index:indexList){
        dc.executeStatement(index);
      }
    }
View Full Code Here

      for(String index:indexList){
        dc.executeStatement(index);
      }
    }
    private void addWorkFlowTaskIndexes() throws SQLException{
      DotConnect dc = new DotConnect();
      if(DbConnectionFactory.isOracle()||
                DbConnectionFactory.isPostgres() ||
                DbConnectionFactory.isMsSql()){
        dc.executeStatement("create index idx_workflow_4 on workflow_task (webasset)");
        dc.executeStatement("create index idx_workflow_5 on workflow_task (created_by)");
        dc.executeStatement("create index idx_workflow_2 on workflow_task (belongs_to)");
        dc.executeStatement("create index idx_workflow_3 on workflow_task (status)");
        dc.executeStatement("create index idx_workflow_1 on workflow_task (assigned_to)");
      }
    }
View Full Code Here

      }
    }

  public void executeUpgrade() throws DotDataException, DotRuntimeException {
    Connection conn = null;
    DotConnect dc = new DotConnect();
    HibernateUtil.startTransaction();
      try {
      conn = DbConnectionFactory.getConnection();
        if (DbConnectionFactory.isMySql())
         dc.executeStatement("SET storage_engine=INNODB", conn);
        if (DbConnectionFactory.isMsSql())
           dc.executeStatement("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");
        workflowTaskChanges();
        workflowCommentChanges();
        workflowHistoryChanges();
    } catch (SQLException e) {
      HibernateUtil.rollbackTransaction();
View Full Code Here

        "begin \n"+
        "select hello_world_bean_id_seq.nextval into :new.id from dual; \n"+
        "end;\n"+
        "/\n;";
    try{
      DotConnect dc = new DotConnect();
      if(DbConnectionFactory.getDBType().equals(DbConnectionFactory.POSTGRESQL)){
        dc.setSQL(PGCREATESQL);
        try {
          dc.loadResult();
        } catch (DotDataException e) {
          Logger.error(this, e.getMessage(), e);
        }
      }else if(DbConnectionFactory.getDBType().equals(DbConnectionFactory.MYSQL)){
        dc.setSQL(MYCREATESQL);
        try {
          dc.loadResult();
        } catch (DotDataException e) {
          Logger.error(this, e.getMessage(), e);
        }
      }else if(DbConnectionFactory.getDBType().equals(DbConnectionFactory.MSSQL)){
        dc.setSQL(MSCREATESQL);
        try {
          dc.loadResult();
        } catch (DotDataException e) {
          Logger.error(this, e.getMessage(), e);
        }
      }else{
        dc.setSQL(OCLCREATESQL);
        try {
          dc.loadResult();
          dc.setSQL(ORACLESEQSQL);
          dc.loadResult();
          dc.setSQL(ORACLETRIGGER);
          dc.loadResult();
        } catch (DotDataException e) {
          Logger.error(this, e.getMessage(), e);
        }     
      }
    }finally{
View Full Code Here

import com.dotmarketing.util.UtilMethods;

public class NotificationFactoryImpl extends NotificationFactory {

  public void saveNotification(Notification notification) throws DotDataException {
      DotConnect dc = new DotConnect();
    dc.setSQL("insert into notification (id,message,notification_type,notification_level,user_id,time_sent) "
            + " values(?,?,?,?,?,?)");

    if(!UtilMethods.isSet(notification.getId())) {
      notification.setId(UUID.randomUUID().toString());
    }

    dc.addParam(notification.getId());
    dc.addParam(notification.getMessage());
    dc.addParam(UtilMethods.isSet(notification.getType())?notification.getType().name():NotificationType.GENERIC.name());
    dc.addParam(notification.getLevel().name());
    dc.addParam(notification.getUserId());
    dc.addParam(new Date());
    dc.loadResult();
    CacheLocator.getNewNotificationCache().remove(notification.getUserId());
  }
View Full Code Here

TOP

Related Classes of com.dotmarketing.common.db.DotConnect

Copyright © 2018 www.massapicom. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.