wiki:DatabaseChanges

Version 27 (modified by shevek, 4 years ago) (diff)

--

Download the Test Database


Update for milestone 2.0

Changes for milestone 2.0:

Download geonames.sql.gz, geonamesadminunits.sql.gz and geonamescountries.sql.gz and import then into the database as described in the INSTALL file for words.sql.gz.

Alter the suggestions table to contain a column for the associated thread in the 'Suggestions discussions' group.

ALTER TABLE  `suggestions` ADD  `thread` INT( 11 ) NULL DEFAULT NULL AFTER  `state` ;

Alter the comments table to reflect the changes for #2050:

ALTER TABLE  `comments` CHANGE  `updated`  `updated` TIMESTAMP NULL DEFAULT NULL ;

Update the Database

Changes for milestone 1.9

Added the suggestions feature. Needs three tables and a new right.

CREATE TABLE `suggestions` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'The primary key',
  `summary` varchar(80) CHARACTER SET utf8 NOT NULL COMMENT 'A short summary of the suggestions objective (no HTML)',
  `description` text NOT NULL COMMENT 'The description of the objective of the decision (HTML)',
  `salt` char(64) NOT NULL,
  `state` smallint(1) NOT NULL,
  `created` date NOT NULL COMMENT 'Creation date of the decision',
  `createdby` int(11) NOT NULL COMMENT 'Member (id( that created the suggestion',
  `modified` date DEFAULT NULL COMMENT 'Last time the suggestion was updated',
  `modifiedby` int(11) DEFAULT NULL,
  `votingstart` date DEFAULT NULL COMMENT 'The date when the voting on the decision started. Voting ends 60 days later.',
  `votingend` date DEFAULT NULL,
  `rankingstarted` date DEFAULT NULL,
  `rankingended` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

CREATE TABLE `suggestions_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `suggestionId` int(11) NOT NULL,
  `summary` varchar(160) NOT NULL,
  `description` text NOT NULL,
  `created` date NOT NULL,
  `createdBy` int(11) NOT NULL,
  `modified` date DEFAULT NULL,
  `modifiedBy` int(11) DEFAULT NULL,
  `result` int(1) DEFAULT NULL COMMENT 'The resulting rank for this option. Calculated when the voting is closed.',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `suggestions_votes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `suggestionId` int(11) NOT NULL,
  `optionId` int(11) NOT NULL,
  `rank` int(1) NOT NULL,
  `memberHash` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1570 DEFAULT CHARSET=utf8;

INSERT INTO rights SET created = '2013-07-26 22:03:00', Name = 'Suggestions', Description = 'Right for the members of the suggestions team. Allows to mark suggestions as duplicate. No levels or scope necessary.'

Changes for milestone 1.8

None.


Changes for milestone 1.7:

See attachments to #2003 "Add an activity tool"

  • activities.sql
  • activitiesattendees.sql
  • activitieswordcode.sql

Afterwards execute

ALTER TABLE  `activities` CHANGE  `address`  `address` VARCHAR( 320 )

to get an address field with a size that matches the code.

Additionally:

/* ticket #1875: settings for show my forum posts link on profile */
INSERT INTO preferences( codeName, position, codeDescription, Description, DefaultValue, PossibleValues, Status) VALUES ('MyForumPostsPagePublic', 46, 'MyForumPostsPagePublicDes', 'MyForumPostsPagePublic description', 'No', 'No;Yes', 'Normal');

And import the attached sql to get translations.

For ticket #1991 two new preferences are necessary:

INSERT INTO preferences( codeName, position, codeDescription, Description, DefaultValue, Status ) 
VALUES ('ForumThreadsOnLandingPage', 100,  'ForumThreadsToShowOnLandingPage',  
'How many forum threads on community landing', '5', 'Inactive');
INSERT INTO preferences( codeName, position, codeDescription, Description, DefaultValue, Status ) 
VALUES ('GroupsThreadsOnLandingPage', 100,  'GroupsThreadsToShowOnLandingPage',  
'How many groups threads on community landing', '5', 'Inactive');

