﻿* SET_DB_P.TXT HARASAWA
P-COMMENT_BEGIN
=SET{W_PER1='zzzz',W_PER2='zzzy'};
=SET{W_MWL1='mast',W_MWL2='work',W_MWL3='work'};
=SET{W_DIOL1='dbo',W_DIOL2='inp',W_DIOL3='out'};
*********( CREATE DATABASE )*********************************************************
U+FORM1=☆お願い☆：実行開始後、端末画面側でエラー確認、Enterで続行下さい。};
W+=SET{WC='&'};
W+=IF{_DB_SOFT='P82'}SET{W_PATH='"?_OS_DRIVE?\Program Files\PostgreSQL\8.2\bin\createdb"'};
W+=IF{_DB_SOFT='P83'}SET{W_PATH='"?_OS_DRIVE?\Program Files\PostgreSQL\8.3\bin\createdb"'};
U+=SET{W_PATH='/usr/local/pgsql/bin/createdb',WC=';'};
U+=MESSAGE{☆お願い☆：端末画面側でエラー確認後、Enterで続行下さい。};
=COMMAND{echo =========================================================================
=?WC?echo ☆お願い☆：パスワードを要求された場合は'?_LOGIN_ID?'のパスワードを入力下さい。
=?WC?echo ==============================================================================
=?WC??W_PATH? -U ?_LOGIN_ID? i_?WLSYSTEM?
=?WC?echo ===========================================
=?WC?echo ☆お願い☆：エラー確認後、Enterで続行下さい。
=?WC?echo ===========================================
W+=?WC?PAUSE};
U+=?WC?read a};
*********( CREATE USER )*********************************************************
=MESSAGE{CREATE USER};
=SET{_LOWER='ZZZZ',W_PER=_LOWER};
NAME=LOGIN
REPEAT=2 (W_RT##)
REPEAT2=4 (W_DIOL$$)
$1+=SET{WPAS=PASSWORD_?W_RT##?1};
$4+=SET{WPAS=PASSWORD_LINK1};
$1+=SET{W_CU='CREATEDB CREATEUSER'};
$2+=SET{W_CU=};
=LOGIN_SQL{CREATE USER ?W_IS??W_RT##?_?W_DIOL$$?_?W_PER? WITH ?W_CU? PASSWORD '?WPAS?'}
=  IF{_DB_ERROR!='P_ALREADY'}ERROR{};
REPEAT=
NAME=
=SET{_LOWER='ZZZY',W_PER=_LOWER};
COPY=LOGIN
*********( REVOKE PUBLIC )*********************************************************
=LOGIN_SQL{REVOKE ALL ON SCHEMA public from public};
*********( CREATE SCEMA )*********************************************************
=MESSAGE{CREATE SCHEMA};
=SET{_LOWER='ZZZZ',W_PER=_LOWER};
NAME=SCHEMA
REPEAT=2  (W_RT##=REAL,TEST)
REPEAT2=2 (W_MW$$=MAST,WORK) 
=LOGIN_SQL{CREATE SCHEMA ?W_IS??W_RT##?_?W_MW$$?_?W_PER? AUTHORIZATION ?W_IS??W_RT##?_dbo_?W_PER?}
=  IF{_DB_ERROR!='P_ALREADY'}ERROR{?_DB_ERROR? ?_ERROR?};
=LOGIN_SQL{GRANT USAGE ON SCHEMA ?W_IS??W_RT##?_?W_MW$$?_?W_PER? TO PUBLIC};
REPEAT=
NAME=
=SET{_LOWER='ZZZY',W_PER=_LOWER};
COPY=SCHEMA
*************( GRANT ALL )************************************************************
=SET{_LOWER='ZZZZ',W_PER=_LOWER};
NAME=ADDROLLMEMBER
=MESSAGE{EXEC EXEC SP_ADDROLEMEMBER (?W_PER?)};
REPEAT=3
REPEAT2=2 (W_PER$$,ZZ,自分))
=LOGIN_SQL{GRANT ALL PRIVILEGES ON SCHEMA ?W_IS??W_RT_A##?_MAST_?W_PER? TO ?W_IS?real_dbo_zzzz};
=LOGIN_SQL{GRANT ALL PRIVILEGES ON SCHEMA ?W_IS??W_RT_A##?_MAST_?W_PER? TO ?W_IS??W_RT_B##?_DBO_?W_PER$$?};
=LOGIN_SQL{GRANT ALL PRIVILEGES ON SCHEMA ?W_IS??W_RT_A##?_WORK_?W_PER? TO ?W_IS??W_RT_B##?_DBO_?W_PER$$?};
=LOGIN_SQL{GRANT ALL PRIVILEGES ON SCHEMA ?W_IS??W_RT_A##?_WORK_?W_PER? TO ?W_IS??W_RT_B##?_INP_?W_PER$$?};
=LOGIN_SQL{GRANT ALL PRIVILEGES ON SCHEMA ?W_IS??W_RT_A##?_WORK_?W_PER? TO ?W_IS??W_RT_B##?_OUT_?W_PER$$?};
=IF{W_PER='zzzz'}JUMP{J};
REPEAT2=
=J:
REPEAT=
NAME=
=SET{_LOWER='ZZZY',W_PER=_LOWER};
COPY=ADDROLLMEMBER
********( CREATE VIEW )*******************************************************************
=SET{W_REAL_TEST='REAL',_REAL_TEST='REAL',_LOWER='REAL',W_REAL_TEST_L=_LOWER};
=SET{_LOWER='ZZZZ',W_PER=_LOWER};
=VIEW:
=SET{W_M=_M_?W_REAL_TEST?};
=SET{_LOWER='?W_IS??W_REAL_TEST?',W_DBX=_LOWER};
REPEAT=2
=MESSAGE{CREATE VIEW(?W_REAL_TEST?,?W_PER?)};
=SET{_LOWER='?W_DBX?_?W_MW##?_?W_PER?',W_SCHEMA=_LOWER};
***( VIEW ZZZZ_SYS_OBJECT_VIEW )***
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_OBJECT_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_OBJECT_VIEW}{VIEW}{
=(ZZZZ_KIND,ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_TEXT
=,ZZZZ_ADD_DATE,ZZZZ_CHANGE_DATE)AS SELECT
= CAST('TABLE'  AS CHAR(5)),CAST('?W_REAL_TEST?' AS CHAR(4))
=,CAST('?W_MW##?' AS CHAR(4)),CAST(' ' AS CHAR(3)),CAST(UPPER(TABLENAME) AS CHAR(30))
=,CAST(' ' AS CHAR(4000)),CAST(NULL AS TIMESTAMP)
=,CAST(NULL AS TIMESTAMP) FROM PG_TABLES WHERE 
=SCHEMANAME='?W_SCHEMA?' 
=UNION SELECT CAST('VIEW'  AS CHAR(5)),CAST('?W_REAL_TEST?' AS CHAR(4))
=,CAST('?W_MW##?' AS CHAR(4)),CAST(' ' AS CHAR(3)),CAST(UPPER(VIEWNAME) AS CHAR(30))
=,CAST(DEFINITION AS CHAR(4000)),CAST(NULL AS TIMESTAMP)
=,CAST(NULL AS TIMESTAMP) FROM PG_VIEWS WHERE
= SCHEMANAME='?W_SCHEMA?' 
=UNION SELECT CAST('FUNC'  AS CHAR(5)),CAST('?W_REAL_TEST?' AS CHAR(4))
=,CAST('?W_MW##?' AS CHAR(4)),CAST(' ' AS CHAR(3)),CAST(UPPER(PRONAME) AS CHAR(30))
=,CAST(PROSRC AS CHAR(4000)),CAST(NULL AS TIMESTAMP)
=,CAST(NULL AS TIMESTAMP) FROM PG_PROC,PG_NAMESPACE WHERE
= NSPNAME='?W_SCHEMA?' AND PROOWNER=NSPOWNER
=}IF{_DB_ERROR!='P_ALREADY'}ERROR{?_DB_ERROR? ?_ERROR?};
***( VIEW ZZZZ_SYS_?W_MW##?_?W_PERL##?_COLUMN_VIEW )*** 
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_COLUMN_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_COLUMN_VIEW}{VIEW}{
=(ZZZZ_KIND,ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_SEQ,ZZZZ_DATA_NAME
=,ZZZZ_DEFAULT_TYPE,ZZZZ_DEFAULT_LENGTH1,ZZZZ_DEFAULT_LENGTH2
=,ZZZZ_DEFAULT,ZZZZ_NULL)AS
= SELECT CAST('TABLE' AS CHAR(5))
NAME=P_COLUMN##
=,CAST('?W_REAL_TEST?' AS CHAR(4))
=,CAST('?W_MW##?' AS CHAR(4)),CAST(' ' AS CHAR(3)),CAST(UPPER(C.RELNAME) AS CHAR(30))
=,A.ATTNUM,CAST(UPPER(A.ATTNAME) AS CHAR(30))
=,CAST(CASE TYPNAME WHEN 'bpchar' THEN CHAR 'CHAR'
= WHEN 'numeric' THEN CHAR 'DECIMAL' WHEN 'int4'
= THEN CHAR 'INT' WHEN 'int2' THEN CHAR 'INT' 
= WHEN 'float8' THEN CHAR 'FLOAT'
= ELSE UPPER(TYPNAME) END AS CHAR(9))
=,CAST(CASE WHEN TYPNAME IN('bpchar','varchar')
= THEN A.ATTTYPMOD-4 WHEN TYPNAME IN('numeric') 
= THEN (A.ATTTYPMOD-4)/65536 WHEN TYPNAME
= IN('timestamp') THEN 17 WHEN TYPNAME IN('text')
= THEN 4000 ELSE 9 END AS INT),CAST(CASE WHEN 
= TYPNAME IN('numeric')
=THEN (A.ATTTYPMOD-4)%65536 ELSE 0 END AS INT)
=,CAST(REPLACE(REPLACE(D.ADSRC,'::bpchar','')
=,'::character varying','') AS CHAR(30))
=,CAST(CASE WHEN A.ATTNOTNULL THEN '0' ELSE '1' END
= AS CHAR(1)) FROM PG_CLASS C JOIN 
NAME=
= PG_TABLES T ON C.RELNAME=T.TABLENAME AND C.RELKIND='r' 
= AND T.SCHEMANAME='?W_SCHEMA?' 
= JOIN PG_USER U ON U.USENAME=T.TABLEOWNER 
NAME=P_COLUMN_JOIN##
= AND C.RELOWNER=U.USESYSID 
= JOIN PG_ATTRIBUTE A ON C.OID=A.ATTRELID AND A.ATTNUM>0
= JOIN PG_TYPE TY ON A.ATTTYPID=TY.OID
= LEFT JOIN PG_ATTRDEF D ON C.RELFILENODE=D.ADRELID
= AND A.ATTNUM=D.ADNUM
NAME=
=UNION SELECT CAST('VIEW' AS CHAR(5))
COPY=P_COLUMN##
= PG_VIEWS T ON C.RELNAME=T.VIEWNAME AND C.RELKIND='v' 
= AND T.SCHEMANAME='?W_SCHEMA?' 
= JOIN PG_USER U ON U.USENAME=T.VIEWOWNER 
COPY=P_COLUMN_JOIN##
=}IF{_DB_ERROR!='P_ALREADY'}ERROR{?_DB_ERROR? ?_ERROR?};
*****( VIEW ZZZZ_SYS_GRANT_VIEW )***** 
=SET{W0L8=WLSYSTEM@LENGTH};COMPUTE{W0L8+=8};
=SET{W_GRANT='PUBLIC',W_GRANTX=};
NAME2=GRANT##
=SET{W_GRANT_?W_GRANT?##=
=' CAST(CASE C.RELKIND WHEN ''v'' THEN ''VIEW'''
=+' ELSE ''TABLE'' END AS CHAR(5)),CAST(''?W_REAL_TEST?'' AS CHAR(4))'
=+',CAST(''?W_MW##?'' AS CHAR(4)),CAST('' '' AS CHAR(3)),CAST(UPPER(C.RELNAME) AS CHAR(30))'
=+',CAST(''?W_GRANT?'' AS CHAR(8)),CAST(CASE WHEN'
=+'    ACLCONTAINS(RELACL,ACLITEM''?W_GRANTX?=r/?W_IS??W_REAL_TEST_L?_dbo_zzzz'')'
=+' OR ACLCONTAINS(RELACL,ACLITEM''?W_GRANTX?=r/?W_IS??W_REAL_TEST_L?_dbo_?W_PER?'')'
=+' THEN CHAR ''S'' ELSE CHAR '' '' END AS CHAR(1)),CAST(CASE WHEN'
=+'    ACLCONTAINS(RELACL,ACLITEM''?W_GRANTX?=a/?W_IS??W_REAL_TEST_L?_dbo_zzzz'')'
=+' OR ACLCONTAINS(RELACL,ACLITEM''?W_GRANTX?=a/?W_IS??W_REAL_TEST_L?_dbo_?W_PER?'')'
=+' THEN CHAR ''I'' ELSE CHAR '' '' END AS CHAR(1)),CAST(CASE WHEN'
=+'    ACLCONTAINS(RELACL,ACLITEM''?W_GRANTX?=w/?W_IS??W_REAL_TEST_L?_dbo_zzzz'')'
=+' OR ACLCONTAINS(RELACL,ACLITEM''?W_GRANTX?=w/?W_IS??W_REAL_TEST_L?_dbo_?W_PER?'')'
=+' THEN CHAR ''U'' ELSE CHAR '' '' END AS CHAR(1)),CAST(CASE WHEN'
=+'    ACLCONTAINS(RELACL,ACLITEM''?W_GRANTX?=d/?W_IS??W_REAL_TEST_L?_dbo_zzzz'')'
=+' OR ACLCONTAINS(RELACL,ACLITEM''?W_GRANTX?=d/?W_IS??W_REAL_TEST_L?_dbo_?W_PER?'')'
=+' THEN CHAR ''D'' ELSE CHAR '' '' END AS CHAR(1)),CAST('' ''AS CHAR(1))'
=+' FROM PG_CLASS C WHERE'
=+'   (ACLCONTAINS(RELACL,ACLITEM''?W_GRANTX?=r/?W_IS??W_REAL_TEST_L?_dbo_zzzz'')'
=+' OR ACLCONTAINS(RELACL,ACLITEM''?W_GRANTX?=r/?W_IS??W_REAL_TEST_L?_dbo_?W_PER?''))'
=+' AND (EXISTS(SELECT T.TABLENAME FROM PG_TABLES T'
=+' JOIN PG_USER U ON U.USENAME=T.TABLEOWNER'
=+' WHERE C.RELNAME=T.TABLENAME' 
=+' AND T.SCHEMANAME=''?W_SCHEMA?'''
=+' AND C.RELOWNER=U.USESYSID AND C.RELKIND=''r'' )'
=+' OR EXISTS(SELECT V,VIEWNAME FROM PG_VIEWS V'
=+' JOIN PG_USER U ON U.USENAME=V.VIEWOWNER' 
=+' WHERE C.RELNAME=V.VIEWNAME'  
=+' AND V.SCHEMANAME=''?W_SCHEMA?'''
=+' AND C.RELOWNER=U.USESYSID AND C.RELKIND=''v''))'};
NAME2=
=SET{W_GRANT='INP_ZZZZ',_LOWER='?W_DBX?_INP_ZZZZ',W_GRANTX=_LOWER};
COPY=GRANT##
=SET{W_GRANT='OUT_ZZZZ',_LOWER='?W_DBX?_OUT_ZZZZ',W_GRANTX=_LOWER};
COPY=GRANT##
=SET{W_GRANT='LNK_ZZZZ',_LOWER='?W_DBX?_LNK_ZZZZ',W_GRANTX=_LOWER};
COPY=GRANT##
=SET{W_GRANT='INP_ZZZY',_LOWER='?W_DBX?_INP_ZZZY',W_GRANTX=_LOWER};
COPY=GRANT##
=SET{W_GRANT='OUT_ZZZY',_LOWER='?W_DBX?_OUT_ZZZY',W_GRANTX=_LOWER};
COPY=GRANT##
=SET{W_GRANT='LNK_ZZZY',_LOWER='?W_DBX?_LNK_ZZZY',W_GRANTX=_LOWER};
COPY=GRANT##
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_GRANT_VIEW}{VIEW}; 
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_GRANT_VIEW}{VIEW}{
=(ZZZZ_KIND,ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_GRANT,ZZZZ_SELECT
=,ZZZZ_INSERT,ZZZZ_UPDATE,ZZZZ_DELETE,ZZZZ_EXECUTE)AS
= SELECT       ?W_GRANT_PUBLIC##?
= UNION SELECT ?W_GRANT_INP_ZZZZ##?
= UNION SELECT ?W_GRANT_OUT_ZZZZ##? 
= UNION SELECT ?W_GRANT_LNK_ZZZZ##?
= UNION SELECT ?W_GRANT_INP_ZZZY##?
= UNION SELECT ?W_GRANT_OUT_ZZZY##?
= UNION SELECT ?W_GRANT_LNK_ZZZY##?
=}IF{_DB_ERROR!='P_ALREADY'}ERROR{?_DB_ERROR? ?_ERROR?};
=DBO_DROP_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_INDEX_VIEW}{VIEW}; 
=SET{W_INDEX='SUBSTRING(INDEXDEF,POSITION(''('''
=+' IN INDEXDEF)+1,POSITION('')'' IN INDEXDEF)'
=+'-POSITION(''('' IN INDEXDEF)-1)'};
=SET{W_SPLIT='CAST(UPPER(TRIM(SPLIT_PART(?W_INDEX?,'','','};
=SET{W_SQL1='CAST(''?W_REAL_TEST?'' AS CHAR(4)),CAST(''?W_MW##?'' AS CHAR(4)),CAST('' '' AS CHAR(3))'
=    +',CAST(UPPER(TABLENAME) AS CHAR(30)),'};
=SET{W_SQL2='CAST(SUBSTRING(INDEXNAME'
=+',LENGTH(TRIM(INDEXNAME)),1) AS CHAR(1))'
=+',CAST(CASE WHEN INDEXDEF LIKE ''CREATE UNIQUE %'' THEN'};
=SET{W_SQL3='END AS CHAR(1)) FROM PG_INDEXES WHERE'
=+' SCHEMANAME=''?W_SCHEMA?'' AND TRIM('' '')<>'};
=DBO_CREATE_OBJECT{?W_M?_SYS_?W_MW##?_?W_PER?_INDEX_VIEW}{VIEW}{(
= ZZZZ_REAL,ZZZZ_WORK,ZZZZ_USER,ZZZZ_NAME,ZZZZ_DATA_NAME,ZZZZ_IX,ZZZZ_NO)AS
= SELECT ?W_SQL1??W_SPLIT? 1)))AS CHAR(80))
=,?W_SQL2? '0' ELSE '1' ?W_SQL3??W_SPLIT? 1)))AS CHAR(80))  
REPEAT2=8
=UNION SELECT ?W_SQL1??W_SPLIT? $+)))AS CHAR(80))
=,?W_SQL2? '$$' ELSE '$+' ?W_SQL3??W_SPLIT? $+)))AS CHAR(80))
REPEAT2=  
=UNION SELECT ?W_SQL1??W_SPLIT? 10)))AS CHAR(80))
=,?W_SQL2? '9' ELSE '0' ?W_SQL3??W_SPLIT? 10)))AS CHAR(80))  
=}IF{_DB_ERROR!='P_ALREADY'}ERROR{?_DB_ERROR??_ERROR?};
REPEAT=
=IF{W_PER='zzzy'}JUMP{J};
=SET{_LOWER='ZZZY',W_PER=_LOWER};BACK{VIEW};
=J:
=IF{W_REAL_TEST='TEST'}JUMP{J};
=SET{_LOWER='ZZZZ',W_PER=_LOWER};
=SET{W_REAL_TEST='TEST',_REAL_TEST='TEST',_LOWER='TEST',W_REAL_TEST_L=_LOWER};
=BACK{VIEW};
=J:
********( CREATE FUNCTION )**************************************************
=MESSAGE{CREATE FUNC};
=SET{W_REAL_TEST='REAL',_REAL_TEST='REAL',_LOWER='REAL',W_REAL_TEST_L=_LOWER};
REPEAT=2
=SET{W_M=_M_?W_REAL_TEST?};
=DBO_DROP_OBJECT{?W_M?_DAY_CHAR_FUNC(CHAR,INT)}{FUNC}; 
=DBO_SQL{CREATE FUNCTION ?W_M?_DAY_CHAR_FUNC(CHAR,INT)RETURNS CHAR AS '
= SELECT TO_CHAR(TO_DATE($1,''YYYYMMDD'') + $2,''YYYYMMDD'');
= ' LANGUAGE SQL;}
=  IF{_DB_ERROR!='P_ALREADY'}ERROR{?_DB_ERROR? ?_ERROR?};
=DBO_DROP_OBJECT{?W_M?_DAY_DATE_FUNC(TIMESTAMP WITH TIME ZONE,INT)}{FUNC}; 
=DBO_SQL{CREATE FUNCTION ?W_M?_DAY_DATE_FUNC(TIMESTAMP WITH TIME ZONE,INT)RETURNS CHAR AS '
= SELECT TO_CHAR(CAST($1 AS DATE) + $2,''YYYYMMDD'');
= ' LANGUAGE SQL;}
=  IF{_DB_ERROR!='P_ALREADY'}ERROR{?_DB_ERROR? ?_ERROR?};
=DBO_DROP_OBJECT{?W_M?_WEEK_CHAR_FUNC(CHAR)}{FUNC}; 
=DBO_SQL{CREATE FUNCTION ?W_M?_WEEK_CHAR_FUNC(CHAR)RETURNS DOUBLE PRECISION AS '
= SELECT EXTRACT(DOW FROM TO_TIMESTAMP($1,''YYYYMMDD''))+1;
= ' LANGUAGE SQL;}
=  IF{_DB_ERROR!='P_ALREADY'}ERROR{?_DB_ERROR??_ERROR?};
=DBO_DROP_OBJECT{?W_M?_WEEK_DATE_FUNC(TIMESTAMP WITH TIME ZONE)}{FUNC}; 
=DBO_SQL{CREATE FUNCTION ?W_M?_WEEK_DATE_FUNC(TIMESTAMP WITH TIME ZONE)RETURNS DOUBLE PRECISION AS'
= SELECT EXTRACT(DOW FROM $1) + 1;
= ' LANGUAGE SQL;}
= IF{_DB_ERROR!='P_ALREADY'}ERROR{?_DB_ERROR??_ERROR?};
=SET{W_REAL_TEST='TEST',_REAL_TEST='TEST',_LOWER='TEST',W_REAL_TEST_L=_LOWER};
REPEAT=
P-COMMENT_END
