Using psql to \watch Star Wars and other silly things!

by Will Leinweber

In this engaging presentation titled "Using psql to \watch Star Wars and other silly things!" at the Keep Ruby Weird 2018 conference, Will Leinweber explores the playful side of programming with PostgreSQL. He connects coding for fun with practical applications, demonstrating how to enjoy coding while still being productive. Will discusses his journey and experiences with automations in chat applications and social media bots, sharing various examples throughout his talk.

Key Points Discussed in the Presentation:

Automation in Coding: Will highlights the joy of automating small tasks for fun, reminiscing about chat bots in older platforms like Campfire and HipChat, and transitioning to modern tools like Slack.
Witty Chat Bots: He shares his experience of creating chat bots that humorously respond to specific keywords, showcasing how fun can be integrated into everyday programming tasks.
Twitter Bots: Examples of using simple scripts to respond to tweets show how coding can engage with social media and offer playful interactions, including a humorous method of generating responses based on tweet characteristics.
Using psql with Star Wars: The presentation’s highlight is utilizing PostgreSQL to watch a digitized version of "Star Wars" by retrieving the textual content stored in a Postgres database. Will walks through the extraction and transformation process of the data, which includes a detailed explanation of how the animation is presented in a terminal interface.
PostgreSQL Functions and Features: He introduces the use of Postgres functions to create a watcher for frames, explaining sequences and how they contribute to the automation of watching a movie.
The Concept of \watch: A notable feature in PostgreSQL that allows for refreshing query output at specified intervals, demonstrating a playful approach to utilizing the database in unexpected ways.
Introduction of ffmpeg: Towards the end, he explains how ffmpeg can be used to output a movie format into a terminal, showcasing the technical side of programming in a fun and interesting way.
Anecdote: Interspersed throughout the talk, Will narrates a whimsical story about a mother with numerous children and their secret pet dog, metaphorically tying the narrative back to his points about coding and automation.

Conclusion and Takeaways: The talk emphasizes the inherent fun in coding and the unique ways automation can bring joy to tedious tasks. Will encourages attendees to consider how programming can be enjoyable and to explore creative avenues within their codebases. He also highlights the evolving capabilities of PostgreSQL and its applicability beyond traditional database functions, inviting discussions and questions about his playful approaches to coding.

00:00:09.440 There's a button on it. Alright, awesome.

00:00:13.830 Hello! So, I'm going to talk to you about using psql to watch Star Wars and some other silly things.

00:00:19.410 This is my contact information. My website is bitfission.com, but if any of you have laptops, please don't go there right now or mute first because it does autoplay media.

00:00:24.900 I work at a company called Citus Data, and we're an open-source extension that turns Postgres into a distributed database. The Postgres part is going to come up because of the psql, and these slides, if you're interested, are at psqlstarwars on my GitHub.

00:00:35.130 I was fortunate enough to speak here in 2015. Was anyone here in 2015? Awesome! I hope you liked that talk; otherwise, you might not like this one either.

00:00:49.440 But I looked at the beginning of my talk when I was preparing for this one, and I realized that one of the first things I did was tell people about my really good gem called 'bundle.' All it is is a gem specification to install Bundler for you. If you type, leave out the 'r' when you install it.

00:00:57.030 At that time, I had 1.4 million downloads, and I wanted to give you all a bundle update right now: we're at 3.9 million downloads, so almost to four million! I know just looking at numbers can be a little difficult, so here's a nice graph to make it a little bit more comprehensible.

00:01:23.220 Also, I noticed that advice to speakers sometimes suggests having a story that connects all your different topics. Unfortunately, this talk is a bunch of random unconnected things, so I figured a story that was completely unconnected would make the most sense. If you think about it a little, it makes sense.

00:01:37.829 So, the story goes like this: there was a woman who had 90 children, which is a lot. To keep track of them, she sensibly named them one, two, three, four, all the way up to ninety. We'll check back in with them later.

00:01:54.479 One of the things I really like about this conference in particular is that it emphasizes coding for fun and not just for work. Still, there are some common themes between coding for fun and coding for work. The first thing I'm going to talk about is automating things, and the second is doing things in the terminal, which is a common tool for many of us.

00:02:13.379 One of the first things that I automated for fun was chat bots. These weren't like bots that were a separate person or a separate entity that you would talk to. Rather, back in the days of Campfire and HipChat, you could have the bot appear to be just you.

00:02:19.590 So what I would do is joke repeatedly that people really like when you do the same joke and never stop. For example, whenever anyone says that something is intense, I go, 'Oh, like camping?' It's hard to notice every time someone says that in one of the chat rooms.

