Fossil Forum

We (the Fossil/SQLite devs) had that same problem a few weeks ago when we were trying to move bug reports off of https://sqlite.org/forum and over to https://sqlite.org/bugs. Stephan suggested that we needed a new command to just do this, but no such command is yet available. For now, you just have to copy artifacts.

The steps:

1. Figure out the SHA3 hash (or a unique prefix thereof) for every artifact that you want to move from one repo to anther.

2. Use the "fossil artifact HASH" command to extract the artifacts that you want to move. Store each one in a separate file.

3. Use the (undocumented and unsupported) "fossil test-content-put" to add the artifacts to the second repository.

4. Run "fossil rebuild" on the second repository.

This is brain-surgery on your repository, so you will do well to turn off auto-sync and make a backup before attempting.

How do I get the list of artifacts that i "want to move"? Is that list every row in the TICKET table?

Let's take ticket 14bc0ad52d919860 as an example. If you click on that ticket, you should see a timeline of changes at the top. Each entry in the timeline shows an "artifact:" followed by the SHA3 hash prefix. Those prefixes are what you want. You will need them all.

You can also click on the "History" submenu tab, to see another view that shows you all the artifacts that go into that one ticket.

How do I get the list of artifacts that i "want to move"?

That's the difficult bit :/. You have to find the ticket itself, which will show up in the timeline, then every edit to that ticket. One catch here is that the ticket's ID is not the UI you need to export - it's a random value generated when the ticket is created. You need "artifact" IDs (hashes) of each piece.

It's probably best if you hold off until we can get such a command in place. We need one for the forum, in any case, so it's likely to be written sooner rather than later. We don't get enough use out of the ticket system to really motivate such a tool for tickets, but maybe the motivation will come once we've got one for forum posts.

For tickets, its quite easy to get all the hashes:

select b.uuid
from ticketchng t
left join blob b
on b.rid=t.tkt_rid
;

Quite a bit slower, but works for all manifest types, eg 'forumpost':

select uuid
from blob
where artifact_to_json(rid)->>'type' = 'forumpost';

Possible types are 'checkin', 'wiki', 'ticket', 'event', 'forumpost', 'tag', 'attachment', 'cluster' - and NULL if not parseable as manifest.

Here is the script I wrote to copy all the tickets from one repo to another (thanks MG):

#!/bin/bash
set -ex
REPOS="/cygdrive/c/Users/dougf/fossil_repos"
FOSSIL="cyg_fossil"
cd $REPOS
pwd
ls -al
mkdir -p website_tickets
sqlite3 website.fossil <<EOF
.header off
.once website_tickets/artifact_extract.sh
select '$FOSSIL artifact ' || substr(b.uuid,1,8) || ' $REPOS/website_tickets/' || substr(b.uuid,1,8) || '.txt' from ticketchng t left join blob b on b.rid=t.tkt_rid;
.once website_tickets/artifact_insert.sh
select '$FOSSIL test-content-put $REPOS/website_tickets/' || substr(b.uuid,1,8) || '.txt' from ticketchng t left join blob b on b.rid=t.tkt_rid;
.q
EOF
chmod +x $REPOS/website_tickets/artifact_extract.sh
chmod +x $REPOS/website_tickets/artifact_insert.sh
cat $REPOS/website_tickets/artifact_extract.sh
cat $REPOS/website_tickets/artifact_insert.sh

# get to a 'from' fossil checkout
cd /cygdrive/c/Users/dougf/website/trunk/advanced
$FOSSIL status
/bin/bash -x $REPOS/website_tickets/artifact_extract.sh
ls -al $REPOS/website_tickets/*.txt

# get to "to" fossil checkout
cd /cygdrive/c/Users/dougf/documents/ot/trunk
$FOSSIL status
/bin/bash -x $REPOS/website_tickets/artifact_insert.sh

$FOSSIL rebuild
$FOSSIL ui &

Success!!! All my tickets are now in the new repo! Thanks for the strategy, Richard :)

select uuid
from blob
where artifact_to_json(rid)->>'type' = 'forumpost';

Here's an equivalent which will run considerably faster:

SELECT b.uuid
FROM blob b, event e, forumpost f
WHERE b.rid=f.fpid AND e.objid=b.rid;

Technically the forumpost table isn't strictly needed: we can check for e.type='f'. However, that would include both forum posts and attachments to forum posts, so we'd have to add a condition like your ->>'type'='forumpost' to filter out the attachments. By checking the forumpost table we automatically filter the attachments.

To export them all as JSON, simply change that first line to:

SELECT json_pretty(json_group_array(artifact_to_json(b.rid)))

With the caveat that that format cannot simply be re-imported into another repo. We "could" add that capability without terribly much effort, but whether that's the form such an export/import should take is TBD.

Yes, waaaaaaay faster. .0008 s vs about 480 s

The event table is not even used for anything in your example, right? At least, the shorter

SELECT b.uuid
FROM blob b, forumpost f
WHERE b.rid=f.fpid;

gives the same result in my case. And is even a bit faster (0.0002 s), but that might be just to small to be measureable by running it once.

Anyhow, what about the following (general) idea?

Lets assume there was a 'filter by artifact-type' for the deconstrunct command. Then, you could * deconstruct only type(s) X to a directory * reconstruct to an intermediate repository only containing X * pull from that into your target repository (forced with --project-code CODE)

All other pieces for that are already in place, just the 'filter' on deconstruct is missing.

mkdir -p /some/temporary/dir
fossil deconstruct -R origin-repo.fossil --only-type forumpost /some/temporary/dir
fossil reconstruct intermediate-repo.fossil /some/temporary/dir
# note project-id from reconstruct command
fossil pull -R target-repo.fossil intermediate-repo.fossil --project-code <intermediate-repo-project-id>
rm -rf /some/temporary/dir
rm intermediate-repo.fossil

The event table is not even used for anything in your example, right?

Doh, correct. It was a remnant of experimentation.

Lets assume there was a 'filter by artifact-type' for the deconstrunct command

That's a really interesting idea.

I tried it with the following ad-hoc implementation of a forumpost w/o attachments filtered deconstruct (equivalent to deconstruct --prefixlength 0 ...):

fossil sql -R origin-repo.fossil "
with sel(uuid) as (
  select b.uuid
  from blob b, forumpost f
  where b.rid=f.fpid
)
select writefile(printf('/some/temporary/dir/%s',uuid),content(uuid))
from sel;
"

and then, as above

fossil reconstruct intermediate-repo.fossil /some/temporary/dir
# note project-id from reconstruct command
fossil pull -R target-repo.fossil intermediate-repo.fossil --project-code <intermediate-repo-project-id>

Works quite well. Only annoyance is, that reconstruct creates two tag-artifacts that * Edit [abc123]: Move branch to trunk * Edit [abc123]: Add propagating tag "trunk"

for * the object abc123 that happend to get rid=1 (obviously a forum post in this case) * by "me" * at "now"

Shunning these two before the pull gets all cleaned up ...

That seems to be glitch in reconstruct, probably should check if the object with rid 1 is a manifest of type 'checkin'.