query.properties

一个cms内容管理平台

	PROJECT_LASTMODIFIED=? \
WHERE \
	CMS_${PROJECT}_RESOURCES.RESOURCE_ID=?	

####
C_RESOURCES_PROJECTVIEW=\
SELECT \
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED,\
	${C_RESOURCES_SELECT_ATTRIBS} \
FROM \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED=?

#
# General subtree selection statement
#

####
C_RESOURCES_READ_TREE=\
SELECT ${C_RESOURCES_SELECT_ATTRIBS},CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM ${C_RESOURCES_SELECT_TABLES}


#
# Files
#

####
C_FILES_READ=\
SELECT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_CONTENTS.CONTENT_ID,\
	CMS_${PROJECT}_CONTENTS.FILE_CONTENT,\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	((${C_RESOURCES_SELECT_TABLES})  \
	${C_JOIN_RESOURCE_FILE}) \
WHERE \
	CMS_${PROJECT}_STRUCTURE.STRUCTURE_ID=?  \
ORDER BY \
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED DESC
	
####
C_FILES_READ_BACKUP=\
SELECT \
	${C_RESOURCES_SELECT_ATTRIBS_BACKUP},\
	CMS_BACKUP_CONTENTS.CONTENT_ID,\
	CMS_BACKUP_CONTENTS.FILE_CONTENT,\
	CMS_BACKUP_RESOURCES.PROJECT_LASTMODIFIED AS LOCKED_IN_PROJECT \
FROM \
	((${C_RESOURCES_SELECT_TABLES_BACKUP}) \
	${C_JOIN_RESOURCE_FILE_BACKUP}) \
WHERE \
	CMS_BACKUP_STRUCTURE.RESOURCE_PATH=? \
	AND CMS_BACKUP_STRUCTURE.PUBLISH_TAG=?

#
# Properties
#
C_PROPERTIES_READ=\
SELECT \
	CMS_${PROJECT}_PROPERTIES.PROPERTY_VALUE,\
	CMS_${PROJECT}_PROPERTIES.PROPERTY_MAPPING_TYPE \
FROM \
	CMS_${PROJECT}_PROPERTYDEF INNER JOIN \
	CMS_${PROJECT}_PROPERTIES \
	ON CMS_${PROJECT}_PROPERTIES.PROPERTYDEF_ID=CMS_${PROJECT}_PROPERTYDEF.PROPERTYDEF_ID \
WHERE \
	CMS_${PROJECT}_PROPERTYDEF.PROPERTYDEF_NAME=? \
    AND \
	(\
		CMS_${PROJECT}_PROPERTIES.PROPERTY_MAPPING_ID=? \
			OR \
		CMS_${PROJECT}_PROPERTIES.PROPERTY_MAPPING_ID=?\
	)
                     
####                     
C_PROPERTIES_READALL=\
SELECT \
	CMS_${PROJECT}_PROPERTYDEF.PROPERTYDEF_NAME,\
	CMS_${PROJECT}_PROPERTIES.PROPERTY_VALUE,\
	CMS_${PROJECT}_PROPERTIES.PROPERTY_MAPPING_TYPE \
FROM \
	CMS_${PROJECT}_PROPERTYDEF INNER JOIN \
	CMS_${PROJECT}_PROPERTIES \
	ON CMS_${PROJECT}_PROPERTIES.PROPERTYDEF_ID=CMS_${PROJECT}_PROPERTYDEF.PROPERTYDEF_ID \
WHERE \
	(\
		CMS_${PROJECT}_PROPERTIES.PROPERTY_MAPPING_ID=? \
			OR \
		CMS_${PROJECT}_PROPERTIES.PROPERTY_MAPPING_ID=?\
	)                   
                     
####                    
C_PROPERTIES_READALL_BACKUP=\
SELECT \
	CMS_BACKUP_PROPERTYDEF.PROPERTYDEF_NAME, \
	CMS_BACKUP_PROPERTIES.PROPERTY_VALUE,\
	CMS_BACKUP_PROPERTIES.PROPERTY_MAPPING_TYPE \
FROM \
	CMS_BACKUP_PROPERTIES INNER JOIN \
	CMS_BACKUP_PROPERTYDEF \
	ON CMS_BACKUP_PROPERTIES.PROPERTYDEF_ID=CMS_BACKUP_PROPERTYDEF.PROPERTYDEF_ID \
WHERE \
	(\
		CMS_BACKUP_PROPERTIES.PROPERTY_MAPPING_ID=? \
		OR CMS_BACKUP_PROPERTIES.PROPERTY_MAPPING_ID=?\
	) \
	AND CMS_BACKUP_PROPERTIES.PUBLISH_TAG=?
                            
	

	
#
# Warning: Expects C_STATE_DELETED = 3 and all states >= 0 / <= 3
#
C_SELECT_NONDELETED_VFS_LINKS=\
SELECT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_RESOURCES.RESOURCE_ID=? \
	AND CMS_${PROJECT}_STRUCTURE.STRUCTURE_STATE IN(0,1,2) \
ORDER BY \
	CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH ASC
	
C_SELECT_VFS_LINKS=\
SELECT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_RESOURCES.RESOURCE_ID=? \
ORDER BY \
	CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH ASC	
	
	
	
	
#
# Groups
#
####
C_GROUPS_GETGROUPSOFUSER=\
SELECT \
	* \
FROM \
	CMS_GROUPS G INNER JOIN \
	CMS_GROUPUSERS GU \
	ON G.GROUP_ID=GU.GROUP_ID \
WHERE \
	GU.USER_ID=?
	
####
C_GROUPS_GETUSERSOFGROUP=\
SELECT \
	U.USER_INFO,\
	U.USER_ID,\
	U.USER_NAME,\
	U.USER_PASSWORD,\
	U.USER_DESCRIPTION,\
	U.USER_FIRSTNAME,\
	U.USER_LASTNAME,\
	U.USER_EMAIL,\
	U.USER_LASTLOGIN,\
	U.USER_FLAGS,\
	U.USER_ADDRESS,\
	U.USER_TYPE \
FROM \
	(CMS_USERS U INNER JOIN CMS_GROUPUSERS GU ON U.USER_ID=GU.USER_ID) \
	INNER JOIN CMS_GROUPS G ON GU.GROUP_ID=G.GROUP_ID \
WHERE \
	G.GROUP_NAME=? \
    AND U.USER_TYPE IN (?, 2)  \
ORDER BY \
	USER_NAME

	
####	
C_TASK_FIND_AGENT=\
SELECT \
	CMS_USERS.USER_ID \
FROM \
	CMS_USERS INNER JOIN CMS_GROUPUSERS \
	ON CMS_USERS.USER_ID=CMS_GROUPUSERS.USER_ID \
WHERE \
	GROUP_ID=?
	
####
C_TASKLOG_READ_PPROJECTLOGS=\
SELECT \
	CMS_TASKLOG..ID,\
	COMENT,\
	TASKREF,\
	USERREF,\
	CMS_TASKLOG.STARTTIME,\
	TYPE,\
	EXTERNALUSERNAME \
FROM \
	CMS_TASKLOG INNER JOIN \
	CMS_TASK ON CMS_TASKLOG.TASKREF=CMS_TASK.ID \
WHERE \
	CMS_TASK.ROOT=?  \
ORDER BY \
	CMS_TASKLOG.STARTTIME


#
# Warning: Expects C_STATE_DELETED = 3 and all states >= 0 / <= 3
#
####
C_SELECT_NONDELETED_VFS_SIBLINGS=\
SELECT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_RESOURCES.RESOURCE_ID=? \
	AND CMS_${PROJECT}_STRUCTURE.STRUCTURE_STATE IN(0,1,2) \
ORDER BY \
	CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH ASC
	
####
C_SELECT_VFS_SIBLINGS=\
SELECT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_RESOURCES.RESOURCE_ID=? \
ORDER BY \
	CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH ASC	
			
#
#	Access Control Entries
#
####
C_ACCESS_UPDATE=\
UPDATE \
	CMS_${PROJECT}_ACCESSCONTROL \
