Un module model pour SQLiteDatabase

Sujets variés concernant le développement en PureBasic
Avatar de l’utilisateur
microdevweb
Messages : 1798
Inscription : mer. 29/juin/2011 14:11
Localisation : Belgique

Un module model pour SQLiteDatabase

Message par microdevweb »

Bonjour à tous,

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
Le code du module Table.pbi

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
Le code du module Model.pbi

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
Windows 10 64 bits PB: 5.70 ; 5.72 LST
Work at Centre Spatial de Liège