We sync with 2 Relational Models: SQL Server with a source of Database and ODP.NET with a source of Mixed. Both DB's have just one schema called AQD, we have grouped related entities but those groupings are only used by the designer. When a new table is added to SQL Server, synced and reverse-engineered as an entity we then use 'Auto-map unmapped entities' to do the Oracle mappings. However the table is created against a schema called 'DEFAULT' rather than AQD like we want and would expect, is there anyway it can be made to use the existing schema?

Could you please attach a repro project file? It would make it faster for us to check it out, just 2 entities, one of them is not yet mapped to the Oracle schema.

Thanks,

Reproduced, looking into it.

Currently it picks the schema which matches the group name the entity is in. If the group name is empty, it picks the default schema. There's currently no other way. It doesn't pick the 'first one' it runs into, which could be a good strategy at first, but if that schema becomes the second one because a new schema is introduced, it goes wrong too, so there's little we can do at the moment for v5.3.

What would a good strategy be in this case, according to you? Some rules the user can define which determine what to do with the schema name? Say I have 3 schemas in the oracle catalog and no groups in the project. How would it be easy for the user to define which schema the new table will be added to?

Otis wrote:

What would a good strategy be in this case, according to you? Some rules the user can define which determine what to do with the schema name? Say I have 3 schemas in the oracle catalog and no groups in the project. How would it be easy for the user to define which schema the new table will be added to?

Firstly I would like it to treat single schema as a special case with everything going to that schema as that's easy.

For the more general case: If the entity was mapped to another DB (as in my case SQL Server) use that schema, if not I don't know. Perhaps don't even try and instead stick with status quo but add ability for the Catalog explorer to move tables and sequences etc between schemas.

I.e, if there are multiple schemas 'Auto-map unmapped entities' puts them to 'DEFAULT' or 'dbo' as current but maybe even an 'unassigned' or 'new' bucket and then have an error condition until the tables are moved to a schema.

TomDog wrote:

Otis wrote:

What would a good strategy be in this case, according to you? Some rules the user can define which determine what to do with the schema name? Say I have 3 schemas in the oracle catalog and no groups in the project. How would it be easy for the user to define which schema the new table will be added to?

Firstly I would like it to treat single schema as a special case with everything going to that schema as that's easy.

Agreed, if there's 1 schema already, it should add the table to that one, not yet another one.

For the more general case: If the entity was mapped to another DB (as in my case SQL Server) use that schema, if not I don't know. Perhaps don't even try and instead stick with status quo but add ability for the Catalog explorer to move tables and sequences etc between schemas.

Moving them is a problem, as that could mean you want them to move in the DB as well (e.g. the table already exists), which is problematic, as that's not doable with simple DDL SQL without dropping the original table.

We're thinking about marking a schema as the default target. So if you have e.g. 3 schemas, mark 1 as the default target, and all tables which are created from ungrouped entities are placed in that schema. That would give more control over it.

Move could be done only till the schema is exported as ddl sql. So the table is still marked as 'created by the designer'.

We'll try to add the 'pick the single schema if there's just 1' change to v5.3 as it feels like a bug. the other changes will move to v5.4

Thanks for sorting that, though given that the designer allows you to rename a schema isn't that as problematic as moving a table from schema to schema?

TomDog wrote:

Thanks for sorting that, though given that the designer allows you to rename a schema isn't that as problematic as moving a table from schema to schema?

renaming a schema is effectively the same as renaming a user, but moving a table from one schema to another requires it to be recreated, at least on oracle (there's a partitioning trick, which requires the partitioning license option on top of the enterprise license :X). On SQL Server there's a 'transfer' statement which can do this tho, but likely copies the data behind the scenes too.

As it was problematic for one DB we didn't proceed with this.

(edit) auto-selecting the existing schema to add new elements to is now implemented in v5.3.3 hotfix (released later today)

Otis wrote:

Moving a table from one schema to another requires it to be recreated, As it was problematic for one DB we didn't proceed with this.

How about moving a Sequence from one schema to another?

No statement for that, I'm afraid: it has to be recreated (see e.g.: https://stackoverflow.com/questions/45425912/how-to-move-a-sequence-to-a-new-schema), so we're not doing that.

Otis wrote:

We'll try to add the 'pick the single schema if there's just 1' change to v5.3 as it feels like a bug. the other changes will move to v5.4

I just tried this in 5.4.3 and it still created a table in DEFAULT rather then DBO_NORTHWIND

Sync Relational Model Data Task processing results
Results of running selected sync tasks
Results of performing task 'Sync with a database to sync 'Northwind: dbo' using meta-data obtained from 'SQL Server''
Wizard canceled by user, aborting
Results of performing task 'Sync with the model to sync 'Oracle (ODP.NET)' using meta-data obtained from 'Entity Model''
Results of synchronization of relational model elements with model elements
Results of automapping unmapped entities
Database: Oracle (ODP.NET)
Actions performed for entity 'Category'
Entity is now mapped onto new element '//localhost/xe.DEFAULT.CATEGORY'
Field 'CategoryId' is now a sequenced field using sequence 'DEFAULT.SEQ_CATEGORY'
Results of automapping unmapped entity fields
No actions taken
Results of Table Field Type Synchronization
No table fields required synchronization
Results of Meta-data adjustment
Results of syncing PK fields
Database: Oracle (ODP.NET)
Pk fields set in table '//localhost/xe.DEFAULT.CATEGORY'
Creating FK Constraints for inheritance relationships
No actions necessary
Creating FK Constraints and Unique Constraints for normal relationships
Database: Oracle (ODP.NET)
New FK created: FK_AE2403D4E6B95D226BD19A593B: DBO_NORTHWIND.PRODUCTS -> DEFAULT.CATEGORY over (DBO_NORTHWIND.PRODUCTS.CATEGORYID)-(DEFAULT.CATEGORY.CATEGORYID)
Creating Unique Constraints for model Unique Constraints
No actions necessary
Setting Identity flags on table fields mapped by sequenced entity fields
No actions necessary
Setting default values on table fields
No actions necessary

Steps: * Exclude a mapped table from a Oracle schema * Sync Oracle model only * Got output above See attached project (with typeconverter assembly)

Reproduced, looking into it.

(edit) still buggy, working on fix.

Fixed in latest hotfix for 5.4.3, now available. The fix we had in place did work, but only if the default schema name was equal the case of the schema picked, which doesn't work on oracle, as the default schema was 'Default' but after processing it becomes DEFAULT, which weren't seen as equal.