Fossil Forum

Using fossil as sqlite database for notes & knowledge management
Login

Using fossil as sqlite database for notes & knowledge management

Using fossil as sqlite database for notes & knowledge management

(1) By Amurg (amurgcodru) on 2023-02-13 08:54:16 [link] [source]

Hi

I'd really appreciate some input regarding the usage of fossil as a database. And you guys here are experts.

In 2021 I began working on a knowledge management system/ note taking app using SQLITE. (I wanted to apply the zettelkasten method) FTS has been extremely helpful because most note taking apps are bad at searching text. Each User would have a repo and his/her own sqlite database.

However, in the past few days I was thinking of migrating to fossil because I'd get a whole load of options including a better delta/diff than the one I implemented.

The idea would be to work on the fossil DB as a sqlite database which would be accessible via a custom web itnerface UI(not the fossil UI). At the same time allowing (with proper configuration and settings) the user to make a local copy of the fossil repo so (s)he can edit files locally and sync them up.

After the user's done editing all files (which I would then need to save in an intermediary table) the user would click a button "commit" to then sync to fossil. This in itself would bring a problem with handling merges and diffs via my interface IF the user would be to locally make changes and push.

Some questions

  1. Can I add tables to the database and query them in the app as if it were a normal sqlite?
  2. Will this interfere with fossil's normal way of working? What I don't want is when I do a rebuild/repack to lose those tables or essential data.
  3. How could I get Fossil to automatically sync those tables aswell?
  4. How would I bbest add files? Is there a way (CLI API?) to checkin only specific files without having a checkout folder?
  5. How could I best extract specific files from fossil? Could I use SQL? Fossil CLI? Or would it be better to do a rest API to the fossil repo? (Maybe this is overkill)
  6. Which foreign key ID/table would I need to reference for the files? Naturally the names could change
  7. Any security considerations appart from directory issues ? BTW does fossil store folder names even if they're super weird or have wrong characters (UTF-8 mania)?
  8. Regarding the fossil web UI, is there an API-like feature (returning for example JSON instead of of HTML)? I'm guessing using the fossil CLI would be the only sane way to do some things.

I'm still thinking of the architectural requirements and needs, the idea is that the notes app stores notes as asciidoctor and then converts them to html. Plus it has a preview feature when hovering above a link to another note and other useful features.

In practice and theory fossil would prove to be valuable especially in the aspect of timeline, history retention and in future proof archiving. I've tried to use enough notes software and even those who advocated plain text files missed things

Thank you for your time

(2) By MBL (RoboManni) on 2023-02-13 16:04:14 in reply to 1 [link] [source]

Can I add tables to the database and query them in the app as if it were a normal sqlite?

To what I know of fossil the answer is yes. Fossil uses three SQLite3 databases: The global configurations file, the repository file and the local checkout file. You are mainly talking about the repository file, I guess.

Will this interfere with fossil's normal way of working? What I don't want is when I do a rebuild/repack to lose those tables or essential data.

Depends on transaction interference or independence. If your table names are prefixed with FT_ then they will survive the rebuild process; as far as I remember.

How could I get Fossil to automatically sync those tables as well?

I am not sure, I guess they will not sync but are local and private. The reasons why I think they are not sync'ed is the unknown structure and if there are primary key fields which could be used.

How would I best add files? Is there a way (CLI API?) to checkin only specific files without having a checkout folder?

Yes, you can add/delete etc unversioned files (uv) - and they can also be synchronized (but need special permission as far as I remember; maybe the y for write unversioned).

How could I best extract specific files from fossil? Could I use SQL? Fossil CLI? Or would it be better to do a rest API to the fossil repo? (Maybe this is overkill)

If you add them as unversioned files (uv add FILE) you can get them out that way also (uv export FILE OUTPUT). Checked-in files are in artifact format (markle-tree block-chained) and you need the fossil tools to get them out. Check-outs will take out all files which where checked in. But there are ways to get single files out of check-in with many of them but this is a little tricky and not the standard usage.

does fossil store folder names even if they're super weird or have wrong characters (UTF-8 mania)?

fossil does not know very much about folders but takes the given names as a whole string - I recommend to stay with valid UTF-8 encoding; ASCII is a subset of it.

These are my $0.02 . I am a fossil user and not an expert in all the points; others may correct me or give additional information to your questions.

