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

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.

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

regards!

Aritz Galdos

I do agree with Aritz.

Just like for Role he mentioned, integrity is being checked while deleting group also:
Code link

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.

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.

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.

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

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.

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.

Can we conclude that the code of liferay may become more clean if we let the DBMS maintain the integrity of the database?

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.