/
DB.php
202 lines (166 loc) · 6.71 KB
/
DB.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
195
196
197
198
199
200
201
202
<?php
class DB
{
protected $_db;
protected static $_instance = null;
private function __construct()
{
$dbName = "learning_db";
$dsn = "mysql:host=localhost";
$username = 'root';
$password = '';
$options = array(
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_LAZY,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);
$this->_db = new PDO($dsn, $username, $password, $options);
$this->_db->query('CREATE DATABASE IF NOT EXISTS ' . $dbName);
$this->_db->query('USE ' . $dbName);
try {
$this->_db->query('SELECT * FROM product LIMIT 1');
} catch (Exception $e) {
$this->fillDB('learning_db.sql');
}
}
public static function DBInstance()
{
if (isset(self::$_instance)) {
return self::$_instance;
} else {
}
return self::$_instance = new self();
}
public function DB()
{
return $this->_db;
}
//
// remove_comments will strip the sql comment lines out of an uploaded sql file
// specifically for mssql and postgres type files in the install....
//
protected function remove_comments($output)
{
$lines = explode("\n", $output);
$output = "";
// try to keep mem. use down
$linecount = count($lines);
$in_comment = false;
for ($i = 0; $i < $linecount; $i++) {
if (preg_match("/^\/\*/", $lines[$i])) {
$in_comment = true;
}
if (!$in_comment) {
if (!preg_match("/^--/", $lines[$i])) {
$output .= $lines[$i] . "\n";
}
}
if (preg_match("/\*\/;/", $lines[$i])) {
$in_comment = false;
}
}
unset($lines);
return $output;
}
//
// remove_remarks will strip the sql comment lines out of an uploaded sql file
//
protected function remove_remarks($sql)
{
$lines = explode("\n", $sql);
// try to keep mem. use down
$sql = "";
$linecount = count($lines);
$output = "";
for ($i = 0; $i < $linecount; $i++) {
if ((strlen($lines[$i]) > 0)) {
if (isset($lines[$i][0]) && $lines[$i][0] != "#") {
$output .= $lines[$i] . "\n";
} else {
$output .= "\n";
}
// Trading a bit of speed for lower mem. use here.
$lines[$i] = "";
}
}
return $output;
}
//
// split_sql_file will split an uploaded sql file into single sql statements.
// Note: expects trim() to have already been run on $sql.
//
protected function split_sql_file($sql, $delimiter)
{
// Split up our string into "possible" SQL statements.
$tokens = explode($delimiter, $sql);
// try to save mem.
$sql = "";
$output = array();
// we don't actually care about the matches preg gives us.
$matches = array();
// this is faster than calling count($oktens) every time thru the loop.
$token_count = count($tokens);
for ($i = 0; $i < $token_count; $i++) {
// Don't wanna add an empty string as the last thing in the array.
if (($i != ($token_count - 1)) || (strlen($tokens[$i] > 0))) {
// This is the total number of single quotes in the token.
$total_quotes = preg_match_all("/'/", $tokens[$i], $matches);
// Counts single quotes that are preceded by an odd number of backslashes,
// which means they're escaped quotes.
$escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$i], $matches);
$unescaped_quotes = $total_quotes - $escaped_quotes;
// If the number of unescaped quotes is even, then the delimiter did NOT occur inside a string literal.
if (($unescaped_quotes % 2) == 0) {
// It's a complete sql statement.
$output[] = $tokens[$i];
// save memory.
$tokens[$i] = "";
} else {
// incomplete sql statement. keep adding tokens until we have a complete one.
// $temp will hold what we have so far.
$temp = $tokens[$i] . $delimiter;
// save memory..
$tokens[$i] = "";
// Do we have a complete statement yet?
$complete_stmt = false;
for ($j = $i + 1; (!$complete_stmt && ($j < $token_count)); $j++) {
// This is the total number of single quotes in the token.
$total_quotes = preg_match_all("/'/", $tokens[$j], $matches);
// Counts single quotes that are preceded by an odd number of backslashes,
// which means they're escaped quotes.
$escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $tokens[$j], $matches);
$unescaped_quotes = $total_quotes - $escaped_quotes;
if (($unescaped_quotes % 2) == 1) {
// odd number of unescaped quotes. In combination with the previous incomplete
// statement(s), we now have a complete statement. (2 odds always make an even)
$output[] = $temp . $tokens[$j];
// save memory.
$tokens[$j] = "";
$temp = "";
// exit the loop.
$complete_stmt = true;
// make sure the outer loop continues at the right point.
$i = $j;
} else {
// even number of unescaped quotes. We still don't have a complete statement.
// (1 odd and 1 even always make an odd)
$temp .= $tokens[$j] . $delimiter;
// save memory.
$tokens[$j] = "";
}
} // for..
} // else
}
}
return $output;
}
protected function fillDB($dump)
{
$sql_query = fread(fopen($dump, 'r'), @filesize($dump)) or die('problem ');
$sql_query = $this->remove_comments($sql_query);
$sql_query = $this->remove_remarks($sql_query);
$sql_query = $this->split_sql_file($sql_query, ';');
foreach ($sql_query as $query) {
$this->_db->query($query);
}
}
}