Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

シェルスクリプト＆PowerShellAdvent Calendar 2024

Day 6
@www-tacos

PowerShellでBoxの同期ファイルからURLをとってみる (CLI未使用)

Posted at

概要

Box DriveでPC上に同期しているファイルからオンライン表示用のURLを取得するPowerShellスクリプトを作ってみた話です。

公式のCLIツールやREST APIを使うのではなく、同期用のアプリが持っているSQLiteのDBを覗く方法で実現しています。

背景

Boxでは Box Drive をインストールすることでPC上にファイルを同期することができます。

そして、このアプリをインストールするとコンテキストメニューに「Box.comで表示」という項目が追加され、ここから同期ファイルをブラウザで開くことができます。

図.コンテキストメニューの例

自分はファイルをターミナルから操作する機会が多いので、この操作、つまりファイルからURLを取得する操作をコマンドで実現できないか考えていました。

まず思いつくのは公式の CLIツール や API ですが、これらはファイルIDを指定して何か操作するといったものなので、同期済みのファイルからURLを取得するのには向いてないようでした。

そこでいろいろ調べてみると、どうやらBox DriveはSQLiteデータベースでファイルとIDの紐づきを管理しているようでした。

ということで、SQLiteデータベースからファイルパスをキーにファイルIDを取得するPowerShellスクリプトを書いてみました。

実装と解説

実装

コード全体はこちらです。

box.ps1 
# パラメータでファイルパスを受け取る
Param([string] $Path)

# 定数
$CONST = @{
  "ROOT_DIR"        = "${PSScriptRoot}"
  "SQLITE_DLL_PATH" = "${HOME}\Downloads\box-cli\sqlite-netFx46-binary-bundle-x64-2015-1.0.119.0\System.Data.SQLite.dll"
  "BASE_URL"        = "https://app.box.com"
  "DB_DIR"          = "${HOME}\AppData\Local\Box\Box\data"
  "DB_NAME"         = "sync"
  "SYNC_DIR"        = "${HOME}\Box"
}

# パラメータに定数を追加してスクリプトブロックに渡す
$ArgumentList = @($Path, $CONST)

# プロセスを分けるためにスクリプトブロックで実行する
$ScriptBlock = {
  # ArgumentListで受け取るパラメータ
  Param([string] $Path, [hashtable] $CONST)

  # ファイルを取得する
  $Item = Get-Item -LiteralPath $Path

  # DBファイルをキャッシュする (shm,walも含まれる)
  Get-ChildItem "$($CONST.DB_DIR)\$($CONST.DB_NAME).db*" | % {
    Copy-Item $_.FullName $CONST.ROOT_DIR
  }

  # DBキャッシュのパスを取得する
  $DataSource = (Get-Item "$($CONST.ROOT_DIR)\$($CONST.DB_NAME).db").FullName

  # ファイル種別を判別する
  $FileType = & {
    switch ($Item) {
      { $_.Extension -eq ".boxnote" } {
        return "notes"
      }
      { $_ -is [System.IO.FileInfo] } {
        return "file"
      }
      { $_ -is [System.IO.DirectoryInfo] } {
        return "folder"
      }
      default {
        throw "ファイル種別を判別できませんでした。"
      }
    }
  }

  # 同期ディレクトリ以降のパスを分割する
  $PathList = ($Item.FullName.Replace($CONST.SYNC_DIR, "") -replace "^\\|\\$", "") -split "\\"
  [array]::Reverse($PathList)
  # 親子関係を条件に検索するためのクエリを作成する
  $ChildName = $PathList[0]
  $JoinStmt = $PathList | Select-Object -Skip 1 | % { $i = 0 } {
    $name = $_
    $i += 1
    "inner join box_item as t${i} on t$($i - 1).parent_item_id = t${i}.box_id and t${i}.name = '${name}'"
  } | Join-String -Separator " "
  $Query = "select t0.box_id from box_item as t0 ${JoinStmt} where t0.name = '${ChildName}'"

  # DLLを読み込む
  Add-Type -Path $CONST.SQLITE_DLL_PATH

  # DBに接続する
  $SQLC = New-Object -TypeName System.Data.SQLite.SQLiteConnection
  $SQLC.ConnectionString = "Data Source=${DataSource};Mode=ReadOnly;"
  $SQLC.Open()

  # URL取得を試みる
  try {
    # クエリを実行する
    $Command = $SQLC.CreateCommand()
    $Command.CommandText = $Query
    $Result = $Command.ExecuteReader()

    # 結果テーブルを取得する
    $DataTable = New-Object "System.Data.Datatable"
    $DataTable.Load($Result)

    # レコードが存在すればURLを計算して出力する
    if ($DataTable.Rows.Count -eq 0) {
      throw "URLを取得できませんでした。"
    }
    else {
      $BoxId = $DataTable.Rows[0].BOX_ID
      $BoxLink = "$($CONST.BASE_URL)/${FileType}/${BoxId}"
      Write-Output "${BoxLink}"
    }
  }
  catch {
    throw $_
  }
  finally {
    # ConnectionをCloseする
    $SQLC.Close() 2>$null
  }
}

