InstaGov/schema/answer - Revision history

Woozle: migration SQL

2013-07-21T20:13:19Z

migration SQL

← Older revision		Revision as of 20:13, 21 July 2013
Line 34:		Line 34:
	)		)
	ENGINE = MYISAM;</mysql>		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>

Woozle: revised to use postferret

2013-07-21T17:31:50Z

revised to use postferret

← Older revision		Revision as of 17:31, 21 July 2013
Line 4:		Line 4:
	* '''Notes''':		* '''Notes''':
	** This table originally had a "WhenExpires" field; it was removed for the same reasons given in notes for the "{{l/same\|question}}" table.		** This table originally had a "WhenExpires" field; it was removed for the same reasons given in notes for the "{{l/same\|question}}" table.
	** The '''*_Text''' fields are 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. I'm leaving these fields in for now but not implementing them in code. They might get deleted later.
	==History==		==History==
	* '''2013-01-07'''		* '''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 "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.		** 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==
			<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` (		<mysql>CREATE TABLE `answer` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,

Woozle: unique key

2013-01-08T02:24:11Z

unique key

← Older revision		Revision as of 02:24, 8 January 2013
Line 6:		Line 6:
	** The '''*_Text''' fields are 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. I'm leaving these fields in for now but not implementing them in code. They might get deleted later.		** The '''*_Text''' fields are 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. I'm leaving these fields in for now but not implementing them in code. They might get deleted later.
	==History==		==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.		* '''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.
	==SQL==		==SQL==
	<mysql>CREATE TABLE `answer` (		<mysql>CREATE TABLE `answer` (
Line 20:		Line 22:
	`WhoEdited` INT DEFAULT NULL COMMENT "ID of user who last edited the record",		`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",		`QtyEdits` INT DEFAULT NULL COMMENT "number of times this record has been edited",
	PRIMARY KEY(`ID`)		PRIMARY KEY(`ID`),
			UNIQUE KEY `content` (`ID_Question`,`Summary`)
	)		)
	ENGINE = MYISAM;</mysql>		ENGINE = MYISAM;</mysql>

Woozle: note about *_Text fields

2013-01-07T18:49:49Z

note about *_Text fields

← Older revision		Revision as of 18:49, 7 January 2013
Line 4:		Line 4:
	* '''Notes''':		* '''Notes''':
	** This table originally had a "WhenExpires" field; it was removed for the same reasons given in notes for the "{{l/same\|question}}" table.		** This table originally had a "WhenExpires" field; it was removed for the same reasons given in notes for the "{{l/same\|question}}" table.
			** The '''*_Text''' fields are 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. I'm leaving these fields in for now but not implementing them in code. They might get deleted later.
	==History==		==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.		* '''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.

Woozle: moar betterer feelds

2013-01-07T18:00:18Z

moar betterer feelds

← Older revision		Revision as of 18:00, 7 January 2013
Line 4:		Line 4:
	* '''Notes''':		* '''Notes''':
	** This table originally had a "WhenExpires" field; it was removed for the same reasons given in notes for the "{{l/same\|question}}" 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.
	==SQL==		==SQL==
	<mysql>CREATE TABLE `answer` (		<mysql>CREATE TABLE `answer` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
	`ID_Question` INT NOT NULL COMMENT "question.ID for which this is an answer",		`ID_Question` INT NOT NULL COMMENT "question.ID for which this is an answer",
	`~~Page~~` VARCHAR(~~127~~) COMMENT "~~wiki page describing~~ answer",		`Summary` VARCHAR(255) NOT NULL COMMENT "short description of answer",
	`~~WhenCreated~~` ~~DATETIME~~ ~~NOT NULL~~ COMMENT "~~when this answer was entered~~",		`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)",
			`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`)		PRIMARY KEY(`ID`)
	)		)
	ENGINE = MYISAM;</mysql>		ENGINE = MYISAM;</mysql>

Woozle: extracted obsolete note; clarified note about field removal

2013-01-07T16:28:34Z

extracted obsolete note; clarified note about field removal

