On Thu, Dec 03, 2009 at 08:33:38AM +0100, Kern Sibbald wrote:
> On MySQL we use BLOBS.  On PostgreSQL, we TEXT and set the encoding to 
> SQL_ASCII so that PostgreSQL will not attempt to do any translation.  This 
> works well, and I hope that PostgreSQL will continue to support letting 
> Bacula insert text characters in the database with no character encoding 
> checks in the future.

Then decide if bacula treats filenames as opaque identifiers or text
(set of defined human-readable characters). Whatever you decide, TEXT
with SQL_ASCII encoding is not the right choice.

If we decide filenames are just opaque identifiers, then BYTEA (isn't
that the same what the "BLOBS" in MySQL), not TEXT should be used. TEXT
is a string of characters. BYTEA is a string of bytes. Bytes with no
specified encoding are not characters.

Advantages:
  - any filename used in the system may be stored in bacula database
  - on restore it will be restored under the exact same name
Disadvantages:
  - database text operations may not work on such data
  - when file is restored on a system with different encoding its name
    will not be what a user expects. Or event restore would not be
    possible there (such string of bytes may be invalid for filename on
    such system)

If we decide filenames are text, then use TEXT fields with proper
encoding.

Advantages:
  - all text operations will work well on the filenames in the database
  - whatever encoding target system uses the filenames will be right
    after restore
Disadvantages:
  - no way to know how to store filename if the system encoding is not
    known
  - if filename is badly encoded it cannot be stored in the database
    as-is (though it may be mangled so the data can still be restored)

Using SQL_TEXT is just a hack. Hack may be an option, but it should not
be the important design decision and requirement.

Greets,
        Jacek

------------------------------------------------------------------------------
Join us December 9, 2009 for the Red Hat Virtual Experience,
a free event focused on virtualization and cloud computing. 
Attend in-depth sessions from your desk. Your couch. Anywhere.
http://p.sf.net/sfu/redhat-sfdev2dev
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users