[R] Version SQLite

[ludoo]

Bonjour à tous ,
peut on changer la version de sqlite de AutoIt à savoir 3.6.22 et comment ,
car j'ai un probleme avec une requête, elle fonctionne sous Sqlite Manager 1.2.4 mais pas sous AutoIt
voici la requête:

► Afficher le texte

SELECT User, GroupeAD, (select total(pages * copies) FROM log G WHERE L.user =
G.user and
g.paper ='A4' and fulltextsearch(GroupeAD, 'GG-XXX', 0)) FROM log L WHERE fulltextsearch(GroupeAD, 'GG-XXX', 0) > 0 GROUP BY user

merci de votre aide.

[jchd]

Le problème de ta requête est qu'elle emploie une extension propriétaire : fulltextsearch() ne fait pas partie de la distribution SQLite standard.

Soit tu peux utiliser leur extension (si c'est une DLL) avec la DLL SQLite standard, soit tu dois utiliser une requête distincte qui pourrait utiliser le support FTS (full text search, qui est un support de table virtuelle permettant ... de la recherche idoine efficacement). FTS3 et/ou FTS4 font partie de la DLL standard. oir la doc SQLite du site pour un exemple de mise en oeuvre. Si leur extension est compilée en statique dans le code de SQLite Manager, tu n'as pas d'autre choix sauf à récupérer le code source et t'en faire une DLL toi-même.

Tu peux de toutes façons aller chercher la dernière DLL x86 sur le site de SQLite et tout simplement copier cette DLL dans le répertoire de ton script compilé par exemple, pour rester simple. N'inclus pas <sqlite.dll.au3> dans ton script et passe le chemin complet de la DLL SQLite que tu souhaites employer en paramètre de SQLite_Startup().

[ludoo]

donc , il faut créer un table virtuel

Code : Tout sélectionner

_SQLite_Exec($hDb, "CREATE VIRTUAL TABLE TexteGroupAd USING fts3(User GroupeAD);")

j'ai un message d'erreur

► Afficher le texte

! SQLite.au3 Error
--> Function: _SQLite_Exec
--> Query: CREATE VIRTUAL TABLE TexteGroupAd USING fts3(User GroupeAD);
--> Error: no such module: fts3

une fois la table créé comment la mettre à jour par rapport à une autre table , en sachant que (User et GroupeAD) sont déjà rempli dans une table log.

[jchd]

Deux choses à vérifier.
Fais d'abord ça :
Affiche ce que renvoie ConsoleWrite("_SQLite_LibVersion = " &_SQLite_LibVersion() & @LF)
D'où provient la DLL que tu emploies ?
Essaye fts4 au lieu de fts3
De toutes façons, si User et GroupeAD sont les noms que tu veux donner à 2 colonnes de ta table FTS, il faut les séparer par une virgule. Voir la syntaxe.

Je suis en "transumance" pour encore un certain temps (et probablement même un temps certain) donc souvent sans Internet mais je trouverai le moyen de te répondre de temps à autre.

J'ai pu vérifier : la version de SQLite fournie avec la dernière release n'est pas compilée avec FTS*, ce qui est bien dommage.
Fais donc simple : va chercher la dernière version pour x86 sur le site de SQLite et place la dll dans le répertoire de ton script.

[ludoo]

version sqlite 3.6.22
fourni avec AutoIt du dossier include

test en récupérant la dll du site sqlite + enlever <sqlite.dll.au3>

Code : Tout sélectionner

_SQLite_Exec($hDb, "CREATE VIRTUAL TABLE TexteGroupAd using fts4(User, GroupeAD)")

message d'erreur :

► Afficher le texte

! SQLite.au3 Error
--> Function: _SQLite_Exec
--> Query: CREATE VIRTUAL TABLE TexteGroupAd using fts4(User, GroupeAD)
--> Error: no such module: fts4

et aussi :

Code : Tout sélectionner

_SQLite_Exec($hDb, "CREATE VIRTUAL TABLE TexteGroupAd using fts3(User, GroupeAD)")

message d'erreur:

► Afficher le texte

! SQLite.au3 Error
--> Function: _SQLite_Exec
--> Query: CREATE VIRTUAL TABLE TexteGroupAd using fts3(User, GroupeAD)
--> Error: no such module: fts3

[jchd]

Heu ?
Fais voir un dir sqlite.dll dans le(s) répertoires concerné(s), car pour moi ça fonctionne nickel (sauf la version 6.22 qui n'inclut pas de FTS).

[ludoo]

le probleme de vient de l'OS win7 x64.
même quand je ne renseigne pas la dll , j'ai toujours _SQLite_LibVersion = 3.6.22
il va chercher la dll sqlite3_x64.dll qui se trouve ds system32 , et cette dll est en version 3.6.22 , comment faire pour résoudre le problème ?
trouvé : il faut rajouter #AutoIt3Wrapper_UseX64=n

sinon j'ai testé sur un autre Os pas de probleme avec le version 3.7.

[ludoo]

j'ai bien ma "table virtual" de crée "TexteGroupAd"
par contre j'arrive pas à la mettre à jour via une autre "log"
voici un bout du code:

Code : Tout sélectionner

_SQLite_Exec($hDb, "CREATE VIRTUAL TABLE TexteGroupAd using fts3(Id, User, GroupeAD)")
_SQLite_Exec($hDb, "INSERT INTO TexteGroupAd (Id, User, GroupeAD) SELECT Id, User, GroupeAD FROM log")

pas de message d'erreur
la table "TexteGroupAd" reste vide .

[jchd]

Oui un script compilé en X64 demande une DLL en X64.

Pour moi la séquence suivante :

Code : Tout sélectionner

create table log (id integer primary key autoincrement, user text, groupead text);
CREATE VIRTUAL TABLE TexteGroupAd using fts3(User, GroupeAD);
insert into log(user, groupead) values ('Pierre', 'Utilisateurs');
insert into log(user, groupead) values ('Pierre', 'Groupe1');
insert into log(user, groupead) values ('Paul', 'Groupe2');
insert into log(user, groupead) values ('Eric', 'Groupe1');
insert into log(user, groupead) values ('Jacques', 'Utilisateurs');
INSERT INTO TexteGroupAd (User, GroupeAD) SELECT User, GroupeAD FROM log;
select * from TexteGroupAd where user match 'pierre';

produit bien :
RecNo User GroupeAD
----- ------ ------------
1 Pierre Utilisateurs
2 Pierre Groupe1

Au passage, pourquoi créer une colonne Id dans la table FTS ?

[ludoo]

oui , erreur de débutant en sqlite , la table log n’était pas encore rempli , donc normal que la base TexteGroupAd soit vide.
c comme ça qu'on progresse
j'ai bien mes 2 tables log et TexteGroupAd.
comment faire une requête sur les 2 tables.
sortir les info user, pages * copies , paper = A4 de la table log puis
l'info sur user GroupeAD Match =GG-xxx de la table TexteGroupAd.
pour contourner le problème je renseigne ds la table TexteGroupAd les info , copie , pages , paper.

Code : Tout sélectionner

_SQLite_Exec($hDb, "CREATE VIRTUAL TABLE TexteGroupAd using fts3(User, GroupeAD, Pages, Copies, Paper)")
_SQLite_Exec($hDb, "INSERT INTO TexteGroupAd (User, GroupeAD, Pages, Copies, Paper) SELECT User, GroupeAD, Pages, Copies, Paper FROM log")

[jchd]

Sauf si tes tables sont vraiment volumineuses, je ne crois pas que tu aies intérêt à te compliquer l'existence avec une recherche FTS. Une recherche FTS ne se justifie pleinement que lorsqu'on recherche un mot précis dans une colonne de texte massif, par exemple le mot 'TexteGroupAd' dans l'ensemble des posts de ce forum.

Dans ton cas et puisque tu as déjà le champ GroupeAD dans la table log, je ne vois pas l'intérêt de ta table FTS.
Détrompe-moi si je dis une ânerie :

Code : Tout sélectionner

SELECT User, GroupeAD, total(pages * copies) FROM log WHERE paper ='A4' and GroupeAD like '%GG-XXX%' GROUP BY user order by user;

[ludoo]

oui j’utilisai cette requête mais le problème de LIKE c que j'ai des groupes qui sont très proches en nom:
ex : GG-Chbob et GG-Chtat

si ds la requête je met ;

Code : Tout sélectionner

ELECT User, GroupeAD, total(pages * copies) FROM log WHERE paper ='A4' and GroupeAD like '%GG-Chbob%' GROUP BY user order by user;

elle renvoie les 2 valeurs GG-Chbob et GG-Chtat
c pourquoi je voulais utiliser la table FTS.

[jchd]

Impossible !
... like '%GG-Chbob%' ...
ne peut _PAS_ sélectionner 'GG-Chtat'

[ludoo]

dans la table Log , j'ai la colonne GroupeAD contenant tous les groupes dont l'utilisateur fait parti , séparé par "|" ex : gg-XX|gg-xy
voici les 2 requêtes que j'utilise :
1er

Code : Tout sélectionner

$res = _SQLite_GetTable2d($hDb, "select user, GroupeAD as [GroupeAD], (select total(pages * copies) from log G where L.user = G.user and g.paper = 'A4' and GroupeAD LIKE "& W("%"&$ADGroup)& ") as [Pages A4 imprimées] from log L where GroupeAD LIKE "& W("%"&$ADGroup)& " group by user ;", $rows, $nrows, $ncols)

2eme

Code : Tout sélectionner

$res = _SQLite_GetTable2d($hDb, "select user, GroupeAD as [GroupeAD], (select total(pages * copies) from log G where L.user = G.user and g.paper = 'A4' and GroupeAD LIKE "& X("%"&$ADGroup)& ") as [Pages A4 imprimées] from log L where GroupeAD LIKE "& X("%"&$ADGroup)& " group by user ;", $rows, $nrows, $ncols)

la fonction W:

Code : Tout sélectionner

Func W($s)
    Return ("'" & StringReplace($s, "'", "",0 ,1) & "%'")
EndFunc    ;==>W

le résultat est complétement différent:
pour la première , j'ai tous les groupes + ceux qui commence de la même manière , ex : gg-chbob et gg-chtat
pour la 2eme , j'ai uniquement le résultat lorsque le groupe est à la fin du champs ex : gg-xx|gg-yy --> résultat gg-yy ; ex : gg-yy|gg-xx --> résultat vide .

[jchd]

dans la table Log , j'ai la colonne GroupeAD contenant tous les groupes dont l'utilisateur fait parti , séparé par "|" ex : gg-XX|gg-xy

Erreur fatale !
On ne doit JAMAIS mettre une donnée multivaluée dans une colonne, c'est contraire à la philosophie SQL. La meilleure preuve est que tu en arrives à échafauder une usine à gaz pour résoudre un problème élémentaire.
En règle générale, si une même donnée est stockée plus d'une fois, alors on doit se poser la question de normaliser le design (sauf dénormalisation volontaire pour des questions d'efficacité, à postériori).

Pour prendre une analogie simple, regarde le répertoire d'un téléphone. Tu pourrais y trouver cette suite d'entrées :
Eric adsl 0975874152
Eric fixe home 0287652140
Eric port 0627891999
Eric port boulot 0615210248
Eric fixe boulot 0178945763
etc.
Je ne connais aucun smartphone qui présenterait ça ainsi :
Eric 0975874152,0287652140,0627891999,0615210248,0178945763
ce serait indémerdable.

Si on transpose en SQL, on va faire :
o) une table des noms avec Id et nom en clair
o) une table des types de liens (adsl, fixe home, portable, portable boulot, ...) avec Id et type en clair
o) une table des numéros avec Id, numéro, typeId, nomId
que l'on peut facilement étendre pour y stocker (avec la même sémantique) les adresses mail, fècesbouc, etc.

