Oracle Reverse-Engineering for public synonyms

paulkatich commented 5 years ago

Expected Behavior

I ran the Reverse engineering tool for oracle database v12.1.0.2.0. The tool created a script that has editionable synonym. I created two schemas the onboarding and the dependent schema as well. But when I run the onboarding command for a new schema I created. I get an error.

Actual Behavior

Cycle #1: [TEST.change0] => [CREATE.n/a] (DISCOVERED) => [TEST.change0] (DISCOVERED) (CYCLE FORMED) at com.gs.obevo.impl.graph.GraphUtil.validateNoCycles(GraphUtil.java:137) at com.gs.obevo.impl.graph.GraphEnricherImpl.createDependencyGraph(GraphEnricherImpl.java:161) at com.gs.obevo.impl.MainDeployer.executeInternal(MainDeployer.kt:211) at com.gs.obevo.impl.MainDeployer.execute(MainDeployer.kt:71) at com.gs.obevo.impl.context.AbstractDeployerAppContext.deploy(AbstractDeployerAppContext.java:139) at com.gs.obevo.db.cmdline.DbDeployerMain.start(DbDeployerMain.java:79) at com.gs.obevo.cmdline.AbstractMain.start(AbstractMain.java:129) at com.gs.obevo.dist.Main$5.value(Main.java:185) at com.gs.obevo.dist.Main$5.value(Main.java:182) at com.gs.obevo.dist.Main.execute(Main.java:119) at com.gs.obevo.dist.Main.execute(Main.java:87) at com.gs.obevo.dist.Main.main(Main.java:69)

Obevo Version where this issue was observed

Steps to reproduce the behavior

Create two schemas. Schema 1 needs to access table in Schema 2. Create a table in schema 2. Create an editionable synonym in schema 1 and run reverse engineering tool and then run the on boarding tool in schema 1.

paulkatich commented 5 years ago

DDL for schema 2:

CREATE TABLE TEST ( NAME VARCHAR2(200 BYTE) );

paulkatich commented 5 years ago

Never mind this is a problem with file name. It is create.sql this is causing issue. This table is created by oracle ERP tool "CREATE$JAVA$LOB$TABLE". The obeovo tool creates a file named CREATE.sql this is causing issues. The reserved filename causing issues.

shantstepanian commented 5 years ago

Hi, I'm a bit curious about this (always looking for opportunities to improve the tool and reduce confusion)

Looks like this is a table that Oracle creates itself (via the loadjava utility, according to a Google search). I'd think that this table should not be managed in your source code, as it is managed by the DB itself. Does that sound right? I'm open to excluding such objects by default from reverse-engineering if so

paulkatich commented 5 years ago

Our Application is legacy. The only way at least when started developing was to use synonyms to share data between schemas. It is natural you would want data from a ERP schema in your custom schema if you are dealing with financials. We are only creating a synonym to access it. It is created by a sql file. So in this case I would say it is better to not exclude it .

paulkatich commented 5 years ago

@shantstepanian Quick question does Obevo supports public synonyms export as part of reverse engineering ?

shantstepanian commented 5 years ago

Regarding my previous question on whether to include the object - I meant specifically about whether we should manage tables like this one in your code - CREATE$JAVA$LOB$TABLE. I agree on handling synonyms

Regarding your question on public synonyms: I haven't used synonyms much in practice, but I'll try to answer:

we do support regular synonyms, e.g. CREATE OR REPLACE SYNONYM SYN1 FOR TABLE_A
From the Oracle docs, it says that a public synonym does not belong to a schema. In that case, we don't support reverse engineering that yet, as our reverse-engineering (and even object deployments) tends to do it within the context of a schema. That said - we do support such database-wide objects as Oracle directories, so it could be possible to add.

Regardless, let me know if one of the above mentioned is your use case, and I will play around with it in the next couple weeks

shantstepanian commented 5 years ago

FYI - I have an improvement on reverse-engineering nested tables; see the info in your previous ticket #219

Regarding public synonyms:

I won't be able to get to this in the next couple weeks, as I have a few other things to do first
The implementation would look similar to what we've done for Oracle directories, per my previous message. I may change how that is implemented in the future, but for now, I'd have to go with this
In the meantime, you'd have to manage public synonyms on our own. You could potentially leverage the /migration script functionality for this
If you want to extract all the public synonyms in your DB, you can try out the following SQL

SELECT obj.OBJECT_NAME
    , dbms_metadata.get_ddl(REPLACE(obj.OBJECT_TYPE,' ','_'), obj.OBJECT_NAME, obj.owner) || ';' AS object_ddl
FROM DBA_OBJECTS obj
WHERE OBJECT_TYPE = 'SYNONYM' AND OWNER = 'PUBLIC'
AND ORACLE_MAINTAINED = 'N'

fyi - I will rename this ticket to "public synonyms", as we do already implement regular synonyms

goldmansachs / obevo