# スクリプトブロックを新規プロセスで実行する
$Job = Start-Job -ScriptBlock $ScriptBlock -ArgumentList $ArgumentList
Receive-Job -Job $Job -Wait

# GCを明示的に呼んでDBファイルを解放する
[System.GC]::Collect()

# DBキャッシュを削除する
Remove-Item "$($CONST.ROOT_DIR)\$($CONST.DB_NAME).db*"

ちなみに今回はPowerShellスクリプトで実装していますが、SQLiteデータベースの参照ができるならなんでもOKのはずです。
CLIツール化するならGoとかPythonとかTypeScriptで作るのもアリかなと思います。

解説

各種パスの定義

最初に、利用するファイルやパスを定義しておきます。

$CONST = @{
  "ROOT_DIR"        = "${PSScriptRoot}"
  "SQLITE_DLL_PATH" = "${HOME}\Downloads\box-cli\sqlite-netFx46-binary-bundle-x64-2015-1.0.119.0\System.Data.SQLite.dll"
  "BASE_URL"        = "https://app.box.com"
  "DB_DIR"          = "${HOME}\AppData\Local\Box\Box\data"
  "DB_NAME"         = "sync"
  "SYNC_DIR"        = "${HOME}\Box"
}

SQLite用のDDLは事前にダウンロードしておいてください。

Box DriveのDBファイルは ~\AppData\Local\Box\Box\data\sync.db です。

Box Driveの同期ディレクトリはおそらく ~\Box で固定かと思いますが、もし違う場合は適宜読み替えてください。

DBファイルのキャッシュ

DBファイルを手元にコピー(キャッシュ)してそれを参照します。

# DBファイルをキャッシュする (shm,walも含まれる)
Get-ChildItem "$($CONST.DB_DIR)\$($CONST.DB_NAME).db*" | % {
  Copy-Item $_.FullName $CONST.ROOT_DIR
}

# DBキャッシュのパスを取得する
$DataSource = (Get-Item "$($CONST.ROOT_DIR)\$($CONST.DB_NAME).db").FullName

どうもDBファイルはアプリが念入りに掴んでいるようで、オリジナルのDBファイルに対してクエリを実行するとロックエラーが発生してしまいました。
なので今回はスクリプトと同じ場所にDBファイルをコピーして、それを読むようにしています。

いちおう Mode=ReadOnly や read_uncommitted = true の設定は試したんですけどダメでした...
まあ別にコピーする方法でも問題はないので妥協案でこういう仕様にしています。

ファイルの判別

BoxのURLは https://app.box.com/file/0000000000000 のようにベースURL+ファイル種別+ファイルIDの形式なので、拡張子とファイルorディレクトリを判定してファイル種別を取得します。

