Database Documentation

[Ashish Kumar Jain，修改在6 年前。]

Hi Guys,

Is it possible for Liferay to share document link where I can find the table details what are the tables we have and which used for what. It will help to understand database.

[Jorge Díaz，修改在6 年前。]

Hi Ashish,

There is no database documentation. It doesn't exists.

You should never directly modify Liferay database, you can create inconsistencies.

Regards,
Jorge

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

Ashish Kumar Jain:

Is it possible for Liferay to share document link where I can find the table details what are the tables we have and which used for what. It will help to understand database.

I have said many times before, the database is not yours to look at nor to understand.

The only approved way to retrieve, update or delete data is through the Liferay API.

Why? Because the Liferay API does more than just set data in the database. It updates indexes, it issues cache notifications across the cluster, it invokes pre- and post-update actions, it can include auditing, and a seemingly simple data update can actually trigger updates to many tables in a specific sequence and within the scope of a transaction. It also always includes permission checks to ensure that updates are allowed by the user attempting the updates.

None of these things are handled by outside database updates. Outside updates can introduce data issues, stale indexes and caches, etc.








Come meet me at Devcon 2017 or 2017 LSNA!

[Olaf Kock，修改在6 年前。]

David H Nebinger:

Outside updates can introduce data issues, stale indexes and caches, etc.

I'd change this to:

Outside updates will introduce data issues, stale indexes and caches, catastrophic failures in the future und unexplainable outages long time after you've changed the data. Then you'll falsely blame Liferay to malfunction even though you've introduced the issue yourself because you assumed that you understood the database.

[Christoph Rabel，修改在6 年前。]

David H Nebinger:

Ashish Kumar Jain:

Is it possible for Liferay to share document link where I can find the table details what are the tables we have and which used for what. It will help to understand database.

I have said many times before, the database is not yours to look at nor to understand.

I disagree. Why does everybody insist that understanding something is a bad thing?

Looking into the database has often helped me understand how to use the API. Several interfaces are badly documented and it's very difficult to find out what parameters an API call wants. It often helped me a lot to understand why something didn't work as expected. Of course, looking into the sourcecode helps too, but sometimes understanding comes faster when you also look into the database.

I agree with the "acting" part though. One should never update the DB directly, it's always dangerous. Even when it's "yours". When there's an API, use it. And on top of that: Service Builder created tables have no relational integrity, so it's even more easy to break things. Far more easy than in well designed table structures.

[Olaf Kock，修改在6 年前。]

Christoph Rabel:

David H Nebinger:

Ashish Kumar Jain:

Is it possible for Liferay to share document link where I can find the table details what are the tables we have and which used for what. It will help to understand database.

I have said many times before, the database is not yours to look at nor to understand.

I disagree. Why does everybody insist that understanding something is a bad thing?

Because typically this question does not come from someone with a 456 posts history on the forums (like you), but rather from someone with a 4 post history (like OP). The assumption that knowledge about the database content will be abused to write data has held to be true so many times, and people (in- and outside of Liferay, the company) have been bitten by this so severely that the message needs to be given first.

Plus, there is nothing to be understood with regards to the database structure. I'm granting you that some aspects (e.g. portal preferences) can be helpful to understand what's going on when you see them in the database. That does not justify publishing "a documentation that helps to understand the database" (TM) though.

Or, to phrase it differently: There are a lot more things in Liferay worth understanding long before one understands "the database"

[Christoph Rabel，修改在6 年前。]

Olaf Kock:

Christoph Rabel:

I disagree. Why does everybody insist that understanding something is a bad thing?

Because typically this question does not come from someone with a 456 posts history on the forums (like you), but rather from someone with a 4 post history (like OP). The assumption that knowledge about the database content will be abused to write data has held to be true so many times, and people (in- and outside of Liferay, the company) have been bitten by this so severely that the message needs to be given first.

So, it's better to assume from the very beginning that somebody with 4 posts is probably doing it wrong? And a poster with 456 postings is probably doing it right? Well, I am still doing a lot of things wrong. I usually find out only when they bite me later on. And believe me, I have walked many dark paths of evil*, often unscathed and sometimes badly burned.

I don't think it's ever good advise to say: "... is not yours to look at nor to understand." I disagree with that sentence wholeheartedly. Don't touch it! It's not yours. FULL AGREE.

But looking at something, understanding how it works, I don't agree there. I found many interesting things in Liferay source code. By the same logic one could also say: "The source code is not yours to look at nor to understand."

* Only in software development/administration, of course, I am an angel otherwise.

[Andrew Jardine，修改在6 年前。]

Well, I am still doing a lot of things wrong.

lol -- way to invalidate all your great advice you've provided in the forums! haha.

This is an interesting thread for sure. I'm with you on some points and with Olaf on others. I mean, 4 posts, you're probably a n00b and if you don't have the knowledge you're going to @$!# it up. But by the same token, we all have to start somewhere, and @!#$'ing it up is usually the best way to learn. With that said, I wouldn't never expect someone from Liferay to provide advice to break a users environment -- especially when the end result is often a misplaced notion that there is something wrong with Liferay (as a product) when it fact it's more accurate that it was not USED properly.

