6.3 Datamodel

Datamodel definitions and definition examples are shown below. For details of Datamodel, see “Designing a data model” and “Designing a workflow”.

(Table) Datamodel definitions

Label Default Value Required*1 Description
apiVersion: Yes API Version
Specify 1.0.
kind: Yes Specify "datamodel".
type: No Specify "resource".
It indicates a new definition to be added to the operational function of Ops I.
name: Yes Internal name of the Datamodel definition.
"name" should follow the conventions shown below:
  • Specify the directory name of this YAML file.
  • The following characters can be entered:
    • Single-byte alphanumeric characters:
      a–z A–Z 0–9
    • Single-byte special characters:
      _ . ( ) -
  • The first character must be a single-byte alphanumeric character or an underscore (_).
  • For other precautions, see "Notes on creating YAML files" 2–4.
label: Yes Display name of the operational function to be written in the YAML file
includes: No List the related YAML files for use in this YAML file.
TipsTips
  • Before this YAML file is registered, the YAML file to be written to must be registered in Ops I.
  • By specifying the "includes" label, you can import information defined in another YAML file. For example, this can be used to import the YAML file for "Attachment", which defines the file naming conventions if documents in table columns are specified.
- kind: Yes Type of YAML file to be loaded
  name: Yes Internal name of YAML file to be loaded
description: No Detailed description of this YAML file
tables: Yes Configure custom table details.
For details about custom tables, see "Types of datamodels".
- name: Yes Internal name of the custom table*2
The characters that can be input are as follows.
  • Single-byte alphanumeric characters:
    a-z 0-9
  • Single-byte special character:
    _
Only a single-byte character from a-z can be specified as the first character. The number of characters allowed is up to 45.
  label: Yes Display name of the custom table
  inherits: No Inheritance settings
table: No Custom table name inherited by the custom table.
Must be included in "includes".
  description: No Detailed description of the custom table
  documents: No Documentation related to the custom table
- document: No Specify documentation related to the custom table.
  columns: Yes Settings for the columns
- name: Yes Internal name of the column
The characters that can be input are as follows.
  • Single-byte alphanumeric characters:
    a-z 0-9
  • Single-byte special character:
    _
Only a single-byte character from a-z can be specified as the first character. The number of characters allowed is up to 63.
TipsTips
You cannot specify a column name provided by default. For details, see "(Table) Columns provided by default".
  label: Yes Column display name
  description: No Detailed description of the column
  type: Yes Specify the column type.
The type of field in the Form component is determined by the specified type. For available types, see (Table) Column types"; for the types of fields in the Form component, see "(Table) Correspondence table: field types in the Form component and column types in custom tables in the Datamodel YAML file".
  not-null: false No If this value is "true", a non-null restriction is added to this column.
  primary: false No If this value is "true", this column is treated as the primary key.
  foreign: false No If this value is "true", this column is treated as the foreign key.
  ref: No This specifies the custom table and the column that will be referenced by the external key if "foreign" is "true". Reference restrictions are set to the column of the specified custom table. This value must be specified in dot notation format as follows.
Referenced custom table name.column name
Example) mytable.mycolumn
The custom table used for reference must be included in the document specified with "includes".
TipsTips
Specify a column with "primary: true" as the ref column. If a column that is not "primary: true" is specified, it will not be registered as a document.
  enum: No Defines selections when the type is "enum"
- name: Yes Internal name of the selection
The characters that can be input are as follows.
  • Single-byte alphanumeric characters:
    a-z A-Z 0-9
  • Single-byte special character:
    _
Only a single-byte character from a-z A-Z can be specified as the first character. The maximum number of characters allowed is 63. Must be unique within a single enum.
  label: Yes Selection display name
  default: No Default value of this column
  index: false No If this value is "true", an index will be generated for this column.
NotesNotes
Regardless of the column type, if the size of the data to be registered is large, index creation might fail, resulting in a failure to register the data.
Ensure that data registered in columns where "true" is specified does not exceed 2,400 bytes.
  document: No Documents associated with the custom table
Specify when "document" is selected as the column type.
There is a need to specify a collection-type document.
kind: Yes Sets the "kind" of document.
name: Yes Sets the "name" of the document.
  generate: No Automatically populate column values. The following can be set.
  • uuid
