Work with Data Stores in JNW Toolkit

JNW Toolkit contains three Maven modules that providу functionality for working with Data Stores.

These modules are as follows:

• com.workfusion.jnw.toolkit:jnw-toolkit-datastores provides a Spring DataSource configured to access the Control Tower database. You can use this module to build a fully custom database layer with any database (DB) framework that can use the Spring DataSource.
• com.workfusion.jnw.toolkit:jnw-toolkit-spring-jdbc depends on jnw-toolkit-datastores and integrates Spring JDBC with Control Tower Data Stores. You can use this module with Spring JDBC or any compatible DB framework.
• com.workfusion.jnw.toolkit:jnw-toolkit-ormlite depends on jnw-toolkit-datastores and integrates the ORMLite library with Data Stores. ORMLite is currently the de facto standard ORM framework for AI Agent development. You can use this module to quickly build your database layer with ORMLite, which is the recommended approach for most use cases.

tip

ORMLite is a framework that provides lightweight Object Relational Mapping between Java classes and SQL databases. There are more mature ORMs that provide this functionality, including Hibernate and iBatis. However, ORMLite is a simple yet powerful wrapper around JDBC.

Data Stores in Control Tower

A Control Tower Data Store corresponds to a database table with a name prefixed ds_. Only such tables are visible in Control Tower. For example, if the table is called ds_attachments, it is visible in the Control Tower UI as the attachments Data Store.

If the table is used by an AI Agent, its name must also conform to specific naming patterns. The patterns ensure that each version of each AI Agent uses separate tables. This prevents naming conflicts between AI Agents and allows multiple versions of the same AI Agent to run simultaneously in the same environment.

Each AI Agent Asset Bundle must contain a meta-info.json file that describes the AI Agent. In addition to other properties, this file must contain the following information (values are examples):

{
  "USE_CASE_CODE": "my-ai-agent",
  "USE_CASE_VERSION": "1.0",
  "DATA_MODEL_VERSION":"1.2",
  "VERSIONED_DATA_STORE_NAME_PATTERN": "{$use_case_code}_{$entity_name}_v{$version}",
  "NON_VERSIONED_DATA_STORE_NAME_PATTERN": "{$use_case_code}_{$entity_name}"
}

note

In previous platform versions, AI Agents were called the Use Cases. USE_CASE_ prefixes remain in the names of configuration properties for backward compatibility.

USE_CASE_CODE is a unique identifier of the AI Agent, and USE_CASE_VERSION is the version of the AI Agent. DATA_MODEL_VERSION is an optional property that allows increasing the version of the AI Agent without changing the version of the data model.

VERSIONED_DATA_STORE_NAME_PATTERN and NON_VERSIONED_DATA_STORE_NAME_PATTERN are the patterns to which all table names must conform. The values in the snippet above are the de facto standard patterns used in most AI Agents, but you can use any pattern you want. Note that patterns must not contain the ds_ prefix, as it is added automatically.

Variables in the patterns are replaced with the corresponding values from the meta-info.json file. The {$use_case_code} placeholder is replaced with the value of USE_CASE_CODE. The {$entity_name} placeholder is replaced with the name of the Data Store (entity here as ORM is the default approach for AI Agent development). The {$version} placeholder is replaced with the value of DATA_MODEL_VERSION if present, otherwise with the value of USE_CASE_VERSION.

For the given example, the table name of the versioned attachments Data Store will be ds_my_ai_agent_attachments_v1_2, while the table name of the non-versioned attachments Data Store named will be ds_my_ai_agent_attachments. For the difference between versioned and non-versioned Data Stores, see Types of Data Stores. Note that NON_VERSIONED_DATA_STORE_NAME_PATTERN cannot contain the {$version} placeholder.

JNW Toolkit manages table names automatically, applying the naming pattern at runtime based on the name and version information provided by Java Native Worker. The following sections explain how to use Data Stores in your AI Agent. These examples assume the meta-info.json file of the AI Agent Asset Bundle is the same as in the example above.

