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:

This is definitely not a recommended approach. Caveat emptor!
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
IJC_USER
IJC_VIEWS
IJC_CUSTOM_ITEMS
IJC_CUSTOM_ITEMS_SHARING
IJC_ITEM_INFO
IJC_ITEM_USER
IJC_SECURITY_INFO
IJC_SECURITY_USERS
IJC_SECURITY_AUTHORITIES
IJC_AUTHORITIES
IJC_SCHEMA_AUTHORITIES
IJC_INVOCATION_LOG
IJC_CHANGE_LOG

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.

IJC_INVOCATION_LOG and IJC_CHANGE_LOG tables added to allows event logging.
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.
ID column removed from IJC_VIEWS table as it was not used. Corresponding trigger and sequence removed on Oracle.
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

IJC_VIEWS was merged into IJC_CUSTOM_ITEMS.
New table IJC_CUSTOM_ITEMS_SHARING, holding role-based sharing data was created and populated with data based on IJC_CUSTOM_ITEMS.SHARING column.
PRIMARY KEY definition of IJC_CUSTOM_ITEMS was changed to SCHEMA_ID, ID.

Instant JChem Meta Data Tables