OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



Reverse Engineer OJS Database with Relationships

Are you responsible for making OJS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, btbell, michael, bdgregg, barbarah, asmecher

Forum rules
What to do if you have a technical problem with OJS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OJS Editorial Support and Discussion subforum; if you have a development question, try the OJS Development subforum.

Reverse Engineer OJS Database with Relationships

Postby amecily » Sun May 27, 2012 7:16 am

Hi there,

I have been trying to create an ERD of the OJS database from a .SQL file in order to document the database design to make it easier for customisation etc. I have managed to reverse engineer the file using MySQL WorkBench, and Toad Data Modeler, and I've tried a number of others. But the relationships between the entities do not form, so the diagram is not of much help.

Is there a way to reverse engineer the database so that the relationships are developed as well?

Thanks a lot,
Amy :)
amecily
 
Posts: 13
Joined: Sun May 27, 2012 6:59 am

Re: Reverse Engineer OJS Database with Relationships

Postby asmecher » Sun May 27, 2012 10:12 am

Hi Amy,

The problem is that we're using ADODB as a database abstraction layer in order to support several DBMSs (MySQL and PostgreSQL); ADODB doesn't explicitly define foreign keys, so you won't be able to get them back from the database either (unless the tool you're using makes guesses based on e.g. column names).

I've taken a crack a couple of times at making a diagram, but there are so many tables that it's really hard to lay everything out usefully. Internally we generally make use of the XML descriptor when we want to look up the database structure (e.g. dbscripts/xml/ojs_schema.xml). Is there a particular relationship I could describe for you?

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 8419
Joined: Wed Aug 10, 2005 12:56 pm

Re: Reverse Engineer OJS Database with Relationships

Postby amecily » Sun May 27, 2012 3:10 pm

Hi Alec,

Thanks a lot for the response.

Okay, I understand what you mean. No thanks regarding the specific relationship at the moment - might need to know some relationships later on, but for now it's just documentation.

Just two questions: Do you know of any tools that do make assumptions based on column names etc.?
And is there no way to generate a diagram using the XML document?

Thanks,
Amy
amecily
 
Posts: 13
Joined: Sun May 27, 2012 6:59 am

Re: Reverse Engineer OJS Database with Relationships

Postby asmecher » Sun May 27, 2012 3:33 pm

Hi Amy,

Unfortunately I don't have any recommendations for you. If you have (or anyone else has), please let us know; we would love to publish diagrams for the database but it's been too much of a pain with the manual tools I've tried.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 8419
Joined: Wed Aug 10, 2005 12:56 pm

Re: Reverse Engineer OJS Database with Relationships

Postby amecily » Tue May 29, 2012 8:24 am

Hi Alec,

I found a tool in Toad Data Modeler that infers relationships based on field numbers and other criteria (I assume). I have managed to generate the relationships between entities. It is a huge diagram though! Would you like me to send you the diagram?

For anyone who is looking for this answer, in Toad Data Modeler (after reverse engineering the database), click on Macros in the Menu bar, then All -> Infer Relationship.

Thanks,
Amy
amecily
 
Posts: 13
Joined: Sun May 27, 2012 6:59 am

Re: Reverse Engineer OJS Database with Relationships

Postby asmecher » Tue May 29, 2012 11:00 am

Hi Amy,

Please do -- I used Toad many years ago and found it useful, but unfortunately I'm using a Linux platform now. If the diagram looks promising I'll see if someone on our team can investigate it a little further. It might help to try generating the diagram using subsets of the full table list -- the editorial process, for example, or subscriptions, or settings. A single diagram with all the tables will be enormous.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 8419
Joined: Wed Aug 10, 2005 12:56 pm

Re: Reverse Engineer OJS Database with Relationships

Postby amecily » Wed May 30, 2012 4:57 am

Hi Alec,

Yes, all these tables do make the diagram enormous. Do perhaps have a list of the main tables that should be used in the diagram? That would be very helpful so that I could select only those tables. Then I'll make the diagram and should I place it on the forum or email it to you?

Thanks,
Amy
amecily
 
Posts: 13
Joined: Sun May 27, 2012 6:59 am

Re: Reverse Engineer OJS Database with Relationships

Postby asmecher » Wed May 30, 2012 8:53 am

Hi Amy,

I've just sent you a PM.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 8419
Joined: Wed Aug 10, 2005 12:56 pm


Return to OJS Technical Support

Who is online

Users browsing this forum: Baidu [Spider], Bing [Bot] and 7 guests