Hei, or hi, see below screen and the main Stored Procedure that the BOTs will check for. I hope you like it, or not.
as said, the SP
USE [superbots]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SERVERS.BOTS.INSTANCES.SelectOrCreateAndSelect] (
@Bot_Name NVarChar(8) = N'WinForms'
, @OS NVarChar(16), @OS_ID int = NULl
, @Server_Serial nvarchar(64), @Server_MachineName NVarChar(32), @Server_Language NChar(2) = N'es'
, @Server_PhysicalCores tinyInt, @Server_LogicalCores tinyint, @Server_PhysicalCoresMaxMhz tinyInt
, @Server_ID int = NULL
, @Settings_Bots_Instances_MaxMinsOfInactivity tinyInt = NULL
, @Language nchar(2) = NULL, @Country nchar(4) = NULL
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Bot_ID tinyINT;
DECLARE @Bot_Instance_ID INT;
DECLARE @Server_MaxBotInstances TinyINT;
SET @OS_ID = (SELECT TOP 1 [OS.ID] FROM dbo.[OS] AS O WHERE O.[OS] = @OS)
IF EXISTS (
SELECT * FROM
dbo.[Servers] AS S WHERE S.[Server.MachineName] = @Server_MachineName
)
BEGIN
SELECT TOP 1 @Server_ID = [Server.ID], @Server_MaxBotInstances = [Server.MaxBotInstances] FROM dbo.[Servers] AS S WHERE S.[Server.MachineName] = @Server_MachineName AND S.[Server.Active] = 1
END
ELSE
BEGIN
INSERT INTO dbo.[Servers] ([OS.ID], [Server.MachineName], [Server.Language],[Server.PhysicalCores], [Server.LogicalCores], [Server.PhysicalCoresMaxMhz] ) VALUES (@OS_ID, @Server_MachineName,@Server_Language,@Server_PhysicalCores,@Server_LogicalCores, @Server_PhysicalCoresMaxMhz )
SET @Server_ID = (SELECT SCOPE_IDENTITY())
SET @Server_MaxBotInstances = (SELECT [Server.MaxBotInstances] FROM dbo.[SERVERS] AS S WHERE S.[Server.ID] = @Server_ID )
END;
-- Buscar instancia de de ultima fecha al menos 6 horas desde ahora que no tenga actividad
SET @Settings_Bots_Instances_MaxMinsOfInactivity = (SELECT TOP 1 [Settings.Bots.Instances.MaxMinsOfInactivity] FROM dbo.[SETTINGS] AS STT WHERE STT.[Setting.IsDefault] = 1 ORDER BY STT.[Setting.ID] DESC)
SET @Bot_ID = (SELECT [Bot.ID] FROM dbo.[BOTS] AS B WHERE B.[Bot.Name] = @Bot_Name )
--VER SI EXISTE INSTANCIA O SE DEBE CREAR
IF EXISTS(
SELECT * FROM dbo.[SERVERS.BOTS.INSTANCES] AS SBI
INNER JOIN dbo.[BOT.INSTANCES] AS BI ON BI.[Bot.Instance.ID] = SBI.[Bot.Instance.ID]
INNER JOIN dbo.[BOTS] AS B ON B.[Bot.ID] = BI.[Bot.ID]
INNER JOIN dbo.[SERVERS] AS S ON S.[Server.ID] = SBI.[Server.ID]
WHERE B.[Bot.Physical] = 0 AND BI.[Bot.Instance.Active] = 1 AND B.[Bot.Name] = @Bot_Name
AND SBI.[Server.ID] = @Server_ID
AND BI.[Bot.Instance.LastOperationDate] < DATEADD(MINUTE,@Settings_Bots_Instances_MaxMinsOfInactivity,SYSDATETIME())
)
BEGIN
SELECT @Bot_Instance_ID = BI.[Bot.Instance.ID]
FROM dbo.[SERVERS.BOTS.INSTANCES] AS SBI
INNER JOIN dbo.[BOT.INSTANCES] AS BI ON BI.[Bot.Instance.ID] = SBI.[Bot.Instance.ID]
INNER JOIN dbo.[BOTS] AS B ON B.[Bot.ID] = BI.[Bot.ID]
INNER JOIN dbo.[SERVERS] AS S ON S.[Server.ID] = SBI.[Server.ID]
WHERE B.[Bot.Physical] = 0 AND BI.[Bot.Instance.Active] = 1 AND B.[Bot.Name] = @Bot_Name
AND SBI.[Server.ID] = @Server_ID
AND BI.[Bot.Instance.LastOperationDate] < DATEADD(MINUTE,@Settings_Bots_Instances_MaxMinsOfInactivity,SYSDATETIME())
END
ELSE
BEGIN
-- NO EXISTE NINGUNA INSTANCIA, DEBE CREARSE PUES, SIEMPRE Y CUANDO NO NOS SOBREPASEMOS DE INSTANCIAS POR SERVIDOR
DECLARE @ActualInstances tinyINT = 0;
--CHECKEAR QUE NO SE HAYA SOBREPASADO, O IGUALADO EL LIMITE DE MAX INSTANCIAS POR SERVER
SELECT @ActualInstances = COUNT(*)
FROM dbo.[SERVERS.BOTS.INSTANCES] AS SBI
INNER JOIN dbo.[BOT.INSTANCES] AS BI ON BI.[Bot.Instance.ID] = SBI.[Bot.Instance.ID]
INNER JOIN dbo.[BOTS] AS B ON B.[Bot.ID] = BI.[Bot.ID]
INNER JOIN dbo.[SERVERS] AS S ON S.[Server.ID] = SBI.[Server.ID]
WHERE B.[Bot.Physical] = 0 AND BI.[Bot.Instance.Active] = 1 AND B.[Bot.Name] = @Bot_Name
AND SBI.[Server.ID] = @Server_ID
AND BI.[Bot.Instance.LastOperationDate] < DATEADD(MINUTE,@Settings_Bots_Instances_MaxMinsOfInactivity,SYSDATETIME())
IF @ActualInstances >= @Server_MaxBotInstances
BEGIN
RETURN SELECT -4
END
ELSE
BEGIN
--CREAR INSTANCIA: BOTSINSTANCES, SERVER.BOT.INSTANCES
INSERT INTO dbo.[BOTS.INSTANCES]
([Bot.ID], [IP.ID] )
VALUES
(@Bot_ID,
(
SELECT TOP 1 [IP.ID] FROM [IPS] AS I WHERE NOT I.[IP.ID] IN (
SELECT BI.[IP.ID] FROM dbo.[SERVERS.BOTS.INSTANCES] AS SBI
INNER JOIN dbo.[BOT.INSTANCES] AS BI ON BI.[Bot.Instance.ID] = SBI.[Bot.Instance.ID]
INNER JOIN dbo.[BOTS] AS B ON B.[Bot.ID] = BI.[Bot.ID]
INNER JOIN dbo.[SERVERS] AS S ON S.[Server.ID] = SBI.[Server.ID]
WHERE B.[Bot.Physical] = 0 AND BI.[Bot.Instance.Active] = 1
AND B.[Bot.Name] = @Bot_Name
AND SBI.[Server.ID] = @Server_ID
AND BI.[Bot.Instance.LastOperationDate] < DATEADD(MINUTE,@Settings_Bots_Instances_MaxMinsOfInactivity,SYSDATETIME())
) AND I.[Country] = ISNULL(@Country, I.[Country])
AND LEFT(I.[Country],2) = ISNULL(@language, LEFT(I.[Country],2))
)
)
SET @Bot_Instance_ID = (SELECT SCOPE_IDENTITY())
END
END
IF NOT EXISTS (SELECT * FROM dbo.[SERVERS.BOTS.INSTANCES] WHERE [Server.ID] = @Server_ID AND [Bot.Instance.ID] = @Bot_Instance_ID )
BEGIN
INSERT INTO dbo.[SERVERS.BOTS.INSTANCES] ([Server.ID], [Bot.Instance.ID] ) VALUES ( @Server_ID, @Bot_Instance_ID)
END
SELECT * FROM dbo.[SERVERS.BOTS.INSTANCES] AS SBI
INNER JOIN dbo.[SERVERS] AS S ON S.[Server.ID] = SBI.[Server.ID]
INNER JOIN dbo.[OS] AS O ON O.[OS.ID] = S.[OS.ID]
INNER JOIN dbo.[BOTS.INSTANCES] AS BI ON BI.[Bot.Instance.ID] = SBI.[Bot.Instance.ID]
INNER JOIN dbo.[IPS] AS I ON I.[IP.ID] = BI.[IP.ID]
INNER JOIN dbo.[BOTS] AS B ON B.[Bot.ID] = BI.[Bot.ID]
INNER JOIN dbo.[ACCOUNTS] AS A ON A.[Account.ID] = BI.[Account.ID]
INNER JOIN dbo.[ACCOUNTS.KEYWORDS] AS AK ON AK.[Account.ID] = A.[Account.ID]
WHERE SBI.[Bot.Instance.ID] = @Bot_Instance_ID AND S.[Server.ID] = @Server_ID AND S.[Server.Active] = 1 AND BI.[Bot.Instance.Active] = 1
--PENDIENTE: COMO RELACIONAR UN ACCOUNT Y UNOS HUMANS, CON LA INSTANCIA DEL BOT ACTUAL ?
-- PONER Account.Id en Bot.Instancias ? SI
-- Y PARA ELLO HACER CUENTA ACCOUNTS (la actual devices.account renombrarla) y en vez de poner account id como property de devices o de server.bla bla, ponerla como propiedad de bot.instanes
--una vez finalizado, pegar en doninam y luego testear y actualizar doninam si fuere el caso
END