Database

#1. Introduction

Almost any web application interacts with a database. Cotonti makes this interaction simple and comprehensive.

Currently support is provided for MySQL 5.1+

#2. Configuration

The database connection configuration is located in the application's configuration file datas/config.php. It is filled in automatically during Cotonti installation.

#3. Running SQL Queries

You can get access to the Cotonti database connection instance with the facade class Cot. The Cotonti database object is built on top of the PDO and provides method for executing database queries and methods that simplify the construction of INSERT, UPDATE and DELETE queries.

You can execute a SQL query by taking the following steps:

1. Create a plain SQL query;
2. Bind parameters (optional);
3. Call one of the SQL execution methods in PDOStatement class.

The following example shows various ways of fetching data from a database:

// Return a set of rows. Each row is an associative array of column names and values.
// an empty array is returned if the query returned no results
$posts = Cot::$db->query('SELECT * FROM post')->fetchAll();

// Return a single (first or next) row from a result set
// False is returned if the query has no result
$post = Cot::$db->query('SELECT * FROM post WHERE id=?', 1)->fetch();

// Returns a single column from the next row of a result set
// Or false if there are no more rows.
$titles = Cot::$db->query('SELECT title FROM post')->fetchColumn();
$count = Cot::$db->query('SELECT COUNT(*) FROM post')->fetchColumn();

For queries that do not return data, you should call the PDOStatement::execute() method instead. For example:

Cot::$db->query('UPDATE post SET status=1 WHERE id=1')->execute();

For INSERT, UPDATE and DELETE queries, instead of writing pure SQL, you can call the insert(), update(), delete() methods, respectively, to create the specified SQL constructs. For example:

// INSERT (table_name, column_values)
Cot::$db->insert(
    'posts', 
    [
        'titlle' => 'Lorem ipsum', 
        'text' => 'Morbi imperdiet tortor ut nisl ultricies finibus',
    ]
);
$newPostId = Cot::$db->lastInsertId();

// UPDATE (table_name, column_values, condition, params)
$condition = [
	"created_at < '" . date('Y-m-d H:i:s', time() - 3600) . "'",
	'category = :category',
];
$updatedRowsCount = Cot::$db->update(
    'posts', 
    ['status' => 1], 
    $condition, 
    ['category' => $category]
);

// DELETE (table_name, condition, params)
$deletedRowsCount = Cot::$db->delete(
    'posts', 
    'status = 0 AND category = ?', 
    $category
);

insert(), update() and delete() methods do not require escaping the table name. This will be done automatically.

insert() and update() methods do not require escaping array elements with data for insert/update.

update() and delete() methods accept a condition that can be a string with an SQL condition or an array of strings. In the case of an array, each string will be wrapped in parentheses and they are combined with the AND operator.

In the example above:

$condition = [
	"created_at < '2024-07-08 12:45:56'",
	'category = :category'
];

is equivalent to:

(created_at < '2024-07-08 12:45:56') AND (category = :category)

#3.1. Binding Parameters

When creating a SQL query with parameters, you should almost always use parameter binding to prevent SQL injection attacks. For example:

$post = Cot::$db->query(
	'SELECT * FROM post WHERE category = ? AND status = ?',
	[$category, status]
)->fetch();

When executing the query, each question mark in accordance with its order will be safely replaced with the corresponding value from the array.

If the request has only one parameter, you can pass a scalar value instead of an array:

$post = Cot::$db->query('SELECT * FROM post WHERE category = ?', $category)->fetch();

You can use scalar parameters:

$post = Cot::$db->query(
	'SELECT * FROM post WHERE id = :id AND status = :status',
	['id' => $id, ':status' => status]
)->fetch();

#3.2. Quoting Table and Column Names

Cot::$db->query(
    'SELECT * FROM ' . Cot::$db->quoteTableName(Cot::$db->pages) 
    . ' WHERE page_cat = ' . Cot::$db->quote($category)
)->fetchColumn();

Quoting methods:

• Cot::$db->quoteTableName() or short version (Cot::$db->quoteT())
  Quotes a table name for use in a query. If the table name contains schema prefix, the prefix will also be properly quoted.
• Cot::$db->quoteColumnName() or short version (Cot::$db->quoteC())
  Quotes a column name for use in a query. If the column name contains prefix, the prefix will also be properly quoted. If the column name is already quoted then this method will do nothing.
• Cot::$db->quote()
  Quotes a string value for use in a query. It is recommended to use parameter binding instead of this method, if possible.

#4. Database Transactions

You may use the Cot::$db->transaction() method to run a set of operations within a database transaction. If an exception is thrown within the transaction closure, the transaction will automatically be rolled back and the exception is re-thrown. If the closure executes successfully, the transaction will automatically be committed. You don't need to worry about manually rolling back or committing while using the transaction method:

Cot::$db->transaction(
	function(): void 
	{
		Cot::$db->update('posts', ['status' = 1], 'category = ?', $category);
		Cot::$db->delete('posts', 'status = 0']);
		// ... executing other SQL statements ...
	}
);

#4.1. Manually Using Transactions

You can use transactions manually and have complete control over commits and rollbacks and error handling. The example above is equivalent to this:

Cot::$db->beginTransaction();
try {
    Cot::$db->update('posts', ['status' = 1], 'category = ?', $category);
    Cot::$db->delete('posts', 'status = 0']);
	// ... executing other SQL statements ...
    
    Cot::$db->commit();
} catch(\Throwable $e) {
    Cot::$db->rollBack();
	throw $e;
}

#4.2. Specifying Isolation Level

Cotonti supports setting isolation levels for your transactions. By default, when starting a new transaction, it will use the default isolation level set by your database system. You can override the default isolation level as follows:

$isolationLevel = \cot\database\DataBaseDictionary::REPEATABLE_READ;

Cot::$db->transaction(
	function(): void 
	{
		....
	},
	$isolationLevel
);
 
// or

Cot::$db->beginTransaction($isolationLevel);

#4.3. Handling Deadlocks

The Cot::$db->transaction() method accepts an optional third argument which defines the number of times a transaction should be retried when a deadlock occurs. Once these attempts have been exhausted, an exception will be thrown:

Cot::$db->transaction(
	function(): void 
	{
		....
	},
	$isolationLevel,
	5
);

#5. Working with Database Schema

(section being updated)

Cotonti database object provides several methods to work with database schema:

• tableExists($tableName) - check if table exists
• fieldExists($tableName, $fieldName) - check if fieild exists
• indexExists($tableName, $indexName, $indexColumns = []) - check if index exists
• addIndex($tableName, $indexName, $indexColumns = []) - create Index. Here $indexColumns is either a string for a single column name or an array for single/multiple columns. $indexName will be used if empty.

#6. Execution of SQL scripts

The CotDB::runScript($script, $translaction = false) method allows you to execute SQL scripts (contents of SQL files). Table names must be escaped with the MySQL escape symbol ` and have the cot_ prefix, which will be replaced with the prefix from the settings in datas/config.php.