Data Store

Data Store

Introduction

The Data Store in Catalyst is a cloud-based relational database management system which stores the persistent data of your application. This data repository includes the data from the application's backend and the data of the application's end users. The Catalyst Data Store enables you to perform data manipulations such as adding new tables, adding and modifying records, defining field characteristics, and deleting data. You can perform these actions by clicking a few buttons, or you can also execute queries in ZCQL to manage the data operations.

You can also set scopes and permissions for each table in the Data Store. They define the availability of the table's data and the levels of access granted to various user roles for each table. The Data Store works with APIs and your application platform's SDK, which you can use to process HTTP requests from the application for various operations. For further information, refer to the SDK documentation on Data Store.

 

SDK and API Documentation

Catalyst offers several APIs to perform various operations in the Data Store. You can also access various Data Store features in the Catalyst SDK packages. For more information and code samples on working in these programming environments, refer to these help pages:

 

Key Features of Catalyst Data Store

  • Can be used to create databases and manage tables in the console without performing query operations.
  • Can be accessed with ZCQL query operations. This enables more control over data processing and handles specific requirements.
  • Can be used to perform data manipulation operations by working with APIs and the platform-specific SDK.
  • Can process bulk data insertion in the background using queues.
  • Can be accessed and modified from client devices based on the scopes and permissions.
  • Contains cloud storage which ensures scalability and universal access.
  • Provides synchronization of data and updates between the various collaborators of the project.
  • Supports advanced search on the indexed columns using Catalyst Search.
  • Supports Bulk Read, Bulk Write, and Bulk Delete operations.
  • Metrics displays the following usage information of Data Store- Table Count and Row Count history.
 

Tables

You can create tables in the Catalyst Data Store to store your application's data. You can create columns for a table that categorize and group similar data together, and then create records.

In Catalyst terminology, the rows in the Data Store represent the individual records of the table, and the columns represent the fields that are created for a table.

Note: You can create upto 200 tables in each project in the development environment. You can request Catalyst for an increase in this limit by contacting our support at support@zohocatalyst.com. We will address each request on a case-by-case basis. There are no upper limits for table creation in the production environment.
 

Create a New Table

To create a new table in the Data Store:

  1. Navigate to Data Store under Develop in the console.
  2. In the Data Store page, click Create a new Table.
  3. Enter the Table Name in the text box and click Create.
    Note: The table name can only contain alphanumeric characters and underscores. Do not include any whitespace or special characters. The table name should also not start with any numeric characters.

The table is now created and displayed in the Data Store page. All tables that you create will be listed on the left hand side of the page.

Once your table is created, a unique Table ID is created for it automatically. You can use it to refer to the table when working with the SDKs and APIs. You can find the Table ID under the table's name when you click on a table from the list.

You can use the search box to search for a particular table by its name.

When you create a new table in the Data Store, a set of default columns for its characteristics are created automatically. You can learn more about these columns in the next section.

 

Update a Table Name

To update a table name in the Data Store:

  1. In the Data Store page, click the ellipsis icon for the table that needs to be renamed and click Edit.
  2. Enter the new name for the table and click Update.
 

Truncate a Table

The truncate operation deletes all the records in the table, while retaining its schema. The columns of the table and the table's metadata will still be available, after the operation has ended.

You can truncate a table if you want to re-populate a table with a new set of data periodically and clear the old data. You can also perform this operation if you want to avoid creating a new table from scratch, and use an existing table with pre-defined schema instead.

To truncate a table in the Data Store:

  1. Click the ellipsis icon for the table that needs to be truncated, then click Truncate.
  2. Enter 'TRUNCATE' in the pop-up window to confirm the operation, then click Confirm.

The truncate process will be initiated. A Catalyst scheduler will run in the background and execute the operation. You will be able to perform other operations in the Data Store while the truncate operation is still in progress.

After the truncate is complete, you will receive a notification in the console about the status of the operation.

Delete a Table

To delete a table from the Data Store:

  1. Click the ellipsis icon for the table that needs to be deleted, then click Delete.
  2. Click Yes, Proceed in the confirmation pop-up.

The table is now deleted from the Data Store.

 

Schema View

The Schema View page of the table displays the columns that are created for the table. As mentioned in the previous section, when a table is created, a set of columns are automatically created for it by default.

Default Columns

The four default columns that exist in every table are:

  • ROWID: Represents the unique ID or the Primary Key of a particular record in the table. When you add a new record, the ROWID value for the record is created automatically.
  • CREATORID: Represents the unique ID of the Catalyst account user.
  • CREATEDTIME: Specifies the time a particular record was created.
  • MODIFIEDTIME: Specifies the time a record was last modified.

In addition to these four default columns, any new columns you create will be listed in the Schema View page under the default columns.

Note: You cannot modify the values in the ROWID and CREATORID columns.
 

Column Characteristics

Every column in the Catalyst Data Store contains a set of characteristics that define the column's structure and behavior. The seven column characteristics are:

  1. Column ID
    The unique ID of a particular column in the table. When you add a new column, the Column ID value for the record is created automatically. You cannot modify the Column ID value.
  2. Column Name
    The name of the column.
  3. Data Type
    Catalyst supports the following data types:
    • Text: Long-form text data type that accommodates more characters than the Var Char data type
    • Var Char: Variable-length character data type that can hold letters and numbers
    • Date: Date value in the format YYYY-MM-DD
    • DateTime: Date and time values in the format YYYY-MM-DD and HH:MM:SS
    • Int: 4-byte integer data type
    • Double: Double precision floating-point number, with a maximum of 17 digits including the decimal part
    • Boolean: Boolean values can be either 'true' or 'false'
    • BigInt: 8-byte integer data type, with a maximum of 17 digits
    • Foreign Key: Refers to the Primary Key of another table
    • Encrypted text : Encrypted long-form text data type
  4. Default Value If the value for a column is left empty by the user, a default value is set for the column.
  5. Search Index Constraint If the Search Index constraint is enabled for a column, it will create a full-text search index for the column in the table. This allows the column to be search-enabled while using Catalyst Search integration in your application.
  6. IsUnique Constraint If the IsUnique constraint is enabled for a column, it will mark the column as containing a unique value, and eliminate the possibility of duplicate values for the same column in the table.
  7. IsMandatory Validator If the IsMandatory validator is enabled for a column, the column will need to contain a non-empty data value. If you attempt to skip adding a value to the column where the IsMandatory validator is enabled, it will generate an error message.

Some of these column characteristics, such as the IsUnique and the IsMandatory are available for multiple data types. The Search Index constraint and Default Value is available for all data types except Text

The Var Char data type includes an additional characteristic called Max Length which lets you define the maximum length of characters allowed to be entered. The highest value for Max Length supported by Catalyst is 255 characters. You will not be able to provide a higher value. When you edit this value, you must always provide a greater value for Max Length than the previous Max Length value.  

Likewise, the Foreign Key data type includes two additional characteristics:

  1. Parent Table: The table whose primary keys should be referred
  2. On Delete: The action that must be performed when the primary key column in the parent table is deleted. It includes two options: Null and Cascade.
    If you select Null, the foreign key's value will be set to null, upon the deletion of the primary key column in the parent table. If you select Cascade, the foreign key column will be deleted entirely upon the deletion of the primary key column in the parent table.

The values of all the column characteristics are pre-populated in the four default columns in every table (ROWID, CREATORID, CREATEDTIME, MODIFIED TIME), except for the Default Value characteristic. The Column ID values are automatically created for the default columns and the Default Value characteristic is null.

The pre-populated values for the other column characteristics for the default columns are:

Column NameData TypeSearch IndexedIsUniqueIsMandatory
ROWIDBigIntFalseFalseFalse
CREATORIDBigIntTrueFalseFalse
CREATEDTIMEDateTimeTrueFalseFalse
MODIFIEDTIMEDateTimeTrueFalseFalse
 

Create a New Column

Note: Catalyst allows you to create upto 100 columns per table in the development environment. You can request Catalyst for an increase in this limit by contacting our support at support@zohocatalyst.com. We will address each request on a case-by-case basis. There are no upper limits for column creation in the production environment.

To create a new column for a table in the Data Store:

  1. In the Schema View section of the table, click [+New Column].
  2. Enter the Column Name and choose the Data Type from the drop-down list.
  3. Enter the required details for the data type and enable the required constraints and validators.
    Note: The column name can only contain alphanumeric characters and certain specific characters. Do not include white spaces in it.
  4. Click Create.

The column is now created and listed in the Schema View page of the table along with its characteristics. You can use the search box to search for a particular column by its name.


 

Edit a Column

You can edit certain characteristics of a column in a table from the Schema View page. The characteristics that cannot be edited include the Column ID, the Data Type and the IsUnique constraint once the column has been created.

To edit a column in a table in the Data Store:

  1. In the Schema View section of the table, click the ellipsis icon for the column that needs to be edited, then click Edit.
  2. Make the necessary changes and click Update.
 

Delete a Column

To delete a column in the Data Store:

  1. In the Schema View section of the table, click the ellipsis icon for the column that needs to be deleted, then click Delete.
  2. Click Yes, Proceed in the confirmation window pop-up.

