cms_add_new_indexes_queries.properties

找了很久才找到到源代码

QUERY_SHOW_INDEX=\
SELECT pgc.relname \
  FROM pg_class pgc, \
  pg_index, \
  pg_class pgc1, \
  pg_authid \
  WHERE pgc.oid = pg_index.indexrelid \
  AND pg_index.indrelid = pgc1.oid \
  AND pgc1.relname='${tablename}' \
  AND pg_authid.oid = pgc.relowner \
  AND pg_authid.rolname = current_user \
  AND pgc.relname not in ( \
                           SELECT pg_constraint.conname \
                           FROM pg_class pgc, \
                           pg_constraint, \
                           pg_authid \
                           WHERE pgc.oid = pg_constraint.conrelid \
                           AND pg_authid.oid = pgc.relowner \
                           AND pgc.relname = '${tablename}' \
                           AND pg_authid.rolname = current_user \
                          )

Q_SHOW_CONSTRAINTS=\
SELECT pg_constraint.conname \
  FROM pg_class pgc, \
  pg_constraint, \
  pg_authid \
  WHERE pgc.oid = pg_constraint.conrelid \
  AND pg_authid.oid = pgc.relowner \
  AND pgc.relname = '${tablename}' \
  AND pg_authid.rolname = current_user
  
Q_DROP_INDEX=\
DROP INDEX ${indexname}

Q_DROP_CONSTRAINT=\
ALTER TABLE ${tablename} \
DROP CONSTRAINT ${indexname}

CMS_CONTENTS_PRIMARY_KEY=\
ALTER TABLE CMS_CONTENTS ADD CONSTRAINT PK_CONTENTS PRIMARY KEY(RESOURCE_ID, PUBLISH_TAG_FROM)

CMS_CONTENTS_01_IDX_INDEX=\
CREATE INDEX CMS_CONTENTS_01_IDX \
    ON CMS_CONTENTS (RESOURCE_ID\, PUBLISH_TAG_TO) \
    TABLESPACE ${indexTablespace}
    
CMS_CONTENTS_02_IDX_INDEX=\
CREATE INDEX CMS_CONTENTS_02_IDX \
    ON CMS_CONTENTS (RESOURCE_ID) \
    TABLESPACE ${indexTablespace}  
    
CMS_CONTENTS_03_IDX_INDEX=\
CREATE INDEX CMS_CONTENTS_03_IDX \
    ON CMS_CONTENTS (PUBLISH_TAG_FROM) \
    TABLESPACE ${indexTablespace}
    
CMS_CONTENTS_04_IDX_INDEX=\
CREATE INDEX CMS_CONTENTS_04_IDX \
    ON CMS_CONTENTS (PUBLISH_TAG_TO) \
    TABLESPACE ${indexTablespace}
    
CMS_CONTENTS_05_IDX_INDEX=\
CREATE INDEX CMS_CONTENTS_05_IDX \
    ON CMS_CONTENTS (RESOURCE_ID\, ONLINE_FLAG) \
    TABLESPACE ${indexTablespace}
    
    
CMS_GROUPS_PRIMARY_KEY=\
ALTER TABLE CMS_GROUPS ADD CONSTRAINT PK_GROUPS PRIMARY KEY(GROUP_ID)

CMS_GROUPS_UNIQUE_KEY_GROUPS=\
ALTER TABLE CMS_GROUPS ADD CONSTRAINT UK_GROUPS UNIQUE(GROUP_NAME, GROUP_OU)

CMS_GROUPS_01_IDX_INDEX=\
CREATE INDEX CMS_GROUPS_01_IDX \
    ON CMS_GROUPS (PARENT_GROUP_ID) \
    TABLESPACE ${indexTablespace}

CMS_GROUPS_02_IDX_INDEX=\
CREATE INDEX CMS_GROUPS_02_IDX \
    ON CMS_GROUPS (GROUP_NAME) \
    TABLESPACE ${indexTablespace}
    
