Categories
Software Development Web Development

How to reverse engineer a database for a symfony project with doctrine… aka t_

TLDR

This is a parody about how I learned to Reverse engineer a Database for a Symfony App with Doctrine.

Here is a short article including tips and tricks I discovered I learned while writing this.

This article jokes about some of what I ran into and what I was thinking while I learned Doctrine. It does give some details and useful information. t_ is how you tell Doctrine to ignore a table in migrations. I was going to just tell Doctrine to ignore all my tables. LOL

To understand why DATA is the most important to me. Watch this video and read the subtitles. Warning you will laugh your ass off.

@2:14 you will understand why us older guys put the database and database design before any other part of the app. You can have 100000000000000000000 interfaces to a database, but if your data is fubar you are fucked. “We can reverse the code right? Sure thing, the database is still fucked though, of course you had bad code running on it” LMFAO Best part of the video.

The same thing happens when you let an ORM design a database and you don’t fully understand the ORM and how it sees data. Get the wrong Entity design and you get lots of redundant fields in your database, or NULLS or just a big mess. Your app might work… until it sees actual traffic. You don’t want to flop on your big day, trust me I’ve done it.

Plus if you design your system by entities then move to a different frame work or want to run two versions of your app using two different frameworks… WELL…

When you learn to do something with one tool and then that tool is taken away.
This does show an interesting way to learn doctrine.

The story

I’ve been designing databases for more than 10 years now. I studied the subject in depth and still own many books on it. I only started using frameworks in the last few years.

I think in terms of data and data manipulation needs while designing apps in order to design the best system. First I read the specifications closely and start designing the database based on those. I try to imagine what data needs to be collected in order to cover the specifications properly.

I decided to try to get doctrine to import the mapping.

The Symfony docs say it should work about 80% and you will need to fill in the rest. It doesn’t really say what the rest is, or give any tips. It kind of assumes you are a doctrine Wizard.

look a wizard meme
Yes I am now a doctrine wizard, because I don’t have to know it first LOL

The issue is, you must damn near be a  Doctrine Wizard in order to understand exactly how the Entities should be modeled in order to match a database design. Otherwise you are like me and have to do this by trial and error wasting lots of time or spend months doing courses and tutorials. Nah, I’ll stick with designing my database the old way thanks.

I am a Wizard

If you have a sloppy Entity you have a sloppy database design. I had to learn how Doctrine would like an entity to be designed to match my database design.

i am wizard meme
So I became a doctrine wizard… sort of LMFAO

Well the first issue I ran into was using foreign keys as a composite primary key.

Doctrine was all like

kitten frew up meme
doctrine no like primary foreign key

Ok so I changed my design so that every table had a unique ID instead of a unique foreign key primary key etc. I deleted most of my entities.

Then I run the command to rebuild the entities aka map them.

php bin/console doctrine:mapping:import "App\Entity" annotation --path=src/Entity

Doctrine creates all my entities. I look them over, not really knowing what all the hell I am really looking at with the annotations and such.

makes sense to me meme
Yes this all looks good to me. LMFAO

Next I decided to diff the database to see what all would need changing.

Ok now Doctrine doesn’t like my tinyint values I used in my MySql database design.

tiny paper meme
Doctrine is all like Tinyint WTF?

So I go through and change every tinyint to smallint. Delete most of the entities.  Run the commands again. You can define your own types for Doctrine more on that in another post.

Lets Change the foreign keys

Now doctrine is not trying to change tinyint to smallint… but it wants to rename ALL of my foreign keys. WTF.

Lets rename the foreign keys for no F***ing reason

Ok that isn’t so bad, maybe it needs them named a specific way. I didn’t google that mess.

Lets get Cryptic with it.

I can live with cryptic foreign key names like…

FK_CA093F7E83172943

Not like I have to see them often. Other than when there is a DB error, now… instead of my beautiful Foreign key scheme which included the table name as a hint I get some cryptic bullshit in my messages and no way to track down WTF key it was.

borat great job meme
It is the little things in programming.

Lets be Null together

But then I look closer and now the diff command wants to change all of my foreign keys to default null.

are you kidding me
Freaking seriously
The purpose of a foreign key is to make the database make sure a value exists in one table before inserting a value that links to it in another table. This is known as referential integrity.

