This article explains how to create a filtered view and a related database inside different database pages while maintaining the date relation. This process is essential for generating daily pages with filtered task databases within the related pages.
Prerequisites
Before we begin, make sure you have the following:
- Two databases:
source_dbandtarget_db - Adequate permissions to create tables, views, and databases
Creating a Filtered View in the Source Database
To create a filtered view, follow these steps:
Step 1: Create a New Table
First, create a new table named filtered_tasks in the source_db with the required columns and data.
CREATE TABLE filtered_tasks (
id INT PRIMARY KEY,
task_id INT,
task_name VARCHAR(50),
created_at DATETIME
);
Step 2: Insert Data into the New Table
Insert the necessary data into the filtered_tasks table.
INSERT INTO filtered_tasks (id, task_id, task_name, created_at)
VALUES (1, 1, 'Task 1', '2022-01-01 10:00:00'),
(2, 2, 'Task 2', '2022-01-02 15:30:00'),
(3, 3, 'Task 3', '2022-01-03 08:45:00'),
...
Step 3: Create a View
Now, create a view named filtered_view in the source_db using the filtered_tasks table and filter the data based on the desired date range.
CREATE VIEW filtered_view AS
SELECT id, task_id, task_name
FROM filtered_tasks
WHERE created_at BETWEEN '2022-01-01' AND '2022-01-31';
Creating a Database and a Table in the Target Database
Next, create a new database named generated_pages_db in the target_db and a table named generated_pages to store the generated daily pages.
CREATE DATABASE generated_pages_db;
USE generated_pages_db;
CREATE TABLE generated_pages (
id INT PRIMARY KEY,
page_name VARCHAR(50),
page_content LONGTEXT
);
Creating a Stored Procedure in the Source Database
Create a stored procedure named generate_daily_pages in the source_db to insert the filtered data into the generated_pages_db.
DELIMITER //
CREATE PROCEDURE generate_daily_pages()
BEGIN
DECLARE cur CURSOR FOR
SELECT id, task_name
FROM filtered_view;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = 1;
INSERT INTO generated_pages (page_name, page_content)
VALUES ('daily_page_2022-01-01', CONCAT('Page content for daily page generated on 2022-01-01:', (SELECT GROUP_CONCAT(task_name SEPARATOR '
') FROM filtered_view WHERE created_at = '2022-01-01')));
OPEN cur;
read_data: LOOP
FETCH cur INTO @task_id, @task_name;
IF @done THEN
LEAVE read_data;
END IF;
INSERT INTO generated_pages (page_name, page_content)
VALUES ('daily_page_2022-01-XX', CONCAT('Page content for daily page generated on 2022-01-XX:', @task_name));
END LOOP;
CLOSE cur;
END//
Executing the Stored Procedure
Finally, execute the generate_daily_pages stored procedure daily to generate the filtered pages and insert them into the generated_pages table in the generated_pages_db.
In this article, we learned how to create a filtered view and a related database inside different database pages while maintaining the date relation. This process is essential for generating daily pages with filtered task databases within the related pages. We covered creating a new table, inserting data, creating a view, creating a new database and table, and creating a stored procedure to insert the filtered data into the new table.
References