InsertTest.php 6.13 KB
Newer Older
1 2 3 4 5 6 7 8 9 10
<?php

/**
 * @file
 * Definition of Drupal\system\Tests\Database\InsertTest.
 */

namespace Drupal\system\Tests\Database;

/**
11
 * Tests the insert builder.
12 13 14 15 16 17 18 19 20 21 22 23
 */
class InsertTest extends DatabaseTestBase {

  public static function getInfo() {
    return array(
      'name' => 'Insert tests',
      'description' => 'Test the Insert query builder.',
      'group' => 'Database',
    );
  }

  /**
24
   * Tests very basic insert functionality.
25 26 27 28 29 30 31 32 33 34 35 36
   */
  function testSimpleInsert() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test}')->fetchField();

    $query = db_insert('test');
    $query->fields(array(
      'name' => 'Yoko',
      'age' => '29',
    ));
    $query->execute();

    $num_records_after = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
37
    $this->assertIdentical($num_records_before + 1, (int) $num_records_after, 'Record inserts correctly.');
38
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Yoko'))->fetchField();
39
    $this->assertIdentical($saved_age, '29', 'Can retrieve after inserting.');
40 41 42
  }

  /**
43
   * Tests that we can insert multiple records in one query object.
44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65
   */
  function testMultiInsert() {
    $num_records_before = (int) db_query('SELECT COUNT(*) FROM {test}')->fetchField();

    $query = db_insert('test');
    $query->fields(array(
      'name' => 'Larry',
      'age' => '30',
    ));

    // We should be able to specify values in any order if named.
    $query->values(array(
      'age' => '31',
      'name' => 'Curly',
    ));

    // We should be able to say "use the field order".
    // This is not the recommended mechanism for most cases, but it should work.
    $query->values(array('Moe', '32'));
    $query->execute();

    $num_records_after = (int) db_query('SELECT COUNT(*) FROM {test}')->fetchField();
66
    $this->assertIdentical($num_records_before + 3, $num_records_after, 'Record inserts correctly.');
67
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Larry'))->fetchField();
68
    $this->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
69
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Curly'))->fetchField();
70
    $this->assertIdentical($saved_age, '31', 'Can retrieve after inserting.');
71
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Moe'))->fetchField();
72
    $this->assertIdentical($saved_age, '32', 'Can retrieve after inserting.');
73 74 75
  }

  /**
76
   * Tests that an insert object can be reused with new data after it executes.
77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
   */
  function testRepeatedInsert() {
    $num_records_before = db_query('SELECT COUNT(*) FROM {test}')->fetchField();

    $query = db_insert('test');

    $query->fields(array(
      'name' => 'Larry',
      'age' => '30',
    ));
    $query->execute();  // This should run the insert, but leave the fields intact.

    // We should be able to specify values in any order if named.
    $query->values(array(
      'age' => '31',
      'name' => 'Curly',
    ));
    $query->execute();

    // We should be able to say "use the field order".
    $query->values(array('Moe', '32'));
    $query->execute();

    $num_records_after = db_query('SELECT COUNT(*) FROM {test}')->fetchField();
101
    $this->assertIdentical((int) $num_records_before + 3, (int) $num_records_after, 'Record inserts correctly.');
102
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Larry'))->fetchField();
103
    $this->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
104
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Curly'))->fetchField();
105
    $this->assertIdentical($saved_age, '31', 'Can retrieve after inserting.');
106
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Moe'))->fetchField();
107
    $this->assertIdentical($saved_age, '32', 'Can retrieve after inserting.');
108 109 110
  }

  /**
111
   * Tests that we can specify fields without values and specify values later.
112 113 114 115 116 117 118 119 120 121 122
   */
  function testInsertFieldOnlyDefinintion() {
    // This is useful for importers, when we want to create a query and define
    // its fields once, then loop over a multi-insert execution.
    db_insert('test')
      ->fields(array('name', 'age'))
      ->values(array('Larry', '30'))
      ->values(array('Curly', '31'))
      ->values(array('Moe', '32'))
      ->execute();
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Larry'))->fetchField();
123
    $this->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
124
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Curly'))->fetchField();
125
    $this->assertIdentical($saved_age, '31', 'Can retrieve after inserting.');
126
    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Moe'))->fetchField();
127
    $this->assertIdentical($saved_age, '32', 'Can retrieve after inserting.');
128 129 130
  }

  /**
131
   * Tests that inserts return the proper auto-increment ID.
132 133 134 135 136 137 138 139 140
   */
  function testInsertLastInsertID() {
    $id = db_insert('test')
      ->fields(array(
        'name' => 'Larry',
        'age' => '30',
      ))
      ->execute();

141
    $this->assertIdentical($id, '5', 'Auto-increment ID returned successfully.');
142 143 144
  }

  /**
145
   * Tests that the INSERT INTO ... SELECT ... syntax works.
146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166
   */
  function testInsertSelect() {
    $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')
      ->from($query)
      ->execute();

    $saved_age = db_query('SELECT age FROM {test} WHERE name = :name', array(':name' => 'Meredith'))->fetchField();
167
    $this->assertIdentical($saved_age, '30', 'Can retrieve after inserting.');
168 169
  }
}