db_changelog.txt

/**************/
/* 2017-02-22
/* Added flavor to like-funktion for posts
/**************/

ALTER TABLE `dd`.`user_like_post` 
ADD COLUMN `flavor` VARCHAR(20) NULL DEFAULT 'neutral' AFTER `post_id`;

ALTER TABLE `dd`.`user_like_post` 
MODIFY COLUMN `flavor` VARCHAR(20) NOT NULL;

/**************/
/* 2017-02-18
/* New like-funktion for posts
/**************/

DROP TABLE IF EXISTS `dd`.`user_like_post`;
CREATE TABLE `dd`.`user_like_post` (
  `user_id` INTEGER UNSIGNED NOT NULL,
  `post_id` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (`user_id`, `post_id`)
) ENGINE = InnoDB DEFAULT CHARSET=latin1;

/**************/
/* 2017-02-09
/* Hotfix for making notice functions work with current test users
/**************/

INSERT INTO `notice_user_setting` (`user_id`) 
VALUES 
 (13)
 ,(4)
 ,(5)
 ,(6);

/**************/
/* 2017-01-29
/* Add table for notice function and notice function settings
/**************/

CREATE TABLE `dd`.`notice` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INTEGER UNSIGNED NOT NULL,
  `timestamp_created` INTEGER UNSIGNED NOT NULL,
  `view_or_email` VARCHAR(5) NOT NULL,
  `type_of_notification` VARCHAR(45) NOT NULL,
  `action_id` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

CREATE TABLE `dd`.`notice_user_setting` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INTEGER UNSIGNED NOT NULL,
  `view_quoting_post` BOOLEAN NOT NULL DEFAULT 0,
  `email_quoting_post` BOOLEAN NOT NULL DEFAULT 0,
  `view_subscribed_topic` BOOLEAN NOT NULL DEFAULT 0,
  `email_subscribed_topic` BOOLEAN NOT NULL DEFAULT 0,
  `view_vote_started` BOOLEAN NOT NULL DEFAULT 0,
  `email_vote_started` BOOLEAN NOT NULL DEFAULT 0,
  `view_vote_finished` BOOLEAN NOT NULL DEFAULT 0,
  `email_vote_finished` BOOLEAN NOT NULL DEFAULT 0,
  `view_proposition_created` BOOLEAN NOT NULL DEFAULT 0,
  `email_proposition_created` BOOLEAN NOT NULL DEFAULT 0,
  `email_message` BOOLEAN NOT NULL DEFAULT 0,
  `email_notification_interval` INTEGER UNSIGNED NOT NULL,
  `last_email_notification` INTEGER UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;


/**************/
/* 2017-01-19
/* Add table `user_password_reset_token` used for page to reset user password
/**************/

CREATE TABLE `user_password_reset_token` (
  `reset_token` varchar(20) NOT NULL,
  `user_id` INTEGER UNSIGNED NOT NULL,
  `timestamp_insertion` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY  (`reset_token`)
) ENGINE=MyISAM AUTO_INCREMENT=221 DEFAULT CHARSET=latin1;

/**************/
/* 2016-04-29 */
/**************/
Adding extra column for handling membership fees

ALTER TABLE `dd`.`user` CHANGE COLUMN `membership_fee_payed` `membership_fee_last_payed` INT(10) UNSIGNED DEFAULT NULL,
 ADD COLUMN `membership_fee_payed_until` INTEGER UNSIGNED AFTER `membership_fee_last_payed`;

/**************/
/* 2016-04-20 */
/**************/
Adding support for inactivated users

ALTER TABLE `dd`.`user` ADD COLUMN `activation_code` VARCHAR(20) DEFAULT NULL AFTER `description`,
 ADD COLUMN `activation_time` INTEGER UNSIGNED DEFAULT 1460019477 AFTER `activation_code`,
 ADD COLUMN `activation_first_name` VARCHAR(45) AFTER `activation_time`,
 ADD COLUMN `activation_last_name` VARCHAR(45) AFTER `activation_first_name`;

Adding accounting table

CREATE TABLE `dd`.`accounting` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `type_of_transaction` VARCHAR(45) NOT NULL,
  `amount` FLOAT NOT NULL,
  `timestamp_accounting` INTEGER UNSIGNED NOT NULL,
  `timestamp_insertion` INTEGER UNSIGNED NOT NULL,
  `user_id` INTEGER UNSIGNED,
  `comment_from_bank` VARCHAR(255),
  `comment_from_admin` VARCHAR(255),
  PRIMARY KEY (`id`)
)
ENGINE = InnoDB;

********************************************************************************

ALTER TABLE `dd`.`ballots` ADD COLUMN `ballot` TEXT AFTER `constituency_id`;
UPDATE `dd`.`ballots` SET `ballot` = `ballot_alternative` WHERE `ballot_alternative` IS NOT NULL;
UPDATE `dd`.`ballots` SET `ballot` = `prio_ranking` WHERE `prio_ranking` IS NOT NULL;
UPDATE `dd`.`ballots` SET `ballot` = `median_vote_value` WHERE `median_vote_value` IS NOT NULL;

ALTER TABLE `dd`.`ballots` 
 DROP COLUMN `ballot_alternative`,
 DROP COLUMN `prio_ranking`,
 DROP COLUMN `median_vote_value`;

ALTER TABLE `dd`.`propositions`
 ADD COLUMN `number_of_abstains` INTEGER UNSIGNED NOT NULL DEFAULT 0
UPDATE `dd`.`propositions` SET `number_of_abstains` = `number_of_enemies`
ALTER TABLE `dd`.`propositions`
 DROP COLUMN `number_of_enemies`

ALTER TABLE `dd`.`propositions_support`
 ALTER COLUMN `user_code` VARCHAR(20);

