Examples of com.dotmarketing.common.db.DotConnect

com.dotmarketing.common.db.DotConnect
Description of the Class @author will @created February 25, 2002 Modified May 8, 2003 Hashtables converted toHashMaps results is now an arraylist

        return "";
    }


    @Override
    public String getOracleScript() {
      DotConnect dc = new DotConnect();
        dc.setSQL( "select 1 from user_tables where table_name = 'PUBLISHING_BUNDLE'" );
        StringBuilder query = new StringBuilder();


        try {
      List<Map<String, Object>> res = dc.loadObjectResults();
      if(res.isEmpty()) {
        query.append("create table publishing_bundle(id varchar2(36) NOT NULL  primary key,name varchar2(255) NOT NULL,publish_date TIMESTAMP,expire_date TIMESTAMP,owner varchar2(100));\n");
        query.append("create table publishing_bundle_environment(id varchar2(36) NOT NULL primary key,bundle_id varchar2(36) NOT NULL,environment_id varchar2(36) NOT NULL);\n");
        query.append("alter table publishing_bundle_environment add constraint FK_bundle_id foreign key (bundle_id) references publishing_bundle(id);\n");
        query.append("alter table publishing_bundle_environment add constraint FK_environment_id foreign key (environment_id) references publishing_environment(id);\n");

View Full Code Here

import com.dotmarketing.util.Logger;
import com.dotmarketing.util.UtilMethods;


public class Task00790DataModelChangesForWebAssets implements StartupTask {
  private void deleteMappingsFromTree() throws SQLException{
    DotConnect dc = new DotConnect();
    String delete_host_containers = "Delete from tree where parent in(select distinct host_inode from identifier) and " 
                              + "child in(select inode from inode where type ='containers')";
    
    String delete_host_template = "Delete from tree where parent in(select distinct host_inode from identifier) and " 
                        + "child in(select inode from inode where type ='template')";
    
    String delete_host_folders =  "Delete from tree where parent in(select distinct host_inode from identifier) and " 
                             + "child in(select inode from inode where type ='folder')";
    
    String delete_folder_folder = "Delete from tree where parent in(select inode from inode where type='folder') and " 
                  + "child in(select inode from inode where type='folder')";
    
    String delete_structure_containers = "Delete from tree where child in(select inode from inode where type='containers') and " 
                       + "parent in(select inode from structure)";
    
    String delete_template_htmlpage = "Delete from tree where child in(select inode from inode where type='htmlpage') and " 
                    + "parent in(select identifier from template)";
    
    dc.executeStatement(delete_host_containers);
    dc.executeStatement(delete_host_template);
    dc.executeStatement(delete_host_folders);
    dc.executeStatement(delete_folder_folder);
    dc.executeStatement(delete_structure_containers);
    dc.executeStatement(delete_template_htmlpage);
  }

View Full Code Here

    dc.executeStatement(delete_structure_containers);
    dc.executeStatement(delete_template_htmlpage);
  }
    
  private void containerTableChanges() throws DotDataException, SQLException {
    DotConnect dc = new DotConnect();
    String addStructure = "ALTER TABLE containers add structure_inode varchar(36)";
    if(DbConnectionFactory.isOracle())
        addStructure=addStructure.replaceAll("varchar\\(", "varchar2\\(");
    String addFK = "ALTER TABLE containers add constraint structure_fk foreign key (structure_inode) references structure(inode)";
    String containerQuery = "Select * from tree where child in(Select inode from inode where type='containers') and " 
           + "parent in(select inode from structure)";
    dc.executeStatement(addStructure);
    dc.executeStatement(addFK);
    
    dc.setSQL(containerQuery);
    List<Map<String, String>> treeResults = dc.loadResults();
    for(Map<String,String> tree : treeResults){
      String stInode = tree.get("parent");
      String containerInode = tree.get("child");
      dc.setSQL("UPDATE containers set structure_inode = ? where inode = ?");
      dc.addParam(stInode);
      dc.addParam(containerInode);
      dc.loadResult();
    }
  }

View Full Code Here

      dc.loadResult();
    }
  }
  
  private void htmlpageTableChanges() throws SQLException, DotDataException {
    DotConnect dc = new DotConnect();
    String addtemplate = "ALTER TABLE htmlpage add template_id varchar(36)";
    if(DbConnectionFactory.isOracle())
        addtemplate=addtemplate.replaceAll("varchar\\(", "varchar2\\(");
    String addFK = "ALTER TABLE htmlpage add constraint template_id_fk foreign key (template_id) references identifier(id)";
    String htmlQuery = "Select * from tree where child in(Select inode from inode where type='htmlpage') and " 
           + "parent in(select identifier from template)";
    
    dc.executeStatement(addtemplate);
    dc.executeStatement(addFK);
    
    dc.setSQL(htmlQuery);
    List<Map<String, String>> treeResults = dc.loadResults();
    for(Map<String,String> tree : treeResults){
      String templateId = tree.get("parent");
      String htmlpageInode = tree.get("child");
      dc.setSQL("UPDATE htmlpage set template_id = ? where inode = ?");
      dc.addParam(templateId);
      dc.addParam(htmlpageInode);
      dc.loadResult();
    }
    addTriggerToHTMLPage();
  }

View Full Code Here

    }
    addTriggerToHTMLPage();
  }
  
  private void triggerChanges() throws SQLException {
    DotConnect dc = new DotConnect();
    String trigger = "";
    if(DbConnectionFactory.isPostgres()){
       trigger =   "CREATE OR REPLACE FUNCTION structure_host_folder_check() RETURNS trigger AS '\n" +
                "DECLARE\n" +
                   "folderInode varchar(36);\n" +
                   "hostInode varchar(36);\n" +
                     "BEGIN\n" +
                   "IF ((tg_op = ''INSERT'' OR tg_op = ''UPDATE'') AND (NEW.host IS NOT NULL AND NEW.host <> '''' AND NEW.host <> ''SYSTEM_HOST''\n" + 
                         "AND NEW.folder IS NOT NULL AND NEW.folder <> ''SYSTEM_FOLDER'' AND NEW.folder <> '''')) THEN\n" +
                           "select host_inode,folder.inode INTO hostInode,folderInode from folder,identifier where folder.identifier = identifier.id and folder.inode=NEW.folder;\n" +
                         "IF (FOUND AND NEW.host = hostInode) THEN\n" +
                            "RETURN NEW;\n" +
                         "ELSE\n" +
                            "RAISE EXCEPTION ''Cannot assign host/folder to structure, folder does not belong to given host'';\n" +
                            "RETURN NULL;\n" +
                         "END IF;\n" +
                       "ELSE\n" +
                        "IF((tg_op = ''INSERT'' OR tg_op = ''UPDATE'') AND (NEW.host IS NULL OR NEW.host = '''' OR NEW.host= ''SYSTEM_HOST''\n" + 
                            "OR NEW.folder IS NULL OR NEW.folder = '''' OR NEW.folder = ''SYSTEM_FOLDER'')) THEN\n" +
                             "IF(NEW.host = ''SYSTEM_HOST'' OR NEW.host IS NULL OR NEW.host = '''') THEN\n" +
                                 "NEW.host = ''SYSTEM_HOST'';\n" +
                                 "NEW.folder = ''SYSTEM_FOLDER'';\n" +
                               "END IF;\n" +
                             "IF(NEW.folder = ''SYSTEM_FOLDER'' OR NEW.folder IS NULL OR NEW.folder = '''') THEN\n" +
                                 "NEW.folder = ''SYSTEM_FOLDER'';\n" +
                             "END IF;\n" +
                           "RETURN NEW;\n" +
                           "END IF;\n" +
                     "END IF;\n" +
                     "RETURN NULL;\n" +
                  "END\n" +
                    "' LANGUAGE plpgsql;";
    }else if(DbConnectionFactory.isOracle()){
      trigger = "CREATE OR REPLACE TRIGGER structure_host_folder_trigger\n" +
                 "BEFORE INSERT OR UPDATE ON structure\n" +
                 "FOR EACH ROW\n" +
                 "DECLARE\n" +
                     "folderInode varchar2(36);\n" +
                     "hostInode varchar2(36);\n" +
                   "BEGIN\n" +
                     "IF (:NEW.host <> 'SYSTEM_HOST' AND :NEW.folder <> 'SYSTEM_FOLDER') THEN\n" +
                       "select host_inode, folder.inode INTO hostInode, folderInode from folder,identifier where folder.identifier = identifier.id and folder.inode = :NEW.folder;\n" +
                     "IF (:NEW.host <> hostInode) THEN\n" +
                       "RAISE_APPLICATION_ERROR(-20000, 'Cannot assign host/folder to structure, folder does not belong to given host');\n" +
                     "END IF;\n" +
                     "ELSE\n" +
                       "IF(:NEW.host IS NULL OR :NEW.host = '' OR :NEW.host = 'SYSTEM_HOST' OR :NEW.folder IS NULL OR :NEW.folder = '' OR :NEW.folder = 'SYSTEM_FOLDER') THEN\n" +
                         "IF(:NEW.host = 'SYSTEM_HOST' OR :NEW.host IS NULL OR :NEW.host = '') THEN\n" +
                           ":NEW.host := 'SYSTEM_HOST';\n" +
                           ":NEW.folder := 'SYSTEM_FOLDER';\n" +
                         "END IF;\n" +
                         "IF(:NEW.folder = 'SYSTEM_FOLDER' OR :NEW.folder IS NULL OR :NEW.folder = '') THEN\n" +
                           ":NEW.folder := 'SYSTEM_FOLDER';\n" +
                           "END IF;\n" +
                         "END IF;\n" +
                       "END IF;\n" +
                   "END;\n" +
                   "/";
    }else if(DbConnectionFactory.isMsSql()){
      trigger = "ALTER TRIGGER structure_host_folder_trigger\n" +
                 "ON structure\n" +
                 "FOR INSERT, UPDATE AS\n" +
                 "DECLARE @newFolder varchar(100)\n" + 
                 "DECLARE @newHost varchar(100)\n" +
                 "DECLARE @folderInode varchar(36)\n" + 
                 "DECLARE @hostInode varchar(36)\n" +
                 "DECLARE cur_Inserted3 cursor LOCAL FAST_FORWARD for\n" +
                 "Select folder, host\n" +
                 "from inserted\n" + 
                 "for Read Only\n" +
                 "open cur_Inserted3\n" +
                 "fetch next from cur_Inserted3 into @newFolder,@newHost\n" +
                 "while @@FETCH_STATUS <> -1\n" +
                 "BEGIN\n" +
                    "IF (@newHost <> 'SYSTEM_HOST' AND @newFolder <> 'SYSTEM_FOLDER')\n" + 
                      "BEGIN\n" +
                       "SELECT @hostInode = identifier.host_inode, @folderInode = folder.inode from folder,identifier where folder.identifier = identifier.id and folder.inode = @newFolder\n" +
                        "IF (@folderInode IS NULL OR @folderInode = '' OR @newHost <> @hostInode)\n" + 
                       "BEGIN\n" +
                           "RAISERROR (N'Cannot assign host/folder to structure, folder does not belong to given host', 10, 1)\n" +
                         "ROLLBACK WORK\n" +
                       "END\n" +
                      "END\n" +
                 "fetch next from cur_Inserted3 into @newFolder,@newHost\n" +
                 "END;";
    }
    List<String> triggers = SQLUtil.tokenize(trigger);
    for(String t:triggers){
      dc.executeStatement(t);
    }
  }