La magie pour ficeler tout ça de façon cohérente fait principalement appel à deux ingrédients : les clés étrangères (FK = foreign keys) entre IDs et les jointures entre tables (JOIN), utilisant le plus souvent ces IDs.

Ne le prend pas mal, je sais bien que tu débutes.

Les relations entre données [re]construisent de l'information. Des données peuvent être :
-) indépendantes (aucune relation, ex. code barre d'un article et salaire du gérant)
-) 1 à 1 (ex. numéro <--> typeId, ou 1 règlement solde 1 facture)
-) 1 à N (ex. nomId <-->> numeroId, ou 1 règlement solde plusieurs factures)
-) N à 1 (ex. inverse du précédent, ou plusieurs règlements soldent 1 facture)
-) N à N (ex. quel nomId a fait crac-crac avec quel nomId, ou plusieurs règlements disparates soldent plusieurs factures)

Tu vires cette colonne atroce d'une liste de groupes et tu crées une table UserGroups qui va contenir des paires (UserId, GroupeId) avec, bien entendu, un seul groupe par entrée. Si un user fait partie de 5 groupes, il aura 5 entrées. Pour conserver l'efficacité, on ne stocke dans cette table que les Id (rowid si tu préfères ce nom-là). Il te faut donc une table des groupes, donnant pour chaque groupeId son nom et éventuellement d'autres caractéristiques directement associées à ce groupe.
N'hésite pas à enfoncer le clou : dans ta table log, on va voir répéter les users des palanquées de fois ! Bingo, on remplace les users dans cette table par un userId et, du coup, on crée une table des users avec Id et user en clair.
Tu as : une table Users, une table Groups, une table de lien UserGroups et une table log qui devrait s'appeler PrintLog.
C'est normal et c'est BIEN. Ce qui serait encore mieux serait que tu trouves par toi-même la table qui manque à ce paysage...

