Unless I have missed something very basic it would appear that sql2java can not cope with a foreign key that involves multiple fields. When loading the bean referenced by foreign key, it only puts the first field into the template bean.
Does anyone have revised template code to get around this?
David
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Well to answer my own question in case anyone else find this and needs it (and so it can be added to later releases) I changed the 3.2 generated code to be:-
//3.2 GET IMPORTED VALUES
public $importedClass get${importedClass}($beanClass bean) throws DAOException
{
$importedClass other = ${importedClassManager}.getInstance().create${importedClass}();
#foreach ( $fk in $foreignKeys )
#if ( $importedTable.getName() == $fk.getForeignColumn().getTable().getName() )
other.$fk.getForeignColumn().getSetMethod()(bean.$fk.getGetMethod()());
#end
#end
bean.set${importedClass}(${importedClassManager}.getInstance().loadUniqueUsingTemplate(other));
return bean.get${importedClass}();
}
Note the inner foreach loop.
Now this is not entirely correct, as it will fail if there are two groups of fields in one table which are referred to the same table. Fortunately this does not happen in the schema that I am working with. To fix that a change to the underlying Java code would be needed.
The problem is that the foreign key constraint name is thrown away. Rather than matching on table name this code above should generate one load per constraint, but this would mean that the constraint name would have to be captured by Database.java so that it could be used in the template.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
OK, so I realise that I am speaking solely to myself in this thread, but I think it is an area that needs to be thought through especially if there is a re-write going on (for V3.0).
Looking at the code in Database.java the only bits of information that are stored are the PK and FK TABLE_NAME and COLUMN_NAME values. In order to do the job properly we do need also to store the FK_NAME, and that is actually both the grouping point for sets of foreign keys and should probably also be used as the name of a method to get the relevant referenced bean.
The same principle should be applied to the primary keys, so the rather blandly named loadByPrimaryKey could be renamed following the PK_NAME and give it some meaning.
Now often the Primary Key names in particular are generated names, and so if little use, but foreign keys frequently have reasonable names and so it should available to the velocity script to use such names which is currently impossible as they are thrown away.
Is there a plan for 3.0, is there a feature list, is there a place where features are being discussed? The only code I can find on the SourceForge site is the 2.6 code. Or is 3.0 so far in the future that actually we should be looking to add such things to 2.6 (I am quite happy to prototype it and submit a patch if that would help but I do need to know which code base to work with).
David
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Actually this is very similar to what I did for my index extension. I saved the name of each index in a map rather than a list. So the key was the index name and the value was the column list. Then each index "MyCoolIndex" became its own named loadby statement loadByMyCoolIndex. I kept a separate list for the unique and not unique indexes so I could make the return type a single bean or an array.
As you mentioned the only trick is making sure all your foreign keys have reasonable names and they are the same across all your databases (dev, acceptance, prod, etc) if you have them.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Sorry for my late reply. Before I start I would like to say I'm in charge of v3.0, not the 2.6.x line. If you need more specific information or requests, contact alain fagot. He will most definitly be glad to help you.
The foreign key issues as you describe it can be solved in almost the same way as the issue nfdavenport has with his indeces. Last weekend I was looking into the whole index thing, trying to fix the primary/foreign key problem inside the v3.0 DatabaseFetcher. As you may (or may not) know, the structure of the project changed a lot in v3.0, giving a lot of new classes and features, but more of that later. First thing I noticed was the lack of information inside the Index and Table classes to hold the information retrieved from the database. I added some fields but I'm not a database wizz, and to be quite frank, we left the whole primary/foreign key thing laying around for too long.
As for the 3.0 version, there is a plan of which features we want to support, only it doesn't exists on the net yet. I would like to release it together with the new website. As a sneak preview, I will tell you some of the features:
Some level of productivity:
- Rewritten Structure : The project has been rewritten from scratch, given us the possibility to use new technologies like JSA (Java Scripting API)
- Package based templates : Templates are grouped inside packages. These packages can be shared with the community once the website will be released.
- Scripting abilities: You can use a scripting language (javascript) to group advanced methods. This way you don't have to modify the sql2java source, just create your script and refer to it from the templates.
- XML based configuration: Configuration will be done trough XML instead of the good old property-files. XML is so much nicer isn't it?
- RoundRobin generation: Modifications made to generated files won't be overwritten with the next generation (if they are placed between 'usercode-tags'.
Still to be created:
- Relationship support: Read relationships between tables from database metadata or from a userdefined XML file.
- Documentation : We are in need of descent documentation.
- Gui / plugin: As Alain Fagot posted in a feature request, it would be nice to integrate sql2java with your favorite IDE.
The code for this new release isn't stored inside CVS. Instead we moved it to SVN. You may check it by checking out the 'greenwade' branch. (the main trunk might be broken. current development is on greenwade branch). Database drops for mysql (and soon javadb) are available inside the 'docs' folder. The actual source can be found inside the trunk folder (I know, that one shouldn't be there. My mistake playing with thing I don't know a thing of.)
Anyway, Sorry for my late reply. I hope this information will help you. If you would like to help us out, just send me a message.
Daan
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Could I suggest that the SVN repository (and its URL) be mentioned on the web site. The CVS repository is the only one that is there. It would also be good to have the roadmap and planned features on the web site.
I am quite happy to help if I can, I need the foreign key bit to work properly for a project (yes, it is an Open Source one) that I am working on.
David
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I know we currently lack the documentation to inform the ones interested in the project. I think we all agree this has to change. I'll ask the one in charge for creating the new website to hurry up. In the meantime, I'll try to setup some temporary development website. I'll ask some of the members if there is someone interested in creating it.
You are always welcome to help with the project, should I add you as a developer?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I would be happy to help, but obviously I need access to the code first. It strikes me that adding me as a developer might be premature, you do not know whether you like my code. Giving me read access to the SVN (I can not find a URL anywhere) so that I can generate fixes and submit them would be a good start.
David
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
OK, I have had a look, and if anything the problem is worse that it was in the 2.6 version.
We have for instance a method in Table.java called public Column getForeignKeyFor(Table pTable), which implies a single column. There is no reason for this implication - multiple keys in primary and foreign keys have been a part of the SQL language for a very long time.
Fetching the foreign keys by constraint name (the SQL engines will generate a name if none is specified or at least they do in all the ones I have worked with) is not difficult, and then we will end up with a structure of the form Map<String fkName, List<Relation>>. This structure is then propagated through the Table class. The method above would also have to return such a construct.
Then the templates (and possibly the scripts, I have not gotten my head around those yet) also need altering, so that they walk the list of fkNames, and for each fkName create a getter (either for a single object or a list of objects) with a name based on the fkName, which then uses the list of columns for that fkName.
The same principle also needs to be be applied to Primary Keys. Here naming the getter is less important as there is only one per table. The important thing is that a primary key can have multiple keys just as a Foreign key can.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Unless I have missed something very basic it would appear that sql2java can not cope with a foreign key that involves multiple fields. When loading the bean referenced by foreign key, it only puts the first field into the template bean.
Does anyone have revised template code to get around this?
David
Well to answer my own question in case anyone else find this and needs it (and so it can be added to later releases) I changed the 3.2 generated code to be:-
//3.2 GET IMPORTED VALUES
public $importedClass get${importedClass}($beanClass bean) throws DAOException
{
$importedClass other = ${importedClassManager}.getInstance().create${importedClass}();
#foreach ( $fk in $foreignKeys )
#if ( $importedTable.getName() == $fk.getForeignColumn().getTable().getName() )
other.$fk.getForeignColumn().getSetMethod()(bean.$fk.getGetMethod()());
#end
#end
bean.set${importedClass}(${importedClassManager}.getInstance().loadUniqueUsingTemplate(other));
return bean.get${importedClass}();
}
Note the inner foreach loop.
Now this is not entirely correct, as it will fail if there are two groups of fields in one table which are referred to the same table. Fortunately this does not happen in the schema that I am working with. To fix that a change to the underlying Java code would be needed.
The problem is that the foreign key constraint name is thrown away. Rather than matching on table name this code above should generate one load per constraint, but this would mean that the constraint name would have to be captured by Database.java so that it could be used in the template.
OK, so I realise that I am speaking solely to myself in this thread, but I think it is an area that needs to be thought through especially if there is a re-write going on (for V3.0).
Looking at the code in Database.java the only bits of information that are stored are the PK and FK TABLE_NAME and COLUMN_NAME values. In order to do the job properly we do need also to store the FK_NAME, and that is actually both the grouping point for sets of foreign keys and should probably also be used as the name of a method to get the relevant referenced bean.
The same principle should be applied to the primary keys, so the rather blandly named loadByPrimaryKey could be renamed following the PK_NAME and give it some meaning.
Now often the Primary Key names in particular are generated names, and so if little use, but foreign keys frequently have reasonable names and so it should available to the velocity script to use such names which is currently impossible as they are thrown away.
Is there a plan for 3.0, is there a feature list, is there a place where features are being discussed? The only code I can find on the SourceForge site is the 2.6 code. Or is 3.0 so far in the future that actually we should be looking to add such things to 2.6 (I am quite happy to prototype it and submit a patch if that would help but I do need to know which code base to work with).
David
I listening. :)
Actually this is very similar to what I did for my index extension. I saved the name of each index in a map rather than a list. So the key was the index name and the value was the column list. Then each index "MyCoolIndex" became its own named loadby statement loadByMyCoolIndex. I kept a separate list for the unique and not unique indexes so I could make the return type a single bean or an array.
As you mentioned the only trick is making sure all your foreign keys have reasonable names and they are the same across all your databases (dev, acceptance, prod, etc) if you have them.
Hello David,
Sorry for my late reply. Before I start I would like to say I'm in charge of v3.0, not the 2.6.x line. If you need more specific information or requests, contact alain fagot. He will most definitly be glad to help you.
The foreign key issues as you describe it can be solved in almost the same way as the issue nfdavenport has with his indeces. Last weekend I was looking into the whole index thing, trying to fix the primary/foreign key problem inside the v3.0 DatabaseFetcher. As you may (or may not) know, the structure of the project changed a lot in v3.0, giving a lot of new classes and features, but more of that later. First thing I noticed was the lack of information inside the Index and Table classes to hold the information retrieved from the database. I added some fields but I'm not a database wizz, and to be quite frank, we left the whole primary/foreign key thing laying around for too long.
As for the 3.0 version, there is a plan of which features we want to support, only it doesn't exists on the net yet. I would like to release it together with the new website. As a sneak preview, I will tell you some of the features:
Some level of productivity:
- Rewritten Structure : The project has been rewritten from scratch, given us the possibility to use new technologies like JSA (Java Scripting API)
- Package based templates : Templates are grouped inside packages. These packages can be shared with the community once the website will be released.
- Scripting abilities: You can use a scripting language (javascript) to group advanced methods. This way you don't have to modify the sql2java source, just create your script and refer to it from the templates.
- XML based configuration: Configuration will be done trough XML instead of the good old property-files. XML is so much nicer isn't it?
- RoundRobin generation: Modifications made to generated files won't be overwritten with the next generation (if they are placed between 'usercode-tags'.
Still to be created:
- Relationship support: Read relationships between tables from database metadata or from a userdefined XML file.
- Documentation : We are in need of descent documentation.
- Gui / plugin: As Alain Fagot posted in a feature request, it would be nice to integrate sql2java with your favorite IDE.
The code for this new release isn't stored inside CVS. Instead we moved it to SVN. You may check it by checking out the 'greenwade' branch. (the main trunk might be broken. current development is on greenwade branch). Database drops for mysql (and soon javadb) are available inside the 'docs' folder. The actual source can be found inside the trunk folder (I know, that one shouldn't be there. My mistake playing with thing I don't know a thing of.)
Anyway, Sorry for my late reply. I hope this information will help you. If you would like to help us out, just send me a message.
Daan
Thanks for the heads up.
Could I suggest that the SVN repository (and its URL) be mentioned on the web site. The CVS repository is the only one that is there. It would also be good to have the roadmap and planned features on the web site.
I am quite happy to help if I can, I need the foreign key bit to work properly for a project (yes, it is an Open Source one) that I am working on.
David
I know we currently lack the documentation to inform the ones interested in the project. I think we all agree this has to change. I'll ask the one in charge for creating the new website to hurry up. In the meantime, I'll try to setup some temporary development website. I'll ask some of the members if there is someone interested in creating it.
You are always welcome to help with the project, should I add you as a developer?
I would be happy to help, but obviously I need access to the code first. It strikes me that adding me as a developer might be premature, you do not know whether you like my code. Giving me read access to the SVN (I can not find a URL anywhere) so that I can generate fixes and submit them would be a good start.
David
On the page http://sourceforge.net/svn/?group_id=54687 (menu Code; item SVN) you will find the following:
svn co https://sql2java.svn.sourceforge.net/svnroot/sql2java sql2java
instead of the trunk, you will need to use the 'greenwade' branch.
In my eclipse installation, I have the following URL:
https://sql2java.svn.sourceforge.net/svnroot/sql2java/branches/greenwade/trunk
Hope this helps,
Alain.
OK, I have had a look, and if anything the problem is worse that it was in the 2.6 version.
We have for instance a method in Table.java called public Column getForeignKeyFor(Table pTable), which implies a single column. There is no reason for this implication - multiple keys in primary and foreign keys have been a part of the SQL language for a very long time.
Fetching the foreign keys by constraint name (the SQL engines will generate a name if none is specified or at least they do in all the ones I have worked with) is not difficult, and then we will end up with a structure of the form Map<String fkName, List<Relation>>. This structure is then propagated through the Table class. The method above would also have to return such a construct.
Then the templates (and possibly the scripts, I have not gotten my head around those yet) also need altering, so that they walk the list of fkNames, and for each fkName create a getter (either for a single object or a list of objects) with a name based on the fkName, which then uses the list of columns for that fkName.
The same principle also needs to be be applied to Primary Keys. Here naming the getter is less important as there is only one per table. The important thing is that a primary key can have multiple keys just as a Foreign key can.