Voici un code qui liste les tables et les colonnes avec leurs caractéristiques, d'une base Sqlite.
Le nom des colonnes et ses caractéristiques se trouvent dans le sql de création de la table.
Code : Tout sélectionner
Enumeration
#Bdd
EndEnumeration
UseSQLiteDatabase()
Macro Db_query(bdd,T_sql)
If Not DatabaseQuery(bdd,T_sql)
MessageRequester("Erreur SQL",T_sql + #LF$ + #LF$ +DatabaseError())
End
EndIf
EndMacro
Structure champs
Nom_colonne.s
Type.s
Cle.b
EndStructure
Global NewList colonnes.champs()
Structure bdd
Nom_table.s
List colonne.champs()
EndStructure
Global NewList Tables.bdd()
File_bdd$ = OpenFileRequester("Base de données","","Base de données (sqlite,db)|*.sqlite;*.db|Tous fichiers (*.*)|*.*",0)
If File_bdd$ <> ""
OpenDatabase(#Bdd,File_bdd$,"","")
T_sql$ = "SELECT name, sql FROM Sqlite_master WHERE Type = 'table' ORDER BY name"
Db_query(#Bdd,T_sql$)
While NextDatabaseRow(#Bdd)
AddElement(Tables())
Tables()\Nom_table = GetDatabaseString(#Bdd,0)
L0$ = ReplaceString(GetDatabaseString(#Bdd,1),#CR$,"")
L0$ = ReplaceString(L0$,#LF$,"")
L0$ = ReplaceString(L0$,Chr(34),"")
L0$ = ReplaceString(L0$,Chr(96),"")
If CountString(L0$,"(") > 1
Tmp_colonne.s = Trim(StringField(L0$,3,"("),")")
EndIf
L1$ = Trim(StringField(L0$,2,"("))
L1$ = Trim(StringField(L1$,1,")"))
ClearList(colonnes())
P_key = #False
For i = 1 To CountString(l1$,",") + 1
L2$ = Trim(StringField(L1$,i,","))
If CountString(L2$," ") > 0
L3$ = Trim(StringField(L2$,1," "))
L4$ = Trim(StringField(L2$,2," "))
ElseIf CountString(L2$,#TAB$) > 0
If CountString(L2$,#TAB$) > 1
Field = 2
Else
Field = 1
EndIf
L3$ = Trim(StringField(L2$,Field,#TAB$))
L4$ = Trim(StringField(L2$,Field + 1, #TAB$))
EndIf
If L3$ = Tmp_colonne
P_key = #True
ElseIf FindString(L2$,"PRIMARY KEY") > 0 And P_key = #False
P_key = #True
Else
P_key = #False
EndIf
If FindString(L3$,"PRIMARY") = 0
AddElement(colonnes())
colonnes()\Nom_colonne = L3$
colonnes()\Type = L4$
colonnes()\Cle = P_key
EndIf
Next
SortStructuredList(colonnes(),#PB_Sort_Ascending|#PB_Sort_NoCase,OffsetOf(champs\Nom_colonne),#PB_String)
ForEach colonnes()
AddElement(Tables()\colonne())
Tables()\colonne() = colonnes()
Next
Wend
FinishDatabaseQuery(#Bdd)
ForEach Tables()
Debug Tables()\Nom_table
ForEach Tables()\colonne()
Debug #TAB$ + "> " + LSet(Tables()\colonne()\Nom_colonne,30) + #TAB$ + LSet(Tables()\colonne()\Type,20) + #TAB$ + Str(Tables()\colonne()\Cle)
Next
Next
EndIf
