Sql

Allows the use of different database servers, while provides unified code base and error handling.
The sql module uses php pdo extension, thus your php have to the PDO installed.
The main functions of sql module is:

Sql database settings

In order to connect an sql database you have to place the appropriate connection settings to site settings in _settings.php 

    global $db;

    //mandatory to connect:
    $db->servertype = "mysql";
    $db->host = "127.0.0.1";
    $db->name = "web_example_db";
    $db->user = "web_db_user";
    $db->password = "secretpass1";

    //optional settings:
    $db->sqlencoding = "utf8";
    $db->schema_editor_password = "secretpass2"; //empty means disabled!
    $db->schema_editor_allowed_for_admin = true;
    //$db->schema_editor_allowed_only_for_login = 'superadmin';

Mandatory settings

Optional settings

Connect and disconnect

Before first time connect to an sql database please check your php have the appropriate sql driver available!

If your sql setting are correctly set in _settings.php the CodKep will automatically connect to the specified database. It means that usually does not need to explicit call connect and disconnect function!

Connect to the sql database

sql_connect()

Disconnects the current opened database

sql_disconnect()

The connect and disconnect functions does not have parameters. The connection is done according to the settings (see above).
If the connection is failed the execution is automatically redirected to the sql_connection_error sql error handling location, which print the sql error message by default.

An sql connection error as looks in a browser:

sqlconnect_error.png

You can test if a database opened or by checking the value of $db->open variable. Usually does not necessary to check this, because all further sql related functions will done this, or do correct error handling.

Note: On successful sql connect the HOOK_sql_connected hook is activated

Execute SQL commands

You can execute arbitrary sql command by the following functions in this section or you use the general database CRUD interface described later. These functions here are "low level" sql functions they almost directly pass the sql commands to the underlying driver interface.

sql_exec($sql,array $parameters = [],$errormsg = '')

This function returns an executed pdo object. See php pdo documentation 

$r1 = sql_exec('SELECT name FROM users;');

$login = 'alice';
$r2 = sql_exec('SELECT name FROM users WHERE login=:login;' , [':login' => $login] );

$secret = 'secretpassword';
sql_exec('UPDATE users SET pwd=:password WHERE login=:login;',
         [':login' => $login,
          ':password' => $secret],
          'Cannot set the password');

In case of error occurred the function returns NULL. Usually does not need to do error handling because the function do automatically.

See error handling section to learn how to handle errors.

sql_exec_noredirect($sql,array $parameters = [])

Same function as sql_exec but never do any redirection in case of error. Useful when sql used in some special hooks where the redirection is not possible or would create a loop.

sql_exec_fetch($sql,array $parameters = [],$errormsg = '')

Executes and fetch an sql command. The parameters and the error handle works similar way as sql_exec the only difference is that this function calls fetch on the executed statement, consequently returns an array instead of pdo object.

It returns an executed and fetched php array. (An associative array of the first record of the result) 

$arr = sql_exec_fetch('SELECT name FROM users WHERE login=:login;' , [':login' => $login] );
print "Name:".$arr['name'];

This function raise error when the result is empty. (In the example above, when the $login is not found in users table the function will fail) If you would like to allow the empty result use the sql_exec_fetchN instead of this.

See error handling section to learn how to handle errors.

sql_exec_fetchN($sql,array $parameters = [],$errormsg = '')

Executes and fetch an sql command. The parameters and the error handle works similar way as sql_exec the only difference is that this function calls fetch on the executed statement, consequently returns an array instead of pdo object. Allows empty result. It returns an executed and fetched array. 

$arr = sql_exec_fetch('SELECT name FROM users WHERE login=:login;' , [':login' => $login] );
print "Name:". (empty($arr) ? "Unknown" : $arr['name']);

See error handling section to learn how to handle errors.

sql_exec_single($sql,array $parameters = [],$errormsg = '')

Executes and fetch an sql command and returns the first field of the first received record. It returns a single value. This function is useful if a single value is queried from the database. 

print "Time:";
print sql_exec_single('SELECT current_time;');

See error handling section to learn how to handle errors.

sql_exec_fetchAll($sql,array $parameters = [],$errormsg = '',$fetch_names_only = false)

Executes an sql command and call fetchAll() on it. It means that this function returns an array containing all of the result set rows. In a nutshell the result is array of associative arrays of resulted records.

If $fetch_names_only parameter is false the fetchAll function called with fetch_style=PDO::FETCH_NAMED otherwise the default mode PDO::FETCH_BOTH is used. To understand this see PDO fetchAll() documentation. 

