Database Integration

[Jeffre Cox, modifié il y a 13 années.]

Hi,
I have only recently started using Liferay. My intentions are to replace a website/php/java application architecture with Liferay. I have managed to get Liferay up and running pretty easily. Some parts of the setup that are relevant to this post are as follows:

1. The database being used is Postgresql9 located on a different server than Liferay.
2. Originally I placed the Liferay tables in one schema, and the legacy tables remained in a separate schema. However, after considering this for a bit I decided that there is no real advantage in the separate schemas. So all of the Liferay tables and the 54 legacy tables from the previous architecture are in the same schema in the same data source.
3. The legacy tables are using sequences and foreign key constraints throughout.


So here are the issues I have run into:


I have started developing a new portlet that makes use of brand new tables and well as some tables from the legacy data. I have gotten things working well via service builder with the new tables, but not so much with the legacy tables. I have used these two posts as reference when trying to get everything working:

Using a legacy db with service builder
Connecting to a separate database using build service in plugins SDK

The issues I have run into are:

1) Service builder does not create foreign key constraints. I have verified this both in table.sql file that gets created as well as
this Post

So being that I would like to enforce referential integrity at the datbase level (other applications outside of Liferay will access this data), is their a way to set the tables up manually. Obviously I can just create things in the DB normally, but I really like how when the portlet is deployed the DB tables are also deployed. Can I just edit the table.sql file?

Furthermore, my tests find that when deploying the portlet the associated tables are backed up, dropped, re-created, then restored. This appears to be what is in the log anyhow. This is of issue with pre-existing tables that have referential constraints, as they won't drop. Is there a way to keep the "deploy" from loading the tables?

2) Somewhat related is that when developing the service.xml file and adding an entity which corresponds to an existing table, I have used the table="myTableName" attribute, however I have not set my own transaction mangers, etc... I assume in the above posts concerning legacy database integration these things were necessary because there was a connection to a different data source. In my instance I am connecting to the liferay data source. Am I wrong in this? Though the legacy data interface is done using hibernate, I am not particularly familiar with Spring. This is what I thought was the correct course of action to get service builder (and corresponding generated service code) to work with the existing tables.

3) Sequences... Though I can configure the service builder to use sequences for incrementing ID's, it doesn't seem to construct the database tables to auto increment. Something that is desired... If I use the "getID" mechanism that is injected into my "impl" code, will it increment the sequence in the database?


Any help or suggestions would be tremendous!!


Jeff

[Minhchau Dang, modifié il y a 13 années.]

Jeffre Cox:

Obviously I can just create things in the DB normally, but I really like how when the portlet is deployed the DB tables are also deployed. Can I just edit the table.sql file?

Since tables.sql gets regenerated each time you run service builder, it may be easier to simply use an upgrade process (which may require some custom patching to work for future deployments in 6.0.5, as mentioned in this post) which runs the database vendor specific SQL to add a foreign key constraint.

Jeffre Cox:

Is there a way to keep the "deploy" from loading the tables?

If you mean, "prevent Liferay from reloading the table for just this portlet, but still create it the first time it's deployed," then if you edit service.properties and set the "build.auto.upgrade" property to false, then Liferay shouldn't auto upgrade your plugin using the default SQL scripts. Not sure if it prevents upgrade processes from running, however.

