Database Tables Description

[saleem khan，修改在9 年前。]

Hi.....
Can anyone tell or describe the liferay Database tables and particularly "Account_" table is used for and please tell me the relationship between all the tables.

It will be greatly helpfull if anyone tells me this
Thansk...

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

No. You should not be in the database at all, and every table/column you're looking to use may be dropped or repurposed in the next Liferay release.

The only access to Liferay data is through the Liferay API, period.

Forget the database is there, it is not yours to look at, play with, report from, join to, ...

[saleem khan，修改在9 年前。]

Thanks for replying sir,
but when i went to an interview they asked me a quesstion like if you create a WCM what are the changes happen in database and in which particular table the data or details of the created WCM gets stored ,thats the reason why i asked this question.

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

The problem is the answer that you want to give changes from version to version and, since Liferay is so configurable, you can plug many external systems in.

Honestly I've been doing Liferay for 9 years and I cannot tell you what tables are modified when a WCM is created. Knowing what table(s) are modified is not a valid test of what Liferay knowledge you do or don't have. The Liferay Developer Certification never mentions the database, the tables, etc. It does ask about APIs, though.

It may be "pedantic" ( ), but all I'm trying to do is push folks in the right direction. Trust me, the moment you try to do something directly to the database is the moment that you will lock yourself into a particular version of Liferay. They see the database as theirs and theirs alone, and they change it at their own whim. I'm sure they make changes to continue to improve the product, but it will leave you banging your head against the wall.

Liferay has published the API and has said that developers should use the API to access the data. This is their mandate, and you (we) should live by it.

[saleem khan，修改在9 年前。]

Thank you sir.
Can you please suggest me a tutorial or and place where i can learn liferay and as well prepare for an interview and is liferay a best field for starting developers like me.

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

liferay.com and Liferay in Action. At the moment those are the two bibles.

[saleem khan，修改在9 年前。]

Thank you sir

[Manikantha Rajamani，修改在9 年前。]

Hi
saleem khan

For porlet u can read Liferay CookBook...which is very usefull


Thanks and Regards
Manikantha

[Arvind Mishra，修改在9 年前。]

"if you create a WCM what are the changes happen in database and in which particular table the data or details of the created WCM gets stored"

This questions till remains unanswered -
Web Content Articles are mainly stored in JournalArticle table.
Lots of other thing could happen:
[indent]Depending upon whether they have structure/templates you will have additional id's mapped to the record.
Permission/Resource tables will also get updated.
Asset* tables will get updated since WCM article is an asset
Other Journal* tables will also get updated.[/indent]


I think understanding of Database for any product gives you upper hand in the overall understanding of the system. Job of Persistence layer is to eliminate SQL knowledge from developers, but as a Sr consultant you should know these things. Database knowledge is very handy when DB updgrade fails and you need to figure out whether the issue is with code or data.

Thanks

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

Arvind Mishra:

Database knowledge is very handy when DB updgrade fails and you need to figure out whether the issue is with code or data.

But again, that's the trap. I recently did a 6.1 to 6.2 upgrade and it failed during startup. Knowing the database was absolutely zero help, as was the messages generated from Liferay.

In order to solve it I had to revert the database and modify the core Liferay upgrade class to actually spit out the exception message that it was swallowing before reporting upgrade failed.

