CREATE TABLE SystemIndex_1_PropertyStore_Metadata (
  Id          INTEGER PRIMARY KEY,
  UniqueKey   TEXT    NOT NULL UNIQUE,
  Name        TEXT    NOT NULL,
  PropertyId  INTEGER NOT NULL,
  VariantType INTEGER NOT NULL,
  StorageType INTEGER NOT NULL,
  MaxSize     INTEGER,
  Flags       INTEGER NOT NULL
);

select * from SystemIndex_1_PropertyStore_Metadata limit 40

select count(*) from SystemIndex_1_PropertyStore_Metadata

enum VARENUM
    {   VT_EMPTY    = 0,
    VT_NULL = 1,
    VT_I2   = 2,
    VT_I4   = 3,
    VT_R4   = 4,
    VT_R8   = 5,
    VT_CY   = 6,
    VT_DATE = 7,
    VT_BSTR = 8,
    VT_DISPATCH = 9,
    VT_ERROR    = 10,
    VT_BOOL = 11,
    VT_VARIANT  = 12,
    VT_UNKNOWN  = 13,
    VT_DECIMAL  = 14,
    VT_I1   = 16,
    VT_UI1  = 17,
    VT_UI2  = 18,
    VT_UI4  = 19,
    VT_I8   = 20,
    VT_UI8  = 21,
    VT_INT  = 22,
    VT_UINT = 23,
    VT_VOID = 24,
    VT_HRESULT  = 25,
    VT_PTR  = 26,
    VT_SAFEARRAY    = 27,
    VT_CARRAY   = 28,
    VT_USERDEFINED  = 29,
    VT_LPSTR    = 30,
    VT_LPWSTR   = 31,
    VT_RECORD   = 36,
    VT_INT_PTR  = 37,
    VT_UINT_PTR = 38,
    VT_FILETIME = 64,
    VT_BLOB = 65,
    VT_STREAM   = 66,
    VT_STORAGE  = 67,
    VT_STREAMED_OBJECT  = 68,
    VT_STORED_OBJECT    = 69,
    VT_BLOB_OBJECT  = 70,
    VT_CF   = 71,
    VT_CLSID    = 72,
    VT_VERSIONED_STREAM = 73,
    VT_BSTR_BLOB    = 0xfff,
    VT_VECTOR   = 0x1000,
    VT_ARRAY    = 0x2000,
    VT_BYREF    = 0x4000,
    VT_RESERVED = 0x8000,
    VT_ILLEGAL  = 0xffff,
    VT_ILLEGALMASKED    = 0xfff,
    VT_TYPEMASK = 0xfff
    } ;

