Tree extensions for SQLAlchemy

I recently needed a performant way to manage a tree, meaning using MPTT, nested sets etc.

I looked at the following libraries, which integrate with SQLAlchemy. These are my notes, some of which seem to be outdated now.

  • No features for querying
  • Injects fields like parent_id, children into the model by itself, without allowing customization.
  • It’s rebuild() is not able to init a tree from scratch.

  • Integrating into the class-based ORM mapper is messy (pr accepted)
  • No rebuild() feature at all (pr accepted)
  • Has bugs when used with ORM attributes are diverging from table column names
  • BIG PLUS: can detect and handle tree moves if attributes on an existing node change.
  • Does not support keeping a sort order around.

  • Seems to have a working rebuild()
  • Can sort of integrate with the class-based ORM mapper setup (although it seems it would need to be globally)
  • tree_recursive_iterator() for querying a tree.
  • To move a subtree, we need to call special move() methods.

sqlamp and sqlalchemy-orm-tree seem to share a lot of ideas/APIs.

sqlamp seems to be the most most stable / actively developed, although sqlalchemy-orm-tree makes a good impression, too, and the tree move detection is so helpful that I decided to go with it.

Deleted Emails – Imap Sync issue with Thunderbird (and Dovecot)

I’ve run into this Thunderbird issue a number of times, always with a Dovecot IMAP server:

  1. You have multiple connections open to an IMAP account.
  2. You delete an email in one client.
  3. The email is not removed from Thunderbird until you restart Thunderbird, or do other sorts of fiddling around.
  4. Meanwhile, marking messages as read syncs just fine immediately.

There are a lot of people complaining about this, and various workarounds suggested (1, 2), but I’ve never seen a clear explanation of why it doesn’t work and who is really at fault, more importantly, why it isn’t fixed.

The best answer I found is that Thunderbird’s implementation of the CONDSTORE IMAP feature (which allows more optimized syncing) is broken, isn’t getting fixed since Thunderbird development is basically dead, and that disabling CONDSTORE, either on the server (i.g. in Dovecot) or in Thunderbird (disable “use_condstore” setting in the advanced config editor) is the best way to fix it.

Options to do CSS in React

  • Suit CSS et al.: separate, regular stylesheets but component naming convention for components.
  • Just manually with inline style attributes. To merge multiple style maps, use helpers like react-style, which enables an array syntax for the style attribute, use a custom function like m() to do the same, or use destructuring: style={ {...a, ....b, ...c} }.
  • Radium supports a [] syntax like react-style, but also adds support for things like :hover and media queries at the expense of wrapping the component.
  • CSS Modules is a CSS-like syntax that gets parsed into a dict to be the used inline.



  • Durchaus komplex, aber auch entsprechend viele Funktionen.
  • Muss installiert werden (irgendein Adobe Air Dreck), dräge UI
  • Teuer (Anlagegüter nur in Enterprise-Version!)
  • Gegenüber Lexware Buchhalter wohl ein Fortschritt.
  • Die Funktion Kontierung nach Regeln ist sehr nett.


  • Sieht super aus, nicht langsam.
  • Die Präsentation des “Kontorahmens” ist sehr nett.
  • Nicht wirklich ein Buchhaltungsprogramm per se; ohne zuvor erstellte Rechnung kann man auch keine Einname abgleichen.
  • Erweiterte Funktionen lassen sich nicht testen ohne zu zahlen.
  • Auch ansonsten gefällt mir die Preisgestaltung nicht. Bei mehr als 2 Konten muss man schon ins Top-Paket.


  • Die einzelnen Versionen haben eine unterschiedliche UI. In der Buchhalter-Version dreht sich die UI tatsächlich um Konten und Buchungen, in der Standardfassung um Rechnungen und Belege.
  • Kann HBCI Kontenzeilen “Belegen/Rechnungen” zuordnen, oder direkt auf SKG Kontorahmen.
  • Fertiger Kontorahmen, keine eigenen Sachkonten.
  • Man kann sich Sachkonten auch nicht anzeigen lassen.
  • Kann an Elster übermitteln.
  • Hat CSV Export.


  • Eher eine Webseite als eine “App”.
  • Ich mag den Modus wo er beim Buchen zuerst nach der Einnahme-/Ausgabeart frägt, und das Konto so quasi versteckt.
  • Keine eigenen Sachkonten, nur Kontorahmen.
  • Nicht wirklich irgendwelche Funktionen.
  • Träge.


  • Extrem unsexy, auch in der Bedienung.
  • Funktioniert aber und hat alles was ich so brauchen könnte, einschliesslich Elster.
  • Ist eine der schnelleren Apps; ich habe das Gefühl das man trotz der hässlichen UI schneller damit arbeiten kann als mit scopeVisio.
  • In der URL steht cgi.exe

Am Ende würde ich hauptsächlich lexOffice und collmex in Erwägung ziehen.

Moving to a new todo app

I’ve been a GetFlow customer since the beginning of 2012. But I’ve been growing exceedingly unsatisfied with the experience, especially since it’s not the cheapest of apps (I was paying 10 dollars per month, now it became more expensive).


  • It’s too slow. Loading the app takes a couple seconds; loading the Android app takes a couple seconds.
  • On Android specifically there is no offline capability. It has to load the whole thing every time, cannot add or check items without a connection, might even have to reload after a screen lock. Terrible if you want to use your shopping list, and the supermarket has bad reception.
  • The Android app doesn’t event let you access subfolders.
  • The search feature is slow, too, and doesn’t seem to find everything.
  • They used to have a section to add long-form text, which I used a lot. But some years back they changed their system to have uneditable comments only, which has been bothering me ever since. It’s just not at all helpful for a single user.

I looked at both Wunderlist and Todoist.

  • Wunderlist has a better Android app; though Todoist is not particularly bad.
  • Todoist’s web interface just looks more attractive to me. Wunderlist does not seem good on a big screen.
  • Both have their UI warts: I hate having to double click in Wunderlist to see the task details, which seems completely unnecessary. Neither do I like how a single click in Todoist enters into a disruptive edit-mode.
  • Both have editable notes, and both don’t implement those in a great way. It’s too uncomfortable to use this for more than short notes. But at least it’s there. In Wunderlist it is better than in Todoist. Hint: The notes should be easy to edit with a single click, ideally support formatting, and not be in a tiny window.
  • Wunderlist has a more traditional approach, with subtasks being an explicit feature. Todoist has a very cool indentation system. However, it has it’s downsides, because “subtasks” are really just their own tasks: They don’t repeat with the parent,
  • Todoist’s “after” repetition mode is pretty cool.

I wrote two scripts to export my GetFlow data and import it into Todoist.

Here is what I found lacking in Todoist so far:

  • Getflow could move a task to a different project by typing the name. This was often easier than using drag&drop.
  • When drag&dropping tasks, the project list left doesn’t scroll up and down as necessary.
  • It doesn’t really want you to keep old tasks around.
  • Todoist does not seem to expose obvious metadata like “when was this task created”. This seems like a small thing, but feels depper. In Todoist, tasks have a more ephemeral feel, less like real “objects” that are a matter of record. The fluid indentation, the easily accessible permanent delete feature, and most importantly the fact that the UI at times seems to encourage you to use the delete feature to clean up contribute to that.
  • In particular, the fact that completing a subtasks does not make that subtasks disappear until the parent task is completed bothers me a lot, and seems clearly not to be a design decision, but a limitation of the data structure – namely that it really is not a subtask, just a task with an indent value assigned.

React.js, Flux, Om: I am trying to think it through

(My notes on some of the idea behind React, Flux, OM, etc., as I understand them)

First, lets remember that react.js is supposed to the view layer. With it, we define the UI through a component hierarchy. It can re-render the DOM fast and easily. We re-render when our application data/state changes. This leaves the M and C parts of MVC. Where does our application logic live, and where does your application data live? In thinking this through and comparing approaches, I found it helpful to consider these two concerns separately).

In standard react.js, you call setState() on any component within the view layer to cause that part of the UI to be re-rendered. Standard react.js doesn’t care how you do your app logic or manage the app data. You can happily add it to the react.js components itself.

