SQLite Forum

Usage of application_id and magic.txt

Usage of application_id and magic.txt

(1) By openthc on 2021-11-11 00:44:58 [link] [source]

Greetings starfighters! My organization uses SQLite a lot and only today we learned about the magic values at header-offset 68. And then I looked at the magic file here -- https://www.sqlite.org/src/artifact?ci=trunk&filename=magic.txt

The documentation recommends that we register in that file; and I know like thousands of folks who also use it as an "application file format" (like we intend to do as well) -- but that magic file has...less entries than I expected and has not been updated since 2014. Not good or bad, just a surprise for me.

This is still recommended path forward? If we're intending to do things the "Right Way" (TM) then we should request/register some value in there? (Like how Fossil has stuff with "0x0f055112" (neat trick ;) ) )


(2) By Larry Brasfield (larrybr) on 2021-11-11 01:54:51 in reply to 1 [link] [source]

[On magic numbers listed in <SQLite-source-root>/src/magic.txt ...]

This is still recommended path forward?

It is recommended if you want "utilities such as file(1)" to report something other than "SQLite3 Database" when given your particular DB files. It is up to you to determine whether to go to this trouble. To assess that, you need to consider how the "magic" text will be made to appear in the /usr/share/misc/magic configuration file used by the "file" utility on systems where your particular DB files are likely to appear and have provenance unknown to those who actually need to discover it. Given those predicates, an unqualified "recommended" seems a bit much in most cases.

If we're intending to do things the "Right Way" (TM) ...

(Chuckle) In my opinion, you or others in your organization are far better poised to decide what "the Right Way" is.

(3) By anonymous on 2021-11-11 02:45:51 in reply to 1 [link] [source]

I also wanted to add thing into there but don't know how to mention, so I try mention on this forum and on the mailing list but it doesn't help. Here is my request again:

The application ID number of a TeXnicard card database is 1778603844. It contains tables named "CARDS", "SYSTEM_VARS", and "USER_VARS" (and possibly additional tables, views, triggers, and/or indexes).

There are others who had also made similar requests and nothing seems to have been done about them (no official replies either, neither accepting nor rejecting them, as far as I could tell; maybe I am wrong).

(4) By anonymous on 2021-11-12 09:21:10 in reply to 3 [link] [source]

Sorry, but i do not unterstand what the request is

(5) By anonymous on 2021-11-12 20:22:04 in reply to 4 [link] [source]

Then I will repeat it, in the format of the magic.txt file, in case that makes it understandable:

>68  belong  =0x6a035744  TeXnicard card database -

(6) By Simon Slavin (slavin) on 2021-11-13 01:38:07 in reply to 5 [link] [source]

How is this the responsibility of the SQLite team ? I see something that the TeXnicard team might want registered in the magic database. Surely it's their responsibility to do so.

Tens of thousands of applications use SQLite databases to keep their data. I don't expect the SQLite team to register them all, keep track of the registrations, cancel a registration if the company switches to another format, etc..

(7) By Bill Wade (billwade) on 2021-11-13 15:16:53 in reply to 6 [link] [source]

The SQLite documentation reads:

"Applications that use SQLite as their application file-format should set the Application ID integer to a unique integer so that utilities such as file(1) can determine the specific file type rather than just reporting "SQLite3 Database". A list of assigned application IDs can be seen by consulting the magic.txt file in the SQLite source repository."

The documentation doesn't explicitly say how to 1) come up with a unique integer, and 2) insure that it remains unique. However it implies that some such mechanism might exist.

A quick reading suggests:

  1. Pick an integer not in magic.txt.
  2. Get it added to magic.txt

Since magic.txt is in the sqlite repository, that would imply contacting someone who has permission to update the sqlite repository. That is what the OP attempted.

The sparsity, and age of the entries in magic.txt is a strong suggestion that some other mechanism should be used. The clues file(1) and "magic" suggest searching through unix (or unix-like) documentation, and that might tell you how to determine if an integer you picked is currently registered, and how to register it for yourself.

(8) By anonymous on 2021-11-13 18:42:17 in reply to 6 [link] [source]

How is this the responsibility of the SQLite team ? I see something that the TeXnicard team might want registered in the magic database. Surely it's their responsibility to do so.

I am the TeXnicard team.

(9) By Joseph R. Justice (jayarejay/GMail) (JayAreJay) on 2021-11-13 20:56:02 in reply to 1 [link] [source]

This is a response having read all the messages on this thread posted through "Nov 13, 2021, 2:09 PM".

I believe there is a misunderstanding between "old hands" in the use of SQLite, Unix and Unix/Unix-like culture, etc vs "newcomers" to these things.

Specifically, I can very well see that, as a newcomer, I could read the information provided by the file magic.txt at the link provided and think "OK, well, this file is telling me I probably should do X, and these people at sqlite.org are the ones responsible for this file and its contents, so obviously these people are the ones I should ask about how to do X".  I'd say that messages #1, 3, 5, and 8 are representative of this point of view.

For the record, I do not think this is a particularly unreasonable thing for people in this class of people to think.  (It may not be a *correct* thing for them to think, mind you, but it is not an unreasonable thing at first glance!)

If it matters, I've been (at least casually) following SQLite, Unix, Unix/Unix-like culture, etc for at *least* two weeks now, so hopefully I can fairly describe myself as not exactly a newcomer to these things.