Pour quiconque débute en SQL, un tel schéma semble inutilement tortueux et c'est bien là que SQL surprend le programmeur "conventionnel" : SQL excelle à retrouver ses billes dans une pléiade de tables liées entre elles par des relations sémantiques claires. Le langage offre la possibilité d'exprimer des relations et des requêtes complexes et/ou subtiles de façon concise, lisible et maintenable (SQL est le langage le plus utilisé dans le monde, et de très loin !).
Exemple de subtilité (artificiel, certes) : dans le schéma ci-dessus, ne pas effacer les groupes qui disparaissent de l'AD réel, ni les users qui en faisaient partie. Cela permet, six mois après une réorganisation (fusion, spin off, ...) de comparer les coûts d'impression avant et après et de prendre les mesures appropriées le cas échéant. Si l'appli a une quelconque vocation à durer, dissocier ainsi AD réel et image de l'AD au fil du temps a un sens. Le problème des changements de groupes et de noms est soluble, comme le Nescawa.

Dans la réalité, il y a une autre caractéristique qui est absente de la discussion ; c'est la localisation physique, variable pour les gens, bien réelle pour une imprimante mais 100% virtuelle pour un groupe AD. Je plaisante, mais le groupe 'secrétaires' est certainement réparti sur plusieurs étages, voire plusieurs bâtiments, villes ou même pays. Une secrétaire va utiliser l'imprimante A3 couleur la plus accessible quand elle a besoin d'A3 couleur. Question pertinente : quel parc d'imprimantes dois-je acheter et où dois-je les placer pour optimiser ma boîte sans frustrer mon personnel ni exploser le budget ?
Ce que je veux dire par là est qu'une appli aura d'autant plus de facilité à évoluer dans le bon sens et au moindre mal si elle est bien ficelée dès le départ et utilise à bon escient les constructions "naturelles" de la plate-forme ou du langage qu'elle emploie.