00:02:37.800 Back in those days, I just had that happen automatically. These days, for better or worse, we're on Slack now, like many people, and while they have that automation with the automatic responding feature, it takes the fun out of it because it comes from the bot.

00:02:54.129 So one of the things I wanted to figure out was what kind of little bits of fun I could have with chat programs. One of the things I noticed with Slack is that it tells you when someone else is typing. Often, people will stop typing when you're typing because they think you're going to say something.

00:03:10.859 So sometimes, I'll just mash on the keyboard a little bit. This is hard to do using the web socket-based gem for Slack. Anytime you get a notification message that someone is typing in the same channel, you just reply right away that you're typing.

00:03:26.909 This works in public channels, private channels, and one-on-one messages. It's just great! It'll look something like this.

00:03:42.110 And I bet you weren't expecting a picture in a terminal-based slide presentation, but that just shows you to expect the unexpected. So back to the story. The woman I mentioned earlier had two children.

00:04:17.660 Another fun thing to do is with Twitter bots. Sometimes, on Twitter, people make jokes, and sometimes they're good, and you want to tell them they did a good job with their joke. But again, you have to watch for the jokes all the time, which is hard to keep track of.

00:04:34.460 So if you write a little program, the first line and the last line here with Redis are just storing the last tweet ID, so that you know this whole method can be idempotent. You can run it again and again, and you're only going to operate on tweets that happened since the last time you ran this.

00:04:59.210 You get those tweets from the last time, process them, and this one's fun right off the bat. Ninety-five percent of the time, randomly, you do nothing, but 5% of the time, you're going to reply to it.

00:05:23.150 You want to generate a response. If the tweet happens to be in Japanese, you might respond with their name in Japanese; otherwise, respond with their English name. Depending on the length of the tweet, if it’s a normal-sized one, you do nothing.

00:05:41.400 If it's a little long, stretch out the first part of their name; if it's over 140 characters and getting into these new-fangled 240-character tweets, stretch out the last part. Then, maybe add some random decorators like an ellipsis or an exclamation point.

00:06:02.110 If you want some brand engagement, add a hashtag or a nice little emoji at the end. All that's left to do is set up those constants, and you have a bot that automatically responds to 'tender love and puns'.

00:06:22.689 On the other hand, you don't tell them that you did it until you give a talk at a conference. Alright, getting into the terminal side of the programs, one of the lefts is the slide presentation software itself.

00:06:51.400 Now, I can't really recommend writing slide software as a procrastination method before giving a talk, but it's a good way to avoid parts of the talk. Later on, I have good word that one of the talks is going to have some stuff on Klein's.

00:07:22.710 I couldn't really figure out how to recursively show what one of these slides would be because it would just go on forever. This may be a little bit cheating to have the program itself read itself at runtime and show you the slide, but it's a little fun.

00:07:39.200 You get some nice syntax highlighting just with jisub. It's fun! You can have it be really advanced; you can show the current slide number, go backwards, go forwards, you know, very high professional slide software.

00:08:07.680 So back to the story: the two children really wanted a dog, but they were forbidden from ever having one. It was like the number one house rule: you will never have a dog, and they were pretty sad about it.

00:08:30.820 But I'll check in with them later. So yeah, what was this about Star Wars? That was the big opening title and everything. Alright, I’ll get to that. This site here, ASCII Mason Coda and Z, has been around for a long time.

00:08:53.850 People have taken it and made it into SSH things that you can connect to. I've seen someone do other things with it, but the credit goes to this person: Simon Jansen, who hand-drew all of these from 'A New Hope' into an ASCII format.

00:09:11.560 Most of this I'm just taking from their work and putting it into Postgres. Why Postgres? Well, I've spent several years working on various Postgres hosting systems, so I think that's kind of warped my brain. I thought, why not Postgres for everything?

00:09:34.690 So the first step we have to do is get the content of this work into a usable format. Unfortunately, I can't just curl it because it requires gzipping, so you have to say you want it gzipped, then uncompress it.

00:09:55.570 This is because it's a two-megabyte HTML file, which is a lot for hand-drawing all those slides. The format just looks like this: some boilerplate HTML and JS stuff at the beginning and at the end, but most of that two megabytes is the single line that starts 'var film'.

00:10:06.350 This uses its own compression algorithm where it's a number of times to repeat the slide followed by 14 new lines for the content of that frame.

00:10:32.190 So we want to put it into Postgres. Of course, we're going to have a column that is the slide number, another column that indicates how often to repeat that slide, and on the actual slide itself, we can go through that file and do an extract-transform-load process.