Turned out a corrupted portlet preference was in the db (corrupted in that the xml was truncated and didn't have proper closing tags).

The point is that the DB knowledge wouldn't have meant anything with respect to resolving it, the code was the only thing that mattered.

I do know and understand the database, I am just as frustrated by the limitation of the javadocs, etc.

That said, new folks start using liferay and end up here in the forum by searching for something in Google. From that perspective, pointing a new user at the database is truly the wrong answer. A new developer should, as I've suggested, ignore the database and focus on how to use the API (the create methods, the add methods, update methods, finders, etc.). That will ultimately make them productive Liferay developers far more than any details they happen to get out of the table structures in the database.

[Arvind Mishra，修改在9 年前。]

Hi David

I was answering the initial query in the thread. I am not pointing new user's to any direction.

In my opinion having database knowledge is an important asset within your armory. There are various use cases which could justify this, it all depends on scale, environment & organization for which an application is developed.

Thanks

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

Right, and in the total Liferay landscape having some knowledge of the database is important, but it is less important than understanding the API and the implication that you cannot just start making database changes via SQL as they will not have expected results at times in Liferay.

And no, even I don't always answer posts with newbies in mind, but database access is one of those frequently searched for elements with respect to Liferay - it's just one of those things new devs need to do and have no experience with Liferay and so they start googling and end up in these kinds of threads.

Thanks for answering though, Arvind, and I hope I didn't step too hardly on your toes

[Arvind Mishra，修改在9 年前。]

Thanks for answering though, Arvind, and I hope I didn't step too hardly on your toes

- Not really, it is all about giving view point, which are mostly driven by experience. These discussions are important for this forum to provide multiple aspects of same situation.

[Joseph Toman，修改在9 年前。]

Let me preface this with the lack of an answer: I have no idea what Account_ does or why it's different than user_. It's possible that it's a list of the default users for each portal instance, but I wouldn't swear to it.

I have to disagree with David a little bit. While it's true that the only way you should access or modify the database is the Liferay API, I have found it invaluable to study the database in lieu of actual detailed documentation for some particular feature. For instance, it's very useful when you are trying to write a DynamicQuery but you don't know the names of the fields you can search against. Or how custom document types get mapped into the various DDM tables and classes. You won't find these in the very fine books that David mentioned, so it's either wade into the database or wade into the source code, again, with the understanding that you're going to have to follow any string you find out to the approved API calls. It shouldn't be this way, but that's the state of documentation right now.

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

Joseph Toman:

For instance, it's very useful when you are trying to write a DynamicQuery but you don't know the names of the fields you can search against.

See, that's just wrong. DQ is based upon the entities, not the tables/columns. An entity can have fields not found in the particular table/column you'd think they'd be, they can have different names/types, and the fact that Liferay repurposes tables/columns means you might confuse what a column is actually for.

Or how custom document types get mapped into the various DDM tables and classes. You won't find these in the very fine books that David mentioned, so it's either wade into the database or wade into the source code, again, with the understanding that you're going to have to follow any string you find out to the approved API calls. It shouldn't be this way, but that's the state of documentation right now.

Again, this is also wrong. Documents may go to the database, but they can also go to jackrabbit (so looking at the tables won't help a bit). And honestly it doesn't matter where they end up going; when you use the API to add them, you also use the API to get them back out again.

I do understand where Joseph is coming from, though. When I start a project I too want to see the DDL because more often than not the data model provides a good overview of the project itself. I expect most projects are very tied to their data model, and most of the time they are.

That said, Liferay is a horse of a different color. They have spent years decoupling the core from the database, and while there are still ties there that are difficult if not impossible to break, the lines are so blurred that you cannot look at the database and think you really know much of anything. And whatever you may think you know for the current version may be completely different on the next Liferay release.

After over 8 years working with Liferay, trust me when I say the API is the only way to go. Forget the database is even there. Work through the API and yes, when necessary, dig into the code, but stay away from the database as it's a trap...

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

where is that many-to-many love_user_ table when you need it ... nah forget that, it is already covered thru other higher level associations which get instantiated in already present substrate definitions. Tho as I think about it, love associations might be defined/valued better in data. By example: am I in the same relationship with my wife that she has with me even tho we call it 'a' relationship ?

I think David's main message is that understanding the db should not be the starting or even an early point as one starts learning about Liferay.

[Joseph Toman，修改在9 年前。]

Are you sure that's right? Even when you are using another storage type for the actual documents, the custom field data still gets stored in the database.

After over 8 years working with Liferay, trust me when I say the API is the only way to go. Forget the database is even there. Work through the API and yes, when necessary, dig into the code, but stay away from the database as it's a trap...

Depending only on the API would be great, but when every entry in the javadoc says only "This utility wraps com.liferay.portal.xxx.impl.XXXLocalServiceImpl and is the primary access..." then you either have to a) stop programming and wait patiently for the javadoc to be improved, or b) find another solution to fill in the blanks about how the API works now. I have found that UTSL and database spelunking have answered questions for me that no published documentation, blog, wiki, or forum articles were able to answer. At the end of the day, answering the question and writing the code is what's important.

I should point out that cleaning up the javadoc is a tremendous task and the team that's working on it seems to be gaining ground, so three cheers for them!

[Hardik Pathak，修改在9 年前。]

But then there are tables like, expandocolumn where you add entries and it's important to know the column name/case. Can someone tell me where can I find the table description for expandocolumn ? Reason is, on my local when I create a custom user field, it's in lower case and in my test environment it creates upper case. And not sure if this can be any issue ?

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

