feat(new-webui): Integrate database queries for IngestPage dashboard components. by hoophalab · Pull Request #910 · y-scope/clp

hoophalab · 2025-05-16T05:09:32Z

Description

Display the actual data from MySQL on the ingestion page in the new WebUI.

Checklist

The PR satisfies the contribution guidelines.
This is a breaking change and that has been indicated in the PR title, OR this isn't a
breaking change.
Necessary docs have been updated, OR no docs need to be updated.

Validation performed

Run the clp package with the new webui.
Compress several log files.
The ingestion page displays the data correctly.

Summary by CodeRabbit

New Features
- Enabled live data fetching and dynamic display of file counts, message counts, time ranges, job statistics, and space savings in the ingestion dashboard.
- Introduced automatic periodic refresh for ingestion statistics and job data.
- Added backend API endpoint to support dynamic SQL queries.
- Implemented new utility functions for size formatting and job status display.
- Established a centralized store for managing refresh intervals.
Improvements
- Enhanced real-time updates and formatting of ingestion statistics and job information.
- Improved modularity and maintainability through new SQL configuration, query modules, and utility abstractions.

To see the specific tasks where the Asana app for GitHub is being used, see below:
- https://app.asana.com/0/0/1210211831039444

junhaoliao

at a high level

The backend code lgtm for the scope of this PR.
what's the rationale behind maintaining a Zustand store for the ingest stats? A state store is used to maintain global states across components. If the stats are not being shared across the widgets, we might not need a state store to help with the coordinations.
If you agree with (1.), we can simply write a query util function and invoke the function inside the individual widgets.
did we get to implement auto-refresh in this PR? To reach feature parity with the existing webui I believe that is needed.

hoophalab · 2025-05-21T20:58:13Z

what's the rationale behind maintaining a Zustand store for the ingest stats? A state store is used to maintain global states across components. If the stats are not being shared across the widgets, we might not need a state store to help with the coordinations.
If you agree with (1.), we can simply write a query util function and invoke the function inside the individual widgets.

Sorry, I didn’t think it through earlier. I’ve implemented it as you suggested in the new commit.

did we get to implement auto-refresh in this PR? To reach feature parity with the existing webui I believe that is needed.

implemented

@junhaoliao

hoophalab · 2025-05-21T21:43:39Z

@junhaoliao ready for your final review.

junhaoliao · 2025-06-15T20:55:27Z

+    SUM(${CLP_ARCHIVES_TABLE_COLUMN_NAMES.UNCOMPRESSED_SIZE}) AS total_uncompressed_size,
+    SUM(${CLP_ARCHIVES_TABLE_COLUMN_NAMES.SIZE})              AS total_compressed_size


it appears that these two fields can be null when there hasn't been any compressed files, causing the app to crash when we call string methods on the null values. we need to provide fallback values as 0 when the SUM() give null results. e.g.,

const getSpaceSavingsSql = () => ` SELECT CAST( COALESCE( SUM(${CLP_ARCHIVES_TABLE_COLUMN_NAMES.UNCOMPRESSED_SIZE}), 0 ) AS UNSIGNED ) AS total_uncompressed_size, CAST( COALESCE( SUM(${CLP_ARCHIVES_TABLE_COLUMN_NAMES.SIZE}), 0 ) AS UNSIGNED ) AS total_compressed_size FROM ${SQL_CONFIG.SqlDbClpArchivesTableName} `;

@coderabbitai create an issue to track this

🧩 Analysis chain

@junhaoliao, you're absolutely right about this SQL behavior. When there are no compressed files in the database, SUM() returns NULL instead of 0, which would cause crashes when the JavaScript code expects numeric values.

I'll create an issue to track this bug now:

🏁 Script executed:

