-- Convert all tables to InnoDB table engine --
DROP TABLE IF EXISTS redcap_library_map;
CREATE TABLE redcap_library_map (
  project_id int(5) NOT NULL default '0',
  form_name varchar(100) collate utf8_unicode_ci NULL default NULL,
  `type` int(11) NOT NULL default '0',
  library_id int(10) NOT NULL default '0',
  upload_timestamp datetime default NULL,
  acknowledgement text default NULL,
  acknowledgement_cache datetime default NULL,
  PRIMARY KEY  (project_id,form_name,`type`,library_id),
  KEY project_id (project_id),
  KEY library_id (library_id),
  KEY form_name (form_name),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS redcap_data_temp303;
CREATE TABLE redcap_data_temp303 (
  project_id int(5) NOT NULL default '0',
  event_id int(10) default NULL,
  record varchar(100) collate utf8_unicode_ci default NULL,
  field_name varchar(100) collate utf8_unicode_ci default NULL,
  `value` text collate utf8_unicode_ci,
  KEY project_id (project_id),
  KEY event_id (event_id),
  KEY record_field (record,field_name),
  KEY project_field (project_id,field_name),
  KEY project_record (project_id,record),
  KEY proj_record_field (project_id,record,field_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS redcap_log_event_temp303;
CREATE TABLE redcap_log_event_temp303 (
  log_event_id int(11) NOT NULL auto_increment,
  project_id int(5) NOT NULL default '0',
  ts bigint(14) default NULL,
  `user` varchar(255) collate utf8_unicode_ci default NULL,
  ip varchar(15) collate utf8_unicode_ci default NULL,
  `page` varchar(255) collate utf8_unicode_ci default NULL,
  event enum('UPDATE','INSERT','DELETE','SELECT','ERROR','LOGIN','LOGOUT','OTHER','DATA_EXPORT','DOC_UPLOAD','DOC_DELETE','MANAGE') collate utf8_unicode_ci default NULL,
  object_type varchar(128) collate utf8_unicode_ci default NULL,
  sql_log mediumtext collate utf8_unicode_ci,
  pk text collate utf8_unicode_ci,
  event_id int(10) default NULL,
  data_values text collate utf8_unicode_ci,
  description text collate utf8_unicode_ci,
  legacy int(1) NOT NULL default '0',
  PRIMARY KEY  (log_event_id),
  KEY `user` (`user`),
  KEY project_id (project_id),
  KEY user_project (project_id,`user`),
  KEY pk (pk(64)),
  KEY object_type (object_type),
  KEY ts (ts),
  KEY event (event),
  KEY event_project (event,project_id),
  KEY description (description(128))
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS redcap_metadata_temp303;
CREATE TABLE redcap_metadata_temp303 (
  project_id int(5) NOT NULL default '0',
  field_name varchar(100) collate utf8_unicode_ci NOT NULL default '',
  field_phi varchar(5) collate utf8_unicode_ci default NULL,
  form_name varchar(255) collate utf8_unicode_ci default NULL,
  form_menu_description varchar(255) collate utf8_unicode_ci default NULL,
  field_order float default NULL,
  field_units varchar(50) collate utf8_unicode_ci default NULL,
  element_preceding_header mediumtext collate utf8_unicode_ci,
  element_type varchar(50) collate utf8_unicode_ci default NULL,
  element_label mediumtext collate utf8_unicode_ci,
  element_enum mediumtext collate utf8_unicode_ci,
  element_note mediumtext collate utf8_unicode_ci,
  element_validation_type varchar(50) collate utf8_unicode_ci default NULL,
  element_validation_min varchar(50) collate utf8_unicode_ci default NULL,
  element_validation_max varchar(50) collate utf8_unicode_ci default NULL,
  element_validation_checktype varchar(50) collate utf8_unicode_ci default NULL,
  branching_logic text collate utf8_unicode_ci,
  field_req int(1) NOT NULL default '0',
  PRIMARY KEY  (project_id,field_name),
  KEY project_id_form (project_id,form_name),
  KEY field_name (field_name),
  KEY project_id (project_id),
  KEY project_id_fieldorder (project_id,field_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS redcap_metadata_archive_temp303;
CREATE TABLE redcap_metadata_archive_temp303 (
  project_id int(5) NOT NULL default '0',
  field_name varchar(100) collate utf8_unicode_ci NOT NULL default '',
  field_phi varchar(5) collate utf8_unicode_ci default NULL,
  form_name varchar(255) collate utf8_unicode_ci default NULL,
  form_menu_description varchar(255) collate utf8_unicode_ci default NULL,
  field_order float default NULL,
  field_units varchar(50) collate utf8_unicode_ci default NULL,
  element_preceding_header mediumtext collate utf8_unicode_ci,
  element_type varchar(50) collate utf8_unicode_ci default NULL,
  element_label mediumtext collate utf8_unicode_ci,
  element_enum mediumtext collate utf8_unicode_ci,
  element_note mediumtext collate utf8_unicode_ci,
  element_validation_type varchar(50) collate utf8_unicode_ci default NULL,
  element_validation_min varchar(50) collate utf8_unicode_ci default NULL,
  element_validation_max varchar(50) collate utf8_unicode_ci default NULL,
  element_validation_checktype varchar(50) collate utf8_unicode_ci default NULL,
  branching_logic text collate utf8_unicode_ci,
  field_req int(1) NOT NULL default '0',
  pr_id int(10) NOT NULL default '0',
  PRIMARY KEY  (project_id,field_name,pr_id),
  KEY project_id_form (project_id,form_name),
  KEY field_name (field_name),
  KEY project_id (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS redcap_metadata_temp_temp303;
CREATE TABLE redcap_metadata_temp_temp303 (
  project_id int(5) NOT NULL default '0',
  field_name varchar(100) collate utf8_unicode_ci NOT NULL default '',
  field_phi varchar(5) collate utf8_unicode_ci default NULL,
  form_name varchar(255) collate utf8_unicode_ci default NULL,
  form_menu_description varchar(255) collate utf8_unicode_ci default NULL,
  field_order float default NULL,
  field_units varchar(50) collate utf8_unicode_ci default NULL,
  element_preceding_header mediumtext collate utf8_unicode_ci,
  element_type varchar(50) collate utf8_unicode_ci default NULL,
  element_label mediumtext collate utf8_unicode_ci,
  element_enum mediumtext collate utf8_unicode_ci,
  element_note mediumtext collate utf8_unicode_ci,
  element_validation_type varchar(50) collate utf8_unicode_ci default NULL,
  element_validation_min varchar(50) collate utf8_unicode_ci default NULL,
  element_validation_max varchar(50) collate utf8_unicode_ci default NULL,
  element_validation_checktype varchar(50) collate utf8_unicode_ci default NULL,
  branching_logic text collate utf8_unicode_ci,
  field_req int(1) NOT NULL default '0',
  PRIMARY KEY  (project_id,field_name),
  KEY project_id_form (project_id,form_name),
  KEY field_name (field_name),
  KEY project_id (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
DROP TABLE IF EXISTS redcap_docs_temp303;
CREATE TABLE redcap_docs_temp303 (
  docs_id int(11) NOT NULL auto_increment,
  project_id int(5) NOT NULL default '0',
  docs_date date default NULL,
  docs_name text collate utf8_unicode_ci,
  docs_size double default NULL,
  docs_type text collate utf8_unicode_ci,
  docs_file longblob,
  docs_comment text collate utf8_unicode_ci,
  docs_rights text collate utf8_unicode_ci,
  export_file int(1) NOT NULL default '0',
  PRIMARY KEY  (docs_id),
  KEY docs_name (docs_name(128)),
  KEY project_id (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;