[jira] [Commented] (AIRAVATA-3326) Improved database migration script creation

["ASF subversion and git services (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/AIRAVATA-3326?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17156732#comment-17156732 ] 

ASF subversion and git services commented on AIRAVATA-3326:
-----------------------------------------------------------

Commit bff2ee2bba90cbfe97f9d3b4eda3f8924a04f5fa in airavata's branch refs/heads/master from Marcus Christie
[ https://gitbox.apache.org/repos/asf?p=airavata.git;h=bff2ee2 ]

AIRAVATA-3326 Remove unused code


> Improved database migration script creation
> -------------------------------------------
>
>                 Key: AIRAVATA-3326
>                 URL: https://issues.apache.org/jira/browse/AIRAVATA-3326
>             Project: Airavata
>          Issue Type: New Feature
>          Components: Registry API
>            Reporter: Marcus Christie
>            Assignee: Marcus Christie
>            Priority: Major
>
> Improve automatic generation of database migration scripts. Work on this was started in AIRAVATA-3126. In that issue the OpenJPA MappingTool was integrated to run and generate a migration script whenever the schema validation fails.
> Continuing on that work, I'd like to
> - create a migration script generation tool (MappingTool) that can be run directly from the command line
> - integrate starting a MariaDB, perhaps via docker-compose, and run MappingTool against that to generate the mysql scripts
> -- use https://github.com/dkanejs/docker-compose-maven-plugin to have maven run
>   docker-compose. The command would end up being something like so:
>   {noformat}
>   mvn docker-compose:up exec:exec@generate-migrations docker-compose:down
>   {noformat}
> - finish the documentation on how to take the generated scripts and integrate them in the various places in the code base where they belong
> - change ide-migration docker-compose database server to mariadb. MariaDB has some good additions to mysql for creating migration scripts that can be re-runnable (e.g., {{IF NOT EXISTS}}).
> - clean up remove generate-sql-persistence.xml and maven goal that used it
> Questions
> - when there are new/changed tables in more than one schema, what migration script gets created? I'm guessing that they will clobber each other so the name of the file needs to be unique to the schema.
> h5. TODO
> - [x] Run derby db and migration script creation in ./target directory
> - [x] derby db not shutting down cleanly?
> - [x] run MappingTool directly, don't bother too much with checking if it validates, just run it
> -- maybe run it in a {{finally}} block?
> - [x] generate schema for all of the registry databases
> - [x] separate logging configuration from test logging and separate airavata-server.properties files (one for derby and one for MariaDB)
> - -[ ] have a mode to generate the entire schema and another mode to generate a migration script-
> -- appears {{createDB}} works better than {{build}} since it generates FKs
> - [x] integrate running MariaDB via Docker and generating migration scripts for that
> - [x] update ide-integration to use MariaDB
> -- also integrate automatically running migration scripts at start up, if possible (scripts in /docker-entrypoint-initdb.d are only executed the first time the container starts
> - [x] update documentation
> - -[ ] refactor schema migration generation code to common so that it can be used in sharing catalog and credential store, etc.-
> - [x] undo the changes to JPAUtils.java to automatically run schema migration generation when schema fails to validate
> h5. Issues
> - Schema generation generates PRIMARY KEY for table VIEWs
> -- maybe use createQuery or createNativeQuery instead? (https://coderanch.com/t/591607/databases/Mapping-JPA-Entity-View)
> - Can create additive schema migrations but not ones that remove columns/tables (see [first comment below|https://issues.apache.org/jira/browse/AIRAVATA-3326?focusedCommentId=17105517&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-17105517])



--
This message was sent by Atlassian Jira
(v8.3.4#803005)