<commit_before>-- Populate.sql sets the increment to begin at 10000, but all tests expect it to start at 1, so set it back to 1 for the test DB population
SELECT setval('qiita.study_study_id_seq', 1, false);

-- Insert some users in the system. Passwords are 'password' for all users
INSERT INTO qiita.qiita_user (email, user_level_id, password, name,
	affiliation, address, phone) VALUES
	('<EMAIL>', 4,
	'$2a$12$gnUi8Qg.0tvW243v889BhOBhWLIHyIJjjgaG6dxuRJkUM8nXG9Efe', 'Dude',
	'Nowhere University', '123 fake st, Apt 0, Faketown, CO 80302',
	'111-222-3344'),
	('<EMAIL>', 3,
	'$2a$12$gnUi8Qg.0tvW243v889BhOBhWLIHyIJjjgaG6dxuRJkUM8nXG9Efe', 'Shared',
	'Nowhere University', '123 fake st, Apt 0, Faketown, CO 80302',
	'111-222-3344'),
	('<EMAIL>', 1,
	'$2a$12$gnUi8Qg.0tvW243v889BhOBhWLIHyIJjjgaG6dxuRJkUM8nXG9Efe', 'Admin',
	'Owner University', '312 noname st, Apt K, Nonexistantown, CO 80302',
	'222-444-6789'),
	('<EMAIL>', 4,
	'$2a$12$gnUi8Qg.0tvW243v889BhOBhWLIHyIJjjgaG6dxuRJkUM8nXG9Efe', 'Demo',
	'Qitta Dev', '1345 Colorado Avenue', '303-492-1984');

-- Insert some study persons
INSERT INTO qiita.study_person (name, email, affiliation, address, phone) VALUES
	('LabDude', '<EMAIL>', 'knight lab', '123 lab street', '121-222-3333'),
	('empDude', '<EMAIL>', 'broad', NULL, '444-222-3333'),
	('PIDude', '<EMAIL>', 'Wash U', '123 PI street', NULL);

-- Insert a study: EMP 1001
INSERT INTO qiita.study (email, emp_person_id, first_contact,
	funding, timeseries_type_id, lab_person_id, metadata_complete,
	mixs_compliant, most_recent_contact, number_samples_collected,
	number_samples_promised, portal_type_id, principal_investigator_id, reprocess,
	spatial_series, study_title, study_alias, study_description,
	study_abstract, vamps_id) VALUES
	('<EMAIL>', 2, '2014-05-19 16:10', NULL, 1, 1, TRUE, TRUE,
	'2014-05-19 16:11', 27, 27, 2, 3, FALSE, FALSE,
	'Identification of the Microbiomes for Cannabis Soils', 'Cannabis Soils', 'Analysis of the Cannabis Plant Microbiome',
	'This is a preliminary study to examine the microbiota associated with the Cannabis plant. Soils samples from the bulk soil, soil associated with the roots, and the rhizosphere were extracted and the DNA sequenced. Roots from three independent plants of different strains were examined. These roots were obtained November 11, 2011 from plants that had been harvested in the summer. Future studies will attempt to analyze the soils and rhizospheres from the same location at different time points in the plant lifecycle.',
	NULL);

-- Add some environmental packages to the study
INSERT INTO qiita.study_environmental_package (study_id, environmental_package_name) VALUES (1, 'soil'), (1, 'plant-associated');

-- Insert study_users (share study 1 with shared user)
INSERT INTO qiita.study_users (study_id, email) VALUES (1, '<EMAIL>');

-- Insert PMIDs for study
INSERT INTO qiita.study_pmid (study_id, pmid) VALUES (1, '123456'), (1, '7891011');

-- Insert an investigation
INSERT INTO qiita.investigation (investigation_name, investigation_description, contact_person_id) VALUES
	('TestInvestigation', 'An investigation for testing purposes', 3);

-- Insert investigation_study (link study 1 with investigation 1)
INSERT INTO qiita.investigation_study (investigation_id, study_id) VALUES (1, 1);

-- Insert the study experimental factor for study 1
INSERT INTO qiita.study_experimental_factor (study_id, efo_id) VALUES (1, 1);

