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 |
postgres_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 postgres_extractor extends base_extractor
019 {
020 /**
021 * {@inheritdoc}
022 */
023 public function write_start($table_prefix)
024 {
025 if (!$this->is_initialized)
026 {
027 throw new extractor_not_initialized_exception();
028 }
029
030 $sql_data = "--\n";
031 $sql_data .= "-- phpBB Backup Script\n";
032 $sql_data .= "-- Dump of tables for $table_prefix\n";
033 $sql_data .= "-- DATE : " . gmdate("d-m-Y H:i:s", $this->time) . " GMT\n";
034 $sql_data .= "--\n";
035 $sql_data .= "BEGIN TRANSACTION;\n";
036 $this->flush($sql_data);
037 }
038
039 /**
040 * {@inheritdoc}
041 */
042 public function write_table($table_name)
043 {
044 static $domains_created = array();
045
046 if (!$this->is_initialized)
047 {
048 throw new extractor_not_initialized_exception();
049 }
050
051 $sql = "SELECT a.domain_name, a.data_type, a.character_maximum_length, a.domain_default
052 FROM INFORMATION_SCHEMA.domains a, INFORMATION_SCHEMA.column_domain_usage b
053 WHERE a.domain_name = b.domain_name
054 AND b.table_name = '{$table_name}'";
055 $result = $this->db->sql_query($sql);
056 while ($row = $this->db->sql_fetchrow($result))
057 {
058 if (empty($domains_created[$row['domain_name']]))
059 {
060 $domains_created[$row['domain_name']] = true;
061 //$sql_data = "DROP DOMAIN {$row['domain_name']};\n";
062 $sql_data = "CREATE DOMAIN {$row['domain_name']} as {$row['data_type']}";
063 if (!empty($row['character_maximum_length']))
064 {
065 $sql_data .= '(' . $row['character_maximum_length'] . ')';
066 }
067 $sql_data .= ' NOT NULL';
068 if (!empty($row['domain_default']))
069 {
070 $sql_data .= ' DEFAULT ' . $row['domain_default'];
071 }
072 $this->flush($sql_data . ";\n");
073 }
074 }
075 $this->db->sql_freeresult($result);
076
077 $sql_data = '-- Table: ' . $table_name . "\n";
078 $sql_data .= "DROP TABLE $table_name;\n";
079 // PGSQL does not "tightly" bind sequences and tables, we must guess...
080 $sql = "SELECT relname
081 FROM pg_class
082 WHERE relkind = 'S'
083 AND relname = '{$table_name}_seq'";
084 $result = $this->db->sql_query($sql);
085 // We don't even care about storing the results. We already know the answer if we get rows back.
086 if ($this->db->sql_fetchrow($result))
087 {
088 $sql_data .= "DROP SEQUENCE {$table_name}_seq;\n";
089 $sql_data .= "CREATE SEQUENCE {$table_name}_seq;\n";
090 }
091 $this->db->sql_freeresult($result);
092
093 $field_query = "SELECT a.attnum, a.attname as field, t.typname as type, a.attlen as length, a.atttypmod as lengthvar, a.attnotnull as notnull
094 FROM pg_class c, pg_attribute a, pg_type t
095 WHERE c.relname = '" . $this->db->sql_escape($table_name) . "'
096 AND a.attnum > 0
097 AND a.attrelid = c.oid
098 AND a.atttypid = t.oid
099 ORDER BY a.attnum";
100 $result = $this->db->sql_query($field_query);
101
102 $sql_data .= "CREATE TABLE $table_name(\n";
103 $lines = array();
104 while ($row = $this->db->sql_fetchrow($result))
105 {
106 // Get the data from the table
107 $sql_get_default = "SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault
108 FROM pg_attrdef d, pg_class c
109 WHERE (c.relname = '" . $this->db->sql_escape($table_name) . "')
110 AND (c.oid = d.adrelid)
111 AND d.adnum = " . $row['attnum'];
112 $def_res = $this->db->sql_query($sql_get_default);
113 $def_row = $this->db->sql_fetchrow($def_res);
114 $this->db->sql_freeresult($def_res);
115
116 if (empty($def_row))
117 {
118 unset($row['rowdefault']);
119 }
120 else
121 {
122 $row['rowdefault'] = $def_row['rowdefault'];
123 }
124
125 if ($row['type'] == 'bpchar')
126 {
127 // Internally stored as bpchar, but isn't accepted in a CREATE TABLE statement.
128 $row['type'] = 'char';
129 }
130
131 $line = ' ' . $row['field'] . ' ' . $row['type'];
132
133 if (strpos($row['type'], 'char') !== false)
134 {
135 if ($row['lengthvar'] > 0)
136 {
137 $line .= '(' . ($row['lengthvar'] - 4) . ')';
138 }
139 }
140
141 if (strpos($row['type'], 'numeric') !== false)
142 {
143 $line .= '(';
144 $line .= sprintf("%s,%s", (($row['lengthvar'] >> 16) & 0xffff), (($row['lengthvar'] - 4) & 0xffff));
145 $line .= ')';
146 }
147
148 if (isset($row['rowdefault']))
149 {
150 $line .= ' DEFAULT ' . $row['rowdefault'];
151 }
152
153 if ($row['notnull'] == 't')
154 {
155 $line .= ' NOT NULL';
156 }
157
158 $lines[] = $line;
159 }
160 $this->db->sql_freeresult($result);
161
162 // Get the listing of primary keys.
163 $sql_pri_keys = "SELECT ic.relname as index_name, bc.relname as tab_name, ta.attname as column_name, i.indisunique as unique_key, i.indisprimary as primary_key
164 FROM pg_class bc, pg_class ic, pg_index i, pg_attribute ta, pg_attribute ia
165 WHERE (bc.oid = i.indrelid)
166 AND (ic.oid = i.indexrelid)
167 AND (ia.attrelid = i.indexrelid)
168 AND (ta.attrelid = bc.oid)
169 AND (bc.relname = '" . $this->db->sql_escape($table_name) . "')
170 AND (ta.attrelid = i.indrelid)
171 AND (ta.attnum = i.indkey[ia.attnum-1])
172 ORDER BY index_name, tab_name, column_name";
173
174 $result = $this->db->sql_query($sql_pri_keys);
175
176 $index_create = $index_rows = $primary_key = array();
177
178 // We do this in two steps. It makes placing the comma easier
179 while ($row = $this->db->sql_fetchrow($result))
180 {
181 if ($row['primary_key'] == 't')
182 {
183 $primary_key[] = $row['column_name'];
184 $primary_key_name = $row['index_name'];
185 }
186 else
187 {
188 // We have to store this all this info because it is possible to have a multi-column key...
189 // we can loop through it again and build the statement
190 $index_rows[$row['index_name']]['table'] = $table_name;
191 $index_rows[$row['index_name']]['unique'] = ($row['unique_key'] == 't') ? true : false;
192 $index_rows[$row['index_name']]['column_names'][] = $row['column_name'];
193 }
194 }
195 $this->db->sql_freeresult($result);
196
197 if (!empty($index_rows))
198 {
199 foreach ($index_rows as $idx_name => $props)
200 {
201 $index_create[] = 'CREATE ' . ($props['unique'] ? 'UNIQUE ' : '') . "INDEX $idx_name ON $table_name (" . implode(', ', $props['column_names']) . ");";
202 }
203 }
204
205 if (!empty($primary_key))
206 {
207 $lines[] = " CONSTRAINT $primary_key_name PRIMARY KEY (" . implode(', ', $primary_key) . ")";
208 }
209
210 // Generate constraint clauses for CHECK constraints
211 $sql_checks = "SELECT conname as index_name, consrc
212 FROM pg_constraint, pg_class bc
213 WHERE conrelid = bc.oid
214 AND bc.relname = '" . $this->db->sql_escape($table_name) . "'
215 AND NOT EXISTS (
216 SELECT *
217 FROM pg_constraint as c, pg_inherits as i
218 WHERE i.inhrelid = pg_constraint.conrelid
219 AND c.conname = pg_constraint.conname
220 AND c.consrc = pg_constraint.consrc
221 AND c.conrelid = i.inhparent
222 )";
223 $result = $this->db->sql_query($sql_checks);
224
225 // Add the constraints to the sql file.
226 while ($row = $this->db->sql_fetchrow($result))
227 {
228 if (!is_null($row['consrc']))
229 {
230 $lines[] = ' CONSTRAINT ' . $row['index_name'] . ' CHECK ' . $row['consrc'];
231 }
232 }
233 $this->db->sql_freeresult($result);
234
235 $sql_data .= implode(", \n", $lines);
236 $sql_data .= "\n);\n";
237
238 if (!empty($index_create))
239 {
240 $sql_data .= implode("\n", $index_create) . "\n\n";
241 }
242 $this->flush($sql_data);
243 }
244
245 /**
246 * {@inheritdoc}
247 */
248 public function write_data($table_name)
249 {
250 if (!$this->is_initialized)
251 {
252 throw new extractor_not_initialized_exception();
253 }
254
255 // Grab all of the data from current table.
256 $sql = "SELECT *
257 FROM $table_name";
258 $result = $this->db->sql_query($sql);
259
260 $i_num_fields = pg_num_fields($result);
261 $seq = '';
262
263 for ($i = 0; $i < $i_num_fields; $i++)
264 {
265 $ary_type[] = pg_field_type($result, $i);
266 $ary_name[] = pg_field_name($result, $i);
267
268 $sql = "SELECT pg_get_expr(d.adbin, d.adrelid) as rowdefault
269 FROM pg_attrdef d, pg_class c
270 WHERE (c.relname = '{$table_name}')
271 AND (c.oid = d.adrelid)
272 AND d.adnum = " . strval($i + 1);
273 $result2 = $this->db->sql_query($sql);
274 if ($row = $this->db->sql_fetchrow($result2))
275 {
276 // Determine if we must reset the sequences
277 if (strpos($row['rowdefault'], "nextval('") === 0)
278 {
279 $seq .= "SELECT SETVAL('{$table_name}_seq',(select case when max({$ary_name[$i]})>0 then max({$ary_name[$i]})+1 else 1 end FROM {$table_name}));\n";
280 }
281 }
282 }
283
284 $this->flush("COPY $table_name (" . implode(', ', $ary_name) . ') FROM stdin;' . "\n");
285 while ($row = $this->db->sql_fetchrow($result))
286 {
287 $schema_vals = array();
288
289 // Build the SQL statement to recreate the data.
290 for ($i = 0; $i < $i_num_fields; $i++)
291 {
292 $str_val = $row[$ary_name[$i]];
293
294 if (preg_match('#char|text|bool|bytea#i', $ary_type[$i]))
295 {
296 $str_val = str_replace(array("\n", "\t", "\r", "\b", "\f", "\v"), array('\n', '\t', '\r', '\b', '\f', '\v'), addslashes($str_val));
297 $str_empty = '';
298 }
299 else
300 {
301 $str_empty = '\N';
302 }
303
304 if (empty($str_val) && $str_val !== '0')
305 {
306 $str_val = $str_empty;
307 }
308
309 $schema_vals[] = $str_val;
310 }
311
312 // Take the ordered fields and their associated data and build it
313 // into a valid sql statement to recreate that field in the data.
314 $this->flush(implode("\t", $schema_vals) . "\n");
315 }
316 $this->db->sql_freeresult($result);
317 $this->flush("\\.\n");
318
319 // Write out the sequence statements
320 $this->flush($seq);
321 }
322
323 /**
324 * Writes closing line(s) to database backup
325 *
326 * @return null
327 * @throws \phpbb\db\extractor\exception\extractor_not_initialized_exception when calling this function before init_extractor()
328 */
329 public function write_end()
330 {
331 if (!$this->is_initialized)
332 {
333 throw new extractor_not_initialized_exception();
334 }
335
336 $this->flush("COMMIT;\n");
337 parent::write_end();
338 }
339 }
340