query.properties

一个cms内容管理平台

VALUES \
	(?,?,?,?,?,?,?,?,?,?,?,?)
	
	
C_USERS_READ=\
SELECT \
	USER_INFO,\
	USER_ID,\
	USER_NAME,\
	USER_PASSWORD,\
	USER_DESCRIPTION,\
	USER_FIRSTNAME,\
	USER_LASTNAME,\
	USER_EMAIL,\
	USER_LASTLOGIN,\
	USER_FLAGS, \
	USER_ADDRESS,\
	USER_TYPE \
FROM \
	CMS_USERS \
WHERE \
	USER_NAME=? \
    AND USER_TYPE IN (?, 2)
	
	
C_USERS_READID=\
SELECT \
	USER_INFO,\
	USER_ID,\
	USER_NAME,\
	USER_PASSWORD,\
	USER_DESCRIPTION,\
	USER_FIRSTNAME,\
	USER_LASTNAME,\
	USER_EMAIL,\
	USER_LASTLOGIN,\
	USER_FLAGS,\
	USER_ADDRESS,\
	USER_TYPE \
FROM \
	CMS_USERS \
WHERE \
	USER_ID=?
	
	
C_USERS_READPW=\
SELECT \
	USER_INFO,\
	USER_ID,\
	USER_NAME,\
	USER_PASSWORD,\
	USER_DESCRIPTION,\
	USER_FIRSTNAME,\
	USER_LASTNAME,\
	USER_EMAIL,\
	USER_LASTLOGIN,\
	USER_FLAGS,\
	USER_ADDRESS,\
	USER_TYPE \
FROM \
	CMS_USERS \
WHERE \
	USER_NAME=? \
               AND USER_PASSWORD=?  \
               AND USER_TYPE IN (?, 2)
	
	
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_USERS_DELETE=\
DELETE FROM \
	CMS_USERS \
WHERE \
	USER_NAME=?
	
	
C_USERS_GETUSERS=\
SELECT \
	USER_INFO,\
	USER_ID,\
	USER_NAME,\
	USER_PASSWORD,\
	USER_DESCRIPTION,\
	USER_FIRSTNAME,\
	USER_LASTNAME,\
	USER_EMAIL,\
	USER_LASTLOGIN,\
	USER_FLAGS,\
	USER_ADDRESS,\
	USER_TYPE \
FROM \
	CMS_USERS \
WHERE \
	USER_TYPE=? \
ORDER BY \
	USER_NAME
	
	
C_USERS_SETPW=\
UPDATE \
	CMS_USERS \
SET \
	USER_PASSWORD=?  \
WHERE \
	USER_NAME=?
	
C_USERS_UPDATE_USERTYPE=\
UPDATE \
	CMS_USERS \
SET \
	USER_TYPE=? \
WHERE \
	USER_ID=?


#
# Tasks, Tasklog, Taskpar
#
C_TABLENAME_TASK=CMS_TASK
C_TABLENAME_TASKLOG=CMS_TASKLOG
C_TABLENAME_TASKTYPE=CMS_TASKTYPE
C_TABLENAME_TASKPAR=CMS_TASKPAR
C_ID=ID
C_TASK_ID=ID
C_TASK_NAME=NAME
C_TASK_STATE=STATE
C_TASK_ROOT=ROOT
C_TASK_PARENT=PARENT
C_TASK_TASKTYPE=TASKTYPEREF
C_TASK_INITIATORUSER=INITIATORUSERREF
C_TASK_ROLE=ROLEREF
C_TASK_AGENTUSER=AGENTUSERREF
C_TASK_ORIGINALUSER=ORIGINALUSERREF
C_TASK_STARTTIME=STARTTIME
C_TASK_WAKEUPTIME=WAKEUPTIME
C_TASK_TIMEOUT=TIMEOUT
C_TASK_ENDTIME=ENDTIME
C_TASK_PERCENTAGE=PERCENTAGE
C_TASK_PERMISSION=PERMISSION
C_TASK_PRIORITY=PRIORITYREF
C_TASK_ESCALATIONTYPE=ESCALATIONTYPEREF
C_TASK_HTMLLINK=HTMLLINK
C_TASK_MILESTONE=MILESTONEREF
C_TASK_AUTOFINISH=AUTOFINISH
C_LOG_ID=CMS_TASKLOG.ID
C_LOG_COMMENT=CMS_TASKLOG.COMENT
C_LOG_EXUSERNAME=CMS_TASKLOG.EXTERNALUSERNAME
C_LOG_STARTTIME=CMS_TASKLOG.STARTTIME
C_LOG_TASK=CMS_TASKLOG.TASKREF
C_LOG_USER=CMS_TASKLOG.USERREF
C_LOG_TYPE=CMS_TASKLOG.TYPE
C_TASKLOG_ID=ID
C_TASKLOG_COMMENT=COMENT
C_TASKLOG_EXUSERNAME=EXTERNALUSERNAME
C_TASKLOG_STARTTIME=STARTTIME
C_TASKLOG_TASK=TASKREF
C_TASKLOG_USER=USERREF
C_TASKLOG_TYPE=TYPE
C_PAR_ID=ID
C_PAR_NAME=PARNAME
C_PAR_VALUE=PARVALUE
C_PAR_TASK=REF
C_TASK_TYPE_FIELDS=AUTOFINISH, ESCALATIONTYPEREF, HTMLLINK, NAME, PERMISSION, PRIORITYREF, ROLEREF