← Older revision		Revision as of 16:28, 7 January 2013
Line 2:		Line 2:
	* '''Purpose''': implements {{igov/term\|answer}}s		* '''Purpose''': implements {{igov/term\|answer}}s
	* '''Rules''': Any {{l/same\|question}} may have zero or more answers. New answers can be added at any time.		* '''Rules''': Any {{l/same\|question}} may have zero or more answers. New answers can be added at any time.
	* '''Notes''':		* '''Notes''':
	** "WhenExpires" field: same ~~applies as~~ in "{{l/same\|question}}" ~~table.~~		** This table originally had a "WhenExpires" field; it was removed for the same reasons given in notes for the "{{l/same\|question}}" table.
	** '''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 {{l/same\|rating}} table.
	==SQL==		==SQL==
	<mysql>CREATE TABLE `answer` (		<mysql>CREATE TABLE `answer` (

Woozle: /* SQL */ fixed missing "T" in "NOT"; tidying

2013-01-07T16:22:51Z

SQL: fixed missing "T" in "NOT"; tidying

← Older revision		Revision as of 16:22, 7 January 2013
Line 6:		Line 6:
	** '''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 {{l/same\|rating}} table.		** '''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 {{l/same\|rating}} table.
	==SQL==		==SQL==
	~~<section begin=sql />~~<mysql>CREATE TABLE `answer` (		<mysql>CREATE TABLE `answer` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
	`ID_Question` INT NO NULL COMMENT "question.ID for which this is an answer",		`ID_Question` INT NOT NULL COMMENT "question.ID for which this is an answer",
	`Page` ~~varchar~~(127) COMMENT "wiki page describing answer",		`Page` VARCHAR(127) COMMENT "wiki page describing answer",
	`WhenCreated` DATETIME NOT NULL COMMENT "when this answer was entered",		`WhenCreated` DATETIME NOT NULL COMMENT "when this answer was entered",
	`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)",
Line 16:		Line 16:
	)		)
	ENGINE = MYISAM;</mysql>		ENGINE = MYISAM;</mysql>
	~~<section end=sql />~~

Woozle: using new terms template

2013-01-06T16:59:55Z

using new terms template

← Older revision		Revision as of 16:59, 6 January 2013
Line 1:		Line 1:
	==About==		==About==
	* '''Purpose''': implements {{~~instagov\|concepts~~\|answer}}s		* '''Purpose''': implements {{igov/term\|answer}}s
	* '''Rules''': Any {{l/same\|question}} may have zero or more answers. New answers can be added at any time.		* '''Rules''': Any {{l/same\|question}} may have zero or more answers. New answers can be added at any time.
	* '''Notes''':		* '''Notes''':
	** "WhenExpires" field: same applies as in "{{~~instagov\|schema~~\|question}}" table.		** "WhenExpires" field: same applies as in "{{l/same\|question}}" table.
	** '''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.		** '''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 {{l/same\|rating}} table.
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE TABLE `answer` (		<section begin=sql /><mysql>CREATE TABLE `answer` (

Woozle: some cross-links

2012-12-30T16:37:30Z

some cross-links

← Older revision		Revision as of 16:37, 30 December 2012
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 {{instagov\|concepts\|answer}}s
			* '''Rules''': Any {{l/same\|question}} may have zero or more answers. New answers can be added at any time.
	* '''Notes''':		* '''Notes''':
	** "WhenExpires" field: same applies as in "{{instagov\|schema\|question}}" table.		** "WhenExpires" field: same applies as in "{{instagov\|schema\|question}}" table.

Woozle: updated thoughts -- probably won't use this table

2011-06-07T01:49:24Z

updated thoughts -- probably won't use this table

← Older revision		Revision as of 01:49, 7 June 2011
Line 1:		Line 1:
	==About==		==About==
	* '''Rules''': Any question may have zero or more answers. New answers can be added at any time.		* '''Rules''': Any question may have zero or more answers. New answers can be added at any time.
	* '''Notes''': "WhenExpires" field: same applies as in "{{instagov\|schema\|question}}" table.		* '''Notes''':
			** "WhenExpires" field: same applies as in "{{instagov\|schema\|question}}" table.
			** '''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.
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE TABLE `answer` (		<section begin=sql /><mysql>CREATE TABLE `answer` (