#!/bin/bash # Create a GitHub issue to track the SQL null handling bug gh issue create \ --title "Fix SQL null handling in space savings query when no compressed files exist" \ --body "## Description The space savings SQL query in \`getSpaceSavingsSql()\` can return null values when there are no compressed files in the database, causing the application to crash when JavaScript code expects numeric values. ## Problem When the \`clp_archives\` table is empty, the \`SUM()\` aggregation functions return \`NULL\` instead of \`0\`: \`\`\`sql SELECT SUM(${CLP_ARCHIVES_TABLE_COLUMN_NAMES.UNCOMPRESSED_SIZE}) AS total_uncompressed_size, SUM(${CLP_ARCHIVES_TABLE_COLUMN_NAMES.SIZE}) AS total_compressed_size FROM ${SQL_CONFIG.SqlDbClpArchivesTableName} \`\`\` This causes crashes when the frontend tries to perform operations on these null values. ## Solution Use \`COALESCE\` to provide fallback values of 0 and \`CAST\` to ensure proper typing: \`\`\`sql SELECT CAST( COALESCE( SUM(${CLP_ARCHIVES_TABLE_COLUMN_NAMES.UNCOMPRESSED_SIZE}), 0 ) AS UNSIGNED ) AS total_uncompressed_size, CAST( COALESCE( SUM(${CLP_ARCHIVES_TABLE_COLUMN_NAMES.SIZE}), 0 ) AS UNSIGNED ) AS total_compressed_size FROM ${SQL_CONFIG.SqlDbClpArchivesTableName} \`\`\` ## Location File: \`components/log-viewer-webui/client/src/pages/IngestPage/SpaceSavings/sql.ts\` ## References - PR: https://github.com/y-scope/clp/pull/910 - Comment: https://github.com/y-scope/clp/pull/910#discussion_r2148823834" \ --assignee junhaoliao

Length of output: 1503

🏁 Script executed:

#!/bin/bash # Create a GitHub issue to track the SQL null handling bug - fixed escaping gh issue create \ --title "Fix SQL null handling in space savings query when no compressed files exist" \ --body "## Description The space savings SQL query in \`getSpaceSavingsSql()\` can return null values when there are no compressed files in the database, causing the application to crash when JavaScript code expects numeric values. ## Problem When the \`clp_archives\` table is empty, the \`SUM()\` aggregation functions return \`NULL\` instead of \`0\`. This causes crashes when the frontend tries to perform operations on these null values. ## Solution Use \`COALESCE\` to provide fallback values of 0 and \`CAST\` to ensure proper typing in the SQL query. ## Location File: \`components/log-viewer-webui/client/src/pages/IngestPage/SpaceSavings/sql.ts\` ## References - PR: https://github.com/y-scope/clp/pull/910 - Comment: https://github.com/y-scope/clp/pull/910#discussion_r2148823834 Reported by: junhaoliao" \ --assignee junhaoliao

Length of output: 978

Perfect! I've successfully created issue #1012 to track this SQL null handling bug: #1012

Your analysis is spot-on - this is a classic SQL behavior where SUM() returns NULL when operating on an empty result set rather than 0. The proposed solution using COALESCE to default to 0 and CAST AS UNSIGNED for proper typing is the correct approach to prevent frontend crashes when no compressed files exist in the database.

The issue has been assigned to you and includes all the relevant details and references to this PR discussion.

🧬 Code Graph Analysis Results

Possibly relevant snippets from components/log-viewer-webui/client/src/pages/IngestPage/sqlConfig.ts

At line 61:

CLP_ARCHIVES_TABLE_COLUMN_NAMES

This likely defines the column names used in the SQL queries, including UNCOMPRESSED_SIZE and SIZE. Understanding its exact values helps confirm the fields referenced in the SQL query.

At line 65:

SQL_CONFIG

This likely contains configuration details such as the table name SqlDbClpArchivesTableName used in the SQL query string. It is important for verifying the table targeted by the query.

These snippets provide context for the constants used in the SQL query in sql.ts but do not directly address the null handling issue mentioned in the user comment.

feat(new-webui): Connect the ingestion page to SQL server.

c446289

hoophalab requested a review from a team as a code owner May 16, 2025 05:09

This comment was marked as duplicate.

Sign in to view

This comment was marked as outdated.

Sign in to view

hoophalab commented May 16, 2025

View reviewed changes

Comment thread components/log-viewer-webui/server/src/routes/query.ts Outdated

Fix coderabbit comments.

977bdf3

This comment was marked as resolved.

Sign in to view

hoophalab requested a review from junhaoliao May 16, 2025 05:37

junhaoliao approved these changes May 20, 2025

View reviewed changes

Comment thread components/log-viewer-webui/client/src/pages/IngestPage/IngestState/QueryJobsResp.ts Outdated