J'espère que tu vois maintenant pourquoi la recherche FTS est inadaptée à ton problème.

J'ai usé le clavier et abusé du forum ... AutoIt. Aaah non, j'ai trouvé comment me faire pardonner :
Exit(0)

[ludoo]

et oui en plus je le savais plus ou moins , mais j'espère une fonction magique en sql pour me tirer de ce mauvais pas ,
en informatique il y a pas de fonction magique mais plutôt des bugs suite à des codes hasardeux dont je montre l'exemple(autopunition).
voici une partie du code pour partir du bon pied.

► Afficher le texte

_SQLite_Exec($hDb, "CREATE TABLE if not exists PrintLog (" & _
" Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," & _
" Date CHAR," & _
" User CHAR," & _
" Pages INTEGER," & _
" Copies INTEGER," & _
" Printer CHAR," & _
" Document CHAR," & _
" Client CHAR," & _
" Paper CHAR," & _
" Language CHAR," & _
" Height INTEGER," & _
" Width INTEGER," & _
" Duplex BOOLEAN," & _
" Grayscale BOOLEAN," & _
" SizeKb INTEGER," & _
" CONSTRAINT ksUniq UNIQUE(Date, User, Printer, Document) ON CONFLICT REPLACE);" & _
"CREATE INDEX if not exists ixDate ON PrintLog (Date);" & _
"CREATE INDEX if not exists ixUser ON PrintLog (User);" & _
"CREATE INDEX if not exists ixPrinter ON PrintLog (Printer);")
_SQLite_Exec($hDb, "CREATE TABLE if not exists UserGroups (" & _
" UserId CHAR," & _
" GroupeId CHAR);")
_SQLite_Exec($hDb, "CREATE TABLE if not exists Users (" & _
" UserId CHAR);")
_SQLite_Exec($hDb, "CREATE TABLE if not exists Groups (" & _
" GroupeId CHAR);")
_SQLite_Exec($hDb, "INSERT INTO Groups (GroupeId) SELECT GroupeId FROM UserGroups;")
_SQLite_Exec($hDb, "INSERT INTO Users (UserId) SELECT UserId FROM UserGroups;")

