2019-09-19 21:49:17.286 INFO taskExecutor-3 org.springframework.batch.core.launch.support.SimpleJobLauncher -  - Job: [SimpleJob: [name=irAnalysis]] launched with the following parameters: [{jobName=IR Analysis: 2: Optum (PDW - DEV) (OPTUM-PDW), analysis_id=2, source_id=1, sessionId=hj9tmhb5, target_table=temp_cohort_hj9tmhb5, time=1568944157063, jobAuthor=anonymous}]
2019-09-19 21:49:17.714 INFO taskExecutor-3 org.springframework.batch.core.job.SimpleStepHandler -  - Executing step: [irAnalysis.createCohortTable]
2019-09-19 21:49:18.796 INFO taskExecutor-3 org.springframework.batch.core.job.SimpleStepHandler -  - Executing step: [irAnalysis.generateCohort]
2019-09-19 21:49:19.042 ERROR taskExecutor-3 org.springframework.batch.core.step.AbstractStep -  - Encountered an error executing step irAnalysis.generateCohort in job irAnalysis
java.util.concurrent.CompletionException: java.lang.NullPointerException
	at java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:273)
	at java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:280)
	at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1592)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.NullPointerException
	at org.ohdsi.webapi.generationcache.GenerationCacheHelper.computeCacheIfAbsent(GenerationCacheHelper.java:39)
	at org.ohdsi.webapi.cohortcharacterization.GenerateLocalCohortTasklet.lambda$null$1(GenerateLocalCohortTasklet.java:91)
	at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1590)
	... 3 more
2019-09-19 21:49:19.718 INFO taskExecutor-3 org.springframework.batch.core.launch.support.SimpleJobLauncher -  - Job: [SimpleJob: [name=irAnalysis]] completed with the following parameters: [{jobName=IR Analysis: 2: Optum (PDW - DEV) (OPTUM-PDW), analysis_id=2, source_id=1, sessionId=hj9tmhb5, target_table=temp_cohort_hj9tmhb5, time=1568944157063, jobAuthor=anonymous}] and the following status: [FAILED]

-- Historical generations table
IF OBJECT_ID('@results_schema.cohort_generations', 'U') IS NULL
CREATE TABLE @results_schema.cohort_generations
(
	generation_id int NOT NULL,
	SUBJECT_ID bigint NOT NULL,
	cohort_start_date date NOT NULL,
	cohort_end_date date NOT NULL
);


-- Link from all generations to the most recent generation for a cohort_definition_id
IF OBJECT_ID('@results_schema.cohort_generations_ref', 'U') IS NULL
CREATE TABLE @results_schema.cohort_generations_ref
(
	generation_id int NOT NULL,
	cohort_definition_id int NOT NULL
);

ALTER TABLE @results_schema.cohort_inclusion ADD COLUMN generation_id int NOT NULL;
ALTER TABLE @results_schema.cohort_inclusion_result ADD COLUMN generation_id int NOT NULL;
ALTER TABLE @results_schema.cohort_inclusion_stats ADD COLUMN generation_id int NOT NULL;

-- Migrate the data so we can drop the cohort_definition_id from the tables above

-- Generate the generation_id sequence based on the cohort definitions in the cohort table
INSERT @results_schema.cohort_generations_ref (generation_id, cohort_definition_id)
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY cohort_definition_id)
FROM @results_schema.cohort
;

-- TODO: Update the downstream tables to use that new generation_id in place of the cohort_definition_id 
-- Might this be easier to by creating a new table, dropping the old one and then renaming (or re-creating)
-- the target table

-- This part below may not be necessary based on how we approach the step above but leaving here so we ensure that the cohort_definition_id is dropped in these tables.
ALTER TABLE @results_schema.cohort_inclusion DROP COLUMN cohort_definition_id int NOT NULL;
ALTER TABLE @results_schema.cohort_inclusion_result DROP COLUMN cohort_definition_id int NOT NULL;
ALTER TABLE @results_schema.cohort_inclusion_stats DROP COLUMN cohort_definition_id int NOT NULL;

-- Historical generations table
IF OBJECT_ID('@results_schema.cohort_generations', 'U') IS NULL
CREATE TABLE @results_schema.cohort_generations
(
	generation_id int NOT NULL,
	SUBJECT_ID bigint NOT NULL,
	cohort_start_date date NOT NULL,
	cohort_end_date date NOT NULL
);

-- Link from all generations to the most recent generation for a cohort_definition_id
IF OBJECT_ID('@results_schema.cohort_generations_ref', 'U') IS NULL
CREATE TABLE @results_schema.cohort_generations_ref
(
	generation_id int NOT NULL,
	cohort_definition_id int NOT NULL
); 

INSERT INTO @results_schema.cohort_generations
SELECT cohort_definition_id as generation_id, subject_id, cohort_start_date, cohort_end_date
FROM @results_schema.cohort;

INSERT INTO @results_schema.cohort_generations_ref
SELECT cohort_definition_id as generation_id, cohort_definition_id as cohort_definition_id
FROM @results_schema.cohort;

