The Problem

Over on the SQLite mailing list, we've been discussing a recent Wall Street Journal article reporting on guy wearied by battling monstrous Excel problems. (Hacker News comment thread.)

I believe the core problem here is that Excel is often used in place of a programmable database system.

That class of problem persists because Big Business doesn't need personal databases. Once a project grows beyond the bounds of an Excel one-off, it's shipped to the IT department as a prototype for a new business tool, rewritten in the professional development system preferred by IT: .NET, Java, PHP, C++, etc. Consequently, that's where the bulk of tooling investments go for the class of problems that give rise to the monster Excel spreadsheet problem.

Since the deep pockets of Big Business don't fund the development of a tool intermediate between spreadsheets and professional software development systems, muggle-friendly personal database systems like FileMaker and Access remain niche products, despite having antecedents back to the mid 1980s, when we had many competing office suites, which often included a simple database system.

Over time, the developers of those simple database systems added enough features and complexity that they turned into light developer tools: dBase, Paradox, Access, FileMaker...

There's a point in the development of such a tool that a DBMS either turns into a professional software development system or it hits a complexity ceiling: many software developers will shun a tool that's merely close to being a replacement for their current favorite programming system, refusing to use it unless it can solve all of the problems they can solve with that favored system.

Thus, traditional commercial software market prospects are poor both above and below a certain complexity level. Below it, people go with free, already-installed tools like Excel to create ad hoc solutions to their problems. Above it, complexity pushes toward professional tools.

I used FileMaker for several years, but stopped upgrading it because the contract that sparked my initial uses of it dried up. For my own personal uses, I fall into the same paths as above: spreadsheets for quick one-offs, moving quickly to custom software tools once I hit a complexity barrier that I set fairly low, being a software developer.

In the Windows world, Microsoft Access could fill that role, but only if Microsoft included it in all of their low-cost Office offerings. As it stands, I suspect most Office users aren't even aware of the existence of Access, and if you told them of it, most wouldn't be interested in paying extra for it.

A Vision

I believe Fossil can fill this niche.

Imagine a system based on Fossil which lets normal people create and manipulate tabular data via a web UI.

Imagine what you can do as a software developer with such a system. Not everything needs to turn into a project in your favorite programming language.

You get a lot for free by starting with Fossil:

• Single binary that serves the command line, local GUI, and web app roles

• HTML templating system for the GUI/web app roles

• UI skinning system

• Role-based access control system

• TH1, Jim Tcl, or “real” Tcl for scripting the database, a la FileMaker or Access

• JSON API

• Flexible web app service via CGI, SCGI, or direct HTTP

• Markdown and Wiki markup rendering

• Email alerts system

• File browsers for use when running locally. Used for selecting a database to open, data files to insert, etc.

• Test harness

• Powerful build system

• SQLite!

Fork or Feature?

I'm not sure whether it makes more sense for this personal database system to be a feature of Fossil or a fork of it.

I ask myself these questions:

1. Would you want to have a wiki, ticket tracker, or forum alongside your personal database system?

2. How much value is there in having versioned content linked to the database?

3. How much of the potential value is lost by making Fossil more complex, given that the whole point of this idea is to produce something scarcely more complex than Excel? Wouldn't it be better to just fork Fossil, jettisoning things like the block chain in the interest of simplicity?

4. If you fork Fossil, how often do you expect to need to merge features in from upstream?

I can argue it both ways:

Fork

A lot of the design of Fossil doesn’t make much sense for a personal database system. For example, most of its user roles are irrelevant to a personal database system, so there's value in redesigning the role system to be an exact fit for the purpose. Instead of a role to grant users the ability to create checkins, you’d want a role to let users add rows to tables, for just one of many obvious examples.

Feature

It wouldn’t be the first time that Fossil got a feature that some of its users ended up soloing, ignoring the rest of the system. Some people use Fossil as just a wiki, and I suspect some will now begin using Fossil 2.7+ as just a forum.

My vote is "feature," but I’m a big Fossil user, so I’m biased.

Personal Database in Action

If we take the "feature" path above, here's how the system might work in practice:

• Initialize a new database:

    $ fossil init foo.db
  

  Yes, just like with Fossil today, so far.

• Start a local GUI:

    $ fossil ui /path/to/foo.db
  

  Note that we don't bother to "open" the DB in a checkout directory. For use as a personal database only, there's no point in that.

  It is important to the concept that the UI let the user do all common manipulations, not just a subset as with Fossil today: create tables, establish relationships, build input forms, create query reports, switch between record and table views, etc. If we require users to drop to the command line for any key operation, we've failed.

  I believe one consequence of that requirement is that there be a mode where Fossil can start the UI without having any database/repository attached, so that it can create one via the GUI. There should also be a way to switch between DBs from the UI, attach other DBs to the current DB, etc.

