add album database management bits

Signed-off-by: Louis Chemineau <louis@chmn.me>

5 files changed, 386 insertions, 0 deletions

diff --git a/lib/Album/AlbumFile.php b/lib/Album/AlbumFile.php
new file mode 100644
index 00000000..acd08707
--- /dev/null
+++ b/lib/Album/AlbumFile.php
@@ -0,0 +1,48 @@
+<?php
+
+declare(strict_types=1);
+/**
+ * @copyright Copyright (c) 2022 Robin Appelman <robin@icewind.nl>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ */
+
+namespace OCA\Photos\Album;
+
+class AlbumFile {
+	private int $fileId;
+	private string $name;
+	private string $mimeType;
+
+	public function __construct(int $fileId, string $name, string $mimeType) {
+		$this->fileId = $fileId;
+		$this->name = $name;
+		$this->mimeType = $mimeType;
+	}
+
+	public function getFileId(): int {
+		return $this->fileId;
+	}
+
+	public function getName(): string {
+		return $this->name;
+	}
+
+	public function getMimeType(): string {
+		return $this->mimeType;
+	}
+}
diff --git a/lib/Album/AlbumInfo.php b/lib/Album/AlbumInfo.php
new file mode 100644
index 00000000..ced62ea0
--- /dev/null
+++ b/lib/Album/AlbumInfo.php
@@ -0,0 +1,48 @@
+<?php
+
+declare(strict_types=1);
+/**
+ * @copyright Copyright (c) 2022 Robin Appelman <robin@icewind.nl>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ */
+
+namespace OCA\Photos\Album;
+
+class AlbumInfo {
+	private int $id;
+	private string $userId;
+	private string $title;
+
+	public function __construct(int $id, string $userId, string $title) {
+		$this->id = $id;
+		$this->userId = $userId;
+		$this->title = $title;
+	}
+
+	public function getId(): int {
+		return $this->id;
+	}
+
+	public function getUserId(): string {
+		return $this->userId;
+	}
+
+	public function getTitle(): string {
+		return $this->title;
+	}
+}
diff --git a/lib/Album/AlbumMapper.php b/lib/Album/AlbumMapper.php
new file mode 100644
index 00000000..99923d8f
--- /dev/null
+++ b/lib/Album/AlbumMapper.php
@@ -0,0 +1,157 @@
+<?php
+
+declare(strict_types=1);
+/**
+ * @copyright Copyright (c) 2022 Robin Appelman <robin@icewind.nl>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ */
+
+namespace OCA\Photos\Album;
+
+use OCP\DB\QueryBuilder\IQueryBuilder;
+use OCP\Files\IMimeTypeLoader;
+use OCP\IDBConnection;
+
+class AlbumMapper {
+	private IDBConnection $connection;
+	private IMimeTypeLoader $mimeTypeLoader;
+
+	public function __construct(IDBConnection $connection, IMimeTypeLoader $mimeTypeLoader) {
+		$this->connection = $connection;
+		$this->mimeTypeLoader = $mimeTypeLoader;
+	}
+
+	public function create(string $userId, string $name): AlbumInfo {
+		$query = $this->connection->getQueryBuilder();
+		$query->insert("photos_albums")
+			->values([
+				'user' => $query->createNamedParameter($userId),
+				'name' => $query->createNamedParameter($name),
+			]);
+		$query->executeStatement();
+		$id = $query->getLastInsertId();
+
+		return new AlbumInfo($id, $userId, $name);
+	}
+
+	public function get(int $id): ?AlbumInfo {
+		$query = $this->connection->getQueryBuilder();
+		$query->select("name", "user")
+			->from("photos_albums")
+			->where($query->expr()->eq('album_id', $query->createNamedParameter($id, IQueryBuilder::PARAM_INT)));
+		$row = $query->executeQuery()->fetch();
+		if ($row) {
+			return new AlbumInfo($id, $row['user'], $row['name']);
+		} else {
+			return null;
+		}
+	}
+
+	/**
+	 * @param string $userId
+	 * @return AlbumInfo[]
+	 */
+	public function getForUser(string $userId): array {
+		$query = $this->connection->getQueryBuilder();
+		$query->select("album_id", "name")
+			->from("photos_albums")
+			->where($query->expr()->eq('user', $query->createNamedParameter($userId)));
+		$rows = $query->executeQuery()->fetchAll();
+		return array_map(function (array $row) use ($userId) {
+			return new AlbumInfo($row['album_id'], $userId, $row['name']);
+		}, $rows);
+	}
+
+	public function rename(int $id, string $newName): void {
+		$query = $this->connection->getQueryBuilder();
+		$query->update("photos_albums")
+			->set("name", $query->createNamedParameter($newName))
+			->where($query->expr()->eq('album_id', $query->createNamedParameter($id, IQueryBuilder::PARAM_INT)));
+		$query->executeStatement();
+	}
+
+	public function delete(int $id): void {
+		$this->connection->beginTransaction();
+		$query = $this->connection->getQueryBuilder();
+		$query->delete("photos_albums")
+			->where($query->expr()->eq('album_id', $query->createNamedParameter($id, IQueryBuilder::PARAM_INT)));
+		$query->executeStatement();
+
+
+		$query = $this->connection->getQueryBuilder();
+		$query->delete("photos_albums_files")
+			->where($query->expr()->eq('album_id', $query->createNamedParameter($id, IQueryBuilder::PARAM_INT)));
+		$query->executeStatement();
+		$this->connection->commit();
+	}
+
+	/**
+	 * @param string $userId
+	 * @return AlbumWithFiles[]
+	 */
+	public function getForUserWithFiles(string $userId): array {
+		$query = $this->connection->getQueryBuilder();
+		$query->select("fileid", "mimetype", "a.album_id")
+			->selectAlias("f.name", "file_name")
+			->selectAlias("a.name", "album_name")
+			->from("photos_albums", "a")
+			->leftJoin("a", "photos_albums_files", "p", $query->expr()->eq("a.album_id", "p.album_id"))
+			->leftJoin("p", "filecache", "f", $query->expr()->eq("p.file_id", "f.fileid"))
+			->where($query->expr()->eq('user', $query->createNamedParameter($userId)));
+		$rows = $query->executeQuery()->fetchAll();
+
+		$filesByAlbum = [];
+		$albumsById = [];
+		foreach ($rows as $row) {
+			$albumId = $row['album_id'];
+			if ($row['fileid']) {
+				$mimeId = $row['mimetype'];
+				$mimeType = $this->mimeTypeLoader->getMimetypeById($mimeId);
+				$filesByAlbum[$albumId][] = new AlbumFile($row['fileid'], $row['file_name'], $mimeType);
+			}
+
+			if (!isset($albumsById[$albumId])) {
+				$albumsById[$albumId] = new AlbumInfo($albumId, $userId, $row['album_name']);
+			}
+		}
+
+		$result = [];
+		foreach ($albumsById as $id => $album) {
+			$result[] = new AlbumWithFiles($album, $filesByAlbum[$id] ?? []);
+		}
+		return $result;
+	}
+
+	public function addFile(int $albumId, int $fileId): void {
+		$query = $this->connection->getQueryBuilder();
+		$query->insert("photos_albums_files")
+			->values([
+				"album_id" => $query->createNamedParameter($albumId, IQueryBuilder::PARAM_INT),
+				"file_id" => $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT)
+			]);
+		$query->executeStatement();
+	}
+
+	public function removeFile(int $albumId, int $fileId): void {
+		$query = $this->connection->getQueryBuilder();
+		$query->delete("photos_albums_files")
+			->where($query->expr()->eq("album_id", $query->createNamedParameter($albumId, IQueryBuilder::PARAM_INT)))
+			->andWhere($query->expr()->eq("file_id", $query->createNamedParameter($fileId, IQueryBuilder::PARAM_INT)));
+		$query->executeStatement();
+	}
+}
diff --git a/lib/Album/AlbumWithFiles.php b/lib/Album/AlbumWithFiles.php
new file mode 100644
index 00000000..4ffc6d50
--- /dev/null
+++ b/lib/Album/AlbumWithFiles.php
@@ -0,0 +1,48 @@
+<?php
+
+declare(strict_types=1);
+/**
+ * @copyright Copyright (c) 2022 Robin Appelman <robin@icewind.nl>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program.  If not, see <http://www.gnu.org/licenses/>.
+ *
+ */
+
+namespace OCA\Photos\Album;
+
+use OC\Files\Cache\CacheEntry;
+
+class AlbumWithFiles {
+	private AlbumInfo $info;
+	/** @var CacheEntry[] */
+	private array $files;
+
+	public function __construct(AlbumInfo $info, array $files) {
+		$this->info = $info;
+		$this->files = $files;
+	}
+
+	public function getAlbum(): AlbumInfo {
+		return $this->info;
+	}
+
+	/**
+	 * @return CacheEntry[]
+	 */
+	public function getFiles(): array {
+		return $this->files;
+	}
+}
diff --git a/lib/Migration/Version20000Date20220727125801.php b/lib/Migration/Version20000Date20220727125801.php
new file mode 100644
index 00000000..295cb5a1
--- /dev/null
+++ b/lib/Migration/Version20000Date20220727125801.php
@@ -0,0 +1,85 @@
+<?php
+
+declare(strict_types=1);
+
+/**
+ * @copyright Copyright (c) 2022 Your name <your@email.com>
+ *
+ * @author Your name <your@email.com>
+ *
+ * @license GNU AGPL version 3 or any later version
+ *
+ * This program is free software: you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation, either version 3 of the
+ * License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU Affero General Public License for more details.
+ *
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program. If not, see <http://www.gnu.org/licenses/>.
+ *
+ */
+
+namespace OCA\Photos\Migration;
+
+use Closure;
+use Doctrine\DBAL\Types\Types;
+use OC\DB\SchemaWrapper;
+use OCP\DB\ISchemaWrapper;
+use OCP\Migration\IOutput;
+use OCP\Migration\SimpleMigrationStep;
+
+/**
+ * Auto-generated migration step: Please modify to your needs!
+ */
+class Version20000Date20220727125801 extends SimpleMigrationStep {
+	public function changeSchema(IOutput $output, Closure $schemaClosure, array $options): ?ISchemaWrapper {
+		/** @var SchemaWrapper $schema */
+		$schema = $schemaClosure();
+
+		if (!$schema->hasTable("photos_albums")) {
+			$table = $schema->createTable("photos_albums");
+			$table->addColumn('album_id', Types::BIGINT, [
+				'autoincrement' => true,
+				'notnull' => true,
+				'length' => 20,
+			]);
+			$table->addColumn('name', 'string', [
+				'notnull' => true,
+				'length' => 255,
+			]);
+			$table->addColumn('user', 'string', [
+				'notnull' => true,
+				'length' => 255,
+			]);
+			$table->setPrimaryKey(['album_id']);
+			$table->addIndex(['user'], 'pa_user');
+		}
+
+		if (!$schema->hasTable('photos_albums_files')) {
+			$table = $schema->createTable('photos_albums_files');
+			$table->addColumn('album_file_id', Types::BIGINT, [
+				'autoincrement' => true,
+				'notnull' => true,
+				'length' => 20,
+			]);
+			$table->addColumn('album_id', Types::BIGINT, [
+				'notnull' => true,
+				'length' => 20,
+			]);
+			$table->addColumn('file_id', Types::BIGINT, [
+				'notnull' => true,
+				'length' => 20,
+			]);
+			$table->setPrimaryKey(['album_file_id']);
+			$table->addIndex(['album_id'], 'paf_folder');
+			$table->addUniqueIndex(['album_id', 'file_id'], 'paf_album_file');
+		}
+
+		return $schema;
+	}
+}