[File] [PATCH] of Magdir/sql SQLite Write-Ahead Log shared memory *-shm
Jörg Jenderek
joerg.jen.der.ek at gmx.net
Fri Jan 6 00:12:36 UTC 2023
-----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-----
-------------- next part --------------
A non-text attachment was scrubbed...
Name: trid-v-shm.txt.gz
Type: application/x-gzip
Size: 765 bytes
Desc: not available
URL: <https://mailman.astron.com/pipermail/file/attachments/20230106/e2e17952/attachment.bin>
-------------- next part --------------
--- file-5.44/magic/Magdir/sql.old 2022-08-16 15:01:06.000000000 +0200
+++ file-5.44/magic/Magdir/sql 2023-01-06 00:48:06.971055200 +0100
@@ -201,6 +201,63 @@
0 belong&0xfffffffe 0x377f0682 SQLite Write-Ahead Log,
!:ext sqlite-wal/db-wal
>4 belong x version %d
+# Summary: SQLite Write-Ahead-Log index (shared memory)
+# From: Joerg Jenderek
+# URL: http://fileformats.archiveteam.org/wiki/SQLite
+# Reference: http://www.sqlite.org/draft/walformat.html#walidxfmt
+# iVersion; WAL-index format version number; always 3007000=2DE218h
+0 ulelong 0x002DE218
+>0 use shm-le
+# big endian variant not tested
+0 ubelong 0x002DE218
+>0 use \^shm-le
+# show information about SQLite Write-Ahead-Log shared memory
+0 name shm-le
+>0 ulelong x SQLite Write-Ahead Log shared memory
+#!:mime application/octet-stream
+!:mime application/vnd.sqlite3
+# db3-shm Acronis BackupAndRecovery F4CEEE47-042C-4828-95A0-DE44EC267A28.db3-shm
+# dbx-shm probably Dropbox filecache.dbx-shm
+# aup3-shm Audacity project tada.aup3-shm
+# srd-shm Microsoft Windows StateRepository service StateRepository-Deployment.srd-shm StateRepository-Machine.srd-shm:
+!:ext sqlite-shm/db-shm/db3-shm/dbx-shm/aup3-shm/srd-shm
+# unused padding space; must be zero
+>4 ulelong !0 \b, unused %x
+# iChange; unsigned integer counter, incremented with each transaction
+>8 ulelong x \b, counter %u
+# isInit; the "isInit" flag; 1 when the shm file has been initialized
+>12 ubyte !1 \b, not initialized %u
+# bigEndCksum; true if the WAL file uses big-ending checksums; 0 if the WAL uses little-endian checksums
+>13 ubyte !0 \b, checksum type %u
+# szPage; database page size in bytes, or 1 if the page size is 65536
+>14 uleshort !1 \b, page size %u
+>14 uleshort =1 \b, page size 65536
+# mxFrame; number of valid and committed frames in the WAL file
+>16 ulelong x \b, %u frames
+# nPage; size of the database file in pages
+>20 ulelong x \b, %u pages
+# aFrameCksum; checksum of the last frame in the WAL file
+>24 ulelong x \b, frame checksum %#x
+# aSalt; two salt value copied from the WAL file header in the byte-order of the WAL file; might be different from machine byte-order
+>32 ulequad x \b, salt %#llx
+# aCksum; checksum over bytes 0 through 39 of this header
+>40 ulelong x \b, header checksum %#x
+# a copy of bytes 0 through 47 of header
+>48 ulelong !3007000 \b, iversion %u
+# nBackfill; number of WAL frames that have already been backfilled into the database by prior checkpoints
+>96 ulelong !0 \b, %u backfilled
+# nBackfillAttempted; number of WAL frames that have attempted to be backfilled
+>>128 ulelong x (%u attempts)
+# read-mark[0..4]; five "read marks"; each read mark is a 32-bit unsigned integer
+>100 ulelong !0 \b, read-mark[0] %#x
+>104 ulelong x \b, read-mark[1] %#x
+>108 ulelong !0xffffffff \b, read-mark[2] %#x
+>112 ulelong !0xffffffff \b, read-mark[3] %#x
+>116 ulelong !0xffffffff \b, read-mark[4] %#x
+# unused space set aside for 8 file locks
+>120 ulequad !0 \b, space %#llx
+# unused space reserved for further expansion
+>132 ulelong !0 \b, reserved %#x
# SQLite Rollback Journal
# https://www.sqlite.org/fileformat.html#rollbackjournal
-------------- next part --------------
A non-text attachment was scrubbed...
Name: file-5.44-sql-shm.diff.sig
Type: application/octet-stream
Size: 1636 bytes
Desc: not available
URL: <https://mailman.astron.com/pipermail/file/attachments/20230106/e2e17952/attachment.obj>
More information about the File
mailing list