catalog program with sqlite3 database
- Posted by ron77 3 weeks ago
- 230 views
Hi all, this time I coded a catalog program with SQLite3.dll database (32-bit) and Win32API GUI in Euphoria. You can enter a record (name, category, and rank), view a selected record, or delete a selected record... catalog_program.exw
include std/dll.e include std/machine.e include std/convert.e include std/sequence.e include std/text.e include std/io.e -- Determine pointer size (4 bytes for 32-bit, 8 bytes for 64-bit) constant POINTER_SIZE = sizeof(C_POINTER) -- Win32 API constants and declarations atom user32, kernel32, sqlite3_dll user32 = open_dll("user32.dll") if user32 = 0 then puts(1, "Failed to load user32.dll\n") abort(1) end if kernel32 = open_dll("kernel32.dll") if kernel32 = 0 then puts(1, "Failed to load kernel32.dll\n") abort(1) end if sqlite3_dll = open_dll("sqlite3.dll") if sqlite3_dll = 0 then puts(1, "Failed to load sqlite3.dll - make sure it's in your PATH or current directory\n") abort(1) end if -- Window messages constant WM_COMMAND = #0111, WM_DESTROY = #0002, WM_SIZE = #0005, WM_CLOSE = #0010, WM_KEYDOWN = #0100 -- Window styles constant WS_OVERLAPPEDWINDOW = #00CF0000, WS_CHILD = #40000000, WS_VISIBLE = #10000000, WS_VSCROLL = #00200000, WS_BORDER = #00800000, WS_TABSTOP = #00010000, WS_CAPTION = #00C00000, WS_SYSMENU = #00080000, WS_EX_DLGMODALFRAME = #00000001 -- Window class styles constant CS_HREDRAW = #0002, CS_VREDRAW = #0001 -- Button styles constant LBS_NOTIFY = #0001 -- Message box types constant MB_ICONERROR = #10, MB_ICONINFORMATION = #40, MB_ICONQUESTION = #20, MB_YESNO = #4, IDYES = 6 -- Show window constants constant SW_SHOW = 5 -- List box messages constant LB_ADDSTRING = #0180, LB_RESETCONTENT = #0184, LB_GETCURSEL = #0188, LB_ERR = -1 -- Virtual key codes constant VK_RETURN = #0D, VK_ESCAPE = #1B -- Control IDs constant IDC_LISTBOX = 1001, IDC_ADD = 1002, IDC_VIEW = 1003, IDC_DELETE = 1004 -- SQLite constants constant SQLITE_OK = 0, SQLITE_ROW = 100 -- Database filename constant DB_FILENAME = "catalog.db" -- Win32 API function declarations with cdecl calling convention atom xRegisterClass, xCreateWindowEx, xShowWindow, xUpdateWindow, xGetMessage, xTranslateMessage, xDispatchMessage, xDefWindowProc, xPostQuitMessage, xLoadIcon, xLoadCursor, xGetModuleHandle, xMessageBox, xSendMessage, xGetDlgItem, xGetClientRect, xMoveWindow, xSetFocus, xGetWindowText, xDestroyWindow xRegisterClass = define_c_func(user32, "RegisterClassA", {C_POINTER}, C_UINT) xCreateWindowEx = define_c_func(user32, "CreateWindowExA", {C_UINT, C_POINTER, C_POINTER, C_UINT, C_INT, C_INT, C_INT, C_INT, C_POINTER, C_POINTER, C_POINTER, C_POINTER}, C_POINTER) xShowWindow = define_c_func(user32, "ShowWindow", {C_POINTER, C_INT}, C_INT) xUpdateWindow = define_c_func(user32, "UpdateWindow", {C_POINTER}, C_INT) xGetMessage = define_c_func(user32, "GetMessageA", {C_POINTER, C_POINTER, C_UINT, C_UINT}, C_INT) xTranslateMessage = define_c_func(user32, "TranslateMessage", {C_POINTER}, C_INT) xDispatchMessage = define_c_func(user32, "DispatchMessageA", {C_POINTER}, C_POINTER) xDefWindowProc = define_c_func(user32, "DefWindowProcA", {C_POINTER, C_UINT, C_POINTER, C_POINTER}, C_POINTER) xPostQuitMessage = define_c_proc(user32, "PostQuitMessage", {C_INT}) xLoadIcon = define_c_func(user32, "LoadIconA", {C_POINTER, C_POINTER}, C_POINTER) xLoadCursor = define_c_func(user32, "LoadCursorA", {C_POINTER, C_POINTER}, C_POINTER) xMessageBox = define_c_func(user32, "MessageBoxA", {C_POINTER, C_POINTER, C_POINTER, C_UINT}, C_INT) xSendMessage = define_c_func(user32, "SendMessageA", {C_POINTER, C_UINT, C_POINTER, C_POINTER}, C_POINTER) xGetDlgItem = define_c_func(user32, "GetDlgItem", {C_POINTER, C_INT}, C_POINTER) xGetClientRect = define_c_func(user32, "GetClientRect", {C_POINTER, C_POINTER}, C_INT) xMoveWindow = define_c_func(user32, "MoveWindow", {C_POINTER, C_INT, C_INT, C_INT, C_INT, C_INT}, C_INT) xSetFocus = define_c_func(user32, "SetFocus", {C_POINTER}, C_POINTER) xGetWindowText = define_c_func(user32, "GetWindowTextA", {C_POINTER, C_POINTER, C_INT}, C_INT) xDestroyWindow = define_c_func(user32, "DestroyWindow", {C_POINTER}, C_INT) xGetModuleHandle = define_c_func(kernel32, "GetModuleHandleA", {C_POINTER}, C_POINTER) -- SQLite function declarations with cdecl calling convention atom xsqlite3_open, xsqlite3_close, xsqlite3_prepare_v2, xsqlite3_step, xsqlite3_finalize, xsqlite3_bind_text, xsqlite3_bind_int, xsqlite3_column_text, xsqlite3_column_int xsqlite3_open = define_c_func(sqlite3_dll, "+sqlite3_open", {C_POINTER, C_POINTER}, C_INT) xsqlite3_close = define_c_func(sqlite3_dll, "+sqlite3_close", {C_POINTER}, C_INT) xsqlite3_prepare_v2 = define_c_func(sqlite3_dll, "+sqlite3_prepare_v2", {C_POINTER, C_POINTER, C_INT, C_POINTER, C_POINTER}, C_INT) xsqlite3_step = define_c_func(sqlite3_dll, "+sqlite3_step", {C_POINTER}, C_INT) xsqlite3_finalize = define_c_func(sqlite3_dll, "+sqlite3_finalize", {C_POINTER}, C_INT) xsqlite3_bind_text = define_c_func(sqlite3_dll, "+sqlite3_bind_text", {C_POINTER, C_INT, C_POINTER, C_INT, C_POINTER}, C_INT) xsqlite3_bind_int = define_c_func(sqlite3_dll, "+sqlite3_bind_int", {C_POINTER, C_INT, C_INT}, C_INT) xsqlite3_column_text = define_c_func(sqlite3_dll, "+sqlite3_column_text", {C_POINTER, C_INT}, C_POINTER) xsqlite3_column_int = define_c_func(sqlite3_dll, "+sqlite3_column_int", {C_POINTER, C_INT}, C_INT) -- Global variables atom hInstance, hWndMain, hListBox, hAddBtn, hViewBtn, hDeleteBtn, db -- Convert Euphoria string to C string function euphoria_to_c_string(sequence s) return allocate_string(s) end function -- Convert C string to Euphoria string function c_string_to_euphoria(atom ptr) if ptr = 0 then return "" end if return peek_string(ptr) end function -- Input box function function input_box(sequence prompt) atom hWndInput, hEdit, hLabel, hInstructionLabel atom done = 0, ok_pressed = 0 atom msg_ptr = allocate(32) -- MSG structure size sequence result = "" -- Create dialog window hWndInput = c_func(xCreateWindowEx, {WS_EX_DLGMODALFRAME, euphoria_to_c_string("STATIC"), euphoria_to_c_string("Input"), or_bits(WS_CAPTION, or_bits(WS_SYSMENU, WS_VISIBLE)), 250, 250, 450, 150, hWndMain, 0, hInstance, 0}) -- Create controls hLabel = c_func(xCreateWindowEx, {0, euphoria_to_c_string("STATIC"), euphoria_to_c_string(prompt), or_bits(WS_CHILD, WS_VISIBLE), 20, 20, 400, 25, hWndInput, 0, hInstance, 0}) hEdit = c_func(xCreateWindowEx, {0, euphoria_to_c_string("EDIT"), euphoria_to_c_string(""), or_bits(WS_CHILD, or_bits(WS_VISIBLE, or_bits(WS_BORDER, WS_TABSTOP))), 20, 50, 400, 30, hWndInput, 0, hInstance, 0}) hInstructionLabel = c_func(xCreateWindowEx, {0, euphoria_to_c_string("STATIC"), euphoria_to_c_string("Press ENTER to confirm or ESC to cancel"), or_bits(WS_CHILD, WS_VISIBLE), 20, 90, 400, 25, hWndInput, 0, hInstance, 0}) c_func(xSetFocus, {hEdit}) while done = 0 do if c_func(xGetMessage, {msg_ptr, 0, 0, 0}) then atom msg_hwnd = peek4u(msg_ptr) atom msg_message = peek4u(msg_ptr + 4) atom msg_wparam = peek4u(msg_ptr + 8) if msg_message = WM_CLOSE and msg_hwnd = hWndInput then done = 1 elsif msg_message = WM_KEYDOWN then if msg_wparam = VK_RETURN then atom buffer = allocate(256) c_func(xGetWindowText, {hEdit, buffer, 255}) result = c_string_to_euphoria(buffer) ok_pressed = 1 done = 1 elsif msg_wparam = VK_ESCAPE then done = 1 end if end if if done = 0 then c_func(xTranslateMessage, {msg_ptr}) c_func(xDispatchMessage, {msg_ptr}) end if else done = 1 end if end while c_func(xDestroyWindow, {hWndInput}) if ok_pressed = 1 then return result else return "" end if end function -- Window procedure function WndProc(atom hWnd, atom uMsg, atom wParam, atom lParam) atom loword_wparam if uMsg = WM_COMMAND then loword_wparam = and_bits(wParam, #FFFF) if loword_wparam = IDC_ADD then add_record_dialog() elsif loword_wparam = IDC_VIEW then view_record_dialog() elsif loword_wparam = IDC_DELETE then delete_record() end if elsif uMsg = WM_SIZE then resize_controls() elsif uMsg = WM_DESTROY then if db != 0 then c_func(xsqlite3_close, {db}) end if c_proc(xPostQuitMessage, {0}) end if return c_func(xDefWindowProc, {hWnd, uMsg, wParam, lParam}) end function -- Get window procedure callback ID constant WndProc_id = routine_id("WndProc") -- Initialize SQLite database procedure init_database() atom rc atom db_ptr = allocate(POINTER_SIZE) rc = c_func(xsqlite3_open, {euphoria_to_c_string(DB_FILENAME), db_ptr}) if POINTER_SIZE = 8 then db = peek8u(db_ptr) else db = peek4u(db_ptr) end if if rc != SQLITE_OK then c_func(xMessageBox, {0, euphoria_to_c_string("Failed to open database."), euphoria_to_c_string("Error"), MB_ICONERROR}) abort(1) end if atom sql_ptr = euphoria_to_c_string("CREATE TABLE IF NOT EXISTS catalog (id INTEGER PRIMARY KEY, name TEXT, category TEXT, rank INTEGER);") atom stmt_ptr = allocate(POINTER_SIZE) rc = c_func(xsqlite3_prepare_v2, {db, sql_ptr, -1, stmt_ptr, 0}) if rc = SQLITE_OK then atom stmt if POINTER_SIZE = 8 then stmt = peek8u(stmt_ptr) else stmt = peek4u(stmt_ptr) end if c_func(xsqlite3_step, {stmt}) c_func(xsqlite3_finalize, {stmt}) end if end procedure -- Load records into listbox procedure load_records() atom rc, stmt atom sql_ptr = euphoria_to_c_string("SELECT id, name, category, rank FROM catalog ORDER BY id;") atom stmt_ptr = allocate(POINTER_SIZE) atom hList = c_func(xGetDlgItem, {hWndMain, IDC_LISTBOX}) c_func(xSendMessage, {hList, LB_RESETCONTENT, 0, 0}) rc = c_func(xsqlite3_prepare_v2, {db, sql_ptr, -1, stmt_ptr, 0}) if rc = SQLITE_OK then if POINTER_SIZE = 8 then stmt = peek8u(stmt_ptr) else stmt = peek4u(stmt_ptr) end if while c_func(xsqlite3_step, {stmt}) = SQLITE_ROW do atom id = c_func(xsqlite3_column_int, {stmt, 0}) atom name_ptr = c_func(xsqlite3_column_text, {stmt, 1}) atom category_ptr = c_func(xsqlite3_column_text, {stmt, 2}) atom rank = c_func(xsqlite3_column_int, {stmt, 3}) sequence name_str = c_string_to_euphoria(name_ptr) sequence category_str = c_string_to_euphoria(category_ptr) sequence display = name_str & " | " & category_str & " | " & sprintf("%d", rank) c_func(xSendMessage, {hList, LB_ADDSTRING, 0, euphoria_to_c_string(display)}) end while c_func(xsqlite3_finalize, {stmt}) end if end procedure -- Add record dialog procedure add_record_dialog() sequence name_str = input_box("Enter name:") if length(name_str) = 0 then return end if sequence category_str = input_box("Enter category:") if length(category_str) = 0 then return end if sequence rank_str = input_box("Enter rank (integer):") if length(rank_str) = 0 then return end if atom rank = to_number(rank_str) atom sql_ptr = euphoria_to_c_string("INSERT INTO catalog (name, category, rank) VALUES (?, ?, ?);") atom stmt_ptr = allocate(POINTER_SIZE) atom rc = c_func(xsqlite3_prepare_v2, {db, sql_ptr, -1, stmt_ptr, 0}) if rc = SQLITE_OK then atom stmt if POINTER_SIZE = 8 then stmt = peek8u(stmt_ptr) else stmt = peek4u(stmt_ptr) end if c_func(xsqlite3_bind_text, {stmt, 1, euphoria_to_c_string(name_str), -1, 0}) c_func(xsqlite3_bind_text, {stmt, 2, euphoria_to_c_string(category_str), -1, 0}) c_func(xsqlite3_bind_int, {stmt, 3, rank}) c_func(xsqlite3_step, {stmt}) c_func(xsqlite3_finalize, {stmt}) load_records() else c_func(xMessageBox, {hWndMain, euphoria_to_c_string("Failed to add record."), euphoria_to_c_string("Error"), MB_ICONERROR}) end if end procedure -- View record dialog procedure view_record_dialog() atom hList = c_func(xGetDlgItem, {hWndMain, IDC_LISTBOX}) atom sel = c_func(xSendMessage, {hList, LB_GETCURSEL, 0, 0}) if sel = LB_ERR then c_func(xMessageBox, {hWndMain, euphoria_to_c_string("No record selected."), euphoria_to_c_string("Info"), MB_ICONINFORMATION}) return end if atom sql_ptr = euphoria_to_c_string("SELECT id, name, category, rank FROM catalog ORDER BY id;") atom stmt_ptr = allocate(POINTER_SIZE) atom rc = c_func(xsqlite3_prepare_v2, {db, sql_ptr, -1, stmt_ptr, 0}) atom idx = 0 sequence info = "" if rc = SQLITE_OK then atom stmt if POINTER_SIZE = 8 then stmt = peek8u(stmt_ptr) else stmt = peek4u(stmt_ptr) end if while c_func(xsqlite3_step, {stmt}) = SQLITE_ROW do if idx = sel then atom id = c_func(xsqlite3_column_int, {stmt, 0}) atom name_ptr = c_func(xsqlite3_column_text, {stmt, 1}) atom category_ptr = c_func(xsqlite3_column_text, {stmt, 2}) atom rank = c_func(xsqlite3_column_int, {stmt, 3}) sequence name_str = c_string_to_euphoria(name_ptr) sequence category_str = c_string_to_euphoria(category_ptr) info = sprintf("ID: %d\r\nName: %s\r\nCategory: %s\r\nRank: %d", {id, name_str, category_str, rank}) exit end if idx += 1 end while c_func(xsqlite3_finalize, {stmt}) end if if length(info) > 0 then c_func(xMessageBox, {hWndMain, euphoria_to_c_string(info), euphoria_to_c_string("Record Details"), MB_ICONINFORMATION}) end if end procedure -- Delete record procedure delete_record() atom hList = c_func(xGetDlgItem, {hWndMain, IDC_LISTBOX}) atom sel = c_func(xSendMessage, {hList, LB_GETCURSEL, 0, 0}) if sel = LB_ERR then c_func(xMessageBox, {hWndMain, euphoria_to_c_string("No record selected."), euphoria_to_c_string("Info"), MB_ICONINFORMATION}) return end if atom sql_ptr = euphoria_to_c_string("SELECT id FROM catalog ORDER BY id;") atom stmt_ptr = allocate(POINTER_SIZE) atom rc = c_func(xsqlite3_prepare_v2, {db, sql_ptr, -1, stmt_ptr, 0}) atom idx = 0 atom id = -1 atom stmt if rc = SQLITE_OK then if POINTER_SIZE = 8 then stmt = peek8u(stmt_ptr) else stmt = peek4u(stmt_ptr) end if while c_func(xsqlite3_step, {stmt}) = SQLITE_ROW do if idx = sel then id = c_func(xsqlite3_column_int, {stmt, 0}) exit end if idx += 1 end while c_func(xsqlite3_finalize, {stmt}) end if if id != -1 then if c_func(xMessageBox, {hWndMain, euphoria_to_c_string("Delete selected record?"), euphoria_to_c_string("Confirm"), or_bits(MB_YESNO, MB_ICONQUESTION)}) = IDYES then sql_ptr = euphoria_to_c_string("DELETE FROM catalog WHERE id = ?;") rc = c_func(xsqlite3_prepare_v2, {db, sql_ptr, -1, stmt_ptr, 0}) if rc = SQLITE_OK then if POINTER_SIZE = 8 then stmt = peek8u(stmt_ptr) else stmt = peek4u(stmt_ptr) end if c_func(xsqlite3_bind_int, {stmt, 1, id}) c_func(xsqlite3_step, {stmt}) c_func(xsqlite3_finalize, {stmt}) load_records() end if end if end if end procedure -- Resize controls when window is resized procedure resize_controls() atom rect_ptr = allocate(16) -- RECT structure c_func(xGetClientRect, {hWndMain, rect_ptr}) atom client_width = peek4s(rect_ptr + 8) atom client_height = peek4s(rect_ptr + 12) atom button_width = 150 atom button_height = 40 atom button_spacing = 60 atom margin = 20 atom listbox_width = client_width - button_width - (margin * 3) atom listbox_height = client_height - (margin * 2) c_func(xMoveWindow, {hListBox, margin, margin, listbox_width, listbox_height, 1}) c_func(xMoveWindow, {hAddBtn, listbox_width + (margin * 2), margin, button_width, button_height, 1}) c_func(xMoveWindow, {hViewBtn, listbox_width + (margin * 2), margin + button_spacing, button_width, button_height, 1}) c_func(xMoveWindow, {hDeleteBtn, listbox_width + (margin * 2), margin + (button_spacing * 2), button_width, button_height, 1}) end procedure -- Main entry point procedure WinMain() atom wc_ptr, msg_ptr hInstance = c_func(xGetModuleHandle, {0}) -- Set up window class wc_ptr = allocate(40) -- WNDCLASS structure size poke4(wc_ptr, or_bits(CS_HREDRAW, CS_VREDRAW)) -- style poke4(wc_ptr + 4, call_back(WndProc_id)) -- lpfnWndProc poke4(wc_ptr + 8, 0) -- cbClsExtra poke4(wc_ptr + 12, 0) -- cbWndExtra poke4(wc_ptr + 16, hInstance) -- hInstance poke4(wc_ptr + 20, c_func(xLoadIcon, {0, 32512})) -- hIcon (IDI_APPLICATION) poke4(wc_ptr + 24, c_func(xLoadCursor, {0, 32512})) -- hCursor (IDC_ARROW) poke4(wc_ptr + 28, 16) -- hbrBackground (COLOR_BTNFACE + 1) poke4(wc_ptr + 32, 0) -- lpszMenuName poke4(wc_ptr + 36, euphoria_to_c_string("CatalogApp")) -- lpszClassName c_func(xRegisterClass, {wc_ptr}) hWndMain = c_func(xCreateWindowEx, {0, euphoria_to_c_string("CatalogApp"), euphoria_to_c_string("Catalog Program"), WS_OVERLAPPEDWINDOW, 150, 150, 700, 500, 0, 0, hInstance, 0}) -- Create controls hListBox = c_func(xCreateWindowEx, {0, euphoria_to_c_string("LISTBOX"), 0, or_bits(WS_CHILD, or_bits(WS_VISIBLE, or_bits(LBS_NOTIFY, WS_VSCROLL))), 20, 20, 450, 350, hWndMain, IDC_LISTBOX, hInstance, 0}) hAddBtn = c_func(xCreateWindowEx, {0, euphoria_to_c_string("BUTTON"), euphoria_to_c_string("Add"), or_bits(WS_CHILD, WS_VISIBLE), 500, 20, 150, 40, hWndMain, IDC_ADD, hInstance, 0}) hViewBtn = c_func(xCreateWindowEx, {0, euphoria_to_c_string("BUTTON"), euphoria_to_c_string("View"), or_bits(WS_CHILD, WS_VISIBLE), 500, 80, 150, 40, hWndMain, IDC_VIEW, hInstance, 0}) hDeleteBtn = c_func(xCreateWindowEx, {0, euphoria_to_c_string("BUTTON"), euphoria_to_c_string("Delete"), or_bits(WS_CHILD, WS_VISIBLE), 500, 140, 150, 40, hWndMain, IDC_DELETE, hInstance, 0}) c_func(xShowWindow, {hWndMain, SW_SHOW}) c_func(xUpdateWindow, {hWndMain}) -- Initialize database and load records init_database() load_records() -- Message loop msg_ptr = allocate(32) -- MSG structure size while c_func(xGetMessage, {msg_ptr, 0, 0, 0}) do c_func(xTranslateMessage, {msg_ptr}) c_func(xDispatchMessage, {msg_ptr}) end while end procedure -- Start the program WinMain()
