5 * Hooks related to the Database system and the Schema API.
8 use Drupal\Core\Database\Query\Condition;
11 * @defgroup database Database abstraction layer
13 * Allow the use of different database servers using the same code base.
15 * @section sec_intro Overview
16 * Drupal's database abstraction layer provides a unified database query API
17 * that can query different underlying databases. It is built upon PHP's
18 * PDO (PHP Data Objects) database API, and inherits much of its syntax and
19 * semantics. Besides providing a unified API for database queries, the
20 * database abstraction layer also provides a structured way to construct
21 * complex queries, and it protects the database by using good security
24 * For more detailed information on the database abstraction layer, see
25 * https://www.drupal.org/docs/8/api/database-api/database-api-overview.
27 * @section sec_entity Querying entities
28 * Any query on Drupal entities or fields should use the Entity Query API. See
29 * the @link entity_api entity API topic @endlink for more information.
31 * @section sec_simple Simple SELECT database queries
32 * For simple SELECT queries that do not involve entities, the Drupal database
33 * abstraction layer provides the functions \Drupal::database()->query() and
34 * \Drupal::database()->queryRange(), which execute SELECT queries (optionally
35 * with range limits) and return result sets that you can iterate over using
36 * foreach loops. (The result sets are objects implementing the
37 * \Drupal\Core\Database\StatementInterface interface.)
38 * You can use the simple query functions for query strings that are not
39 * dynamic (except for placeholders, see below), and that you are certain will
40 * work in any database engine. See @ref sec_dynamic below if you have a more
41 * complex query, or a query whose syntax would be different in some databases.
43 * Note: \Drupal::database() is used here as a shorthand way to get a reference
44 * to the database connection object. In most classes, you should use dependency
45 * injection and inject the 'database' service to perform queries. See
46 * @ref sec_connection below for details.
48 * To use the simple database query functions, you will need to make a couple of
49 * modifications to your bare SQL query:
50 * - Enclose your table name in {}. Drupal allows site builders to use
51 * database table name prefixes, so you cannot be sure what the actual
52 * name of the table will be. So, use the name that is in the hook_schema(),
53 * enclosed in {}, and Drupal will calculate the right name.
54 * - Instead of putting values for conditions into the query, use placeholders.
55 * The placeholders are named and start with :, and they take the place of
56 * putting variables directly into the query, to protect against SQL
58 * - LIMIT syntax differs between databases, so if you have a ranged query,
59 * use \Drupal::database()->queryRange() instead of
60 * \Drupal::database()->query().
62 * For example, if the query you want to run is:
64 * SELECT e.id, e.title, e.created FROM example e WHERE e.uid = $uid
65 * ORDER BY e.created DESC LIMIT 0, 10;
67 * you would do it like this:
69 * $result = \Drupal::database()->queryRange('SELECT e.id, e.title, e.created
72 * ORDER BY e.created DESC',
73 * 0, 10, array(':uid' => $uid));
74 * foreach ($result as $record) {
75 * // Perform operations on $record->title, etc. here.
79 * Note that if your query has a string condition, like:
81 * WHERE e.my_field = 'foo'
83 * when you convert it to placeholders, omit the quotes:
85 * WHERE e.my_field = :my_field
86 * ... array(':my_field' => 'foo') ...
89 * @section sec_dynamic Dynamic SELECT queries
90 * For SELECT queries where the simple query API described in @ref sec_simple
91 * will not work well, you need to use the dynamic query API. However, you
92 * should still use the Entity Query API if your query involves entities or
93 * fields (see the @link entity_api Entity API topic @endlink for more on
96 * The dynamic query API lets you build up a query dynamically using method
97 * calls. As an illustration, the query example from @ref sec_simple above
100 * $result = \Drupal::database()->select('example', 'e')
101 * ->fields('e', array('id', 'title', 'created'))
102 * ->condition('e.uid', $uid)
103 * ->orderBy('e.created', 'DESC')
108 * There are also methods to join to other tables, add fields with aliases,
109 * isNull() to query for NULL values, etc. See
110 * https://www.drupal.org/developing/api/database for many more details.
112 * One note on chaining: It is common in the dynamic database API to chain
113 * method calls (as illustrated here), because most of the query methods modify
114 * the query object and then return the modified query as their return
115 * value. However, there are some important exceptions; these methods (and some
116 * others) do not support chaining:
117 * - join(), innerJoin(), etc.: These methods return the joined table alias.
118 * - addField(): This method returns the field alias.
119 * Check the documentation for the query method you are using to see if it
120 * returns the query or something else, and only chain methods that return the
123 * @section sec_insert INSERT, UPDATE, and DELETE queries
124 * INSERT, UPDATE, and DELETE queries need special care in order to behave
125 * consistently across databases; you should never use
126 * \Drupal::database()->query() to run an INSERT, UPDATE, or DELETE query.
127 * Instead, use functions \Drupal::database()->insert(),
128 * \Drupal::database()->update(), and \Drupal::database()->delete() to obtain
129 * a base query on your table, and then add dynamic conditions (as illustrated
130 * in @ref sec_dynamic above).
132 * Note: \Drupal::database() is used here as a shorthand way to get a reference
133 * to the database connection object. In most classes, you should use dependency
134 * injection and inject the 'database' service to perform queries. See
135 * @ref sec_connection below for details.
137 * For example, if your query is:
139 * INSERT INTO example (id, uid, path, name) VALUES (1, 2, 'path', 'Name');
141 * You can execute it via:
143 * $fields = array('id' => 1, 'uid' => 2, 'path' => 'path', 'name' => 'Name');
144 * \Drupal::database()->insert('example')
149 * @section sec_transaction Transactions
150 * Drupal supports transactions, including a transparent fallback for
151 * databases that do not support transactions. To start a new transaction,
152 * call startTransaction(), like this:
154 * $transaction = \Drupal::database()->startTransaction();
156 * The transaction will remain open for as long as the variable $transaction
157 * remains in scope; when $transaction is destroyed, the transaction will be
158 * committed. If your transaction is nested inside of another then Drupal will
159 * track each transaction and only commit the outer-most transaction when the
160 * last transaction object goes out out of scope (when all relevant queries have
161 * completed successfully).
165 * function my_transaction_function() {
166 * $connection = \Drupal::database();
167 * // The transaction opens here.
168 * $transaction = $connection->startTransaction();
171 * $id = $connection->insert('example')
173 * 'field1' => 'mystring',
178 * my_other_function($id);
182 * catch (Exception $e) {
183 * // Something went wrong somewhere, so roll back now.
184 * $transaction->rollBack();
185 * // Log the exception to watchdog.
186 * watchdog_exception('type', $e);
189 * // $transaction goes out of scope here. Unless the transaction was rolled
190 * // back, it gets automatically committed here.
193 * function my_other_function($id) {
194 * $connection = \Drupal::database();
195 * // The transaction is still open here.
197 * if ($id % 2 == 0) {
198 * $connection->update('example')
199 * ->condition('id', $id)
200 * ->fields(array('field2' => 10))
206 * @section sec_connection Database connection objects
207 * The examples here all use functions like \Drupal::database()->select() and
208 * \Drupal::database()->query(), which can be called from any Drupal method or
209 * function code. In some classes, you may already have a database connection
210 * object in a member variable, or it may be passed into a class constructor
211 * via dependency injection. If that is the case, you can look at the code for
212 * \Drupal::database()->select() and the other functions to see how to get a
213 * query object from your connection variable. For example:
215 * $query = $connection->select('example', 'e');
217 * would be the equivalent of
219 * $query = \Drupal::database()->select('example', 'e');
221 * if you had a connection object variable $connection available to use. See
222 * also the @link container Services and Dependency Injection topic. @endlink
224 * @see https://www.drupal.org/developing/api/database
232 * @defgroup schemaapi Schema API
234 * API to handle database schemas.
236 * A Drupal schema definition is an array structure representing one or
237 * more tables and their related keys and indexes. A schema is defined by
238 * hook_schema(), which usually lives in a modulename.install file.
240 * By implementing hook_schema() and specifying the tables your module
241 * declares, you can easily create and drop these tables on all
242 * supported database engines. You don't have to deal with the
243 * different SQL dialects for table creation and alteration of the
244 * supported database engines.
246 * hook_schema() should return an array with a key for each table that
247 * the module defines.
249 * The following keys are defined:
250 * - 'description': A string in non-markup plain text describing this table
251 * and its purpose. References to other tables should be enclosed in
252 * curly-brackets. For example, the node_field_revision table
253 * description field might contain "Stores per-revision title and
254 * body data for each {node}."
255 * - 'fields': An associative array ('fieldname' => specification)
256 * that describes the table's database columns. The specification
257 * is also an array. The following specification parameters are defined:
258 * - 'description': A string in non-markup plain text describing this field
259 * and its purpose. References to other tables should be enclosed in
260 * curly-brackets. For example, the node table vid field
261 * description might contain "Always holds the largest (most
262 * recent) {node_field_revision}.vid value for this nid."
263 * - 'type': The generic datatype: 'char', 'varchar', 'text', 'blob', 'int',
264 * 'float', 'numeric', or 'serial'. Most types just map to the according
265 * database engine specific data types. Use 'serial' for auto incrementing
266 * fields. This will expand to 'INT auto_increment' on MySQL.
267 * A special 'varchar_ascii' type is also available for limiting machine
268 * name field to US ASCII characters.
269 * - 'mysql_type', 'pgsql_type', 'sqlite_type', etc.: If you need to
270 * use a record type not included in the officially supported list
271 * of types above, you can specify a type for each database
272 * backend. In this case, you can leave out the type parameter,
273 * but be advised that your schema will fail to load on backends that
274 * do not have a type specified. A possible solution can be to
275 * use the "text" type as a fallback.
276 * - 'serialize': A boolean indicating whether the field will be stored as
277 * a serialized string.
278 * - 'size': The data size: 'tiny', 'small', 'medium', 'normal',
279 * 'big'. This is a hint about the largest value the field will
280 * store and determines which of the database engine specific
281 * data types will be used (e.g. on MySQL, TINYINT vs. INT vs. BIGINT).
282 * 'normal', the default, selects the base type (e.g. on MySQL,
283 * INT, VARCHAR, BLOB, etc.).
284 * Not all sizes are available for all data types. See
285 * DatabaseSchema::getFieldTypeMap() for possible combinations.
286 * - 'not null': If true, no NULL values will be allowed in this
287 * database column. Defaults to false.
288 * - 'default': The field's default value. The PHP type of the
289 * value matters: '', '0', and 0 are all different. If you
290 * specify '0' as the default value for a type 'int' field it
291 * will not work because '0' is a string containing the
292 * character "zero", not an integer.
293 * - 'length': The maximal length of a type 'char', 'varchar' or 'text'
294 * field. Ignored for other field types.
295 * - 'unsigned': A boolean indicating whether a type 'int', 'float'
296 * and 'numeric' only is signed or unsigned. Defaults to
297 * FALSE. Ignored for other field types.
298 * - 'precision', 'scale': For type 'numeric' fields, indicates
299 * the precision (total number of significant digits) and scale
300 * (decimal digits right of the decimal point). Both values are
301 * mandatory. Ignored for other field types.
302 * - 'binary': A boolean indicating that MySQL should force 'char',
303 * 'varchar' or 'text' fields to use case-sensitive binary collation.
304 * This has no effect on other database types for which case sensitivity
305 * is already the default behavior.
306 * All parameters apart from 'type' are optional except that type
307 * 'numeric' columns must specify 'precision' and 'scale', and type
308 * 'varchar' must specify the 'length' parameter.
309 * - 'primary key': An array of one or more key column specifiers (see below)
310 * that form the primary key.
311 * - 'unique keys': An associative array of unique keys ('keyname' =>
312 * specification). Each specification is an array of one or more
313 * key column specifiers (see below) that form a unique key on the table.
314 * - 'foreign keys': An associative array of relations ('my_relation' =>
315 * specification). Each specification is an array containing the name of
316 * the referenced table ('table'), and an array of column mappings
317 * ('columns'). Column mappings are defined by key pairs ('source_column' =>
318 * 'referenced_column'). This key is for documentation purposes only; foreign
319 * keys are not created in the database, nor are they enforced by Drupal.
320 * - 'indexes': An associative array of indexes ('indexname' =>
321 * specification). Each specification is an array of one or more
322 * key column specifiers (see below) that form an index on the
325 * A key column specifier is either a string naming a column or an
326 * array of two elements, column name and length, specifying a prefix
327 * of the named column.
329 * As an example, here is a SUBSET of the schema definition for
330 * Drupal's 'node' table. It show four fields (nid, vid, type, and
331 * title), the primary key on field 'nid', a unique key named 'vid' on
332 * field 'vid', and two indexes, one named 'nid' on field 'nid' and
333 * one named 'node_title_type' on the field 'title' and the first four
334 * bytes of the field 'type':
337 * $schema['node'] = array(
338 * 'description' => 'The base table for nodes.',
340 * 'nid' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),
341 * 'vid' => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE,'default' => 0),
342 * 'type' => array('type' => 'varchar','length' => 32,'not null' => TRUE, 'default' => ''),
343 * 'language' => array('type' => 'varchar','length' => 12,'not null' => TRUE,'default' => ''),
344 * 'title' => array('type' => 'varchar','length' => 255,'not null' => TRUE, 'default' => ''),
345 * 'uid' => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
346 * 'status' => array('type' => 'int', 'not null' => TRUE, 'default' => 1),
347 * 'created' => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
348 * 'changed' => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
349 * 'comment' => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
350 * 'promote' => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
351 * 'moderate' => array('type' => 'int', 'not null' => TRUE,'default' => 0),
352 * 'sticky' => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
353 * 'translate' => array('type' => 'int', 'not null' => TRUE, 'default' => 0),
355 * 'indexes' => array(
356 * 'node_changed' => array('changed'),
357 * 'node_created' => array('created'),
358 * 'node_moderate' => array('moderate'),
359 * 'node_frontpage' => array('promote', 'status', 'sticky', 'created'),
360 * 'node_status_type' => array('status', 'type', 'nid'),
361 * 'node_title_type' => array('title', array('type', 4)),
362 * 'node_type' => array(array('type', 4)),
363 * 'uid' => array('uid'),
364 * 'translate' => array('translate'),
366 * 'unique keys' => array(
367 * 'vid' => array('vid'),
369 * // For documentation purposes only; foreign keys are not created in the
371 * 'foreign keys' => array(
372 * 'node_revision' => array(
373 * 'table' => 'node_field_revision',
374 * 'columns' => array('vid' => 'vid'),
376 * 'node_author' => array(
377 * 'table' => 'users',
378 * 'columns' => array('uid' => 'uid'),
381 * 'primary key' => array('nid'),
385 * @see drupal_install_schema()
396 * Perform alterations to a structured query.
398 * Structured (aka dynamic) queries that have tags associated may be altered by any module
399 * before the query is executed.
402 * A Query object describing the composite parts of a SQL query.
404 * @see hook_query_TAG_alter()
405 * @see node_query_node_access_alter()
406 * @see AlterableInterface
407 * @see SelectInterface
411 function hook_query_alter(Drupal\Core\Database\Query\AlterableInterface $query) {
412 if ($query->hasTag('micro_limit')) {
418 * Perform alterations to a structured query for a given tag.
421 * An Query object describing the composite parts of a SQL query.
423 * @see hook_query_alter()
424 * @see node_query_node_access_alter()
425 * @see AlterableInterface
426 * @see SelectInterface
430 function hook_query_TAG_alter(Drupal\Core\Database\Query\AlterableInterface $query) {
431 // Skip the extra expensive alterations if site has no node access control modules.
432 if (!node_access_view_all_nodes()) {
433 // Prevent duplicates records.
435 // The recognized operations are 'view', 'update', 'delete'.
436 if (!$op = $query->getMetaData('op')) {
439 // Skip the extra joins and conditions for node admins.
440 if (!\Drupal::currentUser()->hasPermission('bypass node access')) {
441 // The node_access table has the access grants for any given node.
442 $access_alias = $query->join('node_access', 'na', '%alias.nid = n.nid');
443 $or = new Condition('OR');
444 // If any grant exists for the specified user, then user has access to the node for the specified operation.
445 foreach (node_access_grants($op, $query->getMetaData('account')) as $realm => $gids) {
446 foreach ($gids as $gid) {
447 $or->condition((new Condition('AND'))
448 ->condition($access_alias . '.gid', $gid)
449 ->condition($access_alias . '.realm', $realm)
454 if (count($or->conditions())) {
455 $query->condition($or);
458 $query->condition($access_alias . 'grant_' . $op, 1, '>=');
464 * Define the current version of the database schema.
466 * A Drupal schema definition is an array structure representing one or more
467 * tables and their related keys and indexes. A schema is defined by
468 * hook_schema() which must live in your module's .install file.
470 * The tables declared by this hook will be automatically created when the
471 * module is installed, and removed when the module is uninstalled. This happens
472 * before hook_install() is invoked, and after hook_uninstall() is invoked,
475 * By declaring the tables used by your module via an implementation of
476 * hook_schema(), these tables will be available on all supported database
477 * engines. You don't have to deal with the different SQL dialects for table
478 * creation and alteration of the supported database engines.
480 * See the Schema API Handbook at https://www.drupal.org/node/146843 for details
481 * on schema definition structures. Note that foreign key definitions are for
482 * documentation purposes only; foreign keys are not created in the database,
483 * nor are they enforced by Drupal.
486 * A schema definition structure array. For each element of the
487 * array, the key is a table name and the value is a table structure
492 function hook_schema() {
494 // Example (partial) specification for table "node".
495 'description' => 'The base table for nodes.',
498 'description' => 'The primary identifier for a node.',
504 'description' => 'The current {node_field_revision}.vid version identifier.',
511 'description' => 'The type of this node.',
518 'description' => 'The node title.',
526 'node_changed' => ['changed'],
527 'node_created' => ['created'],
530 'nid_vid' => ['nid', 'vid'],
533 // For documentation purposes only; foreign keys are not created in the
537 'table' => 'node_field_revision',
538 'columns' => ['vid' => 'vid'],
542 'columns' => ['uid' => 'uid'],
545 'primary key' => ['nid'],
551 * @} End of "addtogroup hooks".