C_TASK_FIELDS=AUTOFINISH,ENDTIME,ESCALATIONTYPEREF,ID,INITIATORUSERREF,MILESTONEREF,\
NAME,ORIGINALUSERREF,AGENTUSERREF,PARENT,PERCENTAGE,PERMISSION,PRIORITYREF,ROLEREF,ROOT,\
STARTTIME,STATE,TASKTYPEREF,TIMEOUT,WAKEUPTIME,HTMLLINK


C_TASK_CREATE=\
INSERT INTO CMS_TASK \
	(ID) \
VALUES \
	(?)
	
	
C_TASK_UPDATE=\
UPDATE \
	CMS_TASK \
SET \
	NAME=?,\
	STATE=?,\
	TASKTYPEREF=?,\
	ROOT=?,\
	PARENT=?,\
	INITIATORUSERREF=?,\
	ROLEREF=?,\
	AGENTUSERREF=?,\
	ORIGINALUSERREF=?,\
	STARTTIME=?,\
	WAKEUPTIME=?,\
	TIMEOUT=?,\
	ENDTIME=?,\
	PERCENTAGE=?,\
	PERMISSION=?,\
	PRIORITYREF=?,\
	ESCALATIONTYPEREF=?,\
	HTMLLINK=?,\
	MILESTONEREF=?,\
	AUTOFINISH=? \
WHERE \
	ID=?
	
	
C_TASK_READ=\
SELECT \
	AUTOFINISH,\
	ENDTIME,\
	ESCALATIONTYPEREF,\
	ID,\
	INITIATORUSERREF,\
	MILESTONEREF,\
	NAME,\
	ORIGINALUSERREF,\
	AGENTUSERREF,\
	PARENT,\
	PERCENTAGE,\
	PERMISSION,\
	PRIORITYREF,\
	ROLEREF,\
	ROOT,\
	STARTTIME,\
	STATE,\
	TASKTYPEREF,\
	TIMEOUT,\
	WAKEUPTIME,\
	HTMLLINK \
FROM \
	CMS_TASK \
WHERE \
	ID=?
	
	
C_TASK_END=\
UPDATE \
	CMS_TASK \
SET \
	STATE=4,\
	PERCENTAGE=?,\
	ENDTIME=? \
WHERE \
	ID=?
	
	
C_TASKLOG_WRITE=\
INSERT INTO CMS_TASKLOG \
	(ID,\
	TASKREF,\
	USERREF,\
	STARTTIME,\
	COMENT,\
	TYPE) \
VALUES \
	(?,?,?,?,?,?)
	
	
C_TASKLOG_READ=\
SELECT \
	* \
FROM \
	CMS_TASKLOG \
WHERE \
	ID=?
	
	
C_TASK_FIND_AGENT=\
SELECT \
	CMS_USERS.USER_ID \
FROM \
	CMS_GROUPUSERS,\
	CMS_USERS \
WHERE \
	GROUP_ID=? \
    AND CMS_GROUPUSERS.USER_ID=CMS_USERS.USER_ID \

C_TASK_FORWARD=\
UPDATE \
	CMS_TASK \
SET \
	ROLEREF=?,\
	AGENTUSERREF=?  \
WHERE \
	ID=?
	
	
C_TASKLOG_READ_LOGS=\
SELECT \
	* \
FROM \
	CMS_TASKLOG \
WHERE \
	TASKREF=? \
ORDER BY \
	STARTTIME
	
	
C_TASKLOG_READ_PPROJECTLOGS=\
SELECT \
	ID,\
	COMENT,\
	TASKREF,\
	USERREF,\
	STARTTIME,\
	TYPE,\
	EXTERNALUSERNAME \
FROM \
	CMS_TASKLOG,\
	CMS_TASK \
WHERE \
	TASKREF=CMS_TASK.ID \
                            AND CMS_TASK.ROOT=?  \
ORDER BY \
	STARTTIME


C_TASKPAR_TEST=\
SELECT \
	* \
