
-- Database update July 24, 2018



-- Add Visualization table and create index

CREATE TABLE Visualization (
  visualizationId bigint GENERATED ALWAYS AS IDENTITY,
  analysisId bigint NOT NULL,
  datasetId bigint NOT NULL,
  name varchar(250) NOT NULL,
  description text DEFAULT NULL,
  tags text DEFAULT NULL, -- ["tag'1", "tag-2", ...]
  data bytea NOT NULL,
  contentType varchar(250) DEFAULT NULL,
  metadata text NOT NULL,
  chartType varchar(250) NOT NULL,

  PRIMARY KEY (visualizationId),
  FOREIGN KEY (datasetId) REFERENCES Dataset(datasetId),
  FOREIGN KEY (analysisId) REFERENCES Analysis(analysisId)
);
ALTER SEQUENCE Visualization_visualizationId_seq RESTART WITH 100;

DROP INDEX IF EXISTS idx_visualization_analysis;
CREATE INDEX idx_visualization_analysis ON Analysis(analysisId);



-- Delete visualization data from Attachment table

DELETE FROM Attachment WHERE attachmentSectionType = 'SavedVisualization';



-- Drop unnecessary column from Attachment table

ALTER TABLE Attachment DROP COLUMN attachmentSectionType, DROP COLUMN chartType;