New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Can Pyrseas porting to other DB ? #137

Closed
yikaus opened this Issue Jun 2, 2016 · 4 comments

Comments

Projects
None yet
2 participants
@yikaus

yikaus commented Jun 2, 2016

Really like the feature of dbtoyaml and yamltodb , is it possible for Pyrseas to porting to other DB like Oracle ? by refactoring the db connection ?

Thanks

@jmafc

This comment has been minimized.

Show comment
Hide comment
@jmafc

jmafc Jun 2, 2016

Member

Four years ago, in the post More Database Tools? I toyed with the idea of creating an oratoyaml tool, albeit the intent was to use it more to convert Oracle (or other databases) to Postgres (and thus there would not be a yamltoora). Also, one of our users in Spain did discuss the possibility of porting Pyrseas to work on SQL Server and I did give him some hints, but I never followed up on what happened.

Unfortunately, porting the db connection to talk to Oracle would not be sufficient. Pyrseas queries the internal Postgres system catalogs (rather than the information_schema catalogs). So you would have to replace those queries at the very least. So you'd have to rewrite the query starting at https://github.com/perseas/Pyrseas/blob/master/pyrseas/dbobject/table.py#L662 by referring to Oracle's ALL_TABLES and so forth. Then you'd also have to take care of differences between Oracle and Postgres. For example, Postgres allows tables to inherit from other tables, Oracle has partitions, Postgres has a multitude of object types (e..g., FDW, textsearch, domains/enum types/composites, etc.), Oracle has synonyms.

Member

jmafc commented Jun 2, 2016

Four years ago, in the post More Database Tools? I toyed with the idea of creating an oratoyaml tool, albeit the intent was to use it more to convert Oracle (or other databases) to Postgres (and thus there would not be a yamltoora). Also, one of our users in Spain did discuss the possibility of porting Pyrseas to work on SQL Server and I did give him some hints, but I never followed up on what happened.

Unfortunately, porting the db connection to talk to Oracle would not be sufficient. Pyrseas queries the internal Postgres system catalogs (rather than the information_schema catalogs). So you would have to replace those queries at the very least. So you'd have to rewrite the query starting at https://github.com/perseas/Pyrseas/blob/master/pyrseas/dbobject/table.py#L662 by referring to Oracle's ALL_TABLES and so forth. Then you'd also have to take care of differences between Oracle and Postgres. For example, Postgres allows tables to inherit from other tables, Oracle has partitions, Postgres has a multitude of object types (e..g., FDW, textsearch, domains/enum types/composites, etc.), Oracle has synonyms.

@jmafc

This comment has been minimized.

Show comment
Hide comment
@jmafc

jmafc Jun 2, 2016

Member

I forgot another porting step: if you want a yamltodb (or yamltoora) that talks to Oracle, it will also be necessary to adjust the SQL syntax generated by yamltodb to make it Oracle-compatible.

Member

jmafc commented Jun 2, 2016

I forgot another porting step: if you want a yamltodb (or yamltoora) that talks to Oracle, it will also be necessary to adjust the SQL syntax generated by yamltodb to make it Oracle-compatible.

@yikaus

This comment has been minimized.

Show comment
Hide comment
@yikaus

yikaus Jun 2, 2016

Thanks Joe , It seems very costly work around sql refactor . :-)

Anyway , it was a great work for doing a SQL diff style DB migration tool , really like it.

Cheers

yikaus commented Jun 2, 2016

Thanks Joe , It seems very costly work around sql refactor . :-)

Anyway , it was a great work for doing a SQL diff style DB migration tool , really like it.

Cheers

@jmafc

This comment has been minimized.

Show comment
Hide comment
@jmafc

jmafc Jun 2, 2016

Member

You're welcome.

Member

jmafc commented Jun 2, 2016

You're welcome.

@jmafc jmafc closed this Jun 2, 2016

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment