Difference between revisions of "InstaGov/schema/answer"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(updated thoughts -- probably won't use this table)
(migration SQL)
 
(8 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
==About==
 
==About==
−
* '''Rules''': Any question may have zero or more answers. New answers can be added at any time.
+
* '''Purpose''': implements {{igov/term|answer}}s
−
* '''Notes''':  
+
* '''Rules''': Any {{l/same|question}} may have zero or more answers. New answers can be added at any time.
−
** "WhenExpires" field: same applies as in "{{instagov|schema|question}}" table.
+
* '''Notes''':
−
** '''2011-06-06''' This table may end up not being used, since I've found I can use [[Semantic MediaWiki|SMW]] to store answer data. Also, I'm thinking it should be "choice" rather than "answer", but since it's no longer hard-coded, this is just a matter of updating documentation (and one field name in the  [[../rating|rating]] table.
+
** This table originally had a "WhenExpires" field; it was removed for the same reasons given in notes for the "{{l/same|question}}" table.
 +
==History==
 +
* '''2013-01-07'''
 +
** Added "Summary" field, which I had removed earlier when I was still thinking of letting this be heavily dependent on MW, and who/when fields.
 +
** Added UNIQUE KEY "content" to prevent duplicate answers for the same question.
 +
* '''2013-07-21''' Revising to use {{l/pferret}}; many redundant fields removed. Also removed Pos_Text and Neg_Text fields for now.
 +
** The '''*_Text''' fields were intended as optional overrides to the values in {{l/same|question}}, but the latter has not yet been implemented; I think I may have decided at some point that it wasn't necessary.
 
==SQL==
 
==SQL==
−
<section begin=sql /><mysql>CREATE TABLE `answer` (
+
<mysql>CREATE TABLE `answer` (
−
   `ID` INT NOT NULL AUTO_INCREMENT,
+
  `ID_Post`    INT          NOT NULL COMMENT "post.ID for content",
−
   `ID_Question` INT NO NULL COMMENT "question.ID for which this is an answer",
+
  `ID_Question` INT          NOT NULL COMMENT "question.ID (actually atom.ID) for which this is an answer",
−
   `Page` varchar(127) COMMENT "wiki page describing answer",
+
  PRIMARY KEY(`ID_Post`)
−
   `WhenCreated` DATETIME NOT NULL COMMENT "when this answer was entered",
+
)
 +
ENGINE = MYISAM;</mysql>
 +
==Obsolete==
 +
<mysql>CREATE TABLE `answer` (
 +
   `ID`         INT         NOT NULL AUTO_INCREMENT,
 +
   `ID_Question` INT         NOT NULL COMMENT "question.ID for which this is an answer",
 +
   `Summary` VARCHAR(255)   NOT NULL COMMENT "short description of answer",
 +
   `Page`     VARCHAR(127)            COMMENT "URI of optional site page giving more details",
 
   `Pos_Text` VARCHAR(63) DEFAULT NULL COMMENT "optional: text to show at the 'yes' end of the scale (overrides question default)",
 
   `Pos_Text` VARCHAR(63) DEFAULT NULL COMMENT "optional: text to show at the 'yes' end of the scale (overrides question default)",
 
   `Neg_Text` VARCHAR(63) DEFAULT NULL COMMENT "optional: text to show at the 'no' end of the scale (overrides question default)",
 
   `Neg_Text` VARCHAR(63) DEFAULT NULL COMMENT "optional: text to show at the 'no' end of the scale (overrides question default)",
−
   PRIMARY KEY(`ID`)
+
  `WhenCreated` DATETIME    NOT NULL COMMENT "when this records was entered",
 +
  `WhoCreated`  INT          NOT NULL COMMENT "ID of user who added the record",
 +
  `WhenEdited`  DATETIME DEFAULT NULL COMMENT "when this question was last edited",
 +
  `WhoEdited`  INT      DEFAULT NULL COMMENT "ID of user who last edited the record",
 +
  `QtyEdits`    INT      DEFAULT NULL COMMENT "number of times this record has been edited",
 +
   PRIMARY KEY(`ID`),
 +
  UNIQUE KEY `content` (`ID_Question`,`Summary`)
 
  )
 
  )
 
  ENGINE = MYISAM;</mysql>
 
  ENGINE = MYISAM;</mysql>
−
<section end=sql />
+
For migrating data from old schema to new (some fields omitted):
 +
<mysql>INSERT INTO atom (Class,WhenCreated,WhoCreated,WhenEdited,QtyEdits)
 +
SELECT "an",WhenCreated,WhoCreated,WhenEdited,QtyEdits
 +
FROM OLD_answer;
 +
 
 +
INSERT INTO post (ID_Atom,ID_Owner,Summary,Body)
 +
SELECT atom.ID,NULL,Summary,Body
 +
FROM OLD_answer AS a LEFT JOIN atom ON a.WhenCreated=atom.WhenCreated;
 +
 
 +
INSERT INTO answer (ID_Post,ID_Question)
 +
SELECT ID_Atom,ID_Question
 +
FROM OLD_answer AS a LEFT JOIN post ON a.Summary=post.Summary;
 +
</mysql>

Latest revision as of 20:13, 21 July 2013

About

  • Purpose: implements answers
  • Rules: Any question may have zero or more answers. New answers can be added at any time.
  • Notes:
    • This table originally had a "WhenExpires" field; it was removed for the same reasons given in notes for the "question" table.

History

  • 2013-01-07
    • Added "Summary" field, which I had removed earlier when I was still thinking of letting this be heavily dependent on MW, and who/when fields.
    • Added UNIQUE KEY "content" to prevent duplicate answers for the same question.
  • 2013-07-21 Revising to use PostFerret; many redundant fields removed. Also removed Pos_Text and Neg_Text fields for now.
    • The *_Text fields were intended as optional overrides to the values in question, but the latter has not yet been implemented; I think I may have decided at some point that it wasn't necessary.

SQL

<mysql>CREATE TABLE `answer` ( 

  `ID_Post`     INT          NOT NULL COMMENT "post.ID for content",
  `ID_Question` INT          NOT NULL COMMENT "question.ID (actually atom.ID) for which this is an answer",
  PRIMARY KEY(`ID_Post`)
)
ENGINE = MYISAM;</mysql>

Obsolete

<mysql>CREATE TABLE `answer` ( 

  `ID`          INT          NOT NULL AUTO_INCREMENT,
  `ID_Question` INT          NOT NULL COMMENT "question.ID for which this is an answer",
  `Summary`  VARCHAR(255)    NOT NULL COMMENT "short description of answer",
  `Page`     VARCHAR(127)             COMMENT "URI of optional site page giving more details",
  `Pos_Text` VARCHAR(63) DEFAULT NULL COMMENT "optional: text to show at the 'yes' end of the scale (overrides question default)",
  `Neg_Text` VARCHAR(63) DEFAULT NULL COMMENT "optional: text to show at the 'no' end of the scale (overrides question default)",
  `WhenCreated` DATETIME     NOT NULL COMMENT "when this records was entered",
  `WhoCreated`  INT          NOT NULL COMMENT "ID of user who added the record",
  `WhenEdited`  DATETIME DEFAULT NULL COMMENT "when this question was last edited",
  `WhoEdited`   INT      DEFAULT NULL COMMENT "ID of user who last edited the record",
  `QtyEdits`    INT      DEFAULT NULL COMMENT "number of times this record has been edited",
  PRIMARY KEY(`ID`),
  UNIQUE KEY `content` (`ID_Question`,`Summary`)
)
ENGINE = MYISAM;</mysql>

For migrating data from old schema to new (some fields omitted): <mysql>INSERT INTO atom (Class,WhenCreated,WhoCreated,WhenEdited,QtyEdits) SELECT "an",WhenCreated,WhoCreated,WhenEdited,QtyEdits FROM OLD_answer;

INSERT INTO post (ID_Atom,ID_Owner,Summary,Body) SELECT atom.ID,NULL,Summary,Body FROM OLD_answer AS a LEFT JOIN atom ON a.WhenCreated=atom.WhenCreated;

INSERT INTO answer (ID_Post,ID_Question) SELECT ID_Atom,ID_Question FROM OLD_answer AS a LEFT JOIN post ON a.Summary=post.Summary; </mysql>

Retrieved from "https://htyp.org/mw/index.php?title=InstaGov/schema/answer&oldid=19678"