DDevelopment & Design

Migracja strony kodowej

Ostatnio wystąpił problem z pewną bazą deweloperską, w której nie działały polskie znaki. Sprawdziłem parametry bazy poprzez zapytanie

select d.parameter Dparameter,
       d.value     Dvalue,
       i.value     Ivalue,
       s.value     Svalue
  from nls_database_parameters d, nls_instance_parameters i,
       nls_session_parameters s
 where d.parameter = i.parameter (+)
   and d.parameter = s.parameter (+)
order by 1

ze strony: http://www.databasejournal.com/features/oracle/article.php/3485216/The-Globalization-of-Language-in-Oracle—National-Language-Support.htm

Wartość parametru NLS_CHARACTERSET wskazała, że ustawiona jest zachodnioeuropejska strona kodowa WE8ISO8859P1 czyli ISO 8859-1. Konieczne było jej zmienienie albo na wschodnioeuropejską EE8ISO8859P2 czyli ISO 8859-2, albo na Unicode’ową AL32UTF8. W celu dokonania migracji skorzystałem z instrukcji ze strony http://download.oracle.com/docs/cd/B10500_01/server.920/a96529/ch10.htm

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET AL32UTF8;
SHUTDOWN IMMEDIATE;
STARTUP;

Wszystkie te operacje musiałem robić na maszynie, na której stała baza. SQL*Plusa uruchomiłem komendą

sqlplus / as sysdba

Pojawiły się dwa problemy wynikłe z faktu, że podczas migracji strony kodowej Oracle nie modyfikuje zapisanych danych a jedynie zmienia swoje ustawienia:

  1. Kodowania WE8ISO8859P1 i EE8ISO8859P2 zawsze używają jednego bajtu na znak podczas gdy AL32UTF8 używa od 1 do 4 bajtów. Jednak
    dotyczy to tylko typów CHAR i VARCHAR. W przypadku ustawionego kodowania wielobajtowego stosowane jest w CLOBach 2-bajtowe kodowanie. Zmiana
    strony kodowej na AL32UTF8 spowodowałaby chaos, ponieważ każda para znaków ISO 8859-1 byłaby traktowana jako jeden znak Unicode. Z tego powodu taka
    konwersja jest niemożliwa. Jedynym sposobem jest migracja strony kodowej poprzez import i export danych, ale ta droga nie została
    udokumentowana.
  2. Możliwe jest tylko zmigrowanie na stronę kodową będącą ścisłym nadzbiorem dotychczasowej. Ten warunek oznacza, że dla każdego ciągu bitów dozwolonego w dotychczasowym kodowaniu – ten ciąg bitów mapuje się na ten sam znak w nowym kodowaniu. 7-bitowe kodowanie US7ASCII spełnia ten warunek w stosunku do EE8ISO8859P2 i AL32UTF8, ale WE8ISO8859P1 już nie. Na przykład æ (ae) ma kod E6 w WE8ISO8859P1, ale w AL32UTF8 bajt E6 nie występuje samodzielnie podczas gdy w EE8ISO8859P2 znaku æ nie ma a bajt E6 to ć
    Nie była możliwa migracja z WE8ISO8859P1 na EE8ISO8859P2, więc zmieniłem jako SYS ustawienia Oracle’a:
update sys.props$ set VALUE$='US7ASCII' where NAME='NLS_CHARACTERSET'

Nie polecam tej metody na produkcyjnych bazach, ponieważ zmienianie systemowych ustawień bywa niebezpieczne. Miałem jednak backup bazy, więc zaryzykowałem. Po zmianie wartości w tabeli komenda

ALTER DATABASE CHARACTER SET EE8ISO8859P2;

przeszła bez problemu. Jeśli byłyby jakieś znaki o kodach powyżej 127 to byłyby one wyświetlane jako inne w wielu wypadkach znaki, ale nie było takich przypadków.

— Previous article

