Re: TIP#308: Choice of dict as the row representation

On Nov 17, 2:41 pm, "tom.rmadilo" <tom.rmad...@xxxxxxxxx> wrote:
On Nov 17, 11:18 am, Kevin Kenny <kenn...@xxxxxxx> wrote:

Nevertheless, we have to reconcile the possibility of duplicate column
names with the possibility of NULLs. Here, I'm really tempted to
place the onus on the database driver to disambiguate the names, so
that dictionaries can still be used. I'd like to get some input from
actual driver implementors - if you don't maintain a database
interface, you probably don't have the necessary experience to comment
- about whether this constraint would be acceptable.

NULL values have nothing to do with RDBMSs, at least with the Algebra
of Relations. This algebra is based upon perfect information. NULLs by
definition have no meaning. If the need to handle NULLs is the reason
for using a dict, man, what a bad reason. Even if a dict can
understand NULL values, Tcl doesn't. You still would have to
explicitly test for a NULL value, how can you eliminate this test? To
me it seem much more important to be able to insert a NULL value,
because either the interface or the database itself can substitute in
a default. In addition, assuming that you can use NULLs in a query,
you can specifically tell the database to insert whatever substitute
for NULL you want:

select
b.*,
rtrim(to_char(b.content_date, 'Day')) as day,
to_char(b.content_date,'Month DD, YYYY') as date,
case
when content_template_id is NULL
then '$default_content_template'
else
'$template_root/' || content_template_id
end
as template
from
cams_content b
where
b.content_type_id = :content_type_id
and
b.archive_p = 'f'
$ids_sql
$where_clause_limit
order by
$sort_by
$limit_clause

This API ends up using arrays, so column ordering isn't possible. $
vars are safe and :content_type_id is a bind var supplied by the user.
But also a NULL value is handled by substituting in a useful value, as
determined by the immediate circumstance.



Oratcl currently supports allowing the user to decide if the rows will
be returned as a list 'or' to choose the result s to be stored in a
tcl hash. With a hash member for each column name.

Some have pointed out that SQL allows you to have multiple columns
with the same name.

Oracle does allow this.

SQL> select u_id, u_id, u_id from uni;

U_ID U_ID U_ID
---------- ---------- ----------
1 1 1
2 2 2
1 1 1
2 2 2
1 1 1
2 2 2

6 rows selected.



And my Oratcl extension using Tcl Hash's (arrays) would break with
this kind of query. I never considered this before. Thanks for
bringing it up..

-Todd
.

Relevant Pages

  • Re: TIP#308: Choice of dict as the row representation
    ... names with the possibility of NULLs. ... place the onus on the database driver to disambiguate the names, ... you can specifically tell the database to insert whatever substitute ...
    (comp.lang.tcl)
  • Re: What does this NULL mean?
    ... > always will be NULLS in the real world." ... > comp.databases.theory thread "Does Codd's view of a relational database ... > presenting lots of possible reasons was a very bad argument because only ... management of systems with evolving schema, ...
    (comp.databases.theory)
  • Re: Newie Question (or am I nuts)
    ... It is a fundamental part of the Relational Model which Dr. E. F. ... database really relational?". ... > The problem is that c and c++ don't have the concept of nulls. ... > nulls in VB (or, in our case the VBA, but for all intensive purposes VB ...
    (microsoft.public.access.formscoding)
  • Re: What does this NULL mean?
    ... out of existence have no demonstrated understanding of the database change management environment, and specifically the management of schema evolution and its consequences in regard to the generation of nulls. ... An exception would be when he starts out by defining a Relation the way he does in his Intro to Database Systems, ... eliminating SQL from the ... ever materialises would lead to an exponential number of relvars with ...
    (comp.databases.theory)
  • Re: Linking tables
    ... Employee tables could have a 1 to 1 relationship with the Engineer table. ... Elimination of Nulls, Nulls cause problems in a database because they are ... Null or some database engine managed Default. ...
    (microsoft.public.access.tablesdbdesign)