AudioFerret/v2/topics

About

• Notes: My thinking is that any additional details can go on a wiki page whose name will be built from NameTree.
• History:
  • 2012-12-24 adapted from VbzCart/tables/cat topic

SQL

<mysql>CREATE TABLE `topics` (

 `ID`        INT              NOT NULL AUTO_INCREMENT,
 `ID_Parent` INT          DEFAULT NULL COMMENT "topic.ID of parent topic",
 `Name`      VARCHAR(128)     NOT NULL COMMENT "generic name (context-free, but as short as possible)",
 `NameTree`  VARCHAR(64)  DEFAULT NULL COMMENT "name within context of parent; defaults to Name",
 `NameFull`  VARCHAR(255) DEFAULT NULL COMMENT "descriptive standalone name (context free, can be long)",
 `NameMeta`  VARCHAR(144) DEFAULT NULL COMMENT "plain text for HTML META description tag",
 `Usage`     VARCHAR(255) DEFAULT NULL COMMENT "usage instructions for this topic - show when editing topic",
 `Sort`      VARCHAR(15)  DEFAULT NULL COMMENT "optional sorting key",
 `Variants`  VARCHAR(255) DEFAULT NULL COMMENT "synonyms and other keywords which should find this topic",
 `Mispeled`  VARCHAR(255) DEFAULT NULL COMMENT "same as Variants, but these are WRONG spellings; avoid displaying",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql>

Migration

Table with temporary fields for migration: <mysql>CREATE TABLE `topics` (

 `ID`        INT              NOT NULL AUTO_INCREMENT,
 `ID_Parent` INT          DEFAULT NULL COMMENT "topic.ID of parent topic",
 `ID_Album`  INT          DEFAULT NULL COMMENT "temp field for migration",
 `ID_Artist` INT          DEFAULT NULL COMMENT "temp field for micration",
 `Name`      VARCHAR(128)     NOT NULL COMMENT "generic name (context-free, but as short as possible)",
 `NameTree`  VARCHAR(64)  DEFAULT NULL COMMENT "name within context of parent; defaults to Name",
 `NameFull`  VARCHAR(255) DEFAULT NULL COMMENT "descriptive standalone name (context free, can be long)",
 `NameMeta`  VARCHAR(144) DEFAULT NULL COMMENT "plain text for HTML META description tag",
 `Usage`     VARCHAR(255) DEFAULT NULL COMMENT "usage instructions for this topic - show when editing topic",
 `Sort`      VARCHAR(15)  DEFAULT NULL COMMENT "optional sorting key",
 `Variants`  VARCHAR(255) DEFAULT NULL COMMENT "synonyms and other keywords which should find this topic",
 `Mispeled`  VARCHAR(255) DEFAULT NULL COMMENT "same as Variants, but these are WRONG spellings; avoid displaying",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql> Migrating (old) artists to (new) topics -- first create master topics for "artists" and "albums"... [still working this out] <mysql>INSERT INTO aferret.topics(ID_Parent,ID_Artist, </mysql>