00:10:51.600 We'll look at it, find the line that starts with 'film.' Unfortunately, there's something weird going on with some of the encoding, so rather than figure out that problem, we'll just bulldoze right through and make them empty things. It's also single-quote-escaped, so let's get rid of those.

00:11:11.520 We'll then cut off that 'var film...' line and split the relevant JavaScript at the end. We'll split it by new lines, putting it into groups of 14, and then we have that all there. We'll jam it into Postgres using SQL, and we're almost there!

00:11:34.700 We just have to figure out how to watch it. Now I'm going to introduce you to Postgres functions. This creates a function called 'go' that takes a speed.

00:11:50.580 So to multiply that by how long to sleep in between frames, we have a counter for the current slide. The first thing we do is sleep for the previous slide because that's where we're coming from and then show our current frame.

00:12:04.770 The next time we run this, it'll sleep for that amount and then display the next. The other little tricky part that we can do is use a Postgres sequence.

00:12:24.570 Anytime you create a table in Postgres, if you have an auto-incrementing column, it'll create a sequence for you and attach it to that table. But you can use sequences for anything you want, not just attached to tables.

00:12:38.560 So this one creates one called counter. You can see that if you select the next value, it increments by itself. You can also pick the starting point and have it restart at a number of your choice.

00:12:57.480 If we put these two together, we can hold up the next frame using 'up' and 'enter' repeatedly and watch Star Wars.

00:13:11.660 So are we done? No, there has to be a better way. It's annoying to hit up and enter repeatedly just to watch a movie.

00:13:28.100 Let me introduce you to 'backslash watch.' This was added in Postgres 9.3. Is this the best contribution in the history of computer science? I know one person who would say yes.

00:13:50.920 One thing to note about Postgres commits, which I think is really nice for their open-source project, is that they not only credit the author here, Tom Lane, who committed it to the project.

00:14:09.370 They also credit the author in the commit message and include all the people who reviewed the patch, acknowledging the substantial amount of work involved in reviewing patches.

00:14:29.900 Alright, let’s go to a demo. First, make sure that our sequence started correctly. We start by picking an arbitrary frame in the future.

00:14:45.100 There’s a frame... there’s a frame... there’s a frame... but let’s make it go with watch and 0.01 seconds, and there we go!

00:15:09.520 We have Star Wars inside Postgres.

00:15:20.530 But wait, there’s more! We have to get back to the story. So the kids found a stray dog, and they knew they could never tell any of their friends.

00:15:56.579 Their friends wouldn’t understand how important it was to keep it a secret, so they kept it just between the siblings.

00:16:17.329 They were very clever. They named the dog something specific so that way they could talk about it without their parents figuring out what they were talking about.

00:16:30.000 Also, they grew up to be JavaScript developers.

00:16:40.480 One thing I didn't mention is that someone has gone through the effort of making Star Wars into a terminal-based format, but you can't really wait for all movies to be done like that because it takes a long time.

00:16:58.300 So how do we watch any movie inside our terminal? The answer is you have ffmpeg output in a format called ppm.

00:17:10.160 I tried linking against a codec directly, but that was complicated. I looked into using Libffmpeg as well, but it was also very complex.

00:17:30.900 I realized you could have ffmpeg output to standard out with this ppm format, which starts off with a header stating the version, height, width, and color depth.

00:17:43.970 Then we want to have a place to put all this information. For this part, I'm using a programming language called Crystal, which is visually similar to Ruby, but it's statically typed.

00:18:00.880 In this case, I have the RGB values and the ANSI escape codes to make sure it works at all, displaying ANSI blocks.

00:18:25.160 We can now try to watch a movie in the terminal. Let's see if this works.

00:18:44.210 If this works, you can kind of tell what's going on.

00:19:03.130 Alright, so finally the end of the story. Sadly, the dog ran away, and because the kids never told any of their friends about it.

00:19:55.090 That was amazing! I always wondered who that Twitter bot was.

00:20:07.100 Alright, I think we have a little time. Do you want to take some questions?

00:20:14.400 Sure, we have it! Alright, I'm on my way.

00:20:37.360 First of all, that was just the greatest thing I've ever seen! Thank you for that.

00:20:50.700 My question is, you were mentioning that you were finding the emoji that was closest to the color. What was that based off of? Was it just the most common pixel in the emoji or the average of all of the pixels?

00:21:08.360 Yes, that may be I glossed over that too fast. I took all the emojis and made them use ffmpeg into just four pixel versions, so it's the average of the four quadrants.

00:21:23.440 Questions? It was just that amazing.

00:21:37.200 Alright, thanks!

00:21:44.160 Well, thank you!