# ファイル種別を判別する
$FileType = & {
  switch ($Item) {
    { $_.Extension -eq ".boxnote" } {
      return "notes"
    }
    { $_ -is [System.IO.FileInfo] } {
      return "file"
    }
    { $_ -is [System.IO.DirectoryInfo] } {
      return "folder"
    }
    default {
      throw "ファイル種別を判別できませんでした。"
    }
  }
}

検索クエリの作成

まずファイルパスを階層ごとのアイテム名に分割します。

# 同期ディレクトリ以降のパスを分割する
$PathList = ($Item.FullName.Replace($CONST.SYNC_DIR, "") -replace "^\\|\\$", "") -split "\\"
[array]::Reverse($PathList)

例えば /User/tacos/Box/foo/bar/dummy.txt というパスであれば [dummy.txt, bar, foo] のようにBoxディレクトリ以下のパスが逆順に分割されます。逆順にしているのは次のSELECT文作成のためです。

そして分割したパスからSELECT文を生成します。

# 親子関係を条件に検索するためのクエリを作成する
$ChildName = $PathList[0]
$JoinStmt = $PathList | Select-Object -Skip 1 | % { $i = 0 } {
  $name = $_
  $i += 1
  "inner join box_item as t${i} on t$($i - 1).parent_item_id = t${i}.box_id and t${i}.name = '${name}'"
} | Join-String -Separator " "
$Query = "select t0.box_id from box_item as t0 ${JoinStmt} where t0.name = '${ChildName}'"

たとえば上記例の [dummy.txt, bar, foo] の場合だとSELECT文はこうなります。

SELECT
  t0.box_id
FROM
  box_item AS t0
    INNER JOIN box_item AS t1
      ON t0.parent_item_id = t1.box_id
      AND t1.name = 'bar'
    INNER JOIN box_item AS t2
      ON t1.parent_item_id = t2.box_id
      AND t2.name = 'foo'
WHERE
  t0.name = 'dummy.txt'

そしてこのクエリを実行して box_id を取得しURLを算出しています。

DBキャッシュの削除

このスクリプトではメイン処理をスクリプトブロックにして Start-Job で実行していますが、これは最後のDBキャッシュの削除を行うためです。

どうやら SQLiteConnection がデータソースをロックしてしまうようで、スクリプトが終わるまでDBファイルを削除できないみたいでした。

なので以下のように同一スクリプト内にすべて実装するとGCを呼び出しても意味がなく、またDBファイルの削除もできないようでした。

$SQLC = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$SQLC.ConnectionString = "Data Source=sample.db;"
$SQLC.Open()
$SQLC.Close()

# ここでGCしてもSQLiteConnectionが解放されない
[System.GC]::Collect()

# このスクリプト(のSQLiteConnection)がロックしているので消せない
Remove-Item "sample.db"

なので今回はスクリプトブロックを使ってメイン処理を別プロセスに分け、呼び出し元でGCとキャッシュの削除を行っています。

$ScriptBlock = {
  $SQLC = New-Object -TypeName System.Data.SQLite.SQLiteConnection
  $SQLC.ConnectionString = "Data Source=sample.db;"
  $SQLC.Open()
  $SQLC.Close()
}
$Job = Start-Job -ScriptBlock $ScriptBlock
Receive-Job -Job $Job -Wait

# ここでGCするとScriptBlockのSQLiteConnectionが解放される
[System.GC]::Collect()

# ロックされていないので削除できる
Remove-Item "sample.db"

結論

Box Driveの管理しているSQLiteデータベースを見てファイルパスからURLを取得するスクリプトを紹介しました。

今回はURLの取得が目的でしたが、他にもメタデータがいろいろ入っているようなので他の目的にも使えるかもです。

またBoxに限らず他のサービスでも似たような仕組みであれば応用が利くと思います。
実際、OneDriveもSQLite (~/AppData/Local/Microsoft/OneDrive/ListSync/Business1/settings/Microsoft.FilesOnDemand.db) で同期情報を管理しているようでした。

最後まで読んでいただきありがとうございました。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?