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)" <ji...@apache.org> on 2011/01/11 17:27:46 UTC
[jira] Commented: (MAILBOX-14) Name JPA Database Tables and Columns
[ https://issues.apache.org/jira/browse/MAILBOX-14?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12980165#action_12980165 ]
Eric Charles commented on MAILBOX-14:
-------------------------------------
You can see a first shot on james-jpa-db-1-without-mailbox-fk.png (https://issues.apache.org/jira/secure/attachment/12468023/james-jpa-db-1-without-mailbox-fk.png)
- All tables are prefixed with JAMES_
- Columns names are prefixed with a "context" (example: JAMES_MAIL_PROPERTY columns all begin with PROPERTY). I find this often useful even if it obliges to type a bit more in the SQL queries, but to be discussed.
- PK and FK are suffixed with _SID (System Identitifer)
The column length are often arbitrary. We need to review them, and also maybe have a constant for the default value.
What about the @Basic(optional=false) and @Column(... nullable = false, ...): It's double if I understand well. which one should we use ? (I would go for the @Column nullable)
Other point is the potential FK between JAMES_MAILBOX and JAMES_MAILBOX_MEMBERSHIP (see https://issues.apache.org/jira/secure/attachment/12468024/james-jpa-db-1-with-mailbox-fk.png)
I defined the annotations, updated the queries, but didn't succeed to make it work completely.
With a bit more investment, I guess it would be possible to let it work.
However, I saw that adding that FK would oblige JPA to make more queries (load the Mailbox entities) -> we could loose some performance.
On a datamodel level, the FK is welcome. On a performance, it is not sure.
WDYT?
> Name JPA Database Tables and Columns
> ------------------------------------
>
> Key: MAILBOX-14
> URL: https://issues.apache.org/jira/browse/MAILBOX-14
> Project: James Mailbox
> Issue Type: Improvement
> Reporter: Eric Charles
> Attachments: james-jpa-db-1-with-mailbox-fk.png, james-jpa-db-1-without-mailbox-fk.png
>
>
> The database tables and columns naming are delegated to OpenJPA. This depends on the database.
> We should fix them via JPA annotations so we have predictive and common behaviour on all databases (mysql, oracle,...).
> We should respect the uppercase and underscore pattern (TABLE_NAME, COLUMN_NAME).
> All JPA Domain classes need to be adapted (mailbox) in IMAP, but also in server.
--
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
Re: [jira] Commented: (MAILBOX-14) Name JPA Database Tables and Columns
Posted by Eric Charles <er...@apache.org>.
Hi Andrew,
Many tks for feedbacks.
Seems that Oracle has also concerns with 10240
(http://markmail.org/thread/3rm7xhlofcgvdaq7), it's even going lower
than MS-SQL, to 4000... :)
Tks,
Eric
On 11/01/2011 17:47, Andrew S. Tomlinson wrote:
> I'm glad to see the prefix on columns so we avoid reserved words e.g. "user"
> in MSSQL. Ditto for the table name prefix so I can apply this to my existing
> DB without conflict.
>
> On column sizes please change JAMES_MAIL_HEADER.HEADER_VALUE FROM 10240 TO
> 8000 as this is the maximum size allowed for varchar in MS SQL.
> The header_value used to be 1024 and then was increased to what seems to be
> an arbitrarily larger value but it kills MSSQL.
>
>
> Andrew S. Tomlinson
> ---------------------------------------------------------------------
> This message contains confidential and proprietary information of the
> sender, and is intended only for the person(s) to whom it is addressed. Any
> use, distribution, copying or disclosure by any other person is strictly
> prohibited. If you have received this message in error, please notify the
> e-mail sender immediately, and delete the original message without making a
> copy.
>
> -----Original Message-----
> From: Eric Charles (JIRA) [mailto:jira@apache.org]
> Sent: January-11-11 11:28 AM
> To: server-dev@james.apache.org
> Subject: [jira] Commented: (MAILBOX-14) Name JPA Database Tables and Columns
>
>
> [
> https://issues.apache.org/jira/browse/MAILBOX-14?page=com.atlassian.jira.plu
> gin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12980165#action_
> 12980165 ]
>
> Eric Charles commented on MAILBOX-14:
> -------------------------------------
>
> You can see a first shot on james-jpa-db-1-without-mailbox-fk.png
> (https://issues.apache.org/jira/secure/attachment/12468023/james-jpa-db-1-wi
> thout-mailbox-fk.png)
>
> - All tables are prefixed with JAMES_
> - Columns names are prefixed with a "context" (example: JAMES_MAIL_PROPERTY
> columns all begin with PROPERTY). I find this often useful even if it
> obliges to type a bit more in the SQL queries, but to be discussed.
> - PK and FK are suffixed with _SID (System Identitifer)
>
> The column length are often arbitrary. We need to review them, and also
> maybe have a constant for the default value.
>
> What about the @Basic(optional=false) and @Column(... nullable = false,
> ...): It's double if I understand well. which one should we use ? (I would
> go for the @Column nullable)
>
> Other point is the potential FK between JAMES_MAILBOX and
> JAMES_MAILBOX_MEMBERSHIP (see
> https://issues.apache.org/jira/secure/attachment/12468024/james-jpa-db-1-wit
> h-mailbox-fk.png)
> I defined the annotations, updated the queries, but didn't succeed to make
> it work completely.
> With a bit more investment, I guess it would be possible to let it work.
> However, I saw that adding that FK would oblige JPA to make more queries
> (load the Mailbox entities) -> we could loose some performance.
> On a datamodel level, the FK is welcome. On a performance, it is not sure.
>
> WDYT?
>
>
>> Name JPA Database Tables and Columns
>> ------------------------------------
>>
>> Key: MAILBOX-14
>> URL: https://issues.apache.org/jira/browse/MAILBOX-14
>> Project: James Mailbox
>> Issue Type: Improvement
>> Reporter: Eric Charles
>> Attachments: james-jpa-db-1-with-mailbox-fk.png,
> james-jpa-db-1-without-mailbox-fk.png
>>
>> The database tables and columns naming are delegated to OpenJPA. This
> depends on the database.
>> We should fix them via JPA annotations so we have predictive and common
> behaviour on all databases (mysql, oracle,...).
>> We should respect the uppercase and underscore pattern (TABLE_NAME,
> COLUMN_NAME).
>> All JPA Domain classes need to be adapted (mailbox) in IMAP, but also in
> server.
>
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
RE: [jira] Commented: (MAILBOX-14) Name JPA Database Tables and Columns
Posted by "Andrew S. Tomlinson" <an...@excelsis.ca>.
I'm glad to see the prefix on columns so we avoid reserved words e.g. "user"
in MSSQL. Ditto for the table name prefix so I can apply this to my existing
DB without conflict.
On column sizes please change JAMES_MAIL_HEADER.HEADER_VALUE FROM 10240 TO
8000 as this is the maximum size allowed for varchar in MS SQL.
The header_value used to be 1024 and then was increased to what seems to be
an arbitrarily larger value but it kills MSSQL.
Andrew S. Tomlinson
---------------------------------------------------------------------
This message contains confidential and proprietary information of the
sender, and is intended only for the person(s) to whom it is addressed. Any
use, distribution, copying or disclosure by any other person is strictly
prohibited. If you have received this message in error, please notify the
e-mail sender immediately, and delete the original message without making a
copy.
-----Original Message-----
From: Eric Charles (JIRA) [mailto:jira@apache.org]
Sent: January-11-11 11:28 AM
To: server-dev@james.apache.org
Subject: [jira] Commented: (MAILBOX-14) Name JPA Database Tables and Columns
[
https://issues.apache.org/jira/browse/MAILBOX-14?page=com.atlassian.jira.plu
gin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12980165#action_
12980165 ]
Eric Charles commented on MAILBOX-14:
-------------------------------------
You can see a first shot on james-jpa-db-1-without-mailbox-fk.png
(https://issues.apache.org/jira/secure/attachment/12468023/james-jpa-db-1-wi
thout-mailbox-fk.png)
- All tables are prefixed with JAMES_
- Columns names are prefixed with a "context" (example: JAMES_MAIL_PROPERTY
columns all begin with PROPERTY). I find this often useful even if it
obliges to type a bit more in the SQL queries, but to be discussed.
- PK and FK are suffixed with _SID (System Identitifer)
The column length are often arbitrary. We need to review them, and also
maybe have a constant for the default value.
What about the @Basic(optional=false) and @Column(... nullable = false,
...): It's double if I understand well. which one should we use ? (I would
go for the @Column nullable)
Other point is the potential FK between JAMES_MAILBOX and
JAMES_MAILBOX_MEMBERSHIP (see
https://issues.apache.org/jira/secure/attachment/12468024/james-jpa-db-1-wit
h-mailbox-fk.png)
I defined the annotations, updated the queries, but didn't succeed to make
it work completely.
With a bit more investment, I guess it would be possible to let it work.
However, I saw that adding that FK would oblige JPA to make more queries
(load the Mailbox entities) -> we could loose some performance.
On a datamodel level, the FK is welcome. On a performance, it is not sure.
WDYT?
> Name JPA Database Tables and Columns
> ------------------------------------
>
> Key: MAILBOX-14
> URL: https://issues.apache.org/jira/browse/MAILBOX-14
> Project: James Mailbox
> Issue Type: Improvement
> Reporter: Eric Charles
> Attachments: james-jpa-db-1-with-mailbox-fk.png,
james-jpa-db-1-without-mailbox-fk.png
>
>
> The database tables and columns naming are delegated to OpenJPA. This
depends on the database.
> We should fix them via JPA annotations so we have predictive and common
behaviour on all databases (mysql, oracle,...).
> We should respect the uppercase and underscore pattern (TABLE_NAME,
COLUMN_NAME).
> All JPA Domain classes need to be adapted (mailbox) in IMAP, but also in
server.
--
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
---------------------------------------------------------------------
To unsubscribe, e-mail: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org