query.properties

cms是开源的框架

#
# The pattern _${PROJECT}_ in table names is replaced by the SqlManager with 
# "_ONLINE_" or "_OFFLINE_" to choose the right database tables for
# SQL queries that are project dependent!
#
	
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_GROUPS G,\
	CMS_USERS U,\
	CMS_GROUPUSERS GU \
WHERE \
	G.GROUP_NAME=? \
	AND U.USER_ID=GU.USER_ID \
	AND GU.GROUP_ID=G.GROUP_ID \
	AND U.USER_TYPE IN (?, 2) \
ORDER BY \
	USER_NAME


C_USERS_WRITE=\
UPDATE \
	CMS_USERS \
SET \
	USER_DESCRIPTION=?,\
	USER_FIRSTNAME=?,\
	USER_LASTNAME=?,\
	USER_EMAIL=?,\
	USER_LASTLOGIN=?,\
	USER_FLAGS=?,\
	USER_INFO=?,\
	USER_ADDRESS=?,\
	USER_TYPE=? \
WHERE \
	USER_ID=?


C_LOG_COMMENT=CMS_TASKLOG.coment

C_TASK_READ=\
SELECT \
	* \
FROM \
	CMS_TASK \
WHERE \
	ID=?

# maxdb/sapdb 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 \
WHERE \
    ROWNO <= ? \
ORDER BY \
	PUBLISH_TAG DESC

# maxdb/sapdb order for union must be number of collumm
C_RESOURCES_GET_RESOURCE_WITH_PROPERTYDEF=\
(SELECT DISTINCT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	CMS_${PROJECT}_PROPERTIES, \
	CMS_${PROJECT}_RESOURCES, \
	CMS_${PROJECT}_STRUCTURE \
WHERE \
	CMS_${PROJECT}_PROPERTIES.PROPERTYDEF_ID = ? \
	AND CMS_${PROJECT}_PROPERTIES.PROPERTY_MAPPING_ID = CMS_${PROJECT}_RESOURCES.RESOURCE_ID \
	AND CMS_${PROJECT}_STRUCTURE.RESOURCE_ID = CMS_${PROJECT}_RESOURCES.RESOURCE_ID \
	AND CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH LIKE ?) \
UNION \
(SELECT DISTINCT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	CMS_${PROJECT}_PROPERTIES, \
	CMS_${PROJECT}_STRUCTURE, \
	CMS_${PROJECT}_RESOURCES \
WHERE \
	CMS_${PROJECT}_PROPERTIES.PROPERTYDEF_ID = ? \
	AND CMS_${PROJECT}_PROPERTIES.PROPERTY_MAPPING_ID = CMS_${PROJECT}_STRUCTURE.STRUCTURE_ID \
	AND CMS_${PROJECT}_STRUCTURE.RESOURCE_ID = CMS_${PROJECT}_RESOURCES.RESOURCE_ID \
	AND CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH LIKE ?) \
ORDER BY 3 DESC

C_RESOURCES_GET_RESOURCE_WITH_PROPERTYDEF_VALUE=\
(SELECT DISTINCT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	CMS_${PROJECT}_PROPERTIES, \
	CMS_${PROJECT}_RESOURCES, \
	CMS_${PROJECT}_STRUCTURE \
WHERE \
	CMS_${PROJECT}_PROPERTIES.PROPERTYDEF_ID=? \
	AND CMS_${PROJECT}_PROPERTIES.PROPERTY_MAPPING_ID = CMS_${PROJECT}_RESOURCES.RESOURCE_ID \
	AND CMS_${PROJECT}_STRUCTURE.RESOURCE_ID = CMS_${PROJECT}_RESOURCES.RESOURCE_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, \
	CMS_${PROJECT}_STRUCTURE, \
	CMS_${PROJECT}_RESOURCES \
WHERE \
	CMS_${PROJECT}_PROPERTIES.PROPERTYDEF_ID = ? \
	AND CMS_${PROJECT}_PROPERTIES.PROPERTY_MAPPING_ID = CMS_${PROJECT}_STRUCTURE.STRUCTURE_ID \
	AND CMS_${PROJECT}_STRUCTURE.RESOURCE_ID = CMS_${PROJECT}_RESOURCES.RESOURCE_ID \
	AND CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH LIKE ? \
	AND CMS_${PROJECT}_PROPERTIES.PROPERTY_VALUE LIKE ?) \
ORDER BY 3 DESC

#If a query specification was specified with DISTINCT, the total of the internal 
#lengths of all sorting columns must not exceed 1016 characters;
#otherwise it can comprise 1020 characters.
C_SELECT_PUBLISHED_RESOURCES=\
SELECT \
	STRUCTURE_ID,\
	RESOURCE_ID,\
	RESOURCE_PATH,\
	RESOURCE_STATE,\
	RESOURCE_TYPE,\
	SIBLING_COUNT,\
	PUBLISH_TAG \
FROM \
	CMS_PUBLISH_HISTORY \
WHERE \
	HISTORY_ID=? \
ORDER BY \
	RESOURCE_TYPE ASC,\
	RESOURCE_STATE ASC,\
	SUBSTR(RESOURCE_PATH,1,768) ASC