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
|
<?php
namespace SqlParser\Tests\Builder;
use SqlParser\Parser;
use SqlParser\Components\DataType;
use SqlParser\Components\Expression;
use SqlParser\Components\CreateDefinition;
use SqlParser\Components\Key;
use SqlParser\Components\OptionsArray;
use SqlParser\Statements\CreateStatement;
use SqlParser\Tests\TestCase;
class CreateStatementTest extends TestCase
{
public function testBuilderNull()
{
$stmt = new CreateStatement();
$stmt->options = new OptionsArray();
$this->assertEquals('', $stmt->build());
}
public function testBuilderDatabase()
{
$parser = new Parser(
'CREATE DATABASE `mydb` ' .
'DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_general_ci'
);
$stmt = $parser->statements[0];
$this->assertEquals(
'CREATE DATABASE `mydb` ' .
'DEFAULT CHARACTER SET=utf8 DEFAULT COLLATE=utf8_general_ci',
$stmt->build()
);
}
public function testBuilderTable()
{
$stmt = new CreateStatement();
$stmt->name = new Expression('', 'test', '');
$stmt->options = new OptionsArray(array('TABLE'));
$stmt->fields = array(
new CreateDefinition(
'id',
new OptionsArray(array('NOT NULL', 'AUTO_INCREMENT')),
new DataType('INT', array(11), new OptionsArray(array('UNSIGNED')))
),
new CreateDefinition(
'',
null,
new Key('', array('id'), 'PRIMARY KEY')
)
);
$this->assertEquals(
"CREATE TABLE `test` (\n" .
"`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,\n" .
"PRIMARY KEY (`id`)\n" .
") ",
$stmt->build()
);
}
public function testBuilderPartitions()
{
$query = 'CREATE TABLE ts (' . "\n"
. '`id` INT,' . "\n"
. '`purchased` DATE' . "\n"
. ') ' . "\n"
. 'PARTITION BY RANGE(YEAR(purchased))' . "\n"
. 'PARTITIONS 3' . "\n"
. 'SUBPARTITION BY HASH(TO_DAYS(purchased))' . "\n"
. 'SUBPARTITIONS 2' . "\n"
. '(' . "\n"
. 'PARTITION p0 VALUES LESS THAN (1990) (' . "\n"
. 'SUBPARTITION s0,' . "\n"
. 'SUBPARTITION s1' . "\n"
. '),' . "\n"
. 'PARTITION p1 VALUES LESS THAN (2000) (' . "\n"
. 'SUBPARTITION s2,' . "\n"
. 'SUBPARTITION s3' . "\n"
. '),' . "\n"
. 'PARTITION p2 VALUES LESS THAN MAXVALUE (' . "\n"
. 'SUBPARTITION s4,' . "\n"
. 'SUBPARTITION s5' . "\n"
. ')' . "\n"
. ')';
$parser = new Parser($query);
$this->assertEquals($query, $parser->statements[0]->build());
}
public function testBuilderView()
{
$parser = new Parser(
'CREATE VIEW myView (vid, vfirstname) AS ' .
'SELECT id, first_name FROM employee WHERE id = 1'
);
$stmt = $parser->statements[0];
$this->assertEquals(
'CREATE VIEW myView (vid, vfirstname) AS ' .
'SELECT id, first_name FROM employee WHERE id = 1 ',
$stmt->build()
);
}
public function testBuilderTrigger()
{
$stmt = new CreateStatement();
$stmt->options = new OptionsArray(array('TRIGGER'));
$stmt->name = new Expression('ins_sum');
$stmt->entityOptions = new OptionsArray(array('BEFORE', 'INSERT'));
$stmt->table = new Expression('account');
$stmt->body = 'SET @sum = @sum + NEW.amount';
$this->assertEquals(
'CREATE TRIGGER ins_sum BEFORE INSERT ON account ' .
'FOR EACH ROW SET @sum = @sum + NEW.amount',
$stmt->build()
);
}
public function testBuilderRoutine()
{
$parser = new Parser(
'CREATE FUNCTION test (IN `i` INT) RETURNS VARCHAR ' .
'BEGIN ' .
'DECLARE name VARCHAR DEFAULT ""; ' .
'SELECT name INTO name FROM employees WHERE id = i; ' .
'RETURN name; ' .
'END'
);
$stmt = $parser->statements[0];
$this->assertEquals(
'CREATE FUNCTION test (IN `i` INT) RETURNS VARCHAR ' .
'BEGIN ' .
'DECLARE name VARCHAR DEFAULT ""; ' .
'SELECT name INTO name FROM employees WHERE id = i; ' .
'RETURN name; ' .
'END',
$stmt->build()
);
}
}
|
