package demo

import org.springframework.dao.DataIntegrityViolationException;

import grails.plugin.spock.IntegrationSpec;

class MsSqlConstraintsSpec extends IntegrationSpec {

    def "Inserting multiple rows containing null value into unique column succeeds"() {

        when: "Multiple nulls are inserted"
        new Teacher(name:"Smith", socialSecurityNumber:"soc 1", employeeId: "emp 1").save(flush:true)
        new Teacher(name:"Johnson", socialSecurityNumber:"soc 2", employeeId: "emp 2").save(flush:true)

        // now we have 2 rows where studentId is null and driversLicenseNumber is null
        // that would have been impossible using MS SQL Server unique constraints
        // but gracefully accepted by unique filtered indexes ignoring nulls

        then: "They are gracefully accepted by unique indexes ignoring nulls"
        notThrown(Exception)
    }

    def "Check Exception type on constraint"() {

        when: "Unique CONSTRAINT is violated bypassing Grails validation"
        new Teacher(name:"Smith", socialSecurityNumber:"soc 1", employeeId: "emp 1").save(flush:true)
        new Teacher(name:"Williams", socialSecurityNumber:"soc 1", employeeId: "emp 2").save(flush:true, validate:false)

        then: "DataIntegrityViolationException is thrown"
        thrown(DataIntegrityViolationException)
    }

    def "Check Exception type on unique index"() {

        when: "Unique INDEX is violated bypassing Grails validation"
        new Teacher(name:"Smith", socialSecurityNumber:"soc 1", employeeId: "emp 1").save(flush:true)
        new Teacher(name:"Jones", socialSecurityNumber:"soc 3", employeeId: "emp 1").save(flush:true, validate:false)

        then: "DataIntegrityViolationException is thrown as well"
        thrown(DataIntegrityViolationException)
    }

    def "Check Exception type on multiple column unique index"() {

        when: "Unique INDEX covering multiple coluumns is violated bypassing Grails validation"
        new Student(name:"Brown", firstName:"Bill", socialSecurityNumber:"soc 1", studentId: "stud 1").save(flush:true)
        new Student(name:"Brown", firstName:"Bill", socialSecurityNumber:"soc 2", studentId: "stud 2").save(flush:true, validate:false)

        then: "DataIntegrityViolationException is thrown as well"
        thrown(DataIntegrityViolationException)
    }
}