[Feature][Dashboards] Use embedded procedure to generate dynamic chat in the `Work Logs` dashboard

[Startrekzky]

Search before asking

• I had searched in the issues and found no similar feature requirement.

Use case

The existing approach to generate this table is the active SQL. If I want to add more week's data, I'll have to copy tons of code.

However, I can't directly use the dynamic SQL in Grafana as it only has the read-only permission. Therefore, I'm thinking if we can provide an embedded procedure to allow end users to call this procedure.

Description

Prerequisite: Create a mock dataset for the dynamic SQL to run on

The SQL is

DROP TABLE IF EXISTS _worklogs;
CREATE TABLE _worklogs AS

with _accounts as (
  select ua.account_id, ua.user_id, u.name
    from accounts a 
    join user_accounts ua on a.id = ua.account_id
    join users u on ua.user_id = u.id
  where ua.user_id in (1,2)
),

_activities as (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY `Date` ORDER BY `Time` desc) AS _row_number
  FROM (
    SELECT DATE(created_date) as Date, created_date as Time, 'Create an issue' as Activity, concat('#', issue_key, ' ', title) as Details, a.name as Name
    FROM issues i
    join _accounts a on i.creator_id = a.account_id
    where created_date > '2023-10-09 19:15:28'

    union

    SELECT DATE(resolution_date) as Date, resolution_date as Time, 'Issue resolved' as Activity, concat('#', issue_key, ' ', title) as Details, a.name as Name 
    FROM issues i
    join _accounts a on i.assignee_id = a.account_id
    where resolution_date > '2023-10-09 19:15:28'

    union

    SELECT DATE(authored_date) as Date, authored_date as Time, 'Finish a commit' as Activity, concat(message, ' #', sha) as Details, a.name as Name 
    FROM commits c
    join _accounts a on c.author_id = a.account_id
    where authored_date > '2023-10-09 19:15:28'

    union

    SELECT DATE(created_date) as Date, created_date as Time, 'Open a PR' as Activity, concat('#', pull_request_key, ' ', title) as Details, a.name as Name
    FROM pull_requests pr
    join _accounts a on pr.author_id = a.account_id
    where created_date > '2023-10-09 19:15:28'

    union

    SELECT DATE(merged_date) as Date, merged_date as Time, 'PR gets merged' as Activity, concat('#', pull_request_key, ' ', title) as Details, a.name as Name 
    FROM pull_requests pr
    join _accounts a on pr.author_id = a.account_id
    where merged_date > '2023-10-09 19:15:28'

    union

    SELECT DATE(prc.created_date) as Date, prc.created_date as Time, 'Comment on PR' as Activity, concat('#', pr.pull_request_key, ' ', pr.title) as Details, a.name as Name 
    FROM pull_request_comments prc
    left join pull_requests pr on prc.pull_request_id = pr.id
    join _accounts a on prc.account_id = a.account_id
    WHERE 
      prc.type = 'NORMAL'
      and prc.created_date > '2023-10-09 19:15:28'

    union

    SELECT DATE(prc.created_date) as Date, prc.created_date as Time, 'Review PR' as Activity, concat('#', pr.pull_request_key, ' ', pr.title) as Details, a.name as Name  
    FROM pull_request_comments prc
    left join pull_requests pr on prc.pull_request_id = pr.id
    join _accounts a on prc.account_id = a.account_id
    WHERE 
      prc.type in ('REVIEW', 'DIFF')
      and prc.created_date > '2023-10-09 19:15:28'
  ) t

  ORDER BY Time desc
),

_activity_count_per_day as (
  SELECT 
    Date,
    count(*) as value
  FROM _activities
  GROUP BY 1
),

last_few_calendar_months as(
-- construct the last few calendar months within the selected time period in the top-right corner
	SELECT 
    CAST((now()-INTERVAL (H+T+U) DAY) AS date) as d, 
    DATE_FORMAT(CAST((now()-INTERVAL (H+T+U) DAY) AS date), 'w%u %Y') as week_name,
    DATE_FORMAT(CAST((now()-INTERVAL (H+T+U) DAY) AS date), '%Y%u') as week_number
	FROM ( SELECT 0 H
			UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300
		) H CROSS JOIN ( SELECT 0 T
			UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
			UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
			UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
		) T CROSS JOIN ( SELECT 0 U
			UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
			UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
			UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
		) U
	WHERE
		(now()-INTERVAL (H+T+U) DAY) > '2023-04-09'
),

