query.properties

cms是开源的框架

C_RESOURCES_SELECT_ATTRIBS=\
    CMS_${PROJECT}_STRUCTURE.STRUCTURE_ID,\
	CMS_${PROJECT}_STRUCTURE.RESOURCE_ID,\
	CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH,\
	CMS_${PROJECT}_STRUCTURE.STRUCTURE_STATE,\
	CMS_${PROJECT}_STRUCTURE.DATE_RELEASED,\
	CMS_${PROJECT}_STRUCTURE.DATE_EXPIRED,\
	CMS_${PROJECT}_RESOURCES.RESOURCE_ID,\
	CMS_${PROJECT}_RESOURCES.RESOURCE_TYPE,\
	CMS_${PROJECT}_RESOURCES.RESOURCE_FLAGS,\
	CMS_${PROJECT}_RESOURCES.RESOURCE_STATE,\
	CMS_${PROJECT}_RESOURCES.DATE_CREATED,\
	CMS_${PROJECT}_RESOURCES.DATE_LASTMODIFIED,\
	CMS_${PROJECT}_RESOURCES.USER_CREATED,\
	CMS_${PROJECT}_RESOURCES.USER_LASTMODIFIED,\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED AS LOCKED_IN_PROJECT,\
	CMS_${PROJECT}_RESOURCES.RESOURCE_SIZE,\
	CMS_${PROJECT}_RESOURCES.SIBLING_COUNT
	
	
C_FILES_SELECT_ATTRIBS=\
CMS_${PROJECT}_STRUCTURE.*,\
CMS_${PROJECT}_RESOURCES.*,\
CMS_${PROJECT}_CONTENTS.CONTENT_ID,\
CMS_${PROJECT}_CONTENTS.FILE_CONTENT,\
CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED AS LOCKED_IN_PROJECT

C_FILES_SELECT_ATTRIBS_BACKUP=\
CMS_BACKUP_RESOURCES.*,\
CMS_BACKUP_STRUCTURE.*,\
CMS_BACKUP_CONTENTS.BACKUP_ID,\
CMS_BACKUP_CONTENTS.CONTENT_ID,\
CMS_BACKUP_CONTENTS.FILE_CONTENT,\
CMS_BACKUP_CONTENTS.PUBLISH_TAG,\
CMS_BACKUP_CONTENTS.VERSION_ID,\
CMS_BACKUP_RESOURCES.PROJECT_LASTMODIFIED AS LOCKED_IN_PROJECT

	
C_JOIN_STRUCTURE_RESOURCE=CMS_${PROJECT}_STRUCTURE.RESOURCE_ID=CMS_${PROJECT}_RESOURCES.RESOURCE_ID
C_JOIN_STRUCTURE_RESOURCE_BACKUP=CMS_BACKUP_STRUCTURE.RESOURCE_ID=CMS_BACKUP_RESOURCES.RESOURCE_ID AND CMS_BACKUP_STRUCTURE.BACKUP_ID=CMS_BACKUP_RESOURCES.BACKUP_ID

C_RESOURCES_SELECT_TABLES=CMS_${PROJECT}_STRUCTURE \
			  INNER JOIN CMS_${PROJECT}_RESOURCES \
			  ON CMS_${PROJECT}_STRUCTURE.RESOURCE_ID=CMS_${PROJECT}_RESOURCES.RESOURCE_ID
	
C_RESOURCES_SELECT_TABLES_BACKUP=CMS_BACKUP_STRUCTURE \
				 INNER JOIN CMS_BACKUP_RESOURCES \
				 ON CMS_BACKUP_STRUCTURE.RESOURCE_ID=CMS_BACKUP_RESOURCES.RESOURCE_ID AND CMS_BACKUP_STRUCTURE.BACKUP_ID=CMS_BACKUP_RESOURCES.BACKUP_ID

C_JOIN_RESOURCE_FILE=INNER JOIN CMS_${PROJECT}_CONTENTS \
		     ON CMS_${PROJECT}_CONTENTS.RESOURCE_ID=CMS_${PROJECT}_RESOURCES.RESOURCE_ID

C_JOIN_RESOURCE_FILE_BACKUP=INNER JOIN CMS_BACKUP_CONTENTS \
		            ON CMS_BACKUP_RESOURCES.RESOURCE_ID=CMS_BACKUP_CONTENTS.RESOURCE_ID \
		            AND CMS_BACKUP_RESOURCES.BACKUP_ID=CMS_BACKUP_CONTENTS.BACKUP_ID


#
# SQL statements
#
# PostgreSql specific because of the row limitation
C_PROJECTS_READLAST_BACKUP=\
SELECT \
	PUBLISH_TAG,\
	PROJECT_ID,\
	PROJECT_NAME,\
	PROJECT_PUBLISHDATE,\
	PROJECT_PUBLISHED_BY,\
	PROJECT_PUBLISHED_BY_NAME,\
	USER_ID,\
	USER_NAME,\
	GROUP_ID,\
	GROUP_NAME,\
	MANAGERGROUP_ID,\
	MANAGERGROUP_NAME,\
	PROJECT_DESCRIPTION,\
	DATE_CREATED,\
	PROJECT_TYPE,\
	TASK_ID \
FROM \
	CMS_BACKUP_PROJECTS \
ORDER BY \
	PUBLISH_TAG DESC \
LIMIT ?


####
C_STRUCTURE_WRITE=\
INSERT INTO CMS_${PROJECT}_STRUCTURE \
	(${C_TABLE_STRUCTURE_ATTRIBS},PARENT_ID) \
VALUES \
	(${C_TABLE_STRUCTURE_VALUES},?)

####	
C_RESOURCES_UPDATE_RESOURCES=\
UPDATE \
	CMS_${PROJECT}_RESOURCES \
SET \
	RESOURCE_TYPE=?,\
	RESOURCE_FLAGS=?,\
	DATE_LASTMODIFIED=?,\
	USER_LASTMODIFIED=?,\
	RESOURCE_STATE=?,\
	RESOURCE_SIZE=?,\
	PROJECT_LASTMODIFIED=?,\
	SIBLING_COUNT=? \
WHERE \
	CMS_${PROJECT}_RESOURCES.RESOURCE_ID=?
	

####
C_RESOURCES_UPDATE_STRUCTURE=\
UPDATE \
	CMS_${PROJECT}_STRUCTURE \
SET \
	RESOURCE_ID=?,\
	RESOURCE_PATH=?,\
	STRUCTURE_STATE=?, \
	DATE_RELEASED=?, \
	DATE_EXPIRED=?, \
	PARENT_ID=? \
WHERE \
	CMS_${PROJECT}_STRUCTURE.STRUCTURE_ID=?
	
	
####
C_RESOURCES_READ_ALL_VERSIONS_BACKUP=\
SELECT \
	${C_RESOURCES_SELECT_ATTRIBS_BACKUP},\
	CMS_BACKUP_RESOURCES.PROJECT_LASTMODIFIED AS LOCKED_IN_PROJECT \
FROM \
	${C_RESOURCES_SELECT_TABLES_BACKUP} \
WHERE \
	(CMS_BACKUP_STRUCTURE.RESOURCE_PATH=? OR CMS_BACKUP_RESOURCES.RESOURCE_ID=?) \
ORDER BY \
	CMS_BACKUP_STRUCTURE.VERSION_ID ASC

####
C_RESOURCES_READ_ALL_BACKUP=\
SELECT \
	${C_RESOURCES_SELECT_ATTRIBS_BACKUP},\
	CMS_BACKUP_RESOURCES.PROJECT_LASTMODIFIED AS LOCKED_IN_PROJECT \
FROM \
	${C_RESOURCES_SELECT_TABLES_BACKUP} \
ORDER BY \
	CMS_BACKUP_RESOURCES.VERSION_ID DESC
	
