Mercurial > hg > mgallery

changeset 290:19fbf800b1f7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Work on very early form of virtual gallery support.
author Matti Hamalainen <ccr@tnsp.org>
date Sun, 28 Jul 2019 07:53:36 +0300
parents 13cff35dfbec
children 13a23b120e72
files mgallery.inc.php mgallery.php mgtool.php
diffstat 3 files changed, 625 insertions(+), 2 deletions(-) [+]
line wrap: on
line diff
--- 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 "<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 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"] .= "<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) ||
--- 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 =
     "<?php\n".
     "\$galData = ".var_export($gallery, TRUE).";\n".
@@ -987,7 +1104,7 @@
 
 function mgProcessGalleries($cmd, $path)
 {
-  global $galTNPath, $galMedPath;
+  global $galTNPath, $galMedPath, $galSQLTables, $flagSQL;
 
   // Check validity of some settings
   $galPath = mgGetSetting("base_path");
@@ -1040,6 +1157,16 @@
 
   // 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);
 }
 
@@ -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":