CMS_GROUPS_03_IDX_INDEX=\
CREATE INDEX CMS_GROUPS_03_IDX \
    ON CMS_GROUPS (GROUP_OU) \
    TABLESPACE ${indexTablespace}
    
    
CMS_GROUPUSERS_PRIMARY_KEY=\
ALTER TABLE CMS_GROUPUSERS ADD CONSTRAINT PK_GROUPUSERS PRIMARY KEY(GROUP_ID, USER_ID)

CMS_GROUPUSERS_01_IDX_INDEX=\
CREATE INDEX CMS_GROUPUSERS_01_IDX \
    ON CMS_GROUPUSERS (GROUP_ID) \
    TABLESPACE ${indexTablespace}

CMS_GROUPUSERS_02_IDX_INDEX=\
CREATE INDEX CMS_GROUPUSERS_02_IDX \
    ON CMS_GROUPUSERS (USER_ID) \
    TABLESPACE ${indexTablespace}
    

CMS_OFFLINE_ACCESSCONTROL_PRIMARY_KEY=\
ALTER TABLE CMS_OFFLINE_ACCESSCONTROL ADD CONSTRAINT PK_OFFLINE_ACCESSCONTROL PRIMARY KEY(RESOURCE_ID, PRINCIPAL_ID) 

CMS_OFFLINE_ACCESSCONTROL_01_IDX_INDEX=\
CREATE INDEX OFFLINE_ACCESSCONTROL_01_IDX \
    ON CMS_OFFLINE_ACCESSCONTROL (PRINCIPAL_ID) \
    TABLESPACE ${indexTablespace}
    
CMS_OFFLINE_ACCESSCONTROL_02_IDX_INDEX=\
CREATE INDEX OFFLINE_ACCESSCONTROL_02_IDX \
    ON CMS_OFFLINE_ACCESSCONTROL (RESOURCE_ID) \
    TABLESPACE ${indexTablespace}
    
    
CMS_OFFLINE_CONTENTS_PRIMARY_KEY=\
ALTER TABLE CMS_OFFLINE_CONTENTS ADD CONSTRAINT PK_OFFLINE_CONTENTS PRIMARY KEY(RESOURCE_ID) 


CMS_OFFLINE_PROPERTIES_PRIMARY_KEY=\
ALTER TABLE CMS_OFFLINE_PROPERTIES ADD CONSTRAINT PK_OFFLINE_PROPERTIES PRIMARY KEY(PROPERTY_ID) 

CMS_OFFLINE_PROPERTIES_UNIQUE_KEY_PROPERTIES=\
ALTER TABLE CMS_OFFLINE_PROPERTIES ADD CONSTRAINT UK_OFFLINE_PROPERTIES UNIQUE(PROPERTYDEF_ID, PROPERTY_MAPPING_ID) 

CMS_OFFLINE_PROPERTIES_01_IDX_INDEX=\
CREATE INDEX CMS_OFFLINE_PROPERTIES_01_IDX \
    ON CMS_OFFLINE_PROPERTIES (PROPERTYDEF_ID) \
    TABLESPACE ${indexTablespace}
    
CMS_OFFLINE_PROPERTIES_02_IDX_INDEX=\
CREATE INDEX CMS_OFFLINE_PROPERTIES_02_IDX \
    ON CMS_OFFLINE_PROPERTIES (PROPERTY_MAPPING_ID) \
    TABLESPACE ${indexTablespace}
    

CMS_OFFLINE_PROPERTYDEF_PRIMARY_KEY=\
ALTER TABLE CMS_OFFLINE_PROPERTYDEF ADD CONSTRAINT PK_OFFLINE_PROPERTYDEF PRIMARY KEY(PROPERTYDEF_ID) 

CMS_OFFLINE_PROPERTYDEF_UNIQUE_KEY_PROPERTYDEF=\
ALTER TABLE CMS_OFFLINE_PROPERTYDEF ADD CONSTRAINT UK_OFFLINE_PROPERTYDEF UNIQUE(PROPERTYDEF_NAME) 


