Ferret File System/design

FFS: let's just make some tables and see what we can get working

Tables

Content

CREATE TABLE
  `content` (
    `ID`        int(10) unsigned NOT NULL AUTO_INCREMENT,
    `WhenFirst` timestamp NOT NULL DEFAULT current_timestamp(),
    `WhenFinal` timestamp NOT NULL,
    `HashType`  varchar(255) NOT NULL,
    `HashData`  varchar(255) NOT NULL,
    `Bytes`     bigint(20) NOT NULL,
    PRIMARY KEY (`ID`)
  ) ENGINE = InnoDB

Filesystem Entries

CREATE TABLE
  `fs_entry` (
    `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `ID_Parent` int(11) DEFAULT NULL,
    `FirstSeen` timestamp NOT NULL DEFAULT current_timestamp(),
    `FirstGone` timestamp NULL DEFAULT NULL COMMENT 'NULL if found on last pass',
    `Name` varchar(255) NOT NULL,
    PRIMARY KEY (`ID`)
  ) ENGINE = InnoDB

Spidering

CREATE TABLE
  `spider` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `WhenStart`  timestamp NOT NULL DEFAULT current_timestamp() COMMENT 'when the crawl started',
    `WhenDone`   timestamp NULL DEFAULT NULL COMMENT 'when it finished',
    `ID_Entry`   int(11) NOT NULL COMMENT 'top of tree being crawled',
    PRIMARY KEY (`id`)
  ) ENGINE = InnoDB

Spidering Entry

CREATE TABLE
  `spider_entry` (
    `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `WhenOpen`   timestamp NOT NULL DEFAULT current_timestamp(),
    `WhenShut`   timestamp NULL DEFAULT NULL,
    `ID_Spider`  int(11) NOT NULL,
    `ID_Entry`   int(11) NOT NULL,
    `ID_Hash`    int(11) NOT NULL,
    `isFound`    tinyint(1) NOT NULL,
    PRIMARY KEY (`ID`)
  ) ENGINE = InnoDB