Andmebaasi lahendusvõtted - jaagupku/spicy-memes GitHub Wiki

1.1 Andmete salvestamine andmebaasi

Andmete salvestamine on teostatud protseduuridega. Link andmebaasi .sql faili ja model ning kontroller.

CREATE  PROCEDURE sp_add_comment (IN a_meme_id INT, IN a_user_id INT, IN a_message TEXT CHARSET utf8)
INSERT INTO comments (Id, Meme_Id, User_Id, Message, Date, Points) VALUES (NULL, a_meme_id, a_user_id, a_message, CURRENT_TIMESTAMP, 0);

CREATE  PROCEDURE sp_add_meme (IN a_title VARCHAR(255), IN a_user_id INT, IN a_data_type ENUM('P','V'), IN a_data VARCHAR(100))
INSERT INTO meme (Id, Title, User_Id, Data_Type, Data, Date, Up_Points, Down_Points, hotness) VALUES (NULL, a_title, a_user_id, a_data_type, a_data, CURRENT_TIMESTAMP, '0', '0', '0');

CREATE  PROCEDURE sp_add_user (IN a_user_type INT(1), IN a_user_name VARCHAR(32) CHARSET utf8, IN a_passwdhash VARCHAR(255) CHARSET utf8, IN a_email VARCHAR(128) CHARSET utf8, IN a_mobile VARCHAR(15) CHARSET utf8)
INSERT INTO users (Id, User_Type, User_Name, Password_Hash, Email, Creation_Date, Last_Login_Time, ProfileImg_Id, mobile_number) VALUES (NULL, a_user_type, a_user_name, a_passwdhash, a_email, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'noprofileimg.jpg', a_mobile);

1.2 Andmebaasi andmete esitamine

Andmeid esitatakse läbi vaadete. Anmebaasi .sql struktuuri link, link Memes_model.php, controller link..

CREATE VIEW v_comments AS 
    SELECT comments.Id, comments.Meme_Id, comments.User_Id ,users.User_Name, comments.Message, comments.Points, users.ProfileImg_Id 
        FROM comments INNER JOIN users ON comments.User_Id = users.Id;

CREATE VIEW v_hot_memes  AS  
    SELECT meme.Id, meme.Title, meme.User_Id, users.User_Name, meme.Data_Type, meme.Data, meme.Date, (meme.Up_Points + meme.Down_Points) AS Points 
        FROM meme JOIN users ON meme.User_Id = users.Id ORDER BY meme.hotness DESC;

1.3 Agregeeritud andmete esitamine

Link model faili, link controller faili. link .sql faili

CREATE  PROCEDURE sp_user_total_memes (IN a_user_id INT)
SELECT meme.Data_Type, COUNT(*) AS `count` FROM meme WHERE meme.User_Id=a_user_id GROUP BY meme.Data_Type;