Dz4s5tMF

1@GrabConfig(systemClassLoader=true)
2@Grab('postgresql:postgresql:9.1-901.jdbc4')
3
4def config = [
5    'url' :     System.getProperty('spring.datasource.url', 'jdbc:postgresql://docker:5432/vivascore'),
6    'username': System.getProperty('spring.datasource.username', 'vivascore'),
7    'password': System.getProperty('spring.datasource.password', ''),
8    'driver':   System.getProperty('spring.datasource.driver-class-name', 'org.postgresql.Driver')
9]
10
11def feedbackScore = [UNSATISFIED: 1, NEUTRAL: 3, SATISFIED: 5]
12
13def sql = groovy.sql.Sql.newInstance(config.url, config.username, config.password, config.driver)
14
15sql.execute 'DROP TABLE IF EXISTS LEAD_FEEDBACK_MIGRATE;'
16
17sql.execute '''
18    CREATE TABLE LEAD_FEEDBACK_MIGRATE (
19      message_id BIGINT PRIMARY KEY,
20      publisher_id BIGINT NOT NULL,
21      score SMALLINT,
22      created_at TIMESTAMP WITH TIME ZONE);
23'''
24
25sql.execute 'CREATE INDEX lead_feedback_publisher_id_index ON LEAD_FEEDBACK_MIGRATE (publisher_id);'
26sql.execute 'CREATE INDEX lead_feedback_created_at_index ON LEAD_FEEDBACK_MIGRATE (created_at);'
27
28sql.eachRow('SELECT * FROM LEAD_FEEDBACK') {
29    def newScore = feedbackScore[it.score]
30    sql.execute """
31        INSERT INTO LEAD_FEEDBACK_MIGRATE (message_id, publisher_id, score, created_at) 
32            VALUES ($it.message_id, $it.publisher_id, $newScore, $it.created_at);
33    """
34}
35
36sql.execute 'DROP TABLE IF EXISTS LEAD_FEEDBACK;'
37
38sql.execute 'ALTER TABLE LEAD_FEEDBACK_MIGRATE RENAME TO LEAD_FEEDBACK'
39
40println "ok"