ALTER TABLE @results_schema.cohort_censor_stats RENAME cohort_definition_id TO generation_id;
ALTER TABLE @results_schema.cohort_inclusion RENAME cohort_definition_id TO generation_id;
ALTER TABLE @results_schema.cohort_inclusion_result RENAME cohort_definition_id TO generation_id;
ALTER TABLE @results_schema.cohort_inclusion_stats RENAME cohort_definition_id TO generation_id;
ALTER TABLE @results_schema.cohort_summary_stats RENAME cohort_definition_id TO generation_id;

ALTER TABLE @results_schema.cohort_censor_stats RENAME TO old_cohort_censor_stats;

CREATE TABLE @results_schema.cohort_censor_stats (
  generation_id int NOT NULL,
  lost_count BIGINT NOT NULL
);

INSERT INTO @results_schema.cohort_censor_stats SELECT * FROM @results_schema.old_cohort_censor_stats;

DROP TABLE @results_schema.old_cohort_censor_stats;

-- Historical generations table
IF OBJECT_ID('@results_schema.cohort_generations', 'U') IS NULL
CREATE TABLE @results_schema.cohort_generations
(
	generation_id int NOT NULL,
	SUBJECT_ID bigint NOT NULL,
	cohort_start_date date NOT NULL,
	cohort_end_date date NOT NULL
);

-- Link from all generations to the most recent generation for a cohort_definition_id
IF OBJECT_ID('@results_schema.cohort_generations_ref', 'U') IS NULL
CREATE TABLE @results_schema.cohort_generations_ref
(
	generation_id int NOT NULL,
	cohort_definition_id int NOT NULL
); 

-- Establish the cohort generation references based on the cohort_definition_id's in the system
INSERT INTO @results_schema.cohort_generations
SELECT cohort_definition_id as generation_id, subject_id, cohort_start_date, cohort_end_date
FROM @results_schema.cohort;

INSERT INTO @results_schema.cohort_generations_ref
SELECT cohort_definition_id as generation_id, cohort_definition_id as cohort_definition_id
FROM @results_schema.cohort;

-- Migrate cohort_inclusion
ALTER TABLE @results_schema.cohort_inclusion RENAME TO @results_schema.old_cohort_inclusion;

CREATE TABLE @results_schema.cohort_inclusion (
  generation_id int NOT NULL,
  rule_sequence int NOT NULL,
  name varchar(255) NULL,
  description varchar(1000) NULL
);

INSERT INTO @results_schema.cohort_inclusion (generation_id, rule_sequence, name, description)
SELECT cohort_definition_id, rule_sequence, name, description 
FROM @results_schema.old_cohort_inclusion;

DROP TABLE @results_schema.old_cohort_inclusion;

-- Migrate cohort_inclusion_result
ALTER TABLE @results_schema.cohort_inclusion_result RENAME TO @results_schema.old_cohort_inclusion_result;

CREATE TABLE @results_schema.cohort_inclusion_result (
  generation_id int NOT NULL,
  mode_id int NOT NULL,
  inclusion_rule_mask bigint NOT NULL,
  person_count bigint NOT NULL
);

INSERT INTO @results_schema.cohort_inclusion_result (generation_id, mode_id, inclusion_rule_mask, person_count)
SELECT generation_id, mode_id, inclusion_rule_mask, person_count
FROM @results_schema.old_cohort_inclusion_result
;

DROP TABLE @results_schema.old_cohort_inclusion_result;

-- Migrate cohort_inclusion_stats
ALTER TABLE @results_schema.cohort_inclusion_stats RENAME TO @results_schema.old_cohort_inclusion_stats;

CREATE TABLE @results_schema.cohort_inclusion_stats (
  generation_id int NOT NULL,
  rule_sequence int NOT NULL,
  mode_id int NOT NULL,
  person_count bigint NOT NULL,
  gain_count bigint NOT NULL,
  person_total bigint NOT NULL
);

INSERT INTO @results_schema.cohort_inclusion_stats (generation_id, rule_sequence, mode_id, person_count, gain_count, person_total)
SELECT cohort_definition_id, rule_sequence, mode_id, person_count, gain_count, person_total
FROM @results_schema.old_cohort_inclusion_stats
;

DROP TABLE @results_schema.old_cohort_inclusion_stats;

-- Migrate cohort_summary_stats
ALTER TABLE @results_schema.cohort_summary_stats RENAME TO @results_schema.old_cohort_summary_stats;

CREATE TABLE @results_schema.cohort_summary_stats(
  generation_id int NOT NULL,
  mode_id int NOT NULL,
  base_count bigint NOT NULL,
  final_count bigint NOT NULL
);

INSERT INTO @results_schema.cohort_summary_stats (generation_id, mode_id, base_count, final_count)
SELECT cohort_definition_id, mode_id, base_count, final_count
FROM @results_schema.old_cohort_summary_stats
;

DROP TABLE @results_schema.old_cohort_inclusion_stats;

-- Migrate cohort_censor_stats
ALTER TABLE @results_schema.cohort_censor_stats RENAME TO @results_schema.old_cohort_censor_stats;

CREATE TABLE @results_schema.cohort_censor_stats (
  generation_id int NOT NULL,
  lost_count BIGINT NOT NULL
);

INSERT INTO @results_schema.cohort_censor_stats (generation_id, lost_count)
SELECT cohort_definition_id, lost_count 
FROM @results_schema.old_cohort_censor_stats;

DROP TABLE @results_schema.old_cohort_censor_stats;