As well as changing the way I organise my writing, last year I made some cosmetic improvements to this site. I design everything on this site myself, and I write the CSS by hand – I don’t use any third-party styles or frameworks. I don’t have any design training, and I don’t do design professionally, so I use this site as a place to learn and practice my design skills. It’s a continual work-in-progress, but I’d like to think it’s getting better over time. I design this site for readers. I write long, text-heavy posts with the occasional illustration or diagram, so I want something that will be comfortable to read and look good on a wide variety of browsers and devices. I get a lot of that “for free” by using semantic HTML and the default styles – most of my CSS is just cosmetic. Let’s go through some of the changes. Cleaning up the link styles This is what links used to look like: Every page has a tint colour, and then I was deriving different shades to style different links – a darker shade for visited links, a lighter shade for visited links in dark mode, and a background that appears on hover. I’m generating these new colours programatically, and I was so proud of getting that code working that I didn’t stop to think whether it was a good idea. In hindsight, I see several issues. The tint colour is meant to give the page a consistent visual appearance, but the different shades diluted that effect. I don’t think their meaning was especially obvious. How many readers ever worked it out? And the hover styles are actively unhelpful – just as you hover over a link you’re interested in, I’m making it harder to read! (At least in light mode – in dark mode, the hover style is barely legible.) One thing I noticed is that for certain tint colours, the “visited” colour I generated was barely distinguishable from the text colour. So I decided to lean into that in the new link styles: visited links are now the same colour as regular text. This new set of styles feels more coherent. I’m only using one shade of the tint colour, and I think the meaning is a bit clearer – only new-to-you links will get the pop of colour to stand out from the rest of the text. I’m happy to rely on underlines for the links you’ve already visited. And when you hover, the thick underline means you can see where you are, but the link text remains readable. Swapping out the font I swapped out the font, replacing Georgia with Charter. The difference is subtle, so I’d be surprised if anyone noticed: I’ve always used web safe fonts for this site – the fonts that are built into web browsers, and don’t need to be downloaded first. I’ve played with custom fonts from time to time, but there’s no font I like more enough to justify the hassle of loading a custom font. I still like Georgia, but I felt it was showing its age – it was designed in 1993 to look good on low-resolution screens, but looks a little chunky on modern displays. I think Charter looks nicer on high-resolution screens, but if you don’t have it installed then I fall back to Georgia. Making all the roundrects consistent I use a lot of rounded rectangles for components on this site, including article cards, blockquotes, and code blocks. For a long time they had similar but not identical styles, because I designed them all at different times. There were weird inconsistencies. For example, why does one roundrect have a 2px border, but another one is 3px? These are small details that nobody will ever notice directly, but undermine the sense of visual together-ness. I’ve done a complete overhaul of these styles, to make everything look more consistent. I’m leaning heavily on CSS variables, a relatively new CSS feature that I’ve really come to like. Variables make it much easier to use consistent values in different rules. I also tweaked the appearance: I’ve removed another two shades of the tint colour. (Yes, those shades were different from the ones used in links.) Colour draws your attention, so I’m trying to use it more carefully. A link says “click here”. A heading says “start here”. What does a blockquote or code snippet say? It’s just part of the text, so it shouldn’t be grabbing your attention. I think the neutral background also makes the syntax highlighting easier to read, because the tint colour isn’t clashing with the code colours. I could probably consolidate the shades of grey I’m using, but that’s a task for another day. I also removed the left indent on blockquotes and code blocks – I think it looks nicer to have a flush left edge for everything, and it means you can read more text on mobile screens. (That’s where I really felt the issues with the old design.) What’s next? By tidying up the design and reducing the number of unique elements, I’ve got a bit of room to add something new. For a while now I’ve wanted a place at the bottom of posts for common actions, or links to related and follow-up posts. As I do more and more long-form, reflective writing, I want to be able to say “if you liked this, you should read this too”. I want something that catches your eye, but doesn’t distract from the article you’re already reading. Louie Mantia has a version of this that I quite like: I’ve held off designing this because the existing pages felt too busy, but now I feel like I have space to add this – there aren’t as many clashing colours and components to compete for your attention. I’m still sketching out designs – my current idea is my rounded rectangle blocks, but with a coloured border instead of a subtle grey, but when I did a prototype, I feel like it’s missing something. I need to try a few more ideas. Watch this space! [If the formatting of this post looks odd in your feed reader, visit the original article]

