Don't use a unicode database with postgresql versions < 8.1 ...
> -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Ryo HAYAKAWA > Sent: Friday, September 16, 2005 12:03 PM > To: [email protected] > Subject: [Dbmail-dev] patch for postgresql to work in UNICODE > encoding. > > Hi there. > > It was neccesary for me to run DBmail 2.1.2 on PostgreSQL > database with UNICODE(UTF-8) charset. But, unfortunately, the > UNICODE(UTF-8) charset did not work with it. > > So, I have created a patch to use the > dbmail_messageblks.messageblk field as bytea. > > I attached the patch to this email, and please refer to it. > > The patch will be useful when a problem like the one reported > at the following URL: > > 0000218: invalid byte sequence for encoding "UNICODE" > http://www.dbmail.org/mantis/view.php?id=218 > > The instruction on how to install it is as follows: > > > # cd dbmail-2.1.2 > > # patch -p1 < /path/to/dbmail-2.1.2-pgbytea.patch > > # aclocal > > # autoheader > > # aclocal > > # autoconf > > > > # configure --with-pgsql-bytea > > # make > > # make install > > > > > > The attached file is hard to read because size is big, so I > extracted > > modified points of only C source parts as follows. > > The attached file is hard to read because the size is big( it > has not only C codes but also some configuration for DB, > fixes for autoconf). > > For your convenience, let me paste just the souce code here: > > ========================================================= > diff -urN dbmail-2.1.2.orig/db.c dbmail-2.1.2/db.c > --- dbmail-2.1.2.orig/db.c 2005-08-11 02:53:14.000000000 +0900 > +++ dbmail-2.1.2/db.c 2005-09-15 16:51:57.389077821 +0900 > @@ -913,7 +913,11 @@ > unsigned is_header) > { > char *escaped_query = NULL; > +#ifdef USE_PGSQL_BYTEA > + unsigned maxesclen = (READ_BLOCK_SIZE + 1) * 5 + > DEF_QUERYSIZE; #else > unsigned maxesclen = (READ_BLOCK_SIZE + 1) * 2 + DEF_QUERYSIZE; > +#endif > unsigned startlen = 0; > unsigned esclen = 0; > > @@ -945,8 +949,11 @@ > "VALUES ('%u','",DBPFX, is_header); > > /* escape & add data */ > +#ifdef USE_PGSQL_BYTEA > + esclen = db_escape_binary(&escaped_query[startlen], block, > +block_size); #else > esclen = db_escape_string(&escaped_query[startlen], > block, block_size); > - > +#endif > snprintf(&escaped_query[esclen + startlen], > maxesclen - esclen - startlen, "', '%llu', '%llu')", > block_size, physmessage_id); > diff -urN dbmail-2.1.2.orig/db.h dbmail-2.1.2/db.h > --- dbmail-2.1.2.orig/db.h 2005-06-17 20:00:40.000000000 +0900 > +++ dbmail-2.1.2/db.h 2005-09-15 16:51:57.392077639 +0900 > @@ -193,6 +193,19 @@ > unsigned long db_escape_string(char *to, > const char *from, unsigned long length); > > +#ifdef USE_PGSQL_BYTEA > +/** > + * \brief escape a binary data for use in query > + * \param to string to copy escaped string to. Must be allocated by > +caller > + * \param from original string > + * \param length of orginal string > + * \return length of escaped string > + * \attention behaviour is undefined if to and from overlap */ > +unsigned long db_escape_binary(char *to, > + const char *from, unsigned long > length); #endif > + > /** > * \brief get length in bytes of a result field in a result set. > * \param row row of field > diff -urN dbmail-2.1.2.orig/pgsql/dbpgsql.c > dbmail-2.1.2/pgsql/dbpgsql.c > --- dbmail-2.1.2.orig/pgsql/dbpgsql.c 2005-08-08 > 19:40:35.000000000 +0900 > +++ dbmail-2.1.2/pgsql/dbpgsql.c 2005-09-15 > 16:51:57.397077336 +0900 > @@ -36,6 +36,10 @@ > #include <stdlib.h> > #include <string.h> > > +#ifdef USE_PGSQL_BYTEA > +#define BYTEAOID 17 > +#endif > + > const char *TO_CHAR = "TO_CHAR(%s, 'YYYY-MM-DD HH24:MI:SS' > )"; const char *TO_DATE = "TO_TIMESTAMP('%s', 'YYYY-MM-DD > HH:MI:SS')"; const char *SQL_CURRENT_TIMESTAMP = > "CURRENT_TIMESTAMP"; @@ -47,6 +51,12 @@ static PGresult > *stored_res; static u64_t affected_rows; /**< stores the > number of rows affected by the > * the last query */ > +#ifdef USE_PGSQL_BYTEA > +static void _create_binary_table(void); static void > +_free_binary_table(void); static void > _set_binary_table(unsigned row, > +unsigned field); static char*** bintbl = NULL; #endif > db_param_t _db_params; > > int db_connect() > @@ -155,8 +165,54 @@ > if (res != NULL) > PQclear(res); > res = NULL; > +#ifdef USE_PGSQL_BYTEA > + _free_binary_table(); > +#endif > +} > + > +#ifdef USE_PGSQL_BYTEA > +static void _create_binary_table(void){ > + unsigned rows, fields, i; > + rows = db_num_rows(); fields = db_num_fields(); > + > + if(!bintbl){ > + bintbl = (char***)malloc(sizeof(char**) * rows); > + memset(bintbl, 0, sizeof(char**) * rows); > + for(i = 0; i < rows; i++){ > + *(bintbl + i) = > (char**)malloc(sizeof(char*) * fields); > + memset(*(bintbl + i), 0, sizeof(char*) > * fields); > + } > + } > } > > +static void _free_binary_table(void){ > + unsigned rows, fields, i, j; > + rows = db_num_rows(); fields = db_num_fields(); > + > + if(bintbl){ > + for(i = 0; i < rows; i++){ > + for(j = 0; j < fields; j++) > + if(bintbl[i][j]) > + free(bintbl[i][j]); > + free(bintbl[i]); > + } > + free(bintbl); > + bintbl = NULL; > + } > + > +} > +static void _set_binary_table(unsigned row, unsigned field){ > + unsigned char* tmp; > + size_t result_size; > + if(!bintbl[row][field]){ > + tmp = PQunescapeBytea(PQgetvalue(res, row, > field), &result_size); > + bintbl[row][field] = (char*)malloc(result_size + 1); > + memcpy(bintbl[row][field], tmp, result_size); > + PQfreemem(tmp); tmp = NULL; > + bintbl[row][field][result_size] = '\0'; > + } > +} > +#endif > const char *db_get_result(unsigned row, unsigned field) { > if (!res) { > @@ -171,6 +227,13 @@ > __FILE__, __func__, row, field); > return NULL; > } > +#ifdef USE_PGSQL_BYTEA > + if(PQftype(res, field) == BYTEAOID){ > + _create_binary_table(); > + _set_binary_table(row, field); > + return bintbl[row][field]; > + } > +#endif > return PQgetvalue(res, row, field); > } > > @@ -204,7 +267,9 @@ > result set (i.e. it is a SELECT query) > the global res is > set to this temp_res result set */ > - > +#ifdef USE_PGSQL_BYTEA > + _free_binary_table(); > +#endif > if (the_query != NULL) { > trace(TRACE_DEBUG, "%s,%s: " > "executing query [%s]", __FILE__, > __func__, @@ -269,7 +334,19 @@ { > return PQescapeString(to, from, length); } > +#ifdef USE_PGSQL_BYTEA > +unsigned long db_escape_binary(char *to, > + const char *from, unsigned long length) { > + size_t to_length; > + unsigned char *esc_to; > > + esc_to = PQescapeBytea(from, length, &to_length); > + strncpy(to, esc_to, to_length); > + PQfreemem(esc_to); > + return (unsigned long)(to_length - 1); } #endif > int db_do_cleanup(const char **tables, int num_tables) { > int result = 0; > @@ -302,6 +379,13 @@ > __FILE__, __func__, row, field); > return -1; > } > +#ifdef USE_PGSQL_BYTEA > + if(PQftype(res, field) == BYTEAOID){ > + _create_binary_table(); > + _set_binary_table(row, field); > + return strlen(bintbl[row][field]); > + } > +#endif > return PQgetlength(res, row, field); > } > > ========================================================= > > Regards, > > -- > Ryo Hayakawa > [EMAIL PROTECTED] > >