The API is the way to go, but it looks to me like he just wants to look up a few values, not change them. I have done DB edits a few times before -- I just always make sure to clear all caches (or restart) and to make sure to reindex the search index when I am done With that said, I have always kept my direct access limited to UPDATES, never INSERTS, and never touching more than one table.

I guess it all depends on how dangerously you like to live ;)

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

Andrew Jardine:

I have done DB edits a few times before -- I just always make sure to clear all caches (or restart) and to make sure to reindex the search index when I am done With that said, I have always kept my direct access limited to UPDATES, never INSERTS, and never touching more than one table.

I guess it all depends on how dangerously you like to live ;)

And of course before doing any of this Andrew has gathered a full backup, database and data directory, index, etc. so in case of bad data getting into the system, he could roll everything back to get to a good state.

Although that doesn't really help if you implant bad data that doesn't cause an immediate failure but only shows itself after some time, then restoration is not going to be possible if there have been database changes as a result of usage...








Come meet me at Devcon 2017 or 2017 LSNA!

[Andrew Jardine，修改在6 年前。]

pfftt.... OBVIOUSLY ...

I probably should have added that I have never done (unless I was drunk) any of these changes into any mission critical environments. So when I have broken things, it has at worst been a Development server.

Your point, and Olaf's is valid. In fact, if I have a dollar for every time I have read "don't edit the database directly" in the forums, I would be able to take all the people on this thread out for a night of debauchery.

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

Count me in!










Come meet me at Devcon 2017 or 2017 LSNA!

[Olaf Kock，修改在6 年前。]

Andrew Jardine:

pfftt.... OBVIOUSLY ...

I probably should have added that I have never done (unless I was drunk) any of these changes into any mission critical environments. So when I have broken things, it has at worst been a Development server.

Your point, and Olaf's is valid. In fact, if I have a dollar for every time I have read "don't edit the database directly" in the forums, I would be able to take all the people on this thread out for a night of debauchery.

You're now marked as someone who changes the database despite knowing better, and as someone touches mission critical systems when drunk. This marker can only be removed by a night of debauchery

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

Christoph Rabel:

I don't think it's ever good advise to say: "... is not yours to look at nor to understand." I disagree with that sentence wholeheartedly. Don't touch it! It's not yours. FULL AGREE.

The database is a tempting target for all developers, I've been there myself.

Here's the problems though:

• Developers ask because they want to do more than just query. They have batch updates or want to build a custom SQL statement to join Liferay tables to their own, etc.
• Liferay can change the DB at any time. Anything you know about 6.0 may or may not apply to 6.1, may or may not apply to 6.2, 7.0, and the future 7.1.
• Not all of the DB columns necessarily reflect their usage. Dropping/changing columns was not typically done in previous update cycles, but the columns were sometimes reused for other purposes, and of course this was undocumented so you may or may not know what a column is really for, but there is no guarantee that whatever purpose it serves today will still apply tomorrow.
• Many of the columns are now basic text fields holding json and xml, so you can't build a real query to find records and will likely hose the data if you try updates.
• The DB does not have any foreign keys in place so there is no way to ensure that changing a column or deleting or adding a row won't just hose it all up. Heck, without even unique and/or not null constraints, you can't tell what a column may have or should have, those constraints are only enforced by the code.
• Column values may be FK values or they may be constant values, but there is no clear indicator what a particular value actually represents. Is a group id really a group id or is it a team id or an org id or ...? Context plays an important part in evaluating each column value in each row and one cannot always assume that the usage is going to be 100% consistent.

So yes, I speak in absolutes regarding the database. And yes, I expect that folks won't listen. My hope is that newbies coming to the forum looking for the database diagram will find my post and the warnings about why you shouldn't try it and might actually listen.

For those that don't, I can say "I told you so".

There are lots of really good reasons why someone should not be in the database and few (if any) reasons to go there.








Come meet me at Devcon 2017 or 2017 LSNA!

[Ashish Kumar Jain，修改在6 年前。]

Thank you every one for your comments.

I am completely agree with Christoph Rabel, learning something is not bad.

HI Team,

Can you please help me here on below points.
How I can get all the user details who have different kind of access on Intranet?
How I can get all the folders structure along with full depth?

[Olaf Kock，修改在6 年前。]

Ashish Kumar Jain:

How I can get all the user details who have different kind of access on Intranet?
How I can get all the folders structure along with full depth?

• All User Details: UserLocalService (or UserService)
• different kind of access: ??? please elaborate. Or check Organization-, Group-, UserGroup-, Role- or similar -LocalService or -Service
• Web Content Folders: JournalFolderLocalService or JournalFolderService
• Document Library Folders: DLFolderLocalService, of course, or: DLFolderService
• or if you need the page hierarchy, that's coming from LayoutLocalService.

By the way: For the purpose of getting access to this data, understanding the database is absolutely not necessary.