
/*
	This is a dump of the ngbwalpha db as of 6/17/2014, updated
	with aug_19 changes. .  This is the schema for cipres portal and rest api 
	database.  All tables are innodb.  
	
	Unfortunately we used latin for some tables and utf8 for others.  Ideally
	all would be utf8. TODO: see if we can convert them to utf8.

	I removed all comments and AUTO_INCREMENT=xxx so that this can be used
	to initialize a new db or for comparison with schema of another db.

	Comments added manually at the end are just queries I've found useful
	and want to remember.
*/
DROP TABLE IF EXISTS `application_preferences`;
CREATE TABLE `application_preferences` (
  `NAME` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `VALUE` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `PREFERENCE` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`NAME`,`PREFERENCE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


DROP TABLE IF EXISTS `applications`;
CREATE TABLE `applications` (
  `NAME` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `APP_ID` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `LONGNAME` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `AUTH_TYPE` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `AUTH_USER_ID` bigint(20) NOT NULL,
  `WEBSITE_URL` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `COMMENT` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ACTIVE` bit(1) NOT NULL,
   `DATE_CREATED` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`NAME`),
  UNIQUE KEY `APP_ID` (`APP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


DROP TABLE IF EXISTS `cached_items`;
CREATE TABLE `cached_items` (
  `CACHED_ITEM_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `SESSION_ID` varchar(255) NOT NULL,
  `ATTRIBUTE` varchar(255) NOT NULL,
  `COMMENT` varchar(255) DEFAULT NULL,
  `VERSION` int(11) NOT NULL DEFAULT '0',
  `VALUE` longblob,
  PRIMARY KEY (`CACHED_ITEM_ID`),
  UNIQUE KEY `SESSION_ID` (`SESSION_ID`,`ATTRIBUTE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `data_records`;
CREATE TABLE `data_records` (
  `RECORD_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `USERDATA_ID` bigint(20) NOT NULL,
  `RECORD_TYPE` varchar(100) NOT NULL,
  PRIMARY KEY (`RECORD_ID`),
  KEY `USERDATA_ID` (`USERDATA_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `folder_preferences`;
CREATE TABLE `folder_preferences` (
  `FOLDER_ID` bigint(20) NOT NULL,
  `VALUE` varchar(100) DEFAULT NULL,
  `PREFERENCE` varchar(100) NOT NULL,
  PRIMARY KEY (`FOLDER_ID`,`PREFERENCE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `folders`;
CREATE TABLE `folders` (
  `FOLDER_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `GROUP_ID` bigint(20) NOT NULL,
  `USER_ID` bigint(20) NOT NULL,
  `ENCLOSING_FOLDER_ID` bigint(20) DEFAULT NULL,
  `COMMENT` varchar(255) DEFAULT NULL,
  `CREATION_DATE` datetime NOT NULL,
  `LABEL` varchar(255) NOT NULL,
  `GROUP_READABLE` bit(1) NOT NULL DEFAULT b'0',
  `WORLD_READABLE` bit(1) NOT NULL DEFAULT b'0',
  `VERSION` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`FOLDER_ID`),
  KEY `GROUP_ID` (`GROUP_ID`),
  KEY `USER_ID` (`USER_ID`),
  KEY `ENCLOSING_FOLDER_ID` (`ENCLOSING_FOLDER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `groups`;
CREATE TABLE `groups` (
  `GROUP_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `ADMINISTRATOR` bigint(20) DEFAULT NULL,
  `COMMENT` varchar(255) DEFAULT NULL,
  `CREATION_DATE` datetime NOT NULL,
  `DESCRIPTION` varchar(1023) DEFAULT NULL,
  `GROUPNAME` varchar(255) NOT NULL,
  `VERSION` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`GROUP_ID`),
  UNIQUE KEY `GROUPNAME` (`GROUPNAME`),
  KEY `ADMINISTRATOR` (`ADMINISTRATOR`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `item_metadata`;
CREATE TABLE `item_metadata` (
  `USERDATA_ID` bigint(20) NOT NULL,
  `VALUE` varchar(255) DEFAULT NULL,
  `FIELD` varchar(100) NOT NULL,
  PRIMARY KEY (`USERDATA_ID`,`FIELD`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `job_events`;
CREATE TABLE `job_events` (
  `SE_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `JOBHANDLE` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `EVENT_DATE` datetime NOT NULL,
  `TASK_STAGE` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `NAME` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `VALUE` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`SE_ID`),
  KEY `JOBHANDLE` (`JOBHANDLE`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


DROP TABLE IF EXISTS `job_stats`;
CREATE TABLE `job_stats` (
  `JOBHANDLE` varchar(255) NOT NULL,
  `RESOURCE` varchar(100) NOT NULL,
  `TASK_ID` bigint(20) NOT NULL,
  `USER_ID` bigint(20) NOT NULL,
  `TOOL_ID` varchar(100) NOT NULL,
  `EMAIL` varchar(200) NOT NULL,
  `SUBMITTER` varchar(1023) NOT NULL DEFAULT 'UNKNOWN',
  `REMOTE_JOB_ID` varchar(1023) DEFAULT NULL,
  `TG_CHARGE_NUMBER` varchar(100) DEFAULT NULL,
  `DATE_ENTERED` datetime NOT NULL,
  `DATE_SUBMITTED` datetime DEFAULT NULL,
  `DATE_TERMINATED` datetime DEFAULT NULL,
  `SU_PREDICTED` bigint(20) DEFAULT NULL,
  `SU_COMPUTED` bigint(20) DEFAULT NULL,
  `SU_CHARGED` bigint(20) DEFAULT NULL,
  `SU_OVERRIDE` int(11) DEFAULT NULL,
  `APPNAME` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`JOBHANDLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `record_fields`;
CREATE TABLE `record_fields` (
  `RECORD_ID` bigint(20) NOT NULL,
  `RECORD_FIELD` varchar(100) NOT NULL,
  `VALUE` varchar(1023) DEFAULT NULL,
  PRIMARY KEY (`RECORD_ID`,`RECORD_FIELD`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `running_tasks`;
CREATE TABLE `running_tasks` (
  `JOBHANDLE` varchar(255) NOT NULL,
  `RESOURCE` varchar(100) NOT NULL,
  `TOOL_ID` varchar(100) NOT NULL,
  `USER_ID` bigint(20) NOT NULL,
  `TASK_ID` bigint(20) NOT NULL,
  `SUBMITTER` varchar(1023) NOT NULL,
  `REMOTE_JOB_ID` varchar(1023) DEFAULT NULL,
  `DATE_ENTERED` datetime NOT NULL,
  `LOCKED` datetime DEFAULT NULL,
  `HOSTNAME` varchar(200) DEFAULT NULL,
  `PID` bigint(20) DEFAULT NULL,
  `STATUS` varchar(100) NOT NULL,
  `ATTEMPTS` int(4) NOT NULL DEFAULT '0',
  `OUTPUT_DESC` varchar(5000) DEFAULT NULL,
  `COMMANDLINE` varchar(5000) DEFAULT NULL,
  `SPROPS` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`JOBHANDLE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `running_tasks_parameters`;
CREATE TABLE `running_tasks_parameters` (
  `JOBHANDLE` varchar(255) NOT NULL DEFAULT '',
  `NAME` varchar(100) NOT NULL,
  `VALUE` varchar(1023) DEFAULT NULL,
  PRIMARY KEY (`JOBHANDLE`,`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `source_documents`;
CREATE TABLE `source_documents` (
  `SOURCE_DOCUMENT_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `FILENAME` varchar(1023) NOT NULL,
  `LENGTH` bigint(20) NOT NULL,
  `SIGNATURE` varchar(255) NOT NULL,
  PRIMARY KEY (`SOURCE_DOCUMENT_ID`),
  KEY `SD_SIGNATURE_INDEX` (`SIGNATURE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `sso`;
CREATE TABLE `sso` (
  `SSO_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `USER_ID` bigint(20) NOT NULL,
  `SSO_USERNAME` varchar(255) NOT NULL,
  PRIMARY KEY (`SSO_ID`),
  UNIQUE KEY `SSO_USERNAME` (`SSO_USERNAME`),
  KEY `USER_ID` (`USER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `task_input_parameters`;
CREATE TABLE `task_input_parameters` (
  `INPUT_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `TASK_ID` bigint(20) NOT NULL,
  `PARAMETER` varchar(255) NOT NULL,
  PRIMARY KEY (`INPUT_ID`),
  KEY `TASK_ID` (`TASK_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `task_input_source_documents`;
CREATE TABLE `task_input_source_documents` (
  `INPUT_DOCUMENT_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `INPUT_ID` bigint(20) NOT NULL,
  `SOURCE_DOCUMENT_ID` bigint(20) NOT NULL,
  `DATAFORMAT` varchar(255) NOT NULL,
  `DATATYPE` varchar(255) NOT NULL,
  `ENTITYTYPE` varchar(255) NOT NULL,
  `NAME` varchar(255) DEFAULT NULL,
  `VALIDATED` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`INPUT_DOCUMENT_ID`),
  KEY `INPUT_ID` (`INPUT_ID`),
  KEY `SOURCE_DOCUMENT_ID` (`SOURCE_DOCUMENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `task_log_messages`;
CREATE TABLE `task_log_messages` (
  `TASK_ID` bigint(20) NOT NULL,
  `MESSAGE_INDEX` int(11) NOT NULL,
  `CREATION_DATE` datetime NOT NULL,
  `STAGE` varchar(20) NOT NULL,
  `ERROR` bit(1) NOT NULL DEFAULT b'0',
  `MESSAGE` longtext,
  PRIMARY KEY (`TASK_ID`,`MESSAGE_INDEX`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `task_output_parameters`;
CREATE TABLE `task_output_parameters` (
  `OUTPUT_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `TASK_ID` bigint(20) NOT NULL,
  `PARAMETER` varchar(255) NOT NULL,
  `INTERMEDIATE` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`OUTPUT_ID`),
  KEY `TASK_ID` (`TASK_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `task_output_source_documents`;
CREATE TABLE `task_output_source_documents` (
  `OUTPUT_DOCUMENT_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `OUTPUT_ID` bigint(20) NOT NULL,
  `SOURCE_DOCUMENT_ID` bigint(20) NOT NULL,
  `DATAFORMAT` varchar(255) NOT NULL,
  `DATATYPE` varchar(255) NOT NULL,
  `ENTITYTYPE` varchar(255) NOT NULL,
  `NAME` varchar(255) DEFAULT NULL,
  `VALIDATED` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`OUTPUT_DOCUMENT_ID`),
  KEY `OUTPUT_ID` (`OUTPUT_ID`),
  KEY `SOURCE_DOCUMENT_ID` (`SOURCE_DOCUMENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `task_properties`;
CREATE TABLE `task_properties` (
  `TASK_ID` bigint(20) NOT NULL,
  `VALUE` varchar(4095) DEFAULT NULL,
  `PROPERTY` varchar(100) NOT NULL,
  PRIMARY KEY (`TASK_ID`,`PROPERTY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `tasks`;
CREATE TABLE `tasks` (
  `TASK_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `GROUP_ID` bigint(20) NOT NULL,
  `USER_ID` bigint(20) NOT NULL,
  `ENCLOSING_FOLDER_ID` bigint(20) NOT NULL,
  `COMMENT` varchar(255) DEFAULT NULL,
  `CREATION_DATE` datetime NOT NULL,
  `LABEL` varchar(255) NOT NULL,
  `JOBHANDLE` varchar(255) DEFAULT NULL,
  `OK` bit(1) NOT NULL,
  `STAGE` varchar(20) NOT NULL,
  `TOOL_ID` varchar(100) DEFAULT NULL,
  `IS_TERMINAL` bit(1) NOT NULL DEFAULT b'0',
  `APPNAME` varchar(30) NOT NULL DEFAULT '',
  `VERSION` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`TASK_ID`),
  UNIQUE KEY `JOBHANDLE` (`JOBHANDLE`),
  KEY `GROUP_ID` (`GROUP_ID`),
  KEY `USER_ID` (`USER_ID`),
  KEY `ENCLOSING_FOLDER_ID` (`ENCLOSING_FOLDER_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `tgusage`;
CREATE TABLE `tgusage` (
  `TGUSAGE_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `RESOURCE` varchar(100) NOT NULL,
  `JOBID` varchar(100) NOT NULL,
  `FULL_JOBID` varchar(255) NOT NULL,
  `FULL_RESOURCE` varchar(255) NOT NULL,
  `CHARGE` int(11) NOT NULL,
  `START_TIME` datetime NOT NULL,
  `END_TIME` datetime NOT NULL,
  `SUBMIT_TIME` datetime NOT NULL,
  `CHARGE_NUMBER` varchar(100) NOT NULL,
  `WALL_HRS` varchar(10) NOT NULL,
  `SU` int(11) NOT NULL,
  `NODECOUNT` int(11) NOT NULL,
  `PROCESSORS` int(11) NOT NULL,
  `QUEUE` varchar(100) NOT NULL,
  PRIMARY KEY (`TGUSAGE_ID`),
  UNIQUE KEY `RESOURCE` (`RESOURCE`,`JOBID`,`SUBMIT_TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `tool_parameters`;
CREATE TABLE `tool_parameters` (
  `TASK_ID` bigint(20) NOT NULL,
  `value` varchar(1024) DEFAULT NULL,
  `PARAMETER` varchar(100) NOT NULL,
  PRIMARY KEY (`TASK_ID`,`PARAMETER`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `user_group_lookup`;
CREATE TABLE `user_group_lookup` (
  `USER_ID` bigint(20) NOT NULL,
  `GROUP_ID` bigint(20) NOT NULL,
  PRIMARY KEY (`USER_ID`,`GROUP_ID`),
  KEY `GROUP_ID` (`GROUP_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `user_preferences`;
CREATE TABLE `user_preferences` (
  `USER_ID` bigint(20) NOT NULL,
  `VALUE` varchar(100) DEFAULT NULL,
  `PREFERENCE` varchar(100) NOT NULL,
  PRIMARY KEY (`USER_ID`,`PREFERENCE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `userdata`;
CREATE TABLE `userdata` (
  `USERDATA_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `GROUP_ID` bigint(20) NOT NULL,
  `USER_ID` bigint(20) NOT NULL,
  `ENCLOSING_FOLDER_ID` bigint(20) NOT NULL,
  `SOURCE_DOCUMENT_ID` bigint(20) NOT NULL,
  `COMMENT` varchar(255) DEFAULT NULL,
  `CREATION_DATE` datetime NOT NULL,
  `LABEL` varchar(1023) NOT NULL,
  `DATAFORMAT` varchar(255) NOT NULL,
  `DATATYPE` varchar(255) NOT NULL,
  `ENTITYTYPE` varchar(255) NOT NULL,
  `VALIDATED` bit(1) NOT NULL DEFAULT b'0',
  `VERSION` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`USERDATA_ID`),
  KEY `GROUP_ID` (`GROUP_ID`),
  KEY `USER_ID` (`USER_ID`),
  KEY `ENCLOSING_FOLDER_ID` (`ENCLOSING_FOLDER_ID`),
  KEY `SOURCE_DOCUMENT_ID` (`SOURCE_DOCUMENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `USER_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `DEFAULT_GROUP_ID` bigint(20) DEFAULT NULL,
  `COMMENT` varchar(255) DEFAULT NULL,
  `INSTITUTION` varchar(255) DEFAULT NULL,
  `AREA_CODE` varchar(10) DEFAULT NULL,
  `CITY` varchar(100) DEFAULT NULL,
  `COUNTRY` varchar(50) DEFAULT NULL,
  `EMAIL` varchar(200) NOT NULL,
  `FIRST_NAME` varchar(100) NOT NULL,
  `LAST_NAME` varchar(100) NOT NULL,
  `MAILCODE` varchar(10) DEFAULT NULL,
  `PASSWORD` varchar(50) NOT NULL,
  `PHONE_NUMBER` varchar(20) DEFAULT NULL,
  `ROLE` varchar(50) NOT NULL,
  `STATE` varchar(50) DEFAULT NULL,
  `STREET_ADDRESS` varchar(255) DEFAULT NULL,
  `USERNAME` varchar(200) NOT NULL,
  `WEBSITE_URL` varchar(255) DEFAULT NULL,
  `ZIP_CODE` varchar(10) DEFAULT NULL,
  `ACTIVE` bit(1) NOT NULL,
  `VERSION` int(11) NOT NULL DEFAULT '0',
  `CAN_SUBMIT` bit(1) NOT NULL DEFAULT b'1',
  `LAST_LOGIN` datetime DEFAULT NULL,
  `UMBRELLA_APPNAME` varchar(30) NOT NULL DEFAULT '',
  `ACTIVATION_CODE` varchar(50) DEFAULT NULL,
  `ACTIVATION_SENT` datetime  DEFAULT NULL,
   `DATE_CREATED` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`USER_ID`),
  UNIQUE KEY `USERNAME` (`USERNAME`),
  UNIQUE KEY `EMAIL` (`EMAIL`,`ROLE`,`UMBRELLA_APPNAME`),
  KEY `DEFAULT_GROUP_ID` (`DEFAULT_GROUP_ID`),
  KEY `ACTIVATION` (`ACTIVATION_SENT`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*
    This query gets the last job_event record for each jobhandle in the table.
    See: http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group

select m1.*
from job_events m1 left join job_events m2
on (m1.jobhandle = m2.jobhandle and m1.event_date < m2.event_date)
where m2.event_date is null

	Find tgusage records for a particular user
select tgusage_id
from job_stats inner join tgusage
on job_stats.resource = tgusage.resource and
job_stats.remote_job_id = tgusage.jobid and
abs(datediff(job_stats.date_submitted, tgusage.submit_time)) < 2
where job_stats.user_id =

	This is basically the overlimit query that usage.py does except that it ignores
	charge number and start of period.  The left join insures that the resulting table
	has a row for each user (ie the table named before the words 'left join') even if 
	there is no corresponding row in applications table (ie the table named after 'left join').
	The purpose of the left join is to get the application longname from the application table,
	for umbrella users, so that we can put the application longname in the email.

select job_stats.user_id, users.username, users.email, sum(tgusage.su), users.umbrella_appname,
	applications.longname, users.role
from users left join applications
on users.umbrella_appname = applications.name
inner join job_stats
on job_stats.user_id = users.user_id
inner join tgusage
on job_stats.resource = tgusage.resource and job_stats.remote_job_id = tgusage.jobid and
abs(datediff(job_stats.date_submitted, tgusage.submit_time)) < 2
where
group by job_stats.user_id
having sum(tgusage.su) > 10


*/
