SQLite Database Viewer

[collectordave]

A simple database viewer for SQLite databases.

You will need the GridEX module available here viewtopic.php?f=12&t=69596&hilit=GridExModule

Once you have it here is the code:-

Code: Select all

UseSQLiteDatabase()

IncludeFile "GridExModule.pbi"

Global winMain

Global DBGrid.i,DBTree.i,DataRows.i,DisplayedRows.i,CurrentVOffset.i,CurrentHOffset.i

Global CurrentDBName.s,CurrentDB.i,CurrentTableName.s

Global MyVSCroll.i,MyHSCroll.i

Structure FldDat
  Name.s
  FType.s
  NotNull.i
  PK.i
EndStructure
 
Structure TblDat
  Name.s
  TType.s
  SQL.s
  List Field.FldDat()
EndStructure

Global NewList TableData.TblDat()

Enumeration FormMenu
  #mnuOpen
  #mnuClose
  #mnuSep
  #mnuExit
  #mnuTools
  #mnuVacuum
EndEnumeration

Procedure GetDatabasePragma()
  
  DatabaseQuery(CurrentDB, "SELECT * FROM sqlite_master;")
  
  ClearList(TableData())
  
  ;Load The Table Names etc
  While NextDatabaseRow(CurrentDB)
    
    ;Miss Out SQLite Tables
    If FindString(GetDatabaseString(CurrentDB, DatabaseColumnIndex(CurrentDB, "tbl_name")),"sqlite") = 0
      AddElement(TableData())
      TableData()\TType = GetDatabaseString(CurrentDB, DatabaseColumnIndex(CurrentDB, "type")) 
      TableData()\Name = GetDatabaseString(CurrentDB, DatabaseColumnIndex(CurrentDB, "tbl_name")) 
      TableData()\SQL = GetDatabaseString(CurrentDB, DatabaseColumnIndex(CurrentDB, "sql")) 
    EndIf
  Wend

  FinishDatabaseQuery(CurrentDB)
  
  ;Load Field data
  ForEach TableData()

    ClearList (TableData()\Field())
  
    DatabaseQuery(CurrentDB, "PRAGMA table_info(" + TableData()\Name + ");")

    While NextDatabaseRow(CurrentDB)
      AddElement(TableData()\Field())
      TableData()\Field()\Name = GetDatabaseString(CurrentDB,DatabaseColumnIndex(CurrentDB,"name"))
      TableData()\Field()\FType = GetDatabaseString(CurrentDB,DatabaseColumnIndex(CurrentDB,"type"))
      TableData()\Field()\NotNull = GetDatabaseLong(CurrentDB,DatabaseColumnIndex(CurrentDB,"notnull"))  
      TableData()\Field()\PK = GetDatabaseLong(CurrentDB,DatabaseColumnIndex(CurrentDB,"pk"))  
    Wend
    
    FinishDatabaseQuery(CurrentDB)

  Next
  
EndProcedure

