Database operations

Database Operations

Database operation

Database operations are executed mainly through the database handler DBHandler class. This class makes it possible to run queries easily through predefined helper methods. The features and functions provided through this class are quite important to a level in which they are also extended to database relationships. Here, we will focus on the basic database operations that can be performed through the database handler class.

Running Database Queries

The basic database operations include creating, updating, reading and deleting from data from the database. The DBHandler class provides some basic methods which makes it easier to perform database operations. These methods are grouped below based on the operation they perform

SQL setters: These are methods responsible for setting sql up queries. The main function of these methods is to provide a concise form of constructing sql queries in the format in which they can be easily processed. These methods are listed below:

CRUD Operators: These methods are mainly used to process or execute predefined sql queries. Their secondary purpose is to make it easier to recognize the type of database operation performed.

Helper Operators: These methods provide a form of ease when running database operations. They are mostly stand alone operators that are used to run fast operations on database tables such as creating or dropping tables, checking if a table exist, fetching database tables or table columns and so on. There are varieties of methods under these group and they are listed below

Database Relationships

Other database operations are performed through database relationships which involves communicating with the database using database structures or models which have close interaction with session accounts. More information is provided on database relationships here.

Database Relationships

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.

Docs Database Operations