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 Jun Li <al...@gmail.com> on 2006/03/13 00:16:28 UTC
The DB created from XML has one more index for every table.
Tom,
I've reported this issue before, but I tried this with the latest source
just now, it hasn't been fixed from what I saw.
I'm using SQL Server 2000, trying to create databases from XML file. I tried
with SQL Server 2005 JDBC driver also, just hasn't get it right.
Can you do something on this?
Thanks.
--------------
Cheers,
Jun
Re: The DB created from XML has one more index for every table.
Posted by Thomas Dudziak <to...@gmail.com>.
On 3/22/06, Jun Li <al...@gmail.com> wrote:
> Oh no. It doesn't work here. I have to set both catalog and schema to null
> on SQL Server 2000 with JDBC driver 2005, otherwise I get nothing generated
> in the XML file.
> Therefore, the extra index problem still exists here.
> Anyone else having the same problem?
Can't really help you there then, probably a difference in the DB
setup (my SQL Server instance pretty much uses the default
configuration).
I suggest you debug into the JdbcModelReader, readTables and below, to
see what the JDBC metadata returns. You could also experiment with the
settings for the DumpMetadataTask to see what you get, what are the
schema values and so forth.
> Another issue, I can get the data outputed into XML for SQL Server, but get
> nothing for Oracle using the same codes. Any idea about this? The codes look
> like the following:
>
> FileOutputStream fOS = new FileOutputStream(outputFile);
> DataWriter dataWriter = new DataWriter(fOS);
>
> // Tables that I want to get data from
> List<Table> sysTables = getDataTables(datasource, db);
>
> Table[] tables = new Table[1];
> dataWriter.writeDocumentStart();
> for (Table tb : sysTables)
> {
> String sql = "select * from " + tb.getName();
> tables[0] = tb;
> Iterator dataIt = platform.query(db, sql, tables);
> // Write data to xml file
> dataWriter.write(dataIt);
> }
> dataWriter.writeDocumentEnd();
>
>
> And the way that I read model is like:
>
> Database db = null;
> if ("Oracle".equalsIgnoreCase(dbType)) {
> db = platform.readModelFromDatabase(
> schemaName.toUpperCase(), schemaName.toUpperCase(),
> schemaName.toUpperCase(), null);
> }
> if ("Microsoft SQL Server".equalsIgnoreCase(dbType)) {
> db = platform.readModelFromDatabase(schemaName, null, null, null);
> }
Does the WriteDataToXml subtask produce any output for the same settings ?
Tom
Re: The DB created from XML has one more index for every table.
Posted by Jun Li <al...@gmail.com>.
>
>
> Yup, but I found that it is necessary to set both to their respective
> values instead of null (e.g. for the tests:
>
> http://svn.apache.org/viewcvs.cgi/db/ddlutils/trunk/src/test/jdbc.properties.sqlserver2000?view=markup
> ),
> otherwise too much info was returned. Though all-uppercase is probably
> not required (as opposed to Oracle).
>
> Tom
Oh no. It doesn't work here. I have to set both catalog and schema to null
on SQL Server 2000 with JDBC driver 2005, otherwise I get nothing generated
in the XML file.
Therefore, the extra index problem still exists here.
Anyone else having the same problem?
Another issue, I can get the data outputed into XML for SQL Server, but get
nothing for Oracle using the same codes. Any idea about this? The codes look
like the following:
FileOutputStream fOS = new FileOutputStream(outputFile);
DataWriter dataWriter = new DataWriter(fOS);
// Tables that I want to get data from
List<Table> sysTables = getDataTables(datasource, db);
Table[] tables = new Table[1];
dataWriter.writeDocumentStart();
for (Table tb : sysTables)
{
String sql = "select * from " + tb.getName();
tables[0] = tb;
Iterator dataIt = platform.query(db, sql, tables);
// Write data to xml file
dataWriter.write(dataIt);
}
dataWriter.writeDocumentEnd();
And the way that I read model is like:
Database db = null;
if ("Oracle".equalsIgnoreCase(dbType)) {
db = platform.readModelFromDatabase(
schemaName.toUpperCase(), schemaName.toUpperCase(),
schemaName.toUpperCase(), null);
}
if ("Microsoft SQL Server".equalsIgnoreCase(dbType)) {
db = platform.readModelFromDatabase(schemaName, null, null, null);
}
--
--------------
Cheers,
Jun
Re: The DB created from XML has one more index for every table.
Posted by Thomas Dudziak <to...@gmail.com>.
On 3/21/06, Jun Li <al...@gmail.com> wrote:
> Are you talking about
> platform.readModelFromDatabase(String<http://java.sun.com/j2se/1.4.2/docs/api/java/lang/String.html>
> name,
> String <http://java.sun.com/j2se/1.4.2/docs/api/java/lang/String.html> catalog,
> String <http://java.sun.com/j2se/1.4.2/docs/api/java/lang/String.html> schema,
> String <http://java.sun.com/j2se/1.4.2/docs/api/java/lang/String.html>
> [] tableTypes) here?
>
> But for SQL Server, I have to set both catalog and schema to null to get the
> right result. For Oracle, it's like you said, to set them in upppercase of
> the database's name.
Yup, but I found that it is necessary to set both to their respective
values instead of null (e.g. for the tests:
http://svn.apache.org/viewcvs.cgi/db/ddlutils/trunk/src/test/jdbc.properties.sqlserver2000?view=markup),
otherwise too much info was returned. Though all-uppercase is probably
not required (as opposed to Oracle).
Tom
Re: The DB created from XML has one more index for every table.
Posted by Jun Li <al...@gmail.com>.
Btw, I found that it is important to set both catalog and schema
> patterns to the name of the database all in uppercase (DDLUTILS in my
> case) in order to get useful results.
Are you talking about
platform.readModelFromDatabase(String<http://java.sun.com/j2se/1.4.2/docs/api/java/lang/String.html>
name,
String <http://java.sun.com/j2se/1.4.2/docs/api/java/lang/String.html> catalog,
String <http://java.sun.com/j2se/1.4.2/docs/api/java/lang/String.html> schema,
String <http://java.sun.com/j2se/1.4.2/docs/api/java/lang/String.html>
[] tableTypes) here?
But for SQL Server, I have to set both catalog and schema to null to get the
right result. For Oracle, it's like you said, to set them in upppercase of
the database's name.
> --
> --------------
> Cheers,
> Jun
Re: The DB created from XML has one more index for every table.
Posted by Jun Li <al...@gmail.com>.
Problem solved. Thanks.
On 4/10/06, Thomas Dudziak <to...@gmail.com> wrote:
>
>
> I've enhanced the filtering for sql server a bit so that such unique
> indices are no longer returned. However, this will be more easily done
> once named primary keys are in place (after the 1.0).
>
> Tom
>
--
--------------
Cheers,
Jun
Re: The DB created from XML has one more index for every table.
Posted by Thomas Dudziak <to...@gmail.com>.
On 3/29/06, Jun Li <al...@gmail.com> wrote:
> OK, I know the reason now.
>
> For a schema (generated by DdlUtils from a live database) as following:
>
> <table name="Accrual">
> <column name="AccrualID" primaryKey="true" required="true"
> type="NUMERIC" size="8" autoIncrement="true"/>
> <column name="PeriodFK" primaryKey="false" required="true"
> type="NUMERIC" size="8" autoIncrement="false"/>
> <column name="FileNameChanged" primaryKey="false" required="true"
> type="VARCHAR" size="80" autoIncrement="false"/>
> <column name="AccruedOn" primaryKey="false" required="false"
> type="TIMESTAMP" size="23" autoIncrement="false"/>
> <unique name="PK_Accrual">
> <unique-column name="AccrualID"/>
> </unique>
> <index name="IX_Accrual_Period">
> <index-column name="PeriodFK"/>
> </index>
> </table>
>
> DdlUtils generates sql statements :
>
> CREATE TABLE "Accrual"
> (
> "AccrualID" NUMERIC(8,0) NOT NULL IDENTITY (1,1) ,
> "PeriodFK" NUMERIC(8,0) NOT NULL,
> "FileNameChanged" VARCHAR(80) NOT NULL,
> "AccruedOn" DATETIME,
> PRIMARY KEY ("AccrualID")
> )
>
> And
>
> CREATE UNIQUE INDEX "PK_Accrual" ON "Accrual" ("AccrualID")
>
> CREATE INDEX "IX_Accrual_Period" ON "Accrual" ("PeriodFK")
> OK. It looks all right so far.
> But on SQL Server 2000, with the PRIMARY KEY ("AccrualID")
> in the create table statement, an index called PK__Accrual__XXXXXX will
> generated automatically on the table, here is where the extra index come
> from.
>
> However, if I don't include primary key in the create table statement but
> use
>
> alter table Accrual
> add constraint PK_Accrual primary key
> (
> AccrualID
> )
>
> later to define primary key, the index is called PK_Accrual which is what it
> should be.
>
> I'm not sure is this something that I can config on the SQL Server or
> DdlUtils should take care of this.
I've enhanced the filtering for sql server a bit so that such unique
indices are no longer returned. However, this will be more easily done
once named primary keys are in place (after the 1.0).
Tom
Re: The DB created from XML has one more index for every table.
Posted by Thomas Dudziak <to...@gmail.com>.
On 4/4/06, Jun Li <al...@gmail.com> wrote:
> Any idea about this?
I'll test it with this updated info when I have a bit of time,
hopefully today or tomorrow, and let you know what I found.
Tom
Re: The DB created from XML has one more index for every table.
Posted by Jun Li <al...@gmail.com>.
Tom,
Any idea about this?
On 3/29/06, Jun Li <al...@gmail.com> wrote:
>
> OK, I know the reason now.
>
> For a schema (generated by DdlUtils from a live database) as following:
>
> <table name="Accrual">
> <column name="AccrualID" primaryKey="true" required="true"
> type="NUMERIC" size="8" autoIncrement="true"/>
> <column name="PeriodFK" primaryKey="false" required="true"
> type="NUMERIC" size="8" autoIncrement="false"/>
> <column name="FileNameChanged" primaryKey="false" required="true"
> type="VARCHAR" size="80" autoIncrement="false"/>
> <column name="AccruedOn" primaryKey="false" required="false"
> type="TIMESTAMP" size="23" autoIncrement="false"/>
> <unique name="PK_Accrual">
> <unique-column name="AccrualID"/>
> </unique>
> <index name="IX_Accrual_Period">
> <index-column name="PeriodFK"/>
> </index>
> </table>
>
> DdlUtils generates sql statements :
>
> CREATE TABLE "Accrual"
> (
> "AccrualID" NUMERIC(8,0) NOT NULL IDENTITY (1,1) ,
> "PeriodFK" NUMERIC(8,0) NOT NULL,
> "FileNameChanged" VARCHAR(80) NOT NULL,
> "AccruedOn" DATETIME,
> PRIMARY KEY ("AccrualID")
> )
>
> And
>
> CREATE UNIQUE INDEX "PK_Accrual" ON "Accrual" ("AccrualID")
>
> CREATE INDEX "IX_Accrual_Period" ON "Accrual" ("PeriodFK")
> OK. It looks all right so far. But on SQL Server 2000, with the PRIMARY
> KEY ("AccrualID")
> in the create table statement, an index called PK__Accrual__XXXXXX will
> generated automatically on the table, here is where the extra index come
> from.
>
> However, if I don't include primary key in the create table statement but
> use
>
> alter table Accrual
> add constraint PK_Accrual primary key
> (
> AccrualID
> )
>
> later to define primary key, the index is called PK_Accrual which is what
> it should be.
>
> I'm not sure is this something that I can config on the SQL Server or
> DdlUtils should take care of this.
>
> Thanks.
>
>
> --------------
> Cheers,
>
> Jun
>
--
--------------
Cheers,
Jun
Re: The DB created from XML has one more index for every table.
Posted by Jun Li <al...@gmail.com>.
OK, I know the reason now.
For a schema (generated by DdlUtils from a live database) as following:
<table name="Accrual">
<column name="AccrualID" primaryKey="true" required="true"
type="NUMERIC" size="8" autoIncrement="true"/>
<column name="PeriodFK" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="FileNameChanged" primaryKey="false" required="true"
type="VARCHAR" size="80" autoIncrement="false"/>
<column name="AccruedOn" primaryKey="false" required="false"
type="TIMESTAMP" size="23" autoIncrement="false"/>
<unique name="PK_Accrual">
<unique-column name="AccrualID"/>
</unique>
<index name="IX_Accrual_Period">
<index-column name="PeriodFK"/>
</index>
</table>
DdlUtils generates sql statements :
CREATE TABLE "Accrual"
(
"AccrualID" NUMERIC(8,0) NOT NULL IDENTITY (1,1) ,
"PeriodFK" NUMERIC(8,0) NOT NULL,
"FileNameChanged" VARCHAR(80) NOT NULL,
"AccruedOn" DATETIME,
PRIMARY KEY ("AccrualID")
)
And
CREATE UNIQUE INDEX "PK_Accrual" ON "Accrual" ("AccrualID")
CREATE INDEX "IX_Accrual_Period" ON "Accrual" ("PeriodFK")
OK. It looks all right so far.
But on SQL Server 2000, with the PRIMARY KEY ("AccrualID")
in the create table statement, an index called PK__Accrual__XXXXXX will
generated automatically on the table, here is where the extra index come
from.
However, if I don't include primary key in the create table statement but
use
alter table Accrual
add constraint PK_Accrual primary key
(
AccrualID
)
later to define primary key, the index is called PK_Accrual which is what it
should be.
I'm not sure is this something that I can config on the SQL Server or
DdlUtils should take care of this.
Thanks.
--------------
Cheers,
Jun
Re: The DB created from XML has one more index for every table.
Posted by Thomas Dudziak <to...@gmail.com>.
On 3/19/06, Jun Li <al...@gmail.com> wrote:
> Have you worked it out?
Sorry, was busy with other stuff and hadn't much time for DdlUtils last week.
Anyway, I shortened the schema you provided by removing the attributes
with the default values and the db-created indices:
<database name="build_test">
<table name="Accrual">
<column name="AccrualID" primaryKey="true" required="true"
type="NUMERIC" size="8" autoIncrement="true"/>
<column name="PeriodFK" required="true" type="NUMERIC" size="8"/>
<column name="FileNameChanged" required="true" type="VARCHAR" size="80"/>
<column name="AccruedOn" type="TIMESTAMP" autoIncrement="false"/>
<foreign-key foreignTable="Period" name="FK_Accrual_Period">
<reference local="PeriodFK" foreign="PeriodID"/>
</foreign-key>
</table>
<table name="Period">
<column name="PeriodID" primaryKey="true" required="true"
type="NUMERIC" size="8" autoIncrement="true"/>
<column name="PeriodDesc" required="true" type="VARCHAR" size="50"/>
<column name="StatementDate" type="TIMESTAMP"/>
<column name="LastChangedOn" type="TIMESTAMP"/>
<column name="PeriodMessage" type="VARCHAR" size="1000"/>
</table>
</database>
This schema runs just fine through DdlUtils. Running Ant with
ant -Djdbc.properties.file=src/test/jdbc.properties.sqlserver2000
-Dddlutils.platform=mssql -Dschemapattern=DDLUTILS
-Dcatalogpattern=DDLUTILS -f dump-metadata.xml dump-model
(the dump-metadata.xml and jdbc.properties.sqlserver2000 come with
DdlUtils, you only got to adjust the latter) produced this XML:
<database name="default">
<table name="Accrual">
<column name="AccrualID" primaryKey="true" required="true"
type="NUMERIC" size="8" autoIncrement="true"/>
<column name="PeriodFK" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="FileNameChanged" primaryKey="false"
required="true" type="VARCHAR" size="80" autoIncrement="false"/>
<column name="AccruedOn" primaryKey="false" required="false"
type="TIMESTAMP" size="23" autoIncrement="false"/>
<foreign-key foreignTable="Period" name="FK_Accrual_Period">
<reference local="PeriodFK" foreign="PeriodID"/>
</foreign-key>
</table>
<table name="Period">
<column name="PeriodID" primaryKey="true" required="true"
type="NUMERIC" size="8" autoIncrement="true"/>
<column name="PeriodDesc" primaryKey="false" required="true"
type="VARCHAR" size="50" autoIncrement="false"/>
<column name="StatementDate" primaryKey="false" required="false"
type="TIMESTAMP" size="23" autoIncrement="false"/>
<column name="LastChangedOn" primaryKey="false" required="false"
type="TIMESTAMP" size="23" autoIncrement="false"/>
<column name="PeriodMessage" primaryKey="false" required="false"
type="VARCHAR" size="1000" autoIncrement="false"/>
</table>
</database>
which is the same as the sample schema.
Btw, I found that it is important to set both catalog and schema
patterns to the name of the database all in uppercase (DDLUTILS in my
case) in order to get useful results.
Tom
Re: The DB created from XML has one more index for every table.
Posted by Jun Li <al...@gmail.com>.
Tom,
Have you worked it out?
Thanks.
On 3/13/06, Jun Li <al...@gmail.com> wrote:
>
>
>
> On 3/13/06, Thomas Dudziak <to...@gmail.com> wrote:
> >
> > On 3/13/06, Jun Li <al...@gmail.com> wrote:
> >
> > > I've reported this issue before, but I tried this with the latest
> > source
> > > just now, it hasn't been fixed from what I saw.
> > > I'm using SQL Server 2000, trying to create databases from XML file. I
> > tried
> > > with SQL Server 2005 JDBC driver also, just hasn't get it right.
> > >
> > > Can you do something on this?
> >
> > Please send me the schema you tried to insert (off-the-list if you
> > want) so that I can test it.
> >
> > Tom
> >
>
> Thanks for response, here is a portion of the schema:
>
> <database name="build_test">
> <table name="Accrual">
> <column name="AccrualID" primaryKey="true" required="true"
> type="NUMERIC" size="8" autoIncrement="true"/>
> <column name="PeriodFK" primaryKey="false" required="true"
> type="NUMERIC" size="8" autoIncrement="false"/>
> <column name="FileNameChanged" primaryKey="false" required="true"
> type="VARCHAR" size="80" autoIncrement="false"/>
> <column name="AccruedOn" primaryKey="false" required="false"
> type="TIMESTAMP" size="23" autoIncrement="false"/>
> <foreign-key foreignTable="Period" name="FK_Accrual_Period">
> <reference local="PeriodFK" foreign="PeriodID"/>
> </foreign-key>
> <unique name="PK_Accrual">
> <unique-column name="AccrualID"/>
> </unique>
> <index name="IX_Accrual_Period">
> <index-column name="PeriodFK"/>
> </index>
> </table>
> <table name="Period">
> <column name="PeriodID" primaryKey="true" required="true"
> type="NUMERIC" size="8" autoIncrement="true"/>
> <column name="PeriodDesc" primaryKey="false" required="true"
> type="VARCHAR" size="50" autoIncrement="false"/>
> <column name="StatementDate" primaryKey="false" required="false"
> type="TIMESTAMP" size="23" autoIncrement="false"/>
> <column name="LastChangedOn" primaryKey="false" required="false"
> type="TIMESTAMP" size="23" autoIncrement="false"/>
> <column name="PeriodMessage" primaryKey="false" required="false"
> type="VARCHAR" size="1000" autoIncrement="false"/>
> <unique name="PK_Period">
> <unique-column name="PeriodID"/>
> </unique>
> </table>
> </database>
>
>
>
>
>
> --
> --------------
> Cheers,
> Jun
>
--
--------------
Cheers,
Jun
Re: The DB created from XML has one more index for every table.
Posted by Jun Li <al...@gmail.com>.
On 3/13/06, Thomas Dudziak <to...@gmail.com> wrote:
>
> On 3/13/06, Jun Li <al...@gmail.com> wrote:
>
> > I've reported this issue before, but I tried this with the latest source
> > just now, it hasn't been fixed from what I saw.
> > I'm using SQL Server 2000, trying to create databases from XML file. I
> tried
> > with SQL Server 2005 JDBC driver also, just hasn't get it right.
> >
> > Can you do something on this?
>
> Please send me the schema you tried to insert (off-the-list if you
> want) so that I can test it.
>
> Tom
>
Thanks for response, here is a portion of the schema:
<database name="build_test">
<table name="Accrual">
<column name="AccrualID" primaryKey="true" required="true"
type="NUMERIC" size="8" autoIncrement="true"/>
<column name="PeriodFK" primaryKey="false" required="true"
type="NUMERIC" size="8" autoIncrement="false"/>
<column name="FileNameChanged" primaryKey="false" required="true"
type="VARCHAR" size="80" autoIncrement="false"/>
<column name="AccruedOn" primaryKey="false" required="false"
type="TIMESTAMP" size="23" autoIncrement="false"/>
<foreign-key foreignTable="Period" name="FK_Accrual_Period">
<reference local="PeriodFK" foreign="PeriodID"/>
</foreign-key>
<unique name="PK_Accrual">
<unique-column name="AccrualID"/>
</unique>
<index name="IX_Accrual_Period">
<index-column name="PeriodFK"/>
</index>
</table>
<table name="Period">
<column name="PeriodID" primaryKey="true" required="true"
type="NUMERIC" size="8" autoIncrement="true"/>
<column name="PeriodDesc" primaryKey="false" required="true"
type="VARCHAR" size="50" autoIncrement="false"/>
<column name="StatementDate" primaryKey="false" required="false"
type="TIMESTAMP" size="23" autoIncrement="false"/>
<column name="LastChangedOn" primaryKey="false" required="false"
type="TIMESTAMP" size="23" autoIncrement="false"/>
<column name="PeriodMessage" primaryKey="false" required="false"
type="VARCHAR" size="1000" autoIncrement="false"/>
<unique name="PK_Period">
<unique-column name="PeriodID"/>
</unique>
</table>
</database>
--
--------------
Cheers,
Jun
Re: The DB created from XML has one more index for every table.
Posted by Thomas Dudziak <to...@gmail.com>.
On 3/13/06, Jun Li <al...@gmail.com> wrote:
> I've reported this issue before, but I tried this with the latest source
> just now, it hasn't been fixed from what I saw.
> I'm using SQL Server 2000, trying to create databases from XML file. I tried
> with SQL Server 2005 JDBC driver also, just hasn't get it right.
>
> Can you do something on this?
Please send me the schema you tried to insert (off-the-list if you
want) so that I can test it.
Tom