Starting a migration path from video to filefield

I'm just showing my work as I move forward. I think the best plan for this migration script is to:

1. Advise users to back up their database.
2. Require users to add a cck filefield to their video content types.
3. Require users to create a new node type with the same filefield.
4. Script the insertion of video module values into corresponding filefield fields.
5. Script the changes of all video nodes into the user's new node type.

Working on the big part, number 4, I've picking apart the tables/fields touched by both video and filefield in order to learn how to map them. This is what I sorted out so far but I have a few holes that I don't know how to fill:

In the original video table there are only a few important fields to use as source information:

'nid' - node ID
'vidfile' - the full path to the location of the video file
'size' - which is the user-entered file size, not always the actual size

Using the above info, content_field_video should be mapped like this:

'nid' ---> 'nid' from video table
'vid' ---> 'nid' from video, assuming only one node version
'field_video_fid' ---> serialized file ID to match files table
'field_video_description' ---> 'vidfile' from video, file name only
'field_video_list' ---> value of 1, assuming no multiple files in field

The files table needs to be mapped like this:

'fid' ---> matching serialized value from video 'field_video_fid'
'nid' ---> matching video 'nid'
'filename' ---> 'vidfile' from video table, file name only
'filepath' ---> 'vidfile' from video, local path no file name
'filemime' ---> no matching field in video, detected during file upload
'filesize' ---> 'size' from video, but not always accurate

Adapted script so far, missing the important field mapping steps listed in comment #1.

<?php
/**
* @file
* This script should transform video nodes (video.module) into custom CCK nodes with associated filefields.
* This is the first step in migrating from the video module to the filefield + jQuery_media recipe for
* managing video uploads and playback though it may have many other uses.
* 
* PREREQUISITES
* --------------
* - Must create a new custom content type to become your new video nodes (ex: video2)
* - Must create a new custom CCK file field and configure it to "always list files" (ex: field_file)
* - Assign this new field to both original video content type and your new custom content type
* - The file field's file path should be set to match the file path for the video module (example: files/videos)
* - If your original video nodes have other custom fields, assign those fields to your new content type too
* 
* USAGE
* ----------
* - Backup your Drupal database. Really.
* - Edit the 'Configuration' section below.
* - Place this script in the root of your Drupal site.
* - Run this script by requesting yoursite.com/video_migration.php in your browser.
* - Remove this script from your site to prevent accidental re-run.
* - Disable and uninstall the video modules.
* - Configure user access control as needed.
*
* KNOWN ISSUES
* -------------
* -  Files that have a '+' in the file name must be renamed in both the file system and files table.
*
* AUTHORS
*  -----------
* spydor (see <a href="http://drupal.org/node/201983#comment-828698" title="http://drupal.org/node/201983#comment-828698" rel="nofollow">http://drupal.org/node/201983#comment-828698</a>)
* Moshe Weitzman (<a href="http://drupal.org/moshe" title="http://drupal.org/moshe" rel="nofollow">http://drupal.org/moshe</a>)
* Sean Effel, code scavenger/bottomfeeder
*/

// ***** CONFIGURATION *******

// The file field that you have already created and configured as per Prerequisites.
$field_name = 'field_file';

// The content type that you have already created as per Prerequisites.
$type_name = 'video2';

// ***** END CONFIGURATION *******

require_once './includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);


// Populate the field_video table for every video node.
$table = 'content_'. $field_name;
$fid = $field_name. '_fid';
$description = $field_name. '_description';
$list = $field_name. '_list';

$sql = "INSERT INTO $table (vid, nid, $fid, $description, $list) SELECT n.vid, n.nid, f.fid, n.vidfile FROM video n, '1' WHERE n.nid = f.nid AND n.type = 'video'";
if (db_query($sql)) {
  echo "- $table populated.<br />\n";
}

//  Insert video file information into files table, with magic.
//
//
//  alakazam!