print "<ul>";
$allres = sql_exec_fetchAll('SELECT name FROM users;');
foreach($allres as $res)
 print "<li>".$res['name']."</li>";
print "</ul>";

See error handling section

sql_exec_fetchAll_noredirect($sql,array $parameters = [],$fetch_names_only = false)

Same as sql_exec_fetchAll except the automatic error redirection is disabled.

See error handling section

sql_getLastInsertId($tablename,$keyname,$specifiedname = '')

Try to get the value of the last inserted key. (Through the php lastInsertId() function). On different SQL platform the required sequence name differs. Because of this the function requires the sql table name in the $tablename parameter and the name of the key field in the $keyname parameter. You can pass an optional $specifiedname parameter which overrides the internal logic and (if given) the function calls the lastInsertId() with this.

General database CRUD interface

The CodKep's sql module have a database Create/Read/Update/Delete interface which can generate the sql commands dynamically. It makes easier to create polymorph/variable database actions, or simply creates cleaner look database operations.
This interface uses the CodKep's internal sql functions to execute so that the transaction and error handling is same that you get in sql_exec_* functions.

Demonstration exaples: 

sql_exec_single(
 'SELECT name FROM students WHERE sid=:sidp',
 [':sidp' => 5]);


db_query('students')
  ->get('name')
  ->cond_fv('sid',5,'=')
  ->execute_to_single();


sql_exec('
  SELECT name,identifier FROM students
  INNER JOIN exams ON exams.stid=students.sid
  WHERE exams.eid=:eidp AND exams.result > :resp
  ORDER BY name',
  [':eidp' => 15,':resp' => 1]);


db_query('students')
  ->get_a(['name','identifier'])
  ->join_ffe('exams','','stid','sid')
  ->cond_fv('eid',15,'=')
  ->cond_fv('result',1,'>')
  ->sort('name')
  ->execute();


sql_exec('
  INSERT INTO students(name,bday)
  VALUES(:namep,:bdayp)',
  [':namep' => 'Dave',
   ':bdayp' => '2005-01-03']);


db_insert('students')
  ->set_fv_a(['name' => 'Dave',
              'bday' => '2005-01-03'])
  ->execute();


sql_exec('
  UPDATE exams SET result=:resp
  WHERE stid=:studentp AND result IS NULL',
  [':resp' => 3,
   ':studentp' => 135]);


db_update('exams')
  ->set_fv('result',3)
  ->cond_fv('stid',135,'=')
  ->cond_fnull('result')
  ->execute();

Using of the general database interface:

The interface has 4 starter function to create the four base operation.

All four function above returns a DatabaseQuery descendant object which have many methods to customize/set the features of the desired database operation.

Methods of DatabaseQuery

