Uploaded image for project: 'eZ Publish / Platform'
  1. eZ Publish / Platform
  2. EZP-24842

As a Maintainer I want eZ Platform installer to use Doctrine Schema files

    Details

    • Story Points:
      5

      Description

      We have https://github.com/ezsystems/ezpublish-kernel/blob/master/data/mysql/schema.sql but no corresponding Postgres schema file - this should be added.
      Perhaps we can use https://github.com/ezsystems/ezpublish-kernel/blob/master/eZ/Publish/Core/Persistence/Legacy/Tests/_fixtures/schema.pgsql.sql as a source?

      As a result of many discussions and reviews of the prototype we decided to make this a part of 2.0 release and use Doctrine Schema files.
      After some investigation we've found out that there's no official schema file format as Doctrine DBAL Schema Tool is PHP API. (Not to be confused with Doctrine ORM which actually has defined Yaml format).
      We've decided to create our own Yaml format of a schema which uses Doctrine Schema Tool.

      The remaining thing to do is to make installer an API, so developers can call it from their own code without a need to call ezplatform:install command. However, of course the command will work as it worked before - there's no reason for BC break on that.

      The major BC break is related to replacing schema.sql with schema.yml file. It's important to make it extensible as developers tend to modify it for custom needs. This can be achieved by creating some schema file provider. TBD.

        Activity

        Gunnstein Lye created issue -
        Gunnstein Lye made changes -
        Field Original Value New Value
        Status Open [ 1 ] Confirmed [ 10037 ]
        André Rømcke made changes -
        Epic Link EZP-23950 [ 43788 ]
        André Rømcke made changes -
        Status Confirmed [ 10037 ] Backlog [ 10000 ]
        André Rømcke made changes -
        Rank Ranked higher
        André Rømcke made changes -
        Fix Version/s Known Issues List [ 14512 ]
        André Rømcke made changes -
        Fix Version/s Enablement Issues [ 14512 ]
        André Rømcke made changes -
        Epic Link EZP-23950 [ 43788 ] EZP-25368 [ 52916 ]
        Andrzej Longosz logged work - 04/Oct/16 11:18 AM
        • Time Spent:
          3 hours
           

          working on doctrine-based creation of schema

        Andrzej Longosz made changes -
        Status Backlog [ 10000 ] Development [ 3 ]
        Assignee Andrzej Longosz [ andrzej.longosz@ez.no ]
        Andrzej Longosz made changes -
        Remaining Estimate 0 minutes [ 0 ]
        Time Spent 3 hours [ 10800 ]
        Worklog Id 60867 [ 60867 ]
        Andrzej Longosz logged work - 05/Oct/16 6:33 PM
        • Time Spent:
          5 hours
           

          working on doctrine-based creation of schema

        Andrzej Longosz made changes -
        Time Spent 3 hours [ 10800 ] 1 day [ 28800 ]
        Worklog Id 60883 [ 60883 ]
        Andrzej Longosz made changes -
        Time Spent 1 day [ 28800 ] 1 day, 6 hours, 30 minutes [ 52200 ]
        Worklog Id 60884 [ 60884 ]
        Andrzej Longosz logged work - 06/Oct/16 6:33 PM
        • Time Spent:
          6 hours, 30 minutes
           

          working on doctrine-based creation of schema

        Andrzej Longosz logged work - 07/Oct/16 12:56 PM
        • Time Spent:
          5 hours
           

          solving issues with sequence naming

        Andrzej Longosz made changes -
        Time Spent 1 day, 6 hours, 30 minutes [ 52200 ] 2 days, 3 hours, 30 minutes [ 70200 ]
        Worklog Id 60906 [ 60906 ]
        Show
        Andrzej Longosz added a comment - - edited PR: https://github.com/ezsystems/ezpublish-kernel/pull/1924
        Andrzej Longosz logged work - 10/Oct/16 12:29 PM
        • Time Spent:
          6 hours
           

          solving issue with unsupported by Doctrine MySQL indexes with length

        Andrzej Longosz logged work - 11/Oct/16 12:30 PM
        • Time Spent:
          6 hours
           

          creating script to reset sequences after cleandata import

        Andrzej Longosz made changes -
        Time Spent 2 days, 3 hours, 30 minutes [ 70200 ] 3 days, 1 hour, 30 minutes [ 91800 ]
        Worklog Id 60923 [ 60923 ]
        Andrzej Longosz made changes -
        Time Spent 3 days, 1 hour, 30 minutes [ 91800 ] 3 days, 7 hours, 30 minutes [ 113400 ]
        Worklog Id 60924 [ 60924 ]
        Andrzej Longosz made changes -
        Status Development [ 3 ] Backlog [ 10000 ]
        Hide
        Andrzej Longosz added a comment - - edited

        We don't want to create another SQL script, so we decided to create schema in a generic way, using Doctrine. However we have to postpone this until Kernel 7.0.

        Reason: generic creation of schema is not enough to solve this. We also need to provide cleandata in a more generic way (via Public API?) to properly handle sequences (workaround for resetting them after cleandata import is not the way to do it).

        In short: too many workarounds due to BC on 6.x

        Show
        Andrzej Longosz added a comment - - edited We don't want to create another SQL script, so we decided to create schema in a generic way, using Doctrine. However we have to postpone this until Kernel 7.0 . Reason: generic creation of schema is not enough to solve this. We also need to provide cleandata in a more generic way (via Public API?) to properly handle sequences (workaround for resetting them after cleandata import is not the way to do it). In short: too many workarounds due to BC on 6.x
        André Rømcke made changes -
        Labels v2
        André Rømcke made changes -
        Sprint v2 s0 [ 76 ]
        André Rømcke made changes -
        Rank Ranked lower
        André Rømcke made changes -
        Rank Ranked lower
        Andrzej Longosz made changes -
        Status Backlog [ 10000 ] Development [ 3 ]
        Andrzej Longosz logged work - 01/Mar/17 1:00 PM
        • Time Spent:
          4 hours
           

          working on this issue

        André Rømcke made changes -
        Summary Add Postgres schema Prototype adding Postgres schema using Doctrine schema files
        André Rømcke made changes -
        Description We have https://github.com/ezsystems/ezpublish-kernel/blob/master/data/mysql/schema.sql but no corresponding Postgres schema file - this should be added.

        Perhaps we can use https://github.com/ezsystems/ezpublish-kernel/blob/master/eZ/Publish/Core/Persistence/Legacy/Tests/_fixtures/schema.pgsql.sql as a source?
        We have https://github.com/ezsystems/ezpublish-kernel/blob/master/data/mysql/schema.sql but no corresponding Postgres schema file - this should be added.

        Perhaps we can use https://github.com/ezsystems/ezpublish-kernel/blob/master/eZ/Publish/Core/Persistence/Legacy/Tests/_fixtures/schema.pgsql.sql as a source?


        However we might want to use Doctrine schema to start to take advantage of doctrine migrations for schema.
        SO this is about prototyping this to see feasibility of moving to that for Improved storage engine.
        Andrzej Longosz made changes -
        Time Spent 3 days, 7 hours, 30 minutes [ 113400 ] 4 days, 3 hours, 30 minutes [ 127800 ]
        Worklog Id 63011 [ 63011 ]
        Andrzej Longosz logged work - 02/Mar/17 6:00 PM
        • Time Spent:
          6 hours
           

          working on this issue

        Andrzej Longosz made changes -
        Time Spent 4 days, 3 hours, 30 minutes [ 127800 ] 1 week, 1 hour, 30 minutes [ 149400 ]
        Worklog Id 63065 [ 63065 ]
        Andrzej Longosz made changes -
        Time Spent 1 week, 1 hour, 30 minutes [ 149400 ] 1 week, 7 hours, 30 minutes [ 171000 ]
        Worklog Id 63066 [ 63066 ]
        Andrzej Longosz logged work - 03/Mar/17 5:18 PM
        • Time Spent:
          6 hours
           

          working on this issue, WIP PR

        Andrzej Longosz made changes -
        Time Spent 1 week, 7 hours, 30 minutes [ 171000 ] 1 week, 1 day, 5 hours, 30 minutes [ 192600 ]
        Worklog Id 63401 [ 63401 ]
        Show
        Andrzej Longosz added a comment - PR: https://github.com/ezsystems/ezpublish-kernel/pull/1941
        Andrzej Longosz logged work - 23/Mar/17 5:14 PM
        • Time Spent:
          6 hours
           

          working on this issue

        Andrzej Longosz logged work - 20/Apr/17 5:35 PM
        • Time Spent:
          6 hours
           

          rewritting solution

        Andrzej Longosz made changes -
        Status Development [ 3 ] Development Review [ 10006 ]
        Andrzej Longosz made changes -
        Time Spent 1 week, 1 day, 5 hours, 30 minutes [ 192600 ] 1 week, 2 days, 3 hours, 30 minutes [ 214200 ]
        Worklog Id 63726 [ 63726 ]
        Andrzej Longosz made changes -
        Time Spent 1 week, 2 days, 3 hours, 30 minutes [ 214200 ] 1 week, 3 days [ 230400 ]
        Worklog Id 63727 [ 63727 ]
        Andrzej Longosz logged work - 21/Apr/17 5:36 PM
        • Time Spent:
          4 hours, 30 minutes
           

          final fixes, updating PR

        Sarah Haïm-Lubczanski (Inactive) made changes -
        Rank Ranked higher
        Andrzej Longosz made changes -
        Time Spent 1 week, 3 days [ 230400 ] 1 week, 3 days, 4 hours [ 244800 ]
        Worklog Id 65012 [ 65012 ]
        Andrzej Longosz logged work - 12/Jul/17 6:42 PM
        • Time Spent:
          4 hours
           

          rebased PR, testing with PlatformUI behat scenarios

        Andrzej Longosz made changes -
        Time Spent 1 week, 3 days, 4 hours [ 244800 ] 1 week, 4 days, 3 hours [ 270000 ]
        Worklog Id 65052 [ 65052 ]
        Andrzej Longosz logged work - 17/Jul/17 7:08 PM
        • Time Spent:
          7 hours
           

          solving side issues related to CI and behat tests

        Andrzej Longosz logged work - 19/Jul/17 7:00 PM - edited
        • Time Spent:
          5 hours
           

          debugging travis/behat failures

        Andrzej Longosz made changes -
        Time Spent 1 week, 4 days, 3 hours [ 270000 ] 2 weeks [ 288000 ]
        Worklog Id 65140 [ 65140 ]
        Andrzej Longosz made changes -
        Worklog Id 65140 [ 65140 ]
        Andrzej Longosz made changes -
        Time Spent 2 weeks [ 288000 ] 2 weeks, 5 hours [ 306000 ]
        Worklog Id 65141 [ 65141 ]
        Andrzej Longosz logged work - 20/Jul/17 6:59 PM
        • Time Spent:
          5 hours
           

          debugging travis/docker/behat failure

        Alex Schuster made changes -
        Workflow EZ* Development Workflow [ 95937 ] EZEE Development Workflow [ 108193 ]
        Andrzej Longosz made changes -
        Status Development Review [ 10006 ] Backlog [ 10000 ]
        Roland Benedetti made changes -
        Issue Type Improvement [ 4 ] Story [ 7 ]
        Workflow EZEE Development Workflow [ 108193 ] EZEE and EZP Story Workflow [ 128512 ]
        Roland Benedetti made changes -
        Status Backlog [ 10000 ] Specification [ 10002 ]
        Assignee Andrzej Longosz [ andrzej.longosz@ez.no ] Roland Benedetti [ roland.benedetti@ez.no ]
        Roland Benedetti made changes -
        Original Estimate 0 minutes [ 0 ]
        Assignee Roland Benedetti [ roland.benedetti@ez.no ] Andrzej Longosz [ andrzej.longosz@ez.no ]
        Andrzej Longosz made changes -
        Summary Prototype adding Postgres schema using Doctrine schema files As a Maintainer I want eZ Platform installer to use Doctrine Schema files
        Andrzej Longosz made changes -
        Description We have https://github.com/ezsystems/ezpublish-kernel/blob/master/data/mysql/schema.sql but no corresponding Postgres schema file - this should be added.

        Perhaps we can use https://github.com/ezsystems/ezpublish-kernel/blob/master/eZ/Publish/Core/Persistence/Legacy/Tests/_fixtures/schema.pgsql.sql as a source?


        However we might want to use Doctrine schema to start to take advantage of doctrine migrations for schema.
        SO this is about prototyping this to see feasibility of moving to that for Improved storage engine.
        {quote}
        We have https://github.com/ezsystems/ezpublish-kernel/blob/master/data/mysql/schema.sql but no corresponding Postgres schema file - this should be added.
        Perhaps we can use https://github.com/ezsystems/ezpublish-kernel/blob/master/eZ/Publish/Core/Persistence/Legacy/Tests/_fixtures/schema.pgsql.sql as a source?
        {quote}

        As a result of many discussions and reviews of the prototype we decided to make this a part of {{2.0}} release and use Doctrine Schema files.
        After some investigation we've found out that there's no official schema file format as Doctrine DBAL Schema Tool is PHP API. (Not to be confused with Doctrine ORM which actually has defined Yaml format).
        We've decided to create our own Yaml format of a schema which uses Doctrine Schema Tool.

        The remaining thing to do is to make installer an API, so developers can call it from their own code without a need to call {{ezplatform:install}} command. However, of course the command will work as it worked before - there's no reason for BC break on that.

        The major BC break is related to replacing {{schema.sql}} with {{schema.yml}} file. It's important to make it extensible as developers tend to modify it for custom needs. This can be achieved by creating some schema file provider. TBD.
        Andrzej Longosz made changes -
        Status Specification [ 10002 ] Specification Done [ 10003 ]
        Component/s Install [ 10820 ]
        Assignee Andrzej Longosz [ andrzej.longosz@ez.no ]
        Andrzej Longosz made changes -
        Assignee Andrzej Longosz [ andrzej.longosz@ez.no ]
        Andrzej Longosz made changes -
        Story Points 8
        Roland Benedetti made changes -
        Sprint v2 Rolling sprint [ 76 ] v2 Rolling sprint, Sprint 4 [ 76, 92 ]
        Roland Benedetti made changes -
        Rank Ranked higher
        Andrzej Longosz made changes -
        Status Specification Done [ 10003 ] Development [ 3 ]
        Sylvain Guittard made changes -
        Component/s Platform > Repository & Services (Public API impl) [ 10309 ]
        Roland Benedetti made changes -
        Sprint v2 Rolling sprint, Sprint 4 [ 76, 92 ] v2 Rolling sprint, Sprint 5 [ 76, 93 ]
        Roland Benedetti made changes -
        Rank Ranked lower
        Roland Benedetti made changes -
        Story Points 8 5
        Andrzej Longosz made changes -
        Affects Version/s 2.0.0-beta1 [ 14863 ]
        Andrzej Longosz made changes -
        Fix Version/s 2.0.0 [ 14722 ]
        Roland Benedetti made changes -
        Sprint v2 Rolling sprint, Sprint 5 [ 76, 93 ] v2 Rolling sprint, Sprint 6 [ 76, 94 ]
        Roland Benedetti made changes -
        Rank Ranked higher
        Roland Benedetti made changes -
        Sprint v2 Rolling sprint, Sprint 6 [ 76, 94 ] v2 Rolling sprint [ 76 ]
        Roland Benedetti made changes -
        Rank Ranked lower
        Hide
        Bertrand Dunogier added a comment -

        From PM's perspective, it would be great if you could list benefits we get from that. You most likely went over those when talking about it, and it would help a lot of we had that knowledge.

        Also, please try to clarify what the status is as compared to the latest comments & pull-request. The latest comment hints that it is mostly cleanup & testing, but according to january's first planning, there is more work needed.

        Show
        Bertrand Dunogier added a comment - From PM's perspective, it would be great if you could list benefits we get from that. You most likely went over those when talking about it, and it would help a lot of we had that knowledge. Also, please try to clarify what the status is as compared to the latest comments & pull-request. The latest comment hints that it is mostly cleanup & testing, but according to january's first planning, there is more work needed.
        Hide
        Andrzej Longosz added a comment -

        What we've established so far by discussing it internally in December:

        • there will be a separate package ezsystems/ezplatform-installer providing:
          • bundle, ezplatform:install command and services (so installer can be injected via DI into another process)
          • logic parsing Yaml into SQL, including our custom improvements for PostgreSQL (missing expression index columns support) and MySQL (missing index column length support) implemented by extending Doctrine's PostgreSQLPlatform and MySqlPlatform. Note: we use Doctrine's Schema Tool, but for DBAL it is a PHP API only, so we've created our own format, similar to what I've seen for ORM, but more compact.
          • command and parser dumping existing schema to Yaml file (because why not)
          • in the future: maybe some command to update eZ Platform (just a random thought, didn't share it with anyone until now )
        • mentioned package would be a meta-repository dependency, not kernel, because it requires kernel
        • in kernel we would keep core schema and data (for now both core tables and external storage, in the future: each FieldType would provide its own schema file for external storage) - this is a little bit tricky, because I don't want to hardcode vendor/ezpublish-kernel/data/schema.yml path. Someone suggested schema and data files should be bundle Resources, so maybe it's a way to go.

        AFAIR (last time when I considered this) there's at least one difficulty with that solution: ideally we should get rid of all SQL schemas, including the ones for tests. Running integration tests on a database installed from Yaml schema would be a huge advantage.
        The solutions:

        • parts related to interpreting Yaml schema should remain in kernel
        • maybe interpreting Yaml schema should be another separate package, independent from kernel and reusable in any projects using Doctrine/DBAL?

        Other remaining things to do:

        • check if schema contains all changes done in past months (comparing schemas installed the old and new way, both for Postgres and MySQL).
        • test once again if app works (no extensive behat yet, so that's a risk)
        • make behat setup choose PostgreSQL database, controlled by Travis parameters, so we can at least track if installer works.
        Show
        Andrzej Longosz added a comment - What we've established so far by discussing it internally in December: there will be a separate package ezsystems/ezplatform-installer providing: bundle, ezplatform:install command and services (so installer can be injected via DI into another process) logic parsing Yaml into SQL, including our custom improvements for PostgreSQL (missing expression index columns support) and MySQL (missing index column length support) implemented by extending Doctrine's PostgreSQLPlatform and MySqlPlatform . Note: we use Doctrine's Schema Tool, but for DBAL it is a PHP API only, so we've created our own format, similar to what I've seen for ORM, but more compact. command and parser dumping existing schema to Yaml file (because why not) in the future: maybe some command to update eZ Platform (just a random thought, didn't share it with anyone until now ) mentioned package would be a meta-repository dependency, not kernel, because it requires kernel in kernel we would keep core schema and data (for now both core tables and external storage, in the future: each FieldType would provide its own schema file for external storage) - this is a little bit tricky, because I don't want to hardcode vendor/ezpublish-kernel/data/schema.yml path. Someone suggested schema and data files should be bundle Resources, so maybe it's a way to go. AFAIR (last time when I considered this) there's at least one difficulty with that solution: ideally we should get rid of all SQL schemas, including the ones for tests. Running integration tests on a database installed from Yaml schema would be a huge advantage. The solutions: parts related to interpreting Yaml schema should remain in kernel maybe interpreting Yaml schema should be another separate package, independent from kernel and reusable in any projects using Doctrine/DBAL? Other remaining things to do: check if schema contains all changes done in past months (comparing schemas installed the old and new way, both for Postgres and MySQL). test once again if app works (no extensive behat yet, so that's a risk) make behat setup choose PostgreSQL database, controlled by Travis parameters, so we can at least track if installer works.
        Andrzej Longosz made changes -
        Status Development [ 3 ] Specification Done [ 10003 ]
        Assignee Andrzej Longosz [ andrzej.longosz@ez.no ]
        Andrzej Longosz made changes -
        Assignee Andrzej Longosz [ andrzej.longosz@ez.no ]
        Andrzej Longosz made changes -
        Link This issue blocks EZP-29938 [ EZP-29938 ]
        Andrzej Longosz made changes -
        Status Specification Done [ 10003 ] Development [ 3 ]
        Andrzej Longosz made changes -
        Link This issue blocks EZP-29938 [ EZP-29938 ]
        Andrzej Longosz made changes -
        Status Development [ 3 ] Specification Done [ 10003 ]
        Assignee Andrzej Longosz [ andrzej.longosz@ez.no ]
        Andrzej Longosz made changes -
        Assignee Andrzej Longosz [ andrzej.longosz@ez.no ]
        Andrzej Longosz made changes -
        Status Specification Done [ 10003 ] Specification Review [ 10038 ]
        Andrzej Longosz made changes -
        Status Specification Review [ 10038 ] Specification [ 10002 ]
        Hide
        Andrzej Longosz added a comment -

        Closing this one in favor of the fresh Story EZP-29938

        Show
        Andrzej Longosz added a comment - Closing this one in favor of the fresh Story EZP-29938
        Andrzej Longosz made changes -
        Status Specification [ 10002 ] Closed [ 6 ]
        Assignee Andrzej Longosz [ andrzej.longosz@ez.no ]
        Resolution Obsolete [ 8 ]
        Transition Time In Source Status Execution Times Last Executer Last Execution Date
        Open Open Confirmed Confirmed
        24s 1 Gunnstein Lye 21/Sep/15 11:11 AM
        Confirmed Confirmed Backlog Backlog
        6h 41m 1 André Rømcke 21/Sep/15 5:53 PM
        Development Development Backlog Backlog
        7d 1h 16m 1 Andrzej Longosz 12/Oct/16 10:31 AM
        Backlog Backlog Development Development
        518d 20h 28m 2 Andrzej Longosz 28/Feb/17 2:37 PM
        Development Development Development Review Development Review
        51d 23h 58m 1 Andrzej Longosz 21/Apr/17 3:35 PM
        Development Review Development Review Backlog Backlog
        156d 22h 12m 1 Andrzej Longosz 25/Sep/17 1:48 PM
        Backlog Backlog Specification Specification
        36d 2h 49m 1 Roland Benedetti 31/Oct/17 3:37 PM
        Specification Specification Specification Done Specification Done
        32m 37s 1 Andrzej Longosz 31/Oct/17 4:09 PM
        Specification Done Specification Done Development Development
        386d 12h 24m 2 Andrzej Longosz 18/Jan/19 11:45 AM
        Development Development Specification Done Specification Done
        57d 7h 25m 2 Andrzej Longosz 18/Jan/19 12:00 PM
        Specification Done Specification Done Specification Review Specification Review
        26s 1 Andrzej Longosz 18/Jan/19 12:00 PM
        Specification Review Specification Review Specification Specification
        10s 1 Andrzej Longosz 18/Jan/19 12:00 PM
        Specification Specification Closed Closed
        1m 37s 1 Andrzej Longosz 18/Jan/19 12:02 PM

          People

          • Assignee:
            Unassigned
            Reporter:
            Gunnstein Lye
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Time Tracking

              Estimated:
              Original Estimate - 0 minutes
              0m
              Remaining:
              Remaining Estimate - 0 minutes
              0m
              Logged:
              Time Spent - 2 weeks, 5 hours
              2w 5h

                Agile