Retroactively Use Automatic Path Aliases

This is a small utility project to fix a problem that occurs when you do not immediately set up path alias patterns for content.

Making a module proved not worth the effort. Here are the current SQL queries to run, to default old nodes and users with no aliases set yet to be ready for automatic aliases (which you can then create at Bulk Generate, /admin/config/search/path/update_bulk):

UPDATE node AS n INNER JOIN key_value AS kv ON n.nid=kv.name LEFT JOIN path_alias AS pa ON CONCAT('/node/', n.nid)=pa.path SET kv.value='i:1;' WHERE kv.collection='pathauto_state.node' AND kv.value='i:0;' AND pa.alias IS NULL;

UPDATE users AS u
INNER JOIN key_value AS kv ON u.uid=kv.name LEFT JOIN path_alias AS pa ON CONCAT('/user/', u.uid)=pa.path SET kv.value='i:1;' WHERE kv.collection='pathauto_state.user' AND kv.value='i:0;' and pa.alias is NULL;

Scroll down for the screed about why this is unreasonably difficult to do in a module.

UPDATE: Let this attempt at a module serve as a testament to multiple problems with modern Drupal.

1. The motivation for this module, the fact that nonsensically nodes created before any automatic path aliases are configured for that content type will be marked explicitly as choosing manual, not automatic, aliases, when in reality they have chosen neither and should get the automatic aliases when configured.
2. The fact that there's no easy, straightforward, and readily available way to fix the above situation.
3. This pathauto value is stored in a horrific way— in the key_value table, in a blob column, as JSON-encoded value complete with semicolon, rather than in its own table.
4. There seems not to be any way for entity query to access this pathauto value nor any of the path properties.
5. There's no reasonable (non-frightening) lookup from node to its path.
6. The heavyhanded approach to preventing multiple queries from running in a query statement throws an error even when the semicolon is safely within a quoted value.
7. Using placeholders in non-SELECT query statements does not work – "SQLSTATE[HY093]: Invalid parameter number: no parameters were bound:" – (not with the passed in array, and not with PDO functions like bind_param() or bindValue(), and if there is a way, it is not documented.
8. Doing joins with update is not possible (even for conditional statements) or simply undocumented.

All of which means that something that i shouldn't have to do at all, but can do with a single SQL statement, cannot be done in a module, or rather, cannot be done by running the single reasonably performant SQL statement and it's 5am i've been up all night a police force a few miles from me killed a young man 14 hours ago and this is all you're getting, Drupal world, this screed and a Frankenstein's monster of an MariaDB/MySQL update query; take it with my mixed blessings and curses.

UPDATE node AS n INNER JOIN key_value AS kv ON n.nid=kv.name LEFT JOIN path_alias AS pa ON CONCAT('/node/', n.nid)=pa.path SET kv.value='i:1;' WHERE kv.collection='pathauto_state.node' AND kv.value='i:0;' AND pa.alias IS NULL;

That monstrosity will update every piece of content that does not have any path alias and yet is marked as using manual not automatic aliases by pathauto, to instead be willing to use automatic aliases.

After running this query Bulk Generate in the Pathauto forms will work as you would have bloody well expected in the first place.