Newer
Older
Angie Byron
committed
<?php
/**
* @file
* Hooks related to the Database system and the Schema API.
*/
Alex Pott
committed
use Drupal\Core\Database\Query\Condition;
Angie Byron
committed
/**
* @defgroup database Database abstraction layer
* @{
* Allow the use of different database servers using the same code base.
*
* @section sec_intro Overview
* Drupal's database abstraction layer provides a unified database query API
* that can query different underlying databases. It is built upon PHP's
* PDO (PHP Data Objects) database API, and inherits much of its syntax and
* semantics. Besides providing a unified API for database queries, the
* database abstraction layer also provides a structured way to construct
* complex queries, and it protects the database by using good security
* practices.
*
* For more detailed information on the database abstraction layer, see
Alex Pott
committed
* https://www.drupal.org/docs/8/api/database-api/database-api-overview.
Angie Byron
committed
*
* @section sec_entity Querying entities
* Any query on Drupal entities or fields should use the Entity Query API. See
* the @link entity_api entity API topic @endlink for more information.
*
* @section sec_simple Simple SELECT database queries
* For simple SELECT queries that do not involve entities, the Drupal database
Alex Pott
committed
* abstraction layer provides the functions \Drupal::database()->query() and
* \Drupal::database()->queryRange(), which execute SELECT queries (optionally
* with range limits) and return result sets that you can iterate over using
* foreach loops. (The result sets are objects implementing the
* \Drupal\Core\Database\StatementInterface interface.)
Angie Byron
committed
* You can use the simple query functions for query strings that are not
* dynamic (except for placeholders, see below), and that you are certain will
* work in any database engine. See @ref sec_dynamic below if you have a more
* complex query, or a query whose syntax would be different in some databases.
*
Alex Pott
committed
* Note: \Drupal::database() is used here as a shorthand way to get a reference
* to the database connection object. In most classes, you should use dependency
* injection and inject the 'database' service to perform queries. See
* @ref sec_connection below for details.
Angie Byron
committed
*
* To use the simple database query functions, you will need to make a couple of
* modifications to your bare SQL query:
* - Enclose your table name in {}. Drupal allows site builders to use
* database table name prefixes, so you cannot be sure what the actual
* name of the table will be. So, use the name that is in the hook_schema(),
* enclosed in {}, and Drupal will calculate the right name.
* - Instead of putting values for conditions into the query, use placeholders.
* The placeholders are named and start with :, and they take the place of
* putting variables directly into the query, to protect against SQL
* injection attacks.
* - LIMIT syntax differs between databases, so if you have a ranged query,
Alex Pott
committed
* use \Drupal::database()->queryRange() instead of
* \Drupal::database()->query().
Angie Byron
committed
*
* For example, if the query you want to run is:
* @code
* SELECT e.id, e.title, e.created FROM example e WHERE e.uid = $uid
* ORDER BY e.created DESC LIMIT 0, 10;
* @endcode
* you would do it like this:
* @code
Alex Pott
committed
* $result = \Drupal::database()->queryRange('SELECT e.id, e.title, e.created
Angie Byron
committed
* FROM {example} e
* WHERE e.uid = :uid
* ORDER BY e.created DESC',
* 0, 10, array(':uid' => $uid));
* foreach ($result as $record) {
* // Perform operations on $record->title, etc. here.
* }
* @endcode
*
* Note that if your query has a string condition, like:
* @code
* WHERE e.my_field = 'foo'
* @endcode
* when you convert it to placeholders, omit the quotes:
* @code
* WHERE e.my_field = :my_field
* ... array(':my_field' => 'foo') ...
* @endcode
*
* @section sec_dynamic Dynamic SELECT queries
* For SELECT queries where the simple query API described in @ref sec_simple
* will not work well, you need to use the dynamic query API. However, you
* should still use the Entity Query API if your query involves entities or
* fields (see the @link entity_api Entity API topic @endlink for more on
* entity queries).
*
* The dynamic query API lets you build up a query dynamically using method
* calls. As an illustration, the query example from @ref sec_simple above
* would be:
* @code
Alex Pott
committed
* $result = \Drupal::database()->select('example', 'e')
Angie Byron
committed
* ->fields('e', array('id', 'title', 'created'))
* ->condition('e.uid', $uid)
* ->orderBy('e.created', 'DESC')
* ->range(0, 10)
* ->execute();
* @endcode
*
* There are also methods to join to other tables, add fields with aliases,
Alex Pott
committed
* isNull() to query for NULL values, etc. See
* https://www.drupal.org/developing/api/database for many more details.
Angie Byron
committed
*
* One note on chaining: It is common in the dynamic database API to chain
* method calls (as illustrated here), because most of the query methods modify
* the query object and then return the modified query as their return
* value. However, there are some important exceptions; these methods (and some
* others) do not support chaining:
* - join(), innerJoin(), etc.: These methods return the joined table alias.
* - addField(): This method returns the field alias.
* Check the documentation for the query method you are using to see if it
* returns the query or something else, and only chain methods that return the
* query.
*
Alex Pott
committed
* @section sec_insert INSERT, UPDATE, and DELETE queries
Angie Byron
committed
* INSERT, UPDATE, and DELETE queries need special care in order to behave
Alex Pott
committed
* consistently across databases; you should never use
* \Drupal::database()->query() to run an INSERT, UPDATE, or DELETE query.
* Instead, use functions \Drupal::database()->insert(),
* \Drupal::database()->update(), and \Drupal::database()->delete() to obtain
* a base query on your table, and then add dynamic conditions (as illustrated
* in @ref sec_dynamic above).
*
* Note: \Drupal::database() is used here as a shorthand way to get a reference
* to the database connection object. In most classes, you should use dependency
* injection and inject the 'database' service to perform queries. See
* @ref sec_connection below for details.
Angie Byron
committed
*
* For example, if your query is:
* @code
* INSERT INTO example (id, uid, path, name) VALUES (1, 2, 'path', 'Name');
* @endcode
* You can execute it via:
* @code
* $fields = array('id' => 1, 'uid' => 2, 'path' => 'path', 'name' => 'Name');
Alex Pott
committed
* \Drupal::database()->insert('example')
Angie Byron
committed
* ->fields($fields)
* ->execute();
* @endcode
*
* @section sec_transaction Transactions
* Drupal supports transactions, including a transparent fallback for
* databases that do not support transactions. To start a new transaction,
Alex Pott
committed
* call startTransaction(), like this:
* @code
* $transaction = \Drupal::database()->startTransaction();
* @endcode
* The transaction will remain open for as long as the variable $transaction
* remains in scope; when $transaction is destroyed, the transaction will be
* committed. If your transaction is nested inside of another then Drupal will
* track each transaction and only commit the outer-most transaction when the
* last transaction object goes out out of scope (when all relevant queries have
* completed successfully).
Angie Byron
committed
*
* Example:
* @code
* function my_transaction_function() {
Alex Pott
committed
* $connection = \Drupal::database();
Angie Byron
committed
* // The transaction opens here.
Alex Pott
committed
* $transaction = $connection->startTransaction();
Angie Byron
committed
*
* try {
Alex Pott
committed
* $id = $connection->insert('example')
Angie Byron
committed
* ->fields(array(
* 'field1' => 'mystring',
* 'field2' => 5,
* ))
* ->execute();
*
* my_other_function($id);
*
* return $id;
* }
* catch (Exception $e) {
* // Something went wrong somewhere, so roll back now.
Alex Pott
committed
* $transaction->rollBack();
Angie Byron
committed
* // Log the exception to watchdog.
* watchdog_exception('type', $e);
* }
*
Alex Pott
committed
* // $transaction goes out of scope here. Unless the transaction was rolled
* // back, it gets automatically committed here.
Angie Byron
committed
* }
*
* function my_other_function($id) {
Alex Pott
committed
* $connection = \Drupal::database();
Angie Byron
committed
* // The transaction is still open here.
*
* if ($id % 2 == 0) {
Alex Pott
committed
* $connection->update('example')
Angie Byron
committed
* ->condition('id', $id)
* ->fields(array('field2' => 10))
* ->execute();
* }
* }
* @endcode
*
* @section sec_connection Database connection objects
Alex Pott
committed
* The examples here all use functions like \Drupal::database()->select() and
* \Drupal::database()->query(), which can be called from any Drupal method or
* function code. In some classes, you may already have a database connection
* object in a member variable, or it may be passed into a class constructor
* via dependency injection. If that is the case, you can look at the code for
* \Drupal::database()->select() and the other functions to see how to get a
* query object from your connection variable. For example:
Angie Byron
committed
* @code
* $query = $connection->select('example', 'e');
* @endcode
* would be the equivalent of
* @code
Alex Pott
committed
* $query = \Drupal::database()->select('example', 'e');
Angie Byron
committed
* @endcode
* if you had a connection object variable $connection available to use. See
* also the @link container Services and Dependency Injection topic. @endlink
*
Jess
committed
* @see https://www.drupal.org/developing/api/database
Angie Byron
committed
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
* @see entity_api
* @see schemaapi
*
* @}
*/
/**
* @defgroup schemaapi Schema API
* @{
* API to handle database schemas.
*
* A Drupal schema definition is an array structure representing one or
* more tables and their related keys and indexes. A schema is defined by
* hook_schema(), which usually lives in a modulename.install file.
*
* By implementing hook_schema() and specifying the tables your module
* declares, you can easily create and drop these tables on all
* supported database engines. You don't have to deal with the
* different SQL dialects for table creation and alteration of the
* supported database engines.
*
* hook_schema() should return an array with a key for each table that
* the module defines.
*
* The following keys are defined:
* - 'description': A string in non-markup plain text describing this table
Alex Pott
committed
* and its purpose. References to other tables should be enclosed in curly
* brackets.
Angie Byron
committed
* - 'fields': An associative array ('fieldname' => specification)
* that describes the table's database columns. The specification
* is also an array. The following specification parameters are defined:
* - 'description': A string in non-markup plain text describing this field
Alex Pott
committed
* and its purpose. References to other tables should be enclosed in curly
* brackets. For example, the users_data table 'uid' field description
* might contain "The {users}.uid this record affects."
Angie Byron
committed
* - 'type': The generic datatype: 'char', 'varchar', 'text', 'blob', 'int',
* 'float', 'numeric', or 'serial'. Most types just map to the according
* database engine specific data types. Use 'serial' for auto incrementing
Angie Byron
committed
* fields. This will expand to 'INT auto_increment' on MySQL.
* A special 'varchar_ascii' type is also available for limiting machine
* name field to US ASCII characters.
Angie Byron
committed
* - 'mysql_type', 'pgsql_type', 'sqlite_type', etc.: If you need to
* use a record type not included in the officially supported list
* of types above, you can specify a type for each database
* backend. In this case, you can leave out the type parameter,
* but be advised that your schema will fail to load on backends that
* do not have a type specified. A possible solution can be to
* use the "text" type as a fallback.
* - 'serialize': A boolean indicating whether the field will be stored as
* a serialized string.
* - 'size': The data size: 'tiny', 'small', 'medium', 'normal',
* 'big'. This is a hint about the largest value the field will
* store and determines which of the database engine specific
* data types will be used (e.g. on MySQL, TINYINT vs. INT vs. BIGINT).
Angie Byron
committed
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
* 'normal', the default, selects the base type (e.g. on MySQL,
* INT, VARCHAR, BLOB, etc.).
* Not all sizes are available for all data types. See
* DatabaseSchema::getFieldTypeMap() for possible combinations.
* - 'not null': If true, no NULL values will be allowed in this
* database column. Defaults to false.
* - 'default': The field's default value. The PHP type of the
* value matters: '', '0', and 0 are all different. If you
* specify '0' as the default value for a type 'int' field it
* will not work because '0' is a string containing the
* character "zero", not an integer.
* - 'length': The maximal length of a type 'char', 'varchar' or 'text'
* field. Ignored for other field types.
* - 'unsigned': A boolean indicating whether a type 'int', 'float'
* and 'numeric' only is signed or unsigned. Defaults to
* FALSE. Ignored for other field types.
* - 'precision', 'scale': For type 'numeric' fields, indicates
* the precision (total number of significant digits) and scale
* (decimal digits right of the decimal point). Both values are
* mandatory. Ignored for other field types.
* - 'binary': A boolean indicating that MySQL should force 'char',
* 'varchar' or 'text' fields to use case-sensitive binary collation.
* This has no effect on other database types for which case sensitivity
* is already the default behavior.
* All parameters apart from 'type' are optional except that type
* 'numeric' columns must specify 'precision' and 'scale', and type
* 'varchar' must specify the 'length' parameter.
* - 'primary key': An array of one or more key column specifiers (see below)
* that form the primary key.
* - 'unique keys': An associative array of unique keys ('keyname' =>
* specification). Each specification is an array of one or more
* key column specifiers (see below) that form a unique key on the table.
* - 'foreign keys': An associative array of relations ('my_relation' =>
* specification). Each specification is an array containing the name of
* the referenced table ('table'), and an array of column mappings
* ('columns'). Column mappings are defined by key pairs ('source_column' =>
Jess
committed
* 'referenced_column'). This key is for documentation purposes only; foreign
* keys are not created in the database, nor are they enforced by Drupal.
Angie Byron
committed
* - 'indexes': An associative array of indexes ('indexname' =>
* specification). Each specification is an array of one or more
* key column specifiers (see below) that form an index on the
* table.
*
Alex Pott
committed
* A key column specifier is either a string naming a column or an array of two
* elements, column name and length, specifying a prefix of the named column.
Angie Byron
committed
*
Alex Pott
committed
* As an example, this is the schema definition for the 'users_data' table. It
* shows five fields ('uid', 'module', 'name', 'value', and 'serialized'), the
* primary key (on the 'uid', 'module', and 'name' fields), and two indexes (the
* 'module' index on the 'module' field and the 'name' index on the 'name'
* field).
Angie Byron
committed
*
* @code
Alex Pott
committed
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
* $schema['users_data'] = [
* 'description' => 'Stores module data as key/value pairs per user.',
* 'fields' => [
* 'uid' => [
* 'description' => 'The {users}.uid this record affects.',
* 'type' => 'int',
* 'unsigned' => TRUE,
* 'not null' => TRUE,
* 'default' => 0,
* ],
* 'module' => [
* 'description' => 'The name of the module declaring the variable.',
* 'type' => 'varchar_ascii',
* 'length' => DRUPAL_EXTENSION_NAME_MAX_LENGTH,
* 'not null' => TRUE,
* 'default' => '',
* ],
* 'name' => [
* 'description' => 'The identifier of the data.',
* 'type' => 'varchar_ascii',
* 'length' => 128,
* 'not null' => TRUE,
* 'default' => '',
* ],
* 'value' => [
* 'description' => 'The value.',
* 'type' => 'blob',
* 'not null' => FALSE,
* 'size' => 'big',
* ],
* 'serialized' => [
* 'description' => 'Whether value is serialized.',
* 'type' => 'int',
* 'size' => 'tiny',
* 'unsigned' => TRUE,
* 'default' => 0,
* ],
* ],
* 'primary key' => ['uid', 'module', 'name'],
* 'indexes' => [
* 'module' => ['module'],
* 'name' => ['name'],
* ],
* // For documentation purposes only; foreign keys are not created in the
* // database.
Alex Pott
committed
* 'foreign keys' => [
* 'data_user' => [
Angie Byron
committed
* 'table' => 'users',
Alex Pott
committed
* 'columns' => [
* 'uid' => 'uid',
* ],
* ],
* ],
* ];
Angie Byron
committed
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
* @endcode
*
* @see drupal_install_schema()
*
* @}
*/
/**
* @addtogroup hooks
* @{
*/
/**
* Perform alterations to a structured query.
*
* Structured (aka dynamic) queries that have tags associated may be altered by any module
* before the query is executed.
*
* @param $query
* A Query object describing the composite parts of a SQL query.
*
* @see hook_query_TAG_alter()
* @see node_query_node_access_alter()
* @see AlterableInterface
* @see SelectInterface
*
* @ingroup database
*/
function hook_query_alter(Drupal\Core\Database\Query\AlterableInterface $query) {
if ($query->hasTag('micro_limit')) {
$query->range(0, 2);
}
}
/**
* Perform alterations to a structured query for a given tag.
*
* @param $query
* An Query object describing the composite parts of a SQL query.
*
* @see hook_query_alter()
* @see node_query_node_access_alter()
* @see AlterableInterface
* @see SelectInterface
*
* @ingroup database
*/
function hook_query_TAG_alter(Drupal\Core\Database\Query\AlterableInterface $query) {
// Skip the extra expensive alterations if site has no node access control modules.
if (!node_access_view_all_nodes()) {
// Prevent duplicates records.
$query->distinct();
// The recognized operations are 'view', 'update', 'delete'.
if (!$op = $query->getMetaData('op')) {
$op = 'view';
}
// Skip the extra joins and conditions for node admins.
if (!\Drupal::currentUser()->hasPermission('bypass node access')) {
// The node_access table has the access grants for any given node.
$access_alias = $query->join('node_access', 'na', '%alias.nid = n.nid');
Alex Pott
committed
$or = new Condition('OR');
Angie Byron
committed
// If any grant exists for the specified user, then user has access to the node for the specified operation.
foreach (node_access_grants($op, $query->getMetaData('account')) as $realm => $gids) {
foreach ($gids as $gid) {
Alex Pott
committed
$or->condition((new Condition('AND'))
Angie Byron
committed
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
->condition($access_alias . '.gid', $gid)
->condition($access_alias . '.realm', $realm)
);
}
}
if (count($or->conditions())) {
$query->condition($or);
}
$query->condition($access_alias . 'grant_' . $op, 1, '>=');
}
}
}
/**
* Define the current version of the database schema.
*
* A Drupal schema definition is an array structure representing one or more
* tables and their related keys and indexes. A schema is defined by
* hook_schema() which must live in your module's .install file.
*
* The tables declared by this hook will be automatically created when the
* module is installed, and removed when the module is uninstalled. This happens
* before hook_install() is invoked, and after hook_uninstall() is invoked,
* respectively.
*
* By declaring the tables used by your module via an implementation of
* hook_schema(), these tables will be available on all supported database
* engines. You don't have to deal with the different SQL dialects for table
* creation and alteration of the supported database engines.
*
Jess
committed
* See the Schema API Handbook at https://www.drupal.org/node/146843 for details
Jess
committed
* on schema definition structures. Note that foreign key definitions are for
* documentation purposes only; foreign keys are not created in the database,
* nor are they enforced by Drupal.
Angie Byron
committed
*
* @return array
* A schema definition structure array. For each element of the
* array, the key is a table name and the value is a table structure
* definition.
*
* @ingroup schemaapi
*/
function hook_schema() {
Alex Pott
committed
$schema['users_data'] = [
'description' => 'Stores module data as key/value pairs per user.',
'fields' => [
Alex Pott
committed
'uid' => [
'description' => 'The {users}.uid this record affects.',
Angie Byron
committed
'type' => 'int',
'unsigned' => TRUE,
'not null' => TRUE,
'default' => 0,
],
Alex Pott
committed
'module' => [
'description' => 'The name of the module declaring the variable.',
'type' => 'varchar_ascii',
'length' => DRUPAL_EXTENSION_NAME_MAX_LENGTH,
Angie Byron
committed
'not null' => TRUE,
'default' => '',
],
Alex Pott
committed
'name' => [
'description' => 'The identifier of the data.',
'type' => 'varchar_ascii',
'length' => 128,
Angie Byron
committed
'not null' => TRUE,
'default' => '',
],
Alex Pott
committed
'value' => [
'description' => 'The value.',
'type' => 'blob',
'not null' => FALSE,
'size' => 'big',
],
'serialized' => [
'description' => 'Whether value is serialized.',
'type' => 'int',
'size' => 'tiny',
'unsigned' => TRUE,
'default' => 0,
],
],
Alex Pott
committed
'primary key' => ['uid', 'module', 'name'],
'indexes' => [
Alex Pott
committed
'module' => ['module'],
'name' => ['name'],
],
// For documentation purposes only; foreign keys are not created in the
// database.
'foreign keys' => [
Alex Pott
committed
'data_user' => [
Angie Byron
committed
'table' => 'users',
Alex Pott
committed
'columns' => [
'uid' => 'uid',
],
],
],
];
Alex Pott
committed
Angie Byron
committed
return $schema;
}
/**
* @} End of "addtogroup hooks".
*/