-- -------------------------------------------------------- -- Host: 127.0.0.1 -- Server version: 10.4.28-MariaDB - mariadb.org binary distribution -- Server OS: Win64 -- HeidiSQL Version: 12.5.0.6677 -- -------------------------------------------------------- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET NAMES utf8 */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- Dumping database structure for media_cms CREATE DATABASE IF NOT EXISTS `media_cms` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */; USE `media_cms`; -- Dumping structure for procedure media_cms.delete_entry DELIMITER // CREATE PROCEDURE `delete_entry`( IN `entry_id` INT ) COMMENT 'Sets a value on "deleted_at" column for an entry (used in EntryController.php)' BEGIN UPDATE entries SET deleted_at = current_timestamp WHERE id = entry_id; END// DELIMITER ; -- Dumping structure for procedure media_cms.delete_file DELIMITER // CREATE PROCEDURE `delete_file`( IN `file_id` INT ) COMMENT 'Sets a value on "deleted_at" column for a file (used in FileController.php)' BEGIN UPDATE files SET deleted_at = current_timestamp WHERE id = file_id; END// DELIMITER ; -- Dumping structure for table media_cms.emails CREATE TABLE IF NOT EXISTS `emails` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Email id', `email` varchar(50) NOT NULL DEFAULT '' COMMENT 'User''s email', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores all user emails.'; -- Dumping data for table media_cms.emails: ~4 rows (approximately) DELETE FROM `emails`; INSERT INTO `emails` (`id`, `email`) VALUES (6, 'b@b.b'), (8, '12345'), (11, 'a@a.aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'), (12, '2@2.2'), (13, 'b@a.a'); -- Dumping structure for table media_cms.entries CREATE TABLE IF NOT EXISTS `entries` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entry id', `topic_id` int(10) unsigned NOT NULL COMMENT 'Topic id', `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Entry''s url name', `title` varchar(255) NOT NULL COMMENT 'Entry''s title', `content` text DEFAULT NULL COMMENT 'Entry''s content', `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Creation date', `edited_at` datetime DEFAULT NULL ON UPDATE current_timestamp() COMMENT 'Last edited', `deleted_at` datetime DEFAULT NULL COMMENT 'Deletion date', PRIMARY KEY (`id`), KEY `fk_topic_id` (`topic_id`), CONSTRAINT `fk_topic_id` FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores all entry data. Name is for the url part while title is to display the entry''s name.'; -- Dumping data for table media_cms.entries: ~16 rows (approximately) DELETE FROM `entries`; INSERT INTO `entries` (`id`, `topic_id`, `name`, `title`, `content`, `created_at`, `edited_at`, `deleted_at`) VALUES (3, 1, 'Formatting_stuff', 'Formatting stuff', '# Heading 1\n{| content-list |}\n## Heading 2\n### Heading 3\n#### Heading 4\n##### Heading 5\n###### Heading 6\n\n\'\'italic\'\'\n==redacted==\n**bold**\n__underlined__\n~~crossed out~~\n\n```code```\n\n> List\n>> Indented list\n>>> yo\n\n[[link="https://www.w3schools.com/tags/tag_audio.asp"]]\n[[link="https://www.w3schools.com/tags/tag_audio.asp"|text="link"]]\n\n[[file="1759314153_41e38eac3d205a355258.jpg"|text="**img of floppa**"|style="width:200px"]]\n[[file="1759314243_533f50b6de29b8d10b35.mp3"|style="width: 400px; background-color: yellow"|text="Audio file or smt"]]\n\n{{style="color: blue"|text="styled text"}}\n\n

ghgassgf