• That said, we should allow users to query or modify the database directly via SQL:

    $ fossil sql 'select count(*) from my_new_table'
    4
  

  It reports that there are four rows in your new table.

  This means you can now script access to your new personal database from other systems, from shell scripts upward.

• Copy your DB to a server and stand up an instance for others to use:

    $ fossil server /path/to/foo.db
  

  Fossil is now listening on port 8080 by default. Just as with Fossil today, you also have the ability to set options for more advanced direct HTTP service needs, or to switch to fossil cgi, fossil http, etc.

• Design a form that, when Submitted, adds a row to a table, runs a TH1/Tcl script, and sends an email alert. Now you’ve got a system that lets ordinary people do things that a programmer would expect to need a real programming system to pull off.

If you take the "fork" path instead, I believe operation stays mostly the same as above, just with a different top-level command and less complexity overall.

Extending Fossil

I can imagine several ways to use this new feature from within what we currently think of as a Fossil instance:

1. Rebuild the current ticket UI upon this new base, so that end users of Fossil can extend the ticket system with the new form building GUI tools.

   (The ticket system needs some attention anyway, since we've now got a threaded commenting system with Markdown formatting support, both of which tickets could benefit from.)

2. Build ad hoc developer tools. For example, a CI/CD system could POST results to Fossil's JSON API to create rows in one of these custom tables, which then feeds into some downstream process.

   Open question: do the resulting tables sync between repositories?

3. Build muggle-oriented input UIs to the Fossil-managed development process. The current ticket, wiki, and forum systems are examples of this. Surely there are other tools your non-developer coworkers could use to feed data into the process?

4. Offer relational or blockchain storage. So far, we've considered only the former, but what if the user could create custom card formats and feed data into them, which then sync and otherwise operate the same as existing Fossil block chain artifacts? Or, what if a web form could create existing artifacts with existing Fossil formats?

   One of the persistent wishes is the ability to create and update existing files in the repo. That feature would fall out of this blockchain-backed personal database feature for free.

Anthropological Matters

There are both advantages and risks to pursuing this plan in the FOSS world.

On the plus side, we don't have the market problems that push solutions toward free-but-impoverished tools like Excel or towards full-blown software development systems. FOSS projects tend to be very pragmatic: we don't need to keep employing developers past the point where they lose personal interest in the project. A tool that's good enough just slips quietly into maintenance mode. A tool that's no longer good enough either attracts new development or it dies off naturally.

The major risk is that the FOSS world is best at developing the tools the FOSS world itself needs: software development tools, infrastructure, etc. It is notoriously terrible at developing muggle-friendly software. Every exception I can think of has a big corporate backer, and they're producing that software to further some other end that couldn't be achieved via traditional commercial software development.

That risk is one of the reasons I prefer the "feature" path to the "fork" path: I think a Fossil-based personal database system is more likely to be created, maintained, and thrive as a feature of Fossil than as a separate project.

Another risk comes from the end user side. Will they be put off by the program opening its UI in their browser? Will they be scared off by all of the orthogonal features in Fossil? "Wiki, forum, ticket tracker, user administration...I don't need all that noise!"

That argues for the "fork" option, but I think to make that work, you need to find a corporate backer who needs the world to have personal database systems for some reason tangential to their real line of business.

I find the notion of a database faulty from the regular/office user prospective. It always conveys some unapproachable feeling of "Computer Science". Meanwhile Excel is a hands-on tool that is one step up from Notepad, and that has omipotent cells.

The rigidity of a database has its value for a well-thought-out processes, yet regular user needs are not always well-thought-out, often times they begin ad-hoc, change ad-hoc, with a desire of a fast outcome no matter how dirty is the way to get it. A contrast from pristine and ranged strtuctural nature of a designed database.

Do users need a personal database? I doubt that a regular user would be able to positively state an answer. Do they need features and the benefits that a database system traditionally produces (fast retrieval, organized storage, referencing, reporting). If these features have little friction to use, then users may find some useful application for such features.

Yet, take for example full-search capabilty offered by all of the major OSes. I wonder how often do users indeed resort to search vs. clicking through the folder tree (they created) to get the needed files?

I believe that in order to "popularize" the benefits of databases one needs to find a way integrate the features with day-to-day activities users do on their devices. On PCs a lot of activities revolve around filesystem -- documents, images, well, spreadsheets. What if there was a way to incorporate keying feature into the file system?

In a simplest case a filesystem is keyed on full file-name. For example, there could be a way to define an additional set of identifiers that would apply for objects in a given directory. User could specify those identifier similarly to how it's currently done with name of files/directories. Once such identifiers are known, it's already queriable. There could be a layer over filesystem offering database features.

Filesystem is omnipotent as well, you can use files to store data, relations, processes, formulas. With a wide use of fast storage these days, the access time could be practical, also system caching features could be equally beneficial.

Hope this won't be misconstrued as being aganist the use of databases as such. Just a personal observation from trying to promote database features to very much regular business-oriented and well-educated, alas, not very patient users. Mind it, most of folks understand a structural approach to storing data (or life objects) and most users do devise their personal ways to maintain order. Just the modern system often do not present the users with easy handles for that.

I don't think the Excel novice user to the Excel master user will ever consider needing a personal database to accomplish their objective. For one thing, database design isn't as straight forward as a MS Excel spreadsheet. Secondly, the wrong sql query could give you the wrong answer, if you're not forming the query correctly.

This doesn't mean I don't really enjoy databses and want to store information in them, I just don't see a midlevel worker bee considering anything but Excel, possibly Libre Office.

For me, I would rather have a database than a Excel file, even if it was a couple of 'tabs' in that application.

I have already used fossil as a personal database, for a previous version of my tongue twister catalogue. I stored tongue twister information in tickets. Unlike a plain SQLite database, fossil provided a pretty view, and it kept backups (version history and sync).

These two features could replace the data store in many other projects of mine. At the moment I wind up storing data in text files so I can version them in fossil or storing data in SQLite and versioning it in fossil anyway.

A great example is my contact manager, bguo. Contacts are stored as one file per contact because I like the user interface but also because it makes the contacts easy to version with fossil.

If fossil provided a higher-level command-line and interfaces for editing and viewing data, I could replace fossil-versioned filesystem storage with fossil and greatly simply bguo (and it is already pretty simple).

---

I imagining that queries (or just views) could be versioned somehow. End user commands could be defined as templated database queries with templated output. Well, I think this is easier to make useful for output rather than for input.

Another idea: since this would replace many things where I am using a filesystem, it would be sufficient for only one table to be available and for the table to be weakly typed.

• A versioned mapping arbitrary names to artifacts could get pretty far. In one version of the database, "thomas.levine" maps to artifact abcde..., and in another version, it maps to a2bc1bec.... Does this already exist, maybe?
• A versioned table with an identifier and a json column would also do me very well.

I suppose this is very close to the current wiki, so I will look further at the wiki and try to figure out why I haven't already thought to use it as my database.

Speaking from much personal experience: a port to a library is a rather massive undertaking. i was most of the way there in late 2014 when a severe case of chronic RSI effectively removed me from the programming world (i'm still on medical leave (early retirement) for that, and only get to hack maybe 1-3 days/month nowadays, which is nowhere near enough for a project of that scope). Since that time, Fossil was internally reworked a bit for the SHA3 support, which effectively made libfossil obsolete and unusable until/unless someone were to take it over and bring it up to date (which includes updating it to parse forum-related manifests, but not (necessarily) supporting the forum features themselves).

i would be tickled pink to see someone take over the libfossil code, but so far nobody has done so:

https://fossil.wanderinghorse.net/r/libfossil

i would of course provide technical and moral support to anyone willing to do so, but i cannot commit (as it were) to more than the most trivial of code/doc-related participation.

I like this idea, mostly as a feature instead of a fork and maybe starting by making the tickets system more flexible or extending it, so more custom tables and fields can be added and distributed across clones. So if my information needs not only need to distribute tickets, but other info and present it in a tabular way and query it to build custom reports, Fossil can be used for that also.

That could be a pretty organic way to extend Fossil from custom tickets to custom tables to a Personal Data Base System (I would like in the last scenario to be able to "connect" other light scripting languages, beside Tcl, like Lua, but that's another problem).

making the tickets system more flexible or extending it, so more custom tables and fields can be added

You might be able to do what you want today with what's already been provided.

distributed across clones

It's important to realize that if Fossil ever does add a personal database feature that the current sync mechanism only works on blockchain artifacts, not on the relational-structured parts of the repo DB. Those latter parts are treated quite differently in Fossil:

• A lot of it is simply re-generated from the raw artifacts on each clone, then updated on sync, since much of this data is basically a lookup cache. This is part of what it's doing with the post-clone rebuild.

• The rest can be copied during clone, with sufficient privileges, but has to be pulled explicitly later with a fossil conf pull command, and that isn't a sync, it's a blind overwrite.

I point all of this out because if we just add some new SQLite tables to a Fossil repo DB, they don't start syncing automatically. That's one of the reasons I suggested we might want to have a blockchain mode for this feature.

other light scripting languages, beside Tcl, like Lua

It would be a huge mistake to add a second scripting language to Fossil. (I'm lumping TH1, Jim, and "real" Tcl together as family-mates, with a clear upgrade progression from one to the next.)

I'm only aware of some very popular wide-based software packages that allow multiple scripting languages, and even at that level, it creates massive community fragmentation. You find archives of third-party scripts for such systems with caveats everywhere saying things like "this one works only on Windows because it uses ActiveWhatever Scripting Language Du Jour" or "this one requires that you've built the host package with Scheme" or "this one requires this list of third-party modules..."

Yuck.

Tcl isn't my favorite programming language, but it is quite capable.

Fossil's developer community is far too small to survive the fragmentation that the likes of Vim and Photoshop can.

We have enough of a problem already with full Tcl being optional. I hesitate to suggest that we make Tcl a required dependency of Fossil, but maybe if we were to replace TH1 with a batteries-included Jim Tcl configuration...? (That as opposed to the stripped-down version of Jim included with Autosetup.)

You might be able to do what you want today with what's already been provided.

Thanks. I'll check it out.

It's important to realize that if Fossil ever does add a personal database feature that the current sync mechanism only works on blockchain artifacts, not on the relational-structured parts of the repo DB. Those latter parts are treated quite differently in Fossil:

Maybe we would need to put some db schema and data in the repository itself and teach Fossil (via scripting) how to rebuild the tables from there.

It would be a huge mistake to add a second scripting language to Fossil. (I'm lumping TH1, Jim, and "real" Tcl together as family-mates, with a clear upgrade progression from one to the next.)

I'm only aware of some very popular wide-based software packages that allow multiple scripting languages, and even at that level, it creates massive community fragmentation. You find archives of third-party scripts for such systems with caveats everywhere saying things like "this one works only on Windows because it uses ActiveWhatever Scripting Language Du Jour" or "this one requires that you've built the host package with Scheme" or "this one requires this list of third-party modules..."

Yuck.

Tcl isn't my favorite programming language, but it is quite capable.

Fossil's developer community is far too small to survive the fragmentation that the likes of Vim and Photoshop can.

I don't think adding a second scripting language to Fossil will be ever an official project. That could go in the "feature vs fork" discussion also. I think that yes, community is small, but Lua is pretty portable and follows the similar minimalist Fossil's spirit, so I don't think that it will create a lot of fragmentation in the community to have this feature/fork about scripting.

Maybe the Fossil+Lua powered personal database will be more something like Flint, that is build above Fossil capabilities without creating community fracture. Time will tell. Lua is in my radar, but I'm making almost all my programming in Pharo, including a JSON interface with Fossil and maybe some OS calls. Fossil+Lua is still a far away project, and I don't know if it ever will be done.

Ps: Fossil+Lua would be something like a Lunar Crater :-), because they are Fossils in the Moon (Lua, in Portuguese).

I'm quite aware of Lua's virtues. I've written substantial amounts of code in it.

Nevertheless, I think you'd be better off putting the effort into learning Tcl than in adding Lua to Fossil. Tcl will bend your brain in useful ways, even if you never use it again outside of Fossil.

Learning Tcl also opens up a few new avenues to you, like replacing Autotools with Autosetup.

I have done just small scripting in Lua, but the way I have found it time and again is because it is embedded in stuff I use to get my tasks done (like in LuaLaTeX or Pandoc). I have no doubt that Tcl could be an interesting language to learn sometime, and the idea of adding Lua on top of Fossil is just purely speculative right now. Both learning Tcl or adding Lua to Fossil can be fun projects to do. Meanwhile I will focus on (the mind bending) Pharo + Fossil, that is the combination I'm using now.

It would be a huge mistake to add a second scripting language to Fossil. (I'm lumping TH1, Jim, and "real" Tcl together as family-mates, with a clear upgrade progression from one to the next.)

Yes, I agree, although there is another scripting language in Fossil, specifically, SQL.

We have enough of a problem already with full Tcl being optional...

I think it is fine to not have Tcl in there at all, though. There is already TH1 and SQL.

However, there is sometimes other things you might want. I can suggest to add another table into the .fossil file (or to define a separate text file called .fossilrc instead) to list SQLite extensions to load, and to add a command into the CGI setting files to do the same thing too. But even this might be insufficient for some things; you might want extensions which are not only extensions to SQLite, but to other functions of Fossil, too (e.g. to create additional web pages, wiki formatting macros, etc).

[shunning] would be absolutely necessary for a personal database gui.

I don't buy that.

If we take the relational path, then SQL REPLACE and DELETE suffice. Fossil artifact shunning is neither necessary nor helpful in that case.

If this feature is based on the Fossil blockchain instead, then you should need shunning for it about as often as you need it for Fossil artifacts today. We might need to introduce some new control card data to say "this record updates that one", but then again, we might be able to reuse what's already provided for things like fossil amend.

later available to bad guys with access to the repo

So don't put sensitive data into a Fossil repo that bad guys can access.

We only need to pay ordinary attention to hacking and data theft, not special attention requiring Fossil-specific responses. Other remote-accessible DBMSes have the same risk profile, so we can just say, "Use the same security and risk mitigation strategies for this new feature as for those existing DBMSes," and leave it at that.

Personal versioning is great, but only 10 or fewer are necessary in this vein to avoid loss.

Your applications for DBMS tech may require limited versioning, but not all do.

Some applications require strict audibility back to arbitrary points in time, such as aircraft and medical applications.

Some applications have little need for historical version-keeping, but they generate so little data over time that it just isn't worth actively thinning out the historical data.

Thus DVCSes.

The closest I've seen to talk of DVCS thinning is the shallow cloning idea, and those discussions always assume that it is still possible to pull up arbitrarily old versions. Shallow cloning is merely an optimization to get faster and smaller clones for Fossil's everyday use case.

I had to reconstruct a Fossil repo from an archive of old tarballs once. It made for a much smaller repo than if I'd had all of the intermediate checkin data, but it would take a very specific use case for me to recommend that anyone else thin their existing Fossil repos in the same way. I did it only out of necessity. If I'd had access to the source repo that those tarballs were created from, I'd have just converted it to Fossil directly, rather than reconstruct it.

the baby elephant would be rebase.

I neither want nor need a rebasing feature to support this vision.

I would reach for this tool over Excel, but I am a developer.

I fully expect that the initial versions — perhaps even the first 10 years worth of versions! — to appeal mainly to developers who already use Fossil and would rather just stick the data into a Fossil repo than learn yet another personal DB package.

The first usable versions can fully exclude the current Excel-as-database user crowd, and it still wouldn't be a failure. We can grow to the point where we start attracting members of that crowd over many years, if necessary.

Look at something like Salesforce or Wordpress: those started out very geeky and only grew into mass-market products over many years.

remove the git debate entirely

I take that up in another thread.

better binary diffs(I add a byte to a 1GB image)

I take that up in another thread.

adding more databases to the single repo

I take that up in another thread.

Ok, this feature is dead in the water if you lead with, "Do not store sensitive data!". Meaning, use something else for your; passwords, contacts, images, tax data, warp drive design, and so on.

I was assuming blockchain was the lone approach. SQL access is more powerful, but requires an active filter to avoid corrupting the repo.

Without rebase/shunning, I recreate my repo's(large binary content,erroneous file add's,sensitive data removal) and archive the older for occasional review. I get confidence in the stored data and a tighter repo/timeline. This is not frequent, but it is my only surety when someone adds .* in a subfolder or left a bunch of clear text passwords in a document.

I applaud the philosophy of historical accuracy, but rebase and shunning exist for a reason. I believe in "never tell a lie", yet I lie...sometimes. ;)

Ok, this feature is dead in the water if you lead with, "Do not store sensitive data!"

First, that's selective quoting. A plain text file on a passwordless network share can securely hold sensitive data if no one can connect to the network share because it's on an airgapped network inside a locked room, with the door guarded by an armed ex-Marine. You have to define your threat model before you design your security.

Second, not all databases store sensitive information, so the feature is not "dead in the water" simply because it's unsuited for hosting banking passwords on a public web server.

Are the contents of SQLite's source tree secure from modification? Yes? Then Fossil is secure enough for Hwaci's purpose.

There's already a feature in Fossil that prevents cloning by anonymous and/or "nobody" users. That, a strong password, a check with the Fossil security audit tool, and an HTTPS wrapper should keep not-Mossad out of your data.

SQL access is more powerful, but requires an active filter to avoid corrupting the repo.

Why?

First, the whole idea of a personal DB system is that you rarely have to write SQL, if at all. Many personal DBMSes don't even speak SQL. At that point, Fossil's permission system should suffice; we'd probably just reuse the check-in capability.

Second, if you do dig down to the Tcl/SQL layers, you're passing two tests: 1. You have the necessary permissions to do that. (Probably something above check-in capability.) 2. You are self-selecting as someone willing to do this. We don't need special security on this feature any more than the SQLite shell does.

Have you used Access, Paradox, Filemaker, etc. by the way? You might not even understand where I'm coming from without that backing experience.

Without rebase/shunning

If this feature stores data in the blockchain, those artifacts will be shunnable.

If it uses the SQLite relational DB instead, you have DELETE, REPLACE and UPDATE.

it is my only surety when someone adds .* in a subfolder

"Only?" There's a whole industry devoted to backups, snapshots, and similar tech that sits outside of Fossil.

rebase...exists for a reason

Fossil's gotten along just fine without it for 12 years. So have several other [D]VCSes.

What I find compelling about Microsoft Access is the combination of a relational database and the forms to interact with it in a single file. In essence, it allows delivery of a single-file, customizable application. Anybody remember HyperCard? Lots of people found lots of interesting ways make "one-stack" applications.

I understand the IT objections to combining content and presentation into a monolithic package, although that might be somewhat addressed with "skinning" of some sort (e.g., consider having several sets of forms in Access to work with data in whatever way the user prefers).

I would like to have a single file that can serve both as my SQLite database and a web UI for it. Fossil provides that, tailored to its central mission.

I appreciate the practical example. I hope to learn how to extend the Fossil UI to allow people (particularly me) to design custom forms that will interact with custom data. Ideally, autosync would support both.

I would endorse this as a "bolt-on" following the "fork" pattern - in particular, forked in such a way that the underlying Fossil database and web server could still be inherit new Fossil functionality.

P.S. I should say that my immediate use for this is to monitor some automated reports in an SQLite database and then review and discuss them visually. I can do this with Microsoft Access/MDB, but it would be nicer to do it with Fossil/SQLite.

Always loved HyperCard. Other than spreadsheets, perhaps the ultimate user-oriented programming environment. I still remember lots of teachers building lots of courseware in it. Nothing has yet replaced it.

There actually is a replacement, per se: https://livecode.com/

Thank you. It will be nostalgic to try it out. I'm glad to see that they seem to have some sort of open source option.

You may want to take a look at LiteStore (1,2,3) which is "is a lightweight, self-contained, RESTful, multi-format NoSQL document store server written in Nim and powered by a SQLite backend for storage. It aims to be a very simple and lightweight backend ideal for prototyping and testing REST APIs and client-side, single-page applications and web apps."

I just found it, but it may bridge the gap between Fossil and a Personal Database System. Maybe some addons on the SQLite backend storage can convert it in a Fossil backend.

Cheers,

Wow. LiteStore certainly looks like something I want to explore. Thank you.

I would like to have a single file that can serve both as my SQLite database and a web UI for it.

It seems this set is missing an important ingredient, that is the Program. In the one case it's MS Access (whatever new version...), in the other it's fossil.

The deal you're seeing -- that it's a 'single file' -- is only partly true in case of fossil. Even in that case, the UI is really part of the fossil itself, unlike in case of Access, where it's the user forms coded in .mdb.

I see the big advantage of fossil in that it packs a nice webserver with ability to run scripted extensions. This opens the door for coding whatever Application to deal with personal db needs or such. Such Application can make use of any db it wants, separate SQLite or the repo-based. Same with UI, it can be scripted and served via the ext/.

If one has php installed, then one choice might be to wire Adminer script into ext/ so it would handle the db (it already has forms for that). Just an idea...

I tried to follow up on the idea to try fitting Adminer into Fossil's extroot. This kinda turns Fossil into a quick local db management tool; not sure how much utility in this could be.

In general, Adminer also can work with SQLite dbs (though needs some workaround to skip authentication). How close this POC (proof of concept) implements the ideas mentioned in this thread is up to you to assess. I don't think this POC deserves a branch in the present form, so putting it here.

Basically, the goal was to test if Fossil extroot can serve PHP Applications. In the current version Fossil presents a few hurdles for this:

1. Headers emitted from a typical PHP Application are not being passed fully from Fossil's processing of extroot page output. Specifically, this affects Cookie-based transactions common with PHP Applications.
2. Fossil's CSP (Content Security Policy) handling could collide/mess up with such coming from the PHP Application.

Well, just for this test I found a way aroud the hurdle-1 -- patch below. It's more invoved to patch the hurdle-2. But, luckily it seems to work with CSP warnings in Firefox. Both of these points may need better considerations.

Anyways, here it is, just for fun. To try it you need the following:

1. PHP installed (the script uses /usr/bin/php-cgi)
2. Fossil version patched (src/extcgi.c)
3. Adminer PHP Application wired into Fossils extroot (adminer.php)

The script calls fossil in $PATH, so make sure the patched one is getting called; can be configured in the script below. The script also takes care of step 3.

• To start and run: './fdb-setup.sh'. First time it will download the adminer.php and create the scripts to serve it from extroot.
• To clean up: './fdb-setup.sh clean'

The application runs at http://localhost:8080/ext/fdb and should present a Login form (for a whole slew of supported Db types)

• Select SQLite3 for Db type and just hit Login.
• If all Ok, the next page will allow to create a new SQLite db (e.g. mydb.sqlite)
• Once the db created (it's in the extroot), it can be manipulated.
• Alternatively, a full path to a .sqlite file can be specified for the db name.

NOTE: It seems that some actions redirect to /fdb, instead of /ext/fdb. Not sure why, just manually add /ext in the address.

Also, Fossil repo db does not seem to be supported by Adminer (maybe due to peculiar aliasing in the schema).

Have fun!

The Fossil patch (src/extcgi.c): apply as 'patch -p0 fdb.patch'

---

Index: src/extcgi.c
==================================================================
--- src/extcgi.c
+++ src/extcgi.c
@@ -326,10 +326,14 @@
       }else if( fossil_strnicmp(zLine,"Content-Type:",13)==0 ){
         int j;
         for(i=13; fossil_isspace(zLine[i]); i++){}
         for(j=i; zLine[j] && zLine[j]!=';'; j++){}
         zMime = mprintf("%.*s", j-i, &zLine[i]);
+      }else if( fossil_strnicmp(zLine,"Set-Cookie:",11)==0
+                || fossil_strnicmp(zLine,"Content-Security-Policy:",11)==0 ){
+        cgi_append_header(zLine);
+        cgi_append_header("\n");
       }
     }
   }
   blob_read_from_channel(&reply, fromChild, nContent);
   zFailReason = 0;  /* Indicate success */

---

The fdb-setup.sh script: run as './fdb-setup.sh'

---

#!/bin/bash -e

adminer_ver=4.7.6

TEST_PATH="$PWD/fdb-poc"
FOSSIL_DEV_PATH="/home/yydevelop/work/fossil/build"

export PATH="$PWD:$FOSSIL_DEV_PATH:$PATH"

trap on_ctrl_c INT
on_ctrl_c() {
  echo ""
  echo "$0:DONE test ($TEST_PATH)"
  #cleanup
  exit 0
}

get_externals() {
  [ ! -f adminer-${adminer_ver}.php ] || return 0

  ## Adminer (https://www.adminer.org)
  wget --output-document adminer-${adminer_ver}.php https://github.com/vrana/adminer/releases/download/v${adminer_ver}/adminer-${adminer_ver}.php

  ## SQLite passwordless plugin
  ## https://gist.github.com/FMCorz/f7819107c6848841372624d1becc8958
  wget --output-document adminer-plugin-login-sqlite.tar.gz https://gist.github.com/FMCorz/f7819107c6848841372624d1becc8958/archive/672d7a6a0789b7979246606733ba56dc8a0dd303.tar.gz
  wget --output-document plugin.php https://github.com/vrana/adminer/raw/v${adminer_ver}/plugins/plugin.php
}

setup_adminer() {
  [ ! -f adminer.php ] || return 0

  get_externals

  mv adminer-${adminer_ver}.php adminer.php

  ## login-sqlite plugin
  mkdir plugins
  mv plugin.php plugins/

  mkdir login-sqlite
  tar xfv adminer-plugin-login-sqlite.tar.gz -C login-sqlite --strip-components=1

  mv login-sqlite/index.php ./
  mv login-sqlite/login-sqlite.php plugins/

  rm -rf login-sqlite
  rm adminer-plugin-login-sqlite.tar.gz
}

setup_fdb() {
  [ -d cgi ] || mkdir cgi
  cd cgi

  setup_adminer

  ## create fdb to run adminer

  cat <<EOF>fdb
#!/bin/bash
export REDIRECT_STATUS=1
export SCRIPT_FILENAME="\$PWD/cgi/adminer.sh"
exec \$SCRIPT_FILENAME 2>&1
EOF
 chmod +x fdb

  cat <<EOF>adminer.sh
#!/usr/bin/php-cgi
<?php
## Uses login-sqlite plugin for SQLite passwordless
include("index.php");
## Usual Adminer
#include("adminer.php");
?>
EOF
  chmod +x adminer.sh
  cd ..
}

setup_fossil() {
  [ ! -f ../fdb-poc.fossil ] || return 0

  cd $TEST_PATH
  fossil init ../fdb-poc.fossil
  fossil open ../fdb-poc.fossil
}


start_fdb() {
  cd $TEST_PATH
  fossil ui --extroot $PWD/cgi --nojail --page ext/fdb
}


cleanup() {
  [ -d $TEST_PATH ] || return 0
  cd $TEST_PATH
  rm -rf cgi
  fossil close
  rm ../fdb-poc.fossil
  cd ..
  rmdir $TEST_PATH
}

##### MAIN

if [ "$1" == "clean" ]; then
  cleanup
  echo "$0:DONE $1"
  exit 0
fi


[ -d $TEST_PATH ] || mkdir $TEST_PATH
cd $TEST_PATH
setup_fdb
setup_fossil
start_fdb

---

Thank you for your reply and subsequent patch-posting.

Regarding,

the UI is really part of the fossil itself, unlike in case of Access, where it's the user forms coded in .mdb

that is also only partly true. For Access, it's the layout of the forms and reports that are coded into the .mdb (and stored in database tables), whereas the actual code for rendering those layouts is in the .exe and whatever ancillary DLLs are installed for VBA support, etc.. VBA code for stored procedures naturally resides in the .mdb.

Apparently, LiteStore was written by a man after my own heart:

The release package contains the following files:

• litestore / litestore.exe — The LiteStore executable.
• LiteStore_UserGuide.html — The official user guide describing the program, its usage, architecture, API, etc.
• data.db — The default data store file, containing the LiteStore Administration App.

That’s all. No libraries to download, no installers, just an executable file. I love self-contained programs.

Do you still see merit in this direction?

Sure. The development isn't "stalled" as you characterized it in another thread. I want to use this system, not write it. For development to stall, it has first to start. :)

fossil is not relational - so any such "personal database" layer would need to produce cards.

That's a non sequitur. Syncing doesn't require a hash tree of cards. Techniques for syncing a relational table are well-known and in widespread use at global scale today. I'm not going to list them; get yourself prepared to be snowed under with information, then go search the web for database replication.

Doubtless it is attractive to get syncing for free by building atop Fossil's hash tree, but that has serious implications on how the resulting system works. Can you "fork" a database? Branch it? Merge it? When you change a record, can you get a diff between the versions? Can you bisect a database to find out who changed a given record, and when?

Again, all very attractive…until you return to the starting premise, which is that Excel is a terrible DBMS, and it'd be nice to hand people something simpler to use for a given task. Do you seriously imagine all those Excel warriors will enjoy learning how to resolve merge conflicts in their business databases?

The development isn't "stalled" as you characterized it in another thread

:) I have amended my comment to plainly state the fact that this thread had not received any replies in a couple years.

