Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.


Messages - b_sleeth

Pages: [1]
1
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:
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]

2
One other note.  I changed the .xsl to automatically create and update the PERIOD column.  This means that the processperiod.php does not need to be run after you update your movie database (and if you were following along in the previous post, it also means that you do not need to modify the processperiod.php file).

The changes to the .xsl file were:

Add the PERIOD column to the table create command:
-Find: PICTURENAME TEXT
-Replace with: PICTURENAME TEXT,PERIOD VARCHAR(15)

Set PERIOD to an empty string on the insert commands:
-Find: <xsl:value-of select="');'"/>
-Replace with: <xsl:value-of select="','');'"/>

Update PERIOD after all movies have been inserted:
-Find (2 lines):
    </xsl:for-each>
  </xsl:template>

-Replace with (3 lines):
    </xsl:for-each>
    <xsl:value-of select="'UPDATE movies SET PERIOD = CASE WHEN YEAR >= 2000 THEN CONCAT(LEFT(CAST(YEAR AS CHAR(4)), 3), CASE WHEN RIGHT(CAST(YEAR AS CHAR(4)), 1) >= &quot;5&quot; THEN &quot;5&quot; ELSE &quot;0&quot; END, &quot;-&quot;, LEFT(CAST(YEAR AS CHAR(4)), 3), CASE WHEN RIGHT(CAST(YEAR AS CHAR(4)), 1) >= &quot;5&quot; THEN &quot;9&quot; ELSE &quot;4&quot; END) WHEN YEAR >= 1930 THEN CONCAT(LEFT(CAST(YEAR AS CHAR(4)), 3), &quot;0-&quot;, LEFT(CAST(YEAR AS CHAR(4)), 3), &quot;9&quot;) WHEN YEAR >= 1888 THEN &quot;1888-1929&quot; ELSE &quot;Unknown&quot; END;&#xa;'"/>
  </xsl:template>


Once again, thank you for sharing this excellent web export!

3
Thank you so much for this updated web export.  After installing the update, I came across a few things

In the list_view.tpl needed to change $LANG[RATING] to $LANG[IMDBRATING]

Movie titles are not escaped when being used as "alt=" and "title=" on HTML elements.
-This is only a problem if the movie title contains a single quote.
-I solved this by adding a function to mdb_movie.inc for $MOVIE[TITLEESC] which basically consists of:
return htmlspecialchars($this->data['TITLE'], ENT_QUOTES);
-I then changed all references of "='$MOVIE[TITLE]" to "$MOVIE[TITLEESC]" in the cover*.tpl files

I have some very non-standard width x height cover sizes which do not display correctly
-This required changing a few things
--Changed various "#mdb .movies A.cover*" CSS instruction in style.css by:
---For "IMG" elements: removed width/height, changed margin to auto, and added display:block.
---For non-"IMG" instructions: Added background-color
---This resulted in the following:
Code: [Select]
...
#mdb .movies A.cover_details IMG        { display: block; margin: auto; padding: 0px; text-decoration: none; border: 0px; }
#mdb .movies A.cover_details            { background-color: #88806B; display: block; padding: 0px; border: 4px solid #88806B; margin-right: 10px; width: 130px; height: 180px;}
...
#mdb .movies A.cover IMG        { display: block; margin: auto; padding: 0px; text-decoration: none; border: 0px;}
#mdb .movies A.cover            { background-color: #88806B; display: block; padding: 0px; border: 4px solid #88806B; margin-right: 10px; width: 156px; height: 216px;}
...
#mdb .movies A.cover2 IMG        { display: block; margin: auto; padding: 0px; text-decoration: none; border: 0px;}
#mdb .movies A.cover2            { background-color: #88806B; display: block; padding: 0px; border: 4px solid #88806B; margin-right: 10px; width: 80px; height: 115px;}
...
#mdb .movies A.cover3 IMG        { display: block; margin: auto; padding: 0px; text-decoration: none; border: 0px;}
#mdb .movies A.cover3            { background-color: #88806B; display: block; padding: 0px; border: 6px solid #88806B; margin-right: 10px; width: 315px; height: 450px;}
...
#mdb .movies A.cover_random IMG        { display: block; margin: auto; padding: 0px; text-decoration: none; border: 0px;}
#mdb .movies A.cover_random            { background-color: #88806B; display: block; padding: 0px; border: 3px solid #88806B; margin-right: 0px; width: 55px; height: 77px;}
...
--Modified the resize.php so that if both width and height are given, they are considered as maximum width/height. Bascially, replaced
Code: [Select]
if ($h == NULL) { $h = $height * ($w / $width); }
if ($w == NULL) { $w = $width * ($h / $height); }
with
Code: [Select]
if ( !empty($h) && !empty($w) ) {
$hSizedByTargetW = $height * ($w / $width);
$wSizedByTargetH = $width * ($h / $height);
// If resizing based on width causes the height to exceed target,
//  resize by the target height.
if ( $hSizedByTargetW > $h ) {
$w = $width * ($h / $height);
// If resizing based on height causes the width to exceed target,
//  resize by the target width.
} elseif ( $wSizedByTargetH > $w ) {
$h = $height * ($w / $width);
} else {
  if  ( abs($hSizedByTargetW - $h) < abs($wSizedByTargetH - $w) ) {
$w = $wSizedByTargetH;
  } else {
$h = $hSizedByTargetW;
  }
}
} else {
  if ($h == NULL) { $h = $height * ($w / $width); }
  if ($w == NULL) { $w = $width * ($h / $height); }
}
--Added height to all invocations of the resize.php script.  This was done by replacing the "&w=?" in the *.tpl files with "&w=?&h=??".  This needed to be done for all 5 image widths:
---&w=130 -> &w=130&h=180
---&w=156 -> &w=156&h=216
---&w=80 -> &w=80&h=115
---&w=315 -> &w=315&h=450
---&w=55 -> &w=55&h=77

