Add category system to categorize posts (readme)

This commit implements a new category system to categorize posts. Each
category can have an unlimited number of nested children categories. A
single post don't necessarily need to be in a category, but it can.

Each category can have a full content body like posts or pages, so you
have enough space to describe the content of your categories.

Please note that you need to have at least the following MySQL/MariaDB
versions to use the category system, because it uses "WITH RECURSIVE"
database queries, the so-called "Common-Table-Expressions (CTE)".

MariaDB: 10.2.2
MySQL: 8.0

See: https://mariadb.com/kb/en/with/
See: https://dev.mysql.com/doc/refman/8.0/en/with.html

6 files changed, 324 insertions, 2 deletions

diff --git a/admin/category/delete.php b/admin/category/delete.php
new file mode 100644
index 0000000..e92387c
--- /dev/null
+++ b/admin/category/delete.php
@@ -0,0 +1,56 @@
+<?php
+#===============================================================================
+# DEFINE: Administration
+#===============================================================================
+const ADMINISTRATION = TRUE;
+const AUTHENTICATION = TRUE;
+
+#===============================================================================
+# INCLUDE: Initialization
+#===============================================================================
+require '../../core/application.php';
+
+#===============================================================================
+# Get repositories
+#===============================================================================
+$CategoryRepository = Application::getRepository('Category');
+
+#===============================================================================
+# Throw 404 error if category could not be found
+#===============================================================================
+if(!$Category = $CategoryRepository->find(HTTP::GET('id'))) {
+	Application::error404();
+}
+
+#===============================================================================
+# Check for delete request
+#===============================================================================
+if(HTTP::issetPOST(['token' => Application::getSecurityToken()], 'delete')) {
+	try {
+		if($CategoryRepository->delete($Category)) {
+			HTTP::redirect(Application::getAdminURL('category/'));
+		}
+	} catch(PDOException $Exception) {
+		$messages[] = $Exception->getMessage();
+	}
+}
+
+#===============================================================================
+# Build document
+#===============================================================================
+$FormTemplate = Template\Factory::build('category/form');
+$FormTemplate->set('HTML', parseEntityContent($Category));
+$FormTemplate->set('FORM', [
+	'TYPE' => 'DELETE',
+	'INFO' => $messages ?? [],
+	'DATA' => array_change_key_case($Category->getAll(), CASE_UPPER),
+	'TOKEN' => Application::getSecurityToken()
+]);
+
+$DeleteTemplate = Template\Factory::build('category/delete');
+$DeleteTemplate->set('HTML', $FormTemplate);
+
+$MainTemplate = Template\Factory::build('main');
+$MainTemplate->set('NAME', $Language->text('title_category_delete'));
+$MainTemplate->set('HTML', $DeleteTemplate);
+echo $MainTemplate;
diff --git a/admin/category/index.php b/admin/category/index.php
new file mode 100644
index 0000000..429435a
--- /dev/null
+++ b/admin/category/index.php
@@ -0,0 +1,72 @@
+<?php
+#===============================================================================
+# DEFINE: Administration
+#===============================================================================
+const ADMINISTRATION = TRUE;
+const AUTHENTICATION = TRUE;
+
+#===============================================================================
+# INCLUDE: Initialization
+#===============================================================================
+require '../../core/application.php';
+
+#===============================================================================
+# Get repositories
+#===============================================================================
+$CategoryRepository = Application::getRepository('Category');
+
+#===============================================================================
+# Pagination
+#===============================================================================
+$site_size = Application::get('ADMIN.CATEGORY.LIST_SIZE');
+
+$count = $CategoryRepository->getCount();
+$lastSite = ceil($count / $site_size);
+
+$currentSite = HTTP::GET('site') ?? 1;
+$currentSite = intval($currentSite);
+
+#===============================================================================
+# Redirect to category create form if no category exists
+#===============================================================================
+if($count === 0) {
+	HTTP::redirect(Application::getAdminURL('category/insert.php'));
+}
+
+if($currentSite < 1 OR ($currentSite > $lastSite AND $lastSite > 0)) {
+	Application::error404();
+}
+
+#===============================================================================
+# Get paginated category list
+#===============================================================================
+$categories = $CategoryRepository->getPaginatedTree(
+	$site_size, ($currentSite-1) * $site_size);
+
+foreach($categories as $Category) {
+	$templates[] = generateCategoryItemTemplate($Category, TRUE);
+}
+
+#===============================================================================
+# Build document
+#===============================================================================
+$PaginationTemplate = Template\Factory::build('pagination');
+$PaginationTemplate->set('THIS', $currentSite);
+$PaginationTemplate->set('LAST', $lastSite);
+$PaginationTemplate->set('HREF', Application::getAdminURL('category/?site=%d'));
+
+$ListTemplate = Template\Factory::build('category/index');
+$ListTemplate->set('LIST', [
+	'CATEGORIES' => $templates ?? []
+]);
+
+$ListTemplate->set('PAGINATION', [
+	'THIS' => $currentSite,
+	'LAST' => $lastSite,
+	'HTML' => $PaginationTemplate
+]);
+
+$MainTemplate = Template\Factory::build('main');
+$MainTemplate->set('NAME', $Language->text('title_category_overview', $currentSite));
+$MainTemplate->set('HTML', $ListTemplate);
+echo $MainTemplate;
diff --git a/admin/category/insert.php b/admin/category/insert.php
new file mode 100644
index 0000000..f6f193e
--- /dev/null
+++ b/admin/category/insert.php
@@ -0,0 +1,80 @@
+<?php
+#===============================================================================
+# DEFINE: Administration
+#===============================================================================
+const ADMINISTRATION = TRUE;
+const AUTHENTICATION = TRUE;
+
+#===============================================================================
+# INCLUDE: Initialization
+#===============================================================================
+require '../../core/application.php';
+
+#===============================================================================
+# Get repositories
+#===============================================================================
+$CategoryRepository = Application::getRepository('Category');
+
+#===============================================================================
+# Instantiate new Category entity
+#===============================================================================
+$Category = new ORM\Entities\Category;
+
+#===============================================================================
+# Check for insert request
+#===============================================================================
+if(HTTP::issetPOST('parent', 'slug', 'name', 'body', 'argv', 'time_insert', 'time_update', 'insert')) {
+	$Category->set('parent', HTTP::POST('parent') ?: NULL);
+	$Category->set('slug', HTTP::POST('slug') ?: generateSlug(HTTP::POST('name')));
+	$Category->set('name', HTTP::POST('name') ?: NULL);
+	$Category->set('body', HTTP::POST('body') ?: NULL);
+	$Category->set('argv', HTTP::POST('argv') ?: NULL);
+	$Category->set('time_insert', HTTP::POST('time_insert') ?: date('Y-m-d H:i:s'));
+	$Category->set('time_update', HTTP::POST('time_update') ?: date('Y-m-d H:i:s'));
+
+	if(HTTP::issetPOST(['token' => Application::getSecurityToken()])) {
+		try {
+			if($CategoryRepository->insert($Category)) {
+				HTTP::redirect(Application::getAdminURL('category/'));
+			}
+		} catch(PDOException $Exception) {
+			$messages[] = $Exception->getMessage();
+		}
+	}
+
+	else {
+		$messages[] = $Language->text('error_security_csrf');
+	}
+}
+
+#===============================================================================
+# Generate category list
+#===============================================================================
+foreach($CategoryRepository->getAll([], 'name ASC') as $_Category) {
+	$categoryList[] = [
+		'ID' => $_Category->getID(),
+		'NAME' => $_Category->get('name'),
+		'PARENT' => $_Category->get('parent'),
+	];
+}
+
+#===============================================================================
+# Build document
+#===============================================================================
+$FormTemplate = Template\Factory::build('category/form');
+$FormTemplate->set('FORM', [
+	'TYPE' => 'INSERT',
+	'INFO' => $messages ?? [],
+	'DATA' => array_change_key_case($Category->getAll(), CASE_UPPER),
+	'CATEGORY_LIST' => $categoryList ??  [],
+	'CATEGORY_TREE' => generateCategoryDataTree($categoryList ?? []),
+	'TOKEN' => Application::getSecurityToken()
+]);
+
+$InsertTemplate = Template\Factory::build('category/insert');
+$InsertTemplate->set('HTML', $FormTemplate);
+
+$MainTemplate = Template\Factory::build('main');
+$MainTemplate->set('NAME', $Language->text('title_category_insert'));
+$MainTemplate->set('HTML', $InsertTemplate);
+echo $MainTemplate;
diff --git a/admin/category/update.php b/admin/category/update.php
new file mode 100644
index 0000000..7cee587
--- /dev/null
+++ b/admin/category/update.php
@@ -0,0 +1,84 @@
+<?php
+#===============================================================================
+# DEFINE: Administration
+#===============================================================================
+const ADMINISTRATION = TRUE;
+const AUTHENTICATION = TRUE;
+
+#===============================================================================
+# INCLUDE: Initialization
+#===============================================================================
+require '../../core/application.php';
+
+#===============================================================================
+# Get repositories
+#===============================================================================
+$CategoryRepository = Application::getRepository('Category');
+
+#===============================================================================
+# Throw 404 error if category could not be found
+#===============================================================================
+if(!$Category = $CategoryRepository->find(HTTP::GET('id'))) {
+	Application::error404();
+}
+
+#===============================================================================
+# Check for update request
+#===============================================================================
+if(HTTP::issetPOST('parent', 'slug', 'name', 'body', 'argv', 'time_insert', 'time_update', 'update')) {
+	$Category->set('slug', HTTP::POST('slug') ?: generateSlug(HTTP::POST('name')));
+	$Category->set('name', HTTP::POST('name') ?: NULL);
+	$Category->set('body', HTTP::POST('body') ?: NULL);
+	$Category->set('argv', HTTP::POST('argv') ?: NULL);
+	$Category->set('time_insert', HTTP::POST('time_insert') ?: date('Y-m-d H:i:s'));
+	$Category->set('time_update', HTTP::POST('time_update') ?: date('Y-m-d H:i:s'));
+
+	# Modify parent field only if it is not a self-reference
+	if(HTTP::POST('parent') != $Category->getID()) {
+		$Category->set('parent', HTTP::POST('parent') ?: NULL);
+	}
+
+	if(HTTP::issetPOST(['token' => Application::getSecurityToken()])) {
+		try {
+			$CategoryRepository->update($Category);
+		} catch(PDOException $Exception) {
+			$messages[] = $Exception->getMessage();
+		}
+	}
+
+	else {
+		$messages[] = $Language->text('error_security_csrf');
+	}
+}
+
+#===============================================================================
+# Generate category list
+#===============================================================================
+foreach($CategoryRepository->getAll([], 'name ASC') as $_Category) {
+	$categoryList[] = [
+		'ID' => $_Category->getID(),
+		'NAME' => $_Category->get('name'),
+		'PARENT' => $_Category->get('parent'),
+	];
+}
+
+#===============================================================================
+# Build document
+#===============================================================================
+$FormTemplate = Template\Factory::build('category/form');
+$FormTemplate->set('FORM', [
+	'TYPE' => 'UPDATE',
+	'INFO' => $messages ?? [],
+	'DATA' => array_change_key_case($Category->getAll(), CASE_UPPER),
+	'CATEGORY_LIST' => $categoryList ??  [],
+	'CATEGORY_TREE' => generateCategoryDataTree($categoryList ?? []),
+	'TOKEN' => Application::getSecurityToken()
+]);
+
+$InsertTemplate = Template\Factory::build('category/update');
+$InsertTemplate->set('HTML', $FormTemplate);
+
+$MainTemplate = Template\Factory::build('main');
+$MainTemplate->set('NAME', $Language->text('title_category_update'));
+$MainTemplate->set('HTML', $InsertTemplate);
+echo $MainTemplate;
diff --git a/admin/post/insert.php b/admin/post/insert.php
index b4404fa..bad55ae 100644
--- a/admin/post/insert.php
+++ b/admin/post/insert.php
@@ -13,6 +13,7 @@ require '../../core/application.php';
 #===============================================================================
 # Get repositories
 #===============================================================================
