コード例 #1
0
 @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");
             }
           })
     },
   };
 }
コード例 #2
0
 @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")
     }
   };
 }