Upgrade script for 4.6 -> 4.7

DOH! I didn't even think about the fact that this module would need a special upgrade script. Installing the 4.7 version in my newly upgraded 4.7 drupal instsall produced errors. Of course, the normal install script tried to create tables that already existed. It also tried to access fields/columns that weren't present in the old version (namely "tag")

Here's the error after enabling it:

user warning: Table 'audio_metadata' already exists query: CREATE TABLE audio_metadata ( `vid` int(10) unsigned NOT NULL default '0', `tag` varchar(45) NOT NULL default '', `value` varchar(255) NOT NULL default '', `clean` varchar(255) NOT NULL default '', PRIMARY KEY (`vid`,`tag`,`value`), KEY `audio_metadata_tags` (`clean`) ) /*!40100 DEFAULT CHARACTER SET utf8 */; in /home/httpd/vhosts/bloomston.com/httpdocs/includes/database.mysql.inc on line 120.
user warning: Unknown column 'tag' in 'field list' query: SELECT tag, value FROM audio_metadata WHERE vid=365 in /home/httpd/vhosts/bloomston.com/httpdocs/includes/database.mysql.inc on line 120.

I'm sure all the developers are aware of the issues, but I figured I'd post something more descriptive than a request for a script.

Maybe I'll pick through the .install and see if I can figure out what needs to be updated in the tables. Otherwise I'll be eagerly awaiting any progress on this.

Thanks!

Hi all, just wondered if anyone has created an update script yet? I'm needing to upgrade to 4.7, and I'm not sure how to make my 500 audio nodes work on the otherwise successfully upgraded 4.7 test site.

I'd really appreciate some advice on how to upgrade 4.6 audio to 4.7, and haven't yet had any luck in the forums.

Thanks

Here is a first draft of an update script. Just whipping this up, probably needs some work.

include_once "includes/bootstrap.inc";
include_once 'includes/common.inc';

switch ($GLOBALS['db_type']) {
    case 'mysql':
    case 'mysqli':
      db_query(
<<<MYSQL_UPDATE
        CREATE TABLE {audio} (
          `vid` int(10) unsigned NOT NULL default '0',
          `nid` int(10) unsigned NOT NULL default '0',
          `title_format` varchar(128) default '',
          `play_count` int(10) unsigned NOT NULL default '0',
          `download_count` int(10) unsigned NOT NULL default '0',
          `downloadable` tinyint(1) NOT NULL default '1',
          `fileformat` varchar(10) NOT NULL default '',
          `sample_rate` int(10) unsigned NOT NULL default '0',
          `channel_mode` varchar(10) NOT NULL default '',
          `bitrate` float unsigned NOT NULL default '0',
          `bitrate_mode` varchar(4) NOT NULL default '',
          `playtime` varchar(10) NOT NULL default '',
          PRIMARY KEY  (`vid`)
        ) /*!40100 DEFAULT CHARACTER SET utf8 */;
MYSQL_UPDATE
      ); 
      db_query(
<<<MYSQL_UPDATE
        CREATE TABLE  {audio_file} (
          `vid` int(10) unsigned NOT NULL default '0',
          `origname` varchar(255) NOT NULL default '',
          `filename` varchar(255) NOT NULL default '',
          `filepath` varchar(255) NOT NULL default '',
          `filemime` varchar(255) NOT NULL default '',
          `filesize` int(10) unsigned NOT NULL default '0',
          PRIMARY KEY  (`vid`)
        ) /*!40100 DEFAULT CHARACTER SET utf8 */;
MYSQL_UPDATE
      );
      db_query(
<<<MYSQL_UPDATE
        CREATE TABLE  {audio_metadata} (
          `vid` int(10) unsigned NOT NULL default '0',
          `tag` varchar(45) NOT NULL default '',
          `value` varchar(255) NOT NULL default '',
          `clean` varchar(255) NOT NULL default '',
          PRIMARY KEY  (`vid`,`tag`,`value`),
          KEY `audio_metadata_tags` (`clean`)
        ) /*!40100 DEFAULT CHARACTER SET utf8 */;
MYSQL_UPDATE
      );
      break;
  }

