WEBVTT 1 00:00:01.705 --> 00:00:02.771 All right, so at this point, 2 00:00:02.771 --> 00:00:06.575 I think our DataListBox class is pretty cool, and we 3 00:00:06.575 --> 00:00:09.153 didn't have to write a lot of code to implement it, either. 4 00:00:09.153 --> 00:00:11.536 But unfortunately, as I alluded to at the end of the last 5 00:00:11.536 --> 00:00:14.885 video, there's a bug in it, and you can probably guess 6 00:00:14.885 --> 00:00:16.759 what the challenge is going to be. 7 00:00:16.759 --> 00:00:19.923 But first, though, let's actually see what the bug is. 8 00:00:19.923 --> 00:00:23.840 So I'm going to actually run the programme again. 9 00:00:29.347 --> 00:00:32.762 And what I'm going to do is select Billy Idol 10 00:00:32.762 --> 00:00:35.418 from the lists of artists over here, and you can see 11 00:00:35.418 --> 00:00:38.074 there's a single album here called Greatest Hits. 12 00:00:38.074 --> 00:00:41.450 Now, lots of artists produce Greatest Hits albums, 13 00:00:41.450 --> 00:00:44.048 so it'll be of no surprise if there's other albums 14 00:00:44.048 --> 00:00:45.867 with that name in the database. 15 00:00:45.867 --> 00:00:49.817 So if we select this album is the AlbumsListBox 16 00:00:49.817 --> 00:00:52.022 and make a mental note of the songs on the album, 17 00:00:52.022 --> 00:00:53.476 just at least some of them. 18 00:00:53.476 --> 00:00:56.027 You don't have to memorise the full list, but just note 19 00:00:56.027 --> 00:00:58.304 that the first track here is called American Girl, 20 00:00:58.304 --> 00:01:01.308 and the last one is down here, Something in the Air. 21 00:01:01.308 --> 00:01:02.937 All right, so that's Billy Idol, but if we scroll down 22 00:01:02.937 --> 00:01:04.687 now to Fleetwood Mac, 23 00:01:06.740 --> 00:01:08.142 choose them, 24 00:01:08.142 --> 00:01:10.956 they've also got a Greatest Hits, you can see there. 25 00:01:10.956 --> 00:01:13.720 Click on Greatest Hits, lo and behold, 26 00:01:13.720 --> 00:01:15.560 the first song, American Girl, 27 00:01:15.560 --> 00:01:18.043 and the last one is Something in the Air. 28 00:01:18.043 --> 00:01:21.262 And if you scroll down and look at another one, 29 00:01:21.262 --> 00:01:23.757 Tom Petty and the Heartbreakers here, 30 00:01:23.757 --> 00:01:25.218 so click on that one. 31 00:01:25.218 --> 00:01:27.980 They've got a Greatest Hits, and again, 32 00:01:27.980 --> 00:01:30.309 American Girl and Something in the Air. 33 00:01:30.309 --> 00:01:33.574 And just to be completely sure, click on the Troggs. 34 00:01:33.574 --> 00:01:35.748 We've got Greatest Hits, 35 00:01:35.748 --> 00:01:39.061 and again, another familiar list of songs. 36 00:01:39.061 --> 00:01:41.583 Now, it's not really likely that four different artists 37 00:01:41.583 --> 00:01:44.227 have all produced a Greatest Hits album with identical 38 00:01:44.227 --> 00:01:47.776 track lists, so clearly there's a bug in our class. 39 00:01:47.776 --> 00:01:50.218 The problem is that we're looking up the displayed value 40 00:01:50.218 --> 00:01:53.344 in the database rather than using IDs. 41 00:01:53.344 --> 00:01:57.064 That was a design decision that we made, and it is valid, 42 00:01:57.064 --> 00:01:59.491 but we just have to do it correctly. 43 00:01:59.491 --> 00:02:01.573 Now, if different master records have the same 44 00:02:01.573 --> 00:02:04.582 related record such as different artists having 45 00:02:04.582 --> 00:02:06.993 an album with the same name, our query 46 00:02:06.993 --> 00:02:09.782 just picks the first one from the database. 47 00:02:09.782 --> 00:02:14.086 So your challenge now is to modify the DataListBox class 48 00:02:14.086 --> 00:02:16.315 so that it retrieves the correct record 49 00:02:16.315 --> 00:02:17.909 from the linked table, 50 00:02:17.909 --> 00:02:20.608 the album for the correct artists, in this example. 51 00:02:20.608 --> 00:02:22.907 But keep in mind and remember that we're making 52 00:02:22.907 --> 00:02:26.771 this class generic so that it can cope with any tables 53 00:02:26.771 --> 00:02:29.521 that have a single column primary key. 54 00:02:29.521 --> 00:02:30.842 Now, before you have a go at that, 55 00:02:30.842 --> 00:02:33.224 I'm going to show you how to query the database tables 56 00:02:33.224 --> 00:02:36.584 from within IntelliJ IDEA or PyCharm. 57 00:02:36.584 --> 00:02:39.084 So we're gonna close this down 58 00:02:39.998 --> 00:02:43.443 and click over here to the Database tab, 59 00:02:43.443 --> 00:02:45.117 and we click over here to the console, 60 00:02:45.117 --> 00:02:48.329 little console there to open the console. 61 00:02:48.329 --> 00:02:50.618 And you can see that's opened up a tab where you 62 00:02:50.618 --> 00:02:53.142 can execute SQL queries against the database 63 00:02:53.142 --> 00:02:55.941 and see the results in a pane at the bottom of the screen, 64 00:02:55.941 --> 00:02:58.378 and you'll see that when we actually do a query. 65 00:02:58.378 --> 00:03:01.248 And if you've read about on SQL after the introduction 66 00:03:01.248 --> 00:03:03.311 earlier in this section, you'll be familiar 67 00:03:03.311 --> 00:03:05.083 with the GROUP BY and HAVING clauses. 68 00:03:05.083 --> 00:03:07.735 But if not, they're both well worth investigating. 69 00:03:07.735 --> 00:03:10.031 So we can find out how many duplicate album names 70 00:03:10.031 --> 00:03:13.393 there are by running the query that I'm about to type in. 71 00:03:13.393 --> 00:03:16.226 So we can type SELECT albums.name, 72 00:03:18.731 --> 00:03:20.398 comma, space, count, 73 00:03:21.427 --> 00:03:22.707 and I should really do COUNT in uppercase, 74 00:03:22.707 --> 00:03:24.759 just to be consistent here. 75 00:03:24.759 --> 00:03:28.926 (albums.name) AS, and it's going to be num_albums 76 00:03:33.564 --> 00:03:36.731 FROM albums ORDER BY, sorry, GROUP BY, 77 00:03:39.958 --> 00:03:43.041 GROUP BY, that's gonna be albums.name 78 00:03:44.811 --> 00:03:47.728 HAVING num_albums greater than one. 79 00:03:50.711 --> 00:03:52.102 And in this console, you don't need 80 00:03:52.102 --> 00:03:54.440 to end your queries with a semicolon. 81 00:03:54.440 --> 00:03:58.683 So, click on this little green triangle, and you can see 82 00:03:58.683 --> 00:04:01.444 down at the bottom, we get the results of the query. 83 00:04:01.444 --> 00:04:02.765 So, in this case, we've got four 84 00:04:02.765 --> 00:04:06.611 duplicated album names in the database. 85 00:04:06.611 --> 00:04:08.384 So three of them appear twice, 86 00:04:08.384 --> 00:04:12.225 and our Greatest Hits example appears four times. 87 00:04:12.225 --> 00:04:14.929 So it will be useful to know which artists are associated 88 00:04:14.929 --> 00:04:17.940 with those albums so that we can test our fix. 89 00:04:17.940 --> 00:04:20.933 Now, one thing you could do is run four separate queries 90 00:04:20.933 --> 00:04:23.218 to find the albums in the database for each of those 91 00:04:23.218 --> 00:04:27.486 four names, but SQL's an incredibly powerful language. 92 00:04:27.486 --> 00:04:28.883 In fact, just producing that list 93 00:04:28.883 --> 00:04:30.812 would have been less than ideal 94 00:04:30.812 --> 00:04:32.554 if we try to use Python. 95 00:04:32.554 --> 00:04:35.580 Now, Python's very good at pulling out duplicates in a list. 96 00:04:35.580 --> 00:04:37.812 The problem would be that we'd have to retrieve 97 00:04:37.812 --> 00:04:40.657 the full set of albums from our database first, 98 00:04:40.657 --> 00:04:43.166 and in a very large database, that could result 99 00:04:43.166 --> 00:04:46.113 in a lot of network traffic as well as using a great deal 100 00:04:46.113 --> 00:04:49.479 of the computer's memory just to process the cursor. 101 00:04:49.479 --> 00:04:50.554 So wherever possible, 102 00:04:50.554 --> 00:04:53.705 try to use SQL to do most of the work for you. 103 00:04:53.705 --> 00:04:55.179 Now, in running this query, 104 00:04:55.179 --> 00:04:57.392 we send a simple query to the database, 105 00:04:57.392 --> 00:05:00.027 and we only got back the information we wanted. 106 00:05:00.027 --> 00:05:02.660 If you're planning on doing a lot of database work, 107 00:05:02.660 --> 00:05:04.579 take the time to get familiar with SQL 108 00:05:04.579 --> 00:05:07.212 and the things that it can do for you. 109 00:05:07.212 --> 00:05:09.544 All right, so I'm gonna run another query to retrieve 110 00:05:09.544 --> 00:05:12.132 the artist names for those duplicate albums, 111 00:05:12.132 --> 00:05:15.341 and also demonstrate some of the power of the SQL language. 112 00:05:15.341 --> 00:05:18.028 So let's go ahead and do that. 113 00:05:18.028 --> 00:05:21.452 And I'm going to start with SELECT, then it's going to be 114 00:05:21.452 --> 00:05:24.035 artists._id, then artists.name, 115 00:05:27.996 --> 00:05:28.996 albums.name, 116 00:05:30.977 --> 00:05:31.977 FROM artists 117 00:05:35.532 --> 00:05:38.365 INNER JOIN albums ON albums.artist 118 00:05:42.252 --> 00:05:44.169 is equal to artists._id 119 00:05:48.093 --> 00:05:49.843 WHERE albums.name IN, 120 00:05:55.418 --> 00:05:59.001 and then in parentheses, SELECT albums.name 121 00:06:00.775 --> 00:06:02.442 FROM albums GROUP BY 122 00:06:04.635 --> 00:06:08.802 albums.name HAVING COUNT(albums.name) 123 00:06:13.730 --> 00:06:17.169 greater than one, end parenthesis. 124 00:06:17.169 --> 00:06:21.581 Then we're gonna put an ORDER BY albums.name, 125 00:06:21.581 --> 00:06:23.831 comma, space, artists.name. 126 00:06:26.033 --> 00:06:27.155 Okay. 127 00:06:27.155 --> 00:06:28.738 So if you run that, 128 00:06:30.445 --> 00:06:31.910 you can see we now know the artists 129 00:06:31.910 --> 00:06:34.589 that have albums of the same name, and that would be, 130 00:06:34.589 --> 00:06:37.110 I think, very useful when testing the fix. 131 00:06:37.110 --> 00:06:40.069 And just to confirm that, we've got Billy Idol, 132 00:06:40.069 --> 00:06:42.344 Fleetwood Mac, Tom Petty and the Heartbreakers, 133 00:06:42.344 --> 00:06:43.902 and Troggs with the four Greatest Hits 134 00:06:43.902 --> 00:06:47.070 that we looked at previously in the GUI interface. 135 00:06:47.070 --> 00:06:49.987 So it's now time for the challenge. 136 00:06:56.903 --> 00:06:59.193 All right, so the challenge is to fix the DataListBox 137 00:06:59.193 --> 00:07:02.839 class so that it displays the songs for the correct album 138 00:07:02.839 --> 00:07:05.435 when the same album name appears more than once 139 00:07:05.435 --> 00:07:07.494 in the database as it's currently doing. 140 00:07:07.494 --> 00:07:09.563 Now, as a hint, it's worth taking the time 141 00:07:09.563 --> 00:07:11.966 to work out what you need the query to be, 142 00:07:11.966 --> 00:07:13.983 then check the code to see at what point 143 00:07:13.983 --> 00:07:17.058 you actually have the information that you'll need. 144 00:07:17.058 --> 00:07:18.493 All right, so that's the challenge. 145 00:07:18.493 --> 00:07:22.660 Pause the video, and I'll see you when you get back. 146 00:07:23.500 --> 00:07:24.946 All right, so, how did you go? 147 00:07:24.946 --> 00:07:26.734 Hopefully you managed to sort that out. 148 00:07:26.734 --> 00:07:30.176 So if we go back to our code now in jukebox, 149 00:07:30.176 --> 00:07:33.009 and I'll make a bit of space here. 150 00:07:34.965 --> 00:07:37.936 So we want to have a look at our on_select method. 151 00:07:37.936 --> 00:07:39.994 You can see that now on line 68. 152 00:07:39.994 --> 00:07:42.972 So that code in that method runs a SQL query 153 00:07:42.972 --> 00:07:45.753 to retrieve the ID for the selected album, 154 00:07:45.753 --> 00:07:47.520 but it doesn't actually consider 155 00:07:47.520 --> 00:07:50.072 which artist the album belongs to. 156 00:07:50.072 --> 00:07:53.795 So the query that we actually end up with 157 00:07:53.795 --> 00:07:57.039 when a Greatest Hits album is selected is actually this one. 158 00:07:57.039 --> 00:08:00.789 We get something like SELECT name, comma, _id 159 00:08:03.715 --> 00:08:07.382 FROM albums WHERE name equals Greatest Hits. 160 00:08:10.563 --> 00:08:13.345 So that's actually the problem effectively with this query, 161 00:08:13.345 --> 00:08:15.014 and then we're actually using the fetchone method 162 00:08:15.014 --> 00:08:17.325 to retrieve the first row returned. 163 00:08:17.325 --> 00:08:20.914 And just to confirm that, if we actually take a copy of this 164 00:08:20.914 --> 00:08:24.247 and go back to our console and run that, 165 00:08:27.985 --> 00:08:30.812 you can see that we've got four records being returned, 166 00:08:30.812 --> 00:08:31.758 and it's obvious at this point 167 00:08:31.758 --> 00:08:33.583 that that approach isn't going to work, 168 00:08:33.583 --> 00:08:35.975 because we should only get be getting one possible row. 169 00:08:35.975 --> 00:08:38.996 So what we really need to do is change the WHERE clause 170 00:08:38.996 --> 00:08:42.372 so that it also includes the artist ID. 171 00:08:42.372 --> 00:08:44.288 So let's go back to our code again. 172 00:08:44.288 --> 00:08:46.702 Well, actually, let's go back to the console 173 00:08:46.702 --> 00:08:48.558 and do it in there first, just to test it. 174 00:08:48.558 --> 00:08:50.030 Once we've got this correct, 175 00:08:50.030 --> 00:08:51.733 we can then actually go back and update the code. 176 00:08:51.733 --> 00:08:53.684 So we've got, at the moment, SELECT name, 177 00:08:53.684 --> 00:08:55.068 comma, and _id. 178 00:08:55.068 --> 00:08:59.302 Let's also add another one, so comma, artist 179 00:08:59.302 --> 00:09:01.878 FROM albums where name equals Greatest Hits. 180 00:09:01.878 --> 00:09:04.588 So we wanna do leave the WHERE as WHERE name equals 181 00:09:04.588 --> 00:09:06.815 Greatest Hits, but we also wanna put 182 00:09:06.815 --> 00:09:09.148 AND artists is equal to 176. 183 00:09:13.080 --> 00:09:17.247 So if we run that, we now correctly get just the one result, 184 00:09:18.221 --> 00:09:19.770 which is exactly what we want here. 185 00:09:19.770 --> 00:09:21.934 And just to verify that this is correct, 186 00:09:21.934 --> 00:09:25.607 we can actually just do a quick search, 187 00:09:25.607 --> 00:09:27.690 SELECT * FROM songs WHERE 188 00:09:30.700 --> 00:09:33.074 album equals 399, which you can see, 189 00:09:33.074 --> 00:09:35.079 the ID is in the results window 190 00:09:35.079 --> 00:09:38.363 down at the bottom, and then run that. 191 00:09:38.363 --> 00:09:40.583 And if you're a Billy Idol fan, you'll realise 192 00:09:40.583 --> 00:09:43.603 that all those songs are definitely Billy Idol songs. 193 00:09:43.603 --> 00:09:46.372 I certainly remember those back from the 80s as well. 194 00:09:46.372 --> 00:09:48.403 All right, so at this point, we know what to do. 195 00:09:48.403 --> 00:09:50.504 The next question is how do we go about it? 196 00:09:50.504 --> 00:09:53.590 Well, we need to add that artist ID to our query, 197 00:09:53.590 --> 00:09:55.840 but where are we gonna get that from? 198 00:09:55.840 --> 00:09:57.126 And if we go back to the code and have a look, 199 00:09:57.126 --> 00:09:59.304 and I'll just close down this output window. 200 00:09:59.304 --> 00:10:03.471 Back to our code, and we have a look at our requery method, 201 00:10:04.757 --> 00:10:07.004 we'll see that it was passed to that method. 202 00:10:07.004 --> 00:10:09.833 So when requery is called on the AlbumsListBox, 203 00:10:09.833 --> 00:10:14.469 we get the artist to filter on in the link_value argument. 204 00:10:14.469 --> 00:10:16.367 This is on line 51. 205 00:10:16.367 --> 00:10:19.090 Now, that's the only time we do know the artist ID, 206 00:10:19.090 --> 00:10:21.540 so we need to store it in a data attribute 207 00:10:21.540 --> 00:10:23.703 so that we can then use it later. 208 00:10:23.703 --> 00:10:26.955 So what I'm going to do is add a field to store 209 00:10:26.955 --> 00:10:29.643 the link value in, and it's going to be updated 210 00:10:29.643 --> 00:10:32.042 every time the requery method's called, 211 00:10:32.042 --> 00:10:33.740 and we also need to cater for the fact 212 00:10:33.740 --> 00:10:37.237 that it can be none when the complete table's displayed. 213 00:10:37.237 --> 00:10:38.070 And of course that happens 214 00:10:38.070 --> 00:10:39.931 with the ArtistListBox for example. 215 00:10:39.931 --> 00:10:42.379 So let's go ahead and add the code for that. 216 00:10:42.379 --> 00:10:44.392 I'm gonna start by adding a data attribute 217 00:10:44.392 --> 00:10:46.182 for the link value. 218 00:10:46.182 --> 00:10:48.515 Self.link_value equals None, 219 00:10:50.757 --> 00:10:53.845 and actually, I'll put it further up here 220 00:10:53.845 --> 00:10:55.284 with these other ones. 221 00:10:55.284 --> 00:10:57.462 It should really go there to be more consistent. 222 00:10:57.462 --> 00:11:00.112 Then in our requery method, let's save it. 223 00:11:00.112 --> 00:11:01.656 So we're going to put a note here. 224 00:11:01.656 --> 00:11:05.823 We're going to put self.link_value is equal to link_value, 225 00:11:07.677 --> 00:11:09.669 and let's also add a comment here, 226 00:11:09.669 --> 00:11:13.086 store the ID so we know the quote-unquote 227 00:11:14.171 --> 00:11:17.088 master record we're populated from. 228 00:11:20.449 --> 00:11:22.177 All right, so now we've done that, 229 00:11:22.177 --> 00:11:24.291 the final step is to update the WHERE clause 230 00:11:24.291 --> 00:11:27.183 of our query in the on_select method. 231 00:11:27.183 --> 00:11:28.312 So let's go back and do that. 232 00:11:28.312 --> 00:11:29.899 So I'm going to put that on the screen, the on_select 233 00:11:29.899 --> 00:11:31.752 so we can see what we're doing there. 234 00:11:31.752 --> 00:11:33.495 And I put a slash slash there. 235 00:11:33.495 --> 00:11:35.327 That was actually meant to be a hash. 236 00:11:35.327 --> 00:11:36.927 I'm too used to coding Java as well, 237 00:11:36.927 --> 00:11:38.720 so, I'll just leave that there. 238 00:11:38.720 --> 00:11:39.553 Actually, I'll remove that. 239 00:11:39.553 --> 00:11:40.721 We don't really need that anymore. 240 00:11:40.721 --> 00:11:43.132 Let's remove that completely, and what we now need to do 241 00:11:43.132 --> 00:11:45.655 after the value here, we need to do 242 00:11:45.655 --> 00:11:47.215 a bit of processing for that link value. 243 00:11:47.215 --> 00:11:50.480 So what we're gonna do is start off, and we'll put, 244 00:11:50.480 --> 00:11:54.397 as a comment, get the ID from the database row, 245 00:11:56.992 --> 00:11:58.793 but also, on the next line, a comment, 246 00:11:58.793 --> 00:12:02.126 make sure we're getting the correct one, 247 00:12:04.511 --> 00:12:08.038 and by including the link value if appropriate. 248 00:12:08.038 --> 00:12:10.205 Link_value if appropriate. 249 00:12:12.301 --> 00:12:13.460 And in terms of the actual code, 250 00:12:13.460 --> 00:12:16.377 that's gonna be if self.link_value. 251 00:12:17.808 --> 00:12:21.552 Then we're gonna put value is equal to value, 252 00:12:21.552 --> 00:12:26.026 square brackets, zero, comma, self.link_value, 253 00:12:26.026 --> 00:12:29.193 then a sql_where is gonna be equal to, 254 00:12:31.929 --> 00:12:34.908 double quotes, space, WHERE, space, 255 00:12:34.908 --> 00:12:36.522 and double quote again, plus 256 00:12:36.522 --> 00:12:39.689 self.field, plus, equals question mark 257 00:12:42.088 --> 00:12:45.297 in double quotes, space, AND, space, 258 00:12:45.297 --> 00:12:46.518 then double quote again, 259 00:12:46.518 --> 00:12:48.768 plus self.link_field, plus, 260 00:12:52.248 --> 00:12:53.998 equals question mark. 261 00:12:55.279 --> 00:12:58.442 Now we're going to have an else on the next line instead, 262 00:12:58.442 --> 00:13:00.010 so if we haven't got a link_value, we're just going 263 00:13:00.010 --> 00:13:03.466 to put sql_where, make that equal to WHERE 264 00:13:03.466 --> 00:13:06.605 in double quotes with a space at the start and end, 265 00:13:06.605 --> 00:13:09.936 end double quote, plus, self.field 266 00:13:09.936 --> 00:13:12.911 plus, equals question mark. 267 00:13:12.911 --> 00:13:14.166 All right, then I'll just delete that comment, 268 00:13:14.166 --> 00:13:16.499 'cause we've got the comment up above now. 269 00:13:16.499 --> 00:13:20.332 So link_id is now going to be self.sql.select, 270 00:13:21.292 --> 00:13:25.375 plus, I'm actually going to remove the WHERE now, 271 00:13:26.273 --> 00:13:28.558 because we've actually created the WHERE, the sql_where 272 00:13:28.558 --> 00:13:32.298 variable above, so that's sql_where, comma, value, 273 00:13:32.298 --> 00:13:34.292 and we leave the fetchone call 274 00:13:34.292 --> 00:13:37.243 and then the one in square brackets as it is. 275 00:13:37.243 --> 00:13:39.351 And then we'll actually still just go ahead 276 00:13:39.351 --> 00:13:42.434 and call the self.linked_box_requery. 277 00:13:43.755 --> 00:13:45.060 So the first thing we do is we're making sure 278 00:13:45.060 --> 00:13:48.967 that we do have a link value here on line 78. 279 00:13:48.967 --> 00:13:51.687 If not, sql_where is set to what it had before, 280 00:13:51.687 --> 00:13:54.655 so we're doing just a SQL query there, 281 00:13:54.655 --> 00:13:56.137 which will basically be the entire table, 282 00:13:56.137 --> 00:13:59.318 but if we do have an ID in link_value, then this code 283 00:13:59.318 --> 00:14:02.216 starting on like 79 is executed, so we're building 284 00:14:02.216 --> 00:14:04.980 up a WHERE clause that now includes that link_value. 285 00:14:04.980 --> 00:14:07.735 Now, one thing to note is that value is a tuple, 286 00:14:07.735 --> 00:14:09.568 and tuples are immutable. 287 00:14:09.568 --> 00:14:11.813 So we can't add the ID to the tuple. 288 00:14:11.813 --> 00:14:15.240 So instead, you can see what I'm doing on line 79 289 00:14:15.240 --> 00:14:18.616 is creating a new tuple by combining the first item 290 00:14:18.616 --> 00:14:21.687 in the existing value with our link_value, and we saw 291 00:14:21.687 --> 00:14:24.363 that in section seven of the course when we built up a new 292 00:14:24.363 --> 00:14:28.521 Imelda tuple to correct the spelling mistake in her name. 293 00:14:28.521 --> 00:14:29.747 So, if we have a link_value, 294 00:14:29.747 --> 00:14:32.117 our value tuple will contain both the name 295 00:14:32.117 --> 00:14:35.170 and the master ID, the artist ID, in this case. 296 00:14:35.170 --> 00:14:36.495 But if we don't have a link_value, 297 00:14:36.495 --> 00:14:38.858 the value tuple just contains the name. 298 00:14:38.858 --> 00:14:41.513 In both cases, though, the number of items in the tuple 299 00:14:41.513 --> 00:14:43.426 corresponds to the number of placeholders 300 00:14:43.426 --> 00:14:45.646 in the sql_where clause. 301 00:14:45.646 --> 00:14:47.718 All right, so that should be the bug fixed, 302 00:14:47.718 --> 00:14:50.054 so let's actually try running it, and we'll check those 303 00:14:50.054 --> 00:14:54.221 records that weren't working correctly earlier in the video. 304 00:14:55.287 --> 00:14:57.185 All right, so let's have a look, 305 00:14:57.185 --> 00:14:58.863 and the first one was Billy Idol. 306 00:14:58.863 --> 00:15:02.596 If we click on Billy Idol, click on Greatest Hits, 307 00:15:02.596 --> 00:15:03.718 and that looks a lot better now. 308 00:15:03.718 --> 00:15:07.661 We've got a list there that seem to be the right ones. 309 00:15:07.661 --> 00:15:10.578 And let's just check Fleetwood Mac. 310 00:15:12.655 --> 00:15:14.933 Greatest Hits, that looks a lot better. 311 00:15:14.933 --> 00:15:16.318 Again, a completely different list now. 312 00:15:16.318 --> 00:15:18.041 We haven't got those ones that were appearing before. 313 00:15:18.041 --> 00:15:19.351 Let's try another one. 314 00:15:19.351 --> 00:15:21.184 Go down to the Troggs. 315 00:15:22.085 --> 00:15:24.503 Greatest Hits, different set of songs there, 316 00:15:24.503 --> 00:15:26.599 and they're clearly Troggs songs as well. 317 00:15:26.599 --> 00:15:27.934 We'll also try a couple of other ones. 318 00:15:27.934 --> 00:15:29.334 Let's just go back up. 319 00:15:29.334 --> 00:15:34.082 Another one that was duplicated was the Blue Oyster Cult, 320 00:15:34.082 --> 00:15:36.518 and that had the Champions of Rock, 321 00:15:36.518 --> 00:15:39.292 and the other one that had the same name for an album 322 00:15:39.292 --> 00:15:43.031 was Nazareth, so let's have a look at Nazareth. 323 00:15:43.031 --> 00:15:44.701 Champions of Rock. 324 00:15:44.701 --> 00:15:45.850 Clearly a different set of songs here, 325 00:15:45.850 --> 00:15:47.986 so I think everything is looking good 326 00:15:47.986 --> 00:15:50.846 and working well, and there's a few more we could check. 327 00:15:50.846 --> 00:15:52.723 You could go back to that other query to see the other ones 328 00:15:52.723 --> 00:15:56.077 that were duplicated, but it's pretty clear here that things 329 00:15:56.077 --> 00:15:59.242 are now working correctly, and we've got that bug fixed. 330 00:15:59.242 --> 00:16:00.424 And by the way, if you're not familiar 331 00:16:00.424 --> 00:16:02.865 with any of these artists, entering the artist 332 00:16:02.865 --> 00:16:05.717 and album names into Google, into the Google search engine, 333 00:16:05.717 --> 00:16:06.669 can be a good way to check 334 00:16:06.669 --> 00:16:08.494 that you've got the correct album. 335 00:16:08.494 --> 00:16:11.213 You'll probably struggle to find an exact match with some 336 00:16:11.213 --> 00:16:14.217 of these because it depends on the orchestra and conductor. 337 00:16:14.217 --> 00:16:17.497 For example, I'll just show you one other one, 338 00:16:17.497 --> 00:16:19.915 this one here, Mussorgsky, 339 00:16:19.915 --> 00:16:21.937 Pictures of an Exhibition. 340 00:16:21.937 --> 00:16:23.281 So you might struggle with that particular one 341 00:16:23.281 --> 00:16:25.819 because it depends on the orchestra and conductor. 342 00:16:25.819 --> 00:16:27.537 The results we're getting is different 343 00:16:27.537 --> 00:16:29.430 to the Emerson, Lake and Palmer album, though, 344 00:16:29.430 --> 00:16:31.509 so it's no longer returning the wrong song list, 345 00:16:31.509 --> 00:16:35.857 and the Emerson, Lake and Palmer album I'm talking about, 346 00:16:35.857 --> 00:16:37.863 this one here, you can see 347 00:16:37.863 --> 00:16:40.723 we've clearly got a different set of songs there. 348 00:16:40.723 --> 00:16:42.346 So I think that's a really important lesson 349 00:16:42.346 --> 00:16:45.255 to be taken from this, or your takeaway from this, 350 00:16:45.255 --> 00:16:47.526 and that's to test your programmes thoroughly. 351 00:16:47.526 --> 00:16:49.913 Now, unless you happen to select two matching albums 352 00:16:49.913 --> 00:16:52.231 while testing and notice that they were displaying 353 00:16:52.231 --> 00:16:55.918 the same records, this bug may well have gone unnoticed, 354 00:16:55.918 --> 00:16:58.886 and testing database programmes can be particularly difficult 355 00:16:58.886 --> 00:17:01.535 because the behaviour of a programme is closely tied 356 00:17:01.535 --> 00:17:03.485 to the underlying data. 357 00:17:03.485 --> 00:17:05.412 But running queries like the one we used earlier 358 00:17:05.412 --> 00:17:07.906 in this video is a good idea to identify things 359 00:17:07.906 --> 00:17:10.336 like duplicate columns in the data. 360 00:17:10.336 --> 00:17:12.280 And we should also test the class when there's an artist 361 00:17:12.280 --> 00:17:15.307 with no albums and also an album with no songs listed, 362 00:17:15.307 --> 00:17:17.574 just to make sure it works and doesn't crash. 363 00:17:17.574 --> 00:17:18.436 Now, it does actually work, 364 00:17:18.436 --> 00:17:20.087 but I'll leave you to check that. 365 00:17:20.087 --> 00:17:21.362 All right, so let's finish the video here. 366 00:17:21.362 --> 00:17:22.348 It's got to be quite long, 367 00:17:22.348 --> 00:17:25.145 and I'll see you as always in the next video.