SQLite3 - Tutorial

Hier kannst du häufig gestellte Fragen/Antworten und Tutorials lesen und schreiben.
Benutzeravatar
ts-soft
Beiträge: 22292
Registriert: 08.09.2004 00:57
Computerausstattung: Mainboard: MSI 970A-G43
CPU: AMD FX-6300 Six-Core Processor
GraKa: GeForce GTX 750 Ti, 2 GB
Memory: 16 GB DDR3-1600 - Dual Channel
Wohnort: Berlin

SQLite3 - Tutorial

Beitrag von ts-soft »

SQLite3 - Tutorial für Einsteiger Teil 1

Das Sourceteil dieses Tutorials erstellt eine minimale Adressdatenbank, welche ich im zweiten Teil noch erweitern werde.
Die Erklärungen sind nicht alle vollständig, so das sich weitere Lektüre empfiehlt. Abgesehen vom Nachschlagen in der PureBasic.chm schlage ich noch folgende Internetseiten vor:

http://sql.1keydata.com/de/
http://www.sql-und-xml.de/sql-tutorial/

Adressen, welche ein einfaches Anführungszeichen enthalten, werden hier nicht berücksichtigt. Wer das benötigt sollte einfach alle ' durch '' ersetzen, ansonsten kommt eine Fehlermeldung.

Code: Alles auswählen

Text.s = ReplaceString(Text.s, "'", "''")
Hier der erste Teil des Tutorials:

Code: Alles auswählen

EnableExplicit

UseSQLiteDatabase()

#AdressDB = 0
#frm_Main = 0

Enumeration ; Gadgets
  #gad_Panel
  #gad_ListAnschrift
  #gad_Container
  #gad_strName
  #gad_strVorname
  #gad_strStrasse
  #gad_strPLZ
  #gad_strOrt
  #gad_strTelefon
  #gad_btnHinzufuegen
  #gad_btnAendern
  #gad_btnLoeschen
  #gad_btnSortieren
EndEnumeration

Declare CreateFormMain()
Declare DatenSatzHinzufuegen()
Declare DatenSatzAendern()
Declare DatenSatzLoeschen()
Declare ListFuellen()

Define db_name.s = GetTemporaryDirectory() + "Adressen.db"
Define SQL.s

