db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From CHIU YEE WEAY <ywc...@paradigm.com.my>
Subject FW: Enhancement to the SQL task to support index and unique
Date Sun, 23 Nov 2003 17:09:36 GMT
Greetings Dan,

Would you like to look at this and give me some feedbacks ? Thanks.

Chiu Yee Weay
Systems Engineer
Paradigm Systems Sdn Bhd

---------------------------------------------------------------------

From: CHIU YEE WEAY <ywchiu@paradigm.com.my>
Subject: Enhancement to the SQL task <jdbc> to support index and unique
Date: Thu, 6 Nov 2003 20:52:43 +0800
Content-Type: multipart/mixed;
	boundary="----=_NextPart_000_0037_01C3A4A7.F0C5E2A0"

Greetings,

I have refined the getIndices method as followed :

    /**
     * Retrieves a list of unique/non unique indices for a given table.
     *
     * @param dbMeta JDBC metadata.
     * @param tableName Table from which to retrieve index information.
     * @param unique Flag to indicate whther to retrieve unique/non
unique indices.
     * @return A list of unique/non unique indices in
<code>tableName</code>.
     * @throws SQLException
     */
    public Collection getIndices(DatabaseMetaData dbMeta, String
tableName, boolean unique)
        throws SQLException
    {
        Hashtable indices = new Hashtable();
        ResultSet indexInfo = null;
        try
        {
            indexInfo = dbMeta.getIndexInfo(null, dbSchema, tableName,
unique, false);
            while (indexInfo.next())
            {
                boolean nonUnique = indexInfo.getBoolean(4);
                short type = indexInfo.getShort(7);
                String indexName = indexInfo.getString(6);
                String columnName = indexInfo.getString(9);

                if (type != DatabaseMetaData.tableIndexStatistic &&
nonUnique != unique)
                {
                    Object[] index = (Object[]) indices.get(indexName);
                    List indexColumns;
                    if (index == null)
                    {
                        index = new Object[2];
                        index[0] = indexName;
                        indexColumns = new ArrayList();
                    }
                    else
                    {
                        indexColumns = (ArrayList) index[1];
                    }
                    indexColumns.add(columnName);
                    index[1] = indexColumns;
                    indices.put(indexName, index);
                }
            }
        }
        finally
        {
            if (indexInfo != null)
            {
                indexInfo.close();
            }
        }
        return indices.values();
    }

The full updated source file is also attached.

I have tested manually this class with some DBMSs, e.g. mssql, and it
works fine. I do not have any idea how to write the JUnit test case for
this type of ANT task. Can anyone do the test for me ? Or give me some
hints on this ?

Thanks.


Chiu Yee Weay
Systems Engineer
Paradigm Systems Sdn Bhd

-----Original Message-----
From: Xavier Maysonnave [mailto:xavier@omondo.com] 
Sent: Wednesday, November 05, 2003 4:53 PM
To: Apache Torque Developers List
Subject: Re: Enhancement to the SQL task <jdbc> to support index and
unique

Hi,

This could be a good idea.

I already have a hack who manages the indexes with torque.
But there is some caveats in your proposition.

A foreign key could generate an index and when you reverse the database
and try to 
generate your sql code your database could complain.

Some databases generate automatically an index while some others need a
defined index on a 
foreign key.

The same rule apply to primary keys.

The easiest solution is the following and the job is almost done in my
code.

I need to add two new rules in the db.props file :

indexFilterOnPrimaryKeys and indexFilterOnForeignKeys

This rule is specific to a target database.

With this done I can filter indexes in a clever way.

Regards.

Chiu Yee Weay wrote:

> Greetings Martin and all,
> 
> The task TorqueJDBCTransformTask does support the generation of XML
> schema for tables with columns, primary key and foreign keys, but not
> for index and unique constraints. Are there reasons behind ? The task
> should have generated the index and unique constraints as parts of the
> XML schema.
> 
> Here, I have written a method for retrieving the index information for
> tables. Please review :
> 
>     /**
>      * Retrieves a list of unique/non unique indices for a given
table.
>      *
>      * @param dbMeta JDBC metadata.
>      * @param tableName Table from which to retrieve index
information.
>      * @param unique Flag to indicate whther to retrieve unique/non
> unique indices.
>      * @return A list of unique/non unique indices in
> <code>tableName</code>.
>      * @throws SQLException
>      */
>     public Collection getIndices(DatabaseMetaData dbMeta, String
> tableName, boolean unique)
>         throws SQLException
>     {
>         Hashtable indices = new Hashtable();
>         ResultSet indexInfo = null;
>         try
>         {
>             indexInfo = dbMeta.getIndexInfo(null, dbSchema, tableName,
> unique, false);
>             while (indexInfo.next())
>             {
>                 short type = indexInfo.getShort(7);
>                 if (type != DatabaseMetaData.tableIndexStatistic)
>                 {
>                     boolean nonUnique = indexInfo.getBoolean(4);
>                     if (nonUnique != unique)
>                     {
>                         String indexName = indexInfo.getString(6);
>                         short ordinalPosition = indexInfo.getShort(8);
>                         String columnName = indexInfo.getString(9);
> 
>                         Object[] index = (Object[])
> indices.get(indexName);
>                         Vector indexColumns;
>                         if (index == null)
>                         {
>                             index = new Object[2];
>                             index[0] = indexName;
>                             indexColumns = new Vector();
>                         }
>                         else
>                         {
>                             indexColumns = (Vector) index[1];
>                         }
>                         if (indexColumns.size() < ordinalPosition) {
>                             indexColumns.setSize(ordinalPosition);
>                         }
>                         indexColumns.setElementAt(columnName,
> ordinalPosition - 1);
>                         index[1] = indexColumns;
> 
>                         indices.put(indexName, index);
>                     }
>                 }
>             }
>         }
>         finally
>         {
>             if (indexInfo != null)
>             {
>                 indexInfo.close();
>             }
>         }
>         return indices.values();
>     }
> 
> Attached please find the updated source file for
> TorqueJDBCTransformTask.java, with the new getIndices method and
changes
> in the execute method, which are not yet committed to the CVS.
> 
> If you all agree with the changes, I would like the updates to be
> committed to the CVS. But I do not have the rights to do that, thus,
can
> someone, probably Martin, please help me to do this ?
> 
> Thanks.
> 
> Chiu Yee Weay
> Systems Engineer
> Paradigm Systems Sdn Bhd
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org



----------------------------------------------------------------
This e-mail has been sent via JARING webmail at http://www.jaring.my

Mime
View raw message