# HG changeset patch # User Matti Hamalainen # Date 1564289616 -10800 # Node ID 19fbf800b1f7914d95fba5b04aa96aa05b3e3599 # Parent 13cff35dfbec0dfe8160e853285807d3ff5e3de6 Work on very early form of virtual gallery support. diff -r 13cff35dfbec -r 19fbf800b1f7 mgallery.inc.php --- a/mgallery.inc.php Sun Jul 28 07:50:54 2019 +0300 +++ b/mgallery.inc.php Sun Jul 28 07:53:36 2019 +0300 @@ -97,6 +97,11 @@ "med_height" => [MG_INT, 640], "med_quality" => [MG_INT, 90], + "backend" => [MG_STR, "php"], + "sql_db" => [MG_STR, NULL], + "sql_username" => [MG_STR, ""], + "sql_password" => [MG_STR, ""], + "sql_options" => [MG_STR, []], ]; @@ -341,4 +346,314 @@ } } + +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 "

".$stamp." :: ".var_export(($tmp instanceOf DateTime) ? $tmp : NULL, TRUE)."

"; + 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 diff -r 13cff35dfbec -r 19fbf800b1f7 mgallery.php --- a/mgallery.php Sun Jul 28 07:50:54 2019 +0300 +++ b/mgallery.php Sun Jul 28 07:53:36 2019 +0300 @@ -507,6 +507,131 @@ 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"] .= "

".$sql."

"; + + 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) || diff -r 13cff35dfbec -r 19fbf800b1f7 mgtool.php --- a/mgtool.php Sun Jul 28 07:50:54 2019 +0300 +++ b/mgtool.php Sun Jul 28 07:53:36 2019 +0300 @@ -55,6 +55,34 @@ ]; +// +// 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"], + ], +]; // @@ -647,6 +675,8 @@ function mgWriteGalleryCache($cacheFilename, &$gallery, &$entries, &$parentEntry) { + global $galBackend, $db, $galExifConversions, $flagSQL; + // Store gallery cache for this directory $images = []; $albums = []; @@ -697,6 +727,93 @@ } } + switch ($galBackend) + { + case "sql": + if (!$flagSQL) + break; + + 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("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(); + break; + + } + $str = " $tdata) + { + if (mgExecSQL("DELETE FROM ".$tname) === FALSE) + mgFatal("Failed to purge table '".$tname."'.\n"); + } + } + mgHandleDirectory($cmd, $galPath, $path, $parentData, $parentEntry, $writeMode, $startAt); } @@ -1126,6 +1253,53 @@ 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); @@ -1149,7 +1323,16 @@ break; case "update": case "up": case "upd": case "upda": - mgProcessGalleries(GCMD_UPDATE, mgCArg(2)); + $farg = mgCArg(2); + if ($farg == "--sql") + { + $farg = FALSE; + $flagSQL = TRUE; + } + else + $flagSQL = mgCArg(3) == "--sql"; + + mgProcessGalleries(GCMD_UPDATE, $farg); break; case "rescan": case "re": case "res":