Details
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
- is required for
-
DDC-269
CLI throws Exception that table already exists if --dump-sql specified
-