Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@openjpa.apache.org
Received-SPF: pass (nike.apache.org: local policy)
Subject: Re: [jira] Resolved: (OPENJPA-455) Incorrect MySQL DDL Generation
	for integer types
From: Tim Holloway <timh@mousetech.com>
To: dev@openjpa.apache.org
In-Reply-To: <7262f25e0711290126g4009a0f2x2d84dc8f785ee3da@mail.gmail.com>
References: <5273452.1196291744439.JavaMail.jira@brutus>
	 <1196302546.22005.25.camel@a64.camera.mousetech.com>
	 <7262f25e0711290126g4009a0f2x2d84dc8f785ee3da@mail.gmail.com>
Content-Type: text/plain
Date: Thu, 29 Nov 2007 19:00:31 -0500
Message-Id: <1196380831.22005.30.camel@a64.camera.mousetech.com>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2007-11-29 at 16:26 +0700, Patrick Linskey wrote:
> Hi,
> 
> Thanks for the analysis.
> 

You're welcome.

> > Come to think of it, this also implies that a column insert operation
> > would be flawed. Which, I've just verified, is true.
> 
> What do you mean by this?

If you manually add column definitions to a schema.xml in the middle of
a table definition, the columns will be added to the end of the actual
database schema, not inserted between existing columns in the same order
as the schema indicates.

> 
> -Patrick
> 
> On Nov 29, 2007 9:15 AM, Tim Holloway <timh@mousetech.com> wrote:
> > That was fast!
> >
> > I tried it and eyeballed the results - haven't had a chance to feed it
> > to a live database yet, but it looks good. Only possible problem is I'm
> > not sure if the schema XML type-name attributes are OK, but that's just
> > because I'm not sure what "OK" was supposed to look like anyway.
> >
> > Observation:
> >
> > If you modify a schema.xml to rename a field, the results of the
> > schematool are exactly as advertised. However, "as advertised" means
> > that the operation is performed as an alter table Drop Field followed by
> > an alter table Add Field.
> >
> > The only problem with this is that since SQL databases have a specific
> > column order, and the Add Field adds the newly-renamed field to the end
> > of the table column list, the results are not the same as a true column
> > rename operation - and the schema XML file won't agree with the actual
> > schema on column order.
> >
> > This merits consideration for a future schematool release. Depending on
> > the DBMS, a more accurate operation would be:
> >
> > 1. Alter table rename column
> >
> > 2. Alter table Drop column; alter table Insert Column before/after
> > original-predecessor-or-successor-column
> >
> > 3. Alter table Drop Column; Alter table Insert Column (at end) because
> > your DBMS isn't smart enough to do better. Assuming that even these
> > operations are possible on it.
> >
> > Come to think of it, this also implies that a column insert operation
> > would be flawed. Which, I've just verified, is true.
> >
> >   Tim
> >
> >
> >
> > On Wed, 2007-11-28 at 15:15 -0800, Michael Dick (JIRA) wrote:
> > > [ https://issues.apache.org/jira/browse/OPENJPA-455?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
> > >
> > > Michael Dick resolved OPENJPA-455.
> > > ----------------------------------
> > >
> > >        Resolution: Fixed
> > >     Fix Version/s: 1.1.0
> > >          Assignee: Michael Dick
> > >
> > > > Incorrect MySQL DDL Generation for integer types
> > > > ------------------------------------------------
> > > >
> > > >                 Key: OPENJPA-455
> > > >                 URL: https://issues.apache.org/jira/browse/OPENJPA-455
> > > >             Project: OpenJPA
> > > >          Issue Type: Bug
> > > >    Affects Versions: 1.0.0, 1.0.1
> > > >            Reporter: Michael Dick
> > > >            Assignee: Michael Dick
> > > >             Fix For: 1.1.0
> > > >
> > > >         Attachments: OPENJPA-455.patch.txt
> > > >
> > > >
> > > > Opening a JIRA report on Tim's behalf.
> > > > I turned the schema tool loose on a MySQL production database this
> > > > afternoon and it failed. The essence of the problem appears that DDL was
> > > > being generated with a type declaration of this form:
> > > > int unsigned(10)
> > > > In MySQL, the proper form is:
> > > > int(10) unsigned
> > > > viz:
> > > > ALTER TABLE fubar MODIFY col1 int(10) unsigned;
> > > > Checking other options indicates that similar constructs such as CREATE
> > > > TABLE are likewise defective.
> > > > I looked at the svn trunk head source code in
> > > > org.apache.openjpa.jdbc.sql.MySQLDictionary.java and the parent class
> > > > DBDictionary.java. The offending method appears to be:
> > > > 1508:     public String getTypeName(Column col)
> > > > This method has no override in MySQLDictionary, but apparently needs
> > > > one. I think it's a minor mod, but I'm not currently set up to build and
> > > > test in the environment where the offending database exists.
> > > > This is a SEVERE error. It causes generation of defective SQL for
> > > > SQL-generating options and causes live updates to schemas to fail.
> > > > I don't have a Jira login at present, so if someone could log this, it
> > > > would be appreciated.
> > > >   Thanks,
> > > >    Tim Holloway
> > >
> >
> >
> 
> 
>