Module DB, the foundation of any Data Layer.

 At the heart of any application is the database. Therefore, this segment of the application should be bulletproof.  To prevent repeating code, it should start with the module I call DB.  Module DB it the bottom most module, it is the actual module which interacts with the database.  If later you decide to migrate your database from SQLite to say Postgres, then all you will need to do is tweak THIS Module. 

This module will also contain the Enumeration of "STATE".  By this I mean the current state of the record, is it:

  • New - Created but not yest saved to the database.
  • Clean - The Record is "Untouched". It is Identical to the record saved in the Database.
  • Dirty - The record was loaded from the database, but it has been changed in some manner.
  • DeleteRec - The record has been marked for deletion, but the user has yet to commit the delete to the database.
  • NewDirty - The record is new, but the User is changing something, or the new record is being modified but has never been saved to the database.

These states will be used by the code behind the Form. That blog entry will be coming soon, for now know what these are, and know that they are located on the DB Module.

So, let's define what procedures are needed in the DB Module.

  • Open Database - here we initialize our desired database and open it in preparation for whatever work needs to be performed. if desired this is where you will handle database encryption.
  • Close Database - when database action is completed you must ALWAYS close the database.  Never leave your database in an open state.
  • Query - Executing queries, retrieving information from the database. This could entail retrieving a single field, such as a GetName Procedure, retrieving an entire record, of even thousands of records.
  • Update - This entails changing information in the database, adding records, or deleting records.
  • GetLastID - This is all about retrieving the ID or KEY field of the record you just ADDED (Inserted) to the database.
Let' take a look at the module declaration of our DB Module:

