System zarządzania projektami

Nowa kolumna przechowujaca czas oddania glosu:

ALTER TABLE `post_vote` ADD `vote_time` INT UNSIGNED NULL DEFAULT NULL

CREATE TABLE IF NOT EXISTS `post_subscribe` (
  `post_id` mediumint(8) unsigned NOT NULL,
  `user_id` mediumint(8) unsigned NOT NULL,
  UNIQUE KEY `post_id` (`post_id`,`user_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO post_subscribe( post_id, user_id ) 
SELECT post_id, post_user
FROM post
WHERE post_user > 0;

DELETE FROM post_subscribe WHERE user_id NOT IN (
SELECT user_id
FROM user
);

INSERT IGNORE INTO post_subscribe (post_id, user_id)
SELECT comment_post, comment_user FROM post_comment;

ALTER TABLE `post_subscribe`
  ADD CONSTRAINT `post_subscribe_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `post_subscribe_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `post` (`post_id`) ON DELETE CASCADE;

DROP TRIGGER IF EXISTS `onAfterPostInsert`;
DELIMITER $$
CREATE TRIGGER `onAfterPostInsert` AFTER INSERT ON `post`
 FOR EACH ROW BEGIN
    UPDATE `topic` SET topic_last_post_id = NEW.post_id, topic_last_post_time = NEW.post_time, topic_replies = (topic_replies + 1) WHERE topic_id = NEW.post_topic;
    UPDATE `forum` SET forum_posts = (forum_posts + 1), forum_last_post_id = NEW.post_id WHERE forum_id = NEW.post_forum;
    SET @topicFirstId = (SELECT topic_first_post_id FROM `topic` WHERE topic_id = NEW.post_topic);

    IF NOT @topicFirstId THEN
        UPDATE `topic` SET topic_first_post_id = NEW.post_id, topic_replies = 0 WHERE topic_id = NEW.post_topic;
    END IF;

     IF NEW.post_user > 0 THEN
         UPDATE `user` SET user_post = user_post + 1 WHERE user_id = NEW.post_user;

        INSERT IGNORE topic_user (topic_id, user_id) VALUES(NEW.post_topic, NEW.post_user);
        INSERT post_subscribe (post_id, user_id) VALUES(NEW.post_id, NEW.post_user);
     END IF;
END$$

CREATE TRIGGER `onBeforePostCommentInsert` BEFORE INSERT ON `post_comment`
 FOR EACH ROW BEGIN
    IF (SELECT COUNT(*) FROM post_comment WHERE comment_post = NEW.comment_post) = 0 THEN
        INSERT IGNORE INTO post_subscribe (post_id, user_id) VALUES(NEW.comment_post, NEW.comment_user);
    END IF;
END$$

W zwiazku z wprowadzeniem nowej funkcjonalnosci do systemu Coyote, oto zapytania SQL, ktore maja uaktualnic strukture tabel:

ALTER TABLE `user` ADD `user_ip_access` VARCHAR( 255 ) NOT NULL AFTER `user_ip`
ALTER TABLE `user` ADD `user_alert_login` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'Powiadomienie o prawidłowym logowaniu do konta (na email)' AFTER `user_ip_invalid`
ALTER TABLE `user` ADD `user_alert_access` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'Powiadomienie o nieprawidłowym dostępie do konta (na email)' AFTER `user_alert_login`
ALTER TABLE `session_log` ADD INDEX ( `log_user` )

Z dniem 10 lutego 2011, wprowadzone zostaly zmiany w strukturze systemu majace zoptymalizowac dzialanie modulu forum.
Aby uaktualnic swoja lokalna wersje aplikacji, nalezy wykonac nastepujace instrukcje:

CREATE TABLE post_text (
  text_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  text_post mediumint(8) unsigned NOT NULL,
  text_content text NOT NULL,
  text_time int(10) unsigned NOT NULL,
  text_user mediumint(8) unsigned NOT NULL,
  text_ip varchar(16) NOT NULL,
  text_host varchar(255) NOT NULL,
  text_browser varchar(150) NOT NULL,
  PRIMARY KEY (text_id),
  KEY text_post (text_post)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

INSERT INTO post_text (text_post, text_content, text_time, text_user, text_ip, text_host, text_browser)
SELECT post_id, post_text, post_time, post_user, post_ip, post_host, post_browser
FROM post;

ALTER TABLE `post` CHANGE `post_text` `post_text` INT UNSIGNED NULL DEFAULT NULL;

UPDATE `post` SET post_text = NULL;

ALTER TABLE `post_text`
  ADD CONSTRAINT post_text_ibfk_1 FOREIGN KEY (text_post) REFERENCES post (post_id) ON DELETE CASCADE;

UPDATE post
INNER JOIN post_text ON text_post = post_id
SET post_text = text_id;

--
-- Wyzwalacze 'post_text'
--
DROP TRIGGER IF EXISTS `onAfterPostTextInsert`;
DELIMITER //
CREATE TRIGGER `onAfterPostTextInsert` AFTER INSERT ON `post_text`
 FOR EACH ROW BEGIN
    UPDATE post
    SET post_text = NEW.text_id
    WHERE post_id = NEW.text_post;
END
//
DELIMITER ;

W zwiazku z rozbudowaniem funkcjonalnosci powiadomien, nalezy wykonac na bazie danych nastepujace zapytania:

ALTER TABLE `notify_user` ADD `notifier` TINYINT UNSIGNED NOT NULL DEFAULT '1';

DELIMITER //
DROP TRIGGER IF EXISTS `onAfterUserInsert` //
CREATE TRIGGER `onAfterUserInsert` AFTER INSERT ON `user` 
FOR EACH
ROW BEGIN 
IF NEW.user_id >1
THEN 
SET @group_id = ( SELECT group_id
FROM `group` 
WHERE group_name = "USER" ) ;

INSERT INTO auth_group( user_id, group_id ) 
VALUES (
NEW.user_id, @group_id 
);

INSERT INTO notify_user( notify_id, user_id, notifier ) 
SELECT notify_id, NEW.user_id, notify_default
FROM notify
WHERE notify_default >0;

END IF ;

END // 

DROP TRIGGER IF EXISTS `onAfterNotifyInsert` //
CREATE TRIGGER `onAfterNotifyInsert` AFTER INSERT ON `notify` 
FOR EACH
ROW 
BEGIN 
IF NEW.notify_default >0
THEN 
INSERT INTO notify_user( notify_id, user_id, notifier ) 
SELECT NEW.notify_id, user_id, NEW.notify_default
FROM `user` ;

END IF ;

END //

Poprawka bugu zwiazanego z przenoszeniem watkow na forum. Aktualny kod trigger onBeforeTopicUpdate wyglada tak:

BEGIN

      IF NEW.topic_forum != OLD.topic_forum THEN

          SET NEW.topic_moved_id = OLD.topic_forum;

         UPDATE `post` SET post_forum = NEW.topic_forum WHERE post_topic = OLD.topic_id;
          UPDATE `topic_marking` SET forum_id = NEW.topic_forum WHERE topic_id = OLD.topic_id;

          SET @postCount = (

              SELECT COUNT(post_id)
              FROM post
              WHERE post_topic = NEW.topic_id
          );

          UPDATE forum
          SET forum_topics = forum_topics -1, forum_posts = forum_posts - @postCount, forum_last_post_id = GET_FORUM_LAST_POST(OLD.topic_forum)
          WHERE forum_id = OLD.topic_forum;

          UPDATE forum
          SET forum_topics = forum_topics +1, forum_posts = forum_posts + @postCount, forum_last_post_id = GET_FORUM_LAST_POST(NEW.topic_forum)
          WHERE forum_id = NEW.topic_forum;

          DELETE FROM page_group WHERE page_id = OLD.topic_page;

          INSERT INTO page_group (page_id, group_id)
          SELECT NEW.topic_page, group_id
          FROM page_group
          WHERE page_id = (

              SELECT forum_page
              FROM forum
              WHERE forum_id = NEW.topic_forum

          );

      END IF;
END

W systemie zaszla drobna zmiana. Obecnie powiadomienia sa podswietlone nawet jezeli sa odznaczone jako przeczytane. Podswietlenie jest wazne do czasy wylogowania z systemu. W zwiazku z tym, nastapila zmiana typu dla pola header_unread. Prosze o wykonanie nastepujacej instrukcji SQL:

ALTER TABLE `notify_header` CHANGE `header_unread` `header_read` INT UNSIGNED NOT NULL DEFAULT '0'

W zwiazku z poprawa funkcjonalnosci tagowania, prosze o wykonanie takich instrukcji SQL, aby uaktalnic swoje srodowisko:

CREATE TABLE `tag` (
  `tag_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tag_text` varchar(100) NOT NULL,
  `tag_weight` smallint(5) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`tag_id`),
  UNIQUE KEY `tag_text` (`tag_text`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE `page_tag` (
  `page_id` int(10) unsigned NOT NULL,
  `tag_id` int(10) unsigned NOT NULL,
  KEY `page_id` (`page_id`),
  KEY `tag_id` (`tag_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TRIGGER IF EXISTS `onAfterPageTagInsert`;
DELIMITER //
CREATE TRIGGER `onAfterPageTagInsert` AFTER INSERT ON `page_tag`
 FOR EACH ROW BEGIN
    UPDATE tag SET tag_weight = tag_weight + 1 WHERE tag_id = NEW.tag_id;
END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `onAfterPageTagDelete`;
DELIMITER //
CREATE TRIGGER `onAfterPageTagDelete` AFTER DELETE ON `page_tag`
 FOR EACH ROW BEGIN
    UPDATE tag SET tag_weight = tag_weight - 1 WHERE tag_id = OLD.tag_id;    
    SET @tagWeight = (SELECT tag_weight FROM tag WHERE tag_id = OLD.tag_id);

    IF (@tagWeight = 0) THEN
        DELETE FROM tag WHERE tag_id = OLD.tag_id;
    END IF;
END
//
DELIMITER ;

ALTER TABLE `page_tag`
  ADD CONSTRAINT `page_tag_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`tag_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `page_tag_ibfk_1` FOREIGN KEY (`page_id`) REFERENCES `page` (`page_id`) ON DELETE CASCADE;

Dodatkowo, w trigerze onBeforePageDelete nalezy dodac nastepujaca instrukcje:

DELETE FROM page_tag WHERE page_id = OLD.page_id;

Caly kod triggera:

BEGIN
    IF OLD.page_text IS NOT NULL THEN

          IF ((SELECT COUNT(*) FROM page WHERE page_text = OLD.page_text) = 1) THEN

              DELETE FROM page_text WHERE text_id IN(

                  SELECT text_id
                  FROM page_version
                  WHERE page_id = OLD.page_id
              );
          END IF;
    END IF;

    DELETE FROM page_tag WHERE page_id = OLD.page_id;
END

Dodatkowo, poprawka zwiazana z mozliwoscia akceptowania wybranej odpowiedzi w watku:

ALTER TABLE `topic` ADD `topic_solved` MEDIUMINT UNSIGNED NULL DEFAULT NULL AFTER `topic_vote` ,
ADD INDEX ( `topic_solved` );
ALTER TABLE `topic` ADD FOREIGN KEY ( `topic_solved` ) REFERENCES `post` (
`post_id` 
) ON DELETE SET NULL ;

W zainstalowanej wersji Coyote-CMF nalezy wykonac zapytania:

ALTER TABLE `user` ADD `user_group` MEDIUMINT UNSIGNED NOT NULL DEFAULT '2' COMMENT 'Domyślna grupa użytkownika' AFTER `user_confirm`;
ALTER TABLE `field` ADD `field_profile` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `field_display`;

DROP TRIGGER IF EXISTS `onAfterGroupUpdate`;
DELIMITER //
CREATE TRIGGER `onAfterGroupUpdate` AFTER UPDATE ON `group`
 FOR EACH ROW BEGIN
      SELECT user_id INTO @leader FROM `auth_group` WHERE group_id = NEW.group_id AND user_id = NEW.group_leader;

      IF @leader = 0 THEN
          INSERT INTO `auth_group` (user_id, group_id) VALUES(NEW.group_leader, NEW.group_id);
          UPDATE `user` SET user_permission = "" WHERE user_id = NEW.group_leader;
      END IF;
  END
//
DELIMITER ;
DROP TRIGGER IF EXISTS `onBeforeGroupDelete`;
DELIMITER //
CREATE TRIGGER `onBeforeGroupDelete` BEFORE DELETE ON `group`
 FOR EACH ROW BEGIN
     SET @groupId = (SELECT group_id FROM `group` WHERE group_name = "USER");

     UPDATE `user` SET user_permission = "", user_group = @groupId WHERE user_id IN(
         SELECT user_id FROM `auth_group` WHERE group_id = OLD.group_id
     );
 END
//
DELIMITER ;

DELIMITER ;
DROP TRIGGER IF EXISTS `onBeforeGroupDelete`;
DELIMITER //
CREATE TRIGGER `onBeforeGroupDelete` BEFORE DELETE ON `group`
 FOR EACH ROW BEGIN
     SET @groupId = (SELECT group_id FROM `group` WHERE group_name = "USER");

     UPDATE `user` SET user_permission = "", user_group = @groupId WHERE user_id IN(
         SELECT user_id FROM `auth_group` WHERE group_id = OLD.group_id
     );
 END
//
DELIMITER ;

Ma to zwiazek z poprawkami z rewizji 457

Nowa definicja triggera wyglada tak:

DROP TRIGGER IF EXISTS `onAfterNotifyHeaderDelete`;
DELIMITER //
CREATE TRIGGER `onAfterNotifyHeaderDelete` AFTER DELETE ON `notify_header`
 FOR EACH ROW BEGIN
     UPDATE `user`
     SET user_notify = (user_notify -1), user_notify_unread = (user_notify_unread - OLD.header_unread)
     WHERE user_id = OLD.header_recipient;
 END
//
DELIMITER ;