Re: [patch] add CLOB datatype
Franck Routier <[email protected]> Fri, 27 Apr 2012 12:08:40 +0200
| Newsgroups | gmane.comp.java.orm.simpleorm |
|---|---|
| Message-ID | <[email protected]> |
--------------000704060608010806010906 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Hi, just to make it clear, the test in DataTypeTest passes on Postgresql. It does not bring any real benefit, as TEXT field are retrieved entirely by Postgresql jdbc driver in the ResultSet anyway, but it will allow using 'TEXT' field just fine. But I did not test any other database engine, especially Oracle. Regards, Franck Le 27/04/2012 06:39, anthony berglas a écrit : > Hello Noel, > > Thanks for providing the patch, but we really do need a decent test > case for all changes. (Not sure what HSQL does with Clobs, but if not > supported then the test case should run properly on at least one > database!). > > As it is, Franck will have spent more time on it than the original > authorship, for a feature that no one is currently requiring. > Remember that people can always use raw SQL for odd cases, and a goal > is to keep SimpleOrm simple. And B/Clobs have extra issues that we > should be able to retrieve them as a single string/byte array, or as a > stream. > > As to PostgreSql, there is a fair chance that accessing an ordinary > text field as a Clob should work, remember that the Java type need not > be the same as the SQL type in JDBC. > > Anthony > > On Thu, Apr 26, 2012 at 10:55 PM, Franck Routier > <[email protected] <mailto:[email protected]>> wrote: > > Ok. So I'll fix this. > > What I have done is : > 1) allow user to choose a maxAutoRetrieveSize, to (eventually) > avoid getting all content when building the SRecordInstance > 2) tweak Prostgresql, CachéDB and Mysql driver (to get the right > sql type, eg TEXT or LONGVARCHAR) > 3) add a test in DataTypeTest > > Notice : > 1) Postgresql does not really support Clobs. > This would map to TEXT fields, but the streaming part is not > implemented, and the whole text will be in the resultset anyway. > 2) I was not able to find a way to allow interaction with the Clob > object. > Maybe with another SFieldXXXX that would have an internal type > java.sql.Clob (but then you could only interact while within the > transaction as I understand it). > Anyway, it's probably not very useful for now. > > I have added a test case, but I have no Oracle database to test it > now. Anyone willing to test is welcome. > > Regards, > Franck > > Le 26/04/2012 11:41, Noel Grandin a écrit : >> You know, you are right, that loop does look dodgy. >> To be honest, my code only briefly used CLOB's and then I moved >> onto something else, so that code is no longer in use here. >> >> On 2012-04-24 12:18, Franck Routier wrote: >>> Ok. >>> But, then maybe we should give the user (I mean the developper >>> using Simpleorm) an access to the Clob object, instead of >>> fetching its value on queryFieldValue ? >>> And maybe add a custom method to SFieldClob that would >>> effectively fetch the value, but not on queryFieldValue ? >>> >>> >>> Another point is, did this code work ? I'm asking because the >>> break condition in queryFieldValue seems erroneous, as >>> reader.read(buf) will return -1 on end of file, not 0. ? >>> >>> Franck >>> >>> Le 24/04/2012 11:12, Noel Grandin a écrit : >>>> No good reason, just that it would be a little surprising >>>> performance wise - could trigger a very long fetch from the >>>> server that is likely to be uncached. >>>> >>>> But I don't have a principled objection to it. >>>> >>>> On 2012-04-24 09:12, Franck Routier wrote: >>>>> >>>>> Hi Noël and Anthony, >>>>> >>>>> I've been looking at your code, and I was wondering why Clobs >>>>> wouldn't be comparable... What is the reason behind that ? >>>>> Typically, Postgresql type for Clobs (TEXT) would just compare >>>>> fine, as well as java representation of a Clob (a String). >>>>> Is there any Oracle specificity around that, or simply a size >>>>> problem ?? >>>>> >>>>> Thanks, >>>>> Franck >>>>> >>>> >>>> >>>> >>>> ------------------------------------------------------------------------ >>>> Disclaimer: http://www.peralex.com/disclaimer.html >>>> >>> >> >> >> >> ------------------------------------------------------------------------ >> Disclaimer: http://www.peralex.com/disclaimer.html >> > > > > > -- > > Dr Anthony Berglas, [email protected] > <mailto:[email protected]> Mobile: +61 4 4838 8874 > Just because it is possible to push twigs along the ground with ones nose > does not necessarily mean that that is the best way to collect firewood. > > --------------000704060608010806010906 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit <head> <style type="text/css"> <!-- /* start of attachment style */ .ygrp-photo-title{ clear: both; font-size: smaller; height: 15px; overflow: hidden; text-align: center; width: 75px; } div.ygrp-photo{ background-position: center; background-repeat: no-repeat; background-color: white; border: 1px solid black; height: 62px; width: 62px; } div.photo-title a, div.photo-title a:active, div.photo-title a:hover, div.photo-title a:visited { text-decoration: none; } div.attach-table div.attach-row { clear: both; } div.attach-table div.attach-row div { float: left; /* margin: 2px;*/ } p { clear: both; padding: 15px 0 3px 0; overflow: hidden; } div.ygrp-file { width: 30px; valign: middle; } div.attach-table div.attach-row div div a { text-decoration: none; } div.attach-table div.attach-row div div span { font-weight: normal; } div.ygrp-file-title { font-weight: bold; } /* end of attachment style */ --> </style> </head> <html> <head> <meta content="text/html; charset=ISO-8859-1" http-equiv="Content-Type"> </head> <body bgcolor="#FFFFFF" text="#000000"> Hi,<br> <br> just to make it clear, the test in DataTypeTest passes on Postgresql.<br> It does not bring any real benefit, as TEXT field are retrieved entirely by Postgresql jdbc driver in the ResultSet anyway, but it will allow using 'TEXT' field just fine.<br> <br> But I did not test any other database engine, especially Oracle.<br> <br> Regards,<br> Franck<br> <br> Le 27/04/2012 06:39, anthony berglas a écrit : <blockquote cite="mid:CA+_PZMfHx=Jzuz9_P5UYqmeTNQ7s2+JiK7yoDAgDZWOqWRM=Pg@mail.gmail.com" type="cite"> <div class="gmail_extra">Hello Noel,</div> <div class="gmail_extra"><br> </div> <div class="gmail_extra">Thanks for providing the patch, but we really do need a decent test case for all changes. (Not sure what HSQL does with Clobs, but if not supported then the test case should run properly on at least one database!).</div> <div class="gmail_extra"><br> </div> <div class="gmail_extra">As it is, Franck will have spent more time on it than the original authorship, for a feature that no one is currently requiring. Remember that people can always use raw SQL for odd cases, and a goal is to keep SimpleOrm simple. And B/Clobs have extra issues that we should be able to retrieve them as a single string/byte array, or as a stream.</div> <div class="gmail_extra"><br> </div> <div class="gmail_extra">As to PostgreSql, there is a fair chance that accessing an ordinary text field as a Clob should work, remember that the Java type need not be the same as the SQL type in JDBC.</div> <div class="gmail_extra"><br> </div> <div class="gmail_extra">Anthony<br> <br> <div class="gmail_quote">On Thu, Apr 26, 2012 at 10:55 PM, Franck Routier <span dir="ltr"><<a moz-do-not-send="true" href="mailto:[email protected]" target="_blank">[email protected]</a>></span> wrote:<br> <blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> <div bgcolor="#FFFFFF" text="#000000"> Ok. So I'll fix this.<br> <br> What I have done is :<br> 1) allow user to choose a maxAutoRetrieveSize, to (eventually) avoid getting all content when building the SRecordInstance<br> 2) tweak Prostgresql, CachéDB and Mysql driver (to get the right sql type, eg TEXT or LONGVARCHAR)<br> 3) add a test in DataTypeTest<br> <br> Notice :<br> 1) Postgresql does not really support Clobs.<br> This would map to TEXT fields, but the streaming part is not implemented, and the whole text will be in the resultset anyway.<br> 2) I was not able to find a way to allow interaction with the Clob object.<br> Maybe with another SFieldXXXX that would have an internal type java.sql.Clob (but then you could only interact while within the transaction as I understand it).<br> Anyway, it's probably not very useful for now.<br> <br> I have added a test case, but I have no Oracle database to test it now. Anyone willing to test is welcome.<br> <br> Regards,<br> Franck<br> <br> Le 26/04/2012 11:41, Noel Grandin a écrit : <div> <div class="h5"> <blockquote type="cite"> You know, you are right, that loop does look dodgy. <br> To be honest, my code only briefly used CLOB's and then I moved onto something else, so that code is no longer in use here.<br> <br> On 2012-04-24 12:18, Franck Routier wrote: <blockquote type="cite"> Ok. <br> But, then maybe we should give the user (I mean the developper using Simpleorm) an access to the Clob object, instead of fetching its value on queryFieldValue ?<br> And maybe add a custom method to SFieldClob that would effectively fetch the value, but not on queryFieldValue ?<br> <br> <br> Another point is, did this code work ? I'm asking because the break condition in queryFieldValue seems erroneous, as reader.read(buf) will return -1 on end of file, not 0. ?<br> <br> Franck <br> <br> Le 24/04/2012 11:12, Noel Grandin a écrit : <blockquote type="cite"> No good reason, just that it would be a little surprising performance wise - could trigger a very long fetch from the server that is likely to be uncached.<br> <br> But I don't have a principled objection to it.<br> <br> On 2012-04-24 09:12, Franck Routier wrote: <blockquote type="cite"> <span> </span> <div> <div> <div> <p> Hi Noël and Anthony,<br> <br> I've been looking at your code, and I was wondering why Clobs wouldn't be comparable... What is the reason behind that ?<br> Typically, Postgresql type for Clobs (TEXT) would just compare fine, as well as java representation of a Clob (a String).<br> Is there any Oracle specificity around that, or simply a size problem ??<br> <br> Thanks,<br> Franck<br> <br> </p> </div> </div> </div> </blockquote> <br> <br> <br> <hr><font color="808080" size="-2">Disclaimer: <a moz-do-not-send="true" href="http://www.peralex.com/disclaimer.html" target="_blank">http://www.peralex.com/disclaimer.html</a><br> <br> </font></blockquote> <br> </blockquote> <br> <br> <br> <hr><font color="808080" size="-2">Disclaimer: <a moz-do-not-send="true" href="http://www.peralex.com/disclaimer.html" target="_blank">http://www.peralex.com/disclaimer.html</a><br> <br> </font></blockquote> <br> </div> </div> </div> </blockquote> </div> <br> <br clear="all"> <div><br> </div> -- <br> <p> </p> <p>Dr Anthony Berglas, <a moz-do-not-send="true" href="mailto:[email protected]" target="_blank">[email protected]</a> Mobile: +61 4 4838 8874<br> Just because it is possible to push twigs along the ground with ones nose<br> does not necessarily mean that that is the best way to collect firewood.</p> <br> </div> </blockquote> <br> <!-- |**|begin egp html banner|**| --> <br> <br> <!-- |**|end egp html banner|**| --> <div width="1" style="color: white; clear: both;"/>__._,_.___</div> <!-- Start Recommendations --> <!-- End Recommendations --> <!-- |**|begin egp html banner|**| --> <img src="http://geo.yahoo.com/serv?s=97476590/grpId=7360226/grpspId=1705006905/msgId=2058/stime=1335521366" width="1" height="1"> <br> <!-- |**|end egp html banner|**| --> <!-- |**|begin egp html banner|**| --> <br> <div style="font-family: verdana; font-size: 77%; border-top: 1px solid #666; padding: 5px 0;" > Your email settings: Individual Email|Traditional <br> <a href="http://groups.yahoo.com/group/SimpleORM/join;_ylc=X3oDMTJmOWNtbzdnBF9TAzk3NDc2NTkwBGdycElkAzczNjAyMjYEZ3Jwc3BJZAMxNzA1MDA2OTA1BHNlYwNmdHIEc2xrA3N0bmdzBHN0aW1lAzEzMzU1MjEzNjY-">Change settings via the Web</a> (Yahoo! ID required) <br> Change settings via email: <a href="mailto:[email protected]?subject=Email Delivery: Digest">Switch delivery to Daily Digest</a> | <a href = "mailto:[email protected]?subject=Change Delivery Format: Fully Featured">Switch to Fully Featured</a> <br> <a href="http://groups.yahoo.com/group/SimpleORM;_ylc=X3oDMTJkcjczZTBhBF9TAzk3NDc2NTkwBGdycElkAzczNjAyMjYEZ3Jwc3BJZAMxNzA1MDA2OTA1BHNlYwNmdHIEc2xrA2hwZgRzdGltZQMxMzM1NTIxMzY2"> Visit Your Group </a> | <a href="http://docs.yahoo.com/info/terms/"> Yahoo! Groups Terms of Use </a> | <a href="mailto:[email protected]?subject=Unsubscribe"> Unsubscribe </a> <br> </div> <br> <!-- |**|end egp html banner|**| --> <div style="color: white; clear: both;"/>__,_._,___</div> </body> </html> --------------000704060608010806010906--