Mass deletion of gallery images

  • Betroffene Version
    WoltLab Suite 3.0

    Is there a way to prune gallery images?

    I want to delete all the images from my gallery that are over 5 years old, but only from members who haven't logged in for a year. I have thousands of them.

    How can this be done?

  • The database query won't delete the images in the filesyste. It will only remove the images from the database and therefor from the Woltlab Gallery.

    You can split it in two queries.

    First query collect the filename (in filesystem) of images matching your criteria.

    Second query delecte images from database.

    Thrid step... based on the list from 1st query delete images in filesystem via script.

  • As far as I can see there's no way of doing this in the ACP - which is what I was hoping for.

    I'm not proficient enough to work out the correct SQL queries - and looking at the folder structure of the gallery, wouldn't know which folders to delete and which to keep as the names don't relate to the images or the users.

  • Well, I'm in a hurry at the moment. You can try this SQL first... it won't delete anything. It is basicly the "1st step" to build der fileliste for the scripted deletion.

    SQL
    SELECT gallery1_image.username, imageID, CONCAT ('./gallery/userImages/', SUBSTRING(fileHash,1,2), '/', imageID, '-', filehash, '.png') AS imagePath, CONCAT ('./gallery/userImages/', SUBSTRING(fileHash,1,2), '/', imageID, '-', filehash, '-small.png') AS imagePathSmall, CONCAT ('./gallery/userImages/', SUBSTRING(fileHash,1,2), '/', imageID, '-', filehash, '-tiny.png') AS imagePathTiny, CONCAT ('./gallery/userImages/', SUBSTRING(fileHash,1,2), '/', imageID, '-', filehash, '-medium.png') AS imagePathMedium, CONCAT ('./gallery/userImages/', SUBSTRING(fileHash,1,2), '/', imageID, '-', filehash, '-large.png') AS imagePathLarge, FROM_UNIXTIME(uploadTime)
    FROM gallery1_image LEFT JOIN wcf1_user ON gallery1_image.userID=wcf1_user.userID
    WHERE (gallery1_image.userID IS NULL) OR (wcf1_user.lastActivityTime<UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH)) AND gallery1_image.uploadTime<UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 60 MONTH)));

    This will create a list with: username, imageID, uploadTime and "all the relative file paths from the WSCs main directory as starting point".

    Criteria will be... user id deleted in WSC or user is inactive for 12 month and file is older than 60 month.

    In my test system it creates something like this (with changed parameter 1 month user inactivity and 6 month image age).

    Code
    | username | imageID | imagePath                                | imagePathSmall                                 | imagePathTiny                                 | imagePathMedium                                 | imagePathLarge                                 | FROM_UNIXTIME(uploadTime) |
    +----------+---------+------------------------------------------+------------------------------------------------+-----------------------------------------------+-------------------------------------------------+------------------------------------------------+---------------------------+
    | xxxxxx   |     489 | ./gallery/userImages/2b/489-2bf8193d.png | ./gallery/userImages/2b/489-2bf8193d-small.png | ./gallery/userImages/2b/489-2bf8193d-tiny.png | ./gallery/userImages/2b/489-2bf8193d-medium.png | ./gallery/userImages/2b/489-2bf8193d-large.png | 2015-07-07 18:23:40       |
    | xxxxxx   |     498 | ./gallery/userImages/89/498-89516ccd.png | ./gallery/userImages/89/498-89516ccd-small.png | ./gallery/userImages/89/498-89516ccd-tiny.png | ./gallery/userImages/89/498-89516ccd-medium.png | ./gallery/userImages/89/498-89516ccd-large.png | 2015-08-09 22:36:00       |
    +----------+---------+------------------------------------------+------------------------------------------------+-----------------------------------------------+-------------------------------------------------+------------------------------------------------+---------------------------+

    The deleting part will then be only a slight change in the SQL... basicly switch from "SELECT" to "DELETE". ;)

    P.S.: You can of course simplyfy the SQL, but than the bash script must complete the file paths... the simpler SQL would be:

    SQL
    SELECT gallery1_image.username, imageID, CONCAT ('./gallery/userImages/', SUBSTRING(fileHash,1,2), '/', imageID, '-', filehash) AS imagePath, FROM_UNIXTIME(uploadTime)
    FROM gallery1_image LEFT JOIN wcf1_user ON gallery1_image.userID=wcf1_user.userID
    WHERE (gallery1_image.userID IS NULL) OR (wcf1_user.lastActivityTime<UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MONTH)) AND gallery1_image.uploadTime<UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 5 MONTH)));

    Output will then look like:

    Code
    | xxxxxx   |     498 | ./gallery/userImages/89/498-89516ccd | 2015-08-09 22:36:00       |

    Einmal editiert, zuletzt von Barungar (28. August 2018 um 09:44)

Jetzt mitmachen!

Sie haben noch kein Benutzerkonto auf unserer Seite? Registrieren Sie sich kostenlos und nehmen Sie an unserer Community teil!