Chris@0: query() and Chris@17: * \Drupal::database()->queryRange(), which execute SELECT queries (optionally Chris@17: * with range limits) and return result sets that you can iterate over using Chris@17: * foreach loops. (The result sets are objects implementing the Chris@17: * \Drupal\Core\Database\StatementInterface interface.) Chris@0: * You can use the simple query functions for query strings that are not Chris@0: * dynamic (except for placeholders, see below), and that you are certain will Chris@0: * work in any database engine. See @ref sec_dynamic below if you have a more Chris@0: * complex query, or a query whose syntax would be different in some databases. Chris@0: * Chris@17: * Note: \Drupal::database() is used here as a shorthand way to get a reference Chris@17: * to the database connection object. In most classes, you should use dependency Chris@17: * injection and inject the 'database' service to perform queries. See Chris@17: * @ref sec_connection below for details. Chris@0: * Chris@0: * To use the simple database query functions, you will need to make a couple of Chris@0: * modifications to your bare SQL query: Chris@0: * - Enclose your table name in {}. Drupal allows site builders to use Chris@0: * database table name prefixes, so you cannot be sure what the actual Chris@0: * name of the table will be. So, use the name that is in the hook_schema(), Chris@0: * enclosed in {}, and Drupal will calculate the right name. Chris@0: * - Instead of putting values for conditions into the query, use placeholders. Chris@0: * The placeholders are named and start with :, and they take the place of Chris@0: * putting variables directly into the query, to protect against SQL Chris@0: * injection attacks. Chris@0: * - LIMIT syntax differs between databases, so if you have a ranged query, Chris@17: * use \Drupal::database()->queryRange() instead of Chris@17: * \Drupal::database()->query(). Chris@0: * Chris@0: * For example, if the query you want to run is: Chris@0: * @code Chris@0: * SELECT e.id, e.title, e.created FROM example e WHERE e.uid = $uid Chris@0: * ORDER BY e.created DESC LIMIT 0, 10; Chris@0: * @endcode Chris@0: * you would do it like this: Chris@0: * @code Chris@17: * $result = \Drupal::database()->queryRange('SELECT e.id, e.title, e.created Chris@0: * FROM {example} e Chris@0: * WHERE e.uid = :uid Chris@0: * ORDER BY e.created DESC', Chris@0: * 0, 10, array(':uid' => $uid)); Chris@0: * foreach ($result as $record) { Chris@0: * // Perform operations on $record->title, etc. here. Chris@0: * } Chris@0: * @endcode Chris@0: * Chris@0: * Note that if your query has a string condition, like: Chris@0: * @code Chris@0: * WHERE e.my_field = 'foo' Chris@0: * @endcode Chris@0: * when you convert it to placeholders, omit the quotes: Chris@0: * @code Chris@0: * WHERE e.my_field = :my_field Chris@0: * ... array(':my_field' => 'foo') ... Chris@0: * @endcode Chris@0: * Chris@0: * @section sec_dynamic Dynamic SELECT queries Chris@0: * For SELECT queries where the simple query API described in @ref sec_simple Chris@0: * will not work well, you need to use the dynamic query API. However, you Chris@0: * should still use the Entity Query API if your query involves entities or Chris@0: * fields (see the @link entity_api Entity API topic @endlink for more on Chris@0: * entity queries). Chris@0: * Chris@0: * The dynamic query API lets you build up a query dynamically using method Chris@0: * calls. As an illustration, the query example from @ref sec_simple above Chris@0: * would be: Chris@0: * @code Chris@17: * $result = \Drupal::database()->select('example', 'e') Chris@0: * ->fields('e', array('id', 'title', 'created')) Chris@0: * ->condition('e.uid', $uid) Chris@0: * ->orderBy('e.created', 'DESC') Chris@0: * ->range(0, 10) Chris@0: * ->execute(); Chris@0: * @endcode Chris@0: * Chris@0: * There are also methods to join to other tables, add fields with aliases, Chris@17: * isNull() to query for NULL values, etc. See Chris@0: * https://www.drupal.org/developing/api/database for many more details. Chris@0: * Chris@0: * One note on chaining: It is common in the dynamic database API to chain Chris@0: * method calls (as illustrated here), because most of the query methods modify Chris@0: * the query object and then return the modified query as their return Chris@0: * value. However, there are some important exceptions; these methods (and some Chris@0: * others) do not support chaining: Chris@0: * - join(), innerJoin(), etc.: These methods return the joined table alias. Chris@0: * - addField(): This method returns the field alias. Chris@0: * Check the documentation for the query method you are using to see if it Chris@0: * returns the query or something else, and only chain methods that return the Chris@0: * query. Chris@0: * Chris@17: * @section sec_insert INSERT, UPDATE, and DELETE queries Chris@0: * INSERT, UPDATE, and DELETE queries need special care in order to behave Chris@17: * consistently across databases; you should never use Chris@17: * \Drupal::database()->query() to run an INSERT, UPDATE, or DELETE query. Chris@17: * Instead, use functions \Drupal::database()->insert(), Chris@17: * \Drupal::database()->update(), and \Drupal::database()->delete() to obtain Chris@17: * a base query on your table, and then add dynamic conditions (as illustrated Chris@17: * in @ref sec_dynamic above). Chris@0: * Chris@17: * Note: \Drupal::database() is used here as a shorthand way to get a reference Chris@17: * to the database connection object. In most classes, you should use dependency Chris@17: * injection and inject the 'database' service to perform queries. See Chris@17: * @ref sec_connection below for details. Chris@0: * Chris@0: * For example, if your query is: Chris@0: * @code Chris@0: * INSERT INTO example (id, uid, path, name) VALUES (1, 2, 'path', 'Name'); Chris@0: * @endcode Chris@0: * You can execute it via: Chris@0: * @code Chris@0: * $fields = array('id' => 1, 'uid' => 2, 'path' => 'path', 'name' => 'Name'); Chris@17: * \Drupal::database()->insert('example') Chris@0: * ->fields($fields) Chris@0: * ->execute(); Chris@0: * @endcode Chris@0: * Chris@0: * @section sec_transaction Transactions Chris@0: * Drupal supports transactions, including a transparent fallback for Chris@0: * databases that do not support transactions. To start a new transaction, Chris@17: * call startTransaction(), like this: Chris@17: * @code Chris@17: * $transaction = \Drupal::database()->startTransaction(); Chris@17: * @endcode Chris@17: * The transaction will remain open for as long as the variable $transaction Chris@17: * remains in scope; when $transaction is destroyed, the transaction will be Chris@17: * committed. If your transaction is nested inside of another then Drupal will Chris@17: * track each transaction and only commit the outer-most transaction when the Chris@17: * last transaction object goes out out of scope (when all relevant queries have Chris@17: * completed successfully). Chris@0: * Chris@0: * Example: Chris@0: * @code Chris@0: * function my_transaction_function() { Chris@17: * $connection = \Drupal::database(); Chris@0: * // The transaction opens here. Chris@17: * $transaction = $connection->startTransaction(); Chris@0: * Chris@0: * try { Chris@17: * $id = $connection->insert('example') Chris@0: * ->fields(array( Chris@0: * 'field1' => 'mystring', Chris@0: * 'field2' => 5, Chris@0: * )) Chris@0: * ->execute(); Chris@0: * Chris@0: * my_other_function($id); Chris@0: * Chris@0: * return $id; Chris@0: * } Chris@0: * catch (Exception $e) { Chris@0: * // Something went wrong somewhere, so roll back now. Chris@17: * $transaction->rollBack(); Chris@0: * // Log the exception to watchdog. Chris@0: * watchdog_exception('type', $e); Chris@0: * } Chris@0: * Chris@17: * // $transaction goes out of scope here. Unless the transaction was rolled Chris@17: * // back, it gets automatically committed here. Chris@0: * } Chris@0: * Chris@0: * function my_other_function($id) { Chris@17: * $connection = \Drupal::database(); Chris@0: * // The transaction is still open here. Chris@0: * Chris@0: * if ($id % 2 == 0) { Chris@17: * $connection->update('example') Chris@0: * ->condition('id', $id) Chris@0: * ->fields(array('field2' => 10)) Chris@0: * ->execute(); Chris@0: * } Chris@0: * } Chris@0: * @endcode Chris@0: * Chris@0: * @section sec_connection Database connection objects Chris@17: * The examples here all use functions like \Drupal::database()->select() and Chris@17: * \Drupal::database()->query(), which can be called from any Drupal method or Chris@17: * function code. In some classes, you may already have a database connection Chris@17: * object in a member variable, or it may be passed into a class constructor Chris@17: * via dependency injection. If that is the case, you can look at the code for Chris@17: * \Drupal::database()->select() and the other functions to see how to get a Chris@17: * query object from your connection variable. For example: Chris@0: * @code Chris@0: * $query = $connection->select('example', 'e'); Chris@0: * @endcode Chris@0: * would be the equivalent of Chris@0: * @code Chris@17: * $query = \Drupal::database()->select('example', 'e'); Chris@0: * @endcode Chris@0: * if you had a connection object variable $connection available to use. See Chris@0: * also the @link container Services and Dependency Injection topic. @endlink Chris@0: * Chris@0: * @see https://www.drupal.org/developing/api/database Chris@0: * @see entity_api Chris@0: * @see schemaapi Chris@0: * Chris@0: * @} Chris@0: */ Chris@0: Chris@0: /** Chris@0: * @defgroup schemaapi Schema API Chris@0: * @{ Chris@0: * API to handle database schemas. Chris@0: * Chris@0: * A Drupal schema definition is an array structure representing one or Chris@0: * more tables and their related keys and indexes. A schema is defined by Chris@0: * hook_schema(), which usually lives in a modulename.install file. Chris@0: * Chris@0: * By implementing hook_schema() and specifying the tables your module Chris@0: * declares, you can easily create and drop these tables on all Chris@0: * supported database engines. You don't have to deal with the Chris@0: * different SQL dialects for table creation and alteration of the Chris@0: * supported database engines. Chris@0: * Chris@0: * hook_schema() should return an array with a key for each table that Chris@0: * the module defines. Chris@0: * Chris@0: * The following keys are defined: Chris@0: * - 'description': A string in non-markup plain text describing this table Chris@17: * and its purpose. References to other tables should be enclosed in curly Chris@17: * brackets. Chris@0: * - 'fields': An associative array ('fieldname' => specification) Chris@0: * that describes the table's database columns. The specification Chris@0: * is also an array. The following specification parameters are defined: Chris@0: * - 'description': A string in non-markup plain text describing this field Chris@17: * and its purpose. References to other tables should be enclosed in curly Chris@17: * brackets. For example, the users_data table 'uid' field description Chris@17: * might contain "The {users}.uid this record affects." Chris@0: * - 'type': The generic datatype: 'char', 'varchar', 'text', 'blob', 'int', Chris@0: * 'float', 'numeric', or 'serial'. Most types just map to the according Chris@16: * database engine specific data types. Use 'serial' for auto incrementing Chris@0: * fields. This will expand to 'INT auto_increment' on MySQL. Chris@0: * A special 'varchar_ascii' type is also available for limiting machine Chris@0: * name field to US ASCII characters. Chris@0: * - 'mysql_type', 'pgsql_type', 'sqlite_type', etc.: If you need to Chris@0: * use a record type not included in the officially supported list Chris@0: * of types above, you can specify a type for each database Chris@0: * backend. In this case, you can leave out the type parameter, Chris@0: * but be advised that your schema will fail to load on backends that Chris@0: * do not have a type specified. A possible solution can be to Chris@0: * use the "text" type as a fallback. Chris@0: * - 'serialize': A boolean indicating whether the field will be stored as Chris@0: * a serialized string. Chris@0: * - 'size': The data size: 'tiny', 'small', 'medium', 'normal', Chris@0: * 'big'. This is a hint about the largest value the field will Chris@0: * store and determines which of the database engine specific Chris@16: * data types will be used (e.g. on MySQL, TINYINT vs. INT vs. BIGINT). Chris@0: * 'normal', the default, selects the base type (e.g. on MySQL, Chris@0: * INT, VARCHAR, BLOB, etc.). Chris@0: * Not all sizes are available for all data types. See Chris@0: * DatabaseSchema::getFieldTypeMap() for possible combinations. Chris@0: * - 'not null': If true, no NULL values will be allowed in this Chris@0: * database column. Defaults to false. Chris@0: * - 'default': The field's default value. The PHP type of the Chris@0: * value matters: '', '0', and 0 are all different. If you Chris@0: * specify '0' as the default value for a type 'int' field it Chris@0: * will not work because '0' is a string containing the Chris@0: * character "zero", not an integer. Chris@0: * - 'length': The maximal length of a type 'char', 'varchar' or 'text' Chris@0: * field. Ignored for other field types. Chris@0: * - 'unsigned': A boolean indicating whether a type 'int', 'float' Chris@0: * and 'numeric' only is signed or unsigned. Defaults to Chris@0: * FALSE. Ignored for other field types. Chris@0: * - 'precision', 'scale': For type 'numeric' fields, indicates Chris@0: * the precision (total number of significant digits) and scale Chris@0: * (decimal digits right of the decimal point). Both values are Chris@0: * mandatory. Ignored for other field types. Chris@0: * - 'binary': A boolean indicating that MySQL should force 'char', Chris@0: * 'varchar' or 'text' fields to use case-sensitive binary collation. Chris@0: * This has no effect on other database types for which case sensitivity Chris@0: * is already the default behavior. Chris@0: * All parameters apart from 'type' are optional except that type Chris@0: * 'numeric' columns must specify 'precision' and 'scale', and type Chris@0: * 'varchar' must specify the 'length' parameter. Chris@0: * - 'primary key': An array of one or more key column specifiers (see below) Chris@0: * that form the primary key. Chris@0: * - 'unique keys': An associative array of unique keys ('keyname' => Chris@0: * specification). Each specification is an array of one or more Chris@0: * key column specifiers (see below) that form a unique key on the table. Chris@0: * - 'foreign keys': An associative array of relations ('my_relation' => Chris@0: * specification). Each specification is an array containing the name of Chris@0: * the referenced table ('table'), and an array of column mappings Chris@0: * ('columns'). Column mappings are defined by key pairs ('source_column' => Chris@0: * 'referenced_column'). This key is for documentation purposes only; foreign Chris@0: * keys are not created in the database, nor are they enforced by Drupal. Chris@0: * - 'indexes': An associative array of indexes ('indexname' => Chris@0: * specification). Each specification is an array of one or more Chris@0: * key column specifiers (see below) that form an index on the Chris@0: * table. Chris@0: * Chris@17: * A key column specifier is either a string naming a column or an array of two Chris@17: * elements, column name and length, specifying a prefix of the named column. Chris@0: * Chris@17: * As an example, this is the schema definition for the 'users_data' table. It Chris@17: * shows five fields ('uid', 'module', 'name', 'value', and 'serialized'), the Chris@17: * primary key (on the 'uid', 'module', and 'name' fields), and two indexes (the Chris@17: * 'module' index on the 'module' field and the 'name' index on the 'name' Chris@17: * field). Chris@0: * Chris@0: * @code Chris@17: * $schema['users_data'] = [ Chris@17: * 'description' => 'Stores module data as key/value pairs per user.', Chris@17: * 'fields' => [ Chris@17: * 'uid' => [ Chris@17: * 'description' => 'The {users}.uid this record affects.', Chris@17: * 'type' => 'int', Chris@17: * 'unsigned' => TRUE, Chris@17: * 'not null' => TRUE, Chris@17: * 'default' => 0, Chris@17: * ], Chris@17: * 'module' => [ Chris@17: * 'description' => 'The name of the module declaring the variable.', Chris@17: * 'type' => 'varchar_ascii', Chris@17: * 'length' => DRUPAL_EXTENSION_NAME_MAX_LENGTH, Chris@17: * 'not null' => TRUE, Chris@17: * 'default' => '', Chris@17: * ], Chris@17: * 'name' => [ Chris@17: * 'description' => 'The identifier of the data.', Chris@17: * 'type' => 'varchar_ascii', Chris@17: * 'length' => 128, Chris@17: * 'not null' => TRUE, Chris@17: * 'default' => '', Chris@17: * ], Chris@17: * 'value' => [ Chris@17: * 'description' => 'The value.', Chris@17: * 'type' => 'blob', Chris@17: * 'not null' => FALSE, Chris@17: * 'size' => 'big', Chris@17: * ], Chris@17: * 'serialized' => [ Chris@17: * 'description' => 'Whether value is serialized.', Chris@17: * 'type' => 'int', Chris@17: * 'size' => 'tiny', Chris@17: * 'unsigned' => TRUE, Chris@17: * 'default' => 0, Chris@17: * ], Chris@17: * ], Chris@17: * 'primary key' => ['uid', 'module', 'name'], Chris@17: * 'indexes' => [ Chris@17: * 'module' => ['module'], Chris@17: * 'name' => ['name'], Chris@17: * ], Chris@0: * // For documentation purposes only; foreign keys are not created in the Chris@0: * // database. Chris@17: * 'foreign keys' => [ Chris@17: * 'data_user' => [ Chris@0: * 'table' => 'users', Chris@17: * 'columns' => [ Chris@17: * 'uid' => 'uid', Chris@17: * ], Chris@17: * ], Chris@17: * ], Chris@17: * ]; Chris@0: * @endcode Chris@0: * Chris@0: * @see drupal_install_schema() Chris@0: * Chris@0: * @} Chris@0: */ Chris@0: Chris@0: /** Chris@0: * @addtogroup hooks Chris@0: * @{ Chris@0: */ Chris@0: Chris@0: /** Chris@0: * Perform alterations to a structured query. Chris@0: * Chris@0: * Structured (aka dynamic) queries that have tags associated may be altered by any module Chris@0: * before the query is executed. Chris@0: * Chris@0: * @param $query Chris@0: * A Query object describing the composite parts of a SQL query. Chris@0: * Chris@0: * @see hook_query_TAG_alter() Chris@0: * @see node_query_node_access_alter() Chris@0: * @see AlterableInterface Chris@0: * @see SelectInterface Chris@0: * Chris@0: * @ingroup database Chris@0: */ Chris@0: function hook_query_alter(Drupal\Core\Database\Query\AlterableInterface $query) { Chris@0: if ($query->hasTag('micro_limit')) { Chris@0: $query->range(0, 2); Chris@0: } Chris@0: } Chris@0: Chris@0: /** Chris@0: * Perform alterations to a structured query for a given tag. Chris@0: * Chris@0: * @param $query Chris@0: * An Query object describing the composite parts of a SQL query. Chris@0: * Chris@0: * @see hook_query_alter() Chris@0: * @see node_query_node_access_alter() Chris@0: * @see AlterableInterface Chris@0: * @see SelectInterface Chris@0: * Chris@0: * @ingroup database Chris@0: */ Chris@0: function hook_query_TAG_alter(Drupal\Core\Database\Query\AlterableInterface $query) { Chris@0: // Skip the extra expensive alterations if site has no node access control modules. Chris@0: if (!node_access_view_all_nodes()) { Chris@0: // Prevent duplicates records. Chris@0: $query->distinct(); Chris@0: // The recognized operations are 'view', 'update', 'delete'. Chris@0: if (!$op = $query->getMetaData('op')) { Chris@0: $op = 'view'; Chris@0: } Chris@0: // Skip the extra joins and conditions for node admins. Chris@0: if (!\Drupal::currentUser()->hasPermission('bypass node access')) { Chris@0: // The node_access table has the access grants for any given node. Chris@0: $access_alias = $query->join('node_access', 'na', '%alias.nid = n.nid'); Chris@0: $or = new Condition('OR'); Chris@0: // If any grant exists for the specified user, then user has access to the node for the specified operation. Chris@0: foreach (node_access_grants($op, $query->getMetaData('account')) as $realm => $gids) { Chris@0: foreach ($gids as $gid) { Chris@0: $or->condition((new Condition('AND')) Chris@0: ->condition($access_alias . '.gid', $gid) Chris@0: ->condition($access_alias . '.realm', $realm) Chris@0: ); Chris@0: } Chris@0: } Chris@0: Chris@0: if (count($or->conditions())) { Chris@0: $query->condition($or); Chris@0: } Chris@0: Chris@0: $query->condition($access_alias . 'grant_' . $op, 1, '>='); Chris@0: } Chris@0: } Chris@0: } Chris@0: Chris@0: /** Chris@0: * Define the current version of the database schema. Chris@0: * Chris@0: * A Drupal schema definition is an array structure representing one or more Chris@0: * tables and their related keys and indexes. A schema is defined by Chris@0: * hook_schema() which must live in your module's .install file. Chris@0: * Chris@0: * The tables declared by this hook will be automatically created when the Chris@0: * module is installed, and removed when the module is uninstalled. This happens Chris@0: * before hook_install() is invoked, and after hook_uninstall() is invoked, Chris@0: * respectively. Chris@0: * Chris@0: * By declaring the tables used by your module via an implementation of Chris@0: * hook_schema(), these tables will be available on all supported database Chris@0: * engines. You don't have to deal with the different SQL dialects for table Chris@0: * creation and alteration of the supported database engines. Chris@0: * Chris@0: * See the Schema API Handbook at https://www.drupal.org/node/146843 for details Chris@0: * on schema definition structures. Note that foreign key definitions are for Chris@0: * documentation purposes only; foreign keys are not created in the database, Chris@0: * nor are they enforced by Drupal. Chris@0: * Chris@0: * @return array Chris@0: * A schema definition structure array. For each element of the Chris@0: * array, the key is a table name and the value is a table structure Chris@0: * definition. Chris@0: * Chris@0: * @ingroup schemaapi Chris@0: */ Chris@0: function hook_schema() { Chris@17: $schema['users_data'] = [ Chris@17: 'description' => 'Stores module data as key/value pairs per user.', Chris@0: 'fields' => [ Chris@17: 'uid' => [ Chris@17: 'description' => 'The {users}.uid this record affects.', Chris@0: 'type' => 'int', Chris@0: 'unsigned' => TRUE, Chris@0: 'not null' => TRUE, Chris@0: 'default' => 0, Chris@0: ], Chris@17: 'module' => [ Chris@17: 'description' => 'The name of the module declaring the variable.', Chris@17: 'type' => 'varchar_ascii', Chris@17: 'length' => DRUPAL_EXTENSION_NAME_MAX_LENGTH, Chris@0: 'not null' => TRUE, Chris@0: 'default' => '', Chris@0: ], Chris@17: 'name' => [ Chris@17: 'description' => 'The identifier of the data.', Chris@17: 'type' => 'varchar_ascii', Chris@17: 'length' => 128, Chris@0: 'not null' => TRUE, Chris@0: 'default' => '', Chris@0: ], Chris@17: 'value' => [ Chris@17: 'description' => 'The value.', Chris@17: 'type' => 'blob', Chris@17: 'not null' => FALSE, Chris@17: 'size' => 'big', Chris@17: ], Chris@17: 'serialized' => [ Chris@17: 'description' => 'Whether value is serialized.', Chris@17: 'type' => 'int', Chris@17: 'size' => 'tiny', Chris@17: 'unsigned' => TRUE, Chris@17: 'default' => 0, Chris@17: ], Chris@0: ], Chris@17: 'primary key' => ['uid', 'module', 'name'], Chris@0: 'indexes' => [ Chris@17: 'module' => ['module'], Chris@17: 'name' => ['name'], Chris@0: ], Chris@0: // For documentation purposes only; foreign keys are not created in the Chris@0: // database. Chris@0: 'foreign keys' => [ Chris@17: 'data_user' => [ Chris@0: 'table' => 'users', Chris@17: 'columns' => [ Chris@17: 'uid' => 'uid', Chris@17: ], Chris@0: ], Chris@0: ], Chris@0: ]; Chris@17: Chris@0: return $schema; Chris@0: } Chris@0: Chris@0: /** Chris@0: * @} End of "addtogroup hooks". Chris@0: */