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!

    1. 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?
    PASSWORD_EXPIRES date Counts the date when your password expires.
    PASSWORD_DAYS integer How many days until your password expires.

    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 important events. The logging can be enabled in the Schema editor as described here.

    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.

    1. 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.

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

    1. 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.
    
     1.
        Delete that row from the table. This will remove the security configuration.
    
     1.
        Re-connect with IJC. There will be no security in place.
    
     1.
        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.
        <a name="src-1802639-instantjchemmetadatatables-upgrade-5-12-0-a-a-name-src-1802639-instantjchemmetadatatables-ijc5-12"></a>

    IJC 5.12

    1. IJC_VIEWS was merged into IJC_CUSTOM_ITEMS.

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

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