Cela fait un petit temps que je ne développe plus en Pb, mais pour ne perdre la main j'ai commencer le développement d'un module (non terminé) pour créer facilement un model de données en sqlite (actuellement ne permet que la création de la db ainsi que l'ajout de record)
Voici ce que cela donne en utilisation
Code : Tout sélectionner
XIncludeFile "Model.pbi"
myDb.MODEL::Model = MODEL::new("myDb.db")
; definition table of countries
tbCountries.TABLE::Table = TABLE::new("countries")
tbCountries\addColumn(COLUMN::new("name",COLUMN::#TP_TEXT))
; definition table of cities
tbCities.TABLE::Table = TABLE::new("cities")
tbCities\addColumn(COLUMN::new("countries",COLUMN::#TP_LINKED_TABLE))
tbCities\addColumn(COLUMN::new("name",COLUMN::#TP_TEXT))
tbCities\addColumn(COLUMN::new("postal_code",COLUMN::#TP_INTEGER))
; definition table of customers
tbCustomers.TABLE::Table = TABLE::new("cutomers")
tbCustomers\addColumn(COLUMN::new("cities",COLUMN::#TP_LINKED_TABLE))
tbCustomers\addColumn(COLUMN::new("first_name",COLUMN::#TP_TEXT))
tbCustomers\addColumn(COLUMN::new("sur_name",COLUMN::#TP_TEXT))
tbCustomers\addColumn(COLUMN::new("address",COLUMN::#TP_TEXT))
; add tables into database
myDb\addTable(tbCountries)
myDb\addTable(tbCities)
myDb\addTable(tbCustomers)
; create database
myDb\buildDataBase()
; add some of records
myDb\newRecordValue("countries")
myDb\setStringRecordValue("name","Belgique")
myDb\addRecord()
myDb\newRecordValue("cities")
myDb\setStringRecordValue("name","Waremme")
myDb\setIntegerRecordValue("postal_code",4300)
myDb\setLinkedTableRecordValue("countries",1)
myDb\addRecord()
myDb\newRecordValue("cutomers")
myDb\setLinkedTableRecordValue("cities",1)
myDb\setStringRecordValue("first_name","Dupond")
myDb\setStringRecordValue("sur_name","Alain")
myDb\setStringRecordValue("address","Rue du choux 47")
myDb\addRecord()
Le code du moule Column.pbi
Code : Tout sélectionner
; AUTHOR : MicrodevWeb
; NAME : Table
DeclareModule COLUMN
Enumeration COLUMN_TYPE
#TP_INTEGER
#TP_FLOAT
#TP_DOUBLE
#TP_DATE
#TP_TEXT
#TP_LINKED_TABLE
EndEnumeration
Interface _private
_get_create_request.s()
EndInterface
Interface Column Extends _private
getName.s()
getType()
EndInterface
Declare new(name.s,type = COLUMN::#TP_INTEGER)
EndDeclareModule
Module COLUMN
Structure _struct
*methods
name.s
type.l
EndStructure
;-* PRIVATE FUNCTIONS
Procedure.s _get_request_type(*this._struct)
With *this
Protected request.s
Select \type
Case COLUMN::#TP_TEXT
request = " TEXT "
Case COLUMN::#TP_INTEGER,COLUMN::#TP_LINKED_TABLE
request = " INTEGER "
Case COLUMN::#TP_FLOAT
request = " FLOAT "
Case COLUMN::#TP_DOUBLE
request = " DOUBLE "
Case COLUMN::#TP_DATE
request = " DATE "
EndSelect
ProcedureReturn request
EndWith
EndProcedure
;}
;-* PRIVATE METHODS
Procedure.s _get_create_request(*this._struct)
With *this
Protected request.s
If \type = #TP_LINKED_TABLE
request = "id_"+\name + _get_request_type(*this)
Else
request = \name + _get_request_type(*this)
EndIf
ProcedureReturn request
EndWith
EndProcedure
;}
;-* PUBLIC METHODS
Procedure.s getName(*this._struct)
With *this
ProcedureReturn \name
EndWith
EndProcedure
Procedure getType(*this._struct)
With *this
ProcedureReturn \type
EndWith
EndProcedure
;}
;-* CONSTRUCTOR
Procedure new(name.s,type = COLUMN::#TP_INTEGER)
Protected *this._struct = AllocateStructure(_struct)
With *this
\methods = ?S_MET
\name = name
\type = type
If Len(name)
ProcedureReturn *this
EndIf
ProcedureReturn 0
EndWith
EndProcedure
;}
DataSection
S_MET:
; PRIVATE
Data.i @_get_create_request()
; PUBLIC
Data.i @getName()
Data.i @getType()
E_MET:
EndDataSection
EndModule
Code : Tout sélectionner
; AUTHOR : MicrodevWeb
; NAME : Table
XIncludeFile "Column.pbi"
DeclareModule TABLE
Interface __private
_get_create_request.s()
_get_column_address(column.s)
EndInterface
Interface Table Extends __private
getName.s()
addColumn(column)
EndInterface
Declare new(name.s)
EndDeclareModule
Module TABLE
Structure _struct
*methods
name.s
List myColumns.COLUMN::Column()
Map _myColumns.l()
EndStructure
;-* PRIVATE FUNCTIONS
;}
;-* PRIVATE METHODS
Procedure.s _get_create_request(*this._struct)
With *this
Protected request.s
ForEach \myColumns()
request + \myColumns()\_get_create_request()+","
Next
ProcedureReturn request
EndWith
EndProcedure
Procedure _get_column_address(*this._struct,column.s)
With *this
FindMapElement(\_myColumns(),column)
ProcedureReturn \_myColumns()
EndWith
EndProcedure
;}
;-* PUBLIC METHODS
Procedure.s getName(*this._struct)
With *this
ProcedureReturn \name
EndWith
EndProcedure
Procedure addColumn(*this._struct,*column)
With *this
AddElement(\myColumns())
\myColumns() = *column
AddMapElement(\_myColumns(),\myColumns()\getName())
\_myColumns() = *column
EndWith
EndProcedure
;}
;-* CONSTRUCTOR
Procedure new(name.s)
Protected *this._struct = AllocateStructure(_struct)
With *this
\methods = ?S_MET
\name = name
If Len(name)
ProcedureReturn *this
EndIf
ProcedureReturn 0
EndWith
EndProcedure
;}
DataSection
S_MET:
; PRIVATE
Data.i @_get_create_request()
Data.i @_get_column_address()
; PUBLIC
Data.i @getName()
Data.i @addColumn()
E_MET:
EndDataSection
EndModule
Code : Tout sélectionner
; AUTHOR : MicrodevWeb
; NAME : Model
XIncludeFile "Table.pbi"
DeclareModule MODEL
Interface Model
addTable(table)
buildDataBase()
newRecordValue(table.s)
setIntegerRecordValue(column.s,value.l)
setStringRecordValue(column.s,values.s)
setFloatRecordValue(column.s,values.f)
setDoubleRecordValue(column.s,values.d)
setDateRecordValue(column.s,values.l)
setLinkedTableRecordValue(column.s,values.l)
addRecord()
EndInterface
Declare new(path.s)
EndDeclareModule
Module MODEL
EnableExplicit
UseSQLiteDatabase()
Enumeration
#VL_F
#VL_I
#VL_S
#VL_D
#VL_LI
#VL_DA
EndEnumeration
Structure _recordValue
column.COLUMN::Column
valueF.f
valueD.d
valueI.l
valueT.s
EndStructure
Structure _record
*table.TABLE::Table
List myRecordValues._recordValue()
EndStructure
Structure _struct
*methods
path.s
List myTables.Table::Table()
Map *_myTables()
currentId.l
recordValue._record
EndStructure
;-* PRIVATE FUNCTIONS
Procedure _create_file(*this._struct)
; we create the file if not exist
With *this
Protected idFile
If FileSize(\path) = -1 ; the file don't exists
; we create it
idFile = CreateFile(#PB_Any,\path)
If Not idFile
MessageRequester("FILE MODEL ERROR","Cannot create the database file",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
CloseFile(idFile)
ProcedureReturn #True ; the file already exists
EndIf
ProcedureReturn #True
EndWith
EndProcedure
Procedure _open_base(*this._struct)
With *this
; try to create file
If Not _create_file(*this)
MessageRequester("FILE MODEL ERROR","Cannot create the database file",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
\currentId = OpenDatabase(#PB_Any,\path,"","")
If Not \currentId
MessageRequester("OPEN DATABASE ERROR","Cannot open database",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
; the base is open
ProcedureReturn #True
EndWith
EndProcedure
Procedure _update(*this._struct,request.s)
With *this
If _open_base(*this)
If Not DatabaseUpdate(\currentId,request)
MessageRequester("UPDATE REQUEST DATABASE ERROR",request+Chr(10)+DatabaseError(),#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
CloseDatabase(\currentId)
ProcedureReturn #True
EndIf
; the base cannot open
ProcedureReturn #False
EndWith
EndProcedure
;}
;-* PUBLIC METHODS
Procedure addTable(*this._struct,*table)
With *this
AddElement(\myTables())
\myTables() = *table
AddMapElement(\_myTables(),\myTables()\getName())
\_myTables() = \myTables()
EndWith
EndProcedure
Procedure buildDataBase(*this._struct)
With *this
Protected request.s
If _create_file(*this)
; we create the request
ForEach \myTables()
request = "CREATE TABLE IF NOT EXISTS "+\myTables()\getName()+" ("+
" id INTEGER PRIMARY KEY AUTOINCREMENT, "
; we add its columns
request + \myTables()\_get_create_request()
; we remove the last comma
request = Left(request,Len(request)-1)
; we add a final bracket
request + ")"
If Not _update(*this,request)
ProcedureReturn #False
EndIf
Next
ProcedureReturn #True
EndIf
EndWith
EndProcedure
Procedure newRecordValue(*this._struct,table.s)
With *this
ResetStructure(\recordValue,_record)
FindMapElement(\_myTables(),table)
\recordValue\table = \_myTables()
If Not \recordValue\table
MessageRequester("NEW RECORD VALUE","This don't exists",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
ProcedureReturn #True
EndWith
EndProcedure
Procedure setIntegerRecordValue(*this._struct,column.s,value.l)
With *this
Protected cl.COLUMN::Column = \recordValue\table\_get_column_address(column)
If Not cl
MessageRequester("SET INTEGER RECORD VALUE ERROR","This column "+column+" do not exists",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
If cl\getType() <> COLUMN::#TP_INTEGER
MessageRequester("SET INTEGER RECORD VALUE ERROR","This column "+column+" s not integer type",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
AddElement(\recordValue\myRecordValues())
\recordValue\myRecordValues()\column = cl
\recordValue\myRecordValues()\valueI = value
EndWith
EndProcedure
Procedure setStringRecordValue(*this._struct,column.s,value.s)
With *this
Protected cl.COLUMN::Column = \recordValue\table\_get_column_address(column)
; we verify if the column exists
If Not cl
MessageRequester("SET STRING RECORD VALUE ERROR","This column "+column+" do not exists",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
; we verify if the type is correct
If cl\getType() <> COLUMN::#TP_TEXT
MessageRequester("SET STRING RECORD VALUE ERROR","This column "+column+" is not string type",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
; we verify if this column is not already defined
ForEach \recordValue\myRecordValues()
If \recordValue\myRecordValues()\column = cl
MessageRequester("SET STRING RECORD VALUE ERROR","This column "+column+" is already defined",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
Next
AddElement(\recordValue\myRecordValues())
\recordValue\myRecordValues()\column = cl
\recordValue\myRecordValues()\valueT = value
EndWith
EndProcedure
Procedure setFloatRecordValue(*this._struct,column.s,value.f)
With *this
Protected cl.COLUMN::Column = \recordValue\table\_get_column_address(column)
; we verify if the column exists
If Not cl
MessageRequester("SET FLOAT RECORD VALUE ERROR","This column "+column+" do not exists",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
; we verify if the type is correct
If cl\getType() <> COLUMN::#TP_FLOAT
MessageRequester("SET FLOAT RECORD VALUE ERROR","This column "+column+" is not float type",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
; we verify if this column is not already defined
ForEach \recordValue\myRecordValues()
If \recordValue\myRecordValues()\column = cl
MessageRequester("SET FLOAT RECORD VALUE ERROR","This column "+column+" is already defined",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
Next
AddElement(\recordValue\myRecordValues())
\recordValue\myRecordValues()\column = cl
\recordValue\myRecordValues()\valueF = value
EndWith
EndProcedure
Procedure setDoubleRecordValue(*this._struct,column.s,value.d)
With *this
Protected cl.COLUMN::Column = \recordValue\table\_get_column_address(column)
; we verify if the column exists
If Not cl
MessageRequester("SET DOUBLE RECORD VALUE ERROR","This column "+column+" do not exists",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
; we verify if the type is correct
If cl\getType() <> COLUMN::#TP_DOUBLE
MessageRequester("SET DOUBLE RECORD VALUE ERROR","This column "+column+" is not double type",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
; we verify if this column is not already defined
ForEach \recordValue\myRecordValues()
If \recordValue\myRecordValues()\column = cl
MessageRequester("SET DOUBLE RECORD VALUE ERROR","This column "+column+" is already defined",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
Next
AddElement(\recordValue\myRecordValues())
\recordValue\myRecordValues()\column = cl
\recordValue\myRecordValues()\valueD = value
EndWith
EndProcedure
Procedure setDateRecordValue(*this._struct,column.s,value.l)
With *this
Protected cl.COLUMN::Column = \recordValue\table\_get_column_address(column)
; we verify if the column exists
If Not cl
MessageRequester("SET DATE RECORD VALUE ERROR","This column "+column+" do not exists",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
; we verify if the type is correct
If cl\getType() <> COLUMN::#TP_DATE
MessageRequester("SET DATE RECORD VALUE ERROR","This column "+column+" is not date type",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
; we verify if this column is not already defined
ForEach \recordValue\myRecordValues()
If \recordValue\myRecordValues()\column = cl
MessageRequester("SET DATE RECORD VALUE ERROR","This column "+column+" is already defined",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
Next
AddElement(\recordValue\myRecordValues())
\recordValue\myRecordValues()\column = cl
\recordValue\myRecordValues()\valueI = value
EndWith
EndProcedure
Procedure setLinkedTableRecordValue(*this._struct,column.s,value.l)
With *this
Protected cl.COLUMN::Column = \recordValue\table\_get_column_address(column)
; we verify if the column exists
If Not cl
MessageRequester("SET LINKED TABLE RECORD VALUE ERROR","This column "+column+" do not exists",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
; we verify if the type is correct
If cl\getType() <> COLUMN::#TP_LINKED_TABLE
MessageRequester("SET LINKED TABLE RECORD VALUE ERROR","This column "+column+" is not linked table type",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
; we verify if this column is not already defined
ForEach \recordValue\myRecordValues()
If \recordValue\myRecordValues()\column = cl
MessageRequester("SET LINKED TABLE RECORD VALUE ERROR","This column "+column+" is already defined",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
Next
AddElement(\recordValue\myRecordValues())
\recordValue\myRecordValues()\column = cl
\recordValue\myRecordValues()\valueI = value
EndWith
EndProcedure
Procedure addRecord(*this._struct)
With *this
; the user has not create a record
If Not \recordValue
MessageRequester("ADD RECORD","You are not create a record",#PB_MessageRequester_Error)
ProcedureReturn #False
EndIf
Protected request.s = ""
request = "insert into "+\recordValue\table\getName()+" ("
ForEach \recordValue\myRecordValues()
If \recordValue\myRecordValues()\column\getType() <> COLUMN::#TP_LINKED_TABLE
request + \recordValue\myRecordValues()\column\getName()+","
Else
request +"id_"+\recordValue\myRecordValues()\column\getName()+","
EndIf
Next
; remove last comma
request = Left(request,Len(request) - 1)
request + ") values ("
ForEach \recordValue\myRecordValues()
Select \recordValue\myRecordValues()\column\getType()
Case COLUMN::#TP_TEXT
request + Chr(34) + \recordValue\myRecordValues()\valueT + Chr(34)
Case COLUMN::#TP_INTEGER,COLUMN::#TP_DATE,COLUMN::#TP_LINKED_TABLE
request + Str(\recordValue\myRecordValues()\valueI)
Case COLUMN::#TP_FLOAT
request + StrF(\recordValue\myRecordValues()\valueF)
Case COLUMN::#TP_DOUBLE
request + StrD(\recordValue\myRecordValues()\valueD)
EndSelect
request +","
Next
; remove last comma
request = Left(request,Len(request) - 1)
request + ")"
If Not _update(*this,request)
ProcedureReturn #False
EndIf
ProcedureReturn #True
ResetStructure(\recordValue,_record)
EndWith
EndProcedure
;}
;-* CONTRUCTOR
Procedure new(path.s)
Protected *this._struct = AllocateStructure(_struct)
With *this
\methods = ?S_MET
\path = path
If Len(path)
ProcedureReturn *this
EndIf
ProcedureReturn 0
EndWith
EndProcedure
;}
DataSection
S_MET:
Data.i @addTable()
Data.i @buildDataBase()
Data.i @newRecordValue()
Data.i @setIntegerRecordValue()
Data.i @setStringRecordValue()
Data.i @setFloatRecordValue()
Data.i @setDoubleRecordValue()
Data.i @setDateRecordValue()
Data.i @setLinkedTableRecordValue()
Data.i @addRecord()
E_MET:
EndDataSection
EndModule