Meinsack.click 2023 update

With the new year the city of Stuttgart changed the company collecting the recyclable waste (Gelber Sack). My version of this dataset (meinsack.click) should not break history as the Stuttgart city version does. So my solution was to crawl all calendar files from the new website and cluster them to get the 15 districts that have the same collection dates. Then match them to the old districts and verify a few of them manually.

The calendar files generated by the city website are not rfc5545 compliant and therefore couldn't be imported by the icspy library. I had to add the PRODID to the files after loading them like this:

response = httpx.get(
    "https://www.gelbersack-stuttgart.de/abfuhrplan/export/vaihingen-mitte",
    params={"type": 201}
)
data = response.text.split("\n")
# needed for ical file to be rfc5545 compliant
data.insert(1, "PRODID:-//placeholder//text//EN")
cal = Calendar("\n".join(data))

The results after clustering and mapping are a lot easier to import into a SQL database. No fiddling with manually created pdfs (and therefore very subtle differences) anymore.

Using password-store with fzf and zsh

I switched to pass completely after the lastpass meltdown a few weeks ago. But now I have a few hundred passwords in there and finding them is getting tedious.

Zsh (and oh-my-zsh) for the win here. The pass plugin is very helpful, but with more than one screen of passwords it is too much information at once. The next step was adding the fzf plugin and a snippet from the fzf wiki to my .zshrc. Now a simple pass edit **<TAB> opens a dialog to search for partial filenames and uses this file on pressing return.

I looked into alternatives before but I wanted to have the show and edit commands still usable and no additional magic happening.

Add plots to datasette pages using billboard.js

Continuing from a previous post, now it's time to draw the table filled with values.

As a showcase this post is available as a github project: https://github.com/mfa/showcase-datasette-billboardjs.

For plotting I used billboard.js the first time.

First version: use the API directly with the table

Every table in datasette has a direct link to get the table as csv with specified fields. I used this before, but this time I cannot filter the columns. So the rowid which is a primary-key is plotted. I found no solution within billboard.js to remove rowid from the plot.

Second version: use a SQL query

This worked as expected, but only after I found out that I need to use LIMIT instead of a size parameter. There is even a ticket about it: datasette#1200 - but I agree that LIMIT is good enough - when you know size doesn't work. On the positive side some calculations are possible here, i.e. multiply a value by 1000 because no fractions are wanted in the y-axis. An example query looks like this: /demo.csv?sql=SELECT+created%2C+delta_sec*1000+AS+delta+FROM+reports+ORDER+BY+created+DESC+LIMIT+100

This version is clearly the preferred one because no additional Python code is needed.

Third version: use jinja template and a prepared query

The "I have a hammer"-solution. Here a query is prepared in the plugins/index_vars.py and the data is generated using a jinja-for-loop in the template. The multiplication needed could be done in the SQL query or in the template (as shown in the showcase project). This solution works, but needs additional Python code in the plugins folder which could be avoided (see second version).