DDC-15: Add support to --force option in SchemaTool CLI Task #2128

Closed
doctrinebot opened this Issue Sep 15, 2009 · 6 comments

2 participants

@doctrinebot

Jira issue originally created by user romanb:

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';
@doctrinebot

Comment created by @jwage:

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.

@doctrinebot

Comment created by @guilhermeblanco:

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 ( ... );
@doctrinebot

Comment created by @guilhermeblanco:

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

@doctrinebot

Comment created by @guilhermeblanco:

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

Closing the ticket.

@doctrinebot

Issue was closed with resolution "Invalid"

@doctrinebot doctrinebot added this to the 2.0 milestone Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment