SQL Delete Row

[Inner]

Problem;
Delete requires a condition for example where something is equal to a value in order for the requirements to be met for deletion, I don't have a row id in my table and even if it did it's redundant anyhow as if say, you stated delete id 4 .. ids 1 2 3 5 would still exist, (unless you renumbered the sequence), but the listicon gadget will return a selected state of 4 still and thus 5 can never be deleted.

The question is how do you delete a row from a table without any prerequisite questioning, just straight delete this row # from table without the Spanish inquisition of questioning.

[Inner]

maybe.. never mind

Code: Select all

DELETE FROM "main"."UserTypesDef" WHERE _rowid_ IN ('5');

maybe..

[mk-soft]

DELETE FROM table WHERE recid = number

A little many more code, but ready for testing.

Code: Select all

;-TOP

EnableExplicit

; ***************************************************************************************

; ShowDatabaseItems by mk-soft from 12.02.2017

Procedure GetTextWidth(Text.s, FontID.i = 0)
  Static image
  Protected result
  
  If Not image
    image = CreateImage(#PB_Any, 1, 1)
  EndIf
  
  If image And StartDrawing(ImageOutput(image))
    If FontID
      DrawingFont(FontID)
    EndIf
    result = TextWidth(Text)
    StopDrawing()
  EndIf
  ProcedureReturn result
EndProcedure

; ---------------------------------------------------------------------------------------

Procedure ClearGadgetColumns(Gadget)
  CompilerIf #PB_Compiler_Version <= 551
    ClearGadgetItems(Gadget)
    While GetGadgetItemText(Gadget, -1, 0)
      RemoveGadgetColumn(Gadget, 0)
    Wend
  CompilerElse
    RemoveGadgetColumn(Gadget, #PB_All)
  CompilerEndIf
EndProcedure

; ---------------------------------------------------------------------------------------

Procedure CountGadgetColumns(Gadget)
  Protected result
  CompilerIf #PB_Compiler_Version <= 551
    While GetGadgetItemText(Gadget, -1, result)
      result + 1
    Wend
  CompilerElse
    result = GetGadgetAttribute(Gadget, #PB_ListIcon_ColumnCount)
  CompilerEndIf
  ProcedureReturn result
EndProcedure

; ---------------------------------------------------------------------------------------

Procedure FitColumnWidth(Gadget, Column = #PB_All)
  Protected columns, rows, col, row, width, len, max, dx
  
  CompilerIf #PB_Compiler_OS = #PB_OS_Windows
      If Column = #PB_All
        columns = CountGadgetColumns(Gadget) - 1
        For col = 0 To columns
          SendMessage_(GadgetID(Gadget), #LVM_SETCOLUMNWIDTH, col, #LVSCW_AUTOSIZE_USEHEADER)
        Next
      Else
        SendMessage_(GadgetID(Gadget), #LVM_SETCOLUMNWIDTH, Column, #LVSCW_AUTOSIZE_USEHEADER)
      EndIf
      
    CompilerElse
      rows = CountGadgetItems(Gadget) - 1
      dx = GetTextWidth("X", GetGadgetFont(Gadget))
      If Column = #PB_All
        columns = CountGadgetColumns(Gadget) - 1
        For col = 0 To columns
          max = 0
          For row = -1 To rows
            len = Len(GetGadgetItemText(Gadget, row, col))
            If len > max
              max = len
            EndIf
          Next
          width = max * dx + 4
          SetGadgetItemAttribute(Gadget, 0, #PB_ListIcon_ColumnWidth, width, col)
        Next
      Else
        For row = -1 To rows
          len = Len(GetGadgetItemText(Gadget, row, Column))
          If len > max
            max = len
          EndIf
        Next
        width = max * dx + 4
        SetGadgetItemAttribute(Gadget, 0, #PB_ListIcon_ColumnWidth, width, Column)
      EndIf
     
  CompilerEndIf
  
EndProcedure

Procedure ShowDatabaseRows(Gadget, DBase, Hide = #False)
  Protected result.i, columns.i, index.i, size.i, text.s
  
  Repeat
    If GadgetType(Gadget) <> #PB_GadgetType_ListIcon
      Break
    EndIf
    If Not IsDatabase(DBase)
      Break
    EndIf
    HideGadget(Gadget, Hide)
    ClearGadgetColumns(Gadget)
    columns = DatabaseColumns(DBase)
    For index = 0 To columns - 1
      text = DatabaseColumnName(DBase, index)
      size = GetTextWidth(text) + 12
      AddGadgetColumn(Gadget, index, text, size)
    Next
    While NextDatabaseRow(DBase)
      text = ""
      For index = 0 To columns - 1
        text + GetDatabaseString(DBase, index) + #LF$
      Next
      AddGadgetItem(Gadget, -1, text)
    Wend
    FinishDatabaseQuery(DBase)
    HideGadget(Gadget, #False)
    result = CountGadgetItems(Gadget)
  Until #True
  ProcedureReturn result
  
EndProcedure

; ***************************************************************************************

CompilerIf #PB_Compiler_IsMainFile
  
  ; Constant
  Enumeration ;Window
    #Main
  EndEnumeration
  
  Enumeration ; Menu
    #Menu
  EndEnumeration
  
  Enumeration ; MenuItems
    #MenuExitApplication
  EndEnumeration
  
  Enumeration ; Gadgets
    #List
    #Edit
  EndEnumeration
  
  Enumeration ; Statusbar
    #Status
  EndEnumeration
  
  ; Global Variable
  Global ExitApplication
  
  ; Functions
  
  UseSQLiteDatabase()
  
  Procedure CheckDatabaseUpdate(Database, Query$)
    Protected Result = DatabaseUpdate(Database, Query$)
    If Result = 0
      Debug DatabaseError()
    EndIf
    ProcedureReturn Result
  EndProcedure
  
  Procedure CreateDummyDatabase(DBase)
    If OpenDatabase(DBase, ":memory:", "", "")
      CheckDatabaseUpdate(DBase, "CREATE TABLE food (recid INTEGER PRIMARY KEY ASC, name CHAR(50), weight FLOAT)")
      CheckDatabaseUpdate(DBase, "INSERT INTO food (name, weight) VALUES ('apple', '10.005')")
      CheckDatabaseUpdate(DBase, "INSERT INTO food (name, weight) VALUES ('pear', '5.9')")
      CheckDatabaseUpdate(DBase, "INSERT INTO food (name, weight) VALUES ('banana', '20.35')")
    Else
      Debug "Can't open database !"
    EndIf
  EndProcedure
  
  Procedure UpdateWindow()
    Protected x, y, dx, dy, menu, status
    
    menu = MenuHeight()
    If IsStatusBar(#Status)
      status = StatusBarHeight(#Status)
    Else
      status = 0
    EndIf
    x = 0
    y = 0
    dx = WindowWidth(#Main)
    dy = WindowHeight(#Main) - menu - status
    ResizeGadget(#List, x, y, dx, dy)
  EndProcedure
  
  ; Main
  Procedure Main()
    Protected event, style, dx, dy, count
    Protected row, recid.s
    
    style = #PB_Window_SystemMenu | #PB_Window_MinimizeGadget | #PB_Window_MaximizeGadget | #PB_Window_SizeGadget
    
    If OpenWindow(#Main, #PB_Ignore, #PB_Ignore, 800, 600, "Main", style)
      
      ; Menu
      CreateMenu(#Menu, WindowID(#Main))
      MenuTitle("Ablage")
      MenuItem(#MenuExitApplication, "Be&enden")
      
      CompilerIf #PB_Compiler_OS = #PB_OS_MacOS 
        ; Mac default menu
        If Not IsMenu(#Menu)
          CreateMenu(#Menu, WindowID(#Main))
        EndIf
        MenuItem(#PB_Menu_About, "")
        MenuItem(#PB_Menu_Preferences, "")
      CompilerEndIf
      
      ; Statusbar
      CreateStatusBar(#Status, WindowID(#Main))
      AddStatusBarField(#PB_Ignore)
      
      ; Gadgets
      dx = WindowWidth(#Main)
      dy = WindowHeight(#Main) - StatusBarHeight(#Status) - MenuHeight()
      ListIconGadget(#List, 0, 0, dx, dy, "recid", 100)
      
      ;-Test database
      CreateDummyDatabase(0)
      If DatabaseQuery(0, "SELECT * FROM food")
      ;If DatabaseQuery(0, "SELECT * FROM sqlite_master")
        count = ShowDatabaseRows(#List, 0, #True)
        StatusBarText(#Status, 0, "Items: " + count)
      EndIf
      
      FitColumnWidth(#List)
      
      ; Bind Evnts
      BindEvent(#PB_Event_SizeWindow, @UpdateWindow(), #Main)
      
      ; Main Loop
      Repeat
        event = WaitWindowEvent()
        Select event
          Case #PB_Event_Menu
            Select EventMenu()
                CompilerIf #PB_Compiler_OS = #PB_OS_MacOS
                  
                Case #PB_Menu_About
                  
                Case #PB_Menu_Preferences
                  
                Case #PB_Menu_Quit
                  ExitApplication = #True
                  
                CompilerEndIf
                
              Case #MenuExitApplication
                ExitApplication = #True
                
            EndSelect
            
          Case #PB_Event_Gadget
            Select EventGadget()
              Case #List
                Select EventType()
                  Case #PB_EventType_LeftDoubleClick
                    row = GetGadgetState(#List)
                    recid.s = GetGadgetItemText(#List, row, 0)
                    If MessageRequester("Stop", "Delete recid " + recid + " from food?", #PB_MessageRequester_YesNo) = #PB_MessageRequester_Yes
                      CheckDatabaseUpdate(0, "DELETE FROM food WHERE recid = " + recid)
                      If AffectedDatabaseRows(0)
                        If DatabaseQuery(0, "SELECT * FROM food")
                          count = ShowDatabaseRows(#List, 0, #True)
                          StatusBarText(#Status, 0, "Items: " + count)
                        EndIf
                      EndIf
                    EndIf
                    
                EndSelect
                
            EndSelect
            
          Case #PB_Event_CloseWindow
            Select EventWindow()
              Case #Main
                ExitApplication = #True
                
            EndSelect
            
        EndSelect
        
      Until ExitApplication
      
    EndIf
    
  EndProcedure : Main()
  
  End
  
CompilerEndIf

[Inner]

"CREATE TABLE food (recid INTEGER PRIMARY KEY ASC, name CHAR(50), weight FLOAT)"

It's actually a created table field, so doesn't quite work.. I was wrong above _rowid_ or any kind of WHERE is always in reference to a created table entrance.

Unless your saying we must have 'recid INTEGER PRIMARY KEY ASC' as an indexing method, with such must be visible in a ListIconGadget as deleting an item from a list would require reading the column row text to derive the 'recid' to give sqlite the correct record to delete.

The above I was hoping to void as I'd rather not have a bunch of meaningless unusable data in my ListIconGadget.

[mk-soft]

In any case, you need a recid as the primary key in a table to process data uniquely.
You can also hide/store this with Get/SetGadgetItemData.

[Inner]

In any case, you need a recid as the primary key in a table to process data uniquely.
You can also hide/store this with Get/SetGadgetItemData.

'SetGadgetItemData' true that I was just trying to remember what the command was.

[Inner]

oddly;
"DELETE FROM UserTypesDef WHERE unqueid = 1" -- works perfectly in "DB Browser"

but not from

Code: Select all

  del.s = "DELETE FROM "+table+" WHERE unqueid = "+Str(row)
  
  Debug DatabaseQuery(dbidx, del)

Weird!

[skywalk]

SQLITE added WITHOUTROWID tables in case you have your own primary keys.

[Kiffi]

but not from

Code: Select all

  del.s = "DELETE FROM "+table+" WHERE unqueid = "+Str(row)
  Debug DatabaseQuery(dbidx, del)

Here you need DatabaseUpdate(); not DatabaseQuery()

[Inner]

but not from

Code: Select all

  del.s = "DELETE FROM "+table+" WHERE unqueid = "+Str(row)
  Debug DatabaseQuery(dbidx, del)

Here you need DatabaseUpdate(); not DatabaseQuery()

So that's why it wasn't working, I swear somedays.. I just wake up stupid.

[Inner]

And that's what you was helping with thanks all.
https://www.purebasic.fr/english/viewtopic.php?t=83056