[postgis-devel] dbf attributes size

[strk at refractions.net]

On Mon, Oct 24, 2005 at 05:45:04PM +0200, Markus Schaber wrote:
> Hi, Strk,
> 
> strk at refractions.net wrote:
> >>So we get a cyle from int2->DBF_6->int4->DBF_11->int8->DBF_20->numeric.
> > 
> > Wait a second, there should be no loop in fact:
> > 
> > 	int2->DBF_6->int2
> 
> Well, when reading a DBF_6 you may get -99999 which cannot be saved into
> an int2.

Oops... you're right, this means mapping needs to be different 
depending on direction:

       [LOADING]
       DBF_SIZE        SQL_TYPE        LONGEST_TEXT_REP
       0-5             int2            -99999
       6-10            int4            -9999999999
       11-19           int8            -9999999999999999999
       20-x            numeric(size,0)

       [DUMPING]
       SQL_TYPE        DBF_SIZE        LONGEST_TEXT_REP
       int2            6               -32768
       int4            11              -2147483648
       int8            20              -9223372036854775808
       20-x            numeric(size,0)

I've committed a fix for this.

The loop comes back now :/

	DBF_2->int2->DBF_6->int4->DBF_11->int8->DBF_20->numeric.

Actually we do have a getMaxFieldSize function already in the
dumper.

Up to the last release it was only used for string
fields (bpchar,varchar).

In CVS it is only used for bpchar,varchar when NO limits 
are set on their lenght (atttypmod). This was requested
on postgis-users by the beginning of October:
http://postgis.refractions.net/pipermail/postgis-devel/2005-October/001589.html

What we could do is provide a switch to ALWAYS use the getMaxFieldSize
function, so to obtain the smallest dbf.

One problem I can see with this is that floating numbers size would
depend on how PostgreSQL write them with the canonical output.
We might be able to set constraints using numeric(), but that would
reduce SQL performance (numeric is a variable lenght type).

Comments ?

--strk;