Making the react.js components hold your app data as state will break down as soon as one piece of data affects two parts of the UI. Try to move such state up the view hierarchy to a shared component and you will end up in callback-passing hell. So moving the application data out of the react components is an obvious step.

Making the react.js components itself update the application data should get you pretty far, on the other hand, I think. I imagine things can get fiddly if you multiple sources of changes: from user interaction, events streaming from the server.

So if in your react.js component you have a onClickHandler on a button which modifies an external data structure to add a TODO item, how do we tell the “todo list component” to update itself?

  1. I don’t even want to consider building a findComponent(‘list).update().
  2. You can pass the onClick event all the way up to shared component, and implement the logic there. This will involve a lot of painful callback passing/prop transfer code, I think. You could build a system where a single signal object is passed down, and the button can do signal.trigger('add-todo; ), where as the parent view can catch it and act.

  3. You can modify the data at the level of the button, and create a system where changing your application data model triggers a callback where relevant parts of your UI listen to.

Facebook’s Flux defines a concept of Stores to hold your application data. The stores trigger callbacks when the data is changed, and your react.js components can listen to these change events.

Flux moves all application code out of the react.js components. Instead, interactive elements in your view hierarchy fire actions which go through a global dispatcher. Some application code can live in the actions itself (that is, in flux, within the action creator function). Other code lives in the stores: The stores watch for actions being dispatched, and then change the application data that they hold according to the action (then they trigger a callback, which will cause the views to re-render themselves).

In practice, only a couple components (controller views) listen to the store events, and child components get the data from the parents via pops.

In this model, all data that isn’t transient should live in the stores, and the state of the react.js components would at best contain a copy.

Thinking in react explains it very explicitly.

Om decides that since the re-render is so fast, we can just re-render the whole UI whenever anything changes. So rather than having multiple controller-views that listen to different stores, like in Flux, simple have the root listen to all data changes.

Om has a thing called a cursors, which is a view on a subset of the application data. A cursor can change the part of the data it points to, thus triggering a change event on the complete data structure itself, thus causes a full re-render. Components in the hierarchy are generally being given cursors from the parent as a prop.

Om also uses immutable data structures. There are a variety of benefits, but in particular, it can make the re-render a lot faster: by default, a re-render still means recomposing the whole virtualDOM again. To avoid this, react.js has shouldComponentUpdate().

Let’s talk about shouldComponentUpdate(). I found it hard to understand at first why react.js needs this hook. Assuming that I call setState() when the data changes, don’t I always want a re-render? But of course, that is only true if each state is limited to one component. If we talk about a potentially deeply nested data object like in Om, applied to a deeply nested view hierarchy, changing on integer in one branch does not mean we need to re-render the whole tree. (Also, there will be some cases where you call setState()with a “full reset” data object, and you don’t know or care if any of the state has actually changed).

But since react.js already expects you to call setState() manually, why can’t it compare the new state with the old state by itself? Facebook says they don’t trust you to use setState() exclusively, and not accidentally modify the state in-place. If you trust yourself, you should be able to implement a simple shouldComponentUpdate() to do exactly that comparison.

Om further uses immutable data structures. If your application data is immutable, your checks in shouldComponentUpdate() (if the new data is different than the old for any particular component) can simply compare object identity, which is much faster than comparing a deeply nested structure.

What is the better approach? The Flux stores or the Om singleton? It seems to be that they are both similar and completely different things.

Where it comes to your application code, the default thing in OM (and also derivatives like react-cursor) seems to be to put your application code into your components. Modify the data directly via the cursors. This will cause a re-render. But you can easily change this, and use a Flux-like action system in Om to encapsulate the application code.

The actions themselves might change the application data, or you might have multiple stores, which catch the actions, and which would simply be a namespace within the application data structure, that they manage (this idea is described here).

When it comes to the application model part, they seem not so different. I already mentioned that in Flux, you will have only a few controller views which watch for application data changes and pass it down. If you have very few of them, you might only have one, and then you have the single data structure as in Om. So it seems to me like the the difference between one global data structure, and two global data structures (imagine a virtual parent between them, and its the same).

It’s the same with the callbacks from the application data container (i.e. the stores or the Om atom) to the views. The single data structure in Om causes a re-render from one root controller view is just the simplest version of the controller view concept.

I think it is this that the Om author means when he says “Thus we don’t need React operations like setState, which exists to support both efficient subtree updating as well as good object-oriented style”. If you can make the full render fast enough, you don’t need to essentially manually optimize by targeting specific sub-components with change notifications.

A pattern that you arrive at very quickly with Flux is that for a controller view you have a getState() builder function which gets the state from the store, and then you use it in getInitialState and whenever you call setState(). Once you do that, what do you have is conceptually like a cursor to a subset of your application data.

So I think Om might be on to something here.

So in terms of my architecture, it seems I can make a couple of decisions.

Do I need good performance? Then I can either make sure model updates hit far down in the component hierarchy, and write good shouldComponentUpdate() handlers, or I can use immutable data structures, render everything from the top, and my shouldComponentUpdate() will be reliable and fast.

If my app is small, react might be fast enough even when rebuilding the complete Virtual DOM tree.

How do I communicate changes from my data to my views

I can use Flux stores and have callbacks to subcomponents, or I can have what amount to a single store and have callbacks to the top root for a full-render. I think callbacks are pretty much it. One or more parts of your view will listen for callbacks. These callbacks are triggered by your stores/controller code, or if you use a cursor-system, your datastructure already provides the solution.

While in theory possible, I would avoid having your controller code inside your react components to update both the external models and then re-render the view.

Where do I want to write my application code? Where do I affect changes to my data?

If you don’t want your application code to live with your React.js components, something like the Flux system seems natural, meaning, some code will be in action creators, some code will be in the models. I don’t feel like I should to be too dogmatic about it though, and variations on Flux, which is somewhat loosely defined anyway, seem perfectly feasible, too.

If you want to put some of the logic inside your React.js components itself, you can still have stores and call e.g. TodoStore.add() as an accessor, and have some of your logic inside your data models. Or you can use cursors to modify the data directly, and put your code completely into your view components. Sometimes you might communicate with a parent component via props/callbacks (I like the idea of a signal object, see the first section).

Looking at what I wrote, It seems I’m essentially saying “you can do what you want with react”. There are probably various advantages and disadvantages to the different approaches, which I don’t understand yet, but I found this exercise very helpful for myself to understand the role of react.js and how it compares within these different ideas.

The idea of adding application code to the react components itself has a certain appeal to me, because what you end up with is similar to how you might structure, say, an app in Ext.js or Qooddoo, where each component in the view hierarchy is it’s own mini-MVC entity. The react component being the controller and the render() method being the view.

I’m also tempted a lot to use the React component state for anything that might be UI state, which ends with me communicating events up the view tree more than I want.

On the other hand, there is something beautifully simple about the uni-directional dataflow of Flux. The react.js component is only the view, and my code lives in a swarm of actions. It reminds me of MOVE (

I hate the verbose string comparisons in flux, so I’d look at something like reflux.

There are some links I collected during this.

Some libraries to re-create Om ideas in plain Javascript

Why you might not need MVC with React.js It defines the difference between data state and application state, and points out how react, by nature of overwriting the whole DOM you your own tree, requires you to define all state explicitly (it will be overwritten if it’s only in the DOM).

Ajax should be done in action creators not in stores

Watching a movie with two subtitles

Tools to merge SRT subtitles

SRTMerger – Input: SRT, Output: SRT, Different colors, displays both subtitles at the bottom, on top of each other.

2srt2ass – Input: SRT, Output: ASS. One subtitle on the top of the screen, one subtitles at the bottom (possible because the output format is ASS).

DualSub – Input: SRT, Output: SRT; displays them at the bottom, next to each other. This is essentially a hack, so it requires you to specify the font with which your player will display the subtitles in advance – you are essentially creating subtitles for a particular player, and it can be a bit fiddly. On the plus side, this one has amazing options for keeping subtitles on the screen for longer, when possible, as well as smoothing over small timestamp differences.

Player functionality

Subtitler (VLC plugin)

Dictionaries, APIs and Databases, in German and other languages

Aside from the obvious, like Wiktionary, Duden, there is also:

Attempts to parse Wiktionary (collected from SO)

Part of Speech tagging