Procedure LoadTree()
  
  ClearGadgetItems(DBTree)
  
  While WindowEvent():Wend
  
  AddGadgetItem (DBTree, -1, CurrentDBName,0,0)  
  
  ;Only Table names
  ForEach TableData()
    
    If TableData()\TType = "table" Or TableData()\TType = "view"

      AddGadgetItem(DBTree, -1, TableData()\Name,0,1)
 
    EndIf
  
  Next
  
  SetGadgetItemState(DBTree, 0, #PB_Tree_Expanded)
  
EndProcedure

Procedure GetData(TableName.s,Offset.i)
  
  Protected Criteria.s,RowString.s,ClearString.s

  ;Now get The Data
  Criteria = "SELECT * FROM " + TableName + " LIMIT 20 OFFSET " + Str(Offset) + ";"
  DatabaseQuery(CurrentDB,Criteria)

  RowNumber = 1
  
  While NextDatabaseRow(CurrentDB)
    
    RowString = ""
    ClearString = ""
    ColumnNumber = 0
  
    ForEach TableData()\Field()

      Select DatabaseColumnType(CurrentDB, ColumnNumber)
          
        Case #PB_Database_Blob 
          
          ClearString = ClearString + "" + Chr(10)
          RowString = RowString + "Blob Size " + Str(DatabaseColumnSize(CurrentDB,DatabaseColumnIndex(CurrentDB,TableData()\Field()\Name))) + Chr(10) 

        Case #PB_Database_String
          
          ClearString = ClearString + "" + Chr(10)
          RowString = RowString + GetDatabaseString(CurrentDB,DatabaseColumnIndex(CurrentDB,TableData()\Field()\Name)) + Chr(10)

        Case #PB_Database_Long
          
          ClearString = ClearString + "" + Chr(10)
          RowString = RowString + Str(GetDatabaseLong(CurrentDB,DatabaseColumnIndex(CurrentDB,TableData()\Field()\Name))) + Chr(10)
   
        Case #PB_Database_Float
          
          ClearString = ClearString + "" + Chr(10)
          RowString = RowString + StrF(GetDatabaseFloat(CurrentDB,DatabaseColumnIndex(CurrentDB,TableData()\Field()\Name))) + Chr(10)
           
        Case #PB_Database_Double
          
          ClearString = ClearString + "" + Chr(10)
          RowString = RowString + StrD(GetDatabaseDouble(CurrentDB,DatabaseColumnIndex(CurrentDB,TableData()\Field()\Name))) + Chr(10)
              
        Case #PB_Database_Quad
          
          ClearString = ClearString + "" + Chr(10)
          RowString = RowString + Str(GetDatabaseQuad(CurrentDB,DatabaseColumnIndex(CurrentDB,TableData()\Field()\Name))) + Chr(10)
           
      EndSelect 
      ColumnNumber + 1
    Next  
 
    ClearString = Left(ClearString,Len(ClearString)-1)
    RowString = Left(RowString,Len(RowString)-1)
    GridEx::SetRowText(DBGrid, RowNumber, RowString)
   
    RowNumber + 1
   
  Wend
 
  ;Clear Last Row or Rows
  For iLoop =  RowNumber To 20
   
    Gridex::SetRowText(DBGrid, iLoop,ClearString)

  Next
 
  Gridex::Refresh(DBGrid)

EndProcedure

Procedure SetRowsColumns(TableName.s)
  
  Define Rows.i,Columns.i
  Define Criteria.s,RowString.s
  
  ;Get Columns Required
  ForEach TableData()
    
    If TableData()\Name = TableName
      Break
    EndIf
    
  Next
  
  Columns = ListSize(TableData()\Field())
  
  ;Get Rows required
  Criteria = "SELECT count(1) FROM " + TableName + ";"
  DatabaseQuery(CurrentDB,Criteria)
  FirstDatabaseRow(CurrentDB)
  DataRows =  GetDatabaseLong(CurrentDB,0)
  FinishDatabaseQuery(CurrentDB)
  
  If DataRows > 20
    
    DisplayedRows = 20
    
  Else
    
    DisplayedRows = DataRows
    
  EndIf

  ;Resize Grid For Table data (Max 20 Rows at a time)
  GridEx::ReDefine(DBGrid,DisplayedRows,Columns)
  
  ;Hide Row Header
  GridEx::HideColumn(DBGrid, 0, #True)
    
  ;Set To Allow All Cells To Be Edited
  For iLoop = 1 To Columns
    GridEx::SetCellFlags(DBGrid, GridEx::#AnyRow,iLoop,GridEx::#Edit) 
  Next iLoop
  
  ;Show The Changes
  Gridex::Refresh(DBGrid)
  
  ;Set Headers
  RowString = ""
  ForEach TableData()\Field()
    
    RowString = RowString + TableData()\Field()\Name + Chr(10) 
  
  Next
  
  RowString = Left(RowString,Len(RowString)-1)

  GridEx::SetRowText(DBGrid, 0, RowString)
  
  Gridex::Refresh(DBGrid)
  
  SetGadgetAttribute(MyVSCroll,#PB_ScrollBar_Maximum ,DataRows)
  SetGadgetAttribute(MyHSCroll,#PB_ScrollBar_Maximum ,Columns)
  
  SetGadgetState(MyHSCroll,1)
  
  ;Now Get Data
  CurrentVOffset = 0
  CurrentHOffset = 0
  
  GetData(TableName,CurrentVOffset)   
  
EndProcedure

Procedure Open_Database()
  
  Define DBHnd.l,FileName.s
  
  FileName = OpenFileRequester("Choose Database To Load","","Database (*.db)|*.db;*.db|All files (*.*)|*.*",0)
  
  If FileName

    ;Open Database
    CurrentDB = OpenDatabase(#PB_Any, FileName, #Empty$, #Empty$)  
    If CurrentDB = 0
    
      MessageRequester("Database Error","Failed to open database!")  
    
    Else
    
      CurrentDBName = GetFilePart(FileName,#PB_FileSystem_NoExtension)

      GetDatabasePragma()
      LoadTree()
    
    EndIf
  
  EndIf

EndProcedure


Procedure ScrollH()
  
  If IsDatabase(CurrentDB)
    If CurrentHOffset <> GetGadgetState(MyHSCroll)
      CurrentHOffset = GetGadgetState(MyHSCroll)
      GridEx::SetTopColumn(DBGrid,CurrentHOffset)
    EndIf
  EndIf
  
EndProcedure

Procedure ScrollV()
  
  If IsDatabase(CurrentDB)
    If CurrentVOffset <> GetGadgetState(MyVSCroll)
      CurrentVOffset = GetGadgetState(MyVSCroll)
      GetData(CurrentTableName,CurrentVOffset)
    EndIf
  EndIf
  
  EndProcedure

  winMain = OpenWindow(#PB_Any, 0, 0, 990, 470, "SQLite Viewer", #PB_Window_SystemMenu | #PB_Window_ScreenCentered)
  CreateMenu(#PB_Any, WindowID(winMain))
  MenuTitle("Database")
  MenuItem(#mnuOpen, "Open")
  MenuItem(#mnuClose, "Close")
  MenuBar()
  MenuItem(#mnuExit, "Exit")
  MenuTitle("Tools")
  MenuItem(#mnuVacuum, "Vacuum")
  DBTree = TreeGadget(#PB_Any, 5, 5, 200, 410)
  DBGrid = GridEx::Gadget(winMain, #PB_Any, 210, 5, 745, 410, 20,20,GridEx::#DrawGrid|GridEx::#Border_Single)
  MyVSCroll = ScrollBarGadget(#PB_Any,955,5,20,410,0,100,20,#PB_ScrollBar_Vertical)
  MyHSCroll = ScrollBarGadget(#PB_Any,210,414,745,20,1,100,1)
 
  ;Hide Row Header 
  GridEx::HideColumn(DBGrid, 0, #True)

  ;Dynamic Scrolling
  BindGadgetEvent(MyVSCroll,@ScrollV(),#PB_All)
  BindGadgetEvent(MyHSCroll,@ScrollH(),#PB_All)
  
  Repeat
      
      Event = WaitWindowEvent()
      Select Event
        Case #PB_Event_CloseWindow
          GridEx::Free(DBGrid)
          End
  
      Case #PB_Event_Menu
        Select EventMenu()
            
          Case #mnuOpen
            
            Open_Database()
            
          Case #mnuClose
            
            CloseDatabase(CurrentDB)
            ClearGadgetItems(DBTree)
            GridEx::ClearRows(DBGrid)
            
          Case #mnuVacuum
            
            DatabaseUpdate(CurrentDB,"VACUUM")
            
            MessageRequester("SQLite Viewer","VACUUM Completed Successfully",#PB_MessageRequester_Ok|#PB_MessageRequester_Info)
            
          Case #mnuExit
            
            GridEx::Free(DBGrid)
            End
            
        EndSelect
  
      Case #PB_Event_Gadget
        Select EventGadget()
            
          Case DBTree
          
            If EventType() = #PB_EventType_Change
            
              If GetGadgetItemAttribute(DBTree, GetGadgetState(DBTree), #PB_Tree_SubLevel) = 1
                
                CurrentTableName = GetGadgetItemText(DBTree, GetGadgetState(DBTree))
                
                
                SetRowsColumns(CurrentTableName)

              EndIf

            EndIf   
         
          Case MyVSCroll
            
            If IsDatabase(CurrentDB)
              If CurrentVOffset <> GetGadgetState(MyVSCroll)
                CurrentVOffset = GetGadgetState(MyVSCroll)
                GetData(CurrentTableName,CurrentVOffset)
              EndIf
            EndIf
            
            
          Case MyHSCroll
            
            
            
        EndSelect
        
    EndSelect
    
  ForEver

Let me know if there are any problems.

CD

PS Code updated for ClearRows() and Horizontal scroll bar

[blueb]

I'm getting an error on:

line 332 GridEx::ClearCells(DBGrid)

and all I can find are:

ClearRows()
ClearMultiSort()
ClearSelectedCells()
ClearList()
ClearMap()
ClearlatChange()
ClearLastClick()

Did you create a new procedure in the GridEx module?

[collectordave]

Hi blueb

no new command here is the list from the gridex control,

;{ ----- GridEx Commands -----

; GridEx::AddCellTerm() ; Add a term for calculation to the cell
; GridEx::AddComboItems() ; Adding elements to a ComboBox
; GridEx::AddListItems() ; List of words for autocomplete
; GridEx::AddRow() ; Similar to AddGadgetItem()
; GridEx::AttachPopup() ; Assign popup menu to a cell
; GridEx::AllowColumnResize() ; Allow to resize the column
; GridEx::AllowMouseResize() ; Allow to resize columns and rows
; GridEx::AllowRowResize() ; Allow to resize the row
; GridEx::AutoColumnWidth() ; Set automatic Column width
; GridEx::AutoRowHeight() ; Set automatic row height
; GridEx::ClearCells() ; Clear all cells

What is the error?

Cd

[collectordave]

Line 332 in my programme is End

in the module it is

#ScrollBars

?????

CD

PS Here is a link to the one I am Using https://www.dropbox.com/s/slt4j0agmwcfq ... e.pbi?dl=0

[dcr3]

I'm getting an error on:

line 332 GridEx::ClearCells(DBGrid)

I

Use.

Code: Select all

GridEx::ClearRows(DBGrid)

[blueb]

Sorry Dave,

I used the download available at: viewtopic.php?p=514613#p514613

which does not show the GridEx::ClearCells(DBGrid) command.

The Header displays:

;/ ===================================
;/ = GridExModule [PB 5.6x] =
;/ ===================================
;/
;/ Extended Grid Gadget
;/ ( editing, formatting, marking cells / calculations / sorting / different cell types / ... )
;/
;/ based on 'MyGrid' by 'said' (11/2017)
;/
;/ Module by Thorsten1867 (10/2017)
;/

; ---------------------------
; Last Update: 28.11.2017
; ---------------------------

;{ ----- GridEx Commands -----

; GridEx::AddCellTerm() ; Add a term for calculation to the cell
; GridEx::AddComboItems() ; Adding elements to a ComboBox
; GridEx::AddListItems() ; List of words for autocomplete
; GridEx::AddRow() ; Similar to AddGadgetItem()
; GridEx::AttachPopup() ; Assign popup menu to a cell
; GridEx::AllowColumnResize() ; Allow to resize the column
; GridEx::AllowMouseResize() ; Allow to resize columns and rows
; GridEx::AllowRowResize() ; Allow to resize the row
; GridEx::AutoColumnWidth() ; Set automatic Column width
; GridEx::AutoRowHeight() ; Set automatic row height
; GridEx::ClearRows() ; Clear all cells NOTE: <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


I will try with your version.
Thanks

[collectordave]

Hi

With the GridEX download in bluebs post I get the error on line 321.

GridEx::ClearCells(DBGrid)

Change to

GridEx::ClearRows(DBGrid)

And all works ok

[blueb]

Thanks Dave,

Regarding scrolling. I opened a table with about 40 columns.

Everything works, but it would be nice to have horizontal scrolling for these instances.

[collectordave]

I had problems scrolling large datasets so worked on it should now work for the rows just adding code for the horizontal scroll bar now.

Back in a while with updated code.

CD

[Thorsten1867]

GridExModule.pbi

[collectordave]

Thanks for the link Thorsten1867

Code in first post updated with ClearRows() and Horizontal scroll bar.

Cheers


CD

[blueb]

Thanks Dave (and Thorsten for the new code)
Great example.

[collectordave]

Just been experimenting with having checkboxes appear for boolean fields.

If you find the code starting on line 222


and replace with:-

Code: Select all

  ;Set Headers
  RowString = ""
  iLoop = 1  ;Columns Start From 1
  ForEach TableData()\Field()
    
    If TableData()\Field()\FType = "BOOLEAN" Or TableData()\Field()\FType = "BOOL"
      ;Set Checkbox In Grid For Boolean Fields
      GridEx::SetCellFlags(DBGrid, GridEx::#AnyRow,iLoop,GridEx::#Checkbox)
    EndIf
    iLoop + 1
    
    RowString = RowString + TableData()\Field()\Name + Chr(10) 
  
  Next

Then any field defined in SQLite as BOOLEAN or BOOL will create a checkbox in the relevant column.

Will update first post when finished having ideas.

CD

[doctorized]

In the latest version of GridExModule that Thorsten1867 provided there is no CrearRows(). On the other hand there is CrearCells() that does the job. Very nice work, I have three suggestions.
1) use ListEx and not GridEx as ListEx supports different colors for odd/even rows and looks much better.
2) your colunms have all the same width, so I have to resize almost every column in my tests. ListEx has #FitColumn and auto fits data.
3) add running sql command option. The code is very easy, I wrote one myself for another project, I can post it if you want.

PS. ListEx is from Thorsten1867 too!

[collectordave]

Hi doctorized

I used ClearCells() originally some users did not have this so changed to ClearRows() both work just depends on which version of GridEx you have. The one posted by Thorsten1867 above has CleaCells().

Easy to change read posts above.

1) ListEX in Thorsten1867 latest download will check it out later.
2) GridEx has Autocolumn width as well.
3) Thinking of adding this I posted a module some time ago here viewtopic.php?f=12&t=64656&hilit=query+builder which I am thinking of updateing and adding.

Regards

CD