<commit_before>/*******************************************************************************
 * Copyright 2014 Observational Health Data Sciences and Informatics
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *   http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 ******************************************************************************/
package org.ohdsi.medlineXmlToDatabase;

import java.sql.Types;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.ohdsi.databases.ConnectionWrapper;
import org.ohdsi.databases.ConnectionWrapper.FieldInfo;
import org.ohdsi.utilities.XmlTools;
import org.ohdsi.utilities.collections.OneToManyList;
import org.ohdsi.utilities.collections.OneToManySet;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

/**
 * In this class, we do the actual work of reading the XML and inserting the data into the database
 * 
 * @author MSCHUEMI
 *
 */
public class MedlineCitationParser {
	
	private static String						MEDLINE_CITATION	= "MedlineCitation";
	private OneToManySet<String, String>		tables2Fields		= new OneToManySet<String, String>();
	private OneToManyList<String, FieldInfo>	tables2FieldInfos	= new OneToManyList<String, FieldInfo>();
	private String								pmid;
	private String								pmid_version;
	private ConnectionWrapper					connectionWrapper;
	
	public MedlineCitationParser(ConnectionWrapper connectionWrapper, String schema) {
		this.connectionWrapper = connectionWrapper;
		Set<String> tables = new HashSet<String>();
		for (String table : connectionWrapper.getTableNames(schema)) {
			table = Abbreviator.unAbbreviate(table);
			if (table.toLowerCase().startsWith("medlinecitation"))
				tables.add(table);
		}
		for (String table : tables)
			for (FieldInfo fieldInfo : connectionWrapper.getFieldInfo(Abbreviator.abbreviate(table))) {
				tables2Fields.put(table, fieldInfo.name);
				tables2FieldInfos.put(table, fieldInfo);
			}
	}
	
	public void parseAndInjectIntoDB(Node citation) {
		findPmidAndVersion(citation);
		
		connectionWrapper.setBatchMode(true);
		deleteAllForPmidAndVersion();
		Map<String, String> keys = new HashMap<String, String>();
		keys.put("PMID", pmid);
		keys.put("PMID_Version", pmid_version);
		parseNode(citation, "", "MedlineCitation", new HashMap<String, String>(), true, keys);
		try {
			connectionWrapper.setBatchMode(false);
		} catch (Exception e) {
			System.err.println("Problem inserting in to DB for PMID " + pmid + ": " + e.getMessage());
			e.printStackTrace();
		}
	}
	
	/**
	 * Record could be an update of a previous entry. Just in case, all previous data must be removed
	 * 
	 * @param sqls
	 */
	private void deleteAllForPmidAndVersion() {
		for (String table : tables2Fields.keySet()) {
			String sql = "DELETE FROM " + Abbreviator.abbreviate(table) + " WHERE pmid = " + pmid + " AND pmid_version = " + pmid_version;
			connectionWrapper.execute(sql);
		}
		
	}
	
	private void insertIntoDB(String table, Map<String, String> field2Value) {
		removeFieldsNotInDb(table, field2Value);
		truncateFieldsToDbSize(table, field2Value);
		connectionWrapper.insertIntoTable(table, field2Value);
	}
	
	private void removeFieldsNotInDb(String table, Map<String, String> field2Value) {
		Set<String> fieldsInDb = tables2Fields.get(table.toLowerCase());
		Iterator<Map.Entry<String, String>> iterator = field2Value.entrySet().iterator();
		while (iterator.hasNext()) {
			String field = iterator.next().getKey();
			if (!fieldsInDb.contains(Abbreviator.abbreviate(field.toLowerCase()))) {
				System.err.println("Ignoring field " + field + " in table " + table
						+ " because field is not in DB (meaning it wasn't encountered in the XML files before now)");
				iterator.remove();
			}
		}
	}
	
