The Winamp Skin Museum is powered by a SQLite3 database with 1.2GB of metadata | Hacker News Reader

The Winamp Skin Museum is powered by a SQLite3 database with 1.2GB of metadata

twitter.com

293 pointstosh110 comments

Webamp is quite amazing. I use it on my desktop environment website. I recently added playlist support (m3u/pls/asx) and integrated lazy(er) Butterchurn so it can run as the wallpaper. Winamp/Milkdrop forever!

https://dustinbrett.com/

this is insanely cool! it's hard to imagine wasm stuff has come this far

Speaking specifically about Webamp, it is a manual reimplementation in JavaScript. But we did use Wasm as a tool for the music visualizer: https://jordaneldredge.com/blog/speeding-up-winamps-music-vi...

that's awesome, I'll check it out. hopefully the WebAudioAPI has gotten better to work with over the years

The web audio api code is mostly in this directory: https://github.com/captbaritone/webamp/tree/master/packages/...

Amazing stuff! Especially the https://github.com/captbaritone/eel-wasm part. It's great to be able to salvage all this internet history and keep it alive and going.

How I wish I could listen to Spotify in a Winamp client....

Me too.

This was a thing nearly 10 years ago on windows, not sure if it still works

https://spotiamp-lightweight-spotify-player.en.uptodown.com/...

Webamp has been used to do something like this: https://winampify.io/

(Required paid Spotify account)

That is almost perfect, except it requires a browser tab emulating the full desktop. I'd love to have just the standalone Winamp window

Wow, your website is insane. Great work.

Thanks! Adding stuff like Webamp is my favorite part of making my website.

It is really cool to be able to open a browser in your desktop and visit your website, recursively. How many levels deep have you tested this?

I was only able to go 1 level deep. I could open the top level browser and load the site which allowed me to open another browser but that wouldn't load the site.

I tried on my phone, and found that I ran out of screen space before it stopped working.

It was interesting, seeing all of the clocks tick, one above another, second after second.

Properly coded, there is no reason to assume, that it would not work for an arbitrary depth. Considering, that this is on a website, and that it is running JS, which in most implementations does not implement tail call elimination, coding it properly probably involves externalizing the stack.

The browser is just an iframe, so tail call optimization isn’t relevant.

https://github.com/DustinBrett/daedalOS/blob/b1681546a2ce990...

Seems like a great use case for static sqlite hosting https://github.com/phiresky/sql.js-httpvfs

The love affair of Hackernews and SQLite continues ;)

Thought it was TB but then was just GB.

I remember reading about a company that upgraded their production database from a JSON file to SQLite.

We really need this counter culture in software development that emphasis simplicity over the "Start with Kafka-on-k8s" madness.

Tailscale https://tailscale.com/blog/database-for-2022/

But tailscale is a distributed, multi-region SaaS provider. It's easy for them to use SQLite.

What about a real-world workload? For example, I have 10 users a day on my new next.js app so I clearly need a RDS cluster for burst traffic.

Tailscale has some server components (account management etc.) that are powered with SQLite.

Can read more here: https://tailscale.com/blog/database-for-2022/

One of my websites has been using SQLite3 as a backend database since 2018ish and works perfectly fine.

I mean, aws makes it quite easy to run postgres so I don't see why not either, you don't need a dba more or less than with SQLite + rqlite. If you wanna run onprem you can usually do with one container for quite awhile too. I wouldn't call postgres a "complex dependency" anymore.

The existing replies to this comment of yours make it apparent why “/s” is still necessary :)

I find it hard to believe that someone would mistake the comment as not sarcastic if they read the second paragraph.

So if people comment based only on the first paragraph, would they even get to the /s in the end ?

I thought that SQLlite databases are not suitable for large multi user websites. Something got to do with only being able to handles one transaction at a time. Isn’t that right?

I think this is kind of their point. If you're starting out, you may not need to meet the same considerations as a "large multi user website". So you don't get stuck trying to do that - you start with what you need (appropriately) and adjust down the line.