I changed the processperiod.php to be more dynamic by replacing the calculated period values with:
Code: [Select]
if ( $y >= 2000 ) {
  $period = substr($y, 0, -1) . (substr($y, -1) >= '5' ? '5' : '0');
  $period = $period . '-' . ($period + 4);
} elseif ( $y >= 1930 ) {
  $period = substr($y, 0, -1) . '0';
  $period = $period . '-' . ($period + 9);
} elseif ( $y >= 1888 ) {
  $period="1888-1929";
} else {
  $period="Unknown";
}

I also made the processperiod.php re-runable by moving the call to calculate the periods outside of the test for the column having been added to the datbase.  The 2 lines moved were:
Code: [Select]
$this->processPeriodForMovies();
print "<br>Period processed for all movies.";

I also changed the floatbox options:
-Changed "width:443 height:300" to "width:428" in the various cover*.tpl files.
-NOTE: This requires using a new version of floatbox which auto-calculates the height.  Of course, the new version of floatbox also requires a license to prevent a nag window :( .  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.

4
Development / Resize Images to 200x248 and Change HTML to Auto Width/Height
« on: February 26, 2010, 02:23:51 am »
Once again, thank you for this great template.

In your instructions, you mention resizing images to 200px.  In the CSS, the images will be forced to 200x284 dimension.  Of course, this will cause images that are not in a 200x284 aspect ration to be squashed or stretched.

I ended up resizing my images to a maximum of Width=200px and a maximum height of 284px.  I use ImageMagick's command line tool "mogrify.exe".  Basically, the command line is:

Code: [Select]
mogrify -resize "200x284" *.jpg
Rather than monkeying with the CSS, I then changed the poster HTML IMG tags to include a style="width:auto;height:auto;".  I believe this allows the intent of bounding the display to 200x284 (as the images have already been re-sized), while preserving the image's original aspect ratio.

Thank you again for sharing this template.

5
Thank you VERY much for an excellent script and documentation.

Rather than using the "processperiod.php" script, perhaps the following SQL statement would be more beneficial:

Code: [Select]
UPDATE movies a SET a.PERIOD = CASE
    WHEN a.YEAR >= 2050 THEN '2050-9999'   WHEN a.YEAR >= 2045 THEN '2045-2049'
    WHEN a.YEAR >= 2040 THEN '2040-2044'   WHEN a.YEAR >= 2035 THEN '2035-2039'
    WHEN a.YEAR >= 2030 THEN '2030-2034'   WHEN a.YEAR >= 2025 THEN '2025-2029'
    WHEN a.YEAR >= 2020 THEN '2020-2024'   WHEN a.YEAR >= 2015 THEN '2015-2019'
    WHEN a.YEAR >= 2010 THEN '2010-2014'   WHEN a.YEAR >= 2005 THEN '2005-2009'
    WHEN a.YEAR >= 2000 THEN '2000-2004'   WHEN a.YEAR >= 1990 THEN '1990-1999'
    WHEN a.YEAR >= 1980 THEN '1980-1989'   WHEN a.YEAR >= 1970 THEN '1970-1979'
    WHEN a.YEAR >= 1960 THEN '1960-1969'   WHEN a.YEAR >= 1950 THEN '1950-1959'
    WHEN a.YEAR >= 1940 THEN '1940-1949'   WHEN a.YEAR >= 1930 THEN '1930-1939'
    WHEN a.YEAR >= 1888 THEN '1888-1889'   ELSE 'Unknown' END;

The statement assumes the PERIOD column has already been added to the database.  So, one should add the column ("PERIOD VARCHAR(15)") to the table creation script prior to creating the table, or manually alter the table by adding the column.

Also note that I changed the period descriptions slightly so as to not create overlapping descriptions.  For example, the original php script creates periods 1930-1940 and 1940-1950.  Using these descriptions, one is not sure if the year 1940 is in 1930-1940 or 1940-1950.  The period descriptions above will create the periods as 1930-1939 and 1940-1949; which clearly shows that 1940 is part of the 1940-1949 period.

One other note.  If the records in the movie table are deleted prior to importing, there is no need to drop and re-add the table.  Adding the following SQL statement to the top of the export script will accomplish this:

Code: [Select]
DELETE FROM movies;
With all of this in mind, I have attached an updated export script that adds the delete and update statements in the appropriate spots.  When used as the export template from PVD, the resulting SQL script file can be used to delete all existing records in the movies table, add all of the exported movies from PVD, and finally set the PERIOD on all of the imported records.  Of course, your previous comments of removing all occurrences of "images/" and ensuring a valid format for the date columns in the SQL script file prior to running it are still applicable.

[attachment deleted by admin]

6
Rick,

Thank you for the suggestion.  I also do not use a scanner (at least not on this system).  I have no idea how/why this version of the twain driver was installed, and therefore, do not know what driver I need to update.

In the meantime, following your suggestion, I renamed the file.  PVD now starts without an error message and I did not see any errors logged in the Event Viewer.  I will keep an eye out for any errors that might pop-up related to this (I doubt there will be any).

FYI: Renaming the file proved difficult.  I ended up finding a solution at http://www.experts-exchange.com/Microsoft/Hardware/Q_24082861.html  Guymar had a hard time as well and eventually found a solution somewhere else.  His solution was:

Quote
Solution Part 1:
Update drivers: go to relevant download section of your vendor, get them. Then go to device manager and delete current drivers (ticking the box removing them). Install/unpack vendor drivers and reboot.

Solution part 2:
Manual rename (or delete) both TWAIN.DLL and TWUNK_16.EXE

  • Log on with an admiistrator user account (*)
  • Go to start menu, accessories, right-klick on command prompt and "run as administrator"
  • Type: takeown /f c:\windows\twain.dll
  • Type: icacls c:\windows\twain.dll /grant %USERNAME%:f
  • Type: takeown /f c:\windows\twunk_16.exe
  • Type: icacls c:\windows\twunk_16.exe /grant %USERNAME%:f
  • Type: ren twain.dll twain.dll.bak
  • Type: ren twunk_16.exe twunk_16.exe.bak

*Note: If your username contains a space it will not work!! "John Doe" will have to create a user John or use the administrator user (if activated).

7
Just upgraded from 0.9.9.14 to 0.9.9.16 on Windows Vista Home Premium 64-bit.  When starting the application, I get the splash screen, then an error window titled "Unsupported 16-Bit Application" with the following text:

The program or feature "\??\C:\Windows\Twunk_16.exe" cannot start or run due
to incompatibity with 64-bit versions of Windows. Please contact the software
vendor to ask if a 64-bit Windows compatible version is available.


The only button is "OK".  Once I click OK, the error window closes and the application continues to start.  PVD seems to be running fine once started.

BTW: The error message does misspell incompatibility as "incompatibity".

Pages: [1]