####
C_RESOURCES_READ=\
SELECT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH=? \
ORDER BY \
	CMS_${PROJECT}_STRUCTURE.STRUCTURE_STATE ASC
	
	
####
C_RESOURCES_READBYID=\
SELECT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_STRUCTURE.STRUCTURE_ID=?

	
####	
C_RESOURCES_GET_SUBRESOURCES=\
SELECT \
	${C_RESOURCES_SELECT_ATTRIBS} \
FROM \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_STRUCTURE.PARENT_ID=?

	
	
### C_RESOURCES_GET_RESOURCE_WITH_PROPERTYDEF
C_RESOURCES_GET_RESOURCE_WITH_PROPERTYDEF=\
(SELECT DISTINCT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	CMS_${PROJECT}_PROPERTIES INNER JOIN \
            (CMS_${PROJECT}_RESOURCES INNER JOIN CMS_${PROJECT}_STRUCTURE \
                ON CMS_${PROJECT}_RESOURCES.RESOURCE_ID = CMS_${PROJECT}_STRUCTURE.RESOURCE_ID) \
        ON CMS_${PROJECT}_PROPERTIES.PROPERTY_MAPPING_ID = CMS_${PROJECT}_RESOURCES.RESOURCE_ID \
WHERE \
	CMS_${PROJECT}_PROPERTIES.PROPERTYDEF_ID =? \
	AND CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH LIKE ?) \
UNION \
(SELECT DISTINCT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
        CMS_${PROJECT}_PROPERTIES INNER JOIN \
            (CMS_${PROJECT}_RESOURCES INNER JOIN CMS_${PROJECT}_STRUCTURE \
	       ON CMS_${PROJECT}_RESOURCES.RESOURCE_ID = CMS_${PROJECT}_STRUCTURE.RESOURCE_ID) \
          ON CMS_${PROJECT}_PROPERTIES.PROPERTY_MAPPING_ID = CMS_${PROJECT}_STRUCTURE.STRUCTURE_ID \
WHERE \
	CMS_${PROJECT}_PROPERTIES.PROPERTYDEF_ID =? \
	AND CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH LIKE ?) \
ORDER BY RESOURCE_PATH DESC


### C_RESOURCES_GET_RESOURCE_WITH_PROPERTYDEF_VALUE
C_RESOURCES_GET_RESOURCE_WITH_PROPERTYDEF_VALUE=\
(SELECT DISTINCT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	CMS_${PROJECT}_PROPERTIES INNER JOIN \
            (CMS_${PROJECT}_RESOURCES INNER JOIN CMS_${PROJECT}_STRUCTURE \
                ON CMS_${PROJECT}_RESOURCES.RESOURCE_ID = CMS_${PROJECT}_STRUCTURE.RESOURCE_ID) \
        ON CMS_${PROJECT}_PROPERTIES.PROPERTY_MAPPING_ID = CMS_${PROJECT}_RESOURCES.RESOURCE_ID \
WHERE \
	CMS_${PROJECT}_PROPERTIES.PROPERTYDEF_ID=? \
	AND CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH LIKE ? \
	AND CMS_${PROJECT}_PROPERTIES.PROPERTY_VALUE LIKE ?) \
UNION \
(SELECT DISTINCT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	CMS_${PROJECT}_PROPERTIES INNER JOIN \
            (CMS_${PROJECT}_RESOURCES INNER JOIN CMS_${PROJECT}_STRUCTURE \
	       ON CMS_${PROJECT}_RESOURCES.RESOURCE_ID = CMS_${PROJECT}_STRUCTURE.RESOURCE_ID) \
          ON CMS_${PROJECT}_PROPERTIES.PROPERTY_MAPPING_ID = CMS_${PROJECT}_STRUCTURE.STRUCTURE_ID \
WHERE \
	CMS_${PROJECT}_PROPERTIES.PROPERTYDEF_ID =? \
	AND CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH LIKE ? \
	AND CMS_${PROJECT}_PROPERTIES.PROPERTY_VALUE LIKE ?) \
