forked from codeigniter4/CodeIgniter4
-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy path20160428212500_Create_test_tables.php
194 lines (171 loc) · 9.27 KB
/
20160428212500_Create_test_tables.php
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
<?php
declare(strict_types=1);
/**
* This file is part of CodeIgniter 4 framework.
*
* (c) CodeIgniter Foundation <[email protected]>
*
* For the full copyright and license information, please view
* the LICENSE file that was distributed with this source code.
*/
namespace Tests\Support\Database\Migrations;
use CodeIgniter\Database\Migration;
class Migration_Create_test_tables extends Migration
{
public function up(): void
{
// User Table
$this->forge->addField([
'id' => ['type' => 'INTEGER', 'constraint' => 3, 'auto_increment' => true],
'name' => ['type' => 'VARCHAR', 'constraint' => 80],
'email' => ['type' => 'VARCHAR', 'constraint' => 100],
'country' => ['type' => 'VARCHAR', 'constraint' => 40],
'created_at' => ['type' => 'DATETIME', 'null' => true],
'updated_at' => ['type' => 'DATETIME', 'null' => true],
'deleted_at' => ['type' => 'DATETIME', 'null' => true],
])->addKey('id', true)->addUniqueKey('email')->addKey('country')->createTable('user', true);
// Job Table
$this->forge->addField([
'id' => ['type' => 'INTEGER', 'constraint' => 3, 'auto_increment' => true],
'name' => ['type' => 'VARCHAR', 'constraint' => 40],
'description' => ['type' => 'VARCHAR', 'constraint' => 400, 'null' => true],
'created_at' => ['type' => 'INTEGER', 'constraint' => 11, 'null' => true],
'updated_at' => ['type' => 'INTEGER', 'constraint' => 11, 'null' => true],
'deleted_at' => ['type' => 'INTEGER', 'constraint' => 11, 'null' => true],
])->addKey('id', true)->createTable('job', true);
// Misc Table
$this->forge->addField([
'id' => ['type' => 'INTEGER', 'constraint' => 3, 'auto_increment' => true],
'key' => ['type' => 'VARCHAR', 'constraint' => 40],
'value' => ['type' => 'VARCHAR', 'constraint' => 400, 'null' => true],
])->addKey('id', true)->createTable('misc', true);
// Database Type test table
// missing types:
// TINYINT,MEDIUMINT,BIT,YEAR,BINARY,VARBINARY,TINYTEXT,LONGTEXT,
// JSON,Spatial data types
// `id` must be INTEGER else SQLite3 error on not null for autoincrement field.
$dataTypeFields = [
'id' => ['type' => 'INTEGER', 'constraint' => 20, 'auto_increment' => true],
'type_varchar' => ['type' => 'VARCHAR', 'constraint' => 40, 'null' => true],
'type_char' => ['type' => 'CHAR', 'constraint' => 10, 'null' => true],
// TEXT should not be used on SQLSRV. It is deprecated.
'type_text' => ['type' => 'TEXT', 'null' => true],
'type_smallint' => ['type' => 'SMALLINT', 'null' => true],
'type_integer' => ['type' => 'INTEGER', 'null' => true],
// FLOAT should not be used on MySQL.
// CREATE TABLE t (f FLOAT, d DOUBLE);
// INSERT INTO t VALUES(99.9, 99.9);
// SELECT * FROM t WHERE f=99.9; // Empty set
// SELECT * FROM t WHERE d=99.9; // 1 row
'type_float' => ['type' => 'FLOAT', 'null' => true],
'type_numeric' => ['type' => 'NUMERIC', 'constraint' => '18,2', 'null' => true],
'type_date' => ['type' => 'DATE', 'null' => true],
'type_time' => ['type' => 'TIME', 'null' => true],
'type_datetime' => ['type' => 'DATETIME', 'null' => true],
'type_timestamp' => ['type' => 'TIMESTAMP', 'null' => true],
'type_bigint' => ['type' => 'BIGINT', 'null' => true],
'type_real' => ['type' => 'REAL', 'null' => true],
'type_enum' => ['type' => 'ENUM', 'constraint' => ['appel', 'pears'], 'null' => true],
'type_set' => ['type' => 'SET', 'constraint' => ['one', 'two'], 'null' => true],
'type_mediumtext' => ['type' => 'MEDIUMTEXT', 'null' => true],
'type_double' => ['type' => 'DOUBLE', 'null' => true],
'type_decimal' => ['type' => 'DECIMAL', 'constraint' => '18,4', 'null' => true],
'type_blob' => ['type' => 'BLOB', 'null' => true],
'type_boolean' => ['type' => 'BOOLEAN', 'null' => true],
];
if ($this->db->DBDriver === 'Postgre') {
unset(
$dataTypeFields['type_real'],
$dataTypeFields['type_decimal'],
$dataTypeFields['type_enum'],
);
}
if ($this->db->DBDriver === 'SQLSRV') {
unset($dataTypeFields['type_timestamp']);
$dataTypeFields['type_text'] = ['type' => 'NVARCHAR(max)', 'null' => true];
}
if ($this->db->DBDriver === 'Postgre' || $this->db->DBDriver === 'SQLSRV') {
unset(
$dataTypeFields['type_set'],
$dataTypeFields['type_mediumtext'],
$dataTypeFields['type_double']
);
}
$this->forge->addField($dataTypeFields)->addKey('id', true)->createTable('type_test', true);
// Empty Table
$this->forge->addField([
'id' => ['type' => 'INTEGER', 'constraint' => 3, 'auto_increment' => true],
'name' => ['type' => 'VARCHAR', 'constraint' => 40],
'created_at' => ['type' => 'DATE', 'null' => true],
'updated_at' => ['type' => 'DATE', 'null' => true],
])->addKey('id', true)->createTable('empty', true);
// Secondary Table
$this->forge->addField([
'id' => ['type' => 'INTEGER', 'constraint' => 3, 'auto_increment' => true],
'key' => ['type' => 'VARCHAR', 'constraint' => 40],
'value' => ['type' => 'VARCHAR', 'constraint' => 400, 'null' => true],
])->addKey('id', true)->createTable('secondary', true);
// Stringify Primary key Table
$this->forge->addField([
'id' => ['type' => 'VARCHAR', 'constraint' => 3],
'value' => ['type' => 'VARCHAR', 'constraint' => 400, 'null' => true],
])->addKey('id', true)->createTable('stringifypkey', true);
// Table without auto increment field
$this->forge->addField([
'key' => ['type' => 'VARCHAR', 'constraint' => 40, 'unique' => true],
'value' => ['type' => 'VARCHAR', 'constraint' => 400, 'null' => true],
])->addKey('key', true)->createTable('without_auto_increment', true);
// IP Table
$this->forge->addField([
'ip' => ['type' => 'VARCHAR', 'constraint' => 100],
'ip2' => ['type' => 'VARCHAR', 'constraint' => 100],
])->createTable('ip_table', true);
// Database session table
if ($this->db->DBDriver === 'MySQLi') {
$this->forge->addField([
'id' => ['type' => 'VARCHAR', 'constraint' => 128, 'null' => false],
'ip_address' => ['type' => 'VARCHAR', 'constraint' => 45, 'null' => false],
'timestamp timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL',
'data' => ['type' => 'BLOB', 'null' => false],
]);
$this->forge->addKey('id', true);
$this->forge->createTable('ci_sessions', true);
}
if ($this->db->DBDriver === 'Postgre') {
$this->forge->addField([
'id' => ['type' => 'VARCHAR', 'constraint' => 128, 'null' => false],
'ip_address inet NOT NULL',
'timestamp timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL',
"data bytea DEFAULT '' NOT NULL",
]);
$this->forge->addKey('id', true);
$this->forge->createTable('ci_sessions', true);
}
if ($this->db->DBDriver === 'OCI8') {
$this->db->query('CREATE OR REPLACE PACKAGE calculator AS PROCEDURE plus(left IN NUMBER, right IN NUMBER, result OUT NUMBER); END;');
$this->db->query('CREATE OR REPLACE PACKAGE BODY calculator AS PROCEDURE plus(left IN NUMBER, right IN NUMBER, result OUT NUMBER) IS BEGIN result := left + right; END plus; END calculator;');
$this->db->query('CREATE OR REPLACE PROCEDURE plus(left IN NUMBER, right IN NUMBER, output OUT NUMBER) IS BEGIN output := left + right; END;');
$this->db->query('CREATE OR REPLACE PROCEDURE one(cursor OUT SYS_REFCURSOR) IS BEGIN open cursor for select 1 AS ONE from DUAL; END;');
}
}
public function down(): void
{
$this->forge->dropTable('user', true);
$this->forge->dropTable('job', true);
$this->forge->dropTable('misc', true);
$this->forge->dropTable('type_test', true);
$this->forge->dropTable('empty', true);
$this->forge->dropTable('secondary', true);
$this->forge->dropTable('stringifypkey', true);
$this->forge->dropTable('without_auto_increment', true);
$this->forge->dropTable('ip_table', true);
if (in_array($this->db->DBDriver, ['MySQLi', 'Postgre'], true)) {
$this->forge->dropTable('ci_sessions', true);
}
if ($this->db->DBDriver === 'OCI8') {
$this->db->query('DROP PROCEDURE one');
$this->db->query('DROP PROCEDURE plus');
$this->db->query('DROP PACKAGE BODY calculator');
}
}
}