	private void truncateFieldsToDbSize(String table, Map<String, String> field2Value) {
		for (FieldInfo fieldInfo : tables2FieldInfos.get(table.toLowerCase())) {
			if (fieldInfo.type == Types.VARCHAR || fieldInfo.type == Types.CLOB) {
				String name = null;
				for (String field : field2Value.keySet())
					if (Abbreviator.abbreviate(field).toLowerCase().equals(fieldInfo.name.toLowerCase())) {
						name = field;
						break;
					}
				if (name != null) {
					String value = field2Value.get(name);
					if (value.length() > fieldInfo.length) {
						System.err.println("Truncating field " + fieldInfo.name + " in table " + table + " from " + value.length() + " to " + fieldInfo.length
								+ " characters for PMID " + pmid);
						value = value.substring(0, fieldInfo.length);
						field2Value.put(name, value);
					}
				}<commit_msg>Detecting invalid integer values in XML and dropping them (as found in the versionId attribute for PMID 30793009 in pubmed19n1459.xml)<commit_after>/*******************************************************************************
 * Copyright 2014 Observational Health Data Sciences and Informatics
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *   http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 ******************************************************************************/
package org.ohdsi.medlineXmlToDatabase;

import java.sql.Types;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.ohdsi.databases.ConnectionWrapper;
import org.ohdsi.databases.ConnectionWrapper.FieldInfo;
import org.ohdsi.utilities.XmlTools;
import org.ohdsi.utilities.collections.OneToManyList;
import org.ohdsi.utilities.collections.OneToManySet;
import org.w3c.dom.NamedNodeMap;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

/**
 * In this class, we do the actual work of reading the XML and inserting the data into the database
 * 
 * @author MSCHUEMI
 *
 */
public class MedlineCitationParser {
	
	private static String						MEDLINE_CITATION	= "MedlineCitation";
	private OneToManySet<String, String>		tables2Fields		= new OneToManySet<String, String>();
	private OneToManyList<String, FieldInfo>	tables2FieldInfos	= new OneToManyList<String, FieldInfo>();
	private String								pmid;
	private String								pmid_version;
	private ConnectionWrapper					connectionWrapper;
	
	public MedlineCitationParser(ConnectionWrapper connectionWrapper, String schema) {
		this.connectionWrapper = connectionWrapper;
		Set<String> tables = new HashSet<String>();
		for (String table : connectionWrapper.getTableNames(schema)) {
			table = Abbreviator.unAbbreviate(table);
			if (table.toLowerCase().startsWith("medlinecitation"))
				tables.add(table);
		}
		for (String table : tables)
			for (FieldInfo fieldInfo : connectionWrapper.getFieldInfo(Abbreviator.abbreviate(table))) {
				tables2Fields.put(table, fieldInfo.name);
				tables2FieldInfos.put(table, fieldInfo);
			}
	}
	
	public void parseAndInjectIntoDB(Node citation) {
		findPmidAndVersion(citation);
		
		connectionWrapper.setBatchMode(true);
		deleteAllForPmidAndVersion();
		Map<String, String> keys = new HashMap<String, String>();
		keys.put("PMID", pmid);
		keys.put("PMID_Version", pmid_version);
		parseNode(citation, "", "MedlineCitation", new HashMap<String, String>(), true, keys);
		try {
			connectionWrapper.setBatchMode(false);
		} catch (Exception e) {
			System.err.println("Problem inserting in to DB for PMID " + pmid + ": " + e.getMessage());
			e.printStackTrace();
		}
	}
	
	/**
	 * Record could be an update of a previous entry. Just in case, all previous data must be removed
	 * 
	 * @param sqls
	 */
	private void deleteAllForPmidAndVersion() {
		for (String table : tables2Fields.keySet()) {
			String sql = "DELETE FROM " + Abbreviator.abbreviate(table) + " WHERE pmid = " + pmid + " AND pmid_version = " + pmid_version;
			connectionWrapper.execute(sql);
		}
		
	}
	
	private void insertIntoDB(String table, Map<String, String> field2Value) {
		removeFieldsNotInDb(table, field2Value);
		truncateFieldsToDbSize(table, field2Value);
		dropInvalidValues(table, field2Value);
		connectionWrapper.insertIntoTable(table, field2Value);
	}
	
	private void dropInvalidValues(String table, Map<String, String> field2Value) {
		for (FieldInfo fieldInfo : tables2FieldInfos.get(table.toLowerCase())) {
			if (fieldInfo.type == Types.INTEGER || fieldInfo.type == Types.BIGINT) {
				String name = null;
				for (String field : field2Value.keySet())
					if (Abbreviator.abbreviate(field).toLowerCase().equals(fieldInfo.name.toLowerCase())) {
						name = field;
						break;
					}
				if (name != null) {
					String value = field2Value.get(name);
					try {
						Integer.parseInt(value);
					} catch (NumberFormatException e) {
						System.err.println("Error parsing integer value '" + value + "' for field " + fieldInfo.name + " in table " + table + ". Setting to null.");
						field2Value.remove(name);
					}
				}
			}
		}
	}

	private void removeFieldsNotInDb(String table, Map<String, String> field2Value) {
		Set<String> fieldsInDb = tables2Fields.get(table.toLowerCase());
		Iterator<Map.Entry<String, String>> iterator = field2Value.entrySet().iterator();
		while (iterator.hasNext()) {
			String field = iterator.next().getKey();
			if (!fieldsInDb.contains(Abbreviator.abbreviate(field.toLowerCase()))) {
				System.err.println("Ignoring field " + field + " in table " + table
						+ " because field is not in DB (meaning it wasn't encountered in the XML files before now)");
				iterator.remove();
			}
		}
	}
	
	private void truncateFieldsToDbSize(String table, Map<String, String> field2Value) {
		for (FieldInfo fieldInfo : tables2FieldInfos.get(table.toLowerCase())) {
			if (fieldInfo.type == Types.VARCHAR || fieldInfo.type == Types.CLOB) {
				String name = null;
				for (String field : field2Value.keySet())
					if (Abbreviator.abbreviate(field).toLowerCase().equals(fieldInfo.name.toLowerCase())) {
						name = field;
						break;
					}
				if (name != null) {
					String value = field2Value.get(name);
					if (value.length() > fieldInfo.length) {
						System.err.println("Truncating field " + fieldInfo.name + " in table " + table + " from " + value.length() + " to " + fieldInfo.length
								+ " characters for PMID " + pmid);
						value = value.substring(0, fieldInfo.length);
						field2Value.put(name, value);
					}
				}
{'fraction_non_alphanumeric': 0.13819903519742718, 'fraction_numerical': 0.006968018581382884, 'mean_word_length': 4.348781653129479, 'pattern_counts': {'":': 2, '<': 34, '<?xml version=': 0, '>': 36, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 1}, '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': '4938672', 'n_tokens_mistral': 3753, 'n_tokens_neox': 3281, 'n_words': 859}