It is hard to do right.

The problem is you have SQLite which works some of the time and Postgres which works all of the time. Postgres is a great option for websites of all sizes. There is vanishingly few reasons to ever pick SQLite for a website. When the only real advantage is not having to run a second process which only really matters for local apps.

SQLlite can do handle concurrent reads, writes are a problem.

A site like this which is mostly a read-only archive is a perfect use case for SQLite.

Sure, but you can have raised millions with large numbers of customers before this is an issue. Having multiple customers using your platform at the same time to the extent that their transactions actually end up having to wait a meaningful amount of time requires a lot of usage that you often don’t reach for a long long time.

Or just having any long OLAP queries mixed into your OLTP workload. A readers-writer lock can often end up with tons of writers waiting on one last long read to drain.

SQLite WAL mode means readers and writer don't block each other: https://www.sqlite.org/wal.html

Since it's SQLite, it's also trivial to just copy the database file as a snapshot for separate OLAP queries if necessary.

Not all OLAP queries are for reporting. Sometimes they drive OLTP processes — really complex, separate-statements but same-transaction equivalents to "INSERT/UPDATE/DELETE ... WHERE" queries.

Of course, you can still drive those using a separate snapshot for the read side, to generate an ID set for a follow-up write on the business layer. But now you're doing CQRS — less trivial. You're not getting any of the ACID benefits of doing the OLAP and OLTP queries together in a single transaction; so your data model has to be designed for that (by e.g. having all tables be temporal tables.)

This is sounds pretty great. Is there though that much of a dev ramp up increase from using SQLite over MySQL/PGSQL though?

SQLite only allows one writer at a time. Attempts at multiple DML logins will not block until successful, but simply fail.

The allowed writer can also impact readers, depending on the WAL mode setting.

For DSS uses, where a data store is published once a day, SQLite is wonderful. For OLTP, look elsewhere.

> Attempts at multiple DML logins will not block until successful, but simply fail.

sqlite has a "busy handler" mechanism which tells it how to respond to blocks and most apps use the builtin one with which they can tell it "wait for X milliseconds before failing on a lock."

