Web interface

11 files changed, 496 insertions, 48 deletions

diff --git a/db/install.sql b/db/install.sql
index f58cf5b..fbcf340 100644
--- a/db/install.sql
+++ b/db/install.sql
@@ -1,20 +1,8 @@
 CREATE TABLE `author` (
 	`id` tinyint(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-	`name` varchar(64) NOT NULL,
-	`email` varchar(256) NOT NULL
-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-
-CREATE TABLE `job` (
-	`id` mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-	`package_id` smallint(5) UNSIGNED NOT NULL,
-	`major` tinyint(3) UNSIGNED NOT NULL,
-	`minor` tinyint(3) UNSIGNED NOT NULL,
-	`revision` tinyint(3) UNSIGNED NOT NULL,
-	`time_scheduled` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
-	`time_started` datetime DEFAULT NULL,
-	`time_finished` datetime DEFAULT NULL,
-	`result_code` tinyint(3) UNSIGNED DEFAULT NULL,
-	`log` text
+	`name` varchar(63) NOT NULL,
+	`email` varchar(255) NOT NULL,
+	`password` varchar(255) NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 CREATE TABLE `package` (
@@ -22,6 +10,7 @@ CREATE TABLE `package` (
 	`author_id` tinyint(3) UNSIGNED NOT NULL,
 	`name` varchar(63) NOT NULL,
 	`url` varchar(2047) NOT NULL,
+	`git_url` varchar(2047) NOT NULL,
 	`desc` text NOT NULL,
 	`time_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
@@ -37,7 +26,6 @@ CREATE TABLE `version` (
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
 ALTER TABLE `author`
-	ADD PRIMARY KEY (`id`),
 	ADD UNIQUE KEY `name` (`name`);
 
 ALTER TABLE `package`
diff --git a/frontend/Author.php b/frontend/Author.php
new file mode 100644
index 0000000..669e733
--- /dev/null
+++ b/frontend/Author.php
@@ -0,0 +1,45 @@
+<?php
+class Author extends Model {
+	public static
+		$table = 'author',
+		$fillable_columns = ['name', 'email', 'password'];
+
+	public static function randomPass() {
+		return preg_replace('/[^\w]/', '',
+			base64_encode(bin2hex(openssl_random_pseudo_bytes(4))));
+	}
+
+	public function getPackageIds() {
+		return Package::searchIds($this->pdo, ['`author_id`=?'], [$this->id]);
+	}
+
+	public function getPackages() {
+		return Package::search($this->pdo, ['`author_id`=?'], [$this->id]);
+	}
+
+	public static function hash($password) {
+		return password_hash($password, PASSWORD_DEFAULT, ['cost' => 10]);
+	}
+
+	protected static function mutator($key, $value) {
+		switch ($key) {
+			case 'password':
+				return self::hash($value);
+			default:
+				return parent::mutator($key, $value);
+		}
+	}
+
+	public function isAdmin() {
+		return in_array($this->id, Constants::user_admins);
+	}
+
+	public function verifyPassword($password) {
+		if (!password_verify($password, $this->password))
+			return false;
+		if (password_needs_rehash(
+				$this->password, PASSWORD_DEFAULT, ['cost' => 10]))
+			$this->password = $password;
+		return true;
+	}
+}
diff --git a/frontend/Dockerfile b/frontend/Dockerfile
index e9e468b..73b4a60 100644
--- a/frontend/Dockerfile
+++ b/frontend/Dockerfile
@@ -1,3 +1,3 @@
 FROM php:apache
 
-RUN docker-php-ext-install mysqli
+RUN docker-php-ext-install pdo pdo_mysql
diff --git a/frontend/Model.php b/frontend/Model.php
new file mode 100644
index 0000000..0f70aa2
--- /dev/null
+++ b/frontend/Model.php
@@ -0,0 +1,138 @@
+<?php
+class ModelNotFoundException extends Exception {}
+class ModelCreateFailedException extends Exception {}
+class ModelIllegalAccessException extends Exception {}
+class ModelSetFailedException extends Exception {}
+
+abstract class Model {
+	public static
+		$table             = '',
+		$primary_key       = 'id',
+		$protected_columns = ['id'],
+		$fillable_columns  = [],
+		$timestamps        = [],
+		$dates             = [],
+		$booleans          = [];
+
+	protected $pdo, $data;
+
+	const TABLE_PREFIX = '';
+
+	public function __construct($pdo, $id) {
+		$this->pdo = $pdo;
+
+		$stmt = $this->pdo->prepare("SELECT * FROM `".self::table()."` WHERE `".static::$primary_key."`=?");
+		$stmt->execute([$id]);
+		if ($stmt->rowCount() == 0) {
+			throw new ModelNotFoundException("The ".static::$table." with id '$id' could not be found.");
+		}
+		$this->data = $stmt->fetch(PDO::FETCH_ASSOC);
+	}
+
+	public function __set($key, $value) {
+		if (!in_array($key, static::$fillable_columns)) {
+			throw new ModelIllegalAccessException("Column `".self::table()."`.`$key` cannot be edited.");
+		}
+		if ($this->data[$key] == $value) {
+			return;
+		}
+		$stmt = $this->pdo->prepare("UPDATE `".self::table()."` SET `$key`=? WHERE `".static::$primary_key."`=?");
+		$stmt->execute([
+			$this->mutator($key, $value),
+			$this->data[static::$primary_key]
+		]);
+		if ($stmt->rowCount() != 1) {
+			throw new ModelSetFailedException("Failed to update `".self::table()."`.`$key` to '$value'.");
+		}
+		$this->data[$key] = $value;
+	}
+
+	public function __get($key) {
+		return $this->accessor($key, $this->data[$key]);
+	}
+
+	public static function create($pdo, $values) {
+		$class = get_called_class();
+
+		$columns = array_combine(static::$fillable_columns, $values);
+		$questions = [];
+
+		foreach ($columns as $column => $value) {
+			$columns[$column] = $class::mutator($column, $value);
+			$questions[] = '?';
+		}
+
+		$stmt = $pdo->prepare(
+			"INSERT INTO `".self::table()."` " .
+			"(`" . implode('`, `', array_keys($columns)) . "`) " .
+			"VALUES (" . implode(',', $questions) . ")");
+		$stmt->execute(array_values($columns));
+
+		if ($stmt->rowCount() != 1)
+			throw new ModelCreateFailedException();
+		
+		return new $class($pdo, $pdo->lastInsertId());
+	}
+
+	public static function searchIds($pdo, $where = [], $values = []) {
+		$stmt = $pdo->prepare("SELECT `id` FROM `".static::table()."`" . ((count($where) > 0) ? (" WHERE (" . implode(') AND (', $where) . ")") : ""));
+		$stmt->execute($values);
+
+		$ids = [];
+		foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
+			$ids[] = $row['id'];
+		}
+		return $ids;
+	}
+
+	public static function search($pdo, $where = [], $values = []) {
+		$class = get_called_class();
+
+		$items = [];
+		foreach (self::searchIds($pdo, $where, $values) as $id) {
+			$items[] = new $class($pdo, $id);
+		}
+		return $items;
+	}
+
+	public static function count($pdo, $where = [], $values = []) {
+		$class = get_called_class();
+
+		$stmt = $pdo->prepare("SELECT COUNT(*) FROM `".static::table()."`" . ((count($where) > 0) ? (" WHERE (" . implode(') AND (', $where) . ")") : ""));
+		$stmt->execute($values);
+
+		return $stmt->fetchColumn();
+	}
+
+	protected static function accessor($key, $value) {
+		if (is_null($value)) {
+			return null;
+		} elseif (in_array($key, static::$booleans)) {
+			return (bool) $value;
+		} elseif (in_array($key, static::$dates) || in_array($key, static::$timestamps)) {
+			return strtotime($value);
+		} else {
+			return $value;
+		}
+	}
+
+	protected static function mutator($key, $value) {
+		if (in_array($key, static::$dates) && is_int($value)) {
+			return date('Y-m-d', $value);
+		} elseif (in_array($key, static::$timestamps) && is_int($value)) {
+			return date('Y-m-d H:i:s', $value);
+		} else {
+			return (string) $value;
+		}
+	}
+
+	public function delete() {
+		$stmt = $this->pdo->prepare("DELETE FROM `".self::table()."` WHERE `".static::$primary_key."`=?");
+		$stmt->execute([$this->data[static::$primary_key]]);
+		return $stmt->rowCount() != 0;
+	}
+
+	private static function table() {
+		return self::TABLE_PREFIX . static::$table;
+	}
+}
diff --git a/frontend/Package.php b/frontend/Package.php
new file mode 100644
index 0000000..0b2afed
--- /dev/null
+++ b/frontend/Package.php
@@ -0,0 +1,18 @@
+<?php
+class Package extends Model {
+	public static
+		$table = 'package',
+		$fillable_columns = ['author_id', 'name', 'url', 'git_url', 'desc'];
+
+	public function getAuthor() {
+		return new Author($this->pdo, $this->author_id);
+	}
+
+	public function getVersionIds() {
+		return Version::searchIds($this->pdo, ['`package_id`=?'], [$this->id]);
+	}
+
+	public function getVersions() {
+		return Version::search($this->pdo, ['`package_id`=?'], [$this->id]);
+	}
+}
diff --git a/frontend/Version.php b/frontend/Version.php
new file mode 100644
index 0000000..39d3dd2
--- /dev/null
+++ b/frontend/Version.php
@@ -0,0 +1,10 @@
+<?php
+class Version extends Model {
+	public static
+		$table = 'version',
+		$fillable_columns = ['major', 'minor', 'revision', 'depends'];
+
+	public function getPackage() {
+		return new Package($this->pdo, $this->package_id);
+	}
+}
diff --git a/frontend/conf.php b/frontend/conf.php
index f47a7cb..dea8c25 100644
--- a/frontend/conf.php
+++ b/frontend/conf.php
@@ -3,7 +3,21 @@ define('DB_HOST', 'db');
 define('DB_NAME', 'clpmdb');
 define('DB_USER', 'clpm');
 define('DB_PASS', 'clpm');
+define('DB_PORT', 3306);
 
-$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
-if (mysqli_connect_errno())
-	die('Connection to the database failed.');
+session_start();
+
+try {
+	$_pdo = new PDO("mysql:host=".DB_HOST.";port=".DB_PORT.";dbname=".DB_NAME.";charset=utf8", DB_USER, DB_PASS);
+	$_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+	$_pdo->setAttribute(PDO::ATTR_ORACLE_NULLS, PDO::NULL_NATURAL);
+	$_pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);
+} catch (PDOException $e) {
+	die("Down until PDO error fixed.");
+}
+
+spl_autoload_register(function ($pClass) {
+	$path = __DIR__ . "/$pClass.php";
+	if (file_exists($path))
+		require_once($path);
+});
diff --git a/frontend/foot.php b/frontend/foot.php
new file mode 100644
index 0000000..308b1d0
--- /dev/null
+++ b/frontend/foot.php
@@ -0,0 +1,2 @@
+</body>
+</html>
diff --git a/frontend/head.php b/frontend/head.php
new file mode 100644
index 0000000..8664773
--- /dev/null
+++ b/frontend/head.php
@@ -0,0 +1,20 @@
+<!DOCTYPE html>
+<html>
+<head>
+	<title>CLPM</title>
+
+	<style type="text/css">
+		td, th {
+			padding-right: 1em;
+			text-align: left;
+		}
+
+		.error {
+			color: #b00;
+			font-weight: bold;
+		}
+	</style>
+</head>
+<body>
+	<h1>CLPM</h1>
+	<hr/>
diff --git a/frontend/index.php b/frontend/index.php
new file mode 100644
index 0000000..373052f
--- /dev/null
+++ b/frontend/index.php
@@ -0,0 +1,229 @@
+<?php
+require_once('conf.php');
+
+if (isset($_GET['logout'])) {
+	session_destroy();
+	header('Location: /');
+}
+
+require_once('head.php');
+
+if (isset($_POST['create'])) {
+	$pass = Author::randomPass();
+	try {
+		$author = Author::create(
+			$_pdo, [$_POST['create_name'], $_POST['create_email'], $pass]);
+		?>
+		Your account has been created.
+		Your name is <tt><?=$author->name?></tt> and your password is <tt><?=$pass?></tt>.
+		<b>Store this password in a secure place.</b>
+		<?php
+	} catch (Exception $e) {
+		echo '<p class="error">' . $e->getMessage() . '.</p>';
+	}
+}
+
+if (isset($_POST['login'])) {
+	$authors = Author::search($_pdo, ['`name` like ?'], [$_POST['login_name']]);
+	if (count($authors) == 0) {
+		echo '<p class="error">No such user.</p>';
+	} else {
+		$author = $authors[0];
+		if ($author->verifyPassword($_POST['login_pass'])) {
+			$_SESSION['logged_in_id'] = $author->id;
+		} else {
+			echo '<p class="error">Invalid password.</p>';
+		}
+	}
+}
+
+if (isset($_SESSION['logged_in_id'])) {
+	$_author = new Author($_pdo, $_SESSION['logged_in_id']);
+	echo "You are logged in as {$_author->name}. <a href='?logout'>Logout</a>.<hr/>";
+
+	if (isset($_POST['create_pkg'])) {
+		try {
+			$pkg = Package::create($_pdo,
+				[ $_author->id
+				, $_POST['create_pkg_name']
+				, $_POST['create_pkg_url']
+				, $_POST['create_pkg_git_url']
+				, $_POST['create_pkg_desc']]);
+			echo 'Your package has been created.';
+		} catch (Exception $e) {
+			echo '<p class="error">' . $e->getMessage() . '.</p>';
+		}
+	}
+}
+?>
+
+<h2>Packages</h2>
+<?php
+$pkgs = Package::search($_pdo);
+
+if (count($pkgs) == 0) :
+	echo '<p class="error">No packages found.</p>';
+else :
+?>
+	<table>
+		<tr>
+			<th>Name</th>
+			<th>Author</th>
+			<th>Description</th>
+			<th>Website</th>
+			<th>Versions</th>
+		</tr>
+		<?php
+		foreach ($pkgs as $pkg) {
+			echo 
+				"<tr>
+					<td>{$pkg->name}</td>
+					<td>" . $pkg->getAuthor()->name . "</td>
+					<td>{$pkg->desc}</td>";
+		
+			if ($pkg->url != '')
+				echo "<td><a href='{$pkg->url}' target='_blank'>Go</a></td>";
+			else
+				echo "<td>&endash;</td>";
+		
+			echo "<td>";
+			foreach ($pkg->getVersions() as $v)
+				echo "{$v->major}.{$v->minor}.{$v->revision} ({$v->time_added})";
+			echo "</td>";
+		
+			echo
+					"<td>{$pkg->name}</td>
+				</tr>";
+		}
+		?>
+	</table>
+<?php
+endif;
+?>
+
+<hr/>
+
+<h2>Create an account</h2>
+You only need an account to add new packages.
+<form method="post">
+	<table>
+		<tr>
+			<th>Name</th>
+			<td><input name="create_name"/></td>
+			<td>Posted publicly.</td>
+		</tr>
+		<tr>
+			<th>Email</th>
+			<td><input type="email" name="create_email"/></td>
+			<td>Kept private.</td>
+		</tr>
+		<tr>
+			<th></th>
+			<td><input type="submit" name="create" value="Create"/></td>
+		</tr>
+	</table>
+</form>
+
+<hr/>
+
+<?php
+if (isset($_SESSION['logged_in_id'])) :
+?>
+	<h2>Your packages</h2>
+	<?php
+	$pkgs = $_author->getPackages();
+	if (count($pkgs) == 0) :
+		echo '<p class="error">No packages found.</p>';
+	else :
+	?>
+		<p>To change details, please contact the CLPM maintainer.</p>
+		<table>
+			<tr>
+				<th>Name</th>
+				<th>URL</th>
+				<th>Git URL</th>
+				<th>Description</th>
+			</tr>
+			<?php
+			foreach ($pkgs as $pkg) {
+				echo "
+					<tr>
+						<td>{$pkg->name}</td>";
+				if ($pkg->url != '')
+					echo "<td><a href='{$pkg->url}'>{$pkg->url}</a></td>";
+				else
+					echo "<td>&endash;</td>";
+				echo "
+						<td><a href='{$pkg->git_url}'>{$pkg->git_url}</a></td>
+						<td>{$pkg->desc}</td>
+					</tr>";
+			}
+			?>
+		</table>
+	<?php
+	endif;
+	?>
+
+	<h3>Add new</h3>
+	<form method="post">
+		<table>
+			<tr>
+				<th>Name</th>
+				<td><input name="create_pkg_name"/></td>
+				<td>Shown publicly.</td>
+			</tr>
+			<tr>
+				<th>URL</th>
+				<td><input type="url" name="create_pkg_url"/></td>
+				<td>Shown publicly.</td>
+			</tr>
+			<tr>
+				<th>Git URL</th>
+				<td><input type="url" name="create_pkg_git_url"/></td>
+				<td>
+					<p>Your git repository. It should not require authorisation.</p>
+					<p>On scheduled intervals, we will clone the repository and
+						search for tags that look like a version number, e.g. <tt>v1.0.2</tt>.
+					For those versions that are not yet found in the database,
+						we will checkout the tag and run <tt>make TARGET.tar.gz</tt> for all targets
+						(currently <tt>linux32</tt>, <tt>linux64</tt>, <tt>mac</tt>, <tt>win32</tt> and <tt>win64</tt>).
+					We will then copy tose files to <tt>/repo/PACKAGE/VERSION/PLATFORM.tar.gz</tt>.</p>
+				</td>
+			</tr>
+			<tr>
+				<th>Description</th>
+				<td colspan="2">
+					<textarea name="create_pkg_desc" cols="60" rows="6"></textarea>
+				</td>
+			</tr>
+			<tr>
+				<th></th>
+				<td><input type="submit" name="create_pkg" value="Add"/></td>
+			</tr>
+		</table>
+	</form>
+<?php
+else : // Not logged in
+?>
+	<h2>Login</h2>
+	<form method="post">
+		<table>
+			<tr>
+				<th>Name</th>
+				<td><input name="login_name"/></td>
+			</tr>
+			<tr>
+				<th>Password</th>
+				<td><input type="password" name="login_pass"/></td>
+			</tr>
+			<tr>
+				<th></th>
+				<td><input type="submit" name="login" value="Login"/></td>
+			</tr>
+		</table>
+		<p>If you lost your password, please contact the CLPM maintainer.</p>
+	</form>
+<?php
+endif;
+
+require_once('foot.php');
diff --git a/frontend/list.php b/frontend/list.php
index 24340a8..39b727a 100644
--- a/frontend/list.php
+++ b/frontend/list.php
@@ -3,39 +3,23 @@ require_once('conf.php');
 
 $repo = [];
 
-$stmt = $db->prepare(
-	'SELECT `package`.`id`,`package`.`name`,`url`,`desc`,`author`.`name` ' .
-	'FROM `package`,`author` ' .
-	'WHERE `package`.`author_id` = `author`.`id`');
-$stmt->execute();
-$stmt->bind_result($id, $name, $url, $desc, $author);
-while ($stmt->fetch() === true) {
-	$repo[] = [
-		'id' => $id,
-		'name' => $name,
-		'author' => $author,
-		'desc' => $desc,
-		'url' => $url,
+foreach (Package::search($_pdo) as $pkg) {
+	$new_pkg = [
+		'name' => $pkg->name,
+		'author' => $pkg->getAuthor()->name,
+		'desc' => $pkg->desc,
+		'url' => $pkg->url,
 		'versions' => []
 	];
-}
-$stmt->close();
 
-$stmt = $db->prepare(
-	'SELECT `major`,`minor`,`revision`,`depends` ' .
-	'FROM `version` WHERE `package_id`=?');
-$stmt->bind_param('i', $pkg);
-$stmt->bind_result($maj, $min, $rev, $deps);
-for ($i = 0; $i < count($repo); $i++) {
-	$pkg = $repo[$i]['id'];
-	$stmt->execute();
-	while ($stmt->fetch() === true) {
-		$repo[$i]['versions'][] = [
-			'version' => [$maj, $min, $rev],
-			'depends' => json_decode($deps)
+	foreach ($pkg->getVersions() as $version) {
+		$new_pkg['version'][] = [
+			'version' => [$version->major, $version->minor, $version->revision],
+			'depends' => json_decode($version->depends)
 		];
 	}
-	unset($repo[$i]['id']);
+
+	$repo[] = $new_pkg;
 }
 
 print(json_encode($repo));