Tiny Database Browser

uwekel · Post by **uwekel** » Fri Mar 06, 2015 6:00 pm

Hi,

here ist a minimalistic database browser i have written for myself. It can connect to SQLITE- or PostgreSQL-Databases, or via ODBC to any other database. The ODBC connection must have been setup in the Windows ODBC connection manager.

If you set it up as a tool in the PureBasic IDE, you can use the %PROJECT argument to save the preferences separatly for each of your projects, so if you run the tool, the database used by your project will automatically be loaded. Also the font settings from the PB IDE editor is used then.

You can run all supported SQL commands. Query results will be displayed in a list icon gadget. The column widths will be auto-sized so everything is readable.

It should properly run on Linux and Windows (tested). Ideas and improvements are welcome.

Code: Select all

;DataBrowser for databases supported by PureBasic
;Written by Uwe Keller, 6.3.2015

Runtime Enumeration Window
  #MainWindow
  #ConnectWindow
EndEnumeration
Runtime Enumeration Gadget
  #Splitter
  #SQL
  #List
  #ConnectName
  #ConnectMore
  #ConnectUser
  #ConnectPass
  #ConnectODBC
  #ConnectSQLite
  #ConnectPostgreSQL
  #ConnectCancel
  #ConnectOk
EndEnumeration
Enumeration Toolbar
  #MenuConnect
  #MenuExecute
  #MenuUndo
  #MenuRedo
  #MenuHelp
  #KeyTab
EndEnumeration
Enumeration Statusbar
  #StateName
  #StateSize
  #StateRecords
  #StateTime
EndEnumeration
Enumeration Font
  #SqlFont
  #ListFont
EndEnumeration
Enumeration Dialog
  #ConnectDialog
EndEnumeration
Enumeration Xml
  #ConnectXml
EndEnumeration

#MaxSqlHistoryCount = 25

Global CharWidth
Global NewList SQL.s()

