From Legacy to Admin

This

Speaker 1: uh talk is Legacy Admin. So thank you guys for coming. Um I know you had many other options. 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 middle 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. Um this is me. Uh there's professional Chris and then uh regular Chris. So uh if you want to come talk to me afterwards, uh You know, you'll talk to a regular Chris, not professional Chris. But right now it's a professional Chris.

Speaker 1: So some themes for the talk that I wanted to uh give are don't write low, don't write code unless you have to. And my name is Chris, not Victor. That'll come up. And if you do something if you're doing something hard with Django, then you're probably doing it wrong. And this uh is something I realized when I started using Django initially. Um when I s uh 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. Um so my advice is you know to spend as you know come to DjangoCon. Um go um go on IRC , go on forums, and there's a lot of rich information.

Speaker 1: There's slash R slash Django. So it's and there's a lot of like custom apps, uh templates, and they all have like their suggested ways of doing things. So InspecTB is one way that you save a lot of time if you're looking working with a legacy system. So Inspec DB reads tables and columns, not rows, and our database and produces a model stoppy to standard out. So you don't have to manually write this code. And you could. You could uh you know create a class uh that inherits from models. model and give it an explicit TB uh TB data like name. And you could go through every uh table in your database. Uh but this just jump starts the process for you. And it's not perfect, so we'll go over why uh what what will you have to do afterwards, but it works pretty well. And when would you need this?

Speaker 1: Um and two scenarios have come up when I've needed this. I needed a I had a legacy legacy system and a legacy database, and I just wanted an admin interface to it. Uh so foreign keys are great. Uh You know, primary key constraints are great. There's uh you know all these constraints that MySQL comes with. But in reality, if you have like an additional uh validation layer to a legacy system, then do you want to keep an equal clean state. You can you know reverse engineer a model, add a couple of validations, and you'll have this slick admin interface The Django comes with for free. And the other use case that I've come up with is uh creating management commands. Uh so when you create management commands for going through a bunch of rows uh in the database and making sure that one field is less than another field. Or you want to find all uh rows that have, you know

Speaker 1: certain value. The ORM comes in handy for to for doing these things. And writing custom SQL is sometimes hard. Um particularly if like, you know, it's not it's not designed to be a programming language. It's designed to do specific things in SQL. So that's another reason why uh this is 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. Um and I need to be able to manipulate the data but still keep it in a clean state. Um so why can't you edit the source? Um there's a lot of reasons why you wouldn't be able to edit the source. the source. You have you don't have the source. It's a third-party product. It's written in Rails, or the source is written so poorly it's impossible to understand.

Speaker 1: Uh so at the end of this uh demo, I want I want to be able to have an admin interface that uh with as little work as possible, I want to be able to have my basic crowd 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 ways 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. Uh so the straight from the docs, uh inspect database in introspects the database uh and whatever's pointed. So uh the first thing you'll notice here it's that Whatever your settings uh name is pointed to, that's what it's gonna introspect. Um and it's also and this state 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.

Speaker 1: So something that you don't think about uh is that you'll often have more tables than models because of many-to-many relationships. And introspect uh introspection doesn't doesn't understand that concept. Um And also the most important part of this is this feature is meant to be a shortcut, not as definitive model generation. So I may created a fictitious model. So it's pretty basic. There's a user table. Uh and I made a lot of like 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. Uh 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.

Speaker 1: It's auto-incremented. It's not null. And has the username and then of course it has a plain text password field. We have a company table, uh has one uh 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 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 user has company. uh many to many table. So this relationship in in if it were 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 a of like of Django introspection to know this. So we're gonna see how what this actually produces in a second. Uh

Speaker 1: and another thing that's terrible about this for in terms of Django at least uh is that it has two primary keys. So it has composite primary keys, which is gonna 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 uh primary key. Uh and this is doesn't fit that pattern. And then profile, uh, just when you thought it couldn't get any worse, has a triple uh composite key. And There's a role here. So roll is meant to be a foreign key to this table role, but this is a var car and uh the name here is supposed to match the role in here. So it's like completely worse database design you can imagine. There can be so many problems with this. Upsert anomalies, deletion anomalies, like