+$CategoryRepository = Application::getRepository('Category');
 $PostRepository = Application::getRepository('Post');
 $UserRepository = Application::getRepository('User');
 
@@ -24,7 +25,8 @@ $Post = new ORM\Entities\Post;
 #===============================================================================
 # Check for insert request
 #===============================================================================
-if(HTTP::issetPOST('user', 'slug', 'name', 'body', 'argv', 'time_insert', 'time_update', 'insert')) {
+if(HTTP::issetPOST('category', 'user', 'slug', 'name', 'body', 'argv', 'time_insert', 'time_update', 'insert')) {
+	$Post->set('category', HTTP::POST('category') ?: NULL);
 	$Post->set('user', HTTP::POST('user'));
 	$Post->set('slug', HTTP::POST('slug') ?: generateSlug(HTTP::POST('name')));
 	$Post->set('name', HTTP::POST('name') ?: NULL);
@@ -60,6 +62,17 @@ foreach($UserRepository->getAll([], 'fullname ASC') as $User) {
 }
 
 #===============================================================================
+# Generate category list
+#===============================================================================
+foreach($CategoryRepository->getAll([], 'name ASC') as $Category) {
+	$categoryList[] = [
+		'ID' => $Category->getID(),
+		'NAME' => $Category->get('name'),
+		'PARENT' => $Category->get('parent'),
+	];
+}
+
+#===============================================================================
 # Build document
 #===============================================================================
 $FormTemplate = Template\Factory::build('post/form');
@@ -68,6 +81,8 @@ $FormTemplate->set('FORM', [
 	'INFO' => $messages ?? [],
 	'DATA' => array_change_key_case($Post->getAll(), CASE_UPPER),
 	'USER_LIST' => $userList ??  [],
+	'CATEGORY_LIST' => $categoryList ?? [],
+	'CATEGORY_TREE' => generateCategoryDataTree($categoryList ?? []),
 	'TOKEN' => Application::getSecurityToken()
 ]);
 
diff --git a/admin/post/update.php b/admin/post/update.php
index 3e4d0ab..e68b3ec 100644
--- a/admin/post/update.php
+++ b/admin/post/update.php
@@ -13,6 +13,7 @@ require '../../core/application.php';
 #===============================================================================
 # Get repositories
 #===============================================================================
+$CategoryRepository = Application::getRepository('Category');
 $PostRepository = Application::getRepository('Post');
 $UserRepository = Application::getRepository('User');
 
@@ -26,7 +27,8 @@ if(!$Post = $PostRepository->find(HTTP::GET('id'))) {
 #===============================================================================
 # Check for update request
 #===============================================================================
-if(HTTP::issetPOST('user', 'slug', 'name', 'body', 'argv', 'time_insert', 'time_update', 'update')) {
+if(HTTP::issetPOST('category', 'user', 'slug', 'name', 'body', 'argv', 'time_insert', 'time_update', 'update')) {
+	$Post->set('category', HTTP::POST('category') ?: NULL);
 	$Post->set('user', HTTP::POST('user'));
 	$Post->set('slug', HTTP::POST('slug') ?: generateSlug(HTTP::POST('name')));
 	$Post->set('name', HTTP::POST('name') ?: NULL);
@@ -60,6 +62,17 @@ foreach($UserRepository->getAll([], 'fullname ASC') as $User) {
 }
 
 #===============================================================================
+# Generate category list
+#===============================================================================
+foreach($CategoryRepository->getAll([], 'name ASC') as $Category) {
+	$categoryList[] = [
+		'ID' => $Category->getID(),
+		'NAME' => $Category->get('name'),
+		'PARENT' => $Category->get('parent'),
+	];
+}
+
+#===============================================================================
 # Build document
 #===============================================================================
 $FormTemplate = Template\Factory::build('post/form');
@@ -68,6 +81,8 @@ $FormTemplate->set('FORM', [
 	'INFO' => $messages ?? [],
 	'DATA' => array_change_key_case($Post->getAll(), CASE_UPPER),
 	'USER_LIST' => $userList ??  [],
+	'CATEGORY_LIST' => $categoryList ?? [],
+	'CATEGORY_TREE' => generateCategoryDataTree($categoryList ?? []),
 	'TOKEN' => Application::getSecurityToken()
 ]);