Why Trac uses SQLite as its internal data store

There had been a discussion in the Trac mailing list why Trac uses SQLite as data store instead of the more natural approach of using a version controlled subversion repository for storage of issues and wiki pages. This pages summarizes this discussion by highlighting the arguments and referencing the original posts. Other arguments have been integrated later.

The reader interested by this topic might also be interested by the older proposal:

• the TighterSubversionIntegration proposal, which was written when Subversion was the only VersioningSystemBackend supported by Trac. The ideas in there (and I believe in this page too) can actually be transposed to any backend.
• The newer TracObjectStorageProposal, which suggests that we could actually find a way to gain more flexibility in the choice of a backend storage.

The following notation is used:

1. Introduces a new argument.
   • [+] Statement that strengthens the argument.
   • [-] Statement that invalidates the argument.
     • [+] Statement that reestablishes the argument by refuting the objection.

Pro SVN

1. SQLite is space-expensive, no diffs (​Alik Eliashberg).
   • [-] Diffs could be stored in SQLite instead of full versions (​Emmanuel Blot).
     • [+] Diffs in SQLite would be reinventing the wheel by implementing yet another VC system (​Alik Eliashberg).
   • [-] Disk space is not an issue (​Jonas Borgström).
     • [+] Space is an issue, if valuable data is buried into a bunch of transient state that can easily reconstructed (in terms of Trac by resynchronizing with the repository). Trac mixes transient and non-transient state in a single database. Transient state e.g. does not require backup, valuable data does.
2. Enables the management of wiki pages outside of Trac (​Alik Eliashberg)
   • [-] Removing wiki pages from outside leaves dangling references to this page (​Emmanuel Blot).
     • [+] Pages can also be deleted with trac-admin leaving the same dangling references (​Toni Brkic).
     • [+] Dead links are a natural feature of wikis (​Alik Eliashberg).
   • [-] Editing a wiki page in an editor is non-wiki (​Emmanuel Blot).
     • [+] Editor is more convenient for page creation and large changes. Wiki-Web modifications are means for community interaction (​Alik Eliashberg, ​Toni Brkic).
     • [+] This page has been authored in Emacs to have better overview and spell checking (haui at haumacher.de).
     • [+] It is not only about editing, one might want to auto-generate pages. Wiki is only a means to an end.
   • [+] More important, it facilitates writing scripts that create / edit pages. (Ioan Bizau)
3. A wiki is well suited for project documentation. Project documentation is an integral part of the project and should be stored in the project repository, access should not be limited to Trac access (​Denis Gervalle, ​Subissue).
   • [+] In particular, you want to be able to use tags and branches for project documentation. Using SVN would provide this for free and would allow a single command to create a tag or branch that includes both source and wiki state.
   • [+] We use the wiki for configuration management. Using SVN allows for baselining and tracking the configuration using tagging and branching.
4. No extra RDBMS required, Subversion already has its own database (​Subissue).
   • [+] Less to learn, since people installing trac are already familiar with subversion (​Toni Brkic).
5. Extra benefits from VC systems like merging/diffing with no extra cost (​Toni Brkic).
6. In SVN most everything required for issue tracking is already in place: logging, intelligent reporting, history, access control, email notification (​Subissue).
7. Using SVN would leverage the benefits of FSFS such as being able to run over NFS. Current docs discourage using SQLite over NFS if multiple users can access it at the same time.
   • [-] Modern locking on NFSv3 servers may be sufficient for SQLite.
8. With regard to Java projects. Integrating the Wiki into the same SVN instance as the source code leads to some interesting potential features. You can directly link to your .java source code in the wiki. This means that when you fix an issue in the issue tracker, you can directly reference the changed code. All this can be done naturally using the diff information generated by SVN. (dave@…)
   • [+] If you choose to check your JavaDoc into SVN then you could naturally link from your wiki pages to the JavaDoc or even from the JavaDoc to your wiki pages.
     • [+] A step further is to integrate a JavaDoc processor into the wiki that will generate JavaDoc from .java files. An extremely advanced version might even let you modify the JavaDoc using the wiki and save the changes to the .java files! You can treat JavaDoc as wiki. That is sure to lead to well maintained JavaDoc.
9. You have one less source of material to backup. One currently must run a backup of SVN and Trac seperately.
   • [-] SVN's hotcopy backup is currently not very user friendly
     • [+] Neither is Trac/SQLite's dump
