Database updates

[John Locke <..hidden..>]

Hi,

Couple issues/thoughts.

1. Errors on upgrade sql
2. Normalize/compare installed schema vs files?



1. Just updating my instance using the upgrade sql files, and got these
errors:

            
> freelockco=# \i sql/upgrade/3497-schema-changes.sql
> psql:sql/upgrade/3497-schema-changes.sql:1: ERROR:  syntax error at or
> near "trans_id"
> LINE 1: ALTER TABLE invoice ADD FOREIGN KEY trans_id REFERENCES tran...
>                                             ^
> psql:sql/upgrade/3497-schema-changes.sql:2: ERROR:  syntax error at or
> near "parts_id"
> LINE 1: ALTER TABLE invoice ADD FOREIGN KEY parts_id REFERENCES part...
>                                             ^
> psql:sql/upgrade/3497-schema-changes.sql:4: ERROR:  syntax error at or
> near "chart_id"
> LINE 1: ALTER TABLE tax ADD FOREIGN KEY chart_id REFERENCES account(...
>                                         ^
> psql:sql/upgrade/3497-schema-changes.sql:5: ERROR:  syntax error at or
> near ";"
> LINE 1: ...IGGER ap_audit_trail AFTER insert or update or delete ON ap;
                                                          ^

... I'm using postgresql 8.3.12, if it makes any difference. I did see
some similar errors a few days ago, when updating trunk from a ~2 week
old version.

Which brings me to part 2:

2. What would be the best way to get a consistent, repeatable, text dump
of the table schema only, along with views, types, and triggers?

I'm thinking back to an earlier discussion about database updates, and
having a shell tool to manage them. I'm thinking we should be able to
dump the structural parts of the database to a single file (e.g.
Pg-database.sql) that we can then easily compare to what's in the code
base. I assume this would mean splitting out the functions and the data
that's currently loaded into Pg-database.sql into separate files -- but
once that's done, it should be trivial to see how close the structure of
your database is to the current release, and what needs to be altered to
get it there. Which would be the first step to auto-generating update
scripts.

Thoughts?

Cheers,
John Locke
http://www.freelock.com