If "uuid" is specified, a UUID is automatically generated.
  ref-sys: No Refer to columns of the table (user, group, role) prepared in Ops I.
Do not specify a column with "foreign: true" as the column specified for ref-sys. If a "foreign: true" column is specified, it will not be registered as a document.
Only specify the record itself or a specific column, such as referred system table name or referred system table name.column name.
views: No Define custom view details.
For details about the custom view, see "Types of datamodels"; for examples of custom view definitions, see "Custom view definition example". For details about UI versions, see "UI version".
- name: Yes Internal name of the custom view*2
The following characters can be entered:
  • Single-byte alphanumeric characters:
    a-z 0-9
  • Single-byte special characters:
    _
Only single-byte alphanumeric characters (a-z) can be specified as the first character.
  label: Yes Custom view display name
  description: No Detail description of the custom view
  columns: Yes Column settings
- source: Yes Source of the column to be displayed as a custom view
The source can be specified as a custom table defined using "from" or "join.table". Specify this in the format "DatamodelName.ColumnName".
  name: Yes Internal name of the column
The following characters can be entered:
  • Single-byte alphanumeric characters:
    a-z 0-9
  • Single-byte special characters:
    _
Only single-byte alphanumeric characters (a-z) can be specified as the first character.
TipsTips
Column names provided by default cannot be specified. For details, see"(Table) Columns provided by default".
  label: Yes Column display name
  description: No Detail description of the column
  from: Yes Internal name of the base custom table
  join: No Set when joining multiple custom tables to the base custom table.
- type: Yes Specify the join method:
  • inner: Inner join
  • left: Left outer join
  • right: Right outer join
  table: Yes Internal name of the custom table to be joined
You can specify custom tables and the default user, group, and role tables. For details about the default tables, see "(Table) user table", "(Table) group table", and "(Table) role table".
  conditions: Yes Specify join conditions.
<conditionNode> Yes Join condition.
Specify "and" or "or".
- operator: Yes Operators used in join conditions.
For details about available operators, see "(Table) Available operators".
  operands: Yes Specify values passed to operators in the format "DatamodelName.ColumnName". For details about the notation, see "(Table) Available operators".
- <conditionNode> No Specify this when you want to define hierarchical conditions during a join. Up to three levels can be specified for the entire join element.
  where: No Specify this when setting display conditions for the results after the join.
  conditions: Yes Specify join conditions.
<conditionNode> Yes Join condition.
Specify "and" or "or".
- operator: Yes Operators used in join conditions.
For details about available operators, see "(Table) Available operators".
  operands: Yes Specify values passed to operators in the format "DatamodelName.ColumnName". For details about the notation, see "(Table) Available operators".
- <conditionNode> Yes Specify this when you want to define hierarchical conditions during a join. Up to three levels can be specified for the entire where element.
schemas: No Defines schema
Not supported in UI version 1.0. For details of UI versions, see "UI version".
- name: Yes schema name
  file: Yes JSON Schema file path
The starting point is the directory in which the Datamodel YAML file is saved.
The field types of the Form component are determined by the definition of the specified JSON Schema file. For details, see "(Table) Correspondence table: field types in the Form component and definition of the JSON Schema file in the Datamodel YAML file".
*1: If a label that is not nested has its "Required" field set to "No", the definition is not required. For nested labels, the "Required" setting of a lower-level label applies only when the "Required" setting of its upper-level label is set to "Yes".
*2: The internal name of the table specified in the Datamodel definition must be unique throughout the system.

The column types that can be specified are as follows.

(Table) Column types

Column type
integer*1 numeric*6 varchar*2 text timestamp*5 date*5 time boolean
enum inet*3 document*4 decimal*6 uuid - - -
*1: Basic data type that includes numbers from -2,147,483,648 to +2,147,483,647. Numbers cannot contain decimals or exponents.
*2: The maximum value of the default string is 40 characters. You can change the maximum length of a string by specifying "varchar(number of characters)". The maximum allowable string length is 10,485,760 characters.
*3: IPv6 format is not supported.
*4: Proprietary Ops I type. Documents saved on Git can be selected, and the save path is registered as actual data. Use this only when handling attachments in a UI created with UI version 1.0. Since the [Custom data maintenance] console is created in UI version 1.1, the "document" type cannot be used.
*5: Dates that can be entered for the "timestamp" and "date" types range from 01/01/1900 to 12/31/2099.
*6: Numbers might include decimals.

