diff options
| -rw-r--r-- | includes/database/database.inc | 13 | ||||
| -rw-r--r-- | includes/database/mysql/query.inc | 35 | ||||
| -rw-r--r-- | includes/database/pgsql/query.inc | 21 | ||||
| -rw-r--r-- | includes/database/query.inc | 66 | ||||
| -rw-r--r-- | includes/database/sqlite/query.inc | 8 | ||||
| -rw-r--r-- | modules/simpletest/tests/database_test.test | 24 |
6 files changed, 106 insertions, 61 deletions
diff --git a/includes/database/database.inc b/includes/database/database.inc index fcbd925a2..a5da925de 100644 --- a/includes/database/database.inc +++ b/includes/database/database.inc @@ -1413,6 +1413,19 @@ class ExplicitTransactionsNotSupportedException extends Exception { } class InvalidMergeQueryException extends Exception {} /** + * Exception thrown if an insert query specifies a field twice. + * + * It is not allowed to specify a field as default and insert field, this + * exception is thrown if that is the case. + */ +class FieldsOverlapException extends Exception {} + +/** + * Exception thrown if an insert query doesn't specify insert or default fields. + */ +class NoFieldsException extends Exception {} + +/** * A wrapper class for creating and managing database transactions. * * Not all databases or database configurations support transactions. For diff --git a/includes/database/mysql/query.inc b/includes/database/mysql/query.inc index 84caf0386..09878f96d 100644 --- a/includes/database/mysql/query.inc +++ b/includes/database/mysql/query.inc @@ -15,31 +15,24 @@ class InsertQuery_mysql extends InsertQuery { public function execute() { - - // Confirm that the user did not try to specify an identical - // field and default field. - if (array_intersect($this->insertFields, $this->defaultFields)) { - throw new PDOException('You may not specify the same field to have a value and a schema-default value.'); - } - - if (count($this->insertFields) + count($this->defaultFields) == 0 && empty($this->fromQuery)) { + if (!$this->preExecute()) { return NULL; } - // Don't execute query without values. - if (!isset($this->insertValues[0]) && count($this->insertFields) > 0 && empty($this->fromQuery)) { - return NULL; - } - - $last_insert_id = 0; - - $max_placeholder = 0; - $values = array(); - foreach ($this->insertValues as $insert_values) { - foreach ($insert_values as $value) { - $values[':db_insert_placeholder_' . $max_placeholder++] = $value; + // If we're selecting from a SelectQuery, finish building the query and + // pass it back, as any remaining options are irrelevant. + if (empty($this->fromQuery)) { + $max_placeholder = 0; + $values = array(); + foreach ($this->insertValues as $insert_values) { + foreach ($insert_values as $value) { + $values[':db_insert_placeholder_' . $max_placeholder++] = $value; + } } } + else { + $values = $this->fromQuery->getArguments(); + } $last_insert_id = $this->connection->query((string)$this, $values, $this->queryOptions); @@ -56,6 +49,8 @@ class InsertQuery_mysql extends InsertQuery { // Default fields are always placed first for consistency. $insert_fields = array_merge($this->defaultFields, $this->insertFields); + // If we're selecting from a SelectQuery, finish building the query and + // pass it back, as any remaining options are irrelevant. if (!empty($this->fromQuery)) { return "INSERT $delay INTO {" . $this->table . '} (' . implode(', ', $insert_fields) . ') ' . $this->fromQuery; } diff --git a/includes/database/pgsql/query.inc b/includes/database/pgsql/query.inc index 489d57e65..ab16a70ec 100644 --- a/includes/database/pgsql/query.inc +++ b/includes/database/pgsql/query.inc @@ -15,19 +15,7 @@ class InsertQuery_pgsql extends InsertQuery { public function execute() { - - // Confirm that the user did not try to specify an identical - // field and default field. - if (array_intersect($this->insertFields, $this->defaultFields)) { - throw new PDOException('You may not specify the same field to have a value and a schema-default value.'); - } - - if (count($this->insertFields) + count($this->defaultFields) == 0 && empty($this->fromQuery)) { - return NULL; - } - - // Don't execute query without values. - if (!isset($this->insertValues[0]) && count($this->insertFields) > 0 && empty($this->fromQuery)) { + if (!$this->preExecute()) { return NULL; } @@ -56,6 +44,11 @@ class InsertQuery_pgsql extends InsertQuery { } } } + if (!empty($this->fromQuery)) { + foreach ($this->fromQuery->getArguments() as $key => $value) { + $stmt->bindParam($key, $value); + } + } // PostgreSQL requires the table name to be specified explicitly // when requesting the last insert ID, so we pass that in via @@ -82,6 +75,8 @@ class InsertQuery_pgsql extends InsertQuery { // Default fields are always placed first for consistency. $insert_fields = array_merge($this->defaultFields, $this->insertFields); + // If we're selecting from a SelectQuery, finish building the query and + // pass it back, as any remaining options are irrelevant. if (!empty($this->fromQuery)) { return "INSERT INTO {" . $this->table . '} (' . implode(', ', $insert_fields) . ') ' . $this->fromQuery; } diff --git a/includes/database/query.inc b/includes/database/query.inc index b27eb6a0c..b6b47f48d 100644 --- a/includes/database/query.inc +++ b/includes/database/query.inc @@ -287,7 +287,8 @@ class InsertQuery extends Query { /** * A SelectQuery object to fetch the rows that should be inserted. - * + * + * @var SelectQueryInterface */ protected $fromQuery; @@ -432,29 +433,20 @@ class InsertQuery extends Query { * in multi-insert loops. */ public function execute() { - - $last_insert_id = 0; - - // Check if a SelectQuery is passed in and use that. - if (!empty($this->fromQuery)) { - return $this->connection->query((string) $this, array(), $this->queryOptions); - } - - // Confirm that the user did not try to specify an identical - // field and default field. - if (array_intersect($this->insertFields, $this->defaultFields)) { - throw new PDOException('You may not specify the same field to have a value and a schema-default value.'); - } - - if (count($this->insertFields) + count($this->defaultFields) == 0) { + if (!$this->preExecute()) { return NULL; } - // Don't execute query without values. - if (!isset($this->insertValues[0]) && count($this->insertFields) > 0) { - return NULL; + // If we're selecting from a SelectQuery, finish building the query and + // pass it back, as any remaining options are irrelevant. + if (!empty($this->fromQuery)) { + $sql = (string)$this; + // The SelectQuery may contain arguments, load and pass them through. + return $this->connection->query($sql, $this->fromQuery->getArguments(), $this->queryOptions); } + $last_insert_id = 0; + // Each insert happens in its own query in the degenerate case. However, // we wrap it in a transaction so that it is atomic where possible. On many // databases, such as SQLite, this is also a notable performance boost. @@ -490,6 +482,42 @@ class InsertQuery extends Query { return 'INSERT INTO {' . $this->table . '} (' . implode(', ', $insert_fields) . ') VALUES (' . implode(', ', $placeholders) . ')'; } + + /** + * Generic preparation and validation for an INSERT query. + * + * @return + * TRUE if the validation was successful, FALSE if not. + */ + protected function preExecute() { + // Confirm that the user did not try to specify an identical + // field and default field. + if (array_intersect($this->insertFields, $this->defaultFields)) { + throw new FieldsOverlapException('You may not specify the same field to have a value and a schema-default value.'); + } + + if (!empty($this->fromQuery)) { + // We have to assume that the used aliases match the insert fields. + // Regular fields are added to the query before expressions, maintain the + // same order for the insert fields. + // This behavior can be overriden by calling fields() manually as only the + // first call to fields() does have an effect. + $this->fields(array_merge(array_keys($this->fromQuery->getFields()), array_keys($this->fromQuery->getExpressions()))); + } + + // Don't execute query without fields. + if (count($this->insertFields) + count($this->defaultFields) == 0) { + throw new NoFieldsException('There are no fields available to insert with.'); + } + + // If no values have been added, silently ignore this query. This can happen + // if values are added conditionally, so we don't want to throw an + // exception. + if (!isset($this->insertValues[0]) && count($this->insertFields) > 0 && empty($this->fromQuery)) { + return FALSE; + } + return TRUE; + } } /** diff --git a/includes/database/sqlite/query.inc b/includes/database/sqlite/query.inc index 287fc9008..35314e8a8 100644 --- a/includes/database/sqlite/query.inc +++ b/includes/database/sqlite/query.inc @@ -21,11 +21,7 @@ class InsertQuery_sqlite extends InsertQuery { public function execute() { - if (count($this->insertFields) + count($this->defaultFields) == 0 && empty($this->fromQuery)) { - return NULL; - } - // Don't execute query without values. - if (!isset($this->insertValues[0]) && count($this->insertFields) > 0 && empty($this->fromQuery)) { + if (!$this->preExecute()) { return NULL; } if (count($this->insertFields)) { @@ -40,6 +36,8 @@ class InsertQuery_sqlite extends InsertQuery { // Produce as many generic placeholders as necessary. $placeholders = array_fill(0, count($this->insertFields), '?'); + // If we're selecting from a SelectQuery, finish building the query and + // pass it back, as any remaining options are irrelevant. if (!empty($this->fromQuery)) { return "INSERT INTO {" . $this->table . '} (' . implode(', ', $this->insertFields) . ') ' . $this->fromQuery; } diff --git a/modules/simpletest/tests/database_test.test b/modules/simpletest/tests/database_test.test index bae378c9c..c354e9a8e 100644 --- a/modules/simpletest/tests/database_test.test +++ b/modules/simpletest/tests/database_test.test @@ -518,10 +518,21 @@ class DatabaseInsertTestCase extends DatabaseTestCase { * Test that the INSERT INTO ... SELECT ... syntax works. */ function testInsertSelect() { - $query = db_select('test_people', 'tp')->fields('tp', array('name', 'age', 'job')); + $query = db_select('test_people', 'tp'); + // The query builder will always append expressions after fields. + // Add the expression first to test that the insert fields are correctly + // re-ordered. + $query->addExpression('tp.age', 'age'); + $query + ->fields('tp', array('name','job')) + ->condition('tp.name', 'Meredith'); + // The resulting query should be equivalent to: + // INSERT INTO test (age, name, job) + // SELECT tp.age AS age, tp.name AS name, tp.job AS job + // FROM test_people tp + // WHERE tp.name = 'Meredith' db_insert('test') - ->fields(array('name', 'age', 'job')) ->from($query) ->execute(); @@ -603,8 +614,13 @@ class DatabaseInsertDefaultsTestCase extends DatabaseTestCase { function testDefaultEmptyInsert() { $num_records_before = (int) db_query('SELECT COUNT(*) FROM {test}')->fetchField(); - $result = db_insert('test')->execute(); - $this->assertNull($result, t('Return NULL as no fields are specified.')); + try { + $result = db_insert('test')->execute(); + // This is only executed if no exception has been thrown. + $this->fail(t('Expected exception NoFieldsException has not been thrown.')); + } catch (NoFieldsException $e) { + $this->pass(t('Expected exception NoFieldsException has been thrown.')); + } $num_records_after = (int) db_query('SELECT COUNT(*) FROM {test}')->fetchField(); $this->assertIdentical($num_records_before, $num_records_after, t('Do nothing as no fields are specified.')); |