calendar_weeks table missing from Lake database

[javaface]

trafficstars

Question

Should I have a calendar_weeks table in my Lake database?

Screenshots

Additional context

I have some opendora devlake-go dora api queries returning zero values and I find the queries are referencing a calendar_weeks table not in my Lake database. I see a calendar_months table but no calendar_weeks table.

[javaface]

With some help I see the calendar_weeks is a temporary, dynamic if you will, table. The root of my problem appears to be that the the query is returning zeroes due to lake.cicd_deployment_commits table not having values in repo_id column. Not sure why that is.

[d4x1]

@javaface Which version are you using? And only is field repo_id empty?
If your datasouce has no deployment_commits , then cicd_deployment_commits will be empty.

[javaface]

@javaface Which version are you using? And only is field repo_id empty? If your datasouce has no deployment_commits , then cicd_deployment_commits will be empty.

I'm using v0.19.0 (latest)

cicd_deployments_commits is not empty. However, the repo_id column is not being populated. Any idea why that would be? Should have values like github:GithubRepo:1:607853062.

See the bold portion of this query from opendora api (below) joins on cdc.repo_id which for some reason isn't populated in the table. I am using a GitHub connection.

WITH RECURSIVE calendar_weeks AS ( SELECT STR_TO_DATE( CONCAT(YEARWEEK(FROM_UNIXTIME(1692195382)), ' Sunday'), '%X%V %W' ) AS week_date UNION ALL SELECT DATE_ADD(week_date, INTERVAL 1 WEEK) FROM calendar_weeks WHERE week_date < FROM_UNIXTIME(1708092982) ), _deployments AS( SELECT YEARWEEK(deployment_finished_date) AS week, count(cicd_deployment_id) AS deployment_count FROM ( SELECT cdc.cicd_deployment_id, max(cdc.finished_date) AS deployment_finished_date FROM cicd_deployment_commits cdc JOIN repos ON cdc.repo_id = repos.id WHERE cdc.result = 'SUCCESS' AND cdc.environment = 'PRODUCTION' GROUP BY 1 ) _production_deployments GROUP BY 1 ), count AS ( SELECT YEARWEEK(cw.week_date) AS data_key, CASE WHEN d.deployment_count IS NULL THEN 0 ELSE d.deployment_count END AS data_value FROM calendar_weeks cw LEFT JOIN _deployments d ON YEARWEEK(cw.week_date) = d.week ORDER BY cw.week_date DESC )SELECT t1.data_key, AVG(t2.data_value) AS data_value FROM count t1 JOIN count t2 ON t2.data_key <= t1.data_key GROUP BY t1.data_key

[d4x1]

@javaface What's the version of DevLake you are using?

[javaface]

In my previous comment I indicated "I'm using v0.19.0 (latest)". Is that what you're looking for?

[Startrekzky]

Hi @javaface , we added the table calendar_months to simplify the SQL in the DORA dashboard. Since we don't have a weekly-level DORA metrics, we didn't add the calendar_weeks table.

[javaface]

Hello, I figured out that calendar_weeks has nothing to do with my issue. The opendora team is working on it. See https://github.com/DevoteamNL/opendora/issues/139 if interested. Thanks.