Azure DevOps PipelineでSchemaSpyを動かしSQLServerのER図を生成してStorageAccountの静的Webページにデプロイできるようにしたメモ

# 概要
[前回はGitHub ActionsでDBドキュメントをデプロイした](https://qiita.com/hibohiboo/items/e199e02012a771e9f416)ので、今回はAzure DevOps Pipeline でStorage Accountに公開する。

[公開したER図](https://hibowebstorageaccount.z11.web.core.windows.net/dbdocs/atrpg/relationships.html)

[この時点のソース](https://github.com/hibohiboo/async-ttrpg/blob/18541115a5cb52f3c314ab8472227e27aa65bdcf/.azure-devops/db-docs-pipeline.yaml)

# 環境
- Windows 11
- azure-cli 2.71.0

# StorageAccount準備
[参考記事](https://tech-lab.sios.jp/archives/37044) ではIP制限しているが、ここでは行わない。[Bicep](https://blog.engineer-memo.com/2024/07/15/bicep-%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%A6-azure-%E3%82%B9%E3%83%88%E3%83%AC%E3%83%BC%E3%82%B8%E3%81%AE%E9%9D%99%E7%9A%84%E3%81%AA-web-%E3%82%B5%E3%82%A4%E3%83%88%E3%82%92%E6%9C%89%E5%8A%B9/)で作成する。

```bash:infra/bin/addWebStorageAccount.bash
#!/bin/bash

BIN_DIR=$(cd $(dirname $0) && pwd)
source $BIN_DIR/common.bash

cd $BICEP_DIR && az deployment group create \
  --name storageAccountForWebDeployment \
  --template-file addWebStorageAccount.bicep \
  -g $RESOURCE_GROUP_NAME
```

```bicep:infra/biceps/addWebStorageAccount.bicep
@description('デプロイ先のリージョン')
param location string = resourceGroup().location

var storageAccountName = 'st${uniqueString(resourceGroup().id)}'

resource storageAccount 'Microsoft.Storage/storageAccounts@2024-01-01' = {
  name: storageAccountName
  location: location
  sku: { name: 'Standard_LRS' }
  kind: 'StorageV2'
  properties: {
    accessTier: 'Hot'
    minimumTlsVersion: 'TLS1_2'
    publicNetworkAccess: 'Enabled'
  }
}

resource staticWebsite 'Microsoft.Storage/storageAccounts/blobServices/containers@2024-01-01' = {
  name: '${storageAccount.name}/default/$web'
  properties: {
    publicAccess: 'None'
  }
}

resource blobService 'Microsoft.Storage/storageAccounts/blobServices@2024-01-01' = {
  parent: storageAccount
  name: 'default'
  properties: {
    deleteRetentionPolicy: { enabled: false }
}

resource customScript 'Microsoft.Resources/deploymentScripts@2023-08-01' = {
  name: 'enableStaticWebsiteScript'
  location: location
  kind: 'AzurePowerShell'
  properties: {
    azPowerShellVersion: '13.0'
    environmentVariables: [
      {
        name: 'storageAccountKey'
        value: storageAccount.listKeys().keys[0].value
      }
    ]
    arguments: '-storageAccountName ${storageAccount.name}'
    scriptContent: '''
      param(
        [string] $storageAccountName
      )

      $context = New-AzStorageContext -StorageAccountName $storageAccountName -StorageAccountKey $env:storageAccountKey
      Enable-AzStorageStaticWebsite -Context $context -IndexDocument "index.html" -ErrorDocument404Path "404.html"
    '''
    cleanupPreference: 'OnSuccess'
    retentionInterval: 'PT1H'
  }
}

output staticWebsiteUrl string = 'https://${storageAccount.name}.z11.web.${environment().suffixes.storage}/'

```

実行。実行結果から[jq](https://www.tohoho-web.com/ex/jq.html#about)でURLを取り出している。

```
$ ./infra/bin/addWebStorageAccount.bash | jq '.properties.outputs'
```

実行結果
```json
{
  "staticWebsiteUrl": {
    "type": "String",
    "value": "https://<作成したStorageAccountの名前>.z11.web.core.windows.net/"
  }
}
```

# パイプライン構築手順
[参考記事](https://tech-lab.sios.jp/archives/37044) に従う。`schemaspy.properties`をセキュアに扱っているが、今回は外部DBに接続せず、DockerのDBを参照するのでそこまでしていない。

# JCDBドライバ確認

[ダウンロードページ](https://learn.microsoft.com/ja-jp/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=azuresqldb-current)から確認する。リダイレクトのＵＲＬなので、実際のＵＲＬを確認にはひと手間必要。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99876/599c15e2-54eb-4652-9725-cc80273de7c4.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99876/5f0b1d37-a2c7-4f64-839a-d5249e45949c.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99876/bdea117e-3efa-43d6-be15-9fdbbad6ee58.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99876/12346813-35b0-46c7-8a3e-e903a18443e4.png)

下記のようなURLが取得できる。

```
https://download.microsoft.com/download/745da347-bd51-4bf0-a84e-3465608d8856/jpn/sqljdbc_12.10.0.0_jpn.tar.gz
```

# SpySchema設定ファイル

```properties:schemaspy.properties
schemaspy.t=mssql08
schemaspy.dp=/drivers/mssql-jdbc-12.0.0.jre11.jar
schemaspy.host=host.docker.internal
schemaspy.port=1433
schemaspy.u=sa
schemaspy.p=MyPassword@123
schemaspy.db=test
schemaspy.schemas=atrpg
schemaspy.o=/output
```

# パイプライン

[Service Connection作成手順](https://qiita.com/hibohiboo/items/e7c1d14a30f914fae4d8#azuresubscription-%E3%81%AE%E8%A8%AD%E5%AE%9A)

```yml:.azure-devops/db-docs-pipeline.yaml
trigger:
  branches:
    include:
      - develop
  paths:
    include:
      - database/*
      - .azure-devops/db-docs-pipeline.yaml
pool:
  vmImage: ubuntu-latest
steps:
- checkout: self
- script: |
    chmod 777 database/initdb.d/entrypoint.sh
  displayName: 'permissions for initdb.d'
- task: DockerCompose@1
  displayName: 'Build and run SQL Server container'
  inputs:
    containerregistrytype: 'Azure Container Registry'
    dockerComposeFile: 'database/docker-compose.yml'
    action: 'Run services'
    detached: true

- script: |
    mkdir jdbc
    wget -P jdbc/ https://download.microsoft.com/download/745da347-bd51-4bf0-a84e-3465608d8856/jpn/sqljdbc_12.10.0.0_jpn.tar.gz
    tar -zxvf jdbc/sqljdbc_12.10.0.0_jpn.tar.gz
  displayName: 'Download JDBC Driver'

- script: |
    mkdir -m 777 output
    ls -R
  displayName: 'Create output directory'

- script: |
    docker pull schemaspy/schemaspy
    docker run \
    -v "$(Build.SourcesDirectory)/output:/output" \
    -v "$(Build.SourcesDirectory)/schemaspy.properties:/schemaspy.properties" \
    -v "$(Build.SourcesDirectory)/sqljdbc_12.10/jpn/jars/:/drivers" \
    --add-host=host.docker.internal:host-gateway \
    schemaspy/schemaspy:latest -connprops encrypt\\=false
  displayName: 'Run SchemaSpy'
- task: PublishPipelineArtifact@1
  displayName: 'Publish SchemaSpy output'
  inputs:
    targetPath: '$(Build.SourcesDirectory)/output'
    artifact: 'SchemaSpy'
    publishLocation: 'pipeline'

- task: DownloadPipelineArtifact@2
  displayName: 'Download SchemaSpy artifact'
  inputs:
    artifact: 'SchemaSpy'
    path: '$(Pipeline.Workspace)/SchemaSpy'

- task: AzureCLI@2
  inputs:
    azureSubscription:  <作成したServiceCoonnectionの名前> 
    scriptType: bash
    scriptLocation: inlineScript
    inlineScript: |
      az storage blob upload-batch \
        --account-name <作成したStorageAccountの名前> \
        --destination '$web' \
        --destination-path 'dbdocs' \
        --source "$(Pipeline.Workspace)/SchemaSpy" \
        --overwrite
```

dockerで建てたSQLServerに接続するために、`schemaspy/schemaspy:latest -connprops encrypt\\=false`で認証をスキップしている。

また、プロパティで設定した`host.docker.internal`がLinuxでも有効になるよう、`-add-host=host.docker.internal:host-gateway`のオプションを追加している。




# パイプラインの実行
パイプラインを最初に流すときにはPermitが必要。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99876/01fc16dd-8f36-461c-abec-272d5044bd48.png)


# IP制限をかける ( 2025/05/03 追記 )
IP制限はネットワークから追加できる。 現在接続のＩＰならクライアントＩＰアドレスの追加を押し、保存すればＯＫ。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99876/d725e40b-43cc-4994-bb4e-3733ac6457a8.png)

ただ、この状態だとパイプラインのＩＰも制限してしまって更新に失敗する。

```
In addition, setting the corresponding environment variables can avoid inputting credentials in your command. Please use --help to get more information about environment variable usage.
ERROR: 
The request may be blocked by network rules of storage account. Please check network rule set using 'az storage account show -n accountname --query networkRuleSet'.
If you want to change the default action to apply when no rule matches, please use 'az storage account update'.
```

そのため、パイプラインのIPを追加するように修正する。( [参考: しばやん雑記 - Azure Pipelines の Hosted Agent が持っている Outbound IP アドレスを知りたい](https://blog.shibayan.jp/entry/20200414/1586791379))
ネットワーク追加後に10秒sleepをかけているのは、追加直後だとまだＩＰ制限が解除されていないからである。

```yml:.azure-devops/db-docs-pipeline.yaml
- task: AzureCLI@2
  inputs:
    azureSubscription: 'azure-dev-hobby'
    scriptType: bash
    scriptLocation: inlineScript
    inlineScript: |
      STORAGE_ACCOUNT_NAME=<作成したStorageAccountの名前>
      IP_ADDRESS=$(curl -s https://ipinfo.io/json | jq -r '.ip')
      echo "Your IP address is: $IP_ADDRESS"

      az storage account network-rule add \
        --account-name $STORAGE_ACCOUNT_NAME \
        --ip-address $IP_ADDRESS
      
      sleep 10

      az storage blob upload-batch \
        --account-name $STORAGE_ACCOUNT_NAME \
        --destination '$web' \
        --destination-path 'dbdocs' \
        --source "$(Pipeline.Workspace)/SchemaSpy" \
        --overwrite
```

[この時点のソース](https://github.com/hibohiboo/async-ttrpg/blob/3120b940ce1b1d84da63e70cbdc54c1879146249/.azure-devops/db-docs-pipeline.yaml)


# 参考

[GitHub ActionsでSchemaSpyのER図を生成してGitHubPagesにデータベースのドキュメントをデプロイできるようにしたメモ](https://qiita.com/hibohiboo/items/e199e02012a771e9f416)
[【SchemaSpy】データベース仕様書の作成と公開を自動化してみた【Azure Pipelines】](https://tech-lab.sios.jp/archives/37044)
[Bicep を使用して Azure ストレージの静的な Web サイトを有効化する](https://blog.engineer-memo.com/2024/07/15/bicep-%E3%82%92%E4%BD%BF%E7%94%A8%E3%81%97%E3%81%A6-azure-%E3%82%B9%E3%83%88%E3%83%AC%E3%83%BC%E3%82%B8%E3%81%AE%E9%9D%99%E7%9A%84%E3%81%AA-web-%E3%82%B5%E3%82%A4%E3%83%88%E3%82%92%E6%9C%89%E5%8A%B9/)