Re: full featured alter table?

Jan Wieck <JanWieck@yahoo.com> writes:

> Disagreed. There can not be any "query that fetched it" because it didn't exist
> before. And if the surrounding application issuing that query didn't have that
> attribute before, what's it going to do with it now? It will hopefully just
> swallow it silently.

Sorry, dangling pronoun. "it" would have to be the entity, not the attribute.

> > Whereas if you use select * and make the presentation code handle any columns
> > that appear in the result then adding a new column can be done purely in the
> > database. The query and the presentation layer can adapt without changes.
>
> Ah ... we are talking about presentation code only. I have seen code that does
> a SELECT * from a table having multi-megabyte sized BLOB's only to check IF
> FOUND afterwards ... saving a few keystrokes can be expensive.

Well that's just one reason storing multi-megabyte objects in databases is
usually a bad idea. Certainly it would be pretty unlikely and almost certainly
a bad idea to have an existing entity that suddenly grows a multimegabyte
attribute you hadn't thought of before.

> In the case you have dynamic presentation code that handles this sort of thing,
> you are IMHO supposed to let it query a view. And this view has to be a
> separate view just for this "presentation" (view is a synonym for that, isn't
> it?). That way you are free to change the tables layout in whatever way you
> want to, from adding/renaming/dropping columns, splitting up the attributes
> into separate tables or changing data types ... whatever fits your "technical"
> needs. You can allways adjust the view so that the presentation will be OK
> again.

Bleagh. what a way to create tons of extra work for nothing. So now every time
I add a new attribute to an existing entity I not only have to create the new
column in the table, and create the new presentation code to handle the
attribute, I not only have to check through all the queries that fetch the
data for presentation and add the columns there, I now have to also check
through all the views those queries use and add the columns there as well?

The goal here is to avoid having to adjust ten million places every time you
make a minor change to internal data. It's the whole reason ADTs and objects
were invented. To avoid having to have every piece of code know the internals
of every data type. What all the attributes of an entity are is just such an
internal that should be hidden from code that doesn't need it. Unfortunately
the best SQL can do on that front is SELECT * and even that doesn't work too
well. But it's better than hard coding explicit lists of attributes everywhere
throughout the application.

> > Of course it's not always possible to achieve this 100%. Often the
> > presentation layer wants to do specific formatting for each attribute and
> > doesn't want to display every single attribute. But using select * means
> > there's one fewer place that needs to be edited to effect the new column.
>
> Using SELECT * means that there is one more place to carefully check for
> possible side effects.

sure, except the "possible side effect" is the addition of one more attribute
to the select list, which would be precisely what you're hoping to accomplish
by adding the column.

> > This all depends on having a driver that gives you an API that lets you access
> > columns by name rather than position though.
>
> You need an API that gives you access to the list of attribute names first, and
> then dynamically access those attributes. PL/pgSQL for one does not have this
> capability.

Well, to avoid hard coding the column names in the query you just need to be
able to access the columns by name. To avoid hard coding them in the
presentation layer then yes you would need to be able to retrieve the list of
column names, which most interfaces do in fact provide. I don't think PL/PgSQL
would be my first choice for writing presentation layer code in though. That
said, avoiding it in the presentation layer is usually a lost cause anyways.

--
greg