That's a non sequitur. Syncing doesn't require a hash tree of cards.

Okay, I'm interested in digging into this more. I was under the impression that the file formats make up Fossil's data model, and that SQLite is an implementation detail - effectively an engine for interacting with the DAG that is represented by the unordered list of artifacts.

While Fossil uses SQLite to produce tables event, leaf, tag, etc, I could similarly export the artifacts table and represent the DAG using Elixir data structures.

I assumed that compatibility with Fossil's current approach is desirable, and to be compatible means to produce artifact cards. You disagree?

Do you seriously imagine all those Excel warriors will enjoy learning how to resolve merge conflicts in their business databases?

Absolutely not.

I've reviewed your initial post though and believe I'm thinking of a different path.

Imagine a system based on Fossil which lets normal people create and manipulate tabular data via a web UI.

I think that disqualifies me from this discussion...

Imagine what you can do as a software developer with such a system. Not everything needs to turn into a project in your favorite programming language.

You get a lot for free by starting with Fossil

Hey, I'm back in the game! Your list is exactly what appeals to me about it. It seems like a really strong foundation for building tools that track data over time.

I do see one big difference between your vision and what I'm thinking of though. You are talking about building general Access-like functionality on top of Fossil, that non-developer skilled users can put to good use. I am talking about developers taking advantage of the SQLite implementation, and building more specific workflow tools that get history, branching, and merging "for free" because of Fossil's data model.

Again, all very attractive…until you return to the starting premise, which is that Excel is a terrible DBMS, and it'd be nice to hand people something simpler to use for a given task.

Yeah, so, all very attractive to me as a developer, who likes to build tools for myself and sees Fossil as a very attractive platform for doing so, with potential to quickly build tools for customers at my job as well. Building a system where non-developers can construct databases and their own workflows - very cool, different direction than what I have in mind, and probably a whole lot of work to get right.

I find it surprising that nobody so far mentioned pretty solid web-tools in that direction: Notion and AirTable
Of course we want open source software, but on that side appflowy and anytype look promising.

They might lack proper version control and CLI access are not there directly, but maybe it makes sense to make connections among these tools? I never understood why fossil wants to do everything itself. There could still be a plugin system of extra features which can just be compiled in, like xcaddy.

I never understood why fossil wants to do everything itself.

Short answer: It's very much a cultural thing.

Explanation...

Fossil stems from the sqlite project, which stems from Richard Hipp. He has often been heard to say that his definition of "freedom" is "being able to take care of yourself." That philosophy permeates all of his projects in that they are, except where absolutely unavoidable, free of dependencies on 3rd-party code. libc, zlib, and SSL are the only hard exceptions which readily come to mind. The first two are ubiquitous and implementing the third would be a years-long effort of its own.

In short, if Richard requires an SCM-side feature, whether for sqlite or one of his other projects, fossil is where that goes. Other contributors are more or less free to add new functionality, but any feature which cannot reasonably be built into fossil without 3rd-party dependencies is unlikely to be added. Similarly, within fossil we "reinvent the wheel" at times in order to avoid relying on other projects so that this project can be free, in the sense of Richard's definition, in that it can "take care of itself."

Fossil already has this. The ticket system is a user configured table with multiple user-customizable views. In fact I use this currently for several projects on my team; compute requests, team coverage, vacation planning etc. It works pretty well but some changes would make it even better:

1. Make it easy to extend the ticket table without using sql

2. Have the views do a "fair" job on automatically showing new fields

3. Make it a bit easier to hide that the system is built on a ticketing system.

4. Allow for multiple ticket derived systems in one fossil. I imagine this would be very tough to implement but it would be a breakthrough feature IMHO.

The command line interface for tickets allows for inserting new rows, modifying existing rows and hiding rows. That covers 99% of anything I'd ever need to do.

This flexibility in fossils ticketing system along with the malleability of it's UI has been wonderful. I just wish it was a bit easier to use.

Just my $0.02

I know it's asking a lot but a writeup with a couple of examples would be stellar as I've looked into extending Fossil's ticket tracker previously and never got past the architectural/conceptual stage because it's either difficult under-documented orI didn't understand what I was reading.