WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR  EXIT FAILURE ROLLBACK;

spool SYS_CONFIG_CUSTOMIZATION.log

prompt
prompt Creating table SYS_CONFIG_CUSTOMIZATION
prompt ====================================
prompt
whenever sqlerror continue
drop table SYS_CONFIG_CUSTOMIZATION; 
whenever sqlerror exit failure rollback

-- Create table
create table SYS_CONFIG_CUSTOMIZATION
(
  RECORD_ID        NUMBER not null,
  HEAD_ID          NUMBER,
  ENABLE_FLAG      VARCHAR2(1) default 'Y',
  SOURCE_FILE      VARCHAR2(1000) not null,
  SOURCE_VERSION   VARCHAR2(30),
  DIMENSION_TYPE   VARCHAR2(200) not null,
  DIMENSION_VALUE  VARCHAR2(500) not null,
  ID_VALUE         VARCHAR2(200) not null,
  MOD_TYPE         VARCHAR2(50) not null,
  ARRAY_NAME       VARCHAR2(100),
  INDEX_FIELD      VARCHAR2(100),
  INDEX_VALUE      VARCHAR2(500),
  XPATH            VARCHAR2(2000),
  POSITION         VARCHAR2(50),
  CONFIG_CONTENT   VARCHAR2(4000),
  ATTRIB_KEY       VARCHAR2(100),
  ATTRIB_VALUE     VARCHAR2(2000),
  COMMENTS         VARCHAR2(2000),
  CREATED_BY       NUMBER not null,
  CREATION_DATE    DATE not null,
  LAST_UPDATED_BY  NUMBER not null,
  LAST_UPDATE_DATE DATE not null,
  FIELDS_ORDER     VARCHAR2(2000)
);
-- Add comments to the columns 
comment on column SYS_CONFIG_CUSTOMIZATION.HEAD_ID
  is '关联sys_config_customization_head';
comment on column SYS_CONFIG_CUSTOMIZATION.ENABLE_FLAG
  is '只有此标志=Y的记录才生效';
comment on column SYS_CONFIG_CUSTOMIZATION.SOURCE_FILE
  is '源文件';
comment on column SYS_CONFIG_CUSTOMIZATION.SOURCE_VERSION
  is '源文件版本，取源文件顶层节点的version属性';
comment on column SYS_CONFIG_CUSTOMIZATION.DIMENSION_TYPE
  is '维度类型代码';
comment on column SYS_CONFIG_CUSTOMIZATION.DIMENSION_VALUE
  is '维值';
comment on column SYS_CONFIG_CUSTOMIZATION.ID_VALUE
  is '元素的id值，如果是对顶层节点进行个性化，id值与source_file相同';
comment on column SYS_CONFIG_CUSTOMIZATION.MOD_TYPE
  is '更改方式。set_attrib;设置属性; replace: 用config_content内容整体替代原节点; insert: 将config_content内容插入到position指定的位置; delete: 删除此节点; re_order: 重新排列子节点; re_locate: 重新定位此节点; cdata_replace: 用config_content内容替代原有cdata值; cdata_append:将config_content中的内容插入到原有cdata值，位置由position决定 ';
comment on column SYS_CONFIG_CUSTOMIZATION.ARRAY_NAME
  is '如果是数组元素，所属的数组名';
comment on column SYS_CONFIG_CUSTOMIZATION.INDEX_FIELD
  is '如果是数组元素，用于区分每个元素的属性名';
comment on column SYS_CONFIG_CUSTOMIZATION.INDEX_VALUE
  is '如果是数组元素，用于区分每个元素的属性值';
comment on column SYS_CONFIG_CUSTOMIZATION.XPATH
  is 'xpath路径（将来扩展用）';
comment on column SYS_CONFIG_CUSTOMIZATION.POSITION
  is 'before: 在原节点之前插入 after: 在原节点之后插入 first_child: 作为第一个子节点插入 last_child: 作为最后一个子节点插入';
comment on column SYS_CONFIG_CUSTOMIZATION.CONFIG_CONTENT
  is '新节点的xml内容；或新cdata值';
comment on column SYS_CONFIG_CUSTOMIZATION.ATTRIB_KEY
  is '如果是set_attrib操作，要设置的属性名';
comment on column SYS_CONFIG_CUSTOMIZATION.ATTRIB_VALUE
  is '如果是set_attrib操作，要设置的属性值';
comment on column SYS_CONFIG_CUSTOMIZATION.COMMENTS
  is '此个性化配置的说明';
comment on column SYS_CONFIG_CUSTOMIZATION.FIELDS_ORDER
  is '列排序';
-- Create/Recreate primary, unique and foreign key constraints 
alter table SYS_CONFIG_CUSTOMIZATION
  add primary key (RECORD_ID)
  using index ;
-- Create/Recreate indexes 
create index SYS_CONFIG_CUSTOMIZATION_N1 on SYS_CONFIG_CUSTOMIZATION (HEAD_ID);
create index SYS_CONFIG_CUSTOMIZATION_N2 on SYS_CONFIG_CUSTOMIZATION (SOURCE_FILE, DIMENSION_TYPE, DIMENSION_VALUE);
spool off
exit
