[File] [PATCH] of Magdir/sql SQLite Write-Ahead Log shared memory *-shm
Christos Zoulas
christos at zoulas.com
Fri Jan 6 17:32:44 UTC 2023
Added, thanks!
christos
> On Jan 5, 2023, at 7:12 PM, Jörg Jenderek <joerg.jen.der.ek at gmx.net> wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hello,
>
> some days ago i run Pirisoft ccleaner. Under item for file extension
> under registry cleaner i can scan for errors. There it complains
> about suffix srd-shm. In the same category i get files with suffix
> like:
> sqlite-shm/db-shm/db3-shm/dbx-shm/aup3-shm/srd-shm
> For every file exist a companion file without 4 byte phrase -shm at
> the end. When running file command (version 5.44) on such SQLite
> examples i get an output like:
>
> F4CEEE47-042C-4828-95A0-DE44EC267A28.db3: SQLite 3.x database
> , last written using
> SQLite version 3013000
> , writer version 2
> , read version 2
> , file counter 125
> , database pages 36
> , cookie 0x200
> , schema 4
> , UTF-8
> , version-valid-for 125
> F4CEEE47-042C-4828-95A0-DE44EC267A28.db3-shm: data
> F4CEEE47-042C-4828-95A0-DE44EC267A28.db3-wal: SQLite Write-Ahead Log
> , version 3007000
> StateRepository-Deployment.srd: SQLite 3.x database
> , user version 21761
> , last written using
> SQLite version 3029000
> , page size 512
> , writer version 2
> , read version 2
> , file counter 76
> , database pages 5934
> , 1st free page 1988
> , free pages 30
> , cookie 0x19
> , schema 4
> , UTF-8
> , version-valid-for 76
> StateRepository-Deployment.srd-shm: dBase III DBT
> , next free block
> index 3007000
> , block length 6144
> StateRepository-Machine.srd: SQLite 3.x database
> , user version 21761
> , last written using
> SQLite version 3029000
> , page size 512
> , writer version 2
> , read version 2
> , file counter 510
> , database pages 3767
> , 1st free page 3700
> , free pages 85
> , cookie 0x1c7
> , schema 4
> , UTF-8
> , version-valid-for 510
> StateRepository-Machine.srd-shm: data
> favicons.sqlite: SQLite 3.x database
> last written using
> SQLite version 3022000
> , page size 32768
> , writer version 2
> , read version 2
> , file counter 17
> , database pages 230
> , cookie 0x6
> , schema 4
> , largest root page 8
> , UTF-8
> , vacuum mode 1
> , version-valid-for 17
> favicons.sqlite-shm: data
> favicons.sqlite-wal: SQLite Write-Ahead Log
> , version 3007000
> filecache.dbx: data
> filecache.dbx-shm: data
> filecache.dbx-wal: SQLite Write-Ahead Log
> , version 3007000
> tada.aup3-shm: data
> tada.aup3-wal: SQLite Write-Ahead Log
> , version 3007000
> wpndatabase.db: SQLite 3.x database
> , user version 7
> , last written using
> SQLite version 3029000
> , writer version 2
> , read version 2
> , file counter 3
> , database pages 94
> , cookie 0x13
> , schema 4
> , UTF-8
> , version-valid-for 3
> wpndatabase.db-shm: data
>
>
> With --extension option i get output like:
>
> F4CEEE47-042C-4828-95A0-DE44EC267A28.db3: sqlite/sqlite3/
> db/db3/dbe/sdb/help
> F4CEEE47-042C-4828-95A0-DE44EC267A28.db3-shm: ???
> F4CEEE47-042C-4828-95A0-DE44EC267A28.db3-wal: sqlite-wal/db-wal
> StateRepository-Deployment.srd: sqlite/sqlite3/
> db/db3/dbe/sdb/help
> StateRepository-Deployment.srd-shm: dbt
> StateRepository-Machine.srd: sqlite/sqlite3/
> db/db3/dbe/sdb/help
> StateRepository-Machine.srd-shm: ???
> favicons.sqlite: sqlite/sqlite3/
> db/db3/dbe/sdb/help
> favicons.sqlite-shm: ???
> favicons.sqlite-wal: sqlite-wal/db-wal
> filecache.dbx: ???
> filecache.dbx-shm: ???
> filecache.dbx-wal: sqlite-wal/db-wal
> tada.aup3-shm: ???
> tada.aup3-wal: sqlite-wal/db-wal
> wpndatabase.db: sqlite/sqlite3/
> db/db3/dbe/sdb/help
> wpndatabase.db-shm: ???
>
> For comparison reason i run the file format identification utility
> TrID ( See https://mark0.net/soft-trid-e.html).
> The SHM samples are also not recognized and described as "Unknown!".
> The database examples are here described as "SQLite 3.x database"
> with deprecated mime type application/x-sqlite3 by
> sqlite-3x.trid.xml. The WAL samples are described as "SQLite
> Write-Ahead Log (little endian)" by sqlite-wal-le.trid.xml (See
> appended trid-v-shm.txt.gz).
>
> For comparison reason i also run the file format identification
> utility DROID ( See https://sourceforge.net/projects/droid/). Here
> only the database examples are recognized. These are described as
> "SQLite Database File Format" with version "3" and with mime type
> application/x-sqlite3 by PUID fmt/729.
>
> TrID list the used file name extension and often with -v option the
> related URL pointing to some information. So i found an official
> document about the WAL-Index File Format on sqlite.org. This is now
> expressed by comment lines inside Magdir/sql like:
> # URL: http://fileformats.archiveteam.org/wiki/SQLite
> # Reference: http://www.sqlite.org/draft/walformat.html#walidxfmt
>
> The description of SQLite Write-Ahead happens inside Magdir/sql by
> starting line like:
> 0 belong&0xfffffffe 0x377f0682 SQLite Write-Ahead Log,
>
> So i add afterward lines for corresponding index (shared memory)
> according to header specification. This looks like:
> 0 ulelong 0x002DE218
>> 0 use shm-le
> 0 ubelong 0x002DE218
>> 0 use \^shm-le
> 0 name shm-le
>> 0 ulelong x SQLite Write-Ahead Log shared memory
> !:mime application/vnd.sqlite3
> !:ext sqlite-shm/db-shm/db3-shm/dbx-shm/aup3-shm/srd-shm
> According to specification values in the shm file are written in the
> native byte order of the host computer. In all my examples this was
> little endian. So to match also big endian machines i put displaying
> part in sub routine shm-le and call this with inverted logic for
> possible endian machines. In the first 4 bytes the WAL-index format
> version number is stored. This is always always 3007000 (2DE218
> hexadecimal). Instead of generic mime type application/octet-stream
> i use mime type that is used by the database itself. The suffix is
> generated by adding 4 byte phrase -shm at name of corresponding
> SQLite database. Typical the two suffix sqlite and db are used for
> such databases. But i also found other extensions. db3-shm is used
> in Acronis BackupAndRecovery sample
> F4CEEE47-042C-4828-95A0-DE44EC267A28.db3-shm. dbx-shm is probably
> used for Dropbox sample filecache.dbx-shm. aup3-shm is used for
> Audacity project like tada.aup3-shm. srd-shm is used for Microsoft
> Windows StateRepository service samples like
> StateRepository-Deployment.srd-shm StateRepository-Machine.srd-shm
> found inside c:\ProgramData\Microsoft\Windows\AppRepository.
>
> Furthermore i show some additional facts that might be useful like
> counter, that is incremented with each transaction, the page size
> (value 1 means 65536), the number of valid and committed frames in
> the WAL file and the size of the database file in pages. This is done
> by lines like:
>> 8 ulelong x \b, counter %u
>> 14 uleshort !1 \b, page size %u
>> 14 uleshort =1 \b, page size 65536
>> 16 ulelong x \b, %u frames
>> 20 ulelong x \b, %u pages
> For technical interested user i also show checksums and salt values.
> This is done by lines like:
>> 13 ubyte !0 \b, checksum type %u
>> 24 ulelong x \b, frame checksum %#x
>> 32 ulequad x \b, salt %#llx
>> 40 ulelong x \b, header checksum %#x
> Some areas are declared as reserved, for future usage or unused. So
> show unexpected values here by lines like:
>> 4 ulelong !0 \b, unused %x
>> 120 ulequad !0 \b, space %#llx
>> 132 ulelong !0 \b, reserved %#x
>
> So it becomes obvious that sample filecache.dbx-shm is valid whereas
> the corresponding database filecache.dbx ( apparently belonging to
> DropBox) is "strange" or corrupt.
>
> After applying the above mentioned modifications by patch
> file-5.44-sql-shm.diff then i get a more precise output
> like:
>
> F4CEEE47-042C-4828-95A0-DE44EC267A28.db3: SQLite 3.x database
> , last written using
> SQLite version 3013000
> , writer version 2
> , read version 2
> , file counter 125
> , database pages 36
> , cookie 0x200
> , schema 4
> , UTF-8
> , version-valid-for 125
> F4CEEE47-042C-4828-95A0-DE44EC267A28.db3-shm: SQLite Write-Ahead Log
> shared memory
> , counter 5
> , page size 4096
> , 22 frames
> , 36 pages
> , frame checksum
> 0xd1ad909
> , salt
> 0x77a014863b478010
> , header checksum
> 0xf1d384c8
> , read-mark[1] 0x16
> F4CEEE47-042C-4828-95A0-DE44EC267A28.db3-wal: SQLite Write-Ahead Log
> , version 3007000
> StateRepository-Deployment.srd: SQLite 3.x database
> , user version 21761
> , last written using
> SQLite version 3029000
> , page size 512
> , writer version 2
> , read version 2
> , file counter 76
> , database pages 5934
> , 1st free page 1988
> , free pages 30
> , cookie 0x19
> , schema 4
> , UTF-8
> , version-valid-for 76
> StateRepository-Deployment.srd-shm: SQLite Write-Ahead Log
> shared memory
> , counter 3
> , page size 512
> , 3 frames
> , 6144 pages
> , frame checksum
> 0x2a05a69d
> , salt
> 0xdf68e13402a0d94f
> , header checksum
> 0xb36a3669
> , 3 backfilled
> (3 attempts)
> , read-mark[1] 0x3
> StateRepository-Machine.srd: SQLite 3.x database
> , user version 21761
> , last written using
> SQLite version 3029000
> , page size 512
> , writer version 2
> , read version 2
> , file counter 510
> , database pages 3767
> , 1st free page 3700
> , free pages 85
> , cookie 0x1c7
> , schema 4
> , UTF-8
> , version-valid-for 510
> StateRepository-Machine.srd-shm: SQLite Write-Ahead Log
> shared memory
> , counter 9
> , page size 512
> , 23 frames
> , 4096 pages
> , frame checksum
> 0x5b10cde0
> , salt
> 0xc54c8b6236383cc1
> , header checksum
> 0x1c20b018
> , 23 backfilled
> (23 attempts)
> , read-mark[1] 0x17
> favicons.sqlite: SQLite 3.x database
> , last written using
> SQLite version 3022000
> , page size 32768
> , writer version 2
> , read version 2
> , file counter 17
> , database pages 230
> , cookie 0x6
> , schema 4
> , largest root page 8
> , UTF-8
> , vacuum mode 1
> , version-valid-for 17
> favicons.sqlite-shm: SQLite Write-Ahead Log
> shared memory
> , counter 14
> , page size 32768
> , 34 frames
> , 230 pages
> , frame checksum
> 0x7872ac70
> , salt
> 0x24ed33b2a4e49e9f
> , header checksum
> 0x745511ac
> , read-mark[1] 0x22
> favicons.sqlite-wal: SQLite Write-Ahead Log
> , version 3007000
> filecache.dbx: data
> filecache.dbx-shm: SQLite Write-Ahead Log
> shared memory,
> counter 38
> , page size 1024
> , 215 frames
> , 1680 pages
> , frame checksum
> 0x2a24e8dc
> , salt
> 0x1a5fd27fa6de85e7
> , header checksum
> 0x57bb3403
> , read-mark[1] 0xd7
> filecache.dbx-wal: SQLite Write-Ahead Log
> , version 3007000
> tada.aup3-shm: SQLite Write-Ahead Log
> shared memory
> , counter 2
> , page size 65536
> , 3 frames
> , 13 pages
> , frame checksum
> 0xcd74b6c5
> , salt
> 0xb2e4a81b0ae835af
> , header checksum
> 0x9b8b1438
> , 3 backfilled
> (3 attempts)
> , read-mark[1] 0x3
> tada.aup3-wal: SQLite Write-Ahead Log
> , version 3007000
> wpndatabase.db: SQLite 3.x database
> , user version 7
> , last written using
> SQLite version 3029000
> , writer version 2
> , read version 2
> , file counter 3
> , database pages 94
> , cookie 0x13
> , schema 4
> , UTF-8
> , version-valid-for 3
> wpndatabase.db-shm: SQLite Write-Ahead Log
> shared memory
> , counter 47
> , page size 4096
> , 680 frames
> , 256 pages
> , frame checksum
> 0xf701e417
> , salt
> 0x322a624802a74eea
> , header checksum
> 0x8521ca92
> , 680 backfilled
> (680 attempts)
> , read-mark[1] 0x2a8
>
> With additional --extension option now i get:
> F4CEEE47-042C-4828-95A0-DE44EC267A28.db3: sqlite/sqlite3/
> db/db3/dbe/sdb/help
> F4CEEE47-042C-4828-95A0-DE44EC267A28.db3-shm: sqlite-shm/
> db-shm/db3-shm/dbx-shm/
> aup3-shm/srd-shm
> F4CEEE47-042C-4828-95A0-DE44EC267A28.db3-wal: sqlite-wal/db-wal
> StateRepository-Deployment.srd: sqlite/sqlite3/
> db/db3/dbe/sdb/help
> StateRepository-Deployment.srd-shm: sqlite-shm/
> db-shm/db3-shm/dbx-shm/
> aup3-shm/srd-shm
> StateRepository-Machine.srd: sqlite/sqlite3/
> db/db3/dbe/sdb/help
> StateRepository-Machine.srd-shm: sqlite-shm/
> db-shm/db3-shm/dbx-shm/
> aup3-shm/srd-shm
> favicons.sqlite: sqlite/sqlite3/
> db/db3/dbe/sdb/help
> favicons.sqlite-shm: sqlite-shm/
> db-shm/db3-shm/dbx-shm/
> aup3-shm/srd-shm
> favicons.sqlite-wal: sqlite-wal/db-wal
> filecache.dbx: ???
> filecache.dbx-shm: sqlite-shm/
> db-shm/db3-shm/dbx-shm/
> aup3-shm/srd-shm
> filecache.dbx-wal: sqlite-wal/db-wal
> tada.aup3-shm: sqlite-shm/
> db-shm/db3-shm/dbx-shm/
> aup3-shm/srd-shm
> tada.aup3-wal: sqlite-wal/db-wal
> wpndatabase.db: sqlite/sqlite3/
> db/db3/dbe/sdb/help
> wpndatabase.db-shm: sqlite-shm/
> db-shm/db3-shm/dbx-shm/
> aup3-shm/srd-shm
>
> I hope my diff file can be applied in future version of
> file utility.
>
> Obviously some SQLite database sub classification are missing. I
> will try to handle this in a future session.
>
> With best wishes
> Jörg Jenderek
> - --
> Jörg Jenderek
> -----BEGIN PGP SIGNATURE-----
> Comment: Using GnuPG with Thunderbird - https://www.enigmail.net/
>
> iF0EARECAB0WIQS5/qNWKD4ASGOJGL+v8rHJQhrU1gUCY7dncwAKCRCv8rHJQhrU
> 1mZMAJwIvQykexahmT2cpmAqW3tUUdoeXwCggfPnMApXZzO+8d/hdACO2sOnMRY=
> =ZBqB
> -----END PGP SIGNATURE-----
> <trid-v-shm.txt.gz><file-5_44-sql-shm_diff.DEFANGED-24><file-5_44-sql-shm_diff_sig.DEFANGED-25>--
> File mailing list
> File at astron.com
> https://mailman.astron.com/mailman/listinfo/file
> <sanitizer.log>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 235 bytes
Desc: Message signed with OpenPGP
URL: <https://mailman.astron.com/pipermail/file/attachments/20230106/ae1bbb8d/attachment.asc>
More information about the File
mailing list