query.inc 56 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777177817791780178117821783178417851786178717881789179017911792179317941795179617971798179918001801180218031804180518061807180818091810181118121813181418151816181718181819182018211822182318241825182618271828182918301831183218331834183518361837183818391840184118421843184418451846184718481849185018511852185318541855185618571858185918601861186218631864186518661867186818691870187118721873187418751876187718781879188018811882188318841885188618871888188918901891189218931894189518961897189818991900190119021903190419051906190719081909191019111912191319141915191619171918191919201921192219231924192519261927192819291930193119321933193419351936193719381939194019411942194319441945194619471948194919501951195219531954195519561957195819591960196119621963
  1. <?php
  2. /**
  3. * @addtogroup database
  4. * @{
  5. */
  6. /**
  7. * @file
  8. * Non-specific Database query code. Used by all engines.
  9. */
  10. /**
  11. * Interface for a conditional clause in a query.
  12. */
  13. interface QueryConditionInterface {
  14. /**
  15. * Helper function: builds the most common conditional clauses.
  16. *
  17. * This method can take a variable number of parameters. If called with two
  18. * parameters, they are taken as $field and $value with $operator having a
  19. * value of IN if $value is an array and = otherwise.
  20. *
  21. * Do not use this method to test for NULL values. Instead, use
  22. * QueryConditionInterface::isNull() or QueryConditionInterface::isNotNull().
  23. *
  24. * @param $field
  25. * The name of the field to check. If you would like to add a more complex
  26. * condition involving operators or functions, use where().
  27. * @param $value
  28. * The value to test the field against. In most cases, this is a scalar.
  29. * For more complex options, it is an array. The meaning of each element in
  30. * the array is dependent on the $operator.
  31. * @param $operator
  32. * The comparison operator, such as =, <, or >=. It also accepts more
  33. * complex options such as IN, LIKE, or BETWEEN. Defaults to IN if $value is
  34. * an array, and = otherwise.
  35. *
  36. * @return QueryConditionInterface
  37. * The called object.
  38. *
  39. * @see QueryConditionInterface::isNull()
  40. * @see QueryConditionInterface::isNotNull()
  41. */
  42. public function condition($field, $value = NULL, $operator = NULL);
  43. /**
  44. * Adds an arbitrary WHERE clause to the query.
  45. *
  46. * @param $snippet
  47. * A portion of a WHERE clause as a prepared statement. It must use named
  48. * placeholders, not ? placeholders.
  49. * @param $args
  50. * An associative array of arguments.
  51. *
  52. * @return QueryConditionInterface
  53. * The called object.
  54. */
  55. public function where($snippet, $args = array());
  56. /**
  57. * Sets a condition that the specified field be NULL.
  58. *
  59. * @param $field
  60. * The name of the field to check.
  61. *
  62. * @return QueryConditionInterface
  63. * The called object.
  64. */
  65. public function isNull($field);
  66. /**
  67. * Sets a condition that the specified field be NOT NULL.
  68. *
  69. * @param $field
  70. * The name of the field to check.
  71. *
  72. * @return QueryConditionInterface
  73. * The called object.
  74. */
  75. public function isNotNull($field);
  76. /**
  77. * Sets a condition that the specified subquery returns values.
  78. *
  79. * @param SelectQueryInterface $select
  80. * The subquery that must contain results.
  81. *
  82. * @return QueryConditionInterface
  83. * The called object.
  84. */
  85. public function exists(SelectQueryInterface $select);
  86. /**
  87. * Sets a condition that the specified subquery returns no values.
  88. *
  89. * @param SelectQueryInterface $select
  90. * The subquery that must not contain results.
  91. *
  92. * @return QueryConditionInterface
  93. * The called object.
  94. */
  95. public function notExists(SelectQueryInterface $select);
  96. /**
  97. * Gets a complete list of all conditions in this conditional clause.
  98. *
  99. * This method returns by reference. That allows alter hooks to access the
  100. * data structure directly and manipulate it before it gets compiled.
  101. *
  102. * The data structure that is returned is an indexed array of entries, where
  103. * each entry looks like the following:
  104. * @code
  105. * array(
  106. * 'field' => $field,
  107. * 'value' => $value,
  108. * 'operator' => $operator,
  109. * );
  110. * @endcode
  111. *
  112. * In the special case that $operator is NULL, the $field is taken as a raw
  113. * SQL snippet (possibly containing a function) and $value is an associative
  114. * array of placeholders for the snippet.
  115. *
  116. * There will also be a single array entry of #conjunction, which is the
  117. * conjunction that will be applied to the array, such as AND.
  118. */
  119. public function &conditions();
  120. /**
  121. * Gets a complete list of all values to insert into the prepared statement.
  122. *
  123. * @return
  124. * An associative array of placeholders and values.
  125. */
  126. public function arguments();
  127. /**
  128. * Compiles the saved conditions for later retrieval.
  129. *
  130. * This method does not return anything, but simply prepares data to be
  131. * retrieved via __toString() and arguments().
  132. *
  133. * @param $connection
  134. * The database connection for which to compile the conditionals.
  135. * @param $queryPlaceholder
  136. * The query this condition belongs to. If not given, the current query is
  137. * used.
  138. */
  139. public function compile(DatabaseConnection $connection, QueryPlaceholderInterface $queryPlaceholder);
  140. /**
  141. * Check whether a condition has been previously compiled.
  142. *
  143. * @return
  144. * TRUE if the condition has been previously compiled.
  145. */
  146. public function compiled();
  147. }
  148. /**
  149. * Interface for a query that can be manipulated via an alter hook.
  150. */
  151. interface QueryAlterableInterface {
  152. /**
  153. * Adds a tag to a query.
  154. *
  155. * Tags are strings that identify a query. A query may have any number of
  156. * tags. Tags are used to mark a query so that alter hooks may decide if they
  157. * wish to take action. Tags should be all lower-case and contain only
  158. * letters, numbers, and underscore, and start with a letter. That is, they
  159. * should follow the same rules as PHP identifiers in general.
  160. *
  161. * @param $tag
  162. * The tag to add.
  163. *
  164. * @return QueryAlterableInterface
  165. * The called object.
  166. */
  167. public function addTag($tag);
  168. /**
  169. * Determines if a given query has a given tag.
  170. *
  171. * @param $tag
  172. * The tag to check.
  173. *
  174. * @return
  175. * TRUE if this query has been marked with this tag, FALSE otherwise.
  176. */
  177. public function hasTag($tag);
  178. /**
  179. * Determines if a given query has all specified tags.
  180. *
  181. * @param $tags
  182. * A variable number of arguments, one for each tag to check.
  183. *
  184. * @return
  185. * TRUE if this query has been marked with all specified tags, FALSE
  186. * otherwise.
  187. */
  188. public function hasAllTags();
  189. /**
  190. * Determines if a given query has any specified tag.
  191. *
  192. * @param $tags
  193. * A variable number of arguments, one for each tag to check.
  194. *
  195. * @return
  196. * TRUE if this query has been marked with at least one of the specified
  197. * tags, FALSE otherwise.
  198. */
  199. public function hasAnyTag();
  200. /**
  201. * Adds additional metadata to the query.
  202. *
  203. * Often, a query may need to provide additional contextual data to alter
  204. * hooks. Alter hooks may then use that information to decide if and how
  205. * to take action.
  206. *
  207. * @param $key
  208. * The unique identifier for this piece of metadata. Must be a string that
  209. * follows the same rules as any other PHP identifier.
  210. * @param $object
  211. * The additional data to add to the query. May be any valid PHP variable.
  212. *
  213. * @return QueryAlterableInterface
  214. * The called object.
  215. */
  216. public function addMetaData($key, $object);
  217. /**
  218. * Retrieves a given piece of metadata.
  219. *
  220. * @param $key
  221. * The unique identifier for the piece of metadata to retrieve.
  222. *
  223. * @return
  224. * The previously attached metadata object, or NULL if one doesn't exist.
  225. */
  226. public function getMetaData($key);
  227. }
  228. /**
  229. * Interface for a query that accepts placeholders.
  230. */
  231. interface QueryPlaceholderInterface {
  232. /**
  233. * Returns a unique identifier for this object.
  234. */
  235. public function uniqueIdentifier();
  236. /**
  237. * Returns the next placeholder ID for the query.
  238. *
  239. * @return
  240. * The next available placeholder ID as an integer.
  241. */
  242. public function nextPlaceholder();
  243. }
  244. /**
  245. * Base class for query builders.
  246. *
  247. * Note that query builders use PHP's magic __toString() method to compile the
  248. * query object into a prepared statement.
  249. */
  250. abstract class Query implements QueryPlaceholderInterface {
  251. /**
  252. * The connection object on which to run this query.
  253. *
  254. * @var DatabaseConnection
  255. */
  256. protected $connection;
  257. /**
  258. * The target of the connection object.
  259. *
  260. * @var string
  261. */
  262. protected $connectionTarget;
  263. /**
  264. * The key of the connection object.
  265. *
  266. * @var string
  267. */
  268. protected $connectionKey;
  269. /**
  270. * The query options to pass on to the connection object.
  271. *
  272. * @var array
  273. */
  274. protected $queryOptions;
  275. /**
  276. * A unique identifier for this query object.
  277. */
  278. protected $uniqueIdentifier;
  279. /**
  280. * The placeholder counter.
  281. */
  282. protected $nextPlaceholder = 0;
  283. /**
  284. * An array of comments that can be prepended to a query.
  285. *
  286. * @var array
  287. */
  288. protected $comments = array();
  289. /**
  290. * Constructs a Query object.
  291. *
  292. * @param DatabaseConnection $connection
  293. * Database connection object.
  294. * @param array $options
  295. * Array of query options.
  296. */
  297. public function __construct(DatabaseConnection $connection, $options) {
  298. $this->uniqueIdentifier = uniqid('', TRUE);
  299. $this->connection = $connection;
  300. $this->connectionKey = $this->connection->getKey();
  301. $this->connectionTarget = $this->connection->getTarget();
  302. $this->queryOptions = $options;
  303. }
  304. /**
  305. * Implements the magic __sleep function to disconnect from the database.
  306. */
  307. public function __sleep() {
  308. $keys = get_object_vars($this);
  309. unset($keys['connection']);
  310. return array_keys($keys);
  311. }
  312. /**
  313. * Implements the magic __wakeup function to reconnect to the database.
  314. */
  315. public function __wakeup() {
  316. $this->connection = Database::getConnection($this->connectionTarget, $this->connectionKey);
  317. }
  318. /**
  319. * Implements the magic __clone function.
  320. */
  321. public function __clone() {
  322. $this->uniqueIdentifier = uniqid('', TRUE);
  323. }
  324. /**
  325. * Runs the query against the database.
  326. */
  327. abstract protected function execute();
  328. /**
  329. * Implements PHP magic __toString method to convert the query to a string.
  330. *
  331. * The toString operation is how we compile a query object to a prepared
  332. * statement.
  333. *
  334. * @return
  335. * A prepared statement query string for this object.
  336. */
  337. abstract public function __toString();
  338. /**
  339. * Returns a unique identifier for this object.
  340. */
  341. public function uniqueIdentifier() {
  342. return $this->uniqueIdentifier;
  343. }
  344. /**
  345. * Gets the next placeholder value for this query object.
  346. *
  347. * @return int
  348. * Next placeholder value.
  349. */
  350. public function nextPlaceholder() {
  351. return $this->nextPlaceholder++;
  352. }
  353. /**
  354. * Adds a comment to the query.
  355. *
  356. * By adding a comment to a query, you can more easily find it in your
  357. * query log or the list of active queries on an SQL server. This allows
  358. * for easier debugging and allows you to more easily find where a query
  359. * with a performance problem is being generated.
  360. *
  361. * The comment string will be sanitized to remove * / and other characters
  362. * that may terminate the string early so as to avoid SQL injection attacks.
  363. *
  364. * @param $comment
  365. * The comment string to be inserted into the query.
  366. *
  367. * @return Query
  368. * The called object.
  369. */
  370. public function comment($comment) {
  371. $this->comments[] = $comment;
  372. return $this;
  373. }
  374. /**
  375. * Returns a reference to the comments array for the query.
  376. *
  377. * Because this method returns by reference, alter hooks may edit the comments
  378. * array directly to make their changes. If just adding comments, however, the
  379. * use of comment() is preferred.
  380. *
  381. * Note that this method must be called by reference as well:
  382. * @code
  383. * $comments =& $query->getComments();
  384. * @endcode
  385. *
  386. * @return
  387. * A reference to the comments array structure.
  388. */
  389. public function &getComments() {
  390. return $this->comments;
  391. }
  392. }
  393. /**
  394. * General class for an abstracted INSERT query.
  395. */
  396. class InsertQuery extends Query {
  397. /**
  398. * The table on which to insert.
  399. *
  400. * @var string
  401. */
  402. protected $table;
  403. /**
  404. * An array of fields on which to insert.
  405. *
  406. * @var array
  407. */
  408. protected $insertFields = array();
  409. /**
  410. * An array of fields that should be set to their database-defined defaults.
  411. *
  412. * @var array
  413. */
  414. protected $defaultFields = array();
  415. /**
  416. * A nested array of values to insert.
  417. *
  418. * $insertValues is an array of arrays. Each sub-array is either an
  419. * associative array whose keys are field names and whose values are field
  420. * values to insert, or a non-associative array of values in the same order
  421. * as $insertFields.
  422. *
  423. * Whether multiple insert sets will be run in a single query or multiple
  424. * queries is left to individual drivers to implement in whatever manner is
  425. * most appropriate. The order of values in each sub-array must match the
  426. * order of fields in $insertFields.
  427. *
  428. * @var array
  429. */
  430. protected $insertValues = array();
  431. /**
  432. * A SelectQuery object to fetch the rows that should be inserted.
  433. *
  434. * @var SelectQueryInterface
  435. */
  436. protected $fromQuery;
  437. /**
  438. * Constructs an InsertQuery object.
  439. *
  440. * @param DatabaseConnection $connection
  441. * A DatabaseConnection object.
  442. * @param string $table
  443. * Name of the table to associate with this query.
  444. * @param array $options
  445. * Array of database options.
  446. */
  447. public function __construct($connection, $table, array $options = array()) {
  448. if (!isset($options['return'])) {
  449. $options['return'] = Database::RETURN_INSERT_ID;
  450. }
  451. parent::__construct($connection, $options);
  452. $this->table = $table;
  453. }
  454. /**
  455. * Adds a set of field->value pairs to be inserted.
  456. *
  457. * This method may only be called once. Calling it a second time will be
  458. * ignored. To queue up multiple sets of values to be inserted at once,
  459. * use the values() method.
  460. *
  461. * @param $fields
  462. * An array of fields on which to insert. This array may be indexed or
  463. * associative. If indexed, the array is taken to be the list of fields.
  464. * If associative, the keys of the array are taken to be the fields and
  465. * the values are taken to be corresponding values to insert. If a
  466. * $values argument is provided, $fields must be indexed.
  467. * @param $values
  468. * An array of fields to insert into the database. The values must be
  469. * specified in the same order as the $fields array.
  470. *
  471. * @return InsertQuery
  472. * The called object.
  473. */
  474. public function fields(array $fields, array $values = array()) {
  475. if (empty($this->insertFields)) {
  476. if (empty($values)) {
  477. if (!is_numeric(key($fields))) {
  478. $values = array_values($fields);
  479. $fields = array_keys($fields);
  480. }
  481. }
  482. $this->insertFields = $fields;
  483. if (!empty($values)) {
  484. $this->insertValues[] = $values;
  485. }
  486. }
  487. return $this;
  488. }
  489. /**
  490. * Adds another set of values to the query to be inserted.
  491. *
  492. * If $values is a numeric-keyed array, it will be assumed to be in the same
  493. * order as the original fields() call. If it is associative, it may be
  494. * in any order as long as the keys of the array match the names of the
  495. * fields.
  496. *
  497. * @param $values
  498. * An array of values to add to the query.
  499. *
  500. * @return InsertQuery
  501. * The called object.
  502. */
  503. public function values(array $values) {
  504. if (is_numeric(key($values))) {
  505. $this->insertValues[] = $values;
  506. }
  507. else {
  508. // Reorder the submitted values to match the fields array.
  509. foreach ($this->insertFields as $key) {
  510. $insert_values[$key] = $values[$key];
  511. }
  512. // For consistency, the values array is always numerically indexed.
  513. $this->insertValues[] = array_values($insert_values);
  514. }
  515. return $this;
  516. }
  517. /**
  518. * Specifies fields for which the database defaults should be used.
  519. *
  520. * If you want to force a given field to use the database-defined default,
  521. * not NULL or undefined, use this method to instruct the database to use
  522. * default values explicitly. In most cases this will not be necessary
  523. * unless you are inserting a row that is all default values, as you cannot
  524. * specify no values in an INSERT query.
  525. *
  526. * Specifying a field both in fields() and in useDefaults() is an error
  527. * and will not execute.
  528. *
  529. * @param $fields
  530. * An array of values for which to use the default values
  531. * specified in the table definition.
  532. *
  533. * @return InsertQuery
  534. * The called object.
  535. */
  536. public function useDefaults(array $fields) {
  537. $this->defaultFields = $fields;
  538. return $this;
  539. }
  540. /**
  541. * Sets the fromQuery on this InsertQuery object.
  542. *
  543. * @param SelectQueryInterface $query
  544. * The query to fetch the rows that should be inserted.
  545. *
  546. * @return InsertQuery
  547. * The called object.
  548. */
  549. public function from(SelectQueryInterface $query) {
  550. $this->fromQuery = $query;
  551. return $this;
  552. }
  553. /**
  554. * Executes the insert query.
  555. *
  556. * @return
  557. * The last insert ID of the query, if one exists. If the query
  558. * was given multiple sets of values to insert, the return value is
  559. * undefined. If no fields are specified, this method will do nothing and
  560. * return NULL. That makes it safe to use in multi-insert loops.
  561. */
  562. public function execute() {
  563. // If validation fails, simply return NULL. Note that validation routines
  564. // in preExecute() may throw exceptions instead.
  565. if (!$this->preExecute()) {
  566. return NULL;
  567. }
  568. // If we're selecting from a SelectQuery, finish building the query and
  569. // pass it back, as any remaining options are irrelevant.
  570. if (!empty($this->fromQuery)) {
  571. $sql = (string) $this;
  572. // The SelectQuery may contain arguments, load and pass them through.
  573. return $this->connection->query($sql, $this->fromQuery->getArguments(), $this->queryOptions);
  574. }
  575. $last_insert_id = 0;
  576. // Each insert happens in its own query in the degenerate case. However,
  577. // we wrap it in a transaction so that it is atomic where possible. On many
  578. // databases, such as SQLite, this is also a notable performance boost.
  579. $transaction = $this->connection->startTransaction();
  580. try {
  581. $sql = (string) $this;
  582. foreach ($this->insertValues as $insert_values) {
  583. $last_insert_id = $this->connection->query($sql, $insert_values, $this->queryOptions);
  584. }
  585. }
  586. catch (Exception $e) {
  587. // One of the INSERTs failed, rollback the whole batch.
  588. $transaction->rollback();
  589. // Rethrow the exception for the calling code.
  590. throw $e;
  591. }
  592. // Re-initialize the values array so that we can re-use this query.
  593. $this->insertValues = array();
  594. // Transaction commits here where $transaction looses scope.
  595. return $last_insert_id;
  596. }
  597. /**
  598. * Implements PHP magic __toString method to convert the query to a string.
  599. *
  600. * @return string
  601. * The prepared statement.
  602. */
  603. public function __toString() {
  604. // Create a sanitized comment string to prepend to the query.
  605. $comments = $this->connection->makeComment($this->comments);
  606. // Default fields are always placed first for consistency.
  607. $insert_fields = array_merge($this->defaultFields, $this->insertFields);
  608. if (!empty($this->fromQuery)) {
  609. return $comments . 'INSERT INTO {' . $this->table . '} (' . implode(', ', $insert_fields) . ') ' . $this->fromQuery;
  610. }
  611. // For simplicity, we will use the $placeholders array to inject
  612. // default keywords even though they are not, strictly speaking,
  613. // placeholders for prepared statements.
  614. $placeholders = array();
  615. $placeholders = array_pad($placeholders, count($this->defaultFields), 'default');
  616. $placeholders = array_pad($placeholders, count($this->insertFields), '?');
  617. return $comments . 'INSERT INTO {' . $this->table . '} (' . implode(', ', $insert_fields) . ') VALUES (' . implode(', ', $placeholders) . ')';
  618. }
  619. /**
  620. * Preprocesses and validates the query.
  621. *
  622. * @return
  623. * TRUE if the validation was successful, FALSE if not.
  624. *
  625. * @throws FieldsOverlapException
  626. * @throws NoFieldsException
  627. */
  628. public function preExecute() {
  629. // Confirm that the user did not try to specify an identical
  630. // field and default field.
  631. if (array_intersect($this->insertFields, $this->defaultFields)) {
  632. throw new FieldsOverlapException('You may not specify the same field to have a value and a schema-default value.');
  633. }
  634. if (!empty($this->fromQuery)) {
  635. // We have to assume that the used aliases match the insert fields.
  636. // Regular fields are added to the query before expressions, maintain the
  637. // same order for the insert fields.
  638. // This behavior can be overridden by calling fields() manually as only the
  639. // first call to fields() does have an effect.
  640. $this->fields(array_merge(array_keys($this->fromQuery->getFields()), array_keys($this->fromQuery->getExpressions())));
  641. }
  642. else {
  643. // Don't execute query without fields.
  644. if (count($this->insertFields) + count($this->defaultFields) == 0) {
  645. throw new NoFieldsException('There are no fields available to insert with.');
  646. }
  647. }
  648. // If no values have been added, silently ignore this query. This can happen
  649. // if values are added conditionally, so we don't want to throw an
  650. // exception.
  651. if (!isset($this->insertValues[0]) && count($this->insertFields) > 0 && empty($this->fromQuery)) {
  652. return FALSE;
  653. }
  654. return TRUE;
  655. }
  656. }
  657. /**
  658. * General class for an abstracted DELETE operation.
  659. */
  660. class DeleteQuery extends Query implements QueryConditionInterface {
  661. /**
  662. * The table from which to delete.
  663. *
  664. * @var string
  665. */
  666. protected $table;
  667. /**
  668. * The condition object for this query.
  669. *
  670. * Condition handling is handled via composition.
  671. *
  672. * @var DatabaseCondition
  673. */
  674. protected $condition;
  675. /**
  676. * Constructs a DeleteQuery object.
  677. *
  678. * @param DatabaseConnection $connection
  679. * A DatabaseConnection object.
  680. * @param string $table
  681. * Name of the table to associate with this query.
  682. * @param array $options
  683. * Array of database options.
  684. */
  685. public function __construct(DatabaseConnection $connection, $table, array $options = array()) {
  686. $options['return'] = Database::RETURN_AFFECTED;
  687. parent::__construct($connection, $options);
  688. $this->table = $table;
  689. $this->condition = new DatabaseCondition('AND');
  690. }
  691. /**
  692. * Implements QueryConditionInterface::condition().
  693. */
  694. public function condition($field, $value = NULL, $operator = NULL) {
  695. $this->condition->condition($field, $value, $operator);
  696. return $this;
  697. }
  698. /**
  699. * Implements QueryConditionInterface::isNull().
  700. */
  701. public function isNull($field) {
  702. $this->condition->isNull($field);
  703. return $this;
  704. }
  705. /**
  706. * Implements QueryConditionInterface::isNotNull().
  707. */
  708. public function isNotNull($field) {
  709. $this->condition->isNotNull($field);
  710. return $this;
  711. }
  712. /**
  713. * Implements QueryConditionInterface::exists().
  714. */
  715. public function exists(SelectQueryInterface $select) {
  716. $this->condition->exists($select);
  717. return $this;
  718. }
  719. /**
  720. * Implements QueryConditionInterface::notExists().
  721. */
  722. public function notExists(SelectQueryInterface $select) {
  723. $this->condition->notExists($select);
  724. return $this;
  725. }
  726. /**
  727. * Implements QueryConditionInterface::conditions().
  728. */
  729. public function &conditions() {
  730. return $this->condition->conditions();
  731. }
  732. /**
  733. * Implements QueryConditionInterface::arguments().
  734. */
  735. public function arguments() {
  736. return $this->condition->arguments();
  737. }
  738. /**
  739. * Implements QueryConditionInterface::where().
  740. */
  741. public function where($snippet, $args = array()) {
  742. $this->condition->where($snippet, $args);
  743. return $this;
  744. }
  745. /**
  746. * Implements QueryConditionInterface::compile().
  747. */
  748. public function compile(DatabaseConnection $connection, QueryPlaceholderInterface $queryPlaceholder) {
  749. return $this->condition->compile($connection, $queryPlaceholder);
  750. }
  751. /**
  752. * Implements QueryConditionInterface::compiled().
  753. */
  754. public function compiled() {
  755. return $this->condition->compiled();
  756. }
  757. /**
  758. * Executes the DELETE query.
  759. *
  760. * @return
  761. * The return value is dependent on the database connection.
  762. */
  763. public function execute() {
  764. $values = array();
  765. if (count($this->condition)) {
  766. $this->condition->compile($this->connection, $this);
  767. $values = $this->condition->arguments();
  768. }
  769. return $this->connection->query((string) $this, $values, $this->queryOptions);
  770. }
  771. /**
  772. * Implements PHP magic __toString method to convert the query to a string.
  773. *
  774. * @return string
  775. * The prepared statement.
  776. */
  777. public function __toString() {
  778. // Create a sanitized comment string to prepend to the query.
  779. $comments = $this->connection->makeComment($this->comments);
  780. $query = $comments . 'DELETE FROM {' . $this->connection->escapeTable($this->table) . '} ';
  781. if (count($this->condition)) {
  782. $this->condition->compile($this->connection, $this);
  783. $query .= "\nWHERE " . $this->condition;
  784. }
  785. return $query;
  786. }
  787. }
  788. /**
  789. * General class for an abstracted TRUNCATE operation.
  790. */
  791. class TruncateQuery extends Query {
  792. /**
  793. * The table to truncate.
  794. *
  795. * @var string
  796. */
  797. protected $table;
  798. /**
  799. * Constructs a TruncateQuery object.
  800. *
  801. * @param DatabaseConnection $connection
  802. * A DatabaseConnection object.
  803. * @param string $table
  804. * Name of the table to associate with this query.
  805. * @param array $options
  806. * Array of database options.
  807. */
  808. public function __construct(DatabaseConnection $connection, $table, array $options = array()) {
  809. $options['return'] = Database::RETURN_AFFECTED;
  810. parent::__construct($connection, $options);
  811. $this->table = $table;
  812. }
  813. /**
  814. * Implements QueryConditionInterface::compile().
  815. */
  816. public function compile(DatabaseConnection $connection, QueryPlaceholderInterface $queryPlaceholder) {
  817. return $this->condition->compile($connection, $queryPlaceholder);
  818. }
  819. /**
  820. * Implements QueryConditionInterface::compiled().
  821. */
  822. public function compiled() {
  823. return $this->condition->compiled();
  824. }
  825. /**
  826. * Executes the TRUNCATE query.
  827. *
  828. * @return
  829. * Return value is dependent on the database type.
  830. */
  831. public function execute() {
  832. return $this->connection->query((string) $this, array(), $this->queryOptions);
  833. }
  834. /**
  835. * Implements PHP magic __toString method to convert the query to a string.
  836. *
  837. * @return string
  838. * The prepared statement.
  839. */
  840. public function __toString() {
  841. // Create a sanitized comment string to prepend to the query.
  842. $comments = $this->connection->makeComment($this->comments);
  843. // In most cases, TRUNCATE is not a transaction safe statement as it is a
  844. // DDL statement which results in an implicit COMMIT. When we are in a
  845. // transaction, fallback to the slower, but transactional, DELETE.
  846. // PostgreSQL also locks the entire table for a TRUNCATE strongly reducing
  847. // the concurrency with other transactions.
  848. if ($this->connection->inTransaction()) {
  849. return $comments . 'DELETE FROM {' . $this->connection->escapeTable($this->table) . '}';
  850. }
  851. else {
  852. return $comments . 'TRUNCATE {' . $this->connection->escapeTable($this->table) . '} ';
  853. }
  854. }
  855. }
  856. /**
  857. * General class for an abstracted UPDATE operation.
  858. */
  859. class UpdateQuery extends Query implements QueryConditionInterface {
  860. /**
  861. * The table to update.
  862. *
  863. * @var string
  864. */
  865. protected $table;
  866. /**
  867. * An array of fields that will be updated.
  868. *
  869. * @var array
  870. */
  871. protected $fields = array();
  872. /**
  873. * An array of values to update to.
  874. *
  875. * @var array
  876. */
  877. protected $arguments = array();
  878. /**
  879. * The condition object for this query.
  880. *
  881. * Condition handling is handled via composition.
  882. *
  883. * @var DatabaseCondition
  884. */
  885. protected $condition;
  886. /**
  887. * Array of fields to update to an expression in case of a duplicate record.
  888. *
  889. * This variable is a nested array in the following format:
  890. * @code
  891. * <some field> => array(
  892. * 'condition' => <condition to execute, as a string>,
  893. * 'arguments' => <array of arguments for condition, or NULL for none>,
  894. * );
  895. * @endcode
  896. *
  897. * @var array
  898. */
  899. protected $expressionFields = array();
  900. /**
  901. * Constructs an UpdateQuery object.
  902. *
  903. * @param DatabaseConnection $connection
  904. * A DatabaseConnection object.
  905. * @param string $table
  906. * Name of the table to associate with this query.
  907. * @param array $options
  908. * Array of database options.
  909. */
  910. public function __construct(DatabaseConnection $connection, $table, array $options = array()) {
  911. $options['return'] = Database::RETURN_AFFECTED;
  912. parent::__construct($connection, $options);
  913. $this->table = $table;
  914. $this->condition = new DatabaseCondition('AND');
  915. }
  916. /**
  917. * Implements QueryConditionInterface::condition().
  918. */
  919. public function condition($field, $value = NULL, $operator = NULL) {
  920. $this->condition->condition($field, $value, $operator);
  921. return $this;
  922. }
  923. /**
  924. * Implements QueryConditionInterface::isNull().
  925. */
  926. public function isNull($field) {
  927. $this->condition->isNull($field);
  928. return $this;
  929. }
  930. /**
  931. * Implements QueryConditionInterface::isNotNull().
  932. */
  933. public function isNotNull($field) {
  934. $this->condition->isNotNull($field);
  935. return $this;
  936. }
  937. /**
  938. * Implements QueryConditionInterface::exists().
  939. */
  940. public function exists(SelectQueryInterface $select) {
  941. $this->condition->exists($select);
  942. return $this;
  943. }
  944. /**
  945. * Implements QueryConditionInterface::notExists().
  946. */
  947. public function notExists(SelectQueryInterface $select) {
  948. $this->condition->notExists($select);
  949. return $this;
  950. }
  951. /**
  952. * Implements QueryConditionInterface::conditions().
  953. */
  954. public function &conditions() {
  955. return $this->condition->conditions();
  956. }
  957. /**
  958. * Implements QueryConditionInterface::arguments().
  959. */
  960. public function arguments() {
  961. return $this->condition->arguments();
  962. }
  963. /**
  964. * Implements QueryConditionInterface::where().
  965. */
  966. public function where($snippet, $args = array()) {
  967. $this->condition->where($snippet, $args);
  968. return $this;
  969. }
  970. /**
  971. * Implements QueryConditionInterface::compile().
  972. */
  973. public function compile(DatabaseConnection $connection, QueryPlaceholderInterface $queryPlaceholder) {
  974. return $this->condition->compile($connection, $queryPlaceholder);
  975. }
  976. /**
  977. * Implements QueryConditionInterface::compiled().
  978. */
  979. public function compiled() {
  980. return $this->condition->compiled();
  981. }
  982. /**
  983. * Adds a set of field->value pairs to be updated.
  984. *
  985. * @param $fields
  986. * An associative array of fields to write into the database. The array keys
  987. * are the field names and the values are the values to which to set them.
  988. *
  989. * @return UpdateQuery
  990. * The called object.
  991. */
  992. public function fields(array $fields) {
  993. $this->fields = $fields;
  994. return $this;
  995. }
  996. /**
  997. * Specifies fields to be updated as an expression.
  998. *
  999. * Expression fields are cases such as counter=counter+1. This method takes
  1000. * precedence over fields().
  1001. *
  1002. * @param $field
  1003. * The field to set.
  1004. * @param $expression
  1005. * The field will be set to the value of this expression. This parameter
  1006. * may include named placeholders.
  1007. * @param $arguments
  1008. * If specified, this is an array of key/value pairs for named placeholders
  1009. * corresponding to the expression.
  1010. *
  1011. * @return UpdateQuery
  1012. * The called object.
  1013. */
  1014. public function expression($field, $expression, array $arguments = NULL) {
  1015. $this->expressionFields[$field] = array(
  1016. 'expression' => $expression,
  1017. 'arguments' => $arguments,
  1018. );
  1019. return $this;
  1020. }
  1021. /**
  1022. * Executes the UPDATE query.
  1023. *
  1024. * @return
  1025. * The number of rows affected by the update.
  1026. */
  1027. public function execute() {
  1028. // Expressions take priority over literal fields, so we process those first
  1029. // and remove any literal fields that conflict.
  1030. $fields = $this->fields;
  1031. $update_values = array();
  1032. foreach ($this->expressionFields as $field => $data) {
  1033. if (!empty($data['arguments'])) {
  1034. $update_values += $data['arguments'];
  1035. }
  1036. unset($fields[$field]);
  1037. }
  1038. // Because we filter $fields the same way here and in __toString(), the
  1039. // placeholders will all match up properly.
  1040. $max_placeholder = 0;
  1041. foreach ($fields as $field => $value) {
  1042. $update_values[':db_update_placeholder_' . ($max_placeholder++)] = $value;
  1043. }
  1044. if (count($this->condition)) {
  1045. $this->condition->compile($this->connection, $this);
  1046. $update_values = array_merge($update_values, $this->condition->arguments());
  1047. }
  1048. return $this->connection->query((string) $this, $update_values, $this->queryOptions);
  1049. }
  1050. /**
  1051. * Implements PHP magic __toString method to convert the query to a string.
  1052. *
  1053. * @return string
  1054. * The prepared statement.
  1055. */
  1056. public function __toString() {
  1057. // Create a sanitized comment string to prepend to the query.
  1058. $comments = $this->connection->makeComment($this->comments);
  1059. // Expressions take priority over literal fields, so we process those first
  1060. // and remove any literal fields that conflict.
  1061. $fields = $this->fields;
  1062. $update_fields = array();
  1063. foreach ($this->expressionFields as $field => $data) {
  1064. $update_fields[] = $field . '=' . $data['expression'];
  1065. unset($fields[$field]);
  1066. }
  1067. $max_placeholder = 0;
  1068. foreach ($fields as $field => $value) {
  1069. $update_fields[] = $field . '=:db_update_placeholder_' . ($max_placeholder++);
  1070. }
  1071. $query = $comments . 'UPDATE {' . $this->connection->escapeTable($this->table) . '} SET ' . implode(', ', $update_fields);
  1072. if (count($this->condition)) {
  1073. $this->condition->compile($this->connection, $this);
  1074. // There is an implicit string cast on $this->condition.
  1075. $query .= "\nWHERE " . $this->condition;
  1076. }
  1077. return $query;
  1078. }
  1079. }
  1080. /**
  1081. * General class for an abstracted MERGE query operation.
  1082. *
  1083. * An ANSI SQL:2003 compatible database would run the following query:
  1084. *
  1085. * @code
  1086. * MERGE INTO table_name_1 USING table_name_2 ON (condition)
  1087. * WHEN MATCHED THEN
  1088. * UPDATE SET column1 = value1 [, column2 = value2 ...]
  1089. * WHEN NOT MATCHED THEN
  1090. * INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...
  1091. * @endcode
  1092. *
  1093. * Other databases (most notably MySQL, PostgreSQL and SQLite) will emulate
  1094. * this statement by running a SELECT and then INSERT or UPDATE.
  1095. *
  1096. * By default, the two table names are identical and they are passed into the
  1097. * the constructor. table_name_2 can be specified by the
  1098. * MergeQuery::conditionTable() method. It can be either a string or a
  1099. * subquery.
  1100. *
  1101. * The condition is built exactly like SelectQuery or UpdateQuery conditions,
  1102. * the UPDATE query part is built similarly like an UpdateQuery and finally the
  1103. * INSERT query part is built similarly like an InsertQuery. However, both
  1104. * UpdateQuery and InsertQuery has a fields method so
  1105. * MergeQuery::updateFields() and MergeQuery::insertFields() needs to be called
  1106. * instead. MergeQuery::fields() can also be called which calls both of these
  1107. * methods as the common case is to use the same column-value pairs for both
  1108. * INSERT and UPDATE. However, this is not mandatory. Another convinient
  1109. * wrapper is MergeQuery::key() which adds the same column-value pairs to the
  1110. * condition and the INSERT query part.
  1111. *
  1112. * Several methods (key(), fields(), insertFields()) can be called to set a
  1113. * key-value pair for the INSERT query part. Subsequent calls for the same
  1114. * fields override the earlier ones. The same is true for UPDATE and key(),
  1115. * fields() and updateFields().
  1116. */
  1117. class MergeQuery extends Query implements QueryConditionInterface {
  1118. /**
  1119. * Returned by execute() if an INSERT query has been executed.
  1120. */
  1121. const STATUS_INSERT = 1;
  1122. /**
  1123. * Returned by execute() if an UPDATE query has been executed.
  1124. */
  1125. const STATUS_UPDATE = 2;
  1126. /**
  1127. * The table to be used for INSERT and UPDATE.
  1128. *
  1129. * @var string
  1130. */
  1131. protected $table;
  1132. /**
  1133. * The table or subquery to be used for the condition.
  1134. */
  1135. protected $conditionTable;
  1136. /**
  1137. * An array of fields on which to insert.
  1138. *
  1139. * @var array
  1140. */
  1141. protected $insertFields = array();
  1142. /**
  1143. * An array of fields which should be set to their database-defined defaults.
  1144. *
  1145. * Used on INSERT.
  1146. *
  1147. * @var array
  1148. */
  1149. protected $defaultFields = array();
  1150. /**
  1151. * An array of values to be inserted.
  1152. *
  1153. * @var string
  1154. */
  1155. protected $insertValues = array();
  1156. /**
  1157. * An array of fields that will be updated.
  1158. *
  1159. * @var array
  1160. */
  1161. protected $updateFields = array();
  1162. /**
  1163. * Array of fields to update to an expression in case of a duplicate record.
  1164. *
  1165. * This variable is a nested array in the following format:
  1166. * @code
  1167. * <some field> => array(
  1168. * 'condition' => <condition to execute, as a string>,
  1169. * 'arguments' => <array of arguments for condition, or NULL for none>,
  1170. * );
  1171. * @endcode
  1172. *
  1173. * @var array
  1174. */
  1175. protected $expressionFields = array();
  1176. /**
  1177. * Flag indicating whether an UPDATE is necessary.
  1178. *
  1179. * @var boolean
  1180. */
  1181. protected $needsUpdate = FALSE;
  1182. /**
  1183. * Constructs a MergeQuery object.
  1184. *
  1185. * @param DatabaseConnection $connection
  1186. * A DatabaseConnection object.
  1187. * @param string $table
  1188. * Name of the table to associate with this query.
  1189. * @param array $options
  1190. * Array of database options.
  1191. */
  1192. public function __construct(DatabaseConnection $connection, $table, array $options = array()) {
  1193. $options['return'] = Database::RETURN_AFFECTED;
  1194. parent::__construct($connection, $options);
  1195. $this->table = $table;
  1196. $this->conditionTable = $table;
  1197. $this->condition = new DatabaseCondition('AND');
  1198. }
  1199. /**
  1200. * Sets the table or subquery to be used for the condition.
  1201. *
  1202. * @param $table
  1203. * The table name or the subquery to be used. Use a SelectQuery object to
  1204. * pass in a subquery.
  1205. *
  1206. * @return MergeQuery
  1207. * The called object.
  1208. */
  1209. protected function conditionTable($table) {
  1210. $this->conditionTable = $table;
  1211. return $this;
  1212. }
  1213. /**
  1214. * Adds a set of field->value pairs to be updated.
  1215. *
  1216. * @param $fields
  1217. * An associative array of fields to write into the database. The array keys
  1218. * are the field names and the values are the values to which to set them.
  1219. *
  1220. * @return MergeQuery
  1221. * The called object.
  1222. */
  1223. public function updateFields(array $fields) {
  1224. $this->updateFields = $fields;
  1225. $this->needsUpdate = TRUE;
  1226. return $this;
  1227. }
  1228. /**
  1229. * Specifies fields to be updated as an expression.
  1230. *
  1231. * Expression fields are cases such as counter = counter + 1. This method
  1232. * takes precedence over MergeQuery::updateFields() and it's wrappers,
  1233. * MergeQuery::key() and MergeQuery::fields().
  1234. *
  1235. * @param $field
  1236. * The field to set.
  1237. * @param $expression
  1238. * The field will be set to the value of this expression. This parameter
  1239. * may include named placeholders.
  1240. * @param $arguments
  1241. * If specified, this is an array of key/value pairs for named placeholders
  1242. * corresponding to the expression.
  1243. *
  1244. * @return MergeQuery
  1245. * The called object.
  1246. */
  1247. public function expression($field, $expression, array $arguments = NULL) {
  1248. $this->expressionFields[$field] = array(
  1249. 'expression' => $expression,
  1250. 'arguments' => $arguments,
  1251. );
  1252. $this->needsUpdate = TRUE;
  1253. return $this;
  1254. }
  1255. /**
  1256. * Adds a set of field->value pairs to be inserted.
  1257. *
  1258. * @param $fields
  1259. * An array of fields on which to insert. This array may be indexed or
  1260. * associative. If indexed, the array is taken to be the list of fields.
  1261. * If associative, the keys of the array are taken to be the fields and
  1262. * the values are taken to be corresponding values to insert. If a
  1263. * $values argument is provided, $fields must be indexed.
  1264. * @param $values
  1265. * An array of fields to insert into the database. The values must be
  1266. * specified in the same order as the $fields array.
  1267. *
  1268. * @return MergeQuery
  1269. * The called object.
  1270. */
  1271. public function insertFields(array $fields, array $values = array()) {
  1272. if ($values) {
  1273. $fields = array_combine($fields, $values);
  1274. }
  1275. $this->insertFields = $fields;
  1276. return $this;
  1277. }
  1278. /**
  1279. * Specifies fields for which the database-defaults should be used.
  1280. *
  1281. * If you want to force a given field to use the database-defined default,
  1282. * not NULL or undefined, use this method to instruct the database to use
  1283. * default values explicitly. In most cases this will not be necessary
  1284. * unless you are inserting a row that is all default values, as you cannot
  1285. * specify no values in an INSERT query.
  1286. *
  1287. * Specifying a field both in fields() and in useDefaults() is an error
  1288. * and will not execute.
  1289. *
  1290. * @param $fields
  1291. * An array of values for which to use the default values
  1292. * specified in the table definition.
  1293. *
  1294. * @return MergeQuery
  1295. * The called object.
  1296. */
  1297. public function useDefaults(array $fields) {
  1298. $this->defaultFields = $fields;
  1299. return $this;
  1300. }
  1301. /**
  1302. * Sets common field-value pairs in the INSERT and UPDATE query parts.
  1303. *
  1304. * This method should only be called once. It may be called either
  1305. * with a single associative array or two indexed arrays. If called
  1306. * with an associative array, the keys are taken to be the fields
  1307. * and the values are taken to be the corresponding values to set.
  1308. * If called with two arrays, the first array is taken as the fields
  1309. * and the second array is taken as the corresponding values.
  1310. *
  1311. * @param $fields
  1312. * An array of fields to insert, or an associative array of fields and
  1313. * values. The keys of the array are taken to be the fields and the values
  1314. * are taken to be corresponding values to insert.
  1315. * @param $values
  1316. * An array of values to set into the database. The values must be
  1317. * specified in the same order as the $fields array.
  1318. *
  1319. * @return MergeQuery
  1320. * The called object.
  1321. */
  1322. public function fields(array $fields, array $values = array()) {
  1323. if ($values) {
  1324. $fields = array_combine($fields, $values);
  1325. }
  1326. foreach ($fields as $key => $value) {
  1327. $this->insertFields[$key] = $value;
  1328. $this->updateFields[$key] = $value;
  1329. }
  1330. $this->needsUpdate = TRUE;
  1331. return $this;
  1332. }
  1333. /**
  1334. * Sets the key field(s) to be used as conditions for this query.
  1335. *
  1336. * This method should only be called once. It may be called either
  1337. * with a single associative array or two indexed arrays. If called
  1338. * with an associative array, the keys are taken to be the fields
  1339. * and the values are taken to be the corresponding values to set.
  1340. * If called with two arrays, the first array is taken as the fields
  1341. * and the second array is taken as the corresponding values.
  1342. *
  1343. * The fields are copied to the condition of the query and the INSERT part.
  1344. * If no other method is called, the UPDATE will become a no-op.
  1345. *
  1346. * @param $fields
  1347. * An array of fields to set, or an associative array of fields and values.
  1348. * @param $values
  1349. * An array of values to set into the database. The values must be
  1350. * specified in the same order as the $fields array.
  1351. *
  1352. * @return MergeQuery
  1353. * The called object.
  1354. */
  1355. public function key(array $fields, array $values = array()) {
  1356. if ($values) {
  1357. $fields = array_combine($fields, $values);
  1358. }
  1359. foreach ($fields as $key => $value) {
  1360. $this->insertFields[$key] = $value;
  1361. $this->condition($key, $value);
  1362. }
  1363. return $this;
  1364. }
  1365. /**
  1366. * Implements QueryConditionInterface::condition().
  1367. */
  1368. public function condition($field, $value = NULL, $operator = NULL) {
  1369. $this->condition->condition($field, $value, $operator);
  1370. return $this;
  1371. }
  1372. /**
  1373. * Implements QueryConditionInterface::isNull().
  1374. */
  1375. public function isNull($field) {
  1376. $this->condition->isNull($field);
  1377. return $this;
  1378. }
  1379. /**
  1380. * Implements QueryConditionInterface::isNotNull().
  1381. */
  1382. public function isNotNull($field) {
  1383. $this->condition->isNotNull($field);
  1384. return $this;
  1385. }
  1386. /**
  1387. * Implements QueryConditionInterface::exists().
  1388. */
  1389. public function exists(SelectQueryInterface $select) {
  1390. $this->condition->exists($select);
  1391. return $this;
  1392. }
  1393. /**
  1394. * Implements QueryConditionInterface::notExists().
  1395. */
  1396. public function notExists(SelectQueryInterface $select) {
  1397. $this->condition->notExists($select);
  1398. return $this;
  1399. }
  1400. /**
  1401. * Implements QueryConditionInterface::conditions().
  1402. */
  1403. public function &conditions() {
  1404. return $this->condition->conditions();
  1405. }
  1406. /**
  1407. * Implements QueryConditionInterface::arguments().
  1408. */
  1409. public function arguments() {
  1410. return $this->condition->arguments();
  1411. }
  1412. /**
  1413. * Implements QueryConditionInterface::where().
  1414. */
  1415. public function where($snippet, $args = array()) {
  1416. $this->condition->where($snippet, $args);
  1417. return $this;
  1418. }
  1419. /**
  1420. * Implements QueryConditionInterface::compile().
  1421. */
  1422. public function compile(DatabaseConnection $connection, QueryPlaceholderInterface $queryPlaceholder) {
  1423. return $this->condition->compile($connection, $queryPlaceholder);
  1424. }
  1425. /**
  1426. * Implements QueryConditionInterface::compiled().
  1427. */
  1428. public function compiled() {
  1429. return $this->condition->compiled();
  1430. }
  1431. /**
  1432. * Implements PHP magic __toString method to convert the query to a string.
  1433. *
  1434. * In the degenerate case, there is no string-able query as this operation
  1435. * is potentially two queries.
  1436. *
  1437. * @return string
  1438. * The prepared query statement.
  1439. */
  1440. public function __toString() {
  1441. }
  1442. public function execute() {
  1443. if (!count($this->condition)) {
  1444. throw new InvalidMergeQueryException(t('Invalid merge query: no conditions'));
  1445. }
  1446. $select = $this->connection->select($this->conditionTable)
  1447. ->condition($this->condition);
  1448. $select->addExpression('1');
  1449. if (!$select->execute()->fetchField()) {
  1450. try {
  1451. $insert = $this->connection->insert($this->table)->fields($this->insertFields);
  1452. if ($this->defaultFields) {
  1453. $insert->useDefaults($this->defaultFields);
  1454. }
  1455. $insert->execute();
  1456. return self::STATUS_INSERT;
  1457. }
  1458. catch (Exception $e) {
  1459. // The insert query failed, maybe it's because a racing insert query
  1460. // beat us in inserting the same row. Retry the select query, if it
  1461. // returns a row, ignore the error and continue with the update
  1462. // query below.
  1463. if (!$select->execute()->fetchField()) {
  1464. throw $e;
  1465. }
  1466. }
  1467. }
  1468. if ($this->needsUpdate) {
  1469. $update = $this->connection->update($this->table)
  1470. ->fields($this->updateFields)
  1471. ->condition($this->condition);
  1472. if ($this->expressionFields) {
  1473. foreach ($this->expressionFields as $field => $data) {
  1474. $update->expression($field, $data['expression'], $data['arguments']);
  1475. }
  1476. }
  1477. $update->execute();
  1478. return self::STATUS_UPDATE;
  1479. }
  1480. }
  1481. }
  1482. /**
  1483. * Generic class for a series of conditions in a query.
  1484. */
  1485. class DatabaseCondition implements QueryConditionInterface, Countable {
  1486. /**
  1487. * Array of conditions.
  1488. *
  1489. * @var array
  1490. */
  1491. protected $conditions = array();
  1492. /**
  1493. * Array of arguments.
  1494. *
  1495. * @var array
  1496. */
  1497. protected $arguments = array();
  1498. /**
  1499. * Whether the conditions have been changed.
  1500. *
  1501. * TRUE if the condition has been changed since the last compile.
  1502. * FALSE if the condition has been compiled and not changed.
  1503. *
  1504. * @var bool
  1505. */
  1506. protected $changed = TRUE;
  1507. /**
  1508. * The identifier of the query placeholder this condition has been compiled against.
  1509. */
  1510. protected $queryPlaceholderIdentifier;
  1511. /**
  1512. * Constructs a DataBaseCondition object.
  1513. *
  1514. * @param string $conjunction
  1515. * The operator to use to combine conditions: 'AND' or 'OR'.
  1516. */
  1517. public function __construct($conjunction) {
  1518. $this->conditions['#conjunction'] = $conjunction;
  1519. }
  1520. /**
  1521. * Implements Countable::count().
  1522. *
  1523. * Returns the size of this conditional. The size of the conditional is the
  1524. * size of its conditional array minus one, because one element is the
  1525. * conjunction.
  1526. */
  1527. public function count() {
  1528. return count($this->conditions) - 1;
  1529. }
  1530. /**
  1531. * Implements QueryConditionInterface::condition().
  1532. */
  1533. public function condition($field, $value = NULL, $operator = NULL) {
  1534. if (!isset($operator)) {
  1535. if (is_array($value)) {
  1536. $operator = 'IN';
  1537. }
  1538. elseif (!isset($value)) {
  1539. $operator = 'IS NULL';
  1540. }
  1541. else {
  1542. $operator = '=';
  1543. }
  1544. }
  1545. $this->conditions[] = array(
  1546. 'field' => $field,
  1547. 'value' => $value,
  1548. 'operator' => $operator,
  1549. );
  1550. $this->changed = TRUE;
  1551. return $this;
  1552. }
  1553. /**
  1554. * Implements QueryConditionInterface::where().
  1555. */
  1556. public function where($snippet, $args = array()) {
  1557. $this->conditions[] = array(
  1558. 'field' => $snippet,
  1559. 'value' => $args,
  1560. 'operator' => NULL,
  1561. );
  1562. $this->changed = TRUE;
  1563. return $this;
  1564. }
  1565. /**
  1566. * Implements QueryConditionInterface::isNull().
  1567. */
  1568. public function isNull($field) {
  1569. return $this->condition($field);
  1570. }
  1571. /**
  1572. * Implements QueryConditionInterface::isNotNull().
  1573. */
  1574. public function isNotNull($field) {
  1575. return $this->condition($field, NULL, 'IS NOT NULL');
  1576. }
  1577. /**
  1578. * Implements QueryConditionInterface::exists().
  1579. */
  1580. public function exists(SelectQueryInterface $select) {
  1581. return $this->condition('', $select, 'EXISTS');
  1582. }
  1583. /**
  1584. * Implements QueryConditionInterface::notExists().
  1585. */
  1586. public function notExists(SelectQueryInterface $select) {
  1587. return $this->condition('', $select, 'NOT EXISTS');
  1588. }
  1589. /**
  1590. * Implements QueryConditionInterface::conditions().
  1591. */
  1592. public function &conditions() {
  1593. return $this->conditions;
  1594. }
  1595. /**
  1596. * Implements QueryConditionInterface::arguments().
  1597. */
  1598. public function arguments() {
  1599. // If the caller forgot to call compile() first, refuse to run.
  1600. if ($this->changed) {
  1601. return NULL;
  1602. }
  1603. return $this->arguments;
  1604. }
  1605. /**
  1606. * Implements QueryConditionInterface::compile().
  1607. */
  1608. public function compile(DatabaseConnection $connection, QueryPlaceholderInterface $queryPlaceholder) {
  1609. // Re-compile if this condition changed or if we are compiled against a
  1610. // different query placeholder object.
  1611. if ($this->changed || isset($this->queryPlaceholderIdentifier) && ($this->queryPlaceholderIdentifier != $queryPlaceholder->uniqueIdentifier())) {
  1612. $this->queryPlaceholderIdentifier = $queryPlaceholder->uniqueIdentifier();
  1613. $condition_fragments = array();
  1614. $arguments = array();
  1615. $conditions = $this->conditions;
  1616. $conjunction = $conditions['#conjunction'];
  1617. unset($conditions['#conjunction']);
  1618. foreach ($conditions as $condition) {
  1619. if (empty($condition['operator'])) {
  1620. // This condition is a literal string, so let it through as is.
  1621. $condition_fragments[] = ' (' . $condition['field'] . ') ';
  1622. $arguments += $condition['value'];
  1623. }
  1624. else {
  1625. // It's a structured condition, so parse it out accordingly.
  1626. // Note that $condition['field'] will only be an object for a dependent
  1627. // DatabaseCondition object, not for a dependent subquery.
  1628. if ($condition['field'] instanceof QueryConditionInterface) {
  1629. // Compile the sub-condition recursively and add it to the list.
  1630. $condition['field']->compile($connection, $queryPlaceholder);
  1631. $condition_fragments[] = '(' . (string) $condition['field'] . ')';
  1632. $arguments += $condition['field']->arguments();
  1633. }
  1634. else {
  1635. // For simplicity, we treat all operators as the same data structure.
  1636. // In the typical degenerate case, this won't get changed.
  1637. $operator_defaults = array(
  1638. 'prefix' => '',
  1639. 'postfix' => '',
  1640. 'delimiter' => '',
  1641. 'operator' => $condition['operator'],
  1642. 'use_value' => TRUE,
  1643. );
  1644. $operator = $connection->mapConditionOperator($condition['operator']);
  1645. if (!isset($operator)) {
  1646. $operator = $this->mapConditionOperator($condition['operator']);
  1647. }
  1648. $operator += $operator_defaults;
  1649. $placeholders = array();
  1650. if ($condition['value'] instanceof SelectQueryInterface) {
  1651. $condition['value']->compile($connection, $queryPlaceholder);
  1652. $placeholders[] = (string) $condition['value'];
  1653. $arguments += $condition['value']->arguments();
  1654. // Subqueries are the actual value of the operator, we don't
  1655. // need to add another below.
  1656. $operator['use_value'] = FALSE;
  1657. }
  1658. // We assume that if there is a delimiter, then the value is an
  1659. // array. If not, it is a scalar. For simplicity, we first convert
  1660. // up to an array so that we can build the placeholders in the same way.
  1661. elseif (!$operator['delimiter']) {
  1662. $condition['value'] = array($condition['value']);
  1663. }
  1664. if ($operator['use_value']) {
  1665. foreach ($condition['value'] as $value) {
  1666. $placeholder = ':db_condition_placeholder_' . $queryPlaceholder->nextPlaceholder();
  1667. $arguments[$placeholder] = $value;
  1668. $placeholders[] = $placeholder;
  1669. }
  1670. }
  1671. $condition_fragments[] = ' (' . $connection->escapeField($condition['field']) . ' ' . $operator['operator'] . ' ' . $operator['prefix'] . implode($operator['delimiter'], $placeholders) . $operator['postfix'] . ') ';
  1672. }
  1673. }
  1674. }
  1675. $this->changed = FALSE;
  1676. $this->stringVersion = implode($conjunction, $condition_fragments);
  1677. $this->arguments = $arguments;
  1678. }
  1679. }
  1680. /**
  1681. * Implements QueryConditionInterface::compiled().
  1682. */
  1683. public function compiled() {
  1684. return !$this->changed;
  1685. }
  1686. /**
  1687. * Implements PHP magic __toString method to convert the conditions to string.
  1688. *
  1689. * @return string
  1690. * A string version of the conditions.
  1691. */
  1692. public function __toString() {
  1693. // If the caller forgot to call compile() first, refuse to run.
  1694. if ($this->changed) {
  1695. return NULL;
  1696. }
  1697. return $this->stringVersion;
  1698. }
  1699. /**
  1700. * PHP magic __clone() method.
  1701. *
  1702. * Only copies fields that implement QueryConditionInterface. Also sets
  1703. * $this->changed to TRUE.
  1704. */
  1705. function __clone() {
  1706. $this->changed = TRUE;
  1707. foreach ($this->conditions as $key => $condition) {
  1708. if ($key !== '#conjunction') {
  1709. if ($condition['field'] instanceOf QueryConditionInterface) {
  1710. $this->conditions[$key]['field'] = clone($condition['field']);
  1711. }
  1712. if ($condition['value'] instanceOf SelectQueryInterface) {
  1713. $this->conditions[$key]['value'] = clone($condition['value']);
  1714. }
  1715. }
  1716. }
  1717. }
  1718. /**
  1719. * Gets any special processing requirements for the condition operator.
  1720. *
  1721. * Some condition types require special processing, such as IN, because
  1722. * the value data they pass in is not a simple value. This is a simple
  1723. * overridable lookup function.
  1724. *
  1725. * @param $operator
  1726. * The condition operator, such as "IN", "BETWEEN", etc. Case-sensitive.
  1727. *
  1728. * @return
  1729. * The extra handling directives for the specified operator, or NULL.
  1730. */
  1731. protected function mapConditionOperator($operator) {
  1732. // $specials does not use drupal_static as its value never changes.
  1733. static $specials = array(
  1734. 'BETWEEN' => array('delimiter' => ' AND '),
  1735. 'IN' => array('delimiter' => ', ', 'prefix' => ' (', 'postfix' => ')'),
  1736. 'NOT IN' => array('delimiter' => ', ', 'prefix' => ' (', 'postfix' => ')'),
  1737. 'EXISTS' => array('prefix' => ' (', 'postfix' => ')'),
  1738. 'NOT EXISTS' => array('prefix' => ' (', 'postfix' => ')'),
  1739. 'IS NULL' => array('use_value' => FALSE),
  1740. 'IS NOT NULL' => array('use_value' => FALSE),
  1741. // Use backslash for escaping wildcard characters.
  1742. 'LIKE' => array('postfix' => " ESCAPE '\\\\'"),
  1743. 'NOT LIKE' => array('postfix' => " ESCAPE '\\\\'"),
  1744. // These ones are here for performance reasons.
  1745. '=' => array(),
  1746. '<' => array(),
  1747. '>' => array(),
  1748. '>=' => array(),
  1749. '<=' => array(),
  1750. );
  1751. if (isset($specials[$operator])) {
  1752. $return = $specials[$operator];
  1753. }
  1754. else {
  1755. // We need to upper case because PHP index matches are case sensitive but
  1756. // do not need the more expensive drupal_strtoupper because SQL statements are ASCII.
  1757. $operator = strtoupper($operator);
  1758. $return = isset($specials[$operator]) ? $specials[$operator] : array();
  1759. }
  1760. $return += array('operator' => $operator);
  1761. return $return;
  1762. }
  1763. }
  1764. /**
  1765. * @} End of "addtogroup database".
  1766. */