FROM \
	CMS_TASKPAR \
WHERE \
	REF=? \
	AND PARNAME=?
	
	
C_TASKPAR_UPDATE=\
UPDATE \
	CMS_TASKPAR \
SET \
	PARVALUE=? \
WHERE \
	ID=?
	
	
C_TASKPAR_INSERT=\
INSERT INTO CMS_TASKPAR \
	(ID,\
	REF,\
	PARNAME,\
	PARVALUE) \
VALUES \
	(?,?,?,?)
	
	
C_TASKPAR_GET=\
SELECT \
	* \
FROM \
	CMS_TASKPAR \
WHERE \
	REF=? \
	AND PARNAME=?
	
	
C_TASK_GET_TASKTYPE=\
SELECT \
	ID \
FROM \
	CMS_TASKTYPE \
WHERE \
	NAME=?
	
	
C_TASKTYPE_UPDATE=\
UPDATE \
	CMS_TASKTYPE \
SET \
	AUTOFINISH=?,\
	ESCALATIONTYPEREF=?,\
	HTMLLINK=?,\
	NAME=?,\
	PERMISSION=?,\
	PRIORITYREF=?,\
	ROLEREF=? \
WHERE \
	ID=?
	
	
C_TASKTYPE_INSERT=\
INSERT INTO CMS_TASKTYPE \
	(AUTOFINISH,\
	ESCALATIONTYPEREF,\
	HTMLLINK,\
	ID,\
	NAME,\
	PERMISSION,\
	PRIORITYREF,\
	ROLEREF) \
VALUES \
	(?,?,?,?,?,?,?,?)
	

#
# Statements for cleaning the backup
#
C_BACKUP_READ_MAXVERSION=\
SELECT \
	MAX(PUBLISH_TAG) \
FROM \
	CMS_BACKUP_PROJECTS \
WHERE \
	PROJECT_PUBLISHDATE<?


C_BACKUP_DELETE_STRUCTURE_BYBACKUPID=\
DELETE FROM \
	CMS_BACKUP_STRUCTURE \
WHERE \
	BACKUP_ID=?


C_BACKUP_DELETE_RESOURCES_BYBACKUPID=\
DELETE FROM \
	CMS_BACKUP_RESOURCES \
WHERE \
	BACKUP_ID=?


C_BACKUP_DELETE_CONTENTS_BYBACKUPID=\
DELETE FROM \
	CMS_BACKUP_CONTENTS \
WHERE \
	BACKUP_ID=?

	
C_BACKUP_DELETE_PROPERTIES_BYBACKUPID=\
DELETE FROM \
	CMS_BACKUP_PROPERTIES \
WHERE \
	BACKUP_ID=?


C_BACKUP_READ_BACKUPID_FOR_DELETION=\
SELECT \
	BACKUP_ID \
FROM \
	CMS_BACKUP_STRUCTURE \
WHERE \
	STRUCTURE_ID=? \
	AND RESOURCE_ID=? \
	AND VERSION_ID<=? \
	AND PUBLISH_TAG<? 


C_BACKUP_DELETE_RESOURCE=\
DELETE \
	CMS_BACKUP_STRUCTURE.*,\
	CMS_BACKUP_RESOURCES.*,\
	CMS_BACKUP_CONTENTS.* \
FROM \
	CMS_BACKUP_STRUCTURE,\
	CMS_BACKUP_RESOURCES,\
	CMS_BACKUP_CONTENTS \
WHERE \
	CMS_BACKUP_STRUCTURE.STRUCTURE_ID=? \
	AND CMS_BACKUP_STRUCTURE.PUBLISH_TAG=? \
	AND ${C_JOIN_STRUCTURE_RESOURCE_BACKUP} \
	AND ${C_JOIN_RESOURCE_FILE_BACKUP}
	

C_BACKUP_EXISTS_RESOURCE=\
SELECT \
	CMS_BACKUP_RESOURCES.BACKUP_ID \
FROM \
	CMS_BACKUP_RESOURCES \
WHERE \
	CMS_BACKUP_RESOURCES.RESOURCE_ID=? \
	AND CMS_BACKUP_RESOURCES.PUBLISH_TAG=?


#
# 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) \
	AND ${C_JOIN_STRUCTURE_RESOURCE} \
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=? \
	AND ${C_JOIN_STRUCTURE_RESOURCE} \
ORDER BY \
	CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH ASC	
			
#
#	Access Control Entries
#
C_ACCESS_RESOURCE_ID=RESOURCE_ID
C_ACCESS_PRINCIPAL_ID=PRINCIPAL_ID
C_ACCESS_ACCESS_ALLOWED=ACCESS_ALLOWED
C_ACCESS_ACCESS_DENIED=ACCESS_DENIED
C_ACCESS_ACCESS_FLAGS=ACCESS_FLAGS