par contre pour la partie ???? et pour la table qui manque ( c peut être regrouper les Id)

Bingo, on remplace les users dans cette table par un userId et, du coup, on crée une table des users avec Id et user en clair.
Ce qui serait encore mieux serait que tu trouves par toi-même la table qui manque à ce paysage...

donc au final 4 tables : PrintLog, UserGroups, Users, Groups
ds la table Users , colonne UserId de la table UserGroups
ds la table Groups , colonne GroupeId de la table UserGroups
j'espère pas être trop de la solution.

[jchd]

Et si tu avais une table des imprimantes, hein ?
J'espère avoir de nouveau du net aujourd'hui pour te répondre en clair...

[ludoo]

oui très juste la table imprimante avec les utilisateurs.
j'ai un peu avancé sur la création des tables Users , UserGroups, Groups:
voici le code:

Code : Tout sélectionner

_SQLite_Exec($hDb, "CREATE TABLE if not exists Groups (" & _
        " GroupeId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," & _
        " NameGG CHAR," & _
        " CONSTRAINT pk_UniqGG UNIQUE(NameGG) ON CONFLICT REPLACE);" & _
        " CREATE INDEX if not exists idx_NameGG ON Groups (NameGG);")

_SQLite_Exec($hDb, "CREATE TABLE if not exists Users (" & _
        " UserId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," & _
        " User CHAR," & _
        " CONSTRAINT pk_Uniquser UNIQUE(User) ON CONFLICT REPLACE);" & _
        " CREATE INDEX if not exists idx_User ON Users (User);")

_SQLite_Exec($hDb, "CREATE TABLE if not exists UserGroups (" & _
        " UserId CHAR," & _
        " GroupeId CHAR);")

pour la table PrintLog j'ai rajouté une colonne UserId:

Code : Tout sélectionner

_SQLite_Exec($hDb, "CREATE TABLE if not exists PrintLog (" & _
        " Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT," & _
        " Date CHAR," & _
        " User CHAR," & _
        " Pages INTEGER," & _
        " Copies INTEGER," & _
        " Printer CHAR," & _
        " Document CHAR," & _
        " Client CHAR," & _
        " Paper CHAR," & _
        " Language CHAR," & _
        " Height INTEGER," & _
        " Width INTEGER," & _
        " Duplex BOOLEAN," & _
        " Grayscale BOOLEAN," & _
        " SizeKb INTEGER," & _
        " UserId INTEGER," & _
        " CONSTRAINT ksUniq UNIQUE(Date, User, Printer, Document) ON CONFLICT REPLACE);" & _
        "CREATE INDEX if not exists ixDate ON PrintLog (Date);" & _
        "CREATE INDEX if not exists ixUser ON PrintLog (User);" & _
        "CREATE INDEX if not exists ixPrinter ON PrintLog (Printer);")

le code qui permet d'alimenter les tables :

Code : Tout sélectionner

Global $aLines, $res
_FileReadToArray($sFilePathBDD, $aLines)
_SQLite_SetTimeout($hDb, 60000)         ; on laisse jusqu'à 60 s de timeout, juste au cas où la base serait exploitée/exploitable par un autre process
_SQLite_Exec($hDb, "begin immediate;")  ; on lance une transaction avant l'insertion en boucle, pour la performance
For $line In $aLines
    $res = StringRegExp($line, '([^,]*),([^,]*),?', 1)
    If Not @error Then
        _SQLite_Exec($hDb, "insert into Groups ( NameGG) " & _
                "values (" & X($res[1]) & ");")
    EndIf
