Verzeichnisstruktur phpBB-3.2.0
- Veröffentlicht
- 06.01.2017
So funktioniert es
|
|
Auf das letzte Element klicken. Dies geht jeweils ein Schritt zurück |
Auf das Icon klicken, dies öffnet das Verzeichnis. Nochmal klicken schließt das Verzeichnis. |
|
|
(Beispiel Datei-Icons)
|
Auf das Icon klicken um den Quellcode anzuzeigen |
mssql_extractor.php
001 <?php
002 /**
003 *
004 * This file is part of the phpBB Forum Software package.
005 *
006 * @copyright (c) phpBB Limited <https://www.phpbb.com>
007 * @license GNU General Public License, version 2 (GPL-2.0)
008 *
009 * For full copyright and license information, please see
010 * the docs/CREDITS.txt file.
011 *
012 */
013
014 namespace phpbb\db\extractor;
015
016 use phpbb\db\extractor\exception\extractor_not_initialized_exception;
017
018 class mssql_extractor extends base_extractor
019 {
020 /**
021 * Writes closing line(s) to database backup
022 *
023 * @return null
024 * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor()
025 */
026 public function write_end()
027 {
028 if (!$this->is_initialized)
029 {
030 throw new extractor_not_initialized_exception();
031 }
032
033 $this->flush("COMMIT\nGO\n");
034 parent::write_end();
035 }
036
037 /**
038 * {@inheritdoc}
039 */
040 public function write_start($table_prefix)
041 {
042 if (!$this->is_initialized)
043 {
044 throw new extractor_not_initialized_exception();
045 }
046
047 $sql_data = "--\n";
048 $sql_data .= "-- phpBB Backup Script\n";
049 $sql_data .= "-- Dump of tables for $table_prefix\n";
050 $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n";
051 $sql_data .= "--\n";
052 $sql_data .= "BEGIN TRANSACTION\n";
053 $sql_data .= "GO\n";
054 $this->flush($sql_data);
055 }
056
057 /**
058 * {@inheritdoc}
059 */
060 public function write_table($table_name)
061 {
062 if (!$this->is_initialized)
063 {
064 throw new extractor_not_initialized_exception();
065 }
066
067 $sql_data = '-- Table: ' . $table_name . "\n";
068 $sql_data .= "IF OBJECT_ID(N'$table_name', N'U') IS NOT NULL\n";
069 $sql_data .= "DROP TABLE $table_name;\n";
070 $sql_data .= "GO\n";
071 $sql_data .= "\nCREATE TABLE [$table_name] (\n";
072 $rows = array();
073
074 $text_flag = false;
075
076 $sql = "SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') as IS_IDENTITY
077 FROM INFORMATION_SCHEMA.COLUMNS
078 WHERE TABLE_NAME = '$table_name'";
079 $result = $this->db->sql_query($sql);
080
081 while ($row = $this->db->sql_fetchrow($result))
082 {
083 $line = "\t[{$row['COLUMN_NAME']}] [{$row['DATA_TYPE']}]";
084
085 if ($row['DATA_TYPE'] == 'text')
086 {
087 $text_flag = true;
088 }
089
090 if ($row['IS_IDENTITY'])
091 {
092 $line .= ' IDENTITY (1 , 1)';
093 }
094
095 if ($row['CHARACTER_MAXIMUM_LENGTH'] && $row['DATA_TYPE'] !== 'text')
096 {
097 $line .= ' (' . $row['CHARACTER_MAXIMUM_LENGTH'] . ')';
098 }
099
100 if ($row['IS_NULLABLE'] == 'YES')
101 {
102 $line .= ' NULL';
103 }
104 else
105 {
106 $line .= ' NOT NULL';
107 }
108
109 if ($row['COLUMN_DEFAULT'])
110 {
111 $line .= ' DEFAULT ' . $row['COLUMN_DEFAULT'];
112 }
113
114 $rows[] = $line;
115 }
116 $this->db->sql_freeresult($result);
117
118 $sql_data .= implode(",\n", $rows);
119 $sql_data .= "\n) ON [PRIMARY]";
120
121 if ($text_flag)
122 {
123 $sql_data .= " TEXTIMAGE_ON [PRIMARY]";
124 }
125
126 $sql_data .= "\nGO\n\n";
127 $rows = array();
128
129 $sql = "SELECT CONSTRAINT_NAME, COLUMN_NAME
130 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
131 WHERE TABLE_NAME = '$table_name'";
132 $result = $this->db->sql_query($sql);
133 while ($row = $this->db->sql_fetchrow($result))
134 {
135 if (!sizeof($rows))
136 {
137 $sql_data .= "ALTER TABLE [$table_name] WITH NOCHECK ADD\n";
138 $sql_data .= "\tCONSTRAINT [{$row['CONSTRAINT_NAME']}] PRIMARY KEY CLUSTERED \n\t(\n";
139 }
140 $rows[] = "\t\t[{$row['COLUMN_NAME']}]";
141 }
142 if (sizeof($rows))
143 {
144 $sql_data .= implode(",\n", $rows);
145 $sql_data .= "\n\t) ON [PRIMARY] \nGO\n";
146 }
147 $this->db->sql_freeresult($result);
148
149 $index = array();
150 $sql = "EXEC sp_statistics '$table_name'";
151 $result = $this->db->sql_query($sql);
152 while ($row = $this->db->sql_fetchrow($result))
153 {
154 if ($row['TYPE'] == 3)
155 {
156 $index[$row['INDEX_NAME']][] = '[' . $row['COLUMN_NAME'] . ']';
157 }
158 }
159 $this->db->sql_freeresult($result);
160
161 foreach ($index as $index_name => $column_name)
162 {
163 $index[$index_name] = implode(', ', $column_name);
164 }
165
166 foreach ($index as $index_name => $columns)
167 {
168 $sql_data .= "\nCREATE INDEX [$index_name] ON [$table_name]($columns) ON [PRIMARY]\nGO\n";
169 }
170 $this->flush($sql_data);
171 }
172
173 /**
174 * {@inheritdoc}
175 */
176 public function write_data($table_name)
177 {
178 if (!$this->is_initialized)
179 {
180 throw new extractor_not_initialized_exception();
181 }
182
183 if ($this->db->get_sql_layer() === 'mssqlnative')
184 {
185 $this->write_data_mssqlnative($table_name);
186 }
187 else
188 {
189 $this->write_data_odbc($table_name);
190 }
191 }
192
193 /**
194 * Extracts data from database table (for MSSQL Native driver)
195 *
196 * @param string $table_name name of the database table
197 * @return null
198 * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor()
199 */
200 protected function write_data_mssqlnative($table_name)
201 {
202 if (!$this->is_initialized)
203 {
204 throw new extractor_not_initialized_exception();
205 }
206
207 $ary_type = $ary_name = array();
208 $ident_set = false;
209 $sql_data = '';
210
211 // Grab all of the data from current table.
212 $sql = "SELECT * FROM $table_name";
213 $this->db->mssqlnative_set_query_options(array('Scrollable' => SQLSRV_CURSOR_STATIC));
214 $result = $this->db->sql_query($sql);
215
216 $retrieved_data = $this->db->mssqlnative_num_rows($result);
217
218 if (!$retrieved_data)
219 {
220 $this->db->sql_freeresult($result);
221 return;
222 }
223
224 $sql = "SELECT COLUMN_NAME, DATA_TYPE
225 FROM INFORMATION_SCHEMA.COLUMNS
226 WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = '" . $this->db->sql_escape($table_name) . "'";
227 $result_fields = $this->db->sql_query($sql);
228
229 $i_num_fields = 0;
230 while ($row = $this->db->sql_fetchrow($result_fields))
231 {
232 $ary_type[$i_num_fields] = $row['DATA_TYPE'];
233 $ary_name[$i_num_fields] = $row['COLUMN_NAME'];
234 $i_num_fields++;
235 }
236 $this->db->sql_freeresult($result_fields);
237
238 $sql = "SELECT 1 as has_identity
239 FROM INFORMATION_SCHEMA.COLUMNS
240 WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1";
241 $result2 = $this->db->sql_query($sql);
242 $row2 = $this->db->sql_fetchrow($result2);
243
244 if (!empty($row2['has_identity']))
245 {
246 $sql_data .= "\nSET IDENTITY_INSERT $table_name ON\nGO\n";
247 $ident_set = true;
248 }
249 $this->db->sql_freeresult($result2);
250
251 while ($row = $this->db->sql_fetchrow($result))
252 {
253 $schema_vals = $schema_fields = array();
254
255 // Build the SQL statement to recreate the data.
256 for ($i = 0; $i < $i_num_fields; $i++)
257 {
258 $str_val = $row[$ary_name[$i]];
259
260 // defaults to type number - better quote just to be safe, so check for is_int too
261 if (is_int($ary_type[$i]) || preg_match('#char|text|bool|varbinary#i', $ary_type[$i]))
262 {
263 $str_quote = '';
264 $str_empty = "''";
265 $str_val = sanitize_data_mssql(str_replace("'", "''", $str_val));
266 }
267 else if (preg_match('#date|timestamp#i', $ary_type[$i]))
268 {
269 if (empty($str_val))
270 {
271 $str_quote = '';
272 }
273 else
274 {
275 $str_quote = "'";
276 }
277 }
278 else
279 {
280 $str_quote = '';
281 $str_empty = 'NULL';
282 }
283
284 if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val)))
285 {
286 $str_val = $str_empty;
287 }
288
289 $schema_vals[$i] = $str_quote . $str_val . $str_quote;
290 $schema_fields[$i] = $ary_name[$i];
291 }
292
293 // Take the ordered fields and their associated data and build it
294 // into a valid sql statement to recreate that field in the data.
295 $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\nGO\n";
296
297 $this->flush($sql_data);
298 $sql_data = '';
299 }
300 $this->db->sql_freeresult($result);
301
302 if ($ident_set)
303 {
304 $sql_data .= "\nSET IDENTITY_INSERT $table_name OFF\nGO\n";
305 }
306 $this->flush($sql_data);
307 }
308
309 /**
310 * Extracts data from database table (for ODBC driver)
311 *
312 * @param string $table_name name of the database table
313 * @return null
314 * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor()
315 */
316 protected function write_data_odbc($table_name)
317 {
318 if (!$this->is_initialized)
319 {
320 throw new extractor_not_initialized_exception();
321 }
322
323 $ary_type = $ary_name = array();
324 $ident_set = false;
325 $sql_data = '';
326
327 // Grab all of the data from current table.
328 $sql = "SELECT *
329 FROM $table_name";
330 $result = $this->db->sql_query($sql);
331
332 $retrieved_data = odbc_num_rows($result);
333
334 if ($retrieved_data)
335 {
336 $sql = "SELECT 1 as has_identity
337 FROM INFORMATION_SCHEMA.COLUMNS
338 WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1";
339 $result2 = $this->db->sql_query($sql);
340 $row2 = $this->db->sql_fetchrow($result2);
341 if (!empty($row2['has_identity']))
342 {
343 $sql_data .= "\nSET IDENTITY_INSERT $table_name ON\nGO\n";
344 $ident_set = true;
345 }
346 $this->db->sql_freeresult($result2);
347 }
348
349 $i_num_fields = odbc_num_fields($result);
350
351 for ($i = 0; $i < $i_num_fields; $i++)
352 {
353 $ary_type[$i] = odbc_field_type($result, $i + 1);
354 $ary_name[$i] = odbc_field_name($result, $i + 1);
355 }
356
357 while ($row = $this->db->sql_fetchrow($result))
358 {
359 $schema_vals = $schema_fields = array();
360
361 // Build the SQL statement to recreate the data.
362 for ($i = 0; $i < $i_num_fields; $i++)
363 {
364 $str_val = $row[$ary_name[$i]];
365
366 if (preg_match('#char|text|bool|varbinary#i', $ary_type[$i]))
367 {
368 $str_quote = '';
369 $str_empty = "''";
370 $str_val = sanitize_data_mssql(str_replace("'", "''", $str_val));
371 }
372 else if (preg_match('#date|timestamp#i', $ary_type[$i]))
373 {
374 if (empty($str_val))
375 {
376 $str_quote = '';
377 }
378 else
379 {
380 $str_quote = "'";
381 }
382 }
383 else
384 {
385 $str_quote = '';
386 $str_empty = 'NULL';
387 }
388
389 if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val)))
390 {
391 $str_val = $str_empty;
392 }
393
394 $schema_vals[$i] = $str_quote . $str_val . $str_quote;
395 $schema_fields[$i] = $ary_name[$i];
396 }
397
398 // Take the ordered fields and their associated data and build it
399 // into a valid sql statement to recreate that field in the data.
400 $sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\nGO\n";
401
402 $this->flush($sql_data);
403
404 $sql_data = '';
405
406 }
407 $this->db->sql_freeresult($result);
408
409 if ($retrieved_data && $ident_set)
410 {
411 $sql_data .= "\nSET IDENTITY_INSERT $table_name OFF\nGO\n";
412 }
413 $this->flush($sql_data);
414 }
415 }
416