We have been wrestling with the best way to implement a RESTFul interface to a VDB. Here are the options we have come up with to date: 1. Create a hook to procedures to allow a mapping from HTTP verbs to procedures in a model within a VDB. This war would be generated from Designer and there would be one war per VDB. Example... if I have a procedure name rgetBooks() the procedure would map to a GET at URI=/books and would return all books (assuming that the procedure was written correctly) 2. Create a single war that would have pre-defined URIs for table access for any JDBC connection. Multiple connections could be supported within the same war based on the URI. The JNDI names would be entered by the user in a properties file or some analogous mechanism. Example URI=/{dbschema}/{catalog}/{table}?column1=value would return the record corresponding to "value" in the target table Procedures could also be supported in this manner. 3. Another option would be to generate the war discussed in option 2 from Designer to eliminate the need for the user to hand edit a properties file. There are pros and cons for all the options. 1 puts more onus on the user but 2/3 are definitely the more complicated solutions and they require intimate knowledge of the underlying metadata. Please respond with your feedback on these proposed solutions and feel free to add other alternatives. Thanks, Ted _______________________________________________ teiid-dev mailing list teiid-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/teiid-dev

Steve, Yes... delete /{catalog}/{schema}/{table}?column1=value becomes "DELETE FROM catalog.schema.table WHERE column1=value" Also, by allowing procedures to be exposed RESTFully as well, we can handle more complex processing. With regards to: -update change sets: Are you asking how will updates be handled? I am thinking for the input we will expect JSON only and build our SQL accordingly. Not sure if that's what you meant though. :-)

-null values: We can candle null values via the JSON provider

(RESTEasy support Jackson). -non-equality predicates: This could be handled in a procedure -output mapping: We would support JSON only By legacy restful services, do you mean our current rest support? I don't see that changing.

----- Original Message ----- From: "Steven Hawkins" <shawkins(a)redhat.com&gt; To: "Ted Jones" <tejones(a)redhat.com&gt; Cc: "Randall Hauch" <rhauch(a)redhat.com&gt;, "teiid-dev" <teiid-dev(a)lists.jboss.org&gt;, "teiid-designer-dev" <teiid-designer-dev(a)lists.jboss.org&gt; Sent: Monday, May 2, 2011 4:18:13 PM Subject: Re: [teiid-dev] REST Interface to TEIID Can you provide more detail about the verb to SQL mapping? I'm assuming something like: delete /{catalog}/{schema}/{table}?column1=value becomes "DELETE FROM catalog.schema.table WHERE column1=value" Can you confirm and provide more detail on the handling of things like: -update change sets -null values (in both predicates and in insert/update changes) -non-equality predicates -output mapping (are we binding to xml and in what format, or json, etc.) Also I'm assuming that this approach is only for new development. What about matching the expected behavior of legacy restful services? ----- Original Message ----- > We have been wrestling with the best way to implement a RESTFul > interface to a VDB. Here are the options we have come up with to > date: > > 1. Create a hook to procedures to allow a mapping from HTTP verbs to > procedures in a model within a VDB. This war would be generated from > Designer and there would be one war per VDB. > > Example... if I have a procedure name rgetBooks() the procedure > would > map to a GET at URI=/books and would return all books (assuming that > the procedure was written correctly) > > 2. Create a single war that would have pre-defined URIs for table > access for any JDBC connection. Multiple connections could be > supported within the same war based on the URI. The JNDI names would > be entered by the user in a properties file or some analogous > mechanism. > > Example URI=/{dbschema}/{catalog}/{table}?column1=value would return > the record corresponding to "value" in the target table > > Procedures could also be supported in this manner. > > 3. Another option would be to generate the war discussed in option 2 > from Designer to eliminate the need for the user to hand edit a > properties file. > > > > > There are pros and cons for all the options. 1 puts more onus on the > user but 2/3 are definitely the more complicated solutions and they > require intimate knowledge of the underlying metadata. > > Please respond with your feedback on these proposed solutions and > feel > free to add other alternatives. > > Thanks, > Ted > _______________________________________________ > teiid-dev mailing list > teiid-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/teiid-dev

