query.properties

cms是开源的框架

# check if module master table is available
# note: we dont want to have results, its just to check the table
check_module_master : \
	select SUB_ID from CMS_MODULE_MASTER \
	where SUB_ID < 0 AND SUB_ID > 0

# insert statement to insert a new entry
insert_offline : \
    insert into CMS_MODULE_MASTER \
    ( ${colum_names_master} ) \
    values ( ${colum_values_master} )

# insert statement to insert a new entry online
insert_online : \
    insert into CMS_MODULE_ONLINE_MASTER \
    ( ${colum_names_master} ) \
    values ( ${colum_values_master} )

# insert statement to insert a new entry backup
insert_backup : \
    insert into CMS_MODULE_BACKUP_MASTER \
    ( ${colum_names_master}, TAG_ID, USER_NAME, GROUP_NAME, LASTMODIFIED_BY_NAME ) \
    values ( ${colum_values_master}, ?, ?, ?, ? )

# update statement to write an existing entry
update_offline : \
    update CMS_MODULE_MASTER \
    set ${set_row_master} \
    where MASTER_ID = ? and SUB_ID = ?

# update statement to update the lockstate
update_lockstate_offline : \
    update CMS_MODULE_MASTER \
    set locked_by = ?, project_id = ? \
    where MASTER_ID = ? and SUB_ID = ?

# update statement to update the state and lockstate
update_state_offline : \
    update CMS_MODULE_MASTER \
    set state = ?, locked_by = ? \
    where MASTER_ID = ? and SUB_ID = ?

# update statement to update the owner
update_permissions_offline : \
    update CMS_MODULE_MASTER \
    set USER_ID = ?, GROUP_ID = ?, ACCESS_FLAGS = ?, STATE = ?, \
    LASTMODIFIED_BY = ?, DATE_LASTMODIFIED = ? \
    where MASTER_ID = ? and SUB_ID = ?

# read one line from offline
read_offline : \
    select ${colum_names_master} from CMS_MODULE_MASTER \
    where MASTER_ID = ? and SUB_ID = ?

# read one line from online
read_online : \
    select ${colum_names_master} from CMS_MODULE_ONLINE_MASTER \
    where MASTER_ID = ? and SUB_ID = ?

# reads the lockstate from offline
read_lockstate_offline : \
    select PROJECT_ID, LOCKED_BY \
    from CMS_MODULE_MASTER \
    where MASTER_ID = ? and SUB_ID = ?

# read one line from backup
read_backup : \
    select ${colum_names_master}, TAG_ID, USER_NAME, GROUP_NAME, LASTMODIFIED_BY_NAME \
    from CMS_MODULE_BACKUP_MASTER \
    where MASTER_ID = ? and SUB_ID = ? and TAG_ID = ?

# read all line from backup
read_all_backup : \
    select ${colum_names_master}, TAG_ID, USER_NAME, GROUP_NAME, LASTMODIFIED_BY_NAME \
    from CMS_MODULE_BACKUP_MASTER \
    where MASTER_ID = ? and SUB_ID = ?
    order by TAG_ID

# delete one line from offline
delete_offline : \
    delete from CMS_MODULE_MASTER \
    where MASTER_ID = ? and SUB_ID = ?

# delete one line from online
delete_online : \
    delete from CMS_MODULE_ONLINE_MASTER \
    where MASTER_ID = ? and SUB_ID = ?

# read all entrys for publish
read_all_for_publish : \
    select ${colum_names_master} from CMS_MODULE_MASTER \
    where SUB_ID = ? and PROJECT_ID = ? and STATE <> ?

# read entries in the given channel from offline
readallbychannel_offline : \
    select CMS_MODULE_MASTER.${colum_names_master} \
    from CMS_MODULE_MASTER, CMS_MODULE_CHANNEL_REL \
    where CMS_MODULE_MASTER.MASTER_ID=CMS_MODULE_CHANNEL_REL.MASTER_ID \
    and SUB_ID = ? and CHANNEL_ID=?

# read entries in the given channel from online
readallbychannel_online : \
    select CMS_MODULE_ONLINE_MASTER.${colum_names_master} \
    from CMS_MODULE_ONLINE_MASTER, CMS_MODULE_ONLINE_CHANNEL_REL \
    where CMS_MODULE_ONLINE_MASTER.MASTER_ID=CMS_MODULE_ONLINE_CHANNEL_REL.MASTER_ID \
    and SUB_ID = ? and CHANNEL_ID=?

# insert media statement to insert a new entry
insert_media_offline : \
    insert into CMS_MODULE_MEDIA \
    ( ${colum_names_media} ) \
    values ( ${colum_values_media} )

