wiki:DatabaseChanges

Download the Test Database

  • A more recent test database at state of ROX release milestone 2.3 is currently available from this source: http://static.toubiweb.com/bewelcome/bewelcome.sql.bz2
    The tables for the activities feature are missing there. See changes for milestone 1.7 (ticket #2003) for creating the activities tables.
    The tables for words, languages and geonames have to be imported additionally as described in the INSTALL routine of ROX.

Update for milestone 2.3

Add a fourth and fifth type of broadcast to have a distinguished type for the reminder send out for the suggestions feature (as defined in the decision making process) and in case of a Terms of Use update.

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

Add a state for suggestion options as well, as ranking (and implementation) is based on options.

ALTER TABLE  `suggestions_options` ADD  `state` INT NOT NULL COMMENT  '1 = Ranking; 2 = In Implementation; 4 = Implemented;' AFTER  `suggestionId`;

ALTER TABLE  `suggestions_options` CHANGE  `state`  `state` INT( 11 ) NOT NULL DEFAULT  '0' COMMENT  '1 = Ranking; 2 = In Implementation; 4 = Implemented;';

ALTER TABLE  `suggestions` ADD  `flags` INT NULL DEFAULT NULL COMMENT  'Versatile field. Currently used to store if the voting start message was already posted. ' AFTER  `state` ;

Add a table to store the rankings of the options

CREATE TABLE `suggestions_option_ranks` ( `optionid` int(11) NOT NULL COMMENT 'option that got ranked', `memberhash` char(64) NOT NULL COMMENT 'member who ranked (obfuscated)', `vote` int(1) NOT NULL COMMENT 'vote', PRIMARY KEY (`optionid`, `memberhash`))

Update for milestone 2.2

Ticket #2066:

ALTER TABLE comments ADD DisplayInPublic TINYINT NOT NULL DEFAULT '1';
ALTER TABLE comments ADD AllowEdit TINYINT NOT NULL DEFAULT '0';

Ticket #2027:

ALTER TABLE suggestions ADD laststatechanged DATE DEFAULT NULL;

Update for milestone 2.1

To save a preference for having read comment guidelines.

INSERT INTO preferences( codeName, position, codeDescription, Description, DefaultValue, Status ) 
VALUES ('ReadCommentGuidelines', 0,  'ReadCommentGuidelines',  
'Stores if user has read the comment guidelines (at least once)', '0', 'Inactive');

To add two new categories for "How long have you known ...?" in the profile comments section.

ALTER TABLE `comments` CHANGE `Lenght` `Lenght` SET( 'hewasmyguest', 'hehostedme', 'OnlyOnce', 'HeIsMyFamily', 'HeHisMyOldCloseFriend', 'NeverMetInRealLife', 'TravelledTogether', 'WeAreFriends' ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL 

To hide group comments a new column called VisibleComments was added to the table groups.

ALTER TABLE `groups` ADD `VisibleComments` ENUM( 'no', 'yes' ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no' COMMENT 'If comments in groups are shown or not.' AFTER `VisiblePosts` 

The feedbackcategories table got two new columns and some new content. Please drop the old one and import the following SQL.

-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Sep 07, 2013 at 05:23 PM
-- Server version: 5.5.16
-- PHP Version: 5.3.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `bewelcome`
--

-- --------------------------------------------------------

--
-- Table structure for table `feedbackcategories`
--

DROP TABLE IF EXISTS `feedbackcategories`;
CREATE TABLE IF NOT EXISTS `feedbackcategories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` tinytext COLLATE utf8_unicode_ci NOT NULL COMMENT 'The name of the category (this is a word)',
  `CategoryDescription` tinytext COLLATE utf8_unicode_ci NOT NULL COMMENT 'The description of the feedback category (this is a word)',
  `sortOrder` int(1) NOT NULL,
  `visible` int(1) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `EmailToNotify` text COLLATE utf8_unicode_ci NOT NULL COMMENT 'The list of the email to notify in case a feedback is sent in this category, these are dor comma separated',
  `IdVolunteer` int(11) NOT NULL DEFAULT '0' COMMENT 'Default volunteer in charge of this feedback',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='The different available categories for members feedbacks' AUTO_INCREMENT=66 ;

--
-- Dumping data for table `feedbackcategories`
--

INSERT INTO `feedbackcategories` (`id`, `Name`, `CategoryDescription`, `sortOrder`, `visible`, `created`, `EmailToNotify`, `IdVolunteer`) VALUES
(1, 'Software_issues', 'Category for feedback about bugs and other software issues', 3, 1, '2013-09-07 13:53:54', 'someone@example.com', 0),
(2, 'Safety_and_abuse', 'Category for feedback about safety issues and abuse', 5, 1, '2013-09-07 13:54:01', 'someone@example.com', 0),
(3, 'Account', 'Category for feedback about account issues (e.g. feedback at signup, username/password lost, username changes, problems with login etc.)', 2, 1, '2013-09-07 13:54:04', 'someone@example.com', 0),
(5, 'Media', 'Feedback category for media inquiries', 7, 1, '2013-09-07 13:59:01', 'someone@example.com', 0),
(6, 'Forum_moderation', 'Category for feedback about forum and group issues', 6, 1, '2013-09-07 13:54:09', 'someone@example.com', 0),
(7, 'Suggestions', 'Feedback category for suggestions', 4, 1, '2013-09-07 13:59:08', 'someone@example.com', 0),
(8, 'Volunteering', 'Feedback category for inquiries about volunteering', 8, 1, '2013-09-07 13:59:12', 'someone@example.com', 0),
(9, 'Other', 'Category for feedback that does not fit into any other category', 9, 1, '2013-09-07 13:59:18', 'someone@example.com', 0),
(10, 'Delete_profile', 'Category for feedback written when profile is deleted', 10, 0, '2013-09-07 13:57:23', 'someone@example.com', 0),
(11, 'General_feedback', 'Category for general feedback ', 1, 1, '2013-09-07 13:59:28', 'someone@example.com', 0),
(4, 'Comment_issue', 'Category for feedback about profile comment issues', 11, 0, '2013-09-07 14:52:40', 'someone@example.com', 0);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Added a new column to the suggestions table (stores the thread id of the discussion thread in the suggestions group):

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

Two new columns for the suggestions_options table to be able to delete options (and restore them if needed):

ALTER TABLE  `suggestions_options` ADD  `deleted` DATE NULL DEFAULT NULL AFTER  `modifiedBy` ,
ADD  `deletedBy` INT( 11 ) NULL DEFAULT NULL AFTER  `deleted` ;

Add preference to be able to set the radius for activities near me:

INSERT INTO preferences( codeName, position, codeDescription, Description, DefaultValue, Status ) 
VALUES ('ActivitiesNearMeRadius', 25,  'ActivitiesRadius',  
'Radius set by the user for the list of activities around his/her location', '25', 'Inactive');

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');
Last modified 3 years ago Last modified on Apr 12, 2014 10:18:35 PM