개발노트: Autoit sqlite 편집

== autoit sqlite 예제 == <source lang=autoit> #include <Array.au3> #include <SQLite.au3> _SQLite_Startup() ; Load the DLL If @error Then Exit MsgBox(0, "Error", "Unable to start SQLite, Please verify your DLL") Local $sDatabase = @ScriptDir & '\SQLiteTestDatabase.db' Local $hDatabase = _SQLite_Open($sDatabase) ; Create the database file and get the handle for the database _SQLite_Exec($hDatabase, 'CREATE TABLE People (first_name, last_name);') ; CREATE a TABLE with the name "People" _SQLite_Exec($hDatabase, 'INSERT INTO People VALUES ("Timothy", "Lee");') ; INSERT "Timothy Lee" into the "People" TABLE _SQLite_Exec($hDatabase, 'INSERT INTO People VALUES ("John", "Doe");') ; INSERT "John Doe" into the "People" TABLE Local $aResult, $iRows, $iColumns ; $iRows and $iColuums are useless but they cannot be omitted from the function call so we declare them _SQLite_GetTable2d($hDatabase, 'SELECT * FROM People;', $aResult, $iRows, $iColumns) ; SELECT everything FROM "People" TABLE and get the $aResult _ArrayDisplay($aResult, "Results from the query") _SQLite_Close($hDatabase) _SQLite_Shutdown() </source> == sqlite이용 하여 그룹별로 저장하고 그룹별 탭을 클릭시 해당룹의 내용을 보여주는 예제 == <source lang=autoit> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <SQLite.au3> ; SQLite UDF 포함 (미리 설치 필요) #include <Array.au3> ; 데이터베이스 초기화 _SQLite_Startup() Local $db = _SQLite_Open(":memory:") ; 메모리 기반 데이터베이스 사용 ; 데이터베이스 테이블 생성 _SQLite_Exec($db, "CREATE TABLE data (key TEXT, value TEXT, group_name TEXT);") ; 예제 데이터 삽입 _SQLite_Exec($db, "INSERT INTO data VALUES ('Key1', 'Value1', 'Group1');") _SQLite_Exec($db, "INSERT INTO data VALUES ('Key2', 'Value2', 'Group1');") _SQLite_Exec($db, "INSERT INTO data VALUES ('Key3', 'Value3', 'Group2');") _SQLite_Exec($db, "INSERT INTO data VALUES ('Key4', 'Value4', 'Group2');") _SQLite_Exec($db, "INSERT INTO data VALUES ('Key5', 'Value5', 'Group3');") ; GUI 생성 Local $hGUI = GUICreate("Tab View with SQLite", 500, 400) ; Tab 컨트롤 생성 Local $hTab = GUICtrlCreateTab(10, 10, 480, 350) ; 그룹별 탭 생성 Local $aGroups _SQLite_GetTable2d($db, "SELECT DISTINCT group_name FROM data;", $aGroups) ; 각 그룹 탭에 데이터를 표시 For $i = 1 To UBound($aGroups) - 1 Local $sGroupName = $aGroups[$i][0] GUICtrlCreateTabItem($sGroupName) ; 그룹 내의 키-값 데이터 가져오기 Local $aData _SQLite_GetTable2d($db, "SELECT key, value FROM data WHERE group_name = '" & $sGroupName & "';", $aData) ; 각 그룹의 데이터를 Label로 추가 Local $nY = 20 For $j = 1 To UBound($aData) - 1 Local $sText = $aData[$j][0] & ": " & $aData[$j][1] GUICtrlCreateLabel($sText, 20, $nY, 440, 20) $nY += 25 Next Next ; 첫 번째 탭을 기본 탭으로 설정 GUICtrlCreateTabItem("") ; GUI 표시 GUISetState(@SW_SHOW) ; 메시지 루프 While 1 If GUIGetMsg() = $GUI_EVENT_CLOSE Then ExitLoop WEnd ; 종료 처리 GUIDelete() _SQLite_Close($db) _SQLite_Shutdown() Exit </source> == sqlite 입력 수정 삭제 예제== <source lang=autoit> #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <GuiListView.au3> #include <SQLite.au3> #include <Array.au3> ; SQLite 초기화 _SQLite_Startup() If @error Then MsgBox(16, "Error", "SQLite.dll을 로드할 수 없습니다.") Exit EndIf ; GUI 생성 Global $hGUI = GUICreate("SQLite ListView Demo", 600, 400) Global $hListView = _GUICtrlListView_Create($hGUI, "ID|Name|Age|City", 10, 10, 580, 300) Global $btnInsert = GUICtrlCreateButton("Insert", 10, 320, 80, 30) Global $btnUpdate = GUICtrlCreateButton("Update", 100, 320, 80, 30) Global $btnDelete = GUICtrlCreateButton("Delete", 190, 320, 80, 30) Global $inputName = GUICtrlCreateInput("", 10, 360, 120, 20) Global $inputAge = GUICtrlCreateInput("", 140, 360, 50, 20) Global $inputCity = GUICtrlCreateInput("", 200, 360, 120, 20) ; ListView 설정 _GUICtrlListView_SetColumnWidth($hListView, 0, 50) _GUICtrlListView_SetColumnWidth($hListView, 1, 200) _GUICtrlListView_SetColumnWidth($hListView, 2, 50) _GUICtrlListView_SetColumnWidth($hListView, 3, 200) ; 데이터베이스 생성/연결 Global $hDB = _SQLite_Open("test.db") If @error Then MsgBox(16, "Error", "데이터베이스를 열 수 없습니다.") Exit EndIf ; 테이블 생성 _SQLite_Exec($hDB, "CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INTEGER, city TEXT);") ; 초기 데이터 로드 LoadData() GUISetState(@SW_SHOW) ; 메인 루프 While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE ExitLoop Case $btnInsert InsertData() Case $btnUpdate UpdateData() Case $btnDelete DeleteData() EndSwitch WEnd ; 정리 _SQLite_Close($hDB) _SQLite_Shutdown() GUIDelete($hGUI) ; 함수들 Func LoadData() _GUICtrlListView_DeleteAllItems($hListView) Local $aResult, $iRows, $iCols _SQLite_GetTable2d($hDB, "SELECT * FROM people;", $aResult, $iRows, $iCols) If $iRows > 0 Then For $i = 1 To $iRows _GUICtrlListView_AddItem($hListView, $aResult[$i][0]) _GUICtrlListView_AddSubItem($hListView, $i-1, $aResult[$i][1], 1) _GUICtrlListView_AddSubItem($hListView, $i-1, $aResult[$i][2], 2) _GUICtrlListView_AddSubItem($hListView, $i-1, $aResult[$i][3], 3) Next EndIf EndFunc Func InsertData() Local $sName = GUICtrlRead($inputName) Local $iAge = GUICtrlRead($inputAge) Local $sCity = GUICtrlRead($inputCity) If $sName <> "" And $iAge <> "" And $sCity <> "" Then _SQLite_Exec($hDB, "INSERT INTO people (name, age, city) VALUES ('" & $sName & "', " & $iAge & ", '" & $sCity & "');") GUICtrlSetData($inputName, "") GUICtrlSetData($inputAge, "") GUICtrlSetData($inputCity, "") LoadData() Else MsgBox(48, "Warning", "모든 필드를 입력해주세요.") EndIf EndFunc Func UpdateData() Local $iSelected = _GUICtrlListView_GetSelectedIndices($hListView) If $iSelected <> "" Then Local $iID = _GUICtrlListView_GetItemText($hListView, $iSelected, 0) Local $sName = GUICtrlRead($inputName) Local $iAge = GUICtrlRead($inputAge) Local $sCity = GUICtrlRead($inputCity) If $sName <> "" And $iAge <> "" And $sCity <> "" Then _SQLite_Exec($hDB, "UPDATE people SET name = '" & $sName & "', age = " & $iAge & ", city = '" & $sCity & "' WHERE id = " & $iID & ";") GUICtrlSetData($inputName, "") GUICtrlSetData($inputAge, "") GUICtrlSetData($inputCity, "") LoadData() Else MsgBox(48, "Warning", "모든 필드를 입력해주세요.") EndIf Else MsgBox(48, "Warning", "수정할 항목을 선택해주세요.") EndIf EndFunc Func DeleteData() Local $iSelected = _GUICtrlListView_GetSelectedIndices($hListView) If $iSelected <> "" Then Local $iID = _GUICtrlListView_GetItemText($hListView, $iSelected, 0) _SQLite_Exec($hDB, "DELETE FROM people WHERE id = " & $iID & ";") LoadData() Else MsgBox(48, "Warning", "삭제할 항목을 선택해주세요.") EndIf EndFunc </source> [[category:autoit]]