Moving Images From Database to FileSystem

Contains tips for configurators working with Aware IM
Post Reply
customaware
Posts: 2391
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Moving Images From Database to FileSystem

Post by customaware »

Just thought I'd post this in case anyone is faced with the task of wanting to move a lot of Pictures from the Database to the FileSystem.

While this is rather trivial.... You are generally only ever going to want to do it on a "live" server....and, Nothing is trivial on a "live" server.

I have an old system that uses MS-SQL Express (10Gb Limit) and was built before there was the option of storing Pictures in the FileSystem.
But the users have recently changed the way they use the system and are uploading way more photos (about 2 Gb per month) so needed to
resolve this.

So, while there may be an easier way (there usually always is) this is how I accomplished it and it works fine.

I decided that the actual filename of each photo was not important. What was important was that once the image is moved to the Filesystem that I
had a way on knowing what record in the DB the picture was associated with. So, I simply use the Record ID as the Filename of the moved image.

1. Backup the Server.
2. Create a Folder in the Filesystem where you want the Images to be stored. We will us C:\MYPICTURES
3. In the BO where your Picture Attribute is (lets assume MyPictureBO.MyPicture)... create a new Attribute called MyPicturePath (TEXT 200 chars)
4. Now run a process similar to the following...

Code: Select all

FIND ALL MyPictureBO IN BATCHES OF 1
RENAME DOCUMENT MyPictureBO.MyPicture TO AS_STRING(MyPictureBO.ID)+'.'+FILE_EXTENSION(MyPictureBO.MyPicture) 
EXPORT DOCUMENT MyPictureBO.MyPicture TO FOLDER 'C:\MYPICTURES' 
MyPictureBO.MyPicturePath='C:\MYPICTURES\'+AS_STRING(MyPictureBO.ID)+'.'+FILE_EXTENSION(MyPictureBO.MyPicture) 
Now we have a correctly named version of each image in the correct location in the Filesystem and we have a MyPicturePath that points to it. But Aware still thinks it is in the Database.

5. In you MyPictureBO.... change the MyPicture Attribute to use FileSystem rather than Database and set the Attribute for the Path to MyPictureBO.MyPicturePath.

Now when you run the app... Aware will look for the image in filesystem and of course will find it.

But we still also have the image data in the Database taking up space....

6. Go to the Database and run this Query...

Code: Select all

UPDATE MyPictureBO
SET MyPictureBO_DOCDATA = NULL;
7. Last step is to ensure the FilePath is set properly for each new Picture you are adding.... So, in the BO Rules of MyPictureBO add the following Rule.

Code: Select all

RENAME DOCUMENT MyPictureBO.MyPicture TO AS_STRING(MyPictureBO.ID)+'.'+FILE_EXTENSION(MyPictureBO.MyPicture) 
MyPictureBO.MyPicturePath='C:\MYPICTURES\'+AS_STRING(MyPictureBO.ID)+'.'+FILE_EXTENSION(MyPictureBO.MyPicture) 
Done.
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: Moving Images From Database to FileSystem

Post by tford »

I love your road maps, Mark!

Hoping never to have to use it though. :D
Tom - V8.8 build 3137 - MySql / PostGres
Post Reply