<commit_before>-- ------------------------------------------------------------------------------
-- CloudRecords

CREATE TABLE CloudRecords ( 
  UpdateTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 

  VMUUID VARCHAR(255) NOT NULL, 
  SiteID INT NOT NULL,                -- Foreign key
  CloudComputeServiceID INT NOT NULL, -- Foreign key

  MachineName VARCHAR(255), 

  LocalUserId VARCHAR(255),
  LocalGroupId VARCHAR(255),

  GlobalUserNameID INT NOT NULL,      -- Foreign key
  FQAN VARCHAR(255),
  VOID INT NOT NULL,                  -- Foreign key
  VOGroupID INT NOT NULL,             -- Foreign key
  VORoleID INT NOT NULL,              -- Foreign key

  Status VARCHAR(255),

  StartTime DATETIME, 
  EndTime DATETIME, 
  SuspendDuration INT, 

  WallDuration INT, 
  CpuDuration INT, 
  CpuCount INT, 

  NetworkType VARCHAR(255),
  NetworkInbound INT, 
  NetworkOutbound INT, 
  PublicIPCount INT,
  Memory INT, 
  Disk INT, 

  BenchmarkType VARCHAR(50) NOT NULL,
  Benchmark DECIMAL(10,3) NOT NULL,

  StorageRecordId VARCHAR(255),
  ImageId VARCHAR(255),
  CloudType VARCHAR(255),

  PublisherDNID INT NOT NULL,	    -- Foreign key

  PRIMARY KEY (VMUUID),

  INDEX (UpdateTime),
  INDEX (GlobalUserNameID),
  INDEX (SiteID),
  INDEX (ImageID)

);


DROP PROCEDURE IF EXISTS ReplaceCloudRecord;
DELIMITER //
CREATE PROCEDURE ReplaceCloudRecord(
  VMUUID VARCHAR(255), site VARCHAR(255), cloudComputeService VARCHAR(255),
  machineName VARCHAR(255), 
  localUserId VARCHAR(255),
  localGroupId VARCHAR(255), globalUserName VARCHAR(255), 
  fqan VARCHAR(255), vo VARCHAR(255), 
  voGroup VARCHAR(255), voRole VARCHAR(255), status VARCHAR(255),
  startTime DATETIME, endTime DATETIME, 
  suspendDuration INT,
  wallDuration INT, cpuDuration INT, 
  cpuCount INT, networkType VARCHAR(255),  networkInbound INT, 
  networkOutbound INT, publicIPCount INT, memory INT, 
  disk INT, benchmarkType VARCHAR(50), benchmark DECIMAL(10,3), storageRecordId VARCHAR(255),
  imageId VARCHAR(255), cloudType VARCHAR(255),
  publisherDN VARCHAR(255))
BEGIN
    REPLACE INTO CloudRecords(VMUUID, SiteID, CloudComputeServiceID, MachineName, LocalUserId, LocalGroupId,
        GlobalUserNameID, FQAN, VOID, VOGroupID, VORoleID, Status, StartTime, EndTime, SuspendDuration,
        WallDuration, CpuDuration, CpuCount, NetworkType, NetworkInbound, NetworkOutbound, PublicIPCount, Memory, Disk, BenchmarkType, Benchmark, 
        StorageRecordId, ImageId, CloudType, PublisherDNID)
      VALUES (
        VMUUID, SiteLookup(site), CloudComputeServiceLookup(cloudComputeService), machineName, localUserId, localGroupId, DNLookup(globalUserName), 
        fqan, VOLookup(vo),
        VOGroupLookup(voGroup), VORoleLookup(voRole), status, startTime, endTime, suspendDuration, 
        wallDuration, cpuDuration, cpuCount, networkType, networkInbound, networkOutbound, publicIPCount, memory,
        disk, benchmarkType, benchmark, storageRecordId, imageId, cloudType, DNLookup(publisherDN)
        );
END //
DELIMITER ;


-- ------------------------------------------------------------------------------
-- CloudSummaries

