Martin Stjernholm wrote:
"Stephen R. van den Berg" srb@cuci.nl wrote:
Pike 7.8 returns Pike UNDEFINED objects in Sql.Sql queries for columns that return NULL values (as opposed to integer zero); Pike <7.8 returned integer zeros.
On the pike level the interface isn't ambiguous as it was on the RXML level, since all other values are returned as strings. It's therefore not a problem returning either a normal zero or UNDEFINED for an sql NULL.
Therefore I don't really see the point with the change in 7.8. Note also that a mapping cannot carry UNDEFINED as a value for natural reasons, so in Sql.query() those UNDEFINED's still becomes normal zeroes.
Aaargh. Didn't know that.
The fact that all non-NULL values are returned as strings is another problem in the Sql module, but it does avoid this particular problem. If a better API is added to it, where integers are returned as ints etc, then there should be an SqlNull object there too for sql NULLs. Using UNDEFINED for it would be abuse (see below). (Further discussions about this should go to the pike dev list.)
Well, actually, a cursory check amongst database backends in the current (and older) Pike versions shows:
Returns
- Mysql string/float/int - Oracle string/float/int - pgsql string/float/int - Odbc string/wide-string - Postgres string
So the current backend database API actually desires native types. I.e. using UNDEFINED for NULL there is desirable (because queries to Mysql/Oracle/pgsql cannot distinguish NULL and integer-zero values now) and works.
Those changes shouldn't be necessary since all non-null values are strings. If your sql glue does something else then it doesn't adhere to the Sql module API.
Well, actually it does, since I copied the behaviour from the Mysql low-level driver.
Btw, I wouldn't call the UNDEFINED value in pike an object. It's not, and it'd be a bit misleading to think of it as one.
Well, in light of learning that mappings cannot contain UNDEFINEDs, I propose to introduce the following SqlNULL() type which should be returned by any mappingvalues from Sql.Sql()->query() which are actually an SQL NULL type.
Any thoughts? If there are no objections I'd like to get it into 7.8. The SqlNULL type is carefully tuned to be transparent for most normal uses (casts, comparisons, printing and truth-values).
commit b68690a72d6855ecc187a4be35037cb080db69d4 Author: Stephen R. van den Berg srb@cuci.nl Date: Fri Aug 22 13:01:00 2008 +0200
Sql: Use SqlNULL to represent NULL values returned from Sql.query()
diff --git a/CHANGES b/CHANGES index f1c8f6f..060b5db 100644 --- a/CHANGES +++ b/CHANGES @@ -691,6 +691,8 @@ o Regexp.PCRE.Widestring
o Sql
+ - Return an SqlNULL() object for NULL values in Sql.Sql()->query() results + - Bugfixes in listing Postgres fields.
- If ENABLE_SPAWN_RSQLD is defined, rsqld will be spawned when diff --git a/lib/modules/Sql.pmod/Sql.pike b/lib/modules/Sql.pmod/Sql.pike index 3ec85d5..98a7dac 100644 --- a/lib/modules/Sql.pmod/Sql.pike +++ b/lib/modules/Sql.pmod/Sql.pike @@ -339,6 +339,16 @@ protected string _sprintf(int type, mapping|void flags) return sprintf("Sql.%O", master_sql);
+// Actually the following object is a constant and needs only +// one instance per Pike runtime +.sql_util.SqlNULL SqlNULL = .sql_util.SqlNULL(); + +final protected void replace_SqlNULL(array(mixed) row) { + foreach(row;int i;mixed value) + if(zero_type(value)) + row[i] = SqlNULL; +} + protected array(mapping(string:mixed)) res_obj_to_array(object res_obj) { if (res_obj) @@ -366,11 +376,15 @@ protected array(mapping(string:mixed)) res_obj_to_array(object res_obj) fieldnames = map(fieldnames, lower_case);
if(has_table) - while (row = res_obj->fetch_row()) + while (row = res_obj->fetch_row()) { + replace_SqlNULL(row); res += ({ mkmapping(fieldnames, row + row) }); + } else - while (row = res_obj->fetch_row()) + while (row = res_obj->fetch_row()) { + replace_SqlNULL(row); res += ({ mkmapping(fieldnames, row) }); + }
return res;
diff --git a/lib/modules/Sql.pmod/sql_util.pmod b/lib/modules/Sql.pmod/sql_util.pmod index 9a0bb92..482538d 100644 --- a/lib/modules/Sql.pmod/sql_util.pmod +++ b/lib/modules/Sql.pmod/sql_util.pmod @@ -207,3 +207,61 @@ class MySQLBrokenUnicodeWrapper
#endif + +class SqlNULL +{ + constant is_SqlNULL = 1; + + // Treat these objects as indistinguishable from each other. We + // ought to ensure that there's only one in the pike process + // instead, but that's tricky to solve in the PCode codec. + protected int `== (mixed other) { + return zero_type(other) + || objectp(other) && other->is_SqlNULL; + } + + protected int `! () { + return 1; + } + + protected int(0..1) _is_type(mixed type) { + return 1; + } + + protected mixed cast(string to) { + mixed ret=UNDEFINED; + switch(to) { + case "string": + ret=""; + break; + case "float": + ret=Math.nan; + break; + case "mixed":case "object": + ret=this; + } + return ret; + } + + protected int __hash() { + return 17; + } + + protected string _sprintf (int flag) { + string ret=UNDEFINED; + switch(flag) { + case 'O':ret="SqlNULL()"; + break; + case 's':case 'd':case 'f':case 'g':ret=""; + break; + } + return ret; + } + + int _encode() { + return 0; + } + + void _decode (int dummy) { + } +}