Re: BUG #1643: dbf2pg broken + quick fix

Tom, where are we on this patch?  Seems we need to do something.

Here is the thread:

    http://archives.postgresql.org/pgsql-bugs/2005-05/msg00008.php

---------------------------------------------------------------------------

Tom Lane wrote:
> Boris van Schooten <schooten@cs.utwente.nl> writes:
> > Don't know anything about nulls in dbf though.  I am not a dbase expert, I
> > just run into dbfs often when trying to enter gis data into postgis.
>
> I'm considering the following patch, which turns around the test: check
> for an empty string and if so believe it's a null, otherwise just insert
> the value as-is.  I dunno if the check for null is actually meaningful,
> but I doubt this will break any cases that worked before.  Comments anyone?
>
>             regards, tom lane
>
>
> Index: contrib/dbase/dbf2pg.c
> ===================================================================
> RCS file: /cvsroot/pgsql/contrib/dbase/dbf2pg.c,v
> retrieving revision 1.21
> diff -c -r1.21 dbf2pg.c
> *** contrib/dbase/dbf2pg.c    14 Sep 2004 03:28:28 -0000    1.21
> --- contrib/dbase/dbf2pg.c    4 May 2005 17:55:29 -0000
> ***************
> *** 63,93 ****
>   char       *convert_charset(char *string);
>   #endif
>   void        usage(void);
> - unsigned int isinteger(char *);
>
>
> -
> - unsigned int
> - isinteger(char *buff)
> - {
> -     char       *i = buff;
> -
> -     while (*i != '\0')
> -     {
> -         if (i == buff)
> -             if ((*i == '-') ||
> -                 (*i == '+'))
> -             {
> -                 i++;
> -                 continue;
> -             }
> -         if (!isdigit((unsigned char) *i))
> -             return 0;
> -         i++;
> -     }
> -     return 1;
> - }
> -
>   static inline void
>   strtoupper(char *string)
>   {
> --- 63,70 ----
> ***************
> *** 471,478 ****
>                   /* handle the date first - liuk */
>                   if (fields[h].db_type == 'D')
>                   {
> !                     if ((strlen(foo) == 8) && isinteger(foo))
>                       {
>                           snprintf(pgdate, 11, "%c%c%c%c-%c%c-%c%c",
>                                    foo[0], foo[1], foo[2], foo[3],
>                                    foo[4], foo[5], foo[6], foo[7]);
> --- 448,462 ----
>                   /* handle the date first - liuk */
>                   if (fields[h].db_type == 'D')
>                   {
> !                     if (strlen(foo) == 0)
>                       {
> +                         /* assume empty string means a NULL */
> +                         strcat(query, "\\N");
> +                     }
> +                     else if (strlen(foo) == 8 &&
> +                              strspn(foo, "0123456789") == 8)
> +                     {
> +                         /* transform YYYYMMDD to Postgres style */
>                           snprintf(pgdate, 11, "%c%c%c%c-%c%c-%c%c",
>                                    foo[0], foo[1], foo[2], foo[3],
>                                    foo[4], foo[5], foo[6], foo[7]);
> ***************
> *** 480,505 ****
>                       }
>                       else
>                       {
> !                         /*
> !                          * empty field must be inserted as NULL value in
> !                          * this way
> !                          */
> !                         strcat(query, "\\N");
>                       }
>                   }
> !                 else if ((fields[h].db_type == 'N') &&
> !                          (fields[h].db_dec == 0))
>                   {
> !                     if (isinteger(foo))
> !                         strcat(query, foo);
> !                     else
>                       {
>                           strcat(query, "\\N");
> -                         if (verbose)
> -                             fprintf(stderr, "Illegal numeric value found "
> -                                     "in record %d, field \"%s\"\n",
> -                                     i, fields[h].db_name);
>                       }
>                   }
>                   else
>                   {
> --- 464,482 ----
>                       }
>                       else
>                       {
> !                         /* try to insert it as-is */
> !                         strcat(query, foo);
>                       }
>                   }
> !                 else if (fields[h].db_type == 'N')
>                   {
> !                     if (strlen(foo) == 0)
>                       {
> +                         /* assume empty string means a NULL */
>                           strcat(query, "\\N");
>                       }
> +                     else
> +                         strcat(query, foo);
>                   }
>                   else
>                   {
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073