ALTER TABLE `dd`.`propositions_support`
 ADD COLUMN `constituency_id` INTEGER UNSIGNED NOT NULL;
ALTER TABLE `dd`.`propositions_support`
 ADD COLUMN `delegation_level` INTEGER UNSIGNED;
ALTER TABLE `dd`.`propositions_support`
 ADD COLUMN `delegate_priority` INTEGER UNSIGNED;

/**************/
/* 2015-10-10 */
/**************/

create table `constituency_level` (
    `level` int unsigned NOT NULL auto_increment
	, `name` varchar(20) NOT NULL
	, PRIMARY KEY  (`level`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert into `constituency_level`
(name)
values ('global'),('local_region'),('local_county'),('workgroup');

ALTER TABLE `dd`.`constituencies`
 ADD COLUMN `constituency_level` INTEGER UNSIGNED NOT NULL DEFAULT 1;

ALTER TABLE `dd`.`constituencies`
	ADD FOREIGN KEY (constituency_level) REFERENCES constituency_level(`level`);
    
update constituencies
set constituency_level = 1
where access_type = 'public';
    
update constituencies
set constituency_level = 2
where access_type = 'local_region';
    
update constituencies
set constituency_level = 3
where access_type = 'local_county';
    
update constituencies
set constituency_level = 4
where access_type = 'workgroup';

ALTER TABLE `dd`.`constituencies`
drop column `access_type`;

/**************/
/* 2015-10-13 */
/**************/

ALTER TABLE `dd`.`admin_roles` RENAME TO `dd`.`admin_role`;
ALTER TABLE `dd`.`admin_roles_users` RENAME TO `dd`.`admin_role_user`;
ALTER TABLE `dd`.`ballots` RENAME TO `dd`.`ballot`;
ALTER TABLE `dd`.`calendar_participants` RENAME TO `dd`.`calendar_participant`;
ALTER TABLE `dd`.`categories` RENAME TO `dd`.`category`;
ALTER TABLE `dd`.`constituencies` RENAME TO `dd`.`constituency`;
ALTER TABLE `dd`.`constituencies_delegates` RENAME TO `dd`.`constituency_delegate`;
ALTER TABLE `dd`.`constituencies_users` RENAME TO `dd`.`constituency_user`;
ALTER TABLE `dd`.`delegates` RENAME TO `dd`.`delegate`;
ALTER TABLE `dd`.`delegates_users` RENAME TO `dd`.`delegate_user`;
ALTER TABLE `dd`.`delegate_ballots` RENAME TO `dd`.`delegate_ballot`;
ALTER TABLE `dd`.`forums` RENAME TO `dd`.`forum`;
ALTER TABLE `dd`.`forums_users` RENAME TO `dd`.`forum_user`;
ALTER TABLE `dd`.`membership_fees` RENAME TO `dd`.`membership_fee`;
ALTER TABLE `dd`.`messages` RENAME TO `dd`.`message`;
ALTER TABLE `dd`.`posts` RENAME TO `dd`.`post`;
ALTER TABLE `dd`.`post_reports` RENAME TO `dd`.`post_report`;
ALTER TABLE `dd`.`propositions` RENAME TO `dd`.`proposition`;
ALTER TABLE `dd`.`propositions_support` RENAME TO `dd`.`proposition_support`;
ALTER TABLE `dd`.`users` RENAME TO `dd`.`user`;
ALTER TABLE `dd`.`user_applications` RENAME TO `dd`.`user_application`;
ALTER TABLE `dd`.`voters_delegates` RENAME TO `dd`.`voter_delegate`;
ALTER TABLE `dd`.`votes` RENAME TO `dd`.`vote`;
ALTER TABLE `dd`.`votes_alternatives` RENAME TO `dd`.`vote_alternative`;
ALTER TABLE `dd`.`workgroups` RENAME TO `dd`.`workgroup`;
ALTER TABLE `dd`.`workgroups_candidates` RENAME TO `dd`.`workgroup_candidate`;
ALTER TABLE `dd`.`workgroups_candidates_support` RENAME TO `dd`.`workgroup_candidate_support`;
ALTER TABLE `dd`.`workgroups_members` RENAME TO `dd`.`workgroup_member`;
ALTER TABLE `dd`.`workgroups_members_exclude` RENAME TO `dd`.`workgroup_member_exclude`;
ALTER TABLE `dd`.`workgroup_election_changes` RENAME TO `dd`.`workgroup_election_change`;

/**************/
/* 2015-10-13 */
/**************/

alter table constituency 
add column parent_id int(10) unsigned null;

update constituency c
join kommuner k on c.title = k.kommun
join constituency cp on cp.title = k.lan
set c.parent_id = cp.id;

update constituency c
set parent_id = 313
where constituency_level = 2;

DROP TABLE `kommuner`;

/**************/
/* 2015-11-05 
/* Fix error in data
/**************/

update user set region = 'Blekinge' where region like 'Blekinge%';

/**************/
/* 2015-11-05 
/* Remove abstain proposition option
/**************/

alter table proposition drop column number_of_abstains;

alter table proposition_support
add column support bit null;

update proposition_support
set support = 1 where support_type = 'support';

update proposition_support
set support = 0 where support_type <> 'support';

alter table proposition_support
drop column support_type;

/**************/
/* 2015-12-08 
/* Translate admin roles
/**************/

update admin_role
set title = 'Superadmin'
where id = 1;
update admin_role
set title = 'Member admin'
where id = 2;
update admin_role
set title = 'Forum admin'
where id = 3;
update admin_role
set title = 'Vote admin'
where id = 4;
update admin_role
set title = 'Treasurer'
where id = 5;