$result = db_query("SELECT nid FROM {node} WHERE type = audio");
while($row = db_fetch_object($result)) {
  $node = node_load(array('nid' => $row->nid));
  $fid = db_result(db_query("SELECT fid FROM {files} WHERE nid=%d limit 1", $node->nid));
  if ($fid){
    $node->audio = db_fetch_array(db_query("SELECT * FROM {audio_node_metadata} WHERE fid=%d", $fid));
    $node->files = db_fetch_array(db_query("SELECT * FROM {files} WHERE nid=%d limit 1", $node->nid));
    $node->getid3 = audio_read_id3tags($node->files['filepath']);

    // audio files table
    db_query("INSERT INTO {audio_file} (vid, origname, filename, filepath, filemime, filesize) VALUES (%d, '%s', '%s', '%s', '%s', '%s')",
             $node->nid, $node->audio['filename'], $node->audio['filename'], $node->audio['filepath'],
             $node->audio['filemime'], $node->audio['filesize']);
    // audio table
    db_query("INSERT INTO {audio} (nid, vid, title_format, play_count, downloadable, 
            fileformat, sample_rate, channel_mode, bitrate, bitrate_mode, playtime)    
            VALUES (%d, %d, '%s', %d, %d, %d, '%s', %d, '%s', %f, '%s', '%s')",
            $node->nid, $node->vid, $node->title, $node->audio['playcount'], $node->audio['downloadable'], 
            $node->getid3['fileformat'], $node->getid3['samplerate'], 
            $node->getid3['channel_mode'], $node->getid3['bitrate'], $node->getid3['bitrate_mode'], 
            $node->getid3['playtime']);
    
    $tags = array(
                  'artist' => $node->audio['artist'], 
                  'title' => $node->audio['title'], 
                  'album' => $node->audio['album'], 
                  'year' => $node->audio['year'],
                  'track' => $node->audio['track'],
                  'genre' => $node->audio['genre'], 
                  'comment' =>$node->audio['comment']
                  );
    foreach ($tags as $tag => $value) {                  
    db_query("INSERT INTO {audio_metadata} (vid, tag, value, clean) VALUES (%d, '%s', '%s', '%s')",
            $node->nid, $tag, %value, %value );
    }
  }
}

attaching it as a file, found some typos.

more typos!

Hey thanks guys - great stuff.

Just to clarify... this will be run after already updating the core of your site to 4.7 right?

I'll watch this thread with keen anticipation ;)

Right, after you've upgraded core to 4.7. This script is UNTESTED so only try it out on a dummy or test site!

I'm keen to give this script a try, but not until I figure out how to change the Private file path in Drupal..... at the moment my testsite is still running off my production sites Private files directory.

No explanation on how to change the Private files path in the forums as yet - if anyone here can help, I'll be able to try out this script (just wanting to totally seperate the test site / production site before I run it)...

"How do I change the Private files path?" forum post
http://drupal.org/node/71750

I'm going through this process now and unfortunately the script doesnt work as is.

At least, I am dumping it in the same place as update.php and it fails there.

I've debugged it a little and it looks like the bootstrap isnt quite right.

Any suggestions?

Some more details:

This http://drupal.org/files/issues/audio-upgrade-46-47.php_0.txt

fails like this:

Fatal error: Call to undefined function: db_query() in /home/MiniWeb/brownnoiseunit/public_html.472/audio-upgrade-46-47.php on line 56

db_type looks like its not set correctly either...

i just tried out drewish's updated script and it seems to have worked.

I too tried out Drewishs latest script, which also appeared to work for me.

I had a 4.6 table with around 20 audio nodes and the conversion worked perfect!
Well done!

Sorry, I didnt get a chance to test this. Luckily, I only had a few audio nodes so it wasnt a pain to manually sort it out.

Thanks for the update!

note that this script requires you have the getid3 library installed.