 Hi, as she said, I'm Rick Ott and I work for Wilson Gear Digital, which is an advertising firm and we have an office in Columbus, that's where I work out of, Columbus, Ohio. And then there's an office here in New York. So they sponsored a lot of my ability to be here today, so I'll be plugging them throughout the talk, or plugging us, I should say. All right, so what I'm talking about is a database of things. Let me try something here, if I can. Is that, get me anything? Yeah, and then if I do that, what did that do? I'm trying to figure out how to swap back and forth efficiently on the screen. I think we just need to do this, do this. Can you see that? So what I have is a things database. And I'm gonna run a SQL command here, psql-d-things. That's probably a good idea. Let me see, hmm, will that work? There we go, yay, thank you, dash f, I'm in the wrong directory. Not that it matters. So I'm gonna run a sequence of SQL commands against these lights in front of us here and I'll come back and talk about what's happening as the talk goes on. All right, so we will go back to the talk while this is running. So that should be exciting. So I thought everybody had heard of the Internet of Things when I proposed this talk, but in going around I found a lot of people did not really know what that was and had never heard of the term. So let's very briefly talk about the Internet of Things. It's a buzzword that's been pretty popular in the tech communities for the last couple of years and it's all these different interconnected devices and things that you might have like a smart refrigerator and a smart toaster and smart light bulbs like the ones demoing here today. And Gartner said at the end of 2013, so in postgres terms, this is shortly after 9.3 was generally available. $1.9 trillion impact on the economy by 2020 within the next five or six years. That was a pretty big sum I thought and pretty amazing. And then I saw these quotes by the CEO of Cisco who thinks the decimal place is in the wrong place and he's saying $19 trillion. Originally he said $14 or $15 trillion, but every time he gives a speech it gets a little bigger. I think that's like a quarter or a fifth of the entire world economy, so maybe he's a little off. So let's assume he's off by a factor of 10 and Gartner is right, that's pretty huge. And then if Gartner's off by a factor of 10, it's still this huge industry that is looming. And I think I saw an article recently that was calling it a tsunami of technology. Tim O'Reilly is a famous tech pundit and owner of O'Reilly Publishing. One of the things he was complaining about just a couple weeks ago in an interview was that the internet of things was lacking a consistent system or platform or a way for them to interact or things to work as systems of things. And this he saw was the major stumbling block to the whole hype around the internet of things. In Fast Company there was an article last month that basically said the same thing, that there's no way to get device to device communication working consistently anytime in the foreseeable future. The internet of things is never going to take off because of it. I think the reason they're saying that is because they haven't heard this talk yet today. We have a platform that will do this. So what I'm going to first talk about is foreign data wrappers, because while many people in the room are pretty familiar with them, there are going to be a few people who don't really quite get what foreign data wrappers are yet. So we'll start by talking about foreign data wrappers, get a sense of what they are, since they're the implementation method for the database of things. We'll talk a little bit about things and why things are interesting to databases. Then there's a Python library called Multicorn, which makes developing foreign data wrappers very easy and quick. And I'm going to show an example with the Philips Hue lighting system of an implementation of a database thing, thing, thing. Anyway, let's start with foreign data wrappers. So the SQL-MED standards were proposed in 2003, 12 years ago, and that stands for management of external data. At first, I thought it was something to do with medical health issues. I don't know. Anyway, what they do is they let you represent a table from some other database as a table in your database. And to work on that table and interact with it, it's like foreign data. And I thought that was pretty neat innovation. Postgres introduced some basic SQL-MED capabilities with 8.4 in 2009. With 9.1, they put select capabilities in. And then just a year and a quarter ago, they introduced the ability to actually do updates and change the data on the other end. So that's pretty important because all of this foreign data technology is still really new. A year and a quarter is hardly a lot of time, even in the technology world. And yet, a lot has happened since then. So the first thing, of course, is representing relational databases in your database. And there's lots of them out there that are very even written. No SQL databases. Suddenly, people figured out how to plug no SQL databases in and make them look like tables in Postgres. I wrote the rethink DB1 last fall, and that's what got me started thinking about foreign data wrappers in general. And then connectors, ODBC, JDBC, SQL Alchemy. These are generic connectors. Anything that you can talk to with JDBC, anything that you can talk to with SQL Alchemy, you can pull into your Postgres database as a foreign data wrapper. But that's not even the end of it yet. And it's only been a couple of years. We have all kinds of other things that aren't really databases that are being represented in Postgres as tables through foreign data wrappers. Google. You can actually do a Google search and return the results into a table. Twitter, you can take tweets and pump them into your database and make them look like tables. You can mess around with CSVs and LDAP. Well, LDAP's a database, but sort of XML files. There's this nifty one over here, black hole. That is a nice template that lets you, if you want to code in C, build a foreign data wrapper off of. It works like DevNull on a Unix file system. It just returns null columns, and you can write to it all day long, and it just eats them up. So there's been a lot of technology. The Postgres Wiki page there is where you can learn more about all the different foreign data wrappers that are already written and available and ready for you. There used to be two Wiki pages. One had an S on the end of it, and one didn't. About a month ago, somebody merged them, so now they're just one page. And they both had different content. It was a little confusing when you said, this doesn't look like the Wiki page I was looking at yesterday. It's because, anyway, that's fixed. So, how do you use a foreign data wrapper as a DBA? It's pretty straightforward. You set up a user on the other database with the relevant access permissions. You install an extension in the database, such as the Postgres foreign data wrapper extension. This makes all the libraries available to talk to the other database. You create something called a server, which is a definition of the foreign database. You also have to create a user on the other database. And then the last thing you do is you build a foreign table. So the main difference in creating a foreign table from a regular table is you use that keyword foreign right in front of the table. And then you specify the server that the table goes with, and then any specific options that you want to pass as well. At that point, you can use your foreign table just like a regular table in Postgres. You can select from it. If you have update permissions, you can update to it. And that's pretty neat. However, you can't put indexes or constraints on foreign tables. It's trusting the other end to do. So if the other end doesn't have an index on it, and you need that index, your performance is going to be terrible, if you really want to restrict various values that you're getting back or updating, you have to rely on the foreign end to do that. This one bugs me the most. None of the graphical clients that I've found or tried so far can actually see the tables. That includes PG-Admin. Now, there may be a way to do it in PG-Admin, but I haven't found it. They're just invisible. And that makes it really hard to work with if you used your graphical client. PC will work so if you do the following. And it is for most of the people here. But when you have other people working with your foreign tables, they may not be comfortable with the command line. So you can build a view on top of your foreign table, and all of the clients can see views. And then it'll look just like a regular table to the end user, and they can select and see the columns they can choose from in the table. And so almost always when I build a foreign table, I build a view on top of it just so we can look at it in the clients. Or you can do a backslash DET instead of a DT. I'm not sure why E instead of F for foreign, but there's probably a reason for that. External table, that's a good guess. So you can list all the foreign data extensions you've loaded in, you can list the servers, and you can list the user mappings at the command line. So if I were to cleverly switch over here to, I did that wrong again, didn't I? There. Maybe a slash DET. Did that get small again, or is that? Yeah, see if I can move that up a little bit too. I'm going to go back and forth between the real database in my presentation a few times here. So you can see I have some foreign data tables configured in my laptop's instance of Postgres. I can do a slash DETW. And I have multicorn installed, slash DES. And each of these is a separate server. We'll talk about why that is in just a second too. So as somebody who's worked a lot doing ETL work and data engineering work and moving data from one database to another and BCPing it out, or copying it out, or selecting it out and loading it in and filing it out, my first thought was foreign data wrappers are great. I never have to do ETL again. Why not? I mean, I can put any data I want anywhere with any database in my Postgres instance. This is awesome. Well, there are some caveats to foreign data wrappers that make you want to think twice before you use them. They're really handy and quick. But here I decided to throw a slide in just even though this has nothing to do with the database of things to talk about just foreign data wrappers in general. If you have a lot of small data sources and you're unioning them all together, you might want to look at a different tool to pull all your data in rather than setting up 2 dozen or 1,000 foreign tables in your database. If your queries are going to crush the other guy's server, you probably don't want to just plug a foreign data wrapper in and then crush his server. A lot of times, the reason we're moving data is to get it out of a front end high performance system into a back end analytics engine of some sort. And you don't want to run your queries on the front end if you can avoid it. When you're connecting over the network, when you're connecting over the network, there's a lot of overhead with setting up the connection, going over the network, getting the query, coming back to your database. And if you're doing a lot of little queries, a lot of little results, that network overhead takes so much longer than the query would take, you're probably better off just copying the data into the database where you're at and running the queries locally. If you're running big queries on the other system, then assuming you're not impacting their performance, the network overhead's such a small percentage of the query time that you don't have to concern yourself too much with that issue. If you have a foreign database that's not always there, that goes up and down, disappears, comes and goes, foreign data wrappers aren't going to do you a lot of good and you're still back to copying data again. So there's a lot of technology for copying data. I thought I would throw this up here and getting the data back into your database and avoid using foreign data wrappers. You can use some of the replication technologies that have been introduced lately in Postgres. Symmetric DS, I have to give a plug for. I use that a lot. It's a local to Columbus company, Jump Mind, and it allows you to integrate with any JDBC compliant database. It moves data around scales to tens of thousands of databases to aggregate your data and replicate it. It can replicate in cycles, can do bi-directional replication. It's a really nifty tool. Check it out if you haven't played with it before. It's great for moving data in near real time and small batches around your data ecosystem. You can write batch jobs. And then the last bullet here is you can also put a materialized view on top of your foreign table. Materialized views are another new feature in Postgres. And the nice thing about materialized views is it copies the data from the foreign system to your local system so that you don't have to go to the foreign system to query it, but you don't have to set up anything external to Postgres to actually do the copying. And with 9.4, you can do concurrent refreshes. And concurrent refreshes are awesome because you cannot have to totally block all reeds on that foreign table while that's happening. So materialized views go hand in hand with foreign data wrappers in some cases. So we'll shift gears here and talk about things now. And what do we know about things? This is a picture I took in Columbus at the Franklin Conservatory. It's an art exhibit by a guy named Bruce Monroe from the UK last summer. And these are lights that are really cool-looking. I thought they looked like alien brains to me. And I reversed the color and stuff. But I like this picture, so I had to throw out my slides. So you guys can read. I'm just going to pop these up. These are things that could be connected to your database. And one of the things I was thinking about as I was preparing this talk is why would you have a $1.9 trillion the internet of things industry? And how about this? So suppose you had the cell phone industry. There's going to be a point where everybody on the planet has a cell phone or a smartphone. And most people will not need more than one. So the cell phone industry is going to top out at what, 7 billion cell phones? That's assuming everybody on the planet has a smartphone. But things, everybody needs more things. The more things you have when you die, the more you win. So the market for things is much larger than the market for, say, cell smartphones. So smart things are going to be huge. Everything's going to be smart because people can make money off it for no other reason. So things have identifiers. Each of these light bulbs has a light bulb identifier that goes with it. Things have some sort of state. They're on or off, cold or hot, closed or open. Some things you can change the state on, like I can do with these light bulbs. I can turn them on and off. And when you organize things, they turn into a table. So that means that things, to me, are data. And they can fit right in my database. And that was my cat, by the way, in that picture. We have four cats, a dog, two fish tanks, and two kids. I threw some of those creatures later in the presentation as well. So why would you want to put your things in a database? That seems a little bit crazy. I mean, databases are good for data, and things are good for things. And even if things are data, why would you want to put them in the database? It's kind of a good question. Well, the first and foremost is that databases, in particular, Postgres are well-known and widely supported. If you want to program in Go, if you want to program in Scala, if you want to program in Haskell or Python, or use a LAMP stack, or use a .NET stack, or whatever you want to use, there's a way to connect it to the database. And your average application developer, your average front end developer, your average software developer can sit down and knows how to talk to databases, knows how to interact with them. So this is a standard well-known, already well-established, easy, well, maybe not easy to use, maybe easy to use, I don't know. Anyway, it's well-known and widely supported. So if you can get your things in a database, you open the utility of your things to the larger development community, and not just the data geeks or the API geeks or the REST JSON Go programming geeks. And I think this is where the internet of things can be a platform representation on Postgres. This is the big advantage over these other companies that are out there trying to build. We're going to build a standard API that everybody who makes a thing can configure to our API. And we're going to have a standards committee and figure out what the standard things API is going to be. And maybe in 10 years there'll be a things system that you can use. It's already here. We have all the standards. We have all the tools. We have everything we need to jump into a $2 trillion industry. It's consistent across all things. If you have a table of things, a table is a table, is a table of this refrigerators or light bulbs or garage or openers. Once you have all your things in your database, you can do data science and play with them. You have data science tools that interact with your database and let you look across all your things. Suppose you had earthquake sensors all over the country and you plugged them into your post-gradation database. You could look for patterns in all the different earthquake sensors around the country using standard database and data science tools. This is kind of an interesting use for a database of things. You can join with reference tables and maintenance logs and other types of data that is not necessarily things, such as who installed the slide bulb? When did they change it? When and how long has it been on? How many watts does it burn? Where is it located? How, your imagination can go crazy. When were the belts last changed on the refrigerator? When was the air conditioning data or the air conditioning freon replaced? How many times has the garage door been open in the last month? There's lots of things you can store in the data that would be separate tables that you can then join against your actual things. And you could say, update all the light bulbs and turn them on that haven't been used in the last three years or blink the light bulb that Karen changed last week. So this is a very valuable use of things in data. And you can join and union with each other to build Frankenstein monsters of things. If for some reason you wanted some sort of union table of refrigerators and thermostats, I don't know why, but there might be a reason for it. And here's a database joke for those of you who like database jokes. So another interesting thing in Postgres 9.4 they supported putting triggers on foreign tables, which is weird, you can't put constraints on foreign tables but you can put triggers on them. Okay, that'll be great. So in theory, if you're using your database interface and you wanna use one of these triggers, I haven't tested them yet to see if they really work. You could tell the database open my garage door and then there's a trigger on that table that would also turn your kitchen light on and log the fact that the garage door was opened. So this is all stuff we can do in the database. I think it's why you would wanna build a database of things. We'll jump now into how to actually build a thing in your database. And I'm gonna use multicorn for this. I know some people in here have already complained to me that, oh, multicorn, that's Python, yeah. I'd rather code and see. Couldn't you show me an example and see? Well, I code mostly in Python. I have been for 20 years and since 1.0 came out. So I'm gonna use Python for this and hopefully you can all follow along and use your favorite language and tools later. But the important thing about multicorn is it takes care of all the rocket science of interacting with the database for you. So you don't really have to know how to talk to Postgres to get multicorn to work. Most of that's taken care of in the multicorn implementation. You just have to know how to talk to your thing and how you want it to look in the database. I'm gonna use these Philips Hue lights and I chose these as the demo because they had a really clear and well-written API and really good forum online and it was very straightforward to tackle these as an API. Some things are proprietary still. Some things are hard to figure out how they work. This was pretty straightforward. I was also gonna do Lego Robotics and bring in a Lego robot and make it in my table. But I ran out of time. The four cats, dog, two fish tanks, kids, full-time job. I should point out that Wilson and Gear, although we are a marketing and advertising firm, have nothing to do with Philips Hue at this point. If Philips Hue wants to contact us to do some stuff, we'd be happy to do that. But maybe next time I'll have a robot and robots have sensors and motor positions. So they have all the same things that the other things have. Arm is up into the left and you can do an update and move the arm down and you can read the bumper sensor and stuff on it from the tables. I just haven't gotten it implemented yet. So there's a set of API endpoints in Philips Hue. I've implemented three of them. I've implemented lights, sensors, and config. I have not gotten to scenes or groups yet. So lights lets me control the lights in the Philips Hue system. Sensors, you can plug sensors in. There's one that comes built into the little bridge. And so I use that to implement a sensor select statement and then config lets you change the IP address, change the users, and that sort of thing. Scenes and groups allow you to pre-define colors and combinations on your lights so you can say I want my room to be mellow right now and it'll change all the lights in your room to be mellow. All at once rather than having to figure out what each light should be and tune it. This software is available in this repo on GitHub. I just published it about 15 minutes before this talk. I still have to do the read me on it. So the read me is kind of lame but you can refer to these slides until I finish the read me. When you set up the Philips Hue lights, you pull in, I use PGXN, the multicorn extension. It goes right in one line command. I'm assuming PGXN is installed, that is. Clone the multicorn Postgres Hue repository for my repo. CD into it and run this setup.py install and it's there. The next step is to go in and run some DDL which I'll show you in a few minutes. And that's another cat picture. So we create an extension just like we would for the Postgres foreign data wrapper. We create a server just like we would for the Postgres foreign data wrapper. We don't bother with user mappings in this case and then we create the table. Now, because the foreign data wrappers for these types of things are very specific, I mean the light bulb things is a very specific API, you can't just create any arbitrary table like you might connecting to a foreign database. You have to be specifically aligned with the API. And if the API changes, you may need to change your table or put some translation in between or a view that hides the fact that the API changed. And that's why we have a separate foreign data wrapper for each of the end points, the lights, configs. Each one has a separate set of API interactions that are behind it to make the table. But that's okay, the thing manufacturers can build the foreign data wrapper part, all you have to do is know how to create the tables and use the database of things. So, multicorn implementations work like this. You pull in multicorn. And you inherit a new class from the foreign data wrapper class. Then you build the following functions, select. Can you read the state of your device or your thing and feed it back into the table? So, if you start with this, if you have a thing and you build your select, it's probably about 10 lines of code, 15 lines of code, you have a foreign data wrapper ready to go that can read the state of your device. And everything else is handled by multicorn. It's like magic. The next thing you can do is you can say, well, I wanna change the state. So, let's write a function that does a post back to my thing API and updates the state. And this is, again, a fairly simple and straightforward little routine that updates your thing. You can also implement insert and delete. Typically, we don't add things to the real world from the database. That would be like magic. But some of the APIs do support inserts to an extent. If you have a new device and it's on the network and you have to somehow sync it with your controller, you may have an insert or you may remove a light bulb from your controller. And so there may be cases where you do insert and delete. But for the most part, you don't go, geez, I need a beer fridge in this room. Update, my refrigerators add beer fridge and one just appears there. That would be pretty cool, though. All right, so if you get this far, you've got a basic foreign data wrapper that you can work with and you have started your database of new things. You can also, in multicorn, implement a rollback and commit function. So if you really wanna do thing manipulations in large transactions, you can implement those. I haven't quite figured out how that works in the context of things because if you change the state of a light bulb, I guess you wait until you do the commit before you actually change the state of a light bulb, but that's something to ponder. So for my implementation, I have not worked those out yet to make them work exactly right, but I'm sure it can be done with some clever development and a little bit of thought. Everything else takes place in the rocket science magic engine of multicorn. So I'm gonna jump back and forth between code and this other stuff now to show you what it looks like. So here we have, we're gonna do a create server. This is gonna define some larger scale options that we're just gonna pull in when we create the table. So we create a new class in Python. We inherit the multicorn foreign data wrapper class. I didn't bother renaming it as multicorn foreign data wrapper, I just called it foreign data wrapper. And then we initialize our class and we initialize it with the options we're gonna specify up here. And this lets us set the IP address, the username and miscellaneous things. Let's see if we can do it. Control, cause you guys like code. And then click here. And then control D. And I'm watching the time. So I've got about 15 minutes left here to show you everything. So we're gonna speed up a little. So here's the init under the, after we have established the class, we set our options. And then I also set some global variables for the class in the initialization. And that is the entire init function. That was a very fast run through of it. You're welcome to pull the github and ask me questions later. But I now have this initialize. So I can call the server command. I can't do an update or a select yet. That's the next thing in this list. Let me go back to the presentations. Next slide. So in multicore in the select is called execute. And this confused me at first as to why would they would call it execute. And I think it's because when you do an update, it actually runs the execute first to get the primary keys for the rows you're updating. And then it does the update. So you need the execute to be able to do updates as well. So it's not just select. So they renamed it from select so that you could tell that it had more than one use. So in the execute statement, we'll do a get to our API, get back all of the lights that we're getting, iterate through the lights. We will set them in an order dictionary so that the rows stay in the same order that they were requested. And then pull in each column value and set it. And then I'm gonna go ahead and check the filters that somebody put on it where light ID equals two, where brightness equals 100, where the color is red. And I do it this way because most of the time you're never gonna have more than 64 light bulbs. So you should be able to iterate through 64 objects like really fast. If you had a huge set of devices, you would probably want to work on it another way. The hue API does not let you do a get and get the state of all the lights by some criteria in there. You can't do a select on the API. So we have to do the select and filtering after we get all the lights back. It's kind of clueless but this is the way it's gonna be for all the things we implement. Each API is gonna have its own idiosyncrasies. So we pass quals and columns into the execute. Columns are the ones that the user requested and quals is the filters in the where clause. Not every possible where clause that you're gonna construct is gonna work because you have to implement how to handle the where clause yourself. So I have this thing here called operator function and I pass it the operator from the Postgres request. I get back a Python function and then I use the Python function to check and see if that column and value match. Let me show you that real quick in the code. Some people like talks with lots of code. Some people don't. I apologize if you don't like looking at code. So here's where we do the get right here and then we take the results and turn them into a Python dictionary using a JSON processor and then we iterate through the columns and I'm going to assign each column a value in this new dictionary that's an ordered dictionary that I'm returning that has the columns in the order the user requested them and then I'm gonna do the quals. So I'm going to throw out any rows that are not meeting the filtering criteria. An operator function is something I wrote. I have a slide on that next that's coming right back here. The last thing we do is we yield the row. In Python you can do a return or a yield from a function. A yield returns the rows one at a time. Return would return the entire data set at once. In the smaller cases it doesn't really matter but it's a good practice in foreign data wrappers to use yield and just return the rows back into the system as you get them. Postgres will do the ordering by the way. You don't have to sort them. You just have to handle the filtering. So I took and built this little function. I think it should be part of the multicorn base package and it's not yet. So I include it with the foreign data wrappers. It has every possible operator from Postgres that I could find and then maps it to some sort of Python function and sometimes I have to write Python functions to do the comparisons. And you're welcome to grab this and use it in your foreign data wrappers and it'll save you time. I was noticing a lot of the multicorn examples people rewrote this every time. So there's a bunch of functions in here that do various comparisons on the data so that you cannot implement quals in your select statement. And I may have missed a couple if I did, let me know, but most of them are here. And none of the JSON ones I think are in here. Are they? Yeah, they are. Well, no. So I didn't implement the JSON comparison ones. But this gets you started so you can do your basic select statements and stuff like that. Let's look at it again. Everybody's falling asleep. Here's my lights update. My lights set is on equals true where light. It's kind of dim, update my lights. It's not brightness. So select star from my lights where is on equals true. I get one row back. Let's go back here. And then this operator function just does a comparison with whatever this returns. And so you can do your testing. Updates work similarly. You have to define your primary key somewhere because this is a fixed single table foreign data wrapper. I can hard code the foreign key and I know it's always going to be the same. We call it, multi-core and we'll pass to the update function one row at a time and it updates one row at a time. That's the way multi-core and works. So if I put all the lights on, it'll do the first one and the second one and the third one. It doesn't try to update them all simultaneously which could be an issue if you're doing some sort of transactional thing where you really need them all updating concurrently. But that's the way it's implemented in multi-corn. One of the things I wanted to point, yes. Is it? Okay. I haven't used the other foreign data wrappers that much except for multi-corn ones. Right now. All right, great. So one of the things that I found was interesting is if you said something equals true, multi-corn translates that into the letter T or post-christ translates it into the letter T. The API doesn't accept the letter T. It wanted the word true or a true value. And so I had to translate it. So you're gonna, from running the cases in the API where coming out of the database is gonna be something that doesn't really map and you're gonna have to translate the actual value that the user specified into something that the API can understand. Okay, that could be. It's all still pretty new stuff. So another thing we have is logging and this is really useful when you're developing is to be able to figure out what's going on. You use this command log to post-grads. You can do it in exceptions or by itself. And you have three levels, debug, warning, and error. Well, you have more levels in that, but those are the only three that really are useful. In error, you stop everything the user's doing and say this totally failed and you really didn't do something right or something went totally wrong with the API. Warning says, I'm gonna do it anyhow, but you probably didn't want to do it. And then debug is more along the lines of this variable was equal to this value and is very useful when you're debugging. You can't see those debug unless you, I had to go to the second page because I ran out of space. And unless you set log min messages equals debug one or higher in your post-gradsql.conf and do a PG reload. And then, yes. You go up the set. Probably, yeah. I usually just set it there because every time you make a change to your code, you have to push the new code and then you have to exit your session and go back into your session in order for your new code to be recognized. And so I'm going in and out all the time and rather than set that every time I log in or put it in a init file, I just set it on the database as a whole. It's much faster. If you throw a Python stack trace, you can usually find it in your post-gradsql log file. I couldn't understand why I couldn't see it in my screen but it was in another window where I was telling the logs. And I just mentioned this. You don't need to bounce your database every time you do this Python set up UI install but you do need to log out and back in again to pick up the change. So you drop in and out of your session a lot as you're troubleshooting this. So the first thing I wanted to talk about or the next thing I wanna talk about was reference tables and I had mentioned earlier one of the advantages of a database of things is you can correlate your things to a reference table. And these light bulbs use an XY system for defining the colors and it's really hard intuitively to know what color you're setting your light. So if you wanna set it to that shade of blue right there you have to know that X coordinate and that Y coordinate. It also only takes the values inside this triangle and then you could also use a color temperature or you could use a hue saturation and brightness but as somebody who's done a lot of like D3 programming I much prefer to use HTML color names to set the bulbs. So I built a reference table based on these color names here, let's see if I can pull that up. So I found this webpage which had 764 color names with RGB values associated with them and I wrote a script that translated those RGB values into the XY values and then loaded that into a table. This is all in the GitHub repo. Oh, and now it's gonna show you how that works. So then you can do an update, select from the color table like this and change your colors. So let's try that because it's fun to play with this. This is where I can use huge amounts of time. I'm gonna set all the lights on. It remembers the colors the lights had when they were shut off. What I'm looking for is my previous commands were as changed in the colors. Here's one, let's run that. So you pick a favorite color and you can change it to whatever appeals to you and I don't have to figure out the XY for it. So this is a use case of using a reference table to manipulate my things from within the database and I just keep trying different colors. One thing that's very disappointing is these particular bulbs don't have very good greens. So if I try something like that, if I try other shades, I've tried all different kinds of shades of green and they're just dull. And the other thing they don't do very well is brown. So I know. And so if I go back to my presentation real quick and jump back a slide here. Oh, too far there. This triangle is the limits of the colors that these bulbs can produce and there really isn't much in the green world that these bulbs can work with. So when you push pick something that's outside the range, it just picks the closest thing on the border to it. And so the browns are probably up in here. I don't know. It's fun to play with and really discover the limitations of the bulbs by doing update commands. I can't stop playing with them in the room. So here's a couple more kittens. The last thing I did was I made a very simple table which I called donors. So if somebody inserts a name and a donation here, the trigger on that table will cause the bulb to blink. And if somebody gives us more than $10, all the bulbs blink. Once you start playing with this number of applications you can think of, just keep escalating. So in extra, I have that donors table and I put some SQL here so I didn't have to type it myself. All right, so cheapskate one donates to us. We're still kind of excited to have money, but you know, thanks. Cheapskate two does the same thing. Cheapskate three and they all blink because we're very excited that we had a $20 donation. So I don't know if you could see, I could do a shift command plus here so you could see what I just typed. That is the essence of the database of things. I think what we have is a common central platform for managing the internet of things that developers can all use that could really explode in the next couple of years or six years I don't think any other database can do this besides Postgres and that's pretty exciting since the industry needs it. I need to thank some people. That's my dog trip out in the state park with me. Dustin Legans, he's here today. He actually paid for these bulbs so that I could do this presentation. Dave Wilson is the CEO of our company and he paid for me to come to this conference except for the ticket which was waved because I spoke. Jeff Kosovant and Brian Hoover-Bryans here today, they reviewed my code and helped me with the colors algorithm to do the translation of the colors table for the references, all the rest of the people at work. So I had these light bulbs plugged in at work and every now and then I would like change the colors and make them blink and flash and stuff. And if you can imagine, you're trying to do like a serious job and you're working all day and somebody keeps changing the lights over your head. They were very patient and kind to me about letting me get away with that. The people here who made this possible and my wife and kids, I'm gonna escape out and just take a look at this one script I ran at the beginning, if I can, how come, oh. Does that help if I do this? I'm trying to figure out why it doesn't fit on the screen anymore, that's better. So at the beginning of the presentation, flashystuff.sql, this was just to get your attention. I turn all the lights on. Oh, how come you can't see that? No, you can see it. Sorry, that's better. Let me, no, I can't see it. Okay, I can see it. Turn all the lights on and set the brightness to the full brightness in one update statement. Sleep for a few seconds. Change one of the colors to green. One of the colors to purple. These lights do really nice purples. And one to orange, red, sleep for a few seconds. This L select command sets some blinking for 30 seconds. So I set each one blinking for 30 seconds with a short delay between them. And then color loop command sweeps through the full color range of the light bulbs. It's a built-in effect to the API. So I run that and sleep for a few seconds and then at the end I turn it off. So let's run that and we'll see if people have specific questions. Oh, yep, thank you. So I set them all to red. Sets the three colors. Sets that one blinking two seconds later. Sets the next one blinking two seconds later. Sets the next one blinking. And then they blink for 30 seconds and then they start sweeping in color. So they're changing colors as you watch them. This is fun to play with. I made this little light stand last weekend in my garage. I was worried about taking it through TSA, so I shipped it here. I have no idea what housekeeping thought when I assembled it in my room this morning and had it sitting in there with all the lights on. Excuse me? Can you synchronize? Oh, yeah, well, there are apps out there. You can get that, do that. And I'm sure that you could figure out how to do your SQL commands and coordinate them with some sort of music. And do that through the database instead of through a dedicated app. And that's the idea with the database of things is once you write this, if somebody comes out with another light bulb app, that's better than Philips Hue. You could just change the table name and it'll work. You don't have to write a whole new app that talks to a whole new API. Yes. Why did the Postgres, as far as I know, is the only database with foreign data wrappers. I think there's some SQL med stuff in Informix and there may be other databases out there that are looking at it. And if you like Python, Postgres, this is the way to go then. I'm gonna put this, thank you, slide back up while we're talking. So on here I also have my Twitter handle and the Twitter handles at work. They were live tweeting during this presentation. So if you wanna follow them, they would appreciate it. If you need any marketing type of work, give us a call. We have fun doing multi-channel, data-driven marketing. Anything else? Yeah, well, to the database, doesn't care if it's IPv6 or not. But that's an issue. This is a UPNP device and if some clever hacker had been sitting back there and knew how to hack UPNP, they probably could have changed the colors around and messed me up sitting in the room. I don't have this on the wifi, so my laptop is plugged into a little hub and then the hub is plugged into the bridge and then the bridge has a proprietary Phillips Hue wireless communications channel that it uses to talk to the bulbs. And then each of these bulbs actually talk to each other so you can extend the range of your Phillips Hue system by making sure you have a bulb in between two other bulbs so that you can reach out and find them. But all that wireless stuff, I mean, I imagine somebody with a good de-scrambler of some sort can get in and turn the lights on for you. I've read some fun stories of people messing around with that in people's houses on them. They're really neat though. You can get an app that can syncs it with a TV show. So if you're watching TV, it changes the mood lighting in your room to reflect the scene that you're in and you can play music with them. You can make them voice activated. I just made them work in the database. Thanks.