In my previous post, there was a first for this site: I embedded a post from Mastodon. Like many social media services, Mastodon has built-in support for embedding posts. If you’re looking at a public post, you can get a snippet of HTML and JavaScript to show that post in another web page. You add that snippet to your page, and when somebody opens it, the snippet will appear as a Mastodon post. It’s quick, easy, and not how I did it. When I want to embed post from social media sites, I don’t use the native embed. Instead, I write my own HTML and CSS to mimic their appearance, and it looks pretty close to the real thing. Here’s a comparison of a native/custom Mastodon embed – they’re not exactly the same, but close enough that you probably wouldn’t notice unless you were looking: This is something I’ve been doing for over a decade – I got the original idea from Dr Drang, who does something similar for tweets. (He wrote that post in 2012, and it highlights the value of resilient embeds – two of the four tweets he’s quoted are no longer available. The post would be harder to read if you couldn’t see the tweets he was quoting and replying to.) Many years ago, I copied Dr Drang’s code, created my own variant, and I used that for embedding tweets. I’ve now created another variant that works for Mastodon toots, and I have unfinished branches with more variants for Instagram and Bluesky. Why do I prefer my embeds? There are several reasons: My embeds are smaller and faster. Mastodon posts are short, and yet the native embed downloads nearly a megabyte of data to display 88 words of text – including the audio file boop.mp3, for reasons I can’t imagine. Meanwhile my custom embed requires just 35KB. I try to keep this site pretty lean and lightweight – the average size of an HTML page is just 13KB. Adding a megabyte of data for an embed would undo all that hard work. My embeds don’t require any JavaScript, third-party or otherwise. You don’t need JS to show static content, and adding third-party code introduces a privacy risk for my readers. I’m not completely opposed to JavaScript, but it’s massively overused on the modern web. It’s useful for interactive elements, but I really don’t need it on this content-only site. My embeds are more resilient. Because I have no dependency on the Mastodon server, it doesn’t matter if the server goes away or the toot is deleted. My page will be unaffected. This is why many people include social media posts as images, or copy the text into a blockquote. We’re in a time of increased tumult and instability for social media platforms, but their woes aren’t going to leave holes in my posts. My embeds support dark mode. A few years ago I added dark mode to this site. It’s not something I use myself, but I know it’s important to a lot of people and it was a fun little project. The native Mastodon embeds always show toots in light mode, whereas my embeds will adapt to your preference: On the other hand, the argument in favour of native embeds is that they need minimal effort, they should always work, and they support more features. My custom embeds can’t do pictures, or link previews, or quote toots, because I’ve never embedded a toot that uses those. If/when I do, I’ll have to write the code to support that. I’ll find that fun, but most people would find that annoying. I don’t know what accessibility is like for native embeds. My custom embeds only use a handful of semantic HTML elements, so they get a lot of good behaviour “by default from the browser. I hope native embeds are good for accessibility, but I don’t know enough to say whether my approach is better or worse in that regard. How does it work? I have some HTML and CSS that render the embedded toot. Here’s the entirety of the HTML – I’ve tweaked this ever so slightly for readability, but the key parts are there. <blockquote class="mastodon-embed"> <div class="header"> <a class="name_header" href="https://code4lib.social/@linguistory"> <img class="avatar" src="linguistory.jpg" alt=""> <div class="name"> <span class="display_name">James Truitt (he/him)</span> <span class="account_name">@linguistory@code4lib.social</span> </div> </a> <img class="mastodon_logo" src="logo.svg"> </div> <p class="text"> Do any <a href="https://code4lib.social/tags/digipres">#digipres</a> folks happen to have a handy repo of small invalid bags for testing purposes? <br> <br> I'm trying to automate our ingest process, and want to make sure I'm accounting for as many broken expectations as possible. </p> <p class="meta"> <a href="https://code4lib.social/@linguistory/113924700205617006">31 Jan 2025 at 19:49</a> </p> </blockquote> The CSS styles are a bit long to include here, but you can see them by reading the source code of my demo page. I’m using CSS grid layout to lay out the different components, but otherwise nothing too complicated. I designed my custom embed by creating two HTML files: one with a native embed, and one with my custom embed. I used the developer tools to get key values from the native embed, like colours and spacing, then I kept adding styles to my custom embed until it looked about right. When I want to embed a toot now, I write a line like: {% mastodon https://code4lib.social/@linguistory/113924700205617006 %} This calls a Jekyll plugin that replaces this line with an embedded toot. This code is very scrappy and poorly documented, so it may not be especially easy to adapt to your own site – if you want to do this, start from the HTML and CSS instead. Like everything on this site, my Mastodon embeds are a work-in-progress and not something that everybody should copy. The built-in embeds are quick, easy, and convenient, and they’re what most people should use. But what I like about having my own website is that when I do want to spend an unreasonable amount of effort on something, and do it just because I think it’s fun, I can do that, and nobody can stop me. [If the formatting of this post looks odd in your feed reader, visit the original article]