(defvar my-vt-alist
  '((0 . EMPTY) (1 . NULL) (2 . I2) (3 . I4) (4 . R4) (5 . R8) (6 . CY)
    (7 . DATE) (8 . BSTR) (9 . DISPATCH) (10 . ERROR) (11 . BOOL)
    (12 . VARIANT) (13 . UNKNOWN) (14 . DECIMAL) (16 . I1) (17 . UI1)
    (18 . UI2) (19 . UI4) (20 . I8) (21 . UI8) (22 . INT) (23 . UINT)
    (24 . VOID) (25 . HRESULT) (26 . PTR) (27 . SAFEARRAY) (28 . CARRAY)
    (29 . USERDEFINED) (30 . LPSTR) (31 . LPWSTR) (36 . RECORD) (37 . INT_PTR)
    (38 . UINT_PTR) (64 . FILETIME) (65 . BLOB) (66 . STREAM) (67 . STORAGE)
    (68 . STREAMED_OBJECT) (69 . STORED_OBJECT) (70 . BLOB_OBJECT) (71 . CF)
    (72 . CLSID) (73 . VERSIONED_STREAM) (#xfff . BSTR_BLOB) (#x1000 . VECTOR)
    (#x2000 . ARRAY) (#x4000 . BYREF) (#x8000 . RESERVED) ))

(defun my-vt-name (vt-id)
  (alist-get vt-id my-vt-alist))
;; TEST: (my-vt-name 31) => LPWSTR

| Id | Name                                  | VariantType | (VT Name)| StorageType | MaxSize | Flags |
|----+---------------------------------------+-------------+----------+-------------+---------+-------|
| 25 | 'System.Search.HitCount'              |           3 | I4       |           3 |    NULL |     6 |
| 26 | 'System.Search.GatherTime'            |          64 | FILETIME |          12 |       8 |    58 |
| 27 | 'System.Message.CcName'               |          65 | BLOB     |          13 |    5120 |   119 |
| 28 | 'System.Contact.HomeAddress1Street'   |          31 | LPWSTR   |          11 |    2048 |    55 |
| 29 | 'System.Search.AccessCount'           |          19 | UI4      |           4 |    NULL |    22 |
| 30 | 'System.ItemFolderPathDisplay'        |          31 | LPWSTR   |          11 |   65536 |    51 |
| 31 | 'System.Contact.HomeAddress1Locality' |          31 | LPWSTR   |          11 |    2048 |    55 |
#+TBLFM: $4='(my-vt-name $3);L

(defvar my-wds-property-alist
  (let ((db (sqlite-open "Windows.db")))
    (unwind-protect
        (sqlite-select db "select * from SystemIndex_1_PropertyStore_Metadata")
      (sqlite-close db))))

(defun my-wds-property-name (id) (nth 2 (assq id my-wds-property-alist)))
(defun my-wds-property-vt (id) (nth 4 (assq id my-wds-property-alist)))
(defun my-wds-property-vt-name (id) (my-vt-name (my-wds-property-vt id)))
;; TEST: (cons (my-wds-property-name 30) (my-wds-property-vt-name 30)) => ("System.ItemFolderPathDisplay" . LPWSTR)

CREATE TABLE SystemIndex_1_PropertyStore (
  WorkId   INTEGER NOT NULL,
  ColumnId INTEGER NOT NULL,
  Value    BLOB NOT NULL,
    PRIMARY KEY (WorkId, ColumnId)
) WITHOUT ROWID;

| WorkId | ColumnId | (PropertyName)                     | (VTName) | Value                                                |
|--------+----------+------------------------------------+----------+------------------------------------------------------|
|      1 |       13 | System.IsFolder                    | BOOL     | 1                                                    |
|      1 |       17 | System.FilePlaceholderStatus       | UI4      | 6                                                    |
|      1 |       26 | System.Search.GatherTime           | FILETIME | X'2925c18d31b6d901'                                  |
|      1 |       29 | System.Search.AccessCount          | UI4      | 0                                                    |
|      1 |       30 | System.ItemFolderPathDisplay       | LPWSTR   | 'C:\ProgramData\Microsoft\Windows'                   |
|      1 |       33 | System.ItemPathDisplay             | LPWSTR   | 'C:\ProgramData\Microsoft\Windows\スタート メニュー' |
|      1 |       35 | System.Search.LastIndexedTotalTime | R8       | 0.0                                                  |
|      1 |       39 | System.ItemUrl                     | LPWSTR   | 'file:C:/ProgramData/Microsoft/Windows/Start Menu'   |
|      1 |      188 | System.DateImported                | FILETIME | X'00ea2bc7d261d801'                                  |
|      1 |      295 | System.IsAttachment                | BOOL     | 0                                                    |
|      1 |      306 | System.IsEncrypted                 | BOOL     | 0                                                    |
|      1 |      308 | System.ItemDate                    | FILETIME | X'00ea2bc7d261d801'                                  |
|      1 |      342 | System.Kind                        | BLOB     | X'66006f006c00640065007200'                          |
|      1 |      349 | System.ThumbnailCacheId            | UI8      | X'5600f5bf02b2cf41'                                  |
|      1 |      376 | System.Search.Store                | LPWSTR   | 'file'                                               |
|      1 |      414 | System.ItemFolderNameDisplay       | LPWSTR   | 'Windows'                                            |
|      1 |      420 | System.ItemTypeText                | LPWSTR   | 'ファイル フォルダー'                                |
|      1 |      432 | System.ItemNameDisplay             | LPWSTR   | 'スタート メニュー'                                  |
#+TBLFM: $3='(my-wds-property-name $2);L::$4='(my-wds-property-vt-name $2);L

select * from SystemIndex_1_PropertyStore where ColumnId=39 and Value LIKE 'file:C:/Users/USERNAME/Pictures/%.jpg';

select * from SystemIndex_1_PropertyStore where WorkId=3338;

(defun my-decode-wstr (hexstr)
  (decode-coding-string
   (apply 'unibyte-string
          (cl-loop for i below (length hexstr) by 2
                   collect (string-to-number (substring hexstr i (+ i 2)) 16)))
   'utf-16le))

(my-decode-wstr "5400610072006f000000480061006e0061006b006f00")

(my-decode-wstr "530065006e0062007500720069000000540061006b0061006f00")

#+begin_src sqlite :db Windows.db :header :exports both :eval no-export
select * from SystemIndex_1_PropertyStore_Metadata limit 40
#+end_src

#+RESULTS:
| Id | UniqueKey         | Name         | PropertyId | VariantType | StorageType | MaxSize | Flags |
|  1 | 4703-System_Null  | System.Null  |       4703 |           1 |          13 |     512 |    22 |
...略...

(let ((db (sqlite-open "~/test-sqlite.db")))
  (unwind-protect
      (progn
        ;; ファイル情報テーブルを作る
        (sqlite-execute
         db
         "create table if not exists files (
          id integer primary key,
          path text not null unique,
          mod_time real not null,
          size integer not null)")

        ;; カレントディレクトリにある全ファイルのフルパス、更新日時、サイズを挿入する
        ;; (本当は一文でやった方が速いみたい values (?,?,?),(?,?,?)...)
        (cl-loop for (file . attrs) in (directory-files-and-attributes "." t)
                 do (sqlite-execute
                     db
                     "insert or replace into files (path,mod_time,size) values (?,?,?)"
                     (list file
                           (float-time (file-attribute-modification-time attrs))
                           (file-attribute-size attrs))))

        ;; 更新日時降順に ((パス 更新日時)...) を取得する
        (sqlite-execute
         db
         "select path,datetime(mod_time,'unixepoch','localtime') from files where mod_time >= ? order by mod_time desc"
         ;; 過去30日分取得
         (list (- (float-time) (* 30 24 60 60)))))
    ;; エラーが出てもちゃんと閉じるよ(しなくてもGCのタイミングで閉じるけど)
    (sqlite-close db)))