There are limits to the number of characters that can be set for the value of the “name” attribute of a table or column.
Also, the upper limit varies depending on the conditions.
Refer to the following and define so that all restrictions applying to the conditions are met.

(Table) Character limits for table and column name attributes

No. Prerequisites Restriction
1 Always Table names are a maximum of 45 characters
2 Always Column names are a maximum of 63 characters
3 If there is a "foreign: true" column in the table The total number of characters in the corresponding table name, corresponding column name, table name referred to and column name referred to is a maximum of 53 characters
4 If there is a "type: enum" column in the table The total number of characters in the corresponding table name and corresponding column name is a maximum of 60 characters
5 If there is a "type: enum" column in the table The maximum number of characters in the name of the selection for the corresponding enum name is 63

Ops I provides the following columns by default.

(Table) Columns provided by default

Column name Type Description
id uuid Global unique ID
created_on timestamp Date and time the record was created
updated_on timestamp Date and time the record was updated
created_by varchar (256) Username that created the record
updated_by varchar (256) Username that updated the record
approved boolean Reserved field
domain varchar (256) Reserved field
group varchar (256) Reserved field
version integer Record version
sys_customer varchar (150) ID of the customer to whom the record is linked.
Automatically set for records referenced from workflows.
Customer users cannot access the record if their customer ID does not match the value in this column.
ctx_id uuid Context ID of the workflow referencing the record.
Automatically set for records referenced from workflows.

In addition, the following tables are provided by default. These tables are read-only. They cannot be manipulated from datamodel applications or similar.
To add, edit, or delete data for users, groups, or roles, use the system application or the standard API. For details, see “User management” and “API reference overview > Lists of API functions” in “JP1 Cloud Service Operations Integration API Reference”.

(Table) user table

Column Type Description
id varchar (36) Global unique ID
username varchar (255) Username
first_name varchar (255) First name
last_name varchar (255) Last name
email varchar (255) User e-mail address
customer_id* varchar (255) Customer ID
customer_name* varchar (255) Customer name
company* varchar (255) Company name
department* varchar (255) Department name
memo* varchar (255) Remarks
address* varchar (255) City
phone_number_1* varchar (255) Phone number 1
phone_number_2* varchar (255) Phone number 2
* Available for reference only when joined from a custom view.

(Table) group table

Column Type Description
id varchar (36) Global unique ID
name varchar (255) Group name
description* varchar (255) Detail
email* varchar (255) E-mail address of the group
* Available for reference only when joined from a custom view.

(Table) role table

Column Type Description
id varchar (36) Global unique ID
name varchar (255) Role name

(Table) Available operators

Operator Related SQL Description Example
eq = Equal to - operator: eq
  operands:
    - main_table.main_id
    - ref_table.ref_code
- operator: le
  operands:
    - main_table.main_id
    - "999"
TipsTips
Data type: string or number
(1st and 2nd operands)
ne <> Not equal to
gt > Greater than
ge >= Greater than or equal to
lt < Less than
le <= Less than or equal to
in IN Contained in the specified list or array - operator: notIn
  operands:
    - ref_table.ref_value
    - ["inactive", "deleted"]
TipsTips
Data type: string (1st operand) and array (2nd operand)
notIn NOT IN Not contained in the specified list or array
like LIKE Matches the pattern or wildcard - operator: like
  operands:
    - ref_table.ref_value
    - "test%"
TipsTips
Data type: string (1st and 2nd operands)
notLike NOT LIKE Does not match the pattern or wildcard
isNull IS NULL Is NULL - operator: isNull
  operands:
    - ref_table.ref_value
TipsTips
Data type: string or number
isNotNull IS NOT NULL Is not NULL



The following sections provide definition examples:

■ Custom table definition example

apiVersion: 1.0
kind: datamodel
type: resource
name: lend_vm_datamodel
label: lend_vm_datamodel
description: Defines the datamodel for VM lending ledger