Using versioned Data Stores with Spring JDBC

Add the following dependency to the <dependencies> section of your pom.xml file:

<dependency>
    <groupId>com.workfusion.jnw.toolkit</groupId>
    <artifactId>jnw-toolkit-spring-jdbc</artifactId>
</dependency>

It depends on spring-boot-starter-data-jdbc and jnw-toolkit-datastores, so you do not need to add them explicitly.

Now create your entity and repository classes. An entity is a Java class that represents a single row in the database. A repository is a class that provides database operations for the entity.

To create an entity, define a class that represents the data you want to store in the database and annotate it with Spring Data annotations, for example:

public class InputFile {

    @Id
    private Integer id;

    @Column
    private String name;

    @Column
    private String content;
    
    // constructor, getters, setters, equals(), hashCode(), and toString() are omitted as not relevant for this example.
}

To create a repository for your entity, define an interface that extends org.springframework.data.repository.CrudRepository and annotate it with org.springframework.stereotype.Repository. CrudRepository is a generic interface; you must parameterize it with your entity type and the type of its primary key, for example:

@Repository
public interface InputFileRepository extends CrudRepository<InputFile, Integer> {
    
    // this interface can be left empty unless you wish to implement more complex operations than provided by CrudRepository.
    
}

Spring Data generates the implementation of the repository interface at runtime. You just need to inject it into your code and use it. For example, the following code creates a Bot Task that creates two InputFile entities, saves them to the database, and returns them as the task output.

@Component
public class MyTasks {

    private final InputFileRepository inputFileRepository;
    
    @Autowired
    public MyTasks(InputFileRepository inputFileRepository) {
        this.inputFileRepository = inputFileRepository;
    }
    
    @AutoContract
    @AutoTaskProcessor
    @TaskProcessorOutput(deconstruct = false, columnName = "inputfile")
    public List<InputFile> generateInputFiles() {
        final var inputFiles = List.of(
            new InputFile("input1.txt", "content1"),
            new InputFile("input2.txt","content2")
        ); 
        
        inputFiles.forEach(inputFileRepository::save); // save the `InputFile` entities to the database

        return inputFiles;
    }

}

When working with entities, Spring Data derives the table name from the entity class name. In the example above, pure Spring Data would try to save data to a table named input_file. With the jnw-toolkit-spring-jdbc module, however, Spring Data conforms to the patterns in the meta-info.json file, so the table name in this example is ds_my_ai_agent_input_file_v1_2.

You can change the table name by adding the org.springframework.data.relational.core.mapping.Table annotation to the entity class, for example:

@Table("file")
public class InputFile {
   // now the entity will be mapped to the table named `ds_my_ai_agent_file_v1_2` 
}

Using versioned Data Stores with ORMLite

Add the following dependency to the <dependencies> section of your pom.xml file:

<dependency>
    <groupId>com.workfusion.jnw.toolkit</groupId>
    <artifactId>jnw-toolkit-ormlite</artifactId>
</dependency>

It depends on ormlite-jdbc and jnw-toolkit-datastores, so you do not need to add them explicitly.

Now create your entity and repository classes. An entity is a Java class that represents a single row in the database. A repository is a class that provides database operations for the entity.

To create an entity, define a class that represents the data you want to store in the database and annotate it with ORMLite annotations, for example:

@DatabaseTable
public class InputFile {

    @DatabaseField(id = true, generatedId = true)
    private Integer id;

    @DatabaseField
    private String name;

    @DatabaseField
    private String content;
    
    // constructor, getters, setters, equals(), hashCode(), and toString() are omitted as not relevant for this example.
}

To create a repository for your entity, define a class that extends com.workfusion.jnw.toolkit.orm.repository.OrmLiteRepository and make it a Spring component. OrmLiteRepository is a generic class; you must parameterize it with your entity type. You also need to write a constructor that passes the required arguments to the OrmLiteRepository constructor, for example:

