<commit_before>/* MySql DDL for OpenFurther Biospecimen Model (OFBM) */

/*
  Author: University of Utah Biomedical Informatics Core (BMIC) Team
  Data Architect: <NAME>
  Date: 20140114
  DESCRIPTION:
    The OFBM is a de-normalized biospecimen data mart
    to support ETL data from any single biobanking system.
    It was initially created to provide a simplified data model to connect
    with the OpenFurther data federation framework. 
    However, it may be used for other reporting purpose as well.
    Run ONLY the sections that you need below.
*/

/* BEGIN Possible CLEANUP */
/*
Drop schema ofbm;
create schema ofbm;

set foreign_key_checks=off;
DROP TABLE IF EXISTS ofbm.sample_extid;
DROP TABLE IF EXISTS ofbm.sample_event;
DROP TABLE IF EXISTS ofbm.sample;
DROP TABLE IF EXISTS ofbm.person_dx;
DROP TABLE IF EXISTS ofbm.person_race;
DROP TABLE IF EXISTS ofbm.person_extid;
DROP TABLE IF EXISTS ofbm.person;
DROP TABLE IF EXISTS ofbm.protocol_consent;
DROP TABLE IF EXISTS ofbm.protocol_dx;
DROP TABLE IF EXISTS ofbm.protocol;

Truncate TABLE ofbm.protocol_consent;
Truncate TABLE ofbm.protocol_dx;
Truncate TABLE ofbm.protocol;
Truncate TABLE ofbm.sample_extid;
Truncate TABLE ofbm.sample_event;
Truncate TABLE ofbm.sample;
Truncate TABLE ofbm.person_dx;
Truncate TABLE ofbm.person_race;
Truncate TABLE ofbm.person_extid;
Truncate TABLE ofbm.person;
set foreign_key_checks=on;

*/
/* END CLEANUP */

use ofbm;

/* Person or Patient or Participant */
create table ofbm.person (
  p_id int unsigned comment 'Person ID',
  last_name varchar(64) comment 'Last Name',
  first_name varchar(64) comment 'First Name',
  middle_name varchar(64) comment 'Middle Name',
  gender varchar(100) comment 'Gender',
  ethnicity varchar(100) comment 'Ethnicity',
  birth_dt date comment 'Birth Date',
  birth_yr int unsigned comment 'Birth Year',
  birth_mon int unsigned comment 'Birth Month',
  birth_day int unsigned comment 'Birth Day',
  vital_status varchar(100) comment 'Vital Status',
  death_dt date comment 'Death Date',
  death_yr int unsigned comment 'Death Year',
  ts timestamp default now() comment 'Auto Timestamp to keep track of ETL process',
  primary key (p_id)
)
  engine=innodb
  default charset=utf8
  comment='Person'
;

/* Person Race */
create table ofbm.person_race (
  p_race_id int unsigned auto_increment comment 'Auto-Increment Surrogate ID',
  p_id int unsigned not null comment 'Person ID',
  race varchar(100) comment 'Race',
  ts timestamp default now() comment 'Auto Timestamp to keep track of ETL process',
  primary key (p_race_id)
) 
  engine=innodb 
  default charset=utf8 <commit_msg>Updated some comments for Person Table<commit_after>/* MySql DDL for OpenFurther Biospecimen Model (OFBM) */

/*
  Author: University of Utah Biomedical Informatics Core (BMIC) Team
  Data Architect: <NAME>
  Date: 20140114
  DESCRIPTION:
    The OFBM is a de-normalized biospecimen data mart
    to support ETL data from any single biobanking system.
    It was initially created to provide a simplified data model to connect
    with the OpenFurther data federation framework. 
    However, it may be used for other reporting purpose as well.
    Run ONLY the sections that you need below.
*/

/* BEGIN Possible CLEANUP */
/*
Drop schema ofbm;
create schema ofbm;

set foreign_key_checks=off;
DROP TABLE IF EXISTS ofbm.sample_extid;
DROP TABLE IF EXISTS ofbm.sample_event;
DROP TABLE IF EXISTS ofbm.sample;
DROP TABLE IF EXISTS ofbm.person_dx;
DROP TABLE IF EXISTS ofbm.person_race;
DROP TABLE IF EXISTS ofbm.person_extid;
DROP TABLE IF EXISTS ofbm.person;
DROP TABLE IF EXISTS ofbm.protocol_consent;
DROP TABLE IF EXISTS ofbm.protocol_dx;
DROP TABLE IF EXISTS ofbm.protocol;

Truncate TABLE ofbm.protocol_consent;
Truncate TABLE ofbm.protocol_dx;
Truncate TABLE ofbm.protocol;
Truncate TABLE ofbm.sample_extid;
Truncate TABLE ofbm.sample_event;
Truncate TABLE ofbm.sample;
Truncate TABLE ofbm.person_dx;
Truncate TABLE ofbm.person_race;
Truncate TABLE ofbm.person_extid;
Truncate TABLE ofbm.person;
set foreign_key_checks=on;

*/
/* END CLEANUP */

use ofbm;

/* Person or Patient or Participant */
create table ofbm.person (
  p_id int unsigned comment 'Person ID',
  last_name varchar(64) comment 'Last Name',
  first_name varchar(64) comment 'First Name',
  middle_name varchar(64) comment 'Middle Name',
  gender varchar(100) comment 'Gender Self Reported',
  ethnicity varchar(100) comment 'Ethnicity Hispanic or Not',
  birth_dt date comment 'Birth Date',
  birth_yr int unsigned comment 'Birth Year',
  birth_mon int unsigned comment 'Birth Month',
  birth_day int unsigned comment 'Birth Day',
  vital_status varchar(100) comment 'Vital Status Dead or Alive',
  death_dt date comment 'Death Date',
  death_yr int unsigned comment 'Death Year',
  ts timestamp default now() comment 'Auto Timestamp to keep track of ETL process',
  primary key (p_id)
)
  engine=innodb
  default charset=utf8
  comment='Person'
;

/* Person Race */
create table ofbm.person_race (
  p_race_id int unsigned auto_increment comment 'Auto-Increment Surrogate ID',
  p_id int unsigned not null comment 'Person ID',
  race varchar(100) comment 'Race',
  ts timestamp default now() comment 'Auto Timestamp to keep track of ETL process',
  primary key (p_race_id)
) 
  engine=innodb 
  default charset=utf8 
{'fraction_non_alphanumeric': 0.06964050442311312, 'fraction_numerical': 0.010540184453227932, 'mean_word_length': 4.605485232067511, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2181986', 'n_tokens_mistral': 1784, 'n_tokens_neox': 1648, 'n_words': 641}