2014-04-05, 01:05
Awesome! This is exactly what I was looking for. I ran the script against my MyVideos75 db and it returned an error. Any ideas?
SQL query: Documentation
/* Trigger der den Playcount etc überträgt
*/
DELIMITER |
DROP TRIGGER IF EXISTS `bu_files`;
CREATE TRIGGER `bu_files` BEFORE UPDATE ON `files`
FOR EACH ROW BEGIN
DELETE FROM filestate WHERE filestate.idFile = new.idFile AND filestate.sqlUser = SUBSTRING_INDEX(USER(),'@',1);
INSERT INTO filestate (idFile, lastPlayed, playCount, sqlUser) VALUES(new.idFile, new.lastPlayed, new.playCount, SUBSTRING_INDEX(USER(),'@',1));
END;
/* Erzeugt die movieview neu.
* Enthält auch Änderungen für die RESUME bookmarks
*/
DROP VIEW IF EXISTS `movieview`;
CREATE VIEW `movieview` AS
SELECT movie.*, sets.strSet AS strSet, files.strFileName AS strFileName, path.strPath AS strPath,
filestate.playCount AS playCount, filestate.lastPlayed AS lastPlayed, files.dateAdded AS dateAdded,
bookmark_orig.timeInSeconds AS resumeTimeInSeconds, bookmark_orig.totalTimeInSeconds AS totalTimeInSeconds
FROM movie
LEFT JOIN sets ON sets.idSet = movie.idSet
[...]
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TRIGGER `bu_files` BEFORE UPDATE ON `files`
FOR EACH ROW BEGIN
DELE' at line 3
SQL query: Documentation
/* Trigger der den Playcount etc überträgt
*/
DELIMITER |
DROP TRIGGER IF EXISTS `bu_files`;
CREATE TRIGGER `bu_files` BEFORE UPDATE ON `files`
FOR EACH ROW BEGIN
DELETE FROM filestate WHERE filestate.idFile = new.idFile AND filestate.sqlUser = SUBSTRING_INDEX(USER(),'@',1);
INSERT INTO filestate (idFile, lastPlayed, playCount, sqlUser) VALUES(new.idFile, new.lastPlayed, new.playCount, SUBSTRING_INDEX(USER(),'@',1));
END;
/* Erzeugt die movieview neu.
* Enthält auch Änderungen für die RESUME bookmarks
*/
DROP VIEW IF EXISTS `movieview`;
CREATE VIEW `movieview` AS
SELECT movie.*, sets.strSet AS strSet, files.strFileName AS strFileName, path.strPath AS strPath,
filestate.playCount AS playCount, filestate.lastPlayed AS lastPlayed, files.dateAdded AS dateAdded,
bookmark_orig.timeInSeconds AS resumeTimeInSeconds, bookmark_orig.totalTimeInSeconds AS totalTimeInSeconds
FROM movie
LEFT JOIN sets ON sets.idSet = movie.idSet
[...]
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CREATE TRIGGER `bu_files` BEFORE UPDATE ON `files`
FOR EACH ROW BEGIN
DELE' at line 3