Last week, James Truitt asked a question on Mastodon: James Truitt (he/him) @linguistory@code4lib.social Mastodon #digipres folks happen to have a handy repo of small invalid bags for testing purposes? I'm trying to automate our ingest process, and want to make sure I'm accounting for as many broken expectations as possible. Jan 31, 2025 at 07:49 PM The “bags” he’s referring to are BagIt bags. BagIt is an open format developed by the Library of Congress for packaging digital files. Bags include manifests and checksums that describe their contents, and they’re often used by libraries and archives to organise files before transfering them to permanent storage. Although I don’t use BagIt any more, I spent a lot of time working with it when I was a software developer at Wellcome Collection. We used BagIt as the packaging format for files saved to our cloud storage service, and we built a microservice very similar to what James is describing. The “bag verifier” would look for broken bags, and reject them before they were copied to long-term storage. I wrote a lot of bag verifier test cases to confirm that it would spot invalid or broken bags, and that it would give a useful error message when it did. All of the code for Wellcome’s storage service is shared on GitHub under an MIT license, including the bag verifier tests. They’re wrapped in a Scala test framework that might not be the easiest thing to read, so I’m going to describe the test cases in a more human-friendly way. Before diving into specific examples, it’s worth remembering: context is king. BagIt is described by RFC 8493, and you could create invalid bags by doing a line-by-line reading and deliberately ignoring every “MUST” or “SHOULD” but I wouldn’t recommend this aproach. You’d get a long list of test cases, but you’d be overwhelmed by examples, and you might miss specific requirements for your system. The BagIt RFC is written for the most general case, but if you’re actually building a storage service, you’ll have more concrete requirements and context. It’s helpful to look at that context, and how it affects the data you want to store. Who’s creating the bags? How will they name files? Where are you going to store bags? How do bags fit into your wider systems? And so on. Understanding your context will allow you to skip verification steps that you don’t need, and to add verification steps that are important to you. I doubt any two systems implement the exact same set of checks, because every system has different context. Here are examples of potential validation issues drawn from the BagIt specification and my real-world experience. You won’t need to check for everything on this list, and this list isn’t exhaustive – but it should help you think about bag validation in your own context. The Bag Declaration bagit.txt This file declares that this is a BagIt bag, and the version of BagIt you’re using (RFC 8493 §2.1.1). It looks the same in almost every bag, for example: BagIt-Version: 1.0 Tag-File-Character-Encoding: UTF-8 This tightly prescribed format means it can only be invalid in a few ways: What if the bag doesn’t have a bag declaration? It’s a required element of every BagIt bag; it has to be there. What if the bag declaration is the wrong format? It should contain exactly two lines: a version number and a character encoding, in that order. What if the bag declaration has an unexpected version number? If you see a BagIt version that you’ve not seen before, the bag might have a different structure than what you expect. The Payload Files and Payload Manifest The payload files are the actual content you want to save and preserve. They get saved in the payload directory data/ (RFC 8493 §2.1.2), and there’s a payload manifest manifest-algorithm.txt that lists them, along with their checksums (RFC 8493 §2.1.3). Here’s an example of a payload manifest with MD5 checksums: 37d0b74d5300cf839f706f70590194c3 data/waterfall.jpg This tells us that the bag contains a single file data/waterfall.jpg, and it has the MD5 checksum 37d0…. These checksums can be used to verify that the files have transferred correctly, and haven’t been corrupted in the process. There are lots of ways a payload manifest could be invalid: What if the bag doesn’t have a payload manifest? Every BagIt bag must have at least one Payload Manifest file. What if the payload manifest is the wrong format? These files have a prescribed format – one file per line, with a checksum and file path. What if the payload manifest refers to a file that isn’t in the bag? Either one of the files in the bag has been deleted, or the manifest has an erroneous entry. What if the bag has a file that isn’t listed in the payload manifest? The manifest should be a complete listing of all the payload files in the bag. If the bag has a file which isn’t in the payload manifest, either that file isn’t meant to be there, or the manifest is missing an entry. Checking for unlisted files is how I spotted unwanted .DS_Store and Thumbs.db files. What if the checksum in the payload manifest doesn’t match the checksum of the file? Either the file has been corrupted, or the checksum is incorrect. What if there are payload files outside the data/ directory? All the payload files should be stored in data/. Anything outside that is an error. What if there are duplicate entries in the payload manifest? Every payload file must be listed exactly once in the manifest. This avoids ambiguity – suppose a file is listed twice, with two different checksums. Is the bag valid if one of those checksums is correct? Requiring unique entries avoids this sort of issue. What if the payload directory is empty? This is perfectly acceptable in the BagIt RFC, but it may not be what you want. If you know that you will always be sending bags that contain files, you should flag empty payload directories as an error. What if the payload manifest contains paths outside data/, or relative paths that try to escape the bag? (e.g. ../file.txt) Now we’re into “malicious bag” territory – a bag uploaded by somebody who’s trying to compromise your ingest pipeline. Any such bags should be treated with suspicion and rejected. If you’re concerned about malicious bags, you need a more thorough test suite to catch other shenanigans. We never went this far at Wellcome Collection, because we didn’t ingest bags from arbitrary sources. The bags only came from internal systems, and our verification was mainly about spotting bugs in those systems, not defending against malicious actors. A bag can contain multiple payload manifests – for example, it might contain both MD5 and SHA1 checksums. Every payload manifest must be valid for the overall bag to be valid. Payload filenames There are lots of gotchas around filenames and paths. It’s a complicated problem, and I definitely don’t understand all of it. It’s worth understanding the filename rules of any filesystem where you will be storing bags. For example, Azure Blob Storage has a number of rules around how you can name files, and Amazon S3 has different rules. We stored files in both at Wellcome Collection, and so the storage service had to enforce the superset of these rules. I’ve listed some edge cases of filenames you might want to consider, but it’s not a comlpete list. There are lots of ways that unexpected filenames could cause you issues, but whether you care depends on the source of your bags. If you control the bags and you know you’re not going to include any weird filenames, you can probably skip most of these. We only checked for one of these conditions at Wellcome Collection, because we had a pre-ingest step that normalised filenames. It converted filenames to ASCII, and saved a mapping between original and normalised filename in the bag. However, the normalisation was only designed for one filesystem, and produced filenames with trailing dots that were still disallowed in Azure Blob. What if a filename is too long? Some systems have a maximum path length, and an excessively deep directory structure or long filename could cause issues. What if a filename contains special characters? Spaces, emoji, or special characters (\, :, *, etc.) can cause problems for some tools. You should also think about characters that need to be URL-encoded. What if a filename has trailing spaces or dots? Some filesystems can’t support filenames ending in a dot or a space. What happens if your bag contains such a file, and you try to save it to the filesystem? This caused us issues at Wellcome Collection. We initially stored bags just in Amazon S3, which is happy to take filenames with a trailing dot – then we added backups to Azure Blob, which doesn’t. One of the bags we’d stored in Amazon S3 had a trailing dot in the filename, and caused us headaches when we tried to copy it to Azure. What if a filename contains a mix of path separators? The payload manifest uses a forward slash (/) as a path separator. If you have a filename with an alternative path separator, it might behave differently on different systems. For example, consider the payload file a\b\c. This would be a single file on macOS or Linux, but it would be nested inside two folders on Windows. What if the filenames are a mix of uppercase and lowercase characters? Some fileystems are case-sensitive, others aren’t. This can cause issues when you move bags between systems. For example, suppose a bag contains two different files Macrodata.txt and macrodata.txt. When you save that bag on a case-insensitive filesystem, only one file will be saved. What if the same filename appears twice with different Unicode normalisations? This is similar to filenames which only differ in upper/lowercase. They might be treated as two files on one filesystem, but collapsed into one file on another. The classic example is the word “café”: this can be encoded as caf\xc3\xa9 (UTF-8 encoded é) or cafe\xcc\x81 (e + combining acute accent). What if a filename contains a directory reference? A directory reference is /./ (current directory) or /../ (parent directory). It’s used on both Unix and Windows-like systems, and it’s another case of two filenames that look different but can resolve to the same path. For example: a/b, a/./b and a/subdir/../b all resolve to the same path under these rules. This can cause particular issues if you’re moving between local filesystems and cloud storage. Local filesystems treat filenames as hierarchical paths, where cloud storage like Amazon S3 often treats them as opaque strings. This can cause issues if you try to copy files from cloud storage to a local system – if you’re not careful, you could lose files in the process. The Tag Manifest tagmanifest-algorithm.txt Similar to the payload manifest, the tag manifest lists the tag files and their checksums. A “tag file” is the BagIt term for any metadata file that isn’t part of the payload (RFC 8493 §2.2.1). Unlike the payload manifest, the tag manifest is optional. A bag without a tag manifest can still be a valid bag. If the tag manifest is present, then many of the ways that a payload manifest can invalidate a bag – malformed contents, unreferenced files, or incorrect checksums – can also apply to tag manifests. There are some additional things to consider: What if a tag manifest lists payload files? The tag manifest lists tag files; the payload manifest lists payload files in the data/ directory. A tag manifest that lists files in the data/ directory is incorrect. What if the bag has a file that isn’t listed in either manifest? Every file in a bag (except the tag manifests) should be listed in either a payload or a tag manifest. A file that appears in neither could mean an unexpected file, or a missing manifest entry. Although the tag manifest is optional in the BagIt spec, at Wellcome Collection we made it a required file. Every bag had to have at least one tag manifest file, or our storage service would refuse to ingest it. The Bag Metadata bag-info.txt This is an optional metadata file that describes the bag and its contents (RFC 8493 §2.2.2). It’s a list of metadata elements, as simple label-value pairs, one per line. Here’s an example of a bag metadata file: Source-Organization: Lumon Industries Organization-Address: 100 Main Street, Kier, PE, 07043 Contact-Name: Harmony Cobel Unlike the manifest files, this is primarily intended for human readers. You can put arbitrary metadata in here, so you can add fields specific to your organisation. Although this file is more flexible, there are still ways it can be invalid: What if the bag metadata is the wrong format? It should have one metadata entry per line, with a label-value pair that’s separated by a colon. What if the Payload-Oxum is incorrect? The Payload-Oxum contains some concise statistics about the payload files: their total size in bytes, and how many there are. For example: Payload-Oxum: 517114.42 This tells us that the bag contains 42 payload files, and their total size is 517,114 bytes. If these stats don’t match the rest of the bag, something is wrong. What if non-repeatable metadata element names are repeated? The BagIt RFC defines a small number of reserved metadata element names which have a standard meaning. Although most metadata element names can be repeated, there are some which can’t, because they can only have one value. In particular: Bagging-Date, Bag-Size, Payload-Oxum and Bag-Group-Identifier. Although the bag metadata file is optional in a general BagIt bag, you may want to add your own rules based on how you use it. For example, at Wellcome Collection, we required all bags to have an External-Identifier value, that matched a specific schema. This allowed us to link bags to records in other databases, and our bag verifier would reject bags that didn’t include it. The Fetch File fetch.txt This is an optional element that allows you to reference files stored elsewhere (RFC 8493 §2.2.3). It tells the person reading the bag that a file hasn’t been included in this copy of the bag; they have to go and fetch it from somewhere else. The file is still recorded in the payload manifest (with a checksum you can verify), but you don’t have a complete bag until you’ve downloaded all the files. Here’s an example of a fetch.txt: https://topekastar.com/~daria/article.txt 1841 data/article.txt This tells us that data/article.txt isn’t included in this copy of the bag, but we we can download it from https://topekastar.com/~daria/article.txt. (The number 1841 is the size of the file in bytes. It’s optional.) Using fetch.txt allows you to send a bag with “holes”, which saves disk space and network bandwidth, but at a cost – we’re now relying on the remote location to remain available. From a preservation standpoint, this is scary! If topekastar.com goes away, this bag will be broken. I know some people don’t use fetch.txt for precisely this reason. If you do use fetch.txt, here are some things to consider: What if the fetch file is the wrong format? There’s a prescribed format – one file per line, with a URL, optional file size, and file path. What if the fetch file lists a file which isn’t in the payload manifest? The fetch.txt should only tell us that a file is stored elsewhere, and shouldn’t be introducing otherwise unreferenced files. If a file appears in fetch.txt but not the payload manifest, then we can’t verify the remote file because we don’t have a checksum for it. There’s either an erroneous fetch file entry or a missing manifest entry. What if the fetch file points to a file at an unusable URL? The URL is only useful if the person who receives the bag can use it to download the file. If they can’t, the bag might technically be valid, but it’s functionally broken. For example, you might reject URLs that don’t start with http:// or https://. What if the fetch file points to a file with the wrong length? The fetch.txt can optionally specify the size of a file, so you know how much storage you need to download it. If you download the file, the actual size should match the stated size. What if the fetch files points to a file that’s already included in the bag? Now you have two ways to get this file: you can read it from the bag, or from the remote URL. If a file is listed in both fetch.txt and included in the bag, either that file isn’t meant to be in the bag, or the fetch file has an erroneous entry. We used fetch files at Wellcome Collection to implement versioning, and we added extra rules about what remote URLs were allowed. In particular, we didn’t allow fetching a file from just anywhere – you could fetch from our S3 buckets, but not the general Internet. The bag verifier would reject a fetch file entry that pointed elsewhere. These examples illustrate just how many ways a BagIt bag can be invalid, from simple structural issues to complex edge cases. Remember: the key is to understand your specific needs and requirements. By considering your context – who creates your bags, where they’ll be stored, and how they fit into your wider systems – you can build a validation process to catch the issues that matter to you, while avoiding unnecessary complexity. I can give you my ideas, but only you can build your system. [If the formatting of this post looks odd in your feed reader, visit the original article]

