I have many repositories and trying to create some tools by parse some file in each repo using direct SQLite3 Query. Using fossil command, for example I can run like this:

fossil cat js/app.js -r trunk -R my-repo.fossil | grep version: and it return string for example: version: 1.2.3

Is there an alternatif Query SQL to get such information, or Query to get the content of some file from the latest trunk ?

I've tried this:

SELECT content(blob.uuid) FROM blob WHERE blob.rid = "RID";

However, it turned out that the line endings were modified, see this post for details:

[fossil-users] Direct SQL dumps have modified line endings

The short answer is "no", but...

Some years ago i asked Richard how to get a list of all files for a specific version of the repo, and he explained that doing so would require at least 1 db record for each file for each checkin. For moderately-sized repositories that would mean millions and millions of db records. For example, in the core fossil repo there are 12367 checkin and currently 874 files = 10.8 million entries. For larger repositories that number could easily grow into the hundreds of millions.

One thing to keep in mind about fossil's data is that ALL of the SCM-related content is actually only in the blob table. ALL other SCM-related data in the database is derived from the raw data stored in the blob table. The whole database, except for the blob table, is a cached/normalized "rephrasing" of the blob table.

However, there may be a way to get what you're looking for...

fossil ls

can list the names of all files in a given checkin, but it can't list their versions (because of the limitation mentioned above):

fossil ls -R ~/fossil/fossil.fsl -r trunk | head

However, fossil can output the raw content of a checkin's "manifest":

fossil artifact trunk | head

So you could do something like:

[stephan@lapdog:~/fossil/fossil]$ f artifact trunk | grep src/json.c | cut -d' ' -f3
356dc00fca5481569a58c65a23f85b880edc11e363526226de62da844afe74e9
f53fa14ab92730d30be35311b52a17848eeabe44

Ooops, we need to be careful to escape the period:

[stephan@lapdog:~/fossil/fossil]$ f artifact trunk | grep 'src/json\.c' | cut -d' ' -f3
356dc00fca5481569a58c65a23f85b880edc11e363526226de62da844afe74e9

or use grep's -w flag:

[stephan@lapdog:~/fossil/fossil]$ f artifact trunk | grep -w src/json.c | cut -d' ' -f3
356dc00fca5481569a58c65a23f85b880edc11e363526226de62da844afe74e9

Once you know the version of a file, there are multiple ways to get its raw contents. Using the file json.c, version 356dc00fca5, as an example:

fossil artifact 356dc00fca5

Or, using SQL:

echo "select content('356dc00fca5')" | fossil sqlite  | head

Hmmmm... that last one doesn't work like i thought it did: it outputs the hex-encoded form of the content.

Note that simply outputing the content of the blob.uuid record 356dc00fca5 would probably not work because that record might (might) be compressed and/or delta-encoded:

sqlite> select uuid, size, length(content) from blob where uuid like '356dc00fca5%';
'356dc00fca5481569a58c65a23f85b880edc11e363526226de62da844afe74e9',73408,50

But note that the length() SQL function doesn't work for binary data, so its output value is not reliable in this context. It does, however, demonstrate that the value of the size field (which records the "real" size of the content) differs from the length() of the blob's content field (the file content, compressed and (possibly) delta-encoded).

The line endings are only changed if you use the "fossil sql" command to extract the content manually on a Windows machine. I think you can overcome that by using the command ".binary on" first, before extracting the content. You can also use the something like this:

SELECT writefile('out.txt',content(uuid)) FROM blob WHERE rid=:rid

Some years ago i asked Richard how to get a list of all files for a specific version of the repo, and he explained that doing so would require at least 1 db record for each file for each checkin.

Yeah, but afterwards I added a new virtual table "files_of_checkin" to do this for you without adding all those additional records to the database.

See the header comment for a terse description of how to use this virtual table. The files_of_checkin virtual table is actually used by Fossil itself in the "search" logic.

Yeah, but afterwards I added a new virtual table "files_of_checkin" to do this for you without adding all those additional records to the database.

To my shame, i had forgotten about that

.

Thank you for the information, I'll try your suggestions.

My script is not pure SQL, so using fossil artifact with the RIDs extracted by SQL queries was an alternative.