Generate DB scheme from TCA


(Philipp Gampe) #41

No need to define those in TCA. They can be created automatically (and @professorweb first patch already proves it), as long we can detect that a table needs those fields (l10n enabled or WS enabled).

The goal is to deduce the SQL scheme from the TCA, not duplicate it there.

Potentially incomplete list of TCA field config options that describe scheme information. Most of the are required for datahandler, some are only for display purpose:

MM, MM_insert_fields, MM_hasUidField, MM_match_fields, MM_opposite_field, MM_oppositeUsage, MM_table_where, file_field, foreign_default_sortby, foreign_field, foreign_label, foreign_match_fields, foreign_selector, foreign_sortby, foreign_table, foreign_table_field, foreign_unique, symmetric_field, symmetric_label, symmetric_sortby, dbType, exclusiveKeys, foreign_table_where, MM_table_where, allowNonIdValues, multiple, rootLevel.


(Jigal Van Hemert) #42

To me this is not the way to introduce such a change. A blueprint or another document with the concept and decision here would be correct IMO.

On topic: let’s get rid of ext_tables.sql and replace it with something DBMS agnostic. As both TCA and models define an interpretation of the database structure, TCA is not the one place for me to define DB structures.


(Philipp Gampe) #43

That is why I started this discussion. The concept of blueprints before implementation was not used much lately. That makes it really hard to follow core development for people not hanging out in the chats every day.


(Claus Due) #44

Here’s a radical idea:

  • Deprecate current method of defining schema completely; all parts.
  • Require a Model class for every table
  • Enable doctrine annotations for all detected model classes

To solve the problem of composing classes to add columns etc:

  • Allow adding a list of Trait class names which must be added to the model
  • Put annotations and validation instructions, and getters/setters, in those Traits
  • Use the object registry to transparently create and maintain a proxy class composed from the base model class plus a number of Traits

Such an implementation could work by directly copying the entity class being extended, to a separate directory while preserving the original namespace and class name. Then the copy can be re-written; detecting the opening of the class definition and adding a list of use TraitName entries. Finally, validating that the copy is loadable w/o errors - and then adding it to a registry to make the replacement class load instead of the original when the class is loaded with GeneralUtility::makeInstance.

With sufficient conventions for file names and class names in place we could potentially also scan for and build such classes during composer installation, when composer is in use. With the right setup this substitute class loading can be integrated at the composer class loading level for optimal performance. TYPO3 could then for example in Development context keep track of these classes and regenerate them as necessary (class name doesn’t change, only implemented traits change, scanning for presence of traits is fairly easy).

Potential benefits:

  • Industry standard for schema handling with great flexibility and built-in relational table handling
  • Doctrine annotations can become the universal method of defining schema, both for Extbase and other entities
  • We gain a true set of models for the core classes which can be properly extended
  • We can begin passing instances of entities or use repositories to begin reducing the number of direct record accesses in the core.
  • Can work alongside current schema management (but has drawback, see below)

Drawbacks:

  • Without a method of composing classes this will be a very user unfriendly solution (no way to extend except to replace class)
  • Introducing the creation of runtime-built classes requires a bit of extra care and error prevention and needs to be developer friendly in some way.
  • Debugging entities with xdebug and breakpoints requires setting the breakpoints in the generated proxy class.
  • Might not be easily achieved to list the pending schema update changes in install tool - might not be easy to dispatch the schema updates from install tool.
  • May or may not be compatible with our current schemas for M:N relation binding tables. I hope @lolli may be able to help with that piece of the information puzzle.

EDIT/ADD: If we used Doctrine annotations as the basic layer we could even go the opposite direction of what’s proposed in this thread - and begin generating TCA based on the annotations. For many basic field types the automatically generated TCA would be sufficient; we could then allow changing the configuration with TCA overrides like we do currently (in other words, we phase out the Configuration/TCA and only process Configuration/TCA/Overrides; and/or we change pragma so files act like overrides in both of these folders to make migration transparent).


(Stefano Kowalke) #45

Hi,

Unless TYPO3 does not switch to Doctrine ORM you can not - at least not out of the box. Doctrine DBAL supports only one schema representation with is written in PHP.

Annotations, YAML or XML schema definitions are only supported by Doctrine ORM - at least for the moment. Version 3 of Doctrine ORM will get rid of YAML [1] and recommends XML for model schema representation in production and PHP Annotations for in-house projects. YAML might not the way to go if TYPO3 wants to switch Extbase ORM to Doctrine ORM some day.

[1] https://github.com/doctrine/doctrine2/pull/5932

I am not sure about the SQL compliance of ext_tables.sql nowadays. When I debugged through that part of the core back in 2014 it was no SQL - it just looked like it. The Install Tool took it apart and generated SQL statements for the DB operations which it has to perform, involving the DB Compare feature. With the migration to Doctrine DBAL, I expect this has changed and my statement might not true anymore.

In my fork of the core I migrated all ext_tables.sql to Doctrine DBAL schema represenation [2] and at the end I was able to create the DB tables from that Schema.php files. Out of my personal interest and apart of main topic of my thesis, I started implementing Doctrine’s DBAL migrations compare feature to the DB Compare tool, which I got to semi-work. As far as I remember, I had some trouble with the separation of add, change, and create_table statements and I stopped eventually.

