SQLKey
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
1 SQLKey[편집]
1.1 _EditKeyList[편집]
- notpad.exe 실행
Func _EditKeyList()
_DebugPrint("notepad.exe " & $sKeyListFile)
Run("notepad.exe " & $sKeyListFile)
EndFunc1.2 _WinAPI_AddFontResourceEx[편집]
- 폰트 첨부 함수
#include <GUIConstantsEx.au3>
#include <GuiListView.au3>
#include <MsgBoxConstants.au3>
Example()
Func Example()
GUICreate("ListView Get/Set Selection Mark (v" & @AutoItVersion & ")", 400, 300)
Local $idListview = GUICtrlCreateListView("", 2, 2, 394, 268, BitOR($LVS_SHOWSELALWAYS, $LVS_REPORT))
GUISetState(@SW_SHOW)
; Add columns
_GUICtrlListView_AddColumn($idListview, "Column 0", 100)
; Add items
_GUICtrlListView_AddItem($idListview, "Item 0")
_GUICtrlListView_AddItem($idListview, "Item 1")
_GUICtrlListView_AddItem($idListview, "Item 2")
; Select item 1
_GUICtrlListView_SetSelectionMark($idListview, 1)
MsgBox($MB_SYSTEMMODAL, "Information", "Selected Mark: " & _GUICtrlListView_GetSelectionMark($idListview))
; Loop until the user exits.
Do
Until GUIGetMsg() = $GUI_EVENT_CLOSE
GUIDelete()
EndFunc ;==>Example1.3 Sqlite-Keylist[편집]
- include <Array.au3>
- include <SQLite.au3>
- include <SQLite.dll.au3>
- include <Extras\WM_NOTIFY.au3>
- include <GUIConstantsEx.au3>
- include <GuiImageList.au3>
- include <GuiListView.au3>
- include <GuiStatusBar.au3>
- include <WindowsConstants.au3>
- include <Extras\WM_NOTIFY.au3>
- include <GuiButton.au3>
- ~ _SQLite_Startup(@ScriptDir & "\sqlite3_x64.dll", False, 1)
_SQLite_Startup(@ScriptDir & "\sqlite3.dll", False, 1)
- ~ _SQLite_Startup() ; Load the DLL
If @error Then Exit MsgBox(0, "Error", "Unable to start SQLite, Please verify your DLL")
Global $iTimer,$g_idListView, $g_hStatusBar, $g_iIndex = -1 Global $sDatabase = @ScriptDir & '\SqlminusDBtest.db' Global $hDatabase = _SQLite_Open($sDatabase) ; Create the database file and get the handle for the database Global $aResult, $iRows, $iColumns ; $iRows and $iColuums are useless but they cannot be omitted from the function call so we declare them
$iRval = _SQLite_GetTable2d($hDatabase, 'SELECT KEY_NO, (SELECT GRP_NM FROM TB_GRP WHERE GRP_NO=GRP_NO) GRP_NM, KEY_NM, KEY_CMD ,KEY_DESC FROM TB_KEY;', $aResult, $iRows, $iColumns) ; SELECT everything FROM "People" TABLE and get the $aResult
- ~ _ArrayDisplay($aResult, "Results from the query")
KeyList($aResult)
Func KeyList($aResult)
Local $hGUI = GUICreate("ListView Hit Test (v" & @AutoItVersion & ")", 800, 500,-1,-1,BitOR($WS_MINIMIZEBOX, $WS_CAPTION, $WS_POPUP, $WS_SYSMENU, $WS_SIZEBOX))
$g_idListView = GUICtrlCreateListView("", 2, 30, 800, 446,-1,-1) $g_hStatusBar = _GUICtrlStatusBar_Create($hGUI, -1, "")
$g_hBtn_Add = _GUICtrlButton_Create($hGUI, "+", 700, 5, 20, 20)
- ~ GUICtrlSetFont($g_hBtn_Add, 16, 400, 0, "Courier New")
$g_hBtn_Del = _GUICtrlButton_Create($hGUI, "-", 730, 5, 20, 20)
- ~ GUICtrlSetFont($g_hBtn_Del, 16, 400, 0, "Courier New")
$iTimer = TimerInit()
$g_idListView = GUICtrlGetHandle($g_idListView) ; get the handle for use in the notify events
_GUICtrlListView_AddColumn($g_idListView,$aResult[0][0], 50) _GUICtrlListView_AddColumn($g_idListView,$aResult[0][1], 100) _GUICtrlListView_AddColumn($g_idListView,$aResult[0][2], 100) _GUICtrlListView_AddColumn($g_idListView,$aResult[0][3], 400) _GUICtrlListView_AddColumn($g_idListView,$aResult[0][4], 200)
_WM_NOTIFY_Register()
_ArrayDelete($aResult, 0) _GUICtrlListView_SetItemCount($g_idListView, UBound($aResult) - 1) _GUICtrlListView_AddArray($g_idListView,$aResult)
_DebugPrint("Information", "Load time: " & TimerDiff($iTimer) / 1000 & " seconds")
GUISetState(@SW_SHOW)
_GUICtrlListView_SetSelectedColumn($g_idListView, 0) _GUICtrlListView_SetSelectionMark($g_idListView, 0)
; Loop until user exits
Do
Until GUIGetMsg() = $GUI_EVENT_CLOSE
- ~ _AutoScrollEdit()
GUIDelete()
EndFunc
_SQLite_Close($hDatabase) _SQLite_Shutdown()
Func _SaveKey($key_no="")
Local $aHit,$aItem
$aHit = _GUICtrlListView_HitTest($g_idListView)
_DebugPrint($aHit[0] & " : " & $g_iIndex )
If ($aHit[0] <> -1) And ($aHit[0] <> $g_iIndex) Then
_GUICtrlStatusBar_SetText($g_hStatusBar, @TAB & StringFormat("HitTest Item: %d", $aHit[0]&":"&$aHit[1]))
$aItem = _GUICtrlListView_GetItem($g_idListView, $aHit[0],0)
- ~ _ArrayDisplay($aItem,"")
_DebugPrint("_Savekey....$aItem[3] : " & $aItem[3] )
- ~ $iRval = _SQLite_GetTable($hDatabase, 'SELECT KEY_NO, (SELECT GRP_NM FROM TB_GRP WHERE GRP_NO=GRP_NO) GRP_NM, KEY_NM, KEY_CMD ,KEY_DESC FROM TB_KEY WHERE KEY_NO='& $aItem[3] &';', $aResult, $iRows, $iColumns) ; SELECT everything FROM "People" TABLE and get the $aResult
- ~ _ArrayDisplay($aResult, "Results from the query")
- ~ $key_no = $aItem[3]
- ~ $iRval = _SQLite_GetTable($hDatabase, 'SELECT (SELECT GRP_NM FROM TB_GRP WHERE GRP_NO=GRP_NO) GRP_NM, KEY_NM, KEY_CMD ,KEY_DESC FROM TB_KEY WHERE KEY_NO=' & $key_no &';', $aResult, $iRows, $iColumns) ; SELECT everything FROM "People" TABLE and get the $aResult
- ~ _DebugPrint("$iRval
- " & $iRval[0])
- ~ _ArrayDisplay($iRval, "Query Result")
- ~ _SaveKey($aItem[3])
- ~ _GUICtrlListView_SetItem($g_idListView, "New Item 1", 1,2)
- ~ _GUICtrlListView_SetSelectedColumn($g_idListView, $aItem[3])
- ~ _DebugPrint( "Selected Column
- " & _GUICtrlListView_GetSelectedColumn($g_idListView))
- ~ $aItem = _GUICtrlListView_GetItem($idListview, $aHit[0])
- ~ MsgBox($MB_SYSTEMMODAL, "Information", "Selected Column
- " & _GUICtrlListView_GetSelectedColumn($g_idListView))
- ~ _GUICtrlStatusBar_SetText($g_hStatusBar, @TAB & StringFormat("HitTest Item
- %d", $aHit[0]&":"&$aHit[1]))
$g_iIndex = $aHit[0]
EndIf
EndFunc
Func _ListView_Click()
Local $aHit,$aItem
$aHit = _GUICtrlListView_HitTest($g_idListView)
_DebugPrint($aHit[0] & " : " & $g_iIndex )
If ($aHit[0] <> -1) And ($aHit[0] <> $g_iIndex) Then
_GUICtrlStatusBar_SetText($g_hStatusBar, @TAB & StringFormat("HitTest Item: %d", $aHit[0]&":"&$aHit[1]))
$aItem = _GUICtrlListView_GetItem($g_idListView, $aHit[0],0)
- ~ _ArrayDisplay($aItem,"")
_DebugPrint("$aItem[3] : " & $aItem[3] )
- ~ $iRval = _SQLite_GetTable($hDatabase, 'SELECT KEY_NO, (SELECT GRP_NM FROM TB_GRP WHERE GRP_NO=GRP_NO) GRP_NM, KEY_NM, KEY_CMD ,KEY_DESC FROM TB_KEY WHERE KEY_NO='& $aItem[3] &';', $aResult, $iRows, $iColumns) ; SELECT everything FROM "People" TABLE and get the $aResult
- ~ _ArrayDisplay($aResult, "Results from the query")
- ~ $key_no = $aItem[3]
- ~ $iRval = _SQLite_GetTable($hDatabase, 'SELECT (SELECT GRP_NM FROM TB_GRP WHERE GRP_NO=GRP_NO) GRP_NM, KEY_NM, KEY_CMD ,KEY_DESC FROM TB_KEY WHERE KEY_NO=' & $key_no &';', $aResult, $iRows, $iColumns) ; SELECT everything FROM "People" TABLE and get the $aResult
- ~ _DebugPrint("$iRval
- " & $iRval[0])
- ~ _ArrayDisplay($iRval, "Query Result")
- ~ _SaveKey($aItem[3])
- ~ _GUICtrlListView_SetItem($g_idListView, "New Item 1", 1,2)
- ~ _GUICtrlListView_SetSelectedColumn($g_idListView, $aItem[3])
- ~ _DebugPrint( "Selected Column
- " & _GUICtrlListView_GetSelectedColumn($g_idListView))
- ~ $aItem = _GUICtrlListView_GetItem($idListview, $aHit[0])
- ~ MsgBox($MB_SYSTEMMODAL, "Information", "Selected Column
- " & _GUICtrlListView_GetSelectedColumn($g_idListView))
- ~ _GUICtrlStatusBar_SetText($g_hStatusBar, @TAB & StringFormat("HitTest Item
- %d", $aHit[0]&":"&$aHit[1]))
$g_iIndex = $aHit[0]
EndIf
EndFunc ;==>_ListView_Click
Func WM_NOTIFY($hWnd, $iMsg, $wParam, $lParam)
#forceref $hWnd, $iMsg, $wParam
Local $hWndListView = $g_idListView
Local $hWndFrom, $iIDFrom, $iCode, $tNMHDR, $hWndListView2, $hWndListView3, $tInfo
If Not IsHWnd($g_idListView) Then $hWndListView = GUICtrlGetHandle($g_idListView)
Local $tNMHDR = DllStructCreate($tagNMHDR, $lParam)
Local $hWndFrom = HWnd(DllStructGetData($tNMHDR, "hWndFrom"))
local $iIDFrom = DllStructGetData($tNMHDR, "IDFrom")
Local $iCode = DllStructGetData($tNMHDR, "Code")
Local $aRET[3]
Switch $hWndFrom
Case $hWndListView
Switch $iCode
Case $LVN_COLUMNCLICK ; A column was clicked
_WM_NOTIFY_DebugEvent("$LVN_COLUMNCLICK", $tagNMLISTVIEW, $lParam, "IDFrom,,Item,SubItem,NewState,OldState,Changed,ActionX,ActionY,Param")
; No return value
_DebugPrint("$LVN_COLUMNCLICK" & @CRLF & "--> hWndFrom:" & @TAB & $hWndFrom & @CRLF & _ "-->IDFrom:" & @TAB & $iIDFrom & @CRLF & _ "-->Code:" & @TAB & $iCode & @CRLF & _ "-->Item:" & @TAB & DllStructGetData($tInfo, "Item") & @CRLF & _ "-->SubItem:" & @TAB & DllStructGetData($tInfo, "SubItem") & @CRLF & _ "-->NewState:" & @TAB & DllStructGetData($tInfo, "NewState") & @CRLF & _ "-->OldState:" & @TAB & DllStructGetData($tInfo, "OldState") & @CRLF & _ "-->Changed:" & @TAB & DllStructGetData($tInfo, "Changed") & @CRLF & _ "-->ActionX:" & @TAB & DllStructGetData($tInfo, "ActionX") & @CRLF & _ "-->ActionY:" & @TAB & DllStructGetData($tInfo, "ActionY") & @CRLF & _ "-->Param:" & @TAB & DllStructGetData($tInfo, "Param"))
Case $LVN_KEYDOWN ; A key has been pressed
_WM_NOTIFY_DebugEvent("$LVN_KEYDOWN", $tagNMLVKEYDOWN, $lParam, "IDFrom,,VKey,Flags")
; No return value
Case $NM_CLICK ; Sent by a list-view control when the user clicks an item with the left mouse button
- ~ $tInfo = DllStructCreate($tagNMITEMACTIVATE, $lParam)
_WM_NOTIFY_DebugEvent("$NM_CLICK", $tagNMITEMACTIVATE, $lParam, "IDFrom,,Index,SubItem,NewState,OldState,Changed,ActionX,ActionY,lParam,KeyFlags")
$aInfo = _GUICtrlListView_GetColumn($hWndListView, 0) MsgBox($MB_SYSTEMMODAL, "Information", "Column 0 Width: " & $aInfo[4])
_DebugPrint("$NM_CLICK" & @CRLF & "--> hWndFrom:" & @TAB & $hWndFrom & @CRLF & _ "-->IDFrom:" & @TAB & $iIDFrom & @CRLF & _ "-->Code:" & @TAB & $iCode & @CRLF & _ "-->Index:" & @TAB & DllStructGetData($tInfo, "Index") & @CRLF & _ "-->SubItem:" & @TAB & DllStructGetData($tInfo, "SubItem") & @CRLF & _ "-->NewState:" & @TAB & DllStructGetData($tInfo, "NewState") & @CRLF & _ "-->OldState:" & @TAB & DllStructGetData($tInfo, "OldState") & @CRLF & _ "-->Changed:" & @TAB & DllStructGetData($tInfo, "Changed") & @CRLF & _ "-->ActionX:" & @TAB & DllStructGetData($tInfo, "ActionX") & @CRLF & _ "-->ActionY:" & @TAB & DllStructGetData($tInfo, "ActionY") & @CRLF & _ "-->lParam:" & @TAB & DllStructGetData($tInfo, "lParam") & @CRLF & _ "-->KeyFlags:" & @TAB & DllStructGetData($tInfo, "KeyFlags"))
MsgBox($MB_SYSTEMMODAL, "Information", "Item Text: " & _GUICtrlListView_GetItemText($hWndListView,DllStructGetData($tInfo, "Item") ,2))
- ~ _ListView_Click()
; No return value
Case $NM_DBLCLK ; Sent by a list-view control when the user double-clicks an item with the left mouse button
_WM_NOTIFY_DebugEvent("$NM_DBLCLK", $tagNMITEMACTIVATE, $lParam, "IDFrom,,Index,SubItem,NewState,OldState,Changed,ActionX,ActionY,lParam,KeyFlags")
- ~ $aRET[0] = $tNMHDR.IDFrom
- ~ $aTMP = _GUICtrlListView_SubItemHitTest(GUICtrlGetHandle($aRET[0]))
- ~ $aRET[1] = $aTMP[0]
- ~ $aRET[2] = $aTMP[1]
_DebugPrint("$NM_DBLCLK" & @CRLF & "--> hWndFrom:" & @TAB & $hWndFrom & @CRLF & _ "-->IDFrom:" & @TAB & $iIDFrom & @CRLF & _ "-->Code:" & @TAB & $iCode & @CRLF & _ "-->Index:" & @TAB & DllStructGetData($tInfo, "Index") & @CRLF & _ "-->SubItem:" & @TAB & DllStructGetData($tInfo, "SubItem") & @CRLF & _ "-->NewState:" & @TAB & DllStructGetData($tInfo, "NewState") & @CRLF & _ "-->OldState:" & @TAB & DllStructGetData($tInfo, "OldState") & @CRLF & _ "-->Changed:" & @TAB & DllStructGetData($tInfo, "Changed") & @CRLF & _ "-->ActionX:" & @TAB & DllStructGetData($tInfo, "ActionX") & @CRLF & _ "-->ActionY:" & @TAB & DllStructGetData($tInfo, "ActionY") & @CRLF & _ "-->lParam:" & @TAB & DllStructGetData($tInfo, "lParam") & @CRLF & _ "-->KeyFlags:" & @TAB & DllStructGetData($tInfo, "KeyFlags"))
- ~ MsgBox($MB_SYSTEMMODAL, "Information", "Item Text
- " & _GUICtrlListView_GetItemText($hWndListView, DllStructGetData($tInfo, "Index"),DllStructGetData($tInfo, "SubItem")))
- ~ $aItem = _GUICtrlListView_GetItemTextArray($hWndListView, -1)
- ~ For $i = 1 To $aItem[0]
- ~ $sText &= StringFormat("Column[%2d] %s", $i, $aItem[$i]) & @CRLF
- ~ Next
- ~ MsgBox($MB_SYSTEMMODAL, "Information", "Item 2 (All Columns) Text
- " & @CRLF & @CRLF & $sText)
- ~ _SaveKey()
; No return value
Case $NM_KILLFOCUS ; The control has lost the input focus
_WM_NOTIFY_DebugEvent("$NM_KILLFOCUS", $tagNMHDR, $lParam, "hWndFrom,IDFrom")
; No return value
Case $NM_RCLICK ; Sent by a list-view control when the user clicks an item with the right mouse button
_WM_NOTIFY_DebugEvent("$NM_RCLICK", $tagNMITEMACTIVATE, $lParam, "IDFrom,,Index,SubItem,NewState,OldState,Changed,ActionX,ActionY,lParam,KeyFlags")
;Return 1 ; not to allow the default processing
Return 0 ; allow the default processing
- ~ Case $NM_RDBLCLK ; Sent by a list-view control when the user double-clicks an item with the right mouse button
- ~ _WM_NOTIFY_DebugEvent("$NM_RDBLCLK", $tagNMITEMACTIVATE, $lParam, "IDFrom,,Index,SubItem,NewState,OldState,Changed,ActionX,ActionY,lParam,KeyFlags")
- ~ ; No return value
Case $NM_RDBLCLK ; Sent by a list-view control when the user double-clicks an item with the right mouse button
- ~ $tInfo = DllStructCreate($tagNMITEMACTIVATE, $lParam)
_WM_NOTIFY_DebugEvent("$NM_RDBLCLK", $tagNMITEMACTIVATE, $lParam, "IDFrom,,Index,SubItem,NewState,OldState,Changed,ActionX,ActionY,lParam,KeyFlags")
_GUICtrlListView_DeleteItem($hWndListView, DllStructGetData($tInfo, "Index"))
- ~ _FileWriteToLine ( @ScriptDir&"\Log.txt" , DllStructGetData($tInfo, "Index") , "" , True , True )
- ~ _FileWriteToLine ( @ScriptDir&"\Data\Log02.txt" , DllStructGetData($tInfo, "Index") , "" , True , True )
- ~ _FileWriteToLine ( @ScriptDir&"\Data\Log002.txt" , DllStructGetData($tInfo, "Index") , "" , True , True )
- ~ _FileWriteToLine ( @ScriptDir&"\Log.txt" , DllStructGetData($tInfo, "Index") , "" , True , True )
- ~ _FileWriteToLine ( @ScriptDir&"\Data\Log02.txt" , DllStructGetData($tInfo, "Index") , "" , True , True )
- ~ _FileWriteToLine ( @ScriptDir&"\Data\Log002.txt" , DllStructGetData($tInfo, "Index") , "" , True , True )
- ~ _FileWriteToLine ( @ScriptDir&"\Log.txt" , DllStructGetData($tInfo, "Index") , "" , True , True )
- ~ _FileWriteToLine ( @ScriptDir&"\Data\Log02.txt" , DllStructGetData($tInfo, "Index") , "" , True , True )
- ~ _FileWriteToLine ( @ScriptDir&"\Data\Log002.txt" , DllStructGetData($tInfo, "Index") , "" , True , True )
_DebugPrint("$NM_RDBLCLK" & @CRLF & "--> hWndFrom:" & @TAB & $hWndFrom & @CRLF & _ "-->IDFrom:" & @TAB & $iIDFrom & @CRLF & _ "-->Code:" & @TAB & $iCode & @CRLF & _ "-->Index:" & @TAB & DllStructGetData($tInfo, "Index") & @CRLF & _ "-->SubItem:" & @TAB & DllStructGetData($tInfo, "SubItem") & @CRLF & _ "-->NewState:" & @TAB & DllStructGetData($tInfo, "NewState") & @CRLF & _ "-->OldState:" & @TAB & DllStructGetData($tInfo, "OldState") & @CRLF & _ "-->Changed:" & @TAB & DllStructGetData($tInfo, "Changed") & @CRLF & _ "-->ActionX:" & @TAB & DllStructGetData($tInfo, "ActionX") & @CRLF & _ "-->ActionY:" & @TAB & DllStructGetData($tInfo, "ActionY") & @CRLF & _ "-->lParam:" & @TAB & DllStructGetData($tInfo, "lParam") & @CRLF & _ "-->KeyFlags:" & @TAB & DllStructGetData($tInfo, "KeyFlags")) ;~ FileClose ($File2) ;~ FileClose ($File02) ;~ FileClose ($File002) ; No return value
$aInfo = _GUICtrlListView_GetColumn($hWndListView, DllStructGetData($tInfo, "Index") )
- ~ _ArrayDisplay($aInfo)
MsgBox($MB_SYSTEMMODAL, "Information", "Column 0 Width: " & $aInfo)
Case $NM_RETURN ; The control has the input focus and that the user has pressed the ENTER key
_WM_NOTIFY_DebugEvent("$NM_RETURN", $tagNMHDR, $lParam, "hWndFrom,IDFrom")
; No return value
Case $NM_SETFOCUS ; The control has received the input focus
_WM_NOTIFY_DebugEvent("$NM_SETFOCUS", $tagNMHDR, $lParam, "hWndFrom,IDFrom")
; No return value
EndSwitch
EndSwitch
Return $GUI_RUNDEFMSG
EndFunc ;==>WM_NOTIFY
Func _DebugPrint($s_text, $line = @ScriptLineNumber)
ConsoleWrite( _
"!===========================================================" & @LF & _
"+======================================================" & @LF & _
"-->Line(" & StringFormat("%04d", $line) & "):" & @TAB & $s_text & @LF & _
"+======================================================" & @LF)
EndFunc ;==>_DebugPrint
</source>