Musings on psLib PS_META_STR mapping to SQL99 varchar

[aaron.culliney@…]

This is a brief summary of psDB design issues that Aaron Culliney and Josh
Hoblitt talked about in a recent phone conversation, in particular the
ambiguities in mapping PS_META_STR to SQL type(s). What follows does not
necessarily constitute bugs in the software, but more design assumptions that
could be subject to future change. (Hopefully Bugzilla is the best place for
this to be documented :)

...

Note that the SDRS PSDC-430-007-13 section 5.3.2 mentions mapping a PS_META_STR
to a SQL99 VARCHAR.

1) Should this assumption be made? Is there any chance that software using
psLib would want to insert various sizes of BLOB or TEXT types?

2) How should psLib handle truncation of strings if they are greater than 256
characters (max VARCHAR size)? Currently the string data is passed to MySQL
with no bounds checking where it is assumed MySQL silently truncates it. Should
psLib itself do a bounds check/truncation and warn about this?

...

Furthermore three psDB functions, (psDBSelectRows, psDBUpdateRows,
psDBDeleteRows), contain a psMetadata* where variable. At the end of section
5.3.2 of the SDRS mention is made of this limitation compared to the richness of
SQL99 where semantics. Currently the psDB implementation is to compare for
exact matches for each of the items in the where psMetadata against the
corresponding fields in the table, except for PS_META_STR items which are
compared using MySQL "LIKE" comparison. This allows both for exact (but
case-insensitive) comparison of string values and also glob comparison. For
example to match against "Haleakala" in any sentence, you could look for
"%Haleakala%".

1) Should comparisons be done this way? MySQL "LIKE" has gotten a bad rap for
performance, so do we care about this?

2) If BLOB or TEXT types were present in the table and selected against in the
where clause, their comparison (either a straight compare or "LIKE") would
really bog down the calling software.

3) Also, does this make sense for comparing floating point numbers between psLib
and the DB? Shouldn't there be some user-specified small +/-episilon error
allowance in the comparisons?

...

It could be that these are all non-issues for the intended use of the psLib DB
functions, but I felt they at least needed to be metioned.

[jhoblitt]

I'm actually a little concerned about floating point comparision. I suppose it
would be reasonable to impliment some sort of default epsilion (set at compile
time) for F32/F64 comparisions if we heavily documented it.

I did some digging and I found a simple SQL receipe in the MySQL manual.

​http://dev.mysql.com/doc/mysql/en/problems-with-float.html

What do you think?

[jhoblitt]

Paul Price has removed that we use the epsilon values from float.h

float epsilon is defined as FLT_EPSILON
double epsilon is defined as DBL_EPSILON

This would be problematic if the database and client are on different platforms
but it seems like a good place to start.

[jhoblitt]

s/removed/suggested/

[aaron.culliney@…]

Both the MySQL and C language suggestions for handling epsilon values looks
good. Maybe we can compare the float.h values on various OS/Arch to see if
there would be a problem. Or maybe we can just have a psLib default definition
of epsilon (possibly configurable through other non-DB functions) that is used
for float/double equality checks with the DB routines and elsewhere.

[jhoblitt]

My best guess is that the float.h value for epislon is fairly consistent between
platforms. I'd be surprised if IEEE754 compliant archs had different values and
we don't currently care about any archs that don't support IEEE754.

I think it's safe to move foward using float.h for the time being as it's
trivial to change later.

[Paul Price]

Is this thread resolved?

[jhoblitt]

I don't believe so.

[jhoblitt]

float/double comparisons have been fully implemented.