[2] https://github.com/Konafets/TYPO3CMSDoctrineDBAL/commit/3062549edb72cd5c9959bff674b01b5ea723559a

From a DDD approach I strongly disagree here. The database should be an implementation detail to the developer. The Domain Model, which contains the business rules, is the first citizen and it shouldn’t matter which kind of DB is in usage. The domain model should be framework and database agnostic and be able to persist to MySQL/MariaDB, Postgresl, any NoSql or an EventStream.
But I would not go to hide the DB from the developer. At some point the data needs to be persisted and the dev should be able to define how, what and where.


(Kevin Ditscheid) #46

I think this is exactly the way where to go with this in the end. Using doctrine in the core enabled to switch the stuff to the way you suggested. The logical step now is to get rid of the ancient ext_tables.sql, imho. The goal should be to get closer to the way you described, but I fear for some people the step could be to great. You would have to deprecate tons of stuff. I don’t know how far the development will get to be ready in time for the next release.


(Philipp Gampe) #47

This is not about a a DDD approach, which - as you identified correctly - only relates the the business logic implementation in an OO-Framework. But the business logic is something completely disjunctive from the data storage layer. The later is all about consistency and efficient querying. It must ensure consistency even in the case of failures that might have nothing to do with the business logic.

@namelesscoder The scheme has nothing do to with the OO representation of the data. You might have multiple models for the same table, for example if you have some big columns (TEXT, BLOB) that are only needed in a very few cases.
You cannot generate a (good) DB scheme from a domain model. A DB scheme contains many more things (keys, constrains, triggers, views).

What you are suggesting effectively turns your models in some kind of active records, that are for whatever reason interweaved with an object relational mapper.

@suhemi Not treating your storage layer as first class citizen during development will result in poor query performance and potential data inconsistency (especially in the case of failures). Of course this is rather academic for a small site with a few hundreds to thousand records, but will turn into a nightmare if you have over a million records and a large number of concurrent writes.

That is called mapping configuration. You can (and properly should) do that with annotations. This is only a fraction of the information required for the DB scheme.


(Claus Due) #48

I disagree, you should try the Doctrine annotation driver.


(Armin Vieweg) #49

I like Doctrine2 ORM very much. Especially with traits (for common fields like uid, pid, enabled fields, …) very neat.

But each table must have a representing model then. Even several models using the same table would not be a big deal.

Triggers are moved to php in Doctrine ORM: http://stackoverflow.com/a/18390089/564000 and views can get used this way: http://www.doctrine-project.org/2009/06/19/using-views-with-doctrine.html

And for indexes you’ve got the @index annotation: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/annotations-reference.html#annref-index

So if it’s possible to introduce Doctrine2 ORM to core, I would agree :slight_smile:


(Philipp Gampe) #50

That only proves that you can generate a DB scheme from a domain model.

It says nothing about normalization and data integrity during concurrent writes and failure conditions.

No further comment.


(Claus Due) #51

That’s why I recommend you actually give the annotation driver a proper test run to answer your own questions in these regards. Instead of disregarding a potential replacement with a “no further comment” and, I assume, go on planning how to add another TCA invention instead of looking with an open mind for an industry standard to replace it. All we’re asking for is an open mind instead of outright rejection based on perceived lack of support for whatever edge case you come up with.


(Armin Vieweg) #52

Apparently you’ve got knowledge which I don’t have. What is the problem to set attribute values in php instead of in MySQL (with triggers)?


(Philipp Gampe) #53

Concurrency.

[20 char limit]


(Armin Vieweg) #54

Do you want to have a discussion here? Or are my points to stupid for you to answer in full sentences? …

Transactions btw. are also supported by Doctrine2: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/transactions-and-concurrency.html

So what exactly is your point?


(Philipp Gampe) #55

Transactions are too slow to serve many concurrent requests. They must be used with care.


(Stephan Großberndt) #56

As I am currently setting up a big project on TYPO3 8.7 with PostgreSQL I am all in favour of removing ext_tables.sql - if you do not work with MySQL having to setup the database using MySQL commands is irritating at best, when using geometry columns like postgis it’s currently impossible.


(Oliver Hader) #57

Coming back to initial question, whether to create database schema definitions only from TCA and thus drop ext_tables.php I’d opt for saying NO. The reason basically is, that using TCA only, all special database schema definitions have to be passed through TCA, with the need to invent new special TCA types - Stephan’s geo spatial scenario is a very good example for that.

However, I’m in favor of resolving the demand of writing MySQL specific definitions in ext_tables.sql and replace that one with the mentioned Doctrine DBAL definitions - either using YAML or using PHP objects.

I don’t see side-discussions about ORM, validation, domain-driven-design and transactions belonging to this discussion. These topics have to be discussed and analyzed in dedicated threads.


(Philipp Gampe) #58

@ohader Yes, that is also my impression based on the responses. I will write a summary here in a few days.


(Claus Due) #59

If one truly does exclude the other (and we have no way to set this per-extension at least) then I strongly suggest going the route of PHP objects rather than YAML, or only using YAML temporarily until model-related questions are resolved. Achieving (as in: not blocking) Doctrine annotation support for Extbase or future TYPO3-core models should be a priority - even if you don’t wish to discuss it in this thread.


(Oliver Hader) #60

Claus, feel free to start a new discussion topic about ORM and extending Extbase domain models. I currently don’t see the relation between Extbase and defining database schema in the first place…