import groovy.grape.Grape
import groovy.sql.Sql

class DatabaseSampleTest extends GroovyTestCase {

    static {
        Grape.grab(group:'org.hsqldb', module:'hsqldb', version:'1.8.0.10', classLoader:DatabaseSampleTest.classLoader.rootLoader)
        Sql.newInstance('jdbc:hsqldb:db/sample', 'sa', '', 'org.hsqldb.jdbcDriver').execute """
            CREATE TABLE emp (
                empno INTEGER PRIMARY KEY,
                ename VARCHAR(10),
                job VARCHAR(9)
            );"""
    }

    def db

    void setUp() {
        db = Sql.newInstance('jdbc:hsqldb:db/sample', 'sa', '', 'org.hsqldb.jdbcDriver')
        db.executeUpdate('delete from emp')
    }

    void testDelete() {

        db.dataSet('emp').with {
            it.add EMPNO:7369, ENAME:'SMITH', JOB:'CLERK'
            it.add EMPNO:7499, ENAME:'ALLEN', JOB:'SALESMAN'
            it.add EMPNO:7521, ENAME:'WARD', JOB:'SALESMAN'
        }

        assert 1 == Sql.newInstance('jdbc:hsqldb:db/sample', 'sa', '', 'org.hsqldb.jdbcDriver')
            .executeUpdate('delete from emp where empno = 7499')

        assert db.rows('select * from emp order by empno') == [
            [EMPNO:7369, ENAME:'SMITH', JOB:'CLERK'],
            [EMPNO:7521, ENAME:'WARD', JOB:'SALESMAN']
        ]
    }

}