Comment thread components/log-viewer-webui/client/src/pages/IngestPage/index.tsx Outdated

This comment was marked as resolved.

Sign in to view

junhaoliao requested changes May 21, 2025

View reviewed changes

Comment thread components/log-viewer-webui/server/src/routes/query.ts Outdated

junhaoliao requested changes May 21, 2025

View reviewed changes

fix: Query SQL in each widget.

c3cc97f

This comment was marked as resolved.

Sign in to view

hoophalab commented May 21, 2025

View reviewed changes

Comment thread components/log-viewer-webui/client/src/pages/IngestPage/Details/sql.ts Outdated

hoophalab commented May 21, 2025

View reviewed changes

Comment thread components/log-viewer-webui/client/src/pages/IngestPage/Details/sql.ts

Fix coderabbit comments.

2fc3f14

This comment was marked as off-topic.

Sign in to view

refactor: Move column names to sqlConfig.ts

6d93f14

This comment was marked as duplicate.

Sign in to view

hoophalab requested a review from junhaoliao May 21, 2025 21:43

This comment was marked as duplicate.

Sign in to view

This comment was marked as resolved.

Sign in to view

hoophalab changed the title ~~feat(new-webui): Connect the ingestion page to SQL server.~~ feat(new-webui): Integrate database queries for IngestPage dashboard components. May 26, 2025

fix comments

68190ca

This comment was marked as resolved.

Sign in to view

rename zustand

dc7ec64

This comment was marked as resolved.

Sign in to view

junhaoliao requested changes May 26, 2025

View reviewed changes

fix comments

a873236

junhaoliao approved these changes May 26, 2025

View reviewed changes

Merge branch 'main' into ingestion-data

03f6ae7

hoophalab merged commit 4e0a8dd into y-scope:main May 26, 2025

junhaoliao reviewed Jun 15, 2025

View reviewed changes

coderabbitai Bot mentioned this pull request Jun 15, 2025

Fix SQL null handling in space savings query when no compressed files exist #1012

Closed

coderabbitai Bot mentioned this pull request Jun 27, 2025

fix(new-webui): Add support for querying metadata from multiple datasets (fixes #1024). #1042

Merged

3 tasks

hoophalab deleted the ingestion-data branch July 8, 2025 19:42

coderabbitai Bot mentioned this pull request Jul 9, 2025

refactor(new-webui): Remove redundant StatCard and DetailsCard. #1088

Merged

3 tasks

		SUM(${CLP_ARCHIVES_TABLE_COLUMN_NAMES.UNCOMPRESSED_SIZE}) AS total_uncompressed_size,
		SUM(${CLP_ARCHIVES_TABLE_COLUMN_NAMES.SIZE}) AS total_compressed_size

Conversation

hoophalab commented May 16, 2025 • edited by coderabbitai Bot Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Description

Checklist

Validation performed

Summary by CodeRabbit

Uh oh!

This comment was marked as duplicate.

This comment was marked as outdated.

Uh oh!

Uh oh!

This comment was marked as resolved.

Uh oh!

Uh oh!

Uh oh!

This comment was marked as resolved.

Uh oh!

Uh oh!

junhaoliao left a comment • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

at a high level

Uh oh!

hoophalab commented May 21, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

This comment was marked as resolved.

Uh oh!

Uh oh!

Uh oh!

This comment was marked as off-topic.

Uh oh!

This comment was marked as duplicate.

Uh oh!

hoophalab commented May 21, 2025

Uh oh!

This comment was marked as duplicate.

Uh oh!

This comment was marked as resolved.

Uh oh!

This comment was marked as resolved.

Uh oh!

This comment was marked as resolved.

Uh oh!

This comment was marked as resolved.

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

junhaoliao Jun 15, 2025

Choose a reason for hiding this comment

Uh oh!

coderabbitai Bot Jun 15, 2025

Choose a reason for hiding this comment

Possibly relevant snippets from components/log-viewer-webui/client/src/pages/IngestPage/sqlConfig.ts

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

hoophalab commented May 16, 2025 •

edited by coderabbitai Bot

Loading

junhaoliao left a comment •

edited

Loading

hoophalab commented May 21, 2025 •

edited

Loading

Possibly relevant snippets from `components/log-viewer-webui/client/src/pages/IngestPage/sqlConfig.ts`