A #shell #script : clean the eZ Publish database

super-flatten.sh

#!/bin/bash



# UTILS
usage() {
    echo ""
    echo "SUPER FLATTEN"
    echo "-------------"
    echo "This script clean your eZ Publish database by removing unused entries"
    echo ""
    echo "Usage:    ${0}  <database_name>  <database_user>  <database_password>"
    echo ""
}

mysql_execute() {
	mysql -NB -u${DATABASE_USER} -p${DATABASE_PASSWORD} -hlocalhost -D${DATABASE_NAME} -e"$1"
}



# ARGUMENT CONTROL
if [ $# -ne 3 ]
then
    usage
    exit
fi



# VARIABLES 
DATABASE_NAME=${1}
DATABASE_USER=${2}
DATABASE_PASSWORD=${3}



# GET CON 
TIME_BEFORE="$(date +%s)"

echo "Clean contentobject"
mysql_execute "DELETE FROM ezcontentobject           WHERE id NOT IN (SELECT contentobject_id FROM ezcontentobject_tree);"

echo "Clean contentobject tree node"
mysql_execute "DELETE FROM ezcontentobject_tree      WHERE contentobject_id NOT IN (SELECT id FROM ezcontentobject);"
mysql_execute "DELETE FROM eznode_assignment         WHERE contentobject_id NOT IN (SELECT id FROM ezcontentobject);"

echo "Clean contentobject attribute (could be long)"
mysql_execute "DELETE FROM ezcontentobject_attribute WHERE contentobject_id NOT IN (SELECT id FROM ezcontentobject);"

echo "Clean contentobject version"
mysql_execute "DELETE FROM ezcontentobject_version   WHERE contentobject_id NOT IN (SELECT id FROM ezcontentobject);"

echo "Clean contentobject name"
mysql_execute "DELETE FROM ezcontentobject_name      WHERE contentobject_id NOT IN (SELECT id FROM ezcontentobject);"

echo "Clean contentobject name"
mysql_execute "DELETE FROM ezcontentobject_name      WHERE contentobject_id NOT IN (SELECT id FROM ezcontentobject);"

echo "Clean contentobject link"
mysql_execute "DELETE FROM ezcobj_state_link         WHERE contentobject_id      NOT IN (SELECT id FROM ezcontentobject);"
mysql_execute "DELETE FROM ezcontentobject_link      WHERE from_contentobject_id NOT IN (SELECT id FROM ezcontentobject);"
mysql_execute "DELETE FROM ezcontentobject_link      WHERE to_contentobject_id   NOT IN (SELECT id FROM ezcontentobject);"

echo "Clean contentobject trash"
mysql_execute "DELETE FROM ezcontentobject_trash     WHERE contentobject_id NOT IN (SELECT id FROM ezcontentobject);"

TIME_AFTER="$(date +%s)"
ELAPSED="$(expr $TIME_AFTER - $TIME_BEFORE)"
echo ""
echo "Database cleaned in $ELAPSED seconds"