How to use VCS for objects in multiple schemas?

[Jeff Kandt]

I'm trying the VCS plugin for the first time, after using PL/SQL Developer for many years, but I feel like I must be missing something...

We have several schemas/owners which contain objects for our application, all of which need to get stored in the same source repository (in our case TFS).

Those object names & types may conflict between schemas -- in other words there might be a "COMMON.pck" package in the "OWNER1" schema, and another different package called "COMMON.pck" in the "OWNER2" schema.

There's a regular PL/SQL Developer Preference for "always include owner prefix" which causes the owner to be prepended to the object name within the script itself, so the script always contains something like "CREATE OR REPLACE PACKAGE owner.object..."

But how can I distinguish the FILENAMES? Is there a way to convince either PL/SQL Developer or the VCS plugin to either 1) name all source files with the owner's prefix ("OWNER1.COMMON.pck" vs "OWNER2.COMMON.pck") or 2) Add objects into source control under a separate directory for each schema ("/OWNER1/COMMON.pck" vs "/OWNER2/COMMON.pck").

I suppose I could require developers to open separate VCS projects for each schema they will be modifying objects in, where each project specifies the appropriate schema-specific directory within the source control system, but this seems like a huge inconvenience since we're often working in multiple schemas at once. And how would I ever enforce this? Can I require that a given VCS project will allow only objects from a specific schema to be checked into it?

 

[Marco Kalter]

There is currently no option to implicitly include the schema name in filenames, directories or projects that are under version control.

I can only recommend that you name the files as needed, and use PL/SQL Developer and the VCS Plug-In to work with these files instead of the object definitions stored in the database.

 

[William Robertson]

Traditionally a lot of sites use numbered schemas for dev and small test environments anyway, as an easy way to provide multiple schemas in one database instance without licensing 12c multitenant or setting up dozens of VMs etc, in which case saving the schema name as part of the source code wouldn't work anyway.

I've seen both of the approaches you mention (schema in filename and schema as subdirectory) and personally I think the subdirectory approach is cleaner, but it will depend on your automated deployment system.

I agree a couple of options around this in PL/SQL Developer would be nice to have, regardless of any VCS plugin. For example, 'File > Open > Program File' can currently only be set to a single location in Preferences > Files > Directories. It would be nice to be able to define subdirectories ABC and XYZ which it would pick whenever you were connected as a user matching 'ABC%' or 'XYZ%' respectively.

Regarding how to enforce it - if developers do it wrong they will break the build and have to fix it.

 

[Jeff Kandt]

I can only recommend that you name the files as needed

I should have mentioned that I skipped right to "Allow version
control over DB objects" for my initial testing, but it sounds like that's off the table.

I'll turn that option off for my next round of testing, thanks.