Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-15

Add support to --force option in SchemaTool CLI Task

    Details

    • Type: Task Task
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Invalid
    • Affects Version/s: 2.0-BETA1
    • Fix Version/s: 2.0
    • Component/s: Tools
    • Security Level: All
    • Labels:
      None

      Description

      The way it's currently coded, schema-tool task is enabled --force by default.

      This means that it'll attempt to execute every single SQL command without actually use error checking in SQL statements. One good example is if you attempt to --drop same schema twice. It'll generate a PDOException to you noticing that table does not exist (on second execution).

      Instead of get errors on second attempt, it should gently notify it worked smoothly, except if --force is requested. To support this approach (specifically for --drop, but it should be applied for other types too), it is required to change the way we currently drop tables. Instead of do this:

      DROP TABLE users;
      

      It should try this approach:

      DROP TABLE IF EXISTS users;
      

      The idea is to --drop --force to execute like it is currently and --drop is only execute if exists approach. Same for --create, which means --force will attempt without care of existance in database. --create without --force check for previous existance and drop/create it again.

      Problem is that not all drivers support it natively. Here is what my research gave me so far:

      *SQLite* (3.3+)

      DROP TABLE IF EXISTS users;
      

      *MySQL* (3.22+)

      DROP TABLE IF EXISTS users;
      

      *PostgreSQL*

      DROP TABLE IF EXISTS users;
      

      *MS SQL Server*

      IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users') DROP TABLE users;
      

      *Oracle*

      declare 
        p_exists number :=0;
      begin
        select nvl((select 1 from user_tables where table_name='TABLE1'  and rownum=1),0) into p_exists from dual ;
      if p_exists = 1 then
        execute immediate '...
      

      This is quite complex... maybe we should try:

      IF (SELECT COUNT(1) FROM USER_TABLES WHERE TABLE_NAME = 'users') THEN DROP TABLE users;
      

      Or...

      if ((SELECT COUNT(1) FROM USER_TABLES WHERE TABLE_NAME = 'users') = 1) then execute immediate 'drop table users'; end if;
      

      *DB2*

      IF EXISTS (SELECT name FROM sysibm.systables WHERE name = 'users' ADN type = 'T') THEN
      DROP TABLE users;
      END IF;
      

      *Informix*

      Only information I got so far. Check if the table exists:

      SELECT tabname FROM systables WHERE tabname='users';
      

        Issue Links

          Activity

          Hide
          Jonathan H. Wage added a comment -

          I am not sure if this is really necessary, is it? I made some changes so you can do the following which always works.

          php doctrine schema-tool --drop --create
          

          You always end up with a properly created database when you run that command.

          Show
          Jonathan H. Wage added a comment - I am not sure if this is really necessary, is it? I made some changes so you can do the following which always works. php doctrine schema-tool --drop --create You always end up with a properly created database when you run that command.
          Hide
          Guilherme Blanco added a comment - - edited

          Point is not only with re-creation of DB.
          The issue raises also when you're trying to do same command more than once.
          Example:

          ./doctrine schema-tool --drop && ./doctrine schema-tool --drop
          

          On first execution, it'll correctly remove tables.
          On second execution, it'll generate an issue reporting the first table does not exist.
          Instead of report this situation, it should "lovely" say it worked perfectly.

          Basically, calling --drop twice would execute:

          DROP TABLE IF EXISTS users;
          

          And with new option: --drop --force

          DROP TABLE users;
          

          The same would also apply to --create.
          Without --force, it should only try to create. Considering the presence of --force it should:

          DROP TABLE IF EXISTS users;
          CREATE TABLE users ( ... );
          
          Show
          Guilherme Blanco added a comment - - edited Point is not only with re-creation of DB. The issue raises also when you're trying to do same command more than once. Example: ./doctrine schema-tool --drop && ./doctrine schema-tool --drop On first execution, it'll correctly remove tables. On second execution, it'll generate an issue reporting the first table does not exist. Instead of report this situation, it should "lovely" say it worked perfectly. Basically, calling --drop twice would execute: DROP TABLE IF EXISTS users; And with new option: --drop --force DROP TABLE users; The same would also apply to --create. Without --force, it should only try to create. Considering the presence of --force it should: DROP TABLE IF EXISTS users; CREATE TABLE users ( ... );
          Hide
          Guilherme Blanco added a comment -

          It's not a major issue. But should be fixed in the future.

          Show
          Guilherme Blanco added a comment - It's not a major issue. But should be fixed in the future.
          Hide
          Guilherme Blanco added a comment -

          With the change to Symfony CLI, this option is no longer required.

          Closing the ticket.

          Show
          Guilherme Blanco added a comment - With the change to Symfony CLI, this option is no longer required. Closing the ticket.

            People

            • Assignee:
              Guilherme Blanco
              Reporter:
              Roman S. Borschel
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: