ALTER TABLE DEGREE_MODULE ADD COLUMN IS_OPTIONAL TINYINT(1) DEFAULT 0;

UPDATE DEGREE_MODULE SET IS_OPTIONAL = 1 WHERE NAME LIKE 'OPTATIVA%' AND OJB_CONCRETE_CLASS LIKE '%.COURSEGROUP';
UPDATE DEGREE_MODULE SET IS_OPTIONAL = NULL WHERE NAME LIKE 'RAMO%' AND OJB_CONCRETE_CLASS LIKE '%.COURSEGROUP';
UPDATE DEGREE_MODULE SET IS_OPTIONAL = NULL WHERE NAME LIKE 'ESPECIALIDADE%' AND OJB_CONCRETE_CLASS LIKE '%.COURSEGROUP';
UPDATE DEGREE_MODULE SET IS_OPTIONAL = NULL WHERE NAME LIKE 'ESPECIALIZACAO%' AND OJB_CONCRETE_CLASS LIKE '%.COURSEGROUP';

CREATE TEMPORARY TABLE OPTIONAL_DEGREE_MODULE AS
SELECT KEY_CHILD_DEGREE_MODULE AS ID_INTERNAL
  FROM CONTEXT
 WHERE CONTEXT.KEY_PARENT_COURSE_GROUP IN
(SELECT ID_INTERNAL
   FROM DEGREE_MODULE
  WHERE NAME LIKE 'OPTATIVA%' AND OJB_CONCRETE_CLASS LIKE '%.COURSEGROUP');

CREATE INDEX ID_INTERNAL ON OPTIONAL_DEGREE_MODULE (ID_INTERNAL);

UPDATE DEGREE_MODULE SET IS_OPTIONAL = 1 WHERE ID_INTERNAL IN (SELECT ID_INTERNAL FROM OPTIONAL_DEGREE_MODULE);

DROP TABLE OPTIONAL_DEGREE_MODULE;

CREATE TEMPORARY TABLE OPTIONAL_DEGREE_MODULE AS
SELECT KEY_CHILD_DEGREE_MODULE AS ID_INTERNAL
  FROM CONTEXT
 WHERE CONTEXT.KEY_PARENT_COURSE_GROUP IN
(SELECT ID_INTERNAL
   FROM DEGREE_MODULE
  WHERE NAME LIKE 'OPTATIVA%' AND OJB_CONCRETE_CLASS LIKE '%.COURSEGROUP');

CREATE INDEX ID_INTERNAL ON OPTIONAL_DEGREE_MODULE (ID_INTERNAL);

UPDATE DEGREE_MODULE SET IS_OPTIONAL = 1 WHERE ID_INTERNAL IN (SELECT ID_INTERNAL FROM OPTIONAL_DEGREE_MODULE);

DROP TABLE OPTIONAL_DEGREE_MODULE;
