Cleanup

fix-parent-path.py

# this script fixes `parent_path` issues on the stock.location table
# the parent_path field should store a slash-delimited list of parent
# locations.
#
# e.g. if we have "Physical Locations (1) / WH (2) / Stock (3)", where
# (id), then the parent_path should be:
# - 1/2/3/
#
# USAGE:
# run this script in an Odoo shell (normally /opt/odoo-scripts/odoo-shell.sh
# it will dump a list of SQL UPDATE queries that needs to be run on Postgres

# choose a location to export the SQL UPDATE statements to
out_location = "/tmp/fix-parent-path.sql"

# the script is idempotent, so safe to run on all records
locations = env['stock.location'].search([])

# generate the parent path. this brute force approach only takes ~2s, so no
# point in optimising
def get_ppath(loc):
    if loc.location_id:
        return f"{get_ppath(loc.location_id)}{loc.id}/"
    else:
        return f"{loc.id}/"

# grab a list of SQL UPDATE statements
sql_updates = '\n'.join([f"UPDATE stock_location SET parent_path = '{get_ppath(loc)}' WHERE id = {loc.id};" for loc in locations])


# Option #1: dump the SQL to a file (for large stock_location tables)
with open(out_location, "w") as fd:
    fd.write(sql_updates)
    
# Option #2: print the SQL to stdout
print(sql_updates)