CMS_OFFLINE_RESOURCES_PRIMARY_KEY=\
ALTER TABLE CMS_OFFLINE_RESOURCES ADD CONSTRAINT PK_OFFLINE_RESOURCES PRIMARY KEY(RESOURCE_ID) 

CMS_OFFLINE_RESOURCES_01_IDX_INDEX=\
CREATE INDEX CMS_OFFLINE_RESOURCES_01_IDX \
    ON CMS_OFFLINE_RESOURCES (PROJECT_LASTMODIFIED) \
    TABLESPACE ${indexTablespace}

CMS_OFFLINE_RESOURCES_02_IDX_INDEX=\
CREATE INDEX CMS_OFFLINE_RESOURCES_02_IDX \
    ON CMS_OFFLINE_RESOURCES (PROJECT_LASTMODIFIED, RESOURCE_SIZE) \
    TABLESPACE ${indexTablespace}

CMS_OFFLINE_RESOURCES_03_IDX_INDEX=\
CREATE INDEX CMS_OFFLINE_RESOURCES_03_IDX \
    ON CMS_OFFLINE_RESOURCES (RESOURCE_SIZE) \
    TABLESPACE ${indexTablespace}
    
CMS_OFFLINE_RESOURCES_04_IDX_INDEX=\
CREATE INDEX CMS_OFFLINE_RESOURCES_04_IDX \
    ON CMS_OFFLINE_RESOURCES (DATE_LASTMODIFIED) \
    TABLESPACE ${indexTablespace}
    
CMS_OFFLINE_RESOURCES_05_IDX_INDEX=\
CREATE INDEX CMS_OFFLINE_RESOURCES_05_IDX \
    ON CMS_OFFLINE_RESOURCES (RESOURCE_TYPE) \
    TABLESPACE ${indexTablespace}
    
    
CMS_OFFLINE_STRUCTURE_PRIMARY_KEY=\
ALTER TABLE CMS_OFFLINE_STRUCTURE ADD CONSTRAINT PK_OFFLINE_STRUCTURE PRIMARY KEY (STRUCTURE_ID)

CMS_OFFLINE_STRUCTURE_01_IDX_INDEX=\
CREATE INDEX CMS_OFFLINE_STRUCTURE_01_IDX \
    ON CMS_OFFLINE_STRUCTURE (STRUCTURE_ID, RESOURCE_PATH) \
    TABLESPACE ${indexTablespace}
    
CMS_OFFLINE_STRUCTURE_02_IDX_INDEX=\
CREATE INDEX CMS_OFFLINE_STRUCTURE_02_IDX \
    ON CMS_OFFLINE_STRUCTURE (RESOURCE_PATH, RESOURCE_ID) \
    TABLESPACE ${indexTablespace}
    
CMS_OFFLINE_STRUCTURE_03_IDX_INDEX=\
CREATE INDEX CMS_OFFLINE_STRUCTURE_03_IDX \
    ON CMS_OFFLINE_STRUCTURE (STRUCTURE_ID, RESOURCE_ID) \
    TABLESPACE ${indexTablespace}

CMS_OFFLINE_STRUCTURE_04_IDX_INDEX=\
CREATE INDEX CMS_OFFLINE_STRUCTURE_04_IDX \
    ON CMS_OFFLINE_STRUCTURE (STRUCTURE_STATE) \
    TABLESPACE ${indexTablespace}

CMS_OFFLINE_STRUCTURE_05_IDX_INDEX=\
CREATE INDEX CMS_OFFLINE_STRUCTURE_05_IDX \
    ON CMS_OFFLINE_STRUCTURE (PARENT_ID) \
    TABLESPACE ${indexTablespace}
    
CMS_OFFLINE_STRUCTURE_06_IDX_INDEX=\
CREATE INDEX CMS_OFFLINE_STRUCTURE_06_IDX \
    ON CMS_OFFLINE_STRUCTURE (RESOURCE_ID) \
    TABLESPACE ${indexTablespace}