View Full Code Here

      dc.executeStatement(t);
    }
  }
  
  private void addTriggerToHTMLPage() throws SQLException {
    DotConnect dc = new DotConnect();
    String trigger="";
    if(DbConnectionFactory.isPostgres()){
      trigger = "CREATE OR REPLACE FUNCTION check_template_id()RETURNS trigger AS '\n" +
              "DECLARE\n" +        
                  "templateId varchar(36);\n" +   
              "BEGIN\n" +    
                  "IF (tg_op = ''INSERT'' OR tg_op = ''UPDATE'') THEN\n" +  
                    "select id into templateId from identifier where asset_type=''template'' and id = NEW.template_id;\n" +     
                    "IF FOUND THEN\n" +          
                      "RETURN NEW;\n" +       
                    "ELSE\n" +          
                      "RAISE EXCEPTION ''Template Id should be the identifier of a template'';\n" +        
                      "RETURN NULL;\n" +       
                    "END IF;\n" +   
                  "END IF;\n" + 
              "RETURN NULL;\n" +  
              "END\n" +
              "' LANGUAGE plpgsql;\n" +
              "CREATE TRIGGER check_template_identifier\n" +
              "BEFORE INSERT OR UPDATE\n" +
              "ON htmlpage\n" +
              "FOR EACH ROW\n" +
              "EXECUTE PROCEDURE check_template_id();";
    } else if(DbConnectionFactory.isMsSql()){
             trigger = "CREATE Trigger check_template_identifier\n" +
                    "ON htmlpage\n" +
                    "FOR INSERT,UPDATE AS\n" +  
                    "DECLARE @templateId varchar(36)\n" +
                    "DECLARE @tempIdentifier varchar(36)\n" +
                    "DECLARE htmlpage_cur_Inserted cursor LOCAL FAST_FORWARD for\n" +
                    "Select template_id\n" +
                    "from inserted\n" + 
                    "for Read Only\n" +
                    "open htmlpage_cur_Inserted\n" +   
                    "fetch next from htmlpage_cur_Inserted into @templateId\n" +
                    "while @@FETCH_STATUS <> -1\n" + 
                    "BEGIN\n" +
                    "select @tempIdentifier = id from identifier where asset_type='template' and id = @templateId\n" +      
                    "IF (@tempIdentifier IS NULL)\n" +        
                    "BEGIN\n" +           
                    "RAISERROR (N'Template Id should be the identifier of a template', 10, 1)\n" +          
                    "ROLLBACK WORK\n" +        
                    "END\n"+ 
                    "fetch next from htmlpage_cur_Inserted into @templateId\n" +
                    "END;";       
    } else if(DbConnectionFactory.isOracle()){
      trigger = "CREATE OR REPLACE TRIGGER  check_template_identifier \n" + 
                "BEFORE INSERT OR UPDATE ON htmlpage\n" + 
              "FOR EACH ROW\n" +
              "DECLARE\n" +        
              "rowcount varchar2(100);\n" +   
              "BEGIN\n" +
               "select count(*) into rowcount from identifier where id= :NEW.template_id and asset_type='template';\n" +     
                 "IF (rowcount = 0) THEN\n" +    
                    "RAISE_APPLICATION_ERROR(-20000, 'Template Id should be the identifier of a template');\n" +      
                 "END IF;\n" +   
              "END;";


    }else{
      String mysqlTrigger = "DROP TRIGGER IF EXISTS check_templateId_when_insert;\n" +
                        "CREATE TRIGGER check_templateId_when_insert BEFORE INSERT\n" +
                        "on htmlpage\n" +
                        "FOR EACH ROW\n" +
                        "BEGIN\n" +
                        "DECLARE identCount INT;\n" +
                        "select count(id) into identCount from identifier where id = NEW.template_id and asset_type='template';\n" +
                        "IF(identCount = 0) THEN\n" +
                        "UPDATE htmlpage set template_id = NEW.template_id where id = NEW.inode;\n" + 
                        "END IF;\n" +
                        "END\n" +
                        "#" +
                   "DROP TRIGGER IF EXISTS check_templateId_when_update;\n" +
                      "CREATE TRIGGER check_templateId_when_update  BEFORE UPDATE\n" +
                      "on htmlpage\n" +
                      "FOR EACH ROW\n" +
                      "BEGIN\n" +
                      "DECLARE identCount INT;\n" +
                      "select count(id)into identCount from identifier where id = NEW.template_id and asset_type='template';\n" +
                      "IF(identCount = 0) THEN\n" +
                      "UPDATE htmlpage set template_id=NEW.template_id where id = NEW.inode;\n" + 
                      "END IF;\n" +
                      "END\n" +
                      "#";
      List<String> triggers = SQLUtil.tokenize(mysqlTrigger);
      for(String t:triggers){
        dc.executeStatement(t);
      }
    }
    if(UtilMethods.isSet(trigger)){
      dc.executeStatement(trigger);
    }
  }

