labtech Database

Parameters

Name	Type	Mode
_configId	int(11)	INOUT
_computerIdentifier	varchar(36)	IN
_wpfRenderScore	int(11)	IN
_controlCenterVersion	varchar(50)	IN
_userId	int(11)	IN
_connectionId	int(11)	INOUT
_sessionIdentifier	varchar(36)	INOUT
_localIP	varchar(45)	IN
_externalIP	varchar(45)	IN
_tzOffset	bigint(20)	IN
_activityId	bigint(20)	IN
_actionType	int(11)	IN
_startTime	bigint(20)	IN
_midTime	bigint(20)	IN
_endTime	bigint(20)	IN
_tag	varchar(36)	IN

Definition

BEGIN
  -- Create an entry in ind_data and ensure that there are corresponding entries in ind_config and ind_connection
  -- This is needed because long-running and long-inactive control centers (24+ hours inactive) can have their
  -- ind_config and ind_connection entries purged during daily data upload to cloud.
  -- temp variables
  DECLARE t_computerIdentifier VARCHAR(36);
  DECLARE t_wpfRenderScore INT;
  DECLARE t_controlCenterVersion VARCHAR(50);
  DECLARE t_userId INT;
  DECLARE t_sessionIdentifier VARCHAR(36);
  DECLARE t_configId INT;
  SELECT ComputerIdentifier, WPFRenderScore, ControlCenterVersion, UserID
    INTO t_computerIdentifier, t_wpfRenderScore, t_controlCenterVersion, t_userId
    FROM ind_config
    WHERE ConfigID = _configId;
  --
  -- Is there a record of this configId ?
  IF t_computerIdentifier <> _computerIdentifier OR t_computerIdentifier IS NULL OR
    t_computerIdentifier <> _wpfRenderScore OR t_computerIdentifier IS NULL OR
    t_controlCenterVersion <> _controlCenterVersion OR t_controlCenterVersion IS NULL OR
    t_userId <> _userId OR t_userId IS NULL THEN
    -- try to find a record that has same info but different configId
    SET _configId = 0;
    SELECT ConfigID
      INTO _configId
      FROM ind_config
      WHERE ComputerIdentifier = _computerIdentifier AND
            WPFRenderScore = _wpfRenderScore AND
            ControlCenterVersion = _controlCenterVersion AND
            UserID = _userId LIMIT 1;
    IF _configId = 0 OR _configId IS NULL THEN
      -- No record: create it
      INSERT INTO ind_config (ComputerIdentifier, WPFRenderScore, ControlCenterVersion, UserID)
        VALUES (_computerIdentifier, _wpfRenderScore, _controlCenterVersion, _userId);
      SELECT LAST_INSERT_ID() INTO _configId;
    END IF;
  END IF;
  --
  -- connection id
  SELECT SessionIdentifier
    INTO t_sessionIdentifier
    FROM ind_connection
    WHERE ConfigID = _configId AND ConnectionID = _connectionId LIMIT 1;
  -- is there a record of the connectionId ?
  IF t_sessionIdentifier <> _sessionIdentifier OR t_sessionIdentifier IS NULL THEN
    -- no record with the same connectionId AND _configId, just try to find something that matches the sessionId
    SET _connectionId = 0;
    SELECT ConnectionID, ConfigID
      INTO _connectionId, t_configId
      FROM ind_connection
      WHERE SessionIdentifier = _sessionIdentifier;
    IF _connectionId = 0 OR _connectionId IS NULL THEN
      -- No record: create it
	  IF _sessionIdentifier = 0 OR _sessionIdentifier IS NULL THEN
		-- No session id: create it
		SELECT UUID() INTO _sessionIdentifier;
	  END IF;
      INSERT INTO ind_connection (ConfigID, SessionIdentifier, LocalIP, ExternalIP, TZOffset)
        VALUES (_configId, _sessionIdentifier, _localIP, _externalIP, _tzOffset);
      SELECT LAST_INSERT_ID() INTO _connectionId;
    ELSEIF t_configId <> _configId THEN
      UPDATE ind_connection SET ConfigID = _configId WHERE SessionIdentifier = _sessionIdentifier;
    END IF;
  END IF;
  -- insert the damn data
  INSERT INTO ind_data (ConnectionID, ActivityId, ActionType, StartTime, MidTime, EndTime, Tag)
    VALUES (_connectionId, _activityId, _actionType, _startTime, _midTime, _endTime, _tag);
END

sp_insert_ind_data

Parameters

Definition