add album database management bits

6 files changed, 577 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;
+	}
+}
diff --git a/tests/Album/AlbumMapperTest.php b/tests/Album/AlbumMapperTest.php
new file mode 100644
index 00000000..257d09cd
--- /dev/null
+++ b/tests/Album/AlbumMapperTest.php
@@ -0,0 +1,191 @@
+<?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\Tests\Album;
+
+use OCA\Photos\Album\AlbumFile;
+use OCA\Photos\Album\AlbumInfo;
+use OCA\Photos\Album\AlbumMapper;
+use OCA\Photos\Album\AlbumWithFiles;
+use OCP\Constants;
+use OCP\DB\QueryBuilder\IQueryBuilder;
+use OCP\Files\IMimeTypeLoader;
+use OCP\IDBConnection;
+use Test\TestCase;
+
+/**
+ * @group DB
+ */
+class AlbumMapperTest extends TestCase {
+	/** @var IDBConnection */
+	private $connection;
+	private array $createdFiles = [];
+	/** @var IMimeTypeLoader */
+	private $mimeLoader;
+	/** @var AlbumMapper */
+	private $mapper;
+
+	protected function setUp(): void {
+		parent::setUp();
+
+		$this->createdFiles = [];
+		$this->connection = \OC::$server->get(IDBConnection::class);
+		$this->mimeLoader = \OC::$server->get(IMimeTypeLoader::class);
+		$this->mapper = new AlbumMapper($this->connection, $this->mimeLoader);
+	}
+
+	protected function tearDown():void {
+		$query = $this->connection->getQueryBuilder();
+		$query->delete('filecache')
+			->where($query->expr()->eq('fileid', $query->createParameter("fileid")));
+		foreach ($this->createdFiles as $createdFile) {
+			$query->setParameter("fileid", $createdFile);
+			$query->executeStatement();
+		}
+		$this->createdFiles = [];
+
+		$this->connection->getQueryBuilder()->delete('photos_albums')->executeStatement();
+		$this->connection->getQueryBuilder()->delete('photos_albums_files')->executeStatement();
+
+		parent::tearDown();
+	}
+
+	private function createFile(string $name, string $mimeType, int $size = 10, int $mtime = 10000, int $permissions = Constants::PERMISSION_ALL): int {
+		$mimeId = $this->mimeLoader->getId($mimeType);
+		$mimePartId = $this->mimeLoader->getId(substr($mimeType, strpos($mimeType, '/')));
+		$query = $this->connection->getQueryBuilder();
+		$query->insert('filecache')
+			->values([
+				'storage' => $query->createNamedParameter(-1, IQueryBuilder::PARAM_INT),
+				'parent' => $query->createNamedParameter(-1, IQueryBuilder::PARAM_INT),
+				'path' => $query->createNamedParameter("/dummy/" . $name),
+				'path_hash' => $query->createNamedParameter(md5("dummy/" . $name)),
+				'name' => $query->createNamedParameter($name),
+				'mimetype' => $query->createNamedParameter($mimeId, IQueryBuilder::PARAM_INT),
+				'mimepart' => $query->createNamedParameter($mimePartId, IQueryBuilder::PARAM_INT),
+				'size' => $query->createNamedParameter($size, IQueryBuilder::PARAM_INT),
+				'mtime' => $query->createNamedParameter($mtime, IQueryBuilder::PARAM_INT),
+				'storage_mtime' => $query->createNamedParameter($mtime, IQueryBuilder::PARAM_INT),
+				'permissions' => $query->createNamedParameter($permissions, IQueryBuilder::PARAM_INT),
+				'etag' => $query->createNamedParameter('dummy'),
+			]);
+		$query->executeStatement();
+		$id = $query->getLastInsertId();
+		$this->createdFiles[] = $id;
+		return $id;
+	}
+
+	public function testCreateGet() {
+		$album = $this->mapper->create("user1", "album1");
+
+		$retrievedAlbum = $this->mapper->get($album->getId());
+		$this->assertEquals($album, $retrievedAlbum);
+	}
+
+	public function testCreateList() {
+		$album1 = $this->mapper->create("user1", "album1");
+		$album2 = $this->mapper->create("user1", "album2");
+		$this->mapper->create("user2", "album3");
+
+		$retrievedAlbums = $this->mapper->getForUser('user1');
+		usort($retrievedAlbums, function(AlbumInfo $a, AlbumInfo $b) {
+			return $a->getId() <=> $b->getId();
+		});
+		$this->assertEquals([$album1, $album2], $retrievedAlbums);
+	}
+
+	public function testCreateDeleteGet() {
+		$album = $this->mapper->create("user1", "album1");
+
+		$retrievedAlbum = $this->mapper->get($album->getId());
+		$this->assertEquals($album, $retrievedAlbum);
+
+		$this->mapper->delete($album->getId());
+
+		$this->assertEquals(null, $this->mapper->get($album->getId()));
+	}
+
+	public function testCreateDeleteList() {
+		$album1 = $this->mapper->create("user1", "album1");
+		$album2 = $this->mapper->create("user1", "album2");
+		$this->mapper->create("user2", "album3");
+
+		$this->mapper->delete($album1->getId());
+
+		$retrievedAlbums = $this->mapper->getForUser('user1');
+		usort($retrievedAlbums, function(AlbumInfo $a, AlbumInfo $b) {
+			return $a->getId() <=> $b->getId();
+		});
+		$this->assertEquals([$album2], $retrievedAlbums);
+	}
+
+	public function testCreateRenameGet() {
+		$album = $this->mapper->create("user1", "album1");
+		$this->mapper->rename($album->getId(),"renamed");
+
+		$retrievedAlbum = $this->mapper->get($album->getId());
+		$this->assertEquals("renamed", $retrievedAlbum->getTitle());
+	}
+
+	public function testEmptyFiles() {
+		$album1 = $this->mapper->create("user1", "album1");
+
+		$this->assertEquals([new AlbumWithFiles($album1, [])], $this->mapper->getForUserWithFiles("user1"));
+	}
+
+	public function testAddFiles() {
+		$album1 = $this->mapper->create("user1", "album1");
+		$album2 = $this->mapper->create("user1", "album2");
+
+		$fileId1 = $this->createFile("file1", "text/plain");
+		$fileId2 = $this->createFile("file2", "image/png");
+
+		$this->mapper->addFile($album1->getId(), $fileId1);
+		$this->mapper->addFile($album1->getId(), $fileId2);
+		$this->mapper->addFile($album2->getId(), $fileId1);
+
+		$albumsWithFiles = $this->mapper->getForUserWithFiles("user1");
+		usort($albumsWithFiles, function(AlbumWithFiles $a, AlbumWithFiles $b) {
+			return $a->getAlbum()->getId() <=> $b->getAlbum()->getId();
+		});
+		$this->assertCount(2, $albumsWithFiles);
+
+		$this->assertEquals($album1, $albumsWithFiles[0]->getAlbum());
+		$files = $albumsWithFiles[0]->getFiles();
+		usort($files, function(AlbumFile $a, AlbumFile $b) {
+			return $a->getFileId() <=> $b->getFileId();
+		});
+		$this->assertCount(2, $files);
+		$this->assertEquals(new AlbumFile($fileId1, "file1", "text/plain"), $albumsWithFiles[0]->getFiles()[0]);
+		$this->assertEquals(new AlbumFile($fileId2, "file2", "image/png"), $albumsWithFiles[0]->getFiles()[1]);
+
+		$this->assertEquals($album2, $albumsWithFiles[1]->getAlbum());
+
+		$files = $albumsWithFiles[1]->getFiles();
+		usort($files, function(AlbumFile $a, AlbumFile $b) {
+			return $a->getFileId() <=> $b->getFileId();
+		});
+		$this->assertCount(1, $files);
+		$this->assertEquals(new AlbumFile($fileId1, "file1", "text/plain"), $albumsWithFiles[0]->getFiles()[0]);
+	}
+}