あけましてお目出度うございます

2018年新年最初の up ですね

昨日3日夕方の飛行機で札幌に入りました 本日から早速TAVIとなります この間、RAP and BEAT II臨床試験のためのデータベースを作成していました もっとも、2年前に行った RAP and BEAT国際臨床試験のデータベースを改変するだけですので、それほどの工程になるとは見込んでいません どうせならば、この2年間の Web技術の変化に併せて、Bootstrap4.0や jQuery3.0を取り込みたいと思うのですが、ここいらへんはテストせねば何とも言えませんね

まずは DDL (Data Definition Language)でデータ構造を見直しました とりあえずここまで見直しました

 ###############################################################################################
 ### SQL for RAP and BEAT II Trial Version 1.0  
 ### Based on this DDL, the program is constructed.
 ### Programmed by Shigeru SAITO, MD, FACC, FSCAI, FJCC
 ### on January 4th, 2018.
 ###############################################################################################

CREATE TABLE IF NOT EXISTS `hp_tbls` (
	`id` INT( 11 ) NOT NULL AUTO_INCREMENT,
	`hp_name` VARCHAR( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
	`hp_login_name` VARCHAR( 32 ) UNIQUE NOT NULL DEFAULT '',
	`hp_pwd` VARCHAR( 32 ) NOT NULL DEFAULT '',
	`hp_dr_email_in_charge` VARCHAR( 128 ) NOT NULL DEFAULT '',
	`hp_address` VARCHAR( 256 )CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
	`is_active_hp` BOOLEAN NOT NULL DEFAULT '0',
	`is_usable_hp` BOOLEAN NOT NULL DEFAULT '1',
	`is_deleted_hp` BOOLEAN NOT NULL DEFAULT '0',
	`country_code` TINYINT(2) NOT NULL DEFAULT '1',			/* Country Code; By using this code , 1: JAPAN*/
															/* interface such as feet or lb can be aplied. >10: feet/lb */
	`random_index` INT( 11 ) NOT NULL DEFAULT '0',
	`is_created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY (`id`),
	UNIQUE(`hp_name`)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `dr_tbls` (
	`id` INT( 11 ) NOT NULL AUTO_INCREMENT,
	`sirname` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
	`firstname` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',	
	`is_male` BOOLEAN NOT NULL DEFAULT '1',
	`birth_year` DATE NOT NULL DEFAULT '0000-00-00',
	`hp_tbl_id` INT( 11 ) NOT NULL DEFAULT '0',
	`job_kind` TINYINT( 2 ) NOT NULL DEFAULT '1',
	`email` VARCHAR( 128 ) NOT NULL DEFAULT '',
	`dr_pwd` VARCHAR( 512 ) NOT NULL DEFAULT '',
	`clue` TINYINT( 1 ) NOT NULL DEFAULT '1',
	`hINT` VARCHAR( 512 )NOT NULL DEFAULT '',
	`created` DATE NOT NULL DEFAULT '0000-00-00 00:00:00',
	`login_date` DATE NOT NULL DEFAULT '0000-00-00 00:00:00',
	`ip` VARCHAR(15) NOT NULL DEFAULT '000.000.000.000',
	`dr_url` VARCHAR(60) NOT NULL DEFAULT '',
	`is_active` BOOLEAN NOT NULL DEFAULT '0',
	`is_usable` BOOLEAN NOT NULL DEFAULT '1',
	`is_deleted` BOOLEAN NOT NULL DEFAULT '0',
	`is_pi` BOOLEAN NOT NULL DEFAULT '0',
	`is_created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	`modified` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	PRIMARY KEY (`id`),
	UNIQUE(`email`),
	INDEX(`email`)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `login_logs` (
	`id` INT( 11 ) NOT NULL AUTO_INCREMENT,
	`dr_tbl_id` INT( 11 ) NOT NULL DEFAULT '0',
	`login_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	`login_ip` VARCHAR( 15 ) NOT NULL DEFAULT '000.000.000.000',
	PRIMARY KEY(`id`),
	INDEX(`dr_tbl_id`)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `pt_tbls` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `hp_tbl_id` INT(11) NOT NULL DEFAULT '0',
  `registration_dr_id` INT(11) NOT NULL DEFAULT '0',
  `registration_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `arbitrary_id` VARCHAR(20) NOT NULL DEFAULT '',
  `is_consent` BOOLEAN NOT NULL DEFAULT '0',
  `consent_date` DATE NOT NULL DEFAULT '0000-00-00',
  `is_dra` BOOLEAN NOT NULL DEFAULT '0',		/* 0: ConventionalRadial Approach */
  														/* 1: Distal Radial Approach */
  ###############################################################################################
  ######################### Beginnin of Screen (1) Case Registration #########################
  ###############################################################################################
  `age` INT(3) NOT NULL DEFAULT '0',					/* Age in year */
  `is_male` TINYINT(1) NOT NULL DEFAULT '1',			/* 0: female, 1: male */
  `bh` INT(3) NOT NULL DEFAULT '0',						/* body height in CM */
  `bw` FLOAT(4,1) NOT NULL DEFAULT '0',					/* body weight in Kg */
  `is_dm` BOOLEAN NOT NULL DEFAULT '0',				/* 0: no diabetes, 1: NIDDM, 2: IDDM */
  `is_ht` BOOLEAN NOT NULL DEFAULT '0',				/* 0: not hypertensive, 1: hypertensive */
  `is_hl` BOOLEAN NOT NULL DEFAULT '0',				/* 0: not dyslipidemic, 1: dyslipidemic */
  `is_hu` BOOLEAN NOT NULL DEFAULT '0',				/* 0: not hyperuricemic, 1: hyperuricemic */
  `is_sm` BOOLEAN NOT NULL DEFAULT '0',				/* 0: not smoker at all, 1: Ex-smoker, 2: Current Smoker */
  `is_fhx` BOOLEAN NOT NULL DEFAULT '0',				/* 0: no family history of ischemic heart disease, 1: positive family history of IHD */
  `ckd` INT(2) NOT NULL DEFAULT '0',				/* 0: no CKD, 1: Stage I Protein+ GFR>=90, 2: Stage II Protein+ GFR>=60 */
  														/* 3: Stage III GFR>=30, 4: Stage IV GFR>=15, 5: Stage V Renal Failure */
  `is_cva` BOOLEAN NOT NULL DEFAULT '0',				/* 0: no CerebroVascular Accident, 1: CVA+ */
  `is_pad` BOOLEAN NOT NULL DEFAULT '0',				/* 0: no Peripheral Vascular Disease, 1: PAD+ */
  `hx_of_omi` BOOLEAN NOT NULL DEFAULT '0',			/* 0: no history of previous old myocardial infarction, 1: history of OMI+ */
  `hx_of_pci` BOOLEAN NOT NULL DEFAULT '0',			/* 0: no history of previous PCI, 1: history of PCI+ */
  `hx_of_cabg` BOOLEAN NOT NULL DEFAULT '0',			/* 0: no history of previous coronary bypass surgery, 1: history of CABG+ */
  `hx_of_heart_d` BOOLEAN NOT NULL DEFAULT '0',		/* 0: no history of any heart disease, 1: history of any heart disease+ */
  `comorbidity` TEXT NOT NULL,							/* Any free TEXT */
  `is_scr1_complete` BOOLEAN NOT NULL DEFAULT '0',	/* Screen (1) is alredy registered? 0: not, 1: finished */
  ###############################################################################################
  ######################### Beginnin of Screen (2) Lab_Cathe #########################
  ###############################################################################################
  `rbc` INT(3) NOT NULL DEFAULT '0',					/* RBC x 10^4 */
  `hb` FLOAT(3,1) NOT NULL DEFAULT '0.0',				/* Hemoglobin g/dl */
  `pl` FLOAT(4,1) NOT NULL DEFAULT '0.0',				/* Platelet x 10^4 */
  `cre` FLOAT(3,1) NOT NULL DEFAULT '0.0',				/* Creatinine mg/dl */
  `is_aspirin` BOOLEAN NOT NULL DEFAULT '1',			/* 0: no aspirin, 1: with aspirin */
  `is_thieno` BOOLEAN NOT NULL DEFAULT '1',			/* 0: no thienopyridine, 1: with */
  `is_p2y12` BOOLEAN NOT NULL DEFAULT '0',			/* 0: no p2y12 inhibitor, 1: with */
  `is_warfalin` BOOLEAN NOT NULL DEFAULT '0',		/* 0: no worfalin, 1: with */
  `is_heparin` BOOLEAN NOT NULL DEFAULT '1',			/* 0: no heparin, 1: with heparin */
  `is_2b3a` BOOLEAN NOT NULL DEFAULT '0',			/* 0: no IIb/IIIa antagnonist, 1: with IIb/IIIa antagnonist */
  `biggest_cath_size` TINYINT(1) NOT NULL DEFAULT '0',	/* 1: 4Fr, 2: 5Fr, 3: 6Fr */
  `is_pci` BOOLEAN NOT NULL DEFAULT '0',				/* 0: diagnostic only, 1: going to PCI */
  `assigned_artery_success` BOOLEAN NOT NULL DEFAULT '0', /* 0: success, 1: failed */
  `final_access_artery` TINYINT(1) NOT NULL DEFAULT '0',	/* 0: right CRA, 1: right DRA, 2: left CRA, 3: left DRA, 4: extra-radial */
  `is_prev_puncture` BOOLEAN NOT NULL DEFAULT '0',	/* 0: no previous puncture of the final access side radial argery */
  														/* 1: previous history of puncture */
  `hitting_ra_times` INT(2) NOT NULL DEFAULT '1',			/* How many hitting of radial artery to successful cannulation */
  														/* 10: failed puncture */
  `spasm` BOOLEAN NOT NULL DEFAULT '0',				/* Spasm during whole procedure 0: nothing, 1: any spasm */
  `arterial_cannulation_time` INT(3) NOT NULL DEFAULT '0',			/* Local to successful arterial cannulation in minutes */
  `proc_time` INT(3) NOT NULL DEFAULT '0',				/* Total procedure time in minutes */
  `radiation` INT(4) NOT NULL DEFAULT '0',				/* Total radiation dose in mGy */
  `contrast` INT(4) NOT NULL DEFAULT '0',				/* Total contrast dye volume in ml */
  `puncture_pain` INT (2) NOT NULL DEFAULT '0',					/* Pain score between 0 and 3, none, slight, much, extreme */
  `is_scr2_complete` BOOLEAN NOT NULL DEFAULT '0',	/* Screen (2) is alredy registered? 0: not, 1: finished */
  ###############################################################################################
  ######################### Beginnin of Screen (3) Outcomes #########################
  ###############################################################################################
  `hemostasis_pain` INT (2) NOT NULL DEFAULT '0',					/* Pain score between 0 and 3, none, slight, much, extreme */
  `to_hemostasis_minutes` INT( 3 ) NOT NULL DEFAULT '0',	/* hemostasis time in minutes to final hemostasis */
  `rao_discharge` BOOLEAN NOT NULL DEFAULT '0',		/* Radial Artery Occlusion at discharge 0: patent, 1: occluded */
  `barc_bleeding` INT(2) NOT NULL DEFAULT '0',		/* Puncture Site Bleeding at dischage */
  														/* 0: type 0, 1: type 1, 2: type 2, 3: type 3a, 4: type 3b, 5 type 3c, 6: type 4, 7: type 5a, 8: type 5b */ 
  `pro_succ` BOOLEAN NOT NULL DEFAULT '1',			/* Procedure Success of Index Procedure (PCI or CAG) 0: failed, 1: succeeded */
  `hand_function` INT(2) NOT NULL DEFAULT '0',	/* 0: no disability, 1: minor disability, 2: moderate disability, 3: need help */
  `any_comment` TEXT NOT NULL, 								/* Any TEXT message */
  `is_anomaly` BOOLEAN NOT NULL DEFAULT '0',	/* if radial artery has anomaly or narrowing, set to 1 */
  `anomaly_comment` TEXT NOT NULL, 		/* describe anomaly */
  `is_scr3_complete` BOOLEAN NOT NULL DEFAULT '0' ,	/* Screen (3) is alredy registered? 0: not, 1: finished */
  ###############################################################################################
  ######################### End of Screen (3) of Case Registration #########################
  ###############################################################################################
  `is_deleted` BOOLEAN NOT NULL DEFAULT '0',
  `is_finalized` BOOLEAN NOT NULL DEFAULT '0',
  `finalized_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_access_dr` INT(11) NOT NULL DEFAULT '0',
  `finalized_dr`  INT(11) NOT NULL DEFAULT '0',
  `is_created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_access_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`),
  UNIQUE(`arbitrary_id`),
  KEY `hp_tbl_id` (`hp_tbl_id`),
  KEY `registration_dr_id` (`registration_dr_id`),
  INDEX (`last_access_dr`),
  INDEX (`arbitrary_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `pt_access_log` (
	`id` INT( 11 ) NOT NULL AUTO_INCREMENT,
	`pt_tbl_id` INT( 11 ) NOT NULL DEFAULT '0',
	`access_dr_id` INT( 11 ) NOT NULL DEFAULT '0',
	`access_date` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
	`access_ip` VARCHAR( 15 ) NOT NULL DEFAULT '000.000.000.000',
	PRIMARY KEY (`id`),	
	INDEX(`pt_tbl_id`),
	INDEX(`access_dr_id`)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;

CREATE TABLE IF NOT EXISTS `random_tbls` (
	`id` INT( 11 ) NOT NULL AUTO_INCREMENT,
	`dra_or_cra` TINYINT( 1 ) NOT NULL DEFAULT '0',		/* 0: randomized to DRA, 1: randomized to CRA */
	PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci AUTO_INCREMENT=0;

これからプログラムの修正にとりかかります

投稿者: (KAMAKURA & SAPPORO)Dr_Radialist

Expert Interventional Cardiologist and Amateur Computer Programmer

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です