Kombinierte Ansicht Flache Ansicht Baumansicht
Threads [ Zurück | Nächste ]
toggle
Gavin Meyers
Liferay DB
3. Februar 2011 03:00
Antwort

Gavin Meyers

Rang: New Member

Nachrichten: 15

Eintrittsdatum: 13. September 2010

Neue Beiträge

Hi,
Liferay DB does not make use of FK in any of its tables. How does it take care of data-integrity constraints. through code? if yes, then which class/api is used for this?

Thanks in advance

Gavin
Aritz Galdos
RE: Liferay DB
3. Februar 2011 03:36
Antwort

Aritz Galdos

Rang: Expert

Nachrichten: 405

Eintrittsdatum: 15. Mai 2007

Neue Beiträge

Hi Gavin:

Yes, you are right. Liferay does not use FK. As I understood, this decission was taken for compatibility reasons, this way makin liferay able to run over more DBMS.

Liferay core takes care of integrity. I was in doubt and had a look to de API.

For example, when deleted a role, which can be related to user and a group (ternary relationship mapped in UserGroupRole model bean) , Liferay deals to delete references in UserGroupRole.

For an example you can have a look here


Hope that helps.

Actually it helped me too. emoticon

This was a concrete examplee about roles, but I guess work the same for other usecases.

regards!

Aritz Galdos
Gaurav Jain
RE: Liferay DB
3. Februar 2011 04:19
Antwort

Gaurav Jain

Rang: Regular Member

Nachrichten: 146

Eintrittsdatum: 31. Januar 2011

Neue Beiträge

I do agree with Aritz.

Just like for Role he mentioned, integrity is being checked while deleting group also:
Code link
Robert Folkerts
RE: Liferay DB
21. Februar 2011 16:54
Antwort

Robert Folkerts

Rang: New Member

Nachrichten: 5

Eintrittsdatum: 21. Februar 2011

Neue Beiträge

One of the challenges that I face with any portal is the separation of 'enterprise' and 'application' data. The lportal holds a great deal of data that is strategic to the company hosting it. But the lportal is clearly an 'application database'. The companies, users and so on of the community members are the same people and organizations that are found in the existing 'enterprise database'.

This requires at the very least some ETL (extract, transform, load) process to gather data from the portal and push it to the enterprise database. It should probably also have some process to monitor the enterprise database for data changes that need to to added, manually perhaps by a administrator, to the portal. If Jane Maidenname gets married and changes her her name to Jane Husbandsname, then that needs to be reflected in both company records (HR) and in her portal records. (Her user_ records).

I would like to be able to do this with tools like Pentaho's PDI. It would be a big plus to me if lportal had explicit foreign keys. One of features that I really like about (Groovy on) Grails, is that it generates foreign keys in its ORM, so there is a precedent for an ORM that adds foreign keys explicity. From the column names, it seems that there are at least 360 implied foreign keys in the lportal database. (based on queries of information_schema in the postgresql implmentation) If these were explicit, then I could see them in any ERD tool. This would really help in creating the ETL processes.

I also use the density of foreign keys (ratio of keys to tables in the database) as a tool to estimate the quality of a database design. It has been my experience that this is a pretty good measure. But in the case of Liferay, I get a score of zero which is not all all indicative of the (very good) database design.

There are cases where 'redundant' foreign keys are seen as performance limiting. Then they are intentionally omitted as an optimization. If that is the case, can we at least have the option to choose data validation over performance? If the foreign keys are added with ' on deleted cascade', there is almost no risk of the foreign key somehow interfering with the normal operation of the Java application.
Robert Folkerts
RE: Liferay DB
21. Februar 2011 10:47
Antwort

Robert Folkerts

Rang: New Member

Nachrichten: 5

Eintrittsdatum: 21. Februar 2011

Neue Beiträge

