C, PHP, VB, .NET

Нужно е да се направи база от данни за споделяне на статии между голяма група от потребители. В нея за всички потребители се пази уникален номер и уникално име. Всеки потребител може да има три основни роли – обикновен потребител (може да чете информация), автор (може да споделя информация) и рецензент (преглежда статии и ги одобрява или отхвърля). Разбира се авторите и рецензентите сами по себе си са обикновени потребители. Освен това не е забранено на даден автор да бъде и рецензент на други статии.

За всяка статия се пази нейното заглавие (до 160 символа) и съдържание (количество текстова информация, което ще е подходящо за поле от тип TEXT) и дата на публикуване. Когато някой автор добави нова статия в системата тя стандартно ще отива в „опашка за рецензиране“ (moderation queue). Това означава, че трябва някой рецензент да я провери и редактира преди да бъде официално публикувана. Статии, които не успеят да преминат успешно рецензия просто ще се изтриват. За статиите, които са одобрени задължително се пази информация за това кой е бил рецензента, който ги е одобрил.

Задача 1. Направете ER диаграма на описаната горе база от данни, създайте я в MySQL и вмъкнете примерни данни в нея.

Задача 2. Изведете списък със статиите на автор с име „Ivan”, които все още не са рецензирани.

Задача 3. Изведете списък с имената на авторите, общия им брой рецензирани статии и общия им брой нерецензирани статии (т.е. които все още очакват рецензия). Потребителите, които въобще нямат статии да се пропуснат.

Решение: Първо да определим данните, които ще се пазят със сигурност в базата от данни. Това са:

• Уникални номера на потребителите;
• Потребителски имена;
• Заглавия на статиите;
• Съдържания на статиите;
• Дати на публикуване на статиите;
• Кой потребител е автор на дадена статия;
• Кой потребител е рецензент на дадена статия (ако е рецензирана).

Очевидно зависимите данни са уникалните номера на потребителите и потребителските имена от една страна и заглавията, съдържанието и датата на публикуване за статиите от друга. Те могат да характеризират два класа обекти - Потребители (users) и Статии (articles). Ясно се вижда, че "Кой потребител е автор на дадена статия" и "Кой потребител е рецензент на дадена статия (ако е рецензирана)" са връзки между тези два класа. Тоест ER диаграмата на този етап е следната:

Очевидният първичен ключ за таблица Users е атрибута id. За таблица Articles няма нито очевиден такъв, нито подходящ, затова при създаването на таблицата ще направим допълнителен атрибут "id" (редно е да се отрази и в ER диаграмата - направете го).

Колкото до връзките - всяка таблица задължително трябва да има автор, т.е. author_id ще бъде NOT NULL, докато някои статии ще са рецензирани, а други не - т.е. moderator_id ще може евентуално да бъде NULL.

В условието има още важни неща, а именно:

• "Всеки потребител може да има три основни роли", което е допълнено с "Разбира се авторите и рецензентите сами по себе си са обикновени потребители. Освен това не е забранено на даден автор да бъде и рецензент на други статии.". Това практически означава, че всеки един потребител на системата може да заема всякаква роля - в задачата никъде не е дефинирано ограничение от типа, че "някоя конкретна група потребители не може да заема дадена роля" или "само тези и тези потребители могат да бъдат автори/рецензенти". Следователно не е нужно да правим подкласове на класа обекти Users - всеки един потребител може потенциално да бъде както автор, така и рецензент. В случая каква роля заема за дадена статия ще се определя от връзките между статията и потребителите.
• "Когато някой автор добави нова статия в системата тя стандартно ще отива в „опашка за рецензиране“ (moderation queue)" - тук въпросът е дали има нужда да добавим още един атрибут в класа Articles с тип данни BIT, който да има смисъла на "одобрена" и "неодобрена" статия в зависимост от стойноста? Не е изключено да го направим (по-скоро би било полезно), но в случая можем и да си го спестим - статиите, които имат въведен moderator_id ще приемаме за "рецензирани", а там, където moderator_id е NULL ще приемаме за "нерецензирани". В реална ситуация по-скоро бихме приели варианта с допълнителния атрибут, понеже така даваме шанс за създаване на нови функционалности като "някой рецензент е заключил статия за себе си, но все още не я е рецензирал" или "статията е била рецензирана услешно, но рецензентът е бил изтрит от системата и вече нямаме неговото id". В решението по-долу ще се спрем върху по-лесния вариант (без специална колона за статус в таблицата Articles), а вие впоследствие преправете заявките с другия.
• Колкото до нуждата от създаване на "опашка от нерецензирани статии" - това може много лесно да бъде реализирано, като се извадят записите с NULL стойност в колона moderator_id от таблица Articles и впоследствие се сортират по датата на тяхното въвеждане. Тоест и за опашката няма да е необходимо да пазим допълнителна информация.

Нека сега създадем въпросната база от данни:

CREATE DATABASE usersystem;
USE usersystem;

CREATE TABLE users(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) UNIQUE NOT NULL
)ENGINE = InnoDB CHARACTER SET utf8;

CREATE TABLE articles(
  id INT UNSIGNED AUTO_INCREMENT  PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  contents TEXT NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  published_on DATE NOT NULL,
  FOREIGN KEY (author_id) REFERENCES users(id)
          ON DELETE RESTRICT ON UPDATE RESTRICT,
  moderator_id SMALLINT UNSIGNED NULL DEFAULT NULL,
  FOREIGN KEY (moderator_id) REFERENCES users(id)
          ON DELETE RESTRICT ON UPDATE RESTRICT
)ENGINE = InnoDB CHARACTER SET utf8;

