「SQLログ取得.xls」のつづき。のつづき。postgresのlog編集が出来上がった。
■ツール名「SQLログ取得.xls」
■http://d.hatena.ne.jp/kazpgm/20100125/1264441494のつづき。postgresのlog編集が出来上がった。と思ったがまだやることがあったみたいで2/1、2/2修正中。出来上がったらこの日記に更新する予定。2/3修正済み。出来上がった。＜＝02/04、02/07 02/24 02/25 03/01使いやすいようにまた修正した。
【１】当ツールはhttp://www.vector.co.jp/soft/winnt/business/se481861.html（vector）からダウンロードして使ってください。当ツールの概略
１．postgresを使っている既存ACCESSのやWEBの実行時SQLを解析する。
* ACCESSやWEBを実行する。（解析したいプログラムの単位）
* postgresのlogから指定時間から現在時間までの指定DBを抜き取って、
SQLを人間が見れるきれいなフォーマットに編集してエクセルに貼り付ける。
取得した時間をエクセルに保存しておく。
（SQLの日本語整形も出来る）
* これを繰り返す。【２】「SQLログ取得.xls」・「環境設定」シートに使用法など書いてあります。以下に抜粋しました。
エクセル側セットアップ
・マクロを使用可にしてください。(VBAマクロを使っています。)
・エクセル名「SQLログ取得.xls」は固定です。変更しないでください。
・「SQLConvert.exe」（SQL整形ツール）を使用しています。http://www.geocities.jp/rgyxd366/download.htm(ダウンロード)で「Setup_1_0_3_1.zip」インストーラー版をインストールしてください。
・SQLの日本語整形を行う場合『初期設定：”SQL日本語置換”を使用するため「TBL項目名たち」シート作成。』を行ってください。
注意）DB内で、英字項目名が同一のものは同一の意味（日本語）であるということを基本に作成しています。
たとえば社員テーブルにITEM009（”名前”）、顧客テーブルにITEM009（”資本金”）というように英字項目名が同じで違う意味を持つ場合
このやり方だとまったく意味の無い日本語SQLになります。そのときは『「TBL項目名たち」シートクリア（SQL日本語置換を使用しない場合）』ボタンでクリアしてください。
php側 （getsqllog.php）セットアップ。
・WEBサーバに当phpをおいてください。
・phpソースのソースをLOGを出す環境に合わせてください。
・11行目辺り。LOGの場所を書き換えてください。例）$logpath = '/var/log/postgres'; // postgreログ
$logpath = 'C:\xampp\htdocs\samples\phptail\logsample_honmono.log'; // postgreログ
・89行目辺り。ログの出力（postgresql.confのlog_line_prefix（ログフォーマット））に合わせてください。(log_line_prefix = '%t %c %p %d %h ' #推奨)
" if (preg_match(""/^([0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}) [^ ]+ [^ ]+ [^ ]+ "" . $dbname . "" [^ ]+ [^ ]+[ ]+statement:/i"", $linedata, $matches) ) { // DB名が指定のもののとき出力
"
・99行目辺り。ログの出力（postgresql.confのlog_line_prefix（ログフォーマット））に合わせてください。(log_line_prefix = '%t %c %p %d %h ' #推奨)
} else if (preg_match("/^([0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}) [^ ]+ [^ ]+ [^ ]+ [^ ]+ [^ ]+ [^ ]+[ ]+/", $linedata, $matches) ) { // DB名が指定以外の場合
その他
・動かないときはVBA、ｐｈｐともに自由に変更してください。
注意：本ソフトを使用してパソコン等に不具合・異常が発生したとしても
私共は、一切責任を負いかねます。
使用者が責任を持って使用してください。
// =============================================================
// 2009 kaz PHP自動作成お助けTOOL.(http://kazpgm.ddo.jp/)
// 修正BSDライセンス。
// =============================================================
・メニューが消えないときは「ツールー＞マクロー＞マクロ->メニューの削除」を動かしてください。
・VBAマクロphpを理解できる人がいないと使えないかな。
とおもうツールです。わかる人のみ使ってください。
2010/01/30 03:00-04:30
2010/02/01 24:00-24:30 修正あり。http://d.hatena.ne.jp/kazpgm/20100125/1264441494に追記した。
2010/02/02 02:30-02:35 まだやることがあったみたいで”修正中”って書いた。
2010/02/03 02:10-02:20 SQLログ取得時にSQL整形sしたのをやめて、人が整形したいSQLのみ選んでSQL整形するようにした。
2010/02/04 23:30-23:30 いちいちマクロー＞マクロー＞XXXは使いづらいのでメニューにした。”set DateStyle to 'ISO'”みたいなものを読み飛ばしできるようにした。まあ、仕事で使ったら使いづらかったので。・・・使う人がいろいろなアイディアでいいものにする。ってことで。
2010/02/05 22:00-22:40 (log_line_prefix = '%t %c %p %d %h ' #推奨)を追加した。
2010/02/07 22:00-22:10 「SQLConvert.exe」（SQL整形ツール）を常駐化した
2010-02-24 22:40-23:20 SQL的には変でも1行で見れるものがほしい。日本語SQL整形作成。エクセル画面のハードコピーを全部変更した。
2010/03/01 00:15-01：00「SQLConvert.exe」（SQL整形ツール）を常駐化をやめた
2010/06/22 01:00-01:30 vectorに載せかえ依頼した。内容は以下のよう。（ver201006220100）＜＝6/24OKがきた。
201006220100の修正内容.txt
１、『SQLログ取得.xls』のVBAに以下の修正を行いました。
入力した「テーブル項目の開始位置（項目の開始列を指定）」の行番号を使わずに
10固定にしていた。これを入力行番号を使うように修正した。
■Module7 修正
・Sub TBL項目名たち作成()
＜＜修正前＞＞
outIdx = outIdx + 1
j = 10
Do While Cells(j, 3) <> "" '項目名が入っている内
＜＜修正後＞＞
outIdx = outIdx + 1
j = テーブル項目の開始位置_row
Do While Cells(j, 3) <> "" '項目名が入っている内■クリップボードのSQL内容を日本語ITEMに変換するための修正追加。2015/04/25備忘録
SQL_Log_Getのvbaを使って
「rtnJpnSQL」関数などをちょっと修正、
「クリップボードのSQL内容を日本語ITEMに変換」関数を新規追加し
呼び出すことにより
クリップボードのSQL内容を日本語ITEMに変換する。
←初期設定：”SQL日本語置換”をするため「TBL項目名たち」シート作成はそのまま使う。
・項目名が同じで日本語名が違うものは日本語名（B列）の先頭に<<項目英語名の数>>を追記した。
・項目名、日本語名、属性が同じときテーブル名（D列）にテーブル名を,で並べた。
===================================
Sub TBL項目名たち作成()
・・・
Do While Cells(j, 3) <> "" '項目名が入っている内
＜＜修正前＞＞
If 重複チェック(LCase(Cells(j, 項目英語名_col)), Cells(j, 項目日本語名_col), LCase(Cells(j, 属性_col))) = 0 Then '項目英語名, 項目日本語名, 属性
＜＜修正後START＞＞
If 重複チェック(LCase(Cells(j, 項目英語名_col)), Cells(j, 項目日本語名_col), _
LCase(Cells(j, 属性_col)), LCase(Cells(j, テーブル英語名_col))) = 0 Then '項目英語名, 項目日本語名, 属性,テーブル英語名
＜＜修正後END＞＞
Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(outIdx, 1) = LCase(Cells(j, 項目英語名_col)) '項目英語名
Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(outIdx, 2) = Cells(j, 項目日本語名_col) '項目日本語名
Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(outIdx, 3) = LCase(Cells(j, 属性_col)) '属性
Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(outIdx, 4) = LCase(Cells(テーブル英語名_row, テーブル英語名_col)) 'テーブル名
outIdx = outIdx + 1
End If
j = j + 1
Loop
・・・
Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Activate
Cells.Select
＜＜修正前＞＞
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("D1") _
, Order2:=xlAscending, Key3:=Range("B1"), Order3:=xlAscending, Header _
＜＜修正後START＞＞
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
, Order2:=xlAscending, Key3:=Range("D1"), Order3:=xlAscending, Header _
＜＜修正後END＞＞
:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom _
, SortMethod:=xlPinYin, DataOption1:=xlSortNormal, DataOption2:= _
xlSortNormal, DataOption3:=xlSortNormal
＜＜追加START＞＞
Dim 項目英語名, 項目英語名の数, 項目日本語名, k
i = 1
Do While Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(i, 1) <> "" '項目英語名が入っている内
項目英語名 = Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(i, 1)
j = i + 1
If Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(j, 1) = "" Then '項目英語名が入っていない
Exit Do
Else
項目英語名の数 = 1
Do While Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(j, 1) <> "" '項目英語名が入っている内
If Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(j, 1) = 項目英語名 Then
項目英語名の数 = 項目英語名の数 + 1
j = j + 1
Else
If 項目英語名の数 > 1 Then '複数あった場合、日本語名の先頭に<<項目英語名の数>>を追記する
For k = i To j - 1
Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(k, 2) = _
"<<" & 項目英語名の数 & ">>" & Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(k, 2)
Next
End If
i = j
Exit Do
End If
Loop
End If
Loop
＜＜追加END＞＞
Range("F29").Select
Workbooks("SQLログ取得.xls").Sheets("環境設定").Activate
End Sub
'同じものがすでに登録されていたら行番号を戻す。無かったら0を戻す
＜＜修正前＞＞
Private Function 重複チェック(項目英語名, 項目日本語名, 属性)
＜＜修正後START＞＞
Private Function 重複チェック(項目英語名, 項目日本語名, 属性, テーブル英語名)
＜＜修正後END＞＞
Dim i
重複チェック = 0
i = 1
Do While Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(i, 1) <> "" '項目英語名が入っている内
If Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(i, 1) = 項目英語名 And _
Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(i, 2) = 項目日本語名 And _
Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(i, 3) = 属性 Then
重複チェック = i
＜＜追加START＞＞
Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(i, 4) = _
Workbooks("SQLログ取得.xls").Sheets("TBL項目名たち").Cells(i, 4) & "," & テーブル英語名
＜＜追加END＞＞
Exit Do
End If
i = i + 1
Loop
End Function
' 2010/02/24 追加 start 日本語SQL整形作成。直接修正した。
' 2015/04/25 全面修正した。
' SQLを日本語にする
Private Function rtnJpnSQL(arg_str)
Dim out_str, idx, 項目フラグ, str0
Dim str, str1, str2
out_str = ""
idx = 1
str = Mid(arg_str, 1, 1)
If Len(arg_str) > 1 Then
str2 = Mid(arg_str, idx, 2)
End If
If "A" <= UCase(str) And UCase(str) <= "Z" Then
' ITEMはidx + 1しない！
str1 = ""
項目フラグ = "ITEM"
ElseIf str = "'" Then
str1 = str1
項目フラグ = "LITERAL"
idx = idx + 1
ElseIf str2 = "--" Then
str1 = str2
項目フラグ = "COMMENT"
idx = idx + 2
ElseIf str2 = "/*" Then
str1 = str2
項目フラグ = "COMMENT2STA"
idx = idx + 2
ElseIf str2 = "*/" Then
str1 = str2
項目フラグ = "OTHER"
idx = idx + 2
Else
str1 = str
項目フラグ = "OTHER"
idx = idx + 1
End If
out_str = out_str & str1 '日本語名
Do While (idx < Len(arg_str) + 1)
If 項目フラグ = "ITEM" Then
' 注意：ITEMのときのidxはITEM開始の場所になっている。（そのほかは次の文字にidxされている）
str0 = ""
str1 = ""
Do While (idx < Len(arg_str) + 1)
str = Mid(arg_str, idx, 1)
str2 = Mid(arg_str, idx, 2)
If (0 <= str And str <= 9) Or ("A" <= UCase(str) And UCase(str) <= "Z") _
Or "_" = str Then
str0 = str0 & str
idx = idx + 1
ElseIf str = "'" Then
str1 = str
項目フラグ = "LITERAL"
idx = idx + 1
Exit Do
ElseIf str2 = "--" Then
str1 = str2
項目フラグ = "COMMENT"
idx = idx + 1
Exit Do
ElseIf str2 = "/*" Then
str1 = str2
項目フラグ = "COMMENT2STA"
idx = idx + 2
Exit Do
Else
str1 = str
項目フラグ = "OTHER"
idx = idx + 1
Exit Do
End If
Loop
out_str = out_str & tblItemJpn(str0) & str1 '日本語名
ElseIf 項目フラグ = "LITERAL" Then
' 注意：LITERALのときのidxは次の文字にidxされている
str0 = ""
Do While (idx < Len(arg_str) + 1)
str = Mid(arg_str, idx, 1)
str2 = Mid(arg_str, idx, 2)
If str2 = "''" Then ' リテラルの中
str0 = str0 & str
idx = idx + 1
ElseIf str = "'" Then ' リテラルの終了
str0 = str0 & str
項目フラグ = "OTHER"
idx = idx + 1
Exit Do
Else ' リテラルの中
str0 = str0 & str
idx = idx + 1
End If
Loop
out_str = out_str & str0 'リテラル
ElseIf 項目フラグ = "COMMENT" Then
out_str = out_str & Mid(arg_str, idx)
idx = Len(arg_str) + 1 ' 終了
ElseIf 項目フラグ = "COMMENT2STA" Then
str0 = ""
Do While (idx < Len(arg_str) + 1)
str = Mid(arg_str, idx, 1)
str2 = Mid(arg_str, idx, 2)
If str2 = "*/" Then ' /*コメント終了
str0 = str0 & str2
項目フラグ = "OTHER"
idx = idx + 2
Exit Do
Else ' /*コメントの中
str0 = str0 & str
idx = idx + 1
End If
Loop
out_str = out_str & str0 '/*コメントの中
Else ' OTHER
str0 = ""
Do While (idx < Len(arg_str) + 1)
str = Mid(arg_str, idx, 1)
str2 = Mid(arg_str, idx, 2)
If "A" <= UCase(str) And UCase(str) <= "Z" Then ' 項目発見
' ITEMはidx + 1しない！
項目フラグ = "ITEM"
Exit Do
ElseIf str = "'" Then
str0 = str0 & str
項目フラグ = "LITERAL"
idx = idx + 1
Exit Do
ElseIf str2 = "--" Then
str0 = str0 & str2
項目フラグ = "COMMENT"
idx = idx + 2
Exit Do
ElseIf str2 = "/*" Then
str0 = str0 & str2
項目フラグ = "COMMENT2STA"
idx = idx + 2
Exit Do
Else
str0 = str0 & str
項目フラグ = "OTHER"
idx = idx + 1
End If
Loop
out_str = out_str & str0 'リテラル
End If
Loop
rtnJpnSQL = out_str
End Function
Public Sub クリップボードのSQL内容を日本語ITEMに変換()
Dim rtn, str, str1, 文字配列, 全体行数, 行数
rtn = MsgBox("クリップボードにSQLを入れましたか？SQL内容を日本語ITEMに変換します。" & vbLf & "変換後はクリップボードが書き換わります。", vbYesNo)
If rtn = vbNo Then
Return
End If
' クリップボードからデータを取得する
str = ClipBoard_GetData
'1行ずつに分割
文字配列 = Split(str, vbNewLine)
'全体行数を取得
全体行数 = UBound(文字配列)
'1行目から順に、最後の行まで
For 行数 = 0 To 全体行数 - 1
str1 = str1 & rtnJpnSQL(文字配列(行数)) & vbNewLine
Next 行数
' クリップボードにデータを取得する
ClipBoard_SetData (str1)
End Sub
