joomla/jissues — pull request #342 by elkuku (2014-04-26 15:18:36+00:00)
Title: WIP Database refactoring (aka introducing Doctrine)

Some time ago i watched this very interesting [video](https://www.youtube.com/watch?v=C5P59blXuAA) by @HermanPeeren but life went on and I never got the time to actually play with Doctrine, so I decided to give it a try in my current favorite playground ;)

#### Disclaimer

I have to admit that I

* am no database expert at all
* have never used Doctrine before

so what I am doing here might be just #%&@=!;P

Judge for yourself...

#### This is meant as a "first step":

* Start using Doctrine's ORM tool only to setup and update the database tables.
* Keep using (J)DatabaseQuery to query the database.

Immediate benefits would be:

* Get rid of our custom installer code
* Remove the single `mysql.sql` file and move the information to annotations in table classes for every App
* Provide a database update feature

#### Renaming

While we're doing this, it might be a good time to start a discussion on a naming scheme (it has been pointed out somewhere that we are very inconsistent :wink: )

Table names

* `tablename`
* `appname_tablename`
* other...

Table PKs

* `id`
* `tablename_id`
* other...

etc.

Some readings:

* http://leshazlewood.com/software-engineering/sql-style-guide/
* https://launchbylunch.com/posts/2014/Feb/16/sql-naming-conventions/

Both are quite opposite ;)

#### The table prefix

I wonder if this is something we want to maintain...

Currently the table prefix is non optional in the Joomla! database driver and, if not given, defaults to a hard coded `jos_`.

I'm not sure how to handle this with Doctrine, so I decided to hard code it to `_` in the mean time:(

#### What's done

Service providers:

* [EntityManager](https://github.com/joomla/jissues/blob/doctrine/src/JTracker/Service/EntityManagerProvider.php)
* [Doctrine console runner](https://github.com/joomla/jissues/blob/doctrine/src/JTracker/Service/DoctrineRunnerProvider.php) (not sure why they did it so complicated creating two custom files...)

`./bin/jtracker/*` commands:

* `make database` aka `doctrine orm:schema-tool:create`
* `update database` aka `doctrine orm:schema-tool:update`
* `drop database` aka `doctrine orm:schema-tool:drop`

#### What's missing

* Table keys, foreign keys constraints,

I've hacked the `make dbcomments` CLI command a bit to generate the class properties with their annotations. That might be refined.

#### WIP

I'm putting this here for review and discussion.