// Change the content type from 'video' to the configured type per Prerequisites.
$sql = "UPDATE node SET type = '%s' WHERE type = 'video'";
db_query($sql, $type_name);


// Clear CCK cache.
$sql = "DELETE FROM cache_content";
db_query($sql);
?>

How am I doing so far? This is my stab at populating the new field_video table for each video node. I can tell for certain that there will be an issue, the video table's vidfile value is a path (http://something/something/file.mov) when instead I need to insert only the filename (file.mov). I haven't a clue on how to pull this off.

<?php

// Populate the field_video table for every video node.
$table = 'content_'. $field_name;
$fid = $field_name. '_fid';
$description = $field_name. '_description';
$list = $field_name. '_list';

$sql = "INSERT INTO $table (vid, nid, $fid, $description, $list) SELECT n.vid, n.nid, f.fid, n.vidfile FROM video n, '1' WHERE n.nid = f.nid AND n.type = 'video'";
if (db_query($sql)) {
  echo "- $table populated.<br />\n";
}

?>

This is awesome, I'm trying to learn how to apply it in this case as we speak.

The video module holds the URL of each hosted video file in 'vidfile', so this would work. One of the hitches is that video handles data entry two ways. Users can either attach the file to the node and have it uploaded to the /files/videos directory, or manually enter the URL of a file hosted anywhere (sometimes even Youtube, etc). This second scenario puts a hiccup in my plan.

Maybe its a good idea to make this script so it only effects video nodes hosted in the site's /files/videos directory. We can write the instructions up to reflect this, and advise the user to manually change video nodes that are not hosted on their own server. Be nice to get a list of nodes that were not hosted in the files directory so that the users could find them, edit them, and potentially run the script again.

An alternative might be to advise the user to move all video files blindly into the /files/videos directory after running the script. We could hard code one path into the files table in the script.

How about?

<?php
// Populate the field_video table for every video node.
$table = 'content_'. $field_name;
$fid = $field_name. '_fid';
$description = $field_name. '_description';
$list = $field_name. '_list';

// get filename from video table
$path = variable_get('vidfile', 'video');
$file = basename(str_replace("http://", "", $path));

$sql = "INSERT INTO $table (vid, nid, $fid, $description, $list) SELECT n.vid, n.nid, f.fid, $file, '1' WHERE n.nid = f.nid AND n.type = 'video'";
if (db_query($sql)) {
  echo "- $table populated.<br />\n";
}
?>

Just a shot in the dark. It doesn't yet determine if it is a locally hosted video file or a remote one.

I don't like moving files around on the server. If the script botches a users migration then its not just restoring the database but also means moving big media files back where they came from and that's a lot of work.

It might be a better step just to notify users of video nodes that won't be migrated. That is, any node with a file URL on mysite.com is OK to migrate, any node with a file URL on notmysite.com (youtube, blip, vimeo, etc) should be skipped but noted in a log somewhere.

<?php
// Config: Enter base URL of your site including http://...
$base = 'http://www.mysite.com';

// get filename from video table
$path = variable_get('vidfile', 'video');
if($path contains $base) {
  // video file is hosted locally, safe to migrate
  $filename = basename(str_replace("http://", "", $path));  //mapt to field_video and files tables
  $route = str_replace( $base, "", $path ));
  $filepath = str_replace( $filename, "", $route ));  //map to files table
}
else {
  // video file is NOT hosted locally
  // What should happen?  Print to a log?
}
?>

Please check my work, I don't even speak this language. I am not sure how to integrate this step into the larger script either.

Just wanted to try to put all this work together into one script to see how we're doing. Added a configuration line for base url, added a half baked condition in the first $sql step to only migrate video nodes whose file path contain the matching site's base url. Someone really should check my work.

What's left is the population of the files table, specifically detecting the mimetype based only on the file extension.

<?php