View Full Code Here

    public boolean forceRun() {
        return true;
    }


    void dropConstraint() throws SQLException {
        DotConnect dc = new DotConnect();
        String alterSql = "";
        if (DbConnectionFactory.isMySql())
          alterSql = "alter table workflowtask_files drop foreign key FK_task_file_inode ";
        else
          alterSql = "alter table workflowtask_files drop constraint FK_task_file_inode ";


        dc.executeStatement(alterSql);


    }

View Full Code Here

      DbConnectionFactory.getConnection().setAutoCommit(true);
    } catch (SQLException e) {
      throw new DotDataException(e.getMessage(), e);
    }
    try {
      DotConnect dc=new DotConnect();
      createPushedAssetsTable(dc);
      ClusterFactory.initialize();
    } catch (SQLException e) {
      throw new DotRuntimeException(e.getMessage(),e);
    }

View Full Code Here

public class Task03005CreateModDateForFieldIfNeeded extends AbstractJDBCStartupTask {


    @Override
    public boolean forceRun() {
        try {
            DotConnect dc=new DotConnect();
            dc.setSQL("select mod_date from field");
            dc.loadResult();
        }
        catch(Exception ex) {
            return true;
        }
        return false;

View Full Code Here

        return true;
    }


    void alterProcedure() throws SQLException {
        if (DbConnectionFactory.isMsSql()) {
          DotConnect dc = new DotConnect();
          String alterSql = "alter table tag drop constraint tag_tagname_host;" +
              "alter table tag alter column tagname nvarchar(255) null;" +
              "alter table tag add constraint tag_tagname_host unique (tagname, host_id);";
          dc.executeStatement(alterSql);
        }
    }

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

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

com.dotcms.content.elasticsearch.business.ESContentFactoryImpl

com.dotcms.content.elasticsearch.business.ESContentFactoryImplTest

com.dotcms.content.elasticsearch.business.ESContentletAPIImpl

com.dotcms.content.elasticsearch.business.ESContentletIndexAPI

com.dotcms.content.elasticsearch.business.ESMappingAPIImpl

com.dotcms.content.elasticsearch.business.IndiciesFactoryImpl

com.dotcms.content.elasticsearch.util.ESReindexationProcessStatus

com.dotcms.journal.business.ESDistributedJournalFactoryImpl

com.dotcms.notifications.business.NotificationFactoryImpl

com.dotcms.publisher.assets.business.PushedAssetsFactoryImpl

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.