



set read_rnd_buffer_size=4*1024*1024;  
set  read_buffer_size=4*1024*1024;
set  sort_buffer_size=4*1024*1024;
set join_buffer_size=8*1024*1024;
set global key_buffer_size=256*1024*1024;

set global character_set_server='utf8';
set global collation_server='utf8_unicode_ci';
set character_set_server='utf8';
set collation_server='utf8_unicode_ci';


CREATE TABLE `774_patient` (
  `patientID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `patientStatus` tinyint(3) unsigned NOT NULL default 254,
  `pid` varchar(64) NOT NULL default uuid(),
  `pname` varchar(100) NOT NULL,
  `dob` date NOT NULL DEFAULT '1983-03-06',
  `sex` enum('M','F','O') NOT NULL DEFAULT 'O',
  PRIMARY KEY (`patientID`),
  UNIQUE KEY `idx_774_patient_pid` (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=317145 DEFAULT CHARSET=utf8 ;

CREATE TABLE `774_study` (
  `received` datetime(6) NOT NULL,
  `siuid` varchar(64) NOT NULL default UUID(),
  `patientID` int(10) unsigned NOT NULL,
  `refphys` varchar(100) NOT NULL default uuid(),
  `institution` varchar(64) NOT NULL default uuid(),
  `sdate` date NOT NULL default current_date(),
  `stime` time NOT NULL default current_time(),
  `sdesc` varchar(64) NOT NULL default uuid(),
  `modality` varchar(8) NOT NULL default 'modality',
  `bodypart` varchar(32) NOT NULL default 'hypocamis',
  `acnum` varchar(32) NOT NULL default 'arr3',
  `backedup` datetime(6) NOT NULL default now(),
  `modified` datetime(6) NOT NULL default now(),
  `accessed` datetime(6) NOT NULL default now(),
  `numimages` smallint(5) unsigned NOT NULL default 32,
  `numsnapshots` smallint(5) unsigned NOT NULL default 42,
  `numattachments` smallint(5) unsigned DEFAULT NULL,
  `tsuid` varchar(28) NOT NULL default 'ssl3l3lsss',
  `orderedSOPs` mediumtext DEFAULT NULL,
  `cached` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`received`),
  KEY `idx_774_study_patientID` (`patientID`),
  UNIQUE KEY `idx_774_study_siuid` (`siuid`),
  KEY `idx_774_study_sdate` (`sdate`),
  CONSTRAINT `fk_774_study_patientID` FOREIGN KEY (`patientID`) REFERENCES `774_patient` (`patientID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into 774_patient(patientID,pname) select seq,uuid() from seq_1_to_28902;
insert into 774_study(received,patientID) select date_add(MAKEDATE(2002 + patientID / 3650 , (patientID/10) mod 365 + 1), interval (patientID mod 10) hour), patientID from 774_patient;
insert into 774_study(received,patientID) select date_add(MAKEDATE(2015 + patientID / 3650 , (patientID/10) mod 365 + 1), interval (patientID mod 10) hour), patientID from 774_patient;
insert into 774_study(received,patientID) select date_add(MAKEDATE(1985 + patientID / 3650 , (patientID/10) mod 365 + 1), interval (patientID mod 10) hour), patientID from 774_patient;


select * from 774_study left join 774_patient on 774_study.patientID = 774_patient.patientID where exists (select pname from 774_patient where pname like 'doe%john%^w%^%^%^%') and 774_patient.pname in (select pname from 774_patient where pname like 'doe%john%^w%^%^%^%') order by received desc limit 0,50;


/*
CREATE TABLE `774_study` (
  `received` datetime(6) NOT NULL,
  `siuid` varchar(64) NOT NULL,
  `patientID` int(10) unsigned NOT NULL,
  `refphys` varchar(100) NOT NULL,
  `institution` varchar(64) NOT NULL,
  `sdate` date NOT NULL,
  `stime` time NOT NULL,
  `sdesc` varchar(64) NOT NULL,
  `modality` varchar(8) NOT NULL,
  `bodypart` varchar(32) NOT NULL,
  `acnum` varchar(32) NOT NULL,
  `backedup` datetime(6) NOT NULL,
  `modified` datetime(6) NOT NULL,
  `accessed` datetime(6) NOT NULL,
  `numimages` smallint(5) unsigned NOT NULL,
  `numsnapshots` smallint(5) unsigned NOT NULL,
  `numattachments` smallint(5) unsigned DEFAULT NULL,
  `tsuid` varchar(28) NOT NULL,
  `orderedSOPs` mediumtext DEFAULT NULL,
  `cached` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`received`),
  UNIQUE KEY `idx_774_study_siuid` (`siuid`),
  KEY `idx_774_study_patientID` (`patientID`),
  KEY `idx_774_study_sdate` (`sdate`),
  CONSTRAINT `fk_774_study_patientID` FOREIGN KEY (`patientID`) REFERENCES `774_patient` (`patientID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; */
