You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@ibatis.apache.org by "Vadim Pesochinskiy (JIRA)" <ib...@incubator.apache.org> on 2005/03/08 22:04:52 UTC
[jira] Created: (IBATIS-87) Add notion of DB specific SQL statements
Add notion of DB specific SQL statements
----------------------------------------
Key: IBATIS-87
URL: http://issues.apache.org/jira/browse/IBATIS-87
Project: iBatis for Java
Type: New Feature
Components: SQL Maps
Environment: Any
Reporter: Vadim Pesochinskiy
Priority: Minor
This is probably not very impressive idea, but I will throw it in anyway.
What if we add a DatabaseType property and have SqlMap lookup proper query for it in the same manner as Java internationalization works. DB type attribute can have an arbitrary value, which is only used get proper query.
E.g.
Queries:
getOrder.sqlserver
getOrder
If DataBaseType is 'sqlserver' - 'getOrder.sqlserver' query is picked, if DataBaseType=oracle or DataBaseType=postgres 'getOrder' sql will be executed.
I can implement it, if you don't mind :). Thanks.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
Re: [jira] Commented: (IBATIS-87) Add notion of DB specific SQL statements
Posted by Brandon Goodin <br...@gmail.com>.
"but I would prefer to keep all SQLs in one place."
I'm not sure what you mean by this. But, it is a discouraged practice
to place all your statements into one file. It's bad for team
development and organizationally.
As far as the requirements that are stated above you can accomplish
these things now. You simply have to do some footwork ahead of time to
determine which queries are common and which are database specific.
Then you orgaize your sqlmap files accordingly and use the property
substitution that clinton mentioned.
The idea of exposing the internals of ibatis for these kinds of
scenarios is something we want to take under considerable thought. We
are an 80/20 tool. We don't claim to do all things for every
situation. But, we will do our best to accomodate ideas that fit
within our design goals.
We do encourage and appreciate contributions. If you wish to make a
contribution of thought or code we will happily review it and discuss
it with you. If you expect to have free reign of the repository then I
think you misunderstand OSS. I'm not aware of any OSS project that
allows everyone in the world to commit to the repository unchecked.
That said, I think the solution for this _may_ be to expose SqlMap
"meta data" so that you can make decisions outside of the framework
regarding what you will call. This should be used judiciously and
remain hidden within the DAO layer. But, it may be valuable to come up
with a list of information that can be exposed as read-only sqlmap
meta-data.
In regards to your encryption... you can write your own datasource
implementation or wrap another datasource and have it interpret the
encrypted password. That really has nothing to do with IBatis. All
IBatis would care about is that it has _a_ value for the password.
Besides it wouldn't seem that you would not want your password
encryption seed to exist in the code that you are distributing. Most
likely you would want that to remain on a higher, more trusted and
secure level, such as the web server via JNDI.
Brandon
On Mon, 28 Mar 2005 22:00:19 +0200 (CEST), Vadim Pesochinskiy (JIRA)
<ib...@incubator.apache.org> wrote:
> [ http://issues.apache.org/jira/browse/IBATIS-87?page=comments#action_61668 ]
>
> Vadim Pesochinskiy commented on IBATIS-87:
> ------------------------------------------
>
> How did you implement this? Do you parse the SqlMap config in your code? I am hesitating doing that, because changes in config will make your code not usable. I would not mind doing this, if it was available in the next release instead of being my "proprietary" extension.
>
> Current solution, which Clinton described above, is acceptable, but I would prefer to keep all SQLs in one place.
>
> This is very unfortunate that one cannot easily add contributions to the project, because it kind of undermines the value of open source... On the other hand, managing the scope is more important than ability for everyone to add code.
>
> > Add notion of DB specific SQL statements
> > ----------------------------------------
> >
> > Key: IBATIS-87
> > URL: http://issues.apache.org/jira/browse/IBATIS-87
> > Project: iBatis for Java
> > Type: New Feature
> > Components: SQL Maps
> > Environment: Any
> > Reporter: Vadim Pesochinskiy
> > Priority: Minor
>
> >
> > This is probably not very impressive idea, but I will throw it in anyway.
> > What if we add a DatabaseType property and have SqlMap lookup proper query for it in the same manner as Java internationalization works. DB type attribute can have an arbitrary value, which is only used get proper query.
> > E.g.
> > Queries:
> > getOrder.sqlserver
> > getOrder
> > If DataBaseType is 'sqlserver' - 'getOrder.sqlserver' query is picked, if DataBaseType=oracle or DataBaseType=postgres 'getOrder' sql will be executed.
> > I can implement it, if you don't mind :). Thanks.
>
> --
> This message is automatically generated by JIRA.
> -
> If you think it was sent incorrectly contact one of the administrators:
> http://issues.apache.org/jira/secure/Administrators.jspa
> -
> If you want more information on JIRA, or have a bug to report see:
> http://www.atlassian.com/software/jira
>
>
Re: [jira] Commented: (IBATIS-87) Add notion of DB specific SQL statements
Posted by Brandon Goodin <br...@gmail.com>.
that was a bug in previous versions. You need to upgrade to version 2.0.9b.
Brandon
On Tue, 29 Mar 2005 17:26:34 +0200 (CEST), Scott Z (JIRA)
<ib...@incubator.apache.org> wrote:
> [ http://issues.apache.org/jira/browse/IBATIS-87?page=comments#action_61742 ]
>
> Scott Z commented on IBATIS-87:
> -------------------------------
>
> I wanted to added one more comment regarding Clinton's suggestion. I tried the following:
> <sqlMap resource="eg/myapp/sql/Person_${dbtype}.sql"/>
>
> and never got to work. It took ${dbType} literally and threw an IOException (resournce not found). Everywhere else it's fine to use the property variable. I'm running SQLMAP 2.09.
>
> > Add notion of DB specific SQL statements
> > ----------------------------------------
> >
> > Key: IBATIS-87
> > URL: http://issues.apache.org/jira/browse/IBATIS-87
> > Project: iBatis for Java
> > Type: New Feature
> > Components: SQL Maps
> > Environment: Any
> > Reporter: Vadim Pesochinskiy
> > Priority: Minor
>
> >
> > This is probably not very impressive idea, but I will throw it in anyway.
> > What if we add a DatabaseType property and have SqlMap lookup proper query for it in the same manner as Java internationalization works. DB type attribute can have an arbitrary value, which is only used get proper query.
> > E.g.
> > Queries:
> > getOrder.sqlserver
> > getOrder
> > If DataBaseType is 'sqlserver' - 'getOrder.sqlserver' query is picked, if DataBaseType=oracle or DataBaseType=postgres 'getOrder' sql will be executed.
> > I can implement it, if you don't mind :). Thanks.
>
> --
> This message is automatically generated by JIRA.
> -
> If you think it was sent incorrectly contact one of the administrators:
> http://issues.apache.org/jira/secure/Administrators.jspa
> -
> If you want more information on JIRA, or have a bug to report see:
> http://www.atlassian.com/software/jira
>
>
Re: [jira] Commented: (IBATIS-87) Add notion of DB specific SQL statements
Posted by Clinton Begin <cl...@gmail.com>.
Guys,
Here's a solution that should work for you. We're not ready to commit
such an intrusive change (yes it would be) for what is probably a rare
need. But what we can do is provide you with the tools to better
extend the framework without hacks.
What we'll add is a method to SqlMapClient with the following signature:
boolean containsStatement (String statementName)
Since SqlMapClient is an interface, you can easily insert your own
implementation between the interface and our default. Your
implementation can use the containsStatement() method to check for the
.{dbtype} statement, and if it doesn't exist, you can fallback to the
generic one.
That should solve this issue without "hacks".
Thoughts?
Clinton
On Tue, 29 Mar 2005 01:25:16 +0200 (CEST), Vadim Pesochinskiy (JIRA)
<ib...@incubator.apache.org> wrote:
> [ http://issues.apache.org/jira/browse/IBATIS-87?page=comments#action_61684 ]
>
> Vadim Pesochinskiy commented on IBATIS-87:
> ------------------------------------------
>
> This solution will help to create custom work-arounds. I believe implementing this feature will add a lot of value for a little effort.
>
> > Add notion of DB specific SQL statements
> > ----------------------------------------
> >
> > Key: IBATIS-87
> > URL: http://issues.apache.org/jira/browse/IBATIS-87
> > Project: iBatis for Java
> > Type: New Feature
> > Components: SQL Maps
> > Environment: Any
> > Reporter: Vadim Pesochinskiy
> > Priority: Minor
>
> >
> > This is probably not very impressive idea, but I will throw it in anyway.
> > What if we add a DatabaseType property and have SqlMap lookup proper query for it in the same manner as Java internationalization works. DB type attribute can have an arbitrary value, which is only used get proper query.
> > E.g.
> > Queries:
> > getOrder.sqlserver
> > getOrder
> > If DataBaseType is 'sqlserver' - 'getOrder.sqlserver' query is picked, if DataBaseType=oracle or DataBaseType=postgres 'getOrder' sql will be executed.
> > I can implement it, if you don't mind :). Thanks.
>
> --
> This message is automatically generated by JIRA.
> -
> If you think it was sent incorrectly contact one of the administrators:
> http://issues.apache.org/jira/secure/Administrators.jspa
> -
> If you want more information on JIRA, or have a bug to report see:
> http://www.atlassian.com/software/jira
>
>
[jira] Commented: (IBATIS-87) Add notion of DB specific SQL statements
Posted by "Vadim Pesochinskiy (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-87?page=comments#action_60527 ]
Vadim Pesochinskiy commented on IBATIS-87:
------------------------------------------
Hi Clinton,
Do you have any comments on this issue?
I am trying to sell the idea of using iBATIS in my company (oatsystems.com).
There are few improvements that I will need to implement - this one (minor) and DB password encryption, which is a hard requirement for any enterprise software. Current idea is to wrap the Reader returned from your Resource class and unencrypt password there. It is a bit of a hack, it would be nice if there was some kind of PasswordEncryption interface, implementation of which could be provided to iBATIS. Thank you.
> Add notion of DB specific SQL statements
> ----------------------------------------
>
> Key: IBATIS-87
> URL: http://issues.apache.org/jira/browse/IBATIS-87
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Environment: Any
> Reporter: Vadim Pesochinskiy
> Priority: Minor
>
> This is probably not very impressive idea, but I will throw it in anyway.
> What if we add a DatabaseType property and have SqlMap lookup proper query for it in the same manner as Java internationalization works. DB type attribute can have an arbitrary value, which is only used get proper query.
> E.g.
> Queries:
> getOrder.sqlserver
> getOrder
> If DataBaseType is 'sqlserver' - 'getOrder.sqlserver' query is picked, if DataBaseType=oracle or DataBaseType=postgres 'getOrder' sql will be executed.
> I can implement it, if you don't mind :). Thanks.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Closed: (IBATIS-87) Add notion of DB specific SQL statements
Posted by "Brandon Goodin (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-87?page=history ]
Brandon Goodin closed IBATIS-87:
--------------------------------
Resolution: Won't Fix
See for http://issues.apache.org/jira/browse/IBATIS-94 for more viable solution.
> Add notion of DB specific SQL statements
> ----------------------------------------
>
> Key: IBATIS-87
> URL: http://issues.apache.org/jira/browse/IBATIS-87
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Environment: Any
> Reporter: Vadim Pesochinskiy
> Priority: Minor
>
> This is probably not very impressive idea, but I will throw it in anyway.
> What if we add a DatabaseType property and have SqlMap lookup proper query for it in the same manner as Java internationalization works. DB type attribute can have an arbitrary value, which is only used get proper query.
> E.g.
> Queries:
> getOrder.sqlserver
> getOrder
> If DataBaseType is 'sqlserver' - 'getOrder.sqlserver' query is picked, if DataBaseType=oracle or DataBaseType=postgres 'getOrder' sql will be executed.
> I can implement it, if you don't mind :). Thanks.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-87) Add notion of DB specific SQL statements
Posted by "Vadim Pesochinskiy (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-87?page=comments#action_61668 ]
Vadim Pesochinskiy commented on IBATIS-87:
------------------------------------------
How did you implement this? Do you parse the SqlMap config in your code? I am hesitating doing that, because changes in config will make your code not usable. I would not mind doing this, if it was available in the next release instead of being my "proprietary" extension.
Current solution, which Clinton described above, is acceptable, but I would prefer to keep all SQLs in one place.
This is very unfortunate that one cannot easily add contributions to the project, because it kind of undermines the value of open source... On the other hand, managing the scope is more important than ability for everyone to add code.
> Add notion of DB specific SQL statements
> ----------------------------------------
>
> Key: IBATIS-87
> URL: http://issues.apache.org/jira/browse/IBATIS-87
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Environment: Any
> Reporter: Vadim Pesochinskiy
> Priority: Minor
>
> This is probably not very impressive idea, but I will throw it in anyway.
> What if we add a DatabaseType property and have SqlMap lookup proper query for it in the same manner as Java internationalization works. DB type attribute can have an arbitrary value, which is only used get proper query.
> E.g.
> Queries:
> getOrder.sqlserver
> getOrder
> If DataBaseType is 'sqlserver' - 'getOrder.sqlserver' query is picked, if DataBaseType=oracle or DataBaseType=postgres 'getOrder' sql will be executed.
> I can implement it, if you don't mind :). Thanks.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-87) Add notion of DB specific SQL statements
Posted by "Scott Z (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-87?page=comments#action_61742 ]
Scott Z commented on IBATIS-87:
-------------------------------
I wanted to added one more comment regarding Clinton's suggestion. I tried the following:
<sqlMap resource="eg/myapp/sql/Person_${dbtype}.sql"/>
and never got to work. It took ${dbType} literally and threw an IOException (resournce not found). Everywhere else it's fine to use the property variable. I'm running SQLMAP 2.09.
> Add notion of DB specific SQL statements
> ----------------------------------------
>
> Key: IBATIS-87
> URL: http://issues.apache.org/jira/browse/IBATIS-87
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Environment: Any
> Reporter: Vadim Pesochinskiy
> Priority: Minor
>
> This is probably not very impressive idea, but I will throw it in anyway.
> What if we add a DatabaseType property and have SqlMap lookup proper query for it in the same manner as Java internationalization works. DB type attribute can have an arbitrary value, which is only used get proper query.
> E.g.
> Queries:
> getOrder.sqlserver
> getOrder
> If DataBaseType is 'sqlserver' - 'getOrder.sqlserver' query is picked, if DataBaseType=oracle or DataBaseType=postgres 'getOrder' sql will be executed.
> I can implement it, if you don't mind :). Thanks.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-87) Add notion of DB specific SQL statements
Posted by "Scott Z (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-87?page=comments#action_61578 ]
Scott Z commented on IBATIS-87:
-------------------------------
I have the same idea too. Actually, to make it work even more like java resource lookup (internalization) is that, it will first look for db specific queries, getFoo.{dbType}, and if it's not there, fall back to just "getFoo", which is the generic sql works for all databases. This way, you don't need the redundant queries for the ones that are common to all databases. Right now, we kind of implemented this on our side, we keep a map of statement after the sql map is parsed, then when a query is being executed, we first look for the db specific query (stamentname + ".{dbType}"), if it doesn't find it, it then use the original statement.
> Add notion of DB specific SQL statements
> ----------------------------------------
>
> Key: IBATIS-87
> URL: http://issues.apache.org/jira/browse/IBATIS-87
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Environment: Any
> Reporter: Vadim Pesochinskiy
> Priority: Minor
>
> This is probably not very impressive idea, but I will throw it in anyway.
> What if we add a DatabaseType property and have SqlMap lookup proper query for it in the same manner as Java internationalization works. DB type attribute can have an arbitrary value, which is only used get proper query.
> E.g.
> Queries:
> getOrder.sqlserver
> getOrder
> If DataBaseType is 'sqlserver' - 'getOrder.sqlserver' query is picked, if DataBaseType=oracle or DataBaseType=postgres 'getOrder' sql will be executed.
> I can implement it, if you don't mind :). Thanks.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-87) Add notion of DB specific SQL statements
Posted by "Vadim Pesochinskiy (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-87?page=comments#action_61684 ]
Vadim Pesochinskiy commented on IBATIS-87:
------------------------------------------
This solution will help to create custom work-arounds. I believe implementing this feature will add a lot of value for a little effort.
> Add notion of DB specific SQL statements
> ----------------------------------------
>
> Key: IBATIS-87
> URL: http://issues.apache.org/jira/browse/IBATIS-87
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Environment: Any
> Reporter: Vadim Pesochinskiy
> Priority: Minor
>
> This is probably not very impressive idea, but I will throw it in anyway.
> What if we add a DatabaseType property and have SqlMap lookup proper query for it in the same manner as Java internationalization works. DB type attribute can have an arbitrary value, which is only used get proper query.
> E.g.
> Queries:
> getOrder.sqlserver
> getOrder
> If DataBaseType is 'sqlserver' - 'getOrder.sqlserver' query is picked, if DataBaseType=oracle or DataBaseType=postgres 'getOrder' sql will be executed.
> I can implement it, if you don't mind :). Thanks.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira
[jira] Commented: (IBATIS-87) Add notion of DB specific SQL statements
Posted by "Clinton Begin (JIRA)" <ib...@incubator.apache.org>.
[ http://issues.apache.org/jira/browse/IBATIS-87?page=comments#action_60558 ]
Clinton Begin commented on IBATIS-87:
-------------------------------------
You can kind of do this already with your SQL Map config file..
<sqlMap resource="eg/myapp/sql/Person_${dbtype}.sql"/>
If you use a ${property} in your sqlMapConfig.xml file, you can simply specify the dbtype in the properties resource.
dbtype=sqlserver
Cheers,
Clinton
> Add notion of DB specific SQL statements
> ----------------------------------------
>
> Key: IBATIS-87
> URL: http://issues.apache.org/jira/browse/IBATIS-87
> Project: iBatis for Java
> Type: New Feature
> Components: SQL Maps
> Environment: Any
> Reporter: Vadim Pesochinskiy
> Priority: Minor
>
> This is probably not very impressive idea, but I will throw it in anyway.
> What if we add a DatabaseType property and have SqlMap lookup proper query for it in the same manner as Java internationalization works. DB type attribute can have an arbitrary value, which is only used get proper query.
> E.g.
> Queries:
> getOrder.sqlserver
> getOrder
> If DataBaseType is 'sqlserver' - 'getOrder.sqlserver' query is picked, if DataBaseType=oracle or DataBaseType=postgres 'getOrder' sql will be executed.
> I can implement it, if you don't mind :). Thanks.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
http://www.atlassian.com/software/jira