function dumpTable($table, $style, $is_view = false) { if ($_POST["format"] == "sql_alter") { $create = create_sql($table, $_POST["auto_increment"]); 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 = " . q($table) . " ORDER BY ORDINAL_POSITION"; echo "DELIMITER ;;\nCREATE PROCEDURE adminer_alter (INOUT alter_command text) BEGIN\n\tDECLARE _column_name, _collation_name, after varchar(64) DEFAULT '';\n\tDECLARE _column_type, _column_default text;\n\tDECLARE _is_nullable char(3);\n\tDECLARE _extra varchar(30);\n\tDECLARE _column_comment varchar(255);\n\tDECLARE done, set_after bool DEFAULT 0;\n\tDECLARE add_columns text DEFAULT '"; $fields = array(); $after = ""; foreach (get_rows($query) as $row) { $default = $row["COLUMN_DEFAULT"]; $row["default"] = $default !== null ? q($default) : "NULL"; $row["after"] = q($after); //! rgt AFTER lft, lft AFTER id doesn't work $row["alter"] = escape_string(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 " . q($row["COLUMN_COMMENT"]) : "") . ($after ? " AFTER " . idf_escape($after) : " FIRST")); echo ", ADD {$row['alter']}"; $fields[] = $row; $after = $row["COLUMN_NAME"]; } echo "';\n\tDECLARE columns CURSOR FOR {$query};\n\tDECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;\n\tSET @alter_table = '';\n\tOPEN columns;\n\tREPEAT\n\t\tFETCH columns INTO _column_name, _column_default, _is_nullable, _collation_name, _column_type, _extra, _column_comment;\n\t\tIF NOT done THEN\n\t\t\tSET set_after = 1;\n\t\t\tCASE _column_name"; foreach ($fields as $row) { echo "\n\t\t\t\tWHEN " . q($row["COLUMN_NAME"]) . " THEN\n\t\t\t\t\tSET add_columns = REPLACE(add_columns, ', ADD {$row['alter']}', IF(\n\t\t\t\t\t\t_column_default <=> {$row['default']} AND _is_nullable = '{$row['IS_NULLABLE']}' AND _collation_name <=> " . (isset($row["COLLATION_NAME"]) ? "'{$row['COLLATION_NAME']}'" : "NULL") . " AND _column_type = " . q($row["COLUMN_TYPE"]) . " AND _extra = '{$row['EXTRA']}' AND _column_comment = " . q($row["COLUMN_COMMENT"]) . " AND after = {$row['after']}\n\t\t\t\t\t, '', ', MODIFY {$row['alter']}'));"; //! don't replace in comment } echo "\n\t\t\t\tELSE\n\t\t\t\t\tSET @alter_table = CONCAT(@alter_table, ', DROP ', '`', REPLACE(_column_name, '`', '``'), '`');\n\t\t\t\t\tSET set_after = 0;\n\t\t\tEND CASE;\n\t\t\tIF set_after THEN\n\t\t\t\tSET after = _column_name;\n\t\t\tEND IF;\n\t\tEND IF;\n\tUNTIL done END REPEAT;\n\tCLOSE columns;\n\tIF @alter_table != '' OR add_columns != '' THEN\n\t\tSET alter_command = CONCAT(alter_command, 'ALTER TABLE " . adminer_table($table) . "', SUBSTR(CONCAT(add_columns, @alter_table), 2), ';\\n');\n\tEND IF;\nEND;;\nDELIMITER ;\nCALL adminer_alter(@adminer_alter);\nDROP PROCEDURE adminer_alter;\n\n"; //! indexes } return true; } }
/** Export table structure * @param string * @param string * @param int 0 table, 1 view, 2 temporary view table * @return null prints data */ function dumpTable($table, $style, $is_view = 0) { if ($_POST["format"] != "sql") { echo ""; // UTF-8 byte order mark if ($style) { dump_csv(array_keys(fields($table))); } } else { if ($is_view == 2) { $fields = array(); foreach (fields($table) as $name => $field) { $fields[] = idf_escape($name) . " {$field['full_type']}"; } $create = "CREATE TABLE " . table($table) . " (" . implode(", ", $fields) . ")"; } else { $create = create_sql($table, $_POST["auto_increment"]); } set_utf8mb4($create); if ($style && $create) { if ($style == "DROP+CREATE" || $is_view == 1) { echo "DROP " . ($is_view == 2 ? "VIEW" : "TABLE") . " IF EXISTS " . table($table) . ";\n"; } if ($is_view == 1) { $create = remove_definer($create); } echo "{$create};\n\n"; } } }
function create_sql($mysqli, $content, $parent = null) { global $sqlfp, $shfp, $mapfp; $order = 1; foreach ($content as $val) { if ($val['type'] == "album") { print $val['title'] . " (" . $val['id'] . ")\n"; $title = $mysqli->real_escape_string($val['title']); $slug = $mysqli->real_escape_string(slug($val['name'])); $summary = $mysqli->real_escape_string($val['description']); $internal_id = md5($slug); $created_at = $val['upload_date']; $level = $val['level'] + 1; $last = sizeof($val['items']) - 1; if ($val['items'][$last]['type'] == "photo") { $album_type = 0; } else { $album_type = 2; } $query = "INSERT into " . KOKEN_PREFIX . "albums (id,title,slug,summary,description,level,album_type,published_on,created_on,modified_on,internal_id,total_count) values (" . $val['id'] . ",'" . $title . "','" . $slug . "','" . $summary . "','" . $summary . "', " . $level . ", " . $album_type . "," . $created_at . "," . $created_at . ", " . time() . ", '" . $internal_id . "'," . sizeof($val['items']) . ")"; fwrite($sqlfp, utf8_decode($query) . ";\n"); $folder = $album_type == 2 ? "sets" : "albums"; fwrite($mapfp, urlencode(utf8_decode($val['source'])) . " " . "http://" . KOKEN_URL . "/" . $folder . "/" . slug($val['name']) . "\n"); create_sql($mysqli, $val['items'], $val); } if ($val['type'] == "photo") { $pathinfo = pathinfo($val['image_file']); $filename = $mysqli->real_escape_string($pathinfo['basename']); $caption = $mysqli->real_escape_string($val['caption']); $filesize = filesize($val['image_file']); $slug = $mysqli->real_escape_string(slug($parent['name'] . " " . $pathinfo['filename'])); $internal_id = md5($slug); print $filename . "\n"; $query = "INSERT into " . KOKEN_PREFIX . "content "; $query .= " (id,slug,filename,caption,visibility,filesize,width,height,aspect_ratio,published_on,uploaded_on,captured_on,modified_on,internal_id,exif,exif_make,exif_model,exif_iso,exif_camera_serial,exif_camera_lens)"; $query .= " values"; $query .= " (" . $val['id'] . ",'" . $slug . "','" . $filename . "','" . $caption . "'," . $val['visibility'] . "," . $filesize . ",'" . $val['width'] . "','" . $val['height'] . "','" . $val['aspect_ratio'] . "'," . $val['upload_date'] . "," . $val['upload_date'] . "," . $val['capture_date'] . "," . $val['upload_date'] . ",'" . $internal_id . "'"; $query .= $val['exif_string'] === null ? ",NULL" : ",'" . $mysqli->real_escape_string($val['exif_string']) . "'"; $query .= $val['exif_make'] === null ? ",NULL" : ",'" . $mysqli->real_escape_string($val['exif_make']) . "'"; $query .= $val['exif_model'] === null ? ",NULL" : ",'" . $mysqli->real_escape_string($val['exif_model']) . "'"; $query .= $val['exif_iso'] === null ? ",NULL" : ",'" . $mysqli->real_escape_string($val['exif_iso']) . "'"; $query .= $val['exif_camera_serial'] === null ? ",NULL" : ",'" . $mysqli->real_escape_string($val['exif_camera_serial']) . "'"; $query .= $val['exif_camera_lens'] === null ? ",NULL" : ",'" . $mysqli->real_escape_string($val['exif_camera_lens']) . "'"; $query .= ")"; fwrite($sqlfp, utf8_decode($query) . ";\n"); $newfile = KOKEN_PATH . "/storage/originals/" . substr($internal_id, 0, 2) . "/" . substr($internal_id, 2, 2) . "/" . $pathinfo['basename']; fwrite($shfp, "mkdir -p " . dirname($newfile) . "\n"); fwrite($shfp, "cp " . $val['image_file'] . " " . $newfile . "\n"); $query = "INSERT into " . KOKEN_PREFIX . "join_albums_content (album_id,content_id,`order`) values (" . $parent['id'] . "," . $val['id'] . "," . $order . ")"; fwrite($sqlfp, $query . ";\n"); if ($val['highlight'] === true) { $query = "INSERT into " . KOKEN_PREFIX . "join_albums_covers (album_id,cover_id) values (" . $parent['id'] . "," . $val['id'] . ")"; fwrite($sqlfp, $query . ";\n"); } $order++; } } return true; }
function queryDBresult($sql, $params = array(), $sanitize = true) { global $db, $msg; $sql = create_sql($sql, $params, $sanitize); if (defined('MYSQLI_ENABLED')) { $result = $db->query($sql) or error_log(print_r(mysqli_error(), true), 0) and $msg->addError($displayErrorMessage); } else { $result = mysql_query($sql, $db) or error_log(print_r(mysql_error(), true), 0) and $msg->addError($displayErrorMessage); } return $result; }