You are viewing a plain text version of this content. The canonical link for it is here.
Posted to server-dev@james.apache.org by "Eric Charles (JIRA)" <se...@james.apache.org> on 2010/07/23 15:12:50 UTC
[jira] Created: (IMAP-181) Implement database schema and data
incremental migration for 3.0 release
Implement database schema and data incremental migration for 3.0 release
------------------------------------------------------------------------
Key: IMAP-181
URL: https://issues.apache.org/jira/browse/IMAP-181
Project: JAMES Imap
Issue Type: New Feature
Components: JPA Mailbox
Environment: all
Reporter: Eric Charles
We need a way to upgrade the database (for JPA store) whenever it is needed.
In general, a database upgrade is needed further a JIRA that impacts the database.
This can be:
1. On a db schema level (table, columns, fk, index,...)
2. On data level (update some data, feed a column with relation keys,...).
Plain SQL would be enough for the first point (not for the second one). PLSQL (or similar) would be needed for the second point.
To ease things, a java "software" process will developed based on the existing JPA layer.
The tool should be invoked with JIRA numbers as parameters.
Each provided JIRA number would invoke a DB Change Command.
As POC (Proof of Concept), we will first develop a tool that takes into account.
- https://issues.apache.org/jira/browse/IMAP-176
- https://issues.apache.org/jira/browse/IMAP-172
- https://issues.apache.org/jira/browse/IMAP-168
- https://issues.apache.org/jira/browse/IMAP-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
[jira] Updated: (IMAP-181) Implement database schema and data
incremental migration for 3.0 release
Posted by "Eric Charles (JIRA)" <se...@james.apache.org>.
[ https://issues.apache.org/jira/browse/IMAP-181?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Eric Charles updated IMAP-181:
------------------------------
Attachment: jpa-migrator-2.zip
jpa-migrator-2.zip for review as candidate for new mvn project "jpa-migrator" under current imap directory (in // to jpa mvn project).
Functional as such (tested on real data) for 5 jiras.
Uses much native SQL (don't see any other solution)
Enhancements will be needed:
- Extract the SQL in JAVA classes to XML file.
- Log with log4j.
Waiting for a "go" to commit in trunk.
> Implement database schema and data incremental migration for 3.0 release
> ------------------------------------------------------------------------
>
> Key: IMAP-181
> URL: https://issues.apache.org/jira/browse/IMAP-181
> Project: JAMES Imap
> Issue Type: New Feature
> Components: JPA Mailbox
> Environment: all
> Reporter: Eric Charles
> Assignee: Eric Charles
> Attachments: jpa-migrator-2.zip, jpa-migrator.zip
>
>
> We need a way to upgrade the database (for JPA store) whenever it is needed.
> In general, a database upgrade is needed further a JIRA that impacts the database.
> This can be:
> 1. On a db schema level (table, columns, fk, index,...)
> 2. On data level (update some data, feed a column with relation keys,...).
> Plain SQL would be enough for the first point (not for the second one). PLSQL (or similar) would be needed for the second point.
> To ease things, a java "software" process will developed based on the existing JPA layer.
> The tool should be invoked with JIRA numbers as parameters.
> Each provided JIRA number would invoke a DB Change Command.
> As POC (Proof of Concept), we will first develop a tool that takes into account.
> - https://issues.apache.org/jira/browse/IMAP-176
> - https://issues.apache.org/jira/browse/IMAP-172
> - https://issues.apache.org/jira/browse/IMAP-168
> - https://issues.apache.org/jira/browse/IMAP-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
[jira] Updated: (IMAP-181) Implement database schema and data
incremental migration for 3.0 release
Posted by "Eric Charles (JIRA)" <se...@james.apache.org>.
[ https://issues.apache.org/jira/browse/IMAP-181?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Eric Charles updated IMAP-181:
------------------------------
Attachment: jpa-migrator-3.zip
Add migration for IMAP-184
> Implement database schema and data incremental migration for 3.0 release
> ------------------------------------------------------------------------
>
> Key: IMAP-181
> URL: https://issues.apache.org/jira/browse/IMAP-181
> Project: JAMES Imap
> Issue Type: New Feature
> Components: JPA Mailbox
> Environment: all
> Reporter: Eric Charles
> Assignee: Eric Charles
> Attachments: jpa-migrator-2.zip, jpa-migrator-3.zip, jpa-migrator.zip
>
>
> We need a way to upgrade the database (for JPA store) whenever it is needed.
> In general, a database upgrade is needed further a JIRA that impacts the database.
> This can be:
> 1. On a db schema level (table, columns, fk, index,...)
> 2. On data level (update some data, feed a column with relation keys,...).
> Plain SQL would be enough for the first point (not for the second one). PLSQL (or similar) would be needed for the second point.
> To ease things, a java "software" process will developed based on the existing JPA layer.
> The tool should be invoked with JIRA numbers as parameters.
> Each provided JIRA number would invoke a DB Change Command.
> As POC (Proof of Concept), we will first develop a tool that takes into account.
> - https://issues.apache.org/jira/browse/IMAP-176
> - https://issues.apache.org/jira/browse/IMAP-172
> - https://issues.apache.org/jira/browse/IMAP-168
> - https://issues.apache.org/jira/browse/IMAP-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
[jira] Commented: (IMAP-181) Implement database schema and data
incremental migration for 3.0 release
Posted by "Eric Charles (JIRA)" <se...@james.apache.org>.
[ https://issues.apache.org/jira/browse/IMAP-181?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12893741#action_12893741 ]
Eric Charles commented on IMAP-181:
-----------------------------------
Tks for your feedback Tim.
I corrected the typo.
I wouldn't omit IMAP because you can also have JAMES-...
About the jira interval, I thought to introduce some dependencies (say for example IMAP-168 before IMAP-165). The migration must sometimes respect an order which is not necessarily a numeric order.
We could also extend the requirement to 'upgrade db schema to version x", where x wouldn't necessarily nor follow the james release. This would allow easier database migration between releases (say for developers deploying trunk with real users).
But we also have to take care to not re-develop existing tools (such as http://www.liquibase.org/) and to monitor the jpa evolution on schema synchronization.
> Implement database schema and data incremental migration for 3.0 release
> ------------------------------------------------------------------------
>
> Key: IMAP-181
> URL: https://issues.apache.org/jira/browse/IMAP-181
> Project: JAMES Imap
> Issue Type: New Feature
> Components: JPA Mailbox
> Environment: all
> Reporter: Eric Charles
> Assignee: Eric Charles
> Attachments: jpa-migrator-2.zip, jpa-migrator.zip
>
>
> We need a way to upgrade the database (for JPA store) whenever it is needed.
> In general, a database upgrade is needed further a JIRA that impacts the database.
> This can be:
> 1. On a db schema level (table, columns, fk, index,...)
> 2. On data level (update some data, feed a column with relation keys,...).
> Plain SQL would be enough for the first point (not for the second one). PLSQL (or similar) would be needed for the second point.
> To ease things, a java "software" process will developed based on the existing JPA layer.
> The tool should be invoked with JIRA numbers as parameters.
> Each provided JIRA number would invoke a DB Change Command.
> As POC (Proof of Concept), we will first develop a tool that takes into account.
> - https://issues.apache.org/jira/browse/IMAP-176
> - https://issues.apache.org/jira/browse/IMAP-172
> - https://issues.apache.org/jira/browse/IMAP-168
> - https://issues.apache.org/jira/browse/IMAP-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
[jira] Issue Comment Edited: (IMAP-181) Implement database schema
and data incremental migration for 3.0 release
Posted by "Tim-Christian Mundt (JIRA)" <se...@james.apache.org>.
[ https://issues.apache.org/jira/browse/IMAP-181?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12893642#action_12893642 ]
Tim-Christian Mundt edited comment on IMAP-181 at 7/29/10 10:54 AM:
--------------------------------------------------------------------
Eric, I didn't test, but read through your code and it looks good. I don't think the SQL should cause any problem, because it's not really specialized. Maybe you could introduce a little convenience: "jpa-migrator 165-180" would be equivalent to "jpa-migrator 165 168 172 176 180" (I'd omit the "IMAP").
Thanks for this handy tool.
PS: There's a little typo in the 168 command:
if (! name.startsWith("#mail")) {
throw new IllegalArgumentException("The name must begin with #name");
}
Should be #mail not #name
was (Author: tcm):
Eric, I didn't test, but read through your code and it looks good. I don't think the SQL should cause any problem, because it's not really specialized. Maybe you could introduce a little convenience: "jpa-migrator 165-180" would be equivalent to "jpa-migrator 165 168 172 176 180" (I'd omit the "IMAP").
Thanks for this handy tool.
> Implement database schema and data incremental migration for 3.0 release
> ------------------------------------------------------------------------
>
> Key: IMAP-181
> URL: https://issues.apache.org/jira/browse/IMAP-181
> Project: JAMES Imap
> Issue Type: New Feature
> Components: JPA Mailbox
> Environment: all
> Reporter: Eric Charles
> Assignee: Eric Charles
> Attachments: jpa-migrator-2.zip, jpa-migrator.zip
>
>
> We need a way to upgrade the database (for JPA store) whenever it is needed.
> In general, a database upgrade is needed further a JIRA that impacts the database.
> This can be:
> 1. On a db schema level (table, columns, fk, index,...)
> 2. On data level (update some data, feed a column with relation keys,...).
> Plain SQL would be enough for the first point (not for the second one). PLSQL (or similar) would be needed for the second point.
> To ease things, a java "software" process will developed based on the existing JPA layer.
> The tool should be invoked with JIRA numbers as parameters.
> Each provided JIRA number would invoke a DB Change Command.
> As POC (Proof of Concept), we will first develop a tool that takes into account.
> - https://issues.apache.org/jira/browse/IMAP-176
> - https://issues.apache.org/jira/browse/IMAP-172
> - https://issues.apache.org/jira/browse/IMAP-168
> - https://issues.apache.org/jira/browse/IMAP-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
[jira] Commented: (IMAP-181) Implement database schema and data
incremental migration for 3.0 release
Posted by "Eric Charles (JIRA)" <se...@james.apache.org>.
[ https://issues.apache.org/jira/browse/IMAP-181?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12896343#action_12896343 ]
Eric Charles commented on IMAP-181:
-----------------------------------
First commit: http://svn.apache.org/viewvc?rev=983390&view=rev
Still to do:
- Extract the SQL from JAVA classes to XML file.
- Log with log4j.
> Implement database schema and data incremental migration for 3.0 release
> ------------------------------------------------------------------------
>
> Key: IMAP-181
> URL: https://issues.apache.org/jira/browse/IMAP-181
> Project: JAMES Imap
> Issue Type: New Feature
> Components: JPA Mailbox
> Environment: all
> Reporter: Eric Charles
> Assignee: Eric Charles
> Attachments: jpa-migrator-2.zip, jpa-migrator-3.zip, jpa-migrator.zip
>
>
> We need a way to upgrade the database (for JPA store) whenever it is needed.
> In general, a database upgrade is needed further a JIRA that impacts the database.
> This can be:
> 1. On a db schema level (table, columns, fk, index,...)
> 2. On data level (update some data, feed a column with relation keys,...).
> Plain SQL would be enough for the first point (not for the second one). PLSQL (or similar) would be needed for the second point.
> To ease things, a java "software" process will developed based on the existing JPA layer.
> The tool should be invoked with JIRA numbers as parameters.
> Each provided JIRA number would invoke a DB Change Command.
> As POC (Proof of Concept), we will first develop a tool that takes into account.
> - https://issues.apache.org/jira/browse/IMAP-176
> - https://issues.apache.org/jira/browse/IMAP-172
> - https://issues.apache.org/jira/browse/IMAP-168
> - https://issues.apache.org/jira/browse/IMAP-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
[jira] Commented: (IMAP-181) Implement database schema and data
incremental migration for 3.0 release
Posted by "Tim-Christian Mundt (JIRA)" <se...@james.apache.org>.
[ https://issues.apache.org/jira/browse/IMAP-181?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12893642#action_12893642 ]
Tim-Christian Mundt commented on IMAP-181:
------------------------------------------
Eric, I didn't test, but read through your code and it looks good. I don't think the SQL should cause any problem, because it's not really specialized. Maybe you could introduce a little convenience: "jpa-migrator 165-180" would be equivalent to "jpa-migrator 165 168 172 176 180" (I'd omit the "IMAP").
Thanks for this handy tool.
> Implement database schema and data incremental migration for 3.0 release
> ------------------------------------------------------------------------
>
> Key: IMAP-181
> URL: https://issues.apache.org/jira/browse/IMAP-181
> Project: JAMES Imap
> Issue Type: New Feature
> Components: JPA Mailbox
> Environment: all
> Reporter: Eric Charles
> Assignee: Eric Charles
> Attachments: jpa-migrator-2.zip, jpa-migrator.zip
>
>
> We need a way to upgrade the database (for JPA store) whenever it is needed.
> In general, a database upgrade is needed further a JIRA that impacts the database.
> This can be:
> 1. On a db schema level (table, columns, fk, index,...)
> 2. On data level (update some data, feed a column with relation keys,...).
> Plain SQL would be enough for the first point (not for the second one). PLSQL (or similar) would be needed for the second point.
> To ease things, a java "software" process will developed based on the existing JPA layer.
> The tool should be invoked with JIRA numbers as parameters.
> Each provided JIRA number would invoke a DB Change Command.
> As POC (Proof of Concept), we will first develop a tool that takes into account.
> - https://issues.apache.org/jira/browse/IMAP-176
> - https://issues.apache.org/jira/browse/IMAP-172
> - https://issues.apache.org/jira/browse/IMAP-168
> - https://issues.apache.org/jira/browse/IMAP-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
[jira] Updated: (IMAP-181) Implement database schema and data
incremental migration for 3.0 release
Posted by "Eric Charles (JIRA)" <se...@james.apache.org>.
[ https://issues.apache.org/jira/browse/IMAP-181?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Eric Charles updated IMAP-181:
------------------------------
Attachment: jpa-migrator.zip
Very first draft uncomplete of jpa migrator project.
Comment welcome on the proposed approach:
- separate project
- main with list of jiras
Solutions such as http://www.liquibase.org/ may be a more solid approach, but we may need more flexibility, especially on the data level.
I suppore jpa will in the future better handle the migration and schema changes.
- https://issues.apache.org/jira/browse/IMAP-180 : Need manuall recreate the fk one by one (intermediary tables dissapear)
- https://issues.apache.org/jira/browse/IMAP-176 : Need to change #mail with #private in mailbox.namespace column
- https://issues.apache.org/jira/browse/IMAP-172 : Need to remove messagecount and size columns from mailbox
- https://issues.apache.org/jira/browse/IMAP-168 : Does seem to impact database but not sure. Need confirmation ?
- https://issues.apache.org/jira/browse/IMAP-165 : JPA doesn't incrementally create the @Index. So we need to create manually
> Implement database schema and data incremental migration for 3.0 release
> ------------------------------------------------------------------------
>
> Key: IMAP-181
> URL: https://issues.apache.org/jira/browse/IMAP-181
> Project: JAMES Imap
> Issue Type: New Feature
> Components: JPA Mailbox
> Environment: all
> Reporter: Eric Charles
> Assignee: Eric Charles
> Attachments: jpa-migrator.zip
>
>
> We need a way to upgrade the database (for JPA store) whenever it is needed.
> In general, a database upgrade is needed further a JIRA that impacts the database.
> This can be:
> 1. On a db schema level (table, columns, fk, index,...)
> 2. On data level (update some data, feed a column with relation keys,...).
> Plain SQL would be enough for the first point (not for the second one). PLSQL (or similar) would be needed for the second point.
> To ease things, a java "software" process will developed based on the existing JPA layer.
> The tool should be invoked with JIRA numbers as parameters.
> Each provided JIRA number would invoke a DB Change Command.
> As POC (Proof of Concept), we will first develop a tool that takes into account.
> - https://issues.apache.org/jira/browse/IMAP-176
> - https://issues.apache.org/jira/browse/IMAP-172
> - https://issues.apache.org/jira/browse/IMAP-168
> - https://issues.apache.org/jira/browse/IMAP-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
[jira] Assigned: (IMAP-181) Implement database schema and data
incremental migration for 3.0 release
Posted by "Norman Maurer (JIRA)" <se...@james.apache.org>.
[ https://issues.apache.org/jira/browse/IMAP-181?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Norman Maurer reassigned IMAP-181:
----------------------------------
Assignee: Eric Charles
> Implement database schema and data incremental migration for 3.0 release
> ------------------------------------------------------------------------
>
> Key: IMAP-181
> URL: https://issues.apache.org/jira/browse/IMAP-181
> Project: JAMES Imap
> Issue Type: New Feature
> Components: JPA Mailbox
> Environment: all
> Reporter: Eric Charles
> Assignee: Eric Charles
>
> We need a way to upgrade the database (for JPA store) whenever it is needed.
> In general, a database upgrade is needed further a JIRA that impacts the database.
> This can be:
> 1. On a db schema level (table, columns, fk, index,...)
> 2. On data level (update some data, feed a column with relation keys,...).
> Plain SQL would be enough for the first point (not for the second one). PLSQL (or similar) would be needed for the second point.
> To ease things, a java "software" process will developed based on the existing JPA layer.
> The tool should be invoked with JIRA numbers as parameters.
> Each provided JIRA number would invoke a DB Change Command.
> As POC (Proof of Concept), we will first develop a tool that takes into account.
> - https://issues.apache.org/jira/browse/IMAP-176
> - https://issues.apache.org/jira/browse/IMAP-172
> - https://issues.apache.org/jira/browse/IMAP-168
> - https://issues.apache.org/jira/browse/IMAP-165
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org