search

qbicsoftware / variantstore-service

The Variantstore is a Java/Groovy-based service application implemented using the Micronaut framework and enables storage and access to information on genomic variants and metadata from a connected database via a RESTful API.
MIT License
4 stars 1 forks source link

[FEATURE] Postgresql DB scheme convertion #1

Closed Zethson closed 4 years ago

Zethson commented 5 years ago

I have only converted, but not yet tested it. There's also a number of improvements that may be of interest such as the usage of serial for AUTO_INCREMENT ids . See: https://www.postgresql.org/docs/8.1/datatype.html#DATATYPE-SERIAL

I will update this as I know more.

Zethson commented 5 years ago

I should investigate whether or not CREATE SCHEME is also synonymous to CREATE DATABASE like in mysql for postgres.

If not, it may be a good idea to change that in the postgres version.

Zethson commented 4 years ago

All right - so far so bad. Just running from issue to issue.

By the way, postgresql complains about 'end' which is for example used in oncostore.Gene , since it is a reserved keyword. But I figured that it is also a keyword in mysql. Does this not cause any issues?

christopher-mohr commented 4 years ago

All right - so far so bad. Just running from issue to issue.

By the way, postgresql complains about 'end' which is for example used in oncostore.Gene , since it is a reserved keyword. But I figured that it is also a keyword in mysql. Does this not cause any issues?

I guess the reason is that we're using MariaDB for which it doesn't seem to be a reserved keyword. We should probably rename it to make it mysql compatible.

Zethson commented 4 years ago

I really think that we have to make the scheme mysql compatible first. Any mysql converters can't do their job properly, if they get wrong syntax as input.

I am still afraid that we'll have to convert the DB scheme manually to postgresql, but for now I would start converting the MariaDB scheme to mysql first.

Zethson commented 4 years ago

I've now manually converted the DB scheme as discussed. A few tests of mine look promising, but it might require some more manual testing.

Zethson commented 4 years ago

So I tested it again and the script seems to run through well.

Can be tested by login in with the postgres user and then in the postgres bash shell running:

psql -f oncostore-model-postgresql-manual.sql

Some warnings about tables not existing and skipped commands will appear. This is to be expected, since those are the DROP TABLE IF EXISTS checks. They can be turned off if preferred: https://dba.stackexchange.com/questions/228331/disable-warning-from-drop-table-if-exists

There are some pretty long INDEX names in the script, since the MariaDB sql script had some inconsistencies (at least to me as a newbie?) and I therefore tried to streamline them first. Some of the long INDEX names are therefore truncated. We can discuss how the INDEX naming should be done and then we can just shorten the names according to the final INDEX naming scheme.

It's difficult to determine whether or not the postgres version behaves the same as the MariaDB version, but I tried to convert everything of the MariaDB sql script to postgres sql without any exceptions. I assume, that it still requires a manual review from you, @christopher-mohr .

christopher-mohr commented 4 years ago

Thanks for the update!

There are some pretty long INDEX names in the script, since the MariaDB sql script had some inconsistencies (at least to me as a newbie?) and I therefore tried to streamline them first.

Could you give me an example?

Zethson commented 4 years ago

The MariaDB sql script contained some index names like:

fk_idx

even multiple times, which was not compatible with postgres in the first place. Hence, I replaced them.

Some of the indices in MariaDB were already quite long like

fk_AnnotationSoftware_has_Consequence_AnnotationSoftware1

They are automatically truncated in postgres.