и да въведем примерни данни:

INSERT INTO users(name)
VALUES ("Petar"), ("Ivan"), ("Maria"), ("Philip");

INSERT INTO articles(title, contents, published_on, author_id, moderator_id)
VALUES ("Статия 1", "Съдържание към статия 1...", "2012-03-12", 2, NULL),
       ("Статия 2", "Съдържание към статия 2...", "2012-03-28", 3, NULL),
       ("Статия 3", "Съдържание към статия 3...", "2012-04-04", 3, NULL),
       ("Статия 4", "Съдържание към статия 4...", "2012-02-27", 2, NULL),
       ("Статия 5", "Съдържание към статия 5...", "2012-03-28", 3, 1),
       ("Статия 6", "Съдържание към статия 6...", "2012-04-04", 3, 2),
       ("Статия 7", "Съдържание към статия 7...", "2012-02-27", 2, 1),
       ("Статия 8", "Съдържание към статия 8...", "2012-02-27", 1, 2),
       ("Статия 9", "Съдържание към статия 9...", "2012-02-27", 1, NULL);

Пристъпваме към решението на задача 2. То е повече от тривиално и няма нужда от специални обяснения:

SELECT title, contents, published_on
FROM articles
WHERE moderator_id IS NULL
      AND author_id = ( SELECT id
                        FROM users
                        WHERE name = "Ivan"
                      );

Сега трябва да помислим за задача 3. Лесно можем да изведем имената на хората и броя от рецензираните им статии чрез следната заявка:

SELECT users.id AS user_id, users.name as user_name,
COUNT(articles.id) AS moderatedcount
FROM users JOIN articles ON users.id = articles.author_id
WHERE articles.moderator_id IS NOT NULL
GROUP BY users.id;

Лесно можем да изведем и имената на хората и броя на нерецензираните им статии чрез следната заявка:

SELECT users.id AS user_id, users.name as user_name,
COUNT(articles.id) AS unmoderatedcount
FROM users JOIN articles ON users.id = articles.author_id
WHERE articles.moderator_id IS NULL
GROUP BY users.id;

Как обаче да ги обединим в една? Очевидното решение е като направим JOIN между тези две таблици. При това забележете, че трябва да е FULL JOIN, понеже е възможно както да има потребител без нито една рецензирана статия, така и потребител без нито една нерецензирана.

За съжаление знаем, че MySQL не поддържа FULL JOIN и се налага да се прави сложно чрез изкуствени методи. За щастие в тази конкретна задача и двете заявки произхождат от данни на една таблица - users. Тоест ние можем да направим следното - да вземем всички записи от таблица users, за всеки един от тях да добавим moderatedcount (може да е NULL, т.е. ще използваме LEFT JOIN) и към получения резултат за всеки един ред да добавим и unmoderatedcount (отново с LEFT JOIN). Накрая, понеже в задачата се изисква "потребителите, които въобще нямат статии да се пропуснат", от резултатната таблица трябва да премахнем редовете, в които moderatedcount и unmoderatedcount заедно са с NULL стойности. Ето как би изглеждала такава заявка:

SELECT users.id,
       users.name,
       moderated_articles.moderatedcount,
       unmoderated_articles.unmoderatedcount
FROM users

     LEFT JOIN 
     ( SELECT users.id AS user_id, 
              COUNT(articles.id) AS moderatedcount
        FROM users JOIN articles ON users.id = articles.author_id
        WHERE articles.moderator_id IS NOT NULL
        GROUP BY users.id
     ) AS moderated_articles
     ON users.id = moderated_articles.user_id

     LEFT JOIN 
     ( SELECT users.id AS user_id,
              COUNT(articles.id) AS unmoderatedcount
        FROM users JOIN articles ON users.id = articles.author_id
        WHERE articles.moderator_id IS NULL
        GROUP BY users.id
     ) AS unmoderated_articles
     ON users.id = unmoderated_articles.user_id

GROUP BY users.id
HAVING moderatedcount IS NOT NULL 
       OR 
       unmoderatedcount IS NOT NULL;

Това е валидно решение на задачата. В този си вид обаче заявката въобще не е оптимална. Ето някои неща, които би трябвало да ни направят впечатление:

• В първата вложена заявка правим обхождане на таблицата users гледайки стойностите на колоната moderator_id и във втората пак правим същото обхождане гледайки стойностите по същата колона.
• Взимаме абсолютно всички потребители и за всички правим сметките. В зависимост от натоварването на подобна система може да се окаже така, че много от потребителите въобще не са автори, а са просто обикновени потребители или рецензенти. В такъв случай резултатната таблица (преди прилагането на HAVING) ще бъде ненужно голяма.

Затова можем да помислим и за "по-хитро" решение. Един начин е да си направим два "брояча" в една SELECT заявка и да използваме оператор IF, с който да добавяме 0 или 1 към съотвeтния брояч:

SELECT users.id,
       users.name,
       SUM(IF(articles.moderator_id IS NOT NULL, 1, 0))
	        AS moderatedcount,
       SUM(IF(articles.moderator_id IS NULL, 1, 0))
	        AS unmoderatedcount
FROM users JOIN articles ON users.id = articles.author_id
GROUP BY users.id;

При това решение забележете, че потребителите с едновременно 0 рецензирани и 0 одобрени статии автоматично се премахват още от INNER JOIN условието - тези потребители, които въобще нямат статия не участват в по-нататъшните сметки. Очевидно е, че тази заявка е много по-добра и дори по-пригледна от предишната.

Допълнителна задача: Направете така, че да не е възможно даден автор да е рецензент сам на себе си.