Subtelny feature konstrukcji CONNECT BY

Next article —

Dostęp do MS SQL Servera z poziomu Oracle-a

You May Also Like
DDevelopment & Design

Spock basics

Spock (homepage) is like its authors say 'testing and specification framework'. Spock combines very elegant and natural syntax with the powerful capabilities. And what is most important it is easy to use.

One note at the very beginning: I assume that you are already familiar with principles of Test Driven Development and you know how to use testing framework like for example JUnit.

So how can I start?


Writing spock specifications is very easy. We need basic configuration of Spock and Groovy dependencies (if you are using mavenized project with Eclipse look to my previous post: Spock, Java and Maven). Once we have everything set up and running smooth we can write our first specs (spec or specification is equivalent for test class in other frameworks like JUnit of TestNG).

What is great with Spock is fact that we can use it to test both Groovy projects and pure Java projects or even mixed projects.


Let's go!


Every spec class must inherit from spock.lang.Specification class. Only then test runner will recognize it as test class and start tests. We will write few specs for this simple class: User class and few tests not connected with this particular class.

We start with defining our class:
import spock.lang.*

class UserSpec extends Specification {

}
Now we can proceed to defining test fixtures and test methods.

All activites we want to perform before each test method, are to be put in def setup() {...} method and everything we want to be run after each test should be put in def cleanup() {...} method (they are equivalents for JUnit methods with @Before and @After annotations).

It can look like this:
class UserSpec extends Specification {
    User user
    Document document

    def setup() {
        user = new User()
        document = DocumentTestFactory.createDocumentWithTitle("doc1")
    }

    def cleanup() {

    }
}
Of course we can use field initialization for instantiating test objects:
class UserSpec extends Specification {
    User user = new User()
    Document document = DocumentTestFactory.createDocumentWithTitle("doc1")

    def setup() {

    }

    def cleanup() {

    }
}

What is more readable or preferred? It is just a matter of taste because according to Spock docs behaviour is the same in these two cases.

It is worth mentioning that JUnit @BeforeClass/@AfterClass are also present in Spock as def setupSpec() {...} and def cleanupSpec() {...}. They will be runned before first test and after last test method.


First tests


In Spock every method in specification class, expect setup/cleanup, is treated by runner as a test method (unless you annotate it with @Ignore).

Very interesting feature of Spock and Groovy is ability to name methods with full sentences just like regular strings:
class UserSpec extends Specification {
    // ...

    def "should assign coment to user"() {
        // ...
    }
}
With such naming convention we can write real specification and include details about specified behaviour in method name, what is very convenient when reading test reports and analyzing errors.

Test method (also called feature method) is logically divided into few blocks, each with its own purpose. Blocks are defined like labels in Java (but they are transformed with Groovy AST transform features) and some of them must be put in code in specific order.

Most basic and common schema for Spock test is:
class UserSpec extends Specification {
    // ...

    def "should assign coment to user"() {
        given:
            // do initialization of test objects
        when:
            // perform actions to be tested
        then:
            // collect and analyze results
    }
}

But there are more blocks like:
  • setup
  • expect
  • where
  • cleanup
In next section I am going to describe each block shortly with little examples.

given block

This block is used to setup test objects and their state. It has to be first block in test and cannot be repeated. Below is little example how can it be used:
class UserSpec extends Specification {
    // ...

    def "should add project to user and mark user as project's owner"() {
        given:
            User user = new User()
            Project project = ProjectTestFactory.createProjectWithName("simple project")
        // ...
    }
}

In this code given block contains initialization of test objects and nothing more. We create simple user without any specified attributes and project with given name. In case when some of these objects could be reused in more feature methods, it could be worth putting initialization in setup method.

when and then blocks

When block contains action we want to test (Spock documentation calls it 'stimulus'). This block always occurs in pair with then block, where we are verifying response for satisfying certain conditions. Assume we have this simple test case:
class UserSpec extends Specification {
    // ...