<?php
/**
* @file
* This script transforms video nodes (video.module) into custom CCK nodes with associated filefields.
* This is the first step in migrating from the video module to the filefield + jQuery_media recipe for
* managing video uploads and playback though it may have many other uses.
* 
* PREREQUISITES
* --------------
* - Must create a new custom content type to become your new video nodes (ex: video2)
* - Must create a new custom CCK file field and configure it to "always list files" (ex: field_file)
* - Assign this new field to both original video content type and your new custom content type
* - If video nodes have other custom fields, assign those fields to your new content type, too.
* 
* USAGE
* ----------
* - Backup your Drupal database. Really.
* - Edit the 'Configuration' section below.
* - Place this script in the root of your Drupal site.
* - Run this script by requesting yoursite.com/video_migration.php in your browser.
* - Remove this script from your site to prevent accidental re-run.
* - Disable and uninstall the video modules.
* - Configure user access control as needed.
*
* KNOWN ISSUES
* -------------
* - Only migrates video nodes containing local files, look into Emfield for remotely embedded files.
* - Files that have a '+' in the file name must be renamed in both the file system and files table.
*
*
* AUTHORS
*  -----------
* spydor (see <a href="http://drupal.org/node/201983#comment-828698" title="http://drupal.org/node/201983#comment-828698" rel="nofollow">http://drupal.org/node/201983#comment-828698</a>)
* Moshe Weitzman (<a href="http://drupal.org/moshe" title="http://drupal.org/moshe" rel="nofollow">http://drupal.org/moshe</a>)
* Sean Effel, code scavenger/bottomfeeder
*/

// ***** CONFIGURATION *******

// Set the base URL of your site including http://...
$base = 'http://www.mysite.com';

// Set the file field that you have already created and configured as per Prerequisites.
$field_name = 'field_file';

// Set the content type that you have already created as per Prerequisites.
$type_name = 'video2';


// ***** END CONFIGURATION *******

require_once './includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

// Populate the field_video table for every video node.
$table = 'content_'. $field_name;
$fid = $field_name. '_fid';
$description = $field_name. '_description';
$list = $field_name. '_list';

$path = variable_get('vidfile', 'video');
$video_name = basename(str_replace("http://", "", $path));

$sql = "INSERT INTO $table (vid, nid, $fid, $description, $list) SELECT n.vid, n.nid, f.fid, '%s', '1' WHERE n.nid = f.nid AND n.type = 'video' AND video.vidfile LIKE '%s' ";
if (db_query($sql, $video_name, $base)) {
  echo "- $table populated.<br />\n";
}

// Populate the files table since video.module doesn't even use it - Needs work
$route = str_replace( $base, "", $path ));
$video_path = str_replace( $video_name, "", $route ));  
$video_size = variable_get('size', 'video');    
$video_info = pathinfo($video_name);
$video_ext = $video_info['extension'];
$video_mimetype = (   
  switch ($video_ext) { 
    case 'mov':
      return 'video/quicktime';
    case 'avi' : 
      return 'video/x-msvideo';
    case 'mpg' :    
    case 'mpeg' :   
      return 'video/mpeg';
    case 'divx':
      return 'video/vnd.divx';
    case 'rm':
      return 'application/vnd.rn-realmedia';
    case 'flv':
      return 'flv-application/octet-stream';
    case 'asf':
      return 'video/x-ms-asf';
    case 'wmv':
      return 'video/x-ms-wmv';
    case '3gp':
      return 'video/3gpp';
    case 'mp4':
      return 'video/mp4';
    case 'dir':
    case 'dcr':
      return 'application/x-director';
    case 'ogg':
      return 'application/ogg';
    default:
      // No mimetype detected.
      return '';
    }
  )

$sql = "INSERT INTO files ( fid, nid, filename, filepath, filemime, filesize ) SELECT f.fid, n.nid, '%s', '%s', '%s', '%s' WHERE n.nid = f.nid AND n.type = 'video' AND video.vidfile LIKE '%s' ";
if (db_query($sql, $video_name, $video_path, $video_mimetype, $video_size, $base)) {
  echo "- files table populated.<br />\n";
}

