Support for multiple database schemas

Since version 5.3.4 IJC has supported using tables from multiple Oracle schemas. Schema in this context refers to the database schema, not the IJC schema. By default (and for MySQL and Derby) only tables in the default schema are visible to IJC and all tables that are created by IJC are created in the default schema. The default schema is the one that you use automatically when you connect to Oracle and will have the same name as your username. All tables and other artifacts in this default schema can be referred to using their plain names. If the database user that IJC is using has been granted access to tables in another schema then these can be accessed, but only using the schema qualified table name. e.g. if there are two Oracle users, 'joe' and 'harry' and you connect as joe then the table A_TABLE refers to a table in the default schema which will be JOE, but the table HARRY.A_TABLE refers to a different table in harry's schema.

The main use of accessing tables from multiple schemas is in a big corporate environment where different types of data are managed by different parties e.g. you might have chemistry data in one schema and biology data in another schema. Using multiple schemas adds extra complexity, and is not recommended for basic uses. The schemas will need to be created and managed externally to IJC by your DBA. One benefit is that it is relatively easy to control access to your important data and so prevent the risk of accidental modification. The area of the database that IJC needs to write to (e.g. to manage the user's lists, queries etc.) can be kept separate from your sensitive data to which only read-only access is granted.

What is needed to access tables from other schemas

To be able to access objects from other schema your DBA must have granted sufficient rights to the IJC user to those objects. At the minimum this will be SELECT privilege for read-only access, but might include additional privileges depending on your needs. You must set up these privileges outside IJC. You will need to grant access to all tables views and sequences that are needed. If you cannot see the tables in IJC then its almost certainly a privileges problem. See the notes below for more information about specific cases. There is also more information in the administrators docs

Telling IJC about additional schemas

By default IJC only sees tables in the default schema. To tell it about additional schemas:

1. Connect to the IJC schema in the projects window

2. Open the schema editor and unroll the Schema item

3. Click on the 'Database schemas' to open the following dialog

1. Specify the extra schema(s) that you want IJC to be able to use

2. Apply changes

3. Disconnect and reconnect to the IJC schema to pick up the changes

   Once reconnected tables and views from these additional schemas will now be visible in the tables and views tabs of the schema editor and can be promoted to entities as normal. All users of the IJC schema will see the contents of the additional database schema(s).

Using the schema editor with additional database schemas

Schema editor is mostly unchanged if you are using tables from multiple database schemas. Entities and Data Trees reside within a single namespaces independent of the location of the tables or views they use, thus allowing data from multiple database schemas to be brought together into a single location. Relationships can be created between entities that use tables from different database schemas, and a Data Tree can be assembled involving these Entities. From the perspective of the Data Tree, Entity, or any form or grid view there is no real difference in behaviour if the tables are not in the default database schema. However, these points should be noted:

• If only the default schema is used then all tables and views listed in the schema editor will be displayed as unqualified names. Only if additional schemas have been added are schema qualified names used.

• The expectation is that the main use of tables in additional schemas is where the tables already exist and are managed outside of IJC. Usually this will be in read-only mode, in which case only SELECT privileges are needed, but inserting, updating and deleting rows is also possible if additional privileges are granted.

• When creating relationships between tables in different schemas, if you choose to create a foreign key constraint then there are privileges issues that will need to be handled, depending on the type and the direction of the foreign key constraint (e.g. a join table needs to be created for a many-to-many relationship). In general it is best to either create the foreign key constraint outside IJC using a DBA account (the foreign key can be promoted to a relationship between the two entities) or not to create a foreign key constraint in the database when you create the relationship (uncheck the 'Create DB artifacts' checkbox).

• All types of tables (standard, jchembase, cartridge index on jchembase and cartridge index on standard table) can be used from additional schemas. To be able to use the cartridge indexes (e.g. perform structure searches) additional privileges are needed on the cartridge index. See the JChem cartridge administrators docs for more details and also there is a guide on setting up access to a cartridge based table from a different schema in the administrators tips .

• It is possible for IJC to create standard and jchembase tables (currently not cartridge based tables) in additional schemas but you must grant CREATE ANY TABLE, CREATE ANY VIEW, CREATE ANY SEQUENCE privileges, which is generally not going to be acceptable to your DBA. So whilst IJC supports these operations, the expectation is that generally this will not happen and these tables will be created outside IJC. To create a table in a schema other than the default schema you must qualify the table name with the schema name e.g. HARRY.STRUCTURES. Only schemas known to IJC will be accepted.

• Columns and indexes can be added and dropped in additional schemas as long as you have sufficient privileges. However this may not be the case and if so these operations will fail.

• Relationships between tables in different schemas are supported, but to add a foreign key constraint your database administrator must explicitly grant REFERENCES privilege to the foreign key owner on the table being referred to. Generally speaking this may not be desirable as it couples the two schemas, but can be done if needed. Also note that you can create the relationship in IJC without adding a foreign key constraint to avoid the need to create the constraint.

• JChem property tables from any database schema known to IJC can be used when creating a JChemBase table, but the expectation is that you will use a property table from the schema in which the table is being created.

• The IJC metadata tables (those with names beginning with IJC_*) are always created in the default database schema. It is not possible to use metadata tables from a different database schema.