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

<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.

<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"?

<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.