Developer Function: Extensions: Extended SQL

Overview¶

To enable data updates that cannot be achieved with standard functions, SQL is extended when creating, updating, deleting, etc. records.

Notes¶

1. If used incorrectly, Pleasanter may become unusable or data may be corrupted, so please test it thoroughly before use.
2. Login verification is performed using API keys and sessions, but permission checks for tables are not performed, so these must be performed within SQL.

Limitations¶

1. After updating the extended SQL JSON file or SQL file, the changes will not be reflected until you restart the application.
2. For security reasons, extended SQL cannot be configured from the web interface.

How To Set Up Extended SQL¶

.¥Pleasanter¥App_Data¥Parameters¥ExtendedSqls¥ Create a json file containing the following content and restart IIS. The file extension must be json. It is possible to organize the hierarchy of ExtendedSqls files into folders. In this case, all json files under the folders will be loaded as configuration files.

Variables Available for CommandText (SQL Server)¶

The following statements in CommandText can be used as variables.

Variables available for CommandText (PostgreSQL)¶

The following statements in CommandText can be used as variables.

Variables Available for CommandText (OnUseSecondaryAuthentication only. Common for SQL Server and PostgreSQL)¶

The following statements in CommandText can be used as variables.

Available placeholders for CommandText¶

The following statements in CommandText will be replaced as placeholders.
However, it cannot be used in CommandText with "OnUseSecondaryAuthentication": true.

Loading CommandText from An External File¶

If you create a text file with the extension .sql added to the json file in the same directory as the json file, you can read CommandText from an external file. The file name of the external file for sample.json is sample.json.sql.

Sample Code¶

Example 1: OnUpdated¶

In the example below, the SQL in CommandText will be executed when a record is updated on site ID:2 with "OnUpdated": true.

JSON¶

{
    "Description": "Sample",
    "SiteIdList": [2],
    "OnUpdated": true,
    "CommandText": "-- Write an arbitrary SQL statement."
}

Example 2: OnSelectingWherePermissionsUsers¶

For example, you might not want certain users to appear in the access control selection list.
Users.Body is where the description field of the user details screen is stored. If you enter NOLIST here, the user will be excluded from the list.

JSON¶

{
    (Omission)
    "OnSelectingWherePermissionsUsers": true,
    "CommandText": "(\"Users\".\"Body\" is null or \"Users\".\"Body\"<>'NOLIST')"
}

Current Option List

Enter NOLIST in the description field for a specific record

Excluded from the selection list

Example 3: OnUseSecondaryAuthentication¶

If you want to exclude a user from two-factor authentication, set a SQL statement that returns one or more items and the value "0".
In the example below, if the logged-in user is a member of group ID:1, he/she will not be subject to two-factor authentication.

JSON¶

{
    (Omission)
    "OnUseSecondaryAuthentication": true,
    "CommandText": "select 0 from [GroupMembers] where [GroupMembers].[GroupId] = 1 and [GroupMembers].[UserId] = @UserId;"
}

Executing a Stored Procedure¶

When executing a stored procedure from extended SQL, grant EXECUTE permission to "Implem.Pleasanter_User".

Linked Server¶

In the case of SQL Server, you can use the linked server function to connect to an external database and perform I/O.

Related Information¶

• Developer Function: Extensions: Extended SQL: Executing Extended SQL from
• Manage Table: Server Script