On 2/10/2016 5:36 PM, David G. Johnston wrote:
> There is a process:
> In the empty base creates the necessary data structure.
> Then, based on the contents of the tables "pg_catalog" obtain and
> maintain the description of this structure (snapshot).
> In the future, comparing the (snapshot) with another (snapshot)
> received from the production database, a decision about the difference
> between the structures and the need to transform the production database.
>
> The essence of the problem :
> Developer creates check constrait according to [1] describe the bug.
> In reference (snapshot)to store the result of [1] describe the bug.
> The production database is missing the check constraint and the
> decision of its creation.
> Add instruction is generated from the reference (snapshot)and
> corresponds to [2] describe the bug.
> After this addition, we obtain check constraitwhich is functionally
> equivalent to the reference, but differs from the description of the
> reference.
> And this in turn causes a re-creation check constraitfor each of the
> subsequent verification of the production database structure.
we follow a decidedly different and IMHO simpler process.
1) data base designer creates a .SQL file that generates schema version
1. this is checked into source code control along with the application
components, and is used to initialize a new database.
2) database designer/developer creates a delta .SQL file that will
update X to X+1, and a .SQL file that will create X+1 from scratch.
these are both checked into source code control.
production either runs the delta update file, or the current full schema
file as appropriate. we keep a row in our 'settings table with
setting='schema_version', value='X' (for version X), so automated
installation scripts can apply the correct updates in order.
this process lets us update 'static data' as well as schema structure
(for some databases, the static data has its own versioning, and
seperate set of .sql scripts)
--
john r pierce, recycling bits in santa cruz