Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #30469

Re: jdbc setBinaryStream

Show all headers | View raw

On Tue, 02 Dec 2014 17:56:05 +0000, Andreas Leitgeb wrote:

> Martin Gregorie <martin@address-in-sig.invalid> wrote:
>> On Sun, 30 Nov 2014 14:56:21 +0000, Andreas Leitgeb wrote:
>>> I didn't yet come across clob or text so that's not an area I really
>>> know about.
>> In some databases they can be quite weird, explicitly being stored
>> external to the table that defines them. [...]
> 
> Whether it's in-table or in an extension or extra reserved space for
> smart objects (as in informix) should be transparent to the program code
> inserting, updating or reading information.
>
Yeah, sure, but that doesn't necessarily carry over to the JDBC 
interface. Unfortunately.  I'm certain I was able to do the obvious 
String mapping with character() and character varying() fields, but was 
forced to use a stream to access a text field.
 
>>> In my naive imagination, I'd have expected, that Jdbc (the J stands
>>> for Java, doesn't it?) would define a text/clob interface in terms of
>>> char, thus Reader/Writer (and internally pick it's own byte encoding
>>> based on best performance for actual transfer).
>>> Well, it ain't so, whatever the reasons, maybe even good ones.
>> Probably due to the potential length of a text field?
>
There was a good reason for not going that way, of so it seemed at the 
time. 

Its been quite some years since I wrote the archive and in the meantime 
the archive has just sat there and worked without needing any attention 
except when a message turns up with MIME parts described as using 
MACINTOSH encoding or something else that Mstor and/or JavaMail can't 
parse. As a result, I just don't remember some of that detail - sorry.

I'm fairly certain I looked at using Clobs/Blobs, but couldn't get it to 
play nicely with what I was trying to do, particularly as JavaMail wants 
nothing to do with them.

> That, makes no sense to me.  A Reader is to an char, what InputStream is
> to byte.  Both naturally "support" dynamic length.
>
Yes, but not all JDBC implementations map SQL types onto the Java types 
you might expect.
   
> If it really says "must" for 8bit, then this is surely just to define
> bytes as bit-octets, while some exotic systems might perhaps take 10 or
> 12 bit for a "byte".
>
Yes, thats true. In UTF-8, for instance, it plays nicely with most of the 
common Western characters but some Middle Eastern and Asian characters 
get turned into hex hell when you try to display them..

> The decision between db text or blob ought to be, whether it makes sense
> for the *database* to treat data as characters and allow certain
> operations on it e.g. select a slice(first 100 chars) of it for
> preview-text, and ordering result rows by these previews.
>
Frankly, that isn't and wasn't important. 

My earliest design decision was that I wouldn't attempt to make the 
archive interpret anything in a message except some headers (needed for 
indexing and use as dimensions in the star schema) and plain text MIME 
parts (or the whole of a non-MIME body) which is used to support partial 
whole-body content searches. This gives good enough searching and search 
subsetting that the search performance is considerably faster than 
ferreting through huge local mail folders ever is. To read the message 
content, the whole thing is stuffed into a MIME part that describes it as 
a complete e-mail message, which it is, within a covering e-mail. This  
is addressed to me and sent. Two seconds later its in Evolution's in tray 
in front of me and I can open and read it.

> Otherwise, it's like using a bus to transport cupboards and couches,
> ignoring the capabilities (seats) to transport humans.  Might still
> serve the job, but a plain lorry(blob) would be likely more performant
> during load and unload, mostly because it lacks the passenger seats.
> 
... which is exactly what my, sometimes huge, text field holding the 
entire message does. Parsing the message into its components in the 
archive would serve no useful purpose. The system as it stands is quite 
quick enough. During the day Postfix does a BCC for every incoming and 
outgoing message. These build up in a main queue until the archive's 
overnight archival run, which I've never seen take more than 30 seconds 
to complete (it runs at up to 10 msgs/sec, slower if there are few 
messages to archive, but of course that's because its Java and hasn't run 
long enough to get JITed properly). Searching the archive is typically 
under 25 secs (whole archive), and 5 or less if I limit it to the last 12 
months.
 
> Potential of reducing it by almost 50% does not sound tempting? ;-)
>
Not unless I held the messages in compressed form and I'm not sure that 
would do the runtime much good, especially the search times. Some of the 
dimension keys are bigger than I'd really like too (Subject line) but 
that can't be helped either and don't forget that some of the dimensions 
are M:M (correspondents : messages). 

The one thing that I do occasionally think about is writing a batch 
process that could trawl the archive and flag up any spam it finds. I 
know there's some in there but haven't any idea what proportion it makes 
up. Known spam is, of course, excluded but some "never seen this type 
before" spam may get past Spamassassin and be stored. Later examples will 
get excluded by new SA rules or the various blacklists, but meanwhile the 
early samples are in the archive.
  

-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

Back to comp.lang.java.programmer | Previous | Next — Previous in thread | Find similar | Unroll thread

Thread

jdbc setBinaryStream Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-11-29 14:45 +0000
  Re: jdbc setBinaryStream Daniele Futtorovic <da.futt.news@laposte.net> - 2014-11-29 16:57 +0100
    Re: jdbc setBinaryStream Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-11-29 16:58 +0000
      Re: jdbc setBinaryStream Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-11-29 19:39 +0000
        Re: jdbc setBinaryStream Daniele Futtorovic <da.futt.news@laposte.net> - 2014-11-30 02:37 +0100
          Re: jdbc setBinaryStream Arne Vajhøj <arne@vajhoej.dk> - 2014-11-29 21:26 -0500
            Re: jdbc setBinaryStream Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-11-30 11:12 +0000
  Re: jdbc setBinaryStream Martin Gregorie <martin@address-in-sig.invalid> - 2014-11-29 19:59 +0000
    Re: jdbc setBinaryStream Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-11-29 20:36 +0000
      Re: jdbc setBinaryStream Martin Gregorie <martin@address-in-sig.invalid> - 2014-11-30 00:07 +0000
        Re: jdbc setBinaryStream Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-11-30 14:56 +0000
          Re: jdbc setBinaryStream Martin Gregorie <martin@address-in-sig.invalid> - 2014-12-01 14:06 +0000
            Re: jdbc setBinaryStream Arne Vajhøj <arne@vajhoej.dk> - 2014-12-01 21:36 -0500
              Re: jdbc setBinaryStream Martin Gregorie <martin@address-in-sig.invalid> - 2014-12-02 12:16 +0000
            Re: jdbc setBinaryStream Andreas Leitgeb <avl@auth.logic.tuwien.ac.at> - 2014-12-02 17:56 +0000
              Re: jdbc setBinaryStream Martin Gregorie <martin@address-in-sig.invalid> - 2014-12-02 21:37 +0000

csiph-web