Re: Database updates

[Chris Travers <..hidden..>]

On Wed, Jul 13, 2011 at 2:04 PM, John Locke <..hidden..> wrote:
> Hi,
>
> Couple issues/thoughts.
>
> 1. Errors on upgrade sql

Those were reported to me this am.  I corrected those in svn rev 3500.

Exhibit A on "why never to commit during short intervals at airports."
 Sorry for the inconvenience.

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

Let me think about how best to do this.  It's slightly complicated by
the presence of the external dependencies from pg_contrib.

Ideally I'd like to have something that's easily repeatable and
generated from an authoritative source.  I have an idea.  Will try
this later today.

 Best Wishes,
Chris Travers

>
> Cheers,
> John Locke
> http://www.freelock.com
>
>
>
> ------------------------------------------------------------------------------
> AppSumo Presents a FREE Video for the SourceForge Community by Eric
> Ries, the creator of the Lean Startup Methodology on "Lean Startup
> Secrets Revealed." This video shows you how to validate your ideas,
> optimize your ideas and identify your business strategy.
> http://p.sf.net/sfu/appsumosfdev2dev
> _______________________________________________
> Ledger-smb-devel mailing list
> ..hidden..
> https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
>