WEBVTT 1 00:00:00.697 --> 00:00:01.799 Now, in this video, we're going to look 2 00:00:01.799 --> 00:00:05.098 at how to instal and use the Database Navigator plugin 3 00:00:05.098 --> 00:00:08.269 for the Community Edition, or Community Editions, 4 00:00:08.269 --> 00:00:10.911 of IntelliJ IDEA, as well as PyCharm. 5 00:00:10.911 --> 00:00:12.861 Now, if you're using the Ultimate Edition, 6 00:00:12.861 --> 00:00:15.057 that comes with the plugin already installed, 7 00:00:15.057 --> 00:00:17.701 and the previous video shows you how to use that. 8 00:00:17.701 --> 00:00:19.119 Now, in the last video, 9 00:00:19.119 --> 00:00:21.880 not counting the Ultimate Edition Database View video, 10 00:00:21.880 --> 00:00:23.506 as well as the 11 00:00:23.506 --> 00:00:25.133 short video showing you, or telling you, 12 00:00:25.133 --> 00:00:26.761 there's an alternative to the Ultimate 13 00:00:26.761 --> 00:00:28.916 and Community editions, 14 00:00:28.916 --> 00:00:30.257 we created a simple class 15 00:00:30.257 --> 00:00:32.332 that can store itself to a database, 16 00:00:32.332 --> 00:00:35.665 and initialise itself from the database, if it exists. 17 00:00:35.665 --> 00:00:38.714 So, we should now have a few records in our Accounts table. 18 00:00:38.714 --> 00:00:39.649 Up until now, 19 00:00:39.649 --> 00:00:41.438 we've used the SQLite command interface 20 00:00:41.438 --> 00:00:43.104 to view our database, 21 00:00:43.104 --> 00:00:45.421 but it's important to know how to do that, 22 00:00:45.421 --> 00:00:47.578 and also how to use the SQL commands, 23 00:00:47.578 --> 00:00:49.085 but now that we're back in IntelliJ, 24 00:00:49.085 --> 00:00:50.795 there's actually an easier way. 25 00:00:50.795 --> 00:00:53.071 Now, with the Community Edition of either 26 00:00:53.071 --> 00:00:55.221 IntelliJ IDEA or PyCharm, 27 00:00:55.221 --> 00:00:58.184 the easy way is the Database Navigator plugin. 28 00:00:58.184 --> 00:01:00.747 So, the first thing we need to do is instal the plugin, 29 00:01:00.747 --> 00:01:03.306 and I prefer to do that from the Welcome screen. 30 00:01:03.306 --> 00:01:06.757 Now, you do have to restart IntelliJ IDEA or PyCharm anyway, 31 00:01:06.757 --> 00:01:08.179 after installing a plugin, 32 00:01:08.179 --> 00:01:10.414 so there's little point having a project open. 33 00:01:10.414 --> 00:01:12.854 So, you can see here, if I can go into my About, 34 00:01:12.854 --> 00:01:15.293 that I'm running the Community Edition, 35 00:01:15.293 --> 00:01:18.387 so make sure that you haven't got a project open. 36 00:01:18.387 --> 00:01:20.626 You can see I'm on the main Welcome screen now, 37 00:01:20.626 --> 00:01:23.103 and we can actually go down here to Configure 38 00:01:23.103 --> 00:01:25.174 and select Plugins. 39 00:01:25.174 --> 00:01:26.675 You can see that 40 00:01:26.675 --> 00:01:29.445 the screen is showing a list of available plugins, 41 00:01:29.445 --> 00:01:31.926 and also shows whether they're enabled or not. 42 00:01:31.926 --> 00:01:33.351 Now, if you've installed other plugins, 43 00:01:33.351 --> 00:01:36.396 they'll also appear here, with an option to uninstall them, 44 00:01:36.396 --> 00:01:40.338 but most of these, though, are installed with the IDE, 45 00:01:40.338 --> 00:01:42.450 and you don't have the option to uninstall them. 46 00:01:42.450 --> 00:01:44.397 But, you can disable them, if you want, 47 00:01:44.397 --> 00:01:46.753 so, as an example, if you don't 48 00:01:46.753 --> 00:01:49.157 use Eclipse, and don't work with anyone who does, 49 00:01:49.157 --> 00:01:50.903 then you could disable the Eclipse Integration 50 00:01:50.903 --> 00:01:52.242 and save a bit of memory. 51 00:01:52.242 --> 00:01:55.576 So, we could come down here to Eclipse Integration, 52 00:01:55.576 --> 00:01:59.482 and we could click on the checkbox, to turn the checkbox off 53 00:01:59.482 --> 00:02:01.150 and save a bit of memory that way. 54 00:02:01.150 --> 00:02:03.833 You won't see the Eclipse plugin, now, in PyCharm, 55 00:02:03.833 --> 00:02:04.972 if you're using that. 56 00:02:04.972 --> 00:02:06.316 Now, if you do disable it, 57 00:02:06.316 --> 00:02:08.388 you won't be able to import Eclipse projects, 58 00:02:08.388 --> 00:02:11.116 so it's a good idea to remember which plugins you disable, 59 00:02:11.116 --> 00:02:13.229 if you do, actually, go ahead and disable them. 60 00:02:13.229 --> 00:02:15.303 But, I won't disable anything at this point. 61 00:02:15.303 --> 00:02:16.643 Instead, what I'm going to do is, 62 00:02:16.643 --> 00:02:19.896 come down here and click on Browse Repositories, 63 00:02:19.896 --> 00:02:22.130 and that lets you see all the available plugins, 64 00:02:22.130 --> 00:02:23.839 so that you can instal others. 65 00:02:23.839 --> 00:02:25.708 Now, there's a lot, so you can see that the list 66 00:02:25.708 --> 00:02:28.107 took a little while to appear for the first time. 67 00:02:28.107 --> 00:02:29.735 Now, we're interested in databases, here, 68 00:02:29.735 --> 00:02:33.902 so we're gonna type in "database" in the search box. 69 00:02:36.274 --> 00:02:38.386 And the Database Navigator's towards the top. 70 00:02:38.386 --> 00:02:40.220 You can see here that it's on the screen now, 71 00:02:40.220 --> 00:02:41.769 so I click on that once, 72 00:02:41.769 --> 00:02:45.552 it shows that there's been 883,000 downloads, 73 00:02:45.552 --> 00:02:47.705 as of the time of this video, 74 00:02:47.705 --> 00:02:51.237 so it's obviously a very, very popular plugin. 75 00:02:51.237 --> 00:02:53.555 So, what we need to do then, is click on Instal, 76 00:02:53.555 --> 00:02:55.057 and that will go ahead and download, 77 00:02:55.057 --> 00:02:56.643 and then instal that plugin, 78 00:02:56.643 --> 00:02:59.164 and then we'll need to restart 79 00:02:59.164 --> 00:03:03.331 IntelliJ or PyCharm, once we've actually done that. 80 00:03:04.616 --> 00:03:05.877 Okay, now that that's downloaded, 81 00:03:05.877 --> 00:03:08.057 we get this option to restart IntelliJ IDEA, 82 00:03:08.057 --> 00:03:10.009 so click on that. 83 00:03:10.009 --> 00:03:11.467 Click on Restart, 84 00:03:11.467 --> 00:03:13.582 and IntelliJ will restart, 85 00:03:13.582 --> 00:03:18.546 and the plugin will be enabled, and we can continue. 86 00:03:18.546 --> 00:03:20.577 All right, so that's the plugin now installed, 87 00:03:20.577 --> 00:03:22.688 and IntelliJ restarted, 88 00:03:22.688 --> 00:03:24.398 so we're gonna open the rollback.py 89 00:03:24.398 --> 00:03:26.955 file in the Transactions project again, 90 00:03:26.955 --> 00:03:28.378 and let's see how to use it. 91 00:03:28.378 --> 00:03:32.545 So, I need to go ahead and open that, so let's do that now. 92 00:03:36.477 --> 00:03:37.394 Okay, Open. 93 00:03:40.341 --> 00:03:41.682 So, here's our project, 94 00:03:41.682 --> 00:03:46.564 and we want to have rollback.py open, as it is now. 95 00:03:46.564 --> 00:03:48.232 So, now that we've got the Database Navigator 96 00:03:48.232 --> 00:03:50.507 or the DB Browser plugin installed, 97 00:03:50.507 --> 00:03:52.948 we need to add it to the display. 98 00:03:52.948 --> 00:03:56.361 So, from the View menu, click on View, then Tools, 99 00:03:56.361 --> 00:03:59.607 Tool Windows, and click on DB Browser. 100 00:03:59.607 --> 00:04:02.493 By default, it'll actually open on the left-hand side, 101 00:04:02.493 --> 00:04:05.176 and I've found, in practise, it doesn't play very well 102 00:04:05.176 --> 00:04:06.641 with the Project pane, 103 00:04:06.641 --> 00:04:09.118 so what I suggest you do is, come up here 104 00:04:09.118 --> 00:04:10.665 and click on the cog, 105 00:04:10.665 --> 00:04:13.434 click on Move to, Move to Right, 106 00:04:13.434 --> 00:04:15.178 to put it on the right-hand side, 107 00:04:15.178 --> 00:04:17.172 and that helps with the Project pane, 108 00:04:17.172 --> 00:04:18.716 it doesn't interfere with that, 109 00:04:18.716 --> 00:04:21.158 but the other advantage is that, 110 00:04:21.158 --> 00:04:22.544 it's now in the same place as the 111 00:04:22.544 --> 00:04:24.985 Ultimate Edition's Database view, 112 00:04:24.985 --> 00:04:26.757 so everything's going to look very similar 113 00:04:26.757 --> 00:04:28.835 as we go through subsequent videos. 114 00:04:28.835 --> 00:04:30.417 So, we need to create a new connexion. 115 00:04:30.417 --> 00:04:32.405 Let's kinda move this over a little bit. 116 00:04:32.405 --> 00:04:33.547 So, to create that new connexion, 117 00:04:33.547 --> 00:04:35.820 I'm gonna click on the plus, here. 118 00:04:35.820 --> 00:04:37.566 Now, we're gonna choose SQLite, 119 00:04:37.566 --> 00:04:39.314 but notice that there are some other options 120 00:04:39.314 --> 00:04:42.317 that it supports: Oracle, that's an Oracle database, 121 00:04:42.317 --> 00:04:44.310 MySQL, which is another database, 122 00:04:44.310 --> 00:04:46.182 but now owned by Oracle, also. 123 00:04:46.182 --> 00:04:49.635 PostgreSQL, and there's also a Custom option. 124 00:04:49.635 --> 00:04:52.135 So, we're gonna choose SQLite, 125 00:04:53.251 --> 00:04:56.386 and that'll open up this window. 126 00:04:56.386 --> 00:04:58.992 I suggest you change the Name, here, to something. 127 00:04:58.992 --> 00:05:01.920 I'm gonna call it Account, like so, 128 00:05:01.920 --> 00:05:03.292 and, the other thing we need to do is, 129 00:05:03.292 --> 00:05:07.111 we need to point it to our SQLite database. 130 00:05:07.111 --> 00:05:10.118 Now, a SQLite connexion only needs the full name 131 00:05:10.118 --> 00:05:11.663 of our database, 132 00:05:11.663 --> 00:05:13.978 and just like the Ultimate Edition plugin, though, 133 00:05:13.978 --> 00:05:16.134 you do need to specify the full path, 134 00:05:16.134 --> 00:05:19.505 even though the file, and that's accounts.sqlite, 135 00:05:19.505 --> 00:05:21.496 is in our working directory. 136 00:05:21.496 --> 00:05:23.732 So, once we click on the files box in here, 137 00:05:23.732 --> 00:05:25.887 under Database files, 138 00:05:25.887 --> 00:05:28.821 notice how, here, the ellipses has now appeared. 139 00:05:28.821 --> 00:05:30.449 I'm gonna click on that, 140 00:05:30.449 --> 00:05:32.930 and I'm gonna navigate to the folder 141 00:05:32.930 --> 00:05:34.597 where my project is, 142 00:05:35.780 --> 00:05:38.665 and then select, from that list, accounts.sqlite 143 00:05:38.665 --> 00:05:41.593 from the list of files, there. 144 00:05:41.593 --> 00:05:44.879 So, we've now got it showing there, in the Database files. 145 00:05:44.879 --> 00:05:47.197 It is also a good idea to use the Test Connexion button, 146 00:05:47.197 --> 00:05:49.349 down here, which I'm gonna click. 147 00:05:49.349 --> 00:05:51.382 That verifies that connexion was successful, 148 00:05:51.382 --> 00:05:53.739 and you can see that we've got a note to that effect, 149 00:05:53.739 --> 00:05:55.982 so I'm gonna click on OK at this point, 150 00:05:55.982 --> 00:05:58.669 and I'm also gonna click on OK again. 151 00:05:58.669 --> 00:05:59.770 Over to the right hand side, 152 00:05:59.770 --> 00:06:02.573 you'll notice that Account is now showing, 153 00:06:02.573 --> 00:06:05.909 the Account name, and then the Account database, 154 00:06:05.909 --> 00:06:08.306 and, we can actually expand this out. 155 00:06:08.306 --> 00:06:10.952 I'm gonna click on that, to click on Schemas, now, 156 00:06:10.952 --> 00:06:14.246 and I'm gonna keep expanding, and expand main, 157 00:06:14.246 --> 00:06:16.233 and click on Tables, and then, eventually, 158 00:06:16.233 --> 00:06:17.857 we get to our tables. 159 00:06:17.857 --> 00:06:20.052 Now, that main, by the way, here, 160 00:06:20.052 --> 00:06:22.045 that's because many client-server databases 161 00:06:22.045 --> 00:06:25.137 allow multiple schemas in the database. 162 00:06:25.137 --> 00:06:28.188 Now, SQLite databases can only have a single schema, 163 00:06:28.188 --> 00:06:30.021 but because the plugin's been designed 164 00:06:30.021 --> 00:06:34.042 to cope with more than just SQLite databases, 165 00:06:34.042 --> 00:06:35.627 it's giving us this option. 166 00:06:35.627 --> 00:06:38.188 So, we can actually ignore that, and we won't be using it. 167 00:06:38.188 --> 00:06:39.487 Now, you'll find that any query 168 00:06:39.487 --> 00:06:41.356 the plugin generates for us, 169 00:06:41.356 --> 00:06:44.233 will prefix table names with main., 170 00:06:44.233 --> 00:06:46.386 but we don't have to do that in our queries. 171 00:06:46.386 --> 00:06:47.724 Because there's only one schema, 172 00:06:47.724 --> 00:06:49.227 we don't need to specify it. 173 00:06:49.227 --> 00:06:50.565 You'll see that in a minute, 174 00:06:50.565 --> 00:06:54.065 when DB Browser creates a suggested filter for us, 175 00:06:54.065 --> 00:06:56.544 but the bottom line is, now, that we've got access to our 176 00:06:56.544 --> 00:06:59.957 tables, from our database in a GUI manner, 177 00:06:59.957 --> 00:07:02.231 which is, obviously, pretty cool. 178 00:07:02.231 --> 00:07:04.709 All right, obviously you can see now that DB Browser's 179 00:07:04.709 --> 00:07:08.334 displaying the details of the connexion in a tree view, 180 00:07:08.334 --> 00:07:09.797 and shows a lot more information than 181 00:07:09.797 --> 00:07:12.078 even the Ultimate plugin does. 182 00:07:12.078 --> 00:07:13.904 In fact, really, the only thing it doesn't do, 183 00:07:13.904 --> 00:07:15.774 that the Ultimate plugin does do, 184 00:07:15.774 --> 00:07:19.152 is check the syntax of your SQL strings. 185 00:07:19.152 --> 00:07:20.534 Now, unless you really need that, 186 00:07:20.534 --> 00:07:21.713 and the additional databases 187 00:07:21.713 --> 00:07:25.168 that the Ultimate plugin supports, 188 00:07:25.168 --> 00:07:27.105 then the DB Browser plugin provides 189 00:07:27.105 --> 00:07:30.072 much more detail about your database. 190 00:07:30.072 --> 00:07:31.703 All right, so I've expanded the Tables entry, 191 00:07:31.703 --> 00:07:32.536 as you can see here, 192 00:07:32.536 --> 00:07:35.901 and we can see our two tables: accounts and transactions. 193 00:07:35.901 --> 00:07:38.953 And, you can actually continue expanding those, if you like, 194 00:07:38.953 --> 00:07:40.983 right down to the columns, 195 00:07:40.983 --> 00:07:43.872 as well as things like constraints and indexes, as well, 196 00:07:43.872 --> 00:07:45.252 and even triggers. 197 00:07:45.252 --> 00:07:46.347 Now, there's a lot more information 198 00:07:46.347 --> 00:07:47.606 than we need at the moment, 199 00:07:47.606 --> 00:07:50.088 but if you do a lot of database work in IntelliJ, 200 00:07:50.088 --> 00:07:51.674 then this is a great plugin that, I think, 201 00:07:51.674 --> 00:07:53.705 will be very beneficial for you. 202 00:07:53.705 --> 00:07:57.076 All right, so let's expand the transactions table, 203 00:07:57.076 --> 00:07:58.743 then Columns, 204 00:07:58.743 --> 00:08:01.434 and then I'm gonna click on time. 205 00:08:01.434 --> 00:08:02.898 Now, down at the bottom, what should have happened is, 206 00:08:02.898 --> 00:08:06.567 you should see a little window pop up, down there, 207 00:08:06.567 --> 00:08:08.887 with details about this particular column, 208 00:08:08.887 --> 00:08:11.002 and if, by chance, that's not showing for you, 209 00:08:11.002 --> 00:08:13.768 come up here and make sure this button is clicked. 210 00:08:13.768 --> 00:08:15.514 This is the Show Object Properties, 211 00:08:15.514 --> 00:08:17.057 so I can hide that, as you can see. 212 00:08:17.057 --> 00:08:19.372 Just click it again to actually enable it. 213 00:08:19.372 --> 00:08:20.309 Now, what is cool here, 214 00:08:20.309 --> 00:08:23.890 is the Data type is correctly showing as a timestamp, 215 00:08:23.890 --> 00:08:26.365 and it also, under Attributes, 216 00:08:26.365 --> 00:08:29.596 has got PK, which stands for Primary Key, 217 00:08:29.596 --> 00:08:33.660 so it's showing that this column is part of our primary key, 218 00:08:33.660 --> 00:08:35.446 which is correct, of course. 219 00:08:35.446 --> 00:08:36.979 That's pretty cool. 220 00:08:36.979 --> 00:08:37.812 The other thing we can do is, 221 00:08:37.812 --> 00:08:41.166 we can double-click on these table names, up here, 222 00:08:41.166 --> 00:08:43.161 to see the data in the table, 223 00:08:43.161 --> 00:08:45.032 and this opens up a new window, as you're about to see, 224 00:08:45.032 --> 00:08:47.997 where you can actually specify a filter for the table, 225 00:08:47.997 --> 00:08:50.727 basically the equivalent to the SQL Where clause. 226 00:08:50.727 --> 00:08:53.243 So, I'm going to view the accounts table, 227 00:08:53.243 --> 00:08:55.642 by just double-clicking it, 228 00:08:55.642 --> 00:08:57.635 and there's our little 229 00:08:57.635 --> 00:09:00.227 DB Navigator - Data Filters screen pops up, 230 00:09:00.227 --> 00:09:02.987 and notice, this is now asking us to 231 00:09:02.987 --> 00:09:05.628 finish off a Where statement, 232 00:09:05.628 --> 00:09:07.418 and the other thing is, just note how it's got 233 00:09:07.418 --> 00:09:10.501 the accounts table prefixed by main., 234 00:09:11.354 --> 00:09:13.937 as I said, you can ignore that when writing your own queries 235 00:09:13.937 --> 00:09:15.117 and it'll work quite nicely 236 00:09:15.117 --> 00:09:16.868 without the main. in front of it. 237 00:09:16.868 --> 00:09:18.534 In our case, we don't want a filter, 238 00:09:18.534 --> 00:09:19.673 we want to see all the records, 239 00:09:19.673 --> 00:09:22.601 so I'm gonna click on No Filter. 240 00:09:22.601 --> 00:09:25.331 We then get a list of names, as you can see there, 241 00:09:25.331 --> 00:09:28.013 as well as respective balances. 242 00:09:28.013 --> 00:09:30.987 By the way, once you've actually specified a filter, 243 00:09:30.987 --> 00:09:33.184 DB Browser actually remembers that, 244 00:09:33.184 --> 00:09:35.099 so in future, you can just open the table, 245 00:09:35.099 --> 00:09:36.564 again, just by double-clicking it, 246 00:09:36.564 --> 00:09:38.719 and it will remember that filter. 247 00:09:38.719 --> 00:09:40.623 As you can see, the 248 00:09:40.623 --> 00:09:43.511 table's opened in its own tab in the IntelliJ editor, 249 00:09:43.511 --> 00:09:45.262 and you can switch between working with the data and 250 00:09:45.262 --> 00:09:47.697 your code, obviously, by just clicking on a different tab, 251 00:09:47.697 --> 00:09:49.363 which, again, is pretty cool. 252 00:09:49.363 --> 00:09:51.263 And, that toolbar has actually got a plus button, 253 00:09:51.263 --> 00:09:53.789 that you can see here, to add new rows. 254 00:09:53.789 --> 00:09:55.497 And also, once a row's selected, 255 00:09:55.497 --> 00:09:57.735 a minus button to delete a row. 256 00:09:57.735 --> 00:10:00.613 You can also double-click in a cell to edit the contents. 257 00:10:00.613 --> 00:10:03.129 So, if I click on the plus now, 258 00:10:03.129 --> 00:10:06.948 I'm gonna add a new entry, I'm gonna type in Michael, 259 00:10:06.948 --> 00:10:09.267 and a balance of 500. 260 00:10:09.267 --> 00:10:10.523 And, what you do need to do is, 261 00:10:10.523 --> 00:10:11.540 if you want to add another entry, 262 00:10:11.540 --> 00:10:13.329 you need to move out of there, 263 00:10:13.329 --> 00:10:14.509 and notice that when I did that, 264 00:10:14.509 --> 00:10:17.231 the add button has now become enabled again, 265 00:10:17.231 --> 00:10:19.758 or the Insert record button has become enabled again, 266 00:10:19.758 --> 00:10:21.913 and I can go ahead and add a new row if I want. 267 00:10:21.913 --> 00:10:24.150 I could've used tab to do the same things. 268 00:10:24.150 --> 00:10:25.574 Now, we need another Terry, 269 00:10:25.574 --> 00:10:27.896 but if I try to add another row with the name Terry 270 00:10:27.896 --> 00:10:29.935 and a balance of zero, we're going to get an error 271 00:10:29.935 --> 00:10:32.170 once we move off the new record, 272 00:10:32.170 --> 00:10:34.688 but notice that, over here to the left, 273 00:10:34.688 --> 00:10:36.890 and on the new Michael record that we entered, 274 00:10:36.890 --> 00:10:39.163 it's got a green plus in the left margin. 275 00:10:39.163 --> 00:10:41.074 That indicates that it's a new row 276 00:10:41.074 --> 00:10:43.802 that hasn't yet been committed to the database. 277 00:10:43.802 --> 00:10:45.754 So, if I actually click on plus here, 278 00:10:45.754 --> 00:10:48.087 and we'll add another Terry, 279 00:10:49.009 --> 00:10:51.047 and the balance of zero, 280 00:10:51.047 --> 00:10:53.276 and notice how it's got this little red asterisk. 281 00:10:53.276 --> 00:10:54.661 Well, that's meant to represent 282 00:10:54.661 --> 00:10:56.729 that it's a new row that's being added. 283 00:10:56.729 --> 00:10:59.085 If I actually move off that record, now, 284 00:10:59.085 --> 00:11:03.106 by just pressing tab, we actually get this error, here. 285 00:11:03.106 --> 00:11:05.789 Couldn't create row in table main.accounts, 286 00:11:05.789 --> 00:11:09.402 SQLITE_CONSTRAINT, column name is not unique. 287 00:11:09.402 --> 00:11:11.673 So, click OK to acknowledge the error. 288 00:11:11.673 --> 00:11:14.236 Now, two buttons, at this point, have become enabled 289 00:11:14.236 --> 00:11:15.375 on the toolbar. 290 00:11:15.375 --> 00:11:17.489 The button with the green arrow, here, 291 00:11:17.489 --> 00:11:20.859 that's used to represent, or to commit the change, 292 00:11:20.859 --> 00:11:22.850 and the tooltip in the status bar will say Commit, 293 00:11:22.850 --> 00:11:24.795 if I hold right over that long enough. 294 00:11:24.795 --> 00:11:27.479 Commit, and I've also got Rollback, 295 00:11:27.479 --> 00:11:29.511 and that's the red arrow next to it. 296 00:11:29.511 --> 00:11:32.438 So, clicking Rollback will remove the last set of changes, 297 00:11:32.438 --> 00:11:34.308 and once the changes have been rolled back, 298 00:11:34.308 --> 00:11:36.262 the buttons are no longer enabled. 299 00:11:36.262 --> 00:11:38.742 Now, we've got an error, so lets rollback the changes. 300 00:11:38.742 --> 00:11:40.896 I'm gonna click on that to go back, 301 00:11:40.896 --> 00:11:43.462 and notice that that it, actually, took both records away, 302 00:11:43.462 --> 00:11:46.839 so the Michael record has been deleted again, as well, 303 00:11:46.839 --> 00:11:50.589 so I'm going to add it again, so add Michael, 304 00:11:51.638 --> 00:11:54.239 with a balance of 500, 305 00:11:54.239 --> 00:11:56.593 and this time I'm gonna add Terry, 306 00:11:56.593 --> 00:12:00.371 but I'm going to use the name TerryG, this time, 307 00:12:00.371 --> 00:12:02.567 and use a balance of zero. 308 00:12:02.567 --> 00:12:04.276 Press tab to move off it. 309 00:12:04.276 --> 00:12:06.463 This time it's been accepted, 310 00:12:06.463 --> 00:12:07.557 and to be consistent, 311 00:12:07.557 --> 00:12:09.674 and also to show you how to change the value 312 00:12:09.674 --> 00:12:11.663 of a column that already exists, 313 00:12:11.663 --> 00:12:13.977 I'm gonna change the first Terry to TerryJ, 314 00:12:13.977 --> 00:12:15.926 so I'm gonna double-click in there, 315 00:12:15.926 --> 00:12:18.509 and change the Terry to TerryJ. 316 00:12:19.831 --> 00:12:22.619 And, notice now, the icon to the left 317 00:12:22.619 --> 00:12:24.447 has been changed to a pencil 318 00:12:24.447 --> 00:12:27.899 to show that we're editing an existing row. 319 00:12:27.899 --> 00:12:30.462 At this point, we can actually click on Commit 320 00:12:30.462 --> 00:12:32.206 to commit all those changes. 321 00:12:32.206 --> 00:12:34.643 They've all been updated, as you can see now. 322 00:12:34.643 --> 00:12:37.938 So, I've now got an account for each member of Monty Python. 323 00:12:37.938 --> 00:12:40.540 Now, if you make changes to the data in your code, 324 00:12:40.540 --> 00:12:43.389 you may have to refresh the data when you view it, 325 00:12:43.389 --> 00:12:45.013 and that's this little button, over here. 326 00:12:45.013 --> 00:12:47.450 Click on Refresh, and that re-reads the values 327 00:12:47.450 --> 00:12:48.630 from the database. 328 00:12:48.630 --> 00:12:51.231 So, in other words, if you run a script 329 00:12:51.231 --> 00:12:53.751 and it makes changes to a particular table, 330 00:12:53.751 --> 00:12:55.624 those changes won't necessarily show here, 331 00:12:55.624 --> 00:12:59.075 until you come back and click on Refresh. 332 00:12:59.075 --> 00:13:00.655 All right, so that's basically IntelliJ's 333 00:13:00.655 --> 00:13:03.582 DB Browser plugin, in a nutshell. 334 00:13:03.582 --> 00:13:04.675 Now, before I end this video, 335 00:13:04.675 --> 00:13:07.070 there's one more thing I want to cover before we move on. 336 00:13:07.070 --> 00:13:08.246 We're gonna be looking at committing 337 00:13:08.246 --> 00:13:11.141 and rolling back database transactions. 338 00:13:11.141 --> 00:13:13.619 But, we've also got a table called transactions 339 00:13:13.619 --> 00:13:16.383 that stores details of financial transactions. 340 00:13:16.383 --> 00:13:18.864 Now, using the same word for both of these things 341 00:13:18.864 --> 00:13:21.747 is gonna get very confusing, very quickly, 342 00:13:21.747 --> 00:13:22.601 so what I'm going to do 343 00:13:22.601 --> 00:13:25.524 is rename our transactions table to history. 344 00:13:25.524 --> 00:13:26.944 Now, renaming tables and columns 345 00:13:26.944 --> 00:13:28.451 is something you may need to do, 346 00:13:28.451 --> 00:13:30.319 which is why I've contrived this excuse 347 00:13:30.319 --> 00:13:31.947 to show you how to do it. 348 00:13:31.947 --> 00:13:33.735 So, the first thing we need to do, 349 00:13:33.735 --> 00:13:35.319 is make a change to the code, 350 00:13:35.319 --> 00:13:38.127 so that we don't go creating a new row for Terry. 351 00:13:38.127 --> 00:13:41.381 So, I'm gonna come back here, to our rollback.py. 352 00:13:41.381 --> 00:13:44.918 I'm just gonna move this Database DB Browser window, 353 00:13:44.918 --> 00:13:45.813 close it a little bit to give us 354 00:13:45.813 --> 00:13:47.439 a bit more screen real estate. 355 00:13:47.439 --> 00:13:50.484 And, I'm gonna come down here, 356 00:13:50.484 --> 00:13:52.148 to this code, here, 357 00:13:52.148 --> 00:13:53.609 and instead of Account{"Terry"}, 358 00:13:53.609 --> 00:13:56.700 we're gonna change that to Account{"TerryJ"}. 359 00:13:56.700 --> 00:13:58.122 We've got Graham and Eric, 360 00:13:58.122 --> 00:13:59.669 as you can see there, being added. 361 00:13:59.669 --> 00:14:02.317 Let's also add the two others that we did 362 00:14:02.317 --> 00:14:06.601 manually, in the account tab a minute ago, 363 00:14:06.601 --> 00:14:08.803 so that we've got that in our code permanently. 364 00:14:08.803 --> 00:14:10.970 So, michael = Account 365 00:14:13.091 --> 00:14:14.091 {"Michael"}, 366 00:14:16.109 --> 00:14:17.547 and then 367 00:14:17.547 --> 00:14:19.297 terryG = Account 368 00:14:21.816 --> 00:14:22.733 {"TerryG"}. 369 00:14:23.847 --> 00:14:25.511 So, basically, adding the code now, 370 00:14:25.511 --> 00:14:27.910 to see that Michael and TerryG 371 00:14:27.910 --> 00:14:29.661 are now read from the database, 372 00:14:29.661 --> 00:14:33.076 but also the Terry line of code, here, on line 53, 373 00:14:33.076 --> 00:14:36.405 we changed to correctly reflect the fact that the record 374 00:14:36.405 --> 00:14:39.744 is now TerryG, in our accounts table. 375 00:14:39.744 --> 00:14:42.411 I'm gonna go ahead and run that. 376 00:14:43.401 --> 00:14:44.743 When we do that, we can see that 377 00:14:44.743 --> 00:14:47.386 we've got all the data showing on the screen. 378 00:14:47.386 --> 00:14:48.932 And, just to confirm that, 379 00:14:48.932 --> 00:14:51.861 we note that, in the case of TerryG, the amount was zero, 380 00:14:51.861 --> 00:14:53.975 but, we've retrieved the record for Michael, 381 00:14:53.975 --> 00:14:56.251 balance on account is 5 dollars, 382 00:14:56.251 --> 00:14:57.388 and that is showing us 383 00:14:57.388 --> 00:14:59.819 that the value really is coming from the database, 384 00:14:59.819 --> 00:15:04.051 because, we didn't attempt to initialise it with a value, 385 00:15:04.051 --> 00:15:05.555 here on line 56, 386 00:15:05.555 --> 00:15:07.464 but, of course, when we entered the accounts, 387 00:15:07.464 --> 00:15:10.187 we put in the value of 500, which of course is 5 dollars. 388 00:15:10.187 --> 00:15:12.789 So, that's working nicely, as you can see. 389 00:15:12.789 --> 00:15:15.102 All right, so now that we've made the change to the code 390 00:15:15.102 --> 00:15:16.486 and that's working fine, 391 00:15:16.486 --> 00:15:19.048 we're going to rename the transactions table. 392 00:15:19.048 --> 00:15:22.340 So, to do that, we have to execute a SQL command. 393 00:15:22.340 --> 00:15:24.782 Unfortunately, there's no way to rename tables 394 00:15:24.782 --> 00:15:28.695 from this tree view, over here to the right in DB Browser. 395 00:15:28.695 --> 00:15:32.078 Now, there is actually an icon to open the SQL console. 396 00:15:32.078 --> 00:15:34.600 We come up here and click on that, 397 00:15:34.600 --> 00:15:35.821 and that opens it. 398 00:15:35.821 --> 00:15:38.219 We want to click on Account. 399 00:15:38.219 --> 00:15:39.070 Now, this opens a window 400 00:15:39.070 --> 00:15:41.221 where we can enter some SQL commands. 401 00:15:41.221 --> 00:15:42.603 So, we want to actually type 402 00:15:42.603 --> 00:15:43.520 ALTER TABLE 403 00:15:45.486 --> 00:15:46.910 transactions, and I can press enter there, 404 00:15:46.910 --> 00:15:49.993 'cause what it suggests will come up. 405 00:15:50.905 --> 00:15:52.822 And, RENAME TO history. 406 00:15:54.345 --> 00:15:56.011 It's gonna be the new name of our table, 407 00:15:56.011 --> 00:15:58.365 and put a semicolon on the end, there, 408 00:15:58.365 --> 00:16:00.685 and I'm gonna type COMMIT on the next line. 409 00:16:00.685 --> 00:16:02.636 Now, there is these green buttons, 410 00:16:02.636 --> 00:16:04.999 but I've found, during recording of this video, 411 00:16:04.999 --> 00:16:07.153 that they can be problematic. 412 00:16:07.153 --> 00:16:10.043 So, what I'm going to do is actually commit this manually, 413 00:16:10.043 --> 00:16:12.149 by executing the COMMIT line, 414 00:16:12.149 --> 00:16:14.299 and I've found that, if I didn't do it this way, 415 00:16:14.299 --> 00:16:15.399 then we got some weird errors 416 00:16:15.399 --> 00:16:17.679 and the renaming didn't work properly. 417 00:16:17.679 --> 00:16:20.281 So, I'm gonna move my cursor to the first part, here, 418 00:16:20.281 --> 00:16:23.493 and then click on the green button, 419 00:16:23.493 --> 00:16:24.428 and you can see, that's come up 420 00:16:24.428 --> 00:16:27.112 and says that it's executed successfully. 421 00:16:27.112 --> 00:16:28.254 And, I'm gonna come down here, 422 00:16:28.254 --> 00:16:29.110 not sure whether it's needed, 423 00:16:29.110 --> 00:16:31.180 but I'm gonna do that and click on that again. 424 00:16:31.180 --> 00:16:32.013 And that won't let me do it, 425 00:16:32.013 --> 00:16:33.174 so hopefully, I can over here now, 426 00:16:33.174 --> 00:16:36.060 click on Tables, and right-click, 427 00:16:36.060 --> 00:16:37.602 and click on Reload. 428 00:16:37.602 --> 00:16:39.064 And, actually it has worked, which is good, 429 00:16:39.064 --> 00:16:40.895 so you can see, there, that history's come up, 430 00:16:40.895 --> 00:16:44.927 the table name has been renamed to history. 431 00:16:44.927 --> 00:16:47.958 Believe me when I say that I had issues with that 432 00:16:47.958 --> 00:16:49.177 during the filming of this video, 433 00:16:49.177 --> 00:16:51.371 so the fact that it's gone through now is good. 434 00:16:51.371 --> 00:16:53.447 So, if you have a problem, 435 00:16:53.447 --> 00:16:55.523 you might have to issue a separate commit, or not. 436 00:16:55.523 --> 00:16:57.310 In this case, I actually got an error 437 00:16:57.310 --> 00:17:00.073 trying to do the commit the second time. 438 00:17:00.073 --> 00:17:01.785 It was also problematic, sometimes, 439 00:17:01.785 --> 00:17:03.982 pressing this green option, and then, 440 00:17:03.982 --> 00:17:06.017 this one as well, so, just keep that in mind, 441 00:17:06.017 --> 00:17:08.129 that you may have some little difficulties. 442 00:17:08.129 --> 00:17:11.670 But, you saw that we got it to work in the end. 443 00:17:11.670 --> 00:17:13.949 And, just to be clear, I also found that the button 444 00:17:13.949 --> 00:17:16.308 in the margin locked up PyCharm on a Mac as well, 445 00:17:16.308 --> 00:17:17.852 and I had to force quit, 446 00:17:17.852 --> 00:17:20.171 but it worked okay in Linux and Windows, so 447 00:17:20.171 --> 00:17:21.105 life's probably too short 448 00:17:21.105 --> 00:17:23.184 to worry about exactly what's happening here. 449 00:17:23.184 --> 00:17:24.812 Just use the button on the toolbar, as I did, 450 00:17:24.812 --> 00:17:26.559 and type those commands in, 451 00:17:26.559 --> 00:17:28.796 knowing that you may get an extra error here, 452 00:17:28.796 --> 00:17:30.057 but you can see over here to the right, 453 00:17:30.057 --> 00:17:33.190 once I right-clicked Tables and selected Reload, 454 00:17:33.190 --> 00:17:37.132 that the table name, clearly, has been updated and renamed, 455 00:17:37.132 --> 00:17:38.756 and we've still got our columns. 456 00:17:38.756 --> 00:17:40.053 Well, actually, this is another thing 457 00:17:40.053 --> 00:17:41.393 that does happen occasionally. 458 00:17:41.393 --> 00:17:43.059 Columns seems to disappear. 459 00:17:43.059 --> 00:17:45.257 You need to right-click and click on Reload, 460 00:17:45.257 --> 00:17:47.173 and then the columns actually come back, as well. 461 00:17:47.173 --> 00:17:49.124 So, you can see that we've still got our columns 462 00:17:49.124 --> 00:17:53.029 actually working, and I can double-click history, 463 00:17:53.029 --> 00:17:54.492 click on No Filter, 464 00:17:54.492 --> 00:17:55.796 and we've got no data in their now, 465 00:17:55.796 --> 00:17:57.708 which would be correct with the history table. 466 00:17:57.708 --> 00:18:00.887 If we go back and just close this off, 467 00:18:00.887 --> 00:18:03.003 and with accounts, I'm gonna refresh. 468 00:18:03.003 --> 00:18:06.430 We've still got the accounts table working correctly. 469 00:18:06.430 --> 00:18:08.913 The last thing I need to do, now, before I finish the video, 470 00:18:08.913 --> 00:18:10.577 is to change our code 471 00:18:10.577 --> 00:18:13.547 to work with the fact that the table's changed. 472 00:18:13.547 --> 00:18:16.513 At the moment, we've got the code, on line 5, 473 00:18:16.513 --> 00:18:18.303 looking to create a table, if it doesn't exist, 474 00:18:18.303 --> 00:18:21.190 called transactions, but of course, we've just renamed that, 475 00:18:21.190 --> 00:18:23.669 so let's rename that to history, 476 00:18:23.669 --> 00:18:25.978 like so, which will fix it up. 477 00:18:25.978 --> 00:18:28.174 All right, so, that's the 478 00:18:28.174 --> 00:18:30.653 confusion around the word transaction removed, 479 00:18:30.653 --> 00:18:32.523 so I'm gonna stop the video here. 480 00:18:32.523 --> 00:18:33.356 In the next video, 481 00:18:33.356 --> 00:18:35.322 we're gonna continue on with our Accounts programme, 482 00:18:35.322 --> 00:18:37.684 and start looking at rolling back transactions. 483 00:18:37.684 --> 00:18:40.184 So, see you in the next video.