Forum OpenACS Development: Re: update instead of falling on duplicate acs_objects

Collapse
4: Re: update instead of falling on duplicate acs_objects (response to 1)
Posted by Don Baccus on 06/18/04 01:42 AM
With tables you have to write explicit ALTER TABLE commands to add columns, and you have to know which columns already exist when you do so.

We only do this in old-version-to-new-version update files.

There's nothing in SQL for tables that is equivalent to CREATE OR REPLACE ...

Collapse
5: Re: update instead of falling on duplicate acs_objects (response to 4)
Posted by Eduardo Pérez on 06/18/04 12:39 PM
<blockquote> With tables you have to write explicit ALTER TABLE commands
to add columns, and you have to know which columns already
exist when you do so.
</blockquote>

I know that. I'm asking if it would be a good idea to have a "create or update table " command in (Posgre)SQL to do that updates more easily.

<blockquote> We only do this in old-version-to-new-version update files.
</blockquote>

Of course old-version-to-new-version update files would still be needed but at least "create or update table " wouldn't fail when the table is already created.

<blockquote> There's nothing in SQL for tables that is equivalent to CREATE OR REPLACE ...
</blockquote>

That's what I'm suggesting having something like "create or update table " in (Postgre)SQL.

Collapse
6: Re: update instead of falling on duplicate acs_objects (response to 5)
Posted by Jeff Davis on 06/18/04 02:24 PM
I seriously doubt a "create or update table" would be considered by the postgres team, but if you want to persue it, this is the wrong venue pgsql-hackers or the general list would be better.

There also isn't anything even vaguely like that in any of the SQL standards I don't think.

You might be able to write a plpgsql function that took did it but it would be pretty hairy I think (and to be honest I think the better solution for development is just having drop scripts that work even if the datamodel is only part installed).

Helper functions to do things like check for index existence before creating or dropping an index would be great to have and would probably make upgrading quite a bit more robust.

Collapse
7: Re: update instead of falling on duplicate acs_objects (response to 6)
Posted by Eduardo Pérez on 06/18/04 05:00 PM
<blockquote> I seriously doubt a "create or update table" would be
considered by the postgres team, but if you want to persue
it, this is the wrong venue pgsql-hackers or the general
list would be better.
</blockquote>

I'm asking here first to know if this is a crazy idea or not.

<blockquote> There also isn't anything even vaguely like that in any of
the SQL standards I don't think.
</blockquote>

I also think so, but it may help many SQL applications including OpenACS.

<blockquote> You might be able to write a plpgsql function that took
did it but it would be pretty hairy I think (and to be
honest I think the better solution for development is just
having drop scripts that work even if the datamodel is
only part installed).
</blockquote>

They work OK when they are perfect but in case of any problem you can get a broken app.

<blockquote> Helper functions to do things like check for index
existence before creating or dropping an index would be
great to have and would probably make upgrading quite a
bit more robust.
</blockquote>

What about not falling on "create index" (if there's and index do nothing) or "drop index" (if there's no index do nothing)?

What about "update instead of falling on duplicate acs_objects"?

Collapse
8: Re: update instead of falling on duplicate acs_objects (response to 7)
Posted by Jeff Davis on 06/18/04 05:25 PM
I'm asking here first to know if this is a crazy idea or not.
It is crazy (at least crazy to think anyone who could do it would be interested in doing it).

The fact that nothing like it exists in SQL99 is indicative that it's unlikely that they will be interested. I don't dispute that it might be useful in some circumstances, it's just not going to be implemented.

They work OK when they are perfect but in case of any problem you can get a broken app.

Which is true of most computer things. The work to make the drop script work "perfectly" is a *lot* less than the work to make install scripts magically be able to install on top of a partial install.

What about not falling on "create index" (if there's and index do nothing) or "drop index" (if there's no index do nothing)?
Yes, that's exactly what I meant.

As for updating instead of failing on duplicate acs_object_types, I don;t think you could write something that did not run the risk of updating the wrong acs_object_type. I think I would prefer an error in this case.

Collapse
9: Re: update instead of falling on duplicate acs_objects (response to 8)
Posted by Eduardo Pérez on 06/28/04 05:53 PM
<blockquote> As for updating instead of failing on duplicate
acs_object_types, I don;t think you could write something that
did not run the risk of updating the wrong acs_object_type.
I think I would prefer an error in this case.
</blockquote>

Most system administrators (users) don't like errors. They prefer getting the package installed.
"updating the wrong acs_object_type" is an error anyway and I prefer getting the application working.