SET \
	ACCESS_ALLOWED=?,\
	ACCESS_DENIED=?,\
	ACCESS_FLAGS=? \
WHERE \
	CMS_${PROJECT}_ACCESSCONTROL.RESOURCE_ID=? \
	AND CMS_${PROJECT}_ACCESSCONTROL.PRINCIPAL_ID =?
	
####
C_ACCESS_SETFLAGS_ALL=\
UPDATE \
	CMS_${PROJECT}_ACCESSCONTROL \
SET \
	ACCESS_FLAGS=ACCESS_FLAGS+? \
WHERE \
	CMS_${PROJECT}_ACCESSCONTROL.RESOURCE_ID=?
	
	
#
# Statements for cleaning the backup
#
#
# Deletes the backup file record
#
C_POSTGRESQL_BACKUP_DELETE_CONTENT=\
DELETE FROM \
	CMS_BACKUP_CONTENTS \
WHERE \
	BACKUP_ID=?
	
	
#
# Deletes the backup resource record
#                      
C_POSTGRESQL_BACKUP_DELETE_RESOURCES=\
DELETE FROM \
	CMS_BACKUP_RESOURCES \
WHERE \
	BACKUP_ID=?
	
	
#
# Deletes the backup structure record
# 
C_POSTGRESQL_BACKUP_DELETE_STRUCTURE=\
DELETE FROM \
	CMS_BACKUP_STRUCTURE \
WHERE \
	BACKUP_ID=? 

	
	
####
C_ACCESS_READ_ENTRY=\
SELECT \
	RESOURCE_ID,\
	PRINCIPAL_ID,\
	ACCESS_ALLOWED,\
	ACCESS_DENIED,\
	ACCESS_FLAGS \
FROM \
	CMS_${PROJECT}_ACCESSCONTROL \
WHERE \
	CMS_${PROJECT}_ACCESSCONTROL.RESOURCE_ID=? \
	AND CMS_${PROJECT}_ACCESSCONTROL.PRINCIPAL_ID =?
	
	
	
C_RESOURCES_TRANSFER_RESOURCE=\
UPDATE \
	CMS_${PROJECT}_RESOURCES \
SET \
	USER_CREATED=?,\
	USER_LASTMODIFIED=? \
WHERE \
	CMS_${PROJECT}_RESOURCES.RESOURCE_ID=?	
	
	
	
####
C_PROJECTS_CREATE=\
INSERT INTO CMS_PROJECTS \
	(PROJECT_ID,\
	USER_ID,\
	GROUP_ID,\
	MANAGERGROUP_ID,\
	TASK_ID,\
	PROJECT_NAME,\
	PROJECT_DESCRIPTION,\
	PROJECT_FLAGS,\
	DATE_CREATED,\
	PROJECT_TYPE)\
VALUES \
	(?,?,?,?,?,?,?,?,?,?)

####
C_SELECT_RESOURCES_FOR_PRINCIPAL_ACE=\
SELECT DISTINCT \
	${C_RESOURCES_SELECT_ATTRIBS} \
FROM \
	CMS_${PROJECT}_ACCESSCONTROL, \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_ACCESSCONTROL.PRINCIPAL_ID=? \
	AND CMS_${PROJECT}_ACCESSCONTROL.RESOURCE_ID=CMS_${PROJECT}_RESOURCES.RESOURCE_ID \
	AND ${C_JOIN_STRUCTURE_RESOURCE} \
ORDER BY \
	CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH ASC

####
C_SELECT_RESOURCES_FOR_PRINCIPAL_ATTR=\
(SELECT DISTINCT \
	${C_RESOURCES_SELECT_ATTRIBS} \
FROM \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_RESOURCES.USER_CREATED=? \
	AND ${C_JOIN_STRUCTURE_RESOURCE}) \
UNION \
(SELECT DISTINCT \
	${C_RESOURCES_SELECT_ATTRIBS} \
FROM \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_RESOURCES.USER_LASTMODIFIED=? \
	AND ${C_JOIN_STRUCTURE_RESOURCE})