1 00:00:04,939 --> 00:00:06,010 All right, so at this point, 2 00:00:06,010 --> 00:00:09,810 I think our DataListBox class is pretty cool, and we 3 00:00:09,810 --> 00:00:12,390 didn't have to write a lot of code to implement it, either. 4 00:00:12,390 --> 00:00:14,770 But unfortunately, as I alluded to at the end of the last 5 00:00:14,770 --> 00:00:18,120 video, there's a bug in it, and you can probably guess 6 00:00:18,120 --> 00:00:19,990 what the challenge is going to be. 7 00:00:19,990 --> 00:00:23,160 But first, though, let's actually see what the bug is. 8 00:00:23,160 --> 00:00:27,080 So I'm going to actually run the programme again. 9 00:00:32,580 --> 00:00:36,000 And what I'm going to do is select Billy Idol 10 00:00:36,000 --> 00:00:38,650 from the lists of artists over here, and you can see 11 00:00:38,650 --> 00:00:41,310 there's a single album here called Greatest Hits. 12 00:00:41,310 --> 00:00:44,690 Now, lots of artists produce Greatest Hits albums, 13 00:00:44,690 --> 00:00:47,280 so it'll be of no surprise if there's other albums 14 00:00:47,280 --> 00:00:49,100 with that name in the database. 15 00:00:49,100 --> 00:00:53,050 So if we select this album is the AlbumsListBox 16 00:00:53,050 --> 00:00:55,260 and make a mental note of the songs on the album, 17 00:00:55,260 --> 00:00:56,710 just at least some of them. 18 00:00:56,710 --> 00:00:59,260 You don't have to memorise the full list, but just note 19 00:00:59,260 --> 00:01:01,540 that the first track here is called American Girl, 20 00:01:01,540 --> 00:01:04,540 and the last one is down here, Something in the Air. 21 00:01:04,540 --> 00:01:06,170 All right, so that's Billy Idol, but if we scroll down 22 00:01:06,170 --> 00:01:07,920 now to Fleetwood Mac, 23 00:01:09,980 --> 00:01:11,380 choose them, 24 00:01:11,380 --> 00:01:14,190 they've also got a Greatest Hits, you can see there. 25 00:01:14,190 --> 00:01:16,960 Click on Greatest Hits, lo and behold, 26 00:01:16,960 --> 00:01:18,800 the first song, American Girl, 27 00:01:18,800 --> 00:01:21,280 and the last one is Something in the Air. 28 00:01:21,280 --> 00:01:24,500 And if you scroll down and look at another one, 29 00:01:24,500 --> 00:01:26,990 Tom Petty and the Heartbreakers here, 30 00:01:26,990 --> 00:01:28,450 so click on that one. 31 00:01:28,450 --> 00:01:31,220 They've got a Greatest Hits, and again, 32 00:01:31,220 --> 00:01:33,540 American Girl and Something in the Air. 33 00:01:33,540 --> 00:01:36,810 And just to be completely sure, click on the Troggs. 34 00:01:36,810 --> 00:01:38,980 We've got Greatest Hits, 35 00:01:38,980 --> 00:01:42,300 and again, another familiar list of songs. 36 00:01:42,300 --> 00:01:44,820 Now, it's not really likely that four different artists 37 00:01:44,820 --> 00:01:47,460 have all produced a Greatest Hits album with identical 38 00:01:47,460 --> 00:01:51,010 track lists, so clearly there's a bug in our class. 39 00:01:51,010 --> 00:01:53,450 The problem is that we're looking up the displayed value 40 00:01:53,450 --> 00:01:56,580 in the database rather than using IDs. 41 00:01:56,580 --> 00:02:00,300 That was a design decision that we made, and it is valid, 42 00:02:00,300 --> 00:02:02,730 but we just have to do it correctly. 43 00:02:02,730 --> 00:02:04,810 Now, if different master records have the same 44 00:02:04,810 --> 00:02:07,820 related record such as different artists having 45 00:02:07,820 --> 00:02:10,229 an album with the same name, our query 46 00:02:10,229 --> 00:02:13,020 just picks the first one from the database. 47 00:02:13,020 --> 00:02:17,320 So your challenge now is to modify the DataListBox class 48 00:02:17,320 --> 00:02:19,550 so that it retrieves the correct record 49 00:02:19,550 --> 00:02:21,140 from the linked table, 50 00:02:21,140 --> 00:02:23,840 the album for the correct artists, in this example. 51 00:02:23,840 --> 00:02:26,140 But keep in mind and remember that we're making 52 00:02:26,140 --> 00:02:30,010 this class generic so that it can cope with any tables 53 00:02:30,010 --> 00:02:32,760 that have a single column primary key. 54 00:02:32,760 --> 00:02:34,080 Now, before you have a go at that, 55 00:02:34,080 --> 00:02:36,460 I'm going to show you how to query the database tables 56 00:02:36,460 --> 00:02:39,820 from within IntelliJ IDEA or PyCharm. 57 00:02:39,820 --> 00:02:42,320 So we're gonna close this down 58 00:02:43,230 --> 00:02:46,680 and click over here to the Database tab, 59 00:02:46,680 --> 00:02:48,350 and we click over here to the console, 60 00:02:48,350 --> 00:02:51,560 little console there to open the console. 61 00:02:51,560 --> 00:02:53,850 And you can see that's opened up a tab where you 62 00:02:53,850 --> 00:02:56,380 can execute SQL queries against the database 63 00:02:56,380 --> 00:02:59,180 and see the results in a pane at the bottom of the screen, 64 00:02:59,180 --> 00:03:01,610 and you'll see that when we actually do a query. 65 00:03:01,610 --> 00:03:04,480 And if you've read about on SQL after the introduction 66 00:03:04,480 --> 00:03:06,550 earlier in this section, you'll be familiar 67 00:03:06,550 --> 00:03:08,320 with the GROUP BY and HAVING clauses. 68 00:03:08,320 --> 00:03:10,970 But if not, they're both well worth investigating. 69 00:03:10,970 --> 00:03:13,270 So we can find out how many duplicate album names 70 00:03:13,270 --> 00:03:16,630 there are by running the query that I'm about to type in. 71 00:03:16,630 --> 00:03:19,460 So we can type SELECT albums.name, 72 00:03:21,970 --> 00:03:23,630 comma, space, count, 73 00:03:24,660 --> 00:03:25,940 and I should really do COUNT in uppercase, 74 00:03:25,940 --> 00:03:27,990 just to be consistent here. 75 00:03:27,990 --> 00:03:32,160 (albums.name) AS, and it's going to be num_albums 76 00:03:36,800 --> 00:03:39,970 FROM albums ORDER BY, sorry, GROUP BY, 77 00:03:43,190 --> 00:03:46,280 GROUP BY, that's gonna be albums.name 78 00:03:48,050 --> 00:03:50,960 HAVING num_albums greater than one. 79 00:03:53,950 --> 00:03:55,340 And in this console, you don't need 80 00:03:55,340 --> 00:03:57,680 to end your queries with a semicolon. 81 00:03:57,680 --> 00:04:01,920 So, click on this little green triangle, and you can see 82 00:04:01,920 --> 00:04:04,680 down at the bottom, we get the results of the query. 83 00:04:04,680 --> 00:04:06,000 So, in this case, we've got four 84 00:04:06,000 --> 00:04:09,850 duplicated album names in the database. 85 00:04:09,850 --> 00:04:11,620 So three of them appear twice, 86 00:04:11,620 --> 00:04:15,460 and our Greatest Hits example appears four times. 87 00:04:15,460 --> 00:04:18,160 So it will be useful to know which artists are associated 88 00:04:18,160 --> 00:04:21,180 with those albums so that we can test our fix. 89 00:04:21,180 --> 00:04:24,170 Now, one thing you could do is run four separate queries 90 00:04:24,170 --> 00:04:26,450 to find the albums in the database for each of those 91 00:04:26,450 --> 00:04:30,720 four names, but SQL's an incredibly powerful language. 92 00:04:30,720 --> 00:04:32,120 In fact, just producing that list 93 00:04:32,120 --> 00:04:34,050 would have been less than ideal 94 00:04:34,050 --> 00:04:35,790 if we try to use Python. 95 00:04:35,790 --> 00:04:38,820 Now, Python's very good at pulling out duplicates in a list. 96 00:04:38,820 --> 00:04:41,050 The problem would be that we'd have to retrieve 97 00:04:41,050 --> 00:04:43,890 the full set of albums from our database first, 98 00:04:43,890 --> 00:04:46,400 and in a very large database, that could result 99 00:04:46,400 --> 00:04:49,350 in a lot of network traffic as well as using a great deal 100 00:04:49,350 --> 00:04:52,710 of the computer's memory just to process the cursor. 101 00:04:52,710 --> 00:04:53,790 So wherever possible, 102 00:04:53,790 --> 00:04:56,940 try to use SQL to do most of the work for you. 103 00:04:56,940 --> 00:04:58,410 Now, in running this query, 104 00:04:58,410 --> 00:05:00,630 we send a simple query to the database, 105 00:05:00,630 --> 00:05:03,260 and we only got back the information we wanted. 106 00:05:03,260 --> 00:05:05,900 If you're planning on doing a lot of database work, 107 00:05:05,900 --> 00:05:07,810 take the time to get familiar with SQL 108 00:05:07,810 --> 00:05:10,450 and the things that it can do for you. 109 00:05:10,450 --> 00:05:12,780 All right, so I'm gonna run another query to retrieve 110 00:05:12,780 --> 00:05:15,370 the artist names for those duplicate albums, 111 00:05:15,370 --> 00:05:18,580 and also demonstrate some of the power of the SQL language. 112 00:05:18,580 --> 00:05:21,260 So let's go ahead and do that. 113 00:05:21,260 --> 00:05:24,690 And I'm going to start with SELECT, then it's going to be 114 00:05:24,690 --> 00:05:27,270 artists._id, then artists.name, 115 00:05:31,230 --> 00:05:32,230 albums.name, 116 00:05:34,210 --> 00:05:35,210 FROM artists 117 00:05:38,770 --> 00:05:41,600 INNER JOIN albums ON albums.artist 118 00:05:45,490 --> 00:05:47,400 is equal to artists._id 119 00:05:51,330 --> 00:05:53,080 WHERE albums.name IN, 120 00:05:58,650 --> 00:06:02,240 and then in parentheses, SELECT albums.name 121 00:06:04,010 --> 00:06:05,680 FROM albums GROUP BY 122 00:06:07,870 --> 00:06:12,040 albums.name HAVING COUNT(albums.name) 123 00:06:16,970 --> 00:06:20,400 greater than one, end parenthesis. 124 00:06:20,400 --> 00:06:24,820 Then we're gonna put an ORDER BY albums.name, 125 00:06:24,820 --> 00:06:27,070 comma, space, artists.name. 126 00:06:29,270 --> 00:06:30,390 Okay. 127 00:06:30,390 --> 00:06:31,970 So if you run that, 128 00:06:33,680 --> 00:06:35,150 you can see we now know the artists 129 00:06:35,150 --> 00:06:37,820 that have albums of the same name, and that would be, 130 00:06:37,820 --> 00:06:40,350 I think, very useful when testing the fix. 131 00:06:40,350 --> 00:06:43,300 And just to confirm that, we've got Billy Idol, 132 00:06:43,300 --> 00:06:45,580 Fleetwood Mac, Tom Petty and the Heartbreakers, 133 00:06:45,580 --> 00:06:47,140 and Troggs with the four Greatest Hits 134 00:06:47,140 --> 00:06:50,310 that we looked at previously in the GUI interface. 135 00:06:50,310 --> 00:06:53,220 So it's now time for the challenge. 136 00:07:00,140 --> 00:07:02,430 All right, so the challenge is to fix the DataListBox 137 00:07:02,430 --> 00:07:06,070 class so that it displays the songs for the correct album 138 00:07:06,070 --> 00:07:08,670 when the same album name appears more than once 139 00:07:08,670 --> 00:07:10,730 in the database as it's currently doing. 140 00:07:10,730 --> 00:07:12,800 Now, as a hint, it's worth taking the time 141 00:07:12,800 --> 00:07:15,200 to work out what you need the query to be, 142 00:07:15,200 --> 00:07:17,220 then check the code to see at what point 143 00:07:17,220 --> 00:07:20,290 you actually have the information that you'll need. 144 00:07:20,290 --> 00:07:21,730 All right, so that's the challenge. 145 00:07:21,730 --> 00:07:25,900 Pause the video, and I'll see you when you get back. 146 00:07:26,740 --> 00:07:28,180 All right, so, how did you go? 147 00:07:28,180 --> 00:07:29,970 Hopefully you managed to sort that out. 148 00:07:29,970 --> 00:07:33,410 So if we go back to our code now in jukebox, 149 00:07:33,410 --> 00:07:36,240 and I'll make a bit of space here. 150 00:07:38,200 --> 00:07:41,170 So we want to have a look at our on_select method. 151 00:07:41,170 --> 00:07:43,230 You can see that now on line 68. 152 00:07:43,230 --> 00:07:46,210 So that code in that method runs a SQL query 153 00:07:46,210 --> 00:07:48,990 to retrieve the ID for the selected album, 154 00:07:48,990 --> 00:07:50,760 but it doesn't actually consider 155 00:07:50,760 --> 00:07:53,310 which artist the album belongs to. 156 00:07:53,310 --> 00:07:57,030 So the query that we actually end up with 157 00:07:57,030 --> 00:08:00,270 when a Greatest Hits album is selected is actually this one. 158 00:08:00,270 --> 00:08:04,020 We get something like SELECT name, comma, _id 159 00:08:06,950 --> 00:08:10,620 FROM albums WHERE name equals Greatest Hits. 160 00:08:13,800 --> 00:08:16,580 So that's actually the problem effectively with this query, 161 00:08:16,580 --> 00:08:18,250 and then we're actually using the fetchone method 162 00:08:18,250 --> 00:08:20,560 to retrieve the first row returned. 163 00:08:20,560 --> 00:08:24,150 And just to confirm that, if we actually take a copy of this 164 00:08:24,150 --> 00:08:27,480 and go back to our console and run that, 165 00:08:31,220 --> 00:08:34,049 you can see that we've got four records being returned, 166 00:08:34,049 --> 00:08:34,990 and it's obvious at this point 167 00:08:34,990 --> 00:08:36,820 that that approach isn't going to work, 168 00:08:36,820 --> 00:08:39,210 because we should only get be getting one possible row. 169 00:08:39,210 --> 00:08:42,230 So what we really need to do is change the WHERE clause 170 00:08:42,230 --> 00:08:45,610 so that it also includes the artist ID. 171 00:08:45,610 --> 00:08:47,520 So let's go back to our code again. 172 00:08:47,520 --> 00:08:49,940 Well, actually, let's go back to the console 173 00:08:49,940 --> 00:08:51,790 and do it in there first, just to test it. 174 00:08:51,790 --> 00:08:53,270 Once we've got this correct, 175 00:08:53,270 --> 00:08:54,970 we can then actually go back and update the code. 176 00:08:54,970 --> 00:08:56,920 So we've got, at the moment, SELECT name, 177 00:08:56,920 --> 00:08:58,300 comma, and _id. 178 00:08:58,300 --> 00:09:02,540 Let's also add another one, so comma, artist 179 00:09:02,540 --> 00:09:05,110 FROM albums where name equals Greatest Hits. 180 00:09:05,110 --> 00:09:07,820 So we wanna do leave the WHERE as WHERE name equals 181 00:09:07,820 --> 00:09:10,050 Greatest Hits, but we also wanna put 182 00:09:10,050 --> 00:09:12,380 AND artists is equal to 176. 183 00:09:16,320 --> 00:09:20,480 So if we run that, we now correctly get just the one result, 184 00:09:21,460 --> 00:09:23,010 which is exactly what we want here. 185 00:09:23,010 --> 00:09:25,170 And just to verify that this is correct, 186 00:09:25,170 --> 00:09:28,840 we can actually just do a quick search, 187 00:09:28,840 --> 00:09:30,930 SELECT * FROM songs WHERE 188 00:09:33,940 --> 00:09:36,310 album equals 399, which you can see, 189 00:09:36,310 --> 00:09:38,310 the ID is in the results window 190 00:09:38,310 --> 00:09:41,600 down at the bottom, and then run that. 191 00:09:41,600 --> 00:09:43,820 And if you're a Billy Idol fan, you'll realise 192 00:09:43,820 --> 00:09:46,840 that all those songs are definitely Billy Idol songs. 193 00:09:46,840 --> 00:09:49,610 I certainly remember those back from the 80s as well. 194 00:09:49,610 --> 00:09:51,640 All right, so at this point, we know what to do. 195 00:09:51,640 --> 00:09:53,740 The next question is how do we go about it? 196 00:09:53,740 --> 00:09:56,830 Well, we need to add that artist ID to our query, 197 00:09:56,830 --> 00:09:59,080 but where are we gonna get that from? 198 00:09:59,080 --> 00:10:00,360 And if we go back to the code and have a look, 199 00:10:00,360 --> 00:10:02,540 and I'll just close down this output window. 200 00:10:02,540 --> 00:10:06,710 Back to our code, and we have a look at our requery method, 201 00:10:07,990 --> 00:10:10,240 we'll see that it was passed to that method. 202 00:10:10,240 --> 00:10:13,070 So when requery is called on the AlbumsListBox, 203 00:10:13,070 --> 00:10:17,700 we get the artist to filter on in the link_value argument. 204 00:10:17,700 --> 00:10:19,600 This is on line 51. 205 00:10:19,600 --> 00:10:22,330 Now, that's the only time we do know the artist ID, 206 00:10:22,330 --> 00:10:24,780 so we need to store it in a data attribute 207 00:10:24,780 --> 00:10:26,940 so that we can then use it later. 208 00:10:26,940 --> 00:10:30,190 So what I'm going to do is add a field to store 209 00:10:30,190 --> 00:10:32,880 the link value in, and it's going to be updated 210 00:10:32,880 --> 00:10:35,280 every time the requery method's called, 211 00:10:35,280 --> 00:10:36,980 and we also need to cater for the fact 212 00:10:36,980 --> 00:10:40,470 that it can be none when the complete table's displayed. 213 00:10:40,470 --> 00:10:41,310 And of course that happens 214 00:10:41,310 --> 00:10:43,170 with the ArtistListBox for example. 215 00:10:43,170 --> 00:10:45,610 So let's go ahead and add the code for that. 216 00:10:45,610 --> 00:10:47,630 I'm gonna start by adding a data attribute 217 00:10:47,630 --> 00:10:49,420 for the link value. 218 00:10:49,420 --> 00:10:51,750 Self.link_value equals None, 219 00:10:53,990 --> 00:10:57,080 and actually, I'll put it further up here 220 00:10:57,080 --> 00:10:58,520 with these other ones. 221 00:10:58,520 --> 00:11:00,700 It should really go there to be more consistent. 222 00:11:00,700 --> 00:11:03,350 Then in our requery method, let's save it. 223 00:11:03,350 --> 00:11:04,890 So we're going to put a note here. 224 00:11:04,890 --> 00:11:09,060 We're going to put self.link_value is equal to link_value, 225 00:11:10,910 --> 00:11:12,900 and let's also add a comment here, 226 00:11:12,900 --> 00:11:16,320 store the ID so we know the quote-unquote 227 00:11:17,410 --> 00:11:20,320 master record we're populated from. 228 00:11:23,680 --> 00:11:25,410 All right, so now we've done that, 229 00:11:25,410 --> 00:11:27,530 the final step is to update the WHERE clause 230 00:11:27,530 --> 00:11:30,420 of our query in the on_select method. 231 00:11:30,420 --> 00:11:31,550 So let's go back and do that. 232 00:11:31,550 --> 00:11:33,130 So I'm going to put that on the screen, the on_select 233 00:11:33,130 --> 00:11:34,990 so we can see what we're doing there. 234 00:11:34,990 --> 00:11:36,730 And I put a slash slash there. 235 00:11:36,730 --> 00:11:38,560 That was actually meant to be a hash. 236 00:11:38,560 --> 00:11:40,160 I'm too used to coding Java as well, 237 00:11:40,160 --> 00:11:41,960 so, I'll just leave that there. 238 00:11:41,960 --> 00:11:42,790 Actually, I'll remove that. 239 00:11:42,790 --> 00:11:43,960 We don't really need that anymore. 240 00:11:43,960 --> 00:11:46,370 Let's remove that completely, and what we now need to do 241 00:11:46,370 --> 00:11:48,890 after the value here, we need to do 242 00:11:48,890 --> 00:11:50,450 a bit of processing for that link value. 243 00:11:50,450 --> 00:11:53,720 So what we're gonna do is start off, and we'll put, 244 00:11:53,720 --> 00:11:57,630 as a comment, get the ID from the database row, 245 00:12:00,230 --> 00:12:02,030 but also, on the next line, a comment, 246 00:12:02,030 --> 00:12:05,360 make sure we're getting the correct one, 247 00:12:07,750 --> 00:12:11,270 and by including the link value if appropriate. 248 00:12:11,270 --> 00:12:13,440 Link_value if appropriate. 249 00:12:15,540 --> 00:12:16,700 And in terms of the actual code, 250 00:12:16,700 --> 00:12:19,610 that's gonna be if self.link_value. 251 00:12:21,040 --> 00:12:24,790 Then we're gonna put value is equal to value, 252 00:12:24,790 --> 00:12:29,260 square brackets, zero, comma, self.link_value, 253 00:12:29,260 --> 00:12:32,430 then a sql_where is gonna be equal to, 254 00:12:35,160 --> 00:12:38,140 double quotes, space, WHERE, space, 255 00:12:38,140 --> 00:12:39,760 and double quote again, plus 256 00:12:39,760 --> 00:12:42,920 self.field, plus, equals question mark 257 00:12:45,320 --> 00:12:48,530 in double quotes, space, AND, space, 258 00:12:48,530 --> 00:12:49,750 then double quote again, 259 00:12:49,750 --> 00:12:52,000 plus self.link_field, plus, 260 00:12:55,480 --> 00:12:57,230 equals question mark. 261 00:12:58,510 --> 00:13:01,680 Now we're going to have an else on the next line instead, 262 00:13:01,680 --> 00:13:03,250 so if we haven't got a link_value, we're just going 263 00:13:03,250 --> 00:13:06,700 to put sql_where, make that equal to WHERE 264 00:13:06,700 --> 00:13:09,840 in double quotes with a space at the start and end, 265 00:13:09,840 --> 00:13:13,170 end double quote, plus, self.field 266 00:13:13,170 --> 00:13:16,150 plus, equals question mark. 267 00:13:16,150 --> 00:13:17,400 All right, then I'll just delete that comment, 268 00:13:17,400 --> 00:13:19,730 'cause we've got the comment up above now. 269 00:13:19,730 --> 00:13:23,570 So link_id is now going to be self.sql.select, 270 00:13:24,530 --> 00:13:28,610 plus, I'm actually going to remove the WHERE now, 271 00:13:29,510 --> 00:13:31,790 because we've actually created the WHERE, the sql_where 272 00:13:31,790 --> 00:13:35,530 variable above, so that's sql_where, comma, value, 273 00:13:35,530 --> 00:13:37,530 and we leave the fetchone call 274 00:13:37,530 --> 00:13:40,480 and then the one in square brackets as it is. 275 00:13:40,480 --> 00:13:42,590 And then we'll actually still just go ahead 276 00:13:42,590 --> 00:13:45,670 and call the self.linked_box_requery. 277 00:13:46,990 --> 00:13:48,300 So the first thing we do is we're making sure 278 00:13:48,300 --> 00:13:52,200 that we do have a link value here on line 78. 279 00:13:52,200 --> 00:13:54,920 If not, sql_where is set to what it had before, 280 00:13:54,920 --> 00:13:57,890 so we're doing just a SQL query there, 281 00:13:57,890 --> 00:13:59,370 which will basically be the entire table, 282 00:13:59,370 --> 00:14:02,550 but if we do have an ID in link_value, then this code 283 00:14:02,550 --> 00:14:05,450 starting on like 79 is executed, so we're building 284 00:14:05,450 --> 00:14:08,220 up a WHERE clause that now includes that link_value. 285 00:14:08,220 --> 00:14:10,970 Now, one thing to note is that value is a tuple, 286 00:14:10,970 --> 00:14:12,800 and tuples are immutable. 287 00:14:12,800 --> 00:14:15,050 So we can't add the ID to the tuple. 288 00:14:15,050 --> 00:14:18,480 So instead, you can see what I'm doing on line 79 289 00:14:18,480 --> 00:14:21,850 is creating a new tuple by combining the first item 290 00:14:21,850 --> 00:14:24,920 in the existing value with our link_value, and we saw 291 00:14:24,920 --> 00:14:27,600 that in section seven of the course when we built up a new 292 00:14:27,600 --> 00:14:31,760 Imelda tuple to correct the spelling mistake in her name. 293 00:14:31,760 --> 00:14:32,980 So, if we have a link_value, 294 00:14:32,980 --> 00:14:35,350 our value tuple will contain both the name 295 00:14:35,350 --> 00:14:38,410 and the master ID, the artist ID, in this case. 296 00:14:38,410 --> 00:14:39,730 But if we don't have a link_value, 297 00:14:39,730 --> 00:14:42,090 the value tuple just contains the name. 298 00:14:42,090 --> 00:14:44,750 In both cases, though, the number of items in the tuple 299 00:14:44,750 --> 00:14:46,660 corresponds to the number of placeholders 300 00:14:46,660 --> 00:14:48,880 in the sql_where clause. 301 00:14:48,880 --> 00:14:50,950 All right, so that should be the bug fixed, 302 00:14:50,950 --> 00:14:53,290 so let's actually try running it, and we'll check those 303 00:14:53,290 --> 00:14:57,460 records that weren't working correctly earlier in the video. 304 00:14:58,520 --> 00:15:00,420 All right, so let's have a look, 305 00:15:00,420 --> 00:15:02,100 and the first one was Billy Idol. 306 00:15:02,100 --> 00:15:05,830 If we click on Billy Idol, click on Greatest Hits, 307 00:15:05,830 --> 00:15:06,950 and that looks a lot better now. 308 00:15:06,950 --> 00:15:10,900 We've got a list there that seem to be the right ones. 309 00:15:10,900 --> 00:15:13,810 And let's just check Fleetwood Mac. 310 00:15:15,890 --> 00:15:18,170 Greatest Hits, that looks a lot better. 311 00:15:18,170 --> 00:15:19,550 Again, a completely different list now. 312 00:15:19,550 --> 00:15:21,280 We haven't got those ones that were appearing before. 313 00:15:21,280 --> 00:15:22,590 Let's try another one. 314 00:15:22,590 --> 00:15:24,420 Go down to the Troggs. 315 00:15:25,320 --> 00:15:27,740 Greatest Hits, different set of songs there, 316 00:15:27,740 --> 00:15:29,830 and they're clearly Troggs songs as well. 317 00:15:29,830 --> 00:15:31,170 We'll also try a couple of other ones. 318 00:15:31,170 --> 00:15:32,570 Let's just go back up. 319 00:15:32,570 --> 00:15:37,320 Another one that was duplicated was the Blue Oyster Cult, 320 00:15:37,320 --> 00:15:39,750 and that had the Champions of Rock, 321 00:15:39,750 --> 00:15:42,530 and the other one that had the same name for an album 322 00:15:42,530 --> 00:15:46,270 was Nazareth, so let's have a look at Nazareth. 323 00:15:46,270 --> 00:15:47,940 Champions of Rock. 324 00:15:47,940 --> 00:15:49,090 Clearly a different set of songs here, 325 00:15:49,090 --> 00:15:51,220 so I think everything is looking good 326 00:15:51,220 --> 00:15:54,080 and working well, and there's a few more we could check. 327 00:15:54,080 --> 00:15:55,960 You could go back to that other query to see the other ones 328 00:15:55,960 --> 00:15:59,310 that were duplicated, but it's pretty clear here that things 329 00:15:59,310 --> 00:16:02,480 are now working correctly, and we've got that bug fixed. 330 00:16:02,480 --> 00:16:03,660 And by the way, if you're not familiar 331 00:16:03,660 --> 00:16:06,100 with any of these artists, entering the artist 332 00:16:06,100 --> 00:16:08,950 and album names into Google, into the Google search engine, 333 00:16:08,950 --> 00:16:09,900 can be a good way to check 334 00:16:09,900 --> 00:16:11,730 that you've got the correct album. 335 00:16:11,730 --> 00:16:14,450 You'll probably struggle to find an exact match with some 336 00:16:14,450 --> 00:16:17,450 of these because it depends on the orchestra and conductor. 337 00:16:17,450 --> 00:16:20,730 For example, I'll just show you one other one, 338 00:16:20,730 --> 00:16:23,150 this one here, Mussorgsky, 339 00:16:23,150 --> 00:16:25,170 Pictures of an Exhibition. 340 00:16:25,170 --> 00:16:26,520 So you might struggle with that particular one 341 00:16:26,520 --> 00:16:29,050 because it depends on the orchestra and conductor. 342 00:16:29,050 --> 00:16:30,770 The results we're getting is different 343 00:16:30,770 --> 00:16:32,670 to the Emerson, Lake and Palmer album, though, 344 00:16:32,670 --> 00:16:34,740 so it's no longer returning the wrong song list, 345 00:16:34,740 --> 00:16:39,090 and the Emerson, Lake and Palmer album I'm talking about, 346 00:16:39,090 --> 00:16:41,100 this one here, you can see 347 00:16:41,100 --> 00:16:43,960 we've clearly got a different set of songs there. 348 00:16:43,960 --> 00:16:45,580 So I think that's a really important lesson 349 00:16:45,580 --> 00:16:48,490 to be taken from this, or your takeaway from this, 350 00:16:48,490 --> 00:16:50,760 and that's to test your programmes thoroughly. 351 00:16:50,760 --> 00:16:53,150 Now, unless you happen to select two matching albums 352 00:16:53,150 --> 00:16:55,470 while testing and notice that they were displaying 353 00:16:55,470 --> 00:16:59,150 the same records, this bug may well have gone unnoticed, 354 00:16:59,150 --> 00:17:02,120 and testing database programmes can be particularly difficult 355 00:17:02,120 --> 00:17:04,770 because the behaviour of a programme is closely tied 356 00:17:04,770 --> 00:17:06,720 to the underlying data. 357 00:17:06,720 --> 00:17:08,650 But running queries like the one we used earlier 358 00:17:08,650 --> 00:17:11,140 in this video is a good idea to identify things 359 00:17:11,140 --> 00:17:13,569 like duplicate columns in the data. 360 00:17:13,569 --> 00:17:15,520 And we should also test the class when there's an artist 361 00:17:15,520 --> 00:17:18,540 with no albums and also an album with no songs listed, 362 00:17:18,540 --> 00:17:20,810 just to make sure it works and doesn't crash. 363 00:17:20,810 --> 00:17:21,670 Now, it does actually work, 364 00:17:21,670 --> 00:17:23,319 but I'll leave you to check that. 365 00:17:23,319 --> 00:17:24,599 All right, so let's finish the video here. 366 00:17:24,599 --> 00:17:25,579 It's got to be quite long, 367 00:17:25,579 --> 00:17:28,380 and I'll see you as always in the next video.