// Change the content type from 'video' to the configured type per Prerequisites.
$sql = "UPDATE node SET type = '%s' WHERE type = 'video' AND video.vidfile LIKE '%s'";
db_query($sql, $type_name, $base);


// Clear CCK cache.
$sql = "DELETE FROM cache_content";
db_query($sql);

?>

I realize we have to get the mimetype of each of the files. The video module doesn't capture this as data, but instead treats files based on their extension. I'm picking apart a function from the video module that detects mimetype based on the file extension, how am I doing?

<?php

// Get video node filename
$path = variable_get('vidfile', 'video');
$video_name = basename(str_replace("http://", "", $path));

// reduce filename to extension only, does this only return 3-character exts?
$video_info = pathinfo($video_name);
$video_ext = $video_info['extension'];

// Get the mimetype based on extension
$video_mimetype = (
  switch ($video_ext) {
    case 'mov':
      return 'video/quicktime';
    case 'avi' : 
      return 'video/x-msvideo';
    case 'mpg' :    
    case 'mpeg' :   
      return 'video/mpeg';
    case 'divx':
      return 'video/vnd.divx';
    case 'rm':
      return 'application/vnd.rn-realmedia';
    case 'flv':
      return 'flv-application/octet-stream';
    case 'asf':
      return 'video/x-ms-asf';
    case 'wmv':
      return 'video/x-ms-wmv';
    case '3gp':
      return 'video/3gpp';
    case 'mp4':
      return 'video/mp4';
    case 'dir':
    case 'dcr':
      return 'application/x-director';
    case 'ogg':
      return 'application/ogg';
    default:
      // No mimetype detected.
      return '';
    }
  )

?>

This is my first experience writing up code so thanks for pointing this out. I've edited my comments above to reflect this.

The Mimetype module is required for Filefield already but I don't think we can harness it here, only when uploading fresh files?

If anyone had a moment to look over the three times this script runs an $sql, this is the place where I have the least confidence in my work.

d, did you look at jquery_media yet?
http://www.drupal.org/project/jquery_media

I'm a lover of this jquery module, I made up a decent screencast on how to config:
http://www.drupaltherapy.com/node/50

In the meantime, if you have some skill with mysql, peek at my queries above and tell me if I'm totally whacked. Especially in the WHERE areas and relating to the video table.

@dman: Look at Snapz Pro for screen capture. Jing is free if you sign up for their free service and host with them.

@wmostrey: Thanks for your help with this so far, I'm going to edit the above comments with your changes and give it a maiden voyage. Will report back.

Parse error: syntax error, unexpected T_ECHO in /Applications/MAMP/htdocs/video_migration.php on line 60

Still trying to get to the bottom of this

The script I am currently working on is:

<?php
/**
* @file
* This script transforms video nodes (video.module) into custom CCK nodes with associated filefields.
* This is the first step in migrating from the video module to the filefield + jQuery_media recipe for
* managing video uploads and playback though it may have many other uses.
* 
* PREREQUISITES
* --------------
* - Must create a new custom content type to become your new video nodes (ex: video2)
* - Must create a new custom CCK file field and configure it to "always list files" (ex: field_file)
* - Assign this new field to both original video content type and your new custom content type
* - If video nodes have other custom fields, assign those fields to your new content type, too.
* 
* USAGE
* ----------
* - Backup your Drupal database. Really.
* - Edit the 'Configuration' section below.
* - Place this script in the root of your Drupal site.
* - Run this script by requesting yoursite.com/video_migration.php in your browser.
* - Remove this script from your site to prevent accidental re-run.
* - Disable and uninstall the video modules.
* - Configure user access control as needed.
*
* KNOWN ISSUES
* -------------
* - Only migrates video nodes containing local files, look into Emfield for remotely embedded files.
* - Files that have a '+' in the file name must be renamed in both the file system and files table.
*
*
* AUTHORS
*  -----------
* spydor (see <a href="http://drupal.org/node/201983#comment-828698" title="http://drupal.org/node/201983#comment-828698" rel="nofollow">http://drupal.org/node/201983#comment-828698</a>)
* Moshe Weitzman (<a href="http://drupal.org/moshe" title="http://drupal.org/moshe" rel="nofollow">http://drupal.org/moshe</a>)
* Sean Effel, code scavenger/bottomfeeder
*/

