Ferret File System/v0.1/SQL/firev: Difference between revisions
< Ferret File System | v0.1 | SQL
Jump to navigation
Jump to search
(moved definition to terminology section) |
(updated markup tag; MYISAM -> InnoDB) |
||
| Line 21: | Line 21: | ||
** Renamed "firevs" -> "firev". | ** Renamed "firevs" -> "firev". | ||
==SQL== | ==SQL== | ||
<mysql>CREATE TABLE `firev` ( | <syntaxhighlight lang=mysql>CREATE TABLE `firev` ( | ||
`ID` INT NOT NULL AUTO_INCREMENT, | `ID` INT NOT NULL AUTO_INCREMENT, | ||
`isDelib` BOOL DEFAULT FALSE COMMENT "TRUE = this is known to be a deliberate user edit of the parent", | `isDelib` BOOL DEFAULT FALSE COMMENT "TRUE = this is known to be a deliberate user edit of the parent", | ||
| Line 32: | Line 32: | ||
PRIMARY KEY(`ID`) | PRIMARY KEY(`ID`) | ||
) | ) | ||
ENGINE = | ENGINE = InnoDB;</syntaxhighlight> | ||
===scrapped=== | ===scrapped=== | ||
These might get used later, but I suspect they don't belong here: | These might get used later, but I suspect they don't belong here: | ||
<mysql> | <syntaxhighlight lang=mysql> | ||
`ID_Fideal` INT DEFAULT NULL COMMENT "ID of ideal file", | `ID_Fideal` INT DEFAULT NULL COMMENT "ID of ideal file", | ||
`ID_Parent` INT DEFAULT NULL COMMENT "ID of parent firev, if any", | `ID_Parent` INT DEFAULT NULL COMMENT "ID of parent firev, if any", | ||
</ | </syntaxhighlight> | ||
Revision as of 00:16, 4 December 2017
About
- Purpose: tracks revisions to files (see terms/firev)
- Future:
- We may want to support multiple types of hash, in which case there would be a separate firev_hash table containing the output of each hash-type for each firev hashed.
- not sure if we should be tracking FirstFound and LastFound here. I expect them to be useful, but they might be better determined by looking at the event records.
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 new firev, but not whether it was deliberate or accidental), but they cannot both be TRUE.
History
- 2012-12-25 Adapted/created from fideals
- 2016-02-27
- Added FirstFound, LastFound timestamps.
- Scrapped Fideal, Parent.
- Renamed "firevs" -> "firev".
SQL
CREATE TABLE `firev` (
`ID` INT NOT NULL AUTO_INCREMENT,
`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",
`FirstFound` DATETIME NOT NULL COMMENT "time/date when file was first found during a scan",
`LastFound` DATETIME NOT NULL COMMENT "time/date when file was most recently found during a scan",
PRIMARY KEY(`ID`)
)
ENGINE = InnoDB;
scrapped
These might get used later, but I suspect they don't belong here:
`ID_Fideal` INT DEFAULT NULL COMMENT "ID of ideal file",
`ID_Parent` INT DEFAULT NULL COMMENT "ID of parent firev, if any",