; Wir überprüfen ob die DB bereits existiert, wenn nicht, wird eine leere DB erstellt.
If FileSize(db_name) <= 0
  If CreateFile(0, db_name)
    CloseFile(0)
    If OpenDatabase(#AdressDB, db_name, "", "")
      ; Wir erstellen die Tabelle adressen, hierfür ist die SQL-Anweisung:
      ; "CREATE TABLE name_der_tabelle (feldname [typ], feldname [typ], ...)" verantwortlich
      ; Der Typ des Feldes ist bei SQLite meist optional, deshalb werden wir diesen auch meist weglassen.
      ; Ausnahme von dieser Regel ist ein eindeutiger Identifier und ein Blob. Dieser ist immer vom Typ INTEGER, bzw. BLOB
      ; Syntax für ID-Feld: feldname INTEGER PRIMARY KEY AUTOINCREMENT
      ; Es empfiehlt sich die SQL-Anweisung auf mehrere Zeilen aufzuteilen.
      SQL = "CREATE TABLE adressen (id INTEGER PRIMARY KEY AUTOINCREMENT,"
      SQL + " name, vorname, strasse, plz, ort, telefon, notizen, fotodata BLOB, fotosize INTEGER)"
      
      ; Wenn wir kein Ergebnis benötigen, verwenden wir: DatabaseUpdate(id, SQL-Anweisung),
      ; ansonsten DatabaseQuery(id, SQL-Anweisung) was wir später noch sehen werden.
      If DatabaseUpdate(#AdressDB, SQL) = #False
        Debug DatabaseError()
        End
      EndIf
    Else
      Debug DatabaseError()
      End
    EndIf
  Else
    Debug db_name + " konnte nicht erstellt werden."
    End
  EndIf
Else ; Datenbank existiert bereits und wird geöffnet.
  If OpenDatabase(#AdressDB, db_name, "", "") = #False
    Debug DatabaseError()
    End
  EndIf
EndIf

; Nachdem wir unsere Datenbank geöffnet haben (egal ob leer oder bereits mit Inhalt)
; erstellen wir erstmal das Hauptfenster

CreateFormMain()

Define i

; Jetzt wird unser ListIconGadget mit Daten gefüllt
ListFuellen()

; Hier beginnt unser MainLoop.
Repeat
  Select WaitWindowEvent()
  
    Case #PB_Event_CloseWindow
      CloseDatabase(#AdressDB)
      Break
      
    Case #PB_Event_Gadget
      Select EventGadget()

        Case #gad_ListAnschrift
          Select EventType()
            Case #PB_EventType_Change
              i = GetGadgetState(#gad_ListAnschrift)
              If i > -1
                SetGadgetText(#gad_strName, GetGadgetItemText(#gad_ListAnschrift, i, 1))
                SetGadgetText(#gad_strVorname, GetGadgetItemText(#gad_ListAnschrift, i, 2))
                SetGadgetText(#gad_strStrasse, GetGadgetItemText(#gad_ListAnschrift, i, 3))
                SetGadgetText(#gad_strPLZ, GetGadgetItemText(#gad_ListAnschrift, i, 4))
                SetGadgetText(#gad_strOrt, GetGadgetItemText(#gad_ListAnschrift, i, 5))
                SetGadgetText(#gad_strTelefon, GetGadgetItemText(#gad_ListAnschrift, i, 6))
              Else
                SetGadgetText(#gad_strName, "")
                SetGadgetText(#gad_strVorname, "")
                SetGadgetText(#gad_strStrasse, "")
                SetGadgetText(#gad_strPLZ, "")
                SetGadgetText(#gad_strOrt, "")
                SetGadgetText(#gad_strTelefon, "")                
              EndIf
          EndSelect
        Case #gad_btnHinzufuegen ; Datensatz anfügen
          DatenSatzHinzufuegen()
        
        Case #gad_btnAendern
          If GetGadgetState(#gad_ListAnschrift)  > -1
            DatenSatzAendern()
          Else
            MessageRequester("SQLite Tutorial", "Bitte erst den zu ändernden Datensatz selektieren!")
          EndIf
          
        Case #gad_btnLoeschen
          If GetGadgetState(#gad_ListAnschrift)  > -1
            DatenSatzLoeschen()
          Else
            MessageRequester("SQLite Tutorial", "Bitte erst den zu löschenden Datensatz selektieren!")
          EndIf 
          
        Case #gad_btnSortieren
          ListFuellen()
      EndSelect
  EndSelect
ForEver
End

Procedure ListFuellen()
  ; Jetzt wird unser ListIconGadget mit Daten gefüllt
  ; hierfür ist die SQL-Anweisung SELECT zuständig. Das * steht für alles und hinter FROM wird die Tabelle angegeben
  ; ORDER BY feldname sorgt für eine Sortierung!
  Protected itemtext.s, i
  
  ClearGadgetItems(#gad_ListAnschrift)
  
  If DatabaseQuery(#AdressDB, "SELECT * FROM adressen ORDER BY name")
    While NextDatabaseRow(#AdressDB)
      itemtext = GetDatabaseString(#AdressDB, 0) ; unsere versteckte eindeutige ID
      For i = 1 To 6 ; name bis telefon!
        itemtext + #LF$ + GetDatabaseString(#AdressDB, i)
      Next
      AddGadgetItem(#gad_ListAnschrift, -1, itemtext)
    Wend
    FinishDatabaseQuery(#AdressDB); Diese Funktion ist immer nach einem Query auszuführen!
  Else
    Debug DatabaseError()
  EndIf
EndProcedure

Procedure DatenSatzHinzufuegen()
  Protected.s SQL, id, name, vorname, strasse, plz, ort, telefon, itemtext
  
  name = GetGadgetText(#gad_strName)
  vorname = GetGadgetText(#gad_strVorname)
  strasse = GetGadgetText(#gad_strStrasse)
  plz = GetGadgetText(#gad_strPLZ)
  ort = GetGadgetText(#gad_strOrt)
  telefon = GetGadgetText(#gad_strTelefon)
  
  ; Hierfür ist die SQL-Anweisung: "INSERT INTO ..." zuständig!
  SQL = "INSERT INTO adressen (name, vorname, strasse, plz, ort, telefon) "
  SQL + "VALUES ('"
  SQL + name + "','"
  SQL + vorname + "','"
  SQL + strasse + "','"
  SQL + plz + "','"
  SQL + ort + "','"
  SQL + telefon + "')"
  
  If DatabaseUpdate(#AdressDB, SQL) ; Eintragen in die DB.
    ; Jetzt benötigen wir noch die automatisch generierte ID um sie in unsere Liste einzutragen
    If DatabaseQuery(#AdressDB, "SELECT last_insert_rowid()")
      NextDatabaseRow(#AdressDB)
      id = GetDatabaseString(#AdressDB, 0)
      FinishDatabaseQuery(#AdressDB)
      
      itemtext = id + #LF$ + name + #LF$ + vorname + #LF$ + strasse + #LF$ + plz + #LF$ + ort + #LF$ + telefon 
      AddGadgetItem(#gad_ListAnschrift, -1, itemtext)
    EndIf
  Else
    Debug DatabaseError()
  EndIf
EndProcedure

Procedure DatenSatzAendern()
  Protected.s SQL, id, name, vorname, strasse, plz, ort, telefon, itemtext
  Protected item
  
  name = GetGadgetText(#gad_strName)
  vorname = GetGadgetText(#gad_strVorname)
  strasse = GetGadgetText(#gad_strStrasse)
  plz = GetGadgetText(#gad_strPLZ)
  ort = GetGadgetText(#gad_strOrt)
  telefon = GetGadgetText(#gad_strTelefon)
  
  ; ID des selektierten Eintrags ermitteln
  item = GetGadgetState(#gad_ListAnschrift)
  id = GetGadgetItemText(#gad_ListAnschrift, item, 0)

  ; Zum ändern nutzen wir die SQL-Anweisung: "UPDATE tabellenname SET"
  SQL = "UPDATE adressen SET "
  SQL + "name = '" + name + "',"
  SQL + "vorname = '" + vorname + "',"
  SQL + "strasse = '" + strasse + "',"
  SQL + "plz = '" + plz + "',"
  SQL + "ort = '" + ort + "',"
  SQL + "telefon = '" + telefon + "' "
  SQL + "WHERE id = " + id

  If DatabaseUpdate(#AdressDB, SQL) = #False
    Debug DatabaseError()
  Else
    SetGadgetItemText(#gad_ListAnschrift, item, name, 1)
    SetGadgetItemText(#gad_ListAnschrift, item, vorname, 2)
    SetGadgetItemText(#gad_ListAnschrift, item, strasse, 3)
    SetGadgetItemText(#gad_ListAnschrift, item, plz, 4)
    SetGadgetItemText(#gad_ListAnschrift, item, ort, 5)
    SetGadgetItemText(#gad_ListAnschrift, item, telefon, 6)
  EndIf
EndProcedure

Procedure DatenSatzLoeschen()
  Protected.s SQL, id
  Protected item
  
  item = GetGadgetState(#gad_ListAnschrift)
  id = GetGadgetItemText(#gad_ListAnschrift, item, 0)
  SQL = "DELETE FROM adressen WHERE id = " + id
  
  If DatabaseUpdate(#AdressDB, SQL) = #False
    Debug DatabaseError()
  Else
    RemoveGadgetItem(#gad_ListAnschrift, item)
  EndIf
EndProcedure

Procedure CreateFormMain()
  Protected ListIconFlags = #PB_ListIcon_GridLines | #PB_ListIcon_FullRowSelect
  CompilerIf #PB_Compiler_OS = #PB_OS_Windows
    ListIconFlags | #PB_ListIcon_AlwaysShowSelection
  CompilerEndIf
  
  OpenWindow(#frm_Main, #PB_Ignore, #PB_Ignore, 640, 480, "Adressbuch")
  PanelGadget(#gad_Panel, 0, 0, 640, 415)
    AddGadgetItem(#gad_Panel, -1, "Adressen")
      ListIconGadget(#gad_ListAnschrift, 5, 5, GetGadgetAttribute(#gad_Panel, #PB_Panel_ItemWidth) - 10, GetGadgetAttribute(#gad_Panel, #PB_Panel_ItemHeight) - 10, "id", 1, ListIconFlags)
      ; die erste Spalte verwaltet die eindeutige ID und ist nicht sichtbar! (groesse von 0 ist unter Linux nicht erlaubt, deshalb 1)
      AddGadgetColumn(#gad_ListAnschrift, 1, "Name", 95)
      AddGadgetColumn(#gad_ListAnschrift, 2, "Vorname", 95)
      AddGadgetColumn(#gad_ListAnschrift, 3, "Strasse", 160)
      AddGadgetColumn(#gad_ListAnschrift, 4, "PLZ", 45)
      AddGadgetColumn(#gad_ListAnschrift, 5, "Ort", 125)
      AddGadgetColumn(#gad_ListAnschrift, 6, "Telefon", 90)
  CloseGadgetList()
  ContainerGadget(#gad_Container, 5, 420, 630, 60)
  StringGadget(#gad_strName, 5, 0, 95, 25, "")
  StringGadget(#gad_strVorname, 100, 0, 95, 25, "")
  StringGadget(#gad_strStrasse, 195, 0, 160, 25, "")
  StringGadget(#gad_strPLZ, 355, 0, 45, 25, "")
  StringGadget(#gad_strOrt, 400, 0, 125, 25, "")
  StringGadget(#gad_strTelefon, 525, 0, 95, 25, "")
  ButtonGadget(#gad_btnHinzufuegen, 5, 30, 100, 25, "Hinzufügen")
  ButtonGadget(#gad_btnAendern, 115, 30, 80, 25, "Ändern")
  ButtonGadget(#gad_btnLoeschen, 205, 30, 80, 25, "Löschen")
  ButtonGadget(#gad_btnSortieren, 540, 30, 80, 25, "Sortieren")
  CloseGadgetList()
EndProcedure
Der zweite Teil folgt in den nächsten Tagen. Nicht wundern über die 3 ungenutzten Felder in der Tabelle.

Ich hoffe der Code ist verständlich und ermöglicht Euch einen Einstieg in die Datenbankprogrammierung mit SQLite und PureBasic.

Have Fun
PureBasic 5.73 LTS | SpiderBasic 2.30 | Windows 10 Pro (x64) | Linux Mint 20.1 (x64)
Nutella hat nur sehr wenig Vitamine. Deswegen muss man davon relativ viel essen.
Bild
Benutzeravatar
Captn. Jinguji
Beiträge: 395
Registriert: 07.06.2005 19:47
Computerausstattung: PB 4.73x64, i7, WIN 10x64, ATI NVidia RTX 2070
Wohnort: Witten

Re: SQLite3 - Tutorial

Beitrag von Captn. Jinguji »

Super-Vorlage, um SQL mit PB zu machen.

Für das Verstehen von SQL an sich gefällt mir GALAXQL immer noch am anschaulichsten!
Ist das Kunst hier, oder kann das weg ?
Benutzeravatar
ts-soft
Beiträge: 22292
Registriert: 08.09.2004 00:57
Computerausstattung: Mainboard: MSI 970A-G43
CPU: AMD FX-6300 Six-Core Processor
GraKa: GeForce GTX 750 Ti, 2 GB
Memory: 16 GB DDR3-1600 - Dual Channel
Wohnort: Berlin

Re: SQLite3 - Tutorial

Beitrag von ts-soft »

SQLite3 - Tutorial für Einsteiger Teil 2

Hier wie versprochen, der zweite Teil. Dieser Teil zeigt die Verwendung von Blobs auf. Blobs sind binäre Daten
jeglicher Art, also Bilder, ausführbare Programme usw.

Ursprünglich hatte ich geplant, die Bilderdaten in das Programm des ersten Teiles zu integrieren, aber das er-
schien mir doch zu unübersichtlich. In diesem Teil sind nur die Dinge erläutert, die Neu sind. Dabei handelt es
sich nur um die ersten beiden Proceduren, Bild zur Datenbank hinzufügen und Bild aus Datenbank anzeigen.

Wenn Ihr den Source ausführt, braucht Ihr nur Bilddateien (bmp, jpg, png), aus dem Explorer oder ähnlich, auf
das rechte leere Feld droppen.

Code: Alles auswählen

EnableExplicit

UseSQLiteDatabase()
UseJPEGImageDecoder()
UsePNGImageDecoder()

#Picture_DB = 0
#frm_Main   = 0
#gad_List   = 0
#gad_Image  = 1

Enumeration ; Spalten
  #row_ID
  #row_Name
  #row_FotoData
EndEnumeration

Procedure ZeigeBild(id)
  Protected size, *mem
  
  ; Als erstes wählen (SELECT) wir den gewünschten Datensatz aus.
  ; Diesen bestimmen wir durch den eindeutigen AUTOINCREMENT Wert id,
  ; welchen wir in dem ListView in GadgetItemData speicherten.
  If DatabaseQuery(#Picture_DB, "SELECT * FROM picture WHERE id = " + Str(id))
    NextDatabaseRow(#Picture_DB)
    ; Als erstes ermitteln wir die Grösse in Bytes,
    ; diese muss nicht unbedingt extra in einem Feld der DB gespeichert werden,
    ; und reservieren einen entsprechend grossen Speicher.
    size = DatabaseColumnSize(#Picture_DB, #row_FotoData)
    *mem = AllocateMemory(size)
    If *mem
      ; Mit GetDatabaseBlob laden wir nun die Bilddaten in unseren Speicher.
      If GetDatabaseBlob(#Picture_DB, #row_FotoData, *mem, size)
        ; Der Rest hier sollte eigentlich jedem klar sein ;-)
        CatchImage(0, *mem)
        SetGadgetState(#gad_Image, ImageID(0))
      EndIf
      FreeMemory(*mem)
    EndIf 
    FinishDatabaseQuery(#Picture_DB)
  Else
    Debug DatabaseError()
  EndIf
EndProcedure

Procedure BildHinzufuegen(name.s)
  Protected *mem, size, sql.s, id, item
  
  ; Um ein Bild hinzuzufügen, müssen wir es erst in den Speicher laden.
  If ReadFile(0, name)
    size = Lof(0)
    If size
      *mem = AllocateMemory(size)
      If *mem
        ReadData(0, *mem, size)
        ; SetDatabaseBlob bereitet die Daten für unseren DatabaseUpdate vor.
        ; Der zweite parameter ist nicht die Spalte (row), sondern der Index des Statements,
        ; also der wievielte Blob (das wievielte Fragezeichen). Dieser Index beginnt bei 0!
        SetDatabaseBlob(#Picture_DB, 0, *mem, size)
        ; INSERT INTO kennen wir bereits aus dem ersten Teil.
        ; Neu ist hier lediglich, das nicht ein Wert als String übergeben wird,
        ; sondern nur ein Fragezeichen (?)!
        sql = "INSERT INTO picture (name, fotodata) VALUES ('"
        sql + GetFilePart(name) + "', ?)" 
        If DatabaseUpdate(#Picture_DB, sql)
          ; Hier ermitteln wir noch die eindeutige ID zur Weiterverwendung!
          If DatabaseQuery(#Picture_DB, "SELECT last_insert_rowid()")
            NextDatabaseRow(#Picture_DB)
            id = GetDatabaseLong(#Picture_DB, 0)
            FinishDatabaseQuery(#Picture_DB)
            item = CountGadgetItems(#gad_List)
            AddGadgetItem(#gad_List, item, GetFilePart(name))
            SetGadgetItemData(#gad_List, item, id)
            SetGadgetState(#gad_List, item)
            ZeigeBild(id)
          EndIf     
        Else
          Debug DatabaseError()
        EndIf
        FreeMemory(*mem)
      EndIf
    EndIf
    CloseFile(0)
  EndIf
EndProcedure

Procedure ListFuellen()
  Protected id, i = -1
  ClearGadgetItems(#gad_List)
  
  If DatabaseQuery(#Picture_DB, "SELECT * FROM picture ORDER BY name")
    While NextDatabaseRow(#Picture_DB)
      i + 1
      id = GetDatabaseLong(#Picture_DB, #row_ID)
      AddGadgetItem(#gad_List, i, GetDatabaseString(#Picture_DB, #row_Name))
      SetGadgetItemData(#gad_List, i, id)
    Wend
    FinishDatabaseQuery(#Picture_DB)
    If i > -1
      SetGadgetState(#gad_List, 0)
      ZeigeBild(GetGadgetItemData(#gad_List, 0))
    EndIf
  Else
    Debug DatabaseError()
  EndIf
EndProcedure

Procedure CreateFormMain()
  OpenWindow(#frm_Main, #PB_Ignore, #PB_Ignore, 640, 480, "Fotoalbum")
  ListViewGadget(#gad_List, 5, 5, 200, 470)
  ImageGadget(#gad_Image, 210, 5, 420, 465, 0, #PB_Image_Border)
  GadgetToolTip(#gad_Image, "Neue Bilddatei bitte hier droppen")
  EnableGadgetDrop(#gad_Image, #PB_Drop_Files, #PB_Drag_Copy | #PB_Drag_Move)
EndProcedure

Define db_name.s = GetTemporaryDirectory() + "Picture.db"
Define SQL.s

If FileSize(db_name) <= 0
  If CreateFile(0, db_name)
    CloseFile(0)
    If OpenDatabase(#Picture_DB, db_name, "", "")

      SQL = "CREATE TABLE picture (id INTEGER PRIMARY KEY AUTOINCREMENT, name, fotodata)"

      If DatabaseUpdate(#Picture_DB, SQL) = #False
        Debug DatabaseError()
        End
      EndIf
    Else
      Debug DatabaseError()
      End
    EndIf
  Else
    Debug db_name + " konnte nicht erstellt werden."
    End
  EndIf
Else
  If OpenDatabase(#Picture_DB, db_name, "", "") = #False
    Debug DatabaseError()
    End
  EndIf
EndIf

CreateFormMain()
ListFuellen()

Define.s Files, File, Ext
Define i, j

Repeat
  Select WaitWindowEvent()
    Case #PB_Event_CloseWindow
      CloseDatabase(#Picture_DB)
      Break
    
    Case #PB_Event_Gadget
      If EventGadget() = #gad_List And EventType() = #PB_EventType_LeftClick
        i = GetGadgetState(#gad_List)
        If i >= 0
          ZeigeBild(GetGadgetItemData(#gad_List, i))
        EndIf
      EndIf
      
    Case #PB_Event_GadgetDrop
      If EventGadget() = #gad_Image
        Files = EventDropFiles()
        j = CountString(Files, Chr(10)) + 1
        For i = 1 To j
          File = StringField(Files, i, Chr(10))
          Ext = LCase(GetExtensionPart(File))
          Select Ext
            Case "bmp", "jpg", "png"
              BildHinzufuegen(File)
            Default
              Debug File + " enthält kein unterstütztes Bildformat!"
          EndSelect
         Next i
      EndIf
  EndSelect
ForEver
Ich denke mal, die Grundlagen sind jetzt komplett. Wichtig ist, das Ihr die verwendeten SQL-Anweisungen,
auf den, im ersten Beitrag geposteten, Links nachlest.

Sollte noch etwas fehlen, was euch wichtig erscheint, werde ich es gerne Nachreichen. Es sollte aber in den
Rahmen: "Grundlagen SQLite3 mit PureBasic" passen

Viel Spaß beim ausprobieren

Thomas
PureBasic 5.73 LTS | SpiderBasic 2.30 | Windows 10 Pro (x64) | Linux Mint 20.1 (x64)
Nutella hat nur sehr wenig Vitamine. Deswegen muss man davon relativ viel essen.
Bild
Antworten