Database schema update

This post is a draft for a plan of action to migrate the database from SQLite to Postgres.

New Schema

Here is the present schema design:


Here is the new schema design (with relevant tables):


(Sorry about the strange cut off words in the image – all artifacts of exporting graph from And if you would like the format for these schemas, I can send them to you.)

Some notes about the new schema:

  • The SQLite “stats_build” and “results” table have been combined to a single “results” table.
  • The intent is that the Postgres “package” and “package_test” tables can be used for projects other than Debian. We’ll have to do some thought experiments to make sure this is the case.
  • Not all projects use the word “package” to describe a “bunch of source code corresponding to some distributable binary executable.” When the “package” is significantly different than the Debian concept of a package, the “type” column can be used. For example, for ISOs?

Work after DebConf

First week after DebCamp and DebConf! Both were incredible — the debian project and it’s contributors never fail to impress and delight me. None the less it felt great to have a few quiet, peaceful days of uninterrupted programming.

Notes about last week:

1. Finished Mattia’s final suggestions for the conversion of the package set pages script to python.

Hopefully it will be deployed soon, awaiting final approval πŸ™‚

2. Replace the bash code that produced the left navigation on the home page (and most other pages) with the mustache template the python scripts use.

Previously, html was constructed and spat out from both a python and shell script — now we have a single, DRY mustache template. (At the top of the bash function that produced the navigation html, you will find the comment: “this is really quite incomprehensible and should be killed, the solution is to write all html pages with python…”. Turns out the intermediate solution is to use templates πŸ˜‰ )

3. Thought hard about navigation of the test website, and redesigned (by rearranging) links in the left hand navigation.

After code review, you will see these changes as well! Things to look forward to include:
– A link to the Debian dashboard on the top left of every page (except the package specific pages).
– The title of each page (except the package pages) stretches across the whole page (instead of being squashed into the top left).
– Hover text has been added to most links in the left navigation.
– Links in left navigation have been reordered, and headers added.

Once you see the changes, please let me know if you think anything is unintuitive or confusion, everything can be easily changed!

4. Cross suite and architecture navigation enabled for most pages.

For most pages, you will be one click away from seeing the same statistics for a different suite or architecture! Whoo!

Notes about next week:

Last week I got carried away imagining minor improvements that can be made to the test websites UI, and I now have a backlog of ideas I’d like to implement. I’ve begun editing the script that makes most of the pages with statistics or package list (for example, all packages with notes, or all recently tested packages) to use templates and contain a bit more descriptive text. I’d also like to do a some revamping of the package set pages I converted.

These addition UI changes will be my first tasks for the coming week — since they are fresh on my mind and I’m quite excited about them. The following week I’d like to get back to extensibility and database issues mentioned previously!

Week 5 at DebCamp

I thought I would get a lot done for my outreachy project at Debian — but the incredible people of Debian too much of a distraction πŸ™‚ Every time I asked for help I’d up discussing bash, python, git or debian well beyond my original question.

Things I managed to do include:

  • Finish converting the script which makes the package set pages
  • Fix highlighting in the left hand navigation for pages build by python scripts (not yet deployed – coming soon! :))
  • Investigate and use a bash mustache rendering script which eventually Mattia veto’d in favor of using the python rendering script.

Now the debconf has properly started, I’m not sure how much more work I’ll get done.Β  At the very least I plan to replace the bash html with the mustache scripts I’ve created, garner opinions about navigation-related improvements to and talk to people about the existing database design.

Week 3..orrr 4* on Reproducible Builds

Today I am in Cape Town for DebCamp+Conf!

Yesterday I traveled here from Boston.

The previous four days (Mon-Thurs) I converted (almost entirely) the bash script that create the package set pages of to python. This involved writing more mustache templates, discovering old templates (read: strings of html) in multiple places and moving the package set definition to keep it DRY.

These changes are not yet deployed – here are things I’d like to fiish first:

1. Create a mustache template for the main left side navigation (the comment at the beginning of the bash function that presently builds this html: “this is really quite uncomprehensible and should be killed”). This navigation is used many places, including the front page. This is the only part of the package page html left to convert.

2. Use the mustache templates in the bash scripts where appropriate (there is a bash interpreter of mustache templates!). This will DRY up the code some more πŸ™‚

3. Add some navigation improvements to the package set pages. For example, from the package set page that shows you a summary of packages statuses on testing+amd64, you cannot navigate to the summary of the same package set on unstable+amd64.

4. Improve the main left hand navigation in point (2) above. Similar to the package page improvements, I’ll add hover text and headers and move things around.

*changing the number of the weeks to match other outreachy participants

Week 2 on Reproducible Builds

Here is a small update for last week’s work:

1. Templates now has a templating system, instead of writing HTML in python files πŸ™‚ We are using mustache with pystache. So far, only the html for the package pages have been converted.

2. Navigation improvements on package pages

While converting the package pages to using mustache I also rearranged the navigation bar, adding sections and hover text. Navigation improvements inspired by Gerts Wollney’s email requests.Β  Please check out the changes and provide feedback:<your-favorite-package>

3. Began converting script from bash to python

Before converting the rest of the site to mustache, I spent a day digging into the last unconverted bash script (all other html producing scripts are python). I’ve never written or read that much bash, yet another learning opportunity – I have to admit it’s a bit less intuitive than python πŸ˜‰

Up next:

Finish converting package set script, create more mustache templates, more site improvements! Then fly to South Africa.

Week 1 on Reproducible Builds

In this post I’m reviewing what I’ve done the last 6 days of Outreachy-funded reproducible builds work, outline what I plan to do the next two weeks, and speculate on long term goals. For those of you involved in the Debian reproducible builds project, please provide feedback about future plans and work!

Week One review