CMS_OFFLINE_STRUCTURE_07_IDX_INDEX=\
CREATE INDEX CMS_OFFLINE_STRUCTURE_07_IDX \
    ON CMS_OFFLINE_STRUCTURE (RESOURCE_PATH) \
    TABLESPACE ${indexTablespace}

    
CMS_ONLINE_ACCESSCONTROL_PRIMARY_KEY=\
ALTER TABLE CMS_ONLINE_ACCESSCONTROL ADD CONSTRAINT PK_ONLINE_ACCESSCONTROL PRIMARY KEY(RESOURCE_ID, PRINCIPAL_ID)

CMS_ONLINE_ACCESSCONTROL_01_IDX_INDEX=\
CREATE INDEX ONLINE_ACCESSCONTROL_01_IDX \
    ON CMS_ONLINE_ACCESSCONTROL (PRINCIPAL_ID) \
    TABLESPACE ${indexTablespace}
    
CMS_ONLINE_ACCESSCONTROL_02_IDX_INDEX=\
CREATE INDEX ONLINE_ACCESSCONTROL_02_IDX \
    ON CMS_ONLINE_ACCESSCONTROL (RESOURCE_ID) \
    TABLESPACE ${indexTablespace}
      
CMS_ONLINE_PROPERTIES_PRIMARY_KEY=\
ALTER TABLE CMS_ONLINE_PROPERTIES ADD CONSTRAINT PK_ONLINE_PROPERTIES PRIMARY KEY(PROPERTY_ID)

CMS_ONLINE_PROPERTIES_UNIQUE_KEY_PROPERTIES=\
ALTER TABLE CMS_ONLINE_PROPERTIES ADD CONSTRAINT UK_ONLINE_PROPERTIES UNIQUE(PROPERTYDEF_ID, PROPERTY_MAPPING_ID)
CMS_ONLINE_PROPERTIES_01_IDX_INDEX=\
CREATE INDEX CMS_ONLINE_PROPERTIES_01_IDX \
    ON CMS_ONLINE_PROPERTIES (PROPERTYDEF_ID) \
    TABLESPACE ${indexTablespace}
    
CMS_ONLINE_PROPERTIES_02_IDX_INDEX=\
CREATE INDEX CMS_ONLINE_PROPERTIES_02_IDX \
    ON CMS_ONLINE_PROPERTIES (PROPERTY_MAPPING_ID) \
    TABLESPACE ${indexTablespace}
    

CMS_ONLINE_PROPERTYDEF_PRIMARY_KEY=\
ALTER TABLE CMS_ONLINE_PROPERTYDEF ADD CONSTRAINT PK_ONLINE_PROPERTYDEF PRIMARY KEY(PROPERTYDEF_ID) 

CMS_ONLINE_PROPERTYDEF_UNIQUE_KEY_PROPERTYDEF=\
ALTER TABLE CMS_ONLINE_PROPERTYDEF ADD CONSTRAINT UK_ONLINE_PROPERTYDEF UNIQUE(PROPERTYDEF_NAME) 


CMS_ONLINE_RESOURCES_PRIMARY_KEY=\
ALTER TABLE CMS_ONLINE_RESOURCES ADD CONSTRAINT PK_ONLINE_RESOURCES PRIMARY KEY(RESOURCE_ID)

CMS_ONLINE_RESOURCES_01_IDX_INDEX=\
CREATE INDEX CMS_ONLINE_RESOURCES_01_IDX \
    ON CMS_ONLINE_RESOURCES (PROJECT_LASTMODIFIED) \
    TABLESPACE ${indexTablespace}

CMS_ONLINE_RESOURCES_02_IDX_INDEX=\
CREATE INDEX CMS_ONLINE_RESOURCES_02_IDX \
    ON CMS_ONLINE_RESOURCES (PROJECT_LASTMODIFIED, RESOURCE_SIZE) \
    TABLESPACE ${indexTablespace}