----- Original Message ----- > Steve, > > Yes... delete /{catalog}/{schema}/{table}?column1=value becomes > "DELETE FROM catalog.schema.table WHERE column1=value" > > Also, by allowing procedures to be exposed RESTFully as well, we can > handle more complex processing. > > With regards to: > > -update change sets: Are you asking how will updates be handled? I > am > thinking for the input we will expect JSON only and build our SQL > accordingly. Not sure if that's what you meant though. :-) The question is how will the change set be sent. Your indicating as JSON, i.e. {column1:value}. Does that also imply only literal values, or would you also support expressions? > -null values: We can candle null values via the JSON provider Can you provide an example for a GET predicate?

> (RESTEasy support Jackson). > -non-equality predicates: This could be handled in a procedure > -output mapping: We would support JSON only > > By legacy restful services, do you mean our current rest support? I > don't see that changing. No I mean what we seem to be doing here is a very open ended design of a completely new project (see also Ramesh's suggestion of ht:sql or the servlet based project that you were referencing). That is well and good for new development, but the primary use case for EII/virtualization is providing the ability to easily match existing interfaces. If someone already has a RESTful dataservice defined and wants to have EDS handle it, what is the recommended approach? > > ----- Original Message ----- > From: "Steven Hawkins" <shawkins(a)redhat.com&gt; > To: "Ted Jones" <tejones(a)redhat.com&gt; > Cc: "Randall Hauch" <rhauch(a)redhat.com&gt;, "teiid-dev" > <teiid-dev(a)lists.jboss.org&gt;, "teiid-designer-dev" > <teiid-designer-dev(a)lists.jboss.org&gt; > Sent: Monday, May 2, 2011 4:18:13 PM > Subject: Re: [teiid-dev] REST Interface to TEIID > > Can you provide more detail about the verb to SQL mapping? > > I'm assuming something like: > > delete /{catalog}/{schema}/{table}?column1=value > > becomes "DELETE FROM catalog.schema.table WHERE column1=value" > > Can you confirm and provide more detail on the handling of things > like: > -update change sets > -null values (in both predicates and in insert/update changes) > -non-equality predicates > -output mapping (are we binding to xml and in what format, or json, > etc.) > > Also I'm assuming that this approach is only for new development. > What > about matching the expected behavior of legacy restful services? > > ----- Original Message ----- > > We have been wrestling with the best way to implement a RESTFul > > interface to a VDB. Here are the options we have come up with to > > date: > > > > 1. Create a hook to procedures to allow a mapping from HTTP verbs > > to > > procedures in a model within a VDB. This war would be generated > > from > > Designer and there would be one war per VDB. > > > > Example... if I have a procedure name rgetBooks() the procedure > > would > > map to a GET at URI=/books and would return all books (assuming > > that > > the procedure was written correctly) > > > > 2. Create a single war that would have pre-defined URIs for table > > access for any JDBC connection. Multiple connections could be > > supported within the same war based on the URI. The JNDI names > > would > > be entered by the user in a properties file or some analogous > > mechanism. > > > > Example URI=/{dbschema}/{catalog}/{table}?column1=value would > > return > > the record corresponding to "value" in the target table > > > > Procedures could also be supported in this manner. > > > > 3. Another option would be to generate the war discussed in option > > 2 > > from Designer to eliminate the need for the user to hand edit a > > properties file. > > > > > > > > > > There are pros and cons for all the options. 1 puts more onus on > > the > > user but 2/3 are definitely the more complicated solutions and > > they > > require intimate knowledge of the underlying metadata. > > > > Please respond with your feedback on these proposed solutions and > > feel > > free to add other alternatives. > > > > Thanks, > > Ted > > _______________________________________________ > > teiid-dev mailing list > > teiid-dev(a)lists.jboss.org > > https://lists.jboss.org/mailman/listinfo/teiid-dev

What is the legacy support for Restful service we are talking about? I thought MMX not Teiid never exposed data through REST before. Am I missing something here?

