Hi @bozana
I have 3845 supplementary files.
Is there some “magic update query” to solve it? 
Regards,
Tarcisio Pereira
Just to be sure: Is this the total amount of results you have found executing the SQL above? Or maybe the total amount of entries in your DB table submission_supplementary_files?
revistas3=> select count(*) from submission_supplementary_files ;
count
16518
(1 row)
and
revistas3=> SELECT count(sf.*) FROM submission_files sf, genres g, submission_supplementary_files ssf WHERE sf.file_id = ssf.file_id AND sf.revision = ssf.revision AND sf.file_stage = 10 AND sf.assoc_type = 521 AND sf.genre_id = g.genre_id AND g.supplementary = 1;
count
3845
(1 row)
Oh, yes… a lot…
I’ll try to provide a script that would move those supp file to the submission stage…
Best,
Bozana
Could you please take a look/investigate how are the other supp files that are not in the result of this SQL statement: SELECT sf.* FROM submission_files sf, genres g, submission_supplementary_files ssf WHERE sf.file_id = ssf.file_id AND sf.revision = ssf.revision AND sf.file_stage = 10 AND sf.assoc_type = 521 AND sf.genre_id = g.genre_id AND g.supplementary = 1 ?
Why are they not in the result?
Thanks!
Bozana
Hi @bozana
Sorry about the delay, dark times around here… 
select sf.file_stage, count(*) from submission_supplementary_files ssf
join submission_files sf on (ssf.file_id = sf.file_id)
group by 1;
file_stage | count
------------±------
2 | 8873
4 | 3583
6 | 146
9 | 63
10 | 3861
11 | 122
15 | 143
18 | 379
(8 rows)
Time: 31.671 ms
Regards,
Tarcisio Pereira.
Oh, OK, I see: the other supp files are in another file stage and not as galleys, correct?
Is it sufficient for you to move just those as galleys and thus in the stage = 10 to the submission stage, submission files grid? – for the script to do exactly that…
Best,
Bozana
1 Like
it is not enough to just change that entry in the DB, so please wait for the script – those files has also to be renamed in the files folder…
Best,
Bozana
2 Likes
Before you apply the script below, please check also these following 3 things:
– check if there are any supp files that have dependent files
SELECT sf.* FROM submission_files sf, genres g, submission_supplementary_files ssf WHERE sf.file_id = ssf.file_id AND sf.revision = ssf.revision AND sf.file_stage = 10 AND sf.genre_id = g.genre_id AND g.supplementary = 1 AND EXISTS (SELECT sf2.assoc_id FROM submission_files sf2 WHERE sf2.assoc_id = sf.file_id)
– check if there are any remote galleys that need extra attention
SELECT * FROM submission_galleys WHERE remote_url <> null OR remote_url <> ''
– check if there are any supp files in the stage_id = 10 but not belonging to a galley
SELECT sf.* FROM submission_files sf, genres g, submission_supplementary_files ssf WHERE sf.file_id = ssf.file_id AND sf.revision = ssf.revision AND sf.file_stage = 10 AND sf.assoc_type = NULL AND sf.genre_id = g.genre_id AND g.supplementary = 1
Let me know what are the results of those queries. If any, they would need a special treatment.
Else, the CLI script that you could use to move all galley supp files to submission files grid is here: moveSuppFiles CLI script · bozana/ojs@d3617f6 · GitHub i.e. ojs/moveSuppFiles.php at moveSuppFile · bozana/ojs · GitHub.
You would need to take the script, copy/put it into your folder tools, and then run the script with your journal ID as parameter, like this: php tools/moveSuppFiles <journal_id> (for example php tools/moveSuppFiles 1, where 1 is the journal ID).
Very important: Please backup everything before running the script!!! It would be best to either do it first on a copy of your installation in a test environment/server, or to take your installation for some time down/from production, and properly test the result before putting it in production again, i.e. that you can restore your backup again in the worst case.
Best,
Bozana
2 Likes
Hi @bozana
Sorry for the delay, we had a holiday here.
Unfortunately:
revistas3=> SELECT count(sf.*) FROM submission_files sf, genres g, submission_supplementary_files ssf WHERE sf.file_id = ssf.file_id AND sf.revision = ssf.revision AND sf.file_stage = 10 AND sf.genre_id = g.genre_id AND g.supplementary = 1 AND EXISTS (SELECT sf2.assoc_id FROM submission_files sf2 WHERE sf2.assoc_id = sf.file_id);
-[ RECORD 1 ]
count | 933
Samples:
-[ RECORD 1 ]------+------------------------------------------------------------------------------
--------------------------------------------------
file_id | 55009
revision | 1
source_file_id |
source_revision |
submission_id | 45951
file_type | application/pdf
file_size | 28011
original_file_name | Atestado_Matricula.pdf
file_stage | 10
viewable | 0
date_uploaded | 2012-10-12 11:41:03
date_modified | 2012-10-12 11:41:03
assoc_id | 142211
genre_id | 2136
direct_sales_price |
sales_type |
uploader_user_id | 63164
assoc_type | 521
-[ RECORD 2 ]------+------------------------------------------------------------------------------
--------------------------------------------------
file_id | 45271
revision | 1
source_file_id |
source_revision |
submission_id | 38355
file_type | application/zip
file_size | 17363
original_file_name | 25814-29882-1-SP.docx
file_stage | 10
viewable | 0
date_uploaded | 2012-08-14 18:19:47
date_modified | 2012-08-14 18:19:47
assoc_id | 142197
genre_id | 1790
direct_sales_price |
sales_type |
uploader_user_id | 1207
assoc_type | 521
-[ RECORD 3 ]------+------------------------------------------------------------------------------
--------------------------------------------------
file_id | 81857
revision | 1
source_file_id |
source_revision |
submission_id | 62651
file_type | application/msword
file_size | 2952879
original_file_name | Parecer do Comite de etica 2.docx
file_stage | 10
viewable | 0
date_uploaded | 2013-10-03 20:25:22
date_modified | 2013-10-03 20:25:22
assoc_id | 142534
genre_id | 2424
direct_sales_price |
sales_type |
uploader_user_id | 7627
assoc_type | 521
revistas3=> SELECT count(*) FROM submission_galleys WHERE remote_url <> null OR remote_url <> ‘’;
-[ RECORD 1 ]
count | 1
Sample:
-[ RECORD 1 ]-+-----------
galley_id | 109405
locale | pt_BR
submission_id | 111081
file_id | 199971
label | PDF
seq | 0
remote_url | URL remoto
is_approved | 0
revistas3=> SELECT count(sf.*) FROM submission_files sf, genres g, submission_supplementary_files ssf WHERE sf.file_id = ssf.file_id AND sf.revision = ssf.revision AND sf.file_stage = 10 AND sf.assoc_type = NULL AND sf.genre_id = g.genre_id AND g.supplementary = 1;
-[ RECORD 1 ]
count | 0
So I did not run the script.
Regards,
Tarcisio Pereira
Ah, my fault for the first SQL – something is missing – so could you please try this one:
SELECT sf.* FROM submission_files sf, genres g, submission_supplementary_files ssf WHERE sf.file_id = ssf.file_id AND sf.revision = ssf.revision AND sf.file_stage = 10 AND sf.genre_id = g.genre_id AND g.supplementary = 1 AND sf.assoc_type = 515 AND EXISTS (SELECT sf2.assoc_id FROM submission_files sf2 WHERE sf2.assoc_id = sf.file_id)
And for the second SQL – remote supp files – is this a normal galley/full text or a supp file? – it seems that it is a normal galley/full text, so no problem with this…
And the third SQL result looks fine too.
Best,
Bozana
Hi @bozana
New fist query:
revistas3=> SELECT sf.* FROM submission_files sf, genres g, submission_supplementary_files ssf WHERE sf.file_id = ssf.file_id AND sf.revision = ssf.revision AND sf.file_stage = 10 AND sf.genre_id = g.genre_id AND g.supplementary = 1 AND sf.assoc_type = 515 AND EXISTS (SELECT sf2.assoc_id FROM submission_files sf2 WHERE sf2.assoc_id = sf.file_id);
file_id | revision | source_file_id | source_revision | submission_id | file_type | file_size | o
riginal_file_name | file_stage | viewable | date_uploaded | date_modified | assoc_id | genre_id |
direct_sales_price | sales_type | uploader_user_id | assoc_type
---------±---------±---------------±----------------±--------------±----------±----------±-
------------------±-----------±---------±--------------±--------------±---------±---------±
-------------------±-----------±-----------------±-----------
(0 rows)
And for the second SQL, it’s a normal galley.
I will test soon. Thank you by your time.
Regards,
Tarcisio Pereira.
Hi @bozana
I tested it and it looked good.
Congratulations on the script.
I have one more question, today I administer 184 journal.
Is there a more practical way of running the script for all of them other than one by one?
Regards,
Tarcisio Pereira.
I can add an option to the script to consider all journals. Would that help?
Best,
Bozana
Sorry for the delay :-
Here is the new script, supporting the parameter “all” meaning all journals: ojs/moveSuppFiles.php at moveSuppFile · bozana/ojs · GitHub.
I haven’t tested it properly, so please use it in a test environment first and make sure everything went fine, before applying it in the production system.
Best,
Bozana
2 Likes
Hi @bozana
I have run the moveSuppFiles script and it works, the reason was because I have upgraded an OJS 2.4.6 to OJS 3.1.1.4 and I have found the supplementary files as supplementary galleys in the abstract page.
Thanks a lot for the script.
1 Like
Hi,
Thanks Bozana for this slice of magic. 
I tried the script on ojs 3.2.1-4 and failed because the changes in the DB model, but a simple replacement in the table names (submission → publication) was enough to make it run again.
PR committed, just in case somebody else falls in same hole:
Also thanks to @israel.cefrin for the tip.
Take care,
m.
1 Like