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

# はじめに

Windwos10の付箋アプリ（[Microsoft Sticky Notes](https://www.microsoft.com/ja-jp/p/microsoft-sticky-notes/9nblggh4qghw)）のデータは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について
たとえば、こんな感じの付箋があるとして
![付箋](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348583/0d01c879-3942-ce8f-2fbc-09a36a0c0a0a.png)

```:付箋の中身（サンプル）
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
```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行目にあるらしい

# 確認
付箋の一覧にある検索機能で、検索
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/348583/6683ea9b-1ad9-ce72-5701-d2384f2c3bbd.png)

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

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