One week of Outreachy completed! What have I done?

  • Reproduced the reproducible builds tests website locally
  • Added information to the INSTALL file about reproducing the tests website (viewable here)
  • Checked in changes that broke nearly every link to the tests website
  • Fixed mosts of the broken links by adding redirects. (Please let me know if you find any!)

The change that broke everything was the addition of a directory:

The directory was added to contain all Debian-specific pages, in line with the other project’s reproducible builds status pages: arch linux, fedora, coreboot, etcs. Previously, all Debian pages we simply served directly out of the DocumentRoot. To fix all the broken things, I’m pretty sure I had to find, inspect, and addΒ  /debian or change global variables within every file pointer in the entire tests website. Sometime tedious, but chasing down bugs and complaints was mostly fun πŸ™‚

I also learned (everything I now know) about Apache websites, redirects, the website/navigation/directory structure of, and the roles of many of the reproducible scripts in

Week Two plan

What will or should I do next?

In the short term, over the next two weeks, I hope to make useful improvements to the tests website and backend while continuing to get up to speed (as well as learn Python).

  • Improve navigation on
    • fix highlighting in the nav bar on package pages
    • address navigation bar re-organizing requests like this one
    • add documentation/hovertext for links
  • Create a front page for the test reproducible builds site (update: probably not do this yet, low priority, already “too many front pages” for reproducible builds)
  • Convert bin/ to python

Have other thoughts about minor improvements to Please let me know! The above list is not internally prioritized, feel free to ask for things to be bubbled up.

Longer-term goals

My long term summer goal is to make the Debian test code more easily extensible to show the reproducible results from other projects. This will lower the barrier for new projects to keep track of the reproducibility of their code, for great good.

This starts with the reproducible.db database, which presently only tracks reproducible testing results for the Debian project.Β  The reproducible builds project’s needs have outgrown the original SQLight database, so this redesigning includes a migration to Postgre. Goals of the redesign include ease of querying/comparing packages across distributions, as well as generalization to include results from projects other than Debian. I’ll start on this work in two weeks, when I get to DebCamp! πŸ™‚

Redesigning the database will also lead to updating the python script which use that data to produce the Debian tests website. Other project scripts (like Fedora, RedHat and Coreboot) can then be updated to track results in the database as well, instead simply directly producing their own test websites.

update: as an intermediate step — before redesigned the reproducible.db database to handle multiple projects — h01ger recommended I help the FreeBSD project recorded tests to a FreeBSD specific database.

Summer of Reproducible Builds


Hello friend, family, fellow Outreachy participants, and the Debian community!

This blog's primary purpose will be to track the progress of the Outreachy project in which I'm participating this summer πŸ™‚  This post is to introduce myself and my project (working on the Debian reproducible builds project).

What is Outreachy? You might not know! Let me empower you: Outreachy is an organization connecting woman and minorities to mentors in the free (as in freedom) software community, /and/ funding for three months to work with the mentors and contribute to a free software project.  If you are a woman or minority human that likes free software, or if you know anyone in this situation, please tell them about Outreachy πŸ™‚ Or put them in touch with me, I'd happily tell them more.

So who am I?

My name is Valerie Young. I live in the Boston Metropolitan Area (any other outreachy participants here?) and hella love free software. 

Some bullet pointed Val facts in rough reverse chronological order:
- I run Debian but only began contributing during the Outreachy application process
- If you went to DebConf2015, you might have seen me dye nine people's hair blue, blond or Debian swirl.
- If you stop through Boston I could be easily convinced to dye your hair.
- I worked on electronic medical records web application for the last two years (lotsa Javascriptin' and Perlin' at athenahealth)
- Before that I taught a programming summer program at University of Moratuwain Sri Lanka.
- Before that I got a degrees in physics and computer science at Boston University.
- At BU I helped start a hackerspace where my interest in technology, free software, hacker culture, anarchy, the internet all began.
- I grew up in the very fine San Francisco Bay Area.

What will I be working on?

Reproducible builds!

In the near future I'll write a β€œWhat is reproducible builds? Why is it so hot right now?” post.  For now, from a high (and not technical) level, reproducible builds is a broad effort to verify that the computer executable binary programs you run on your computer come from the human readable source code they claim to. It is not presently /impossible/ to do this verification, but it's not easy, and there are a lot of nuanced computer quirks that make it difficult for the most experienced programmer and straight-up impossible for a user with no technical expertise. And without this ability to verify -- the state we are in now -- any executable piece of software could be hiding secret code. 

The first step towards the goal of verifiability is to make reproducibility a essential part of software development. Reproducible builds means this: when you compile a program from the source code, it should always be identical, bit by bit. If the program is always identical, you can compare your version of the software to any trusted programmer with very little effort. If it is identical, you can trust it -- if it's not, you have reason to worry.

The Debian project is undergoing an effort to make the entire Debian operating system verifiable reproducible (hurray!). My outreachy-funded summer contribution involves the improving and updating – a site that presently presently surfaces the results of reproducibility testing of several free software projects (including Debian, Fedora, coreboot, OpenWrt, NetBSD, FreeBSD and ArchLinux). However, the design of is a bit confusing, making it difficult for a user to find how to check on the reproducibility of a given package for one of the aforementioned projects, or understand the reasons for failure. Additional, the backend test results of Debian are outgrowing the original SQLite database, and many projects do not log the results of package testing at all. I hope, by the end of the summer, we'll have a more beefed-out and pretty site as well as better organized backend data πŸ™‚

This summer there will be 3 other Outreachy participants working on the Debian reproducible builds project! Check out their blogs/projects:

Thanks to our Debian mentors -- Lunar, Holger Levsen, and Mattia Rizzolo -- for taking us on πŸ™‚