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!