    def "should assign user to comment when adding comment to user"() {
        given:
            User user = new User()
            Comment comment = new Comment()
        when:
            user.addComment(comment)
        then:
            comment.getUserWhoCreatedComment().equals(user)
    }

    // ...
}

In when block there is a call of tested method and nothing more. After we are sure our action was performed, we can check for desired conditions in then block.

Then block is very well structured and its every line is treated by Spock as boolean statement. That means, Spock expects that we write instructions containing comparisons and expressions returning true or false, so we can create then block with such statements:
user.getName() == "John"
user.getAge() == 40
!user.isEnabled()
Each of lines will be treated as single assertion and will be evaluated by Spock.

Sometimes we expect that our method throws an exception under given circumstances. We can write test for it with use of thrown method:
class CommentSpec extends Specification {
    def "should throw exception when adding null document to comment"() {
        given:
            Comment comment = new Comment()
        when:
            comment.setCommentedDocument(null)
        then:
            thrown(RuntimeException)
    }
}

In this test we want to make sure that passing incorrect parameters is correctly handled by tested method and that method throws an exception in response. In case you want to be certain that method does not throw particular exception, simply use notThrown method.


expect block

Expect block is primarily used when we do not want to separate when and then blocks because it is unnatural. It is especially useful for simple test (and according to TDD rules all test should be simple and short) with only one condition to check, like in this example (it is simple but should show the idea):
def "should create user with given name"() {
    given:
        User user = UserTestFactory.createUser("john doe")
    expect:
        user.getName() == "john doe"
}



More blocks!


That were very simple tests with standard Spock test layout and canonical divide into given/when/then parts. But Spock offers more possibilities in writing tests and provides more blocks.


setup/cleanup blocks

These two blocks have the very same functionality as the def setup and def cleanup methods in specification. They allow to perform some actions before test and after test. But unlike these methods (which are shared between all tests) blocks work only in methods they are defined in. 


where - easy way to create readable parameterized tests

Very often when we create unit tests there is a need to "feed" them with sample data to test various cases and border values. With Spock this task is very easy and straighforward. To provide test data to feature method, we need to use where block. Let's take a look at little the piece of code:

def "should successfully validate emails with valid syntax"() {
    expect:
        emailValidator.validate(email) == true
    where:
        email }

In this example, Spock creates variable called email which is used when calling method being tested. Internally feature method is called once, but framework iterates over given values and calls expect/when block as many times as there are values (however, if we use @Unroll annotation Spock can create separate run for each of given values, more about it in one of next examples).

Now, lets assume that we want our feature method to test both successful and failure validations. To achieve that goal we can create few parameterized variables for both input parameter and expected result. Here is a little example:

def "should perform validation of email addresses"() {
    expect:
        emailValidator.validate(email) == result
    where:
        email         result }
Well, it looks nice, but Spock can do much better. It offers tabular format of defining parameters for test what is much more readable and natural. Lets take a look:
def "should perform validation of email addresses"() {
    expect:
        emailValidator.validate(email) == result
    where:
        email           | result
        "WTF"           | false
        "@domain"       | false
        "foo@bar.com"   | true
        "a@test"        | false
}
In this code, each column of our "table" is treated as a separate variable and rows are values for subsequent test iterations.

Another useful feature of Spock during parameterizing test is its ability to "unroll" each parameterized test. Feature method from previous example could be defined as (the body stays the same, so I do not repeat it):
@Unroll("should validate email #email")
def "should perform validation of email addresses"() {
    // ...
}
With that annotation, Spock generate few methods each with its own name and run them separately. We can use symbols from where blocks in @Unroll argument by preceding it with '#' sign what is a signal to Spock to use it in generated method name.


What next?


Well, that was just quick and short journey  through Spock and its capabilities. However, with that basic tutorial you are ready to write many unit tests. In one of my future posts I am going to describe more features of Spock focusing especially on its mocking abilities.