SQLite Simple Admin

Share your advanced PureBasic knowledge/code with the community.
collectordave
Addict
Addict
Posts: 1309
Joined: Fri Aug 28, 2015 6:10 pm
Location: Portugal

SQLite Simple Admin

Post by collectordave »

For all those times when you want to create a small database on the fly and code the creation into your application.

This is a very simple SQLite database administrator. Not support yet for indexes and views.

Intended for a beginner to use and to be cross platform.

The application allows you to create a new database or open an existing one and then to Add,Rename or Delete tables and the columns in the tables.

After loading the application can produce a file with a PureBasic procedure to create the database if you choose.

The code is in two parts.

The first is 'dlgAddColumn.pbi' :-

Code: Select all

DeclareModule AddColumn
  
  Structure FldDat
    Name.s
    FType.s
    NotNull.i
    Autoincrement.i
    Length.i
    PK.i
  EndStructure
  
  
  
  
  Declare.i Open(*FieldData.FldDat)
  
EndDeclareModule

Module AddColumn
  
  Global dlgAddColumn

  Global txtName, strName, txtType, cmbType, txtLength,strLength,chkNotNull, chkAutoincrement, chkPrimaryKey, btnSave, btnCancel

  Procedure LoadTypeCombo()
    
    Define FieldType.s
  
    Restore FieldTypes
  
    Read.s FieldType
    Repeat
    
      AddGadgetItem(cmbType,-1,FieldType)
      Read.s FieldType
  
    Until FieldType = "Finish" 
    
  EndProcedure
  
  Procedure CheckColumnData(*FieldData.FldDat)
    
    Define ColumnType.s

 
    
    ColumnType = GetGadgetText(cmbType)
    
    Select ColumnType
        
      Case "VARCHAR","CHARACTER","VARYING CHARACTER","NCHAR","NATIVE CHARACTER","NVARCHAR"
        
        If *FieldData\Length > 0
          *FieldData\FType = ColumnType + " (" +  *FieldData\Length + ")"
        EndIf
        
      Default
        
        *FieldData\FType = ColumnType
        
    EndSelect

  EndProcedure
  
  Procedure.i Open(*FieldData.FldDat)

    Define Quit.i,RetVal.i
    
  dlgAddColumn = OpenWindow(#PB_Any, 0, 0, 250, 240, "Column Detail", #PB_Window_SystemMenu | #PB_Window_Tool | #PB_Window_WindowCentered)
  txtName = TextGadget(#PB_Any, 10, 10, 90, 20, "Column Name")
  strName = StringGadget(#PB_Any, 110, 10, 130, 20, "")
  txtType = TextGadget(#PB_Any, 10, 40, 90, 20, "Type")
  cmbType = ComboBoxGadget(#PB_Any, 110, 40, 130, 20)
  txtLength = TextGadget(#PB_Any, 10, 70, 90, 20, "Length")
  strLength= StringGadget(#PB_Any, 110, 70, 130, 20, "20") 
  chkNotNull = CheckBoxGadget(#PB_Any, 110, 100, 130, 20, "Not Null")
  chkAutoincrement = CheckBoxGadget(#PB_Any, 110, 130, 130, 20, "Autoincrement")
  chkPrimaryKey = CheckBoxGadget(#PB_Any, 110, 160, 130, 20, "Primary Key")
  btnSave = ButtonGadget(#PB_Any, 20,200, 70, 25, "Save")
  btnCancel = ButtonGadget(#PB_Any, 170, 200, 70, 25, "Cancel")
  
  StickyWindow(dlgAddColumn,#True)
  
    Quit = #False
    LoadTypeCombo()

    Repeat
      
      Event = WaitWindowEvent()
      Select Event
        Case #PB_Event_CloseWindow
          RetVal = #False
          Quit = #True
          CloseWindow(dlgAddColumn)
  
        Case #PB_Event_Menu
          Select EventMenu()
          EndSelect
  
        Case #PB_Event_Gadget
          Select EventGadget()
            
            Case btnCancel
            
              RetVal = #False
              Quit = #True
              CloseWindow(dlgAddColumn) 
            
            Case btnSave

                ;Put Settings In Structure
                *FieldData\Name = GetGadgetText(strName)
                *FieldData\Length = Val(GetGadgetText(strLength))
                CheckColumnData(*FieldData)                          ;Sets *FieldData\FType
                *FieldData\NotNull = GetGadgetState(chkNotNull)
                *FieldData\PK = GetGadgetState(chkPrimaryKey)
                *FieldData\Autoincrement = GetGadgetState(chkAutoincrement)
              
                RetVal = #True
                Quit = #True
                CloseWindow(dlgAddColumn) 
             

          EndSelect
      EndSelect
    
    Until Quit = #True
  
  ProcedureReturn RetVal
  
EndProcedure

  DataSection
  FieldTypes:
  Data.s "INT","INTEGER","TINYINT","SMALLINT","MEDIUMINT"
  Data.s "BIGINT","UNSIGNED BIG INT","INT2","INT8","CHARACTER"
  Data.s "VARCHAR","VARYING CHARACTER","NCHAR","NATIVE CHARACTER","NVARCHAR"
  Data.s "TEXT","CLOB","BLOB","REAL","DOUBLE"
  Data.s "DOUBLE PRECISION","FLOAT","NUMERIC","NUMBER","DECIMAL","DEC","BOOLEAN"
  Data.s "CURRENCY","DATE","DATETIME","Finish"
EndDataSection



EndModule
The Second Is 'winMain.pb' :-

Code: Select all

UseSQLiteDatabase()

  IncludeFile "dlgAddColumn.pbi"
Global winMain.i

Global lsiColumns, cmbTables, btnRenameTable, btnDeleteTable, btnAddColumn, btnRenameColumn, frmTables, frmColumns, btnDeleteColumn, btnAddTable

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


Enumeration FormMenu
  #mnuNewDatabase
  #mnuOpenDatabase
  #mnuExportPB
  #mnuExit
EndEnumeration

Global EditField.FldDat

Global NewList TableData.TblDat() 

Global CurrentDBID.i,CurrentDBName.s

Procedure Open_Database(DBName.s)

  ;Open  Database
  CurrentDBID = OpenDatabase(#PB_Any, DBName.s, #Empty$, #Empty$)  
  If CurrentDBID = 0
    
    MessageRequester("Database Error","Failed to open database!")  

  EndIf
  
EndProcedure

Procedure GetDatabasePragma()
  
  DatabaseQuery(CurrentDBID, "SELECT * FROM sqlite_master;")
  
  ClearList(TableData())

  While NextDatabaseRow(CurrentDBID)
    
    If GetDatabaseString(CurrentDBID, DatabaseColumnIndex(CurrentDBID, "type")) = "table"
      ;Miss Out SQLite Tables
      If FindString(GetDatabaseString(CurrentDBID, DatabaseColumnIndex(CurrentDBID, "tbl_name")),"sqlite") = 0
        AddElement(TableData())
        TableData()\TType = GetDatabaseString(CurrentDBID, DatabaseColumnIndex(CurrentDBID, "type")) 
        TableData()\Name = GetDatabaseString(CurrentDBID, DatabaseColumnIndex(CurrentDBID, "tbl_name")) 
        TableData()\SQL = GetDatabaseString(CurrentDBID, DatabaseColumnIndex(CurrentDBID, "sql")) 
      EndIf 
    EndIf
  Wend

  FinishDatabaseQuery(CurrentDBID)

  ForEach TableData()

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

    While NextDatabaseRow(CurrentDBID)
      AddElement(TableData()\Field())
      TableData()\Field()\Name = GetDatabaseString(CurrentDBID,DatabaseColumnIndex(CurrentDBID,"name"))
      TableData()\Field()\FType = GetDatabaseString(CurrentDBID,DatabaseColumnIndex(CurrentDBID,"type"))
      TableData()\Field()\NotNull = GetDatabaseLong(CurrentDBID,DatabaseColumnIndex(CurrentDBID,"notnull"))  
      TableData()\Field()\PK = GetDatabaseLong(CurrentDBID,DatabaseColumnIndex(CurrentDBID,"pk")) 
    
        StartPos = FindString(TableData()\sql,TableData()\Field()\Name)
        EndPos = FindString(TableData()\sql,#LF$,StartPos)
        
        If EndPos > 0
          If FindString(Mid(TableData()\sql,StartPos-1,EndPos-StartPos-1),"AUTOINCREMENT",StartPos)
            TableData()\Field()\Autoincrement = #True
          EndIf
        EndIf
    Wend
    
    FinishDatabaseQuery(CurrentDBID)

  Next
  
EndProcedure

Procedure ExportPBCode()
  
  Define CodeString.s
  
  OpenFile(0, "Create " + CurrentDBName + ".pb")
  
  
  
  
  
  
  CodeString = "Procedure Create" + CurrentDBName + "DB()"
  
  WriteStringN(0,Codestring)
  WriteStringN(0,"")
 
  CodeString = "  Define Criteria.s,DBID.i"
  
  WriteStringN(0,Codestring)
  WriteStringN(0,"")
  
  CodeString = "  CreateFile(0," + #DQUOTE$ + CurrentDBName + ".db" + #DQUOTE$ + ")"
  WriteStringN(0,Codestring)
  WriteStringN(0,"")
  
  CodeString = "  CloseFile(0)"
  WriteStringN(0,Codestring)
  WriteStringN(0,"")

  CodeString = "  DBID = OpenDatabase(#PB_Any," +  #DQUOTE$ + CurrentDBName + ".db" + #DQUOTE$ + ", #Empty$, #Empty$)"
  WriteStringN(0,Codestring)
  WriteStringN(0,"")

  ForEach TableData()
    
    CodeString = "  Criteria = " + #DQUOTE$ + "CREATE TABLE [" + TableData()\Name + "] ("
    
    ForEach TableData()\Field()
      
      CodeString = CodeString + "[" + TableData()\Field()\Name + "]" + " " + TableData()\Field()\FType + " "
      If TableData()\Field()\PK
        CodeString = CodeString + "PRIMARY KEY "
      EndIf
      If TableData()\Field()\Autoincrement
        CodeString = CodeString + "AUTOINCREMENT "
      EndIf 
      
      CodeString = CodeString + ","
      
      
    Next
      
    CodeString = Left(CodeString,Len(CodeString)-1)
    CodeString = CodeString + ");" + #DQUOTE$
 
    WriteStringN(0,Codestring)
    WriteStringN(0,"")    
      
    CodeString = "  DatabaseUpdate(DBID,Criteria)"
    WriteStringN(0,Codestring)
    WriteStringN(0,"")    
      
  Next
  
  CodeString = "EndProcedure"
  WriteStringN(0,Codestring)
 
  CloseFile(0)

  
EndProcedure

Procedure LoadTableCombo()
  
  ClearGadgetItems(cmbTables)
  
  While WindowEvent():Wend
  
  ForEach TableData()
    
    If TableData()\TType = "table"

      AddGadgetItem(cmbTables, -1, TableData()\Name)

    EndIf
  
  Next

EndProcedure

Procedure LoadColumnsList(TableName.s)
  
    Define LiString.s
  
  ClearGadgetItems(lsiColumns)
  
  
  
  ForEach TableData()
    If TableData()\Name = TableName
      Break
    EndIf
  Next
  
  ForEach TableData()\Field()
    
    LiString = TableData()\Field()\Name + Chr(10) + TableData()\Field()\FType + Chr(10)
    If TableData()\Field()\NotNull = 1
      LiString = LiString + "Yes" + Chr(10)
    Else
      LiString = LiString + "No" + Chr(10)
    EndIf
    
    ;Autoincrement
    If TableData()\Field()\Autoincrement = #True
      LiString = LiString + "Yes" + Chr(10)
    Else
      LiString = LiString + "No" + Chr(10)
    EndIf    
  
    If TableData()\Field()\PK = 1
      LiString = LiString + "Yes" 
    Else
      LiString = LiString + "No" 
    EndIf   
    
    AddGadgetItem(lsiColumns,-1,LiString)
    LiString = ""
  
  Next
  
  ;Colour Alternate Rows
  For iLoop = 0 To CountGadgetItems(lsiColumns) Step 2
  
    SetGadgetItemColor(lsiColumns,  iLoop, #PB_Gadget_BackColor,  $FFFFCC, #PB_All)
  
  Next iLoop
  
  
  
EndProcedure

Procedure AddTable()
  
  Define NewTableName.s,Criteria.s
  
  
NewTableName = InputRequester("New Table Name", "Enter The New Table Name. Leave blank to cancel.","")   

If Len(NewTableName) > 0
  
  Criteria = "CREATE TABLE [" + NewTableName + "] (" + #LF$ + "[Default] INTEGER);"
  
  DatabaseUpdate(CurrentDBID,Criteria)
  
    GetDatabasePragma()
    
    LoadTableCombo()
    
    ;Show First Column
    SetGadgetState(cmbTables,0)
    
    ;Show Fields
    If GetGadgetText(cmbTables) <> ""
      LoadColumnsList(GetGadgetText(cmbTables))
    EndIf
  
  EndIf
   
  
EndProcedure

Procedure RenameTable(OldTableName.s)
  
  
  Define NewTableName.s,Criteria.s
  
  
NewTableName = InputRequester("New Table Name", "Enter The New Table Name. Leave blank to cancel.","")   

If Len(NewTableName) > 0
  
  Criteria = "ALTER TABLE [" + OldTableName + "]RENAME TO [" + NewTableName + "];"
  DatabaseUpdate(CurrentDBID,Criteria)
  
    GetDatabasePragma()
    
    LoadTableCombo()
    
    ;Show First Column
    SetGadgetState(cmbTables,0)
    
    ;Show Fields
    If GetGadgetText(cmbTables) <> ""
      LoadColumnsList(GetGadgetText(cmbTables))
    EndIf
  
EndIf

  
EndProcedure

Procedure DropTable(TableName.s)
  
  Define Criteria.s
  
  Criteria = "DROP TABLE " + TableName + ";"
  DatabaseUpdate(CurrentDBID,Criteria)
 
  GetDatabasePragma()
    
  LoadTableCombo()
    
  ;Show First Column
  SetGadgetState(cmbTables,0)
    
  ;Show Fields
  If GetGadgetText(cmbTables) <> ""
    LoadColumnsList(GetGadgetText(cmbTables))
  EndIf

  
EndProcedure

Procedure AddColumn()
  
  Define Criteria.s
  
  DisableWindow(winMain,#True)
  
  If AddColumn::Open(@EditField) = #True

    ;Position List
    ForEach TableData()
      If TableData()\Name = GetGadgetText(cmbTables)
        Break        
      EndIf
    Next
    
    ;Create Temporary table with new Column  
    Criteria = "CREATE TABLE [Temp] ("
  
    ForEach TableData()\Field()
    
      Criteria = Criteria + "[" + TableData()\Field()\Name + "] " + TableData()\Field()\Ftype + ","
    
    Next
    
    ;Add New Column Name
    Criteria = Criteria + "[" + EditField\Name + "] " + EditField\Ftype + ","
    
    Criteria = Left(Criteria,Len(Criteria) -1)
  
    Criteria = Criteria + ");"
    
    DatabaseUpdate(CurrentDBID,Criteria)   
    
    ;Copy Data To New Column
    Criteria = "INSERT INTO Temp("
  
    ForEach TableData()\Field()
           
      Criteria = Criteria + TableData()\Field()\Name + ","
    
    Next
  
    ;Copy Data To Temp Table
    Criteria = Left(Criteria,Len(Criteria) -1)
  
    Criteria = Criteria + ") SELECT "
  
    ForEach TableData()\Field()
    
      Criteria = Criteria + TableData()\Field()\Name + ","

    Next
  
    Criteria = Left(Criteria,Len(Criteria) -1)
  
    Criteria = Criteria + " FROM " + TableData()\Name + ";"
  
    DatabaseUpdate(CurrentDBID,Criteria)   
  
  
    ;Delete Original Table
    Criteria = "DROP Table " + TableData()\Name
    DatabaseUpdate(CurrentDBID,Criteria)
  
    ;Rename Temporary
    Criteria = "ALTER TABLE [Temp] RENAME TO [" + TableData()\Name + "];"
    DatabaseUpdate(CurrentDBID,Criteria)
  
    ;Now UpDate List
    AddElement(TableData()\Field())
    TableData()\Field()\Name = EditField\Name
    TableData()\Field()\Autoincrement = EditField\Autoincrement
    TableData()\Field()\FType = EditField\FType
    TableData()\Field()\Length = EditField\Length 
    TableData()\Field()\NotNull = EditField\NotNull  
    TableData()\Field()\PK = EditField\PK  
  
    ;Show In Column List
    LoadColumnsList(GetGadgetText(cmbTables))
    
  Else
    
    ;Debug "Cancelled"
    
  EndIf
  DisableWindow(winMain,#False)
  
EndProcedure

Procedure ReNameColumn(Column.s)
  
  Define NewColumnName.s,Criteria.s

  NewColumnName = InputRequester("New Column Name", "Enter The New Column Name. Leave blank to cancel.","")   

  If  NewColumnName
  
    ;Position List
    ForEach TableData()
      If TableData()\Name = GetGadgetText(cmbTables)
        Break        
      EndIf
    Next
  
    ;Create Temporary table with new Column  
    Criteria = "CREATE TABLE [Temp] ("
  
    ForEach TableData()\Field()
    
      If TableData()\Field()\Name = Column
    
        Criteria = Criteria + "[" + NewColumnName + "] " + TableData()\Field()\Ftype + ","
      
      Else
    
        Criteria = Criteria + "[" + TableData()\Field()\Name + "] " + TableData()\Field()\Ftype + ","
    
      EndIf
    
    Next
  
    Criteria = Left(Criteria,Len(Criteria) -1)
  
    Criteria = Criteria + ");"

    DatabaseUpdate(CurrentDBID,Criteria)

    ;Copy Data 
    Criteria = "INSERT INTO Temp("
  
    ForEach TableData()\Field()
    
      If TableData()\Field()\Name = Column
      
        Criteria = Criteria + NewColumnName + ","
      
      Else
          
        Criteria = Criteria + TableData()\Field()\Name + ","
    
      EndIf
    
    Next
  
    Criteria = Left(Criteria,Len(Criteria) -1)
  
    Criteria = Criteria + ") SELECT "
  
    ForEach TableData()\Field()
    
      Criteria = Criteria + TableData()\Field()\Name + ","

    Next
  
    Criteria = Left(Criteria,Len(Criteria) -1)
  
    Criteria = Criteria + " FROM " + TableData()\Name + ";"
  
    DatabaseUpdate(CurrentDBID,Criteria)

    ;Delete Original Table
    Criteria = "DROP Table " + TableData()\Name
    DatabaseUpdate(CurrentDBID,Criteria)
  
    ;Rename Temporary
    Criteria = "ALTER TABLE [Temp] RENAME TO [" + TableData()\Name + "];"
    DatabaseUpdate(CurrentDBID,Criteria) 
  
    ;Now UpDate List
    ForEach TableData()\Field()
      If TableData()\Field()\Name = Column
        TableData()\Field()\Name = NewColumnName
        Break
      EndIf
    Next
  
    ;Show In Column List
    LoadColumnsList(GetGadgetText(cmbTables))
    
  EndIf
  
EndProcedure

Procedure DeleteColumn(Column.s)
  
  Define Criteria.s
  
  ;CPosition Pragma List
  ForEach TableData()
    If TableData()\Name = GetGadgetText(cmbTables)
      Break        
    EndIf
           
  Next
  
  Criteria = "CREATE TABLE [Temp] ("
  
  ForEach TableData()\Field()
    
    If TableData()\Field()\Name <> Column
    
    Criteria = Criteria + "[" + TableData()\Field()\Name + "] " + TableData()\Field()\Ftype + ","
    
    EndIf
    
  Next
  
  Criteria = Left(Criteria,Len(Criteria) -1)
  
  Criteria = Criteria + ");"

  DatabaseUpdate(CurrentDBID,Criteria)
  
  ;Copy Data without deleted column
  Criteria = "INSERT INTO Temp("
  
  ForEach TableData()\Field()
    
    If TableData()\Field()\Name <> Column
    
    Criteria = Criteria + TableData()\Field()\Name + ","
    
    EndIf
    
  Next
  
  Criteria = Left(Criteria,Len(Criteria) -1)
  
  Criteria = Criteria + ") SELECT "
  
  ForEach TableData()\Field()
    
    If TableData()\Field()\Name <> Column
    
      Criteria = Criteria + TableData()\Field()\Name + ","
    
    EndIf
    
  Next
  
  Criteria = Left(Criteria,Len(Criteria) -1)
  
  Criteria = Criteria + " FROM " + TableData()\Name + ";"
  
  DatabaseUpdate(CurrentDBID,Criteria)

  ;Delete Original Table
  Criteria = "DROP Table " + TableData()\Name
  DatabaseUpdate(CurrentDBID,Criteria)
  
  ;Rename Temporary
  Criteria = "ALTER TABLE [Temp] RENAME TO [" + TableData()\Name + "];"
  DatabaseUpdate(CurrentDBID,Criteria)
  
  ;Now UpDate List
  ForEach TableData()\Field()
    If TableData()\Field()\Name = Column
      DeleteElement(TableData()\Field())
      Break
    EndIf
  Next
  
  ;Show In Column List
  LoadColumnsList(GetGadgetText(cmbTables))

EndProcedure

Procedure CreateDatabase()
  
  Define FileName.s
  
  FileName = OpenFileRequester("Enter New Database Name","","Database (*.db)|*.db;*.db|All files (*.*)|*.*",0)

  
  If FileName
    
    ;Add .db Extension If No Extension
    If GetExtensionPart(FileName) = ""
      FileName = FileName + ".db"
    EndIf
    
    ;Create The File
    CreateFile(0,FileName)
    CloseFile(0)
    
    ;Clear All
    ClearGadgetItems(lsiColumns)
    ClearGadgetItems(cmbTables)
    
    CurrentDBName = GetFilePart(FileName,#PB_FileSystem_NoExtension)

    
    
    Open_Database(FileName)
    
    SetWindowTitle(winMain,"SQLite Simple Admin Editing  " + CurrentDBName)
    
    GetDatabasePragma()
    
    LoadTableCombo()
    
    ;Show First Column
    SetGadgetState(cmbTables,0)
    
    ;Show Fields
    If GetGadgetText(cmbTables) <> ""
      LoadColumnsList(GetGadgetText(cmbTables))
    EndIf
    
    ;Enable Gadgets
    DisableGadget(btnAddTable,#False)
    DisableGadget(btnRenameTable,#False)  
    DisableGadget(btnDeleteTable,#False)  
    DisableGadget(btnAddColumn,#False) 
    DisableGadget(btnRenameColumn,#False)  
    DisableGadget(btnDeleteColumn,#False)
  
  EndIf
  
EndProcedure

Procedure LoadDatabase()
  
  Define FileName.s
  
  FileName = OpenFileRequester("Choose Database To Load","","Database (*.db)|*.db;*.db|All files (*.*)|*.*",0)
  
  If FileName
    
    ;Clear All
    ClearGadgetItems(lsiColumns)
    ClearGadgetItems(cmbTables)
    
    CurrentDBName = GetFilePart(FileName,#PB_FileSystem_NoExtension)
    Open_Database(FileName)
    
    SetWindowTitle(winMain,"SQLite Simple Admin Editing  " + CurrentDBName)
    
    GetDatabasePragma()
    
    LoadTableCombo()
    
    ;Show First Column
    SetGadgetState(cmbTables,0)
    
    ;Show Fields
    If GetGadgetText(cmbTables) <> ""
      LoadColumnsList(GetGadgetText(cmbTables))
    EndIf
    
    ;Enable Gadgets
    DisableGadget(btnAddTable,#False)
    DisableGadget(btnRenameTable,#False)  
    DisableGadget(btnDeleteTable,#False)  
    DisableGadget(btnAddColumn,#False) 
    DisableGadget(btnRenameColumn,#False)  
    DisableGadget(btnDeleteColumn,#False)
    

    
  EndIf

EndProcedure

  winMain = OpenWindow(#PB_Any, 0, 0, 900, 320, "SQLite Simple Admin", #PB_Window_SystemMenu | #PB_Window_ScreenCentered)
  CreateMenu(0, WindowID(winMain))
  MenuTitle("Database")
  MenuItem(#mnuNewDatabase, "New")
  MenuItem(#mnuOpenDatabase, "Open")
  MenuBar()
  MenuItem(#mnuExportPB, "Export Code")
  MenuBar()
  MenuItem(#mnuExit, "Exit")
  lsiColumns = ListIconGadget(#PB_Any, 180, 40, 580, 240, "Name", 100, #PB_ListIcon_GridLines | #PB_ListIcon_FullRowSelect)
  AddGadgetColumn(lsiColumns,1,"Type",105)
  AddGadgetColumn(lsiColumns,2,"Not Null",100)
  AddGadgetColumn(lsiColumns,3,"Autoincrement",100)
  AddGadgetColumn(lsiColumns,4,"Primary Key",100)
  cmbTables = ComboBoxGadget(#PB_Any, 15, 40, 140, 25)
  btnAddTable = ButtonGadget(#PB_Any, 15, 80, 140, 25, "Add")  
  btnRenameTable = ButtonGadget(#PB_Any, 15, 120, 140, 25, "Rename")
  btnDeleteTable = ButtonGadget(#PB_Any, 15, 160, 140, 25, "Delete")
  btnAddColumn = ButtonGadget(#PB_Any, 770, 40, 100, 25, "Add")
  btnRenameColumn = ButtonGadget(#PB_Any, 770, 80, 100, 25, "Rename")
  btnDeleteColumn = ButtonGadget(#PB_Any, 770, 120, 100, 25, "Delete")  
  frmTables = FrameGadget(#PB_Any, 10, 10, 150, 280, " Tables ")
  frmColumns = FrameGadget(#PB_Any, 170, 10, 720, 280, " Columns ")

  DisableGadget(btnAddTable,#True)
  DisableGadget(btnRenameTable,#True)  
  DisableGadget(btnDeleteTable,#True)  
  DisableGadget(btnAddColumn,#True) 
  DisableGadget(btnRenameColumn,#True)  
  DisableGadget(btnDeleteColumn,#True)
  
  Repeat
      
      Event = WaitWindowEvent()
      Select Event
        Case #PB_Event_CloseWindow
          End
  
      Case #PB_Event_Menu
        Select EventMenu()
            
          Case #mnuNewDatabase
            
            If IsDatabase(CurrentDBID)
              CloseDatabase(CurrentDBID)
            EndIf 
            
            CreateDatabase()
            
            Debug "Start New Database"
            
          Case #mnuOpenDatabase
            
            If IsDatabase(CurrentDBID)
              CloseDatabase(CurrentDBID)
            EndIf            
            LoadDatabase()
            
          Case #mnuExportPB
            
            Debug "Export Pure Basic Code"
            ExportPBCode()
            
            
          Case #mnuExit
            
            If IsDatabase(CurrentDBID)
              CloseDatabase(CurrentDBID)
            EndIf
            
            End
            
        EndSelect
  
      Case #PB_Event_Gadget
        Select EventGadget()
            
          Case btnAddTable
            
            ;Add New Table
            AddTable()
            
          Case btnRenameTable
            
            ;Rename Table
            If GetGadgetText(cmbTables) <> ""
              RenameTable(GetGadgetText(cmbTables))
            EndIf
            
          Case btnDeleteTable
            
            ;Drop Table
            If GetGadgetText(cmbTables) <> ""
              DropTable(GetGadgetText(cmbTables))
            EndIf
            
          Case cmbTables
            
            ;Load Selected Tables Columns
            If GetGadgetText(cmbTables) <> ""
              LoadColumnsList(GetGadgetText(cmbTables))
            EndIf
            
          Case btnAddColumn

            AddColumn()
            
          Case btnRenameColumn

            If GetGadgetState(lsiColumns) > -1            
            
              ReNameColumn(GetGadgetItemText(lsiColumns,GetGadgetState(lsiColumns)))
              
            EndIf
            
          Case btnDeleteColumn
            
            If GetGadgetState(lsiColumns) > -1 
              
              If CountGadgetItems(lsiColumns) > 1
              
                DeleteColumn(GetGadgetItemText(lsiColumns,GetGadgetState(lsiColumns)))
              
              Else
              
                MessageRequester("Warning","A Table Must Have At Least One Column",#PB_MessageRequester_Ok|#PB_MessageRequester_Warning)
              
              EndIf
              
            EndIf
            
        EndSelect
    EndSelect
    
  ForEver
  
Warning!!!!!
The code should be seen as Alpha Test so before using backup your databases!

No warranty is given.

If anyone could test on Mac and Linux and\or suggest improvements to the code I would be gratefull.

Regards

CD
Any intelligent fool can make things bigger and more complex. It takes a touch of genius — and a lot of courage to move in the opposite direction.
User avatar
Kwai chang caine
Always Here
Always Here
Posts: 5342
Joined: Sun Nov 05, 2006 11:42 pm
Location: Lyon - France

Re: SQLite Simple Admin

Post by Kwai chang caine »

Hello CollectorDave
I have not try all the functions, but apparently that worlks here with W10 X64 / v5.70 X86
Thanks for sharing 8)
ImageThe happiness is a road...
Not a destination
collectordave
Addict
Addict
Posts: 1309
Joined: Fri Aug 28, 2015 6:10 pm
Location: Portugal

Re: SQLite Simple Admin

Post by collectordave »

Hi kwai

Thanks for the reply.

I have now tested all the functions on my large stamps database and all worked.

A little slow on manipulating the columns (Rename and delete) may look at adding a transaction for the data copy routines.

If anyone thinks it a good idea I can look at adding support for indexes and Views as well.

The other thing it shows is how to do a lot of the basic things with sqlite.

Kind regards

collectordave
Any intelligent fool can make things bigger and more complex. It takes a touch of genius — and a lot of courage to move in the opposite direction.
Post Reply