Ways of editing your Schema

The Schema Editor

The Schema Schema Editor is the expert tool for managing your IJC database. Most simple operations can be performed without needing to use the Schema Editor, but in some cases it may be easier to use the Schema Editor, and some advanced operations can only be performed using it.

The Schema Editor allows you to manage your current IJC Entities and Fields, as well as to add any existing database artifacts that are not yet known to IJC. In this way it lets you incorporate data from an existing database into IJC.

Schema Editor overview

The Schema Editor contains a list of Entities and their contents in the left hand panel. The contents of an Entity are its Fields, Indexes and Relationships. Select any item in the explorer in the left hand panel and you will see its properties in the main panel.

Filter artifacts

An item in IJC usually represents something in the database. Typically:

An Entity represents a database table
A Field represents a database column
A relationship represents a foreign key constraint

When an object is created in IJC the database artifact is created and IJC is made aware of this together with any additional data that is needed about the item. What happens if the database artifact has not been created by IJC? In this case IJC will not know about the artifact and will not possess the additional information it needs about it to be able to manage it. This may be the case if you have connected to a database that already has tables in it that were created by other means. The Schema Editor lets you see these artifacts and "promote" them into IJC so that it can manage them. Sometimes when you do this you need to provide the missing information to IJC so that it knows how to handle them.

To show or hide these "foreign" artifacts use the Filter toggle button in the Schema Editor toolbar.

Promoting artifacts

When you have an artifact that is not yet part of an IJC schema you may be able to promote it into IJC. Click on the "Promote" button in the editor. Alternatively you can click on the appropriate button in the toolbar for the type of item that you want to promote. You will see a wizard that takes you through the promotion process. The information your need to provide is typically a subset of the information you need to provide when you create this sort of item in IJC (the information that cannot be read from the database).

Note: Not all "foreign" artifacts can yet be promoted. This is somewhat dependent on the underlying database type.

Manage Entities

Entities can be added and removed from IJC using the Schema Editor.

Create a new Entity by clicking on the 'New ... Entity' icons ( )
Promote an existing table to an Entity by clicking on the 'Existing ... Entity' icons ( )
Delete an Entity by selecting it in the explorer tree, right clicking on it and selecting 'Delete...'. You will be warned about any other artifacts that will also need to be deleted and asked to confirm that you want the database table to be deleted from the database.

See the Editing Entities page for more details about creating Entities.

Manage Fields

Fields can be added and removed from IJC using the Schema Editor.

Create a new Field by clicking on the 'New ... Field' icons ( )
Promote an existing table to an Entity by clicking on the 'Existing ... Entity' icons ( )
Delete a Field by selecting it in the explorer tree, right clicking on it and selecting 'Delete...'. You will be warned about any other artifacts that will also need to be deleted and asked to confirm that you want the database column to be deleted from the database.

See the Editing Fields page for more details about creating Field.

Manage Indexes

Indexes provide the database with a much faster way to find values in a database column. Situations where adding an index will be benefitial (or not) are listed in the Performance tips tips and tricks page.

Please consult your database administrator if you are not clear about indexes.

To add an index in the Schema Editor:

Select an Entity or Field in the Schema Editor and click on the Add index icon ( ) in the Schema Editor toolbar. Alternatively right click on the field you want to add the index to and choose 'New Index'. The New Index dialog will open.
Specify the column(s) that will be indexed. If you started the dialog with a Field selected then IJC will assume you want to add an index to the column(s) used by that Field. If you had an Entity selected you need to specify the columns yourself. You add a column by selecting it from the 'Column' combo box and clicking on the 'Add' button. It will be added to the list displayed in the table.
The currently specified column(s) can be changed by using the 'Up'. 'Down' and 'Remove' buttons (note: if the index contains multiple columns the order is important). Usually you will only want a single column in the index).
The order the column values are stored in the database can be changed from ascending to descending using the checkbox for the column in the table. Usually the default will be OK.
Specify a name for the index if the default provided is not satisfactory.
If you want the index to restrict the values for those columns to be unique (duplicate values are not permitted) check the 'Unique keys' checkbox.
Click on the 'Finish' button and the index will be added. Adding the index may take some time for large tables.

To remove an index in the Schema Editor:

Find the index under the Indexes node of an Entity.
Right click on the index and choose 'Drop index'.

Note: not all indexes can be removed as some are requried by the tables or foreign keys in the database.

Manage Relationships

Relationships define associations between entities. Add a relationship in the Schema Editor by first adding any Fields that are required to define the Relationship and then clicking on the 'New relationship' in ( ) in the Schema Editor toolbar. See the Editing Relationships page for more details about creating Relationships.

Manage JChem Property Tables

A JChem database needs a Property Table which is needed to store various bits of information about the JChem structure tables. By default a single property table named JCHEMPROPERTIES is created when the IJC schema was originally created, and this is sufficient for most purposes. However it is possible to use a different table name, or to to use multiple property tables. Note: this is not recommended unless you have a specific reason for needing to do so.

To manage JChem property tables do the following:

Click on the 'Manage JChem property tables' icon ( ) in the Schema Editor toolbar. The editor will open.
Add or delete a propety table using the appropriate buttons. Note: you can only delete a property table if it does not currently manage any JChem tables. Delete the JChem tables before deleting the property table.

Important: Do not delete JChem tables directly from the database or the information in the property tables will become inconsistent. Always delete them using IJC which will ensure that the property table is updated appropriately. Property tables can also be managed using other ChemAxon tools such as JChem Manager. However, avoid doing this for databases used by IJC or the information may become inconsistent.