[Bug]: Azure Data Studio requires sys.master_files view to return results along with needing an empty table for msdb.dbo.backupset to show databases in the Manage tool

[bill-ramos-rmoswi]

What happened?

Before I prepare a Pull request, I'd like to make sure no one is working on providing an update to sys.master_files view and the creation of the msdb.dbo.backupset table to support Azure Data Studio's feature Manage command for a SQL Server connection to either an Aurora PostgreSQL with Babelfish or WiltonDB.

The proposed change is to update the sys.master_files to return one row for each database in sys.databases and aggregate the sum of the pg_total_relation_size() for each table in a database to compute the database size for the size column, along with column values for state, state_desc, name, type (always 0), database_id, file_id (always 1) and type_desc (always ROWS).

The other change is to create a new table in the msdb database called dbo.backupset that normally would have information for backups. I recommend using an actual table instead of a view to allow programs like WiltonDB's backup command to write backup information into the table.

The end goal is to display database size chart as shown below.

This fix would also allow ADS for the Databases page to display the list of Babelfish databases shown below.

Both examples are from WiltonDB on windows with the proposed modifications.

Azure Data Studio executes the following T-SQL statement to get the needed data.

-- Azure Data Studio - Manage Databases Query
WITH

    db_size

    AS

    (

        SELECT database_id, CAST(SUM(CAST(size AS BIGINT)) * 8.0 / 1024 AS BIGINT) size

        FROM sys.master_files

        GROUP BY database_id

    ),

    db_backup

    AS

    (

        SELECT database_name, MAX(backup_start_date) AS last_backup

        FROM msdb.dbo.backupset

        GROUP BY database_name

    )

SELECT name, state_desc AS state, db_size.size, db_backup.last_backup

FROM sys.databases LEFT JOIN db_size ON sys.databases.database_id = db_size.database_id

LEFT JOIN db_backup ON sys.databases.name = db_backup.database_name

WHERE state_desc='ONLINE'

ORDER BY name ASC

Version

BABEL_3_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

Which flavor of Linux are you using when you see the bug?

Other

Relevant log output

With the existing sys.master_files view, ADS executes the following statement from the log.

2024-05-19 18:39:37.074 PDT,wilton,18200,"sql_batch statement: with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select top 10
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
where database_id > 4
order by DataFileSizeMB desc
"
2024-05-19 18:39:37.061 PDT,wilton,12568,Query duration: 0.049 ms

This returns no rows. ADS then attempts to make a query shown earlier against msdb.dbo.backupset resulting in the following log messages.

2024-05-19 14:31:35.784 PDT,wilton,21408,"Unmapped error found. Code: 16908420, Message: relation ""msdb_dbo.backupset"" does not exist, File: parse_relation.c, Line: 1402, Context: TDS"
2024-05-19 14:31:35.784 PDT,wilton,21408,"relation ""msdb_dbo.backupset"" does not exist"
2024-05-19 14:31:35.784 PDT,wilton,21408,"Unmapped error found. Code: 16908420, Message: relation ""msdb_dbo.backupset"" does not exist, File: parse_relation.c, Line: 1402, Context: babelfishpg_tsql"
2024-05-19 14:31:35.784 PDT,wilton,21408,"Unmapped error found. Code: 16908420, Message: relation ""msdb_dbo.backupset"" does not exist, File: parse_relation.c, Line: 1402, Context: babelfishpg_tsql"

Code of Conduct

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

[jsudrik]

You can go ahead with the pull request. No one else is working on these. Thanks Bill!!