JDBCDataStore should handle clob/blob fields

Justin Deoliveira added a comment - 23/Oct/08 11:19 AM should be straight forward, just add the mappings. I guess the only question is what java types should these map to? byte[].class? the H2 dialect uses blob to store geometries so we might have to override in that case.

Andrea Aime added a comment - 23/Oct/08 3:52 PM Ok cool. I can add the mappings and add some tests. Is H2 able to tell apart a real blob from a geometry? Like, is there any metadata/hint/whatever that you can use to decide that in a table like {{myFeatureType(id:int, geom:Geometry, sevenOfNineNaughtyPicture: blob) only the first is a geometry, whilst the second is (a work of art) hemm... a blob?

Justin Deoliveira added a comment - 23/Oct/08 8:58 PM Actually it is possible, however we don;t it currently. What we could do is add a comment to the field (which shows up as a REMARK in the jdbc metadata). Actually this is something I want to do to be able to tell the actual type of the goemetry field, currently we just return back generic geometry. So basically the create table statement would become: CREATE TABLE myFeatureType (id INT, geom BLOB COMMENT 'POINT', sevenOfNIghtNaughtyPicture BLOB); Want to open a seperate jira for that one against h2 specifically?

Andrea Aime added a comment - 05/Jun/09 12:23 PM Hi, I've tried to implement a very simple blob/clob support, I wanted to discuss a bit the patch. The patch I'm about to attach adds the support in the most naive way, mapping blob to byte[] and clob to String. It does not take into account the H2 issues at the moment. I've tested it with Oracle and it works. Yet, I'm not sure if this is the best way to go. The issue is, BLOB and CLOB can be huge, and in that case, it would be better to load them and store them "on demand", whilst this patch basically loads everything into memory. Yet, I don't see a clear solution for the binding in the case of streaming: the streams are directional, they can be used to read or to write, so it seems that if we wanted to support streaming we'd have to make up our own support class. The other issue is that the features should be detached, one can take any feature from a datastore, close the datastore, and still be able to use the feature. But a streaming field would need to keep a connection to the database, or at least a way to grab a connection (the latter would be better), but that would mean a reference to the DataSource, and this would mean the feature itself would be usable only as long as the datastore is around. Opinions? Is full memory loading a "good enough" solution? It would cover the case in which people used BLOB for small images or small documents, and CLOB for a few thousands chars.

Justin Deoliveira added a comment - 08/Jun/09 2:52 PM I would say the in memory approach is "good enough", given the amount i have seen this feature requested vs the effort involved in implementing the streaming approach. Looking at OracleBlobConverterFactory: the inner converter class "OracleDateConverter" is a typo? As for H2, have you run the H2 tests to see if any problems are introduced. The dialect mapping in H2 is overriden by the dialect so I don't think it will be an issue, be nice to be sure though.

Christian Mueller added a comment - 09/Jun/09 7:22 AM Adding a patch file for DB2. In my opinion In memory support is good enough.

Justin Deoliveira added a comment - 07/May/10 1:45 PM Hey guys, this patch was a bit outdated so I updated it. Good to commit? I Don't really have an oracle or db2 instance to test with.

Andrea Aime added a comment - 07/May/10 3:12 PM - edited I've checked Oracle and it works fine. I thought it was a pity not to have PostGIS into the mix so I whipped up support for it as well. Patch attached (only H2 missing! ;-) ) Ah, sorry, in the patch there is a change of the postgis property files, scrap it (it's late and I did not have to to look into how to do that git git history editing)

Justin Deoliveira added a comment - 07/May/10 3:15 PM Cool thanks Andrea, and yes I will stop dragging my heals and add the tests for H2 :P

Justin Deoliveira added a comment - 07/May/10 5:06 PM ACtually I take it back... this is not so easy to add support to H2. The reason being that H2 blindly assumes a BLOB column == geometry. Its fixable but not trivial. I guess I will wait until a user asks for this or runs into this problem. Oh wait... H2 has no users. Looks like I am in luck :P

Andrea Aime added a comment - 08/May/10 3:33 AM Ha, that is your fault, with the recent extensions you basically have everything you need to make a little spatial database, time to start marketing it without mercy! :-p

Justin Deoliveira added a comment - 08/May/10 3:11 PM Opened GEOT-3071 for h2 specific issue.

Andrea Aime added a comment - 10/May/10 3:42 AM The commit missed a few changes that were included in my latest patch: - closing iterators after using them - add BINARY as a type that maps to byte[] - add special encoding method in the postgis dialect to handle bytea type I've just committed those extras to make the build work with postgis (would just hang otherwise)

Andrea Aime added a comment - 28/Nov/10 2:58 AM Mass closing all issues that have been in "resolved" state for 2 months or more without any feedback or update