The recommended way to create new a condition object (which can be add to the DatabaseQuery cond_* or join_* methods is to calling one of the following two function:

Both function returns a DatabaseCond object. Every DatabaseCond object contains an array of conditions (connected with the defined logical relation). You can add sub conditions with the methods of DatabaseCond. This sub conditions can be stacked with the cond() method which also receives a DatabaseCond object.

Methods of DatabaseCond

Let's see a more complex condition example where more condition object is stacked each other: 

/*
SELECT name FROM users
WHERE (gender = 'm' AND name REGEXP '^D') OR
      (gender = 'f' AND NOT( name REGEXP '^A' OR name REGEXP '^B' ));
*/

db_query('users')
    ->get('name')
    ->cond(
            cond('or')
             ->add( cond('and')
                     ->fv('gender','m','=')
                     ->fv('name','^D','regex')
                  )
             ->add( cond('and')
                      ->fv('gender','f','=')
                      ->add(not_cond('or')
                             ->fv('name','^A','regex')
                             ->fv('name','^B','regex'))
                  )
          )
    ->execute_to_arrays()

Keep in mind that the built in top level condition object (which default exists in DatabaseQuery to make it possible to add more conditions top level) has always AND logic. It's means that you have to add one sub condition object with OR logic to achieve top level OR conditions between arguments. You can see this method in the example above.

Later the node module extends this interface to query nodes more easy way, see node_query() for more details.

The FIELDSPEC typed parameters

Most methods in DatabaseQuery and DatabaseCond can receive field specification. This can be done two way:

  1. A simple string
    • This case the string contains the name of the field
    • Example: "name"
  2. Array of strings
    • This case the array should contains two string where the first have to specify the container while the second specify the field by name
    • Example: ["users","apple"]

Options available in methods

Available options in methods. (This options can be mixed together when available)

namevalueDescriptionAvailable here
"function"stringEmbrace the value in a function callset_fv(), set_fe(), get(), get_a(), sort()
"more_args"stringAdds additional arguments to the function callset_fv(), set_fe(), get(), get_a(), sort()
"direction""REVERSE"Reverse the sorting directions on this fieldsort()
"opposite"trueNegate the condition's result where setcond_ff(), cond_fv(), cond_fe(), cond_fb(), cond_fnull(), ff(), fv(), fe(), fb(), fnull()
"f1function"stringEmbrace the field 1 in a function callcond_ff(), ff()
"f2function"stringEmbrace the field 2 in a function callcond_ff(), ff()
"ffunction"stringEmbrace the field in a function callcond_fv(), cond_fe(), cond_fb(), cond_fnull(), fv(), fe(), fb(), fnull()
"vfunction"stringEmbrace the value in a function callcond_fv(), fv()
"efunction"stringEmbrace the expression in a function callcond_fe(), fe()
"noredirect"trueDisable automatic page redirection in case of errorexecute(), execute_to_arrays()
"errormsg"stringCustom error message, which is passed to the underlying sql_exec_* functions. See hereexecute(), execute_and_fetch(), execute_to_row(), execute_to_single(), execute_to_arrays()
"fetch_names_only"trueThis options is set the fetch_names_only parameter of sql_exec_fetchAll(). See hereexecute_to_arrays()

Auto join system

You can register table+field pairs in db query system which is usable to join tables with less code.
In a properly set environment you only have to tell which table to join, the system will do the field matches according to the registrations done with the following function:

db_register_autojoin($join_this_container, $to_this_container, $this_fieldname, $to_fieldname, $mode = ONE_WAY)
Registers a new auto join hint in system.

Example: 

function hook_mymodule_init()
{
    db_register_autojoin('people','address', 'addr', 'a_id', TWO_WAY);
}

After you do this registration above you can use the join_auto() and join_opt_auto() function in db queries. 

$r = db_query('people')
     ->get('name')
     ->join_auto('address')
     ->get('address_string')
     ->sort('name')
     ->execute_to_arrays();

// Here the join_auto('address') will be translated to
//    join_ffe('address','',['people','addr'],['address','a_id])

print to_table($r);

Simple and easy database operations

There is a simplified database access interface for simple database manipulations based on DatabaseQuery (The Database CRUD interface described above) This operations is usable for simple insert/delete/update/list elements from a single sql table.

You can use the following helper function to use this simplified interface:

db_x($tablename)

This function above (means db eXecute) creates a SimpleDatabaseQuery instance which has the following methods:

In the functions above the $field_value_filters array values can be the following "magic strings":

Examples using of this simple database manipulation interface 

//Inserts a new record to the "mysqltable" table
//with the describe:'Lorem ipsum' and height:150 values
db_x('mysqltable')->add(['describe' => 'Lorem ipsum','height' => 150]);

//Delete the id:123 record from the "mysqltable" table
db_x('mysqltable')->del(['id' => 123]);

//Change the name field value to "Dave" on "names" table id:112 record.
db_x('names')->update(['name' => 'Dave'],['id' => 112]);

//Get the following field values of the id:112 record from "names" table.
$a = db_x('names')->get(['name','id','age'],['id' => 112]);
print implode(', ',$a);

//List all employe from "names" sorted by name and descending to age with the specified fields.
$a = db_x('names')->lst(['name','id','age'],['employee' => '##TRUE##'],['name','-age']);
print to_table($a);

//Simply get the name of the id:88 from "names" table.
$n = db_x('names')->get('name',['id' => 88]);
print "The name is $n";

Sql transactions

It's possible to use sql transactions, the sql module have helper functions to do this. The transaction manage functions works on the current database connections.

Functions to manage transactions

Every transaction should begin by calling sql_transaction(). There is no way to nest sql transactions, thus every sql_transaction() should proceed some other function which rollback or commits the transaction.

Note: If you start a new transaction in another opened transaction the system will raise an error message

Example to use transactions 

//Example to use transactions
sql_transaction();
sql_exec("UPDATE systable SET value=:val WHERE key=:key;",
         [':key' => $key,
          ':val' => $value]);
//In case the update failed the execution is redirected to error handling page,
//thus the sql_commit won't executed
sql_commit();

Without automatic error redirection 

$db->auto_error_page = false;
sql_transaction();
sql_exec("UPDATE systable SET value=:val WHERE key=:key;",
         [':key' => $key,
          ':val' => $value]);
if($db->error)
{
    sql_rollback();
    //other error handling
    return;
}
sql_commit();

If you have started transaction the $db->transaction value is true otherwise is false.

Error handling

Most sql related function can failed by any reasons. Every time you call a functions of sql module the following processes will be done:

  1. Set $db->error to false and $db->errormsg to empty
  2. Set the $db->lastsql value to the command will be executed
  3. Do the sql related work
    • In case of success, will normally exit
    • In case of error
      1. Set $db->error to true
      2. Fill the $db->errormsg with an available error message.
      3. By default do an automatic redirection to an sql error page. It means that the execution won't continue after the failed function call.

In a nutshell usually you does not have to care about error handling because the site will automatically stop the execution in case of error, and will do a redirection to an sql error page and show an error message.

An example of an error:

sqlerror.png

In case of error the following sql error routes are used:

Functionspath
sql_connect(), sql_disconnect()sql_connection_error
sql_exec_*(), sql_transaction(), sql_commit(), sql_rollback(), etc...sql_error

You can disable the automatic error redirection by set the $db->auto_error_page to false. If you do this way you should check the value of $db->error after every sql command.

In case you disabled the automatic error redirection and you handle and error, you can always find the available error message in $db->errormsg and the last executed sql command in $db->lastsql variables.

By default two default built in error handling route exists in CodKep the:

You can redefine this error targets in case you need a special kind of error handling. For example: In case you create REST interfaces you need to send special HTTP response code and error message instead of built in methods. (Which responds with http/html of course) Redefining of this error targets can be done by the $db->error_locations global variable the following way: 

global $db;

$db->error_locations['connection_error'] = 'my_sql_connection_error_route';
$db->error_locations['generic_error'   ] = 'my_sql_generic_error_route';

Of course you need to define these routes above and associate with your callbacks as described here.

Schema editor

The CodKep core itself is not depend on sql, it means that the CodKep inner core does not have any sql schema requirements.
On the other hand further system and user modules can have some sql schema requirements, to achieve their functionality. (Some system modules like user, node or file also have sql schema requirements)

The CodKep's sql module has a schema editor page, which can check the schema requirements of the active modules, and can update the schema if necessary. After a clean codkep install the schema editor can build the initial database structure for the codkep's sql related system modules.

The schema editor is available on sqlschema internal location.

  • http://yoursite.com/index.php?q=sqlschema
  • http://yoursite.com/sqlschema (if clean url's enabled)

A schema editor page looks like similar way: schemaeditor.png

In order to reach schema editor page, you should set the schema editor password or admin access.
See sql settings

Define sql schema requirements

The modules can define their sql schema requirements by implementing HOOK_required_sql_schema hook. This hook does not receive parameters, it returns an associative array which describe the required schema.

The structure of return array of the following

It looks like this way in a real example: 

//Implemented "required_sql_schema" of meetroom module.
function hook_meetroom_required_sql_schema()
{
    $t = [];
    $t['meetroom_module_meetings_table'] =
        [
            "tablename" => 'meetings',
            "columns" => [
                'id'        => 'SERIAL',
                'room'      => 'VARCHAR(3)',
                'takeby'    => 'VARCHAR(16)',
                'title'     => 'VARCHAR(256)',
                'prior'     => 'NUMERIC(3)',
                'modd'      => 'TIMESTAMP',
                'mdate'     => 'DATE',
                'mstart'    => 'VARCHAR(8)',
                'mend'      => 'VARCHAR(8)',
            ],
        ];
    return $t;
}

If you implement the HOOK_required_sql_schema hook with a correct return value, the schema requirements will display in schema editor page and the requirements can be met with some clicks.

Hooks

The following hooks can be implement to interact with sql module.

HookDescription
HOOK_required_sql_schema()Define SQL schema requirements of a module
HOOK_before_sql_schema_collection()Runs before the sqlschema code collects the sql requirements. Usable to include dynamic codes which contains definitions.
HOOK_sql_connected()Runs after the sql connection is established
HOOK_execute_sql($sql,$parameters)Runs before an sql command is executed. It received the sql command and parameters but cannot modify its.
HOOK_begin_generate_sql($object)Runs before an sql command is generated by DatabaseQuery descendant class.
HOOK_end_generate_sql($object)Runs after an sql command is generated by DatabaseQuery descendant class.
HOOK_sql_show_builtin_connerror_page()Runs immediately before the built in sql connection error page is delivered.
HOOK_sql_show_builtin_error_page()Runs immediately before the built in sql general error page is delivered.