Speaker 1: you name it. So that's that's my shitty model. And then just for fun, if we have time at the end, I'm gonna go over this table because I created like the worst uh field names possible just to see what they would produce. Um so the source of Inspect DB, um this is the source. Uh can you it's good size Uh okay, so the there's two primary loops. The first loop uh uses connection. introspection table names. Uh so connection. introspection is a uh connection-specific introspection function to get the table names. So depending on what database you're using, it'll get it'll use a different command to get back that list of table names. But then once it has that list of table names, um it's agnostic. This code, uh uh

Speaker 1: the rest of this code is agnostic. to the type of database you have. So I looked at the source code for uh the different uh introspection definitions. So MySQL, Oracle, and Postgres all have them. And if you look at the documentation, it says MySQL and Postgres are uh pretty like supposed to be first-class citizens. And Oracle has like the same uh function, introspection functions. methods defined to introspect a database. So I didn't get a chance to test uh 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. that but so once you get a table name uh at that first primary loop the secondary part of the loop uh or inside that loop uh gets the relationships gets the indexes and then it has a secondary loop that loops through all the

Speaker 1: uh columns and at the end of this, I know I didn't like do a good job of um this screenshot, but at the end of this it generates a or like it yields a uh output uh to the it yields an output of the column name to uh standard out so that's what we're gonna use now. So this was a little quiz I made. These are the introspection specific commands that my sequel sor that Django uses to get the table names. So my SQL is just show tables, uh 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. Um it doesn't give you a list of the tables sorted by you know, when they were created.

Speaker 1: It doesn't give you a list of the tables sorted by uh the name. And all of these are the same. So when it goes through that loop, it's basically gonna get uh there's just no way to guarantee what order it's in. Um so this is what we talked about before. The we use the connection introspection uh to get the table names, the relationships, and the indexes, and the columns. But then we're kind of agnostic at that next layer to generate the models. Once we have all that information from our um from our connection. And this is the many-to-many fail. Um like I said, this should map with a many-to-many uh on either the user or the company field. Depending, uh but this maps as three individual models. Um so let's take a look at what would happen if we did this.

Speaker 1: So python manage. py db shell. So I created this in my SQL, so we'll take a look at this in MySQL. 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 um 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 in the in and uh right now. So if we run our sync database first, we're going to create tables that Django needs to run uh to log in and uh you and to hold our users.

Speaker 1: But then if we run inspect database after that, those tables will also be in our database and they will be introspected as well. And that's not really what we want because those are our those models are already defined somewhere else. So we're gonna run uh Inspect DB first. So like I said, it goes directly to standard out, um, which is not very useful, but you can take a look at it. Um , So it tells you what to do, um, so which is nice, so I don't have to be here giving you a talk. You can just read this. You rearrange the models, you make sure that I think everyone has a primary key, and if you want, you can remove managed false. Um it doesn't really say what that's gonna do, besides it's gonna allow uh Django to have a lifecycle. And you're allowed to rename the models, but you're not allowed to rename the db

Speaker 1: tables, which is like the meta option to explicitly state what the table will name And then there's this line, which you know 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. All right, so let's go ahead and put this to. So I created an app for this. And let's see what we produce. This is the same thing, but we're just in a file now. Um And now we can run our sync db. So the first problem that we're gonna run into is uh related name collision.

Speaker 1: So let's add a related name to one of the uh namespace collisions. Let's see, related name. So looking at our model, um user has company user user. And by the way, like 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 uh is that the namespace will conflict. So the the accessors to get the results set uh will be uh 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. Okay, so we can sync our database now. Uh so all we had to do is add a related name, and it told us to do that. So that was easy enough.

Speaker 1: Here's my email if you want to send me something. All right, so we've sunked, uh we have uh ran our synced database, and now we can run our server. So it worked now. Um 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. Uh so let's look at what went wrong, and this is a common thing.

Speaker 1: Um the integer field has a primary key, but it's an auto field, and I know that it's an auto field 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 auto field It's gone back. Alright, 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. Um just because that annoys me. Um sorry for my use of lambdas.

Speaker 1: Now let's add a user. Also very annoying. So username is going to be Chris, and my password is going to be password is taco. Okay, so those were like the two easy tables to map. They had one primary key, they were auto-incremented. It miss 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.