# insert media statement to insert a new entry
insert_media_online : \
    insert into CMS_MODULE_ONLINE_MEDIA \
    ( ${colum_names_media} ) \
    values ( ${colum_values_media} )

# insert media statement to insert a new entry
insert_media_backup : \
    insert into CMS_MODULE_BACKUP_MEDIA \
    ( ${colum_names_media}, TAG_ID ) \
    values ( ${colum_values_media}, ? )

# update media statement to write an existing entry
update_media_offline : \
    update CMS_MODULE_MEDIA \
    set ${set_row_media} \
    where MEDIA_ID = ? and MASTER_ID = ?

# delete one media line from offline
delete_media_offline : \
    delete from CMS_MODULE_MEDIA \
    where MEDIA_ID = ? and MASTER_ID = ?

# delete all media lines from offline
delete_all_media_offline : \
    delete from CMS_MODULE_MEDIA \
    where MASTER_ID = ?

# delete all media lines from online
delete_all_media_online : \
    delete from CMS_MODULE_ONLINE_MEDIA \
    where MASTER_ID = ?

# read one media line from online
read_media_online : \
    select ${colum_names_media} from CMS_MODULE_ONLINE_MEDIA \
    where MASTER_ID = ? \
    order by MEDIA_TYPE, MEDIA_POSITION, MEDIA_TITLE

# read one media line from offline
read_media_offline : \
    select ${colum_names_media} from CMS_MODULE_MEDIA \
    where MASTER_ID = ? \
    order by MEDIA_TYPE, MEDIA_POSITION, MEDIA_TITLE

# read one media line from backup
read_media_backup : \
    select ${colum_names_media} from CMS_MODULE_BACKUP_MEDIA \
    where MASTER_ID = ? \
    and TAG_ID = ? \
    order by MEDIA_TYPE, MEDIA_POSITION, MEDIA_TITLE

# insert channel statement to insert a new entry
insert_channel_offline : \
    insert into CMS_MODULE_CHANNEL_REL \
    ( MASTER_ID, CHANNEL_ID, CHANNEL_NAME ) \
    values (?, ?, ?)

# insert channel statement to insert a new entry
insert_channel_online : \
    insert into CMS_MODULE_ONLINE_CHANNEL_REL \
    ( MASTER_ID, CHANNEL_ID, CHANNEL_NAME  ) \
    values (?, ?, ?)

# delete one channel line from offline
delete_channel_offline : \
    delete from CMS_MODULE_CHANNEL_REL \
    where MASTER_ID = ? and CHANNEL_NAME = ?

# delete all channel lines from offline
delete_all_channel_offline : \
    delete from CMS_MODULE_CHANNEL_REL \
    where MASTER_ID = ?

# delete all channel lines from online
delete_all_channel_online : \
    delete from CMS_MODULE_ONLINE_CHANNEL_REL \
    where MASTER_ID = ?

# read channels from online
read_channel_online : \
    select CHANNEL_ID, CHANNEL_NAME from CMS_MODULE_ONLINE_CHANNEL_REL \
    where MASTER_ID = ?

# read channels from offline
read_channel_offline : \
    select CHANNEL_ID, CHANNEL_NAME from CMS_MODULE_CHANNEL_REL \
    where MASTER_ID = ?

# read channel names from online
read_channel_names_online : \
    select CHANNEL_NAME from CMS_MODULE_ONLINE_CHANNEL_REL \
    where MASTER_ID = ?

# read channel names from offline
read_channel_names_offline : \
    select CHANNEL_NAME from CMS_MODULE_CHANNEL_REL \
    where MASTER_ID = ?