// ***** CONFIGURATION *******

// Set the base URL of your site including http://...
$base = 'http://www.mysite.com';

// Set the file field that you have already created and configured as per Prerequisites.
$field_name = 'field_file';

// Set the content type that you have already created as per Prerequisites.
$type_name = 'video2';


// ***** END CONFIGURATION *******

require_once './includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

// Populate the field_video table for every video node.
$table = 'content_'. $field_name;
$fid = $field_name. '_fid';
$description = $field_name. '_description';
$list = $field_name. '_list';

$path = variable_get('vidfile', 'video');
$video_name = basename(str_replace("http://", "", $path));

$sql = "INSERT INTO $table (vid, nid, $fid, $description, $list) SELECT n.vid, n.nid, f.fid, '%s', '1' WHERE n.nid = f.nid AND n.type = 'video' AND video.vidfile LIKE '%s' ";
if (db_query($sql, $video_name, $base)) {
  echo "- $table populated.<br />\n";
}

// Populate the files table since video.module doesn't even use it - Needs work
$route = str_replace( $base, "", $path ));
$video_path = str_replace( $video_name, "", $route ));  
$video_size = variable_get('size', 'video');    
$video_info = pathinfo($video_name);
$video_ext = $video_info['extension'];
$video_mimetype = (   
  switch ($video_ext) { 
    case 'mov':
      return 'video/quicktime';
    case 'avi' : 
      return 'video/x-msvideo';
    case 'mpg' :    
    case 'mpeg' :   
      return 'video/mpeg';
    case 'divx':
      return 'video/vnd.divx';
    case 'rm':
      return 'application/vnd.rn-realmedia';
    case 'flv':
      return 'flv-application/octet-stream';
    case 'asf':
      return 'video/x-ms-asf';
    case 'wmv':
      return 'video/x-ms-wmv';
    case '3gp':
      return 'video/3gpp';
    case 'mp4':
      return 'video/mp4';
    case 'dir':
    case 'dcr':
      return 'application/x-director';
    case 'ogg':
      return 'application/ogg';
    default:
      // No mimetype detected.
      return '';
    }
  )

$sql = "INSERT INTO files ( fid, nid, filename, filepath, filemime, filesize ) SELECT f.fid, n.nid, '%s', '%s', '%s', '%s' WHERE n.nid = f.nid AND n.type = 'video' AND video.vidfile LIKE '%s' ";
if (db_query($sql, $video_name, $video_path, $video_mimetype, $video_size, $base)) {
  echo "- files table populated.<br />\n";
}

// Change the content type from 'video' to the configured type per Prerequisites.
$sql = "UPDATE node SET type = '%s' WHERE type = 'video' AND video.vidfile LIKE '%s'";
db_query($sql, $type_name, $base);


// Clear CCK cache.
$sql = "DELETE FROM cache_content";
db_query($sql);

?>

Wim, thanks for all your help here. I've got no results from running this script. I'm running it here on a Mac OS X and MAMP development site and I get nothing but the white screen displaying when the script runs. Even with error_reporting switched on, I can't learn anything more.

Here are some changes to the two sql runs, but running them still turns up a blank screen. My grasp of mysql is so poor that I'm basically making these things up. I understand the logic but I haven't got the language or vocabulary to write the queries. It will take a much bigger gun than me to straighten them out.

