When trying to migrate our OTRS database from MySQL to PostgreSQL, I
noticed that the MySQL database uses LONGBLOBs in several places, which
are binary objects, while on the other hand, OTRS creates TEXT columns
for these columns when using PostgreSQL, which is a character type, not
binary. Similarly, for Oracle and SQLServer, OTRS also uses character
types for these columns.
This makes it difficult to migrate between the databases, because you
need to transform from binary to character, using the proper encoding.
From looking at the source code, most of the LONGBLOBs are assumed to
be UTF-8 encoded strings which can be converted to Postgres TEXT with
convert_to(column, 'utf8'), while other LONGBLOBs, e.g. those storing
representing attachments are assumed to be binary resp. the original
encoding of the attachment. These are handled differently by OTRS - for
databases other than MySQL, OTRS stores them in BASE64 encoding. The
conversion can be done with Postgres using encode(column, 'base64').
My question is: Why do the other databases use character objects for all
the LONGBLOBs including those that really contain binary data, so that
for these databases, a BASE64 encoding is necessary, which costs
performance when encoding and decoding and 33% more storage? Wouldn't it
be much better and easier if OTRS used BLOBs consistently across all
databases for the binary columns, and TEXT for the other large columns?
All the databases support this. The BLOBs are just called BYTEA in
Postgres. And MySQL also has LONGTEXT, no need to use LONGBLOBs if it's
actually text.
Also, would it be possible to use BYTEA in PostgreSQL instead of TEXT
for the binary data columns, changing the database scheme accordingly
and setting the DB::DirectBlob flag to 1 so that OTRS stops BASE64
encoding these columns?
-- Christoph
---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
