OJS3 - Hide all supplementary files

Hi @bozana

I have 3845 supplementary files.
Is there some “magic update query” to solve it? :grin:

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)

Hi @Tarcisio_Pereira

Oh, yes… a lot…
I’ll try to provide a script that would move those supp file to the submission stage…

Best,
Bozana

Hi @bozana,

I’ll be waiting.
Thank you by your time,

Tarcisio Pereira

Hi @Tarcisio_Pereira

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… :mage:

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.

hi @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

Hi @bozana

I will test it.

Regards,
Tarcisio Pereira

Hi @Tarcisio_Pereira,

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

Hi @Tarcisio_Pereira

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

Hi @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.

Hi @Tarcisio_Pereira,

I can add an option to the script to consider all journals. Would that help?

Best,
Bozana

Hi @bozana

Yes, it will help me a lot.
Thank you by this magic script :mage:

Regards,
Tarcisio Pereira

Hi @Tarcisio_Pereira

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. :slight_smile:

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