To save user's rating, call rate.php
:
rate.php?user=Trapper852@Adelphia.net&song_id=1500&rating=10 http://d02-0330a.kn.vutbr.cz/winlyrics/real/rate.php?user=Trapper852@Adelphia.net&song_id=1500&rating=10
Params:
@param user string Identifier - e.g. e-mail - of the user who rates the song. @param song_id unsigned int ID of the rated song. UNSIGNED INT @param rating signed byte Rating of the song.
Returns:
one of OK
, BAD_PARAM
, DB_ERROR
,
SQL_ERROR
, SESSION_ERROR
.
May be followed by a space and additional data, specifying the error details.
OK
– everything ok, rating saved.BAD_PARAM
– bad parameters (see above) – may also mean
non-existent user, song, etc.
DB_ERROR
– database error, e.g. could not connect
to DB.SQL_ERROR
– error occured when performing SQL query. Details
in /logs/log.txt
.SESSION_ERROR
– given session ID is not valid, need to
re-authentize.authentize.php
1.
>> `http://localhost:81/winlyrics/real/rate_sess.php?sess_id=f8675g1390s7dngloohqitsd74&song_id=15&rating=10`
2.
<< `OK`
Params:
@param sess_id string Session ID, previously acquired using authentize.php @param song_id unsigned int ID of the rated song. UNSIGNED INT @param rating signed byte Rating of the song.
Returns: Same as above.
Creates a user-song-rating triplet for user
Trapper852@Adelphia.net
and song 1500
, rated
-10
:
1.
CALL wl2_SetRating( (
SELECT
id
FROM
wl2_users
WHERE
email =
'Trapper852@Adelphia.net'
), 1500, -10 );
Changes that rating to 50:
1.
CALL wl2_SetRating( (
SELECT
id
FROM
wl2_users
WHERE
email =
'Trapper852@Adelphia.net'
), 1500, 50 );
Creates rating for other song and other user:
1.
CALL wl2_SetRating( 20, 1700, 40 );
Does nothing (invalid user)
1.
CALL wl2_SetRating( (
SELECT
id
FROM
wl2_users
WHERE
email =
'non@existent@user'
), 200, 30 );
Removes the rating for given user-song tuple:
1.
CALL wl2_SetRating( (
SELECT
id
FROM
wl2_users
WHERE
email =
'Trapper852@Adelphia.net'
), 1500,
NULL
);
01.
CREATE
TABLE
`winlyricscom`.`wl2_users`
SELECT
*
FROM
users;
02.
ALTER
TABLE
`winlyricscom`.`wl2_users`
03.
ADD
COLUMN
`pass_md5`
char
(32)
character
set
cp1250
collate
cp1250_bin
NOT
NULL
,
04.
MODIFY
`email` `email`
varchar
(128)
character
set
cp1250
NOT
NULL
,
05.
KEY
`email` (`email`);
06.
07.
-- evt. --
08.
INSERT
INTO
wl2_users
09.
SELECT
id,
''
AS
pass, jmeno, adresa, mesto, stat_kod, postal, email
FROM
reg_users;
1.
CREATE
TABLE
`winlyricscom`.`wl2_songs_rating` (
2.
`id_song`
int
(10) unsigned
NOT
NULL
auto_increment,
3.
`rating_sum`
int
(10) unsigned
NOT
NULL
,
4.
`rating_cnt`
int
(10) unsigned
NOT
NULL
,
5.
`rating`
float
NOT
NULL
,
6.
PRIMARY
KEY
(`id_song`)
7.
) ENGINE=MyISAM
DEFAULT
CHARSET=utf-8;
1.
CREATE
TABLE
`winlyricscom`.`wl2_users_ratings` (
2.
`id_user`
int
(10) unsigned
NOT
NULL
,
3.
`id_lyrics`
int
(10) unsigned
NOT
NULL
,
4.
`rating` tinyint(4)
NOT
NULL
,
5.
PRIMARY
KEY
(`id_user`,`id_lyrics`)
6.
) ENGINE=MyISAM
DEFAULT
CHARSET=utf8;
wl2_SetRating
Creates / updates rating of given song by the given user. Repeated rating
from one user changes his previous rating. Stores the data in
wl2_users_ratings
.
01.
DELIMITER $$
02.
03.
DROP
PROCEDURE
IF EXISTS `winlyricscom`.`wl2_SetRating`$$
04.
CREATE
DEFINER=`root`@`localhost`
PROCEDURE
`winlyricscom`.`wl2_SetRating`(idUser
INT
UNSIGNED, idSong
INT
UNSIGNED, iRating TINYINT)
05.
BEGIN
06.
07.
08.
SELECT
rating
INTO
@iRating
FROM
wl2_users_ratings
WHERE
id_user = idUser
AND
id_song = idSong;
09.
10.
IF @iRating = 0
THEN
11.
INSERT
INTO
wl2_users_ratings
SET
id_user = idUser, id_song = idSong, rating = iRating;
12.
ELSE
13.
UPDATE
wl2_users_ratings
SET
rating_sum = iRating
WHERE
id_user = idUser
AND
id_song = idSong;
14.
END
IF;
15.
16.
17.
END
$$
18.
19.
DELIMITER ;
wl2_RebuildRating
Rebuilds the average ratings in wl2_songs_rating
based on data
in wl2_users_ratings
.
01.
DELIMITER $$
02.
03.
DROP
PROCEDURE
IF EXISTS `winlyricscom`.`wl2_RebuildRating`$$
04.
CREATE
DEFINER=`root`@`localhost`
PROCEDURE
`winlyricscom`.`wl2_RebuildRating`()
05.
BEGIN
06.
07.
TRUNCATE
wl2_songs_rating;
08.
09.
INSERT
INTO
wl2_songs_rating
10.
SELECT
id_song,
11.
SUM
(rating)
AS
rating_sum,
COUNT
(*)
AS
rating_cnt,
12.
SUM
(rating) /
COUNT
(*)
AS
rating
13.
FROM
wl2_users_ratings
GROUP
BY
id_song;
14.
15.
16.
17.
END
$$
18.
19.
DELIMITER ;