@SuppressWarnings("serial")
@DataProvider
public Object[][] dropViewStatements() {
return new Object[][] {
new Object[] {"drop_view 'some_table'", SQLStatementFactory.create("DROP VIEW some_table")},
new Object[] {
"drop_view oracle: 'some_table', ms_sql: 'different_table', postgres: 'ps_table'",
SQLStatementFactory.create(
new HashMap<String, String>() {
{
put("ms_sql", "DROP VIEW different_table");
put("oracle", "DROP VIEW some_table");
put("postgres", "DROP VIEW ps_table");
}
})
},
};
}
@SuppressWarnings("serial")
@DataProvider
public Object[][] alterStatements() {
return new Object[][] {
new Object[] {
"alter 't' add 'a' storing BOOL",
SQLStatementFactory.create(
new HashMap<String, String>() {
{
put("ms_sql", "ALTER TABLE t ADD a TINYINT DEFAULT 0 NOT NULL");
put("oracle", "ALTER TABLE t ADD a NUMBER(1,0) DEFAULT 0 NOT NULL");
put("postgres", "ALTER TABLE t ADD a BOOLEAN DEFAULT FALSE NOT NULL");
}
})
},
new Object[] {
"alter 't' add 'a' storing BOOL allowing null",
SQLStatementFactory.create(
new HashMap<String, String>() {
{
put("ms_sql", "ALTER TABLE t ADD a TINYINT DEFAULT 0 NULL");
put("oracle", "ALTER TABLE t ADD a NUMBER(1,0) DEFAULT 0 NULL");
put("postgres", "ALTER TABLE t ADD a BOOLEAN DEFAULT FALSE NULL");
}
})
},
new Object[] {
"alter 't' add 'a' storing DATE",
SQLStatementFactory.create(
new HashMap<String, String>() {
{
put("ms_sql", "ALTER TABLE t ADD a DATETIME DEFAULT GETDATE() NOT NULL");
put("oracle", "ALTER TABLE t ADD a TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL");
put("postgres", "ALTER TABLE t ADD a TIMESTAMP (6) DEFAULT NOW() NOT NULL");
}
})
},
new Object[] {
"alter 't' add 'a' storing NVARCHAR(16)",
SQLStatementFactory.create(
new HashMap<String, String>() {
{
put("ms_sql", "ALTER TABLE t ADD a NVARCHAR(16) NOT NULL");
put("oracle", "ALTER TABLE t ADD a NVARCHAR2(16) NOT NULL");
put("postgres", "ALTER TABLE t ADD a VARCHAR(16) NOT NULL");
}
})
},
new Object[] {
"alter 't' add 'a' storing NVARCHAR(16) allowing null",
SQLStatementFactory.create(
new HashMap<String, String>() {
{
put("ms_sql", "ALTER TABLE t ADD a NVARCHAR(16) NULL");
put("oracle", "ALTER TABLE t ADD a NVARCHAR2(16) NULL");
put("postgres", "ALTER TABLE t ADD a VARCHAR(16) NULL");
}
})
},
new Object[] {
"alter 't' add 'a' storing VARCHAR(16)",
SQLStatementFactory.create(
new HashMap<String, String>() {
{
put("ms_sql", "ALTER TABLE t ADD a VARCHAR(16) NOT NULL");
put("oracle", "ALTER TABLE t ADD a VARCHAR2(16) NOT NULL");
put("postgres", "ALTER TABLE t ADD a VARCHAR(16) NOT NULL");
}
})
},
new Object[] {
"alter 't' add 'a' storing VARCHAR(16) allowing null",
SQLStatementFactory.create(
new HashMap<String, String>() {
{
put("ms_sql", "ALTER TABLE t ADD a VARCHAR(16) NULL");
put("oracle", "ALTER TABLE t ADD a VARCHAR2(16) NULL");
put("postgres", "ALTER TABLE t ADD a VARCHAR(16) NULL");
}
})
},
new Object[] {
"alter 't' add 'a' storing 'char(1)'",
SQLStatementFactory.create("ALTER TABLE t ADD a char(1)")
},
new Object[] {
"alter 't' drop 'c'",
SQLStatementFactory.create(
new HashMap<String, String>() {
{
put(
"ms_sql",
"DECLARE @DefaultConstraintName nvarchar(200)\n"
+ " SELECT @DefaultConstraintName = Name FROM sys.default_constraints\n"
+ " WHERE PARENT_OBJECT_ID = OBJECT_ID('t')\n"
+ " AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns\n"
+ " WHERE NAME = N'c'\n"
+ " AND object_id = OBJECT_ID(N't'))\n"
+ " IF @DefaultConstraintName IS NOT NULL\n"
+ " EXEC('ALTER TABLE t DROP CONSTRAINT ' + @DefaultConstraintName)\n"
+ " WHILE 1=1\n"
+ " BEGIN\n"
+ " DECLARE @ConstraintName nvarchar(200)\n"
+ " SET @ConstraintName = (SELECT TOP 1 constraint_name FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE\n"
+ " WHERE TABLE_NAME='t' AND COLUMN_NAME='c')\n"
+ " IF @ConstraintName IS NULL BREAK\n"
+ " EXEC('ALTER TABLE t DROP CONSTRAINT ' + @ConstraintName)\n"
+ " END\n"
+ " ALTER TABLE t DROP COLUMN c");
put("default", "ALTER TABLE t DROP COLUMN c");
}
})
},
new Object[] {
"alter 't' rename 'a' to 'b'",
SQLStatementFactory.create(
new HashMap<String, String>() {
{
put("ms_sql", "EXEC sp_rename 't.[a]', 'b', 'COLUMN'");
put("oracle", "ALTER TABLE t RENAME COLUMN a TO b");
put("postgres", "ALTER TABLE t RENAME COLUMN a TO b");
}
})
},
new Object[] {
"alter 't' retype 'a' to VARCHAR(128)",
SQLStatementFactory.create(
new HashMap<String, String>() {
{
put("ms_sql", "ALTER TABLE t ALTER COLUMN a VARCHAR(128) NOT NULL");
put(
"oracle",
"\nDECLARE\n"
+ "l_nullable VARCHAR(1);\n"
+ "\n"
+ "BEGIN\n"
+ " SELECT nullable INTO l_nullable FROM user_tab_columns WHERE table_name = UPPER('t') AND column_name = UPPER('a');\n"
+ "\n"
+ " IF l_nullable = 'N' THEN\n"
+ " EXECUTE IMMEDIATE 'ALTER TABLE t MODIFY (a VARCHAR2(128) )';\n"
+ " END IF;\n"
+ " IF l_nullable = 'Y' THEN\n"
+ " EXECUTE IMMEDIATE 'ALTER TABLE t MODIFY (a VARCHAR2(128) NOT NULL)';\n"
+ " END IF;\n"
+ "END;\n");
put(
"postgres",
"ALTER TABLE t ALTER COLUMN a TYPE VARCHAR(128);"
+ "ALTER TABLE t ALTER COLUMN a SET NOT NULL");
}
})
},
new Object[] {
"alter 't' retype 'a' to VARCHAR(128) allowing null",
SQLStatementFactory.create(
new HashMap<String, String>() {
{
put("ms_sql", "ALTER TABLE t ALTER COLUMN a VARCHAR(128) NULL");
put(
"oracle",
"\nDECLARE\n"
+ "l_nullable VARCHAR(1);\n"
+ "\n"
+ "BEGIN\n"
+ " SELECT nullable INTO l_nullable FROM user_tab_columns WHERE table_name = UPPER('t') AND column_name = UPPER('a');\n"
+ "\n"
+ " IF l_nullable = 'N' THEN\n"
+ " EXECUTE IMMEDIATE 'ALTER TABLE t MODIFY (a VARCHAR2(128) NULL)';\n"
+ " END IF;\n"
+ " IF l_nullable = 'Y' THEN\n"
+ " EXECUTE IMMEDIATE 'ALTER TABLE t MODIFY (a VARCHAR2(128) )';\n"
+ " END IF;\n"
+ "END;\n");
put(
"postgres",
"ALTER TABLE t ALTER COLUMN a TYPE VARCHAR(128);"
+ "ALTER TABLE t ALTER COLUMN a DROP NOT NULL");
}
})
},
new Object[] {
"alter 't' retype 'a' to INTEGER",
SQLStatementFactory.create(
new HashMap<String, String>() {
{
put("ms_sql", "ALTER TABLE t ALTER COLUMN a INT NOT NULL");
put(
"oracle",
"\nDECLARE\n"
+ "l_nullable VARCHAR(1);\n"
+ "\n"
+ "BEGIN\n"
+ " SELECT nullable INTO l_nullable FROM user_tab_columns WHERE table_name = UPPER('t') AND column_name = UPPER('a');\n"
+ "\n"
+ " IF l_nullable = 'N' THEN\n"
+ " EXECUTE IMMEDIATE 'ALTER TABLE t MODIFY (a NUMBER(10,0) )';\n"
+ " END IF;\n"
+ " IF l_nullable = 'Y' THEN\n"
+ " EXECUTE IMMEDIATE 'ALTER TABLE t MODIFY (a NUMBER(10,0) NOT NULL)';\n"
+ " END IF;\n"
+ "END;\n");
put(
"postgres",
"ALTER TABLE t ALTER COLUMN a TYPE INT;"
+ "ALTER TABLE t ALTER COLUMN a SET NOT NULL");
}
})
},
new Object[] {
"alter 't' retype 'a' to INTEGER allowing null",
SQLStatementFactory.create(
new HashMap<String, String>() {
{
put("ms_sql", "ALTER TABLE t ALTER COLUMN a INT NULL");
put(
"oracle",
"\nDECLARE\n"
+ "l_nullable VARCHAR(1);\n"
+ "\n"
+ "BEGIN\n"
+ " SELECT nullable INTO l_nullable FROM user_tab_columns WHERE table_name = UPPER('t') AND column_name = UPPER('a');\n"
+ "\n"
+ " IF l_nullable = 'N' THEN\n"
+ " EXECUTE IMMEDIATE 'ALTER TABLE t MODIFY (a NUMBER(10,0) NULL)';\n"
+ " END IF;\n"
+ " IF l_nullable = 'Y' THEN\n"
+ " EXECUTE IMMEDIATE 'ALTER TABLE t MODIFY (a NUMBER(10,0) )';\n"
+ " END IF;\n"
+ "END;\n");
put(
"postgres",
"ALTER TABLE t ALTER COLUMN a TYPE INT;"
+ "ALTER TABLE t ALTER COLUMN a DROP NOT NULL");
}
})
},
new Object[] {
"alter 't' add_primary 'a' and 'b' and 'c'",
SQLStatementFactory.create(
"ALTER TABLE t ADD CONSTRAINT pk_t_a_b_c PRIMARY KEY ( a, b, c )")
},
new Object[] {
"alter 't' add_unique 'a' and 'b' and 'c'",
SQLStatementFactory.create("ALTER TABLE t ADD CONSTRAINT uq_t_a_b_c UNIQUE ( a, b, c )")
},
new Object[] {
"alter 't' drop_primary 'a' and 'b' and 'c'",
SQLStatementFactory.create("ALTER TABLE t DROP CONSTRAINT pk_t_a_b_c")
},
new Object[] {
"alter 't' drop_unique 'a' and 'b' and 'c'",
SQLStatementFactory.create("ALTER TABLE t DROP CONSTRAINT uq_t_a_b_c")
}
};
}
