From fefa367242de85f46250fb8da46dbae3f5545836 Mon Sep 17 00:00:00 2001 From: Thomas Lange Date: Thu, 17 Jun 2021 00:18:34 +0200 Subject: Add migrations directory This commit adds the directory core/db/migrations/ which contains all migrations that were applied to the database since the first release. --- core/db/migrations/1.sql | 3 +++ core/db/migrations/2.sql | 3 +++ core/db/migrations/3.sql | 6 ++++++ core/db/migrations/4.sql | 3 +++ core/db/migrations/5.sql | 3 +++ 5 files changed, 18 insertions(+) create mode 100644 core/db/migrations/1.sql create mode 100644 core/db/migrations/2.sql create mode 100644 core/db/migrations/3.sql create mode 100644 core/db/migrations/4.sql create mode 100644 core/db/migrations/5.sql (limited to 'core/db') diff --git a/core/db/migrations/1.sql b/core/db/migrations/1.sql new file mode 100644 index 0000000..a875d13 --- /dev/null +++ b/core/db/migrations/1.sql @@ -0,0 +1,3 @@ +ALTER TABLE `page` ADD `argv` VARCHAR(100) NULL DEFAULT NULL AFTER `body`; +ALTER TABLE `post` ADD `argv` VARCHAR(100) NULL DEFAULT NULL AFTER `body`; +ALTER TABLE `user` ADD `argv` VARCHAR(100) NULL DEFAULT NULL AFTER `body`; diff --git a/core/db/migrations/2.sql b/core/db/migrations/2.sql new file mode 100644 index 0000000..525b768 --- /dev/null +++ b/core/db/migrations/2.sql @@ -0,0 +1,3 @@ +ALTER TABLE `page` ADD UNIQUE KEY `time_insert` (`time_insert`); +ALTER TABLE `post` ADD UNIQUE KEY `time_insert` (`time_insert`); +ALTER TABLE `user` ADD UNIQUE KEY `time_insert` (`time_insert`); diff --git a/core/db/migrations/3.sql b/core/db/migrations/3.sql new file mode 100644 index 0000000..ae46ed0 --- /dev/null +++ b/core/db/migrations/3.sql @@ -0,0 +1,6 @@ +ALTER TABLE `post` ENGINE=InnoDB; +ALTER TABLE `post` DROP INDEX `body`; +ALTER TABLE `page` ADD FULLTEXT KEY `search` (`name`, `body`); +ALTER TABLE `post` ADD FULLTEXT KEY `search` (`name`, `body`); +ALTER TABLE `post` ADD CONSTRAINT `post_user` FOREIGN KEY (`user`) + REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; diff --git a/core/db/migrations/4.sql b/core/db/migrations/4.sql new file mode 100644 index 0000000..4a26c48 --- /dev/null +++ b/core/db/migrations/4.sql @@ -0,0 +1,3 @@ +ALTER TABLE `page` MODIFY `argv` VARCHAR(250); +ALTER TABLE `post` MODIFY `argv` VARCHAR(250); +ALTER TABLE `user` MODIFY `argv` VARCHAR(250); diff --git a/core/db/migrations/5.sql b/core/db/migrations/5.sql new file mode 100644 index 0000000..a3c8e5a --- /dev/null +++ b/core/db/migrations/5.sql @@ -0,0 +1,3 @@ +CREATE TABLE `migration` (`schema_version` smallint(4) NOT NULL) + ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +INSERT INTO `migration` (`schema_version`) VALUES (5); -- cgit v1.2.3 From 1c03652fa244d76ac15760b33a36711b23a3ef22 Mon Sep 17 00:00:00 2001 From: Thomas Lange Date: Thu, 17 Jun 2021 00:26:29 +0200 Subject: Move database.sql to core/db/ --- core/db/database.sql | 86 ++++++++++++++++++++++++++++++++++++++++++++++++++++ database.sql | 86 ---------------------------------------------------- readme.md | 2 +- 3 files changed, 87 insertions(+), 87 deletions(-) create mode 100644 core/db/database.sql delete mode 100644 database.sql (limited to 'core/db') diff --git a/core/db/database.sql b/core/db/database.sql new file mode 100644 index 0000000..ae61034 --- /dev/null +++ b/core/db/database.sql @@ -0,0 +1,86 @@ +-- ============================================================================= +-- Internal information table for migrations +-- ============================================================================= +CREATE TABLE `migration` (`schema_version` smallint(4) NOT NULL) + ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +INSERT INTO `migration` (`schema_version`) VALUES (5); + +-- ============================================================================= +-- Table structure for page items +-- ============================================================================= +CREATE TABLE `page` ( + `id` smallint(6) NOT NULL, + `time_insert` datetime NOT NULL, + `time_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `user` tinyint(4) NOT NULL, + `slug` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, + `name` varchar(100) NOT NULL, + `body` text NOT NULL, + `argv` varchar(250) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ============================================================================= +-- Table structure for post items +-- ============================================================================= +CREATE TABLE `post` ( + `id` smallint(6) NOT NULL, + `time_insert` datetime NOT NULL, + `time_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `user` tinyint(4) NOT NULL, + `slug` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, + `name` varchar(100) NOT NULL, + `body` text NOT NULL, + `argv` varchar(250) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ============================================================================= +-- Table structure for user items +-- ============================================================================= +CREATE TABLE `user` ( + `id` tinyint(4) NOT NULL, + `time_insert` datetime NOT NULL, + `time_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + `slug` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, + `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, + `password` char(64) CHARACTER SET latin1 DEFAULT NULL, + `fullname` varchar(40) NOT NULL, + `mailaddr` varchar(60) NOT NULL, + `body` text NOT NULL, + `argv` varchar(250) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +-- ============================================================================= +-- Insert demo page, post and user +-- ============================================================================= +INSERT INTO `page` (`id`, `time_insert`, `time_update`, `user`, `slug`, `name`, `body`, `argv`) VALUES +(1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1, 'example-page', 'Example Page', 'OK. You discovered that there is also a page functionality. But what is the difference between a **page** and a **post**? This is simple: There is not really much difference. But you can style posts and pages within the themes CSS completely independent from each other. For example, use **pages** for things like your imprint, your terms of use, your FAQ or other stuff. And **posts** for your main blog posts. A **page** (and also a **user**) has exactly the same functionality as already described within the [first post]({POST[1]})! 8)', NULL); +INSERT INTO `post` (`id`, `time_insert`, `time_update`, `user`, `slug`, `name`, `body`, `argv`) VALUES +(1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1, 'hello-world', 'Hello World!', 'Hello! This is the automatically generated first post on your new blog installation. You can type [Markdown](https://daringfireball.net/projects/markdown/) plaintext into the editor to format your content like you want.\r\n\r\nIn this post you can see several examples to [format your content with Markdown](https://daringfireball.net/projects/markdown/syntax) and with the *special codes* provided by this blog application. After you are familiar with the text formatting and done with the exploration of your new blog application, you can delete this post and create your own one. 😃\r\n\r\n![Demo image: Computer Guy (Public Domain)]({FILE[\"image/content/computer-guy-public-domain.svg\"]})\r\n\r\n## Dynamic internal URLs for items\r\nIf you want to link an item, please do not put the URL to the item hardcoded into your content! What if you want to change your sites address (or the base directory) in the future? Then you have to change all internal links in your content. This is not cool!\r\n\r\nTherefore, you can use the following code **without spaces between the braces** by knowing the unique ID of an item to link it dynamically:\r\n\r\n1. Example: `{ POST[1] }` \r\n{POST[1]}\r\n\r\n2. Example: `{ PAGE[1] }` \r\n{PAGE[1]}\r\n\r\n3. Example: `{ USER[1] }` \r\n{USER[1]}\r\n\r\n## Dynamic internal URLs for other resources\r\nThis also applies to any other resource that exists in the blog system and that you want to link to! You can link any other resource dynamically either relative to your base directory or relative to your resource directory (`/rsrc/`) for static files:\r\n\r\n* Example: `{ BASE[\"foo/bar/\"] }` \r\n{BASE[\"foo/bar/\"]}\r\n\r\n* Example: `{ FILE[\"foo/bar/\"] }` \r\n{FILE[\"foo/bar/\"]}', NULL); +INSERT INTO `user` (`id`, `time_insert`, `time_update`, `slug`, `username`, `password`, `fullname`, `mailaddr`, `body`, `argv`) VALUES +(1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'change-me', 'ChangeMe', '$2y$10$jH48L1K1y9dB303aI2biN.ob0biZDuUbMxPKadi3wDqOIxj6yNT6K', 'John Doe', 'mail@example.org', 'Describe yourself.', NULL); + +-- ============================================================================= +-- Add keys for table columns +-- ============================================================================= +ALTER TABLE `page` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `time_insert` (`time_insert`), ADD UNIQUE KEY `slug` (`slug`), ADD KEY `page_user` (`user`); +ALTER TABLE `post` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `time_insert` (`time_insert`), ADD UNIQUE KEY `slug` (`slug`), ADD KEY `post_user` (`user`); +ALTER TABLE `user` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `time_insert` (`time_insert`), ADD UNIQUE KEY `slug` (`slug`), ADD UNIQUE KEY `username` (`username`); + +-- ============================================================================= +-- Add FULLTEXT indexes for table columns +-- ============================================================================= +ALTER TABLE `page` ADD FULLTEXT KEY `search` (`name`, `body`); +ALTER TABLE `post` ADD FULLTEXT KEY `search` (`name`, `body`); + +-- ============================================================================= +-- Add AUTO_INCREMENT for primary keys +-- ============================================================================= +ALTER TABLE `page` MODIFY `id` smallint(6) NOT NULL AUTO_INCREMENT; +ALTER TABLE `post` MODIFY `id` smallint(6) NOT NULL AUTO_INCREMENT; +ALTER TABLE `user` MODIFY `id` tinyint(4) NOT NULL AUTO_INCREMENT; + +-- ============================================================================= +-- Add foreign keys for data integrity +-- ============================================================================= +ALTER TABLE `page` ADD CONSTRAINT `page_user` FOREIGN KEY (`user`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; +ALTER TABLE `post` ADD CONSTRAINT `post_user` FOREIGN KEY (`user`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; diff --git a/database.sql b/database.sql deleted file mode 100644 index ae61034..0000000 --- a/database.sql +++ /dev/null @@ -1,86 +0,0 @@ --- ============================================================================= --- Internal information table for migrations --- ============================================================================= -CREATE TABLE `migration` (`schema_version` smallint(4) NOT NULL) - ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -INSERT INTO `migration` (`schema_version`) VALUES (5); - --- ============================================================================= --- Table structure for page items --- ============================================================================= -CREATE TABLE `page` ( - `id` smallint(6) NOT NULL, - `time_insert` datetime NOT NULL, - `time_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `user` tinyint(4) NOT NULL, - `slug` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, - `name` varchar(100) NOT NULL, - `body` text NOT NULL, - `argv` varchar(250) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - --- ============================================================================= --- Table structure for post items --- ============================================================================= -CREATE TABLE `post` ( - `id` smallint(6) NOT NULL, - `time_insert` datetime NOT NULL, - `time_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `user` tinyint(4) NOT NULL, - `slug` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, - `name` varchar(100) NOT NULL, - `body` text NOT NULL, - `argv` varchar(250) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - --- ============================================================================= --- Table structure for user items --- ============================================================================= -CREATE TABLE `user` ( - `id` tinyint(4) NOT NULL, - `time_insert` datetime NOT NULL, - `time_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - `slug` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, - `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, - `password` char(64) CHARACTER SET latin1 DEFAULT NULL, - `fullname` varchar(40) NOT NULL, - `mailaddr` varchar(60) NOT NULL, - `body` text NOT NULL, - `argv` varchar(250) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - --- ============================================================================= --- Insert demo page, post and user --- ============================================================================= -INSERT INTO `page` (`id`, `time_insert`, `time_update`, `user`, `slug`, `name`, `body`, `argv`) VALUES -(1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1, 'example-page', 'Example Page', 'OK. You discovered that there is also a page functionality. But what is the difference between a **page** and a **post**? This is simple: There is not really much difference. But you can style posts and pages within the themes CSS completely independent from each other. For example, use **pages** for things like your imprint, your terms of use, your FAQ or other stuff. And **posts** for your main blog posts. A **page** (and also a **user**) has exactly the same functionality as already described within the [first post]({POST[1]})! 8)', NULL); -INSERT INTO `post` (`id`, `time_insert`, `time_update`, `user`, `slug`, `name`, `body`, `argv`) VALUES -(1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 1, 'hello-world', 'Hello World!', 'Hello! This is the automatically generated first post on your new blog installation. You can type [Markdown](https://daringfireball.net/projects/markdown/) plaintext into the editor to format your content like you want.\r\n\r\nIn this post you can see several examples to [format your content with Markdown](https://daringfireball.net/projects/markdown/syntax) and with the *special codes* provided by this blog application. After you are familiar with the text formatting and done with the exploration of your new blog application, you can delete this post and create your own one. 😃\r\n\r\n![Demo image: Computer Guy (Public Domain)]({FILE[\"image/content/computer-guy-public-domain.svg\"]})\r\n\r\n## Dynamic internal URLs for items\r\nIf you want to link an item, please do not put the URL to the item hardcoded into your content! What if you want to change your sites address (or the base directory) in the future? Then you have to change all internal links in your content. This is not cool!\r\n\r\nTherefore, you can use the following code **without spaces between the braces** by knowing the unique ID of an item to link it dynamically:\r\n\r\n1. Example: `{ POST[1] }` \r\n{POST[1]}\r\n\r\n2. Example: `{ PAGE[1] }` \r\n{PAGE[1]}\r\n\r\n3. Example: `{ USER[1] }` \r\n{USER[1]}\r\n\r\n## Dynamic internal URLs for other resources\r\nThis also applies to any other resource that exists in the blog system and that you want to link to! You can link any other resource dynamically either relative to your base directory or relative to your resource directory (`/rsrc/`) for static files:\r\n\r\n* Example: `{ BASE[\"foo/bar/\"] }` \r\n{BASE[\"foo/bar/\"]}\r\n\r\n* Example: `{ FILE[\"foo/bar/\"] }` \r\n{FILE[\"foo/bar/\"]}', NULL); -INSERT INTO `user` (`id`, `time_insert`, `time_update`, `slug`, `username`, `password`, `fullname`, `mailaddr`, `body`, `argv`) VALUES -(1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'change-me', 'ChangeMe', '$2y$10$jH48L1K1y9dB303aI2biN.ob0biZDuUbMxPKadi3wDqOIxj6yNT6K', 'John Doe', 'mail@example.org', 'Describe yourself.', NULL); - --- ============================================================================= --- Add keys for table columns --- ============================================================================= -ALTER TABLE `page` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `time_insert` (`time_insert`), ADD UNIQUE KEY `slug` (`slug`), ADD KEY `page_user` (`user`); -ALTER TABLE `post` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `time_insert` (`time_insert`), ADD UNIQUE KEY `slug` (`slug`), ADD KEY `post_user` (`user`); -ALTER TABLE `user` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `time_insert` (`time_insert`), ADD UNIQUE KEY `slug` (`slug`), ADD UNIQUE KEY `username` (`username`); - --- ============================================================================= --- Add FULLTEXT indexes for table columns --- ============================================================================= -ALTER TABLE `page` ADD FULLTEXT KEY `search` (`name`, `body`); -ALTER TABLE `post` ADD FULLTEXT KEY `search` (`name`, `body`); - --- ============================================================================= --- Add AUTO_INCREMENT for primary keys --- ============================================================================= -ALTER TABLE `page` MODIFY `id` smallint(6) NOT NULL AUTO_INCREMENT; -ALTER TABLE `post` MODIFY `id` smallint(6) NOT NULL AUTO_INCREMENT; -ALTER TABLE `user` MODIFY `id` tinyint(4) NOT NULL AUTO_INCREMENT; - --- ============================================================================= --- Add foreign keys for data integrity --- ============================================================================= -ALTER TABLE `page` ADD CONSTRAINT `page_user` FOREIGN KEY (`user`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -ALTER TABLE `post` ADD CONSTRAINT `post_user` FOREIGN KEY (`user`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; diff --git a/readme.md b/readme.md index 632d6a1..48d8b07 100644 --- a/readme.md +++ b/readme.md @@ -11,7 +11,7 @@ Easy blogging application written with PHP7! The application comes with a fullte ## Installation 1. Download the repository and extract it to the target directory where it should be installed. -2. Create your MySQL database and import the `database.sql` file. +2. Create your MySQL database and import the `core/db/database.sql` file. 3. Rename `core/configuration-example.php` to `core/configuration.php` and customize the configuration and set in any case the settings for the database connection. 4. Navigate your browser to `/admin/auth.php` and authenticate with the default username `ChangeMe` and the password `changeme` (please note that the username is case-sensitive). -- cgit v1.2.3