The column is now deleted from the table.

 

Scopes and Permissions

The scopes and permissions settings in the Data Store allow you to define the Table Scopes and the Table Permissions for every table. You can use these settings to customize the availability of your data and prevent unauthorized access to it.

Table Scopes

The Table Scopes defines the availability scope of a particular table. This limits the availability of the data to the range that you specify. The three table scopes that are available in the Data Store are:

  • Global: If you select Global as the scope for a table that you create, the table's entire data set can be accessed globally. This can be used for tables which all your application's users will require complete access to.
  • Org: If you select the Org as the scope for a table that you create, only the users of your organization can access the table's data.
  • User: If you select User as the scope for a table that you create, only you can access the table's data.

The table scopes are defined for each user role. For example, if you select the table scope as Org for the user role App Admin, the users of the role will be able to view the table's data only if they belong in the same organization as yours.

To set the Table Scope for a table:

  1. In the Data Store page, click the table name and then click Scopes & Permissions.
  2. Select a scope for the table from the three options, for each user role, under Table Scope.

The table's data set is now available for the scope that you have selected for each user role.

 

Table Permissions

The Table Permissions defines the level of permission to access and modify the table which is granted to various Catalyst user roles. The user roles for Catalyst users can be managed in the Roles section of Authentication. You can learn more about user roles from the Roles help page.

There are four specific access permissions for every table in the Data Store:

  • Select: If this option is selected for a user role, the users with that role can view the table's data.
  • Update: If this option is selected for a user role, the users with that role can update the existing data in the table.
  • Insert: If this option is selected for a user role, the users with that role can add new data to the table.
  • Delete: If this option is selected for a user role, the users with that role can delete existing content from the table and the table itself.

To set the Table Permissions for a table:

  1. In the Scopes & Permissions section of the table, click the required check boxes under Table Permissions to enable the necessary access permissions for every user role.

The access permissions for the table's data are now set for the user roles in your Catalyst account.

 

Data Views

The Data Views page for a table lists out the records that have been created for the table. The columns of the records are displayed for each record as columns, including the four default columns CREATORID, MODIFIEDTIME, CREATEDTIME, and ROWID. The values of these default columns are created automatically.

Note: You will be able to create 5000 records in each table per project in the development environment. You can create upto 25,000 records overall in each project in the development environment. There are no upper limits for record creation in the production environment.
 

Create a New Record

To create a new record for a table in the Data Store:

  1. In the Data Views section, click Add Row if you are adding the first record. A [+New Row] is present if records exist for the table.
  2. Enter the values for the columns of the record.
  3. Click Add.

The data record is now saved will now be displayed in the Data Views page. You can use the search box to search for a particular record by the name of one of its indexed columns.

Note:
  • You cannot search for a record by a column name for which the Search Index constraint is disabled.
  • Ensure that you enter the value for a column in its data type's specified format. The text box will prevent you from providing a mismatched value. In certain cases, error messages will be displayed to indicate mismatched values. 
  • For columns where the IsUnique constraint is enabled, ensure that you don't enter values that are already present in other records. Similarly, for the columns where the IsMandatory validator is enabled, ensure that you do not leave the data value blank.
  • For a column where the data type is a Foreign Key, you must provide the ROWID of the parent table's record which it refers, as its value.
 

Edit a Record

To edit a record from a table in the Data Store:

  1. In the Data Views section of the table, click the ellipsis icon for the record that needs to be edited, then click Edit.
  2. Make the necessary changes and click Update.
 

Delete a Record

To delete a record from a table in the Data Store:

  1. In the Data Views section of the table, click the ellipsis icon for the record that needs to be delted, then click Delete.
  2. Click Yes, Proceed in the confirmation pop-up.
 

Bulk Read and Bulk Write Records

Catalyst enables you to perform bulk read and bulk write operations on the tables in the Data Store using the CLI or the APIs.

  • Bulk Read Records: This operation reads large volumes of records from a specific table in the Data Store, and generates a CSV file containing the results of the read operation if the job is successful.
  • Bulk Write Records: This operation fetches large volumes of records from a CSV file uploaded in the File Store, bulk writes them to a specific table in the Data Store, and generates a report file containing the details of the job execution.

To learn about these operations in detail, refer to these help links:

 

Bulk Delete Records

Catalyst enables you to delete records in bulk from a specific table in the Data Store, using an API or as an SDK operation.

The bulk delete operation can delete a maximum of 200 rows in a single API call or an SDK operation.

To learn about this operation in detail, refer to these help links:

Share this post : FacebookTwitter

Still can't find what you're looking for?

Write to us: support@zohocatalyst.com