(3) By Stephan Beal (stephan) on 2023-02-13 16:41:51 in reply to 2 [link] [source]

Can I add tables to the database and query them in the app as if it were a normal sqlite?

To what I know of fossil the answer is yes.

Yes BUT... A rebuild of the db will delete them unless the table names start with fx_. It reserves all other table names for its own use.

(7) By Amurg (amurgcodru) on 2023-02-14 09:43:06 in reply to 2 [link] [source]

  • UV would be extremely usefull for static files such as js/css/images.

  • But i'd still want to store the notes themselves in a versioned format:)

  • So I'd probably need to store the data in a separate sqlite DB, have a fossil repo AND a fossil checkout to make things work.

Probably UV the sqlite DB..

Thanks!

(4) By Warren Young (wyoung) on 2023-02-13 17:51:30 in reply to 1 [link] [source]

There's some extensive speculation on this topic in a long thread I started here with a long, detailed post, years back now.

Between that and the existing responses you've gotten, there's only a bit of nibbling around the edges to do…

How could I get Fossil to automatically sync those tables aswell?

If you want the existing sync mechanisms to work on the new data, it would have to be in the blob hash tree.

The other thread refers to this as a "block chain" because it dates to the time when we had a fling with that trendy term here, but I've since argued extensively that Fossil is not a blockchain.

I bring this up not purely to clear up some obsolete terminology but to suggest that the discussion of why Fossil isn't a blockchain might lead you to further insights. Maybe Fossil should be a proper blockchain or a digital ledger or an AP-mode partial consensus system or whatever, and that gets you to what you need to make this work. I dunno; you tell us. :)

does fossil store folder names even if they're super weird

I'm not aware of any lack in Fossil's Unicode handling.

The closest thing I'm aware of is the perennial CR+LF vs LF thing, where Fossil had to pick one side, annoying partisans of the other. The easiest way I'm aware of to see that is to make some changes to a CR+LF file, then:

  $ fossil diff -i > foo.patch
  $ fossil stash save -m saved
  $ patch -p0 < foo.patch

Bewm!

And if you aren't having all kinds of fun by this point, say:

  $ fossil stash apply

Beheeeeewwwwwwmmmm!

To return this to the context of your question, UTF-8 file names in Fossil cards should be decoded as UTF-16 before being passed to the file APIs in native Windows builds of Fossil.

I'm not aware that we make any attempt to enforce the extensive list of illegal characters and names in Windows file names, but I think that's a good thing. I have such a piece of software running in the background right now, and I'm regularly annoyed when it enforces a Windows limitation on macOS for a database that is never ever ever going to be ported to Windows, so why should I be limited by the other platform?

is there an API-like feature…returning for example JSON…

Yes.

the notes app stores notes as asciidoctor

If you bring that rendering engine to Fossil and then let it be used in forum posts, embedded docs, wiki docs, ticket posts, etc., great. If you are unwilling to do that, then I'd suggest giving up on that element of your current requirements and switching to Fossil Markdown.

Another old and dead dream I had is to do something like this such that we could put book-length content into Fossil proper, all broken up into easily-maintained chapters so you don't get the entire book on a single page. If nothing else, having that in Fossil would be nice, if only for user manuals and such.

(6) By Amurg (amurgcodru) on 2023-02-14 09:36:00 in reply to 4 [source]

HI

Thanks for the information!

Yeah, storing the data in the blob hash tree would complicate matters extremely.

I was talking about my own project which already generates asciidoctor. I also use asciidoctor in fossil separately, saved as .adoc and generate it locally as HTML.

Generating books out of asciidoctor is a breeze, you can separate chapters/pages which you can include:: - Even with automatic TOC generation, and many nifty things. Pushing to epub/pdf/html.

I've seen posts on this forum about extending markdown to add footnotes, TOC etc.. but it seems way to complicated for the fact that asciidoctor already has these built in.

I'll look into the JSON api and see if I can make something happen for the wiki/forum bits. At the moment I suppose I can't store pure HTML which I'd have rendered via javascript because it would allow XSS unless it's restricted or sanitized somehow.

BTW. Regarding this, is there a way to make changes in the fossil repo itself for example to allow specific changes (ex template/theme, custom css/javascript) to be shared between repo fossils without rebuilding the fossil executable?

(9) By Warren Young (wyoung) on 2023-02-14 14:14:48 in reply to 6 [link] [source]

