1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
|
<?php
// $Id$
/**
* @file
* Tests for the Database Schema API.
*/
/**
* Unit tests for the Schema API.
*/
class SchemaTestCase extends DrupalWebTestCase {
public static function getInfo() {
return array(
'name' => 'Schema API',
'description' => 'Tests table creation and modification via the schema API.',
'group' => 'Database',
);
}
/**
*
*/
function testSchema() {
// Try creating a table.
$table_specification = array(
'description' => 'Schema table description.',
'fields' => array(
'id' => array(
'type' => 'int',
'default' => NULL,
),
'test_field' => array(
'type' => 'int',
'not null' => TRUE,
'description' => 'Schema column description.',
),
),
);
db_create_table('test_table', $table_specification);
// Assert that the table exists.
$this->assertTrue(db_table_exists('test_table'), 'The table exists.');
// Assert that the table comment has been set.
$this->checkSchemaComment($table_specification['description'], 'test_table');
// Assert that the column comment has been set.
$this->checkSchemaComment($table_specification['fields']['test_field']['description'], 'test_table', 'test_field');
// An insert without a value for the column 'test_table' should fail.
$this->assertFalse($this->tryInsert(), 'Insert without a default failed.');
// Add a default value to the column.
db_field_set_default('test_table', 'test_field', 0);
// The insert should now succeed.
$this->assertTrue($this->tryInsert(), 'Insert with a default succeeded.');
// Remove the default.
db_field_set_no_default('test_table', 'test_field');
// The insert should fail again.
$this->assertFalse($this->tryInsert(), 'Insert without a default failed.');
// Test for fake index and test for the boolean result of indexExists().
$index_exists = Database::getConnection()->schema()->indexExists('test_table', 'test_field');
$this->assertIdentical($index_exists, FALSE, 'Fake index does not exists');
// Add index.
db_add_index('test_table', 'test_field', array('test_field'));
// Test for created index and test for the boolean result of indexExists().
$index_exists = Database::getConnection()->schema()->indexExists('test_table', 'test_field');
$this->assertIdentical($index_exists, TRUE, 'Index created.');
// Rename the table.
db_rename_table('test_table', 'test_table2');
// Index should be renamed.
$index_exists = Database::getConnection()->schema()->indexExists('test_table2', 'test_field');
$this->assertTrue($index_exists, 'Index was renamed.');
// We need the default so that we can insert after the rename.
db_field_set_default('test_table2', 'test_field', 0);
$this->assertFalse($this->tryInsert(), 'Insert into the old table failed.');
$this->assertTrue($this->tryInsert('test_table2'), 'Insert into the new table succeeded.');
// We should have successfully inserted exactly two rows.
$count = db_query('SELECT COUNT(*) FROM {test_table2}')->fetchField();
$this->assertEqual($count, 2, 'Two fields were successfully inserted.');
// Try to drop the table.
db_drop_table('test_table2');
$this->assertFalse(db_table_exists('test_table2'), 'The dropped table does not exist.');
// Recreate the table.
db_create_table('test_table', $table_specification);
db_field_set_default('test_table', 'test_field', 0);
db_add_field('test_table', 'test_serial', array('type' => 'int', 'not null' => TRUE, 'default' => 0, 'description' => 'Added column description.'));
// Assert that the column comment has been set.
$this->checkSchemaComment('Added column description.', 'test_table', 'test_serial');
// Change the new field to a serial column.
db_change_field('test_table', 'test_serial', 'test_serial', array('type' => 'serial', 'not null' => TRUE, 'description' => 'Changed column description.'), array('primary key' => array('test_serial')));
// Assert that the column comment has been set.
$this->checkSchemaComment('Changed column description.', 'test_table', 'test_serial');
$this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
$max1 = db_query('SELECT MAX(test_serial) FROM {test_table}')->fetchField();
$this->assertTrue($this->tryInsert(), 'Insert with a serial succeeded.');
$max2 = db_query('SELECT MAX(test_serial) FROM {test_table}')->fetchField();
$this->assertTrue($max2 > $max1, 'The serial is monotone.');
$count = db_query('SELECT COUNT(*) FROM {test_table}')->fetchField();
$this->assertEqual($count, 2, 'There were two rows.');
}
function tryInsert($table = 'test_table') {
try {
db_insert($table)
->fields(array('id' => mt_rand(10, 20)))
->execute();
return TRUE;
}
catch (Exception $e) {
return FALSE;
}
}
/**
* Checks that a table or column comment matches a given description.
*
* @param $description
* The asserted description.
* @param $table
* The table to test.
* @param $column
* Optional column to test.
*/
function checkSchemaComment($description, $table, $column = NULL) {
if (method_exists(Database::getConnection()->schema(), 'getComment')) {
$comment = Database::getConnection()->schema()->getComment($table, $column);
$this->assertEqual($comment, $description, 'The comment matches the schema description.');
}
}
/**
* Tests creating unsigned columns and data integrity thereof.
*/
function testUnsignedColumns() {
// First create the table with just a serial column.
$table_name = 'unsigned_table';
$table_spec = array(
'fields' => array('serial_column' => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE)),
'primary key' => array('serial_column'),
);
$ret = array();
db_create_table($table_name, $table_spec);
// Now set up columns for the other types.
$types = array('int', 'float', 'numeric');
foreach ($types as $type) {
$column_spec = array('type' => $type, 'unsigned' => TRUE);
if ($type == 'numeric') {
$column_spec += array('precision' => 10, 'scale' => 0);
}
$column_name = $type . '_column';
$table_spec['fields'][$column_name] = $column_spec;
db_add_field($table_name, $column_name, $column_spec);
}
// Finally, check each column and try to insert invalid values into them.
foreach ($table_spec['fields'] as $column_name => $column_spec) {
$this->assertTrue(db_field_exists($table_name, $column_name), 'Unsigned ' . $column_spec['type'] . ' column was created.');
$this->assertFalse($this->tryUnsignedInsert($table_name, $column_name), 'Unsigned ' . $column_spec['type'] . ' column rejected a negative value.');
}
}
/**
* Tries to insert a negative value into columns defined as unsigned.
*
* @param $table_name
* The table to insert
* @param $column_name
* The column to insert
* @return
* TRUE if the insert succeeded, FALSE otherwise
*/
function tryUnsignedInsert($table_name, $column_name) {
try {
db_insert($table_name)
->fields(array($column_name => -1))
->execute();
return TRUE;
}
catch (Exception $e) {
return FALSE;
}
}
}
|