CMS_ONLINE_RESOURCES_03_IDX_INDEX=\
CREATE INDEX CMS_ONLINE_RESOURCES_03_IDX \
    ON CMS_ONLINE_RESOURCES (RESOURCE_SIZE) \
    TABLESPACE ${indexTablespace}

CMS_ONLINE_RESOURCES_04_IDX_INDEX=\
CREATE INDEX CMS_ONLINE_RESOURCES_04_IDX \
    ON CMS_ONLINE_RESOURCES (DATE_LASTMODIFIED) \
    TABLESPACE ${indexTablespace}
    
CMS_ONLINE_RESOURCES_05_IDX_INDEX=\
CREATE INDEX CMS_ONLINE_RESOURCES_05_IDX \
    ON CMS_ONLINE_RESOURCES (RESOURCE_TYPE) \
    TABLESPACE ${indexTablespace}
    
    
CMS_ONLINE_STRUCTURE_PRIMARY_KEY=\
ALTER TABLE CMS_ONLINE_STRUCTURE ADD CONSTRAINT PK_ONLINE_STRUCTURE PRIMARY KEY (STRUCTURE_ID)

CMS_ONLINE_STRUCTURE_01_IDX_INDEX=\
CREATE INDEX CMS_ONLINE_STRUCTURE_01_IDX \
    ON CMS_ONLINE_STRUCTURE (STRUCTURE_ID, RESOURCE_PATH) \
    TABLESPACE ${indexTablespace}

CMS_ONLINE_STRUCTURE_02_IDX_INDEX=\
CREATE INDEX CMS_ONLINE_STRUCTURE_02_IDX \
    ON CMS_ONLINE_STRUCTURE (RESOURCE_PATH, RESOURCE_ID) \
    TABLESPACE ${indexTablespace}

CMS_ONLINE_STRUCTURE_03_IDX_INDEX=\
CREATE INDEX CMS_ONLINE_STRUCTURE_03_IDX \
    ON CMS_ONLINE_STRUCTURE (STRUCTURE_ID, RESOURCE_ID) \
    TABLESPACE ${indexTablespace}

CMS_ONLINE_STRUCTURE_04_IDX_INDEX=\
CREATE INDEX CMS_ONLINE_STRUCTURE_04_IDX \
    ON CMS_ONLINE_STRUCTURE (STRUCTURE_STATE) \
    TABLESPACE ${indexTablespace}

CMS_ONLINE_STRUCTURE_05_IDX_INDEX=\
CREATE INDEX CMS_ONLINE_STRUCTURE_05_IDX \
    ON CMS_ONLINE_STRUCTURE (PARENT_ID) \
    TABLESPACE ${indexTablespace}

CMS_ONLINE_STRUCTURE_06_IDX_INDEX=\
CREATE INDEX CMS_ONLINE_STRUCTURE_06_IDX \
    ON CMS_ONLINE_STRUCTURE (RESOURCE_ID) \
    TABLESPACE ${indexTablespace}

CMS_ONLINE_STRUCTURE_07_IDX_INDEX=\
CREATE INDEX CMS_ONLINE_STRUCTURE_07_IDX \
    ON CMS_ONLINE_STRUCTURE (RESOURCE_PATH) \
    TABLESPACE ${indexTablespace}
    
    
CMS_PROJECTRESOURCES_PRIMARY_KEY=\
ALTER TABLE CMS_PROJECTRESOURCES ADD CONSTRAINT PK_PROJECTRESOURCES PRIMARY KEY(PROJECT_ID, RESOURCE_PATH)

CMS_PROJECTRESOURCES_01_IDX_INDEX=\
CREATE INDEX CMS_PROJECTRESOURCES_01_IDX \
    ON CMS_PROJECTRESOURCES (RESOURCE_PATH) \
    TABLESPACE ${indexTablespace}
    
    
CMS_PROJECTS_PRIMARY_KEY=\
ALTER TABLE CMS_PROJECTS ADD CONSTRAINT PK_PROJECTS PRIMARY KEY(PROJECT_ID)