@Component
public class InputFileRepository extends OrmLiteRepository<InputFile> {

    @Autowired
    // ConnectionSourceProvider and TaskExecutionContext are present in the Spring context, so you don't need to worry about them.
    public TestOrmDataRepository(ConnectionSourceProvider connectionSourceProvider, TaskExecutionContext taskExecutionContext) {
        super(connectionSourceProvider, taskExecutionContext, InputFile.class);
    }

    // This class inherits all standard database operations from the `OrmLiteRepository` class. You can add your own operations using the source of the `OrmLiteRepository` as an example.
}

Now inject the repository into your code and use it. For example, the following code creates a Bot Task that creates two InputFile entities, saves them to the database, and returns them as the task output.

@Component
public class MyTasks {

    private final InputFileRepository inputFileRepository;
    
    @Autowired
    public MyTasks(InputFileRepository inputFileRepository) {
        this.inputFileRepository = inputFileRepository;
    }
    
    @AutoContract
    @AutoTaskProcessor
    @TaskProcessorOutput(deconstruct = false, columnName = "inputfile")
    public List<InputFile> generateInputFiles() {
        final var inputFiles = List.of(
            new InputFile("input1.txt", "content1"),
            new InputFile("input2.txt","content2")
        ); 
        
        inputFiles.forEach(inputFileRepository::create); // save the `InputFile` entities to the database

        return inputFiles;
    }

}

When working with entities, ORMLite derives the table name from the entity class name. In the example above, pure ORMLite would try to save data to a table named input_file. With the jnw-toolkit-ormlite module, however, ORMLite conforms to the patterns in the meta-info.json file, so the table name in this example is ds_my_ai_agent_input_file_v1_2.

You can change the table name by specifying the tableName parameter of the @DatabaseTable annotation on the entity class, for example:

@DatabaseTable(tableName = "file")
public class InputFile {
   // now the entity will be mapped to the table named `ds_my_ai_agent_file_v1_2` 
}

Types of Data Stores

From the JNW Toolkit's perspective, there are three types of Data Stores: versioned, non-versioned, and global. By default, all Data Stores are considered versioned. However, you can change the type of a Data Store by adding the com.workfusion.devtools.Datastore annotation to the Spring Data or ORMLite entity class, for example:

@Datastore(type = DatastoreType.NON_VERSIONED)
@DatabaseTable // ORMLite entity
public class InputFile {}

Or:

@Datastore(type = DatastoreType.GLOBAL)
@Table // Spring Data entity
public class InputFile {}

• A versioned Data Store is mapped to a database table by VERSIONED_DATA_STORE_NAME_PATTERN. Each version of the AI Agent has its own table.

• A non-versioned Data Store is mapped to a database table by NON_VERSIONED_DATA_STORE_NAME_PATTERN. All versions of the AI Agent with the same USE_CASE_CODE share the same table.

• A global Data Store is mapped to a database table with only the ds_ prefix added. All versions of all AI Agents share the same table.

Versioned Data Stores are the safest option and should be used in most cases. Some designs, however, benefit from non-versioned Data Stores. Global Data Stores are used for rare, specific cases.

Reusable Bot Tasks

info

Mechanisms in jnw-toolkit-spring-jdbc and jnw-toolkit-ormlite are designed to be used in the context of an existing AI Agent. If a Bot Task that uses them is placed in a Business Process not belonging to any AI Agent, database operations from these modules will fail.

As shown in the examples above, the Data Store's table name is resolved based on the AI Agent to which the Bot Task belongs. This means that if the Bot Task is reused in another AI Agent, it will use the database table of that AI Agent, not the original one. This allows you to reuse Bot Tasks across various AI Agents without changing the code.

However, in some cases, it is necessary to use a database table from a fixed AI Agent, regardless of the AI Agent to which the Bot Task belongs. Currently, the only way to do this is to use DatastoreType.GLOBAL and change the table name in the entity class.