C_ACCESS_CREATE=\
INSERT INTO CMS_${PROJECT}_ACCESSCONTROL \
	(RESOURCE_ID,\
	PRINCIPAL_ID,\
	ACCESS_ALLOWED,\
	ACCESS_DENIED,\
	ACCESS_FLAGS) \
VALUES \
	(?,?,?,?,?)
	
	
C_ACCESS_UPDATE=\
UPDATE \
	CMS_${PROJECT}_ACCESSCONTROL \
SET \
	ACCESS_ALLOWED=?,\
	ACCESS_DENIED=?,\
	ACCESS_FLAGS=? \
WHERE \
	RESOURCE_ID=? \
	AND PRINCIPAL_ID = ?
	
	
C_ACCESS_SETFLAGS_ALL=\
UPDATE \
	CMS_${PROJECT}_ACCESSCONTROL \
SET \
	ACCESS_FLAGS=ACCESS_FLAGS+? \
WHERE \
	RESOURCE_ID=?
	
	
C_ACCESS_REMOVE=\
DELETE FROM \
	CMS_${PROJECT}_ACCESSCONTROL \
WHERE \
	RESOURCE_ID=? \
	AND PRINCIPAL_ID = ?
	
	
C_ACCESS_REMOVE_ALL=\
DELETE FROM \
	CMS_${PROJECT}_ACCESSCONTROL \
WHERE \
	RESOURCE_ID=?
	
C_ACCESS_REMOVE_ALL_FOR_PRINCIPAL=\
DELETE FROM \
	CMS_OFFLINE_ACCESSCONTROL \
WHERE \
	PRINCIPAL_ID=?

C_ACCESS_REMOVE_ALL_FOR_PRINCIPAL_ONLINE=\
DELETE FROM \
	CMS_ONLINE_ACCESSCONTROL \
WHERE \
	PRINCIPAL_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_ACCESS_READ_ENTRIES=\
SELECT \
	RESOURCE_ID,\
	PRINCIPAL_ID,\
	ACCESS_ALLOWED,\
	ACCESS_DENIED,\
	ACCESS_FLAGS \
FROM \
	CMS_${PROJECT}_ACCESSCONTROL \
WHERE \
	CMS_${PROJECT}_ACCESSCONTROL.RESOURCE_ID=?

	
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,\
	RESOURCE_PATH ASC

C_DELETE_PUBLISH_HISTORY=\
DELETE FROM \
	CMS_PUBLISH_HISTORY \
WHERE \
	PUBLISH_TAG>=0 \
	AND PUBLISH_TAG<?
	
	
#	
# Static export
#
C_STATICEXPORT_WRITE_PUBLISHED_LINKS=\
INSERT INTO CMS_STATICEXPORT_LINKS \
	(LINK_ID,\
	LINK_RFS_PATH,\
	LINK_TYPE,\
	LINK_PARAMETER,\
	LINK_TIMESTAMP) \
VALUES \
	(?,?,?,?,?)

   
C_STATICEXPORT_READ_PUBLISHED_RESOURCES=\
SELECT \
	COUNT(*) \
FROM \
	CMS_STATICEXPORT_LINKS \
WHERE \
	LINK_RFS_PATH=?    
	

C_STATICEXPORT_DELETE_PUBLISHED_LINKS=\
DELETE FROM \
	CMS_STATICEXPORT_LINKS \
WHERE \
	LINK_RFS_PATH=? \
	AND LINK_TYPE=? \
	AND LINK_PARAMETER=?	

 
C_STATICEXPORT_DELETE_ALL_PUBLISHED_LINKS=\
DELETE FROM \
	CMS_STATICEXPORT_LINKS \
WHERE \
	LINK_TYPE=? 	
  
C_STATICEXPORT_READ_ALL_PUBLISHED_LINKS=\
SELECT \
	LINK_RFS_PATH \
FROM \
	CMS_STATICEXPORT_LINKS \
WHERE \
	LINK_TYPE=? \
	AND LINK_TIMESTAMP>=? 
 
 
C_STATICEXPORT_READ_PUBLISHED_LINK_PARAMETERS=\
SELECT \
	LINK_PARAMETER \
FROM \
	CMS_STATICEXPORT_LINKS \
WHERE \
	LINK_RFS_PATH=?

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}) 
	
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_RESOURCES_DELETE_PUBLISH_HISTORY_ENTRY=\
DELETE FROM \
    CMS_PUBLISH_HISTORY \
WHERE \
    HISTORY_ID = ? \
    AND PUBLISH_TAG = ? \
    AND STRUCTURE_ID = ? \
    AND RESOURCE_ID = ?