DeclareModule DB
	Declare Open(DBTYPEID.i = DBTYPE::#Sqlite)
	Declare Close(id)
	Declare Query(id,Sql.s)
	Declare Update(id,Sql.s)
	Declare getLastID(dbid.i,table.s)
	;Sqlite  = "Relative/Path/DBName.db"
  ;DDBC    = "Relative/Path/To/Folder"
  ;Postgre = "host=localhost port=5432 dbname=test"
  ;MySQL   = "host=localhost port=3306 dbname=test"
	;MariaDB = "host=localhost port=3306 dbname=test"
	#DB_Name = "DatabaseName.db"
	#DB_ID = ""
	#db_Password = ""
	Enumeration 
		#New 
		#NewDirty
		#Clean
		#Dirty
		#DeleteRec
	EndEnumeration
EndDeclareModule

If you are using a PureBasic Supported Database other than SQLite I have added in, but commented, the other databases you can be using.
Be sure to replace "DatabaseName.db" with your own Database Name. Also note the Enumerations needed for opening your database.
  • #DB_Name - the fixed value of the Name of your Database
  • #DB_ID - User ID used to connect with the database. Note for SQLite this must be "".
  • #DB_Password - The User Password to connect to the database. For SQLite this must be "".
Now I will take each Procedure individually, and make sure that you understand how they work.

One of the great strengths of a Module is to allow you to have private and public procedures. When other Modules and programs interact the can ONLY interact with the Public Procedures, as governed by their Declare signatures.  This allows up to build supporting procedures in which no one can mess with.

Our first Procedure inside the Module is a Private Procedure.

Procedure SetDBType(DBTYPEID.i)
	  Select DBTYPEID
	    Case DBTYPE::#Sqlite
	      UseSQLiteDatabase()
	    Case DBTYPE::#Postgre
	      UsePostgreSQLDatabase()
	    Case DBTYPE::#MySQL
	      UseMySQLDatabase()
	    Case DBTYPE::#MariaDB
	      UseMySQLDatabase()
	    Case DBTYPE::#ODBC
	      UseODBCDatabase()
	  EndSelect
	EndProcedure

This procedure means little outside the context it is used, which is the opening of the database. So, let's look at him too:

	Procedure Open(DBTYPEID.i = DBTYPE::#Sqlite)
	  Protected ProgramDataPath$
	  SetDBType(DBTYPEID)
	  ProgramDataPath$= GetUserDirectory(#PB_Directory_ProgramData)+"DatabasePath\"
	  
; 	  UseSQLiteDatabase("sqlcipher.dll")
; 		Protected id = OpenDatabase(#PB_Any,ProgramDataPath$ + #DB_PATH, #DB_ID, #DB_Password)
;     update(id, "PRAGMA key = 'XXXXXXXXXX'")
	  
		Protected id = OpenDatabase(#PB_Any,ProgramDataPath$ + #DB_Name, #DB_ID, #db_Password)
		If Not id
			MessageRequester("Database Error","Cannot open database",#PB_MessageRequester_Error)
			End
		EndIf
		ProcedureReturn id
	EndProcedure

The OPEN Procedure takes in a single ID of Type DBType.  Note that DBType is an enumerated Type Module which defines the database types that PureBasic supports. Soon I'll have an entry of Type Modules.

So here we call our private SetDBType procedure to give the PureBasic to use the appropriate database. Next, we Identify the path to our database. The call the Open Database command, capturing the database handle in the variable ID, which is passes back to the calling Module.

Next Comes the database close.

	Procedure close(id)
		If IsDatabase(id)
			CloseDatabase(id)
		EndIf
	EndProcedure

This procedure is short and sweet, simply closing the database of the passed in ID.

Then Comes the database query:

	Procedure query(id,Sql.s)
		If Not DatabaseQuery(id,Sql)
			MessageRequester("SQL ERROR",Sql + #LF$ + DatabaseError(),#PB_MessageRequester_Error)
			ProcedureReturn #False
		EndIf
		ProcedureReturn #True
	EndProcedure
Once again, this procedure receives the database ID, and SQL to run. It the runs the SQL and passes back the results.

Next is the Update, now remember this includes SQL Insert, SQL Update, and SQL Delete commands.

	Procedure update(id,Sql.s)
		Protected valRet = DatabaseUpdate(id,Sql)
		If Not valRet
			MessageRequester("SQL ERROR",Sql + #LF$ + DatabaseError(),#PB_MessageRequester_Error)
		EndIf
		
		ProcedureReturn valRet
	EndProcedure
Once again, this procedure receives the database ID, and SQL to run. It the runs the SQL and passes back the results.

Our final Procedure is the GetLastID.

	Procedure getLastID(dbid.i, table.s)
		Protected req.s,index
		req = "Select last_insert_rowid(), ID from " + table
		 If DatabaseQuery(dbId, req)
		 	While NextDatabaseRow(dbid)
		 		index = GetDatabaseLong(dbId,DatabaseColumnIndex(DBID,"ID"))
		 	Wend
		 EndIf

		ProcedureReturn index
	EndProcedure
br />



Here this procedure receives the database ID, and the Table Name.  Here we retrieve the last record on the table and return its table ID field.

Putting all these code segments to0gether you get this:

; **************************************************************
; Project		: ClassBuilder V2
; Author		 : Pete Hollyer
; Module		 : DB.pbi
; **************************************************************
XIncludeFile "../Types/DBType.pbi"

DeclareModule DB
	Declare Open(DBTYPEID.i = DBTYPE::#Sqlite)
	Declare Close(id)
	Declare Query(id,Sql.s)
	Declare Update(id,Sql.s)
	Declare getLastID(dbid.i,table.s)
	;Sqlite  = "Relative/Path/DBName.db"
  ;DDBC    = "Relative/Path/To/Folder"
  ;Postgre = "host=localhost port=5432 dbname=test"
  ;MySQL   = "host=localhost port=3306 dbname=test"
	;MariaDB = "host=localhost port=3306 dbname=test"
	#DB_Name = "DatabaseName.db"
	#DB_ID = ""
	#db_Password = ""
	Enumeration 
		#New 
		#NewDirty
		#Clean
		#Dirty
		#DeleteRec
	EndEnumeration
EndDeclareModule

Module DB
	EnableExplicit
	
	Procedure SetDBType(DBTYPEID.i)
	  Select DBTYPEID
	    Case DBTYPE::#Sqlite
	      UseSQLiteDatabase()
	    Case DBTYPE::#Postgre
	      UsePostgreSQLDatabase()
	    Case DBTYPE::#MySQL
	      UseMySQLDatabase()
	    Case DBTYPE::#MariaDB
	      UseMySQLDatabase()
	    Case DBTYPE::#ODBC
	      UseODBCDatabase()
	  EndSelect
	EndProcedure
	
	Procedure Open(DBTYPEID.i = DBTYPE::#Sqlite)
	  Protected ProgramDataPath$
	  SetDBType(DBTYPEID)
	  ProgramDataPath$= GetUserDirectory(#PB_Directory_ProgramData)+"DatabasePath\"
	  
; 	  UseSQLiteDatabase("sqlcipher.dll")
; 		Protected id = OpenDatabase(#PB_Any,ProgramDataPath$ + #DB_PATH, #DB_ID, #DB_Password)
;     update(id, "PRAGMA key = 'XXXXXXXXXX'")
	  
		Protected id = OpenDatabase(#PB_Any,ProgramDataPath$ + #DB_Name, #DB_ID, #db_Password)
		If Not id
			MessageRequester("Database Error","Cannot open database",#PB_MessageRequester_Error)
			End
		EndIf
		ProcedureReturn id
	EndProcedure
	
	Procedure close(id)
		If IsDatabase(id)
			CloseDatabase(id)
		EndIf
	EndProcedure
	
	Procedure query(id,Sql.s)
		If Not DatabaseQuery(id,Sql)
			MessageRequester("SQL ERROR",Sql + #LF$ + DatabaseError(),#PB_MessageRequester_Error)
			ProcedureReturn #False
		EndIf
		ProcedureReturn #True
	EndProcedure
	
	Procedure update(id,Sql.s)
		Protected valRet = DatabaseUpdate(id,Sql)
		If Not valRet
			MessageRequester("SQL ERROR",Sql + #LF$ + DatabaseError(),#PB_MessageRequester_Error)
		EndIf
		
		ProcedureReturn valRet
	EndProcedure
	
	Procedure getLastID(dbid.i, table.s)
		Protected req.s,index
		req = "Select last_insert_rowid(), ID from " + table
		 If DatabaseQuery(dbId, req)
		 	While NextDatabaseRow(dbid)
		 		index = GetDatabaseLong(dbId,DatabaseColumnIndex(DBID,"ID"))
		 	Wend
		 EndIf

		ProcedureReturn index
	EndProcedure
	
EndModule
This completes this Blog Entry on Module DB.  My Next entry will be the using of this in a real-life scenario.




Comments

Post a Comment

Popular posts from this blog

How to build this without writing Code

I'm not sure if I should do this now, because I have not explained a number of additional aspects to the code, but for those of you who are interested.  The foundation of what I shown you here, IS the foundation of code my PBXClassBuilder-Standard generates for you.  And I truly mean generates  for you. You can have all this code generated for you without writing a single line of it yourself. Now PBXClassBuilder-Standard is not perfect, it is approximately 90=95% accurate. And I am constantly tweaking it edging this number ever higher. To use PBXClassBuilder-Standard, You should start with Forms already laid out and built.  Within PBXClassBuilder-Standard you use intuitive screens to define your table structure, while linking the Table Fields to their intended form Fields.  Then link the Tables together as Parent Child, and finally you simply click buttons to generate the code, Including the SQL Code to create the tables. The Whole development process, post For...
Read more

Connecting the Forms to the Data-Layer (part 8)

 So now we are down to just the SolarSystem Code Sheet. This is NOT a rubberstamp of the Galaxy. The MAIN reason is that the Galaxy Records are Sourced directly from the Database, As you will recall our SolarSystem is Sourced from the Galaxy Record's Solar System Collection NOT the Database. This makes for a VERY Significant difference between in the code. Our Procedure are the save ALL the Steps are the same, the differences are in the details. Starting once again at the top, we have our declares: Define quitfrmSolarSystem Declare SolarSystemCustom() Declare SolarSystemSetScreen() Declare SolarSystemCreate() Declare SolarSystemModify() Declare SolarSystemDelete() Declare SolarSystemSetData() Declare SolarSystemSave() Declare SolarSystemClose() And then, as before the SetScreen Procedure, and Control-Loop. Procedure SolarSystemSetScreen() Protected String$, x, y, w, h, i, ID Shared WorkSolarSystem Protected EventID, MenuID, GadgetID, WindowID, Index Shared quitfrm...
Read more

Connecting the Forms to the Data-Layer (part 9)

Here is the whole View Code Sheet Define quitfrmSolarSystem Declare SolarSystemCustom() Declare SolarSystemSetScreen() Declare SolarSystemCreate() Declare SolarSystemModify() Declare SolarSystemDelete() Declare SolarSystemSetData() Declare SolarSystemSave() Declare SolarSystemClose() Procedure SolarSystemSetScreen() Protected String$, x, y, w, h, i, ID Shared WorkSolarSystem Protected EventID, MenuID, GadgetID, WindowID, Index Shared quitfrmSolarSystem quitfrmSolarSystem = #False If Window_frmSolarSystem() SolarSystemCustom() SetActiveWindow(#Window_frmSolarSystem) ;Set Screen Data SetGadgetText(#Gadget_frmSolarSystem_txtName, WorkSolarSystem\Name) SetGadgetText(#Gadget_frmSolarSystem_txtGrow, Str(WorkSolarSystem\GRow)) SetGadgetText(#Gadget_frmSolarSystem_txtGCol, Str(WorkSolarSystem\GCol)) SetActiveGadget(#Gadget_frmSolarSystem_txtName) Repeat EventID =WaitWindowEvent() MenuID =EventMenu() GadgetID =EventG...
Read more