Cleanup Metadata Table #408

New issue

Closed

opened 2024-03-24 10:15:37 +00:00 by p3k · 1 comment

p3k commented

2024-03-24 10:15:37 +00:00

(Migrated from github.com)

There are some dangling entries in the metadata table that can be removed.

💡 Run this in the MariaDB container (3010).

#!/usr/bin/env sh

database=antville

sql() {
  mysql "$database" -Nrs -e "$@"
}

sqldump() {
  mysqldump "$database" -nt --single-transaction "$@"
}

metadata_types="$(sql 'select unique(parent_type) from metadata' | tr \\n ' ')"
rollback_file="rollback-cleaned-metadata.sql"

touch "$rollback_file"
printf 'Orphaned rows in metadata of\n'

# Do *not* quote metadata_types in the next line!
for type in $metadata_types; do
  if [ "$type" = 'Story' ]; then
    table=content
  elif [ "$type" = 'User' ]; then
    table=account
  else
    table=$(echo "$type" | tr '[:upper:]' '[:lower:]')
  fi

  where_clause="parent_type = '$type' and parent_id not in (select id from $table)"
  count=$(sql "select count(*) from metadata where $where_clause")
  printf ' * %s: %s\n' "$type" "$count"

  if [ "$count" -lt 1 ]; then
    continue;
  fi

  read -p '   Delete? y|N ' continue

  if [ "$continue" != y ]; then
    printf '   OK, rows remain untouched.\n'
    continue
  fi

  printf '   Deleting...\n'
  # Saving the records that are going to be removed (rollback)
  sqldump metadata --where="$where_clause" >> "$rollback_file"
  sql "delete from metadata where $where_clause"
done

There are some dangling entries in the metadata table that can be removed. > :bulb: Run this in the MariaDB container (3010). ```shell #!/usr/bin/env sh database=antville sql() { mysql "$database" -Nrs -e "$@" } sqldump() { mysqldump "$database" -nt --single-transaction "$@" } metadata_types="$(sql 'select unique(parent_type) from metadata' | tr \\n ' ')" rollback_file="rollback-cleaned-metadata.sql" touch "$rollback_file" printf 'Orphaned rows in metadata of\n' # Do *not* quote metadata_types in the next line! for type in $metadata_types; do if [ "$type" = 'Story' ]; then table=content elif [ "$type" = 'User' ]; then table=account else table=$(echo "$type" | tr '[:upper:]' '[:lower:]') fi where_clause="parent_type = '$type' and parent_id not in (select id from $table)" count=$(sql "select count(*) from metadata where $where_clause") printf ' * %s: %s\n' "$type" "$count" if [ "$count" -lt 1 ]; then continue; fi read -p ' Delete? y|N ' continue if [ "$continue" != y ]; then printf ' OK, rows remain untouched.\n' continue fi printf ' Deleting...\n' # Saving the records that are going to be removed (rollback) sqldump metadata --where="$where_clause" >> "$rollback_file" sql "delete from metadata where $where_clause" done ```

p3k commented

2024-03-24 10:16:00 +00:00

(Migrated from github.com)

Script ran successfully on Antville.org – closing.