_calendar_months_with_rank as (
  SELECT  
    d, 
    concat(week_name, ' (', DATE_FORMAT(DATE_SUB(d, INTERVAL WEEKDAY(d) DAY), '%m/%d'), '~', DATE_FORMAT(DATE_ADD(DATE_SUB(d, INTERVAL WEEKDAY(d) DAY), INTERVAL 6 DAY), '%m/%d'), ')') as week_name,
    week_number,
    DATE_FORMAT(d, '%W') as weekday,
    dense_rank() over(ORDER BY week_number desc) as week_rank
  FROM  last_few_calendar_months
  ORDER BY 1 desc
),

_final_datasets as (
  SELECT  
    _calendar_months_with_rank.*, 
    case when _activity_count_per_day.value is null then 0 else _activity_count_per_day.value end as activity_count 
  from _calendar_months_with_rank left join _activity_count_per_day on _calendar_months_with_rank.d = _activity_count_per_day.Date
)

SELECT * FROM _final_datasets;

This SQL is equivalent to the _final_dataset in the dashboard chart (see the pic below). I just replaced the Grafana variables and methods (E.g. $time_filter(), $time_to, $time_from, and $users) with static values to get it run on Navicat.

Run the dynamic SQL to generate the chart

I wrote a procedure to dynamically generate a table with the last 54 weeks as the column, Monday, Tuesday, ..., Sunday as the rows.

DROP TABLE IF EXISTS WeeklyActivity;

SET SESSION group_concat_max_len = 1000000; -- Ensure the group_concat function doesn't truncate the results.

-- Step 1: Create the Table with Dynamic Columns
SET @columns = (
  SELECT GROUP_CONCAT(DISTINCT 
    CONCAT('`', week_name, '` INT DEFAULT 0')
    ORDER BY week_number DESC
    SEPARATOR ', '
  )
  FROM _worklogs
);

SET @create_table_sql = CONCAT('CREATE TEMPORARY TABLE IF NOT EXISTS WeeklyActivity (`Day` VARCHAR(9), ', @columns, ');');

PREPARE create_stmt FROM @create_table_sql;
EXECUTE create_stmt;
DEALLOCATE PREPARE create_stmt;

SELECT @columns;
SELECT @create_table_sql;

SELECT * FROM WeeklyActivity;

-- Step 2: Populate the Table with Aggregate Values for Each Day

-- Retrieve the distinct week names from the _worklogs table to generate the column list
SELECT GROUP_CONCAT(DISTINCT CONCAT('`', week_name, '`')
                    ORDER BY week_number DESC
                    SEPARATOR ', ') INTO @week_names
FROM (
    SELECT week_name, week_number 
    FROM _worklogs
    GROUP BY week_name, week_number
    ORDER BY week_number DESC
    LIMIT 54
) AS subquery_week_names;

select @week_names;

-- Prepare the dynamic SQL for inserting the weekly activity counts
SET @insert_sql = CONCAT(
    'INSERT INTO WeeklyActivity (Day, ', @week_names, ') ',
    'SELECT weekday, ',
    (SELECT GROUP_CONCAT(
        CONCAT('SUM(CASE WHEN week_name = ''', week_name, ''' THEN activity_count ELSE 0 END) AS ', CONCAT('`', week_name, '`'))
        ORDER BY week_number DESC
        SEPARATOR ','
    ) FROM (
        SELECT week_name, week_number 
        FROM _worklogs
        GROUP BY week_name, week_number
        ORDER BY week_number DESC
        LIMIT 54
    ) AS subquery),
    ' FROM _worklogs ',
    'WHERE weekday IN (SELECT DISTINCT weekday FROM _worklogs) ',
    'GROUP BY weekday ',
    'ORDER BY FIELD(weekday, ''Monday'', ''Tuesday'', ''Wednesday'', ''Thursday'', ''Friday'', ''Saturday'', ''Sunday'');'
);

SELECT @insert_sql;

-- Execute the dynamic SQL statement
PREPARE insert_stmt FROM @insert_sql;
EXECUTE insert_stmt;
DEALLOCATE PREPARE insert_stmt;

SELECT * FROM WeeklyActivity

The result of the dynamic SQL is

which is the same as the existing chart

To Do

• Find the solution to pre-define the dynamic SQL in a procedure
• Make sure that users can call the procedure based on the temp table _final_results in the Work Logs dashboard

Related issues

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[Startrekzky]

It's not feasible after further investigation.