Second, trying to mimic and come up with our own htsql like functionality is lot of work (it took them 6 years). We need to see if we can re-use their framework or something similar framework and integrate with Teiid. HTSQL have port for Postgres, unfortunately their implementation is all Python based, we need to bridge the gaps to deploy into JBoss. This would be very nice long term goal.

I agree with you that we are not going to implement query like semantics the procedure approach is quick hitter, however I am opposed to the idea of creating a new REST-model in the Designer for this (That is what I understood is required).

We should probably just work on enabling the current Web service models through Rest, instead of SOAP interface. In this scenario only issue really is how to covert the XML into JSON or alike. This will take us to same expectations as with SOAP. May be this what you are saying?

Ramesh.. On Tue, 2011-05-03 at 11:25 -0400, Steven Hawkins wrote: > Just to be clear, what I am getting at is that I would rather see us > focus in the near term on a narrowly scoped enablement of RESTFul > services for EDS. The more we attempt to invent or define a lot of > new query semantics (like ht:sql) the more we are pushing out > implementation and ensuring that the usage is only for new > development. > > ----- Original Message ----- > > ----- Original Message ----- > > > Steve, > > > > > > Yes... delete /{catalog}/{schema}/{table}?column1=value becomes > > > "DELETE FROM catalog.schema.table WHERE column1=value" > > > > > > Also, by allowing procedures to be exposed RESTFully as well, we > > > can > > > handle more complex processing. > > > > > > With regards to: > > > > > > -update change sets: Are you asking how will updates be handled? > > > I > > > am > > > thinking for the input we will expect JSON only and build our > > > SQL > > > accordingly. Not sure if that's what you meant though. :-) > > > > The question is how will the change set be sent. Your indicating > > as > > JSON, i.e. {column1:value}. Does that also imply only literal > > values, > > or would you also support expressions? > > > > > -null values: We can candle null values via the JSON provider > > > > Can you provide an example for a GET predicate? > > > > > (RESTEasy support Jackson). > > > -non-equality predicates: This could be handled in a procedure > > > -output mapping: We would support JSON only > > > > > > By legacy restful services, do you mean our current rest > > > support? I > > > don't see that changing. > > > > No I mean what we seem to be doing here is a very open ended > > design of > > a completely new project (see also Ramesh's suggestion of ht:sql > > or > > the servlet based project that you were referencing). That is well > > and > > good for new development, but the primary use case for > > EII/virtualization is providing the ability to easily match > > existing > > interfaces. If someone already has a RESTful dataservice defined > > and > > wants to have EDS handle it, what is the recommended approach? > > > > > > > > ----- Original Message ----- > > > From: "Steven Hawkins" <shawkins(a)redhat.com&gt; > > > To: "Ted Jones" <tejones(a)redhat.com&gt; > > > Cc: "Randall Hauch" <rhauch(a)redhat.com&gt;, "teiid-dev" > > > <teiid-dev(a)lists.jboss.org&gt;, "teiid-designer-dev" > > > <teiid-designer-dev(a)lists.jboss.org&gt; > > > Sent: Monday, May 2, 2011 4:18:13 PM > > > Subject: Re: [teiid-dev] REST Interface to TEIID > > > > > > Can you provide more detail about the verb to SQL mapping? > > > > > > I'm assuming something like: > > > > > > delete /{catalog}/{schema}/{table}?column1=value > > > > > > becomes "DELETE FROM catalog.schema.table WHERE column1=value" > > > > > > Can you confirm and provide more detail on the handling of > > > things > > > like: > > > -update change sets > > > -null values (in both predicates and in insert/update changes) > > > -non-equality predicates > > > -output mapping (are we binding to xml and in what format, or > > > json, > > > etc.) > > > > > > Also I'm assuming that this approach is only for new > > > development. > > > What > > > about matching the expected behavior of legacy restful services? > > > > > > ----- Original Message ----- > > > > We have been wrestling with the best way to implement a > > > > RESTFul > > > > interface to a VDB. Here are the options we have come up with > > > > to > > > > date: > > > > > > > > 1. Create a hook to procedures to allow a mapping from HTTP > > > > verbs > > > > to > > > > procedures in a model within a VDB. This war would be > > > > generated > > > > from > > > > Designer and there would be one war per VDB. > > > > > > > > Example... if I have a procedure name rgetBooks() the > > > > procedure > > > > would > > > > map to a GET at URI=/books and would return all books > > > > (assuming > > > > that > > > > the procedure was written correctly) > > > > > > > > 2. Create a single war that would have pre-defined URIs for > > > > table > > > > access for any JDBC connection. Multiple connections could be > > > > supported within the same war based on the URI. The JNDI names > > > > would > > > > be entered by the user in a properties file or some analogous > > > > mechanism. > > > > > > > > Example URI=/{dbschema}/{catalog}/{table}?column1=value would > > > > return > > > > the record corresponding to "value" in the target table > > > > > > > > Procedures could also be supported in this manner. > > > > > > > > 3. Another option would be to generate the war discussed in > > > > option > > > > 2 > > > > from Designer to eliminate the need for the user to hand edit > > > > a > > > > properties file. > > > > > > > > > > > > > > > > > > > > There are pros and cons for all the options. 1 puts more onus > > > > on > > > > the > > > > user but 2/3 are definitely the more complicated solutions and > > > > they > > > > require intimate knowledge of the underlying metadata. > > > > > > > > Please respond with your feedback on these proposed solutions > > > > and > > > > feel > > > > free to add other alternatives. > > > > > > > > Thanks, > > > > Ted > > > > _______________________________________________ > > > > teiid-dev mailing list > > > > teiid-dev(a)lists.jboss.org > > > > https://lists.jboss.org/mailman/listinfo/teiid-dev > _______________________________________________ > teiid-dev mailing list > teiid-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/teiid-dev

For quick win, I would take current Web service model, and expose them as REST. Since these procedures already expose XML, that should be it.

Exposing as JSON data should be done through some XML JSON converter based on some URL flag.

I would even consider adding this on to the existing SOAP WAR generation stuff as an addon.

I do not see any need for us to use any REST frameworks for this either. Just come up with simple schema included REST like URL and intercept and convert to same execution object the SOAP stuff is doing. Going after generic model with extension metadata not valuable at this point IMO. Once we put the data binding on it, someone will say they want it based on schema then goes back to the web service model anyway.

Ramesh.. On Tue, 2011-05-03 at 13:41 -0400, Steven Hawkins wrote: > ----- Original Message ----- > > What is the legacy support for Restful service we are talking > > about? I > > thought MMX not Teiid never exposed data through REST before. Am I > > missing something here? > > > > By legacy I mean someone has an existing RESTful service not > implemented through EDS, but wants to do it through EDS - the common > virtualization use case. > > > Second, trying to mimic and come up with our own htsql like > > functionality is lot of work (it took them 6 years). We need to > > see if > > we can re-use their framework or something similar framework and > > integrate with Teiid. HTSQL have port for Postgres, unfortunately > > their > > implementation is all Python based, we need to bridge the gaps to > > deploy > > into JBoss. This would be very nice long term goal. > > > > Sure, a long term goal would be to provide some general restful > interface to relational that can include all sorts of novel querying > capabilities. Whether someone else has a license friendly way of > doing that or we develop it ourselves is an open question. My > concern however is still around the next EDS feature release. There > are all sorts of things we could do, but we have a rather short > window to hit that release. > > > I agree with you that we are not going to implement query like > > semantics > > the procedure approach is quick hitter, however I am opposed to > > the > > idea > > of creating a new REST-model in the Designer for this (That is > > what I > > understood is required). > > No I am not in favor of introducing a rest meta-model. I would > either be in favor of starting off with just the assumed URI > /{catalog}/{schema}/{procedure} mapping (it would still be open what > the data binding would be), or using the pending generic extension > metadata feature to set properties (similar to resteasy annotations) > on the procedure/params/results as to what their restful mapping > should be. > > > We should probably just work on enabling the > > current Web service models through Rest, instead of SOAP > > interface. In > > this scenario only issue really is how to covert the XML into JSON > > or > > alike. This will take us to same expectations as with SOAP. May be > > this > > what you are saying? > > Since the web services model at it's core is just a set of > procedures the only difference with any other procedure is that the > output is known to be xml. > > > > > > > Ramesh.. > > > > On Tue, 2011-05-03 at 11:25 -0400, Steven Hawkins wrote: > > > Just to be clear, what I am getting at is that I would rather > > > see us > > > focus in the near term on a narrowly scoped enablement of > > > RESTFul > > > services for EDS. The more we attempt to invent or define a lot > > > of > > > new query semantics (like ht:sql) the more we are pushing out > > > implementation and ensuring that the usage is only for new > > > development. > > > > > > ----- Original Message ----- > > > > ----- Original Message ----- > > > > > Steve, > > > > > > > > > > Yes... delete /{catalog}/{schema}/{table}?column1=value > > > > > becomes > > > > > "DELETE FROM catalog.schema.table WHERE column1=value" > > > > > > > > > > Also, by allowing procedures to be exposed RESTFully as > > > > > well, we > > > > > can > > > > > handle more complex processing. > > > > > > > > > > With regards to: > > > > > > > > > > -update change sets: Are you asking how will updates be > > > > > handled? > > > > > I > > > > > am > > > > > thinking for the input we will expect JSON only and build > > > > > our > > > > > SQL > > > > > accordingly. Not sure if that's what you meant though. :-) > > > > > > > > The question is how will the change set be sent. Your > > > > indicating > > > > as > > > > JSON, i.e. {column1:value}. Does that also imply only literal > > > > values, > > > > or would you also support expressions? > > > > > > > > > -null values: We can candle null values via the JSON > > > > > provider > > > > > > > > Can you provide an example for a GET predicate? > > > > > > > > > (RESTEasy support Jackson). > > > > > -non-equality predicates: This could be handled in a > > > > > procedure > > > > > -output mapping: We would support JSON only > > > > > > > > > > By legacy restful services, do you mean our current rest > > > > > support? I > > > > > don't see that changing. > > > > > > > > No I mean what we seem to be doing here is a very open ended > > > > design of > > > > a completely new project (see also Ramesh's suggestion of > > > > ht:sql > > > > or > > > > the servlet based project that you were referencing). That is > > > > well > > > > and > > > > good for new development, but the primary use case for > > > > EII/virtualization is providing the ability to easily match > > > > existing > > > > interfaces. If someone already has a RESTful dataservice > > > > defined > > > > and > > > > wants to have EDS handle it, what is the recommended approach? > > > > > > > > > > > > > > ----- Original Message ----- > > > > > From: "Steven Hawkins" <shawkins(a)redhat.com&gt; > > > > > To: "Ted Jones" <tejones(a)redhat.com&gt; > > > > > Cc: "Randall Hauch" <rhauch(a)redhat.com&gt;, "teiid-dev" > > > > > <teiid-dev(a)lists.jboss.org&gt;, "teiid-designer-dev" > > > > > <teiid-designer-dev(a)lists.jboss.org&gt; > > > > > Sent: Monday, May 2, 2011 4:18:13 PM > > > > > Subject: Re: [teiid-dev] REST Interface to TEIID > > > > > > > > > > Can you provide more detail about the verb to SQL mapping? > > > > > > > > > > I'm assuming something like: > > > > > > > > > > delete /{catalog}/{schema}/{table}?column1=value > > > > > > > > > > becomes "DELETE FROM catalog.schema.table WHERE > > > > > column1=value" > > > > > > > > > > Can you confirm and provide more detail on the handling of > > > > > things > > > > > like: > > > > > -update change sets > > > > > -null values (in both predicates and in insert/update > > > > > changes) > > > > > -non-equality predicates > > > > > -output mapping (are we binding to xml and in what format, > > > > > or > > > > > json, > > > > > etc.) > > > > > > > > > > Also I'm assuming that this approach is only for new > > > > > development. > > > > > What > > > > > about matching the expected behavior of legacy restful > > > > > services? > > > > > > > > > > ----- Original Message ----- > > > > > > We have been wrestling with the best way to implement a > > > > > > RESTFul > > > > > > interface to a VDB. Here are the options we have come up > > > > > > with > > > > > > to > > > > > > date: > > > > > > > > > > > > 1. Create a hook to procedures to allow a mapping from > > > > > > HTTP > > > > > > verbs > > > > > > to > > > > > > procedures in a model within a VDB. This war would be > > > > > > generated > > > > > > from > > > > > > Designer and there would be one war per VDB. > > > > > > > > > > > > Example... if I have a procedure name rgetBooks() the > > > > > > procedure > > > > > > would > > > > > > map to a GET at URI=/books and would return all books > > > > > > (assuming > > > > > > that > > > > > > the procedure was written correctly) > > > > > > > > > > > > 2. Create a single war that would have pre-defined URIs > > > > > > for > > > > > > table > > > > > > access for any JDBC connection. Multiple connections could > > > > > > be > > > > > > supported within the same war based on the URI. The JNDI > > > > > > names > > > > > > would > > > > > > be entered by the user in a properties file or some > > > > > > analogous > > > > > > mechanism. > > > > > > > > > > > > Example URI=/{dbschema}/{catalog}/{table}?column1=value > > > > > > would > > > > > > return > > > > > > the record corresponding to "value" in the target table > > > > > > > > > > > > Procedures could also be supported in this manner. > > > > > > > > > > > > 3. Another option would be to generate the war discussed > > > > > > in > > > > > > option > > > > > > 2 > > > > > > from Designer to eliminate the need for the user to hand > > > > > > edit > > > > > > a > > > > > > properties file. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > There are pros and cons for all the options. 1 puts more > > > > > > onus > > > > > > on > > > > > > the > > > > > > user but 2/3 are definitely the more complicated solutions > > > > > > and > > > > > > they > > > > > > require intimate knowledge of the underlying metadata. > > > > > > > > > > > > Please respond with your feedback on these proposed > > > > > > solutions > > > > > > and > > > > > > feel > > > > > > free to add other alternatives. > > > > > > > > > > > > Thanks, > > > > > > Ted > > > > > > _______________________________________________ > > > > > > teiid-dev mailing list > > > > > > teiid-dev(a)lists.jboss.org > > > > > > https://lists.jboss.org/mailman/listinfo/teiid-dev > > > _______________________________________________ > > > teiid-dev mailing list > > > teiid-dev(a)lists.jboss.org > > > https://lists.jboss.org/mailman/listinfo/teiid-dev

