-- changing date type from VARCHAR to DATETIME to ease sorting
ALTER TABLE DICOM
CHANGE COLUMN DM_FILE_ST_DATE DM_FILE_ST_DATE DATETIME NULL DEFAULT NULL ,
CHANGE COLUMN DM_FILE_SER_DATE DM_FILE_SER_DATE DATETIME NULL DEFAULT NULL ;

CREATE TABLE DICOMTYPE (
  DCMT_ID VARCHAR(3) NOT NULL,
  DCMT_DESC VARCHAR(50) NOT NULL,
  PRIMARY KEY (DCMT_ID)
);

ALTER TABLE DICOM 
ADD COLUMN DM_DCMT_ID VARCHAR(3) NULL AFTER DM_THUMBNAIL,
ADD INDEX FK_DICOM_DICOMTYPE_idx (DM_DCMT_ID ASC);

ALTER TABLE DICOM 
ADD CONSTRAINT FK_DICOM_DICOMTYPE
  FOREIGN KEY (DM_DCMT_ID)
  REFERENCES DICOMTYPE (DCMT_ID)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;
  
INSERT INTO MENUITEM (MNI_ID_A, MNI_BTN_LABEL, MNI_LABEL, MNI_TOOLTIP, MNI_SHORTCUT, MNI_SUBMENU, MNI_CLASS, MNI_IS_SUBMENU, MNI_POSITION) VALUES ('dicomtype', 'angal.menu.btn.dicomtype', 'angal.menu.dicomtype', 'x', 'X', 'types', 'org.isf.dicomtype.gui.DicomTypeBrowser', 'N', '13');
INSERT INTO GROUPMENU (GM_UG_ID_A, GM_MNI_ID_A, GM_ACTIVE) VALUES ('admin','dicomtype',1);
  