includes:
  - kind: attachment
    name: Evidence of VM creation

#Define column 
tables:
  - name: lend_vm_table
    label: VM lending ledger table
    columns: 
      - name: app_number
        label: Application number
        description: Application serial number
        type: varchar(32)
        not-null: false
        primary: false
        foreign: false

      - name: sys_name
        label: System name
        description: Name of system using VM
        type: enum
        enum:
          - name: order_mgt_sys
            label: Order management system
          - name: customer_mgt_sys
            label: Customer management system
          - name: production_mgt_sys
            label: Production management system
        not-null: false
        primary: false
        foreign: false
        
      - name: vm_name
        label: VM name
        description: Name of lent VM
        type: varchar(32)
        not-null: false
        primary: false
        foreign: false
                
      - name: os_name
        label: OS type
        description: Select Windows or Linux
        type: enum
        enum:
          - name: Windows
            label: Windows
          - name: Linux
            label: Linux
        not-null: false
        primary: false
        foreign: false
        
      - name: cpu
        label: CPU
        description: Number of vCPU cores to use for VM
        type: integer
        not-null: false
        primary: false
        foreign: false
        

(omitted)

                
      - name: start_date
        label: Usage start date
        description: Start date of VM usage
        type: date   
        not-null: false
        primary: false
        foreign: false

      - name: applicant
        label: Usage requester
        description: Usage requester
        type: varchar(256)
        not-null: false
        primary: false
        foreign: false
        

(omitted)

        
      - name: notice
        label: Notice
        type: text
        not-null: false
        primary: false
        foreign: false
        default:
          <strong>The VM lending service will be suspended during the following time periods for system maintenance. <br>
          XX XX, 20XX (Wed) 9:00 - 12:00<br>
          We apologize for any inconvenience. </strong>

Those parts that are followed by similar sections are omitted and marked as "(Omitted)".

■ Custom view definition example

This example shows a part of the Datamodel YAML definition.

Define the custom tables “main_table” and “ref_table”.

tables:
  - name: main_table
    label: main_table
    columns:
      - name: main_id
        label: main_id
        type: varchar(50)
      - name: main_title
        label: main_title
        type: varchar(50)

  - name: ref_table
    label: ref_table
    columns:
      - name: ref_code
        label: ref_code
        type: varchar(50)
      - name: ref_value
        label: ref_value
        type: varchar(50)

Define the custom view. Link the custom tables here.

  • For a single custom table:
    Link the custom table "ref_table" to the custom view "simple_ref_view".
  • When joining multiple custom tables:
    Join and link the custom tables "main_table" and "ref_table" to the custom view "main_ref_join_view".
For a single custom table When joining multiple custom tables
views:
  - name: simple_ref_view
    label: Simple Reference View
    description: Basic view
    columns:
      - source: ref_table.ref_code
        name: code
        label: Code
        description: table code
      - source: ref_table.ref_value
        name: value
        label: Value
        description: table value
    from: ref_table 		views:
  - name: main_ref_join_view
    label: Main Reference Join View
    description: Join view
    columns:
      - source: main_table.main_id
        name: main_id
        label: Main ID
        description: Main table ID
      - source: main_table.main_title
        name: main_title
        label: Main Title
        description: Main table title
      - source: ref_table.ref_code
        name: ref_code
        label: Reference Code
        description: Reference table code
      - source: ref_table.ref_value
        name: ref_value
        label: Reference Value
        description: Reference table value
    from: main_table
    join:
      - type: inner
        table: ref_table
        conditions:
          and:
            - operator: eq
              operands:
                - main_table.main_id
                - ref_table.ref_code

■ Example of JSON Schema file definition

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "type": "object",
  "properties": {
    "text": {
      "type": "string"
    },
    "number": {
      "type": "number"
    },
    "richText": {
      "type": "string",
      "formControl": "text"
    },
    "selectBox": {
      "type": "string",
      "enum": ["item1", "item2", "item3"]
    },
    "checkBox": {
      "type": "boolean"
    },
    "dateTime": {
      "type": "string",
      "format": "date-time"
    },
    "date": {
      "type": "string",
      "format": "date"
    },
    "time": {
      "type": "string",
      "format": "time"
    }
  }
}