Not quite sure what I am asking.

[nfdavenport]

I have been working on a code base for 5+ years that started out as a version of sql2java (pre sourceforge?).  It is amazing to look at the generated code now and see how similar yet how entirely different it is compared to what you have now.

I completely changed the class structure and got rid of the Manager/Bean relationship.  (I just didn't like working with two classes when I could just have one.)  I implemented named loadBys and deletes for indexes(oracle and mysql), improved the null and large number handling, added order-bys and the ability to pass in connections, and all kinds of other stuff.  Later I ported my stuff and made a PHPWriter that would output everything as PHP.  Some of what I did looks to have eventually been added here and a lot has not.

I am at a cross-roads now.  I need to update my support of different databases as I have focused most of my changes on Oracle.  However, I don't want to lose my new class hierarchy, index support or connection passing.  I could probably fix my stuff pretty easily, but there seems to be a great deal in the current version worth using. 

Would it make sense to create a completely separate set of templates that did things the way I like?  Would any one even be interested enough to have me try to work it into the project?  I think I can do everything I need with a new set of templates, except the changes needed for index support and template switching.

I am just mostly happy to see sql2java is still alive after 5-6 years.  I don't know if I even have time for what I am proposing as I know little yet about velocity.  I guess I just kind of wanted to float this out there and see what anyone has to say.

Thanks

[nfdavenport]

Hmmmm.  I've been trying to look into this more.  I think some of the previous discussion about template packs would be what I am trying to do.  Is that something that is still in the works?

[Alain Fagot Béarez]

About the new implementation of the template packs, Daan would give you much more information.  He is the development head on that part of the project.  However this would be only available in the 3.0 version which is still under development.

Without waiting for the next version, you could already convert your templates for the 2.6 generator.  If you need help for anything, I will be enjoyed to assist you.  As far as I am concerned, I would like to integrate in sql2java the templates I have found in three other sourceforge projects: axgen, cpedia and ibatis.  Just that I don't get enough time for that and the 3.0 configuration wizard and the normal life...

The most important part in creating a new set of templates is in clearly defining the "perschema" templates and the "pertable" templates.  The "perschema" template generates one file for each database, while the "pertable" generates one for every table in the database.  The usual way of producing a new template is by placing the references to velocity variables at the right places in some already working class.  I can lend you a hand for that but you already will learn much from reading the existing templates.

As for the index support, I may need your help for getting the generator working as you expect it to do.

And last but not least, you will need to explain me your concept of "template switching".  ;-)

Welcome in our team!

Alain.

[nfdavenport]

Thanks for the quick response.

All I meant by template switching was an easy way to switch in my set of templates for the default set of templates.  I was guessing that this is what template packs was talking about, perhaps not.  Either way, it sound like people are customizing template so it should be easy enough to make it run mine instead.

The big trick sounds like getting the additional variables I need into the generator so that they can be used as velocity variables.  Just to give you an idea of what I have done, especially in case I get busy and disappear I'll post the index methods below.  I have no idea how compatible they are anymore, since it is based on a 5 year old branch.

Table.java

private Hashtable uniqueKeyHash = new Hashtable();
private Hashtable otherKeyHash = new Hashtable();

public Hashtable getUniqueKeys()
{
    return uniqueKeyHash;
}
public Vector getUniqueKey(String keyName)
{
    return (Vector) uniqueKeyHash.get(keyName);
}
public void addUniqueKey(String keyName, Vector columns)
{
    uniqueKeyHash.put(keyName,columns);
}

public Hashtable getOtherKeys()
{
    return otherKeyHash;
}
public Vector getOtherKey(String keyName)
{
    return (Vector) otherKeyHash.get(keyName);
}
public void addOtherKey(String keyName, Vector columns)
{
    otherKeyHash.put(keyName,columns);
}

Database.java 
(I call one of the following two methods based on the name of the driver.  You may already have a better way to know which type of database you are dealing with.)

    private void loadIndexKeysOracle() throws SQLException
    {
        PreparedStatement    _pstmt        = null;
        Table                table        = null;
        String                tableName    = null;
        String                indexName    = null;
        String                columnName    = null;
        Vector                keyColumns    = null;
        String                uniqueness    = null;

        StringBuffer SQL = new StringBuffer("SELECT aic.table_name, aic.index_name, aic.column_name, ai.uniqueness");
        SQL.append(" FROM all_indexes ai, all_ind_columns aic");
        SQL.append(" WHERE ai.index_name = aic.index_name");
        SQL.append(" AND ai.owner = aic.index_owner");
        SQL.append(" AND ai.owner = ?");
        SQL.append(" ORDER BY aic.table_name, aic.index_name, aic.column_position");

        _pstmt = conn.prepareStatement(SQL.toString());
        _pstmt.setString(1,getSchema());

        ResultSet rs = _pstmt.executeQuery();
        while(rs.next())
        {
            if (indexName == null || !indexName.equals(rs.getString(2)))
            {
                if (indexName != null && keyColumns != null)
                {
                    if ("UNIQUE".equals(uniqueness))
                    {
                        table.addUniqueKey(indexName,keyColumns);
                    }
                    else
                    {
                        table.addOtherKey(indexName,keyColumns);
                    }
                }
                indexName    = rs.getString(2);
                uniqueness    = rs.getString(4);
                keyColumns = new Vector();
            }

            tableName = rs.getString(1);
            if (table == null || !tableName.equals(table.getName()))
            {
                table = getTable(tableName);
                if (table == null)
                {
                    System.out.println("Null Table "+tableName);
                }
            }

            columnName    = rs.getString(3);
            //System.out.println("table:"+table.getName()+"  indexName:"+indexName+"  columnName:"+columnName+"  uniqueness:"+uniqueness);

            Column col = table.getColumn(columnName);
            if (col != null)
            {
                if (keyColumns != null)  //in case we removed keyColumns in a previous pass.
                {
                    keyColumns.add(col);
                }
            }
            else
            {
                keyColumns = null;
                System.out.println("COLUMN NOT FOUND FOR UNIQUE KEY: table:"+table.getName()+"  indexName:"+indexName+"  columnName:"+columnName+"  uniqueness:"+uniqueness);
            }

        }

        if (table != null && keyColumns != null)
        {
            if ("UNIQUE".equals(uniqueness))
            {
                table.addUniqueKey(indexName,keyColumns);
            }
            else
            {
                table.addOtherKey(indexName,keyColumns);
            }
        }

        rs.close();
        _pstmt.close();
    }