<?php
// insert values into the filefield table from the video table
$sql = "INSERT INTO $table (vid, nid, '%d', '%s', '%d') SELECT n.vid, n.nid, f.fid, '%s', '1' FROM video WHERE n.nid = f.nid AND n.type = 'video' AND video.vidfile LIKE '%s' ";
if (db_query($sql, $fid, $description, $list, $video_name, $base)) {
  echo "- $table populated.<br />\n";
}
?>

<?php
// insert values into the files table from the video table
$sql = "INSERT INTO files ( fid, nid, filename, filepath, filemime, filesize ) SELECT f.fid, n.nid, '%s', '%s', '%s', '%d' FROM video WHERE n.nid = f.nid AND n.type = 'video' AND video.vidfile LIKE '%s' ";
if (db_query($sql, $video_name, $video_path, $video_mimetype, $video_size, $base)) {
  echo "- files table populated.<br />\n";
}
?>

Yes, video table includes a nid that matches to node table. Look at comment #1 for the field mapping I intended to do.

I definitely don't understand the f. and n. thingies.

The $base represents the site domain, which is only part of the value held in the vidfile in video table. Vidfile contains the full web address of the file. That's why I tried this LIKE command hoping to get something like an "if vidfile contains $base then". The way it looks now it wouldn't work?

It seems as if the script is running but not doing anything. I get a white screen when the script loads, but no errors. I made a few changes, yours from above and added curly brackets around table names at the start of INSERT INTO {files} and so on. Something is not right, could it be the environment that I am working in, on a local MAMP install?

Also, is there some check or test that can alert me on run when something doesn't do what's expected?

The only thing written on the mysql or the php error logs is what looks like startup stuff, no events recorded when this script runs.

080904 14:12:38 mysqld started
080904 14:12:38 [Warning] You have forced lower_case_table_names to 0 through a command-line option, even though your file system '/Applications/MAMP/db/mysql/' is case insensitive. This means that you can corrupt a MyISAM table by accessing it with different cases. You should consider changing lower_case_table_names to 1 or 2
080904 14:12:38 [Warning] One can only use the --user switch if running as root

080904 14:12:38 InnoDB: Started; log sequence number 0 43655
080904 14:12:38 [Note] /Applications/MAMP/Library/libexec/mysqld: ready for connections.
Version: '5.0.41' socket: '/Applications/MAMP/tmp/mysql/mysql.sock' port: 8889 Source distribution

I changed the single quotes around the {%s} in the first sql, it did not help. Actually, none of the queries seem to be running properly. Could this be from my use of the $base value in each of the queries? All three have a WHERE $base LIKE v.vidfile, and if I handled that wrong then they wouldn't work.

My thought process was to exclude database changes to video nodes that did not hold the files on the same server. The user set the $base value as the domain name in the config section. Since the video table's vidfile is a web address it would either contain or not contain the $base value. If vidfile contained $base it was a local file and safe to change.

However, stripping out the whole AND v.vidfile LIKE '%%s%' portion of the query gives the same white screen results.

I'll email you with a link to download. Thanks very much for helping out.

I get some activity when the script runs now. I get a "files table populated" and some errors for a few missing files, but no files are attached to the nodes and the node types aren't changed. It's a mystery.

At one point the script asks for user configured URL information that is stored by the video module. At another point it pulls local URL information from the server itself. Then in the queries it compares the two in the WHERE portions.

For me, I'm running this script on a copy of my production database but hosted on a dev server, so those two values would never match up. Is this going to break the queries and tell me that the files table was populated but not do any other operations?

Here is the version I'm working with.

Status update...

Throughout this process of tweaking this script, I have been spending about 30 minutes a day manually transforming the several hundred video nodes on our organizational site. Just this morning I managed to transform the last one, and my need for this script has evaporated for the most part.

I'm still interested in completing it, but I don't have the time to test and debug it and as I said before this is really not a strength of mine to begin with. Hopefully its close enough that the next person to need it can work out the kinks on their own.

I'll still be watching this thread so I can help now and then. Thanks to the folks above that pitched in to get this migration path this far.