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
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Priority | Major [ 3 ] | Minor [ 4 ] |
| 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: {code:sql} DROP TABLE users; {code} It should try this approach: {code:sql} DROP TABLE IF EXISTS users; {code} 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+) {code:sql} DROP TABLE IF EXISTS users; {code} **MySQL** (3.22+) {code:sql} DROP TABLE IF EXISTS users; {code} **PostgreSQL** {code:sql} DROP TABLE IF EXISTS users; {code} **MS SQL Server** {code:sql} IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users') DROP TABLE users; {code} **Oracle** {code:sql} 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 '... {code} This is quite complex... maybe we should try: {code:sql} IF (SELECT COUNT(1) FROM USER_TABLES WHERE TABLE_NAME = 'users') THEN DROP TABLE users; {code} Or... {code:sql} if ((SELECT COUNT(1) FROM USER_TABLES WHERE TABLE_NAME = 'users') = 1) then execute immediate 'drop table users'; end if; {code} **DB2** {code:sql} IF EXISTS (SELECT name FROM sysibm.systables WHERE name = 'users' ADN type = 'T') THEN DROP TABLE users; END IF; {code} **Informix** Only information I got so far. Check if the table exists: {code:sql} SELECT tabname FROM systables WHERE tabname='users'; {code} |
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: {code:sql} DROP TABLE users; {code} It should try this approach: {code:sql} DROP TABLE IF EXISTS users; {code} 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+) {code:sql} DROP TABLE IF EXISTS users; {code} **MySQL** (3.22+) {code:sql} DROP TABLE IF EXISTS users; {code} **PostgreSQL** {code:sql} DROP TABLE IF EXISTS users; {code} **MS SQL Server** {code:sql} IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users') DROP TABLE users; {code} **Oracle** {code:sql} 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 '... {code} This is quite complex... maybe we should try: {code:sql} IF (SELECT COUNT(1) FROM USER_TABLES WHERE TABLE_NAME = 'users') THEN DROP TABLE users; {code} Or... {code:sql} if ((SELECT COUNT(1) FROM USER_TABLES WHERE TABLE_NAME = 'users') = 1) then execute immediate 'drop table users'; end if; {code} **DB2** {code:sql} IF EXISTS (SELECT name FROM sysibm.systables WHERE name = 'users' ADN type = 'T') THEN DROP TABLE users; END IF; {code} **Informix** Only information I got so far. Check if the table exists: {code:sql} SELECT tabname FROM systables WHERE tabname='users'; {code} |
| Fix Version/s | 2.0 [ 10021 ] | |
| Fix Version/s | 2.0-BETA1 [ 10030 ] |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Resolution | Invalid [ 6 ] |
| Workflow | jira [ 10065 ] | jira-feedback [ 15414 ] |
| Workflow | jira-feedback [ 15414 ] | jira-feedback2 [ 17278 ] |
| Workflow | jira-feedback2 [ 17278 ] | jira-feedback3 [ 19535 ] |