FileFerret/SQL/firevs

About
"Firev" is short for "file revision". A firev is a particular series of byte values that represents a particular revision, or version, of a ../terms/fideal. Fideal records are generally only created when multiple copies of the same file are found, though they may also be created manually in order to track more information about a file.

A firev can be created either deliberately (as a result of user editing) or accidentally (imperfect transcription, drive corruption, viral infection).

Fields

 * isDelib: TRUE if this is known to be a deliberate user edit of the parent firev (must be FALSE if isAccid is TRUE)
 * isAccid: TRUE if this is known to be an accidental corruption of the parent firev (must be FALSE if isDelib is TRUE)
 * Descr should describe, if known, what distinguishes this firev from other firevs of the same fideal (or from the parent firev, if there is one).
 * FileHash is a unique fingerprint (hash) of the file, using an algorithm unlikely to produce duplicate hashes for even the smallest variation. See hash for details; apparently these can be as long as 128 hexadecimal digits = 64 bytes.

I think we're also going to want a field, possibly called FileMean, which changes very little (or not at all) when there are small changes to the data. This should make it easier to connect firevs to a fideal.

Rules

 * isDelib and isAccid can both be FALSE if the cause of the firev is not known (i.e. we know there's a knew firev, but not whether it was deliberate or accidental), but they cannot both be TRUE.

History

 * 2012-12-25 Adapted/created from fideals

SQL
CREATE TABLE `firevs` ( `ID`        INT  NOT     NULL AUTO_INCREMENT,  `ID_Fideal` INT  DEFAULT NULL  COMMENT "ID of ideal file",  `ID_Parent` INT  DEFAULT NULL  COMMENT "ID of parent firev, if any",  `isDelib`   BOOL DEFAULT FALSE COMMENT "TRUE = this is known to be a deliberate user edit of the parent",  `isAccid`   BOOL DEFAULT FALSE COMMENT "TRUE = this is known to be an accidental corruption of the parent",  `Descr`     VARCHAR(255)       COMMENT "description of differences",  `FileSize`  INT                COMMENT "correct file size in bytes",  `FileHash`  VARBINARY(64)      COMMENT "unique hash",  PRIMARY KEY(`ID`) ) ENGINE = MYISAM;