-- Insert the raw data filepaths for study 1
INSERT INTO qiita.filepath (filepath, filepath_type_id, checksum, checksum_algorithm_id, data_directory_id) VALUES
('1_s_G1_L001_sequences.fastq.gz', 1, '852952723', 1, 5),
('1_s_G1_L001_sequences_barcodes.fastq.gz', 3, '852952723', 1, 5),
('2_sequences.fastq.gz', 1, '852952723', 1, 5),
('2_sequences_barcodes.fastq.gz', 3, '852952723', 1, 5);

-- Insert the raw data information for study 1
INSERT INTO qiita.raw_data (filetype_id) VALUES (3), (2);

-- Insert (link) the raw data with the raw filepaths
INSERT INTO qiita.raw_filepath (raw_data_id, filepath_id) VALUES (1, 1), (1, 2);

-- Insert (link) the study with the raw data
INSERT INTO qiita.study_raw_data (study_id, raw_data_id) VALUES (1, 1), (1, 2);

-- Add the study_sample for study 1
INSERT INTO qiita.study_sample (study_id, sample_id) VALUES
	(1, '1.SKB8.640193'),
	(1, '1.SKD8.640184'),
	(1, '1.SKB7.640196'),
	(1, '1.SKM9.640192'),
	(1, '1.SKM4.640180'),
	(1, '1.SKM5.640177'),
	(1, '1.SKB5.640181'),
	(1, '1.SKD6.640190'),
	(1, '1.SKB2.640194'),
	(1, '1.SKD2.640178'),
	(1, '1.SKM7.640188'),
	(1, '1.SKB1.640202'),
	(1, '1.SKD1.640179'),
	(1, '1.SKD3.640198'),
	(1, '1.SKM8.640201'),
	(1, '1.SKM2.640199'),
	(1, '1.SKB9.640200'),
	(1, '1.SKD5.640186'),
	(1, '1.SKM3.640197'),
	(1, '1.SKD9.640182'),
	(1, '1.SKB4.640189'),
	(1, '1.SKD7.640191'),
	(1, '1.SKM6.640187'),
	(1, '1.SKD4.640185'),
	(1, '1.SKB3.640195'),
	(1, '1.SKB6.640176'),
	(1, '1.SKM1.640183');

-- Add the study sample columns for study 1
INSERT INTO qiita.study_sample_columns (study_id, column_name, column_type) VALUES
	(1, 'sample_id', 'varchar'),
	(1, 'SEASON_ENVIRONMENT', 'varchar'),
	(1, 'ASSIGNED_FROM_GEO', 'varchar'),
	(1, 'TEXTURE', 'varchar'),
	(1, 'TAXON_ID', 'varchar'),
	(1, 'DEPTH', 'float8'),
	(1, 'HOST_TAXID', 'varchar'),
	(1, 'COMMON_NAME', 'varchar'),
	(1, 'WATER_CONTENT_SOIL', 'float8'),
	(1, 'ELEVATION', 'float8'),
	(1, 'TEMP', 'float8'),
	(1, 'TOT_NITRO', 'float8'),
	(1, 'SAMP_SALINITY', 'float8'),
	(1, 'ALTITUDE', 'float8'),
	(1, 'ENV_BIOME', 'varchar'),
	(1, 'COUNTRY', 'varchar'),
	(1, 'PH', 'float8'),
	(1, 'ANONYMIZED_NAME', 'varchar'),
	(1, 'TOT_ORG_CARB', 'float8'),
	(1, 'Description_duplicate', 'varchar'),
	(1, 'ENV_FEATURE', 'varchar'),
	(1, 'physical_specimen_location', 'varchar'),
	(1, 'physical_specimen_remaining', 'bool'),
	(1, 'dna_extracted', 'bool'),
	(1, 'sample_type', 'varchar'),
	(1, 'collection_timestamp', 'timestamp'),
	(1, 'host_subject_id', 'varchar'),
	(1, 'description', 'varchar'),
	(1, 'latitude', 'varchar'),
	(1, 'longitude', 'varchar');