One of my recent home organisation projects has been sorting out my LEGO collection. I have a bunch of sets which are mixed together in one messy box, and I’m trying to separate bricks back into distinct sets. My collection is nowhere near large enough to be worth sorting by individual parts, and I hope that breaking down by set will make it all easier to manage and store. I’ve been creating spreadsheets to track the parts in each set, and count them out as I find them. I briefly hinted at this in my post about looking at images in spreadsheets, where I included a screenshot of one of my inventory spreadsheets: These spreadsheets have been invaluable – I can see exactly what pieces I need, and what pieces I’m missing. Without them, I wouldn’t even attempt this. I’m about to pause this cleanup and work on some other things, but first I wanted to write some notes on how I’m creating these spreadsheets – I’ll probably want them again in the future. Getting a list of parts in a set There are various ways to get a list of parts in a LEGO set: Newer LEGO sets include a list of parts at the back of the printed instructions You can get a list from LEGO-owned website like LEGO.com or BrickLink There are community-maintained databases on sites like Rebrickable I decided to use the community maintained lists from Rebrickable – they seem very accurate in my experience, and you can download daily snapshots of their entire catalog database. The latter is very powerful, because now I can load the database into my tools of choice, and slice and dice the data in fun and interesting ways. Downloading their entire database is less than 15MB – which is to say, two-thirds the size of just opening the LEGO.com homepage. Bargain! Putting Rebrickable data in a SQLite database My tool of choice is SQLite. I slept on this for years, but I’ve come to realise just how powerful and useful it can be. A big part of what made me realise the power of SQLite is seeing Simon Willison’s work with datasette, and some of the cool things he’s built on top of SQLite. Simon also publishes a command-line tool sqlite-utils for manipulating SQLite databases, and that’s what I’ve been using to create my spreadsheets. Here’s my process: Create a Python virtual environment, and install sqlite-utils: python3 -m venv .venv source .venv/bin/activate pip install sqlite-utils At time of writing, the latest version of sqlite-utils is 3.38. Download the Rebrickable database tables I care about, uncompress them, and load them into a SQLite database: curl -O 'https://cdn.rebrickable.com/media/downloads/colors.csv.gz' curl -O 'https://cdn.rebrickable.com/media/downloads/parts.csv.gz' curl -O 'https://cdn.rebrickable.com/media/downloads/inventories.csv.gz' curl -O 'https://cdn.rebrickable.com/media/downloads/inventory_parts.csv.gz' gunzip colors.csv.gz gunzip parts.csv.gz gunzip inventories.csv.gz gunzip inventory_parts.csv.gz sqlite-utils insert lego_parts.db colors colors.csv --csv sqlite-utils insert lego_parts.db parts parts.csv --csv sqlite-utils insert lego_parts.db inventories inventories.csv --csv sqlite-utils insert lego_parts.db inventory_parts inventory_parts.csv --csv The inventory_parts table describes how many of each part there are in a set. “Set S contains 10 of part P in colour C.” The parts and colors table contains detailed information about each part and color. The inventories table matches the official LEGO set numbers to the inventory IDs in Rebrickable’s database. “The set sold by LEGO as 6616-1 has ID 4159 in the inventory table.” Run a SQLite query that gets information from the different tables to tell me about all the parts in a particular set: SELECT ip.img_url, ip.quantity, ip.is_spare, c.name as color, p.name, ip.part_num FROM inventory_parts ip JOIN inventories i ON ip.inventory_id = i.id JOIN parts p ON ip.part_num = p.part_num JOIN colors c ON ip.color_id = c.id WHERE i.set_num = '6616-1'; Or use sqlite-utils to export the query results as a spreadsheet: sqlite-utils lego_parts.db " SELECT ip.img_url, ip.quantity, ip.is_spare, c.name as color, p.name, ip.part_num FROM inventory_parts ip JOIN inventories i ON ip.inventory_id = i.id JOIN parts p ON ip.part_num = p.part_num JOIN colors c ON ip.color_id = c.id WHERE i.set_num = '6616-1';" --csv > 6616-1.csv Here are the first few lines of that CSV: img_url,quantity,is_spare,color,name,part_num https://cdn.rebrickable.com/media/parts/photos/9999/23064-9999-e6da02af-9e23-44cd-a475-16f30db9c527.jpg,1,False,[No Color/Any Color],Sticker Sheet for Set 6616-1,23064 https://cdn.rebrickable.com/media/parts/elements/4523412.jpg,2,False,White,Flag 2 x 2 Square [Thin Clips] with Chequered Print,2335pr0019 https://cdn.rebrickable.com/media/parts/photos/15/2335px13-15-33ae3ea3-9921-45fc-b7f0-0cd40203f749.jpg,2,False,White,Flag 2 x 2 Square [Thin Clips] with Octan Logo Print,2335pr0024 https://cdn.rebrickable.com/media/parts/elements/4141999.jpg,4,False,Green,Tile Special 1 x 2 Grille with Bottom Groove,2412b https://cdn.rebrickable.com/media/parts/elements/4125254.jpg,4,False,Orange,Tile Special 1 x 2 Grille with Bottom Groove,2412b Import that spreadsheet into Google Sheets, then add a couple of columns. I add a column image where every cell has the formula =IMAGE(…) that references the image URL. This gives me an inline image, so I know what that brick looks like. I add a new column quantity I have where every cell starts at 0, which is where I’ll count bricks as I find them. I add a new column remaining to find which counts the difference between quantity and quantity I have. Then I can highlight or filter for rows where this is non-zero, so I can see the bricks I still need to find. If you’re interested, here’s an example spreadsheet that has a clean inventory. It took me a while to refine the SQL query, but now I have it, I can create a new spreadsheet in less than a minute. One of the things I’ve realised over the last year or so is how powerful “get the data into SQLite” can be – it opens the door to all sorts of interesting queries and questions, with a relatively small amount of code required. I’m sure I could write a custom script just for this task, but it wouldn’t be as concise or flexible. [If the formatting of this post looks odd in your feed reader, visit the original article]