id	summary	reporter	owner	description	type	status	priority	milestone	component	version	severity	resolution	keywords	cc
400	Musings on psLib PS_META_STR mapping to SQL99 varchar	aaron.culliney@…	jhoblitt	"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."	defect	closed	high		PSLib SDRS	unspecified	normal	fixed