Hardik Pathak:

But then there are tables like, expandocolumn where you add entries and it's important to know the column name/case. Can someone tell me where can I find the table description for expandocolumn ? Reason is, on my local when I create a custom user field, it's in lower case and in my test environment it creates upper case. And not sure if this can be any issue ?

Um, no, you don't go creating/editing records in the expandocolumn table. You never, ever manually change database records because, without knowing the internals of how things are linked, you can end up corrupting your data and bringing down the Liferay system.

If that sounds like a scary statement, well it's supposed to. It's not thrown out as some sort of remote possibility, it is actually quite easy to break your system through a simple direct database change. Some guy was just asking how to delete an org when there are still users in the org (something the interface doesn't allow). Well if that one org record, a single record in the DB, was deleted manually, his DB and therefore his liferay instance would be corrupted.

As far as your case thing is concerned, usually admins create the custom fields via the interface, they're not created via code. I'd guess that it was created in one environment using lowercase for the internal name and mixed case in another environment.

[Hardik Pathak，修改在9 年前。]

Yes, I guess we have remotely managed instance so when I created on local which was a lowercase and when the remote admin created in Test environment was upper case ! Thank you for your reply though.

[Pier Paolo Ramon，修改在9 年前。]

I don't want to look pedantic, but David has been pretty rough, while getting straight to the point. Trying to understand Liferay looking at the database is the wrong direction. The database is there just to provide a persistence layer. Have a look at the documentation. That will bring you faster on track.

Sent from my iPhone with Liferay.com Forums

[Pier Paolo Ramon，修改在9 年前。]

This discussion should be promoted to a reference for every developer looking for resources to understand Liferay

Sent from my iPhone with Liferay.com Forums

[Pier Paolo Ramon，修改在9 年前。]

Oh, BTW I'm very scared by the fact that Saleem had to answer that kind of question for an interview in the first place. Was that some kind of trick, or the interviewer just got LR completely wrong?

Sent from my iPhone with Liferay.com Forums

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

IMHO Liferay developer training is the best way forward. I've been developing over Liferay since Community Edition v5.2.3 and only did training last year... I should have done the training long ago as I would have saved myself a tremendous amount of forum spelunking.

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

I realize this is an old thread, but there is no answer aside from a very strong "I DON'T KNOW". Is there a place I can view the table information (schema, table names, etc)

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

No tom, it doesn't exist.

And the reason it doesn't is because direct database manipulation is not supported. The Liferay API takes care of all data persistence whether it is to the database, to local files or to a search appliance. Any direct manipulation of the database may potentially lead to a crashed system.

The Liferay API is published and that is the only approved method for accessing and storing data for the Liferay system.

This message is not meant to sound harsh but I'm sure it does. If such a document existed folks would start monkeying with data (bulk loads, direct record injection, external system co-modification) and Liferay environments would start breaking. Folks would come back here asking how to fix this and how to repair that, folks would be grumbling about Liferay stability when it wasn't really a Liferay issue, ... Liferay changes the database from release to release, so any information about schema would break from release to release.

The Liferay API is truly the only way to manage the data and, for all intents and purposes, you should forget the database actually exists.






Come meet me at the LSNA!

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

Does Liferay offer a data obfuscation tool in order to obfuscate sensitive production data down to dev and qc environments?

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

No, that's not available.

That's really a business issue that the contents, docs etc cannot be shared between environs. When you have this kind of situation is best to just not copy your prod db & docs to lower environments. Your devs and testers will need to generate suitable test data through Liferay (to leverage the API).

If I had to generate a slew of test data, I'd probably look at some sort of load testing tool, record scripts to drive the UI portion and then randomize data fed to the load tests.

But "obfuscating production data" is just not going to work out well. Web content, for example, is a clob column w/ xml for the content data. If you tried to obfuscate or mangle this data, you'd likely end up breaking the xml and resulting in a broken system.





Come meet me at the LSNA!

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

You stated my case for me. I do not want to break an XML column, so I need to know what's in the database so I can properly define fields for obfuscation, and write scripts to obfuscate documents with suitable replacements. It's not difficult stuff and does not break Liferay if you know what you are doing. I've inserted thousands of documents into message board attachments and document library through Oracle with no issues.

Generating fresh content is not an option, and not very reliable. It is too dissimilar from production content to be of any use.