Brainstorming/HostFerret/SQL/hf checkins: Difference between revisions

From Woozle Writes Code
Jump to navigation Jump to search
m (Woozle moved page User:Woozle/HostFerret/SQL/hf checkins to Ferreteria/modules/HostFerret/SQL/hf checkins without leaving a redirect: belongs here)
No edit summary
Line 2: Line 2:
'''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.
'''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).
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==
==Fields==

Revision as of 17:38, 8 July 2017

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

<mysql>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;</mysql>