 there's a button on it. All right, awesome. All right, hello. So I'm going to talk to you about watching, using P-SQL to watch Star Wars and some other silly things. This is my contact information. My website is bitfusion.com, but any of you who have laptops don't go there right now or mute first because it does autoplay midis. I work at a company called Citus and we're an open source extension that turns Postgres into a distributed database, and the Postgres part is going to come up because of the P-SQL. And these slides, if you're interested in, are at P-SQL Star Wars on my GitHub. I went, I was fortunate enough to speak here in 2015. Was anyone here in 2015? Awesome. I hope you like that talk, otherwise you might not like this one either. But I looked at the beginning of my talk when I was preparing for this and I realized I, one of the first things I did was tell people about my really good gem called Bundle that all it is is a gem spec to install Bundler for you if you leave out the R when you install it. And at that time, I had 1.4 million downloads and I wanted to give you all a bundle update of, right now we're at 3.9 million downloads, so almost to 4 million. Now I know just looking at numbers can be a little difficult. So here's a nice graph, if that makes it a little bit more comprehensible, I hope. Also, I, you know, advice to speakers, sometimes they say you should have a story that, you know, connects all your different topics. Unfortunately though, 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 that a little, it makes sense. If you think about it a lot, just think about it a little. Okay, so there was, so the story goes, it starts off like this. There was a woman who had 90 children, which is a lot. And to keep track of them, she just sensibly named them 1, 2, 3, 4, all the way up to 90. We'll check back in with them later. So one of the things I really like about this conference in particular is it shows about, you know, coding for fun and not just for work. And still though, there's some common themes between coding for fun and coding for work. One is the first things I'm going to talk about is automating things. And the second is, you know, doing things in the terminal, which is a common tool for many of us. Automation things. One of the first things I automated for fun was chat bots. And these weren't like chat bots that were a separate person or a separate bot that you would talk to. Rather, back in the days of campfire and hip chat, you could have the bot appear to be just you. And so what I would do is jokes that I would make over and over again that people really like when you do the same joke and never stop for 10 years. One of them is whenever anyone says that something's intense, I go, oh, like camping. And so, but it's hard to notice every time someone says that in one of the chat programs. And so back in those days, I just had that happen automatically. These days, for better or worse, we're on slack now, like as many people, and while they have that automatic responding feature, it kind of takes the fun out of it because it comes as the bot. And so one of the things I wanted to figure out is, like, what kind of little bits of fun can I have with the chat programs? And one of the things I noticed with slack, because 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. And so sometimes I'll just mash on the keyboard a little bit. And that's hard to do. This, using the WebSocket-based gem for Slack, anytime you get a notification message that someone is typing, on the same channel you just reply right away that you're typing. And this works in public channels. It works in private channels. It works on one-on-one messages. And it's just great. It looks something like this. And I bet you weren't expecting a picture in a terminal-based slide presentation, but that just shows you, expect the unexpected. So back to the story. So 90 herself had two children. I'll keep the story going. And so another fun thing to do is Twitter bots. And sometimes on Twitter people make jokes and sometimes they're good. And you want to tell them, you did a good job with your joke. But again, you have to watch for the jokes all the time. It's hard to keep track of that. And so if you write a little program, and the first line and the last line here with the Redis, all that's doing is storing the last tweet ID so that you know, so that this whole method can be idempotent. So 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. You get those tweets that are, you know, from the last time. And then you process them. And this, this one's fun. Right off the bat, 95% of the time randomly you do nothing. But the other 5%, you're going to reply to it. And you want to generate a response. And then also my slide program can go back. It's pretty good. You can generate a response. And then reply to the target with your response. And so this is, if the tweet happens to be in Japanese, maybe you respond with their name in Japanese. Otherwise, respond to it with their English name. And then depending on the length of the tweet, if it's a normal size one, do nothing. If it's a little bit long, stretch out the first part of their name. If it's over 140, getting into these new, fangled 240 tweets, stretch out the last part. And then maybe add some randomly, some other decorators, like an ellipse or explanation point, or if you want to add a brand engagement at a hashtag. Or a nice little emoji at the end. And then all that's left to do is set up those constants. And then what you have is a bot that automatically responds to tender loves puns. And then the other part is you don't tell them that you did it until you give a talk at a conference. All right, so getting into the terminal side of the programs. One of the stuff is this slide presentation software itself. Now I can't really recommend, if you have to give a talk and a great way to procrastinate is to write slide software rather than the parts of the talk. And later on, I have good word that one of the talk is going to have some stuff on quines. 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 maybe works. It's a little bit cheating of having the program itself read itself at runtime and show you the slide. But that's a little fun meta sort of thing. You get some nice syntax highlighting just with G sub. It's fun. And then also you can be really advanced. You can show the current slide number. You can go hit B for backwards. That go forwards. Very high professional slide software. Okay, back to the story. So there are two children. They really wanted a dog, but they were forbidden from ever having a dog. It was like the number one house rule is you will never have a dog. And that was pretty sad. But we'll have to check them later. So yeah, wasn't this about Star Wars? That was the big opening title and everything. All right, I'll get to that. So this site here, Ascumation.co.nz, has been around for a long time. People have taken that and made it into SSH things that you can connect to. I've seen someone do other things with it, but the credit to it goes to this person, Simon Jansen, who actually took the time to hand draw all of these things from A New Hope into an ASCII format. And so most of this, I'm just taking their work and putting it into Postgres. Why Postgres? Well, I've spent the last several, several years working on various Postgres as a hosting system. So I think that's kind of warped my brain. So I think, why not Postgres for everything? So the first step we have to do is get the content of this work into a usable format. Unfortunately, you can't just curl it because it requires that it's gzipped. So you have to say that you want it in gzipped, un-gzip it. And that's 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, more of that, but most of that two megabytes is the single line that starts VAR film. And he made this own compression sort of algorithm where it's a number of times to repeat the slide and then 14 new lines for the content of that frame. And so we want to put in Postgres, of course. We're going to have a column that is the slide number, another column that is how many, how often to repeat that slide, and then the actual slide itself. We can go through that file, do an extract transform load loop. Look at it, find the one that starts with the film. Unfortunately, there's something going weird with some of the encoding. So rather than figure out what that problem is, just bulldoze right through and just make them empty things. It's also single quote escape, so let's get rid of those, and then cut off that VAR film and the split stuff in the JavaScript at the end. Split it by new lines, put in groups of 14. And then we have that all there. Jam it into Postgres using SQL. And we're almost there. We just have to figure out how to watch it. So now I'm going to introduce you to Postgres functions. Very exciting. So this creates a function called go that takes a speed. So to multiply that by for how long to sleep in between frames and then a counter, which is the current slide counter. And so the first thing we do is we sleep for the previous slide, because that's where we're coming from, and then show our current frame. And then the next time we run this, it'll sleep for that amount and then display the next. And so on. The other little tricky part that we can do is use a Postgres sequence. Now, 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 just use sequences for anything you want and don't have them attached to tables. So this one, create one called counter. You can see if you select next value, it increments by itself. You can also pick the starting point. You can have it restart at 20 and then it starts, or 200, and it starts counting from 200. And then if we put these two together, we can hit up and enter, up and enter, up and enter, and watch Star Wars. So are we done? No, there has to be a better way. It's annoying to hit up and enter over and over again just to watch a movie. And so 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. One thing to note about Postgres Commits, which I think is really nice for their open source project, is while the author here is Tom Lane, he's the one who committed it to the project, they put the author in the commit message. And then what's also very nice is they put all the people who reviewed the patch, because often reviewing a patch can be a substantial amount of work also. So the reviewers get credit. So all right, so let's go to a demo here. So if we make sure that our sequence is started correctly. So we start, I'm just picking an arbitrary frame in the future. We can look at it. There is a frame. There's a frame, there's a frame. But let's make it go with watch in 0.01 seconds. And there we go, we have Star Wars inside Postgres. But wait, there's more. Oh, we have to get back to the story. So the kids, they found a stray dog and they knew they could never tell any of their friends because their friends would, they didn't know how important it was to keep it a secret. So they kept it just the siblings. They're the only person who ever knew about the dog. And they were very clever. They named the dog this. So that way they could talk about this and not have their parents figure out what they were talking about. Also they grew up to be JavaScript developers. Yeah, so one thing I didn't mention is that, so one of the things, so that's very good with Star Wars that someone has gone through the effort of making it 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. So how do we watch any movie inside our terminal? And so the answer is you have FFMPEG output, what's called a PPM format. I tried looking into linking against a codec directly, but that was really complicated. I tried using like lib FFMPEG, but it was also very complicated. So I realized you could have FFMPEG output to standard out with this PPM format, which is pretty easy. It starts off with a little bit of a header saying which version it is, in this case six. It gives the height and the width, so 80 and 40, and then the color depth, which I've only ever seen as 255, but I imagine it could be other things. I ignore that. And then, and this code is at PPM to emoji. So once we have that, we wanna have a place to put all this information. For this part, I'm using a programming language called Crystal, which is visually pretty similar to Ruby except it's statically typed. And so in this case, I have the RGB values, and then just to make sure that it could work at all, I wanted to just display ANSI blocks. And so this is the ANSI control character is to do an RGB value, and then put a block and then end those modifications. So now we can try to watch a movie in the terminal. Let's see if this works. I have this little program cat. Yeah, so this is very small. I'm not gonna show this, but just these are the FFM peg, the FFM peg things, you can see it's very long. So all this program is doing this so that I don't have to remember how to type all that from hand. So if we do this, let me do, so if we do this, and if this works, oh, look, I'm on a, it's me. But I'm sure several of the astute people among you, notice that I am not Star Wars, but this is. But I figure that just showing those ANSI color blocks is a little bit cheating, because they can be any color they want. Something a little bit more interesting is what if we take every four of those pixels and represent them as one emoji? And so to do this, you need to find a repo of all the emojis, like how your system's gonna display them, in my case, the Apple emojis. Use this FFM peg to make them into four color pixels, read out those values and store them, and then after you do this realize that because you are on an old version, you're a Mojave not, half of those don't work. And so this is what the file looks like. So this is the RGB values in order from the different four quadrants, and then the code point for the emoji. Out of the about 2,600 of them, only about 1,000 were usable by me, and so I had a good list of those ones. And then you have to figure out how do we find the nearest emoji for those four things. This is more following on from the color talk. This is a formula for distance of colors. You can see it's somewhat like what it would be just for distances in a 3D chart space, but with some other weighting that supposedly makes it better for human perception. I got this formula from a free encyclopedia called Wikipedia. I don't know if it's actually good or not. It seems to do the job. It does come with a square root thing, but I realized if I'm just comparing distances and everything is taking a square root, I can leave that off and save some CPU cycles. And so let's see what these things look like with emoji. This works, nope, emoji. So this works, you know what I mean? It's represented by emoji. You can kinda tell what's going on, it's kinda... All right, so finally the end of the story. Sadly, this ran away. And because the kids never told any of their friends about it and because they couldn't put up posters asking for it, really only 90s kids will remember this. That was amazing. I always wondered who that Twitter bot was. And now I know. I think we have a little bit of a time. Do you wanna take some questions? Sure, I guess. Do we have it up? All right, I'm on my way. First of all, that was just the greatest thing I've ever seen. Thank you for that. Thank you. Thank you. Thank you. Thank you. Thank you. My question is, so 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 was it the average of all of the pixels? Yeah, so maybe I glossed over that a little too fast. So I took all the emojis and made them, using ffmpeg, into just four pixel things. And so it's the average of the four quadrants. Questions. It was just that amazing. All right, thanks, Will. Thank you.