In order for your referenced code example to replicate a foreign key, there should be a transaction that rolls back in the event that either delete fails. For me, any database interaction should be ACID (atomic, consistent, isolated and durable). In my opinion, this code example actually strengths the case that a foreign key should be used. The two delete statements use seperate group.getGroupId() to determine which records to delete. Is it not possible that the two calls will return different values and result in invalid references? There are other concurrent users and on rare occasions, function calls error on the JVM.
Robert Folkerts
RE: Liferay DB
21. Februar 2011 18:33
Antwort

Robert Folkerts

Rang: New Member

Nachrichten: 5

Eintrittsdatum: 21. Februar 2011

Neue Beiträge

There is a final advantage to adding foreign key constraints. The act of adding the constraint uses the database engine to validate the existing design. As as experiment (that I would never do in production), I generated a list of about 300 foreign keys from some assumptions that I made about the database naming conventions. I may well be wrong in my assumpions, but I added over 300 foreign keys with only 4 errors.

For example, I tried to add the following foreign key in PostgreSQL:

alter table portletitem add constraint fk_portletitem_portlet foreign key (portletid) references portlet(portletid) on delete cascade;

The database engine returns an error and reports that there is no unique constraint on portletid in the portlet table. Unlike most tables, the portlet table has an id named id_ that is used as the primary key. Most of the time, the table and the column have similar names, like phone and phoneid. Other tables that appear to reference the portal table all use a column named portalid, which is a varchar(75). In the portlet table, the column portletid is a varchar(200). I have not looked into this in detail, but I will bet that bad things could happen if two porlets share a common portalstringid. I'll also bet that all of the portletid columns reference the same strings. So I will argue that they should all be varchar(200) or they all should be varchar(75), depending upon the maximum length of a portletid. These are very picky criticisms, but I will also bet that they are correct.
Natasa Bulatovic
RE: Liferay DB
14. Dezember 2011 07:29
Antwort

Natasa Bulatovic

Rang: Junior Member

Nachrichten: 28

Eintrittsdatum: 7. Juni 2011

Neue Beiträge

can someone advise (send link) where the diagram of the database structure is and information on which tables are used by which portlets? thank you
Hitoshi Ozawa
RE: Liferay DB
15. Dezember 2011 15:09
Antwort

Hitoshi Ozawa

Rang: Liferay Legend

Nachrichten: 7949

Eintrittsdatum: 23. März 2010

Neue Beiträge

You can get a pretty good meta data information from the service.xml file. You can open it up on Liferay IDE graphical interface too.

BTW, people shouldn't be directly looking at the database but should be looking at the interface to do data manipulation operations.
Connect the ETL tools to use Liferay's web service or Java interfaces.
David H Nebinger
RE: Liferay DB
15. Dezember 2011 15:52
Antwort

David H Nebinger

Community Moderator

Rang: Liferay Legend

Nachrichten: 11321

Eintrittsdatum: 1. September 2006

Neue Beiträge

Natasa Bulatovic:
can someone advise (send link) where the diagram of the database structure is and information on which tables are used by which portlets? thank you


No such diagram exists. No foreign keys exist. Portlets do not use tables at all, and you shouldn't either.

Portlets call services which may (or may not) use tables, and many portlets use many services. You will never find a "this portlet populates this table" description because they are not one to one.
Leon Z
RE: Liferay DB
20. Dezember 2011 04:41
Antwort

Leon Z

Rang: Junior Member

Nachrichten: 35

Eintrittsdatum: 29. April 2009

Neue Beiträge

Can we conclude that the code of liferay may become more clean if we let the DBMS maintain the integrity of the database?
David H Nebinger
RE: Liferay DB
20. Dezember 2011 05:55
Antwort

David H Nebinger

Community Moderator

Rang: Liferay Legend

Nachrichten: 11321

Eintrittsdatum: 1. September 2006

Neue Beiträge

This is a holy war (db-based relationship management vs code-based relationship management) of which each side cannot agree.

The only outcome that matters is that the Liferay DB is Liferay's entity and whether they want to do db-based or code-based relationship management is entirely up to them and not us. Seeing as it is working for the most part, do not expect a change any time soon.