<commit_before>/*
 * Copyright (C) 2010 PIRAmIDE-SP3 authors
 *
 * 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.
 * 
 * This software consists of contributions made by many individuals, 
 * listed below:
 *
 * Author: <NAME> <<EMAIL>>
 *         <NAME> <<EMAIL>>
 *         <NAME> <<EMAIL>>
 *
 */
package piramide.interaction.reasoner.db.deploy;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Vector;

import org.apache.commons.codec.binary.Base64;
import org.apache.commons.io.FileUtils;

import piramide.interaction.reasoner.db.DatabaseManager;

import com.csvreader.CsvReader;

public class DatabaseMigrator extends DatabaseManager {

	private static final String INVALID_FILE_PATH = "BadOnes.txt";
	
	public DatabaseMigrator() throws Exception {
	}

	public static void main(String [] args) throws Exception {
		new DatabaseMigrator().populateTables();
	}
	
	/**
	* Given the Downloaded table, we populate the Devices and Trends tables.
	*/
	public void populateTables() {
		int invalidData = 0;
		
		try{
			final Connection con = DriverManager.getConnection( CONNECTION_URL, USERNAME, PASSWORD);
			
			final Statement stmtClearTrends = con.createStatement();
			stmtClearTrends.execute("DELETE FROM Trends");
			stmtClearTrends.close();
			
			final Statement stmtClearDevices = con.createStatement();
			stmtClearDevices.execute("DELETE FROM Devices");
			stmtClearDevices.close();
			
			
			con.setAutoCommit(false);
			
			
			final Statement stmtDownloaded = con.createStatement();
			final ResultSet rsDownloaded   = stmtDownloaded.executeQuery("SELECT device_name, wurfl_id, marketing_name, brand_name, model_name, " + 
										"real_height, real_width, reso_height, " + 
										"reso_width, value, region FROM Downloaded");
			final List<String> deviceNames = new Vector<String>();
			final PreparedStatement stmtDevices = con.prepareStatement("" +
								"INSERT INTO Devices(device_name, wurfl_id, marketing_name, brand_name, model_name, " +
								"real_height, real_width, real_size, reso_height, reso_width, reso_size) " +
								"Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");
			final PreparedStatement stmtTrends = con.prepareStatement("" +
								"INSERT INTO Trends( device_name, region, month, year, value, error_margin ) " +
								"Values(?, ?, ?, ?, ?, ? )");
			final Map<String, Integer> month2month = new HashMap<String, Integer>();
			month2month.put("Jan",  Integer.valueOf(1));
			month2month.put("Feb",  Integer.valueOf(2));
			month2month.put("Mar",  Integer.valueOf(3));
			month2month.put("Apr",  Integer.valueOf(4));
			month2month.put("May",  Integer.valueOf(5));
			month2month.put("Jun",  Integer.valueOf(6));
			month2month.put("Jul",  Integer.valueOf(7));
			month2month.put("Aug",  Integer.valueOf(8));
			month2month.put("Sep",  Integer.valueOf(9));
			month2month.put("Oct",  Integer.valueOf(10));
			month2month.put("Nov",  Integer.valueOf(11));
			month2month.put("Dec",  Integer.valueOf(12));
						
			int counter = 0;
			final Set<String> badOnes = new HashSet<String>();
								
			while(rsDownloaded.next()){
				++counter;
				
				if(counter % 100 == 0){
					System.out.println("committed... " + counter);
					con.commit();
				}
				
				final String deviceName = rsDownloaded.getString("device_name");
				
				if(!deviceNames.contains(deviceName)){
					stmtDevices.setString(1, deviceName);
					stmtDevices.setString(2, rsDownloaded.getString("wurfl_id"));
					stmtDevices.setString(3, rsDownloaded.getString("marketing_name"));
					stmtDevices.setString(4, rsDownloaded.getString("brand_name"));
					stmtDevices.setString(5, rsDownloaded.getString("model_name"));
					stmtDevices.setFloat(6, rsDownloaded.getFloat("real_height"));
					stmtDevices.setFloat(7, rsDownloaded.getFloat("real_width"));
					stmtDevices.setFloat(8, rsDownloaded.getFloat("real_height") * rsDownloaded.getFloat("real_width"));
					stmtDevices.setInt(9, rsDownloaded.getInt("reso_height"));
					stmtDevices.setInt(10, rsDownloaded.getInt("reso_width"));
					stmtDevices.setInt(11, rsDownloaded.getInt("reso_height") * rsDownloaded.getInt("reso_width"));
					stmtDevices.execute();
					deviceNames.add(deviceName);
				}
					
				final String base64csv = rsDownloaded.getString("value");
				
				final CsvReader csvReader2 = buildCSVReader(base64csv);
				if(!csvReader2.readRecord() && base64csv.length() != 924){
					final byte [] binaryCSV = new Base64().decode(base64csv);
					final String stringCSV = new String(binaryCSV, "utf-8");
					if(stringCSV.contains("could not be interpreted")){
						badOnes.add(  rsDownloaded.getString("region") + "@" + deviceName );
					}
				}
				
				
				final CsvReader csvReader = buildCSVReader(base64csv);
				
				int previousMonth = 0;
				int previousYear  = 0;
				float currentValue = 0.0f;
				String currentErrorMargin = "";
				
				boolean executed = false;
				while(csvReader.readRecord()){
					executed = true;
					final String week        = csvReader.get(0);
					final float value        = Float.parseFloat(csvReader.get(1));
					final String errorMargin = csvReader.get(2);
					
					final String [] weekParts = week.split(" ");
					
					final int month = month2month.get(weekParts[0]).intValue();
					final int year  = Integer.parseInt(weekParts[2]);
					
					if(month == previousMonth && year == previousYear){
						currentValue += value;
						currentErrorMargin = currentErrorMargin + "; " + errorMargin;
						continue;
					}
					
					stmtTrends.setString(1, deviceName);
					stmtTrends.setString(2, rsDownloaded.getString("region"));
					stmtTrends.setInt(3, previousMonth);
					stmtTrends.setInt(4, previousYear);
					stmtTrends.setFloat(5, currentValue);
					stmtTrends.setString(6, currentErrorMargin);
					stmtTrends.execute();<commit_msg> * Adapting to the new release of WURFL<commit_after>/*
 * Copyright (C) 2010 PIRAmIDE-SP3 authors
 *
 * 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.
 * 
 * This software consists of contributions made by many individuals, 
 * listed below:
 *
 * Author: <NAME> <<EMAIL>>
 *         <NAME> <<EMAIL>>
 *         <NAME> <<EMAIL>>
 *
 */
package piramide.interaction.reasoner.db.deploy;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Vector;

import org.apache.commons.codec.binary.Base64;
import org.apache.commons.io.FileUtils;

import piramide.interaction.reasoner.db.DatabaseManager;

import com.csvreader.CsvReader;

public class DatabaseMigrator extends DatabaseManager {

	private static final String INVALID_FILE_PATH = "BadOnes.txt";
	
	public DatabaseMigrator() throws Exception {
	}

	public static void main(String [] args) throws Exception {
		new DatabaseMigrator().populateTables();
	}
	
	/**
	* Given the Downloaded table, we populate the Devices and Trends tables.
	*/
	public void populateTables() {
		int invalidData = 0;
		
		try{
			final Connection con = DriverManager.getConnection( CONNECTION_URL, USERNAME, PASSWORD);
			
			final Statement stmtClearTrends = con.createStatement();
			stmtClearTrends.execute("DELETE FROM Trends");
			stmtClearTrends.close();
			
			final Statement stmtClearDevices = con.createStatement();
			stmtClearDevices.execute("DELETE FROM Devices");
			stmtClearDevices.close();
			
			
			con.setAutoCommit(false);
			
			
			final Statement stmtDownloaded = con.createStatement();
			final ResultSet rsDownloaded   = stmtDownloaded.executeQuery("SELECT device_name, wurfl_id, marketing_name, brand_name, model_name, " + 
										"real_height, real_width, reso_height, " + 
										"reso_width, value, region FROM Downloaded");
			final List<String> deviceNames = new Vector<String>();
			final PreparedStatement stmtDevices = con.prepareStatement("" +
								"INSERT INTO Devices(device_name, wurfl_id, marketing_name, brand_name, model_name, " +
								"real_height, real_width, real_size, reso_height, reso_width, reso_size) " +
								"Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )");
			final PreparedStatement stmtTrends = con.prepareStatement("" +
								"INSERT INTO Trends( device_name, region, month, year, value, error_margin ) " +
								"Values(?, ?, ?, ?, ?, ? )");
			final Map<String, Integer> month2month = new HashMap<String, Integer>();
			month2month.put("Jan",  Integer.valueOf(1));
			month2month.put("Feb",  Integer.valueOf(2));
			month2month.put("Mar",  Integer.valueOf(3));
			month2month.put("Apr",  Integer.valueOf(4));
			month2month.put("May",  Integer.valueOf(5));
			month2month.put("Jun",  Integer.valueOf(6));
			month2month.put("Jul",  Integer.valueOf(7));
			month2month.put("Aug",  Integer.valueOf(8));
			month2month.put("Sep",  Integer.valueOf(9));
			month2month.put("Oct",  Integer.valueOf(10));
			month2month.put("Nov",  Integer.valueOf(11));
			month2month.put("Dec",  Integer.valueOf(12));
						
			int counter = 0;
			final Set<String> badOnes = new HashSet<String>();
								
			while(rsDownloaded.next()){
				++counter;
				
				if(counter % 100 == 0){
					System.out.println("committed... " + counter);
					con.commit();
				}
				
				final String deviceName = rsDownloaded.getString("device_name");
				
				if(deviceName.equals("htc x7500") || deviceName.equals("samsung sgh-m300") 
						|| deviceName.equals("nokia verizon") || deviceName.equals("kddi a1407pt") 
						|| deviceName.equals("nokia thr880i") || deviceName.equals("softbank softbank 910t") 
						|| deviceName.equals("nokia 8860") || deviceName.equals("<NAME> w580i")
						|| deviceName.equals("motorola c139") || deviceName.equals("samsung stripe")
						|| deviceName.equals("samsung sgh-c414") || deviceName.equals("samsung gt-e2121l")
						|| deviceName.equals("samsung sgh-z400") || deviceName.equals("i-mobile 606")
						|| deviceName.equals("softbank v302sh") || deviceName.equals("motorola krzr k1c")
						)
					continue;
				
				if(!deviceNames.contains(deviceName)){
					stmtDevices.setString(1, deviceName);
					stmtDevices.setString(2, rsDownloaded.getString("wurfl_id"));
					stmtDevices.setString(3, rsDownloaded.getString("marketing_name"));
					stmtDevices.setString(4, rsDownloaded.getString("brand_name"));
					stmtDevices.setString(5, rsDownloaded.getString("model_name"));
					stmtDevices.setFloat(6, rsDownloaded.getFloat("real_height"));
					stmtDevices.setFloat(7, rsDownloaded.getFloat("real_width"));
					stmtDevices.setFloat(8, rsDownloaded.getFloat("real_height") * rsDownloaded.getFloat("real_width"));
					stmtDevices.setInt(9, rsDownloaded.getInt("reso_height"));
					stmtDevices.setInt(10, rsDownloaded.getInt("reso_width"));
					stmtDevices.setInt(11, rsDownloaded.getInt("reso_height") * rsDownloaded.getInt("reso_width"));
					stmtDevices.execute();
					deviceNames.add(deviceName);
				}
					
				final String base64csv = rsDownloaded.getString("value");
				
				final CsvReader csvReader2 = buildCSVReader(base64csv);
				if(!csvReader2.readRecord() && base64csv.length() != 924){
					final byte [] binaryCSV = new Base64().decode(base64csv);
					final String stringCSV = new String(binaryCSV, "utf-8");
					if(stringCSV.contains("could not be interpreted")){
						badOnes.add(  rsDownloaded.getString("region") + "@" + deviceName );
					}
				}
				
				
				final CsvReader csvReader = buildCSVReader(base64csv);
				
				int previousMonth = 0;
				int previousYear  = 0;
				float currentValue = 0.0f;
				String currentErrorMargin = "";
				
				boolean executed = false;
				while(csvReader.readRecord()){
					executed = true;
					final String week        = csvReader.get(0);
					final float value        = Float.parseFloat(csvReader.get(1));
					final String errorMargin = csvReader.get(2);
					
					final String [] weekParts = week.split(" ");
					final Integer monthString = month2month.get(weekParts[0]);
					if(monthString == null){
						System.err.println("Failed to load device: " + deviceName);
						continue;
					}
					final int month = monthString.intValue();
					final int year  = Integer.parseInt(weekParts[2]);
					
					if(month == previousMonth && year == previousYear){
						currentValue += value;
						currentErrorMargin = currentErrorMargin + "; " + errorMargin;
						continue;
					}
					
					final String region = rsDownloaded.getString("region");
					
					//System.out.println(deviceName + " " + region + " " + month + " " + year);
					
					stmtTrends.setString(1, deviceName);
					stmtTrends.setString(2, region);
					stmtTrends.setInt(3, previousMonth);
					stmtTrends.setInt(4, previousYear);
					stmtTrends.setFloat(5, currentValue);
					stmtTrends.setString(6, currentErrorMargin);
					stmtTrends.execute();
{'fraction_non_alphanumeric': 0.12929180828188944, 'fraction_numerical': 0.015814663244780466, 'mean_word_length': 3.8693009118541033, 'pattern_counts': {'":': 0, '<': 34, '<?xml version=': 0, '>': 34, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 44, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2018606', 'n_tokens_mistral': 5450, 'n_tokens_neox': 4379, 'n_words': 947}