1 00:00:00,730 --> 00:00:03,370 This series of videos covers the topic of modifying views. 2 00:00:04,210 --> 00:00:05,390 Now, I have to admit that 3 00:00:05,660 --> 00:00:07,300 the amount of coverage is 4 00:00:07,490 --> 00:00:10,070 a bit disproportionate compared to defining and using views. 5 00:00:10,820 --> 00:00:12,380 Commonly, people only define 6 00:00:12,860 --> 00:00:13,740 views and use them and don't 7 00:00:14,040 --> 00:00:14,950 try to modify them in applications, 8 00:00:15,990 --> 00:00:17,320 but when views are modified 9 00:00:18,100 --> 00:00:19,120 the issues become a bit tricky. 10 00:00:19,750 --> 00:00:20,960 So, it is important to cover the topic. 11 00:00:21,790 --> 00:00:23,450 First, a reminder of why people use views. 12 00:00:24,060 --> 00:00:27,040 They use them to hide data from some users, so for authorization purposes 13 00:00:28,140 --> 00:00:29,030 they make some queries easier, 14 00:00:29,490 --> 00:00:30,760 they help you modularize 15 00:00:31,590 --> 00:00:33,690 your database applications, and, as 16 00:00:33,800 --> 00:00:35,270 I've said several times, our 17 00:00:35,350 --> 00:00:37,590 real applications do use lots and lots of views. 18 00:00:38,170 --> 00:00:39,560 Now, querying views is 19 00:00:39,860 --> 00:00:41,720 relatively straight forward, as we've seen. 20 00:00:42,480 --> 00:00:43,420 Once a view has been 21 00:00:43,730 --> 00:00:44,960 defined and given a name, say 22 00:00:45,140 --> 00:00:46,630 V, then, in our 23 00:00:46,810 --> 00:00:47,980 queries over the database, we 24 00:00:48,070 --> 00:00:50,030 can reference V as if it were a regular table. 25 00:00:51,160 --> 00:00:52,370 What happens underneath is that 26 00:00:52,560 --> 00:00:53,750 when we reference V in a 27 00:00:54,010 --> 00:00:55,680 query, it's rewritten to use 28 00:00:56,180 --> 00:00:57,600 the tables over which V 29 00:00:57,750 --> 00:00:59,380 was defined, and we'll call those the base tables. 30 00:01:00,110 --> 00:01:02,790 Those can actually be regular tables or they can be other views. 31 00:01:04,150 --> 00:01:05,470 Now, what about modifying views? 32 00:01:06,120 --> 00:01:06,890 Once a view has been defined, 33 00:01:07,290 --> 00:01:09,840 can we modify it just like it's any table in the database? 34 00:01:11,040 --> 00:01:12,370 Well, in one way that 35 00:01:12,580 --> 00:01:13,670 doesn't make sense because V 36 00:01:14,070 --> 00:01:15,340 isn't stored, so it's not 37 00:01:15,520 --> 00:01:16,430 a table that we can go in 38 00:01:16,740 --> 00:01:18,470 and update, insert tupples, 39 00:01:18,830 --> 00:01:21,120 modify tupples, or delete tupples. 40 00:01:21,330 --> 00:01:22,250 But, in another way, it absolutely 41 00:01:22,820 --> 00:01:24,090 has to make sense, because 42 00:01:24,310 --> 00:01:25,530 the idea of views is that 43 00:01:25,740 --> 00:01:27,420 some users or application developers 44 00:01:28,580 --> 00:01:30,270 are seeing those views as their 45 00:01:30,510 --> 00:01:31,970 only view of the database. 46 00:01:32,830 --> 00:01:34,150 We saw that three-level 47 00:01:34,660 --> 00:01:35,920 idea of databases. 48 00:01:36,230 --> 00:01:37,640 We have the physical layer, the 49 00:01:37,770 --> 00:01:39,190 disc, the conceptual layer, the 50 00:01:39,290 --> 00:01:40,210 relation, and then the logical 51 00:01:40,800 --> 00:01:41,990 layer which again, some applications 52 00:01:42,700 --> 00:01:44,260 see as their access to the database. 53 00:01:45,420 --> 00:01:48,090 Well, our solution is actually parallel to what we do with queries. 54 00:01:49,120 --> 00:01:50,810 When we have a modification command 55 00:01:51,230 --> 00:01:52,750 that involves a view V, we'll 56 00:01:53,220 --> 00:01:54,840 rewrite that modification command to 57 00:01:54,950 --> 00:01:56,460 modify instead the base 58 00:01:56,910 --> 00:01:58,140 tables over which V is defined. 59 00:01:59,160 --> 00:02:00,310 If those base tables happen to 60 00:02:00,370 --> 00:02:01,400 be views themselves that we have 61 00:02:01,500 --> 00:02:03,510 a recursive process of rewriting 62 00:02:04,020 --> 00:02:06,250 those modifications to further go 63 00:02:06,410 --> 00:02:07,300 down to, until we get 64 00:02:07,540 --> 00:02:08,450 to the base tables that are actually 65 00:02:08,830 --> 00:02:09,950 the tables stored in the database. 66 00:02:10,750 --> 00:02:12,210 So maybe this is no big deal. 67 00:02:12,550 --> 00:02:13,850 We saw in our demo that 68 00:02:14,100 --> 00:02:15,420 modifying queries that reference 69 00:02:15,870 --> 00:02:17,820 views into queries that 70 00:02:17,990 --> 00:02:19,710 reference the base tables is 71 00:02:19,890 --> 00:02:21,260 a relatively straight-forward process. 72 00:02:22,260 --> 00:02:22,800 Well, I am going to say 73 00:02:23,050 --> 00:02:24,140 right up front, it's not quite 74 00:02:24,530 --> 00:02:26,070 as straight-forward when we are talking about modifications. 75 00:02:27,790 --> 00:02:30,310 So let's draw a picture to see what's going on. 76 00:02:30,590 --> 00:02:31,550 Let's say we have our 77 00:02:31,690 --> 00:02:33,470 view V here, and V 78 00:02:33,750 --> 00:02:35,310 is defined based on a 79 00:02:35,610 --> 00:02:37,420 query over its base tables. 80 00:02:37,830 --> 00:02:40,660 And for now, let's just assume that those are stored actual relations. 81 00:02:41,210 --> 00:02:43,740 So it's defined over relations R1 to RN. 82 00:02:43,750 --> 00:02:46,960 Now someone comes along 83 00:02:47,690 --> 00:02:49,230 and they want to modify V. 84 00:02:49,570 --> 00:02:50,730 Now V is just a logical 85 00:02:51,020 --> 00:02:52,210 concept but, the user 86 00:02:52,840 --> 00:02:53,890 thinks of V as a table, 87 00:02:54,490 --> 00:02:55,780 so they write a modification 88 00:02:56,190 --> 00:02:57,420 command so that would be say, 89 00:02:57,840 --> 00:03:00,260 insert, delete, or update command using SQL language. 90 00:03:01,090 --> 00:03:02,490 And they're imagining that V is 91 00:03:02,620 --> 00:03:03,880 a stored table, so they're 92 00:03:04,090 --> 00:03:05,090 imagining that the result 93 00:03:05,590 --> 00:03:07,070 of that modification command is 94 00:03:07,290 --> 00:03:09,160 going to be a new table V prime. 95 00:03:10,440 --> 00:03:11,910 What needs to happen 96 00:03:12,150 --> 00:03:13,280 is down here at the 97 00:03:13,380 --> 00:03:15,690 bottom, that modification, that 98 00:03:15,840 --> 00:03:17,290 imaginary modification to V, 99 00:03:17,930 --> 00:03:19,280 has to be translated to actual 100 00:03:19,620 --> 00:03:21,210 modifications on the base 101 00:03:21,510 --> 00:03:23,190 tables R1 through RN. 102 00:03:24,180 --> 00:03:25,730 So now, we modify one or 103 00:03:25,770 --> 00:03:26,880 more of the base tables to 104 00:03:27,530 --> 00:03:30,130 R1 prime through RN prime. 105 00:03:31,430 --> 00:03:32,880 And now the idea is 106 00:03:33,300 --> 00:03:34,860 that our imaginary V prime 107 00:03:35,370 --> 00:03:37,300 then is the same query. 108 00:03:37,620 --> 00:03:38,770 It's the result of this same 109 00:03:39,120 --> 00:03:42,080 query, V's definition over the 110 00:03:42,200 --> 00:03:43,650 new value, the new R1 111 00:03:44,280 --> 00:03:46,890 prime through RN prime, the updated base tables. 112 00:03:48,280 --> 00:03:49,210 So, if we can always figure 113 00:03:49,550 --> 00:03:51,330 out how to translate this modification 114 00:03:52,150 --> 00:03:53,630 up here into the modifications 115 00:03:54,480 --> 00:03:56,260 down here so that the 116 00:03:56,350 --> 00:03:57,910 square diagram holds so that the 117 00:03:58,090 --> 00:04:00,400 resulting modifications here give 118 00:04:00,720 --> 00:04:02,090 us the effect we wanted upstairs, 119 00:04:03,200 --> 00:04:03,930 then, we're in good shape. 120 00:04:04,380 --> 00:04:05,600 So, the question is can we 121 00:04:05,780 --> 00:04:07,260 always perform this translation, 122 00:04:07,840 --> 00:04:09,940 the modifications, so the square diagram holds. 123 00:04:10,420 --> 00:04:12,420 And the answer is, actually 124 00:04:13,340 --> 00:04:14,010 usually, yes. 125 00:04:14,370 --> 00:04:15,870 Usually there is a translation 126 00:04:16,120 --> 00:04:17,410 that works for us, 127 00:04:17,540 --> 00:04:19,700 and we'll see some examples of that in our demos. 128 00:04:20,880 --> 00:04:22,700 The problem actually is that 129 00:04:22,970 --> 00:04:24,920 there's often many such translations, 130 00:04:25,150 --> 00:04:28,840 and so we don't know actually which one the user intended. 131 00:04:30,260 --> 00:04:32,290 So let me give an extremely simple example. 132 00:04:33,000 --> 00:04:34,300 Let's suppose that our relation, 133 00:04:35,030 --> 00:04:36,780 R, has two attributes, 134 00:04:37,290 --> 00:04:38,510 A and B, and our 135 00:04:38,660 --> 00:04:40,180 view, V, is defined as 136 00:04:40,350 --> 00:04:42,460 the projection on A of 137 00:04:42,620 --> 00:04:44,480 R. Let's say that 138 00:04:44,650 --> 00:04:46,020 our current contents of relation 139 00:04:46,680 --> 00:04:48,980 are just one tuple to tuple 1-2. 140 00:04:49,320 --> 00:04:50,400 In that case, the current contents 141 00:04:51,150 --> 00:04:53,470 of view V are just the tuple 1. 142 00:04:54,230 --> 00:04:55,420 Now let's say the user comes 143 00:04:55,700 --> 00:04:56,800 along, it's a user who is 144 00:04:57,090 --> 00:04:58,310 operating on the database through view 145 00:04:58,600 --> 00:04:59,930 V, and they say insert 146 00:05:00,640 --> 00:05:03,160 into view V please the tuple three. 147 00:05:04,300 --> 00:05:05,760 So we need to translate that 148 00:05:06,150 --> 00:05:07,810 insertion, which is up here, 149 00:05:08,440 --> 00:05:10,300 into insertion or some 150 00:05:10,700 --> 00:05:12,090 modification on the base 151 00:05:12,370 --> 00:05:13,490 tables so that the 152 00:05:13,570 --> 00:05:14,760 view will, when we're done, 153 00:05:15,310 --> 00:05:16,840 contain the tuples one and three. 154 00:05:17,710 --> 00:05:19,610 Well, we can certainly do that. 155 00:05:20,150 --> 00:05:21,890 The problem is, what exactly do we insert? 156 00:05:22,390 --> 00:05:25,120 We could insert for example 3-1. 157 00:05:25,610 --> 00:05:27,550 We could insert 3-2 and so on. 158 00:05:28,140 --> 00:05:29,340 So there's actually an infinite 159 00:05:29,570 --> 00:05:31,300 number of translations that will 160 00:05:31,640 --> 00:05:33,380 create the tuple three in the view. 161 00:05:33,890 --> 00:05:35,240 Here's an even more extreme example. 162 00:05:35,970 --> 00:05:36,990 Let's suppose we have a 163 00:05:37,380 --> 00:05:38,760 relation with one attribute 164 00:05:39,330 --> 00:05:40,800 and our view be 165 00:05:41,180 --> 00:05:42,940 the average value of 166 00:05:43,070 --> 00:05:44,270 that. So if, for example, 167 00:05:44,890 --> 00:05:46,160 our relation has the values 168 00:05:46,830 --> 00:05:51,380 135, then the average at this point would be three. 169 00:05:52,400 --> 00:05:53,430 Now let's say that the user 170 00:05:53,610 --> 00:05:55,910 comes along and says let me update that average. 171 00:05:56,380 --> 00:05:58,400 I'm gonna set that average to be seven. 172 00:05:59,420 --> 00:06:00,680 Well how do we update the 173 00:06:00,770 --> 00:06:03,310 base data so that its average is now seven. 174 00:06:04,060 --> 00:06:05,010 Well, as you can imagine, there 175 00:06:05,190 --> 00:06:06,650 are many, many, many, actually 176 00:06:07,260 --> 00:06:08,590 an infinite number, of ways to 177 00:06:08,790 --> 00:06:11,640 update the base data so that the view average would now be seven. 178 00:06:12,290 --> 00:06:13,890 So that's the crux of the problem. 179 00:06:14,250 --> 00:06:15,610 How do we decide which modifications 180 00:06:16,530 --> 00:06:17,640 to make to the base tables 181 00:06:18,340 --> 00:06:20,620 so that we get the desired modification to the view? 182 00:06:21,380 --> 00:06:22,510 Correctness is not so 183 00:06:22,690 --> 00:06:23,880 hard to achieve but resolving 184 00:06:24,540 --> 00:06:25,600 ambiguity can be. 185 00:06:26,810 --> 00:06:28,240 Existing systems have actually taken 186 00:06:28,890 --> 00:06:30,570 a fairly different approach as to this problem. 187 00:06:31,280 --> 00:06:32,230 Again, to specify the problem 188 00:06:32,620 --> 00:06:34,860 we have modifications specified on 189 00:06:35,100 --> 00:06:36,440 a view V. We need to 190 00:06:36,640 --> 00:06:38,140 rewrite those to modify the 191 00:06:38,220 --> 00:06:39,600 base tables so that when 192 00:06:40,060 --> 00:06:41,190 we have our view of 193 00:06:41,480 --> 00:06:42,680 over new base tables that 194 00:06:42,910 --> 00:06:45,090 reflects the desired new state of the view. 195 00:06:46,210 --> 00:06:47,880 One approach is that the 196 00:06:48,130 --> 00:06:51,250 view creator actually specifies that rewriting process. 197 00:06:52,220 --> 00:06:54,050 So they will say, you know, when somebody 198 00:06:54,400 --> 00:06:55,400 tries to insert into the view, 199 00:06:55,650 --> 00:06:56,420 here's what we need to do 200 00:06:56,480 --> 00:06:57,380 on the base tables, if they 201 00:06:57,540 --> 00:07:00,030 try to delete here's what we do, and update here's what we do. 202 00:07:00,360 --> 00:07:01,600 So the positive of this 203 00:07:01,690 --> 00:07:02,660 approach is that all modifications 204 00:07:03,470 --> 00:07:05,080 can be handled, because the 205 00:07:05,200 --> 00:07:06,290 view creator is going to specify 206 00:07:06,780 --> 00:07:08,830 exactly what happens in the case of those modifications. 207 00:07:09,720 --> 00:07:11,260 The downside is that there's 208 00:07:11,450 --> 00:07:13,110 no guarantee of correctness, meaning there's 209 00:07:13,510 --> 00:07:14,620 no guarantee that that square 210 00:07:15,070 --> 00:07:16,520 diagram is adhered to, and 211 00:07:16,720 --> 00:07:17,850 we'll see examples in our 212 00:07:17,960 --> 00:07:18,830 demo of where it's not, 213 00:07:19,080 --> 00:07:20,870 or even that the translations are meaningful. 214 00:07:21,560 --> 00:07:22,660 So we're relying on the view 215 00:07:22,900 --> 00:07:24,440 creator to create correct 216 00:07:24,840 --> 00:07:26,210 and meaningful translations explicitly. 217 00:07:27,880 --> 00:07:29,280 The second approach is to 218 00:07:29,450 --> 00:07:30,600 restrict the views and 219 00:07:30,780 --> 00:07:31,980 modifications that are allowed 220 00:07:32,710 --> 00:07:34,170 so that the system can automatically 221 00:07:34,520 --> 00:07:37,030 perform the translation into modifications 222 00:07:38,080 --> 00:07:39,540 on the base tables that are 223 00:07:39,680 --> 00:07:41,950 correct, meaningful and and unambiguous. 224 00:07:43,190 --> 00:07:44,350 So the plus now, of 225 00:07:44,460 --> 00:07:45,580 course, is there's no user intervention 226 00:07:46,220 --> 00:07:47,380 and the result is going to be correct. 227 00:07:48,240 --> 00:07:49,280 The downside is that the 228 00:07:49,370 --> 00:07:50,580 restrictions on the view 229 00:07:51,010 --> 00:07:52,840 and the modification combinations are 230 00:07:53,320 --> 00:07:54,910 fairly significant, as we'll see. 231 00:07:55,980 --> 00:07:57,570 So, the first approach is actually 232 00:07:57,850 --> 00:07:59,440 enabled by a type of trigger 233 00:07:59,860 --> 00:08:01,530 that's known as, "instead of." 234 00:08:02,020 --> 00:08:03,370 I alluded to those triggers 235 00:08:03,830 --> 00:08:05,020 briefly in the trigger video 236 00:08:05,400 --> 00:08:07,040 but here is where we are actually going to see them in action. 237 00:08:08,870 --> 00:08:11,070 In postgres, there's a concept called "Rules," that's very similar. 238 00:08:12,230 --> 00:08:13,370 The second approach is actually 239 00:08:13,410 --> 00:08:14,540 the one that's adopted 240 00:08:15,030 --> 00:08:16,750 by the SQL Standard, and the 241 00:08:17,070 --> 00:08:18,680 SQL Standard gives a very 242 00:08:19,160 --> 00:08:22,340 rigorous limitations on what views can be modified. 243 00:08:23,780 --> 00:08:25,170 Systems vary in what they implement. 244 00:08:25,580 --> 00:08:26,670 Most of them are actually a 245 00:08:26,770 --> 00:08:28,670 little bit more flexible than the 246 00:08:28,740 --> 00:08:31,310 standard, and we'll see that in upcoming demos as well.