This is where I draw the line with Doctrine

draw the line meme
Sorry doctrine. No I will not ruin my database design just to use you.

Allowing nulls on a foreign key is STUPID. This means that now your application code must make sure a value is not null before inserting it and it must exist in the other table first… GO HOME YOU ARE DRUNK, TAKE SISTER NULL BACK TO JAVALAND FFS.

Which means you can’t use some of symfony’s validation techniques because they rely on the same Entity class annotations. Sure you can add to the form validation, but you still need to make sure a value exists in the linked table before you can insert it or else you insert a null value, which destroys referential integrity.

Null values on foreign keys just make no sense to me.

makes no sense meme
That makes no damn logical sense!

So in order to use Doctrine it looks to me like I must ruin my beautiful table design. I only wanted to use it for the migrations and some ease of use deals. But I do not want to destroy my database to do so.

For example if I have a hashtagged_images table and that table contains a hashtag_id and an image_id . A table like the following

hashtagged_images

  • id
  • hashtag_id
  • image_id

Why would I ever want either of those to be null? That is like dividing by zero. You never ever, want either of those null, that will make your database a flaming hot mess.

Yay lets have nulls in the database

Now your code must check whether anything contains a null before your app can use it. It must also make sure that before you insert a hashtag for an image that you must first hit the hashtags table to make sure a hashtag with a specific id exists. Then you must hit the images table too, to make sure an image with such an ID exists. Then you can finally insert your images hashtag.

That is a ton of work when a simple “not null foreign key” constraint would do all of that for you. It would also shift the query work from the app to the database like it should be.

A new team member may not know your system and forget to check for null for example. Here is more info about nulls in the database.

Null is only useful when you have a table with columns that don’t need a value when the record is initially inserted. Something like a collection ID for an image a user uploads. It very well may not belong to a collection, therefore you would need to allow for a NULL field value or have a default value.

These days I try to just create default values for everything. I try to have as few Nulls in the database as possible. The longer you program, the more you hate NULL.

Nope, Not gonna do it…

So that is how I came to prefixing all tables with t_ so that Doctrine would leave them alone.

nothing works meme
I kept trying and trying to make changes
But I have this disorder that makes me INSANELY persistent.

I refused to leave this alone.  So I kept making changes and running this command

php bin/console doctrine:mapping:import "App\Entity" annotation --path=src/Entity

Then running a migration diff and viewing the changes that doctrine wanted to make in the migration up.

Then I got this idea.

smiley bright idea meme
Then I got an idea!!!

What if I try adding nullable=false to the join column definitions? I didn’t bother googling it or trying to find it in the damn docs. I just did it.

just do it meme
Let me just try this.

So after I added that and reran the diff, not the entity mapping, and BOOM Doctrine was no longer suggesting in the migration up that it should change the foreign key to default to null.

detecting much win meme
Boom I won.

So when you want your foreign keys which are an ID in another linked table to not be null like they should be. You should have a definition like this in your Entity

/**
     * @var \ImageHashtags
     *
     * @ORM\ManyToOne(targetEntity="ImageHashtags")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="image_hashtag_id", referencedColumnName="id", nullable=false)
     * })
     */

This tiny little line nullable=false changes everything. It tells Doctrine you do not want that field to be nullable.

I don’t know why the Doctrine mapping code can’t get this right. This information is provided to it by the DB query. Maybe I could help find this code and update it so that it includes this tiny line if the DB query returns it.

The story must go on…

but there is still more stuff to do

You also need to fix EVERY LAST ENTITY NOW. Yes for each and every entity you must now open the file and list the damn repository for it. For some reason the Doctrine code can’t do this either, even though the Repository is just the Entity name with Repository added to it.

now I do more work.

So open up every last single Entity and create a line like this above the class definition in  a comment (I also created another maker to fix this, I still need to create a repo etc. for it)

* @ORM\Entity(repositoryClass=PageUrlsRepository::class)

Otherwise you will get Errors every time you try to use a Repository with a custom method name.

Now I am a wizard

i am a wizard meme
Now I am a Doctrine Wizard

Here is an article about tips and tricks to reverse engineer your database with Doctrine.

By Robert jackson

This is my website. I write articles about programming and more. I've been programming 16 years now.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: