Posted by: b_sleeth
« on: January 07, 2013, 08:57:40 pm »I actually have 3 flavors of the export template.
The first one is for use with the .xslt transformation. Besides making the exported data a well-formed XML document rooted at "<movies>", it also wraps the exported data in "<![CDATA[", "]]>" tags. In hopes of getting the auto-load feature to work, I also replaced all CR/LF in the export data with "<br>". This is required because the auto-load PHP script uses a read operation that breaks the data by CR/LF and this causes problems when attempting to run each line of text as a full SQL statement. Although the "<br>" fixed this problem, the auto-load routine still suffers from a character encoding problem which causes "garbage" characters to show up in the data (e.g., "Alfonso Cuarón" might be imported as "Alfonso CuarÅn").
My second export template was an experiment with the MySQL "LOAD XML". This export template allows you to simply export your movie database and then use the following MySQL statements to import the XML file without having to use .xslt transformation. This method worked great on my local MySQL v5.5 database; unfortunately, my hosting site is using MySQL v5.1 which does not support the "LOAD XML" command (I think "LOAD XML" was added in 5.5). The full SQL import (including drop/add of the movies table) is:
The third export template was another experiment, this time with MySQL DUMP. Unfortunately, there appears to be no native method to restore a mysqldump which was dumped using the XML format (--xml option). There is a Google Code project, mysqldump-x-restore, which provides an .xslt style sheet to transform the XML DUMP into INSERT statements. But, that does not eliminate the extra step. There is also a MySQL DevZone article, Using XML in MySQL 5.1 and 6.0, which discusses adding a stored procedure that will handle the import.
The bottom line is that I am using the first export template and the PVD_Movies_bs.xsl transformation style sheet (also provided). Once my hosting site updates to MySQL 5.5 (as-if that is going to happen anytime soon), I will use the second export template so that I do not need to run the transformation.
The end result of all of this can bee seen at Sleeth DVD Library. If you are interested my CSS and web templates, I can post those as well along with my "readme" notes to myself regarding the changes I made (FYI: My notes are "mostly" complete, but I am sure I missed one or two things).
Also note that none of my export templates attempt to fix the date format issue as documented by devilingrey. I use a text editor that supports a RegEx find/replace command and use the following commands (without quotes ... actually, I created a macro to run all of the find/replace commands and then save and close the file):
Find RegExp "(1[0-2])\/([1-3][0-9])\/([1-9][0-9]{0,3})"
Replace All "\3-\1-\2"
Find RegExp "(1[0-2])\/([1-9])\/([1-9][0-9]{0,3})"
Replace All "\3-\1-0\2"
Find RegExp "([1-9])\/([1-3][0-9])\/([1-9][0-9]{0,3})"
Replace All "\3-0\1-\2"
Find RegExp "([1-9])\/([1-9])\/([1-9][0-9]{0,3})"
Replace All "\3-0\1-0\2"
These series of find/replace commands are a bit retentive as they are ensuring a valid month (1-9, or 10-12), a semi-valid date (1-9, 10-39), and a 4-digit year. The end result is that dates will be reformatted as yyyy-mm-dd (where month and day have leading zeros).
[attachment deleted by admin]
The first one is for use with the .xslt transformation. Besides making the exported data a well-formed XML document rooted at "<movies>", it also wraps the exported data in "<![CDATA[", "]]>" tags. In hopes of getting the auto-load feature to work, I also replaced all CR/LF in the export data with "<br>". This is required because the auto-load PHP script uses a read operation that breaks the data by CR/LF and this causes problems when attempting to run each line of text as a full SQL statement. Although the "<br>" fixed this problem, the auto-load routine still suffers from a character encoding problem which causes "garbage" characters to show up in the data (e.g., "Alfonso Cuarón" might be imported as "Alfonso CuarÅn").
My second export template was an experiment with the MySQL "LOAD XML". This export template allows you to simply export your movie database and then use the following MySQL statements to import the XML file without having to use .xslt transformation. This method worked great on my local MySQL v5.5 database; unfortunately, my hosting site is using MySQL v5.1 which does not support the "LOAD XML" command (I think "LOAD XML" was added in 5.5). The full SQL import (including drop/add of the movies table) is:
Code: [Select]
USE rainfusion_movies;
DROP TABLE IF EXISTS movies;
CREATE TABLE movies (NUM INT NOT NULL, TITLE TEXT, ORIGINALTITLE TEXT, AKA TEXT, YEAR INT,
CATEGORY TEXT, COUNTRY TEXT, STUDIO TEXT, RELEASES TEXT, RATING TEXT, TAGS TEXT,
DIRECTOR TEXT, PRODUCER TEXT, SCENARIO TEXT, MUSIC TEXT, ACTORS TEXT, DESCRIPTION TEXT,
COMMENT TEXT, TAGLINE TEXT, URL TEXT, IMDBRATING FLOAT, ALLMOVIERATING FLOAT, ORATING TEXT,
DATEADDED DATE, PATH TEXT, COUNT TEXT, TYPE TEXT, MEDIATYPE TEXT, LENGTH INT, FILESIZE INT,
LANGUAGE TEXT, SUBTITLES TEXT, TRANSLATION TEXT, RESOLUTION TEXT, FRAMERATE TEXT,
VIDEOFORMAT TEXT, VIDEOBITRATE INT, AUDIOFORMAT TEXT, AUDIOBITRATE INT, LABEL TEXT,
FEATURES TEXT, VIEWED TEXT, VIEWDATE TEXT, WISH TEXT, BOOKMARK TEXT, PICTURENAME TEXT,
PERIOD VARCHAR(15), PRIMARY KEY (NUM));
LOAD XML
LOCAL
INFILE 'antexport/movies.xml'
REPLACE
INTO TABLE movies
ROWS IDENTIFIED BY '<row>';
UPDATE movies a
SET a.PERIOD = CASE
WHEN a.YEAR >= 2000 THEN CONCAT(LEFT(CAST(a.YEAR AS CHAR(4)), 3),
CASE WHEN RIGHT(CAST(a.YEAR AS CHAR(4)), 1) >= "5" THEN "5" ELSE "0" END,
"-", LEFT(CAST(a.YEAR AS CHAR(4)), 3),
CASE WHEN RIGHT(CAST(a.YEAR AS CHAR(4)), 1) >= "5" THEN "9" ELSE "4" END)
WHEN a.YEAR >= 1930 THEN CONCAT(LEFT(CAST(a.YEAR AS CHAR(4)), 3), "0-", LEFT(CAST(a.YEAR AS CHAR(4)), 3), "9")
WHEN a.YEAR >= 1888 THEN "1888-1929"
ELSE "Unknown" END;The third export template was another experiment, this time with MySQL DUMP. Unfortunately, there appears to be no native method to restore a mysqldump which was dumped using the XML format (--xml option). There is a Google Code project, mysqldump-x-restore, which provides an .xslt style sheet to transform the XML DUMP into INSERT statements. But, that does not eliminate the extra step. There is also a MySQL DevZone article, Using XML in MySQL 5.1 and 6.0, which discusses adding a stored procedure that will handle the import.
The bottom line is that I am using the first export template and the PVD_Movies_bs.xsl transformation style sheet (also provided). Once my hosting site updates to MySQL 5.5 (as-if that is going to happen anytime soon), I will use the second export template so that I do not need to run the transformation.
The end result of all of this can bee seen at Sleeth DVD Library. If you are interested my CSS and web templates, I can post those as well along with my "readme" notes to myself regarding the changes I made (FYI: My notes are "mostly" complete, but I am sure I missed one or two things).
Also note that none of my export templates attempt to fix the date format issue as documented by devilingrey. I use a text editor that supports a RegEx find/replace command and use the following commands (without quotes ... actually, I created a macro to run all of the find/replace commands and then save and close the file):
Find RegExp "(1[0-2])\/([1-3][0-9])\/([1-9][0-9]{0,3})"
Replace All "\3-\1-\2"
Find RegExp "(1[0-2])\/([1-9])\/([1-9][0-9]{0,3})"
Replace All "\3-\1-0\2"
Find RegExp "([1-9])\/([1-3][0-9])\/([1-9][0-9]{0,3})"
Replace All "\3-0\1-\2"
Find RegExp "([1-9])\/([1-9])\/([1-9][0-9]{0,3})"
Replace All "\3-0\1-0\2"
These series of find/replace commands are a bit retentive as they are ensuring a valid month (1-9, or 10-12), a semi-valid date (1-9, 10-39), and a 4-digit year. The end result is that dates will be reformatted as yyyy-mm-dd (where month and day have leading zeros).
[attachment deleted by admin]
. If you use the floatbox (v 3.51) as included with the downloads, the height of the box will not be correct. So, you may want to change it to something like "width:443 height:315". This will work for most movie descriptions.