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
|
<?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 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()
);
}
}
|
