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
|
<?php
/** Export one database (e.g. development) so that it can be synced with other database (e.g. production)
* @link https://www.adminer.org/plugins/#use
* @author Jakub Vrana, https://www.vrana.cz/
* @license https://www.apache.org/licenses/LICENSE-2.0 Apache License, Version 2.0
* @license https://www.gnu.org/licenses/gpl-2.0.html GNU General Public License, version 2 (one or other)
*/
class AdminerDumpAlter extends Adminer\Plugin {
function dumpFormat() {
if (Adminer\DRIVER == 'server') {
return array('sql_alter' => 'Alter');
}
}
private function dumpAlter() {
// drop old tables
$query = "SELECT TABLE_NAME, ENGINE, TABLE_COLLATION, TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE()";
echo "DELIMITER ;;
CREATE PROCEDURE adminer_alter (INOUT alter_command text) BEGIN
DECLARE _table_name, _engine, _table_collation varchar(64);
DECLARE _table_comment varchar(64);
DECLARE done bool DEFAULT 0;
DECLARE tables CURSOR FOR $query;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN tables;
REPEAT
FETCH tables INTO _table_name, _engine, _table_collation, _table_comment;
IF NOT done THEN
CASE _table_name";
foreach (Adminer\get_rows($query) as $row) {
$comment = Adminer\q($row["ENGINE"] == "InnoDB" ? preg_replace('~(?:(.+); )?InnoDB free: .*~', '\1', $row["TABLE_COMMENT"]) : $row["TABLE_COMMENT"]);
echo "
WHEN " . Adminer\q($row["TABLE_NAME"]) . " THEN
" . (isset($row["ENGINE"]) ? "IF _engine != '$row[ENGINE]' OR _table_collation != '$row[TABLE_COLLATION]' OR _table_comment != $comment THEN
ALTER TABLE " . Adminer\idf_escape($row["TABLE_NAME"]) . " ENGINE=$row[ENGINE] COLLATE=$row[TABLE_COLLATION] COMMENT=$comment;
END IF" : "BEGIN END") . ";";
}
echo "
ELSE
SET alter_command = CONCAT(alter_command, 'DROP TABLE `', REPLACE(_table_name, '`', '``'), '`;\\n');
END CASE;
END IF;
UNTIL done END REPEAT;
CLOSE tables;
END;;
DELIMITER ;
CALL adminer_alter(@adminer_alter);
DROP PROCEDURE adminer_alter;
SELECT @adminer_alter;
";
}
function dumpDatabase($db) {
static $first = true;
if ($_POST["format"] == "sql_alter") {
if ($first) {
$first = false;
echo "SET @adminer_alter = '';\n\n";
} else {
$this->dumpAlter();
}
return true;
}
}
function dumpTable($table, $style, $is_view = 0) {
if ($_POST["format"] == "sql_alter") {
$create = Adminer\create_sql($table, $_POST["auto_increment"], $style);
if ($is_view) {
echo substr_replace($create, " OR REPLACE", 6, 0) . ";\n\n";
} else {
echo substr_replace($create, " IF NOT EXISTS", 12, 0) . ";\n\n";
// create procedure which iterates over original columns and adds new and removes old
$query = "SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLLATION_NAME, COLUMN_TYPE, EXTRA, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = " . Adminer\q($table) . "
ORDER BY ORDINAL_POSITION";
echo "DELIMITER ;;
CREATE PROCEDURE adminer_alter (INOUT alter_command text) BEGIN
DECLARE _column_name, _collation_name, after varchar(64) DEFAULT '';
DECLARE _column_type, _column_default text;
DECLARE _is_nullable char(3);
DECLARE _extra varchar(30);
DECLARE _column_comment varchar(255);
DECLARE done, set_after bool DEFAULT 0;
DECLARE add_columns text DEFAULT '";
$fields = array();
$after = "";
foreach (Adminer\get_rows($query) as $row) {
$default = $row["COLUMN_DEFAULT"];
$row["default"] = ($default !== null ? Adminer\q($default) : "NULL");
$row["after"] = Adminer\q($after); //! rgt AFTER lft, lft AFTER id doesn't work
$row["alter"] = Adminer\escape_string(
Adminer\idf_escape($row["COLUMN_NAME"])
. " $row[COLUMN_TYPE]"
. ($row["COLLATION_NAME"] ? " COLLATE $row[COLLATION_NAME]" : "")
. ($default !== null ? " DEFAULT " . ($default == "CURRENT_TIMESTAMP" ? $default : $row["default"]) : "")
. ($row["IS_NULLABLE"] == "YES" ? "" : " NOT NULL")
. ($row["EXTRA"] ? " $row[EXTRA]" : "")
. ($row["COLUMN_COMMENT"] ? " COMMENT " . Adminer\q($row["COLUMN_COMMENT"]) : "")
. ($after ? " AFTER " . Adminer\idf_escape($after) : " FIRST")
);
echo ", ADD $row[alter]";
$fields[] = $row;
$after = $row["COLUMN_NAME"];
}
echo "';
DECLARE columns CURSOR FOR $query;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET @alter_table = '';
OPEN columns;
REPEAT
FETCH columns INTO _column_name, _column_default, _is_nullable, _collation_name, _column_type, _extra, _column_comment;
IF NOT done THEN
SET set_after = 1;
CASE _column_name";
foreach ($fields as $row) {
echo "
WHEN " . Adminer\q($row["COLUMN_NAME"]) . " THEN
SET add_columns = REPLACE(add_columns, ', ADD $row[alter]', IF(
_column_default <=> $row[default]
AND _is_nullable = '$row[IS_NULLABLE]'
AND _collation_name <=> " . (isset($row["COLLATION_NAME"]) ? "'$row[COLLATION_NAME]'" : "NULL") . "
AND _column_type = " . Adminer\q($row["COLUMN_TYPE"]) . "
AND _extra = '$row[EXTRA]'
AND _column_comment = " . Adminer\q($row["COLUMN_COMMENT"]) . "
AND after = $row[after]
, '', ', MODIFY $row[alter]'));"; //! don't replace in comment
}
echo "
ELSE
SET @alter_table = CONCAT(@alter_table, ', DROP ', '`', REPLACE(_column_name, '`', '``'), '`');
SET set_after = 0;
END CASE;
IF set_after THEN
SET after = _column_name;
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE columns;
IF @alter_table != '' OR add_columns != '' THEN
SET alter_command = CONCAT(alter_command, 'ALTER TABLE " . Adminer\table($table) . "', SUBSTR(CONCAT(add_columns, @alter_table), 2), ';\\n');
END IF;
END;;
DELIMITER ;
CALL adminer_alter(@adminer_alter);
DROP PROCEDURE adminer_alter;
";
//! indexes
}
return true;
}
}
function dumpData() {
if ($_POST["format"] == "sql_alter") {
return true;
}
}
function dumpFooter() {
if ($_POST["format"] == "sql_alter") {
$this->dumpAlter();
}
}
protected $translations = array(
'cs' => array('' => 'Exportuje jednu databázi (např. vývojovou) tak, že může být synchronizována s jinou databází (např. produkční)'),
'de' => array('' => 'Exportiert eine Datenbank (z. B. Entwicklung), damit sie mit einer anderen Datenbank (z. B. Produktion) synchronisiert werden kann'),
'pl' => array('' => 'Eksportuje jedną bazę danych (np. programistyczną), aby można ją było zsynchronizować z inną bazą danych (np. produkcyjną)'),
'ro' => array('' => 'Exportați o bază de date (de exemplu, development) astfel încât să poată fi sincronizată cu o altă bază de date (de exemplu, de producție)'),
'ja' => array('' => 'データベース (開発用など) をエクスポートし、別のデータベース (本番用など) と同期'),
);
}
|
