I am working in a migration from fireboard to vBulletin and this migration involved a certain amount of custom scripts for those functions unsupported by impex; i am running those scripts locally on my machine before to upload the finished database on the server.
Among all the script, i did write a script to transport attachments from fireboard to vbulletin; the script is written trying to understand how the attachment system works from the database interactions, transform the attachement in a BLOB and update it in the filedata table - following impex instructions to store attachments in the DB - and did produced a good amount of empty attachment links on the forum; would like to know what modifications should i do to make the import work properly, thanks.
PHP Code:
<?php
/**
* Retrieve fireboard attachments,
* upload it as BLOB and insert it into related vBulletin threads
* hope this works ^^;
*/
ini_set('max_execution_time', 2400);
require('includes/db_connection.php');
//define the url where to get source attachments
define('BASE_ATT_PATH','http://localhost/vbcms/');
//initialize variables
//configure the attachment type
$attachtypes = array(
array('ext'=>'gif','mimetype'=>'Content-type: image/gif', 'maxW'=>620,'maxH'=>280,'maxsize'=>20000,'th'=>1),
array('ext'=>'GIF','mimetype'=>'Content-type: image/gif', 'maxW'=>620,'maxH'=>280,'maxsize'=>20000,'th'=>1),
array('ext'=>'jpeg','mimetype'=>'Content-type: image/jpeg', 'maxW'=>620,'maxH'=>280,'maxsize'=>20000, 'th'=>1),
array('ext'=>'jpg','mimetype'=>'Content-type: image/jpeg', 'maxW'=>620,'maxH'=>280,'maxsize'=>100000, 'th'=>1),
array('ext'=>'JPG','mimetype'=>'Content-type: image/jpeg', 'maxW'=>620,'maxH'=>280,'maxsize'=>100000, 'th'=>1),
array('ext'=>'jpe','mimetype'=>'Content-type: image/jpeg', 'maxW'=>620,'maxH'=>280,'maxsize'=>20000, 'th'=>1),
array('ext'=>'txt','mimetype'=>'Content-type: plain/text', 'maxW'=>0,'maxH'=>0,'maxsize'=>20000, 'th'=>0),
array('ext'=>'png','mimetype'=>'Content-type: image/png', 'maxW'=>620,'maxH'=>280,'maxsize'=>20000, 'th'=>1),
array('ext'=>'doc','mimetype'=>'Content-type: application/msword', 'maxW'=>0,'maxH'=>0,'maxsize'=>20000, 'th'=>0),
array('ext'=>'htm','mimetype'=>'Content-type: text/html', 'maxW'=>0,'maxH'=>0,'maxsize'=>20000, 'th'=>0),
array('ext'=>'html','mimetype'=>'Content-type: text/html', 'maxW'=>0,'maxH'=>0,'maxsize'=>20000, 'th'=>0),
array('ext'=>'pdf','mimetype'=>'Content-type: application/pdf', 'maxW'=>0,'maxH'=>0,'maxsize'=>20000, 'th'=>0),
array('ext'=>'bmp','mimetype'=>'Content-type: image/bitmap', 'maxW'=>620,'maxH'=>280,'maxsize'=>20000, 'th'=>1),
array('ext'=>'psd','mimetype'=>'Content-type: unknown/unknown', 'maxW'=>0,'maxH'=>0,'maxsize'=>20000, 'th'=>0),
array('ext'=>'zip','mimetype'=>'Content-type: application/zip', 'maxW'=>0,'maxH'=>0,'maxsize'=>100000, 'th'=>0),
array('ext'=>'rar','mimetype'=>'Content-type: application/x-rar', 'maxW'=>0,'maxH'=>0,'maxsize'=>100000, 'th'=>0),
);
//make attachtypes global to be called from inside function
$jfbthreads = array();
$jfbattachs = array();
$vbusers = array();
$vbposts = array();
$vbattachdatas = array();
$vbattach = 'temp/';
$vbattachthumb = 'temp/';
$now = time();
//connect to joomla database
connect_oldwebsite();
//retrieve attachment info from fireboard
$sql0 = "SELECT * FROM jos_fb_attachments";
$res0 = mysql_query($sql0) or die('Invalid query: ' . mysql_error());
while($row0 = mysql_fetch_object($res0)){
array_push($jfbattachs, array( 'mesid'=>$row0->mesid,
'filelocation'=>$row0->filelocation,
));
} //end while
//retrieve fireboard thread info
//just the thread id to compare with importedthreadid in vbulletin
foreach($jfbattachs as $i=>$value){
$sql00 = "SELECT * FROM jos_fb_messages WHERE id = ".$jfbattachs[$i]['mesid']." ";
$res00 = mysql_query($sql00) or die('Invalid query: ' . mysql_error());
if($res00){
while($row00 = mysql_fetch_array($res00) ){
array_push($jfbthreads,array( 'id'=>$row00['id'],
'thread'=>$row00['thread'] ,
'catid'=>$row00['catid'] ,
'name'=>$row00['name'] ,
'userid'=>$row00['userid'] ,
'email'=>$row00['email'] ,
'subject'=>$row00['subject'] ,
'time'=>$row00['time'] ,
'ip'=>$row00['ip'] ,
));
}//end while
} //end if
} //end foreach
//connect to vbulletin
connect_vbcms();
//retrieve required thread information from the thread table
foreach($jfbthreads as $i=>$value){
$sql1 = "SELECT * FROM post WHERE importthreadid = ".$jfbthreads[$i]['thread']." ";
$res1 = mysql_query($sql1) or die('Invalid query: ' . mysql_error());
if($res1){
while($row1 = mysql_fetch_object($res1)){
array_push( $vbposts, array( 'postid'=>$row1->postid ,
'threadid'=>$row1->threadid ,
'parentid'=>$row1->parentid ,
'username'=>$row1->username ,
'userid'=>$row1->userid ,
'title'=>$row1->title ,
'dateline'=>$row1->dateline ,
'pagetext'=>$row1->pagetext ,
'allowsmilie'=>$row1->allowsmilie ,
'showsignature'=>$row1->showsignature ,
'ipaddress'=>$row1->ipaddress ,
'iconid'=>$row1->iconid ,
'visible'=>$row1->visible ,
'attach'=>$row1->attach ,
'infraction'=>$row1->infraction ,
'reportthreadid'=>$row1->reportthreadid ,
'htmlstate'=>$row1->htmlstate ,
'importthreadid'=>$row1->importthreadid ,
'importpostid'=>$row1->importpostid ,
));
} //end while
} //end if
} //end foreach
//time to start populate
//we need to populate several tables: first of all filedata with the file content,
//second the linkers in the table attachment, then attachmentcategoryuser table,
//and at last set a flag in the correct post table and update the counter in the thread table
$querycnt = 0;
foreach ($vbposts as $i=>$value){
foreach($jfbthreads as $j=>$value){
if($jfbthreads[$j]['id'] == $vbposts[$i]['importpostid'] ){
//parse jfbattachs - filter paths to be read from the database properly
foreach($jfbattachs as $k=>$value){
if($jfbattachs[$k]['mesid'] == $jfbthreads[$j]['id']){
if(stristr($jfbattachs[$k]['filelocation'],'fbfiles') != false){
$attachmentpath = str_ireplace('/home/oldwebsite/public_html/', '' , $jfbattachs[$k]['filelocation']);
} //end if
if(stristr($jfbattachs[$k]['filelocation'],'com_fireboard') != false){
$attachmentpath = str_ireplace('/web/htdocs/www.oldwebsite.org/home/components/com_fireboard/', '' , $jfbattachs[$k]['filelocation']);
} //end if
if(stristr($jfbattachs[$k]['filelocation'],'oldwebsite/public') != false){
$attachmentpath = str_ireplace('/home/oldwebsite/public_html/components/com_fireboard/', '' , $jfbattachs[$k]['filelocation']);
} //end if
if(stristr($jfbattachs[$k]['filelocation'],'oldwebsite/public_html/images') != false){
$attachmentpath = str_ireplace('/home/oldwebsite/public_html/', '' , $jfbattachs[$k]['filelocation']);
} //end if
print 'id: '.$vbposts[$i]['postid'].' - oldid: '.$jfbthreads[$j]['id'].' - path - old: '.$jfbattachs[$k]['filelocation'].' - new: '.$attachmentpath.'<br/>';
$fnarray = explode('/',$attachmentpath);
$filename = $fnarray[(count($fnarray))-1];
$fnnoext = substr($filename,0,-4);
$fnext = substr($filename,-3);
$retrievefile = BASE_ATT_PATH . $attachmentpath;
print'retrievefile: '.$retrievefile.'<br/>importedfile: '.$importedfile.'<br/>';
//lets start with extensions, treat it differently from archives
$isImage = 0;
switch($fnext){
case 'gif':
$file1 = imagecreatefromgif($retrievefile);
$isImage = 1;
$imgPar = retrieveImgParams('gif',$attachtypes);
break;
case 'GIF':
$file1 = imagecreatefromgif($retrievefile);
$isImage = 1;
$imgPar = retrieveImgParams('gif',$attachtypes);
break;
case 'jpeg':
$file1 = imagecreatefromjpeg($retrievefile);
$isImage = 1;
$imgPar = retrieveImgParams('jpeg',$attachtypes);
break;
case 'jpg':
$file1 = imagecreatefromjpeg($retrievefile);
$isImage = 1;
$imgPar = retrieveImgParams('jpg',$attachtypes);
break;
case 'JPG':
$file1 = imagecreatefromjpeg($retrievefile);
$isImage = 1;
$imgPar = retrieveImgParams('jpg',$attachtypes);
break;
case 'jpe':
$file1 = imagecreatefromjpeg($retrievefile);
$isImage = 1;
$imgPar = retrieveImgParams('jpe',$attachtypes);
break;
case 'txt':
$file1 = file_get_contents($retrievefile);
$isImage = 0;
$imgPar = retrieveImgParams('txt',$attachtypes);
break;
case 'htm':
$file1 = file_get_contents($retrievefile);
$isImage = 0;
$imgPar = retrieveImgParams('htm',$attachtypes);
break;
case 'html':
$file1 = file_get_contents($retrievefile);
$isImage = 0;
$imgPar = retrieveImgParams('htnl',$attachtypes);
break;
case 'png':
$file1 = imagecreatefrompng($retrievefile);
$isImage = 1;
$imgPar = retrieveImgParams('png',$attachtypes);
break;
case 'doc':
$file1 = file_get_contents($retrievefile);
$isImage = 0;
$imgPar = retrieveImgParams('doc',$attachtypes);
break;
case 'pdf':
$file1 = file_get_contents($retrievefile);
$isImage = 0;
$imgPar = retrieveImgParams('pdf',$attachtypes);
break;
case 'bmp':
$file1 = imagecreatefrombmp($retrievefile);
$isImage = 1;
$imgPar = retrieveImgParams('bmp',$attachtypes);
break;
case 'psd':
$file1 = file_get_contents($retrievefile);
$isImage = 0;
$imgPar = retrieveImgParams('psd',$attachtypes);
break;
case 'zip':
$file1 = file_get_contents($retrievefile);
$isImage = 0;
$imgPar = retrieveImgParams('zip',$attachtypes);
break;
case 'rar':
$file1 = file_get_contents($retrievefile);
$isImage = 0;
$imgPar = retrieveImgParams('rar',$attachtypes);
break;
} //end switch
if ($isImage == 1){
print 'the file '.$retrievefile.' <u>is an image</u> (or so i think)<br/>';
$maxsizeW = $imgPar['maxW'];
$maxsizeH = $imgPar['maxH'];
$hasThumb = $imgPar['th'];
$mimeType = $imgPar['mimetype'];
$imgext = $imgPar['ext'];
//determine sizes
$imgsize = getimagesize($retrievefile);
$img1w = $imgsize[0];
$img1h = $imgsize[1];
$ratio = round($img1w/$img1h,2);
//determine size for avatar
$img1w = ($img1w >$maxsizeW ) ? $maxsizeW : $img1w;
$img1h = ($img1h >$maxsizeH ) ? $maxsizeH : $img1h;
//determine size for thumbnail
$img2w = ($img1w >100 ) ? 100 : $img1w;
$img2h = round($img2w/$ratio); //rounded width/ratio
//create avatar pic
$attach = imagecreatetruecolor($img1w,$img1h);
imagecopyresampled($attach,$file1,0,0,0,0,$img1w,$img1h,$img1w,$img1h);
//create avatar thumbnail
if($hasThumb == 1){
$attachthumb = imagecreatetruecolor($img2w,$img2h);
imagecopyresampled($attachthumb,$file1,0,0,0,0,$img2w,$img2h,$img1w,$img1h);
}// end if
//start buffering
ob_start();
switch($imgext){
case 'gif':
$imgrendered = $vbattach.$fnnoext.'.'.$imgext;
imagegif($attach, $imgrendered);
break;
case 'GIF':
$imgrendered = $vbattach.$fnnoext.'.'.$imgext;
imagegif($attach, $imgrendered);
break;
case 'jpeg':
$imgrendered = $vbattach.$fnnoext.'.'.$imgext;
imagejpeg($attach,$imgrendered,80);
break;
case 'jpg':
$imgrendered = $vbattach.$fnnoext.'.'.$imgext;
imagejpeg($attach,$imgrendered,80);
break;
case 'JPG':
$imgrendered = $vbattach.$fnnoext.'.'.$imgext;
imagejpeg($attach,$imgrendered,80);
break;
case 'jpe':
$imgrendered = $vbattach.$fnnoext.'.'.$imgext;
imagejpeg($attach,$imgrendered,80);
break;
case 'png':
$imgrendered = $vbattach.$fnnoext.'.'.$imgext;
imagepng($attach,$imgrendered);
break;
case 'bmp':
$imgrendered = $vbattach.$fnnoext.'.png';
//store it as png since bmp support in GD is non existent
imagepng($attach,$imgrendered);
break;
} //end switch
ob_end_clean();
if($hasThumb == 1){
ob_start();
//all thumbnails are stored as png
$thumbrendered = $vbattachthumb.$fnnoext."_thu.png";
imagepng($attachthumb,$thumbrendered);
ob_end_clean();
}//end if
} //end if
if($isImage == 0){
$img1w = 0;
$img1h = 0;
$img2w = 0;
$img2h = 0;
$mimeType = $imgPar['mimetype'];
$imgext = $imgPar['ext'];
$hasThumb = 0;
//gotta copy files in temp/
ob_start();
$imgrendered = $vbattach.$fnnoext.'.'.$imgext;
file_put_contents($imgrendered, $file1);
ob_end_clean();
} //end if is Image
// process the file
// start with storing the pic in the filedata table
$myattach = addslashes(file_get_contents($imgrendered));
$myattachfilesize = filesize($imgrendered);
$myattachhash = md5_file($imgrendered);
if($hasThumb == 1){
$myattachthumb = addslashes(file_get_contents($thumbrendered));
$myattachthumbfilesize = filesize($thumbrendered);
} else {
$myattachthumb = null;
$myattachthumbfilesize = 0;
$img2w = 0;
$img2h = 0;
} //end if
//prepare the mysql query for filedata
$sql11 = "INSERT INTO filedata ( filedataid,
userid,
dateline,
thumbnail_dateline,
filedata,
filesize,
filehash,
thumbnail,
thumbnail_filesize,
extension,
refcount,
width,
height,
thumbnail_width,
thumbnail_height
) VALUES (
NULL,
".$vbposts[$i]['userid'].",
".$now.",
".$now.",
'".$myattach."',
".$myattachfilesize.",
'".$myattachhash."',
'".$myattachthumb."',
".$myattachthumbfilesize.",
'".$imgext."',
1,
".$img1w.",
".$img1h.",
".$img2w.",
".$img2h."
)";
@fclose($imgrendered);
if($hasThumb == 1){
@fclose($thumbrendered);
}
$res11 = mysql_query($sql11) or die('Invalid query: ' . mysql_error());
printf("Records inserted: %d\n", mysql_affected_rows());
$filedataindex = mysql_insert_id();
sleep(1);
//sql query for table attachment
//retrieve if there are already records linked to that post
$sql12 = "INSERT INTO attachment ( attachmentid,
contenttypeid,
contentid,
userid,
dateline,
filedataid,
state,
counter,
posthash,
filename,
caption,
reportthreadid,
settings,
displayorder,
importattachmentid
) VALUES (
NULL,
1,
".$vbposts[$i]['postid'].",
".$vbposts[$i]['userid'].",
".$now.",
".$filedataindex.",
'visible',
1,
'',
'".$fnnoext.'.'.$imgext."',
NULL,
0,
NULL,
0,
".$jfbthreads[$j]['id']."
)";
print 'sql12 ='.$sql12.'<br/>';
$res12 = mysql_query($sql12) or die('Invalid query: ' . mysql_error());
printf("Records inserted: %d\n", mysql_affected_rows());
$attachmentid = mysql_insert_id();
// sql query for attachmentcategoryuser
$sql13 = "INSERT INTO attachmentcategoryuser ( filedataid,
userid,
categoryid,
filename,
dateline
) VALUES (
".$filedataindex.",
".$vbposts[$i]['userid'].",
0,
'".$fnnoext.'.'.$imgext."',
".$now."
)";
print 'sql13 ='.$sql13.'<br/>';
$res13 = mysql_query($sql13) or die('Invalid query: ' . mysql_error());
printf("Records inserted: %d\n", mysql_affected_rows());
//and at last fix the post table adding there is an attach
//need to insert the BBCODE [ATTACH]attachnumber[/ATTACH]
$sql14 = "UPDATE post SET attach = 1, pagetext = CONCAT(pagetext,'[ATTACH]".$attachmentid."[/ATTACH]<br/>') WHERE postid = ".$vbposts[$i]['postid']." ";
print 'sql14 ='.$sql14.'<br/>';
$res14 = mysql_query($sql14) or die('Invalid query: ' . mysql_error());
printf("Records inserted: %d\n", mysql_affected_rows());
if($querycnt<50){
$querycnt++;
} else {
print 'sleep<br/>';
sleep(5);
$querycnt = 0;
}
} //end if
} //end foreach
} //end if
} //end foreach
} //end foreach
//imagecreatefrombmp function from php.net since GD does not support bmp directly
function imagecreatefrombmp($p_sFile){
// Load the image into a string
$file = fopen($p_sFile,"rb");
$read = fread($file,10);
while(!feof($file)&&($read<>""))
$read .= fread($file,1024);
$temp = unpack("H*",$read);
$hex = $temp[1];
$header = substr($hex,0,108);
// Process the header
// Structure: http://www.fastgraph.com/help/bmp_header_format.html
if (substr($header,0,4)=="424d")
{
// Cut it in parts of 2 bytes
$header_parts = str_split($header,2);
// Get the width 4 bytes
$width = hexdec($header_parts[19].$header_parts[18]);
// Get the height 4 bytes
$height = hexdec($header_parts[23].$header_parts[22]);
// Unset the header params
unset($header_parts);
}
// Define starting X and Y
$x = 0;
$y = 1;
// Create newimage
$image = imagecreatetruecolor($width,$height);
// Grab the body from the image
$body = substr($hex,108);
// Calculate if padding at the end-line is needed
// Divided by two to keep overview.
// 1 byte = 2 HEX-chars
$body_size = (strlen($body)/2);
$header_size = ($width*$height);
// Use end-line padding? Only when needed
$usePadding = ($body_size>($header_size*3)+4);
// Using a for-loop with index-calculation instaid of str_split to avoid large memory consumption
// Calculate the next DWORD-position in the body
for ($i=0;$i<$body_size;$i+=3)
{
// Calculate line-ending and padding
if ($x>=$width)
{
// If padding needed, ignore image-padding
// Shift i to the ending of the current 32-bit-block
if ($usePadding)
$i += $width%4;
// Reset horizontal position
$x = 0;
// Raise the height-position (bottom-up)
$y++;
// Reached the image-height? Break the for-loop
if ($y>$height)
break;
}
// Calculation of the RGB-pixel (defined as BGR in image-data)
// Define $i_pos as absolute position in the body
$i_pos = $i*2;
$r = hexdec($body[$i_pos+4].$body[$i_pos+5]);
$g = hexdec($body[$i_pos+2].$body[$i_pos+3]);
$b = hexdec($body[$i_pos].$body[$i_pos+1]);
// Calculate and draw the pixel
$color = imagecolorallocate($image,$r,$g,$b);
imagesetpixel($image,$x,$height-$y,$color);
// Raise the horizontal position
$x++;
}
// Unset the body / free the memory
unset($body);
// Return image-object
return $image;
} //end function
//function that parse the attachtypes array and return the values for the file format
function retrieveImgParams($type,$attachtypes){
// global $attacthypes;
// print_r($attachtypes);
print 'type = '.$type.'<br/>';
foreach ($attachtypes as $i=>$value){
if($attachtypes[$i]['ext'] == $type){
return array( 'ext'=> $attachtypes[$i]['ext'],
'mimetype'=> $attachtypes[$i]['mimetype'],
'maxW'=> $attachtypes[$i]['maxW'],
'maxH'=> $attachtypes[$i]['maxH'],
'maxsize'=> $attachtypes[$i]['maxsize'],
'th'=> $attachtypes[$i]['th'],
);
} //end if
}//end foreach
} //end function
?>
More Information