|
#! /bin/sh
|
|
#
|
|
# If delete a Redmine ticket by mistake,
|
|
# output an SQL statement for restoring the ticket from the backup environment.
|
|
|
|
ISSUE_NO=$1
|
|
|
|
# copy from /var/lib/redmine/config/database.yml (without double-quote)
|
|
|
|
DATABASE=redmine
|
|
username=redmine
|
|
password=password1
|
|
|
|
# Create SQL statement related to specified issue number
|
|
|
|
mysqldump -u ${username} -p${password} ${DATABASE} issues --where "id=${ISSUE_NO}" --skip-add-drop-table --no-create-info=TRUE --skip-disable-keys --skip-dump-date --skip-comments --extended-insert=FALSE | grep "INSERT" > INSERT_${ISSUE_NO}.sql
|
|
mysqldump -u ${username} -p${password} ${DATABASE} custom_values --where "customized_id=${ISSUE_NO}" --skip-add-drop-table --no-create-info=TRUE --skip-disable-keys --skip-dump-date --skip-comments --extended-insert=FALSE | grep "INSERT" >> INSERT_${ISSUE_NO}.sql
|
|
|
|
mysqldump -u ${username} -p${password} ${DATABASE} journals --where "journalized_id=${ISSUE_NO}" --skip-add-drop-table --no-create-info=TRUE --skip-disable-keys --skip-dump-date --skip-comments --extended-insert=FALSE | grep "INSERT" >> INSERT_${ISSUE_NO}.sql
|
|
mysqldump -u ${username} -p${password} ${DATABASE} journal_details --where "journal_id in (select id from journals where journalized_id=${ISSUE_NO})" --skip-add-drop-table --no-create-info=TRUE --lock-tables=false --skip-disable-keys --skip-dump-date --skip-comments --extended-insert=FALSE | grep "INSERT" >> INSERT_${ISSUE_NO}.sql
|
|
|
|
mysqldump -u ${username} -p${password} ${DATABASE} attachments --where "container_id=${ISSUE_NO}" --skip-add-drop-table --no-create-info=TRUE --skip-disable-keys --skip-dump-date --skip-comments --extended-insert=FALSE | grep "INSERT" >> INSERT_${ISSUE_NO}.sql
|
|
mysqldump -u ${username} -p${password} ${DATABASE} watchers --where "watchable_id=${ISSUE_NO}" --skip-add-drop-table --no-create-info=TRUE --skip-disable-keys --skip-dump-date --skip-comments --extended-insert=FALSE | grep "INSERT" >> INSERT_${ISSUE_NO}.sql
|
|
mysqldump -u ${username} -p${password} ${DATABASE} time_entries --where "issue_id=${ISSUE_NO}" --skip-add-drop-table --no-create-info=TRUE --skip-disable-keys --skip-dump-date --skip-comments --extended-insert=FALSE | grep "INSERT" >> INSERT_${ISSUE_NO}.sql
|
|
|
|
mysqldump -u ${username} -p${password} ${DATABASE} issue_relations --where "issue_from_id=${ISSUE_NO}" --skip-add-drop-table --no-create-info=TRUE --skip-disable-keys --skip-dump-date --skip-comments --extended-insert=FALSE | grep "INSERT" >> INSERT_${ISSUE_NO}.sql
|
|
mysqldump -u ${username} -p${password} ${DATABASE} issue_relations --where "issue_to_id=${ISSUE_NO}" --skip-add-drop-table --no-create-info=TRUE --skip-disable-keys --skip-dump-date --skip-comments --extended-insert=FALSE | grep "INSERT" >> INSERT_${ISSUE_NO}.sql
|
|
|
|
mysqldump -u ${username} -p${password} ${DATABASE} changesets_issues --where "issue_id=${ISSUE_NO}" --skip-add-drop-table --no-create-info=TRUE --skip-disable-keys --skip-dump-date --skip-comments --extended-insert=FALSE | grep "INSERT" >> INSERT_${ISSUE_NO}.sql
|
|
|
|
# check if INSERT-sql file size >0
|
|
|
|
if [ -s INSERT_${ISSUE_NO}.sql ]; then
|
|
|
|
echo "copy and execute sql statement on restore redmine server INSERT_${ISSUE_NO}.sql"
|
|
|
|
echo "and copy attachment files below"
|
|
mysql -u ${username} -p${password} ${DATABASE} -e "select disk_filename from attachments where container_id=${ISSUE_NO}"
|
|
else
|
|
echo "specified issue no ${ISSUE_NO} not existed"
|
|
fi
|