開発者向け機能：拡張機能：拡張SQL
## 概要
標準機能では実現できないデータの更新を可能にするために、レコードの作成時、更新時、削除時等のSQLを拡張します。
## 注意事項
1. 誤って使用するとプリザンターが利用できなくなったり、データが壊れたりする可能性がありますので、十分なテストを行った上でご利用ください。
1. APIキーやセッションによるログイン確認は行いますが、テーブルへの権限チェックなどが行われないため、SQLの中で実施する必要があります。
## 制限事項
1. 拡張SQLのJSONファイルやSQLファイルを更新した後は「アプリケーションを再起動」するまで反映しません。
1. セキュリティ上の理由により拡張SQLはWeb画面から設定できません。
## 拡張SQLの設定方法
.¥Pleasanter¥App_Data¥Parameters¥ExtendedSqls¥ 配下に以下の内容を含むjsonファイルを作成し、IISを再起動してください。ファイルの拡張子は必ずjsonにしてください。ExtendedSqls配下はフォルダで階層化する事が可能です。この場合、配下の全てのjsonファイルが設定ファイルとして読み込まれます。
|パラメータ名|設定例|説明|
|:--|:--|:--|
|Name|Sample|APIから実行させる際の名前を設定します。|
|Description|"このSQLは・・・を実行します"|SQLの説明。動作には影響しません。|
|Disabled|false|trueの場合は無効化され動作しません。|
|DeptIdList|[1,2,3]|対象となる組織IDを配列形式で指定します。指定しない場合には省略可能です。|
|GroupIdList|[1,2,3]|対象となるグループIDを配列形式で指定します。指定しない場合には省略可能です。|
|UserIdList|[1,2,3]|対象となるユーザIDを配列形式で指定します。指定しない場合には省略可能です。|
|SiteIdList|[1,2,3]|対象となるサイトのサイトIDを配列形式で指定します。指定しない場合には省略可能です。|
|IdList|[1,2,3]|対象となるレコードのIDを配列形式で指定します。指定しない場合には省略可能です。|
|ColumnList|["ClassA"]|OnSelectingColumnを使用する際に対象となる[データベースのカラム名](/ja/manual/dev-column-name)を指定します。|
|Api|false|trueの場合[APIから拡張SQLを実行](/ja/manual/extended-sql-api)する事が可能です。また[サーバスクリプト](/ja/manual/table-management-server-script)の「extendedSqlオブジェクト」から使用可能となります。|
|DbUser|"Owner"|APIから拡張SQLを実行する際のDBユーザを指定します。指定しない場合は"User"で実行されます。APIから実行する場合のみ有効となります。|
|Html|false|trueの場合Htmlのinputタグにhiddenタイプとして取得した値を格納します。|
|OnCreating|false|trueの場合レコード作成前に実行します。|
|OnCreated|false|trueの場合レコード作成後に実行します。|
|OnUpdating|false|trueの場合レコード更新前に実行します。|
|OnUpdated|true|trueの場合レコード更新後に実行します。|
|OnDeleting|false|trueの場合レコード削除前に実行します。|
|OnDeleted|false|trueの場合レコード削除後に実行します。|
|OnBulkDeleting|false|trueの場合レコード一括削除前に実行します。|
|OnBulkDeleted|false|trueの場合レコード一括削除後に実行します。|
|OnImporting|false|trueの場合レコードインポート前に実行します。|
|OnImported|false|trueの場合レコードインポート後に実行します。|
|OnSelectingColumn|false|trueの場合一覧画面及び編集画面に表示する項目の内容を動的に取得するためのSQLを追加します。|
|OnSelectingWhere|false|trueの場合一覧画面及び編集画面に表示するレコードを限定するためのWhere句を追加します。|
|OnSelectingWhereParams|["ExtendedFieldName"]|指定した拡張フィールドに値が入力されたときにOnSelectingWhereへ値をパラメータとして追加します。 ※値は拡張フィールドの"Name"を指定します|
|OnSelectingWherePermissionsDepts|false|trueの場合アクセス制御の選択肢一覧に表示するDeptsテーブルのレコードを限定するWhere句を追加します|
|OnSelectingWherePermissionsGroups|false|trueの場合アクセス制御の選択肢一覧に表示するGroupsテーブルのレコードを限定するWhere句を追加します|
|OnSelectingWherePermissionsUsers|false|trueの場合アクセス制御の選択肢一覧に表示するUsersテーブルのレコードを限定するWhere句を追加します|
|OnSelectingOrderBy|false|trueの場合一覧画面に表示するレコードを並び替えるためのOrderBy句を追加します。|
|OnSelectingOrderByParams|["ExtendedFieldName"]|指定した拡張フィールドに値が入力されたときにOnSelectingOrderByへ値をパラメータとして追加します。 ※値は拡張フィールドの"Name"を指定します|
|OnUseSecondaryAuthentication|false|trueの場合二段階認証前に実行します。|
|CommandText|"update [Issues] set ....."|実行するSQLを記述します。|
## CommandTextに使用可能な変数（SQL Server）
CommandText内の下記の記述は変数として利用できます。
|変数|設定例|説明|
|:--|:--|:--|
|@_T|1|テナントIDに置換されます。|
|@_D|10|実行ユーザが所属する組織の組織IDに置換されます。|
|@_U|30|実行ユーザのユーザIDに置換されます。|
## CommandTextに使用可能な変数（PostgreSQL）
CommandText内の下記の記述は変数として利用できます。
|変数|設定例|説明|
|:--|:--|:--|
|@ipT|1|テナントIDに置換されます。|
|@ipD|10|実行ユーザが所属する組織の組織IDに置換されます。|
|@ipU|30|実行ユーザのユーザIDに置換されます。|
## CommandTextに使用可能な変数（OnUseSecondaryAuthenticationのみ。SQL Server、PostgreSQL共通）
CommandText内の下記の記述は変数として利用できます。
|変数|設定例|説明|
|:--|:--|:--|
|@TenantId|1|テナントIDに置換されます。|
|@UserId|30|実行ユーザのユーザIDに置換されます。|
## CommandTextに使用可能なプレースホルダー
CommandText内の下記の記述はプレースホルダーとして置換されます。
ただし、”OnUseSecondaryAuthentication”: trueでのCommandTextでは利用できません。
|プレースホルダー|設定例|説明|
|:--|:--|:--|
|{{SiteId}}|2|サイトIDに置換されます。|
|{{Id}}|10|レコードのIDに置換されます。|
|{{Timestamp}}|20171106 09:00:00.000|フォームから投入されたレコード更新前のタイムスタンプに置換されます。OnUpdatingの場合のみ使用できます。レコードの更新競合のチェックに利用できます。|
## CommandTextを外部ファイルから読み込む
jsonファイルと同じディレクトリに、jsonファイルに拡張子.sqlを追加したテキストファイルを作成すると、CommandTextを外部ファイルから読み込む事が可能です。sample.jsonの外部ファイルのファイル名はsample.json.sqlです。
## サンプルコード
### 例1 : OnUpdated
下記の例では、サイトID:2で、"OnUpdated": trueによりレコードが更新された際にCommandTextのSQLが実行されます。
##### JSON
```
{
"Description": "Sample",
"SiteIdList": [2],
"OnUpdated": true,
"CommandText": "-- Write an arbitrary SQL statement."
}
```
### 例2 : OnSelectingWherePermissionsUsers
アクセス制御の選択肢リストに特定のユーザを表示したくない場合の例。
Users.Bodyはユーザ詳細画面の説明欄の格納先。ここにNOLISTと入力しておくとリストから除外される。
##### JSON
```json
{
(省略)
"OnSelectingWherePermissionsUsers": true,
"CommandText": "(\"Users\".\"Body\" is null or \"Users\".\"Body\"<>'NOLIST')"
}
```
現在の選択肢リスト
特定のレコードにて説明欄にNOLISTと入力
選択肢リストから除外されている
### 例3 : OnUseSecondaryAuthentication
二段階認証の対象外としたい場合は、1件以上かつ「0」の値を返すSQLを設定する。
下記の例では、ログインユーザがグループID:1のグループメンバーである場合、二段階認証の対象外とする。
##### JSON
```json
{
(省略)
"OnUseSecondaryAuthentication": true,
"CommandText": "select 0 from [GroupMembers] where [GroupMembers].[GroupId] = 1 and [GroupMembers].[UserId] = @UserId;"
}
```
## ストアドプロシージャの実行
拡張SQLからストアドプロシージャを実行する場合には「Implem.Pleasanter_User」にEXECUTE権限を付与してください。
## リンクサーバー
SQL Serverの場合にはリンクサーバー機能を使用することで、外部のデータベースと接続しI/Oを行うことが可能です。
## 関連情報
<div id="ManualList"><ul><li><a href="/ja/manual/table-management-server-script">テーブルの管理：サーバスクリプト</a><span>2024/10/08 up</span></li></ul></article>
<ul><li><a href="/ja/manual/dev-column-name">項目名とデータベース上のカラム名の対応</a><span>2024/03/05 up</span></li></ul></article>
<ul><li><a href="/ja/manual/server-script">開発者向け機能：サーバスクリプト</a><span>2023/06/01 up</span></li></ul></article>
<ul><li><a href="/ja/manual/extended-sql-api">開発者向け機能：拡張機能：拡張SQL：APIから拡張SQLを実行する</a><span>2024/09/13 up</span></li></ul></article>
<ul><li><a href="/ja/manual/faq-server-script-log">FAQ：サーバスクリプトのエラーログを出力したい</a><span>2024/04/29 up</span></li></ul></article></div><input id="SearchTextHidden" type="hidden" value="" />