private void loadIndexKeysMySQL() throws SQLException
    {
        PreparedStatement    _pstmt        = null;
        ResultSet            rs            = null;
        Table                table        = null;
        String                indexName    = null;
        String                columnName    = null;
        Vector                keyColumns    = null;
        String                uniqueness    = null;

        Table tables[] = getTables();
        for(int i = 0; i < tables.length; i++)
        {
            table = tables[i];

            StringBuffer SQL = new StringBuffer("SHOW INDEX FROM "+getSchema()+"."+table.getName());
            _pstmt = conn.prepareStatement(SQL.toString());
            rs = _pstmt.executeQuery();

            indexName    = null;
            columnName    = null;
            keyColumns    = null;
            uniqueness    = null;

            while(rs.next())
            {
                if (indexName == null || !indexName.equals(rs.getString(3)))
                {
                    if (indexName != null)
                    {
                        if ("UNIQUE".equals(uniqueness) || "0".equals(uniqueness))
                        {
                            if (!"PRIMARY".equals(indexName))
                            {
                                table.addUniqueKey(indexName,keyColumns);
                            }
                        }
                        else
                        {
                            if (!"PRIMARY".equals(indexName))
                            {
                                table.addOtherKey(indexName,keyColumns);
                            }
                        }
                    }

                    keyColumns = new Vector();
                }

                indexName    = rs.getString(3);
                columnName    = rs.getString(5);
                uniqueness    = rs.getString(2);

                //System.out.println("table:"+table.getName()+"  indexName:"+indexName+"  columnName:"+columnName+"  uniqueness:"+uniqueness);

                Column col = table.getColumn(columnName);
                if (col != null)
                {
                    keyColumns.add(col);
                }
                else
                {
                    System.out.println("COLUMN NOT FOUND FOR UNIQUE KEY:"+columnName);
                }
            }

            if (keyColumns != null)
            {
                if ("UNIQUE".equals(uniqueness) || "0".equals(uniqueness))
                {
                    if (!"PRIMARY".equals(indexName))
                    {
                        table.addUniqueKey(indexName,keyColumns);
                    }
                }
                else
                {
                    if (!"PRIMARY".equals(indexName))
                    {
                        table.addOtherKey(indexName,keyColumns);
                    }
                }
            }

            rs.close();
            _pstmt.close();
        }
    }

[Alain Fagot Béarez]

You don't have to wait for the next version to convert your templates and start using them.  Just take a look at the way the 2.6 version already implements "template folders".  They are not as flexible and powerful as will be the "template packs" (i.e. no scripting).

I ported back to the 2.6.x line the code for getting the indices from the database metadata.  The methods in the Table class do not exactly match yours but I hope you may switch your templates to the new interface.  If it results to difficult to you, just post a Feature Request with your templates as attachment.

http://sourceforge.net/tracker/?group_id=54687&atid=474470

In the mean time, you can download the code from the CVS repository to give it a try.

[nfdavenport]

That's great.  I will give it a shot.

Thanks.

[nfdavenport]

Well I downloaded from CVS the other day and tried to get the sample just working, but I couldn't.  I built branch fine, but I couldn't compile the sample.  The templates in the the hssql sample stuff had some problems.

I am going to go back to 2.6.5 and just try and get it to work first before doing anything fancy.

[nfdavenport]

I got 2.6.5 Saturday and saw the message this morning about the fixes in CVS.  I'll give them a shot today.  I did find one other little problem with 2.6.5, but I still not conversant enough with the template yet to pinpoint it.  I post it as its own thread.

[Gerits Daan]

Hello both, I know this is somewhat of a late response but I hope I can help out.

First of all, you are right. The purpose of the template packs is what you expect. People may create new packs, sharing them with the rest of us.

At the moment the common template pack is working, although it isn't production proof yet. You might have a look at it to decide whether it is useful for your case or not.

For your custom properties, take a look at the scripting API inside release 3.0. It will allow you to create complicated tasks using javascript. Maybe you can find a way to use it for your property problem. If not, we're always open for suggestions to improve the software.

Regards,

Daan