# all colum names of the mastermodule
colum_names_master : \
    MASTER_ID,\
    SUB_ID,\
    USER_ID,\
    GROUP_ID,\
    PROJECT_ID,\
    ACCESS_FLAGS,\
    STATE,\
    LOCKED_BY,\
    LASTMODIFIED_BY,\
    DATE_CREATED,\
    DATE_LASTMODIFIED,\
    PUBLICATION_DATE,\
    PURGE_DATE,\
    FLAGS,\
    FEED_ID,\
    FEED_REFERENCE,\
    FEED_FILENAME,\
    TITLE,\
    DATA_BIG_0,\
    DATA_BIG_1,\
    DATA_BIG_2,\
    DATA_BIG_3,\
    DATA_BIG_4,\
    DATA_BIG_5,\
    DATA_BIG_6,\
    DATA_BIG_7,\
    DATA_BIG_8,\
    DATA_BIG_9,\
    DATA_MEDIUM_0,\
    DATA_MEDIUM_1,\
    DATA_MEDIUM_2,\
    DATA_MEDIUM_3,\
    DATA_MEDIUM_4,\
    DATA_MEDIUM_5,\
    DATA_MEDIUM_6,\
    DATA_MEDIUM_7,\
    DATA_MEDIUM_8,\
    DATA_MEDIUM_9,\
    DATA_SMALL_0 ,\
    DATA_SMALL_1 ,\
    DATA_SMALL_2 ,\
    DATA_SMALL_3 ,\
    DATA_SMALL_4 ,\
    DATA_SMALL_5 ,\
    DATA_SMALL_6 ,\
    DATA_SMALL_7 ,\
    DATA_SMALL_8 ,\
    DATA_SMALL_9 ,\
    DATA_SMALL_10,\
    DATA_SMALL_11,\
    DATA_SMALL_12,\
    DATA_SMALL_13,\
    DATA_SMALL_14,\
    DATA_SMALL_15,\
    DATA_SMALL_16,\
    DATA_SMALL_17,\
    DATA_SMALL_18,\
    DATA_SMALL_19,\
    DATA_SMALL_20,\
    DATA_SMALL_21,\
    DATA_SMALL_22,\
    DATA_SMALL_23,\
    DATA_SMALL_24,\
    DATA_SMALL_25,\
    DATA_SMALL_26,\
    DATA_SMALL_27,\
    DATA_SMALL_28,\
    DATA_SMALL_29,\
    DATA_SMALL_30,\
    DATA_SMALL_31,\
    DATA_SMALL_32,\
    DATA_SMALL_33,\
    DATA_SMALL_34,\
    DATA_SMALL_35,\
    DATA_SMALL_36,\
    DATA_SMALL_37,\
    DATA_SMALL_38,\
    DATA_SMALL_39,\
    DATA_INT_0,\
    DATA_INT_1,\
    DATA_INT_2,\
    DATA_INT_3,\
    DATA_INT_4,\
    DATA_INT_5,\
    DATA_INT_6,\
    DATA_INT_7,\
    DATA_INT_8,\
    DATA_INT_9,\
    DATA_REFERENCE_0,\
    DATA_REFERENCE_1,\
    DATA_REFERENCE_2,\
    DATA_REFERENCE_3,\
    DATA_REFERENCE_4,\
    DATA_REFERENCE_5,\
    DATA_REFERENCE_6,\
    DATA_REFERENCE_7,\
    DATA_REFERENCE_8,\
    DATA_REFERENCE_9,\
    DATA_DATE_0,\
    DATA_DATE_1,\
    DATA_DATE_2,\
    DATA_DATE_3,\
    DATA_DATE_4\

