Brainstorming/HostFerret/SQL/hf checkins

About

Purpose: Rather than log each and every single checkin, we'll just keep statistics about stretches of checkins that fall within expected time-limits. That is, as long as a checkin isn't too late, we'll just treat it as part of a stretch of checkins and keep stats about the stretch. Each record is actually a stretch; maybe I should rename the table.

Also for now, there will be no security – i.e. you don't have to have an account or permission to log a check-in (though you will need to have permission to access the logs). We will need security in order to implement DNS host record updating; otherwise an attacker could claim to be one of our hosts and put up a page which looks just like one of our login pages.

Fields

• Stats*: These collect enough information to allow mean and variance/standard deviation to be calculated.
  • Stats_Count (N): number of checkins total
  • Stats_DiffSum: total of all time-intervals (note that this should be divided by (N-1) to produce mean
  • Stats_DiffSSq: sum of time-interval squares

SQL

CREATE TABLE `hf_checkins` (
  `ID`                   INT NOT NULL AUTO_INCREMENT,
  `NameGiven`       VARCHAR(255) NULL COMMENT "self-identifying string offered by remote",
  `WhenStart`       DATETIME NOT NULL COMMENT "when the stretch started",
  `ID_Session`           INT NOT NULL COMMENT "session.ID of checkin connection",
  `Stats_Count`          INT NOT NULL COMMENT "number of checkins in this stretch",
  `Stats_DiffSum`        INT NOT NULL COMMENT "SUM(X) where X is interval since previous checkin",
  `Stats_DiffSSq`        INT NOT NULL COMMENT "SUM(X^2)",
   PRIMARY KEY(`ID`)
)
ENGINE = InnoDB;