You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-user@db.apache.org by "Douglas Granzotto (Infoarte)" <dg...@infoarte.trix.net> on 2006/06/14 15:02:30 UTC
Plataform.getAlterTablesSql results
Hi,
I am using the following method:
String getAlterTablesSql(Database desiredDb, boolean doDrops, boolean modifyColumns, boolean continueOnError)
from Plataform
It is returning this sql code:
ALTER TABLE IPI
ADD CLASSFISCAL VARCHAR(12);
ALTER TABLE IPI
ALTER CLASSFISCAL POSITION 4;
CREATE TABLE IPI_
(
IPICOD VARCHAR(2) NOT NULL,
IPIDESC VARCHAR(30),
TIPO INTEGER,
CLASSFISCAL VARCHAR(12),
VERSAO INTEGER,
PRIMARY KEY (IPICOD)
);
INSERT INTO IPI_ (CLASSFISCAL,IPICOD,IPIDESC,TIPO,VERSAO) SELECT CLASSFISCAL,IPICOD,IPIDESC,TIPO,VERSAO FROM IPI;
DROP TABLE IPI;
CREATE TABLE IPI
(
IPICOD VARCHAR(2) NOT NULL,
IPIDESC VARCHAR(30),
TIPO INTEGER,
CLASSFISCAL VARCHAR(12),
VERSAO INTEGER,
PRIMARY KEY (IPICOD)
);
CREATE INDEX IX_IPI_1 ON IPI (IPIDESC);
INSERT INTO IPI (IPICOD,IPIDESC,TIPO,CLASSFISCAL,VERSAO) SELECT IPICOD,IPIDESC,TIPO,CLASSFISCAL,VERSAO FROM IPI_;
DROP TABLE IPI_;
I believe that it would be necessary, only this:
ALTER TABLE IPI
ADD CLASSFISCAL VARCHAR(12);
ALTER TABLE IPI
ALTER CLASSFISCAL POSITION 4;
Since I only added a column.
I don't understand, why of the remaining of the code.
CREATE TABLE IPI_, INSERT INTO IPI_,
DROP TABLE IPI, etc
Thanks,
Douglas Granzotto
Re: Plataform.getAlterTablesSql results
Posted by "Douglas Granzotto (Infoarte)" <dg...@infoarte.trix.net>.
----- Original Message -----
From: "Thomas Dudziak" <to...@gmail.com>
To: <dd...@db.apache.org>
Sent: Wednesday, June 14, 2006 7:23 PM
Subject: Re: Plataform.getAlterTablesSql results
> On 6/14/06, Douglas Granzotto (Infoarte) <dg...@infoarte.trix.net>
wrote:
>
> > Yes, I got the sources on 06/08/2006.
> > The FirebirdBuider.java class is ok.
> > The script generated by plataform.getAlterTablesSql(Database desiredDb,
> > boolean doDrops, boolean modifyColumns, boolean continueOnError) works
> > well.
> > The only problem are these lines, that I think are unnecessary.
>
> DdlUtils will rebuild the table if the changes cannot be done via
> specific SQL commands. In your case, I guess that DdlUtils detected
> not only the column addition but also another change that cannot be
> handled via a specific SQL statement.
> Since the generated SQL does not contain the original table
> definition, its hard to say what the change was that lead to the full
> table rebuild. Could you perhaps post the SQL for creating the
> original table ?
>
> Tom
>
Original Table:
CREATE TABLE "IPI" (
"IPICOD" VARCHAR(2) NOT NULL,
"IPIDESC" VARCHAR(30),
"TIPO" INTEGER,
"VERSAO" INTEGER,
PRIMARY KEY ("IPICOD"));
CREATE INDEX "IX_IPI_1" ON "IPI"("IPIDESC");
New Table:
CREATE TABLE "IPI" (
"IPICOD" VARCHAR(2) NOT NULL,
"IPIDESC" VARCHAR(30),
"TIPO" INTEGER,
"CLASSFISCAL" VARCHAR(12),
"VERSAO" INTEGER,
CONSTRAINT "PK_IPI" PRIMARY KEY ("IPICOD"));
CREATE INDEX "IX_IPI_1" ON "IPI"("IPIDESC");
SQL script generated by getAlterTablesSql:
ALTER TABLE IPI
ADD CLASSFISCAL VARCHAR(12);
ALTER TABLE IPI
ALTER CLASSFISCAL POSITION 4;
CREATE TABLE IPI_
(
IPICOD VARCHAR(2) NOT NULL,
IPIDESC VARCHAR(30),
TIPO INTEGER,
CLASSFISCAL VARCHAR(12),
VERSAO INTEGER,
PRIMARY KEY (IPICOD)
);
INSERT INTO IPI_ (CLASSFISCAL,IPICOD,IPIDESC,TIPO,VERSAO) SELECT
CLASSFISCAL,IPICOD,IPIDESC,TIPO,VERSAO FROM IPI;
DROP TABLE IPI;
CREATE TABLE IPI
(
IPICOD VARCHAR(2) NOT NULL,
IPIDESC VARCHAR(30),
TIPO INTEGER,
CLASSFISCAL VARCHAR(12),
VERSAO INTEGER,
PRIMARY KEY (IPICOD)
);
CREATE INDEX IX_IPI_1 ON IPI (IPIDESC);
INSERT INTO IPI (IPICOD,IPIDESC,TIPO,CLASSFISCAL,VERSAO) SELECT
IPICOD,IPIDESC,TIPO,CLASSFISCAL,VERSAO FROM IPI_;
DROP TABLE IPI_;
Re: Plataform.getAlterTablesSql results
Posted by Thomas Dudziak <to...@gmail.com>.
On 6/14/06, Douglas Granzotto (Infoarte) <dg...@infoarte.trix.net> wrote:
> Yes, I got the sources on 06/08/2006.
> The FirebirdBuider.java class is ok.
> The script generated by plataform.getAlterTablesSql(Database desiredDb,
> boolean doDrops, boolean modifyColumns, boolean continueOnError) works
> well.
> The only problem are these lines, that I think are unnecessary.
DdlUtils will rebuild the table if the changes cannot be done via
specific SQL commands. In your case, I guess that DdlUtils detected
not only the column addition but also another change that cannot be
handled via a specific SQL statement.
Since the generated SQL does not contain the original table
definition, its hard to say what the change was that lead to the full
table rebuild. Could you perhaps post the SQL for creating the
original table ?
Tom
Re: Plataform.getAlterTablesSql results
Posted by "Douglas Granzotto (Infoarte)" <dg...@infoarte.trix.net>.
----- Original Message -----
From: "Thomas Dudziak" <to...@gmail.com>
To: <dd...@db.apache.org>
Sent: Wednesday, June 14, 2006 12:00 PM
Subject: Re: Plataform.getAlterTablesSql results
> On 6/14/06, Douglas Granzotto (Infoarte) <dg...@infoarte.trix.net>
wrote:
>
> > I am using Firebird 1.5, and this supports add column.
> > So much that I removed the unnecessary commands,
> > only remaining:
> >
> > > ALTER TABLE IPI
> > > ADD CLASSFISCAL VARCHAR(12);
> > >
> > > ALTER TABLE IPI
> > > ALTER CLASSFISCAL POSITION 4;
> >
> > And that's ran ok, the modification was executed with success.
>
> Are you sure that you're using the latest DdlUtils source ? I've added
> support for Firebird specific statements about two weeks ago:
>
>
http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/
platform/firebird/FirebirdBuilder.java?view=markup
>
> (in method processChange(Database, Database, AddColumnChange)).
>
> Tom
>
Yes, I got the sources on 06/08/2006.
The FirebirdBuider.java class is ok.
The script generated by plataform.getAlterTablesSql(Database desiredDb,
boolean doDrops, boolean modifyColumns, boolean continueOnError) works
well.
The only problem are these lines, that I think are unnecessary.
CREATE TABLE IPI_
(
IPICOD VARCHAR(2) NOT NULL,
IPIDESC VARCHAR(30),
TIPO INTEGER,
CLASSFISCAL VARCHAR(12),
VERSAO INTEGER,
PRIMARY KEY (IPICOD)
);
INSERT INTO IPI_ (CLASSFISCAL,IPICOD,IPIDESC,TIPO,VERSAO) SELECT
CLASSFISCAL,IPICOD,IPIDESC,TIPO,VERSAO
FROM IPI;
DROP TABLE IPI;
CREATE TABLE IPI
(
IPICOD VARCHAR(2) NOT NULL,
IPIDESC VARCHAR(30),
TIPO INTEGER,
CLASSFISCAL VARCHAR(12),
VERSAO INTEGER,
PRIMARY KEY (IPICOD)
);
CREATE INDEX IX_IPI_1 ON IPI (IPIDESC);
INSERT INTO IPI (IPICOD,IPIDESC,TIPO,CLASSFISCAL,VERSAO) SELECT
IPICOD,IPIDESC,TIPO,CLASSFISCAL,VERSAO
FROM IPI_;
DROP TABLE IPI_;
Douglas
Re: Plataform.getAlterTablesSql results
Posted by Thomas Dudziak <to...@gmail.com>.
On 6/14/06, Douglas Granzotto (Infoarte) <dg...@infoarte.trix.net> wrote:
> I am using Firebird 1.5, and this supports add column.
> So much that I removed the unnecessary commands,
> only remaining:
>
> > ALTER TABLE IPI
> > ADD CLASSFISCAL VARCHAR(12);
> >
> > ALTER TABLE IPI
> > ALTER CLASSFISCAL POSITION 4;
>
> And that's ran ok, the modification was executed with success.
Are you sure that you're using the latest DdlUtils source ? I've added
support for Firebird specific statements about two weeks ago:
http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/firebird/FirebirdBuilder.java?view=markup
(in method processChange(Database, Database, AddColumnChange)).
Tom
Re: Plataform.getAlterTablesSql results
Posted by "Douglas Granzotto (Infoarte)" <dg...@infoarte.trix.net>.
>
> This depends on what platform you use. Some databases fully support
> adding columns (e.g. MySQL), for some this can only be used under
> specific conditions, and for some, there is no way of adding the
> column except by re-creating the table as is done in the SQL that you
> posted.
>
> Tom
>
I am using Firebird 1.5, and this supports add column.
So much that I removed the unnecessary commands,
only remaining:
> ALTER TABLE IPI
> ADD CLASSFISCAL VARCHAR(12);
>
> ALTER TABLE IPI
> ALTER CLASSFISCAL POSITION 4;
And that's ran ok, the modification was executed with success.
Douglas
Re: Plataform.getAlterTablesSql results
Posted by Thomas Dudziak <to...@gmail.com>.
On 6/14/06, Douglas Granzotto (Infoarte) <dg...@infoarte.trix.net> wrote:
> I am using the following method:
> String getAlterTablesSql(Database desiredDb, boolean doDrops, boolean modifyColumns, boolean continueOnError)
> from Plataform
>
> It is returning this sql code:
> ALTER TABLE IPI
> ADD CLASSFISCAL VARCHAR(12);
>
> ALTER TABLE IPI
> ALTER CLASSFISCAL POSITION 4;
>
> CREATE TABLE IPI_
> (
> IPICOD VARCHAR(2) NOT NULL,
> IPIDESC VARCHAR(30),
> TIPO INTEGER,
> CLASSFISCAL VARCHAR(12),
> VERSAO INTEGER,
> PRIMARY KEY (IPICOD)
> );
>
> INSERT INTO IPI_ (CLASSFISCAL,IPICOD,IPIDESC,TIPO,VERSAO) SELECT CLASSFISCAL,IPICOD,IPIDESC,TIPO,VERSAO FROM IPI;
>
> DROP TABLE IPI;
>
> CREATE TABLE IPI
> (
> IPICOD VARCHAR(2) NOT NULL,
> IPIDESC VARCHAR(30),
> TIPO INTEGER,
> CLASSFISCAL VARCHAR(12),
> VERSAO INTEGER,
> PRIMARY KEY (IPICOD)
> );
>
> CREATE INDEX IX_IPI_1 ON IPI (IPIDESC);
>
> INSERT INTO IPI (IPICOD,IPIDESC,TIPO,CLASSFISCAL,VERSAO) SELECT IPICOD,IPIDESC,TIPO,CLASSFISCAL,VERSAO FROM IPI_;
>
> DROP TABLE IPI_;
>
>
>
> I believe that it would be necessary, only this:
> ALTER TABLE IPI
> ADD CLASSFISCAL VARCHAR(12);
>
> ALTER TABLE IPI
> ALTER CLASSFISCAL POSITION 4;
>
> Since I only added a column.
> I don't understand, why of the remaining of the code.
> CREATE TABLE IPI_, INSERT INTO IPI_,
> DROP TABLE IPI, etc
This depends on what platform you use. Some databases fully support
adding columns (e.g. MySQL), for some this can only be used under
specific conditions, and for some, there is no way of adding the
column except by re-creating the table as is done in the SQL that you
posted.
Tom