Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Converting Database Stored Attachments to the File System
<?php
/*
* Notes:
* This script will convert attachments from being stored on the DB to the file system
* NOTE: This script removes the atachments from your database. Before running this script make a backup. For real.
*/
//No time limit
set_time_limit(0);
// Show errors
error_reporting(E_ALL);
ini_set('display_errors', 1);
//PATH TO YOUR HELPSPOT CONFIG FILE
include('config.php');
//PATH TO HELPSPOTS ADODB FILES
include(cBASEPATH.'/helpspot/adodb/adodb.inc.php');
include(cBASEPATH.'/helpspot/lib/util.lib.php');
include(cBASEPATH.'/helpspot/lib/api.lib.php'); //has functions used in hsInitSettings
//CREATE DB CONNECTION
hsInitDB();
hsInitSettings();
// Number Files Left
$numQuery = $GLOBALS['DB']->GetOne('SELECT COUNT(*) as remaining_files FROM HS_Documents WHERE blobFile IS NOT NULL;');
echo "Queries Left: $numQuery\n";
//FIND ATTACHMENTS
$query = $GLOBALS['DB']->Execute('SELECT HS_Request_History.dtGMTChange, HS_Documents.xDocumentId,HS_Documents.sFilename,HS_Documents_Location.sFileLocation
FROM HS_Request_History,HS_Documents
LEFT OUTER JOIN HS_Documents_Location ON HS_Documents.xDocumentId = HS_Documents_Location.xDocumentId
WHERE HS_Request_History.xDocumentId <> 0 AND HS_Request_History.xDocumentId = HS_Documents.xDocumentId
AND HS_Documents_Location.sFileLocation IS NULL');
//LOOP OVER AND CONVERT TO FILES
while($file = $query->FetchRow()){
//Check that there isn't already a file location
if(hs_empty($file['sFileLocation'])){
//Setup time and directories
$year = date('Y', $file['dtGMTChange']);
$month = date('n', $file['dtGMTChange']);
$day = date('j', $file['dtGMTChange']);
// Create path to directory location if it doesn't exist
if(!is_dir(cHD_ATTACHMENT_LOCATION_PATH .'/'. $year .'/'. $month .'/'. $day)){
if(!is_dir(cHD_ATTACHMENT_LOCATION_PATH .'/'. $year)) @mkdir(cHD_ATTACHMENT_LOCATION_PATH .'/'. $year); //make year folder
if(!is_dir(cHD_ATTACHMENT_LOCATION_PATH .'/'. $year .'/'. $month)) @mkdir(cHD_ATTACHMENT_LOCATION_PATH .'/'. $year .'/'. $month); //make month folder
//Don't need is_dir check here since it's done first so we know it isn't
@mkdir(cHD_ATTACHMENT_LOCATION_PATH .'/'. $year .'/'. $month .'/'. $day); //make day folder
}
//hashed file name to prevent "bad guys" from finding it easy should someone put their files in the web root path.
$ext = explode('.', $file['sFilename']);
$id = count($ext)-1;
$extension = ($ext[$id] ? $ext[$id] : 'txt');
//Use uniqid() in hash to ensure it's unique
$file_path = '/'. $year .'/'. $month .'/'. $day .'/'. md5($file['sFilename'] . uniqid('helpspot')) .'.'. $extension;
//Get the body of the file
$blob = $GLOBALS['DB']->GetOne('SELECT blobFile FROM HS_Documents WHERE xDocumentId = '.$file['xDocumentId']);
// Try and write files to disk
$file_write_worked = writeFile(cHD_ATTACHMENT_LOCATION_PATH . $file_path, $blob);
// Add document to document table
if($file_write_worked){
//add path to DB
$doclocadd = $GLOBALS['DB']->Execute( 'INSERT INTO HS_Documents_Location(xDocumentId,sFileLocation) VALUES(?,?)',
array($file['xDocumentId'], $file_path) );
//remove blob text
$blob = $GLOBALS['DB']->Execute('UPDATE HS_Documents SET blobFile=null WHERE xDocumentId=?', array($file['xDocumentId']));
//If the update fails exit
if(!$blob) die('Database update failed on xDocumentId: '.$file['xDocumentId'].', try restarting script');
}else{
echo 'Cannot write to path: '.cHD_ATTACHMENT_LOCATION_PATH . $file_path."\n";exit;
}
}
}
@fideloper

This comment has been minimized.

Copy link
Owner Author

commented Dec 2, 2016

If you have too many attachments (causing the SELECT query to choke before it starts processing files to the file system) you can add a LIMIT to the SELECT query. You then can run this script multiple times (although that may be needed in any case).

This script is safe to run multiple times, repeatedly.

Change:

$query = $GLOBALS['DB']->Execute('SELECT HS_Request_History.dtGMTChange, HS_Documents.xDocumentId,HS_Documents.sFilename,HS_Documents_Location.sFileLocation
                                  FROM HS_Request_History,HS_Documents
                                    LEFT OUTER JOIN HS_Documents_Location ON HS_Documents.xDocumentId = HS_Documents_Location.xDocumentId
                                  WHERE HS_Request_History.xDocumentId <> 0 AND HS_Request_History.xDocumentId = HS_Documents.xDocumentId 
                                            AND HS_Documents_Location.sFileLocation IS NULL');

To:

$query = $GLOBALS['DB']->Execute('SELECT HS_Request_History.dtGMTChange, HS_Documents.xDocumentId,HS_Documents.sFilename,HS_Documents_Location.sFileLocation
                                  FROM HS_Request_History,HS_Documents
                                    LEFT OUTER JOIN HS_Documents_Location ON HS_Documents.xDocumentId = HS_Documents_Location.xDocumentId
                                  WHERE HS_Request_History.xDocumentId <> 0 AND HS_Request_History.xDocumentId = HS_Documents.xDocumentId 
                                            AND HS_Documents_Location.sFileLocation IS NULL LIMIT 10000');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.