wiki:DatabaseChanges

Version 25 (modified by shevek, 5 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.

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');