PureBasic

Forums PureBasic
Nous sommes le Sam 24/Aoû/2019 10:10

Heures au format UTC + 1 heure




Poster un nouveau sujet Répondre au sujet  [ 1 message ] 
Auteur Message
 Sujet du message: Un module model pour SQLiteDatabase
MessagePosté: Jeu 16/Aoû/2018 12:46 
Hors ligne
Avatar de l’utilisateur

Inscription: Mer 29/Juin/2011 14:11
Messages: 1646
Localisation: Belgique
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:
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:
; 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:
; 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:
; 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.71 beta 2


Haut
 Profil  
Répondre en citant le message  
Afficher les messages postés depuis:  Trier par  
Poster un nouveau sujet Répondre au sujet  [ 1 message ] 

Heures au format UTC + 1 heure


Qui est en ligne

Utilisateurs parcourant ce forum: Aucun utilisateur enregistré et 1 invité


Vous ne pouvez pas poster de nouveaux sujets
Vous ne pouvez pas répondre aux sujets
Vous ne pouvez pas éditer vos messages
Vous ne pouvez pas supprimer vos messages

Rechercher:
Aller à:  
cron

 


Powered by phpBB © 2008 phpBB Group | Traduction par: phpBB-fr.com
subSilver+ theme by Canver Software, sponsor Sanal Modifiye