namespace TCMSDAL.QueryBuilders; /// /// SQL constants for Test Environment Management. /// All queries filter by DBINSTANCETYPE = 1 (TEST) — LIVE connections are never exposed. /// Required index: MSERVERCONFIG(DBINSTANCETYPE, STATUS) INCLUDE (CONNECTIONNAME, DATABASENAME, SERVERCONFIGID) /// public static class TCMSTestEnvQB { // ── List ───────────────────────────────────────────────────────────────── public const string GET_TEST_ENVIRONMENTS = @" SELECT sc.SERVERCONFIGID AS ServerConfigId, sc.CONNECTIONNAME AS ConnectionName, sc.DATABASENAME AS DatabaseName, s.SERVERIP AS ServerIP, s.SERVERNAME AS ServerName, sc.DATABASETYPE AS DatabaseType, CASE sc.DATABASETYPE WHEN 0 THEN 'SQL Server' WHEN 1 THEN 'Oracle' WHEN 2 THEN 'PostgreSQL' WHEN 3 THEN 'MySQL' ELSE 'Unknown' END AS DatabaseTypeText, ISNULL(sc.CONTROLSOURCEDBNAME, '') AS ControlSourceDb, sc.STATUS AS Status, sc.DBINSTANCETYPE AS DbInstanceType, rl.TRIGGEREDON AS LastResetOn, rl.TRIGGEREDBY AS LastResetBy, CAST(0 AS BIT) AS IsStale FROM MSERVERCONFIG sc JOIN MSERVER s ON sc.SERVERID = s.SERVERID OUTER APPLY ( SELECT TOP 1 TRIGGEREDON, TRIGGEREDBY FROM TCMS.DBO.TTESTENVRESETLOG WHERE SERVERCONFIGID = sc.SERVERCONFIGID AND SUCCESS = 1 ORDER BY TRIGGEREDON DESC ) rl WHERE sc.DBINSTANCETYPE = 1 -- TEST instances only AND sc.STATUS = 1 -- ACTIVE AND sc.CLIENTID = @ClientId -- tenant scoping ORDER BY sc.CONNECTIONNAME"; // Note: executed via SystemConnAsync() (gb5system connection), hence the 3-part TCMS.DBO reference. // ── Raw connection for GetTestDbConnection (decrypted by BLL) ─────────── public const string GET_TEST_DB_CONNECTION_RAW = @" SELECT sc.SERVERCONFIGID AS ServerConfigId, sc.CONNECTIONNAME AS ConnectionName, s.SERVERIP AS ServerIP, sc.DATABASETYPE AS DatabaseType, sc.DATABASENAME AS DatabaseName, sc.DATABASEUSERNAME AS UserName, sc.DATABASEPASSWORD AS Password, ISNULL(sc.DATABASEPORT, '') AS Port, sc.DBINSTANCETYPE AS DbInstanceType FROM MSERVERCONFIG sc JOIN MSERVER s ON sc.SERVERID = s.SERVERID WHERE sc.SERVERCONFIGID = @ServerConfigId AND sc.STATUS = 1"; // ── Register new test DB ───────────────────────────────────────────────── public const string INSERT_MSERVERCONFIG = @" INSERT INTO MSERVERCONFIG (CONNECTIONNAME, SERVERID, DATABASETYPE, DATABASENAME, DATABASEUSERNAME, DATABASEPASSWORD, DATABASEPORT, CONTROLSOURCEDBNAME, DBINSTANCETYPE, NATURE, DBSOURCETYPE, SYSTEMSERVERCONFIGID, CLIENTID, CLIENTSITEID, STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, OFFSET, MAXVALUE, PARENTSERVERCONFIGID, FCMCONFIG, SERVICESERVERID) OUTPUT INSERTED.SERVERCONFIGID VALUES (@ConnectionName, @ServerId, @DatabaseType, @DatabaseName, @DatabaseUserName, @DatabasePassword, @Port, @ControlSourceDbName, 1 /*TEST*/, 0, 5, -1, @ClientId, @ClientSiteId, 1, 0, 5, @CreatedById, GETDATE(), @CreatedById, GETDATE(), 0, 0, -1, '', -1)"; public const string GET_TEST_ENV_BY_ID = @" SELECT sc.SERVERCONFIGID AS ServerConfigId, sc.CONNECTIONNAME AS ConnectionName, sc.DATABASENAME AS DatabaseName, s.SERVERIP AS ServerIP, s.SERVERNAME AS ServerName, sc.DATABASETYPE AS DatabaseType, CASE sc.DATABASETYPE WHEN 0 THEN 'SQL Server' WHEN 1 THEN 'Oracle' WHEN 2 THEN 'PostgreSQL' WHEN 3 THEN 'MySQL' ELSE 'Unknown' END AS DatabaseTypeText, ISNULL(sc.CONTROLSOURCEDBNAME, '') AS ControlSourceDb, sc.STATUS AS Status, sc.DBINSTANCETYPE AS DbInstanceType, NULL AS LastResetOn, NULL AS LastResetBy, CAST(0 AS BIT) AS IsStale FROM MSERVERCONFIG sc JOIN MSERVER s ON sc.SERVERID = s.SERVERID WHERE sc.SERVERCONFIGID = @ServerConfigId AND sc.DBINSTANCETYPE = 1"; public const string GET_MSERVER_SELECT_LIST = @" SELECT SERVERID AS ServerId, SERVERNAME AS ServerName, SERVERIP AS ServerIP FROM MSERVER WHERE STATUS = 1 ORDER BY SERVERNAME"; public const string CHECK_CONNECTIONNAME_EXISTS = @" SELECT COUNT(1) FROM MSERVERCONFIG WHERE CONNECTIONNAME = @ConnectionName AND STATUS <> 2"; // ── Deregister ─────────────────────────────────────────────────────────── public const string DEREGISTER_TEST_DB = @" UPDATE MSERVERCONFIG SET STATUS = 2, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETDATE() WHERE SERVERCONFIGID = @ServerConfigId AND DBINSTANCETYPE = 1"; // safety: never deregister LIVE configs // ── Reset log (TCMS database — executed via IQueryExecutor, no 3-part name) ── public const string INSERT_RESET_LOG = @" INSERT INTO TTESTENVRESETLOG (SERVERCONFIGID, CONNECTIONNAME, CONTROLSOURCE, TRIGGEREDBY, TRIGGEREDON, DURATIONMS, SUCCESS, ERRORMESSAGE) OUTPUT INSERTED.RESETLOGID VALUES (@ServerConfigId, @ConnectionName, @ControlSource, @TriggeredBy, GETDATE(), @DurationMs, @Success, @ErrorMessage)"; public const string UPDATE_RESET_LOG_OUTCOME = @" UPDATE TTESTENVRESETLOG SET DURATIONMS = @DurationMs, SUCCESS = @Success, ERRORMESSAGE = @ErrorMessage WHERE RESETLOGID = @ResetLogId"; public const string GET_RESET_HISTORY = @" SELECT RESETLOGID AS ResetLogId, SERVERCONFIGID AS ServerConfigId, CONNECTIONNAME AS ConnectionName, CONTROLSOURCE AS ControlSource, TRIGGEREDBY AS TriggeredBy, TRIGGEREDON AS TriggeredOn, DURATIONMS AS DurationMs, SUCCESS AS Success, ERRORMESSAGE AS ErrorMessage FROM TTESTENVRESETLOG WHERE SERVERCONFIGID = @ServerConfigId ORDER BY TRIGGEREDON DESC"; // ── Schema migration helpers (one-time setup SQL, not used at runtime) ─── public const string CREATE_RESET_LOG_TABLE = @" IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DBO' AND TABLE_NAME = 'TTESTENVRESETLOG') BEGIN CREATE TABLE TCMS.DBO.TTESTENVRESETLOG ( RESETLOGID INT IDENTITY(1,1) PRIMARY KEY, SERVERCONFIGID INT NOT NULL, CONNECTIONNAME NVARCHAR(35) NOT NULL, CONTROLSOURCE NVARCHAR(100)NOT NULL, TRIGGEREDBY NVARCHAR(60) NOT NULL, TRIGGEREDON DATETIME NOT NULL DEFAULT GETDATE(), DURATIONMS INT, SUCCESS BIT NOT NULL DEFAULT 0, ERRORMESSAGE NVARCHAR(500) ); END"; public const string ADD_CONTROLSOURCEDBNAME_COLUMN = @" IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MSERVERCONFIG' AND COLUMN_NAME = 'CONTROLSOURCEDBNAME') BEGIN ALTER TABLE MSERVERCONFIG ADD CONTROLSOURCEDBNAME NVARCHAR(100) NOT NULL CONSTRAINT DF_MSERVERCONFIG_CTRLSRC DEFAULT ''; END"; }