Instant JChem Meta Data Tables

IJC stores some information it needs to use in special tables in the database. Each IJC database will contain these tables, and they are created when IJC first creates or connects to the database. When connecting to a remote database this happens when you use the 'New schema' wizard in IJC. The tables are also created automatically when you create a new local database. These tables all start with the name IJC_.

These tables should not normally be changed outside of IJC as this risks corrupting the IJC configuration. However in some rare cases you may need to make changes to these tables manually to correct errors. Also, in principle it would also be possible to modify the IJC configuration if you modify these tables in the correct way. However:

  1. This is definitely not a recommended approach. Caveat emptor!

  2. We reserve the right to modify the format of data in these tables in future versions, so what works today may not work tomorrow.
    These IJC_* tables only store IJC configuration information. Data such as structures or data from those structures is contained in other tables. This means that the IJC_* tables can be safely deleted, and no real data will be lost. But all of your IJC configuration data, including all your users forms, queries and lists will be lost, so this should only be done in extreme cases. If the IJC_* tables are deleted the 'New schema' wizard can be used to re-create them.

The content of the meta data tables is fairly self explanatory. Here is a brief description of the tables and their functions.

IJC_SCHEMA

This table is the main starting point for the IJC meta data. It contains information about the IJC schema, and the entities, fields, relationships and data trees that it contains. All of these are shared between all users (e.g. they belong to IJC, not any individual user. All items are referred to by their IDs, which are 32 character strings. With the exception of schema, all items have a parent they belong to:

  • Entity belongs to schema

  • Field belongs to entity

  • Relationship belongs to schema

  • Data tree belongs to schema
    The table structure is as follows:

Column

Type

Description

SCHEMA_ID

text

The ID of the schema the item belongs to.

ITEM_ID

text

The ID of the item.

ITEM_INDEX

integer

Index of the item within its parent when there are multiple items

GENERIC_TYPE

text

The generic type of the item. e.g FIELD for a field.

IMPL_TYPE

text

The specific implementation type of the item. Potentially there could be multiple implementations of any particular item type.

PARENT_ID

text

The ID of the item's parent. Defined in all cases. The parent of the schema is the schema itself.

DESCRIPTION

text

An optional description. Not currently used.

ITEM_VALUE

text

The specific configuration or the item, specified as an XML document.

IJC_USER

This table defines the users in IJC. A user is automatically added to this table when they first use the database. Security information about the user is not stored in this table.

The table structure is as follows:

Column

Type

Description

SCHEMA_ID

text

The ID of the schema the item belongs to.

USERNAME

text

The username of the user

LOGIN_TIME

timestamp

The time the user logged in. Will be null if user is not currently connected.

HEARTBEAT

timestamp

The time the user last performed an operation. e.g. when they were last active. Will be null if user is not currently connected.

LOGIN_COUNT

integer

The number of times this user has logged in. This column was added in IJC 5.8.

IJC_VIEWS

Since IJC 5.12, this is only a dummy table. The contents has been moved to IJC_CUSTOM_ITEMS

IJC_CUSTOM_ITEMS

This table defines the views, scripts, queries and lists that belong to an individual user.

The table structure is as follows:

Column

Type

Description

ID

text

The IJC ID of the user item.

SCHEMA_ID

text

The schema the item belongs to.

ITEM_ID

text

The IJC ID of the schema item (entity, field, datatree etc.) related to this user item.

USERNAME

text

The name of the user who owns the view.

NAME

text

The display name of the query or list.

DESCRIPTION

text

A description of the query or list (not currently used).

VIEW_INDEX

integer

Index of the query or list within its parent (not currently used)

GENERIC_TYPE

text

The generic type of the item. e.g QUERY for a query.

TYPE

text

The implementation type of the view.

CREATED_BY

text

The user who created the item.

LAST_CHANGED_BY

text

The user who last updated the item. Currently this will always be the person who created it.

CREATED_ON

timestamp

When the item was created.

LAST_CHANGED_ON

timestamp

When the item was last updated.

DATA

text

The definition of the list of query. This definition is subject to change.

SHARING

integer

Obsolete from 5.12. Was defining the shared status of the item. Now, this information is held in more detailed way in IJC_CUSTOM_ITEMS_SHARING

IJC_CUSTOM_ITEMS_SHARING
This table stores information about which user roles can access a given view, list etc.

IJC_ITEM_INFO

This table provides a way to store arbitrary extra information for any IJC item. Currently this is not used. If you are a developer who wants to use this feature then please contact us.

IJC_ITEM_USER

Used for storing information about a particular user. The table structure is as follows:

Column

Type

Description

SCHEMA_ID

text

The ID of the schema the item belongs to.

ITEM_ID

text

The ID of the item that the information refers to.

USERNAME

text

The name of the user who owns the view.

TYPE

text

The type of the item.

CREATION_TIME

timestamp

When the item was created.

INFO

text

Configuration for the item.

IJC_SECURITY_INFO

Used for storing the security configuration for the IJC database. If custom security is defined for an IJC schema its configuration will be in this table. Do not modify this configuration. The only operation that should be considered is deleting the security configuration from the table by deleting the appropriate row. This might be necessary if your configuration has become corrupted for some reason. Deleting the custom security configuration will revert the IJC schema to use default security, so you can then re-configure the security again.

Column

Type

Description

SCHEMA_ID

text

The ID of the schema the security configuration applies to.

ITEM_ID

text

The item this refers to. Normally the schema itself.

INFO_TYPE

text

The type of data. Used to identify the particular row needed for any particular implementation.

EXTRA_1

text

For implementation specific purposes.

EXTRA_2

text

For implementation specific purposes.

EXTRA_3

text

For implementation specific purposes.

EXTRA_4

text

For implementation specific purposes.

INFO_VALUE

text

The actual configuration involved. Contents depend on the implementation.

IJC_SECURITY_USERS

If you are using IJC database based security then this table defines your users. If not, then it will not be present. The table structure is as follows:

Column

Type

Description

USERNAME

text

The username of the user.

PASSWORD

text

The password of the user. This will typically be encrypted and salted according to the settings in the security configuration so you should not edit values directly, only through the IJC user interface.

ENABLED

bit or char

Is this user enabled?

IJC_SECURITY_AUTHORITIES

If you are using IJC database based security then this table defines the roles that have been assigned to each user. If not, then it will not be present. The table structure is as follows:

Column

Type

Description

USERNAME

text

The username of the user

AUTHORITY

text

The name of a role assigned to a user.

IJC_AUTHORITIES

This table was added in IJC 5.4. It defines the roles that are used by IJC. By default it is populated with the 6 standard roles, ROLE_USER, ROLE_EDIT_DATA, ROLE_EDIT_SCHEMA and ROLE_ADMIN. In 15.10.5.0 release, two additional roles were added, ROLE_EXPORT_DATA and ROLE_EDIT_SCRIPT. These 6 roles must NOT be deleted from the table. Additional roles can be added if needed (see section on IJC_SCHEMA_AUTHORITIES table below).

Column

Type

Description

AUTHORITY

text

The name of the role

DESCRIPTION

text

A human friendly description of the role.

RANK

integer

Used to determine the order the roles appear in IJC. You can change these to change the order.

IJC_SCHEMA_AUTHORITIES

This table was added in IJC 5.4. It allows role based entitlements to be defined. Currently this is restricted to allowing items from the IJC_SCHEMA table to be filtered out according to which roles the user has been assigned, both other functionality will added in future. A guide to setting this up is described in the Filtering items using roles tips and trick.

Column

Type

Description

SCHEMA_ID

text

The ID of the schema.

ITEM_ID

text

The item the row corresponds to.

PROPERTY

text

The property that is involved.

AUTHORITY

text

The role that is involved.

IJC_INVOCATION_LOG

This table was added in IJC 5.4. It is used to record import events.

Column

Type

Description

INVOCATION_ID

text

Unique ID of the invocation.

START_TIME

time

The time the event started.

DURATION

number

The time taken in millis.

DESCRIPTION

text

A description of the event.

USERNAME

text

The user who caused the event.

SCHEMA_ID

text

The ID of the schema.

IJC_CHANGE_LOG

Records changes made to the database. Each change is tied to a row in the IJC_INVOCATION_LOG table. Currently only changes to the database model (e.g. creating a table) are recorded.

Column

Type

Description

CHANGE_ID

text

A Unique ID for the change.

DURATION

number

How long making the change took.

CHANGE_TYPE

text

The type of change.

ITEM_TYPE

text

The type of item affected.

PARENT

text

The parent of the item, if any.

AFFECTED_ROWS

number

The number of rows affected.

DETAILS

text

Details of the change.

INVOCATION_ID

text

The invocation ID for the change.

SEQ_INDEX

number

The order of the change in the sequence of changes for a particular INVOCATION_ID.

Details of IJC table upgrades

Please be aware of these changes before upgrading your database. We try to ensure that the upgrades work correctly, but errors could potentially occur. You are strongly recommended to back up your database before upgrading.

IJC 5.4

In IJC 5.4 there were several changes to the IJC tables.

  1. IJC_INVOCATION_LOG and IJC_CHANGE_LOG tables added to allows event logging.

  2. IJC_AUTHORITIES and IJC_SCHEMA_AUTHORITIES added to allow role based filtering of items in the IJC_SCHEMA table. Foreign key constraint added from IJC_SECURITY_AUTHORITIES table to IJC_AUTHORITIES. See the Filtering items using roles tip for how to use these tables.

  3. ID column removed from IJC_VIEWS table as it was not used. Corresponding trigger and sequence removed on Oracle.

  4. Contents of IJC_SECURITY_INFO table migrated to use new classes. This was necessary because the security libraries were upgrade from Acegi to Spring Security, which meant that different class names were needed in the security configurations. This is done by substituting the old values with the new ones and then writing the updated configuration back to the IJC_SECURITY_INFO table. If you have multiple IJC schemas present with security policies then all will be updated together as part of the IJC meta data table upgrades. In most cases this process should be automatic, but if you have a non-standard security configuration it may fail. If this happens you must fix the problems manually. During the upgrade process the schema IDs and the security definition (XML) is written to an output window for you to use as a reference. Copy and paste this information so that you have it for reference later. To manually update the security if the automatics process has failed you need to do this:

    1. Identify the appropriate row in the IJC_SECURITY_INFO using the schema ID.

    2. Delete that row from the table. This will remove the security configuration.

    3. Re-connect with IJC. There will be no security in place.

    4. Define your security policy as usual. You can use the appropriate template as the basis for this, and use information from the old policy that was output for you when you tried to do the update originally.

IJC 5.12

  1. IJC_VIEWS was merged into IJC_CUSTOM_ITEMS.

  2. New table IJC_CUSTOM_ITEMS_SHARING, holding role-based sharing data was created and populated with data based on IJC_CUSTOM_ITEMS.SHARING column.

  3. PRIMARY KEY definition of IJC_CUSTOM_ITEMS was changed to SCHEMA_ID, ID.