# set-row to set all data fro the mastermodule
set_row_master : \
    MASTER_ID = ?,\
    SUB_ID = ?,\
    USER_ID = ?,\
    GROUP_ID = ?,\
    PROJECT_ID = ?,\
    ACCESS_FLAGS = ?,\
    STATE = ?,\
    LOCKED_BY = ?,\
    LASTMODIFIED_BY = ?,\
    DATE_CREATED = ?,\
    DATE_LASTMODIFIED = ?,\
    PUBLICATION_DATE = ?,\
    PURGE_DATE = ?,\
    FLAGS = ?,\
    FEED_ID = ?,\
    FEED_REFERENCE = ?,\
    FEED_FILENAME = ?,\
    TITLE = ?,\
    DATA_BIG_0 = ?,\
    DATA_BIG_1 = ?,\
    DATA_BIG_2 = ?,\
    DATA_BIG_3 = ?,\
    DATA_BIG_4 = ?,\
    DATA_BIG_5 = ?,\
    DATA_BIG_6 = ?,\
    DATA_BIG_7 = ?,\
    DATA_BIG_8 = ?,\
    DATA_BIG_9 = ?,\
    DATA_MEDIUM_0 = ?,\
    DATA_MEDIUM_1 = ?,\
    DATA_MEDIUM_2 = ?,\
    DATA_MEDIUM_3 = ?,\
    DATA_MEDIUM_4 = ?,\
    DATA_MEDIUM_5 = ?,\
    DATA_MEDIUM_6 = ?,\
    DATA_MEDIUM_7 = ?,\
    DATA_MEDIUM_8 = ?,\
    DATA_MEDIUM_9 = ?,\
    DATA_SMALL_0  = ?,\
    DATA_SMALL_1  = ?,\
    DATA_SMALL_2  = ?,\
    DATA_SMALL_3  = ?,\
    DATA_SMALL_4  = ?,\
    DATA_SMALL_5  = ?,\
    DATA_SMALL_6  = ?,\
    DATA_SMALL_7  = ?,\
    DATA_SMALL_8  = ?,\
    DATA_SMALL_9  = ?,\
    DATA_SMALL_10 = ?,\
    DATA_SMALL_11 = ?,\
    DATA_SMALL_12 = ?,\
    DATA_SMALL_13 = ?,\
    DATA_SMALL_14 = ?,\
    DATA_SMALL_15 = ?,\
    DATA_SMALL_16 = ?,\
    DATA_SMALL_17 = ?,\
    DATA_SMALL_18 = ?,\
    DATA_SMALL_19 = ?,\
    DATA_SMALL_20 = ?,\
    DATA_SMALL_21 = ?,\
    DATA_SMALL_22 = ?,\
    DATA_SMALL_23 = ?,\
    DATA_SMALL_24 = ?,\
    DATA_SMALL_25 = ?,\
    DATA_SMALL_26 = ?,\
    DATA_SMALL_27 = ?,\
    DATA_SMALL_28 = ?,\
    DATA_SMALL_29 = ?,\
    DATA_SMALL_30 = ?,\
    DATA_SMALL_31 = ?,\
    DATA_SMALL_32 = ?,\
    DATA_SMALL_33 = ?,\
    DATA_SMALL_34 = ?,\
    DATA_SMALL_35 = ?,\
    DATA_SMALL_36 = ?,\
    DATA_SMALL_37 = ?,\
    DATA_SMALL_38 = ?,\
    DATA_SMALL_39 = ?,\
    DATA_INT_0 = ?,\
    DATA_INT_1 = ?,\
    DATA_INT_2 = ?,\
    DATA_INT_3 = ?,\
    DATA_INT_4 = ?,\
    DATA_INT_5 = ?,\
    DATA_INT_6 = ?,\
    DATA_INT_7 = ?,\
    DATA_INT_8 = ?,\
    DATA_INT_9 = ?,\
    DATA_REFERENCE_0 = ?,\
    DATA_REFERENCE_1 = ?,\
    DATA_REFERENCE_2 = ?,\
    DATA_REFERENCE_3 = ?,\
    DATA_REFERENCE_4 = ?,\
    DATA_REFERENCE_5 = ?,\
    DATA_REFERENCE_6 = ?,\
    DATA_REFERENCE_7 = ?,\
    DATA_REFERENCE_8 = ?,\
    DATA_REFERENCE_9 = ?,\
    DATA_DATE_0 = ?,\
    DATA_DATE_1 = ?,\
    DATA_DATE_2 = ?,\
    DATA_DATE_3 = ?,\
    DATA_DATE_4 = ?\

# all colum values of the mastermodule
colum_values_master : \
    ? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,\
    ? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,\
    ? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,\
    ? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,\
    ? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,\
    ? ,? ,?

# all colum names of the mastermedia
colum_names_media : \
    MEDIA_ID,\
    MASTER_ID,\
    MEDIA_POSITION,\
    MEDIA_WIDTH,\
    MEDIA_HEIGHT,\
    MEDIA_SIZE,\
    MEDIA_MIMETYPE,\
    MEDIA_TYPE,\
    MEDIA_TITLE,\
    MEDIA_NAME,\
    MEDIA_DESCRIPTION,\
    MEDIA

# set-row to set all data fro the media
set_row_media : \
    MEDIA_ID = ? ,\
    MASTER_ID = ? ,\
    MEDIA_POSITION = ? ,\
    MEDIA_WIDTH = ? ,\
    MEDIA_HEIGHT = ? ,\
    MEDIA_SIZE = ? ,\
    MEDIA_MIMETYPE = ? ,\
    MEDIA_TYPE = ? ,\
    MEDIA_TITLE = ? ,\
    MEDIA_NAME = ? ,\
    MEDIA_DESCRIPTION = ? ,\
    MEDIA = ?

# all colum names of the mastermedia
colum_values_media : \
    ? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,?
    
# writes an entry in the publish history for a published COS resource
C_RESOURCES_WRITE_PUBLISH_HISTORY : \
INSERT INTO CMS_PUBLISH_HISTORY \
	(PUBLISH_TAG,\
	STRUCTURE_ID,\
	RESOURCE_ID,\
	RESOURCE_PATH,\
	RESOURCE_STATE,\
	RESOURCE_TYPE,\
	HISTORY_ID,\
	SIBLING_COUNT) \
VALUES \
	(?,?,?,?,?,?,?,?)