Speaker 1: 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 primary key uh composite primary keys. Uh so there's uh 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's an ID field anyways. And you can Make that not null primary key and auto increment. So you're adding this uh extra uh field to each table that uh has a composite key, and you're just reducing that composite key to this extra field now. So

Speaker 1: I did not memorize how to do that. So let's drop into the D B shell. So I for the two tables that have composite keys, I'm adding a column ID int 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, uh there was some MySQL uh bug that I had to uh 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.

Speaker 1: Okay, 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. I'll save it, come back, and I'm gonna create a role, and I know I want this role to be uh worker bee. Now inside of my uh profile, if I want to create a new profile, I'm gonna give it an ID and it's gonna be worker be. But I'm going to misspell B. So we wanted this role to be consistent with the roles table, 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 gonna check and see what we can do about that.

Speaker 1: So in our profiles, I'm gonna add a validation to ensure that that happens. And I'm gonna borrow most of this from here. Gotta raise a validation error. Import roll. Um So we're gonna import validation error, we're gonna import our roles, and if the uh text for the profile role does not match something that we have in our value list for the names, uh then we will raise a validation error.

Speaker 1: Roll not bound. So we have one profile that's in a bad state. I'm gonna add another one that's supposedly in a good state. Worker B. So worker B, at first I'm gonna misspell it. So worker B is now being validated and it says roles not found, so we can update it. Uh if we change it back to worker B, which is in our roles table. 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. Okay. And I also created a management command uh to kind of demonstrate what you could do.

Speaker 1: Uh you know, this management command isn't specific to running uh 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 in and find all the places where that role relationship is messed up, you can do that without um thanks. 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. Uh it looks at all the profiles, and for every profile, it makes sure the role is uh in the uh name. So it found one bad apple and the primary key was one. Okay.

Speaker 1: So these are the two paths I talked about. Uh sorry, 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 DB table name to look at that view and then create a primary key in that view, but it then it messes up your updates and inserts. Uh so uh 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 wanna take a look at that and let me know. Um so if we run Inspec DB again, what's gonna happen? Uh it's gonna generate models for all the stuff that we've already generated. It's also gonna override all the code we've written. So this is only meant to be uh one time thing.

Speaker 1: So if you run InspecDB after SyncDB, Inspec creates models for my built-in Django models. All right, so another question, uh and this is a trick question, so if anyone wants to brave it, uh let me know. What will happen if I give this code to another developer? Does anyone know? Or anyone think they know? Okay, so uh we have manage set to false right now. So Django won't create those tables because it's unmanaged. So if we gave this, uh so the answer is it depends. If they have a database that doesn't have those tables, uh then it's not going to create them for them. And they're gonna kind of think like what's going on here? Um and if they have a database that has those tables, uh then things will work more or less.

Speaker 1: Uh so that's part of that SQL custom uh command. You can if you need to give this database to somebody else and you want to create those uh files or if you give so if you want to take out the managed part or sorry the manage equals false part uh which uh before we keep talking about that I should show you Um this meta option managed equals false. So if you want to take out that managed equals false, then the default value is managed equals false. equals true. So if you gave this to somebody else and you took all those out and you ran a syncdb, for that next person it would create these tables. And I wanted to do one last thing at the head time. Oh well actually you know that And just for fun, uh is this table I created. And as we know, every table gets mapped to a model, and every column gets mapped to a field in Python.

Speaker 1: But what if the fields uh would conflict or what if they would create things that are nonsensical in Django? Uh for example, the field name pass, which is a var car. Pass is a key word in Python. Uh so what's gonna happen? Um Underscore underscore is gonna conflict with following uh foreign key relationships. So let's just take a look at what it does, just for fun. Uh just for fun, the fields get added, like this underscore field gets added, and then since both of these, the underscores get uh 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 namespaces conflict. Then there

Speaker 1: the number 12 as a MySQL column name is also in valid identifier in Python because it's just a number. So number underscore 12 gets added. Uh and I think I'm out of time. You

Speaker 2: either have time or the whole question.

Speaker 1: Sure. So don't write 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 uh that's hard, you're probably doing it wrong. So anybody have any questions?