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->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 function:

sql_exec($sql, $parameters = array(), $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, $parameters = array())

Same function as sql_exec but newer 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, $parameters = array(), $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, $parameters = array(), $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, $parameters = array(), $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, $parameters = array(), $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 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.

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 = array();
    $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_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.
Page generated in 0.059 seconds.