Обсуждение: Problem with columns of type Text and Drupal/Prado/PHP5

Hi,

My software for context:
CentOS 4.3 Linux
PHP 5.1
Prado 2.1
Drupal 4.72
PostgreSQL 8.1.4


I use Prado, an advanced  PHP5 framework which maintains a viewstate
(session data) which is essentially a lot of text (possibly containing
some binary data). I also use Drupal for my content management system,
which has support for PostgreSQL, but mainly mysql. Examining the
database storage for PHP sessions, it uses a column marked as TEXT to
store session data. In my case of integrating not only Drupal, but
Prado, I have extra data from Prado being saved into the session table.
With mysql, columns of type TEXT are actually BLOBS. When running Drupal
and Prado with mysql, session management works fine. With Drupal, Prado
and pgsql however, I have a serious problem.

It appears that in the Prado viewstate are some characters (binary??)
that offend the pgsql TEXT column and write errors occur whereby not all
the text is written to the session table, resulting in instability.
There is a point in the text stream where some binary data probably
causes an EOF or something?

Since mysql TEXT columns are really just BLOBS, and they work fine with
the viewstate data, is there a way to just change the column type from
TEXT to something akin to a BLOB column in pgsql so that I can use pgsql
instead of mysql?

I examined the docs and it seems there is no easy way to just define a
column as holding binary data for simple read/write operations. I am
hoping there is a simple solution to swapping a column of type TEXT so
that I can continue to integrate Drupal with Prado. I use Creole and
Propel as my ORM system and have verified that PHP is not the problem,
but it is the actual attempt to write to the pgsql column of type TEXT.

I hope that is clear enough to garner some useful suggestions!!

Thanks

Dave

> It appears that in the Prado viewstate are some characters (binary??)
> that offend the pgsql TEXT column and write errors occur whereby not all
> the text is written to the session table, resulting in instability.
> There is a point in the text stream where some binary data probably
> causes an EOF or something?

Are you sure it isn't data that is UNICODE? I find it odd that they
would be storing actual binary strings as session data (but who knows).


> Since mysql TEXT columns are really just BLOBS, and they work fine with
> the viewstate data, is there a way to just change the column type from
> TEXT to something akin to a BLOB column in pgsql so that I can use pgsql
> instead of mysql?

There is, several different things LO and Bytea.


Sincerely,

Joshua D. Drake




--

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

On Aug 2, 2006, at 12:05 PM, hunkybill@gmail.com wrote:

> Examining the database storage for PHP sessions, it uses a column
> marked as TEXT to store session data. In my case of integrating not
> only Drupal, but Prado, I have extra data from Prado being saved
> into the session table. With mysql, columns of type TEXT are
> actually BLOBS. When running Drupal and Prado with mysql, session
> management works fine. With Drupal, Prado and pgsql however, I have
> a serious problem.

You need to use the bytea column type. If you need examples, Drupal
prior to 4.7 used a text column in their cache table which caused
problems with PostgreSQL. In 4.7 it has been changed to bytea. So a
comparison of the 4.6/4.7 Drupal sources should give you clues if you
want to alter additional fields for binary data because of the Prado.



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL