Demande d'informations sur utilisation SQLite

[cage]

Bonjour a tous,
Je gère plusieurs bases d'informations a partir de List et de Map et de Structures pour les afficher ensuite dans une ListIconGadget()
Ces bases sont au format texte avec un formatage particulier que je lis ligne a ligne pour en tirer les informations voulues.
La plus grosse base contient environ 200.000 enregistrements et fait environ 60Mo
A chaque fois que je lance mon application, je suis obligé de relire une des bases pour l'afficher et idem si je change de base.
Pour éviter cette relecture des bases systématiques, je voudrais créer une database SQLite pour chaque base texte

Ma question: SQLite est-il suffisant et assez rapide pour supporter 200.000 enregistrements ?

Merci pour votre éclairage.
cage

[falsam]

Ma question: SQLite est-il suffisant et assez rapide pour supporter 200.000 enregistrements ?

C'est de la rigolade pour SQlite

Le nombre d'enregistrements théorique est de 2 puissance 64.
Lien https://www.sqlite.org/limits.html#:~:t ... tle%20data.

[Ar-S]

C'est clairement le rôle d'une BDD

[cage]

Merci pour votre retour.
Je vais donc me lancer dans l'aventure SQLite pour gérer les données.
cage

[cage]

Bonsoir a tous,
Je rencontre un petit soucis dans la création de ma data base avec les données a insérer qui comportent un apostrophe (').
J'obtiens l'erreur suivante

Code : Tout sélectionner

L'Equipe ...
near "Equipe": syntax error

Avez-vous une idée de comment contourner ce soucis, car mes données contiennent beaucoup d'apostrophes.
cage

[cage]

Je sais qu'on peut doubler chaque apostrophe dans la donnée a insérer dans la database.

Code : Tout sélectionner

myData$=ReplaceString(MyData$,"'","''")

et que ça corrige le problème.
Je me demandais s'il n'y avait pas une autre solution.
cage

[Marc56]

PB a implémenté depuis la v5.40 les variable de liaison (requêtes préparées),en anglais: Prepared statement
Cela permet entre autre de saisir des données avec apostrphes, guillements, saut de ligne etc sans s'occuper de rien.
Pour une chaine: SetDatabaseString()
Example pour SQLite et PB (tiré de l'aide sur DatabaseQuery())

Code : Tout sélectionner

  SetDatabaseString(#Database, 0, "test")  
  If DatabaseQuery(#Database, "SELECT * FROM employee WHERE id=?")  

[cage]

@Marc56
Je dois être #Null car je n'ai rien compris aux exemples.
Voici un code fonctionnel pour expliquer mon problème:

Code : Tout sélectionner

EnableExplicit

UseSQLiteDatabase()

Define IN, OUT
Define entry$
Define CHAMP1$, CHAMP2$, CHAMP3$, CHAMP4$, DATAS.s
Define DatabaseIN$  = "database-test.txt"
Define DatabaseOUT$ = "database-test.db"

Define QUERY_CREATE.s="CREATE TABLE TEST ("
       QUERY_CREATE.s+"CHAMP1 VARCHAR,"
       QUERY_CREATE.s+"CHAMP2 VARCHAR,"
       QUERY_CREATE.s+"CHAMP3 VARCHAR,"
       QUERY_CREATE.s+"CHAMP4 VARCHAR)"

Define QUERY_DELETE.s="DROP TABLE IF EXISTS TEST"

Procedure _DatabaseUpdate(Database, Query$)
  If DatabaseUpdate(Database, Query$)
    ProcedureReturn #True
  Else
    Debug DatabaseError()
    ProcedureReturn #False
  EndIf
EndProcedure

OUT = CreateFile(#PB_Any, DatabaseOUT$)
If OUT
  CloseFile(OUT)
Else
  Debug "Impossible de créer la database "+DatabaseOUT$
  End
EndIf

IN = ReadFile(#PB_Any, DatabaseIN$)
If IN
  OUT = OpenDatabase(#PB_Any, DatabaseOUT$, #Empty$, #Empty$)
  If OUT
    _DatabaseUpdate(OUT, QUERY_DELETE.s)
    _DatabaseUpdate(OUT, QUERY_CREATE.s)
    ;;; === LECTURE DONNÉES TEXTE ===
    While Eof(IN) = 0
      entry$ = Trim(ReadString(IN))
      If entry$ = #Null$
        Continue
      EndIf
      ;
      ; traitement des entrées.
      ;
      CHAMP1$="résultat du traitement pour champ1"
      CHAMP2$="résultat du traitement pour champ2"
      CHAMP3$="résultat du traitement pour champ3"
      CHAMP4$="résultat du traitement pour 'champ4'"
      ;
      CHAMP4$=ReplaceString(CHAMP4$,"'","''")
      ;
      DATAS.s= "'"+CHAMP1$+"','"+CHAMP2$+"','"+CHAMP3$+"','"+CHAMP4$+"'"
      ;
      _DatabaseUpdate(OUT, "INSERT INTO TEST (Champ1, Champ2, Champ3, Champ4) VALUES ("+DATAS+")")
    Wend
    ;;; === FIN LECTURE DONNÉES TEXTE ===
    CloseDatabase(OUT)
  Else
    Debug "Impossible d'ouvrir "+DatabaseOUT$+" en écriture !"
  EndIf
  CloseFile(IN)
Else
  Debug "Impossible d'ouvrir "+DatabaseIN$+" en lecture !"
EndIf

Lecture ligne a ligne du fichier IN et écriture des champs dans le fichier OUT
Après traitement de la ligne en cours, je me retrouve avec 4 champs dont je ne sais pas s'ils contiennent des apostrophes.
Dans l'exemple, j'ai mis 2 apostrophes dans le champ4
Si on commente le ReplaceString() du champ4 alors on obtient une erreur et pas d'enregistrement des données.
Comment traiterais-tu ce problème avec les informations que tu m'a donnés ?
Autre question, dans mon cas, toutes les données sont du texte de longueur variable < 254 caractères.
Que vaut-il mieux utiliser, CHAR, VARCHAR ou TEXT dans la déclaration des champs.
Merci pour ton aide.
cage

[Ar-S]

Je n'ai pas testé ton code mais déjà, tu peux utiliser VARCHAR plutôt que CHAR
La différence étant si je ne me trompe pas que CHAR va alouer une longueur FIXE alors que VARCHAR va allouer une valeur MAX.

En gros si tu VARCHAR (254) et que tu mets "TOTO", ça prendra la place de TOTO en mémoire
si tu CHAR (254) et que tu mets "TOTO", ça prendra tout l'espace (254) en mémoire.

Char est plus rapide mais il ne faut l'utiliser que si les données de ta colonne sont toujours de la même taille.

[Marc56]

Si on commente le ReplaceString() du champ4 alors on obtient une erreur et pas d'enregistrement des données.

Personne n'est #Null, simplement on reste souvent trop concentré sur un seul point.
Il faut user et abuser de Debug et ShowVariableViewer() et voir ce que programme "voit"
Si tu ajoutes l'affichage de la requête avant envoi:

Code : Tout sélectionner

      DATAS.s= "'"+CHAMP1$+"','"+CHAMP2$+"','"+CHAMP3$+"','"+CHAMP4$+"'"
      ;
      Define SQL$ = "INSERT INTO TEST (Champ1, Champ2, Champ3, Champ4) VALUES ("+DATAS+")"
      Debug SQL$
      _DatabaseUpdate(OUT, SQL$)

Tu verra l'erreur: la requête trouve du texte après la fermeture de la chaine. Champ4. Le premier ' autour de champ4 ferme la chaine donc la suite semble en trop.

Code : Tout sélectionner

... 'résultat du traitement pour 'champ4'')
near "champ4": syntax error

d'où le message 'erreur de syntaxe proche de "champ4"
D'où l'intérêt des Prepared statement.

Suite:
À la base SQLite ne gère que 5 type de données TEXT, NUMERIC, INTEGER, REAL, BLOB. Sauf en mode stricte, mais pour l'instant ne t'en occupe pas, mets TEXT pour tout ce qui est du texte. Il se débrouille pour allouer l'espace comme il faut.
https://www.sqlite.org/datatype3.html

La partie Prepared statement se traiterait comme ceci

Code : Tout sélectionner

      SetDatabaseString(OUT, 0, "résultat du traitement pour champ1")
      SetDatabaseString(OUT, 1, "résultat du traitement pour champ2")
      SetDatabaseString(OUT, 2, "résultat du traitement pour champ3")
      SetDatabaseString(OUT, 3, "résultat du traitement pour 'champ4'")

      _DatabaseUpdate(OUT, "INSERT INTO TEST (Champ1, Champ2, Champ3, Champ4) VALUES (?, ?, ?, ?)")

Chaque '?' représente le contenu total d'une variable (ici du texte, sinon on utilise SetDatabaseInteger( etc))
SetDatabaseString() va donc transformer les caractères non gérés ( " ' ) et filtrer
Il faut donc juste veiller à les présenter dans l'ordre ou ils ont étés défini.

PS.
- Tu peux "echapper" une chaine comme en C SetDatabaseString(OUT, 5, ~"Hello \"Bonjour\" le monde")
- En SQL si tu saisi tous les champs d'une table dans l'ordre de la table, il est inutile de préciser les champs, tu peux donc résumer à:

Code : Tout sélectionner

"INSERT INTO TEST VALUES (?, ?, ?, ?)"

- Si tu n'a pas besoin de traiter le résultat d'une requête avant la suivante, tu peux en passer plusieurs en une ligne avec un point-virgule comme séparateur (comme dans un script SQL)

Code : Tout sélectionner

DatabaseUpdate(OUT, "DROP TABLE xxx ; INSERT ...

Edit: Après test et modification de ton code.
J'ai créé un fichier texte de test et je le saucissonne avec StringField (équivalent de SPLIT dans d'autres langages). Mon fichier texte de test contient 4 champs et plein de ' " ' " inside )

Code : Tout sélectionner

    ;;; === LECTURE DONNÉES TEXTE ===
    While Eof(IN) = 0
      entry$ = Trim(ReadString(IN))
      If entry$ = #Null$
        Continue
      EndIf
      ;
      ; traitement des entrées.
      ;
      SetDatabaseString(OUT, 0, StringField(entry$, 1, ","))
      SetDatabaseString(OUT, 1, StringField(entry$, 2, ","))
      SetDatabaseString(OUT, 2, StringField(entry$, 3, ","))
      SetDatabaseString(OUT, 3, StringField(entry$, 4, ","))

      _DatabaseUpdate(OUT, "INSERT INTO TEST VALUES (?, ?, ?, ?)")

    Wend
    ;;; === FIN LECTURE DONNÉES TEXTE ===

(Oui, StringField est l'une des rares commande pour laquelle le premier élément est 1 et non pas 0)

[cage]

Un grand merci pour ces explications et exemples qui me permettent de mieux comprendre certaines commandes PB concernant les databases.
J'ai modifié mon application avec l'utilisation de SetDatabaseString() et tout fonctionne comme souhaité.
J'utilise TEXT en lieu et place de VARCHAR et au final c'est bien plus rapide et la base pèse bien moins lourd.
Encore merci a toi mais je penses que j'aurais encore besoin de tes lumières pour la partie lecture de la base.
cage

[cage]

Bonsoir a tous,

Je reviens solliciter votre aide pour vérifier si mon code tient la route.
Ce code récupère la playlist des chaines TV de Free.fr (Abonnés Freebox uniquement) et crée une base que je peux relire pour l'afficher dans mon application myplayeriptv
Avant de m'investir plus avant dans la lecture de la base, je voudrais être sur d'avoir a peu près compris la création de la base.
Dans l'application finale, la base contient un vingtaine de playlist différentes soit à ce jour, environ 300 000 entrées.
Le fichier en entrée est playlist.m3u et le fichier en sortie est playlist.db sans user/password
Merci a vous.
cage

Code : Tout sélectionner

EnableExplicit

; https://www.sqlite.org
; https://sqlitebrowser.org
; https://www.sqliteexpert.com

; http://mafreebox.freebox.fr/freeboxtv/playlist.m3u

Global ORDRE$
Global GENRE$
Global MEDIA$
Global TITRE$
Global TITRE$
Global GROUPE$
Global SERIE$
Global PAYS$
Global REFID$
Global FAVOR$

Procedure.a fileExist(f$)
  If FileSize(f$) < 0
    ProcedureReturn #False
  Else
    ProcedureReturn #True
  EndIf
EndProcedure

#live="Live"

UseSQLiteDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
  If DatabaseUpdate(Database, Query$)
    ProcedureReturn #True
  Else
    Debug DatabaseError()
  EndIf
EndProcedure

Global IN, OUT, DB, I
Global PlaylistFile$ = "playlist.m3u"
Global DatabaseFile$ = "playlist.db"
Global QUERY_CREATE.s, DATAS.s
Global entry$, begin$, title$, refid$
Global VRAI_CHANNEL, FAUX_CHANNEL, LAST_CHANNEL, LIVE

If Not fileExist(DatabaseFile$)
  OUT = CreateFile(#PB_Any, DatabaseFile$)
  If OUT
    CloseFile(OUT)
  Else
    Debug "Can't create the database file !"
    End
  EndIf
EndIf

#QUERY_DELETE="DROP TABLE IF EXISTS FREEBOX"

#QUERY_CREATE="CREATE TABLE FREEBOX ("+
"ORDRE   VARCHAR PRIMARY KEY,"+
"GENRE   VARCHAR,"+
"MEDIA   VARCHAR,"+
"TITRE   VARCHAR,"+
"GROUPE  VARCHAR,"+
"SERIE   VARCHAR,"+
"PAYS    VARCHAR,"+
"REFID   VARCHAR,"+
"FAVOR   VARCHAR)"+
"WITHOUT ROWID"

Define URL$ = "http://mafreebox.freebox.fr/freeboxtv/playlist.m3u"
Define httprequest
Define httpstatuscode$
httprequest = HTTPRequest(#PB_HTTP_Get, URL$, #Empty$, #PB_HTTP_HeadersOnly)
If httprequest
  httpstatuscode$ = HTTPInfo(httprequest, #PB_HTTP_StatusCode)
  FinishHTTP(httprequest)
  If httpstatuscode$ = "200"
    Debug "Vous êtes abonné Free.fr"
    If ReceiveHTTPFile(URL$,PlaylistFile$)
      Debug "La playlist est téléchargée."
    Else
      Debug "La playlist n'est pas téléchargée."
    EndIf
  Else
    Debug "Vous n'êtes pas abonné Free.fr"
  EndIf
EndIf

I=0

IN = ReadFile(#PB_Any, PlaylistFile$)
If IN
  OUT = OpenDatabase(#PB_Any, DatabaseFile$, #Empty$, #Empty$)
  If OUT
    CheckDatabaseUpdate(OUT, #QUERY_DELETE)
    CheckDatabaseUpdate(OUT, #QUERY_CREATE)
    ;;; === LECTURE PLAYLIST ===
    While Eof(IN) = 0
      entry$ = Trim(ReadString(IN)) ;: Debug entry$
      If entry$ = #Null$
        Continue
      EndIf
      begin$ = UCase(Left(entry$,7))
      Select begin$
        Case "#EXTINF"
          I+1
          VRAI_CHANNEL+1
          title$ = #Null$
          If FindString(entry$,"---") Or
             FindString(entry$,"***") Or
             FindString(entry$,"===") Or
             FindString(entry$,"+++") Or
             FindString(entry$,"###")
            VRAI_CHANNEL-1
            FAUX_CHANNEL+1
            Continue
          EndIf
          title$ = Trim(StringField(entry$,2,","))
        Case "HTTP://", "HTTPS:/", "RTSP://"
          LAST_CHANNEL+1
          If title$ = #Empty$
            ;I-1
            Continue
          EndIf
          LIVE+1
          refid$  = Str(LAST_CHANNEL)
          refid$  = RSet(refid$,3,"0")
          ;;; ====== DATABASE ======
          ORDRE$  = RSet(Str(I),3,"0")
          GENRE$  = #live
          MEDIA$  = entry$
          TITRE$  = title$
          TITRE$  = ReplaceString(TITRE$,"'","''")
          GROUPE$ = #Empty$
          SERIE$  = #Empty$
          PAYS$   = #Empty$
          REFID$  = #Empty$
          FAVOR$  = #Empty$
          
          DATAS.s= "'"+ORDRE$+"','"+GENRE$+"','"+MEDIA$+"','"+TITRE$+"','"+GROUPE$+"','"+SERIE$+"','"+PAYS$+"','"+REFID$+"','"+FAVOR$+"'"
          
          CheckDatabaseUpdate(OUT, "INSERT INTO FREEBOX (ORDRE, GENRE, MEDIA, TITRE, GROUPE, SERIE, PAYS, REFID, FAVOR) VALUES ("+DATAS+")")
      EndSelect
      ;;; === FIN LECTURE PLAYLISTS ===
    Wend
    CloseDatabase(OUT)
    DB = OpenDatabase(#PB_Any, DatabaseFile$, #Empty$, #Empty$, #PB_Database_SQLite)
    If DB
      Debug "Open database Ok"
      CheckDatabaseUpdate(DB, "PRAGMA auto_vacuum = FULL")
      CheckDatabaseUpdate(DB, "VACUUM")
      CloseDatabase(DB)
      Debug Str(Round(FileSize(DatabaseFile$)/1024,#PB_Round_Up))+" Ko"
    EndIf ; If DB
  Else
    Debug "Can't open the database file for writing !"
  EndIf   ; If OUT
  CloseFile(IN)
EndIf     ; In IN

[boddhi]

Je reviens solliciter votre aide pour vérifier si mon code tient la route.

Comme te l'a judicieusement conseillé Marc, privilégie l'emploi du jeu d'instructions SetDatabaseXxxxx() pour affecter des valeurs à des champs de BDD. Cela a plein d'avantages !
Ainsi, remplace ton code :

Code : Tout sélectionner

ORDRE$  = RSet(Str(I),3,"0")
GENRE$  = #live
MEDIA$  = entry$
TITRE$  = title$
TITRE$  = ReplaceString(TITRE$,"'","''")
GROUPE$ = #Empty$
SERIE$  = #Empty$
PAYS$   = #Empty$
REFID$  = #Empty$
FAVOR$  = #Empty$
CheckDatabaseUpdate(OUT, "INSERT INTO FREEBOX (ORDRE, GENRE, MEDIA, TITRE, GROUPE, SERIE, PAYS, REFID, FAVOR) VALUES ("+DATAS+")")

par

Code : Tout sélectionner

ORDRE$  = RSet(Str(I),3,"0")
GENRE$  = #live
MEDIA$  = entry$
TITRE$  = title$
SetDatabaseString(OUT,0,ORDRE$)
SetDatabaseString(OUT,1,GENRE$)
SetDatabaseString(OUT,2,MEDIA$)
SetDatabaseString(OUT,3,TITRES$)
CheckDatabaseUpdate(OUT, "INSERT INTO FREEBOX (ORDRE, GENRE, MEDIA, TITRE) VALUES (?,?,?,?)")

NB : J'ai viré les variables qui ont la valeur #Empty$ et les champs de la requête correspondants car, dans ton exemple, ils sont complètement inutiles. Les champs de la table non spécifiés seront de facto mis en 'Empty'

EDIT : Ne tiens pas compte de ce qui est écrit ci-dessus, je viens de voir sur un autre topic que tu as finalement adopté cet usage !

Une petite astuce également :
Avant ta ligne

Code : Tout sélectionner

    While Eof(IN) = 0

Insère la ligne :

Code : Tout sélectionner

CheckDatabaseUpdate(OUT, "BEGIN")

et après le Wend correspondant, la ligne :

Code : Tout sélectionner

CheckDatabaseUpdate(OUT, "COMMIT")

Cela te permettra de gagner énormément de temps lors de l'écriture de tes données, surtout lorsque tu en as beaucoup.
En gros, les données injectées dans la base seront préalablement et provisoirement stockées dans un journal et ne seront définitivement injectées dans la table qu'après le 'COMMIT' (Il n'y pas de contre-indication dans ton code qui ne t'autorise pas à utiliser cette astuce )

Et pour te convaincre du gain de temps, tu peux par exemple créer une boucle avec 1 000 ou 2 000 enregistrements à insérer dans ta table et commenter/décommenter les lignes 'BEGIN' et 'COMMIT'. Et là, tu verras la différence ...

[cage]

Merci pour les 2 commandes "BEGIN" et "COMMIT" a l'ouverture de la base SQLite et avant la fermeture.
Ça fait une grande différence, j'ai l'impression que la gestion de la base devient instantanée.
Encore un grand merci pour ton aide et pour le temps que tu passes a corriger/améliorer mes codes.
cage

[boddhi]

a l'ouverture de la base SQLite et avant la fermeture.

Attention : Précision, ce n'est pas à l'ouverture et à la fermeture de la base que doivent être invoquées ces fonctions MAIS AVANT (pour 'BEGIN') et APRES (pour 'COMMIT') toute grosse MàJ (DatabaseUpdate) qu'elles doivent l'être !!!
Donc, généralement, avant/après les boucles For...Next, While...Wend, etc.