[ Silence ] All right. Welcome back to Computer Science S-75. This is Lecture 5, SQL, Continued. So this worked out so well last time. I thought we could start with the same question. What did we do last time? Yes. Posting into internet SQL. OK, good, so an internet SQL. So structured query language. Someone from this side now, what is SQL about? Yeah, Jack [assumed spelling]. It's a nice way that we can interact with the data that we can store in MySQL database. OK. Nice way to interact with a data that you can store in a MySQL database. And in what sense, Axel [assumed spelling], do you interact with the database? Well, you can interact with it because it runs on a port on a server-- OK. -- and essentially just stop it via a terminal window-- OK. -- type commands but there is a GUI that's called PhpMyAdmin-- OK. -- that we use together with MySQL. OK, good. So we've seen at least a couple of ways to interface with the MySQL database. You can use the command line or the actual MySQL client, which is the sort of retro black and white window, which is wonderfully useful but also limited just in terms of its screen real state. We also looked at a tool called PhpMyAdmin, which is popular. It's not the only tool but it tends to be popular. It's a coincidence for our purposes that it's actually written in PHP. We haven't looked underneath the hood but if you did start poking around, you would see that that entire GUI is a dynamic website implemented in PHP but there are others. If you're a Windows user, there's actually a downloadable Windows client that you can use natively on your own Windows computer and connect to, for instance, a remote MySQL database. Generally, you cannot, though, connect too far away MySQL databases. The reason being that the communication is not, by default, encrypted in any way and indeed MySQL itself does not really have very good built-in functionality to do the equivalent of say HTTPS or SSL. So it's generally best practiced to keep your MySQL database on the same network as the machines that are actually talking to it. So to be more clear, you could not generally sign up for like dreamhost.com, have MySQL database somewhere out there on the internet and then use like this Windows client to connect to it because everything you've been sending, including your password, in the clear. So be mindful of that. All right. So that's how you interface with it. What kinds of queries or statements did we see last time are possible? INSERT INTO. INSERT INTO. So this is for inserting rows into a table. We call that MySQL databases or relational databases, which means there are rows and columns. What other statements did we see? SELECT and then would you want to select a star, a wildcard star or natural-- Good. So we saw SELECT queries like select star from the table name. And that gives everything or you can say select X, Y, Z, that will just give you those three fields and then we introduced the notion of a predicate, and a predicate lets you filter that result set, much like XPath lets you filter the node set that comes back. You can say where user ID equals 1 or where email address equals JHarvard at something dot something or the like. So you can filter your queries and you can even Boolean and/or them together. So you can say give me anyone whose gender is male or gender is female. So you can get back to everyone in that way even though star would suffice in a case like that. So in short, we have this ability to filter our results. So that's SELECT. We've talked about INSERT. There's DELETE. We haven't really used this one yet but you can use DELETE to delete rows as you'll find. What's another? Yeah. UPDATE. UPDATE. So UPDATE is for actually updating existing rows. This might make sense if a user updates their password, their name, their phone number, whatever the case may be. So, for those unfamiliar, realize that this kind of database follows a paradigm that has kind of a silly acronym associated with it but handy to know. This is the-- literally, the CRUD model for database queries. Anyone want to take a stab at guessing what this acronym means? It's really kind of stupid. At least the acronym but the capabilities that it's describing is actually quite common. So C is for-- Yeah. It might be Create, Read and then something and then Delete. So close. Yes. What's the something? What's the U? Update. Update. That's all it is. So Create, Read, Update, Delete. These are the incarnation of this acronym in SQL is what we've already said, create is insert, read is select, update is update, and delete is delete. But you'll find that in other context where you have this kind of expressive capabilities, that it follows this so called CRUD model. So just FYI if you come across that. All right. So, let's introduce a couple of new features now of SQL and then we'll try to take a stab at designing something a little more complex than last weeks user's table. So recall that MySQL has a whole bunch of types. And here we have on the top left, string-related ones, then dates and times, then numeric ones, then some floating point values and blobs. Let see if we can pluck off a few these interesting characteristics of each of these. So when might you use a VARCHAR field in a table? How about someone from this side again? VARCHAR, when should you go for a VARCHAR? Yeah, Louis [assumed spelling]. Middle initial. OK, so a middle initial. Why? Because you'll know like the length of it and see the space. OK, good. So it's probably just one character, maybe two or maybe zero. And so, if that's the case, you might not necessarily want to waste space unnecessarily. So initials, not necessarily a very compelling case because we're only talking one or two bytes. So what's another scenario that's a little more compelling for a VARCHAR? When you use it when you do not know the length of the thing that user inputs. Yes. Username or name. Good. So for something where you really don't know the length and an initial, I mean, most of the time, it's one or zero maybe two, but I'm hard-pressed to even think of a two-letter one. So for something that's a little more variable by nature, email address or name, then a VARCHAR might be reasonable because then you can say anyone's name can be up to 30 characters but frankly most people's names are a few characters, 10 maybe, if that. So, why waste 22 or so additional characters, bytes in your database, storing them unnecessarily if do not need them. So there's a price we pay, though, for this. What's the downside of saying variable length CHARs as opposed to just a CHAR field? Yeah. Searching is slower if it's not on the same size. Yeah. So searching is most likely slower even if it's imperceptible for certain cases. In large scale scenarios, we have many, many, many rows of data in your database and you actually want to search those rows efficiently. One of the upsides of using a CHAR, which by contrast is a fixed length field, is that you can generally search that field more efficiently because you don't have sort of this variable length to your cells in that table so to speak, rather everything is fixed length. So just like an array allows you random access, similarly might to be able to leverage the idea of random access in your column if all of the lengths are identical, eight bytes, six bytes, sort of the like. The downside of course of a fixed length is that if your middle initial is just one letter or if your email address is fewer than the fixed length you've come up with, well, then you're just wasting space. So it's a tradeoff, we just do not get anything for free. What about text fields? Why did we introduce those? When are they useful? Yeah. I think text was for like longer texts. OK, so longer text. That were much bigger than what you would typically-- say the length of the VARCHAR, some text would be very much bigger than VARCHAR. Good. So much bigger than a VARCHAR, typically. So if it's like in essay fields. So if it's like a college application kind of thing where you want people to upload big blob-- actually, college essays are what, like 500 words-- So probably not a good scenario there, but if it's a much larger corpus of text, maybe it's the contents of a web page that you screen scraped and you want to store all of that data in just a big chunk of text, maybe you would use a text field. And one of the upsides of that, as we'll probably encounter in PhpMyAdmin again tonight, is that you can specify that you want to have a full text index on it, which means you can use Google-like queries to search for keywords in that field, and you don't get those same capabilities in for instance a VARCHAR or a CHAR itself. So what does this mean? Well, typically, when you're using a VARCHAR or a CHAR, you can say something like this, SELECT star FROM users WHERE what might be reasonable. How about let's say WHERE citystate-- oops, autocomplete-- WHERE citystate LIKE, and now I do not necessarily know what their state is going be but I want to search for all states, let's say containing the word let's say "New". I do not know why I want to do this but I want things like New York, New London. I want to get back all of the rows in the table where people are from towns whose names contain the word "new". It's completely arbitrary, this particular example, but how can I express this? Well, there's the LIKE operator in SQL whereby you can pattern-match, and the fact that I've used this percent signs happens to be the wildcard character in the context of a predicate. So in this context, it's not the star, it's in fact the percent sign but this allows me to do pattern matching inside of a CHAR field or a VARCHAR field. However, if I instead make something a text field then I can actually use a more powerful matching expression that allows me to say, "Give me all the things that match this keyword and this one," and then your database engine can even rank them in terms of relevance which might be advantageous if you're building a search engine for your site or the like. But to do that it needs to be a text field. This is probably-- typically the more common one when you're just trying to look up queries. And you'll see this perhaps in MySQL if you actually leverage its search tab, which we didn't use last week, but you'll see it among the various options. All right. So, dates and date times and years. Why use something like a date or date time or year field when clearly, you could implement this yourself with just CHAR fields or VARCHARs? What's the point? Yeah? It's probably more standardized and may be more efficient. Good. So more standardized and arguably could be made more efficient. For instance, to represent a year, we humans, we typically write out four characters but maybe a computer can do that a little more efficiently by storing the number of years from some offset, 1970. Now, that would be dumb in this case because then you can never store other years but you-- stands the reason that the computer could come up a more efficient representation than you plus it is in deed standardized. You also have the ability in MySQL to call functions that are built in to the database engine itself. So, you can manipulate things like dates and times just like you can actually do in PHP and in other languages. But if you ask for a date, you can get it back in-- a date time rather, you get it back in your preferred format, maybe it's 24-hour time, 12-hour time, you get that kind of flexibility. And so it's standardized and it's better typed inside of your database. So, it's not just some free form string that you're trusting yourself and other developers to actually insert reliably. And in term of numbers, TINYINT SMALLINT MEDIUMINT, INT, and BIGINT. So INT is pretty common to use. Facebook, for instance, use INTs early on for its IDs. But what's the downside of using an INT for things like Facebook IDs? Yeah? You eventually get a lot of them might run out of the-- what you can store in it. Yeah, exactly. So there is a finite number of INTS, which in this case are 32-bit values, and if they're unsigned, you have as many as 4 billion values which frankly would be a good problem to have if you site has more than 4 billion users, and therefore you've overflowed in INT, but you got to think about and in Facebook's case, people are signing up for fake accounts and they're already had like 800 million legitimate users. So, they're getting up there anyway, and so it was definitely a scenario in which they would not have wanted to use 32-bit INTs long-term just because you're fixed in the maximum number values that you can use there. So, frankly, these days, if you're doing anything where you anticipate a huge number of rows, it's not unreasonable to use BIGINT, which would be a 64-bit value and what kinds of scenarios, well, probably not users, you don't need 2 to the 64 possible users in your system most likely. But if you're writing things out like log files or you're recording transactions of some sort where you have no idea how many widgets you're going to sell in your e-commerce site, or you just don't want to have to bound yourself, it's probably worth spending an extra 32 bits just to ensure that you'll have a higher probability of uniqueness long-term, especially when consider the auto_increment feature. And this is kind of one of the little downsides of it. What was the auto_increment feature of a numeric field? Yeah? If you add auto_increment, it's automatically going to add into itself, so you're going to have, say, an automatic unique ID-- Exactly. -- so you don't have to query the database with the largest number, take it back to the server, plus one, and then INSERT. Exactly, so with auto_increment, by contrast, you can tell the database, this field here-- let's call it ID as we did with the user's table last week. You can just say, this should be number and it should start at one and it should forever auto_increment for each additional insertion that I do. And that's fine and you can get up to 2 billion with signed numbers, 4 billion with unsigned, or 2 to the 64 with unsigned 64-bit numbers. But, what you don't get is the ability to reuse numbers. So, if you delete, for instance, a user from that table, their number is not going to get reused. So you could end up getting sparsely-populated data, which isn't necessarily a bad thing, but that just means that even though you might not have 4 billion users, you might have used 4 billion IDs, it just so happens that a bunch of those people deleted their accounts. Now, you could go through and change around people's IDs but if-- I mean, if you do use Facebook, just imagine how many thing would break if you started changing people's IDs, any URLs people had copied and pasted around the world containing a user ID would now be invalid if your re-- changing people IDs around. So that's probably a bad idea. And frankly, it's just expensive to go back and figure out where are the holes in your data, so most databases just kind of forge ahead blindly to higher and higher numbers so at least you have more wiggle room with something like a 64-bit value. So those are some of the gotchas. All right, so floating point numbers doubles are what you would expect in most languages. Decimal is an interesting one and it will come up probably in the context for you of project one which will talk about tonight, where you want o represent money somehow, dollars and cents for at least US currency. What's the problem with representing money in general with a floating point value in any language really, yeah? If you ever want to display it and it has a cent value that runs like 90 cents or 80 cents, it cuts off the last zero and it just looks like $12.9 or something like that. OK. So, sure. So there is this aesthetic issue involved in floating point numbers where if the amount is a dollar 90, as we would see it on a store, well the zero is not strictly necessary for that number's representation. So a computer, by default, might just do dollar sign 1.9 and that's it. So that problem we can fix pretty easily by just formatting it as a correct string with two digits. But there's an underlying problem whenever you use floating point values, whether it's a float or double, 32-bit or 64-bit real number. What's the problem? Why should you not store money as floats or doubles? Yeah, Jack. Rounding issues. Rounding issues, what do you mean? It's like-- from my experience with floats and doubles, occasionally if you try to store money in or any value in, and you want to get down to the exact value back in the end, you're going to end up with 0.999 and something-- Good. Exactly. In fact, there is an infinite number of numbers that you cannot represent precisely using a floating point value, whether it's a 32-bit float or a 64-bit double, and you can think of it is follows, if you have a 32-bit value or even a bigger one, a 64-bit value, there's still a finite number of numbers that you can represent with those bits. However, how many real numbers are there in the world, where a real number, in this case, is something with a decimal point and a number before and after it? How many of those are there? Infinite. So there's an infinite, right? But if you only have a finite number of ways of representing real numbers in a computer, as you do with the float or double, but there's an infinite number of them, you're going to have to round or cut corner somewhere. So the problem of representing something important, like money, using floating point values, is that you might be trying to represent a dollar 90. But you know what, the closest the computer can actually get using its bits is $1.89 cents or $1.89.9999 cents, which is not exactly right. So, how many of you have ever seen, "Office Space" the movie? Just you and me? Just Louis and me? Or "Superman 3", even less likely? Chris [assumed spelling] saw that one as well. OK. So, if you haven't, this is a good excuse to watch a movie. You can say it's for class. Watch the movie, "Office Space", which is the funnier of the two. And there is a scene in this movie-- this is not a spoiler at all, but there is a scene in this-- it's kind of a spoiler, but you-- by now, you should have seen this. It came out like 10 years ago. So, there is a scene in the movie where these guys are trying to steal money from their company because the company's computers are not taking into account this kind of imprecision of values. So, essentially, they write a program that says anytime there is an amount, like a dollar point 89 or rather, let's say a dollar 90.00001, which would normally be shown as just a dollar and 90. There're still some fractions of cents there, right? There's an opportunity to steal those fractions of cents but the company would never know because all they see on their paperwork is 1.90. So, they write a program to steal all of those fractions of pennies and hilarity ensues as a result. So a real world example of floating point imprecision. Excellent movie called "Office Space." All right. So, blobs, we haven't used them, not really going to use them, but what could they be used for? Yeah. Images. Images, yes. So, binary objects of some sort, binary large objects. But again, I would generally recommend that if you need to store binary data, you store in a folder on the file system, and then store in your database instead what? Yeah. Path. Just the path. The file name or the path to it, whatever directory it happens to be stored and plus its filename, something like that. All right. So, here are just a couple of functions that are germane to the data types built in to MySQL. If you go to that URL there, you'll see a whole bunch of others that are built in, things like averaging and summation and the like. But here's just two very common ones, date formatting and time formatting. So long as you have stored a date or time in one of those date time-related fields we looked at, literally date, time, date, time, year and so forth, you can call this function in your SQL query to get back a different representation of that. So, how does this work in practice? Well, instead of doing something like SELECT sales date From-- OK, auto complete, just going to keep doing this to us. SELECT salesdate FROM mytable. If instead, you want to not get back something like-- and actually let me change it to this. If you don't want to get something back that's not very user-friendly like 2012-07-01 at 23 hours, 1 minute, and 23 seconds, like this is what will come back by default, that's not a very user friendly. You could get that back and then use something like PHP to massage it into something a little more user-friendly or you can actually say DATE_FORMAT, passing this in, and then passing in something like Y-M-D to just get back the month, or you could something, like M/D, to just get back month/day and so forth. And you can do something similar with-- you can do something similar with times as well. All right. So, just a couple of functions but realize that functionality exists. But now, let's take a look at one other fundamentally different approach from last time. So, here's a snippet of representative code that actually doesn't do all that much besides connecting to a database, but uses different functions than we used last time. What were the functions we used last week to implement our various log-in examples to connect to a database and select the database and so forth? Yeah. You do MySQL and then Connect. Good, so mysql_connect. Then there is mysql_select_db for selecting a database. Then there is mysql_query for querying the database. And there's a whole bunch of others. In fact, if we look this up in the documentation, let me go ahead and pull up a browser. Let me go ahead and search for MySQL PHP. That will pull up the manual page on php.net that has a listing of all of the various functions here, and the ones we're are about to get back here, albeit a little slowly every time we try to do this. There we go. All right. So, you can see here a list of most of the MySQL functions. The rest are cut off. And there's more here than we probably are going to care to ever use. But mysql_connect is up there at the top. Mysql_fetch_assoc is there. There is another one, mysql_fetch_array, which allows you to get back in numeric array if you would prefer. There's mysql_fetch_object if you're familiar with object-oriented programming. You can actually have the database, or rather the library, return to you a PHP object inside of which are properties that represent the various cells from a row. And a whole bunch of other ones here. There's mysql_num_rows. We use that to figure out how many rows had come back in our result set and so forth. But there is a downside of using this built-in library in PHP. One is the function names are atrocious. The worst of them is recall mysql_real_escape_string. Now, just because it's got awfully named, it doesn't mean it's a bad thing to use. In fact, not using it is a far worse sin than having use that particular name since you'll be vulnerable to SQL injection attacks, which we talked briefly about last time, but we will look again more closely at the end of the semester when we focus on security. But also, you're doing a couple of things that are little short-sighted. One, you have to-- you're tied now to MySQL. If you ever decide or your new boss ever decides or whatever, someone decides to change the database from MySQL to something called PostgreSQL or Oracle or the like, you literally have to go through and rewrite all of your code or at least do a massive find and replace, and even then, some things are likely to break. So, you've just committed yourself from day 1 to using MySQL. And maybe that's fine and maybe 9 times out of 10 that's fine, but for the other scenario is it really worth the aggravation down the line? But more compellingly too, it's so much easier to make mistakes, right? If you-- as soon as you start writing code for project 1, which involves using a database, the very first time you forget to call mysql_real_escape_string, that's all it takes for your site to get compromised. And at that point, it would be so nice if instead someone else could do the scrubbing of data for you so that the burden is not on every one of us in this room to have to call this particular function every time we want to protect ourselves against theses kinds of attacks. Better would be to use a library that does that kind of escaping for us. So enter into the picture something called PDO, portable data objects, which refers to a code, like this, that abstracts away the detail of what kind of database you're using. And by that, I mean, you can use the exact same code to talk to MySQL, to talk to Oracle, Microsoft Access even SQLite, which recall is just a little binary file that you store locally on disk. So you have this layer of abstraction now so that the code you write doesn't have to change at all if you do change your database. The only thing you have to change is a variable or an argument that you pass in up here in the top, for instance. Notice that I've said a variable called DSN, by convention mysql:dbname=lecture. So that's really no different from last time, host=127.0.0.1. And the only part of that line that I would have to change if I move to something, like Oracle, is the one mention of MySQL. So, essentially, that's a unique string that identifies for this library what type of driver to use, MySQL, Oracle of the like, and then what the data base name is and the host actually is. And then the user and password have nothing to do really with the type of database you're using. So, what do I have now? So for those unfamiliar with tries and catches, this simply has to do with exception handling, so this doesn't have anything fundamentally to do to this-- with this idea of using this library, but the way it works is that when you call a new PDO and the jargon here is instantiating a new object of type PDO. You pass in a couple of three arguments, the connection string, user, and password. And then if something goes wrong, rather than returning false as is the case with a lot of PHP functions, rather than triggering an error which generally forces your program to die, it instead does what's called "throws an exception". And if your code has not been designed to catch any such exceptions, your program will quit, right then and there, crashing on the user effectively. So instead, if we say "prepare to catch" a PDO exception, and if one happens, let's call it $e, at least, you can, in a slightly more user-friendly way, handle that kind of error. Now what might the error be, username or password is wrong, MySQL is down, something like that, but this line of code essentially, becomes our equivalent now, of mysql_connect. Now, beyond this, and you'll see in section tonight as well as with this tutorial which we refer to in project 1, pretty much the-- all of the lessons from last time and all of the lessons from tonight about SQL itself still apply. This doesn't change SQL, this doesn't change your selects or your updates or your inserts or deletes, all it changes really is how you initially, connect to the database, and it also just changes what function you calls. Instead of mysql_query, you're instead going to execute a function called EXECUTE or another one called PREPARE and the nice thing about PREPARE is that it creates in database terminology, which called a prepared statement. This is sort of like a compiled SQL string, so if you have query you execute a whole lot, select star from table where foo equals bar. If you're constantly executing that string-- that query, but maybe only one part of it is changing, maybe the value you're searching for is what's changing, so select star from users where ID equals something. If you want to prepare that query but call it again and again and again, and every time you call it, you just want to change what? The ID, that you're searching for, you can call PDO's PREPARE function and this will essentially optimize that query to just have one part of it changed on each iteration. And the other upside is when you plug in an ID to a prepared statement or really plug in any value to a prepared statement, guess what the library does for you for free. Yeah. It escapes it. It escapes it, so you no longer have to think about any of that. So understanding it still good certainly, but you no longer have to worry about trusting yourself to write a code that's 100% correct when it comes to security. So again, let's see some examples tonight in section. This is what we'll have use in project one, but realize this is just a layer on top of those MySQL functions that come built into PHP. Now there's the PDO library which just solves a couple of problems that you might, otherwise, run into. All right so last time, we had life pretty simple. We only had one table called "Users" and what kinds of fields do we have in our Users' table? Yeah? Username and password. Username, password and what else? And we have user ID. We eventually added a user ID. Why did we introduce a user ID, yeah? To be the primary key. We wanted a primary key-- a primary key, which you uniquely identifies every row in the table, but why not just use username? Because username may be duplicated and maybe the unique one. OK, well let's assume that usernames, by design, will be unique just like the numeric IDs will be unique but what was better about using an integer for the ID, the unique identifier, as opposed to just trusting that usernames are unique? Yeah? The [inaudible] will be sending a lot more data if you send the username. Yeah, exactly, it's really, a performance thing. If users can have variable length usernames, three characters, four, maybe even 12 characters long, if you want to look up a user, that means you have to search for 3 or 4 or 12 characters. By contrast, if you also assigned users a unique numeric identifier like an INT or BIGINT, then you are spending exactly 4 bytes of maybe 8 bytes on every user, so you again have a fixed width table effectively and it's just much more efficient in general to search for integers, whether they're 32 or 64-bits, than for arbitrary length strings. So that's the reason we ultimately introduce the ID field but the problem too, is that we started telling the story about normalization, and we started talking about, "Well, what if users have cities and states and zip codes?" There's obviously going to be some redundancy, and so we propose factoring out what, from a user's table, if we also want to store their addresses, cities and states and zips and so forth. Well, how did we solve that inefficiency? Yeah. We store the zip codes instead of the actual address. Yeah, good. So rather than store Cambridge, mass, O2138. Cambridge, mass, O2138. Cambridge, mass-- so it's already getting boring to say and imagine just storing that again and again and again for every user, we can do better than this. Well, what do we minimally need to identify user's locales, probably just the zip code. So now we could have a zips table that has city, state and zip as its columns, but the only thing we put in the user's table is which of those three fields? Zip code. The zip code, exactly. So, that's nice but it feels like the price I'm now paying is every time I want to look up a user and figure out their address, now I have to do a select on the user's table and also another on the zip's table. And my God, imagine if we did this with other pieces of data as well, all right? If we have other common pieces of data, would we have to now select from a third table or fourth table? It feels like we're solving one problem but introducing others, namely ones related to performance. But thankfully, relational databases allow you to join tables together. So let's take a look at this as an example. So at top right here there're a couple of tables. This is taken from a site called w3schools.com which has varying degrees of accuracy but this particular example is decent to work with. We have two tables, employees and orders. And in the employee's table we have a field called Employee_ID and then another field called Name. So it looks like Ola has an employee ID of 01, Tove has one of O2, Stephen of O3, and so forth, so nothing too unreasonable there. And now under Orders, we have a product ID, a product, and an employee ID table that apparently is specifying who sold these products. So it's just some kind of log, maybe it's for commission so you know whom-- who to pay and how much to pay of who sold what. Well, let's start finding some faults with this design and see if we can't fix but let's also consider what they did that wasn't bad. So at the top, the employee's table, if you were doing this table, what would you have done differently? Isaac [assumed spelling], that counts as a hand up. Well-- Find faults with the employee's table at the moment. [ Inaudible Remark ] Sir, that's actually-- I forgot your name. Scoot. Scott, yes. Find faults with the employee's table. [ Inaudible Remark ] OK, good. So we might as-- we have a database, right? We might as well tease apart, first name and last name because storing it with just the commas is just completely unnecessary. It's as though we've created a fake column by sort of merging together the idea of a CSV for the database table, why not just put last name field and first name field? So we could do that. So that's pretty good. All right, and what else? Well, for some reason, these employee IDs are written as 01, 02 which suggests that they're using like a CHAR or a VARCHAR. And that's just dumb at this point, right? It should just be a number and if it's an actual number, it would suggest that there's not a prefix of zero but now we're just kind of inferring from the example. But that could be a potential fault. So now, what about orders? Well, it looks like here that what they have done which is good, is they have not put the employee's names in the order's table. They've just put the employee's employee IDs. So this is similar conceptually then to not putting city, state, zip in a user's table just putting zip. So that seems like they made a smart decision, what was kind of stupid though, nonetheless? Find fault with the order's table now. Jack, that counts as a hand up. Everyone has to stop scratching their head. Find faults with the order's table. I don't really see much fault in the order's table, you know, except for maybe that old one, like the employee IDs or being 01, they have 01-- I'm going to decline that one. So no, I can't reuse that idea. There're still faults here though. Yeah. You could have an order ID. Yes, so we have no way of uniquely identifying these orders and maybe there's no use case for it but that does seem inconsistent with the idea of having employee IDs and product IDs. And even beyond that, there's still-- there's pretty bad fault in the order's table. Axel. I don't know if this counts as a fault but I really wouldn't order the tables from the employee ID and order from the product ID [inaudible]. OK, order the table from product ID. But isn't that what they've done it on the left? Yes, but what about to the 35 and 36? Oh, to the 30-- That's just arbitrarily. I see. So let's-- it is an arbitrary example and maybe those were also got deleted because they were canceled even though that's kind of a lot of cancellations. Jack. I figured it. They have product ID, and the product stored in the table while-- yes, they have a product ID they can store that somewhere else. Exactly. [ Inaudible Remark ] Exactly. So whoever made this table kind of got it half right and they did factor out the employee ID and put only the employee ID here, but then they sort of forgot that lesson learned and seemed to be duplicating product name, product name, product name, product name, and you can't see it here but suppose another person orders another printer. Well, what's going to end up in the next row? Well, it's going to be a product ID of 234 but then the word "printer" in the second column and then whoever the employee ID is that's sold on that printer. So we probably should introduce another table. What should that table be called reasonably? Products. Products, right? So like a product table. Inside of which is a product ID field, and a name field which then suggests that how should we fix the order's table, what should go? Yeah, Isaac. Just product ID. So just keep the product IDs and ditch what? And take out then product. Exactly, ditch the product. Now, if you continue this kind of normalization as it's called, your database starts to get very cleanly designed. And by clean, I mean, there is only one authoritative place to find out what the name is of a product. You check the products' table and there's only one mention of printer or table or chair. It's not duplicated all over the place. And this is good obviously if you just change the name of something, you wouldn't want to have to go through all of your various tables looking for all of the redundantly named things just so you can update them. So that's good, but again, it feels like I'm creating a huge amount of work for myself now because whereas in a simple world, I just select star from one table, now I have like three different tables. And how do I select data simultaneously so that the data I get back is representative of a given moment of time and not from this second followed by another second followed by another second, whereby your queries themselves might be some number of seconds or milliseconds apart. How do I get a snapshot in time? Well, we can actually do this all at once. We can do this with a couple of different syntaxis. So let's try this. Let's assume for the moment that this is good enough even though the faults we already found, if we assume these table structures, though, how can we go about querying data and getting it back all at once? Well, here's some slightly new syntax and I've written on three lines really just for readability. SQL doesn't care. It only matters when-- that the whole expression is syntactically valid. So SELECT Employees.Name,Orders.Product. Now, what does this mean? Well, Employees.Name, as you can probably infer, is referring to the name field of what table? Employee. So that's all. The dot notation does exactly that, and we saw the dot notation actually [inaudible] last week. Orders.Product, same idea, select the product name from the order's table. Where do you want to select those fields from? It's a little-- it feels a little redundant but this is just the way it is. You then specify what tables this query is selecting data from. So you have to say, from employees comma orders. And it doesn't matter of the order in which you say them but you have to say the table names that you want to involve in this query. But now I'm doing this last predicate. And this is definitely more involved than the ones we've looked at briefly thus far where Employees.Employee_ ID=Orders.Employee_ID. In English, what is that predicate doing for us? Yeah. [ Inaudible Remark ] Exactly. It's creating a cross reference of sort between the two tables namely employees and orders and it's explaining to the database how to join those two tables together. Now, I can sort of simulate this with my fingers. Let me pull up the data over here. What is obviously the one common field between the employee's table and the order's table? So it's the Employee_ID, right? That's the only that's in both. So if we again kind of do the little physical depiction I did this with my fingers last week, suppose that here are the employees and supposed that these are the employee IDs, the tips of my fingers here. And now the order's table also has employee ID and it's the tips of my fingers here. Effectively, what that query is doing by saying match Employees.Employee _ID=orders.employee_ID, it's saying to line up the employee's ID. So if this is 001 on the left, it's 01 on the right. If it's 03 on the left, it's 03 on the right. So you kind of join these things together using identical employee IDs and what does this whole query return? What returns a result say which recalls a fancy way of saying a temporary table, that table looks like this where these fields have been conjoined somehow and each row represents the combination of some data from one table with the other table. So, let's actually try to see this in practice. Let's go and recreate then we'll improve upon it. So let me go over to phpMyAdmin in the appliance, and I'm going to go ahead and create a new database. I'm going to go ahead and call this jharvard_Monday just for kicks today. So now we have a clean database. We won't commingle it with last time. Let me choose the Monday database. And now let's create a table. Now, in general I would not do what W3Schools did here with the capitalization, but let's just keep it identical. So we'll create an employee's table, and how many columns did it have? Two. So let's do that and then the first one was called Employee_ID. It could be an INT but let's just do BIGINT just to be different this time. And then it had a name field which should be of what type, probably? Yes. So let's go in the VARCHAR, and now we have to make a design decision, how many characters should the name be? Fifty. Fifty? OK. So let's-- 50. Something reasonable but be consistent in general probably. And now what else do we want to choose here? Should either ID or employee name be null? Probably not, I want both really in my database. Index, should employee ID be a primary unique index or a full text? Primary. Primary. Why, because it hopefully uniquely identifies the user. And we're going to kind of clean up 1 to 10. We're not going to have prefixed leading 0s in our employee IDs. Let's actually use a number like a BIGINT. How about the name? Should we make the names unique? [ Inaudible Remark ] Right, there's a lot of like Mike Smiths in the world. It would kind of be unfortunate if we can only hire one at our company so we probably should not impose that. When might I want to define an INDEX on a field? So it's not unique INDEX it's just an INDEX. What was the advantage of defining an INDEX? I didn't really talk about this but just think instinctively, why would you want to index, so to speak, a database field? For searchability For searchability, for performance, right? If a field is not indexed and you want to search on it, you essentially have to do linear search. You have to search the whole column to find the values you're looking for. But if instead you as the database designer, say index this field, you're essentially providing a hint to MySQL that you or someone will maybe want to search on this field, so please do some effort upfront to optimize those future searches. And it will create some kind of tree structure, it's generally called the B-tree structure that gives us more like logarithmic search time instead of linear search time, which for those unfamiliar, just means it will be faster. How about AI, which is just a shorthand way of saying auto_increments? Should name or ID be auto_increment? Probably ID if we don't want to have to deal with managing those ourselves. In terms of defaults, we probably don't want to give people default names or anything like that, so let's leave everything else alone. Lastly, let me mention this. We'll come back to this. We didn't choose this last time but notice under database engine, there are different engines you can use. And I made the comparison with like NTFS and HFS+ for file systems, if you're familiar with those words. But for now we're going to leave it on the default InnoDB, but later we'll see what the actual implications are of not giving that choice much thought. There will be some tradeoffs. So let's save. All right. So what query was just executed? Again, here is one of the upsides of phpMyAdmin. It kind of teaches you as you go by telling you what query it actually executed to do your bidding just a moment ago. So we'd executed to create table statements. So now, let's go ahead and create this table. And let's go ahead and choose create table, orders. How many columns does this thing have? There's three at the moment. And we'll just deal with the imperfections of it now. In those three columns, where what, product ID? So, Prod_ID. And then we had product. And then we had what, as the last field? Employee_ID. Good, Employee_ID. All right, so for product ID, what should this guy be? Let's go with BIGINTS. So if we're going to use BIGINTS everywhere, let's just use them for this guy too, product should be what? VARCHAR. VARCHAR. How long should the products name be? Depends on what you're selling. Depends on what you're selling. We are selling printers, tables, and chairs. Twenty. Twenty, all right? The marketing people will be upset with you when they come up with the longer word. But that's OK. Employee_ID should be what? BIGINTS. BIGINTS for consistency now, right, because this is as we'll call it, a foreign key whereas in the other table it was a primary key. All right, we'll leave blank there. Let's see. Null, should any of these fields be null? Probably not, unless you want no one to be able to solve these things. Index, which should the product ID be? Primary case. Primary case. So this one is kind of a trick question, so I would argue that primary key is not appropriate here, but why? Here's the-- here are the tables again. Yeah. It's not really that one search word, you want to link the product to the employee. True, but I would argue I would want to search on product ID. Suppose I want to know how many chairs have we sold, I could do a select star from orders where product ID equals 865. So, searching on is compelling, but primary key, primary key means that uniquely identifies the row. So what would the implication be of making product ID unique in a primary key at that, in this particular table? Yeah. If you had more than one chair and your table which you're probably going to have [inaudible]. Exactly. We could only ever sell one printer or one table or one chair, or rather in individual employee could only sell one printer or one table, or one chair because again the definition of primary key must uniquely identify a row. So, if you have another-- if you have employe number 1, if Ola sells a second printer, what should the row be? It should be 2, 3, 4 printer or 1, but that would violate the primary key constraint. So really, someone else offered up the solution earlier, what field is really missing from the order's table that should be there? [ Inaudible Remark ] I might be just call it order ID. So something that uniquely identifies the order. And if we did introduce that, I would propose making order ID the primary key because then you can start it one and two and three and for everything you sell, you can increment the order ID. So, what might we want to do then with the poorly designed table that we have here? Well, let's go ahead and-- I'm going to propose indexing it. It's not unique but this way I can search on it, which reasonable to want to search on an ID fields. Now the next field was Name. Should we have any keys on the product name? No. OK, I heard a no. Anyone who want to argue the opposite, and then well flesh out which is best? So I'll play the contrarian. So I'm going to propose yes, I do want an INDEX on here. Why might I be taking that position no? And again the field in question is the name of the product. Yeah. We want to search for all tables that were sold? Yeah, what if I want to search for all tables that were sold? So I'm, you know, the salesperson. I don't really remember what the number is for that product but I want to check how many chairs have I sold or my boss wants to check how many chairs have I sold, and maybe we have different types of chairs. Maybe we have products called big chair, small chair, white chair, black chair, you know, any kind of longer product name that has the key word chair but I, like a person just using a computer, I just want to do the equivalent of like a Goggle search. I want to search for chair. Now, what's the SQL keyword that we can use to do those kinds of keyword searches? Like. Like. So we saw like earlier. So actually, the like expression here would probably be "%chair%", because that would find me any products that have the word chair in them. Now that could fail if there's some weird word out there that has chair as a prefix or as a substring, but it's probably good enough for our purposes here. So why might I want to index then the name if I want to search on it? Now, maybe I do, maybe I don't but if I do I should have that as an index, but not unique because then I could only share-- sell one chair for instance. Now lastly, employee ID, should employee ID be a primary key, unique or index? Full text isn't relevant because it's not a text field. Yeah. Exactly, probably you want to index it because it is an ID and we want to not only search on it here, we also want to do the join thing. And in fact, anytime you're joining one table with another, the field or fields that you want to join on should be defined as having indexes for performance, otherwise, again, it's a very expensive operation. If you remember-- what's your name again? Ben. Ben, OK, trying to get them all now. All right. So, let's finish this up. Let's make this an INDEX. And should any of these be auto_increment? If we had like a sales ID that's-- Good. If we had, let's keep calling it an order ID. Then yes, that would be reasonable if order ID uniquely identifies that order. But as it stands now, no, I don't want this auto incrementing because I'm going to deliberately specify who sold what and what that ID actually is. So let me go ahead and click Save. And now let's go ahead and look back at this query. So, when I SELECT Employees.Name, Orders.Product FROM Employees, Orders WHERE Employee_ID in one table equals Employee_ID in the other, I get back this sort of visual effect, and what does the temporary table look like? Well, if we kind of do that query, we get back this table. So, it's a temporary table, it's my result set, it has some number of rows, in this case, three and I have all Ola and Stephen and all of the items that they sold. But this is a little confusing to me. I thought I have colleagues named Tove and Kari also. Why are they not in my table? What's the mistake or problem here, Isaac? [ Inaudible Remark ] Yeah. This isn't a bug, right? It's just that neither of them is selling very much right now because look at the orders table, who sold things, employee ID one and then three and three sold two things. So, Tove and Kari haven't sold anything. So this is indeed correct, it just so happens that only Stephen and Ola have sold something from among the products. So, there's another syntax now we can use to do this join. This is what's generally called an implicit join, why is it implicit? Well, I have nowhere used literally the world join and you're just kind of implicitly saying create a new table that's the result of joining these two tables by a way of the where clause that I had there. But we can be more explicit here and we can do something like this. Notice this is after, before, after, before. So, here's the after version, what's different? I still say SELECT Employees.Name, Orders.Product but this time, I say From Employees JOIN Orders. So, I explicitly say, I want to join employees and orders but how do I want to join them, I have to specify on Employee_ID equals Employee_ID from each of the respective tables. So, the end result is identical and it's really up to you as to which one is more clear. Frankly, I almost always go with the join syntax like this just because it's super explicit as to what I'm joining on what and it's a little more clear to me what's going on. And again, the white space where I fit enter is meaningless. I just did this for formatting reasons on the screen. So that you've heard it now, there's other types of joins. There are left joins, there are right joins and outer and inner and now what the relevance is for us is it won't really affect you with project 1 but sometimes there are corner cases, like what if you don't have-- suppose that you-- let's see, who is number three. Suppose that Stephen was fired, he no longer works at the company. So you go ahead and delete him from the employees table. You probably don't want to lose his entire sales history because if you're trying to balance the books, you want to know what you sold and who sold it even if that person is not there. But the problem, though, is that if Stephen is not in the employees table anymore and you do a join employee ID, those rows are going to disappear. They will not appear in the output and we won't know that Stephen sold both the table and a chair. So, by using left join or right join, you can essentially say which of your two tables that you're joining should carry a little more weight. So, in other words, if in this case, I did a right join, now which is the table on the left and which is the right? It's not quite obvious from the way I've formatted the text here but notice I said FROM Employees JOIN Orders. If you write that out in a sentence, which one's on the left employees and then orders. So, employee join orders, if you write it out, you have a left table and a right table, even though it might happen to be on separate lines. So what's the implication then? If I actually said FROM Employees right JOIN Orders, that means that every relevant row in the orders table should be included in the result set. And if it just so happens that there is no corresponding employee ID in the employees table because Stephen was let go, that's OK, still give me a row for all of the stuff he sold but put no there and no there. You don't know who sold it but you don't want to lose track of the fact that it was sold. Now, by contrast, a left join would not solve this problem. But if something were missing from the orders table then maybe you do a left join. But the idea here is that you bias it toward one table or the other just so that you don't accidentally drop some rows. All right, any questions on joins and we'll try to make this more near term relevant when we discuss later today project 1. All right. So let's go ahead and take a five-minute break but the teaser for now is we're going to come back and talk about milk and going to the store and putting in a refrigerator. So hopefully that will get you to come back all excited. Take a five-minute break. All right. So, consider the following scenario. You have a roommate and you have a refrigerator and you both like milk and you open them-- one of you gets home one day and you open the fridge and you're out of milk. And so, you close the fridge, you head outside, you walk across the street to CVS and you get in line to buy some milk and the lines at CVS these days are always ridiculously long because they have those self-checkout machines. So it's perfect. Because now your roommate comes home while you're still waiting in line at the self-checkout machine at CVS. He or she discovers you're out of milk, he or she closes the refrigerator, walks outside, goes to the supermarket instead of CVS, so you don't actually cross paths. Then some number of minutes later, you both get home and voila, you now have twice as much milk and milk spoils so this was not a very good plan because you like milk but you don't really like milk so it's not like you're going to drink two gallons of milk. So now, you've wasted some money in some milk. All right. It's a horrible problem, right? So how do you solve this in the real world? How do you avoid getting twice as much milk? Yes? I mean you could just write a note on the fridge that you've gone out to buy-- OK, good. Right, so it's totally reasonable decision. If you are the first one to come home and you realize there's no milk, just leave your roommate a note, right? It's a courteous thing to do. You'll go get the milk. He or she will appreciate it. And so when he or she then comes home, opens the fridge door and realizes, oh, there's no milk, he or she does not take it upon himself to go to the store as well. So this is good. A note is very reasonable thing and we can actually kind of ramp up the metaphor a bit and instead of saying like leaving a note on the fridge, you could be really kind of crazy and just lock the fridge altogether if you're the first person, right? Because the problem here fundamentally is that both of you are independently checking the state of the refrigerator. Now, you start thinking of the refrigerator as a variable that has some value and that value is either zero or one there's milk or maybe it's a number that indicates how many ounces of milk you have, but either way, it's a variable, both of you at the moment in the first version of the story are independently checking its state but the problem is that your operations of checking the variable's state and updating the variable's state, in other words, opening the fridge and looking and then, buying more milk, those operations are not, atomic. They can happen but there can be interruptions in between those two operations. What's the interrupt here? You check the variable's state, you then start to go for milk, your roommate then checks the variable's state and you both have correct views of the world, but you, the first person, have made a decision based on that variable's initial state, by walking across the street to CVS. So when you come back is when the problem ensues. So again, a note will solve this problem assuming your roommate reads it and if he or she is not really paying attention to the post-it notes, you could literally put a lock on the refrigerator. What would that prevent? Well that would physically prevent your roommate from checking the value of that variable. Now, it could be a little awkward because if they're really determined to have milk, you're going to come home and they're still going to be holding on to the door because you've blocked them, so to speak. They're in what's called deadlock mode if we really abuse this analogy to computer stuff. So they're in deadlock because nothing can actually happen there or they're spinning and waiting for the lock. Deadlock usually involves two people. So, we can solve that problem in the real world, but now, let's consider a related problem in the world of databases. So, the same scenario arouse in conversation last week, when we were talking about registering for a website or buying something from a website. For instance in a simple scenario of registration, suppose that your name is Mike Smith and someone else's name is Mike Smith. And just by bad lock, both of you hop on the internet one day and tried to register for the very new facebook.com, the next big thing. And you really want Smith as your username or Mike or M. Smith. Whatever the case may be you both what the same username. And suppose that by bad luck, you're both sitting at your laptops and you both hit enter at the same time, well, what happens? Well, those HTTP requests go to the server, the server has a database now, so there's some code that passes those username requests off to the database and what's going to happen? Well even though, we like to think of computers as doing dozens of things at once because they seem to be. Really, they're typically doing one thing or maybe a finite number of things simultaneously. And in this case let's consider a simple story whereby your two requests might have arrived at the same time at the server for the username Smith, but one of you has got to win, right? One of them will be serviced ever so quickly before the other one. So what's the problem? What if that first, request is, is Smith available? This guy checks, but then the computer again needs to maintain this illusion of parallelism, so as we'd said last week this thread, your request is going to be put to sleep, maybe for a second, probably, just for a split second at which point the other person's thread is going to be a awoken and it's going to check, is Smith available? The answer to that query is also going to be yes if you just use a simple MySQL SELECT statement, right? You might select. Select star from users where username equals Smith. If that returns zero rows, this guy knows it's available. If that query returns zero rows, that means this guy can have that username as well. So suppose you issue your SELECT statement first. But then in your next line of code is when you actually do your insert. How do you create a new user, you insert into that table. But what's going to happen? So the first user has his choice of username, Smith, inserted. The second one, what happens? Now there's a collision, right? And so the MySQL query function or the corresponding PDO is going to return some kind of error. And yet why should it be an error? You told both of these Michaels that Smith is available so this is an incorrect result, quite bad. Now instead, let's assume a more compelling scenario, right? OK, so what, the second Mike Smith didn't get his favorite username, so not a huge deal. But now consider the case of money or ATM. So if you want to go to a cash machine and suppose that you're being really crafty, you're a bad guy, and you've somehow figured out how to duplicate your plastic ATM card, that's not hard, it's just a magnetic strip. And suppose you go up to an ATM and you cover up the cameras so there's no corner cases, and you put in the two ATM cards simultaneously and you log in and then you do something like, I want to do a withdraw of $100 enter simultaneously on these two machines. How is this story similar? Where in lies the same problem? Axel? When the ATMs checks the account for balance or whatever, it's going to return the same value to both ATMs. Good. So essentially it's going to withdraw the same amount from the same-- from the same balance and the end result is going to be the same. OK, exactly. So if it's really the same kind of story, it's just the variable you're checking now is your account balance and suppose that you only have $100 in your account, you being the bad guy, but you've executed this query simultaneously. So just as in the case of milk, just as in the case of the username checking, both ATMs say select star from, let's say, accounts where user ID equals 12345, whatever, that is the unique identifier on my ATM card. And what I'm selecting this time is balance and the balance comes back and I say, "Oh, this user has $100" but both ATMs get that same answer. So what does an ATM do then next? Well, if there is $100 in the account, what should it do? It should dispense $100 from both machines. So now you've withdrawn $200 even though you only had $100 and somehow your account now is at negative 100 or worse, zero, right, if the math just kind of works out that way and they're using an unsigned insert because it doesn't really makes sense for a bank to just give you more money than you actually have, unless you actually signed paperwork for a loan. So in the worse case from the bank's perspective, they have just given a guy who owns $100, $200 and they don't even know it because the account balance is now zero. So how could we have solved this problem? How could we have solved the username problem so that the expected outcome is indeed correct? Yeah? Lock the fridge. OK, so lock the fridge. So what does that mean in the case of the ATM? Well, you just have to ensure that after you ask a question like how much money does this guy have, you then perform the deduction before answering any similar queries for other ATMs for that particular guy. You somehow need to make your two operations of checking balance and withdrawing money atomic, so to speak. Atomic in the sense that it's like a very small particle, it all happens at once together. So how do we do this? We don't really have this way of-- we don't really have a way of doing this in code just yet because in PHP, recall, when we were doing all those log in examples last week, we called MySQL query and then MySQL query in different places of code. You want to somehow be able to execute two queries simultaneously. Now, you saw semicolons in use for the MySQL command line client, the black and white interface. But it's not enough to just separate your queries by semicolons and send them both to the database at once because they can still get interrupted. You have to be more explicit when you want your database operations to be atomic and there's a couple of ways to do that. There is this handy syntax in MySQL where you can do the following. This doesn't quite solve the ATM example but it does solve similar problems and that you can express yourself as follows. INSERT INTO table, whatever it's called, columns a, b, c, what values do you want to insert for a, b, and c? Let's arbitrarily say the value is 1, 2, 3. However, if one of those fields, say, a is defined as a primary key, unique key, and if you are trying to insert a duplicate key, this syntax let's you say, "Mm-mm, don't do that. Instead, just update one of those field's values." So what's the implication here? Let's actually take an example like let's say ordering something-- let's come up with a good one here. Let's actually use stocks, stocks symbols. So suppose you're implementing a website for keeping track of what shares of stock someone owns and you just want a total count. How many shares of Google do I own? How many shares of Microsoft do I own and so forth. So in whatever table you're using to store a user's portfolio, you simply want to have one row for Google maximally and in another column next to Google, you want the total number of shares. And if it's one, that's fine, but if the user buys some Google shares today and then buy some more tomorrow, you want to update that same row. You do not want duplicate Google rows just because there's no need. It's inefficient. You can just update that particular value in this version of the story. So with this kind of query, you could say exactly that. INSERT INTO table and then in parenthesis "Google" or whatever its stock symbol is, comma something, comma 1 where the 1 is the number of shares that you're trying to buy. If, though, there is a duplicate key, well, what's likely to be the unique key here? Goog, G-O-O-G happens to be the symbol for Google. So if you already have a row in this table with a key of Goog, what is this code telling it to do? It's instead saying, "Don't insert a new row with the values a, b, c. Instead, just update the existing row c value by incrementing it by one." So in other words, this is one way of expressing with a new query that we haven't seen before with the ON DUPLICATE KEY syntax essentially checking for the presence of a row or a value and then acting based on that check all atomically, all at once. By contrast, if you had to do this manually, you would need to do a select to first find out if there's already a row for Google, then you would do an update or an insert. This allows you to collapse a select followed by an update or an insert into just one query. So that's one way of doing this but unfortunately that is not enough. So thankfully, databases typically support transaction these days. So InnoDB is now relevant. The storage engine that we chose earlier for my database was InnoDB by default. That just means that's the format in which all of my data is stored. So what does that really mean? Well, that means I have a feature called transactions that I can use on that database engine type but not on others. So how do you use transactions? You can do this. These four lines represent four separate SQL statements. The first, START TRANSACTION and you can also say begin transaction, they're synonyms. That just means here comes a sequence of SQL queries that I want to execute atomically. In other words, execute all of these together or none of them do not interrupt anyone of these steps with someone else's queries or anything like that. Put a lock on these rows. So UPDATE account SET balance = balance - 1000 WHERE number = 2, UPDATE account SET balance = balance + 1000 WHERE number = 1. What's the context here? It's kind of arbitrary. But imagine that I'm just trying to transfer $1000 between two different accounts. One of those accounts, account number is one, the other account number is two. So this is one of those scenarios much like the ATM withdrawal that I want both the deduction from one account and the deposit into the other to happen together or not at all, otherwise I'm going to end up losing $1000 accidentally or gaining $1000 accidentally and not just moving $1000 from one account to the other. COMMIT, as the name suggests, means commit this transaction to the database. In other words, execute. So by saying START TRANSACTION, you're essentially telling the database to buffer up a few SQL statements. Once you do COMMIT, it executes them all without letting anyone else squeeze inside there. What's also nice is that there's a command we can use that's sort of the opposite of COMMIT, it's ROLLBACK. So this is another powerful feature of InnoDB. Whereby suppose we do the following, start transaction, update one account, update the other account and now you do a check, select the balance from the accounts where number equals 2 and then in my-- with my hash sign there, I'm just proposing that we have some PHP code. It's in the comment because I just mean it to be pseudo code. Suppose that I screwed up and after selecting the balance from account where number equals 2, now I realize, shoot, now the user has gone to a negative $1000 or negative $1 even. I noticed a problem and I want to undo every one of the steps that I just executed whether it's three steps or two or ten. I want to undo these changes. It's like hitting command or control Z, Z, Z, Z, Z when you really screw up in an essay you want to just go back, back, back, back, back or in some programming code you're writing. So ROLLBACK undoes everything up until the most recent transaction. So it's wonderfully powerful and allows you to try to achieve some result. But if you can't, you don't have to figure out how to fix all of your various tables and cells based on the changes you made thus far. So wonderfully powerful and it's also quite efficient and it's in contrast to something called locks. So MyISAM, for many years, was the more popular default engine for MySQL. The upside of MyISAM is that it actually tends in certain benchmarks to be faster than InnoDB especially for selects and sometimes writes, but you don't get the same property of having transaction support but you do have locks. In MyISAM tables, which is just another option from that dropdown, essentially the data then is stored in a slightly different format. But if you want to achieve the same idea, you have to literally lock the whole table. So the syntax for that which is less commonly necessary now is LOCK TABLES account WRITE. Now, what does that mean? Account is the table name. And what kind of things do you prevent people from doing? From writing. So you can think of this is as like the padlock on the fridge. You're preventing your roommate from writing to the fridge by inserting more milk into that variable. So this just means don't let-- anyone else can read the table, I don't really care, but I don't want them to write to the table here. So as now, I'm going to select the balance and then I'm going to go ahead and update the balance, this time equal to 1500. And then I'm going to unlock the tables. So in effect, you can do the same thing. You can say execute all of this stuff and don't let anyone get in the middle. But the problem with locks in MyISAM is that you literally lock the entire table which means even if someone wants to check someone else's account balance or someone else wants to make a deposit that has nothing to do with account number 2. The entire table is locked. So this tends to be bad for efficiency if you're essentially telling everyone, every other row especially if you have a million rows, no one else can deposit money, check their account balances or anything because I am blocking this whole table. Transactions in InnoDB by contrast effectively only lock the rows that you care about. So it's much better for performance at least in terms of transactions. So that there is the tradeoff. All right, questions? So let me come back to one thing that relates now to InnoDB as well. So recall that we were re-implementing these couple of tables for the example involving employees and products. And suppose now, what I want to do is refine a little bit this thing here, Employee_ID, recall we defined an index on it. Now, why do we do that? For performance really. It's a unique identifier. I might want to search by employee ID so I made it an index. But notice this, I'm going to go down to relation view in phpMyAdmin which is this link here below the table summary. And now, notice that, oops, did I not actually check that box. I'm going to go back. Let's see, employees, we have. So here's how you look up what indexes you've created in my-- phpMyAdmin. I've clicked the employees table. And here I see a reminder of what the columns are called. If I click Indexes, I'll see that this tables has two indexes at the moment, a primary index on the Employee_ID field and it's unique by definition of primary. And then I remember that I also put an index on Name which was on the Name column and it's BTREE. That's an illusion to the tree structure. That actually does the-- That optimizes the searching. But now, let me go back to Orders because I actually think I did not check the box correctly earlier and look at my Indexes. And indeed-- OK, I completely screwed up earlier. I didn't mean to check, select all of those things. This is a shortcoming of my memory and of phpMyAdmin's interface. What I did here in defining an index, I defined it on all of three fields which was not what I intended. I wanted to have an index on each of the individual field so that I could search on each field individually. This index suggests that I want to select-- I want to search on all three fields simultaneously. As with the where statement like where product ID equals 1 and product equals table and Employee_ID equals 3. So that's not the kind of query I had in mind. So I'm going to fix this. Let me click Drop next to this key rather next to this index. And let me do this manually. So I'm going to go here and add a index. I'm going to go here and add an index and over here and add an index. Unfortunately, phpMyAdmin, when you do it all at once, apparently assumes you want a joint key. So now, I have three separate indices which is the intended behavior. And the link I'm going to click now is relation view. And this is another feature you get with a database like MySQL that you don't get with XML or with CSV or the like. Notice under Relations, so I can now define what are called foreign key constraints. And that's a feature of InnoDB only. In other words, I can now tell the database where these fields also appear. And the one I care about at the moment is Employee_ID. Notice here that under this checkbox, I have a list of all of the other fields I've defined in other tables with indices. And the one I want to do is I want to say that this table, these orders .Employee_ID field has a relationship with the employee's table .Employee_ID field. In other words, I have given this an index in the orders table, Employee_ID. And where else does Employee_ID appear? Obviously in the employee's table where it's a primary key. So if I want to teach the database that Employee_ID in the orders table is a foreign key. And what's a foreign key? Well, it's a field that happens to be a primary key elsewhere. I literally just make that association here. So now, the database, MySQL knows that Employee_ID is a primary key in employees. And it's a foreign key in orders. Well, who cares? Well now, you can set up rules for undelete and unupdate. In other words, by restricting this field in this way, I can now prevent developers, myself included, from deleting this employee from the database. Why? Because if Stephen, employee three, has both a row in the employee's table and he's also sold a couple of things in the orders table, by specifying this relationship between the two tables, I can ensure that I or a colleague doesn't accidentally delete Stephen from the employee's table because I've restricted deletes and updates. In other words, if I try to delete this-- delete Stephen, it's not going to be allowed because I'm going to be violating these constraints. By contrast, I can specify that I don't want to restrict those kinds of operations. I want to do something like CASCADE. And CASCADE is kind of the opposite. What this means is that if I delete Stephen from the employee's table, you know what, go ahead and delete Stephen from the orders table, too. We're done with Stephen. If he's fired, that's it. We're going to remove all evidence of him. So this might not be the desired behavior. Sometimes it is, sometimes it isn't. But if you did want that behavior, where deleting from one table automatically triggers deletes from another, you can do this again all in the database. So what's the upside? Well, you don't have to worry about this in code, whether it's PHP or some other language, your database can do be self-managing in this way. So all you have to worry about is deleting the one thing you care abut, Stephen, from the user's table. And if Stephen's name or ID is anywhere else in your entire database and any of your dozens of tables, these kinds of operations can go through automatically and clean all that up for you. Now again, probably bad for terminating an employee because you don't want to forget what they did actually sell. So we might instead want to do something like restrict in that case. But that's another advantage of InnoDB that you don't get from MyISAM. All right, so let's talk about design with an eye toward project 1. So project 0 still in progress presumably, per the course's website we'll announce tomorrow on the course's homepage how to submit it and the process for doing so. So keep an eye on that. You will need to submit it electronically. And then this second project 1 is due next week so realize there's deliberately this overlap so that even though project 0 is not due until Wednesday, we'll release the next project on a Monday just so that you can manage your own time. And if you get a head start, great. If not, you can start on Wednesday or thereafter. All right, so CS75 Finance is the challenge at hand next. And this is your second project that actually involves MySQL this time around. So an actual database, no more XML. Division for CS75 Finance for the specification, whose PDF is already online is to implement your own etrade.com-like website, a website that allows you to manage the buying and selling of stocks and to allow users to pretend that they're actually buying and selling real stocks. In other words, if I'm a user using your version of CS75 Finance, you're going to have to allow me to register for an account and then log in to your site. And the moment I register, it's a nice little promotion you're going to give me $10,000. And you're going to do that by associating that with me, my user ID, somewhere in your database. And then, you're going to enable me to get stock quotes like how much is Google right now, how much is Yahoo right now or any other company, and then buy and sell stocks, so to speak. And how do you buy a stock? Well, you're going to check how much the stock currently is. And if I want to buy one share, you're going to do the math, one share times stock price is probably a few dollars or a few hundred dollars. Then you're going to check what's my account balance. I have $10,000 out of the gate. And then you're going to do the math and say, "OK now, you have $9,000 something but you have one share of stock in some company. So the building blocks we've been looking at tonight in terms of locks and in terms of automatically updating rows and the like, it's definitely going to come into play here especially since money is involved, where correctness is all the more important because otherwise someone, you or the bank, are going to lose money. So the proposal we have here is this. First, take notice that here is the most recent piece of spam I got over the weekend, advertising some penny stock and this penny stock goes for 3 cents right now. Invariably, we have to change this example every year because these penny stocks keep getting driven out of business. But this is an email I got. Someone encouraging me to buy this penny stock and a penny stock is one that's really cheap, some usually some number of pennies. And this was 3 cents as of yesterday. So, what's the relevance here? This is really just a screenshot of Yahoo Finance. What's nice about Yahoo Finance is that you can look up stock quotes like this one. ROSV is the stock symbol. If you're unfamiliar with buying and selling stocks, realize that this specification points you at a tutorial for them that applies to-- that will give you a sense of how this world works if unfamiliar. But let me go to Yahoo Finance and type in ROSV, which again is the symbol for Rostock or Rostock Ventures Comp, Corp, whatever that is, and get quotes. And I see pretty much this. So it looks like no, it didn't really trade heavily today. In fact, 20,000 shares were actually bought or sold today but the price remains at 3 cents. So this is nice and interesting but what's really need about Yahoo is that down here at the bottom is that they have this little toolbox. Because what we want to do is not want to-- we don't want to resort to something like screen scraping where to get a stock price from Yahoo, we have to grab their web page and then read the HTML and figure out what number in that mess is the stock price. Rather here is a nice link, Download Data. So let's actually click this. I'm going to click Download Data delayed and that's just a reminder that it's actually a few minutes delayed. It's not truly real time. Now notice this downloaded file called quotes.csv. So this is nice because now we can tie it together last week's discussion of CSV and open up this file. It's going to open up a spreadsheet program like numbers or like Excel and what do I have here, well this is it. So it's a very simple spreadsheet, it seems, where the left-hand column contains ROSV, the next column contains 0.03 which is presumably the price, the next column contains today's date and then the time and then I'm not sure about those other fields, draw off the top of my head because it's not actually indicated but in a spec, we actually point out exactly what-- how you can figure out what all the various fields mean. But the real important one for now is just the price. It's 3 cents. So what's the implication of having access to CSVs? It turns out it's really easy in PHP to write code that essentially pretends to be a browser, hits a foreign URL, gets back the result, and if-- it's great if the result is in CSV. What function do we talk about last week that will actually parse the CSV file and hand you back an array? What's that? XPath? Not XPath. So XPath was only for XML but there was a function that we talked about that can open a CSV file and parse it for you and hand all of the issues involving commas and quotes and the like. Louis? Fgetcsv. Yeah, so fgetcsv. We didn't use it but now is going to be our chance tonight to actually use this function to fetch the data and then do something with it. So we can pretend to be a browser, effectively downloading the CSV file. Why? Well if we'd look at the Yahoo Finance URL, let's take a look at what it actually is. Let me go ahead and control click and choose copy link address and then paste this into a little text file. Notice that this is the URL that we just visited, download.finance .yahoo.com/d/ quotes.csv? The question mark is the interesting part because that means there's some dynamism. S equals ROSV and f equals a lot of stuff. So take a guess, what does the s HTTP parameter apparently represent here? Yeah? Search? Search or, yeah, more concretely here, though, what are you searching for? Isaac? Stock. The stock symbol, so s is presumably the stock symbol. Now I didn't create this URL. Remember I searched for ROSV and hit enter, but it appears to have generated later in that page under the toolbox of link that, you know, I feel like I can fake this, right? We already know that you can generate your own URLs or your own strings in PHP, surely I could copy and paste most of this URL but just change which part of it in my forthcoming project? Just the value of? The stock? S, yeah, just the value of the stock. Now what does f refer to? That's another parameter and I only know what those fields mean by looking it up in the documentation. We give you URL in line but this is just some arbitrary sort of 1990 style thing from Yahoo where they have sl, I think that is, 1d1t1. Each of those letters and numbers just represents a certain field, like the stock price, the date, the time, all of the columns we saw on our spreadsheet, they are derived from that cryptic looking string there and some random guy in the internet figured out what all of these meant and that's the URL we put in the specs so that you can go see what they all represent. But by default, we get back this. This last thing, &e=.csv is actually a hack. Older browsers sometimes used to choke when you were trying to download something like a CSV if the file extension in the URL was not actually CSV. So even though this is not a file, this is probably a dynamically generated spreadsheet of sort, a CSV file, it's not an actual file on a hard drive somewhere, that's just a silly little old school workaround for various browser issues that have largely disappeared at least in this case. So what's the takeaway here? It feels like I should be able to copy and paste this entire URL and just change this if I want to implement my own stock trading website that allows users to specify stocks via my own interface. So let's take a look at an example here. Let me go into the appliance where I have this simple page waiting, this is one of the snippets of code available on the lecture's page tonight for tonight's lecture. This is index.php. The functionality I've implemented in advance here is this. If I go here and type in GOOG as Google stock symbol, it doesn't matter if it's upper case or lower case for Yahoo, and then click Submit, notice that this is what comes back. So as of tonight, Monday, it's apparently 574.92. Let's try another one. Microsoft, MSFT is their symbol, submit, there is this 29.44. We can do something like Facebook, submit, there is this 28 and so forth. Now, where is this coming? This is not hard coded. This is generated dynamically by the server. So let's take a look now at index.php. Let me go ahead and open up index, and it's a pretty small program and index rather is just my form. So notice this is where we were a moment ago. Where do I send the user when I submit the form just to be clear? Yeah? Quote.php. Quote.php. I'm using GET just because I don't have to but I chose to, input name="symbol". I didn't call it s but not a big deal. I can call it anything I want. Type as text and then a submit button. So that's it. And now what's the next file obviously of interest? Quote.php, so let me go in there and open up quote.php, and this is how I go about getting the stock price. So notice at the very top of my file I've got some PHP tags, so now I'm in PHP mode. Notice I'm next doing this. I'm URL-encoding s. What does it mean to URL-encode something? Yeah? I think it takes all the-- all the spaces and turns it into, like, [inaudible] and all that. Yes. Spaces are generally just simplified as pluses but are B percent 20, yup, percent 20. So yes, URL-encode takes a string and ensures that there are no dangerous characters or confusing characters for URL's sake. What are the confusing characters? Well, URLs can not have spaces. So how are they typically represented? With a space character or with descriptive sequence of percent two, zero. So URL-encode does that. Now, is this a big deal? I just typed in GOOG. URL-encode is not going to do anything to that string. It's not going to do anything to Microsoft. It's not going to do anything to FB. But if I typed in some weird punctuation for certain stock symbols or I hit the space bar accidentally, that would handle that kind of scenario. So this is just one of those do it as a matter of principle. Any time you send user input to a browser, you call htmlspecialchars. Any time you send user input to a database, you call mysql_real_escape_string or now the prepare function in PDO. Any time you pass something to a URL, you should call urlencode on it, those three rules of thumb. All right. So $url is my next line in line four and notice all I did literally what I promised. I copied and pasted the URL from Yahoo's website and I put this little placeholder here of $s with the curly braces around it. The curly braces are not strictly necessary. It's just good practice that I've gotten into just in case it's a complex looking variable like a superglobal or an associative array. So that just means put the value of s there and s, recall, was defined one line earlier to be the return value of urlencode. All right, the next line, what am I doing? This is what's great about PHP sometimes. It just does so many things easily, $handle = fopen URL "r" let's go in reverse order. What does the r likely mean? Yeah? Read. Read. So this doesn't really make sense in the context of URLs because it's not like you can write, change the URL writing code on your server as opposed to someone else's. But fopen is generally used to open a file, file open. But PHP overloads a lot of these file related functions. So that if you instead give it, not the name of a file that's in your current folder, but you instead give it to URL, it will open a connection to that server via TCP/IP, it will do all the requisite DNS stuff, it will get back the results of that URL and hand it to you in the form of a reference, called the file handle. So that's what $handle represents. It represents essentially the return value from that server that represents the file that came back, which is hopefully an actual CSV. Now, here is the function I promised that we could use. Fgetcsv takes a file handle, so it doesn't take a string, doesn't take a file name, it takes a file handle, which is the return the value of fopen. So, you have to do it this way. And it reads in the first row from that file. So, you would have to call fgetcsv again and again and again if the spreadsheet contained multiple stock symbols. But as we saw, there is just one. So, I'm only calling this once. So fgetcsv returns the first. And what's the data type that comes back? Well, it's an array, where it's a numerically indexed array, where $row open bracket zero close bracket will represent the first column from the CSV. Bracket one the next column, bracket two the next column and so forth. So, there is no notion of headers in CSV. Sometimes companies or people will put the first row as having words for human friendliness however fgetcsv is not going to use that at all. So, in fact, you sometimes want to call fgetcsv once to E-up that first line as being useful for humans but not for machines, because it's the next row and beyond that actually has your data. But for us, so simple, because there's only one column or one row to worry about that came back from Yahoo. So I just call fgetcsv. And then for good measure, I call fclose on the handle to close that connection. I don't need anything more. Now, down below, I have some super simple HTML and notice what I'm plugging in, the current price of this. Now, just to be clear, why am I calling htmlspecialchars on the symbol that the user typed in to my little form? Yeah. [ Inaudible Remark ] Exactly. I want to protect against what we've been calling cross-site scripting attacks, so more on that again to come. But for now, we want to make sure that they've not typed any dangerous characters or been tricked into typing in some dangerous characters. And now the interesting part, the current price of that symbol is and then some more PHP code over here. And what am I outputting? Row bracket one. How do I know it's one? I just looked at the CSV, right? We just open the N numbers or an Excel. We saw that column one, not zero, column one is the price, so I want to print that out. Now there is a problem here, like Jack's concern earlier about money, could end up causing us some problem. And if the price happens to be something, like a $1.90, maybe we would get $1.09. You'll have to actually experiment with some real stock quotes and see what kinds of numbers Yahoo is spitting out. But realize there's a function called number_format in PHP, printf in PHP. Both of which allow you to format numbers to a specific number of decimal places, so you can guarantee that your money will be formatted in the right way. And back to our discussion now of databases, what were the data types that allow decimal points? It was float, double and there is one other. Yeah. Decimal. Yeah. It was called decimal, which doesn't exists in most programming languages, but this is a database field type that actually allows you to specify precisely the number of digits you want before the period and after the period. So for this look-- ultimately look in the MySQL documentation for more. It's very easy to use. But you'll be able to decide you probably want two, maybe three, maybe even four numbers after the decimal point. It really depends on what kind of precision Yahoo is returning, is it just cents or is it fractions of cents. And ask for how many numbers to the left of the symbol-- the left of the period you'll kind of have to make a judgment call. I mean, Google is like $500, so you need at least three digits. Berkshire Hathaway, sometimes $1000, so you might need four digits. But you probably don't need 20 digits because that would be a really expensive stock. So you're going to have a judgment call there. But realize that decimal for money is probably your best bet, because there is no imprecision. If you specify give me two numbers after the decimal place, that's what you'll get. You won't get weird rounding issues as you would more traditionally. So realize decimal will be your friend in the database and something, like printf or number_format, will be your friend when it comes to the aesthetics of showing a human the number. All right. So, that's how we can fetch stock queries, and that's it for integration. What's really nice here is that with CS75 finance you can integrate real time stock prices into your application by just using this machine readable format, CSV, which we all agree is kind of a crappy format. It's not very flexible. You can't sort of tag information very well. It's very flood. It's vulnerable to like commas and quotes being an awkward place. But it's at least machine readable. And it's not something that we have to worry about parsing ourselves. So, when we get back that value, we can just plug it right in. And we can now do it to perform math and bys and the cells and the like, so a bit of a word on strategy then. So, this website has to support these features. And the dot, dot, dot refers to other things that you might want to add by your own choice. So, you're going to need to enable users to log in to your website. Now, you have a bit of a chicken and an egg problem here, because how you're going to implement log in functionality before you actually have users. And again, obviously, it's going to be you pretending to be a user or creating users. So, you'll think through how best to approach this. If you want to implement log in, well, you have the advantage of having seen like nine different log in examples thus far, probably you shouldn't use the earliest of those, with hard coded John Harvard username and password. But the most recent ones, where you actually use the database. So, you actually have code already from class that you can adopt for your own project to implement log ins. Now, how do you implement registration? Well, registration is not going to use select from your user's table. What keywords we're going to use instead? Yeah. Insert into. Insert into, right? If you want to register for an account, you, the programmer, are probably going to have to call one or more insert statements to add that user to the table once they've told you their preferred username and password. Passwords, that's in the design decision, right? We talked about not storing in clear text, not the best practice. You should at least hash it, but using the password function might not be the best approach. So, again, more on project 1 in sections tonight. But realize even though the functionality is maybe obvious, the implementation is meant to be non-obvious. A lot of interesting decision points to make. What I would propose in general is that if you want to implement log-in first, that's fine and you should. But you can simulate registrations, how? Just use phpMyAdmin, right? Create your user's table. Literally click the insert tab and just manually insert some fake users, so that you at least have someone to play with. Then, go and test your log in code using those users you already inserted manually. Once you have log in working, then you can actually implement register.php or similar to actually do those inserts. So realize, you certainly shouldn't try tackling steps one and two and three and four and five and then test everything, you can absolutely do this step by step by just simulating certain things by using the command line or phpMyAdmin. So, what about get quotes? Well, here, too, you kind of have some code from class, right? How do you go about reading a stock quote from Yahoo? It's like two lines of code, maybe three. So you at least have that module now. But that's a good candidate to write a function for it, so it's not going to be the only thing your file does. You can write a function that you then call, so you could have like a helpers.php file again or something like that if you've gone that route for project 0. So getting the quote is relatively easy. Now selling and buying is less obvious. So, why do I propose selling first? Create your portfolio table, or whatever you're going to call the table that keeps track of who has bought what. And how do you stimulate buying stocks before you have that in functionality? Use phpMyAdmin, right? Go to the insert tab and just manually pretend like you bought Goog and these many shares or you bought Microsoft and these many shares. Then you can implement selling functionality. And do look at the spec, because we do specify what we mean by selling. We, for instance, don't let yourself fractions of shares. We specify that you can-- the user has to sell them all at once, so you don't have to sell just a few of them. But when it comes to buying, you're going to have to do a few things. You're going to have to not only get a quote right then and there because we want it to be current. You're going to have to then may check how much money the user actually has. So, somewhere in this database, you have to keep track of my account balance, which initially we propose as $10,000. After that, it should go up and down based on what the user does. And you're going to have to make sure that if the user has already bought some Google shares, you're going to have to update that row in your database, so that you don't just have many, many, many different Google rows when clearly you could consolidate them and just care about the total. So in short, there's going to be another-- a number of other design decisions. But how many tables do we seem to be talking about already, minimally? Minimally two, users and portfolios. OK, users and portfolios. And what kinds of field should probably go into user's table, even though there are many ways to do this? Yeah? Username, password, probably email and the, perhaps, portfolio ID or something like that, [inaudible] perhaps. OK. Good. So, your username, password and maybe email or maybe that could just be the username. The-- And some kind of unique ID, because you're going to want to correlate a user in the user's table with a portfolio owner in the portfolio's table, because what fields might go in the portfolio's table? Your stocks? Your stocks. So one field is maybe the symbol of the thing you own. One field is the ID of the person who owns that symbol. And then, what's probably at least the third field in the portfolio's table? Yeah? How many stocks you have [inaudible]. Exactly. Quantity. Do you want to store the price in the portfolio's table? No. Probably not, right, because the price is going to change presumably. So, it's not like once you buy a stock, that's what it's worth. It's going to change day to day. Yeah? Just looking at the stock, you might want to actually store the purchase price because [inaudible] calculate the difference. Good. It's gone up or down. So there is an opportunity. And here in lies the dot, dot, dot. Maybe it would be a good idea to store the purchase price recognizing that the current price will change. But then, you can do some basic arithmetic and say your portfolio is up 50% or it's down 50%. But to do that, you have to know what the starting point or the starting price was. But there, realize that you run into some design issues, like if you're going to start restoring the purchase price, now you might not want to consolidate rows in your portfolio table. Because what if I buy a share of Google at $400, but another share of Google at $500 I want to somehow remember that one of those shares was 400, one of those was 500 or I want to store the average price or something like that, now I have some non-obvious accounting issues to deal with. So realizing the spec, we do allow you to simplify certain things, but those are the kinds of tradeoffs ultimately that you need to make. Any questions? So it's actually-- you'll actually find that it's pretty neat once you have the things up and running and you actually buy and sell these stocks and then check your portfolio the next day because if you're querying Yahoo every time the user wants to look at their portfolio as you'll have to, you'll be able to see whether things are going up or down. And certainly, if you're buying like millions of shares of penny stocks or whatnot, you can actually see things change day to day if there's actually a price movement and it's going from 3 cents to 4 cents. It's actually a nice 33% profit overnight. But, of course, this is just imaginary and you're just doing the math even though the actual volume of shares might not be consistent with reality. Any questions. All right. So, why don't we officially wrap there? I'll stick around for questions. We'll let Chris get set up and dive a bit more into PDO in section, into project 1 and into SQL itself. All right. [ Silence ]