DBMS

Database Manager

Database system

The mysql database management system is the only supported system by the spoova framework. The database DB class is the root manager class that makes it easy to connect to the database and perform several operations smartly. In spoova, there are two different ways by which a database connection can be made before operations can be performed. Connections can either be made manually or by default through the use of a default database configuration file.

Manual Database Connection

A manual database connection is one which is made directly or started manually through the code. Through the DB and DBHandler classes, we can easily set up a new connection using the syntax below:

Manual Connection Syntax

  <?php

  use \spoova\mi\core\classes\DB;

  $dbm = (new DB(MiSQL | PDO)); // initialize database class

  // connect and return database operations handler class
  $dbh = $dbm->openDB(DBNAME, DBUSER, DBPASS, DBPORT, DBSERVER, DBSOCKET);

In the sample above, the database class DB was first initilized in the first line. This database class supports two type of connection which are MySQL denoted as "MiSQL" and "PDO". The default connection is PDO. Hence, we can change this default by simply overiding the default option as shown below:

Specifying connection type

  $dbm = (new DB('MiSQL')); // using mysql connection

In the second line, we initialized a database operation handler class. The database handler class is mostly used to perform database operations. Before this class can be used, a valid database connection parameter must be specified based on the connection requirements. For example, the sample format below sets on required parameters for a connection omitting the socket value.

Setting connection parameters

  $dbh = $dbm->openDB(DBNAME, DBUSER, DBPASS, DBPORT, DBSERVER);

The openDB() method of the database class tries to create a new connection. This connection, if successful, it will return the DBHandler class which will make it possible to perform database operations.

Automatic Database Connection

An automatic database connection is one which is dependent on a default database configuration file which the database class inherits its connection parameters from. The default connection parameters are easily loaded from the icore/dbconfig.php file. This file contains connection parameters defined for offline and online environments. Depending on the project environment, spoova internal picks its suitable connection parameters. The connection parameters can be easily configured manually from the mentioned file or by running simple terminal commands like below:

Setting offline connection parameters

  php mi config:dboffline dbname dbuser dbpass dbport dbserver dbsocket

Similarly, we can set up online connection parameters as shown below:

Setting online connection parameters

  php mi config:dbonline dbname dbuser dbpass dbport dbserver dbsocket

The online connection parameters are optional and are expected to be defined manually when the project application is actively deployed on a live enviroment. Once we define the default parameters, the database handler DBHandler class will automatically pick its connection parameters when initialized. Easily, we can start a new connection as shown below:

Setting connection parameters

  <?php

  use \spoova\mi\core\classes\DB;

  $dbm = (new DB); // initialize database class

  $dbh = $dbm->openDB(); // start automatic connection

The openDB() method of the database class tries to create a new connection using default configuration parameters. This connection, if successful, it will return the DBHandler class which will make it possible to perform database operations.

Database Basic Connection

A basic connection is made when a database name is not selected. This may be useful in situations where the connection is required to be used to created a database name or to perform some basic core operations. In this case, a database name parameter will not be specified when connection is made with the openDB method. A sample of this is displayed below:

Basic Database Connection

  $dbm = new DB;

  $dbh = $dbm->open('', DBUSER, DBPASS, DBSERVER, DBPORT); //basic connection

In the sample above, the DBNAME was not specified. This type of connection will only connect to the database without trying to select any database name.

Database Parameters Modification

Database connection parameters that have already been defined by default can be modified by defining the required parameters. Usually in this case, the username or the entire required value must be defined. For example, the code below is one in which only the username parameter is overridden.

Overide default parameter

  $dbm = new DB;

  $dbh = $dbm->open(DBNAME); //overide DBNAME only

In the sample above, other values not defined will be imported from the basic database configuration file.

Testing Connectivity

Whenever a new connection is opened, it is important to check if the connection is successful. With the database class, either check when a database is connection is made or a database name is selected.

Testing Database Connection

  $dbh($dbc = (new DB)->openDB();
  
  if($dbc->active()){

    //run this if database is connected

  } else {

    vdump($dbc->error(true)); // display connection error 

  }

The sample above tests if the default connection is active. However, the database's successful connection does not neccessarily mean that a database has been selected. The best way to test if a database handler is connected to database and a database name has been successfully selected is to test the database handler itself. A sample of this is shown below:

Testing Database Connection with Database Name

  $dbh($dbc = (new DB)->openDB();
  
  if($dbc->active()){

    if( $dbh->isConnected() ) {

        //database name is connected and selected

    }else{

        vdump($dbc->error(true)); // display connection error 
    }

  } else {

    vdump($dbc->error(true)); // display connection error 

  }

From the above sample, the isConnected() will never return true if an error occurs. This means that we can shorten the line of code as shown below:

  $dbh($dbc = (new DB)->openDB();

  if( $dbh->isConnected() ) {

      //database name is connected and selected

  }else{

      vdump($dbc->error(true)); // display connection error 
  }

It should be noted that top level connection parameters will affect subsequent connection when strictly defined. This is further discussed under User Account Control . For the purpose of this tutorial, $dbh will be referred to as $db since it is the database handler class that handles majority of the database crud operations.

Running Database Queries (CRUD)

Database queries are handled using database crud and non-crud operators which are listed below:

SQL setters:

These are methods responsible for setting sql up queries

CRUD Operators:

These are query executors. They tell database on how to process predefined sql queries.

Helper Operators:

Other query executors are helper method which helps to reduce the time frame for performing simple tasks. These are listed and explained below:

table_exists()

This method returns true if a table exists in the database

  $db = (new DB)->openDB();
  
  if($db->table_exists('table_name')) {
      
    //run this code ...
      
  }

column_exist()

This method returns true if a column exists in the database table name

  $db = (new DB)->openDB();
  
  if($db->column_exists('table_name', 'column_name')) {
      
    //run this code ...
      
  }

addColumn()

This method adds a column to database table. The syntax is shown below:

  $db = (new DB)->openDB();
  
  if($db->addColumn([table_name => column_name], type, pipe, definition, default)) {
  
  
    where: 

     table_name  : name of table where column will be added 

     column_name : name of column to be added 

     type        : type of column e.g ( decimal(2,5); varchar(200), e.t.c)

     pipe        : FIRST | AFTER FIELDNAME (After can be replaced with a pipe e.g "|Email" means AFTER Email )

     definition  : field definition (e.g NOT NULL, UNIQUE)

     default     : field default value.
  
  
  }

Note: The type (datetime) will set a default of 1970-01-01 00:00:00 as the default datetime which still translates as zero.

drop()

This method drops a database, database field or column. Examples are shown below

  $db = (new DB)->openDB();
  
  if($db->drop(true)) {
  
    //currently connected database dropped successfully!
  
  }
  
  if($db->drop('table_name', true)) {
  
    //selected table_name of current database dropped successfully!
  
  }
  
  if($db->drop('table_name', 'column_name')) {
  
    //relative column dropped successfully
  
  }

Handling Errors:

When using any of the helper methods, the DBHandler error_exists() and error() method must be supplied an argument of true in order to function as expected. However DBStatus::err() will still return the last error encountered. Errors are discussed below.

Spoova is a silent framework. Most errors are not displayed unless requested.

checking errors

Database Status:

Database status tracker class DBStatus helps to keep track of last executed sql queries and error responses.

database status

Database Relationships:

The database information can be communicated with under three database relationships which are the one-to-one, one to many and many to many database relationships. These relationships are handled through the database model class which determines how information are processed and returned. More information is provided about database relationships here. It may also be important to learn about working with Session, Forms and User classes before proceeding with database models because these classes have some form of close relationship with each other.