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

11660 ワード
付箋 sqlite Windows10 sqlite テキストリンク

はじめに

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

まとめ

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