オラクル登録済みTBL→DDL生成→ポスグレ用に変換

 1#!/bin/bash
 2# ファイル名：mkddl4pos
 3# 実行形式：mkddl4pos owner （ownerはオラクルのユーザ名（スキーマ名））
 4#
 5# 事前準備：
 6# table.lstにはOUTPUTしたいTBLのみ縦1列に記載したものを用意
 7# カレントに pos, ora の二つのディレクトリがあること
 8# カレントに get_table_ddl.sql, ora2pos.awk があること
 9#
10# 説明：
11# コマンド実行すると、oraにはオラクルTBLのDDLが出力され、
12# posにはoraにあるファイルをポスグレ用に置換したファイルが出力される。
13
14# 引数チェック
15if [ $# -ne 1 ]; then echo "引数不正。usage: ./ora2pos_ddl owner"; exit 1; fi
16
17# table.lstの存在チェック
18if [ ! -r table.lst ]; then echo "table.lst がありません"; exit 1 fi
19
20# ora, posのディレクトリ存在チェック
21if [ ! -d ora ]; then echo "ディレクトリ ora がありません"; exit 1; fi
22if [ ! -d pos ]; then echo "ディレクトリ pos がありません"; exit 1; fi
23
24# get_table_ddl.sql, ora2pos.awk の存在チェック
25if [ ! -r get_table_ddl.sql ]; then echo "get_table_ddl.sql がありません"; exit 1; fi
26if [ ! -r get_table_ddl.sql ]; then echo "ora2pos.awk がありません"; exit 1; fi
27
28# ここからメイン処理
29UOWNER=`awk -v str=$1 'BEGIN{print toupper(str)}'`
30POSDIR=./pos
31ORADIR=./ora
32export ORADIR  # ORADIRは後で呼び出すsqlファイル内で参照するのでexportする
33
34# オラクルDDL作成
35while read table_name
36do
37    if [ "${table_name}" == "" ]; then continue; fi
38    TNAME=`awk -v str=${table_name} 'BEGIN{print toupper(str)}'`
39    sqlplus -s ${UOWNER}/${UOWNER} @get_table_ddl.sql ${UOWNER} ${TNAME}
40    echo "$TNAME のDDL作成完了"
41done < table.lst
42
43# ポスグレ用DDLに変換
44(cd ${ORADIR}; ls -1) | \
45while read ddl_file
46do
47    awk -f ora2pos.awk ${ORADIR}/${ddl_file} > ${POSDIR}/${ddl_file}
48done
49
50echo "全てのDDL作成完了しました"
51exit 0

 1SET LIN 20000
 2-- １行に収まらない内場合改行して表示
 3SET WRAP ON
 4SET PAGESIZE 0
 5SET LONG 400000
 6SET LONGCHUNKSIZE 400000
 7-- 問い合わせの結果レコード件数表示制御
 8SET FEEDBACK OFF
 9-- コマンド表示制御
10SET ECHO OFF
11-- コマンド結果表示制御
12SET TERMOUT OFF
13-- 行末の空白除去
14SET TRIMS ON
15-- バインド変数新旧表示除去
16SET VERIFY OFF
17
18-- 以下のexec文では引数箇所を改行するとどういうわけか効かなくなるのでワンライナーで書くこと
19-- DDLの終わりにセミコロン
20EXEC DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE );
21-- PK情報はalter tableで表現
22EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE );
23-- STORAGE属性を省略
24EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE );
25-- セグメント属性を出力
26EXEC DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', TRUE );
27-- 表領域を省略
28EXEC DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE', FALSE );
29--パーティション省略
30EXEC DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'PARTITIONING', FALSE );
31-- スキーマ名省略
32EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA', FALSE );
33
34--出力ファイル名変数定義
35col spoolout_file_name new_value spool_file_name format a50
36select '&2'||'.sql' spoolout_file_name from dual;
37
38spool ${ORADIR}/&spool_file_name
39
40-- TABLE DDLの取得
41select dbms_metadata.get_ddl('TABLE', t.TABLE_NAME, owner)
42  from dba_tables t
43 where owner= '&1' and t.TABLE_NAME = '&2';
44
45-- INDEX DDLの取得
46select dbms_metadata.get_ddl('INDEX', t.INDEX_NAME, t.OWNER )
47  from dba_indexes t
48       left outer join DBA_CONSTRAINTS c on t.OWNER =c.INDEX_OWNER and t.INDEX_NAME = c.INDEX_NAME
49 where t.OWNER= '&1' and t.TABLE_NAME ='&2' and c.CONSTRAINT_TYPE is null;
50
51spool off;
52exit

 1{
 2    # ENABLEはポスグレにはないので削除する
 3    $0 = gensub(/ ENABLE([,;]|$)/, "\\1", 1)
 4    # NUMBER 4桁以下 -> smallint
 5    $0 = gensub(/ NUMBER\([1-4],0\))/, " smallint", 1)
 6    # NUMBER 5桁以上9桁以下 -> integer
 7    $0 = gensub(/ NUMBER\([5-9],0\))/, " integer", 1)
 8    # NUMBER 10桁以上18桁以下 -> bigint
 9    $0 = gensub(/ NUMBER\(1[0-8],0\))/, " bigint", 1)
10    # NUMBER 19桁以上 -> numeric
11    $0 = gensub(/ NUMBER/, " numeric", 1)
12    # VARCHAR2 -> varchar
13    $0 = gensub(/ VARCHAR2/, " varchar", 1)
14    # CHAR -> char
15    $0 = gensub(/ CHAR/, " char", 1)
16    # TIMESTAMP -> timestamp
17    $0 = gensub(/ TIMESTAMP/, " timestamp", 1)
18    # DATE -> timestamp
19    $0 = gensub(/ DATE/, " timestamp", 1)
20    # (DEFAULT指定の)sysdate -> current_timestamp
21    $0 = gensub(/ sysdate/, " current_timestamp", 1)
22    # RAW -> bytea
23    $0 = gensub(/ RAW/, " bytea", 1)
24    # BLOB -> bytea
25    $0 = gensub(/ BLOB/, " bytea", 1)
26    # ダブルクォート削除
27    $0 = gensub(/"/, "", "g")
28
29# テーブルセグメント属性を修正
30# PCTFREEに対応するfillfactorのみ残す。fillfactor = 100 - PCTFREE の関係がある。
31/SEGMENT CREATION/{
32    str = $0;
33    getline var; str = str var
34    getline var; str = str var
35    pctfree = substr(str, match(str, /[0-9]+/), RLENGTH)
36    $0 = gensub(/SEGMENT CREATION.+/, "with (fillfactor = " (100 - pctfree) ");", 1, str)
37}
38
39# ALTER TABLE文またはCREATE INDEX文の属性修正。
40# 1. PCTFREEに対応するfillfactorのみ残す。fillfactor = 100 - PCTFREE の関係がある。
41# 2. COMPUTE STATISTICS句は索引での統計の収集を開始および停止するもの。
42#    var10.1以上は自動収集されるので無視する。
43# 3. LOGGING/NOLOGGINGはポスグレにないので無視する。
44/(USING INDEX|PCTFREE)/{
45    str = $0
46    pctfree = substr(str, match(str, /[0-9]+/), RLENGTH)
47    $0 = gensub(/(USING INDEX|PCTFREE).+/, "with (fillfactor = " (100 - pctfree) ");", 1, str)
48}
49{print}