DROP TABLE IF EXISTS CloudSummaries;
CREATE TABLE CloudSummaries (
  UpdateTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  SiteID INT NOT NULL, -- Foreign key
  CloudComputeServiceID INT NOT NULL, -- Foreign key

  Month INT NOT NULL,
  Year INT NOT NULL,<commit_msg>Add protoype v2 LRVM support - ReplaceCloudRecord<commit_after>-- ------------------------------------------------------------------------------
-- CloudRecords

CREATE TABLE CloudRecords ( 
  UpdateTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 

  VMUUID VARCHAR(255) NOT NULL, 
  SiteID INT NOT NULL,                -- Foreign key
  CloudComputeServiceID INT NOT NULL, -- Foreign key

  MachineName VARCHAR(255), 

  LocalUserId VARCHAR(255),
  LocalGroupId VARCHAR(255),

  GlobalUserNameID INT NOT NULL,      -- Foreign key
  FQAN VARCHAR(255),
  VOID INT NOT NULL,                  -- Foreign key
  VOGroupID INT NOT NULL,             -- Foreign key
  VORoleID INT NOT NULL,              -- Foreign key

  Status VARCHAR(255),

  StartTime DATETIME, 
  EndTime DATETIME, 
  MeasurementTime DATETIME NOT NULL,
  MeasurementMonth INT NOT NULL,
  MeasurementYear INT NOT NULL,

  SuspendDuration INT, 
  WallDuration INT, 
  CpuDuration INT, 
  CpuCount INT, 

  NetworkType VARCHAR(255),
  NetworkInbound INT, 
  NetworkOutbound INT, 
  PublicIPCount INT,
  Memory INT, 
  Disk INT, 

  BenchmarkType VARCHAR(50) NOT NULL,
  Benchmark DECIMAL(10,3) NOT NULL,

  StorageRecordId VARCHAR(255),
  ImageId VARCHAR(255),
  CloudType VARCHAR(255),

  PublisherDNID INT NOT NULL,	    -- Foreign key

  -- Measurement(Month, Year) allows for usage to be assigned
  -- to accounting periods at load time
  PRIMARY KEY (VMUUID, MeasurementMonth, MeasurementYear),

  INDEX (UpdateTime),
  INDEX (GlobalUserNameID),
  INDEX (SiteID),
  INDEX (ImageID)

);


DROP PROCEDURE IF EXISTS ReplaceCloudRecord;
DELIMITER //
CREATE PROCEDURE ReplaceCloudRecord(
  VMUUID VARCHAR(255), site VARCHAR(255), cloudComputeService VARCHAR(255),
  machineName VARCHAR(255), 
  localUserId VARCHAR(255),
  localGroupId VARCHAR(255), globalUserName VARCHAR(255), 
  fqan VARCHAR(255), vo VARCHAR(255), 
  voGroup VARCHAR(255), voRole VARCHAR(255), status VARCHAR(255),
  startTime DATETIME, endTime DATETIME, 
  suspendDuration INT,
  wallDuration INT, cpuDuration INT, 
  cpuCount INT, networkType VARCHAR(255),  networkInbound INT, 
  networkOutbound INT, publicIPCount INT, memory INT, 
  disk INT, benchmarkType VARCHAR(50), benchmark DECIMAL(10,3), storageRecordId VARCHAR(255),
  imageId VARCHAR(255), cloudType VARCHAR(255),
  publisherDN VARCHAR(255))

BEGIN
    DECLARE suspendDurationNotNull INT;
    DECLARE cpuDurationNotNull INT;
    DECLARE wallDurationNotNull INT;
    DECLARE measurementTimeCalculated DATETIME;
    
    -- If the incoming suspendDuration is NULL, we can't compute a MeasurementTime
    -- so set a resonable value in that case
    -- DECLARE suspendDurationNotNull INT;
    SET suspendDurationNotNull = IFNULL(suspendDuration, 0);

    -- If the incoming cpuDuration is NULL, we may not be able to compute a MeasurementTime
    -- as cpuDuration is used to calculate MeasurementTime if the incoming wallDuration is also NULL
    -- so set a resonable value so we can use that if needed
    SET cpuDurationNotNull = IFNULL(cpuDuration, 0);


    -- If the incoming wallDuartion is NULL we can't compute a MeasurementTime.
    -- Based off LRVMv1, we use cpuDurationNotNull, which from above is guaranteed
    -- to be a resonable value
    SET wallDurationNotNull = IFNULL(wallDuration,cpuDurationNotNull);

    -- Calculated the time of measurement so we can use it later to determine which
    -- accounting period this incoming record belongs too.
    SET measurementTimeCalculated = TIMESTAMPADD(SECOND, (suspendDurationNotNull + wallDurationNotNull), StartTime);

    INSERT INTO CloudRecords(VMUUID, SiteID, CloudComputeServiceID, MachineName,
        LocalUserId, LocalGroupId, GlobalUserNameID, FQAN, VOID, VOGroupID,
        VORoleID, Status, StartTime, EndTime, MeasurementTime, MeasurementMonth,
        MeasurementYear, SuspendDuration, WallDuration, CpuDuration, CpuCount,
        NetworkType, NetworkInbound, NetworkOutbound, PublicIPCount, Memory, Disk,
        BenchmarkType, Benchmark, StorageRecordId, ImageId, CloudType, PublisherDNID)
      VALUES (
        VMUUID, SiteLookup(site), CloudComputeServiceLookup(cloudComputeService), machineName,
        localUserId, localGroupId, DNLookup(globalUserName), fqan, VOLookup(vo), VOGroupLookup(voGroup),
        VORoleLookup(voRole), status, startTime, endTime, measurementTimeCalculated, Month(measurementTimeCalculated), Year(measurementTimeCalculated),
        suspendDurationNotNull, wallDurationNotNull, cpuDurationNotNull, cpuCount,
        networkType, networkInbound, networkOutbound, publicIPCount, memory, disk,
        benchmarkType, benchmark, storageRecordId, imageId, cloudType, DNLookup(publisherDN))
      ON DUPLICATE KEY UPDATE
        -- Then the incoming record belong in a accounting period which we
        -- already have some data (for this VM/Site)
        -- If the incoming measurementTime is greater than the currently stored one
        -- update all columns with the new values.
        -- It's possible these updates do not occur in an "all or nothing" fashion
        -- as per https://thewebfellas.com/blog/conditional-duplicate-key-updates-with-mysql
        -- so measurementTime is the last thing to be updated.
        -- There is no nicer way to do the 'ON DUPLICATE KEY UPDATE'
        -- other than field by field, and we can't get around making the greater than
        -- comparison everytime as we can only reference the current value in the
        -- 'ON DUPLICATE KEY UPDATE' block
        CloudRecords.SiteID = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, SiteLookup(site), CloudRecords.SiteID),
        CloudRecords.CloudComputeServiceID = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, CloudComputeServiceLookup(cloudComputeService), CloudRecords.CloudComputeServiceID),
        CloudRecords.MachineName = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, machineName, CloudRecords.MachineName),
        CloudRecords.LocalUserId = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, localUserId, CloudRecords.LocalUserId),
        CloudRecords.LocalGroupId = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, localGroupId, CloudRecords.LocalGroupId),
        CloudRecords.GlobalUserNameID = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, DNLookup(globalUserName), CloudRecords.GlobalUserNameID),
        CloudRecords.FQAN = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, fqan, CloudRecords.FQAN),
        CloudRecords.VOID = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, VOLookup(vo), CloudRecords.VOID),
        CloudRecords.VOGroupID = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, VOGroupLookup(voGroup), CloudRecords.VOGroupID),
        CloudRecords.VORoleID = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, VORoleLookup(voRole), CloudRecords.VORoleID),
        CloudRecords.Status = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, status, CloudRecords.Status),
        CloudRecords.StartTime = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, startTime, CloudRecords.StartTime),
        CloudRecords.EndTime = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, endTime, CloudRecords.EndTime),
        CloudRecords.SuspendDuration = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, suspendDurationNotNull, CloudRecords.SuspendDuration),
        CloudRecords.WallDuration = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, wallDurationNotNull, CloudRecords.WallDuration),
        CloudRecords.CpuDuration = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, cpuDurationNotNull, CloudRecords.CpuDuration),
        CloudRecords.CpuCount = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, cpuCount, CloudRecords.CpuCount),
        CloudRecords.NetworkType = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, networkType, CloudRecords.NetworkType),
        CloudRecords.NetworkInbound = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, networkInbound, CloudRecords.NetworkInbound),
        CloudRecords.NetworkOutbound = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, networkOutbound, CloudRecords.NetworkOutbound),
        CloudRecords.PublicIPCount = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, publicIPCount, CloudRecords.PublicIPCount),
        CloudRecords.Memory = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, memory, CloudRecords.Memory),
        CloudRecords.Disk = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, disk, CloudRecords.Disk),
        CloudRecords.BenchmarkType = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, benchmarkType, CloudRecords.BenchmarkType),
        CloudRecords.Benchmark = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, Benchmark, CloudRecords.Benchmark),
        CloudRecords.StorageRecordId = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, storageRecordId, CloudRecords.StorageRecordId),
        CloudRecords.ImageId = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, imageId, CloudRecords.ImageId),
        CloudRecords.CloudType = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, cloudType, CloudRecords.CloudType),
        CloudRecords.PublisherDNID = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, DNLookup(publisherDN), CloudRecords.PublisherDNID),

        CloudRecords.MeasurementMonth = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, Month(measurementTimeCalculated), CloudRecords.MeasurementMonth),
        CloudRecords.MeasurementYear = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, Year(measurementTimeCalculated), CloudRecords.MeasurementYear),

        CloudRecords.MeasurementTime = IF(measurementTimeCalculated > CloudRecords.MeasurementTime, measurementTimeCalculated, CloudRecords.MeasurementTime)
    ;
END //
DELIMITER ;


-- ------------------------------------------------------------------------------
-- CloudSummaries

DROP TABLE IF EXISTS CloudSummaries;
CREATE TABLE CloudSummaries (
  UpdateTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  SiteID INT NOT NULL, -- Foreign key
  CloudComputeServiceID INT NOT NULL, -- Foreign key

  Month INT NOT NULL,
  Year INT NOT NULL,
{'fraction_non_alphanumeric': 0.09385136596266129, 'fraction_numerical': 0.013407337994665898, 'mean_word_length': 3.9978386167146973, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 35, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3662859', 'n_tokens_mistral': 4069, 'n_tokens_neox': 3669, 'n_words': 1030}