1 Topic by manixs2013 (edited by manixs2013 2018-03-11 10:22:59)

  • Registered: 2017-05-15
  • Posts: 722

Topic: Maintenance of Link Picture

Hello MVD!

Good Day!.

I have a question on how to maintain the linked images in the project. First on how to delete a not linked image to avoid bloated files. It is very difficult to find what are the connected images or files. Is there any way or script to delete the unconnected images?

Thank You Much!

Manixs

2 Reply by mathmathou (edited by mathmathou 2018-03-11 21:12:56)

  • Registered: 2015-02-24
  • Posts: 446

Re: Maintenance of Link Picture

Hello manixs2013,


Are you talking about deleting the files on the drive that are not "linked" into the database, or deleting links in database that do not exist as files where they are supposed to be ?


If you used the File field type or the Image field type, you will notice that MVD creates to fields in database : a blob one and a text one. The text one will contain the full path to the file/image and can be use to check if the said file or image is still where it is supposed to be.


Let's say I have a simple table like
Asset with asset_name as TEXT and asset_image as IMAGE (I have in fact two fields in database for the image, a BLOB and a TEXT)


On a button click here is whar you could do :

procedure Form1_Button2_OnClick (Sender: string; var Cancel: boolean);
var
    PathList : TDataSet;
    ImgURL : String;
begin
    //Build a dataset with all image path found in db
     SQLQuery('SELECT asset_image_filename FROM asset',PathList);
        //Loop through all the results you got from the query one by one
        while not PathList.Eof do
            begin
                //for ease of reading assign the result to a string variable
                ImgURL := PathList.FieldByName('asset_image_filename').AsString;
                    if ImgURL <> '' then //if the path is not empty
                        begin
                            //check if the file exists and do what you want accordingly
                            if FileExists(ImgURL) then ShowMessage('File OK') else ShowMessage('Missing File');
                        end;
                //Don't forget this instruction to go to next result set, or you procedure will get stuck
                PathList.Next;
            end;
        //Free the dataset after use
        PathList.Free;
end;

Here I just display a message to inform the user the the file exists or not, but you could very well delete the database info if the file is not present on the disk.


To do so, I would add an important info to the query : the id of the record.
Based on this, I just update the database table where needed.


Without the comments, this could look like :

procedure Form1_Button2_OnClick (Sender: string; var Cancel: boolean);
var
    PathList : TDataSet;
    ImgURL, ImgID : String;
begin
    SQLQuery('SELECT id, asset_image_filename FROM asset',PathList);
        while not PathList.Eof do
            begin
                ImgURL := PathList.FieldByName('asset_image_filename').AsString;
                    if ImgURL <> '' then
                        begin
                            if FileExists(ImgURL) then ShowMessage('File OK') else
                                begin
                                    ImgID := PathList.FieldByName('id').AsString;
                                    SQLExecute('UPDATE asset SET asset_image = NULL, asset_image_filename = NULL WHERE asset.id ="'+ImgID+'"');
                                end;
                        end;
                PathList.Next;
            end;
        PathList.Free;
end;

This is not the prettiest code I've written (I just arrived at the office) and there is no security whatsoever (not try catch, not user confirmation...) but I hope it will get you started. This is just to clean database from path that no more lead to image on the drive.

I also cleaned the BLOB field, which should not be needed if you used the "link file option".


From time to time, if you delete a lot of info from you database, a simple

SQLExecute('vacuum');

can reclaim a lot of lost space in your database smile


Have a good day


Cheers


Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • Registered: 2017-05-15
  • Posts: 722

Re: Maintenance of Link Picture

Are you talking about deleting the files on the drive that are not "linked" into the database.  Yes this is what i needed Sir.   Thank you so much for you kind time to reply my query.

Thanks!