Mercurial > hg > mgallery
changeset 343:d6422e856ecd
Remove the backend/SQL stuff for now.
| author | Matti Hamalainen <ccr@tnsp.org> |
|---|---|
| date | Sun, 27 Aug 2023 04:16:05 +0300 |
| parents | 4dd202fffdf7 |
| children | 0f14a20f48ca |
| files | mgallery.inc.php mgallery.php mgtool.php |
| diffstat | 3 files changed, 2 insertions(+), 616 deletions(-) [+] |
line wrap: on
line diff
--- a/mgallery.inc.php Sun Aug 27 01:45:44 2023 +0300 +++ b/mgallery.inc.php Sun Aug 27 04:16:05 2023 +0300 @@ -101,12 +101,6 @@ "med_width" => [MG_INT, 1200], "med_height" => [MG_INT, 900], "med_quality" => [MG_INT, 90], - - "backend" => [MG_STR_LC, "php"], - "sql_db" => [MG_STR, FALSE], - "sql_username" => [MG_STR, ""], - "sql_password" => [MG_STR, ""], - "sql_options" => [MG_STR_ARRAY, []], ]; @@ -380,313 +374,4 @@ } -function mgLogSQLError($dbh, $sql) -{ - return mgError("SQL error '".implode("; ", $dbh->errorInfo())."' in statement: \"".$sql."\""); -} - - -function mgDBGetSQLParam($dbh, $type, $value) -{ - switch ($type) - { - case "d": return intval($value); - case "s": return $dbh->quote($value); - case "b": return intval($value) ? 1 : 0; - case "D": - if ($value instanceOf DateTime) - { - switch ($dbh->getAttribute(PDO::ATTR_DRIVER_NAME)) - { - case "pgsql" : $fmt = "Y-m-d H:i:sP"; break; - case "sqlite" : $fmt = DATE_RFC3339; break; - case "mysql" : $fmt = "Y-m-d H:i:s"; break; - default : $fmt = DATE_RFC3339; - } - return $dbh->quote($value->format($fmt)); - } - else - return intval($value); - } -} - - -function mgSQLToDateTime($dbh, $stamp) -{ - switch ($dbh->getAttribute(PDO::ATTR_DRIVER_NAME)) - { - case "pgsql": - // PostgreSQL 'timestamptz' format - $tmp = DateTime::createFromFormat("Y-m-d H:i:sP", $stamp); - break; - - case "sqlite": - // SQLite can use RFC3339 format - $tmp = DateTime::createFromFormat(DATE_RFC3339, $stamp); - break; - - case "mysql": - // MySQL uses UTC internally, no way to specify TZ - $tmp = DateTime::createFromFormat("Y-m-d H:i:s", $stamp); - break; - - default: - $tmp = NULL; - } - - // echo "<p>".$stamp." :: ".var_export(($tmp instanceOf DateTime) ? $tmp : NULL, TRUE)."</p>"; - return ($tmp instanceOf DateTime) ? $tmp : NULL; -} - - -function mgConnectSQLDB() -{ - global $db; - try { - $db = new PDO(mgGetSetting("sql_db"), - mgGetSetting("sql_username", NULL), - mgGetSetting("sql_password", NULL), - mgGetSetting("sql_options", array())); - } - catch (PDOException $e) { - mgError("Could not connect to SQL database: ".$e->getMessage()."."); - return FALSE; - } - return ($db !== false); -} - - -function mgDBPrepareSQLUpdate($dbh, $table, $cond, $pairs, $values = NULL) -{ - $sql = []; - foreach ($pairs as $name => $attr) - { - $sql[] = $name."=".mgDBGetSQLParam($dbh, - $attr, $values !== NULL ? $values[$name] : $name); - } - return - "UPDATE ".$table." SET ".implode(",", $sql). - ($cond != "" ? " ".$cond : ""); -} - - -function mgDBPrepareSQL_V($dbh, $fmt, $argv) -{ - $len = strlen($fmt); - $sql = ""; - $argn = 0; - $argc = count($argv); - - for ($pos = 0; $pos < $len; $pos++) - { - if ($fmt[$pos] == "%") - { - if ($argn < $argc) - $sql .= mgDBGetSQLParam($dbh, $fmt[++$pos], $argv[$argn++]); - else - { - mgError("Invalid SQL statement format string '".$fmt. - "', not enough parameters specified (".$argn." of ".$argc.")"); - return FALSE; - } - } - else - $sql .= $fmt[$pos]; - } - - return $sql; -} - - -function mgDBPrepareSQL($dbh) -{ - $argv = func_get_args(); - return mgDBPrepareSQL_V($dbh, $argv[1], array_splice($argv, 2)); -} - - -function mgPrepareSQL() -{ - global $db; - $argv = func_get_args(); - return mgDBPrepareSQL_V($db, $argv[0], array_splice($argv, 1)); -} - - -function mgDBExecSQLInsert($dbh, $sql) -{ - switch ($dbh->getAttribute(PDO::ATTR_DRIVER_NAME)) - { - case "pgsql": - if (($res = mgDBFetchSQLColumn($dbh, $sql." RETURNING id")) !== false) - return $res; - else - return FALSE; - - default: - if (mgDBExecSQL($dbh, $sql) !== false) - return $dbh->lastInsertId(); - else - return FALSE; - } -} - - -function mgDBExecSQL($dbh, $sql) -{ - if (($res = $dbh->query($sql)) !== FALSE) - return $res; - else - { - mgLogSQLError($dbh, $sql); - return FALSE; - } -} - - -function mgDBFetchSQL($dbh, $sql) -{ - if (($res = $dbh->query($sql)) !== FALSE) - return $res->fetch(); - else - { - mgLogSQLError($dbh, $sql); - return FALSE; - } -} - - -function mgDBFetchSQLColumn($dbh, $sql, $column = 0) -{ - if (($res = $dbh->query($sql)) !== FALSE) - return $res->fetchColumn($column); - else - { - mgLogSQLError($dbh, $sql); - return FALSE; - } -} - - -function mgPrepareSQLUpdate($table, $cond, $pairs) -{ - global $db; - return mgDBPrepareSQLUpdate($db, $table, $cond, $pairs); -} - - -function mgExecSQLInsert($sql) -{ - global $db; - return mgDBExecSQLInsert($db, $sql); -} - - -function mgExecSQL($sql) -{ - global $db; - return mgDBExecSQL($db, $sql); -} - - -function mgFetchSQL($sql) -{ - global $db; - return mgDBFetchSQL($db, $sql); -} - - -function mgFetchSQLColumn($sql, $column = 0) -{ - global $db; - return mgDBFetchSQLColumn($db, $sql, $column); -} - - -function mgDBBeginTransaction($dbh = FALSE) -{ - global $db; - return mgDBExecSQL(($dbh !== FALSE) ? $dbh : $db, "BEGIN TRANSACTION"); -} - - -function mgDBCommitTransaction($dbh = FALSE) -{ - global $db; - return mgDBExecSQL(($dbh !== FALSE) ? $dbh : $db, "COMMIT"); -} - - -function mgDBGetTableSchema($dbh, $schema) -{ - $res = []; - $driver = $dbh->getAttribute(PDO::ATTR_DRIVER_NAME); - - // Go through the table schema, definition by definition - foreach ($schema as $scol) - { - $tmp = []; - - // And each element of the one definition - // (like 'foo INTEGER AUTOINCREMENT') - foreach ($scol as $elem) - switch ($driver) - { - case "pgsql": - switch ($elem) - { - case "AUTOINCREMENT": - // For Postgres, use SERIAL for autoincrement and - // "cleverly" replace the 2nd element with SERIAL - // assuming that it is INTEGER or such. - $tmp[1] = "SERIAL"; - break; - - case "DATETIME": - $tmp[] = "TIMESTAMPTZ"; - break; - - default: - $tmp[] = $elem; - break; - } - break; - - case "mysql": - switch ($elem) - { - case "AUTOINCREMENT": - $tmp[] = "AUTO_INCREMENT"; - break; - - case "DATETIME": - $tmp[] = "TIMESTAMP"; - break; - - default: - $tmp[] = $elem; - break; - } - break; - - case "sqlite": - $tmp[] = $elem; - break; - - default: - die("Don't know how to handle PDO driver '".$driver."' yet.\n"); - } - - $res[] = implode(" ", $tmp); - } - - return implode(", ", $res); -} - - -function mgDBCreateOneTable($dbh, $name, $schema) -{ - return (mgDBExecSQL($dbh, "CREATE TABLE IF NOT EXISTS ".$name." (".$schema.")") !== FALSE) ? TRUE : FALSE; -} - ?> \ No newline at end of file
--- a/mgallery.php Sun Aug 27 01:45:44 2023 +0300 +++ b/mgallery.php Sun Aug 27 04:16:05 2023 +0300 @@ -544,132 +544,6 @@ fclose($fp); } -// Check for chosen backend mode -if (($galBackend = mgGetSetting("backend")) === null) - die("MGallery backend mode not set.\n"); - -switch ($galBackend = strtolower($galBackend)) -{ - case "sql": - // If SQL backend, and gallery is virtual, attempt a select - if (isset($galData["virtual"])) - { - // Connect to database - if (mgConnectSQLDB() === FALSE) - die("Could not open database connection.\n"); - - $asql = ["SELECT * FROM images ". - "LEFT JOIN paths ON images.path_id=paths.id ". - "WHERE"]; - - $ccmds = preg_split("/\s*([,|])\s*/", - strtolower($galData["virtual"]), -1, - PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE); - - foreach ($ccmds as $ccmd) - switch ($ccmd) - { - case "keyword": - if (!isset($galData["v_keyword"])) - die("Invalid definition.\n"); - - $asql[] = mgPrepareSQL( - "images.id IN (SELECT image_id FROM images_meta ". - "LEFT JOIN metadata ON images_meta.meta_id=metadata.id ". - "WHERE metadata.field='keywords' AND data=%s)", - $galData["v_keyword"]); - break; - - case "month_range": - if (!isset($galData["v_month_begin"]) || - !isset($galData["v_month_end"])) - die("Invalid definition.\n"); - - $asql[] = mgPrepareSQL( - "date_part('month', datetime) BETWEEN %d AND %d", - $galData["v_month_begin"], - $galData["v_month_end"]); - break; - - case "date_range": - if (!isset($galData["v_date_begin"]) || - !isset($galData["v_date_end"])) - die("Invalid definition.\n"); - - $asql[] = mgPrepareSQL( - "datetime BETWEEN %s AND %s", - $galData["v_date_begin"], - $galData["v_date_end"]); - break; - - case "year": - if (!isset($galData["v_year"])) - die("Invalid definition.\n"); - - // Laatuklunssi - $v_year = strtolower($galData["v_year"]); - if (substr($v_year, 0, 7) == "parent:") - { - if (isset($galData["parent"])) - { - $v_field = substr($v_year, 7); - if (isset($galData["parent"]["caption"])) - $galData["v_year"] = $galData["parent"][$v_field]; - else - die("Foobar\n"); - } - else - die("Fazboo\n"); - } - - $asql[] = mgPrepareSQL( - "date_part('year', datetime)=%d", - $galData["v_year"]); - break; - - case "|": - $asql[] = "OR"; - break; - - case ",": - $asql[] = "AND"; - break; - - default: - die("Invalid virtual gallery definition.\n"); - } - - $asql[] = "ORDER BY datetime DESC"; - $sql = implode(" ", $asql); - - // Query images for this gallery - $galData["header"] .= "<p>".$sql."</p>"; - - if (($res = mgExecSQL($sql)) === FALSE) - die("Could not execute virtual gallery.\n"); - - // Push the entries into our data structures - foreach ($res as $entry) - if (!in_array($entry["filename"], $galImagesIndex)) - { - $entry["datetime"] = mgSQLToDateTime($db, $entry["datetime"]); - $entry["type"] = 0; - - $galEntries[$entry["filename"]] = $entry; - $galImagesIndex[] = $entry["filename"]; - } - } - //print_r($galEntries); - break; - - case "php": - break; - - default: - die("Invalid MGallery backend mode '".$galBackend."'.\n"); -} - - // If no data available, show an error page if (!isset($galData) || !isset($galEntries) || !isset($galAlbumsIndex) || !isset($galImagesIndex))
--- a/mgtool.php Sun Aug 27 01:45:44 2023 +0300 +++ b/mgtool.php Sun Aug 27 04:16:05 2023 +0300 @@ -57,36 +57,6 @@ // -// SQL table schemas -// -$galSQLTables = -[ - "images" => [ - ["id" , "INTEGER", "PRIMARY KEY", "AUTOINCREMENT"], - ["path_id" , "INTEGER"], - ["filename" , "VARCHAR(64)"], - ], - - "paths" => [ - ["id" , "INTEGER", "PRIMARY KEY", "AUTOINCREMENT"], - ["path" , "VARCHAR(128)"], - ], - - "metadata" => [ - ["id" , "INTEGER", "PRIMARY KEY", "AUTOINCREMENT"], - ["field" , "VARCHAR(64)"], - ["data" , "VARCHAR(64)", "UNIQUE"], - ["status" , "INTEGER"], - ], - - "images_meta" => [ - ["image_id" , "INTEGER"], - ["meta_id" , "INTEGER"], - ], -]; - - -// // Convert and scale image file function, for generating // the intermediate size images and thumbnails. Uses the // PHP ImageMagick or GraphicsMagick bindings. @@ -831,7 +801,7 @@ function mgWriteGalleryCache($cacheFilename, &$gallery, &$entries, &$parentEntry) { - global $galBackend, $db, $galExifConversions, $flagSQL; + global $galBackend, $db, $galExifConversions; // Store gallery cache for this directory $images = []; @@ -902,85 +872,6 @@ } } - if ($galBackend == "sql" && $flagSQL) - { - mgDBBeginTransaction(); - - if (($path_id = mgFetchSQLColumn(mgPrepareSQL("SELECT id FROM paths WHERE path=%s", $gallery["path"]))) === FALSE && - ($path_id = mgExecSQLInsert(mgPrepareSQL("INSERT INTO paths (path) VALUES (%s)", $gallery["path"]))) === FALSE) - mgFatal("SQL path select failed.\n"); - - foreach ($output as $entry => &$edata) - if ($edata["type"] == 0) - { - $ekeys = ["path_id", "filename"]; - $evals = [$path_id, mgDBGetSQLParam($db, "s", $entry)]; - $esets = []; - - foreach ($galExifConversions as $econv) - if ($econv[GEC_IS_UNIQUE]) - { - $ekey = $econv[GEC_NAME]; - - // Skip unset and special case(s) - if (!isset($esets[$ekey]) && isset($edata[$ekey])) - { - $esets[$ekey] = TRUE; - - switch ($econv[GEC_TYPE]) - { - case MG_DATE: $etype = "D"; break; - case MG_INT: $etype = "d"; break; - default: $etype = "s"; break; - } - - $ekeys[] = $ekey; - $evals[] = mgDBGetSQLParam($db, $etype, $edata[$ekey]); - } - } - - $sql = "INSERT INTO images (".implode(",", $ekeys).") VALUES (".implode(",", $evals).")"; - if (($image_id = mgExecSQLInsert($sql)) === FALSE) - mgFatal("Failed.\n"); - - // Special handling for non-unique fields - foreach ($galExifConversions as $econv) - { - $ekey = $econv[GEC_NAME]; - if (!$econv[GEC_IS_UNIQUE] && - array_key_exists($econv[GEC_NAME], $edata)) - { - if (is_array($edata[$ekey])) - { - foreach ($edata[$ekey] as $kw) - { - $sql = mgPrepareSQL("INSERT INTO metadata (field,data,status) VALUES (%s,%s,0) ON CONFLICT(data) DO UPDATE SET status=1", $ekey, $kw); - if (($id = mgExecSQLInsert($sql)) === FALSE) - mgFatal("Failure!\n"); - - $sql = mgPrepareSQL("INSERT INTO images_meta (image_id,meta_id) VALUES (%d,%d)", $image_id, $id); - if (($id = mgExecSQL($sql)) === FALSE) - mgFatal("Failure!\n"); - } - } - else - { - $sql = mgPrepareSQL("INSERT INTO metadata (field,data,status) VALUES (%s,%s,0) ON CONFLICT(data) DO UPDATE SET status=1", $ekey, $edata[$ekey]); - if (($id = mgExecSQLInsert($sql)) === FALSE) - mgFatal("Failure!\n"); - - $sql = mgPrepareSQL("INSERT INTO images_meta (image_id,meta_id) VALUES (%d,%d)", $image_id, $id); - if (($id = mgExecSQL($sql)) === FALSE) - mgFatal("Failure!\n"); - } - - } - } - } - - mgDBCommitTransaction(); - } - $str = "<?php\n". "\$galData = ".var_export($gallery, TRUE).";\n". @@ -1295,7 +1186,7 @@ function mgProcessGalleries($cmd, $path) { - global $galTNPath, $galMedPath, $galSQLTables, $flagSQL; + global $galTNPath, $galMedPath; // Check validity of some settings $galPath = mgGetSetting("base_path"); @@ -1349,15 +1240,6 @@ // Start working echo "Gallery path: '".$galPath."', starting at '".$path."' ...\n"; - if (($cmd == GCMD_CLEAN || $cmd == GCMD_RESCAN) && $flagSQL) - { - foreach ($galSQLTables as $tname => $tdata) - { - if (mgExecSQL("DELETE FROM ".$tname) === FALSE) - mgFatal("Failed to purge table '".$tname."'.\n"); - } - } - mgHandleDirectory($cmd, $galPath, $path, $parentData, $parentEntry, $writeMode, $startAt); } @@ -1534,53 +1416,6 @@ if (($pageTimeZone = mgGetSetting("timezone")) !== NULL) date_default_timezone_set($pageTimeZone); -// Check backend -if (($galBackend = mgGetSetting("backend")) === null) - die("MGallery backend mode not set.\n"); - -switch ($galBackend = strtolower($galBackend)) -{ - case "sql": - // Connect to database - if (mgConnectSQLDB() === FALSE) - die("Could not open database connection.\n"); - - // Create SQL schema elements for metadata fields - $tmpSQL = []; - foreach ($galExifConversions as $econv) - if ($econv[GEC_IS_UNIQUE]) - { - switch ($econv[GEC_TYPE]) - { - case MG_STR: $etype = "VARCHAR(256)"; break; - case MG_INT: $etype = "INTEGER"; break; - case MG_DVA: $etype = "VARCHAR(32)"; break; - case MG_DATE: $etype = "DATETIME"; break; - } - - $tmpSQL[$econv[GEC_NAME]] = $etype; - } - - // Merge to base "images" table schema - foreach ($tmpSQL as $ekey => $etype) - $galSQLTables["images"][] = [$ekey, $etype]; - - // Check tables - foreach ($galSQLTables as $name => $schemaData) - { - $schema = mgDBGetTableSchema($db, $schemaData); - if (!mgDBCreateOneTable($db, $name, $schema)) - die("Failed to create SQL table '".$name."'.\n"); - } - break; - - case "php": - break; - - default: - die("Invalid MGallery backend mode '".$galBackend."'.\n"); -} - // Check for commandline arguments $cmd = mgCArgLC(1); @@ -1629,14 +1464,6 @@ case "update": case "up": case "upd": case "upda": $farg = mgCArg(2); - if ($farg == "--sql") - { - $farg = FALSE; - $flagSQL = TRUE; - } - else - $flagSQL = mgCArg(3) == "--sql"; - mgProcessGalleries(GCMD_UPDATE, $farg); break;