Procedure Connect()
  ;open database
  name.s = ReadPreferenceString("Name", "")
  If name
    user.s = ReadPreferenceString("User", "")
    pass.s = ReadPreferenceString("Pass", "")
    plugin = ReadPreferenceInteger("Plugin", #PB_Database_ODBC)
    ;open database
    If Not OpenDatabase(0, name, user, pass, plugin)
      MessageRequester("DataBrowser", DatabaseError())
    ElseIf plugin = #PB_Database_SQLite
      StatusBarText(0, #StateName, GetFilePart(name))
      StatusBarText(0, #StateSize, Str(FileSize(name) / 1000) + " KB")
    Else
      StatusBarText(0, #StateName, name)
      StatusBarText(0, #StateSize, "")
    EndIf
  EndIf
EndProcedure
Procedure ConnectState()
  ;set connect dialoggadget state
  noname = Bool(Trim(GetGadgetText(#ConnectName)) = "")
  nosqlite = Bool(GetGadgetState(#ConnectSQLite) = 0)
  DisableGadget(#ConnectOk, noname)
  DisableGadget(#ConnectMore, nosqlite)
EndProcedure
Procedure ConnectDialog()
  If CreateDialog(#ConnectDialog) And OpenXMLDialog(#ConnectDialog, #ConnectXml, "ConnectDialog", 0, 0, 0, 0, WindowID(#MainWindow))
    ;fill settings
    SetGadgetText(#ConnectName, ReadPreferenceString("Name", ""))
    SetGadgetText(#ConnectUser, ReadPreferenceString("User", ""))
    SetGadgetText(#ConnectPass, ReadPreferenceString("Pass", ""))
    Select ReadPreferenceInteger("Plugin", #PB_Database_ODBC)
    Case #PB_Database_ODBC
      SetGadgetState(#ConnectODBC, #True)
    Case #PB_Database_SQLite
      SetGadgetState(#ConnectSQLite, #True)
    Case #PB_Database_PostgreSQL
      SetGadgetState(#ConnectPostgreSQL, #True)
    EndSelect
    ;shortcuts
    AddKeyboardShortcut(#ConnectWindow, #PB_Shortcut_Escape, 0)
    AddKeyboardShortcut(#ConnectWindow, #PB_Shortcut_Return, 1)
    ;run dialog
    Repeat
      ConnectState()
      Select WaitWindowEvent()
      Case #PB_Event_Menu
        Select EventMenu()
        Case 0
          Break
        Case 1
          PostEvent(#PB_Event_Gadget, #ConnectWindow, #ConnectOk, #PB_EventType_LeftClick)
        EndSelect
      Case #PB_Event_CloseWindow
        Break
      Case #PB_Event_Gadget
        Select EventGadget()
        Case #ConnectMore
          f.s = GetGadgetText(#ConnectName)
          f = OpenFileRequester("Connect or create Database", f, "All Files|*.*", 0)
          If f
            SetGadgetText(#ConnectName, f)
            ;for sqlite create new database if file does not exist
            If FileSize(f) = -1 And CreateFile(0, f)
              CloseFile(0)
            EndIf
          EndIf
        Case #ConnectCancel
          Break
        Case #ConnectOk
          ;store changes
          WritePreferenceString("Name", GetGadgetText(#ConnectName))
          WritePreferenceString("User", GetGadgetText(#ConnectUser))
          WritePreferenceString("Pass", GetGadgetText(#ConnectPass))
          If GetGadgetState(#ConnectODBC)
            WritePreferenceInteger("Plugin", #PB_Database_ODBC)
          ElseIf GetGadgetState(#ConnectSQLite)
            WritePreferenceInteger("Plugin", #PB_Database_SQLite)
          Else
            WritePreferenceInteger("Plugin", #PB_Database_PostgreSQL)
          EndIf
          Connect()
          Break
        EndSelect
      EndSelect
    ForEver
    CloseWindow(#ConnectWindow)
  Else
    Debug DialogError(#ConnectDialog)
  EndIf
EndProcedure
Procedure Query(SQL.s)
  ;measure time
  e = ElapsedMilliseconds()
  ;query data
  If DatabaseQuery(0, sql)
    ;remove rows
    ClearGadgetItems(#List)
    ;remove columns but not the first one
    While GetGadgetItemText(#List, -1, 1)
      RemoveGadgetColumn(#List, 1)
    Wend
    ;count maximum characters per column
    Dim w.l(DatabaseColumns(0))
    ;add columns
    For i = 0 To DatabaseColumns(0) - 1
      s.s = DatabaseColumnName(0, i)
      If i = 0
        SetGadgetItemText(#List, -1, s, 0)
      Else
        AddGadgetColumn(#List, i, s, 100)
      EndIf
      ;keep maximum chars per columns (for column auto-sizing)
      l.l = Len(s)
      If w(i) < l
        w(i) = l
      EndIf
    Next
    ;add rows
    While NextDatabaseRow(0)
      For i = 0 To DatabaseColumns(0) - 1
        s = GetDatabaseString(0, i)
        ;replace unwanted characters
        ReplaceString(s, #TAB$, " ", #PB_String_InPlace)
        ReplaceString(s, #LF$, " ", #PB_String_InPlace)
        ReplaceString(s, #CR$, " ", #PB_String_InPlace)
        If i = 0
          text.s = s
        Else
          text + #LF$ + s
        EndIf
        ;keep maximum chars per columns (for column auto-sizing)
        l.l = Len(s)
        If w(i) < l
          w(i) = l
        EndIf
      Next
      ;add row
      AddGadgetItem(#List, -1, text)
    Wend
    FinishDatabaseQuery(0)
    ;auto-size columns
    For i = 0 To ArraySize(w())
      w = w(i) * CharWidth + 16 ;(some padding required)
      SetGadgetItemAttribute(#List, -1, #PB_ListIcon_ColumnWidth, w, i)
    Next
    ;show row count and execution time
    StatusBarText(0, #StateRecords, Str(CountGadgetItems(#List)) + " rows")
    StatusBarText(0, #StateTime, Str(ElapsedMilliseconds() - e) + "ms")
  Else
    MessageRequester("DataBrowser", DatabaseError())
  EndIf
EndProcedure
Procedure Update(SQL.s)
  ;measure time
  e = ElapsedMilliseconds()
  ;update database and give error message if failed
  If DatabaseUpdate(0, SQL)
    ;show execution time
    StatusBarText(0, #StateTime, Str(ElapsedMilliseconds() - e) + "ms")
  Else
    MessageRequester("DataBrowser", DatabaseError())
  EndIf
EndProcedure
Procedure Execute()
  ;append to history
  sql.s = Trim(GetGadgetText(#SQL))
  ;remove it elsewhere from the history
  ForEach Sql()
    If Sql() = sql
      DeleteElement(Sql())
      LastElement(Sql())
      Break
    EndIf
  Next
  ;append to history
  AddElement(Sql())
  Sql() = sql
  ;remove too much items
  If ListSize(Sql()) > #MaxSqlHistoryCount
    FirstElement(Sql())
    DeleteElement(Sql())
    LastElement(Sql())
  EndIf
  ;query or update
  If UCase(StringField(sql, 1, " ")) = "SELECT"
    Query(sql)
  Else
    Update(sql)
  EndIf
EndProcedure
Procedure Undo()
  If PreviousElement(Sql())
    SetGadgetText(#Sql, Sql())
  EndIf
EndProcedure
Procedure Redo()
  If NextElement(Sql())
    SetGadgetText(#Sql, Sql())
  EndIf
EndProcedure
Procedure Help()
  ;open the appropriate database documentation
  Select ReadPreferenceInteger("Plugin", 0)
  Case #PB_Database_ODBC
    url.s = "http://en.wikipedia.org/wiki/SQL"
  Case #PB_Database_SQLite
    url.s = "http://sqlite.org/docs.html"
  Case #PB_Database_PostgreSQL
    url = "http://www.postgresql.org/docs/"
  EndSelect
  ;show link in browser
  CompilerIf #PB_Compiler_OS = #PB_OS_Linux
    RunProgram("xdg-open", url, "")
  CompilerElse
    RunProgram(url)
  CompilerEndIf
EndProcedure
Procedure Resize()
  #Pad = 4
  x = #Pad
  CompilerIf #PB_Compiler_OS = #PB_OS_Windows
    y = ToolBarHeight(0)
    h = #Pad
  CompilerElse
    y = #Pad
  CompilerEndIf
  w = WindowWidth(#MainWindow) - #Pad - #Pad
  h + WindowHeight(#MainWindow) - #Pad - #Pad - ToolBarHeight(0) - StatusBarHeight(0)
  ResizeGadget(#Splitter, x, y, w, h)
EndProcedure
Procedure State()
  ;get database state
  nodb = Bool(Not IsDatabase(0))
  nohistory = Bool(ListSize(Sql()) = 0)
  isfirst = Bool(ListIndex(Sql()) = 0)
  islast = Bool(ListIndex(Sql()) = ListSize(Sql()) - 1)
  ;toggle buttons
  DisableToolBarButton(0, #MenuExecute, nodb)
  DisableToolBarButton(0, #MenuUndo, nodb | nohistory | isfirst)
  DisableToolBarButton(0, #MenuRedo, nodb | nohistory | islast)
  DisableToolBarButton(0, #MenuHelp, nodb)
  ;toggle gadgets
  DisableGadget(#SQL, nodb)
  DisableGadget(#List, nodb)
EndProcedure
Procedure ToolButton(Id, Icon, Tip.s)
  ToolBarStandardButton(Id, Icon)
  ToolBarToolTip(0, Id, Tip)
EndProcedure
Procedure Loop()
  Repeat
    Select WaitWindowEvent()
    Case #PB_Event_Menu
      Select EventMenu()
      Case #MenuConnect
        ConnectDialog()
      Case #MenuExecute
        Execute()
      Case #MenuUndo
        Undo()
      Case #MenuRedo
        Redo()
      Case #MenuHelp
        Help()
      Case #KeyTab
        If GetActiveGadget() = #SQL
          SetActiveGadget(#List)
        Else
          SetActiveGadget(#SQL)
        EndIf
      EndSelect
      State()
    Case #PB_Event_SizeWindow
      Resize()
    Case #PB_Event_CloseWindow
      Break
    Case #PB_Event_Gadget
      Select EventGadget()
      Case #SQL
        Select EventType()
        Case #PB_EventType_Focus
          AddKeyboardShortcut(#MainWindow, #PB_Shortcut_PageUp, #MenuUndo)
          AddKeyboardShortcut(#MainWindow, #PB_Shortcut_PageDown, #MenuRedo)
        Case #PB_EventType_LostFocus
          RemoveKeyboardShortcut(#MainWindow, #PB_Shortcut_PageUp)
          RemoveKeyboardShortcut(#MainWindow, #PB_Shortcut_PageDown)
        EndSelect
      EndSelect
      State()
    EndSelect
  ForEver
EndProcedure
Procedure Main()
  ;define default fonts
  CompilerIf #PB_Compiler_OS = #PB_OS_Linux
    fontname.s = "Monospace"
    fontsize.l = 11
  CompilerElse
    fontname.s = "Consolas"
    fontsize.l = 10
  CompilerEndIf
  ;get fonts from PB IDE settings, if available
  If OpenPreferences(GetEnvironmentVariable("PB_TOOL_Preferences"))
    PreferenceGroup("Editor")
    fontname = ReadPreferenceString("EditorFontName", fontname)
    fontsize = ReadPreferenceLong("EditorFontSize", fontsize)
    ClosePreferences()
  EndIf
  ;create SQL editor and list fonts
  LoadFont(#SqlFont, fontname, fontsize)
  LoadFont(#ListFont, fontname, fontsize - 1)
  ;open settings
  path.s = GetHomeDirectory()
  CompilerIf #PB_Compiler_OS = #PB_OS_Linux
    path + ".config/"
  CompilerElse
    path + "AppData\Roaming\"
  CompilerEndIf
  OpenPreferences(path + "databrowser.conf")
  ;if project name handed over, store settings project related
  If CountProgramParameters() = 1
    PreferenceGroup(ProgramParameter())
  EndIf
  ;open main window
  OpenWindow(#MainWindow,
             ReadPreferenceInteger("MainX", 0),
             ReadPreferenceInteger("MainY", 0),
             ReadPreferenceInteger("MainW", 640),
             ReadPreferenceInteger("MainH", 480),
             "DataBrowser",
             #PB_Window_SizeGadget | #PB_Window_MinimizeGadget | #PB_Window_MaximizeGadget)
  ;tool bar
  CreateToolBar(0, WindowID(#MainWindow))
  ToolButton(#MenuConnect, #PB_ToolBarIcon_Open, "Connect to a database [Ctrl+O]")
  ToolButton(#MenuExecute, #PB_ToolBarIcon_Find, "Perform SQL command, query or update [F5]")
  ToolBarSeparator()
  ToolButton(#MenuUndo, #PB_ToolBarIcon_Undo, "Go to previous SQL command [PageUp]")
  ToolButton(#MenuRedo, #PB_ToolBarIcon_Redo, "Go to next SQL command [PageDown]")
  ToolBarSeparator()
  ToolButton(#MenuHelp, #PB_ToolBarIcon_Help, "Get help for the current database [F1]")
  ;status bar
  CreateStatusBar(0, WindowID(#MainWindow))
  For i = 1 To 4
    AddStatusBarField(#PB_Ignore)
  Next
  ;shortcuts
  AddKeyboardShortcut(#MainWindow, #PB_Shortcut_Control | #PB_Shortcut_O, #MenuConnect)
  AddKeyboardShortcut(#MainWindow, #PB_Shortcut_F5, #MenuExecute)
  AddKeyboardShortcut(#MainWindow, #PB_Shortcut_F1, #MenuHelp)
  AddKeyboardShortcut(#MainWindow, #PB_Shortcut_Tab, #KeyTab)
  ;get character width of list font (we measure only once for speed)
  CreateImage(0, 1, 1)
  StartDrawing(ImageOutput(0))
  DrawingFont(FontID(#ListFont))
  CharWidth = TextWidth("X")
  StopDrawing()
  FreeImage(0)
  ;gadgets
  EditorGadget(#SQL, 0, 0, 0, 0, #PB_Editor_WordWrap)
  SetGadgetFont(#SQL, FontID(#SqlFont))
  ListIconGadget(#List, 0, 0, 0, 0, "", 100, #PB_ListIcon_FullRowSelect)
  SetGadgetFont(#List, FontID(#ListFont))
  SplitterGadget(#Splitter, 0, 0, 640, 480, #SQL, #List, #PB_Splitter_FirstFixed)
  SetGadgetState(#Splitter, ReadPreferenceInteger("Splitter", 100))
  Resize()
  ;read SQL history and decode line feeds
  If ExaminePreferenceKeys()
    While NextPreferenceKey()
      If Left(PreferenceKeyName(), 3) = "SQL"
        AddElement(Sql())
        Sql() = PreferenceKeyValue()
        ReplaceString(Sql(), Chr(1), #LF$, #PB_String_InPlace)
        ReplaceString(Sql(), Chr(2), #CR$, #PB_String_InPlace)
      EndIf
    Wend
  EndIf
  If LastElement(Sql())
    SetGadgetText(#SQL, Sql())
  EndIf
  SetActiveGadget(#SQL)
  ;load GUI
  If Not CatchXML(#ConnectXml, ?ConnectDialog, ?EndConnectDialog - ?ConnectDialog) Or Not XMLStatus(#ConnectXml) = #PB_XML_Success
    RaiseError(- 1)
  EndIf
  ;init database libs
  UseODBCDatabase()
  UseSQLiteDatabase()
  UsePostgreSQLDatabase()  
  ;restore most recent connection
  Connect()
  ;event loop
  Loop()
  ;save settings with encoded line feeds
  ForEach Sql()
    ReplaceString(Sql(), #LF$, Chr(1), #PB_String_InPlace)
    ReplaceString(Sql(), #CR$, Chr(2), #PB_String_InPlace)
    WritePreferenceString("SQL" + Str(ListIndex(Sql()) + 1), Sql())
  Next
  WritePreferenceInteger("Splitter", GetGadgetState(#Splitter))
  WritePreferenceInteger("MainX", WindowX(#MainWindow))
  WritePreferenceInteger("MainY", WindowY(#MainWindow))
  WritePreferenceInteger("MainW", WindowWidth(#MainWindow))
  WritePreferenceInteger("MainH", WindowHeight(#MainWindow))
EndProcedure

Main()

DataSection
  ConnectDialog:
  IncludeBinary "DataBrowser.xml"
  EndConnectDialog:
EndDataSection

You also need the Dialog definition. Save it as "DataBrowser.xml" in the same directory.

Code: Select all

<?xml version='1.0' encoding='UTF-8'?>
<dialogs>
    <window id='#ConnectWindow' name='ConnectDialog' text='Connect database' flags='#PB_Window_WindowCentered'>
        <vbox>
            <gridbox columns='2' colexpand='item:2' rowexpand='equal'>
                <text text='Plugin:'/>
                <hbox expand='item:3'>
                    <option id='#ConnectODBC' text='ODBC'/>
                    <option id='#ConnectSQLite' text='SQLite'/>
                    <option id='#ConnectPostgreSQL' text='PostgreSQL'/>
                    <empty/>
                </hbox>
                <text text='Name:'/>
                <hbox expand='item:1'>
                    <string id='#ConnectName'/>
                    <button id='#ConnectMore' text='...'/>
                </hbox>
                <text text='Username:'/>
                <string id='#ConnectUser' width='300'/>
                <text text='Password:'/>
                <string id='#ConnectPass' width='300' flags='#PB_String_Password'/>
            </gridbox>
            <hbox expand='item:1'>
                <empty/>
                <hbox expand='equal'>
                    <button id='#ConnectCancel' text='Cancel'/>
                    <button id='#ConnectOk' text='OK' disabled='yes'/>
                </hbox>
            </hbox>
        </vbox>
    </window>
</dialogs>

Regards, Uwe

afriend · Post by **afriend** » Fri Mar 06, 2015 8:30 pm

Just what I needed. Thanks for posting and sharing

falsam · Post by **falsam** » Fri Mar 06, 2015 10:31 pm

Nice code uwekel. Thank you for sharing

t57042 · Post by **t57042** » Thu Dec 17, 2015 9:19 pm

The progam does not compile (PB 5.30 an PB 5.40 - windows 7).
A window is displayed and then a message: Purebasic_compilation.exe problem.

Any suggestions?

Richard

PureBasic Forums - English

Tiny Database Browser

Tiny Database Browser

Re: Tiny Database Browser

Re: Tiny Database Browser

Re: Tiny Database Browser