Database details

[tom muck，修改在7 年前。]

Does anyone have a list of core Liferay tables, or a diagram of relationships?

[David H Nebinger，修改在7 年前。]

It doesn't exist Tom.

As I've said many times before, stay out of the DB. It's not yours to play around in. It is a black box subject to change with every patch, every release. Any information you gleam out of the database will be short lived.

The only approved way of accessing Liferay data is through the Liferay API.

[tom muck，修改在7 年前。]

Is the API accessible through PLSQL?

[David H Nebinger，修改在7 年前。]

Nope, and neither is the Liferay database.

Trust me, the tables are not to be exposed. They have no database-supplied foreign keys, no cascade delete constraints, etc. It is too easy to change/delete something that in turn can make your environment unusable.

Some tables have relationships to many different tables; any time you see the class name/class pk columns, that means the table can pretty much refer to any other Liferay table.

Liferay uses indexing heavily, but Liferay will only index data persisted via the Liferay API. If you change any of the data directly, the index will be out of sync with the index and odd things can result from that.

And like I said, it is subject to change at any time.

I always tell my clients to stay out of the database. I know it's tempting, I know at first all you want to do is join against Liferay data or something and eventually you start feeling okay w/ updating something but before you know it your system starts throwing odd exceptions for unknown and unclear reasons.

Do yourself a favor and just pretend it does not exist.

[tom muck，修改在7 年前。]

So far two non-answers. I appreciate your concern; I'll take it under advisement. It appears this community could benefit from a deep analysis and documentation of the database. My work on the platform has no benefit from working in the API. I need to access the database. Period. End of story. It appears to be badly designed with data duplication (why a userid and username across 80+ tables? If there is a reason, I'd like to know it). I'm well familiar with the classpk column, used it in many queries, but my work is now more involved. I have previously inserted, deleted, updated data in Document Library and Message Boards. I can get to my end goal without assistance, but was hoping someone knew what they were doing and had done the legwork before. I'll continue on my own, thanks.

[Jack Bakker，修改在7 年前。]

tom muck:

Does anyone have a list of core Liferay tables, or a diagram of relationships?

in postgresql, for a list of tables:
select table_name from information_schema.tables where table_schema = 'public'

but don't look at the results to the query without wearing a helmet, given the dangers of gleaming short lived knowledge that David points out - which is a reactive condition similar to the one exemplified in the following Monty Python skit:

https://www.youtube.com/watch?v=WwbnvkMRPKM

ok, silliness aside:
- regarding the userid, username, and others that are in many tables, perhaps search "Liferay Audit Fields"
- regarding database-centric PLSQL, note that Liferay is designed to support many different databases (search "Liferay Compatibility Matrix")..
- the api is very extensive and is the best way in (though you write you don't want to use it ?) ; though sometimes one needs to write custom sql (which again is supported by the api)

enjoy

[tom muck，修改在7 年前。]

Thanks, I'm using Oracle, but I already have lists of tables and fields, just need to sort it from the custom tables from custom applications running in Liferay on our servers, which I can do fairly easily. More interested in foreign keys and relationships. I realize most of the constraints are at the application level, but knowledge of the foreign keys and what needs to change when a record is inserted into, for example, the DLFILEENTRY table. I assumed userid and username for for audit purposes, but my question is "why both"? Does a username change in the application? Doesn't it always relate to the same userid? I don't mind using the API, but I'm not a Java programmer and at this point I'm just dealing with data.

Yes, I realize getting into the database is the equivalent of "pushing the red button -- don't ever push the red button" but I know the risks and accept them willingly.

[Jack Bakker，修改在7 年前。]

Changes made through the api might get persisted in various places in the database and also in the likes of search indexes, and in filesystem for the likes for documents.

David's expert advice to stay away from making changes directly in the database is good advice as you very likely will break things.

Likewise, I wouldn't start with consulting a neurologist if I wanted to learn how to play the harp.

Good luck.

[tom muck，修改在7 年前。]

Thanks Jack, but using your analogy, as a SQL programmer I shouldn't be venturing into Java code. My expertise is with the database. I know that things are persisted across several tables in the application, which is why I would like to know those foreign keys. You guys have said "I don't know" in every way imaginable, and "keep your hands off", but I'll wait for a helpful answer and in the meantime do the legwork myself.