save build status in an SQL database

[warner]

Some things would be made easier if we were to store build status in a database that could be read by other processes. SQLite would be a good starting point.

[warner]

http://buildbot.net/repos/sqlify/ contains a work-in-progress tree for this. Take a look at Branch.ChangeLog? for an idea of how much (or how little, rather) has been done so far.

[Pike]

Sob, timeline doesn't talk about ticket comments :-/

Anyway, I'm interested in this, as I mentioned in the mailing list.

I heard reports that sqlite is not very fond of concurrent access, which would make it a not-so-good choice for environments like Mozilla, at least that's my guess. Given our constraints in terms of firewalls and load.

Have you considered using a intermediate library like sqlalchemy or sqlobject or even the stuff that django comes with? It seems to me that they'd be easier to code with (in reverse order I listed them), and would keep plain SQL statements out of the code while abstracting from particular database implementations at the same time.

[volsung]

SQLite does a reasonable job of dealing with concurrent access (see  http://www.sqlite.org/lockingv3.html) as long as the OS file locking is correct. The SQLite locking page warns that many NFS implementations don't do file locking, so that is a potential showstopper. The main problem I've run into, at least with SQLObject, is that the database wrappers don't block when there is lock contention between a reader and a writer process, but instead immediately throw an exception. The caller has to catch the exception and try again. I haven't checked if the lower level pysqlite2 (or whatever it is now in Python 2.5) module provides some way to control lock behavior. (I may have also missed the appropriate option in SQLObject.)

Certainly I would also vote for using a database module to abstract out the backend. I've only used SQLObject and Django. I prefer the Django database interface, but I think extracting it from the framework would not be worth the trouble. Lots of people rave about SQLAlchemy, so that is of course also worth a look.

[dustin]

I like the schema, in general. I think that it gets toward a separation of event-based status from what I think we should rename to "build history". Think of it this way: I want to be *notified* of events like a build failure (via IM, IRC, email, whatever). But the web displays aren't notifications of anything -- they're displays of the current state, or perhaps recent history.

I'd like to make that a bit more formal in the structure of the code that *uses* this status information. Some work on the interfaces could allow code processing the cstatus? list to differentiate status targets (that want notifications) from status displays (that need access to build history).

In my experience, SQLite works great, until your DB hits about 1G, at which point it crawls. It's also not twisted, so you'll either have to wrap a thread pool library around it (which isn't difficult) or deal with delays while it executes queries.

I think that a better idea will be to define a reasonably flexible Interface, with lots of getFooByBar(), getLatestNBings(), and so on, and then implement a few backends to that, one of them using the twisted DB layer. Another using SQLite would be a possibility, as *might* one based out of the current pickle scheme (perhaps after loading it all into memory?).

I'll play around with it, and report back.

[sminnee]

Hi dustin - did you get very far with this?

[dustin]

No -- it's a BuildbotOneOh? feature, for surely.

[sminnee]

dustin - any chance that you could push your work in progress on to a github fork, or possibly post a patch here, so that we might be able to help push this forward?

[dustin]

Actually, Brian Warner working on it now ..

[dustin]

work in progress at  https://github.com/djmitche/buildbot/commits/9/builds