 And the last thing I am doing is actually moving data from the existing table where the picture is to this new table right. So I am creating data also. Now if you look at this refactoring right, we took care of structure of putting that data, we took care of maintaining the constraint back to the database like back to the original table, we took care of maintaining a foreign key constraint to the original employee ID that we had and we also took care of moving the data from that original table down to the new table that we created. So split table in some ways is created using multiple refactoring right. So you can achieve bigger refactoring by using a series of smaller refactoring. Just like Martin said in his code refactoring book like you can get to a bigger refactoring by using smaller refactoring in a series of changes. One thing to notice here is we are leaving nothing to chance that data is not moved or like foreign key constraint is not there or primary key is not there. We are doing design the proper way that is supposed to happen. We are just making sure that all of these happen in a same step right. We are not doing this at different times. So later on when I show code I will show you that all four steps here is one migration that you run on the database right. So we are creating the table, the foreign key, the primary key and the data movement all in one go. Because when you deploy this code the code is expecting all of that to be there right. When you run refactoring a major thing that you have to notice or keep an eye on is as the application changes your database has to change in the same step. They cannot change in different steps because when you deploy code the code is expecting that new structure to be there right. If it does not find the structure then you have this weird like table not found, column not found, view not found kind of errors coming around and then that is when you know that your table or your database and code is not moving at the same pace right. So here is a refactoring called as add lookup table and then we have make column nullable I am not going to talk in detail about this but same kind of structure applies to this. A good situation may be like if you are in a green field project like a brand new project that is not legacy does not have any other applications talking to you most likely you do not need the transition layer in the middle right. So you can just drop the column off immediately right there right. So that kind of stuff is totally possible if you are in a green field project. So different kinds of refactoring are available here right. Of course like I said in the beginning without good practices surrounding the refactoring you cannot actually implement these practices or even if you implement these practices you will face a lot of failures that go along with it like if you do not have enough test if you do not have enough test that test the application code as well as the database code when you deploy you may have problems when they do not talk to each other and things like that right. So let us see what those practices are right. One is configuration management. We do not generally tend to configure like put our database stuff in configuration management people like put version control for application code but all the database stuff is managed by the DBA and people do not even care what is happening behind that wall. That is a bad practice to have because then you cannot link the application code to the database code and create a version for both sides right. So that is what we should strive to do like and it also allows for a common code ownership like you want to see what the table structure looks like and at the same time the DBA should be willing to go look in your code to see how you are accessing the tables right. So that is where you will get to see all the SQL that is being fired by the application and things like that. So that is important to have common code ownership. It is also important that you put all of your database artifacts like in the source deposit. If you want to look at the model that someone is creating like urban model or things like that you should have access to it right. You should not have to like ask permission for someone to like give me a like a screenshot of the ER diagram or like if you go to the data teams wall there is like this huge ER diagram printed on screen with 500 tables like that is not user consumable right. So you want to want to have access to those kinds of things. At the same time you want to include like setup and config data like a lot of people have this product list of states list of address types all this kind of stuff. This is all config data you should version control it because if it changes you need to know who change it why change what was the difference and things like that right. So that is important to include in version control you and at the end of the CI cycle just like you create a jar or a war or a DLL or whatever you create you should create something that goes on the database to like a diff script or like a deploy script or whatever that takes you from the previous version to the next version right. So make sure you create that kind of artifact and the last and the most important thing is only deploy what is checked in like don't just go to the production database and make a change right. I can do this but what happens is that change doesn't get then reflected back into dev and then you have this two or three days worth of debugging seeing why this is not working and that kind of stuff right. So only deploy code that is checked in right and this means like database code or any DDL changes that you do anything that you do right. The other concept that really helps in working this way is the concept of sandboxing right. So if you are in this in this red column on the X side right so bunch of developers are working against a single database you make one refactoring immediately rest of the team gets that refactoring even if they want it or don't want it right. Because you have not checked in code yet right their code starts failing because now they're expecting the old table that they had is not there anymore or you have split the table and the original table is not there. All kinds of stuff they are into a world of pain right. So what you want to do is if you are doing some changes you want to have your own schema right just like how you have your own copy of the code base right you're not working on a common code base somewhere everybody has a copy of the own code base then why not have a copy of the own database so you can change it in whatever way you want right. So that you get the freedom to change and at the same time you get the freedom to experiment like you tried something like splitting a table this way that way and it didn't work out you can roll back and start again right. In the other case you don't have that choice if you made the change the change is there everybody else got it then you have to scramble to fix it right and the last practice that is really important is tracking changes like because we are making so many changes it's important that you track changes like I was saying in the before like whatever change or whatever refactoring you do that is a migration script all changes go into one script right and that is called a delta or migration script and migration scripts are a development time activity they are not deployment time activity like generally I have seen like people develop develop develop and now we can release this so when it goes to QA the DBA sits with someone and creates a migration script right that is a deployment time activity very risk prone who is going to test those migration scripts who is going to make sure that the context that was with the developer when the change happened is transferred to the DBA to make the right kind of scripts data migration scripts that go with it all of that is lost right when the change is happening the developer knows the context so it is better that the data people sit with the developer and write that migration script then and there the other advantage that happens with this is because it is version control you can get this change script tested like multiple times through CI right I'll show you how that happens later when I go to court right so that kind of tracking changes really helps you because now you're doing it at development time the other advantage it gives us is when you're deploying you don't need to really think what to deploy because all of that is already done you can deploy whenever you want to so this is a enabling step towards continuous delivery if you are thinking of doing continuous delivery you have to be doing this without this continuous delivery is not going to work right so that what it gives you is the power or the ability to release whenever you want you don't have to like think about when to release right and you can package all of these migration scripts and just like create a artifact normally and the production DBA can just like write a script that will deploy this without any intervention or without any actual manual work that needs to be tested and things like that right the same package scripts or the same migration scripts can be used for n number of versions like you're deploying to UAV doesn't matter you're deploying to QA doesn't matter you're deploying to production doesn't matter or if some customer wants to take a beta release of your new version and wants to deploy it in own environment you can send that also to him doesn't really matter you can deploy the same script anywhere that you want right so that this allows you for various databases to be at different versions like some product some developer pair maybe working on fixing a production bug and they want to be on version 43 right they can take a copy of or they can check out as version 43 deploy it and they have a database immediately that was version 43 and start working right or you may want to have two or three different versions in QA that is one one may be 48 the other may 49 the other may be like 22 whatever right so you can deploy these things as you want just by looking at the version and taking the database artifact as of that version and deploying it so that you don't have to rely on the DBA or the sysops people to get you the backup as of that date and then take the tape back and then install all of that just goes away right so this makes the team much more productive and gives them the freedom to be doing whatever they want at different versions right of course now you're saying there is so much change coming how is this gonna like all sync up together right so assuming I'm assuming you're doing agile already and this is the situation you are in right if you're not doing agile this is the first step you will get to right so where you have a dev team that is doing a build by itself checking into source control there's a continuous integration engine you may be using Jenkins you may be using go you may be using TFS doesn't really matter right and that is talking to the same dev database right that dev database is being managed by your DBA team or your DevOps team where they check changes from you like you send an email or you like go talk to them or you work through whatever ways or you may be create a change request whatever ways you work through them like they are making the change to the dev database when it time when it comes time to deploy that change they are making the change script and deploying it to the production environment or the client test environment or whatever others place that you need to deploy at the same time they are taking the code base and deploying it to the right environments right so this is fraught with risk because the change management is happening at two different levels devs are putting code in the code base like source control and the DBAs are managing change in the database right with this new approach what you should be doing is doing the migration scripts at dev time right we are not saying get rid of the DBAs what we are saying is put them where they can provide the most productivity to the team along with the developers where they are helping write the migration scripts because design is happening when the migration scripts are being written right when the developers are happy with the migration scripts they check in into source control and that's where the migration script comes up right and then once you check in something if you are using CI that checked in thing will trigger a build right so you use the same migration script to trigger the continuous integration engine and you notice that the dev database is no longer dev database it is an integration database it's a different database that you are working against not the same database right and you are applying the same migration scripts that were done by the developer tested locally and checked in like there is no manual intervention here nobody is sitting there and writing a new migration script it's the same migration script that is being applied to the integration database if the build passes here everything is good then you can pack as this stuff if the build fails then you have to fix it right so you are getting faster feedback on your changes even on the database side right once this is successful what you are doing is you are creating an artifact in the artifact you are packaging all the migration scripts together creating a database deployment package and you are packaging all of your code and creating a jar or a war or a dll or exe whatever that you create or a installer or a package doesn't really matter all of them are packaged together right now once it once this happens you know for a fact that when the artifact is picked up from a CI engine both work together very well because I had deployed the same database and my code was running against that with all the unit test the functional test whatever I ran in the CI build right the confidence level that you have on both these things working together is very high right so when you deploy you don't really have to think will these two combinations work together right it's got tested it all the unit test ran your database interface layers got tested all of that so now when you deploy you take these artifacts and deploy the database side package on the database and the code side stuff on the code side like whatever containers you may be running or whatever it is you deploy them and both will work right and since they are a package script the dba just dba or csobs or even devs we have even worked on projects where the application has this migration scripts embedded inside it and on startup it runs the migration scripts basically upgrades database itself and goes right so you don't need to rely on a bunch of other external factors or manual interventions to do this right so of course you are saying ah this is all great now show me right so let's go to code and see what else stuff I have set up come out of presentation mode this is embarrassing like my computer's frozen so give me a second here so what I was going to show like maybe you can catch me after I'm done here because I have not much time here so what I was going to show is like have a setup of Jenkins that is talking to Oracle database running in a virtual machine that is running on my machine like a Windows machine that is running inside my on my box and make code changes using like Java and make a change and check in that change into github right and then the CI engine pulls changes from the github and so that at the end it takes those changes applies those changes and then gives you the change back as an build artifact right during this cycle what you are doing is when you make the change locally you're running it locally yourself like the build script runs locally so you're verifying the change works like the thing you're verifying is are there any syntax errors in the changes you made are they working logically and is my code the way I because the database change the application is also going to change and is my new code working with my new change database right that is important once all of those are done now the next step is to make sure that my unit tests are also running right all the unit test they actually talk to the database maybe some integration test actually talk to the database and things like that are all verified and once you verified you check in that stuff the same thing again runs in CI and creates an artifact so you can see that your changes got tested twice already even for the first build right so you have tested locally because the build you check in only after the build passes and it got run on CI also so you already have two verification and as more builds happen you have the same changes go through the CI verification again and again so you don't have to worry about when I deploy will these changes be good right so the only thing you may need to worry about is like in dev database I may have a small size versus in production I may have millions of rows like the type of change I made in dev will it run as fast enough in production that may be the only thing that you need to worry about and that's where pairing with the dba type people really helps right so unfortunately all right so here's a virtual machine that I have running and here's local Jenkins that I'm running right so you look at this Jenkins I am I have this project called EvoDB that's been running for some time I also got like a last build successful last yesterday night I think right and in this artifacts you will see there's obligation jar and there's a great zip that I'm creating right so let's try to just just build this locally here or even make a change here I have a bunch of code and things like this and so I'm just firing like a dear is is it visible outside right so I'm just running a target what it is gonna do is my database is not up yet but that's fine so what is happening is when you run this target right what happens is it actually talks to the database drops whatever is in my schema and rebuilds the database right this is coming up so that's the tech challenges but that's okay we look at this one change that I have here right so for example this is a change I introduced right where I'm introducing the account type as a table account type as a table right and this account type is we are not only creating the table that creates the account type we are creating a sequence that goes with it we are giving some data like default data for the account type this is the setup that we need to talk about like account type is it checking is it saving says that investment and that kind of stuff that is also embedded in here right so once you check in this kind of change the database actually goes through here and we can even look at here I guess that may help us look at the console output right so that is the kind of thing happening is the CI engine is actually gonna ping the github repo and get all the data is gonna run like a clean this is what I was trying to run like this DB in it it is working against the database remember how we said it will run against different databases the CI instance is running against a CI database and my local stuff here was running against my own user right this promote is my own user so yeah okay now the database is up right so you see this local build that is running that is applying all the changes in the order that they were created and that stuff is running against the database like there is no like fancy man she string matching and that kind of stuff that you're trying to do it's actually running against the database so it's verified right so once you have this running against the database you have other stuff also that goes with it right like you are creating test data you're creating compiling your DB code you're compiling your Java code you're running all the tests that go with it and you are creating a distribution package like you created your jar and you created your upgrade zip right so let's do that build locally here and see what happens right so and all right so when I say all what it's gonna do is recreate my database compile all the code run all the unit test and then create a package locally also right so you are verifying the database interaction in this code setup also right so if you're wondering where do I get this code base I have open source this code base so you can look at it and play with it right so I did that show me part right so once you have this kind of setup done right so the package jar is there and the database deployment package is there now deployment is actually very easy like you just take the package and you run and target or maven target or whatever that you package along with it right and then you can run it wherever you want to you don't have to worry about which version goes where right and the biggest thing it gives you is this boat metaphor like that helps in terms of continuous delivery like if you're going from one shore to the other shore and you have this boat that goes only once in six months lot of people will try to jump on that boat right maybe even drown the boat or capsize the boat because of that load that it takes this is how we do deployment because if you're deploying once in six months once in 12 months lot of business people want their features in that release right and that that creates tension in the development team stress in the development team it also leads to like lots of mistakes lot of stuff is being worked at the same time creates all kinds of confusion so instead of that if this boat used to go from one shore to the other shore every Tuesday for example you have lot more less pressure to do that and a lot more less code to deploy or changes to deploy so you will make less mistakes and you will like kind of even go home and sleep that night right so that is what helps or this kind of practices help in the continuous delivery angle so that's my talk that's my Twitter handle that's the day website I was saying where all of these refactoring are available and that's the GitHub project where you can take the code that I just showed and maybe run it locally so that you get to see how that all of that stuff works together that's my talk if you have questions I'm here till tomorrow evening come grab me I can maybe show code a little bit more better because of technical difficulties here I was not able to show you what I wanted to