[DB-SIG] dBase III and VFP tables

Chris Clark Chris.Clark at ingres.com
Wed Jul 20 17:50:58 CEST 2011


Carl Karsten wrote:
> On Tue, Jul 19, 2011 at 6:39 PM, Ethan Furman <ethan at stoneleaf.us> wrote:
>   
>> I am finally adding Null support to my dbf package
>> (http://pypi.python.org/pypi/dbf) and, as usual, the VFP side is being a
>> pain.
>>
>> The issue is that the Logical, Date, and DateTime field types, even when the
>> table does not allow Null values, can be empty -- and when Null values are
>> allowed, those fields can be empty or Null.  (Empty pretty much meaning not
>> initialized -- neither True nor False, and no Date nor Time.)
>>
>> At this point, also, my module does not support the DB API (that is a goal
>> though), so right now you pretty much get direct access to the physical
>> table.
>>     

Take a look at engine (formally SnakeSQL) and squawk for ideas on adding 
both SQL and a dbapi interface.

http://pypi.python.org/pypi/engine/
http://pypi.python.org/pypi/SnakeSQL/

http://pypi.python.org/pypi/squawk/

I've not used either so this isn't a recommendation but they look very cool.

>> My question:  for the direct access side, is there any advantage to treating
>> an empty L, D, or T field differently from a Null L, D, or T field?  Or
>> should they be semantically the same, always returning, for example, None
>> whether it's actually Null in the table or just empty?
>>     
>
> for those of you that are wondering about this nuttyness:  VFP is a
> dBase derivative.  In there early days, there was no support for Null.
>  But a side effect of adding a row but not storing values to it was
> empty fields.  the best example is a date.  dates are stored on disk
> as a string "YYYYMMDD"  and in the case of an empty date: "        ".
> Same with numbers: N(6,2) would store 3.14 as "  3.14" and an empty
> number was "      ".  Then they added Null support, but instead of
> using this empty state, they added a flag, so now a field could be
> Null, Blank or have a value.  which was a mistake, but here we are.
>
> For D/T I am not sure you have a choice.  What value would you return
> for an empty date other than Null?
>
> for Logical I would return False.  Mainly because I am pretty sure
> that because in VFP an empty logical will be treated as .f. anywhere a
> logical is evaluated.
>   

My 2 cents, it looks like the "type" number "n(6,2)" is really a string 
with some restrictions on formatting (i.e. only contains digits, a 
single period, and spaces). You could simple return strings for 
everything. An optional (and configurable) type conversion layer could 
then be used on top of that.

Ingres has an old (non ANSI-) date type that we still support, this old 
date can be empty (empty string '' rather than blank padded as described 
in earlier in the thread). IMHO dbapi drivers can't go returning empty 
strings for dates (as this is wildly incompatible with other DBMSs), we 
return a "magic" date for empty dates (the year 9999). It is reasonable 
to add an extension return type that supports empty, e.g. one could then 
do checks like:

if my_result is empty:
    ....

But I'd suggest that empty support NOT be enabled by default. So lots of 
options :-S


Chris



More information about the DB-SIG mailing list