-- Crate the sample_1 dynamic table
CREATE TABLE qiita.sample_1 (
	sample_id						varchar,
	SEASON_ENVIRONMENT				varchar,
	ASSIGNED_FROM_GEO				varchar,
	TEXTURE							varchar,
	TAXON_ID						varchar,
	DEPTH							float8,
	HOST_TAXID						varchar,
	COMMON_NAME						varchar,
	WATER_CONTENT_SOIL				float8,
	ELEVATION						float8,
	TEMP							float8,
	TOT_NITRO						float8,
	SAMP_SALINITY					float8,
	ALTITUDE						float8,
	ENV_BIOME						varchar,
	COUNTRY							varchar,
	PH								float8,
	ANONYMIZED_NAME					varchar,
	TOT_ORG_CARB					float8,
	Description_duplicate			varchar,
	ENV_FEATURE						varchar,
	physical_specimen_location		varchar,
	physical_specimen_remaining		bool,
	dna_extracted					bool,
	sample_type						varchar,
	collection_timestamp			timestamp,
	host_subject_id					varchar,
	description						varchar,
	latitude						varchar,
	longitude						varchar,
	CONSTRAINT pk_sample_1 PRIMARY KEY ( sample_id )
);

-- Populates the sample_1 dynamic table
INSERT INTO qiita.sample_1 (sample_id, SEASON_ENVIRONMENT, ASSIGNED_FROM_GEO, TEXTURE, TAXON_ID, DEPTH, HOST_TAXID, COMMON_NAME, WATER_CONTENT_SOIL, ELEVATION, TEMP, TOT_NITRO, SAMP_SALINITY, ALTITUDE, ENV_BIOME, COUNTRY, PH, ANONYMIZED_NAME, TOT_ORG_CARB, Description_duplicate, ENV_FEATURE, physical_specimen_location, physical_specimen_remaining, dna_extracted, sample_type, collection_timestamp, host_subject_id, description, latitude, longitude) VALUES
	('1.SKM7.640188', 'winter', 'n', '63.1 sand, 17.7 silt, 19.2 clay', '1118232', 0.15, '3483', 'root metagenome', 0.101, 114, 15, 1.3, 7.44, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.82, 'SKM7', 3.31, 'Bucu Roots', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:B6', 'Cannabis Soil Microbiome', 60.1102854322, 74.7123248382),
	('1.SKD9.640182', 'winter', 'n', '66 sand, 16.3 silt, 17.7 clay', '1118232', 0.15, '3483', 'root metagenome', 0.178, 114, 15, 1.51, 7.1, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.82, 'SKD9', 4.32, 'Diesel Root', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:D3', 'Cannabis Soil Microbiome', 23.1218032799, 42.838497795),
	('1.SKM8.640201', 'winter', 'n', '63.1 sand, 17.7 silt, 19.2 clay', '1118232', 0.15, '3483', 'root metagenome', 0.101, 114, 15, 1.3, 7.44, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.82, 'SKM8', 3.31, 'Bucu Roots', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:D8', 'Cannabis Soil Microbiome', 3.21190859967, 26.8138925876),
	('1.SKB8.640193', 'winter', 'n', '64.6 sand, 17.6 silt, 17.8 clay', '1118232', 0.15, '3483', 'root metagenome', 0.164, 114, 15, 1.41, 7.15, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.94, 'SKB8', 5, 'Burmese root', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:M7', 'Cannabis Soil Microbiome', 74.0894932572, 65.3283470202),
	('1.SKD2.640178', 'winter', 'n', '66 sand, 16.3 silt, 17.7 clay', '410658', 0.15, '3483', 'soil metagenome', 0.178, 114, 15, 1.51, 7.1, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.8, 'SKD2', 4.32, 'Diesel bulk', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:B5', 'Cannabis Soil Microbiome', 53.5050692395, 31.6056761814),
	('1.SKM3.640197', 'winter', 'n', '63.1 sand, 17.7 silt, 19.2 clay', '410658', 0.15, '3483', 'soil metagenome', 0.101, 114, 15, 1.3, 7.44, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.82, 'SKM3', 3.31, 'Bucu bulk', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:B7', 'Cannabis Soil Microbiome', 63.6505562766, 31.2003474585),
	('1.SKM4.640180', 'winter', 'n', '63.1 sand, 17.7 silt, 19.2 clay', '939928', 0.15, '3483', 'rhizosphere metagenome', 0.101, 114, 15, 1.3, 7.44, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.82, 'SKM4', 3.31, 'Bucu Rhizo', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:D2', 'Cannabis Soil Microbiome', 31.7167821863, 95.5088566087),
	('1.SKB9.640200', 'winter', 'n', '64.6 sand, 17.6 silt, 17.8 clay', '1118232', 0.15, '3483', 'root metagenome', 0.164, 114, 15, 1.41, 7.15, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.8, 'SKB9', 5, 'Burmese root', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:B3', 'Cannabis Soil Microbiome', 12.6245524972, 96.0693176066),
	('1.SKB4.640189', 'winter', 'n', '64.6 sand, 17.6 silt, 17.8 clay', '939928', 0.15, '3483', 'rhizosphere metagenome', 0.164, 114, 15, 1.41, 7.15, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.94, 'SKB4', 5, 'Burmese Rhizo', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:D7', 'Cannabis Soil Microbiome', 43.9614715197, 82.8516734159),
	('1.SKB5.640181', 'winter', 'n', '64.6 sand, 17.6 silt, 17.8 clay', '939928', 0.15, '3483', 'rhizosphere metagenome', 0.164, 114, 15, 1.41, 7.15, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.94, 'SKB5', 5, 'Burmese Rhizo', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:M4', 'Cannabis Soil Microbiome', 10.6655599093, 70.784770579),
	('1.SKB6.640176', 'winter', 'n', '64.6 sand, 17.6 silt, 17.8 clay', '939928', 0.15, '3483', 'rhizosphere metagenome', 0.164, 114, 15, 1.41, 7.15, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.94, 'SKB6', 5, 'Burmese Rhizo', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:D5', 'Cannabis Soil Microbiome', 78.3634273709, 74.423907894),
	('1.SKM2.640199', 'winter', 'n', '63.1 sand, 17.7 silt, 19.2 clay', '410658', 0.15, '3483', 'soil metagenome', 0.101, 114, 15, 1.3, 7.44, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.82, 'SKM2', 3.31, 'Bucu bulk', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:D4', 'Cannabis Soil Microbiome', 82.8302905615, 86.3615778099),
	('1.SKM5.640177', 'winter', 'n', '63.1 sand, 17.7 silt, 19.2 clay', '939928', 0.15, '3483', 'rhizosphere metagenome', 0.101, 114, 15, 1.3, 7.44, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.82, 'SKM5', 3.31, 'Bucu Rhizo', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:M3', 'Cannabis Soil Microbiome', 44.9725384282, 66.1920014699),
	('1.SKB1.640202', 'winter', 'n', '64.6 sand, 17.6 silt, 17.8 clay', '410658', 0.15, '3483', 'soil metagenome', 0.164, 114, 15, 1.41, 7.15, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.94, 'SKB1', 5, 'Burmese bulk', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:M2', 'Cannabis Soil Microbiome', 4.59216095574, 63.5115213108),
	('1.SKD8.640184', 'winter', 'n', '66 sand, 16.3 silt, 17.7 clay', '1118232', 0.15, '3483', 'root metagenome', 0.178, 114, 15, 1.51, 7.1, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.8, 'SKD8', 4.32, 'Diesel Root', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:D9', 'Cannabis Soil Microbiome', 57.571893782, 32.5563076447),
	('1.SKD4.640185', 'winter', 'n', '66 sand, 16.3 silt, 17.7 clay', '939928', 0.15, '3483', 'rhizosphere metagenome', 0.178, 114, 15, 1.51, 7.1, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.8, 'SKD4', 4.32, '<NAME>', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:M9', 'Cannabis Soil Microbiome', 40.8623799474, 6.66444220187),<commit_msg>Fixing latitude/longitude types on populate<commit_after>-- Populate.sql sets the increment to begin at 10000, but all tests expect it to start at 1, so set it back to 1 for the test DB population
SELECT setval('qiita.study_study_id_seq', 1, false);

-- Insert some users in the system. Passwords are 'password' for all users
INSERT INTO qiita.qiita_user (email, user_level_id, password, name,
	affiliation, address, phone) VALUES
	('<EMAIL>', 4,
	'$2a$12$gnUi8Qg.0tvW243v889BhOBhWLIHyIJjjgaG6dxuRJkUM8nXG9Efe', 'Dude',
	'Nowhere University', '123 fake st, Apt 0, Faketown, CO 80302',
	'111-222-3344'),
	('<EMAIL>', 3,
	'$2a$12$gnUi8Qg.0tvW243v889BhOBhWLIHyIJjjgaG6dxuRJkUM8nXG9Efe', 'Shared',
	'Nowhere University', '123 fake st, Apt 0, Faketown, CO 80302',
	'111-222-3344'),
	('<EMAIL>', 1,
	'$2a$12$gnUi8Qg.0tvW243v889BhOBhWLIHyIJjjgaG6dxuRJkUM8nXG9Efe', 'Admin',
	'Owner University', '312 noname st, Apt K, Nonexistantown, CO 80302',
	'222-444-6789'),
	('<EMAIL>', 4,
	'$2a$12$gnUi8Qg.0tvW243v889BhOBhWLIHyIJjjgaG6dxuRJkUM8nXG9Efe', 'Demo',
	'Qitta Dev', '1345 Colorado Avenue', '303-492-1984');

-- Insert some study persons
INSERT INTO qiita.study_person (name, email, affiliation, address, phone) VALUES
	('LabDude', '<EMAIL>', 'knight lab', '123 lab street', '121-222-3333'),
	('empDude', '<EMAIL>', 'broad', NULL, '444-222-3333'),
	('PIDude', '<EMAIL>', 'Wash U', '123 PI street', NULL);

-- Insert a study: EMP 1001
INSERT INTO qiita.study (email, emp_person_id, first_contact,
	funding, timeseries_type_id, lab_person_id, metadata_complete,
	mixs_compliant, most_recent_contact, number_samples_collected,
	number_samples_promised, portal_type_id, principal_investigator_id, reprocess,
	spatial_series, study_title, study_alias, study_description,
	study_abstract, vamps_id) VALUES
	('<EMAIL>', 2, '2014-05-19 16:10', NULL, 1, 1, TRUE, TRUE,
	'2014-05-19 16:11', 27, 27, 2, 3, FALSE, FALSE,
	'Identification of the Microbiomes for Cannabis Soils', 'Cannabis Soils', 'Analysis of the Cannabis Plant Microbiome',
	'This is a preliminary study to examine the microbiota associated with the Cannabis plant. Soils samples from the bulk soil, soil associated with the roots, and the rhizosphere were extracted and the DNA sequenced. Roots from three independent plants of different strains were examined. These roots were obtained November 11, 2011 from plants that had been harvested in the summer. Future studies will attempt to analyze the soils and rhizospheres from the same location at different time points in the plant lifecycle.',
	NULL);

-- Add some environmental packages to the study
INSERT INTO qiita.study_environmental_package (study_id, environmental_package_name) VALUES (1, 'soil'), (1, 'plant-associated');

-- Insert study_users (share study 1 with shared user)
INSERT INTO qiita.study_users (study_id, email) VALUES (1, '<EMAIL>');

-- Insert PMIDs for study
INSERT INTO qiita.study_pmid (study_id, pmid) VALUES (1, '123456'), (1, '7891011');

-- Insert an investigation
INSERT INTO qiita.investigation (investigation_name, investigation_description, contact_person_id) VALUES
	('TestInvestigation', 'An investigation for testing purposes', 3);

-- Insert investigation_study (link study 1 with investigation 1)
INSERT INTO qiita.investigation_study (investigation_id, study_id) VALUES (1, 1);

-- Insert the study experimental factor for study 1
INSERT INTO qiita.study_experimental_factor (study_id, efo_id) VALUES (1, 1);

-- Insert the raw data filepaths for study 1
INSERT INTO qiita.filepath (filepath, filepath_type_id, checksum, checksum_algorithm_id, data_directory_id) VALUES
('1_s_G1_L001_sequences.fastq.gz', 1, '852952723', 1, 5),
('1_s_G1_L001_sequences_barcodes.fastq.gz', 3, '852952723', 1, 5),
('2_sequences.fastq.gz', 1, '852952723', 1, 5),
('2_sequences_barcodes.fastq.gz', 3, '852952723', 1, 5);

-- Insert the raw data information for study 1
INSERT INTO qiita.raw_data (filetype_id) VALUES (3), (2);

-- Insert (link) the raw data with the raw filepaths
INSERT INTO qiita.raw_filepath (raw_data_id, filepath_id) VALUES (1, 1), (1, 2);

-- Insert (link) the study with the raw data
INSERT INTO qiita.study_raw_data (study_id, raw_data_id) VALUES (1, 1), (1, 2);

-- Add the study_sample for study 1
INSERT INTO qiita.study_sample (study_id, sample_id) VALUES
	(1, '1.SKB8.640193'),
	(1, '1.SKD8.640184'),
	(1, '1.SKB7.640196'),
	(1, '1.SKM9.640192'),
	(1, '1.SKM4.640180'),
	(1, '1.SKM5.640177'),
	(1, '1.SKB5.640181'),
	(1, '1.SKD6.640190'),
	(1, '1.SKB2.640194'),
	(1, '1.SKD2.640178'),
	(1, '1.SKM7.640188'),
	(1, '1.SKB1.640202'),
	(1, '1.SKD1.640179'),
	(1, '1.SKD3.640198'),
	(1, '1.SKM8.640201'),
	(1, '1.SKM2.640199'),
	(1, '1.SKB9.640200'),
	(1, '1.SKD5.640186'),
	(1, '1.SKM3.640197'),
	(1, '1.SKD9.640182'),
	(1, '1.SKB4.640189'),
	(1, '1.SKD7.640191'),
	(1, '1.SKM6.640187'),
	(1, '1.SKD4.640185'),
	(1, '1.SKB3.640195'),
	(1, '1.SKB6.640176'),
	(1, '1.SKM1.640183');

-- Add the study sample columns for study 1
INSERT INTO qiita.study_sample_columns (study_id, column_name, column_type) VALUES
	(1, 'sample_id', 'varchar'),
	(1, 'SEASON_ENVIRONMENT', 'varchar'),
	(1, 'ASSIGNED_FROM_GEO', 'varchar'),
	(1, 'TEXTURE', 'varchar'),
	(1, 'TAXON_ID', 'varchar'),
	(1, 'DEPTH', 'float8'),
	(1, 'HOST_TAXID', 'varchar'),
	(1, 'COMMON_NAME', 'varchar'),
	(1, 'WATER_CONTENT_SOIL', 'float8'),
	(1, 'ELEVATION', 'float8'),
	(1, 'TEMP', 'float8'),
	(1, 'TOT_NITRO', 'float8'),
	(1, 'SAMP_SALINITY', 'float8'),
	(1, 'ALTITUDE', 'float8'),
	(1, 'ENV_BIOME', 'varchar'),
	(1, 'COUNTRY', 'varchar'),
	(1, 'PH', 'float8'),
	(1, 'ANONYMIZED_NAME', 'varchar'),
	(1, 'TOT_ORG_CARB', 'float8'),
	(1, 'Description_duplicate', 'varchar'),
	(1, 'ENV_FEATURE', 'varchar'),
	(1, 'physical_specimen_location', 'varchar'),
	(1, 'physical_specimen_remaining', 'bool'),
	(1, 'dna_extracted', 'bool'),
	(1, 'sample_type', 'varchar'),
	(1, 'collection_timestamp', 'timestamp'),
	(1, 'host_subject_id', 'varchar'),
	(1, 'description', 'varchar'),
	(1, 'latitude', 'float8'),
	(1, 'longitude', 'float8');

-- Crate the sample_1 dynamic table
CREATE TABLE qiita.sample_1 (
	sample_id						varchar,
	SEASON_ENVIRONMENT				varchar,
	ASSIGNED_FROM_GEO				varchar,
	TEXTURE							varchar,
	TAXON_ID						varchar,
	DEPTH							float8,
	HOST_TAXID						varchar,
	COMMON_NAME						varchar,
	WATER_CONTENT_SOIL				float8,
	ELEVATION						float8,
	TEMP							float8,
	TOT_NITRO						float8,
	SAMP_SALINITY					float8,
	ALTITUDE						float8,
	ENV_BIOME						varchar,
	COUNTRY							varchar,
	PH								float8,
	ANONYMIZED_NAME					varchar,
	TOT_ORG_CARB					float8,
	Description_duplicate			varchar,
	ENV_FEATURE						varchar,
	physical_specimen_location		varchar,
	physical_specimen_remaining		bool,
	dna_extracted					bool,
	sample_type						varchar,
	collection_timestamp			timestamp,
	host_subject_id					varchar,
	description						varchar,
	latitude						float8,
	longitude						float8,
	CONSTRAINT pk_sample_1 PRIMARY KEY ( sample_id )
);

-- Populates the sample_1 dynamic table
INSERT INTO qiita.sample_1 (sample_id, SEASON_ENVIRONMENT, ASSIGNED_FROM_GEO, TEXTURE, TAXON_ID, DEPTH, HOST_TAXID, COMMON_NAME, WATER_CONTENT_SOIL, ELEVATION, TEMP, TOT_NITRO, SAMP_SALINITY, ALTITUDE, ENV_BIOME, COUNTRY, PH, ANONYMIZED_NAME, TOT_ORG_CARB, Description_duplicate, ENV_FEATURE, physical_specimen_location, physical_specimen_remaining, dna_extracted, sample_type, collection_timestamp, host_subject_id, description, latitude, longitude) VALUES
	('1.SKM7.640188', 'winter', 'n', '63.1 sand, 17.7 silt, 19.2 clay', '1118232', 0.15, '3483', 'root metagenome', 0.101, 114, 15, 1.3, 7.44, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.82, 'SKM7', 3.31, 'Bucu Roots', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:B6', 'Cannabis Soil Microbiome', 60.1102854322, 74.7123248382),
	('1.SKD9.640182', 'winter', 'n', '66 sand, 16.3 silt, 17.7 clay', '1118232', 0.15, '3483', 'root metagenome', 0.178, 114, 15, 1.51, 7.1, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.82, 'SKD9', 4.32, 'Diesel Root', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:D3', 'Cannabis Soil Microbiome', 23.1218032799, 42.838497795),
	('1.SKM8.640201', 'winter', 'n', '63.1 sand, 17.7 silt, 19.2 clay', '1118232', 0.15, '3483', 'root metagenome', 0.101, 114, 15, 1.3, 7.44, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.82, 'SKM8', 3.31, 'Bucu Roots', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:D8', 'Cannabis Soil Microbiome', 3.21190859967, 26.8138925876),
	('1.SKB8.640193', 'winter', 'n', '64.6 sand, 17.6 silt, 17.8 clay', '1118232', 0.15, '3483', 'root metagenome', 0.164, 114, 15, 1.41, 7.15, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.94, 'SKB8', 5, 'Burmese root', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:M7', 'Cannabis Soil Microbiome', 74.0894932572, 65.3283470202),
	('1.SKD2.640178', 'winter', 'n', '66 sand, 16.3 silt, 17.7 clay', '410658', 0.15, '3483', 'soil metagenome', 0.178, 114, 15, 1.51, 7.1, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.8, 'SKD2', 4.32, 'Diesel bulk', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:B5', 'Cannabis Soil Microbiome', 53.5050692395, 31.6056761814),
	('1.SKM3.640197', 'winter', 'n', '63.1 sand, 17.7 silt, 19.2 clay', '410658', 0.15, '3483', 'soil metagenome', 0.101, 114, 15, 1.3, 7.44, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.82, 'SKM3', 3.31, 'Bucu bulk', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:B7', 'Cannabis Soil Microbiome', 63.6505562766, 31.2003474585),
	('1.SKM4.640180', 'winter', 'n', '63.1 sand, 17.7 silt, 19.2 clay', '939928', 0.15, '3483', 'rhizosphere metagenome', 0.101, 114, 15, 1.3, 7.44, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.82, 'SKM4', 3.31, 'Bu<NAME>', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:D2', 'Cannabis Soil Microbiome', 31.7167821863, 95.5088566087),
	('1.SKB9.640200', 'winter', 'n', '64.6 sand, 17.6 silt, 17.8 clay', '1118232', 0.15, '3483', 'root metagenome', 0.164, 114, 15, 1.41, 7.15, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.8, 'SKB9', 5, 'Burmese root', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:B3', 'Cannabis Soil Microbiome', 12.6245524972, 96.0693176066),
	('1.SKB4.640189', 'winter', 'n', '64.6 sand, 17.6 silt, 17.8 clay', '939928', 0.15, '3483', 'rhizosphere metagenome', 0.164, 114, 15, 1.41, 7.15, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.94, 'SKB4', 5, 'Burmese Rhizo', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:D7', 'Cannabis Soil Microbiome', 43.9614715197, 82.8516734159),
	('1.SKB5.640181', 'winter', 'n', '64.6 sand, 17.6 silt, 17.8 clay', '939928', 0.15, '3483', 'rhizosphere metagenome', 0.164, 114, 15, 1.41, 7.15, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.94, 'SKB5', 5, 'Burmese Rhizo', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:M4', 'Cannabis Soil Microbiome', 10.6655599093, 70.784770579),
	('1.SKB6.640176', 'winter', 'n', '64.6 sand, 17.6 silt, 17.8 clay', '939928', 0.15, '3483', 'rhizosphere metagenome', 0.164, 114, 15, 1.41, 7.15, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.94, 'SKB6', 5, 'Burmese Rhizo', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:D5', 'Cannabis Soil Microbiome', 78.3634273709, 74.423907894),
	('1.SKM2.640199', 'winter', 'n', '63.1 sand, 17.7 silt, 19.2 clay', '410658', 0.15, '3483', 'soil metagenome', 0.101, 114, 15, 1.3, 7.44, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.82, 'SKM2', 3.31, 'Bucu bulk', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:D4', 'Cannabis Soil Microbiome', 82.8302905615, 86.3615778099),
	('1.SKM5.640177', 'winter', 'n', '63.1 sand, 17.7 silt, 19.2 clay', '939928', 0.15, '3483', 'rhizosphere metagenome', 0.101, 114, 15, 1.3, 7.44, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.82, 'SKM5', 3.31, 'Bucu Rhizo', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:M3', 'Cannabis Soil Microbiome', 44.9725384282, 66.1920014699),
	('1.SKB1.640202', 'winter', 'n', '64.6 sand, 17.6 silt, 17.8 clay', '410658', 0.15, '3483', 'soil metagenome', 0.164, 114, 15, 1.41, 7.15, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.94, 'SKB1', 5, 'Burmese bulk', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:M2', 'Cannabis Soil Microbiome', 4.59216095574, 63.5115213108),
	('1.SKD8.640184', 'winter', 'n', '66 sand, 16.3 silt, 17.7 clay', '1118232', 0.15, '3483', 'root metagenome', 0.178, 114, 15, 1.51, 7.1, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.8, 'SKD8', 4.32, 'Diesel Root', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:D9', 'Cannabis Soil Microbiome', 57.571893782, 32.5563076447),
	('1.SKD4.640185', 'winter', 'n', '66 sand, 16.3 silt, 17.7 clay', '939928', 0.15, '3483', 'rhizosphere metagenome', 0.178, 114, 15, 1.51, 7.1, 0, 'ENVO:Temperate grasslands, savannas, and shrubland biome', 'GAZ:United States of America', 6.8, 'SKD4', 4.32, '<NAME>', 'ENVO:plant-associated habitat', 'ANL', TRUE, TRUE, 'ENVO:soil', '2011-11-11 13:00', '1001:M9', 'Cannabis Soil Microbiome', 40.8623799474, 6.66444220187),
{'fraction_non_alphanumeric': 0.17755861294398428, 'fraction_numerical': 0.14614628667696195, 'mean_word_length': 5.375699261579772, 'pattern_counts': {'":': 0, '<': 24, '<?xml version=': 0, '>': 24, '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': '122760', 'n_tokens_mistral': 14869, 'n_tokens_neox': 11989, 'n_words': 3082}