That line works like a charm.
But I found a new one:
96 UPDATE submission_files sf SET genre_id = g.genre_id FROM genres g, submissions s, article_galleys_migration agm WHERE g.entry_key = ‘STYLE’ AND g.context_id = s.context_id AND s.submission_id = sf.submission_id AND sf.file_id = agm.style_file_id
This line have 2 changes. After the table name and inside the where clausule.
The order by change is something @bozana was looking into – Bozana, did this end up leading to a commit?
As for the ADODB patch, we’re trying to avoid maintaining forks of third-party code. I suspect we’ll start by upgrading the ADODB version, which may resolve this issue, and continue looking at replacing ADODB entirely, in favour of a newer migrations-based approach to versioning (and hopefully a big simplification of our upgrade toolset).
Regards,
Alec Smecher
Public Knowledge Project Team
I had an error upgrading from version 2.4.8.2 to 3.1.0. I used this patch, but I think this line is wrong, right?
<query driver="postgres7">UPDATE submission_files sf SET genre_id = g.genre_id FROM genres g, submissions s, article_galleys_migration agm WHERE g.entry_key = 'STYLE' AND g.context_id = s.context_id AND s.submission_id = sf.submission_files.submission_id AND sf.file_id = agm.style_file_id</query>
sf.submission_files.submission_id appears to be redundant.
I applied the pacth and got the following error: Upgrade failed: DB: ERROR: schema sf does not exist.
I removed sf and left only submission_files and it worked.
I left it like this in the 3.0.0_update.xml:
line 96:
UPDATE submission_files SET genre_id = g.genre_id FROM genres g, submissions s, article_galleys_migration agm WHERE g.entry_key = ‘STYLE’ AND g.context_id = s.context_id AND s.submission_id = submission_files.submission_id AND submission_files.file_id = agm.style_file_id
line 102:
UPDATE submission_files SET assoc_type = 515, assoc_id = agm.file_id FROM article_galleys_migration agm WHERE submission_files.file_id = agm.style_file_id
Thanks a lot! I will merge that change…
It is correct that PostgreSQL needs the full main table name that is being updated to be used and not the alias…
Mine:
PostgreSQL 9.4.12 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
Probably @Adriano_Jose version is < 9.
submission_files sf SET genre_id = g.genre_id FROM genres g, submissions s, article_galleys_migration agm WHERE g.entry_key = ‘STYLE’ AND g.context_id = s.context_id AND s.submission_id = sf.submission_id AND sf.file_id = agm.style_file_id
Adriano’s query:
UPDATE submission_files sf SET genre_id = g.genre_id FROM genres g, submissions s, article_galleys_migration agm WHERE g.entry_key = ‘STYLE’ AND g.context_id = s.context_id AND s.submission_id = sf.submission_files.submission_id AND sf.file_id = agm.style_file_id