If you want Liferay to never do it for any plugin (which may be true in your environment if you're managing your own tables), you would need to create a service hook which wraps ServiceComponentLocalService and doesn't do anything on initServiceComponent or upgradeDB.

Jeffre Cox:

I assume in the above posts concerning legacy database integration these things were necessary because there was a connection to a different data source. In my instance I am connecting to the liferay data source. Am I wrong in this?

No, you are correct. If you are using the same database connection and connecting to the same database as Liferay, you will not need to configure another transaction manager if you are willing to trust the way Liferay itself handles its own transactions.

Jeffre Cox:

Though I can configure the service builder to use sequences for incrementing ID's, it doesn't seem to construct the database tables to auto increment. Something that is desired...

I'm not really 100% sure what you're asking (actually, since I know almost nothing about sequences, I could probably say 0%). I haven't seen a populated sequences.sql before so I don't know what the service builder DTD is describing.

From the Hibernate informal documentation, it sounds like PostgreSQL supports identity generators, so if all you need is an auto-incrementing serial column, Hibernate should be able to figure it out based on the generated Hibernate mapping file for an id-type="identity" in service builder if you pre-create the table.

That being said, maybe what you're trying to point out is that the tables.sql won't have the right column definition since we don't do any special replaces for identity columns (we create it as a regular integer/long as opposed to a serial), so allowing Liferay to auto-create the tables wouldn't work? That looks to be the case, but maybe you're talking specifically about sequences.

[Jeffre Cox, modifié il y a 13 années.]

Since tables.sql gets regenerated each time you run service builder, it may be easier to simply use an upgrade process (which may require some custom patching to work for future deployments in 6.0.5, as mentioned in this post) which runs the database vendor specific SQL to add a foreign key constraint.

This appears very promising. However if I understand things correctly, I would need to pull down the Liferay source and then patch it for everything to work? Less excited about that, but doable. Another path I was entertaining was to create my own sql file called "tables-ext.sql" and then update the build.xml file such that when ever the service builder task is run, the generated "tables.sql" gets overwritten by the "tables-ext.sql". However, I am unsure about where the "public static final String TABLE_SQL_CREATE" variables come into play...

If you mean, "prevent Liferay from reloading the table for just this portlet, but still create it the first time it's deployed," then if you edit service.properties and set the "build.auto.upgrade" property to false, then Liferay shouldn't auto upgrade your plugin using the default SQL scripts. Not sure if it prevents upgrade processes from running, however.

If you want Liferay to never do it for any plugin (which may be true in your environment if you're managing your own tables), you would need to create a service hook which wraps ServiceComponentLocalService and doesn't do anything on initServiceComponent or upgradeDB

Nope not looking for every plugin, just the one being deployed. Just looking for the worst case scenario. Such that I could manually create all the necessary tables in outside of the plugin deployment. With the hope that I could use service builder to generate the data access/service code. Then deploy the portlet and all deployment tasks occur other than creating the tables. Are you saying that if I set "build.auto.upgrade" to false when I deploy, it will skip only the table creation? if so this maybe be the best path. I could update the deploy task to run my own table creation scripts.

I'm not really 100% sure what you're asking (actually, since I know almost nothing about sequences, I could probably say 0%). I haven't seen a populated sequences.sql before so I don't know what the service builder DTD is describing.

Well when using id-type="sequence" id-param="mytable_seq", service builder will generate the proper sequence name in sequences.sql. Then during the upgrade process, will create those sequences in the database. However, those sequences being used to auto-increment the corresponding table doesn't seem to happen Certainly when the table is created it is not created with the proper default value of nextval('my_seq'). I even tried to use the portal-model-hints-ext.xml file to provide a default-value hint, but didn't seem to pick up the hint.

From the Hibernate informal documentation, it sounds like PostgreSQL supports identity generators, so if all you need is an auto-incrementing serial column, Hibernate should be able to figure it out based on the generated Hibernate mapping file for an id-type="identity" in service builder if you pre-create the table.

My experience with using hibernate and postgres is that I just need to define the column as type serial and all works out, so this sounds promising if I can get the deployment not to load the schema service builder creates. In other words if I can stop the table creation step of the deployment, then the generated data access code should still work with the pre-created tables. Though I have noticed all of the ".create" methods seem to require and id, which should not be required if using an auto-incrementing ID. That said, maybe I will just need to instantiate a new object without and ID and then use ".update" to persist.

That being said, maybe what you're trying to point out is that the tables.sql won't have the right column definition since we don't do any special replaces for identity columns (we create it as a regular integer/long as opposed to a serial), so allowing Liferay to auto-create the tables wouldn't work? That looks to be the case, but maybe you're talking specifically about sequences.

Yes. Since the identity column will be of type integer/long (actually in postgres this becomes a bigint if you use a long) the generated sql will not create table correctly. One can still use the sequences to update the identity by making the proper db call, returning the next value of the sequence then using it as the new rows ID value. This however can be tricky. If when creating the table the identity column type is 'serial' then the ID column is auto incremented. Mind you the 'serial' type is just a 'macro' for setting the default value of the identity to 'nextval('sequence_name').


Thanks so much for the response. It is certainly quite helpful and gives me a few other things to try!!


Jeff

[Jens Göring, modifié il y a 13 années.]

Concerning the creation of the foreign keys you might want to take a look at liquibase (http://www.liquibase.org/) instead of altering the generated sql or using liferay's upgrade process.

[Jeffre Cox, modifié il y a 13 années.]

Jens,
Wow liquibase looks very, very useful. I would like to use this. Do you use it within the context of a portlet? if so, how do you use it? Do you somehow turn off the database creation aspects of deploying a portlet?


Jeff

[Jens Göring, modifié il y a 13 années.]

Our solution probably does not apply to your system: We still use the ext environment and have all our services there. That way it was easy to integrate Liquibase by adding it to the web.xml in the ext environment. We do not deploy any portlets that contain services.

(Don't take this as a recommendation to use the ext environment - we just did not migrate to plugin api yet.)

If there is any liferay automatism that alters table structure on portlet deploy (or elsewhere), I would completly turn it off and change the database with liquibase only to keep complete control.

There should be no problem integrating liquibase via it's servlet listener in the web.xml of a portlet plugin. It should even be no problem to have multiple portlets with multiple liquibase changesets each responsible for changes to their own tables since liquibase locks the database and won't execute any changeset twice.