Next


;~ Global $aLines, $res
_FileReadToArray($fileMois, $aLines)
_SQLite_SetTimeout($hDb, 60000)         ; on laisse jusqu'à 60 s de timeout, juste au cas où la base serait exploitée/exploitable par un autre process
_SQLite_Exec($hDb, "begin immediate;")  ; on lance une transaction avant l'insertion en boucle, pour la performance
For $line In $aLines
    $res = StringRegExp($line, '([^,]*),([^,]*),([^,]*),([^,]*),([^,]*),"([^,]*)",([^,]*),([^,]*),([^,]*),([^,]*),([^,]*),([^,]*),([^,]*),([^,]*)kb,?', 1)
    If Not @error Then
        $res[11] = Number($res[1] = 'DUPLEX')
        $res[12] = Number($res[12] = 'GRAYSCALE')
        _SQLite_Exec($hDb, "insert into Printlog ( date, user, pages, copies, printer, " & _
                "document, client, paper, language, height, " & _
                " width, duplex, grayscale, sizeKb) " & _
                "values (" & X($res[0]) & XX($res[1]) & ZZ($res[2]) & ZZ($res[3]) & XX($res[4]) & _
                XX($res[5]) & XX($res[6]) & XX($res[7]) & XX($res[8]) & ZZ($res[9]) & _
                ZZ($res[10]) & ZZ($res[11]) & ZZ($res[12]) & ZZ($res[13]) & ");")
    EndIf
Next

_FileReadToArray($sFilePathBDD, $aLines)
_SQLite_SetTimeout($hDb, 60000)         ; on laisse jusqu'à 60 s de timeout, juste au cas où la base serait exploitée/exploitable par un autre process
_SQLite_Exec($hDb, "begin immediate;")  ; on lance une transaction avant l'insertion en boucle, pour la performance
For $line In $aLines
    $res = StringRegExp($line, '([^,]*),([^,]*),?', 1)
    If Not @error Then
        _SQLite_Exec($hDb, "insert into UserGroups ( UserId, GroupeId) " & _
                "values (" & X($res[0]) & XX($res[1]) & ");")
    EndIf
Next
 

et apres remplir la colonne UserId de la table Users dans la table PrintLog de la colonne UserId

Code : Tout sélectionner

_SQLite_Exec($hDb, "INSERT INTO PrintLog (UserId) SELECT UserId FROM Users Where User = UserId;")

mais je bloque et pas sur que ce soit la bonne méthode.

ah oui un truc que je comprend pas dans les tables Users et Groups les colonnes UserId et GroupeId ne commence pas par 1.

[jchd]

