OJS 3308 - same name for primary-key in some tables

Software Support
1

Hello.
Looking at the OJS version 3.3.0-8 database, I noticed that the same name (file_id) was used as the primary-key for 4 different tables (files,issue_files,library_files,temporary_files ). Also, still all these tables still use AUTO_INCREMENT. :scream:
Although the literature usually suggests using different names, if OJS had a database with a relationship between the tables, where there is a foreign key, this would not be a problem. But this is not what happens, because the tables were not connected.
The result of this is that it generates a great difficulty to understand the relationships between the database tables, isn’t it ?

Please, someone can explain what happened in the project ?

2

Hi @murilodbva,

OJS started before foreign key declarations were reliably available in the database systems it supports (primarily very old versions of MySQL, but also related to the ADODB library we used to use for database abstraction). Now that we require a newer MySQL and have replaced ADODB’s schema management with Laravel’s toolset, we are actively adding FOREIGN KEY declarations for the forthcoming 3.4.0 release. Because of the number of these, it’ll probably take us another release or two to get them all in place.

If you have a specific question about the schema, I can probably answer it here.

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like
4

Hi @asmecher , thank you so much for the quick reply. =)

“If you have a specific question about the schema, I can probably answer it here.”
R: In OJS version 3214, can you say how can I discover wich tables uses the file_id from ‘submission_files’ ?
(I’m trying it for removing orphaned ‘submission_files’. I’m trying to upgrade to version 3308 and it’s disturbing)

5

Hi @murilodbva,

The submission_files.file_id column refers to the files table.

Regards,
Alec Smecher
Public Knowledge Project Team

7

But submission_files.file_id is AUTO_INCREMENT.

Usually (or allways) AUTO_INCREMENT id can’t be FOREIGN KEY, or am I wrong? :worried:

8

Do all file_id's in other tables refers to files table ?

9

Hi @murilodbva,

In OJS 3.3.0-8, submission_files.file_id should not be AUTO_INCREMENT, it’s a FOREIGN KEY referencing files.file_id.

The other tables you mentioned earlier (e.g. temporary_files, library_files, issue_files) have their own file_id primary keys, and they are not related to submission_files or files.

Regards,
Alec Smecher
Public Knowledge Project Team

1 Like
10

ojs update ojs-3.1.2-1 – ojs-3.3.0-8

but generic import not working.

This is ok?

MariaDB [ojsdata]> desc  submission_files;
+---------------------------+---------------------+------+-----+---------+----------------+
| Field                     | Type                | Null | Key | Default | Extra          |
+---------------------------+---------------------+------+-----+---------+----------------+
| submission_file_id        | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| source_submission_file_id | bigint(20)          | YES  |     | NULL    |                |
| submission_id             | bigint(20)          | NO   | MUL | NULL    |                |
| genre_id                  | bigint(20)          | YES  |     | NULL    |                |
| file_stage                | bigint(20)          | NO   | MUL | NULL    |                |
| direct_sales_price        | varchar(255)        | YES  |     | NULL    |                |
| sales_type                | varchar(255)        | YES  |     | NULL    |                |
| viewable                  | tinyint(4)          | YES  |     | NULL    |                |
| created_at                | datetime            | NO   |     | NULL    |                |
| updated_at                | datetime            | NO   |     | NULL    |                |
| uploader_user_id          | bigint(20)          | YES  |     | NULL    |                |
| assoc_type                | bigint(20)          | YES  |     | NULL    |                |
| assoc_id                  | bigint(20)          | YES  |     | NULL    |                |
| file_id                   | bigint(20) unsigned | NO   | MUL | NULL    |                |
+---------------------------+---------------------+------+-----+---------+----------------+
14 rows in set (0.003 sec)

MariaDB [ojsdata]> desc  files;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| file_id  | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| path     | varchar(255)        | NO   |     | NULL    |                |
| mimetype | varchar(255)        | NO   |     | NULL    |                |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.001 sec)

  submission_file_id    Extra  auto_increment ???
11

@mauser1 ,
This topic is already responsed and it’s a other subject you wroten.
It’s better you open new topic.
Open and I can try help you there. :slightly_smiling_face:

12

Hi @murilodbva,

At a glance, those tables look fine. The submission_files.submission_file_id column is auto_increment as it should be. (The submission_files.file_id column is a foreign key to files.file_id.)

Regards,
Alec Smecher
Public Knowledge Project Team

13

Thanks. There will probably be a problem with import xml.

Unfortunately, we don’t really find examples of imports. By logic, we take the exported xmlt as a basis.

14

Hi @mauser1,

Could you open a new topic about the import XML problems, with details on what you’re importing and the errors are that you’re encountering?

Thanks,
Alec Smecher
Public Knowledge Project Team

17

Yes, we’ve already done on 24 Feb: https://forum.pkp.sfu.ca/t/after-upgrading-to-3-3-0-8-error-500-on-trying-to-import-issues-articles-native-xml-plugin/72121

18

This topic was automatically closed after 6 days. New replies are no longer allowed.