Creating up to date database seed automatically

[banym]

Hi,

after some discussion in the irc channel there came up the idea to create a Jenkins job to have an up to date database seed every time there are some changes made in the repository.
From my side the plan should be to have a up to date database seed which maybe can be included or downloaded at installation time or is available in a repository for download.

I would propose to create this seed using Jenkins because it can monitor the repository as it does for the code repository and run a build if there is a change.

My questions:
* How many seeds should we store?
* Should the tool be able to create older seeds based on a special revision of the repository?
* Is it practical to deliver the the software and the database seed separated? (It would the installer make a little bit smaller)

Regards,

Dominik

[red1]

As Carlos has raised the issue of big size of having seeds at every change i would think to maintain seeds only for last few builds. Beyond maybe 10 builds, the DB seeds should be removed. Thus the end user will have to rebuild its own DB with previous official DB seeds plus migration scripts. We have to put a clear note how to do that and where to find the migration scripts.

But then, if your idea to create DB seeds only on change of DB or new migration script, then that sounds creative too. I think even a stop gap separate DB seed in that sense will be good. That means a DB seed at every nth migration seed, or on a weekly basis. In fact this is how i first remembered Compiere was first done in SourceForge in 2003.

[globalqss]

Thanks Dominik for bringing this here.

Following your idea I would suggest:

- Create a new repository just for seeds and it would be great to have a XML or text representations (oracle/postgresql) of the databases (see below the purpose)
- Jenkins task to recreate seeds and XML/text representation whenever a migration script is committed, and commit the resulting files on the new repository
- Note that modifying/deleting a migration script would be hard to manage unless we have a "starting point" - so I suggest we keep the actual "last version" seed as the initial point to create the permanent seed
- Jenkins could verify and notify any error on script oracle or postgresql
- the installer creation process can be changed to take the new seed instead of the original (that we still keep and upload per release)

Purpose of XML/text representation of databases:

- historically I've found really hard to realize what a migration script is doing on the database. QA/peer review of java changes is doable most of the times just with the commit email, but QA/peer reviewing a migration script is very hard (you can check the consistency of the script, but not easily the consistency of the change being made on DB).
And the bigger the script the harder to review it - as an example look the 047_libero_migration_script.sql (4.5MB) that broke a lot of things on database that were fixed before, and/or introduced lots of new dictionary issues.

- the second purpose is also catch up differences between the oracle vs postgres migration script - that's a common source of errors also, and very, very hard to detect. But easy to detect if we have a XML/text representation that can be comparable

WDYT?

Regards,

Carlos Ruiz

[banym]

Hi,

o.k so are there some standards for sql expressions in an abstract xml language?

Next step would be to write the changes done on the last seed in this new expression language and create a tool which can be used by jenkins to apply this changes and deploy and test it on postgres and oracle?

I have not really an idea how to review the differences between the databases. What is you proposal here?

Regards,

Dominik

[globalqss]

Hi Dominik, no, I'm not meaning writing the SQL migration scripts in XML.

My intention is to have one or several XMLs after the migration script is applied on postgresql and oracle.
These XMLs (or text) would be a representation of the database - it would be even better if it can be written in a readable format (like the old 2pack data).
I tried some time ago to achieve such goal with 2pack but failed because of the size of the file (there must be a documentation of that POC somewhere).

We could think also about using DDLUTILS - this tool can write the schema an data in a comparable XML format.
NOTE: My preference on old 2pack over DDLUTILS is because old 2pack translate the _ID to the corresponding name or code of the related table, making the peer review even better. But having just the IDs can be fine also as an initial step.

> I have not really an idea how to review the differences between the databases. What is you proposal here?

Having the comparable XMLs for both (oracle and postgresql) then a comparison is simply a diff.

Regards,

Carlos Ruiz