Je sens que je vais (encore) faire un pavé et je m'en excuse. Ceci dit, c'est un bon exemple non élémentaire d'emploi de SQL[ite] pour une appli qui reste simple et compréhensible mais qui peut évoluer vers des requêtes franchement tortueuses par la suite. Je généralise peut-être un poil trop, mais c'est aussi pour ouvrir des possibilités et éventuellement donner des idées à des utilisateurs qui douteraient de la puissance de cet formidable outil (associé à la versatilité d'AutoIt).

Voici le schéma que je te propose (pardonne-moi, j'ai changé certains noms mais tout me semble plus rigoureusement nommé ainsi) :

► Afficher le texte

Code : Tout sélectionner

CREATE TABLE [Groups] (
  [GroupId] INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE AUTOINCREMENT, 
  [GroupName] CHAR NOT NULL ON CONFLICT IGNORE COLLATE NOCASE);

CREATE UNIQUE INDEX [ixGroupName] ON [Groups] ([GroupName] COLLATE NOCASE);


CREATE TABLE [Users] (
  [UserId] INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE AUTOINCREMENT, 
  [UserName] CHAR NOT NULL ON CONFLICT IGNORE);

CREATE UNIQUE INDEX [ixUserName] ON [Users] ([UserName] COLLATE NOCASE);


CREATE TABLE [Printers] (
  [PrinterId] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [PrinterName] CHAR NOT NULL COLLATE NOCASE);

CREATE UNIQUE INDEX [ixPrinterName] ON [Printers] ([PrinterName] COLLATE NOCASE);


CREATE TABLE [UsersGroups] (
  [UserId] INTEGER CONSTRAINT [fkUsersGroup_User] REFERENCES [Users]([UserId]) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY DEFERRED, 
  [GroupId] INTEGER CONSTRAINT [fkUsersGroups_Group] REFERENCES [Groups]([GroupId]) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT [] PRIMARY KEY ([UserId], [GroupId]) ON CONFLICT IGNORE);


CREATE TABLE [PrintOuts] (
  [Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
  [PrinterId] INTEGER CONSTRAINT [fkPrinter] REFERENCES [Printers]([PrinterId]) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY DEFERRED, 
  [UserId] INTEGER CONSTRAINT [fkUserPrintOut] REFERENCES [Users]([UserId]) ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY DEFERRED, 
  [Date] CHAR NOT NULL, 
  [Document] CHAR DEFAULT (''), 
  [Pages] INTEGER NOT NULL, 
  [Copies] INTEGER NOT NULL, 
  [Client] CHAR DEFAULT (''), 
  [Paper] CHAR NOT NULL, 
  [Language] CHAR DEFAULT (''), 
  [Height] INTEGER, 
  [Width] INTEGER, 
  [Duplex] BOOL DEFAULT (0), 
  [Grayscale] BOOL DEFAULT (0), 
  [SizeKb] INTEGER);

CREATE INDEX [ixPrintOutDate] ON [PrintOuts] ([Date]);

CREATE INDEX [ixPrintOutUserId] ON [PrintOuts] ([UserId]);

CREATE INDEX [ixPrintOutsPrinterId] ON [PrintOuts] ([PrinterId]);
 

Il n'y a plus de répétition de UserName, GroupName, PrinterName, juste des clés étrangères (foreign keys) qui sont liées.
Idéalement, on pourrait trouver dans la table priters tout ce qui se rattache à chaque imprimante. Y stocker par exemple le fait qu'elle soit couleur ou pas, duplex ou pas, sa technologie, son coût à la page, ses formats de papier, etc peut par la suite fournir des réponses pertinentes à certaines questions du type "Quelles imprimantes couleur sont-elles trop souvent utilisées pour imprimer en noir seul, et par qui ?" Idem pour les différents formats de papier, etc.

Je te laisse LMGTFY les termes qui sonnent bizarre et plonger dans la doc SQLite.

Attention à tes "ON CONFLICT REPLACE" car "REPLACE" signifie en fait "DELETE" suivi de "INSERT". Avec le mécanisme de clés étrangères avec la clause CASCADE, tu effacerais tout ce qui concerne cet user (lien vers ses groupes et ses printouts).
Si on tente d'insérer un user déjà présent, il suffit de faire tout simplement "IGNORE" (idem pour group et printer). On met aussi une clé primaire sur (UserId, GroupId) dans la table de liens UsersGroups, avec Ignore aussi. Tu vas voir pourquoi on fait ça, par delà l'unicité.

Pour la partie code maintenant :
-) pas besoin de répéter le timeout, une seule fois suffit après création de la connexion avec la base.
-) si tu ouvres une transaction (begin ...) il faut la clore avec soit "commit" (appliquer tous les changements d'un seul bloc) ou "rollback" (ignorer tous ces changements d'un seul bloc).

En pratique avec le schéma ci-dessus, tu dois avoir une liste fichier (ou extrait de ton AD de course) des users avec leur(s) groupes. Cette liste doit normalement contenir (sans répétition mais on s'en contrefout) tous les groupes et tous les users, sous leurs noms respectifs.
Disons donc que tu as cette liste sous forme d'un tableau 2D (disons $ADdata) ayant pour colonnes UserName et GroupName.
BEGIN IMMEDIATE;
Tu fais donc bêtement une boucle for qui parcourt ce tableau et tu fais pour chaque rangée en une seule instruction _SQLite_Exec :
insert into users (username) values ($ADdata[$i][0]);
insert into groups (groupname) values ($ADdata[$i][1]);
insert into usersgroups (userid, groupid) values ((select userid from users where username = $ADdata[$i][0]), (select groupid from groups where groupname = $ADdata[$i][1]));

next; fin de la boucle d'insertion
COMMIT; fin de transaction, plouf, d'un seul coup

L'idée est que si tu fais avec AutoIt les deux SELECT pour tester leur existence, ensuite leur éventuelle insertion et ensuite encore un test pour savoir si le cas échéant le couple user/group n'existerait pas déjà et finalement l'insertion conditionnelle de ce lien, tu vas bouffer bien plus de temps que si tu passes ce gros bébé à SQLite directement en le laissant se débrouiller avec, sachant que les clauses d'unicité avec l'option IGNORE font qu'on ne cassera rien et qu'on n'aura pas de doublon. Faut pas oublier que SQLite est écrit en C fortement optimisé et qu'il fera toujours le même boulot de façon fiable bien plus rapidement que si on décortique tout en codant ce boulot en AutoIt (et en risquant plus de bugs).

mais je bloque et pas sur que ce soit la bonne méthode.

Exact. Deux plombs là-dedans : ... where user = userid est une ânerie et de toutes façons, pas besoin de "remplir" cette colonne indépendamment des autres !

Pour charger PrintOuts, on va faire à peu près la même chose : insérer le printername (on s'en cogne s'il existe déjà, because unique et ignore) puis insérer les colonnes normalement pour chaque rangée de printout. Le tout dans une transaction globale de la boucle pour éviter que ça rame inutilement.

BEGIN immediate;
for ...
insert into printers (printername) values ($lenomduprinter);
insert into printouts (printerid, userid, ...) values ((select userid from users where username = $lenomduuser), (select printerid from printers where printername = $lenomduprinter), le reste des colonnes ... comme tu faisais;
next
commit;

Tu vas me dire "bon, et maintenant, comment "joindre userid de la table printouts et user, par exemple ?"
La réponse est JOIN (et même "NATURAL JOIN") qui va te permettre de "synchroniser" les champs identiques (userid) des tables où ils figurent.

Je veux la liste des users qui ont imprimé plus de 5 pages A3 dans les 30 derniers jours, avec le total des pages pour chacun, triée par nombre de pages total décroissant et nom d'utilisateur croissant :

select username as "Utilisateur", total(pages * copies) as "Pages imprimées" from printouts natural join users where date >= date('now', '-30 days') and paper like 'a3' group by userid having "Pages imprimées" > 5 order by "Pages imprimées" desc, username;

Une requête SQL non triviale comporte typiquement un bon paquet de joins, éventuellement dans des sous-requêtes, c'est parfaitement normal.

J'ai fortement sabré par manque de mon environnement de travail habituel. Je te laisse le soin de mettre ça en forme, de corriger les typos et d'ajouter la sauce syntaxique et AutoIt qui convient.

N"hésite pas à venir hurler ta rage s'il y a quelque chose qui cloche sévère dans ce que j'ai pondu (à l'arrache, je dois dire). Bonne chance et dis-nous si tu t'en sors !

[ludoo]

merci pour les explications sur le fonctionnement du sqlite , pour la doc sqlite elle est très complète mais hélas pour moi en anglais donc je ne saisis pas toutes les explications des commandes.
par exemple :
CONSTRAINT
ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY DEFERRED, celle ci, elle est balaise.
Pour le script pour la création de la base sqlite , j'ai un problème pour la créer avec autoit j'ai un message d'erreur sur la fonction CREATE.
voici le code :

Code : Tout sélectionner

_SQLite_Exec($hDb, "CREATE TABLE Groups (" & _
        " GroupeId INTEGER NOT NULL PRIMARY KEY ON CONFLICT IGNORE AUTOINCREMENT," & _
        " GroupName CHAR NOT NULL ON CONFLICT IGNORE COLLATE NOCASE," & _
        CREATE UNIQUE INDEX [ixGroupName] ON [Groups] ([GroupName] COLLATE NOCASE);

ou il faut exécuter le script en sql?
je sais pas ce qui bloque, soit le premier create ou le create unique.
question , je compte récupérer la liste des users AD tous les mois , comment mettre à jour ma table users.
à savoir si je m'en sors , oui grâce à l'aide et les explications que tu apporte ,

N"hésite pas à venir hurler ta rage s'il y a quelque chose qui cloche sévère dans ce que j'ai pondu (à l'arrache, je dois dire). Bonne chance et dis-nous si tu t'en sors !

arrhhhh il y a des tables de partout, elles se croisent ds tous les sens.