The Fossil SCM (https://fossil-scm.org), the SCM in which sqlite itself is hosted, is an sqlite application which serves thousands of users per day, many of them in write transactions. Its forum is a separate sqlite3 instance. sqlite3's own forum is another... all of those serve many users, many of whom are writing, and in 14 years of using/contributing to fossil-scm.org, almost on a daily basis, i've hit exactly two locking errors.

The Single Writer Principle is probably the most important principle for concurrent systems design. Often it’s best to design wholly around it.

In this case, I doubt there are any writers at all.

I haven't gotten much past the planning phase but I'm thinking it would be a pretty good fit to use Erlang (Elixir in my case) and sqlite together, which should make SWP pretty straightforward, which in addition to offloading some of the concurrency work off of sqlite, also should make it fairly straightforward to swap out for postgres. If in fact I ever hit that point.

Phoenix now has sqlite3 as an option when running phx.new. I just played with it yesterday to also checkout litespeed syncing the database to backblaze. It was very easy to setup and test by running phx.gen.auth and monitoring the data being synced to b2. There is a blog post about running SQLite in dev and Postgres in production using Phoenix, much like rails does. It’s worth spending a few minutes checking it all out. I have a project in mind where I think it would be a good fit.

Define large. I'd guess sqlite can do 10-100K TPS, no problem.

You'll want a read cache to avoid repeatedly rendering the same HTML, etc in the frontend, where most CPU is spent. That'll accidentally reduce database load.

Assuming 10% writes (which is really, really write heavy), that'll get you to 1M page views per second. After that, you'll need to rearchitect.

(MySQL and Postgres are probably better choices, but I'd evaluate all three if I was setting this up for real.)

Just because you can use something doesn’t mean you should use it.

If you have 10k writes a second and 90k reads a second then SQLite is an awful choice.

Have you benchmarked it?

Given modern commodity servers and no network overhead I’d buy it not being a problem.

My intuition is you are right but given the operational simplicity improvements it would be worth exploring.

This. Like I said, postgres or mysql are probably better choices. However, modern hardware (especially small sync disk writes) is really fast,and sqlite has its own strong points.

With the amount of memory these days, the whole SQLite db would be cached in memory. Read heavy application is not a problem at all.

Yeah at that point you're basically as fast as redis or any other good binary tree datastructure store.

Somewhere along the way, we seem to have forgotten that we have processors that can do 3 billion mathematical operations per second.

I'm currently trying to shame some people at work into acting on the fact that they wrote some code that should take about 1µs per call that is instead taking over 100. If you're stupid with cycles then you get to be stupid with cores too, and then you get to be stupid with locking semantics.

Unless you are working in aerospace, military, or similar shaming people seems uncouth and perhaps not the best way to inspire meaningful change?

Shame is somewhere around #5 on a list, and never for subordinates.

Also I’ve worked on commercial aviation software, and maybe three of us even knew the meaning of the word, so I’m curious what domain you saw it in.

If its mostly read then it's fine

Context: I see a bunch of people here recommending SQLite. I have a suggestion, try out LMDB. It's kind of like noSQL SQLite. It's a simple in-process K/V store with a few features like compound keys that allow you to model relational data well enough.

I recently used lmdb for webhighlighter.com (specifically the wrapper: https://www.npmjs.com/package/node-lmdb), and it was a fantastic decision.

A lot of people here say "use SQLite for small projects". But even using SQLite can be significant over-engineering. Running migrations? Writing SQL? That's too much effort for me.

For example: In my application, people can leave comments on a (what is effectively) a post. A SQL-native solution might have a table for comments, with foreign keys to post IDs. That's 10x more engineering then I want to do for an MVP. I just store all the comments as an array on a post. This means reads read all the comments and writes require reading all the comments, appending, and then re-writing. That's totally fine, and will probably scale me to 100x my current traffic.

LMDB-JS is great. It allows you to serialize arbitrary JS objects to LMDB using the message pack encoding system. This makes for some super concise code.

Here's my entire data layer: - Interface: https://github.com/vedantroy/grape-juice/blob/main/site/app/... - Implementation: https://github.com/vedantroy/grape-juice/blob/main/site/app/...

TL;DR -- I won't use SQLite, for, I don't know, my first 10K users?

Flashback to when leveldb was hot in the node ecosystem

KV stores are fun but you end up writing a lot of queries and indexes manually

> But even using SQLite can be significant over-engineering. Running migrations? Writing SQL? That's too much effort for me.

Key-value stores are not a replacement for SQL, they solve a simpler problem. If you actually can do with a key value store I would argue a SQL table with two columns key and value with key being a primary key will do just fine. And using it needs simple select and 'insert on duplicate key update' statements. The effort for migrations won't be higher than whatever you had to configure for your key-value store.

However, if you can't actually do with a key-value store you will end up writing a lot of the features SQL provides in application code. Indices, joins, grouping, ordering, etc. You might not notice it at first, but you will blow up complexity in your business logic reinventing existing SQL features and chances are high you're doing it worse and less performant than what e.g. Postresql offers out of the box.

SQL is just such a powerful tool that is at the same time incredibly easy to use for simple scenarios.

Added benefit of using SQLite, you can migrate to a more powerful SQL database later without having to reengineer your whole data layer.

You can get pretty far using LMDB, specifically, its compound-key feature is quite good, and gets you a decent chunk of the relational model. Combined with Message Pack encoding, you can get an extremely concise data layer.

https://github.com/kriszyp/lmdb-js/discussions/170

I think there's a reason Tail Scale used a literal JSON file for up till 150 MB. of data: https://tailscale.com/blog/an-unlikely-database-migration/

So, I think the unconventional wisdom is correct.

Have you seen SQLightning and/or LumoSQL?

* https://github.com/LMDB/sqlightning

* https://github.com/LumoSQL/LumoSQL

SQLightning was the initial project combining SQLite3 with an LMDB backend. It seemed to be more an experimental/Proof-of-Concept thing, and isn't maintained.

LumoSQL is an alternative project (maintained), providing a SQLite3 front end with various optional storage backends. One of which is LMDB.

Note - I'm not affiliated with either project, I just remembered they exist. :)

It is very cool that this is archived. I was the application skin admin over at deviantArt and Deskmod way back in the day (wild times, pre-Web 2.0 so everybody was just making it up as they go) but sadly none of these names ring a bell. I think Deskmod just piddled out and dA got bought and the original crew all left. I lived in the middle of nowhere so being exposed to that level of programming as well as passion for art was extremely inspiring. I even made a punk music spinoff called I Hate Music that introduced me to lifelong friends all over the world.

how many llamas and whips can you fit in 1.2GB?

all of them apparently

it really does kick the llama's *ss

I have built a graph-based knowledge management system (https://github.com/brettkromkamp/contextualise) on top of SQLite. It runs great. Also, from a management point of view (e.g., deployments, backups) its ease of use is second to none. I migrated the application from PostgreSQL (which is also a great RDBMS) to SQLite and I haven’t looked back.

I love the webamp website. But, in the world of multigig RAM and multigbps I/O speeds, 1.2GB isn't that much. Not sure if that's something novel for SQLite3 though.

unlike a webserver setup maybe 20 years ago now it's trivially easy to hold even a 30GB database entirely in RAM and never touch disk, for a vast performance increase.

I think I saw a used x86-64 server with 512GB of RAM for sale for $1700 recently.

> I think I saw a used x86-64 server with 512GB of RAM for sale for $1700 recently.

Where? Sounds like a terrific bargain, my old supermicro only has 384GB.

ebay, was a dell r630 with two somewhat older 14-core xeons, 28 cores

You could pick up an R620 with 768GB Ram and 2 8 core Xeons for around 1400GBP (https://www.bargainhardware.co.uk/dell-poweredge-r620-10-sff...) without disks... DDR4 is a bit more expensive... but DDR3 is still fast for a lot of jobs...

R620 is a problem for anything that needs high bandwidth networking, however, because the pci-e slots are only 2.0. ruling out the use of many 10GbE interfaces or things like two port 100GbE

Since the 1970's Unix has held frequently used pages in cache. Still does!

not the same thing as tuning the db settings to specifically cache the whole thing in RAM and maybe even making its disk storage live on a RAM drive that's created at boot.

Time to revive the sticky bit!

That's mlock() and it does already exist. People really like to reinvent things their OS already has because they're one step too far above the kernel to use them - like building a web service to send files between two internal servers when you could use NFS.

If only this were true for S3 and friends.

(It is true for clients of scale out NFS servers, for what it's worth.)

If you don't care much about the data, no harm putting it in /dev/shm :)

Not novel at all. SQLite3 can handle 100GB+ files on fast storage without breaking a sweat.

Yeah, I have a project with about this much data, and literally read the whole file into memory, and rewrite the whole file on disk every time I need to modify it. It's hilarious that it works.

Wait, is that just how SQlite works? It can't update an entry on disk, it just has to rewrite the whole file...?

It writes in a file, but only the part needed, not the whole file (like any other DB with persistence)

It can also use a WAL log file and periodically checkpoint to the actual db

No, they're saying they've implemented something that's even more simple than an embedded database and it still works fine for them.

If you can stand several 100s of milliseconds of latency on every write then writing a 1.2gb flat file is not a problem.

At that point, why not just mmap the whole file?

Probably because file truncation/expansion with mmap is tricky.

Is there such a thing as a Winamp VS Aimp2 war? Once I discovered Aimp back then, when I still used Windows on a daily basis, I never switched back to Winamp. Aimp seemed so much more refined and organized. Wondering, if most people, who reminisce about Winamp, even know about Aimp.

I should go back to Winamp if its still the same (best music player I ever used)... I used to to make it small and always on top and drag it on my current window's title bar next to the minimize button...

I use WACUP, no issues at all.

Cool, so much good memories.

There's a bug in webamp though, choosing "Options -> Double Size" only enlarges the player and equalizer. The playlist remains the same size.

I don't believe that's a bug, that's how Winamp behaved

Correct

I have a lot of smaller datasets I am hoping to expose similarly once CloudFlare D1 becomes available.

Fly.io does a decent job, but hoping it gets cheaper/simpler with D1.

Fly.io will give you an always-on 256MB of RAM instance for $1.94/month - and they offer 2,340 hours per month in their free tier, which is enough to run three of those for the whole month without spending any money at all.

If we're spamming free DB's I always mention Supabase, 2*500MB PostgreSQL.

I’d love to have this kind of database for everything! Music, themes, packages, applications. All of it!

Great work

Any reason / use for all that metadata?

Contains all the text content extracted from each skin. Readme text, config files, as well as metadata about how the skins performed on social media (likes/retweets from the @winampskins Twitter bot) and also NSFW stats (as reported by users and confirmed by me) and if the skin is approved by users in Discord to be tweeted by the bot.

All of these are used for search (text is indexed into Algolia) and ranking:

Most likes/retweets are shown first, then approved, then rejected. NSFW skins are deprioritized as well.

Extracted config files are also useful for quickly testing/validating the various parsers that I had to write as part of the Webamp project.

Be so much better if it was just normal files on disk.

SQLite files are normal files. It's a "Recommended Storage Format" by the U.S. Library of Congress. https://www.sqlite.org/locrsf.html

I don't have a Twitter account, so I can only read so much until Twitter hides the content behind a login/signup screen. Maybe it's just me but I find it ironic that a writeup about preserving content is given away to a corporation that blocks it in this manner.

Tweet author here (not op). I'll repost here:

The Winamp Skin Museum is powered by a sqlite3 database containing 1.2gb of metadata about 86,000 Winamp skins.

It's all exposed in this explorable GraphQL endpoint

https://api.webamp.org/graphql

A bit about the data...

It includes:

* Original filenames and md5 hashes of each skins

* Names/metadata of all files compressed WITHIN the skins (file size, date, filename)

* Text content of all text files found within the skins

* URL/likes/retweets if the skin was share by @winampskins (or on Instagram)

* Full metadata/info about each skin's @internetarchive page

* Info about manual reviews (good to tweet? NSFW?)

* URLs to download skin files or screenshots

Kind [of] fun data to comb though (if you're like me).

If anyone is interested in getting the raw DB to play with, or has ideas for extra stuff to expose in the graph, get in touch.

Thanks! That's really nice of you :)

I want to know more about the bowling pin man - I mean, there's probably not much more to be said about him, but he raises so many questions!

Me too!

The zapper function in ublock origin can be used to get around this limitation. Some people on HN alos talk about nitter, I havent used it, but maybe that would work too.

Please don't complain about tangential annoyances—things like article or website formats, name collisions, or back-button breakage. They're too common to be interesting.

https://news.ycombinator.com/newsguidelines.html

Is it tangential to mention that the content is not accessible?

Yep, lots of moderator comments about it, including specifically twitter. There are workarounds that get posted in thread, use those or post one yourself.

https://nitter.net/captbaritone/status/1535471373191028737

Using a medium with 140^W 280 symbols limit to convey things larger than that is...

Tip: replace “Twitter.com” in the url with “nitter.net”.

It’s what Twitter web should be.

For reference / FWIW: https://nitter.net/captbaritone/status/1535471373191028737

Reply on news.ycombinator.com