D-Net

package eu.dnetlib.data.claims;

import eu.dnetlib.data.claims.entity.Claim;

import eu.dnetlib.data.claims.entity.Result;

import eu.dnetlib.data.claims.handler.*;

import eu.dnetlib.data.claims.sql.SQLStoreException;

import eu.dnetlib.data.claims.utils.*;

import org.apache.log4j.BasicConfigurator;

import org.apache.log4j.Level;

import org.apache.log4j.Logger;

import org.junit.Before;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.context.ApplicationContext;

import org.springframework.context.annotation.PropertySource;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import org.springframework.test.context.ContextConfiguration;

import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.io.BufferedWriter;

import java.io.FileWriter;

import java.io.PrintWriter;

import java.util.ArrayList;

import java.util.List;

import static junit.framework.Assert.assertNotNull;

@RunWith(SpringJUnit4ClassRunner.class)

@ContextConfiguration(locations = {"springContext-claims.xml"})

@PropertySource("springContext-claims.properties")

public class CopyFromBetaTest {

	private Logger log = Logger.getLogger(this.getClass());

	ClaimHandler claimHandler = null;

    FetchClaimHandler fetchClaimHandler = null;

    private PrintWriter out;

    @Before

	public void init() throws Exception {

		BasicConfigurator.configure();

        ApplicationContext context = new ClassPathXmlApplicationContext("eu/dnetlib/data/claims/springContext-claims.xml");

        claimHandler = context.getBean(ClaimHandler.class);

        fetchClaimHandler = context.getBean(FetchClaimHandler.class);

	}

    @Test

    public void CopyFromBetaToProduction(){

        log.setLevel(Level.ERROR);

        QueryGenerator q = new QueryGenerator();

        System.out.println(q.generateCreateClaimTablesQuery());

        try {

            fetchClaimHandler.getQueryGenerator().setMigrationTable("claims_beta");

            ArrayList<String> communities = new ArrayList<>();

            communities.add("ee");

//            communities.add("mes");

//            communities.add("fam");

//            communities.add("ni");

//            communities.add("dh-ch");

//            communities.add("covid-19");

            String startDate = "2018-03-01";

            String endDate = "2020-11-22";

            fetchClaimHandler.getSqlDAO().getSqlStore().setDbUrl("jdbc:postgresql://scoobydoo.di.uoa.gr:5432/claims_beta");

            List<Claim> claims = fetchClaimHandler.fetchClaimsByDateForDashboards(startDate,endDate,  null,null,true, communities);

            System.out.println("Beta claims: ");

            System.out.println(claims.size());

            claimHandler.getSqlDAO().getSqlStore().setDbUrl("jdbc:postgresql://scoobydoo.di.uoa.gr:5432/movedb");

            out = new PrintWriter(new BufferedWriter(new FileWriter("/home/argirok/claims_migratedFromBeta_record_paths.txt", true)));

            for (Claim claim : claims) {

                if(out!=null) {

                    if(!claim.getTargetType().equals("project") && !claim.getTargetType().equals("context")){

                        System.out.println(((Result)claim.getTarget()).getRecordPath());

                        out.println("cp beta_records/" + ((Result)claim.getTarget()).getRecordPath() + " records/"+((Result)claim.getTarget()).getRecordPath());

                    }

                    if(!claim.getSourceType().equals("project") && !claim.getSourceType().equals("context")){

                        System.out.println(((Result)claim.getSource()).getRecordPath());

                        out.println("cp beta_records/" + ((Result)claim.getSource()).getRecordPath() + " records/"+((Result)claim.getSource()).getRecordPath());

                    }

                }

                System.out.println(claim.getId());

//                claimHandler.saveClaim(claim);

            }

            if(out!=null) {

                out.close();

            }

            List<Claim> claimsmoveDB = fetchClaimHandler.fetchClaimsByDate(startDate,endDate, null, null, true);

            System.out.println("Movedb claims:");

            System.out.println(claimsmoveDB.size());

        } catch (Exception | SQLStoreException e) {

            log.error("Error in migration",e);

        }

    }

}

            clause = clause.concat(" claim.claimed_in_dashboard = ? or source_id = ?");

            clauseParams.add(dashboards.get(i));

1.[beta]  work on beta

1.1 connect to beta

	ssh argiro.kokogiannaki@beta.services.openaire.eu

1.2 Export tables from beta

	pg_dump  -U  dnet  -h localhost dnet_openaireplus  -t context -t project -t result -t has_source_context -t has_source_project -t has_source_result -t has_target_project -t has_target_result -t claim -t notification --password >claims20190720.sql

	pg_dump --column-inserts --data-only  -U  dnet  -h localhost dnet_openaireplus  -t notification  --password >notifications20190801.sql

1.3 Get records

	ll /srv/claims/records/

	tar -czvf records.beta.20190801.tar.gz /srv/claims/records/

	 scp records.beta.20190801.tar.gz argirok@scoobydoo.di.uoa.gr:~/

2. dump in local db

2.1 create local db

	create database claims_beta

2.2 insert records

	psql -h localhost  -U postgres  claims_beta  --password < claims20190720.sql

