Talk:Deployment

From MapbenderWiki

Jump to: navigation, search

Contents

[edit]

Database deployment

[edit]

Dia & tedia2sql

[edit]

concept

We still don't have an automated routine to generate the SQL dumps for a new release. The problems are the schema SQLs, as the data SQL is pretty much exchangeable for MySQL and PostgreSQL.

I was thinking about this: Maybe create the ER model in Dia, and then use tedia2sql to export the model to MySQL, PostgreSQL etc. The export is basically a Perl script which parses the XML saved by Dia. You have to misuse the UML syntax to get results though. A class corresponds to a table, an attribute corresponds to a column etc.

We would have a) a tool to maintain the database structure b) an up-to-date image to teach the db structure to new Mapbender devs c) various output formats like MySQL, PostgreSQL, Oracle etc.

There is even a tool to import PostgreSQL databases into Dia, postgresql_autodoc, but it is far from perfect, but could be at least helpful.

I have made a prototypical Dia file, covering only three tables. But it seems to work for both MySQL and PostgreSQL.

Please share your valued opinion. Maybe you know a better way to solve this problem.

[edit]

steps

(1) Create the schema in Dia, here a screenshot of the prototype:

Image:Db depl dia.png

(2) SQL export via tedia2sql

MySQL (note: use innodb and not mysql, because its output is MyISAM. 

tedia2sql -t innodb -d -s -o <target folder>/<target filename>.sql -i <input filename>.dia

PostgreSQL 

tedia2sql -t postgres -d -s -o <target folder>/<target filename>.sql -i <input filename>.dia

You have plenty of files in your target folder, only a few are needed 

schemaCreate.sql
constraintsCreate.sql
specialPreStatements.sql
specialPostStatements.sql // only for PostgreSQL, contains the sequence update

You would import the data SQL after the specialPreStatements.

[edit]

concerns

  • For updates, we would still need a special set of SQLs.
  • For DB-specific features like auto_increment, you have to do some tweaking (see right side of picture above). So if we added a new database (Oracle), I guess we would again have work, but not more than with editing the dump manually.
Retrieved from "http://www.mapbender.org/Talk:Deployment"
Personal tools