Windows10の付箋をキーワード検索するハナシ

はじめに

Windwos10の付箋アプリ（Microsoft Sticky Notes）のデータはsqliteで管理されているみたい！
うまくやれば付箋の中身のキーワード検索くらいできそう

データの場所

%USERPROFILE%\AppData\Local\packages\Microsoft.MicrosoftStickyNotes_8wekyb3d8bbwe\LocalState\plum.sqlite

データの内容

テーブル自体は複数あり、付箋のデータはNoteというテーブルで管理されている模様

Noteの構造

１付箋、１レコードで構成されている模様

列名	型	PK	Not Null	備考
Text	varchar			付箋の中身
WindowPosition	varchar			多分付箋の位置、Bese64ぽいのでエンコードされている
IsOpen	integer			表示：0、非表示：1
IsAlwaysOnTop	integer			0が設定、常に最前面に表示するような設定あったっけ？
CreationNoteIdAnchor	varchar			NULLが設定
Theme	varchar			付箋の色が設定（例：黄色い付箋＝Yellow）
IsFutureNote	integer			0が設定
RemoteId	varchar			NULLが設定
ChangeKey	varchar			NULLが設定
LastServerVersion	varchar			NULLが設定
RemoteSchemaVersion	integer			NULLが設定
IsRemoteDataInvalid	integer			NULLが設定
PendingInsightsScan	integer			NULLが設定
Type	varchar			NULLが設定
Id	varchar	○	○	UUIDが設定
ParentId	varchar			UUIDが設定
CreatedAt	bigint			数値18桁が設定
DeletedAt	bigint			NULLが設定
UpdatedAt	bigint			数値18桁が設定

一部項目は、クラウド保存時に設定されるのかもしれない
（試していない）

Textについて

たとえば、こんな感じの付箋があるとして

付箋の中身（サンプル）

html {
    height  : 100%;
    overflow: hidden;
}
body {
    height  : 100%;
    overflow: auto;
}

Textの中身（サンプル）

\id=0276d7d9-39d3-41bd-908b-f22a2f766d4f html {
\id=b504d61d-e1e9-40d0-9d0b-5a55bbaa84e7     height  : 100%;
\id=add3d3ca-494b-4dd5-904a-c1e59926a24e     overflow: hidden;
\id=30e210c6-2762-4db9-a699-c6fe3ed3fe12 }
\id=825775bd-b9a5-42d3-b010-6c1cf1bb8f78 body {
\id=ef22f95b-fd9d-463d-9b56-355e9cf03215     height  : 100%;
\id=b47ac60c-1f5e-4ebc-bdf3-2876e37bf032     overflow: auto;
\id=57921bf5-dd95-4ded-b7ad-613341f3e800 }

改行コードはLF
行ごとに先頭にID（UUID）が付与されているが用途は不明

ちなみに付箋の中身は、文字修飾や画像の挿入もできるが
その場合、Textがどのようになるかは不明（試していない）

キーワード検索するためのSQL

付箋のキーワード検索

WITH RECURSIVE SplitTextIntoLines(id,Lno,RetrievedText,RemainingText) AS
(
  SELECT
    Id,
    1 Lno,
    substr(Text, 1, INSTR(Text, CHAR(10)) - 1) RetrievedText,
    substr(Text, INSTR(Text, CHAR(10)) + 1) || CHAR(10) RemainingText
  FROM Note
  UNION ALL
  SELECT
    Id,
    Lno + 1,
    SUBSTR(RemainingText, 1, INSTR(RemainingText, CHAR(10)) - 1) RetrievedText,
    SUBSTR(RemainingText, INSTR(RemainingText, CHAR(10)) + 1) RemainingText
  FROM SplitTextIntoLines
  WHERE RemainingText != '' 
)
, NoteForSearch AS
(
  SELECT
    SplitTextIntoLines.Id,
    SplitTextIntoLines.Lno,
	Note.IsOpen,
	Note.Theme,
    SUBSTR(SplitTextIntoLines.RetrievedText, 42) LineText
  FROM SplitTextIntoLines
  INNER JOIN Note
    ON SplitTextIntoLines.Id = Note.Id
)
SELECT 
  Id,
  IsOpen,
  Theme,
  Lno,
  LineText
FROM NoteForSearch
WHERE LineText LIKE '%[検索ワード]%'
ORDER BY
  Id,
  Lno
;

再起呼び出しでText内の改行で分割し、行単位になるようにしてある
各行の先頭のIDも不要なのでバッサリカット

使用する際は、[検索キーワード]を好きなキーワードに置き換えて使用する

実行

SQLiteのコマンドラインツールでデータを開く

sqlite3.exeでplum.sqliteをで開く

>sqlite3.exe %USERPROFILE%\AppData\Local\packages\Microsoft.MicrosoftStickyNotes_8wekyb3d8bbwe\LocalState\plum.sqlite

SQLを入力

SQLを入力

sqlite> WITH RECURSIVE SplitTextIntoLines(id,Lno,RetrievedText,RemainingText) AS
   ...> (
   ...>   SELECT
   ...>     Id,
   ...>     1 Lno,
   ...>     substr(Text, 1, INSTR(Text, CHAR(10)) - 1) RetrievedText,
   ...>     substr(Text, INSTR(Text, CHAR(10)) + 1) || CHAR(10) RemainingText
   ...>   FROM Note
   ...>   UNION ALL
   ...>   SELECT
   ...>     Id,
   ...>     Lno + 1,
   ...>     SUBSTR(RemainingText, 1, INSTR(RemainingText, CHAR(10)) - 1) RetrievedText,
   ...>     SUBSTR(RemainingText, INSTR(RemainingText, CHAR(10)) + 1) RemainingText
   ...>   FROM SplitTextIntoLines
   ...>   WHERE RemainingText != ''
   ...> )
   ...> , NoteForSearch AS
   ...> (
   ...>   SELECT
   ...>     SplitTextIntoLines.Id,
   ...>     SplitTextIntoLines.Lno,
   ...> Note.IsOpen,
   ...> Note.Theme,
   ...>     SUBSTR(SplitTextIntoLines.RetrievedText, 42) LineText
   ...>   FROM SplitTextIntoLines
   ...>   INNER JOIN Note
   ...>     ON SplitTextIntoLines.Id = Note.Id
   ...> )
   ...> SELECT
   ...>   Id,
   ...>   IsOpen,
   ...>   Theme,
   ...>   Lno,
   ...>   LineText
   ...> FROM NoteForSearch
   ...> WHERE LineText LIKE '%html {%'
   ...> ORDER BY
   ...>   Id,
   ...>   Lno
   ...> ;

SQLを実行

SQLを実行

cb3430ac-922c-4089-b60d-0b99a8f0e75c|1|Yellow|11|html {

開かれている黄色い付箋の11行目にあるらしい

確認

付箋の一覧にある検索機能で、検索

SQLは正しい結果を取得できていた模様
ただし、検証不足なので付箋のデータによっては正常に動かない可能性あり！

まとめ

不明な部分多すぎだけど、キーワード検索はできた。
やったぜ！