3. prepare db to put new records

3.1 create db

	create database claims_prod

3.2  create claim tables for local db

3.3 set sequence numbers not to override anything in production:

	ALTER SEQUENCE result_id_seq RESTART WITH 20000; ALTER SEQUENCE claim_id_seq RESTART WITH 13000;

4. Run Java migration

5. export prod records

	 pg_dump --column-inserts --data-only  -U  postgres  -h localhost movedb  -t context -t project -t result -t has_source_context -t has_source_project -t has_source_result -t has_target_project -t has_target_result -t claim -t  --password >claims_new_20190720.sql

6. [prod] set sequence numbers after the ids of the new claims

	 psql -h postgresql.services.openaire.eu  dnet_openaireplus -U dnet

	ALTER SEQUENCE result_id_seq RESTART WITH 11000; ALTER SEQUENCE claim_id_seq RESTART WITH 12800;

7.[prod]

7.1 insert records in production db

	psql -U  dnet  -h postgresql.services.openaire.eu dnet_openaireplus  --password < claims_new_20190720.sql

8.[prod] notifications in production

8.1 insert notifications records in production db

	psql -U  dnet  -h postgresql.services.openaire.eu dnet_openaireplus  --password < notifications20190801.sql

9. Copy files

9.1[beta] In /srv/claims/records/ folder of beta run copy.sh script

	set date in case you don't want to  get them all (but all are new anyway)

9.2 [prod] copy new records folder in production add in /srv/claims/ folder

9.3 [prod] in /srv/claims/ folder  run copyToProduction.sh  script

-- add column

#altered in beta & production

#ALTER TABLE claim ADD COLUMN claimed_in_dashboard varchar(50);

-- update column with specific value:

#run in production 25-11-2019select

#UPDATE claim SET claimed_in_dashboard = 'production_explore';

-- update claims linked to a community

#run in beta 25-11-2019

#UPDATE claim SET claimed_in_dashboard = concat('beta_connect_',split_part(source_id,'::',1)) where source_type = 'context' and claim_date between '2018-03-01' and '2019-12-31' ;

--which users claims since connect release

select claimedby,claimed_in_dashboard, source_id, target_id  from claim where claim_date between '2018-03-01' and '2019-12-31';

-- update claims made by a community user/manager

#run in beta 25-11-2019

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_ni' where  ( claimedby = 'sorina.pop@creatis.insa-lyon.fr' or claimedby = 'xavier.rolland@inria.fr'  or claimedby = 'micheL.dojat@inserm.fr' or claimedby = 'camille.maumet@inria.fr') and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_elexir-gr' where  ( claimedby = 'schatz@imis.athena-innovation.gr' or claimedby = 'vergoulis@imis.athena-innovation.gr' ) and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_fam' where  ( claimedby = 'taha.imzilen@ird.fr' or claimedby = 'bastien.merigot@umontpellier.fr'  or claimedby = 'paul.taconet@ird.fr' or claimedby = 'julien.barde@ird.fr') and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_mes' where  ( claimedby = 'spesant@marum.de' or claimedby = 'javier.moreno@gm.uca.es') and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_ni' where  ( claimedby = 'sorina.pop@creatis.insa-lyon.fr' or claimedby = 'xavier.rolland@inria.fr'  or claimedby = 'micheL.dojat@inserm.fr' or claimedby = 'camille.maumet@inria.fr') and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_rda' where  ( claimedby = 'ilaria.fava@gmail.com') and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_dh-ch' where  ( claimedby = 'achille.felicetti@gmail.com' or claimedby = 'francesca.murano@unifi.it' or claimedby = 'sokhi.huda@uinsby.ac.id') and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_ee' where  ( claimedby = 'evita.mailli@icre8.eu' or claimedby = 'avassilopoulos.aua@gmail.com' or claimedby = 'ebun.akinsete@icre8.eu') and claim_date between '2018-03-01' and '2019-12-31' ;

#UPDATE claim SET claimed_in_dashboard =  'beta_connect_dariah' where  ( claimedby = 'erzsebet.toth-czifra@dariah.eu') and claim_date between '2018-03-01' and '2019-12-31' ;

# init rest values with explore

select claimedby,claimed_in_dashboard, source_id, target_id  from claim where claimed_in_dashboard is null;

#run in beta 25-11-2019

#UPDATE claim SET claimed_in_dashboard = 'beta_explore' where claimed_in_dashboard is null;

--

#if the properties file is cached and there is no value for  dashboard, the claimed_in_dashboard value may be 'production_undefined'. Correct with:

# select * from claim where claimed_in_dashboard = 'production_undefined' ;

#UPDATE claim SET claimed_in_dashboard = 'production_explore' where claimed_in_dashboard = 'production_undefined' ;

--

select * from claim where claim_date between '2018-03-01' and '2019-12-31' and  ( claimed_in_dashboard ='beta_connect_mes'  or  claimed_in_dashboard ='beta_connect_ee'  or claimed_in_dashboard ='beta_connect_ni' or claimed_in_dashboard ='beta_connect_dh-ch' or claimed_in_dashboard ='beta_connect_fam');