\n\n{| borders\n|-\n!! Heading 1\n!! Heading 2\n|-\n|| Cell 1 || \n|| Cell 2\n|-\n|| Cell 3 whaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaat\n|| Cell 4\n|}\n\n{| borders style="width: 800px"\n|-\n!! {80%} Heading 1\n!! {20%} Heading 2\n|-\n|| Cell 1 \n|| [[file="1759314243_533f50b6de29b8d10b35.mp3"|style="width: 400px; background-color: yellow"|text="Audio file or smt"]]\n|-\n|| Cell 3\n|| Cell 4\n|}\n', '2025-09-16 14:14:23', '2025-12-17 14:36:40', NULL), (6, 2, 'Prezentacija', 'Prezentācija', '[[style="font-size: 20px"|text="Autors: Kārlis Grīnvalds 410. grupas izglītojamais"]]\n\n{| content-list |}\n\n# Projekts\n**Mēdiju satura pārvaldības sistēmas prototipa izstrāde, izmantojot Vikipēdijas principus.**\n\n# Kas bija plānots\n**Projekta mērķis** ir izstrādāt Vikipēdijas prototipu, kurā lietotāji varēs apkopot un apskatīt informāciju par jebkuru mēdiju žanru – žurnāli, māksla, video, grāmatas, stāsti, u.t.t.\n**Uzdevumi:**\n> Izstrādāt autentificēšanās iespēju;\n> Izstrādāt tematu un rakstu lapas izveides un rediģēšanas iespējas;\n> Nodrošināt rakstu meklēšanu;\n> Nodrošināt drošu datu glabāšanu datubāzē;\n> Izstrādāt administrēšanas paneli.\n\n# Kas tika izdarīts\n\n**Autentificēšanās:**\n> Lietotāji var izveidot profilus.\n> Lietotāji var ierakstīties sistēma un izrakstīties.\n\n**Lapu izveides un rediģēšanas iespējas:**\n> Šīs darbības var veikt tie lietotāji, kuri ir ierakstījušies sistēmā.\n> Tematus var izveidot.\n> Tematu saturu var rediģēt.\n> Var izveidot jaunus rakstus, rediģēt un arī izdzēst tos.\n\n**Rakstu meklēšana:**\n> Lietotājs var uzmeklēt rakstus.\n\n**Droša datu glabāšana datubāzē:**\n> Lietotāju dati ir sadalīti vairākās tabulās.\n> Lietotāju paroles tiek nošifrētas.\n> Pastāv aizsardzība pret SQL injekcijām.\n> Aizmugursistēmā tikai ir atsauces uz datubāzes funkcijām/procedurām.\n\n**Administrēšanas panelis:**\n> Lietotāji, kuri ir ierakstījušies sistēmā, var piekļūt tam.\n> Var pārskatīt visus rakstus un augšupielādētos failus, kā arī tos pārvaldīt.', '2025-12-17 10:10:31', '2026-04-08 12:21:55', NULL), (7, 2, 'load_content.js', 'load_content.js', 'You can [[link="/test/load_content.js?a=edit"|text="edit"]] this page here\n{| side-content\n|-\n|| {{width=100px; colspan=2}} [[file="1776071571_664f2e3097d407f1194c.png"|style="width: 300px"]]\n|-\n!! Name:\n|| Ščūnis yeeeeeeeeeeeeeeeee\n|}\n\n&&& cool comment\n{| content-list |}\n\ntext\n# \'\'Heading 1\'\'\ntext\n## ==Heading 2==\ntext\n### **Heading 3**\ntext\n#### __Heading 4__\ntext\n##### ~~Heading 5~~\ntext\n###### Heading 6\n\n\'\'text\'\'\n==text==\n**text**\n__text__\n~~text~~\n\n# [[style="color: blue"|text="styled text"]]\n\nLorem ipsum __~~dolor sit **amet, co**nsectetur~~ adipiscing el==it, sed do eiusmod tempor incididunt== ut \'\'labore et dolore magna\'\' aliqua__.\n\nThis is ```code text```\nIt should disable everything ```check\n\nLorem ipsum __~~dolor sit **amet, co**nsectetur~~ adipiscing el==it, sed do eiusmod tempor incididunt== ut \'\'labore et dolore magna\'\' aliqua__.\n\nshould be it```\n\n**LIST!!!!!!!!**\n> text\n> text\n>> text\n>>> text\n> {{style="color: blue"|text="styled **text**"}}\n>>>> text\n>> text\n> > what\n\n[[link="dadada"|text="ba {{style="color: blue"|text="bink"}}"]]\n\n{| borders\n|-\n|| [[file="1765878110_f35932e2fc15785a83c9.png"|text="text [[style="color: blue"|text="styled text"]]"|style="width:200px"]]\n|| Lorem ipsum __~~dolor sit **amet, co**nsectetur~~ adipiscing el==it, sed do eiusmod tempor incididunt== ut \'\'labore et dolore magna\'\' aliqua__.\n|}', '2025-12-17 10:28:18', '2026-04-13 12:13:37', NULL), (8, 2, 'test_2', 'test 2', 'You can [[link="/test/test_2?a=edit"|text="edit"]] this page here\n\n\n\n{| borders style="background-color: red"\n|-\n!! Heading 1\n!! Heading 2\n!! Heading 3\n|-\n|| huh\n> text\n>> dbdbdbdb\n>> > text\n|| {{colspan=2}} yup\n|-\n|| Cell 1\neare\n|| Cell 2\n|| Cell 3\n|}\n\nLorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.', '2026-01-08 14:20:32', '2026-02-03 13:08:51', NULL), (9, 1, 'Headings', 'Headings', 'You can add a heading by adding a hash tag in the beginning of the line. By adding more, you can have sub headings.\n\n# Heading 1\n## Heading 2\n### Heading 3\n#### Heading 4\n##### Heading 5\n```\n# Heading 1\n## Heading 2\n### Heading 3\n#### Heading 4\n##### Heading 5\n```\n\nYou can also add style to headings.\n\n# \'\'Italic Heading\'\'\n```# \'\'Italic Heading\'\'```', '2026-02-02 19:13:31', '2026-02-23 09:25:12', NULL), (10, 1, 'Text', 'Text', '{| content-list |}\n\n# Different style of text\nYou can add text, by inputting anything in the content input field. Text can be formatted and styled in any way.\nThey can be mixed together like this:\nLorem ipsum __~~dolor sit **amet, co**nsectetur~~ adipiscing el==it, sed do eiusmod tempor incididunt== ut \'\'labore et dolore magna\'\' aliqua__.\n```Lorem ipsum __~~dolor sit **amet, co**nsectetur~~ adipiscing el==it, sed do eiusmod tempor incididunt== ut \'\'labore et dolore magna\'\' aliqua__.```\n\n**Note: text won\'t be formatted if the special characters aren\'t on the same line**\n\n## Bold\nYou can make text bold by enclosing it in double star symbols - **\n**This is bold**\n```**This is bold**```\n\n## Italic\nYou can make text italic by enclosing it in double single quotes - \'\'\n\'\'This is italic\'\'\n```\'\'This is italic\'\'```\n\n## Underlined\nYou can underline text by enclosing it in double underscore characters - __\n__This is underlined__\n```__This is underlined__```\n\n## Crossed out\nYou can cross out text by enclosing it in double tilde characters - ~~\n~~This is crossed out~~\n```~~This is crossed out~~```\n\n## Redacted\nYou can redact text by enclosing it in double equal symbols - ==\nThis is ==redacted==\n```This is ==redacted==```\n\n# Code block\nEnclosing text in 3 backtick (`) characters will make it look like code. They don\'t need to be on the same line to create code block; It can start from one line and end on another. As previously showcased, it can disable any formatting.\n```\nCode block\n```', '2026-02-02 19:13:39', '2026-04-08 09:46:25', NULL), (11, 1, 'Lists', 'Lists', 'To create an unordered list, you need to add greater than symbol (>) at the beginning of a line followed by a space. To indent it, add more.\n\nThis is a list:\n> item 1\n>> item 2\n>>> item 3\n>> item 4\n> item 5\n> item 6\n>>>>>> item 7\n> item 8\n\n```This is a list:\n> item 1\n>> item 2\n>>> item 3\n>> item 4\n> item 5\n> item 6\n>>>>>> item 7\n> item 8\n```\n\nOrdered lists can only be manually made.\n1. item 1\n1.1. item 2\n2. item 3\n\n```1. item 1\n 1.1. item 2\n2. item 3\n```', '2026-02-02 19:14:05', '2026-03-02 09:37:56', NULL), (12, 1, 'Tables', 'Tables', '{| content-list |}\n\n# Making a table\n\nTo create a table, you need to add "{|" and "|}" on separate lines. Inserting "|-" on a different line will add a row; They need to be the only symbols on that line in order to make a table row. Starting a line with "||" then adding space will create a table cell. "!!" will create table heading.\n\nThe result looks like this:\n{|\n|-\n!! Heading 1\n|-\n|| Cell 1\n|-\n|| Cell 2\n|}\n```\n{|\n|-\n!! Heading 1\n|-\n|| Cell 1\n|-\n|| Cell 2\n|}\n```\n# Adding style to tables\nYou can add some style to your tables after "{|"\n\n## Borders\nYou can add borders to a table by adding \'\'borders\'\' after "{|".\n{| borders\n|-\n!! Heading 1\n|-\n|| Cell 1\n|}\n```\n{| borders\n|-\n!! Heading 1\n|-\n|| Cell 1\n|}\n```\n\n## Custom CSS\n\'\'style=""\'\' will allow you to add custom CSS to your table. View [[link="https://www.w3schools.com/css/default.asp"|text="W3Schools page"]] about CSS. Currently, there isn\'t a way to add custom table border colors.\n{| borders style="background-color: wheat;"\n|-\n!! Heading 1\n|-\n|| Cell 1\n|}\n```\n{| borders style="background-color: wheat;"\n|-\n!! Heading 1\n|-\n|| Cell 1\n|}\n```\n\n# Customize table cells\nYou can add double curly brackets - "{{}}" - after creating a table heading or cell. This will allow you to customize its width and span. To add both width, rowspan, colspan, you need to divide them with a semicolon (;).\n\n## Table cell width\nAdd "width=" and a valid value. View [[link="https://www.w3schools.com/cssref/css_units.php"|text="W3Schools page"]] about CSS units.\n{| borders\n|-\n|| {{width=200px}} Cell 1\n|| Cell 2\n|-\n|| Cell 3\n|| Cell 4\n|}\n```\n{| borders\n|-\n|| {{width=200px}} Cell 1\n|| Cell 2\n|-\n|| Cell 3\n|| Cell 4\n|}\n```\n\n## Span\nTo expand the cell, use "rowspan=" or "colspan=" followed by an integer number.\n{| borders\n|-\n|| {{rowspan=2}} Cell 1\n|| Cell 2\n|| {{colspan=2}} Cell 3\n|-\n|| Cell 4\n|| Cell 5\n|| Cell 6\n|}\n```\n{| borders\n|-\n|| {{rowspan=2}} Cell 1\n|| Cell 2\n|| {{colspan=2}} Cell 3\n|-\n|| Cell 4\n|| Cell 5\n|| Cell 6\n|}\n```\n\n# Custom tables\nThese are special types of tables that can only be used once in an entry.\n\n## Content list table\nBy inserting "{| content-list |}", you can add a content list to an entry.\n\n## Side table\nBy adding \'\'side-content\'\' after "{|", you can make a side table. It has a custom style added and cannot be changed. It will appear at the top of the page.\n```\n{| side-content\n|-\n|| Table cell\n|}\n```', '2026-02-02 19:14:13', '2026-03-02 10:36:20', NULL), (13, 1, 'Links_and_files', 'Links and files', 'Basic link and file syntax - [[link/file|style/text|style/text]]. First must be a link or a file url, second and third can be text or style.\n\n{| content-list |}\n\n# Links\nYou can create a link by enclosing \'\'link="URL"\'\' in double square brackets.\nLink to [[link="https://www.youtube.com/"]].\n```[[link="https://www.youtube.com/"]]```\n\nTo add custom text, insert \'\'text=""\'\' and add text inside the qoutes.\nLink to [[link="https://www.youtube.com/"|text="Youtube main page"]].\n```[[link="https://www.youtube.com/"|text="Youtube main page"]]```\n\nTo add custom CSS, insert \'\'style=""\'\' and add CSS code inside the qoutes.\nVisit [[link="https://www.w3schools.com/Css/"|style="color: blue; font-size: 20px"]] to learn about CSS.\n```[[link="https://www.w3schools.com/Css/"|style="color: blue; font-size: 20px"]]```\n\nYou can add both text and style to your link.\n```[[link="URL"|style="style"|text="text"]]```\n\n# Files\nSame as creating a link, but replace \'\'link\'\' with \'\'file\'\'. Files must be uploaded before they can be used. You can find the file name on administrator panel page.\nExample:\n```[[file="NAME"]]```\n\nSupported file types:\n> gif\n> jpeg\n> jpg\n> png\n> webp\n> mp3\n> wav\n> ogg\n\n## Image\nClicking on an image will transfer you to a different page where you can view the file.\n[[file="1770108492_22ede878e680d6ae0809.png"]]\n\n## Audio\nAudio files have a control panel.\n[[file="1770108516_318c2bdaf1d5ab146dfc.mp3"]]\n\n## Captions\nBy adding \'\'text=""\'\', you can add captions to your file. For images caption will show below it, but for audio files - above.\n[[file="1770108492_22ede878e680d6ae0809.png"|text="Cool image caption"]]\n```[[file="NAME"|text="text"]]```\n\nYou can also add links.\n[[file="1770108526_abaf7d63a46fcc1b4c25.wav"|text="[[link="/wiki/Page:Main"|text="Return to main page"]]"]]\n```[[file="URL"|text="[[link="URL"|text="text"]]"]]```\n\n## Custom CSS\nYou can custom CSS to a file by adding \'\'style=""\'\'. It\'s useful if the uploaded file\'s resolution is big. Visit [[link="https://www.w3schools.com/Css/"|text="W3Schools"]] to learn about CSS.\n[[file="1770108503_b953cb4e8467598d6ef2.jpg"|style="width: 200px; border: 4px dotted red"]]\n```[[file="URL"|style="width: 200px; border: 4px dotted red"]]```', '2026-02-02 19:14:21', '2026-02-03 11:09:49', NULL), (14, 1, 'Custom_style', 'Custom style', '', '2026-02-02 19:14:33', NULL, NULL), (15, 1, 'Miscellaneous', 'Miscellaneous', 'This page isn\'t complete\n\nComment text - doesn\'t appear when viewing an entry.\n```&&& comment```\n\nStyle\n```[[style="style"|text="text"]]```', '2026-02-02 19:14:46', '2026-03-02 10:41:34', NULL), (16, 2, 'fas_gte_gsht_rgd_jrtgxd', 'fas gte gsht rgd jrtgxd', 'Testing', '2026-02-02 21:09:40', NULL, NULL), (17, 2, 'uytsfd_jga_liudgh_fad', 'uytsfd jga liudgh fad', 'Testing', '2026-02-02 21:09:52', NULL, NULL), (18, 2, 'pqwe_hrs_agjtu_sgdth', 'pqwe hrs agjtu sgdth', 'Testing', '2026-02-02 21:10:02', NULL, NULL), (19, 2, 'fask_hr_fsmg_rptrm_fs', 'fask hr fsmg rptrm fs', 'Testing\n> > te', '2026-02-02 21:10:15', '2026-02-23 09:00:21', NULL), (20, 9, 'gdfgdfd', 'gdfgdfd', '', '2026-04-08 13:59:47', NULL, NULL); -- Dumping structure for table media_cms.files CREATE TABLE IF NOT EXISTS `files` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'File id', `topic_id` int(10) unsigned NOT NULL COMMENT 'Topic id', `name` varchar(255) NOT NULL COMMENT 'Randomly generated file name', `description` text DEFAULT NULL COMMENT 'File''s description', `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Upload date', `edited_at` datetime DEFAULT NULL ON UPDATE current_timestamp() COMMENT 'Last edited', `deleted_at` datetime DEFAULT NULL COMMENT 'Deletion date', PRIMARY KEY (`id`), KEY `fk_topic` (`topic_id`), CONSTRAINT `fk_topic` FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores all uploaded file data. Topic id is needed to make that uploaded files only be accessible on one topic. Does not store the actual uploaded file.'; -- Dumping data for table media_cms.files: ~6 rows (approximately) DELETE FROM `files`; INSERT INTO `files` (`id`, `topic_id`, `name`, `description`, `created_at`, `edited_at`, `deleted_at`) VALUES (12, 1, '1770108492_22ede878e680d6ae0809.png', '', '2026-02-03 10:48:12', NULL, NULL), (13, 1, '1770108503_b953cb4e8467598d6ef2.jpg', '', '2026-02-03 10:48:23', NULL, NULL), (14, 1, '1770108516_318c2bdaf1d5ab146dfc.mp3', '', '2026-02-03 10:48:36', NULL, NULL), (15, 1, '1770108526_abaf7d63a46fcc1b4c25.wav', '', '2026-02-03 10:48:46', NULL, NULL), (16, 1, '1770108534_660a7d2b8fbd11ba089f.ogg', '', '2026-02-03 10:48:54', NULL, NULL), (17, 2, '1776071571_664f2e3097d407f1194c.png', 'ye', '2026-04-13 12:12:51', NULL, NULL), (18, 8, '1776094558_4432ac508d08de4bf3e8.png', '', '2026-04-13 18:35:58', '2026-04-13 18:47:20', '2026-04-13 18:47:20'); -- Dumping structure for procedure media_cms.find_email DELIMITER // CREATE PROCEDURE `find_email`( IN `email` TEXT ) COMMENT 'Input email, outputs email''s id (used in UserController.php)' BEGIN SELECT e.id FROM emails AS e WHERE e.email = email; END// DELIMITER ; -- Dumping structure for procedure media_cms.find_user DELIMITER // CREATE PROCEDURE `find_user`( IN `email` INT, IN `password` INT ) COMMENT 'Input email id and password id, outputs user id and username (used in UserController.php)' BEGIN SELECT u.id, u.username FROM users AS u WHERE u.email = email AND u.password = PASSWORD; END// DELIMITER ; -- Dumping structure for procedure media_cms.find_user_by_name DELIMITER // CREATE PROCEDURE `find_user_by_name`( IN `name` VARCHAR(30) ) COMMENT 'Input username, outputs user id (used in UserController.php)' BEGIN SELECT u.id FROM users AS u WHERE u.username = NAME; END// DELIMITER ; -- Dumping structure for procedure media_cms.get_entries_by_topic DELIMITER // CREATE PROCEDURE `get_entries_by_topic`( IN `topic_id` INT ) COMMENT 'Finds all entries data with the same topic id (used in EntryController.php)' BEGIN SELECT e.id, e.name, e.title, DATE_FORMAT(e.created_at, "%b %d, %Y at %H:%i:%s") AS created_at, DATE_FORMAT(e.edited_at, "%b %d, %Y at %H:%i:%s") AS edited_at FROM entries AS e WHERE e.topic_id = topic_id AND ISNULL(e.deleted_at); END// DELIMITER ; -- Dumping structure for procedure media_cms.get_entry DELIMITER // CREATE PROCEDURE `get_entry`( IN `topic_name` VARCHAR(50), IN `entry_name` VARCHAR(50) ) COMMENT 'Input topic and entry name, outputs entry data (used in EntryController.php)' BEGIN SELECT e.id, e.title, e.content, DATE_FORMAT(e.created_at, "%b %d, %Y at %H:%i:%s") AS created_at, DATE_FORMAT(e.edited_at, "%b %d, %Y at %H:%i:%s") AS edited_at FROM entries AS e LEFT JOIN topics AS t ON t.id = e.topic_id WHERE t.name = topic_name AND e.name = entry_name AND ISNULL(e.deleted_at); END// DELIMITER ; -- Dumping structure for procedure media_cms.get_file DELIMITER // CREATE PROCEDURE `get_file`( IN `givenfilename` VARCHAR(255) ) COMMENT 'Input file name, outputs file data (used in FileController.php)' BEGIN SELECT f.id, t.name AS topic_name, f.name, f.description, DATE_FORMAT(f.created_at, "%b %d, %Y at %H:%i:%s") AS created_at, DATE_FORMAT(f.edited_at, "%b %d, %Y at %H:%i:%s") AS edited_at FROM files AS f LEFT JOIN topics AS t ON t.id = f.topic_id WHERE f.name = givenfilename AND ISNULL(f.deleted_at); END// DELIMITER ; -- Dumping structure for procedure media_cms.get_password_using_email DELIMITER // CREATE PROCEDURE `get_password_using_email`( IN `emailID` INT ) COMMENT 'Input email id, outputs user''s password data (used in UserController.php)' BEGIN SELECT p.id, p.`password`, p.salt FROM users AS u LEFT JOIN passwords AS p ON p.id = u.`password` WHERE u.email = emailID; END// DELIMITER ; -- Dumping structure for procedure media_cms.get_permissions DELIMITER // CREATE PROCEDURE `get_permissions`( IN `topic_name` VARCHAR(50), IN `user_id` INT ) COMMENT 'Input topic name and user id, outputs user''s permissions (used in TransferController.php)' BEGIN SELECT t.owner, p.`create`, p.upload, p.edit_entries, p.edit_files, p.edit_topic, p.delete_entries, p.delete_files FROM topics AS t LEFT JOIN permissions AS p ON p.topic_id = t.id WHERE t.name = topic_name AND ((t.owner = user_id OR p.user_id = user_id) OR t.id = p.topic_id AND ISNULL(p.user_id)); END// DELIMITER ; -- Dumping structure for procedure media_cms.get_permissions_topics DELIMITER // CREATE PROCEDURE `get_permissions_topics`( IN `user_id` INT ) COMMENT 'Input user id, outputs all permissions for topics (used in TopicController.php)' BEGIN SELECT t.name AS t_name, t.title AS t_title, t.public AS t_public, t.owner AS t_owner, p.`read`, p.`create`, p.upload, p.edit_entries, p.edit_files, p.edit_topic, p.delete_entries, p.delete_files FROM topics AS t LEFT JOIN permissions AS p ON p.topic_id = t.id WHERE user_id = t.owner OR (p.user_id = user_id AND p.`read` = 1) OR (t.public = 0 AND ISNULL(p.topic_id) = FALSE AND ISNULL(p.user_id)) ORDER BY t.owner ASC, t.public DESC, t.name ASC; END// DELIMITER ; -- Dumping structure for procedure media_cms.get_permission_read DELIMITER // CREATE PROCEDURE `get_permission_read`( IN `topic_name` VARCHAR(50), IN `user_id` INT ) COMMENT 'Input topic name and user id, outputs permissions to view the topic (used in TransferController.php)' BEGIN SELECT t.owner, t.public, p.`read` FROM topics AS t LEFT JOIN permissions AS p ON p.topic_id = t.id WHERE CASE WHEN (user_id = 0) THEN t.name = topic_name AND t.public = 1 ELSE t.name = topic_name AND (t.owner = user_id OR t.public = 1 OR ((p.user_id = user_id OR t.id = p.topic_id) AND p.`read` = 1)) END; END// DELIMITER ; -- Dumping structure for procedure media_cms.get_public_topics DELIMITER // CREATE PROCEDURE `get_public_topics`() COMMENT 'Outputs all public topics (used in TopicController.php)' BEGIN SELECT t.id, t.name, t.title FROM topics AS t WHERE t.public = 1 AND ISNULL(t.deleted_at); END// DELIMITER ; -- Dumping structure for procedure media_cms.get_topic_by_name DELIMITER // CREATE PROCEDURE `get_topic_by_name`( IN `topic_name` VARCHAR(50) ) COMMENT 'Input topic name, outputs topic data (used in TopicController.php)' BEGIN SELECT t.id, t.title, t.content, DATE_FORMAT(t.created_at, "%b %d, %Y at %H:%i:%s") AS created_at, DATE_FORMAT(t.edited_at, "%b %d, %Y at %H:%i:%s") AS edited_at FROM topics AS t WHERE t.name = topic_name AND ISNULL(t.deleted_at); END// DELIMITER ; -- Dumping structure for procedure media_cms.get_topic_files DELIMITER // CREATE PROCEDURE `get_topic_files`( IN `topic_id` INT ) COMMENT 'Input topic id, outputs all files related to that topic (used in FileController.php)' BEGIN SELECT f.id, f.name, DATE_FORMAT(f.created_at, "%b %d, %Y at %H:%i:%s") AS created_at, DATE_FORMAT(f.edited_at, "%b %d, %Y at %H:%i:%s") AS edited_at FROM files AS f WHERE f.topic_id = topic_id AND ISNULL(f.deleted_at); END// DELIMITER ; -- Dumping structure for procedure media_cms.get_topic_id DELIMITER // CREATE PROCEDURE `get_topic_id`( IN `given_name` VARCHAR(50) ) COMMENT 'Input topic name, outputs topic id (used in TopicController.php)' BEGIN SELECT t.id FROM topics AS t WHERE t.name = given_name; END// DELIMITER ; -- Dumping structure for table media_cms.passwords CREATE TABLE IF NOT EXISTS `passwords` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Password id', `password` text NOT NULL COMMENT 'Hashed password', `salt` varchar(10) NOT NULL DEFAULT '' COMMENT 'Randomly generated string', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores user''s encrypted passwords. Salt column is used to add extra security for hashed passwords and to verify if the user''s inputted password would match with the actual password.'; -- Dumping data for table media_cms.passwords: ~4 rows (approximately) DELETE FROM `passwords`; INSERT INTO `passwords` (`id`, `password`, `salt`) VALUES (5, '$2y$10$NtIiP7/27XCn4s5uuF5R5uSM/Z3QSZtQVGGm58mmOSOx1EUKwWH0a', 'kS8pPVAbKM'), (7, '$2y$10$1Oj47u8logCJcrTcGKuVluO8opOUCsSpVjtdt9nbwf.pX3JdGYpGS', 'dBbgYUHLQh'), (10, '$2y$10$F24y5hMj2UN/rSHK/toWKuDLmAaz9.phsfxQygXeqwIiO1vcxAKRe', 'owpeF1ZG38'), (11, '$2y$10$iuI1S8KrB7B0aoWFL0tD/eS/4ELF60RcLvXfe0DuDcrsjc.eWDc6a', 'rQnupYhIcB'), (12, '$2y$10$vVcQKjXi4Yd6WYxJVRfUA.dKVJRbT7qvHfDlgnIJSIeg4dgJV/qVa', 'WtUPmFu156'); -- Dumping structure for table media_cms.permissions CREATE TABLE IF NOT EXISTS `permissions` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Role id', `topic_id` int(10) unsigned NOT NULL COMMENT 'Topic id', `user_id` int(10) unsigned DEFAULT NULL COMMENT 'User id', `read` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Can view the topic', `create` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Can make entries', `upload` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Can upload files', `edit_entries` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Can edit entries', `edit_files` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Can edit uploaded files', `edit_topic` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Can edit topic''s main page', `delete_entries` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Can delete entries', `delete_files` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT 'Can delete files', PRIMARY KEY (`id`), KEY `FK_Role_Topic` (`topic_id`), KEY `FK_Role_User` (`user_id`), CONSTRAINT `FK_Role_Topic` FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_Role_User` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores information about user rules in different topics'; -- Dumping data for table media_cms.permissions: ~2 rows (approximately) DELETE FROM `permissions`; INSERT INTO `permissions` (`id`, `topic_id`, `user_id`, `read`, `create`, `upload`, `edit_entries`, `edit_files`, `edit_topic`, `delete_entries`, `delete_files`) VALUES (1, 4, NULL, 1, 0, 1, 0, 0, 0, 0, 0), (2, 2, NULL, 1, 1, 1, 1, 1, 1, 1, 1); -- Dumping structure for function media_cms.post_email DELIMITER // CREATE FUNCTION `post_email`(`param` TEXT ) RETURNS int(11) COMMENT 'Creates new email and returns email id (used in UserController.php)' BEGIN DECLARE emailID INT; SET emailID = (SELECT e.id FROM emails AS e WHERE e.email = param); IF(ISNULL(emailID) = TRUE) THEN INSERT INTO emails (email) VALUES (param); RETURN (SELECT e.id FROM emails AS e ORDER BY e.id DESC LIMIT 1); ELSE RETURN 0; END IF; END// DELIMITER ; -- Dumping structure for procedure media_cms.post_entry DELIMITER // CREATE PROCEDURE `post_entry`( IN `object` JSON ) COMMENT 'Creates a new entry (used in EntryController.php)' BEGIN INSERT INTO entries (topic_id, name, title, content) VALUES (JSON_VALUE(object, "$.topic_id"), JSON_VALUE(object, "$.name"), JSON_VALUE(object, "$.title"), JSON_VALUE(object, "$.content")); END// DELIMITER ; -- Dumping structure for procedure media_cms.post_file DELIMITER // CREATE PROCEDURE `post_file`( IN `object` JSON ) COMMENT 'Creates a file entry (used in FileController.php)' BEGIN INSERT INTO files (topic_id, name, description) VALUES (JSON_VALUE(object, "$.topic_id"), JSON_VALUE(object, "$.name"), JSON_VALUE(object, "$.description")); END// DELIMITER ; -- Dumping structure for function media_cms.post_password DELIMITER // CREATE FUNCTION `post_password`(`hashed` TEXT, `salt` TEXT ) RETURNS int(11) COMMENT 'Creates new password and returns password id (used in UserController.php)' BEGIN INSERT INTO passwords (PASSWORD, salt) VALUES (hashed, salt); RETURN (SELECT p.id FROM passwords AS p ORDER BY p.id DESC LIMIT 1); END// DELIMITER ; -- Dumping structure for function media_cms.post_topic DELIMITER // CREATE FUNCTION `post_topic`(`topic_owner` INT, `topic_name` VARCHAR(12), `topic_title` VARCHAR(255) ) RETURNS varchar(50) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT 'Creates a new topic and returns the topic url name (used in TopicController.php)' BEGIN INSERT INTO topics (owner, name, title) VALUES (topic_owner, topic_name, topic_title); RETURN (SELECT t.name FROM topics AS t WHERE t.owner = topic_owner AND t.name = topic_name); END// DELIMITER ; -- Dumping structure for procedure media_cms.post_user DELIMITER // CREATE PROCEDURE `post_user`( IN `object` JSON ) COMMENT 'Creates a new user (used in UserController.php)' BEGIN INSERT INTO users (username, email, PASSWORD) VALUES (JSON_VALUE(object, "$.username"), JSON_VALUE(object, "$.email"), JSON_VALUE(object, "$.password")); END// DELIMITER ; -- Dumping structure for procedure media_cms.put_entry DELIMITER // CREATE PROCEDURE `put_entry`( IN `object` JSON, IN `entry_id` INT ) COMMENT 'Updates entry''s content (used in EntryController.php)' BEGIN UPDATE entries SET NAME = JSON_VALUE(object, "$.name"), title = JSON_VALUE(object, "$.title"), content = JSON_VALUE(object, "$.content") WHERE id = entry_id; END// DELIMITER ; -- Dumping structure for procedure media_cms.put_file DELIMITER // CREATE PROCEDURE `put_file`( IN `file_id` INT, IN `file_desc` TEXT ) COMMENT 'Updates file''s description (used in FileController.php)' BEGIN UPDATE files SET description = file_desc WHERE id = file_id; END// DELIMITER ; -- Dumping structure for procedure media_cms.put_topic DELIMITER // CREATE PROCEDURE `put_topic`( IN `object` JSON, IN `topic_name` VARCHAR(50) ) COMMENT 'Updates topic content (used in TopicController.php)' BEGIN UPDATE topics SET title = JSON_VALUE(object, "$.title"), content = JSON_VALUE(object, "$.content") WHERE `name` = topic_name; END// DELIMITER ; -- Dumping structure for procedure media_cms.search_data DELIMITER // CREATE PROCEDURE `search_data`( IN `param` TEXT, IN `topic_id` INT ) COMMENT 'Input text, outputs entries with similar words (used in EntryController.php)' BEGIN SELECT e.id, e.name, e.title FROM entries AS e WHERE (LOWER(e.title) LIKE CONCAT(param, "%") OR LOWER(e.title) LIKE CONCAT("%", param, "%") OR LOWER(e.title) LIKE CONCAT("%", param)) AND e.topic_id = topic_id AND ISNULL(e.deleted_at); END// DELIMITER ; -- Dumping structure for table media_cms.topics CREATE TABLE IF NOT EXISTS `topics` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Topic id', `owner` int(10) unsigned NOT NULL COMMENT 'User id', `public` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '1 - true, 0 - false', `name` varchar(50) NOT NULL COMMENT 'Topic''s url name', `title` varchar(255) NOT NULL COMMENT 'Topic''s title', `content` text DEFAULT NULL COMMENT 'Topic''s content', `created_at` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Creation date', `edited_at` datetime DEFAULT NULL ON UPDATE current_timestamp() COMMENT 'Last edited', `deleted_at` datetime DEFAULT NULL COMMENT 'Deleted at', PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `fk_owner` (`owner`), CONSTRAINT `fk_owner` FOREIGN KEY (`owner`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores all topic data - the owner of a topic, topic url name, title and its'' content. Also has set time and date when it was created, last edited and deleted.'; -- Dumping data for table media_cms.topics: ~8 rows (approximately) DELETE FROM `topics`; INSERT INTO `topics` (`id`, `owner`, `public`, `name`, `title`, `content`, `created_at`, `edited_at`, `deleted_at`) VALUES (1, 5, 1, 'wiki', 'User guide', '> [[link="/wiki/Text"|text="Text"]]\n> [[link="/wiki/Headings"|text="Headings"]]\n> [[link="/wiki/Lists"|text="Lists"]]\n> [[link="/wiki/Tables"|text="Tables"]]\n> [[link="/wiki/Links_and_files"|text="Links and files"]]\n&&& > [[link="/wiki/Custom_style"|text="Custom style"]]\n> [[link="/wiki/Miscellaneous"|text="Miscellaneous"]]', '2025-09-16 09:09:12', '2026-04-13 13:54:21', NULL), (2, 8, 0, 'test', 'Testing', '[[link="/test/Page:Admin"]]\nyippee', '2025-12-16 11:09:43', '2026-04-13 12:46:24', NULL), (4, 5, 0, 'sdfghj', 'Testing', '[[link="/test/Page:Admin"]]\nyippee', '2026-03-07 11:46:12', '2026-04-13 12:46:24', NULL), (5, 5, 0, 'zdyviG8KWVem', 'Testing', '[[link="/test/Page:Admin"]]\nyippee', '2026-03-07 11:46:37', '2026-04-13 12:46:24', NULL), (6, 5, 0, 'kY4DwV2hyzqM', 'Testing', '[[link="/test/Page:Admin"]]\nyippee', '2026-03-07 11:47:39', '2026-04-13 12:46:24', NULL), (7, 5, 0, 'UQZOnBXl1eJN', 'Testing', '[[link="/test/Page:Admin"]]\nyippee', '2026-03-07 11:48:32', '2026-04-13 12:46:24', NULL), (8, 5, 0, 'FzrfgXTphKI8', 'Testing', '\nyippee', '2026-03-07 11:49:05', '2026-04-13 14:35:24', NULL), (9, 13, 0, 'jTwL52iWG8Zf', 'Testing', '[[link="/test/Page:Admin"]]\nyippee', '2026-04-08 13:50:03', '2026-04-13 12:46:24', NULL); -- Dumping structure for table media_cms.users CREATE TABLE IF NOT EXISTS `users` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'User id', `username` varchar(20) NOT NULL COMMENT 'User''s name', `email` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'User''s email id', `password` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'User''s password id', PRIMARY KEY (`id`), KEY `fk_email_id` (`email`), KEY `fk_password_id` (`password`), CONSTRAINT `fk_email_id` FOREIGN KEY (`email`) REFERENCES `emails` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_password_id` FOREIGN KEY (`password`) REFERENCES `passwords` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores all created user profiles. Has user id, username, email id and password id.'; -- Dumping data for table media_cms.users: ~4 rows (approximately) DELETE FROM `users`; INSERT INTO `users` (`id`, `username`, `email`, `password`) VALUES (5, 'badmin', 6, 5), (8, 'tester001', 8, 7), (11, '1111', 11, 10), (12, '2222', 12, 11), (13, 'user112', 13, 12); -- Dumping structure for procedure media_cms.verify_file DELIMITER // CREATE PROCEDURE `verify_file`( IN `givenfilename` VARCHAR(50), IN `giventopicname` VARCHAR(255) ) COMMENT 'Input topic name and file name, outputs file id (used in FileController.php)' BEGIN SELECT f.id FROM files AS f LEFT JOIN topics AS t ON t.id = f.topic_id WHERE f.name = givenfilename AND t.name = giventopicname AND ISNULL(f.deleted_at); END// DELIMITER ; /*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */; /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */; /*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;