Обсуждение: Which data type to use for UTF8 JSON and perl/PHP: varchar, text or bytea?

Hello,

I run a small card game with PostgreSQL 8.4.11 on
CentOS 6  at https://apps.facebook.com/video-preferans/

                                  List of databases
Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 pref      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

The client is in Flash, game daemon in Perl 5.10.1
and the web pages (using same database) in PHP 5.3.

My game uses UTF8 and uses the Unicode chars ♠♦♣♥
for the card suits + game language/users are russian.

The users ask for 1 feature for longer time already:
a game journal (i.e. logging of cards dealt and played).

I'd like to store that data (user names, their hands, etc.)
as JSON data in UTF8 encoding, length aprox. 1000 bytes

Does anybody have an advice on what data type
to use best for such a JSON "string"?

Should I take varchar, text or bytea.

And for the latter - how to handle it in Perl
if I currently use DBI and DBD::Pg?

For PHP I probably should use pg_(un)escape_bytea?

(And does this all work with "?" placholders?)

Regards
Alex

On Thu, Apr 26, 2012 at 2:45 PM, Alexander Farber
<alexander.farber@gmail.com> wrote:
> Hello,
>
> I run a small card game with PostgreSQL 8.4.11 on
> CentOS 6  at https://apps.facebook.com/video-preferans/
>
>                                  List of databases
> Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges
> -----------+----------+----------+-------------+-------------+-----------------------
>  pref      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>
> The client is in Flash, game daemon in Perl 5.10.1
> and the web pages (using same database) in PHP 5.3.
>
> My game uses UTF8 and uses the Unicode chars ♠♦♣♥
> for the card suits + game language/users are russian.
>
> The users ask for 1 feature for longer time already:
> a game journal (i.e. logging of cards dealt and played).
>
> I'd like to store that data (user names, their hands, etc.)
> as JSON data in UTF8 encoding, length aprox. 1000 bytes
>
> Does anybody have an advice on what data type
> to use best for such a JSON "string"?
>
> Should I take varchar, text or bytea.
>
> And for the latter - how to handle it in Perl
> if I currently use DBI and DBD::Pg?
>
> For PHP I probably should use pg_(un)escape_bytea?
>
> (And does this all work with "?" placholders?)

upgrade your database and use the new json type/features.

merlin

Hello,

On Thu, Apr 26, 2012 at 10:11 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> Does anybody have an advice on what data type
>> to use best for such a JSON "string"?
>>
>> Should I take varchar, text or bytea.
>>
>> And for the latter - how to handle it in Perl
>> if I currently use DBI and DBD::Pg?
>>
>> For PHP I probably should use pg_(un)escape_bytea?
>>
>> (And does this all work with "?" placholders?)
>
> upgrade your database and use the new json type/features.

do you mean PostgreSQL 9.x?

I don't see anything "json" at
http://www.postgresql.org/docs/9.0/static/datatype.html
or in the search (besides something in "contrib")?

And does it work well with Perl 5.10 and PHP 5.3?

My hoster offers CentOS 6.x only

Regards
Alex

On Thu, Apr 26, 2012 at 3:46 PM, Alexander Farber
<alexander.farber@gmail.com> wrote:
> Hello,
>
> On Thu, Apr 26, 2012 at 10:11 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>>> Does anybody have an advice on what data type
>>> to use best for such a JSON "string"?
>>>
>>> Should I take varchar, text or bytea.
>>>
>>> And for the latter - how to handle it in Perl
>>> if I currently use DBI and DBD::Pg?
>>>
>>> For PHP I probably should use pg_(un)escape_bytea?
>>>
>>> (And does this all work with "?" placholders?)
>>
>> upgrade your database and use the new json type/features.
>
> do you mean PostgreSQL 9.x?
>
> I don't see anything "json" at
> http://www.postgresql.org/docs/9.0/static/datatype.html
> or in the search (besides something in "contrib")?
>
> And does it work well with Perl 5.10 and PHP 5.3?
>
> My hoster offers CentOS 6.x only

it's in core for 9.2.  for 9.1 you have to compile and install it as
an extension:

http://people.planetpostgresql.org/andrew/index.php?/archives/255-JSON-for-PG-9.2-...-and-now-for-9.1!.html
also
http://people.planetpostgresql.org/andrew/index.php?/archives/244-Under-the-wire.html

barring the above, I'd definitely use the 'text' type (or varchar,
which is the same thing).  all should play well with php.

merlin