@Test public void scriptRunnerShouldBatchPostgresFunctionsAndProcedures() throws Exception { ScriptRunner runner = new ScriptRunner(DatabaseType.POSTGRESQL); Reader reader = new InputStreamReader(getClass().getResourceAsStream("stored-procedure-postgresql.sql")); MockConnection connection = new MockConnection(); runner.execute(connection, reader); List statements = connection.getStatementResultSetHandler().getExecutedStatements(); assertThat(statements.size(), is(4)); assertThat( statements.get(0).toString(), is( equalToIgnoringWhiteSpace( "CREATE FUNCTION getQtyOrders(customerID int) RETURNS int AS $$ DECLARE qty int; BEGIN SELECT COUNT(*) INTO qty FROM Orders WHERE accnum = customerID; RETURN qty; END; $$ LANGUAGE plpgsql"))); assertThat( statements.get(1).toString(), is( equalToIgnoringWhiteSpace( "CREATE FUNCTION one() RETURNS integer AS ' SELECT 1 AS result; ' LANGUAGE SQL"))); assertThat( statements.get(2).toString(), is( equalToIgnoringWhiteSpace( "CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$ BEGIN IF NEW.empname IS NULL THEN RAISE EXCEPTION 'empname cannot be null'; END IF; IF NEW.salary IS NULL THEN RAISE EXCEPTION '% cannot have null salary', NEW.empname; END IF; IF NEW.salary < 0 THEN RAISE EXCEPTION '% cannot have a negative salary', NEW.empname; END IF; NEW.last_date := current_timestamp; NEW.last_user := current_user; RETURN NEW; END; $emp_stamp$ LANGUAGE plpgsql"))); assertThat(statements.get(3).toString(), is(equalToIgnoringWhiteSpace("SELECT one()"))); }
@Test public void scriptRunnerShouldBatchMySQLFunctionsAndProcedures() throws Exception { ScriptRunner runner = new ScriptRunner(DatabaseType.MYSQL); Reader reader = new InputStreamReader(getClass().getResourceAsStream("stored-procedure-mysql.sql")); MockConnection connection = new MockConnection(); runner.execute(connection, reader); List statements = connection.getStatementResultSetHandler().getExecutedStatements(); assertThat(statements.size(), is(3)); assertThat( statements.get(0).toString(), is( equalToIgnoringWhiteSpace( "CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN CONCAT('Hello, ',s,'!')"))); assertThat( statements.get(1).toString(), is( equalToIgnoringWhiteSpace( "CREATE FUNCTION weighted_average (n1 INT, n2 INT, n3 INT, n4 INT) RETURNS INT DETERMINISTIC BEGIN DECLARE avg INT; SET avg = (n1+n2+n3*2+n4*4)/8; RETURN avg; END"))); assertThat( statements.get(2).toString(), is( equalToIgnoringWhiteSpace( "CREATE PROCEDURE payment(payment_amount DECIMAL(6,2), payment_seller_id INT) BEGIN DECLARE n DECIMAL(6,2); SET n = payment_amount - 1.00; INSERT INTO Moneys VALUES (n, CURRENT_DATE); IF payment_amount > 1.00 THEN UPDATE Sellers SET commission = commission + 1.00 WHERE seller_id = payment_seller_id; END IF; END"))); }
@Test public void scriptRunnerShouldHandleComplexCommands() throws Exception { ScriptRunner runner = new ScriptRunner(DatabaseType.MYSQL); Reader reader = new InputStreamReader(getClass().getResourceAsStream("complex.sql")); MockConnection connection = new MockConnection(); runner.execute(connection, reader); List statements = connection.getStatementResultSetHandler().getExecutedStatements(); String expectedSql = "update dav_file set parent = ( select id from ( select id from dav_file where name = '__SITE_PROTECTED__' ) as x )" + " where ( name = 'templates' and parent is null )" + " or ( name = 'velocity' and parent is null )" + " or ( name = 'tags' and parent is null )" + " or ( name = 'ctd' and parent is null )"; assertThat(statements.size(), is(1)); assertThat(statements.get(0).toString(), is(equalToIgnoringWhiteSpace(expectedSql))); }
@Test public void scriptRunnerShouldBatchSimpleCommands() throws Exception { ScriptRunner runner = new ScriptRunner(DatabaseType.UNKNOWN); Reader reader = new InputStreamReader(getClass().getResourceAsStream("simple.sql")); MockConnection connection = new MockConnection(); runner.execute(connection, reader); List statements = connection.getStatementResultSetHandler().getExecutedStatements(); assertThat(statements.size(), is(2)); assertThat( statements.get(0).toString(), is( equalToIgnoringWhiteSpace( "create table users ( username varchar not null, password varchar not null )"))); assertThat( statements.get(1).toString(), is( equalToIgnoringWhiteSpace( "alter table users add index (username), add unique (username)"))); }
@Test public void scriptRunnerShouldUseTheSameDelimiterUntilExplicitlyChanged() throws Exception { ScriptRunner runner = new ScriptRunner(DatabaseType.UNKNOWN); Reader reader = new InputStreamReader(getClass().getResourceAsStream("function-mysql.sql")); MockConnection connection = new MockConnection(); runner.execute(connection, reader); List statements = connection.getStatementResultSetHandler().getExecutedStatements(); assertThat(statements.size(), is(3)); assertThat( statements.get(0).toString(), is(equalToIgnoringWhiteSpace("DROP FUNCTION IF EXISTS simpleFunction"))); assertThat( statements.get(1).toString(), is( equalToIgnoringWhiteSpace( "CREATE FUNCTION simpleFunction() RETURNS varchar(100) READS SQL DATA begin declare message varchar(100) default 'Hello Word'; return message; end"))); assertThat( statements.get(2).toString(), is(equalToIgnoringWhiteSpace("select simpleFunction()"))); }
@Test public void scriptRunnerShouldExecuteLastStatementWhenDelimiterIsMissing() throws Exception { ScriptRunner runner = new ScriptRunner(DatabaseType.UNKNOWN); Reader reader = new InputStreamReader(getClass().getResourceAsStream("missing-last-deliminator.sql")); MockConnection connection = new MockConnection(); runner.execute(connection, reader); List statements = connection.getStatementResultSetHandler().getExecutedStatements(); assertThat(statements.size(), is(2)); assertThat( statements.get(0).toString(), is( equalToIgnoringWhiteSpace( "create table users ( username varchar not null, password varchar not null )"))); assertThat( statements.get(1).toString(), is( equalToIgnoringWhiteSpace( "create table roles ( name varchar not null unique, description text not null )"))); }