10. It's trendy: Apple is using SVN as the backing store for the ajax-wysiwyg "Teams Wiki Server" that will be released with 10.5 Server.
11. Storing data in a versioned system would lend itself to distributed bug tracking. DistributedTrac for more thoughts on this. Note this is not "Pro SVN" as much as "Pro VCS" or even more accurately, "Pro Backend Agnostic". Different shops have different workflows, and the tools should be flexible enough to support that.

Pro SQLite

1. SVN integration would counteract Trac support for other VC systems.
   • [-] Support for other VC systems can be achieved with a backend-independent VC interface (​Alik Eliashberg:)
     • [+] A backend-independent VC interface has a least common denominator problem (only features supported that are supported by all VC systems) (​trac at nogga.de).
       • [-] Adding Trac support for other VC systems has the same least common denominator problem, because Trac could no longer make use of unique SVN features. There is no need for supporting other VC systems at all (​trac at nogga.de).
   • [-] Integration with other VC systems should be a very low priority compared to creating a feature-rich system based on SVN. SVN is, of course, open source and free. CVS doesn't have the sophistication required to support all wiki features (e.g. you can't rename files…). Let's not get bogged down with the lowest common denominator. (dave@…)
2. As workaround, a macro can include SVN files into wiki contents (​François Harvey, ​Mario Ruggier, ​François Harvey).
   • [-] This doesn't allow SVN files to be edited via the wiki. Also you still have two different repositories (SVN and wiki) to backup.
3. Trac heavily depends on SQLite and SQL (​Jonas Borgström).
   • [-] There is no reason why Trac should'nt store "transient" data in a regular database. Such database would be required anyway, because some features of Trac need indexing for fast searching. Valuable non-transient data should stored in a version controlled repository (haui at haumacher.de).
4. Revision numbers of the repository would be incremented for every wiki page and ticket added or edited.
   • [-] A different, dedicated, repository could be used for project documentation instead the one being Trac'ed if this is an issue.
   • [-] I can't imagine a situation where I'm bothered about this. Do you really care if your repository revision is 45 or 345? (dave@…)
5. Trac currently does not modify the repository at all — changing it to do so might create other problems.
   • [-] "Other problems" is not an argument (haui at haumacher.de)
   • [-] If there really are "other problems", this could be solved by using a repository different from the project repository for storing issues and wiki pages.
   • [+] This would require the user running the webserver to have write access to the subversion repo. You really don't want to do that if you care about security.
   • [+] Trac can only use direct file access to access subversion repos (correct me if I'm wrong), your subversion repo may be using an incompatible access method (e.g. we use ssh+svn so write access is only possible by the ssh user)
6. If you use a continuous build system such as ​CruiseControl, all of a sudden changing wiki pages (and thus checking them into SVN) would cause spurious rebuilds of your project.
   • [-] CruiseControl (and presumably other continuous build systems) can be configured to only rebuild when a certain subset of the repository changes. For example you can configure CruiseControl to only look at changes to the "trunk/src" directory, not the "trunk/wiki" directory.

Conclusion

Count the [-] and [+] in the pros and cons section. It seems that the title of the pages was badly chosen.

• [-] Counting pros and cons is a bad way to evaluate an argument, because different points can have vastly have different weights. For example: "Should I eat? Pros: I will survive. Cons: I might get indigestion." Well, looks like 1 for and 1 against, so it's a draw!
• Even taking into account that some points are more important than others it seems very clear from reading the above listing that way more advantages and interesting possibilities have been pointed out with the suggested approach than possible problems. Note that there is not a single "pro SQL" argument that has not been argued against/refuted in a sensible manner and not a single "pro SVN" argument, that has been left with an invalidating argument. Judging from the ​Page History this argument seems to have been settled quite clearly.

Another viewpoint

• trac isnt long since bound to SQLite only - there are adapters for postgres.
  • [+] this helps to marry trac with other information sources when you are able to inject data w/o the web interface
  • [-] this does not touch the listed arguments for SVN-integration at all. Swapping PostgreSQL for SQLite won't help accessing documentation (and what else is a wiki) using $EDITOR, does it?

• alternative storage could also be ZODB - which would provide a more natural way to store python objects, has indices, has all historic data for timetravel and does not require the user to install yet another program in conjunction with trac and svn (and apache) - afaic one of tracs main goals is to "go out of the way" of the programmer. ZODB can also be used across networks and used to inject data just like the alternative SQL databases. And you avoid pollution of the SVN with wiki data.