ORDER BY RESOURCE_PATH DESC
	
####
C_RESOURCES_GET_RESOURCE_IN_PROJECT_WITH_STATE=\
SELECT DISTINCT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED =? \
	AND \
	(\
		(\
			CMS_${PROJECT}_RESOURCES.RESOURCE_STATE=? \
			AND CMS_${PROJECT}_STRUCTURE.STRUCTURE_STATE<=?\
		)\
		OR \
		(\
			CMS_${PROJECT}_STRUCTURE.STRUCTURE_STATE=? \
			AND CMS_${PROJECT}_RESOURCES.RESOURCE_STATE<=?\
		)\
	) \
ORDER BY \
	CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH ASC

####
C_RESOURCES_GET_RESOURCE_IN_PROJECT_WITHOUT_STATE=\
SELECT DISTINCT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED =? \
	AND \
	(\
		CMS_${PROJECT}_RESOURCES.RESOURCE_STATE<>? \
		OR CMS_${PROJECT}_STRUCTURE.STRUCTURE_STATE<>?\
	)\
ORDER BY \
	CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH ASC	
	
####	
C_RESOURCES_GET_RESOURCE_IN_PROJECT_IGNORE_STATE=\
SELECT DISTINCT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED =? \
ORDER BY \
	CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH ASC	
		

####	
C_RESOURCE_REPLACE=\
UPDATE \
	CMS_${PROJECT}_RESOURCES \
SET \
	RESOURCE_TYPE=?,\
	RESOURCE_SIZE=? \
WHERE \
	CMS_${PROJECT}_RESOURCES.RESOURCE_ID=?


####
C_RESOURCES_UPDATE_SIBLING_COUNT=\
UPDATE \
	CMS_${PROJECT}_RESOURCES \
SET \
	SIBLING_COUNT=? \
WHERE \
	CMS_${PROJECT}_RESOURCES.RESOURCE_ID=?
	
	
####
C_RESOURCES_UPDATE_FLAGS=\
UPDATE \
	CMS_${PROJECT}_RESOURCES \
SET \
	RESOURCE_FLAGS=? \
WHERE \
	CMS_${PROJECT}_RESOURCES.RESOURCE_ID=?
	

####	
C_RESOURCES_UPDATE_RESOURCE_STATE=\
UPDATE \
	CMS_${PROJECT}_RESOURCES \
SET \
	RESOURCE_STATE=?, \
    	PROJECT_LASTMODIFIED=? \
WHERE \
	CMS_${PROJECT}_RESOURCES.RESOURCE_ID=?
	
	
####
C_RESOURCES_UPDATE_RESOURCE_STATELASTMODIFIED=\
UPDATE \
	CMS_${PROJECT}_RESOURCES \
SET \
	RESOURCE_STATE=?, \
        DATE_LASTMODIFIED=?, \
        USER_LASTMODIFIED=?, \
        PROJECT_LASTMODIFIED=? \
WHERE \
	CMS_${PROJECT}_RESOURCES.RESOURCE_ID=?	
	
####
C_RESOURCES_UPDATE_STRUCTURE_STATE=\
UPDATE \
	CMS_${PROJECT}_STRUCTURE \
SET \
	STRUCTURE_STATE=? \
WHERE \
	CMS_${PROJECT}_STRUCTURE.STRUCTURE_ID=?
	
	
####
C_RESOURCES_UPDATE_RELEASE_EXPIRED=\
UPDATE \
	CMS_${PROJECT}_STRUCTURE \
SET \
	DATE_RELEASED=?, \
    	DATE_EXPIRED=? \
WHERE \
	CMS_${PROJECT}_STRUCTURE.STRUCTURE_ID=?	
	

####
C_RESOURCES_UPDATE_PROJECT_LASTMODIFIED=\
UPDATE \
	CMS_${PROJECT}_RESOURCES \
SET \