template/theme, custom css/javascript) to be shared between repo fossils

Custom skinning coupled with the configuration command:

  $ fossil conf -R repoA.fossil export skin skin.conf
  $ fossil conf -R repoB.fossil import      skin.conf

Note that you do not specify the "area" in the import command: the contents of the file tell Fossil what you exported from repoA.fossil.

(5) By Offray (offray) on 2023-02-13 19:15:54 in reply to 1 [link] [source]

Hi,

Despite of not being directly related with your questions, I have been using Fossil as the storage back end for two projects:

  1. a decoupled content management system (CMS) for publishing web sites.
  2. a personal management system (PKMS) powered by Fossil + TiddlyWiki + Pharo.

We don't use Asciidoctor for those system, but different light markup systems depending on the context:

  • For long(er) documents edited in the file system, we use Markdown, converted to HTML via Pandoc or Markdeep, which can be rendered directly in a graphical web browser with JavaScript enabled (i.e. almost all browsers).
  • For shorter documents and modular documents, edited in the web browser, we use WikiText (TiddlyWiki's native format). TiddlyWiki (TW) not only provides the web editing interface, but also several templates for PKMS and project management utilities. As TW stores all the wiki in a single HTML file, we use TiddlyWikiPharo to serialize all documents in STON, a JavaScript inspired data serialization format for Pharo.

You can see and example of our TW + Fossil + Pharo usage in our tabletop roleplaying gaming wiki which is stored and served in Fossil as an unversioned TiddlyWiki file (TW is used also as an editing interface) and the TW units of information (called tiddlers) is versioned as individual files in Fossil.

This combination has proven to be the best for our knowledge management needs as it is pretty agile, low on resources and let us to take advantage of each of the individual pieces' strengths (Fossil for versioning and publishing, TW for a single page application with web UI for knowledge management, Pharo for live coding and programming).

Hope this help in this architectural phase to survey the similar/different approaches to use Fossil as a backend for PKMS needs.

(8) By Amurg (amurgcodru) on 2023-02-14 09:59:41 in reply to 5 [link] [source]

Something to research. Thanks for the links

So to be clear and make sure I understand properly: on your website, you can edit wiki's in tiddliwiki (directly from the browser) and they're stored in fossil via the JSON API?

Indeed this would be interesting alternative with different twist to the current system I've built based on sqlite

(11) By Offray (offray) on 2023-02-16 16:44:30 in reply to 8 [link] [source]

TiddlyWiki (TW) Single Page Application (SPA) provides inside its HTML file:

  • the web UI for editing the wiki
  • the information storage as JSON
  • the capabilities to extend/customize the wiki functionality, via macros, plugins, scripts and themes.

Given such base this is my workflow:

  • I edit the HTML file locally in the browser in via TW web UI
  • I save such file as an unversioned file in Fossil.
  • I read the JSON storage inside TW's HTML file, serialize it as STON (which is friendlier and more flexible that JSON) and I save TW's units of information (called tiddlers) as individual versioned files in Fossil.

So I have the SPA capabilites of TiddlyWiki, the publication capabilities of Fossil via unversioned files and the historical archives for tracking files via versioned files. Both infrastructures (TW and Fossil) are connected joyfully ;-) via Pharo live coding capabilities.

(10) By anonymous on 2023-02-15 19:12:51 in reply to 1 [link] [source]

The idea would be to work on the fossil DB as a sqlite database which would be accessible via a custom web itnerface UI(not the fossil UI). At the same time allowing (with proper configuration and settings) the user to make a local copy of the fossil repo so (s)he can edit files locally and sync them up.

Not exactly the same as you're describing, but for more than a year my main notetaking app was a customized Fossil repo, with all notes and metadata stored inside the tickets database.

I did everything through CGI scripts. The one catch was that I had to edit the sudoers file on my Ubuntu server so it could call Fossil (I don't honestly remember the details). What were the advantages? I had a full SQLite database with version control, and it was all in the cloud, so I could use it from my Chromebook. Importantly, authentication was handled by Fossil. All I had to check was environment variable FOSSIL_USER inside the CGI script. It's an understatement to say it was the most powerful notetaking app I've ever used.

The only reason I stopped using it was because my Chromebook died, at which time I no longer needed a cloud app. I thought about using the wiki with the JSON API. I probably didn't go that route because the tickets database offers more features.