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_COMMIT=commit
C_ROLLBACK=rollback
C_TRIMBLOB={call dbms_lob.trim(?,?)}

#
# statements for CMS_CONTENTS
#

#
# Create a new empty file content record
#
C_ORACLE_CONTENTS_ADD=\
INSERT INTO CMS_${PROJECT}_CONTENTS \
	(CONTENT_ID,\
	RESOURCE_ID,\
	FILE_CONTENT) \
VALUES \
	(?,?,empty_blob())


#
# Selects the blob FILE_CONTENT for update.
# If record is already locked, statement will wait for 10 seconds
#
C_ORACLE_CONTENTS_UPDATECONTENT=\
SELECT \
	FILE_CONTENT \
FROM \
	CMS_${PROJECT}_CONTENTS \
WHERE \
	RESOURCE_ID=? \
FOR UPDATE WAIT 10

#
# Create a new empty backup content record
#
C_ORACLE_CONTENTS_ADDBACKUP=\
INSERT INTO CMS_BACKUP_CONTENTS \
	(CONTENT_ID,\
	RESOURCE_ID,\
	FILE_CONTENT,\
	PUBLISH_TAG,\
	VERSION_ID,\
	BACKUP_ID) \
VALUES \
	(?,?,empty_blob(),?,?,?)

#
# Selects the blob FILE_CONTENT for update in a backup record.
# If record is already locked, statement will wait for 10 seconds
#
C_ORACLE_CONTENTS_UPDATEBACKUP=\
SELECT \
	FILE_CONTENT \
FROM \
	CMS_BACKUP_CONTENTS \
WHERE \
	CONTENT_ID=? \
	AND BACKUP_ID=? \
FOR UPDATE WAIT 10

#
# Backup the content 
#
C_ORACLE_CONTENTS_BACKUPCONTENT=\
UPDATE \
	CMS_BACKUP_CONTENTS \
SET \
	FILE_CONTENT=? \
WHERE \
	CONTENT_ID=? \
	AND BACKUP_ID=?
	

#
# Read the content
#
C_ORACLE_CONTENTS_READCONTENT=\
SELECT \
	FILE_CONTENT \
FROM \
	CMS_OFFLINE_CONTENTS \
WHERE \
	RESOURCE_ID=?
	

#
# Statements for CMS_USERS
#

#
# Write data for already existing user.
# USER_INFO is not written, must be updated with C_ORACLE_USERS_UPDATEINFO
#
C_ORACLE_USERS_WRITE=\
UPDATE \
	CMS_USERS \
SET \
	USER_DESCRIPTION=?,\
	USER_FIRSTNAME=?,\
	USER_LASTNAME=?,\
	USER_EMAIL=?,\
	USER_LASTLOGIN=?,\
	USER_FLAGS=?,\
	USER_ADDRESS=?,\
	USER_TYPE=? \
WHERE \
	USER_ID=?

#
# Add data for new user.
# USER_INFO is not written, instead a new blob is inserted for later update
#
C_ORACLE_USERS_ADD=\
INSERT INTO CMS_USERS \
	(USER_ID,\
	USER_NAME,\
	USER_PASSWORD,\
	USER_DESCRIPTION,\
	USER_FIRSTNAME,\
	USER_LASTNAME,\
    USER_EMAIL,\
    USER_LASTLOGIN,\
    USER_FLAGS,\
    USER_INFO,\
    USER_ADDRESS,\
    USER_TYPE) \
VALUES \
	(?,?,?,?,?,?,?,?,?,empty_blob(),?,?)
	

#
# Selects the blob USER_INFO for update.
# If record is already locked, statement will wait for 10 seconds
#
C_ORACLE_USERS_UPDATEINFO=\
SELECT \
	USER_INFO \
FROM \
	CMS_USERS \
WHERE \
	USER_ID=? \
FOR UPDATE WAIT 10

#
# Deletes the backup file record
#
C_ORACLE_BACKUP_DELETE_CONTENT=\
DELETE FROM \
	CMS_BACKUP_CONTENTS \
WHERE \
	BACKUP_ID=?
	
#
# Deletes the backup resource record
#                      
C_ORACLE_BACKUP_DELETE_RESOURCES=\
DELETE FROM \
	CMS_BACKUP_RESOURCES \
WHERE \
	BACKUP_ID=?
	
	
#
# Deletes the backup structure record
# 
C_ORACLE_BACKUP_DELETE_STRUCTURE=\
DELETE FROM \
	CMS_BACKUP_STRUCTURE \
WHERE \
	BACKUP_ID=? 
	
                           
# statements for backup projects
C_ORACLE_PROJECTS_READLAST_BACKUP=\
SELECT \
	* \
FROM \
	(SELECT \
		PUBLISH_TAG,\
		PROJECT_ID,\
		PROJECT_NAME,\
		PROJECT_PUBLISHDATE,\
		PROJECT_PUBLISHED_BY,\
		PROJECT_PUBLISHED_BY_NAME,\
		PROJECT_DESCRIPTION,\
		TASK_ID,\
		USER_ID,\
		USER_NAME,\
		GROUP_ID,\
		GROUP_NAME,\
		MANAGERGROUP_ID,\
		MANAGERGROUP_NAME,\
		DATE_CREATED,\
		PROJECT_TYPE \
	FROM \
		CMS_BACKUP_PROJECTS \
	ORDER BY \
		PUBLISH_TAG DESC) \
WHERE \
	ROWNUM<=?

	
# statements optimized with hints for oracle
C_RESOURCES_READ=\
SELECT \
	${C_RESOURCES_SELECT_ATTRIBS},\
	CMS_${PROJECT}_RESOURCES.PROJECT_LASTMODIFIED \
FROM \
	${C_RESOURCES_SELECT_TABLES} \
WHERE \
	CMS_${PROJECT}_STRUCTURE.RESOURCE_PATH=? \
	AND ${C_JOIN_STRUCTURE_RESOURCE} \
ORDER BY \
	CMS_${PROJECT}_STRUCTURE.STRUCTURE_STATE ASC