Can you provide more detail about the verb to SQL mapping? I'm assuming something like: delete /{catalog}/{schema}/{table}?column1=value becomes "DELETE FROM catalog.schema.table WHERE column1=value" Can you confirm and provide more detail on the handling of things like: -update change sets -null values (in both predicates and in insert/update changes) -non-equality predicates -output mapping (are we binding to xml and in what format, or json, etc.) Also I'm assuming that this approach is only for new development. What about matching the expected behavior of legacy restful services? ----- Original Message ----- > We have been wrestling with the best way to implement a RESTFul > interface to a VDB. Here are the options we have come up with to date: > > 1. Create a hook to procedures to allow a mapping from HTTP verbs to > procedures in a model within a VDB. This war would be generated from > Designer and there would be one war per VDB. > > Example... if I have a procedure name rgetBooks() the procedure would > map to a GET at URI=/books and would return all books (assuming that > the procedure was written correctly) > > 2. Create a single war that would have pre-defined URIs for table > access for any JDBC connection. Multiple connections could be > supported within the same war based on the URI. The JNDI names would > be entered by the user in a properties file or some analogous > mechanism. > > Example URI=/{dbschema}/{catalog}/{table}?column1=value would return > the record corresponding to "value" in the target table > > Procedures could also be supported in this manner. > > 3. Another option would be to generate the war discussed in option 2 > from Designer to eliminate the need for the user to hand edit a > properties file. > > > > > There are pros and cons for all the options. 1 puts more onus on the > user but 2/3 are definitely the more complicated solutions and they > require intimate knowledge of the underlying metadata. > > Please respond with your feedback on these proposed solutions and feel > free to add other alternatives. > > Thanks, > Ted > _______________________________________________ > teiid-dev mailing list > teiid-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/teiid-dev _______________________________________________ teiid-dev mailing list teiid-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/teiid-dev