CMS_PROJECTS_UNIQUE_KEY_PROJECTS=\
ALTER TABLE CMS_PROJECTS ADD CONSTRAINT UK_PROJECTS UNIQUE(PROJECT_OU,PROJECT_NAME,DATE_CREATED) 

CMS_PROJECTS_01_IDX_INDEX=\
CREATE INDEX CMS_PROJECTS_01_IDX \
    ON CMS_PROJECTS (PROJECT_FLAGS) \
    TABLESPACE ${indexTablespace}

CMS_PROJECTS_02_IDX_INDEX=\
CREATE INDEX CMS_PROJECTS_02_IDX \
    ON CMS_PROJECTS (GROUP_ID) \
    TABLESPACE ${indexTablespace}
    
CMS_PROJECTS_03_IDX_INDEX=\
CREATE INDEX CMS_PROJECTS_03_IDX \
    ON CMS_PROJECTS (MANAGERGROUP_ID) \
    TABLESPACE ${indexTablespace}

CMS_PROJECTS_04_IDX_INDEX=\
CREATE INDEX CMS_PROJECTS_04_IDX \
    ON CMS_PROJECTS (USER_ID) \
    TABLESPACE ${indexTablespace}

CMS_PROJECTS_05_IDX_INDEX=\
CREATE INDEX CMS_PROJECTS_05_IDX \
    ON CMS_PROJECTS (PROJECT_NAME) \
    TABLESPACE ${indexTablespace}

CMS_PROJECTS_06_IDX_INDEX=\
CREATE INDEX CMS_PROJECTS_06_IDX \
    ON CMS_PROJECTS (PROJECT_OU) \
    TABLESPACE ${indexTablespace}
    
CMS_PROJECTS_07_IDX_INDEX=\
CREATE INDEX CMS_PROJECTS_07_IDX \
    ON CMS_PROJECTS (PROJECT_OU,PROJECT_NAME) \
    TABLESPACE ${indexTablespace}
    
    
CMS_PUBLISH_HISTORY_PRIMARY_KEY=\
ALTER TABLE CMS_PUBLISH_HISTORY ADD CONSTRAINT PK_PUBLISH_HISTORY PRIMARY KEY (HISTORY_ID, PUBLISH_TAG, STRUCTURE_ID, RESOURCE_PATH)

CMS_PUBLISH_HISTORY_01_IDX_INDEX=\
CREATE INDEX CMS_PUBLISH_HISTORY_01_IDX \
    ON CMS_PUBLISH_HISTORY (PUBLISH_TAG) \
    TABLESPACE ${indexTablespace}
    
    
CMS_STATICEXPORT_LINKS_PRIMARY_KEY=\
ALTER TABLE CMS_STATICEXPORT_LINKS ADD CONSTRAINT PK_CMS_STATICEXPORT_LINKS PRIMARY KEY (LINK_ID) 

CMS_STATICEXPORT_LINKS_01_IDX_INDEX=\
CREATE INDEX CMS_STATICEXPORT_LINKS_01_IDX \
    ON CMS_STATICEXPORT_LINKS (LINK_RFS_PATH) \
    TABLESPACE ${indexTablespace}    
    
    
CMS_USERS_PRIMARY_KEY=\
ALTER TABLE CMS_USERS ADD CONSTRAINT PK_USERS PRIMARY KEY(USER_ID) 

CMS_USERS_UNIQUE_KEY_USERS=\
ALTER TABLE CMS_USERS ADD CONSTRAINT UK_USERS UNIQUE(USER_NAME, USER_OU)

CMS_USERS_01_IDX_INDEX=\
CREATE INDEX CMS_USERS_01_IDX \
    ON CMS_USERS (USER_NAME) \
    TABLESPACE ${indexTablespace}
    
CMS_USERS_02_IDX_INDEX=\
CREATE INDEX CMS_USERS_02_IDX \
    ON CMS_USERS (USER_OU) \
    TABLESPACE ${indexTablespace}