Changes for milestone 1.6:

See attachments to #1809 (or download the current language and words table)

  • fixrightsvolunteers.sql
  • iso639fix.sql
  • signlangtrans.sql

Additionally:

/* ticket #1959: Adding a new gender type */
ALTER TABLE  `members` CHANGE  `Gender`  `Gender` ENUM(  'IDontTell',  'male',  'female',  'other' ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT  'IDontTell' COMMENT  'The gender of the member';

/*ticket #1880: Adding a new preference */
INSERT INTO preferences( codeName, position, codeDescription, Description, DefaultValue, PossibleValues, 
STATUS ) 
VALUES (
'PreferenceDisableTinyMCE', 31,  'DisableTinyMCEDes',  'Disable TinyMCE',  'No',  'No;Yes',  'Normal'
);

Old DB Changes

If you are updating from an older test database:

  • Milestone 1.4 had the following changes to the database:
/* Ticket #1695 */
CREATE TABLE groups_related(id int(11) PRIMARY KEY auto_increment, group_id int(11), related_id int(11), addedby int(11), deletedby int(11), ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);


/* Ticket #1823 */
INSERT INTO preferences(codeName, codeDescription, Description, DefaultValue, PossibleValues, Status) VALUES ('ShowMyGroupsTopicsOnly', 'ShowMyGroupsTopicsOnlyDes', 'ShowMyGroupsTopicsOnly description', 'No', 'No;Yes', 'Normal');



/* Ticket #1763 */

ALTER TABLE `preferences` ADD `position` INT NOT NULL AFTER `id`;

/* In case the implementation changes sometime keep language at the top */
UPDATE preferences SET position = 10 WHERE Id = 1;

/* Move profile visits to public profile */
/* Unfortunately public profile isn't stored in preferences */
UPDATE preferences SET position = 21 WHERE Id = 23;

/* Now group the advanced function and the spam folder */
UPDATE preferences SET position = 30 WHERE Id = 7;
UPDATE preferences SET position = 35 WHERE Id = 5;

/* All forum related stuff */
UPDATE preferences SET position = 40 WHERE Id = 12;
UPDATE preferences SET position = 45 WHERE Id = 25;

/* All notification and newsletter stuff */
UPDATE preferences SET position = 50 WHERE Id = 8;
UPDATE preferences SET position = 55 WHERE Id = 13;

/* All time related stuff */
UPDATE preferences SET position = 60 WHERE Id = 17;
UPDATE preferences SET position = 65 WHERE Id = 21;

/* All inactive items at the end in random order */
/* Just in case someone activates them */
UPDATE preferences SET position = 100 WHERE status = 'Inactive';


Changes for milestone 1.5:

/* Ticket #1858 */
ALTER TABLE `broadcast` CHANGE `Type` `Type` ENUM( 'Normal', 'RemindToLog', 'Specific' ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Normal' COMMENT 'Normal or Reminder to log in';

/* Ticket #1892 */
INSERT INTO `rights` (`id`, `created`, `Name`, `Description`) VALUES (NULL, CURRENT_TIMESTAMP, 'Treasurer', 'This right enables the treasurer to keep the donations bar shown on /donate to be (more) accurate by adding bank transfers to the database.

Only one level (10) needed (as there''s only one treasurer) and scope should always be "ALL".');

ALTER TABLE `params`  ADD `neededperyear` INT NOT NULL DEFAULT '1260' COMMENT 'Amount needed per year as shown during the donation campaign.' AFTER `ToggleDonateBar`,  ADD `campaignstartdate` DATE NOT NULL DEFAULT '2012-10-11' COMMENT 'The date the donation campaign started, used to gather the donated amount in this campaign.' AFTER `neededperyear`;

/* Ticket #1930 */
INSERT INTO `rights` (`id`, `created`, `Name`, `Description`) VALUES (NULL, CURRENT_TIMESTAMP, 'Profile', 'This right allows a volunteer to edit a profile');