On the other hand, I can very well see that, as a "old hand" of these very same things, it is probably Blatantly Obvious to me that Of Course the people at sqlite.org have *nothing* to do with file(1) and its configuration, how to get magic numbers registered, etc etc etc, and why would anyone think otherwise when they should instead Just Know (or at least Just Know How To Find Out) about file(1), how to get new magic numbers registered, etc etc etc, so why are they asking me/us about this stuff in the first place?  I'd say that messages #2, 6, and perhaps to some extent 7 are representative of this point of view.


In the interest of, hopefully, reconciling this misunderstanding, and perhaps even preventing it from reoccurring in the future, I'd like to suggest the following:

(1) I did a search engine search (I happened to use Bing, I suspect others would work about as well) for the following string herein enclosed by double-quotes (the actual search did not include the double-quotes):

     "file(1) magic.txt"

(2) Of the links provided by my chosen search engine for that phrase, the one at https://www.man7.org/linux/man-pages/man1/file.1.html seemed fairly immediately applicable to the question at hand.

(3) On that page, in the Colophon section, the following text seemed applicable:

     This page is part of the file (a file type guesser) project.
     Information about the project can be found at

Also, earlier on that page, in the Magic Directory and History sections, the following text seemed of interest:

     The magic file entries have been collected from various sources,
     mainly USENET, and contributed by various authors.  Christos Zoulas
     (address below) will collect additional or corrected magic file
     entries.  A consolidation of magic file entries will be distributed


     Primary development and maintenance from 1990 to the present by
     Christos Zoulas ⟨christos@astron.com⟩.

(4) At the darwinsys.com page, you will find a link to the "file" mailing list, at https://mailman.astron.com/mailman/listinfo/file .  Following that link and perusing through it shows that the file(1) program and its resources for magic numbers is under current active maintenance.

You will also find a link to a read-only copy of the source code, at https://github.com/file/file .  On that page, the contents of the file README.md appears to have some applicable information for the purposes of the original poster to this thread.

Hopefully items (1) through (4) above adequately well answer the question originally posed, or at least provide enough information that the original poster can move forward on their own to achieve their desired goal.

(5) For the purpose of avoiding this question going forward, I would suggest that the contents of the magic.txt file as provided by the source code of SQLite be augmented by the following information (or similar information intended to achieve the same effect), as a comment forming a new paragraph(s) following the paragraph beginning with "INTEGER can be any signed 32-bit integer.":

     The following is provided as an example of what magic(5) text for 
     the Unix file(1) utility looks like.  The developers of SQLite, 
     and the community of developers and users of SQLite found at 
     sqlite.org, have no influence on or ability to register your 
     chosen magic number information.  

     For more information on how to accomplish this, look at the 
     information at the following links (which were all known good as 
     of November 2021).  The community of developers and users of 
     SQLite do not really have more information on how to register 
     magic numbers for your application beyond what is available at 
     these links:


For the record, I freely grant permission for the above text, as-is or modified as seems appropriate, to be incorporated into the contents of the SQLite magic.txt file by the developers of SQLite under the standard license terms for SQLite and without further attribution or credit to me (or, if the developers see fit, with whatever attribution or credit they provide for other patches provided by people external to the core development team for SQLite).


Somewhat unrelated to the above, but related to it, apparently there are *other* file type guessing programs out there besides file(1).  (I learned this in following the links provided above and related web-surfing.)

It is possible this might be of some interest to the original poster in this thread, and/or to the development team for SQLite.

In particular, I found reference to the following (I haven't especially perused these links much beyond noting their existence):

https://www.nationalarchives.gov.uk/information-management/manage-information/preserving-digital-records/droid/ and 


It looks like there might be others out there as well.

Hope this is of some use, interest.  Thanks for your time.

Be well.

Joseph R. Justice

(10.1) By Ryan Smith (cuz) on 2021-11-14 09:49:27 edited from 10.0 in reply to 6 [source]

How is this the responsibility of the SQLite team?

This lies exactly with the SQLite team. Not sure if it can be classed a "responsibility" since there are no promises of which no demand can be made, but to get your software registered in the magix.txt file, is most certainly something needing to be OK'd and then acted upon by the SQLite team.

To the OP: The list of software in that file, which could be deemed "registered SQLite file formats" have been added over the years upon just such requests as yours, so there is no problem asking. However, the habit (having seen this request some few times before) seems to be to accept mainly mainstream/public types of software, and only after some public prevalence.

To use Richard's own words: "Let's wait until we encounter some of your software in the wild, then reconsider." (This may not be the verbatim quote since I don't have it handy, but I believe it to be the exact sentiment).

I hope that clears things up a bit for all.

(11) By openthc on 2021-11-15 16:56:26 in reply to 9 [link] [source]

@jayarejay is truly a friend to all. From this thread and the details provided by @jayarejay I found my way to this code:


And then around line 127 is some very familiar looking chunks :)

So, it appears the "correct" answer is that folk (ie: me) who want their application file formats to be recognized should send a patch for libmagic and file(1) -- and that the magic.txt file that is part of the SQLite codebase is really just a hint/pointer/clue of what to put in libmagic

Perhaps the SQLite codbase magic.txt file could be updated to point FNG like me to go elsewhere for submitting the patch (I assumed SQLite maintained their list and worked with up-stream to get into file(1) codebase)

Also, I think this sentence has the numbers backwards?

""The user_version is very similar to application_id except that it is stored at offset 68 instead of offset 60""

user_version is at 60 and application_id is at 68