/Mosaic-Catalogue-Controllers/src/main/java/org/mosaic/catalogue/controllers/presto/PrestoController.java

package org.mosaic.catalogue.controllers.presto;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.mosaic.catalogue.controllers.errors.AjaxErrorHandler;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.augmentiq.maxiq.base.dao.sql.operation.configuration.ObjectSerializationHandler;
import com.augmentiq.maxiq.base.dao.sql.operation.configuration.exceptions.SystemException;
import com.augmentiq.maxiq.cache.support.Cache;
import com.augmentiq.maxiq.constant.cache.CacheConstants;
import com.augmentiq.maxiq.core.dao.configuration.generic.genericdao.GenericStoreDao;
import com.augmentiq.maxiq.entity.model.configuration.bean.DataSource;
import com.augmentiq.maxiq.entity.model.configuration.bean.FieldMapping;
import com.augmentiq.maxiq.model.connector.datanode.DataNode;
import com.augmentiq.maxiq.services.presto.PrestoServices;
import com.google.gson.Gson;

@Controller
public class PrestoController extends AjaxErrorHandler {

	@Autowired
	private PrestoServices prestoServices;

	@RequestMapping(value = "/runPrestoQuery", method = { RequestMethod.POST })
	public @ResponseBody List<FieldMapping> runPrestoQuery(@RequestBody String query) throws SystemException {

		HashSet<String> tableSet = new HashSet<String>();
		String from = "from\\s+(?:\\w+\\.)*(\\w+)($|\\s+[WHERE,JOIN,START\\s+WITH,ORDER\\s+BY,GROUP\\s+BY])";
		String join = "join\\s+(?:\\w+\\.)*(\\w+)($|\\s+[WHERE,JOIN,START\\s+WITH,ORDER\\s+BY,GROUP\\s+BY])";
		ArrayList<String> patternlist = new ArrayList<>();
		patternlist.add(from);
		patternlist.add(join);
		for (String pattern : patternlist) {
			Pattern p = Pattern.compile(pattern, Pattern.CASE_INSENSITIVE);
			Matcher m = p.matcher(query);

			while (m.find()) {
				tableSet.add(m.group(1));
			}
		}
		if (!tableSet.isEmpty()) {
			for (String tableName : tableSet) {
				String json = GenericStoreDao.getConfigurationByName(tableName);
				if (json != null) {
					String prestoTableAlias = null;
					DataSource dataSource = (DataSource) ObjectSerializationHandler
							.toObject(StringUtils.replace(json, "\\", "\\\\"), DataSource.class);
					prestoTableAlias = dataSource.getPrestoTableAlias();
					query = query.replaceAll(tableName, prestoTableAlias);
					query = query.replaceAll(prestoTableAlias + "_", tableName + "_");
					query = query.replaceAll("_" + prestoTableAlias, "_" + tableName);
				}
			}
		}
		Connection connection = null;
		List<FieldMapping> fieldMappingList = new ArrayList<FieldMapping>();
		// String query = "select
		// emp.employee_id,emp.dept_id,employee_name,dept.dept_name,dept.dept_head
		// from mysql.unitrax.employee emp INNER JOIN postgres.public.department
		// dept ON emp.dept_id = dept.dept_id";
		// query with mongo db
		// String query = "select
		// emp.employee_id,emp.dept_id,employee_name,dept.dept_name,dept.dept_head,sal.emp_salary
		// from mysql.unitrax.employee emp INNER JOIN postgres.public.department
		// dept ON emp.dept_id = dept.dept_id INNER JOIN
		// mongodb.datacatalog.salary sal ON sal.emp_id = emp.employee_id";
		Statement statement = null;
		try {

			Class.forName("com.facebook.presto.jdbc.PrestoDriver");

			connection = DriverManager.getConnection("jdbc:presto://localhost:9090/", "root", "");
			// "jdbc:presto://localhost:8080/mysql/tutorials", "tutorials", “");

			// connect mysql server tutorials database here
			statement = connection.createStatement();

			// String sql="select
			// emp.employee_id,emp.dept_id,employee_name,dept.dept_name,dept.dept_role
			// from mysql.unitrax.employee emp INNER JOIN
			// postgres.public.department dept ON emp.dept_id = dept.dept_id";

			// select mysql table author table two columns
			ResultSet columnresultSet = statement.executeQuery(query);
			ResultSet dataresultSet = statement.executeQuery(query);
			while (columnresultSet.next()) {
				int columnCount = columnresultSet.getMetaData().getColumnCount();
				for (int i = 1; i <= columnCount; i++) {
					FieldMapping feildmapping = new FieldMapping();

					feildmapping.setFieldName(columnresultSet.getMetaData().getColumnLabel(i));
					feildmapping.setPosition(Long.valueOf(i));
					fieldMappingList.add(feildmapping);
				}
				break;
			}
			while (dataresultSet.next()) {

				int count = 0;
				int columnCount = dataresultSet.getMetaData().getColumnCount();

				for (int i = 1; i <= columnCount; i++) {

					String rowLevelColumnValue = null;
					try {
						rowLevelColumnValue = dataresultSet.getString(i);
						if (rowLevelColumnValue.contains("\"")) {
							rowLevelColumnValue = rowLevelColumnValue.replaceAll("\"", "");
						}
					} catch (Exception e) {
						rowLevelColumnValue = null;
					}
					FieldMapping fieldMapping = fieldMappingList.get(i - 1);
					fieldMapping.getSampleRecords().add(count, rowLevelColumnValue);

				}

				count++;

			}

			columnresultSet.close();
			columnresultSet.close();
			statement.close();
			connection.close();

		} catch (SQLException sqlException) {
			sqlException.printStackTrace();
		} catch (Exception exception) {
			exception.printStackTrace();
		}

		return fieldMappingList;
	}

	@RequestMapping(value = "/getPublishedDS", method = { RequestMethod.GET, RequestMethod.POST })
	public @ResponseBody DataNode getPublishedDataSource() throws SystemException {

		return prestoServices.getPublishedDataSource();
	}

}