sql.txt

ALTER TABLE `user` ADD UNIQUE(`email`);
ALTER TABLE `user` ADD `alternate_email` VARCHAR(100) NULL AFTER `email`;
ALTER TABLE `user` ADD `full_name` VARCHAR(100) NULL AFTER `alternate_email`;
ALTER TABLE `user` ADD `is_active` BOOLEAN NOT NULL AFTER `full_name`;

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

CREATE TABLE `ewu_connect`.`community` ( `id` INT NOT NULL AUTO_INCREMENT ,  `community_name` VARCHAR(500) NOT NULL ,  `about` VARCHAR(500) NOT NULL ,  `cover_photo_url` VARCHAR(500) NULL DEFAULT NULL ,  `logo_url` VARCHAR(500) NULL DEFAULT NULL ,  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ,    PRIMARY KEY  (`id`)) ENGINE = InnoDB;
ALTER TABLE `community` ADD `user_id` INT NOT NULL;

ALTER TABLE `community` ADD `tag_name` INT NOT NULL ;
ALTER TABLE `community` CHANGE `tag_name` `tag_name` VARCHAR(5000) NOT NULL;

CREATE TABLE `community_users` ( `community_id` INT NOT NULL ,  `user_id` INT  NULL ,  `joined` DATETIME NOT NULL ,  `approveed` BOOLEAN NOT NULL DEFAULT FALSE ) ENGINE = InnoDB;
​ALTER TABLE community_users ADD FOREIGN KEY (community_id) REFERENCES community(id) on DELETE cascade;
ALTER TABLE community_users ADD FOREIGN KEY (user_id) REFERENCES user(id) on DELETE set null;
ALTER TABLE `community_users` CHANGE `joined` `joined` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;



ALTER TABLE community ADD FOREIGN KEY (user_id) REFERENCES user(id);

CREATE TABLE `post` ( `id` INT NOT NULL AUTO_INCREMENT , `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP , `title` VARCHAR(500) NOT NULL , `text` VARCHAR(2000) NOT NULL , `link` VARCHAR(500) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDBALTER TABLE `post` ADD `community_id` INT NOT NULL AFTER `user_id`;
ALTER table post add user_id int not null;
​ALTER table post add foreign key (user_id) references user(id) on DELETE no action;

CREATE TABLE `file` ( `file_type` VARCHAR(500) NOT NULL , `url` VARCHAR(500) NOT NULL , `post_id` INT NOT NULL , `id` INT NOT NULL AUTO_INCREMENT , PRIMARY KEY (`id`)) ENGINE = InnoDB;
ALTER TABLE file add CONSTRAINT foreign key (post_id) references post(id) on DELETE cascade;
ALTER TABLE `file` ADD `file_name` VARCHAR(5000) NOT NULL AFTER `id`;
ALTER TABLE post add community_id int not null
ALTER TABLE post add constraint FOREIGN KEY (community_id) references community(id) ON DELETE cascade;
ALTER TABLE `post` CHANGE `link` `link` VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;
ALTER TABLE `post` CHANGE `date` `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE `post` ADD `public` BOOLEAN NOT NULL DEFAULT TRUE AFTER `community_id`;


ALTER TABLE `community` ADD `public` BOOLEAN NOT NULL  DEFAULT '1' AFTER `tag_name`;

ALTER TABLE `community_users` CHANGE `approveed` `approveed_as_mod` TINYINT(1) NOT NULL DEFAULT '0';

********************
ALTER TABLE `post` CHANGE `user_id` `user_id` INT(11) NULL;