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;
}
