 talk is Legacy Admin. So thank you guys for coming. I know you had many other option. So thanks for coming to this talk, and I hope it'll be informative. And it's about InspectDB, which is a Django command that helps you reverse engineer a model from an existing database. Once again, my name is Victor Christopher Cabral, but I go by my needle name Chris. When I was practicing this, I ran a little long, so I'm going to speak kind of fast to try and get as much information in as I can. This is me. There's professional Chris and then regular Chris. So if you want to come talk to me afterwards, you know, you'll talk to regular Chris, not professional Chris. But right now it's a professional Chris. So some themes for the talk that I wanted to give are don't write code unless you have to. And my name is Chris, not Victor. That'll come up. And if you're doing something hard with Django, then you're probably doing it wrong. And this is something I realized when I started using Django initially. When I created a custom command to populate initial data, and then I realized that there were fixtures. So this happens a lot. When you like, when you start to work on something, you don't read all the documentation or you see some cool part of it that you want to do and don't realize that there's other things. So my advice is, you know, to spend, you know, come to Django con, go, go on IRC, go on forums, and there's a lot of rich information. There's flash r slash Django. So it's, and there's a lot of like custom apps, templates, and they all have like their suggested ways of doing things. So Inspector V is one way that you save a lot of time if you're looking working with a legacy system. So Inspector V reads tables and columns not rows and our database and produces a model stop pie to standard out. So you don't have to manually write this code. And you could, you could, you know, create a class that inherits from model stop model and give it an explicit TV TV data like name. And you could go through every table in your database. But this just jump starts the process for you. And it's not perfect. So we'll go over why. What will you have to do afterwards? But it works pretty well. And when when you need this, and two scenarios have come up when I've needed this, I needed a, I have legacy, legacy system legacy database, and I just wanted an admin interface to it. So foreign keys are great. You know, primary key constraints are great. There's, you know, all these constraints that my SQL comes with. But in reality, if you have like an additional validation layer to legacy system that you want to keep in a clean state, you can, you know, reverse engineer model out a couple of validations, and you'll have this slick admin interface that Django comes with for free. And the other use case that I've come up with is creating management commands. So when you create management commands for going through a bunch of rows in the database and making sure that one field is less than another field, or you want to find all rows that have, you know, certain value, the ORM comes in handy for doing these things. And writing custom SQL is sometimes hard, particularly if like, you know, it's not designed to be a programming language. It's designed to do specific things in SQL. So that's another reason why this has come in handy in the past. So I've kind of created a fictitious scenario of why I'm doing this demo. And I have a legacy system with a legacy database, and I can't touch the code for whatever reason. And I need to be able to manipulate the data but still keep it in a clean state. So why can't you edit the source? There's a lot of reasons why you wouldn't be able to edit the source. You don't have the source. It's a third-party product. It's written in Rails. Or, you know, the source is written so poorly it's impossible to understand. So at the end of this demo, I want to be able to have an admin interface that, with as little work as possible, I want to be able to have my basic CRUD operations. And I want to be able to leave the existing database intact more or less. And we'll talk about how we break that and when we have to break that. And different ways to get around it. But I more or less want that legacy system that's interacting with this code to be the same after this. So, straight from the docs, inspect database, introspects the database, and whatever is pointed. So the first thing you'll notice here is that whatever your settings name is pointed to, that's what it's going to introspect. And it's also, in this statement, doesn't seem like it says a lot, but it actually says a lot. The script will inspect the database and create a model for each table. So something that you don't think about is that you'll often have more tables than models because of many to many relationships. And introspection doesn't understand that concept. And also the most important part of this is this feature is meant to be a shortcut, not as definitive model generation. So I created a fictitious model. So it's pretty basic. There's a user table. And I made a lot of egregious errors. Don't blame me. I designed this to be terrible on purpose so that we could understand what would happen if you designed a database that's terrible. Because you have to work with databases that are terrible. And when you're introspecting them, they're not going to be perfect. So I have a user table. It has one primary key. It's auto-incremented. It's not null. And it has a username. And then, of course, it has a plain text password field. We have a company table. It has one primary key. It's auto-incremented. It's got a name. And it's self-referential. So a company can have a parent company. So it's an interesting corner case. And then, this is what I was talking about before. A user can belong to many companies. And a company can have many users. And they're related by this. The user has company. Many to many table. So this relationship, if we're in Django, you'd have a user model that has a many to many relationship with a company or vice versa. But there's really no way of Django introspection to know this. So we're going to see what this actually produces in a second. And another thing that's terrible about this, in terms of Django, at least, is that it has two primary keys. So it has composite primary keys, which is going to be a problem for us, and we'll see why. But Django basically assumes that you have every single model. It assumes that you have one ID field that has primary key. And this doesn't fit that pattern. And then profile, just when you thought it couldn't get any worse, has a triple composite key. And there's a roll here. So roll is meant to be a foreign key to this table roll. But this is a VAR car, and the name here is supposed to match the roll in here. So it's like completely worse database design you can imagine. There can be so many problems with this. Absurd anomalies, deletion anomalies, like you name it. So that's my shitty model. And then just for fun, if we have time at the end, I'm going to go over this table because I created the worst field names possible just to see what they would produce. So the source of InspectDB, this is the source, can you, it's good size. OK, so there's two primary loops. The first loop uses connection.interspection table names. So connection.interspection is a connection-specific introspection function to get the table names. So depending on what database you're using, it'll use a different command to get back that list of table names. But then once it has that list of table names, it's agnostic. The rest of this code is agnostic to the type of database you have. So I looked at the source code for the different introspection definitions. So MySQL, Oracle, and Postgres all have them. And if you look at the documentation, it says MySQL and Postgres are pretty supposed to be first class citizens. And Oracle has the same introspection functions methods defined to introspect a database. So I didn't get a chance to test Oracle, but I don't see any reason why it wouldn't work. And I was looking online, and it seems like a lot of people have gotten to work. So maybe I can look up that later. And we can talk about that. So once you get a table name at that first primary loop, the secondary part of the loop or inside that loop gets the relationships, gets the indexes, and then it has a secondary loop that loops through all the columns. And at the end of this, I know I didn't do a good job of this screenshot. But at the end of this, it yields an output of the column name to standard out. So that's what we're going to use now. So this was a little quiz I made. These are the introspection specific commands that Django uses to get the table names. Somebody equals to show tables, select tables for Oracle. And something you'll notice about these is each of these commands is not guaranteed to come in any particular order. So show tables just gives you a list of the tables. It doesn't give you a list of the tables sorted by when they were created. It doesn't give you a list of the tables sorted by the name. And all of these are the same. So when it goes through that loop, it's basically going to get, there's just no way to guarantee what order it's in. So this is what we talked about before. We used the connection introspection to get the table names, the relationships, and the indexes, and the columns. But then we were kind of agnostic at that next layer to generate the models once we have all that information from our connection. And this is the many to many fail. Like I said, this should map with a many to many on either the user, the company field, depending. But this maps as three individual models. So let's take a look at what would happen if we did this. So Python managed up pi DB shell. So I created this in my SQL. So we'll take a look at this in my SQL. So these are the six tables we started with. And we haven't synced our database. So we don't have any of the Django specific tables. So the question is, do we want to sync our database, or do database introspection first? So database introspection looks at the database and creates a model from it. SyncDB creates, or syncs our database to the correct point that we're at right now, or whatever we have right now. So if we run our sync database first, we're going to create tables that Django needs to log in and hold our users. But then as we run and inspect database after that, those tables will also be in our database. And they will be introspective as well. And that's not really what we want, because those models are already defined somewhere else. So we're going to run inspect DB first. So like I said, it goes directly to standard out, which is not very useful. But you can take a look at it. So it tells you what to do, which is nice. So I don't have to be here giving you a talk. You can just read this. You rearrange out the models. You make sure that I think everyone has a primary key. And if you want, you can remove managed false. It doesn't really say what that's going to do, besides it's going to allow Django to have a lifecycle. And you're allowed to rename the models, but you're not allowed to rename the DB tables, which is like the meta option to explicitly state what the table name is to override the convention. And then there's this line, which I'm sure it won't be important. You'll have to insert the output of Django Admin SQL custom app name into your database, whatever that is. So let's go ahead and put this to. So I created a map for this. And let's see what we produce. This is the same thing, but we're just in a file now. And now we can run our sync DB. So the first problem that we're going to run into is related name collision. So let's add a related name to one of the namespace collisions. Let's see. So looking at our model, user has company user user. And by the way, MySQL Admin generated these field names, so I'm sorry that they're terrible. And I was too lazy to change them. But it has two foreign keys to user has company. And the problem with that is that the namespace will conflict, so the accessors to get the results that will be the same when it auto generates it. So added related name to one of them. And now let's try and sync DB. OK, so we can sync our database now. So all we had to do was add a related name, and it told us to do that. So that was easy enough. Here's my email if you want to send me something. All right, so we've run our sync database, and now we can run our server. So it worked now. It's running. So let's go to the Admin. Let's log in. So I added something to auto register all the models so we can look at them right away. We can look at our company and create a new company. So this is weird. There's a company ID, and I normally don't have to enter in my own primary keys. So let's look at what went wrong. And this is a common thing. The integer field has a primary key, but it's an autofield. And I know that it's an autofield because I created the data model. And company has auto increment, but I can somehow edit the auto-incremented value, which doesn't really make sense. So to fix this, we can add an autofield. All right, so that field disappears. Now it'll be auto-incremented in the background. And I'm going to create a company in my schedule. I'm going to save it just because that annoys me. Sorry for my use of lambdas. Now let's add a user. Also very annoying. So user name is going to be Chris. And my password is going to be password is taco. OK, so those were the two easy tables to map. They had one primary key. They were auto-incremented. It messed up on identifying that it's an auto increment field, but that was easy enough to fix. And even if we didn't, we could just enter a value there. The next thing that's going to come up is these composite primary keys. So we don't have a primary key listed here. So there's multiple ways to approach this. So related name, we talked about that. Do I normally have to add my primary keys? No. So Django hates composite primary keys. So there's a way to fix this. You can drop the primary keys and keep the foreign key constraints. And you can add a new field called ID. And Django's model assumes that there is an ID field anyways. And you can make that not null primary key an auto increment. So you're adding this extra field to each table that has a composite key. And you're just reducing that composite key to this extra field now. So I did not memorize how to do that. So let's drop in the DB shell. So for the two tables that have composite keys, I'm adding a column ID into primary key auto increment. And before I did that, I dropped the primary keys that already existed, which were those composite primary keys. And I'm trying to do this quickly. And for the other table for this user has company, which is the same thing, there was some of my SQL bug that I had to get around by dropping the foreign keys. So I can re-enable those afterwards. But for the purpose of this demo, it doesn't need to be. OK, so I have a company. I have a user. And now I want to put that user in that company with that many to many relationship. Save it. Come back. And I'm going to create a role. And I know I want this role to be worker B. Now inside of my profile, if I want to create a new profile, I'm going to give it an ID. And it's going to be worker B. But I'm going to misspell B. So we wanted this role to be consistent with the roles even though there's not a foreign key relationship. And it's not going to allow us to do that right now because there's no validation on the role field. So that's not good. So we're going to check and see what we can do about that. So in our profiles, I'm going to add a validation to ensure that that happens. And I'm going to borrow most of this from here. Got to raise the validation error. So we're going to import validation error. We're going to import our roles. And if the text for the profile role does not match something that we have in our value list for the names, then we will raise a validation error. So we have one profile that's in a bad state. I'm going to add another one that's supposedly in a good state, worker B. So worker B, at first, I'm going to misspell it. So worker B is now being validated. And it says role is not found. So we can update it if we change it back to worker B, which is in our role stable. So that's a basic way to do validation. If something's messed up, you can still, you know, in Python, run some type of check. OK. And I also created a management command to kind of demonstrate what you could do. You know, this management command isn't specific to running a website. So if you just wanted to find all the places where you're interacting with your database, run a piece of code on it in Python, and find all the places where that role relationship is messed up, you can do that without text. Do not purchase. And it's called post sync. So I added my post sync, it has a handle. It tries to find bad apples. It lets you know it's doing it. It looks at all the profiles. And for every profile, it makes sure the role is in the name. So it found one bad apple, and the primary key was one. OK. So these are the two paths I talked about. So this is the one path I talked about. You can remove the primary key, add a new field named ID, and make that the primary key, and create auto increment field. And the other path that I didn't specify was that you can actually create a view and then change the dbtable name to look at that view, and then create a primary key in that view. But then it messes up your updates and inserts. So path one is what I took. And there's other ways to do this too. There's a Django app that does composite keys. I haven't tried it out. So if you guys want to take a look at that and let me know. So if we run inspectEV again, what's going to happen? It's going to generate models for all the stuff that we've already generated. It's also going to override all the code we've written. So this is only meant to be one time thing. So if you run inspectEV after syncedDB and inspectCreates models for my built-in Django models. All right. And this is a trick question. So if anyone wants to brave it, let me know. What will happen if I give this code to another developer? Does anyone know? Or anyone think they know? OK. So we have managed set to false right now. So Django won't create those tables because it's unmanaged. So if we gave this, so the answer is it depends. If they have a database that doesn't have those tables, then it's not going to create them for them. And they're going to kind of think like, what's going on here? And if they have a database that has those tables, then things will work more or less. So that's part of that SQL custom command. If you need to give this database to somebody else and you want to create those files, or if you want to take out the managed equals false part, which before we keep talking about that, I should show you, this meta option managed equals false. So if you want to take out that managed equals false, then the default value is managed equals true. So if you gave this to somebody else and you took all those out and you ran a syncedDB, for that next person, it would create these tables. And I wanted to do one last thing at the head. Oh, well, actually, you know it. And just for fun is this table I created. And as we know, every table gets mapped to a model and every column gets mapped to a field and Python. But what if the fields would conflict, or what if they would create things that are nonsensical in Django? For example, the field name pass, which is a varchar. Pass is a key word in Python. So what's going to happen? Underscore underscore is going to conflict with following foreign key relationships. So let's just take a look at what it does, just for fun. Just for fun, the fields get added. Like this underscore field gets added. And then since both of these, the underscores get removed from the next field, even though it has two underscores. And then the one underscore field gets removed. And then they just keep on adding these numbers to it so that none of the namespace is conflict. Then the number 12 as a MySQL column name is also invalid identifier in Python because it's just a number. So number underscore 12 gets added. And I think I'm out of time. Sure. So don't read code unless you have to. My name is Chris, not Victor. Come say hi. I'm a pretty friendly guy. And if you're doing something with Django that's hard, you're probably doing it wrong. So anyway, buddy, have any questions?