im@sparqlを触ってみる その3 - クエリの内容を理解する
の続き。
実際にちょこちょこ触って、クエリの雰囲気を掴めてきたので、自分なりの理解の仕方を書いておく(一部間違えているかも)。
用語
Resource Description Framework - Wikipedia
略称RDF。 データベース(im@sparql)内のデータの構成を示すもの。
主語(subject)・述語(predicate)・目的語(object)の3要素(トリプル)で関係情報を示す。 (リレーショナルデータベースでは無い=NoSQLと考えて良いのだろうか?)
無理矢理オブジェクトに当てはめると、「主語」はオブジェクト本体、「述語」はプロパティ名及び型、「目的語」はプロパティの値、のようなものと解釈した。
主語・述語にはURI、目的語には値やURIなどが指定される(目的語がURIの場合はオブジェクト型のプロパティのような形で階層構造になる)。
SPARQL - Wikipedia
RDFに対するクエリ言語。 SQL風の文法らしい(私自身はSQLを碌に触ったことないので不明)。
今回の記事で扱うクエリはこの言語で書かれたものになる。
どことなくXPathに近い雰囲気も感じる。
クエリ
今回は以下のクエリの内容を理解する。
PREFIX
PREFIX schema: <http://schema.org/> PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX imas: <https://sparql.crssnky.xyz/imasrdf/URIs/imas-schema.ttl#>
主語・述語・目的語の一部はURIで示されるが、いちいち書くのは大変なのでエイリアスを設定するのが一般的。
例えば
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
はhttp://www.w3.org/1999/02/22-rdf-syntax-ns#
をrdf:
で省略できるようになり、
<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>
をrdf:type
と書けるようになる。
SELECT
SELECT *
SELECT ?変数名
とすることで、結果として出力する値(変数)を指定できる。
今回は*
が指定されているため、次のWHERE
内で使用された変数全てが結果として出力される。
WHERE-1
WHERE内は文が多いので、前後に分けて考えてみる。
WHERE { ?s rdf:type imas:ScriptText; imas:Source ?source; schema:text ?text.
WHERE内には主語 述語 目的語
と書き、書かれた条件に当てはまるものが探索される。
まず?s rdf:type imas:ScriptText
となっているため、主語(?s)は任意で、述語がrdf:type
、目的語がimas:ScriptText
となるものが探索され、結果が?s
変数に入る。
3行目には2個しか要素が書かれていないが、前の行が;
で終わっているため、主語は前の行と同じもの指定する、となる(4行目も同じ)。
3行目、4行目では2行目で見つかった主語(?s)についてimas:Source
、schema:text
の情報を?source
変数、?text
変数に格納している。
WHERE-2
?source schema:name ?name; filter(regex(str(?name),"千早")) }
1行目は先ほどと同じで、?source
についてschema:name
の情報を?name
変数に格納している。
2行目ではフィルター、特定の要素のみの抽出が行われている。
filter
はかっこ内の評価結果がTrueとなるものだけを結果として出力する。
中の式は、?name
変数をstr()
関数で文字列化し、regex
(正規表現マッチ)で"千早"
と一致するものだけがTrueとなる。
WHEREまとめ
出力される変数(結果)
rdf:type
がimas:ScriptText
となる要素?s
?s
のimas:Source
を示す?source
?s
のschema:text
を示す?text
?source
のschema:name
を示す?name
ただし、?name
が"千早"
に一致するもののみ。
Order By
order by ?text
?text
変数の値でソート。
自分なりの解釈まとめ
はてなブログだとシンタックスハイライトが効かないので読みにくいけれど……。
# 使用する名前空間の宣言 PREFIX schema: <http://schema.org/> PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX imas: <https://sparql.crssnky.xyz/imasrdf/URIs/imas-schema.ttl#> # WHERE内の全変数出力 SELECT * WHERE { # rdf:type が imas:ScriptText のものを ?s に入れて ?s rdf:type imas:ScriptText; # $s の imas:Source を ?source に imas:Source ?source; # $s の schema:text を ?text に入れる schema:text ?text. # ?source の schema:name を ?name に入れて ?source schema:name ?name; # ?name が "千早" と一致するものだけを抽出 filter(regex(str(?name),"千早")) }order by ?text # ?text の値でソート
参考サイト
im@sparqlを触ってみる その2 - PowerShellのデータ取得を関数化する
上記の記事でPowerShellから情報を取得することには成功したが、そのままでは使いにくいため、関数に切り出してみた。
動作環境
Windows10 Pro 64bit上のWindows PowerShellのみで確認。
> $PSVersionTable Name Value ---- ----- PSVersion 5.1.17134.165 PSEdition Desktop PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...} BuildVersion 10.0.17134.165 CLRVersion 4.0.30319.42000 WSManStackVersion 3.0 PSRemotingProtocolVersion 2.3 SerializationVersion 1.1.0.1
コード(psm1)
毎回クエリを投げるのは負荷的に避けたかったため、簡単なキャッシュ機構を付けてみた。
ただ、関数だけでは状態を保持しにくいため、モジュール(psm1)として、モジュール内の変数にキャッシュを保持させる構成としている。
# 結果のキャッシュ [Collections.Generic.Dictionary[string,psobject[]]]$jsonCache = New-Object -TypeName 'Collections.Generic.Dictionary[string,psobject[]]' function Request-Imasparql { [CmdletBinding()]Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$QueryString ) Set-StrictMode -Version Latest $ErrorActionPreference = 'Stop' # 改行及びインデント削除 [string]$trimQuery = [regex]::Replace( $QueryString , ' *[\r\n]{1,2} *' , [string]::Empty ) # キャッシュにあれば情報取得&リターン [psobject[]]$jsons = @() if ( $jsonCache.TryGetValue( $trimQuery , [ref]$jsons ) ) { Write-Information 'use cache' Write-Information ('query = ' + $trimQuery) return $jsons } Write-Information 'access' [Net.WebClient]$wc = New-Object -TypeName Net.WebClient -Property @{ BaseAddress = 'https://sparql.crssnky.xyz/spql/imas/query' Encoding = [Text.UTF8Encoding]$false } # クエリの設定 $wc.QueryString.Set( 'output', 'json' ) # 出力形式 [string]$escapedQuery = [uri]::EscapeDataString( $trimQuery ) $wc.QueryString.Set( 'query', $escapedQuery ) Write-Information ('escaped query = ' + $escapedQuery) [string]$jsonTxt = $wc.DownloadString( [string]::Empty ) [psobject]$data = ConvertFrom-Json -InputObject $jsonTxt $jsons = $data.results.bindings $jsonCache.Add( $trimQuery , $jsons ) return $jsons } Export-ModuleMember -Function Request-Imasparql
サンプル
上記モジュールを使って前回の記事と同じ処理を書くと以下のようになる。
Import-Module -Name '上記コードを○○.psm1として保存して、保存したファイルのフルパス' # https://sparql.crssnky.xyz/imas/ 内の「千早のセリフテキストを取得」のクエリ [string]$query = @' PREFIX schema: <http://schema.org/> PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX imas: <https://sparql.crssnky.xyz/imasrdf/URIs/imas-schema.ttl#> SELECT * WHERE { ?s rdf:type imas:ScriptText; imas:Source ?source; schema:text ?text. ?source schema:name ?name; filter(regex(str(?name),"千早")) }order by ?text '@ # 情報取得 [psobject[]]$resultJson = Request-Imasparql -QueryString $query -InformationAction Continue # 必要な情報をコンソール出力 $resultJson | select -Property @( @{N="Name";E={$_.name.value}}, @{N="Text";E={$_.text.value}} ) | Write-Output
im@sparqlを触ってみる その1 - PowerShellでデータを取得する
→ その2
はじめに
自分のプログラミング以外の趣味関係のファンサイトに、「im@sparql」というサイトがある。
こちらはWEB上にデータベースがあり、クエリ文字列を付加してGETすれば情報が取れるというものらしい。 (自分も良く分かっていないので詳細は以下なども参照)
自分自身、各種基本知識・理解が不足しているため、「とりあえず動かしてみる」を目標にPowerShellから情報を取得するコードを作ってみた。
取得する情報
今回はim@sparqlの「千早のセリフテキストを取得」のクエリをそのまま使用して、情報を取得する。
コード
# ベースのURL [string]$baseUrl = 'https://sparql.crssnky.xyz/spql/imas/query?query=' # https://sparql.crssnky.xyz/imas/ 内の「千早のセリフテキストを取得」のクエリ # 一行に納めるため、SplitしてからJoinしている [string]$query = [string]::Join( '' , @' PREFIX schema: <http://schema.org/> PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> PREFIX imas: <https://sparql.crssnky.xyz/imasrdf/URIs/imas-schema.ttl#> SELECT * WHERE { ?s rdf:type imas:ScriptText; imas:Source ?source; schema:text ?text. ?source schema:name ?name; filter(regex(str(?name),"千早")) }order by ?text '@.Split("`r`n") ) # クエリ文字列をエスケープ # また、そのままだとXMLで結果が返されるため '&output=json' を付与する [uri]$openUri = [uri]($baseUrl + [uri]::EscapeDataString( $query ) + '&output=json') # 情報取得 $result = Invoke-WebRequest -Method Get -Uri $openUri if ( $result.StatusCode -ne 200 ) { throw } # $result.Content はByte配列なので、文字列(JSON)に変換し # JSON文字列をPSObjectへ変換 $json = [Text.Encoding]::UTF8.GetString( $result.Content ) | ConvertFrom-Json # 必要な情報を出力 $json.results.bindings | select -Property @( @{N="Name";E={$_.name.value}}, @{N="Text";E={$_.text.value}} )
コンソール出力
Name Text ---- ---- 如月千早 …! 如月千早 …………。 如月千早 …いえ、もうすぐ本番ですし、そこで体力を使い果たしてしまったら問題ですよ。 (中略) 如月千早 静香…あなたならいつか、できる時がくるわ。アイドルを続けていれば、必ず。…必ずよ。
ハマったところ
結果がXML
JavaScriptのXMLHttpRequest
やCOMのMSXML2.XMLHTTP
を使用した場合、responseText
としてJSON文字列が取得できた。
しかし、.NET Framework系のメソッドを使用した場合はXMLで結果が返却された。
詳しい人からGET時のURLにパラメータを追加することで結果を制御できるとの話があり、試すとうまくいった('&output=json'の付与)。
文字化け
Worksheets.Item()がObject型を返す理由の妄想
結論
シートモジュールのせい。
(18/07/07 追記)
引数にインデックス・名前の配列を渡すと、Excel.Sheets
型で返されるため、純粋に「返すオブジェクトの種類が固定ではない」せいとなります。
Dim s As Object Set s = Worksheets.Item(Array("Sheet1", 2)) Debug.Print TypeName(s) '-> Sheets
以下は以前の記事の内容になります。
結論への経緯
ワークシートはWorksheet型ではない
Excelのワークシートは、厳密に言うとWorksheet型ではありません(ワークシート以外にもシートの種類はありますが、それらについても同様です)。
では何か?と言うとWorksheet型をベースに拡張された、各シート固有の型となります。 これらの型は一つとして同じ物はありません。
そのため、Worksheet型として扱うとWorksheetとしての共通機能は使用できますが、各シート固有の機能は使用できません。
補足:各シート固有の部分
シートの参照方法
ワークシートを参照する方法には以下の2種類があります。
Sheet1
などのシートオブジェクトを直接参照Worksheets.Item()
による名前・番号を指定した参照
1の場合、Worksheetよりも具体的な型で取得できるため、各シート固有の機能を使用できます。 ただし、プロジェクト外部から使用したい場合は参照設定が必要になります。
2の場合、Worksheets.Item()
の返り値がObject型であるため、正しい名前を指定すれば各シート固有の機能を使用できます。
プロジェクト外部からでも、名前などが正しければ使用できます。
仮に返り値がWorksheet型の場合、一度Object型や各シート固有の型にキャストしないと各シート固有の機能を使用できません。
Worksheets.Item()
がWorksheet型だと問題になる例
実際にそれぞれのシートが別の機能を持っているならば「Sheet1
などのシートオブジェクトを直接参照」で処理をすれば問題はありません。
しかし「同じ形式のシートが複数枚存在し、それぞれに対して同じ処理をしたい」といったことがあった場合、 返り値がWorksheet型だと何らかの型変換が必要になってしまいます。
'm番目からn番目のシート上にある、 '`CommandButton1`という名前のActiveXコントロールのキャプションを表示する For i = m To n '`Worksheets.Item()`がObject型の場合 Debug.Print Worksheets.Item(i).CommandButton1.Caption '直接参照でOK '`Worksheets.Item()`がWorksheet型の場合 Dim tmp As Object Set tmp = Worksheets.Item(i) '要キャスト Debug.Print tmp.CommandButton1.Caption Next i
このあたりを良い感じに処理できるようにするために、Sheets
型という汎用的な型でWorksheets
を表しているのではないかと思います。
画像ファイルを名前の日付で分類(DateTime.TryParseExact の使い方メモ)
が結構便利そうだったので使い方の確認がてらPowerShellでタイトルの処理を作ってみた。
対象のファイル群
Dropboxの自動アップロードによってスマホからPCに同期された画像ファイルに対して処理を行う。
画像ファイルの名前は自動で2018-03-23 12.34.56.jpg
といった形式となるため、その名前を日付に変換して分類を行う。
コード
<# .Synopsis Dropboxでアップロードされた画像ファイルを日付で分類する yyMMのフォルダを作成して、その中に移動する #> [string]$rootPath = # カレントディレクトリ以下のファイルを対象にする場合 $PWD.ProviderPath # PS1として保存して、その保存先フォルダ内を対象にする場合 #[IO.Path]::GetDirectoryName($MyInvocation.MyCommand.Definition) # Dropboxで自動アップロードされたファイルは以下のような名前になる # e.g. 2018年3月23日12時34分56秒に撮影した画像の場合 # 2018-03-23 12.34.56.jpg [string]$dateFormat = 'yyyy-MM-dd HH.mm.ss' # DateTime.TryParseExact の引数指定が面倒だったため、スクリプトブロックに格納 [scriptblock]$tryParse = { param([string]$dateString, [ref]$outDate) return [datetime]::TryParseExact( $dateString, $dateFormat, [Globalization.DateTimeFormatInfo]::CurrentInfo, [System.Globalization.DateTimeStyles]::None, $outDate ) } # パースした結果受け取り用変数 [datetime]$parsedDate = [datetime]::MinValue # $rootPath 内のファイルに対して操作 Get-ChildItem -LiteralPath $rootPath | # 名前を日付に変換できるものだけにフィルター ?{$tryParse.Invoke( # $_ には [System.IO.FileSystemInfo] が入るはず [IO.Path]::GetFileNameWithoutExtension($_.Name), [ref]$parsedDate) } | # 取得した日付を書式設定した値でグループ化(PowerShellのパイプラインの動作上、$parsedDateはちゃんと反映される) Group-Object -Property {$parsedDate.ToString('yyMM')} | # 各グループに対して処理 %{ # 出力先のフォルダ作成 # [IO.Directory]::CreateDirectory は冪等性のある処理っぽいのですでに存在していてもOK [string]$destDir = [IO.Path]::Combine($rootPath, $_.Name) [IO.Directory]::CreateDirectory($destDir) > $null # 各ファイルを移動 $_.Group | %{ [string]$moveToPath = [IO.Path]::Combine($destDir,$_.Name) Write-Host ('{0} => {1}' -f $_.Name, [IO.Path]::GetFileName($destDir)) $_.MoveTo($moveToPath) } }
Excelの選択しているセルの行・列に色を付ける(書式を設定する)
Twitterで面白そうなネタを見つけたのでやってみる。
はまさんのツイート: "アクティブセルの行全体に色を付ける方法です。横に長い表の場合は、便利です。 https://t.co/wJr1nNaxxX… "
リンク先の方法は非常にシンプルで良いのですが、直前に触った一つのセルしか対象になりません。
複数セル選択に対応できないかと、いじくり回していたら何とかなったので備忘録として残します。
コード
'Worksheet Module Private Sub Worksheet_SelectionChange(ByVal Target As Range) '条件付き書式で使う数式(常にTrue) Const FC_ID = "=ISTEXT(""ID1"")" '条件付き書式を適用する範囲 Dim crossRng As Excel.Range Set crossRng = Excel.Union(Target.EntireRow, Target.EntireColumn) '条件付き書式を探す Dim fc As Excel.FormatCondition If tryGetFmtCond(Me, FC_ID, fc) Then '条件付き書式の範囲を変更(元に戻すの履歴は消えない) Call fc.ModifyAppliesToRange(crossRng) Else '見つからなかったので新規作成 Set fc = crossRng.FormatConditions.Add(xlExpression, Formula1:=FC_ID) With fc.Interior .Pattern = XlPattern.xlPatternGray25 .PatternColor = vbYellow End With 'fc.Interior End If End Sub '`ws`から`condFormula`の数式の条件付き書式を探す。 '見つかったらTrueおよび`oFmtCond`に見つかった条件付き書式を返す。 Private Function tryGetFmtCond( _ ws As Excel.Worksheet, _ condFormula As String, _ ByRef oFmtCond As Excel.FormatCondition) As Boolean Dim fc As Excel.FormatCondition For Each fc In ws.Cells.FormatConditions Select Case True Case fc.Type <> XlFormatConditionType.xlExpression, _ fc.Formula1 <> condFormula 'Next Case Else Set oFmtCond = fc Let tryGetFmtCond = True Exit Function End Select Next fc End Function
動作イメージ
— いみひと (@nukie_53) 2018年2月22日
やっていること
- 常にTRUEの条件付き書式を作成する(常にその範囲に書式が設定される)
- 条件付き書式の適用範囲を、選択セルに応じて動的に変更する
余談
FormatCondition
のModifyAppliesToRange
など一部のメソッドは、実行しても「元に戻す」の履歴は消えないようです。
問題点
条件付き書式を自己生成するため、止める手段がありません。
アドイン化&クラスモジュール化して、インスタンス・破棄で制御する形にすれば良いですが……
メモ:VBAからDiscordにメッセージを送信する
完全に見様見真似のメモ。
ほぼこちらの内容をVBAにしただけ。
webhookのURLを取得
この辺から取得する。
設定 > テーマ > 詳細設定 >開発者モード のチェックが必要かも
当然ながら、自分がサーバー権限持ってないと取得できない。
ただ、簡単にサーバーは建てられるので、試すだけだけなら非常に楽。
メッセージ送信
MSXML2.XMLHTTP
オブジェクトを作ってPOSTするだけ。
失敗した場合は、sendした後にresponseText
にエラーメッセージが入る。
また、openの第三引数varAsync
にTrueを指定しないと、sendで強制停止させられてエラーになる。
Sub SendDiscordMsgSample() Dim msg As String msg = "Hello Discord!!" Const WEBHOOK_URL = ' 上記で取得したURL文字列 Dim xhr As Object 'As MSXML2.XMLHTTP60 Set xhr = VBA.CreateObject("MSXML2.XMLHTTP") With xhr .open "POST", WEBHOOK_URL, True .setRequestHeader "Content-Type", "application/json" .send "{""content"":""" & msg & """}" End With 'xhr End Sub
結果
参考
公式。ちゃんと理解していない。