/*
 * Copyright (C) 2018, 2019, 2023 Andrew Gegg
 *
 *	This file is part of the Garden Notebook application
 *
 * The Garden Notebook application is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/gpl.html>.
 */

/*
	Change log
	2.0.1   Reduced logging during processResults().
            Initialise cache to reduce rehashing.
            Optimise simple cases
	2.2.0   Support hsqldb dialect
    2.4.0   Support MS SQLServer
    3.0.0	Added plantSpecies(int val...) to get round problem with populating the cacheVarietyBySpecies
    			when adding a new variety.
	3.1.0	Use jakarta implementation of JSON
 */

package uk.co.gardennotebook.mysql;

import uk.co.gardennotebook.spi.*;

import java.time.LocalDateTime;
import java.util.List;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;

import java.sql.*;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.message.EntryMessage;
import java.io.FileWriter;
import java.io.IOException;
import java.io.File;

import jakarta.json.JsonArrayBuilder;
import jakarta.json.JsonBuilderFactory;
import jakarta.json.JsonWriter;
import jakarta.json.JsonWriterFactory;

import java.util.HashMap;
import java.util.stream.Collectors;
import jakarta.json.JsonObjectBuilder;

/**
*
*{@inheritDoc}
*
*	@author	Andy Gegg
*	@version	3.1.0
*	@since	1.0
*/
final class PlantVarietyLister implements IPlantVarietyLister
{
	private static final Logger LOGGER = LogManager.getLogger();

	//	Special case handling rules out DBKeyHandler
	private boolean useId = false;
	private int[] idList = new int[10];
	private int idNext = 0;	// next free slot in list

	private boolean usePlantSpeciesId = false;
	private int[] plantSpeciesIdList = new int[10];
	private int plantSpeciesIdNext = 0;

	private boolean useWhere = false;

	PlantVarietyLister() {}

	@Override
	public List<IPlantVariety> fetch() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("fetch()");

        if (MySQLCache.cachePlantVariety == null || MySQLCache.cachePlantVariety.isEmpty()) //  2.0.1
        {
            initialiseCache();
        }
		List<IPlantVariety> vals = new ArrayList<>();
        
        // handle simple cases  2.0.1
        if (useId && idNext==1 && MySQLCache.cachePlantVariety.containsKey(idList[0]))
        {
            LOGGER.debug("fetch(): simple case, specific PV, already known");
            vals.add(MySQLCache.cachePlantVariety.get(idList[0]));
            return vals;
        }
        if (!useId && usePlantSpeciesId && plantSpeciesIdNext==1 && MySQLCache.cacheVarietyBySpecies.containsKey(plantSpeciesIdList[0]) )
        {
            LOGGER.debug("fetch(): simple case, specific species, already known: psId: {}", plantSpeciesIdList[0]);
            if ((MySQLCache.cacheVarietyBySpecies.get(plantSpeciesIdList[0]) == null) || (MySQLCache.cacheVarietyBySpecies.get(plantSpeciesIdList[0]).isEmpty()) )
            {
                return Collections.emptyList();
            }
			return MySQLCache.cacheVarietyBySpecies.get(plantSpeciesIdList[0]).values().stream().
					sorted((a, b) -> a.getCommonName().compareToIgnoreCase(b.getCommonName())).collect(Collectors.toList());
        }
        // prepare a slot for the PlantSpecies so that a null entry will be known to have no varieties - ON A LATER CALL
        if (usePlantSpeciesId)
        {
            for (int ix=0; ix<plantSpeciesIdNext; ix++)
            {
                MySQLCache.cacheVarietyBySpecies.putIfAbsent(plantSpeciesIdList[ix], null);
            }
        }
        // end 2.0.1
        
		boolean fetchAll = !useWhere;
			String query = "";
		switch (DBConnection.DB_IN_USE)
		{
			case MariaDB, MySQL -> query = buildSQL_MySQL();
			case hsqldb -> query = buildSQL_hsqldb();
			case MSSQLServer -> query = buildSQL_MSSQLServer();
			default -> {
				LOGGER.debug("fetch(): no known rdbms");
				throw new GNDBException(new IllegalStateException("no known RDBMS"));
			}
		}
LOGGER.debug("fetch(): query: {}", query);

		try (Connection conn = DBConnection.getConnection(); Statement stmt = conn.createStatement();)
		{
			ResultSet rs = stmt.executeQuery(query);
			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> vals = processResults_MySQL(rs);
				case hsqldb -> vals = processResults_hsqldb(rs);
				case MSSQLServer -> vals = processResults_MSSQLServer(rs);
				default -> {
					LOGGER.debug("fetch(): no known rdbms");
					throw new GNDBException(new IllegalStateException("no known RDBMS"));
				}
			}
			stmt.close();
		}catch (SQLException ex) {
			LOGGER.error("fetch(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}

		if (vals.isEmpty()) return Collections.emptyList();

		for (IPlantVariety vx : vals)
		{
			MySQLCache.cachePlantVariety.putIfAbsent(vx.getKey(), vx);
            //  2.0.1
            int psId = ((PlantVariety)vx).getPlantSpeciesId();
            MySQLCache.cacheVarietyBySpecies.putIfAbsent(psId, new HashMap<>());
            MySQLCache.cacheVarietyBySpecies.get(psId).put(vx.getKey(), vx);
		}
		if (fetchAll) { MySQLCache.completePlantVariety = true; }

		populatePlantSpecies(vals, fetchAll);

LOGGER.traceExit(log4jEntryMsg);
		return vals;
	}	// fetch()

    private String buildSQL_MySQL()
    {
        StringBuilder query = new StringBuilder("select d.*, c.* from plantvariety as d ");
        query.append("left join (select * from comment where ownerType = 'PV') as c ");
        query.append("on d.plantVarietyId = c.ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query);
        }
        query.append(" order by d.plantSpeciesId, LOWER(d.commonName), d.plantVarietyId, c.date");
        return query.toString();
    }   //  buildSQL_MySQL()

    private String buildSQL_hsqldb()
    {
        StringBuilder query = new StringBuilder("select d.*, c.* from plantvariety as d ");
        query.append("left join (select commentId as c_commentId, ownerId as c_ownerId, date as c_date, comment as c_comment, lastUpdated as c_lastUpdated, created as c_created from comment where ownerType = 'PV') as c ");
        query.append("on d.plantVarietyId = c_ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query);
        }
        query.append(" order by d.plantSpeciesId, LOWER(d.commonName), d.plantVarietyId, c_date");
        return query.toString();
    }   //  buildSQL_hsqldb()

    private String buildSQL_MSSQLServer()
    {
        StringBuilder query = new StringBuilder("select " +
                                                    "d.plantVarietyId as d_plantVarietyId, " +
                                                    "d.plantSpeciesId as d_plantSpeciesId, " +
                                                    "d.commonName as d_commonName, " +
                                                    "d.latinName as d_latinName, " +
                                                    "d.description as d_description, " +
                                                    "d.hardiness as d_hardiness, " +
                                                    "d.lifeType as d_lifeType, " +
                                                    "d.plantType as d_plantType, " +
                                                    "d.utility as d_utility, " +
                                                    "d.synonymSet as d_synonymSet, " +
                                                    "d.created as d_created, " +
                                                    "d.lastUpdated as d_lastUpdated, " +
                                                    " c.* from plantvariety as d ");
        query.append("left join (select commentId as c_commentId, ownerId as c_ownerId, date as c_date, comment as c_comment, lastUpdated as c_lastUpdated, created as c_created from comment where ownerType = 'PV') as c ");
        query.append("on d.plantVarietyId = c_ownerId ");
        if (useWhere)
        {
            useWhere = false;
            buildCommonSQL(query);
        }
        query.append(" order by d.plantSpeciesId, LOWER(d.commonName), d.plantVarietyId, c_date");
        return query.toString();
    }   //  buildSQL_MSSQLServer()

    private void buildCommonSQL(StringBuilder query)
    {
        boolean first = true;
        if (this.useId)
        {
            if (first) query.append(" where ");
            else query.append(" and");
            if (idNext > 1) {
                query.append(" d.plantVarietyId in (");
                for(int ix = 0; ix < idNext; ix++) { query.append(idList[ix]).append(", "); }
                query.replace(query.length()-2, query.length(), ")");
            }
            else
                query.append(" d.plantVarietyId = ").append(idList[0]);
            first = false;
            this.useId = false;
            this.idNext = 0;
        }
        if (this.usePlantSpeciesId)
        {
            if (first) query.append(" where ");
            else query.append(" and");
            if (plantSpeciesIdNext > 1) {
                query.append(" d.plantSpeciesId in (");
                for(int ix =0; ix < plantSpeciesIdNext; ix++) {query.append(plantSpeciesIdList[ix]).append(", ");}
                query.replace(query.length()-2, query.length(), ")");
            }
            else
                query.append(" d.plantSpeciesId = ").append(plantSpeciesIdList[0]);
            first = false;
            this.usePlantSpeciesId = false;
            this.plantSpeciesIdNext = 0;
        }
    }   //  buildCommonSQL()

	private List<IPlantVariety> processResults_MySQL(ResultSet rs) throws SQLException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("processResults_MySQL()");

		List<IPlantVariety> tempList = new ArrayList<>();

		PlantVariety item = null;

		while (rs.next())
		{
			int plantVarietyId = rs.getInt("d.plantVarietyId");
			int plantSpeciesId = rs.getInt("d.plantSpeciesId");
			String commonName = rs.getString("d.commonName");
			String latinName = rs.getString("d.latinName");
			String synonymSet = rs.getString("d.synonymSet");
			String description = rs.getString("d.description");
			String utility = rs.getString("d.utility");
			String hardiness = rs.getString("d.hardiness");
			String lifeType = rs.getString("d.lifeType");
			String plantType = rs.getString("d.plantType");
			LocalDateTime lastUpdated = rs.getTimestamp("d.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d.created").toLocalDateTime();
LOGGER.debug("plantVarietyId: {}, plantSpeciesId: {}, commonName: {}, lastUpdated: {}, created: {}", plantVarietyId, plantSpeciesId, commonName, lastUpdated, created); //  2.0.1
			if (item != null && plantVarietyId == item.getId())
			{// additional comment on the item
LOGGER.debug("processResults(): got additional comment for: {}", item);
				Comment comm = new Comment(rs.getInt("c.commentId"),
					rs.getInt("c.ownerId"),
					"PV",
					rs.getDate("c.date").toLocalDate(),
					rs.getString("c.comment"),
					rs.getTimestamp("c.lastUpdated").toLocalDateTime(),
					rs.getTimestamp("c.created").toLocalDateTime());
LOGGER.debug("processResults(): extra comment is: {}", comm);
				item = new PlantVariety(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c.commentId");
				if (rs.wasNull())
				{// no comment
					item = new PlantVariety(plantVarietyId, plantSpeciesId, commonName, latinName, synonymSet, description, utility, hardiness, lifeType, plantType, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						plantVarietyId,
						"PV",
						rs.getDate("c.date").toLocalDate(),
						rs.getString("c.comment"),
						rs.getTimestamp("c.lastUpdated").toLocalDateTime(),
						rs.getTimestamp("c.created").toLocalDateTime());
LOGGER.debug("processResults(): first comment is: {}", comm);
					item = new PlantVariety(plantVarietyId, plantSpeciesId, commonName, latinName, synonymSet, description, utility, hardiness, lifeType, plantType, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

LOGGER.traceExit(log4jEntryMsg);
		return tempList;
	}	// processResults_MySQL()

	private List<IPlantVariety> processResults_hsqldb(ResultSet rs) throws SQLException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("processResults_hsqldb()");

		List<IPlantVariety> tempList = new ArrayList<>();

		PlantVariety item = null;

		while (rs.next())
		{
			int plantVarietyId = rs.getInt("plantvariety.plantVarietyId");
			int plantSpeciesId = rs.getInt("plantVariety.plantSpeciesId");
			String commonName = rs.getString("plantVariety.commonName");
			String latinName = rs.getString("plantVariety.latinName");
			String synonymSet = rs.getString("plantVariety.synonymSet");
			String description = rs.getString("plantVariety.description");
			String utility = rs.getString("plantVariety.utility");
			String hardiness = rs.getString("plantVariety.hardiness");
			String lifeType = rs.getString("plantVariety.lifeType");
			String plantType = rs.getString("plantVariety.plantType");
			LocalDateTime lastUpdated = rs.getTimestamp("plantVariety.lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("plantVariety.created").toLocalDateTime();
LOGGER.debug("plantVarietyId: {}, plantSpeciesId: {}, commonName: {}, lastUpdated: {}, created: {}", plantVarietyId, plantSpeciesId, commonName, lastUpdated, created); //  2.0.1
			if (item != null && plantVarietyId == item.getId())
			{// additional comment on the item
LOGGER.debug("processResults(): got additional comment for: {}", item);
				Comment comm = new Comment(rs.getInt("c_commentId"),
					rs.getInt("c_ownerId"),
					"PV",
					rs.getDate("c_date").toLocalDate(),
					rs.getString("c_comment"),
					rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
					rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults(): extra comment is: {}", comm);
				item = new PlantVariety(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
					item = new PlantVariety(plantVarietyId, plantSpeciesId, commonName, latinName, synonymSet, description, utility, hardiness, lifeType, plantType, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						plantVarietyId,
						"PV",
						rs.getDate("c_date").toLocalDate(),
						rs.getString("c_comment"),
						rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
						rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults(): first comment is: {}", comm);
					item = new PlantVariety(plantVarietyId, plantSpeciesId, commonName, latinName, synonymSet, description, utility, hardiness, lifeType, plantType, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

LOGGER.traceExit(log4jEntryMsg);
		return tempList;
	}	// processResults_hsqldb()

	private List<IPlantVariety> processResults_MSSQLServer(ResultSet rs) throws SQLException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("processResults_MSSQLServer()");

		List<IPlantVariety> tempList = new ArrayList<>();

		PlantVariety item = null;

		while (rs.next())
		{
			int plantVarietyId = rs.getInt("d_plantVarietyId");
			int plantSpeciesId = rs.getInt("d_plantSpeciesId");
			String commonName = rs.getString("d_commonName");
			String latinName = rs.getString("d_latinName");
			String synonymSet = rs.getString("d_synonymSet");
			String description = rs.getString("d_description");
			String utility = rs.getString("d_utility");
			String hardiness = rs.getString("d_hardiness");
			String lifeType = rs.getString("d_lifeType");
			String plantType = rs.getString("d_plantType");
			LocalDateTime lastUpdated = rs.getTimestamp("d_lastUpdated").toLocalDateTime();
			LocalDateTime created = rs.getTimestamp("d_created").toLocalDateTime();
LOGGER.debug("plantVarietyId: {}, plantSpeciesId: {}, commonName: {}, lastUpdated: {}, created: {}", plantVarietyId, plantSpeciesId, commonName, lastUpdated, created); //  2.0.1
			if (item != null && plantVarietyId == item.getId())
			{// additional comment on the item
LOGGER.debug("processResults(): got additional comment for: {}", item);
				Comment comm = new Comment(rs.getInt("c_commentId"),
					rs.getInt("c_ownerId"),
					"PV",
					rs.getDate("c_date").toLocalDate(),
					rs.getString("c_comment"),
					rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
					rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults(): extra comment is: {}", comm);
				item = new PlantVariety(item, comm);
			}
			else
			{
				if (item != null) tempList.add(item);
				int cid = rs.getInt("c_commentId");
				if (rs.wasNull())
				{// no comment
					item = new PlantVariety(plantVarietyId, plantSpeciesId, commonName, latinName, synonymSet, description, utility, hardiness, lifeType, plantType, lastUpdated, created);
				}
				else
				{// new item with comment
					Comment comm = new Comment(cid,
						plantVarietyId,
						"PV",
						rs.getDate("c_date").toLocalDate(),
						rs.getString("c_comment"),
						rs.getTimestamp("c_lastUpdated").toLocalDateTime(),
						rs.getTimestamp("c_created").toLocalDateTime());
LOGGER.debug("processResults(): first comment is: {}", comm);
					item = new PlantVariety(plantVarietyId, plantSpeciesId, commonName, latinName, synonymSet, description, utility, hardiness, lifeType, plantType, lastUpdated, created, comm);
				}
			}
		}
		if (item != null) tempList.add(item);

LOGGER.traceExit(log4jEntryMsg);
		return tempList;
	}	// processResults_MSSQLServer()

	/*
	*	Populate the parents slot(s)
	*/
	private void populatePlantSpecies(List<IPlantVariety> vals, boolean fetchAll) throws GNDBException
	{
// parent table type: TD
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("populatePlantSpecies()");

		if (fetchAll)
		{
			new PlantSpeciesLister().fetch();
			return;
		}
		int[] keys = vals.stream().
			mapToInt(c -> ((PlantVariety)c).getPlantSpeciesId()).filter(c -> c>0).distinct().
			filter(c -> !MySQLCache.cachePlantSpecies.containsKey(c)).
			toArray();

		if (keys.length > 0)
		{
			new PlantSpeciesLister().id(keys).fetch();
		}
LOGGER.traceExit(log4jEntryMsg);
	}

	void clear()
	{
		MySQLCache.cachePlantVariety.clear();
		MySQLCache.completePlantVariety = false;
        
        MySQLCache.cacheVarietyBySpecies.clear();   //  2.0.1
	}

	/**
	*
	*Select only the PlantVariety entries with these ids
	*May be called multiple times to extend the list
	*
	*	@param vals	a list of ids
	*	@return	 this Lister
	*/
	PlantVarietyLister id(int... vals)
	{
		useId = true;
		if (idNext + vals.length >= idList.length)
		{
			idList = Arrays.copyOf(idList, idList.length+vals.length+10);
		}
		for (int val : vals)
		{
			idList[idNext++] = val;
		}
		this.useWhere = true;
		return this;
	}

	@Override
	public IPlantVarietyLister plantVariety(IPlantVariety... items)
	{
		if (items == null) return this;
		if (items.length == 0) return this;
		int[] keys = new int[items.length];
		int keyCount = 0;
		for (IPlantVariety item : items)
		{
			if (item == null) continue;
			Integer ky = item.getKey();
			if (ky == null) continue;
			keys[keyCount++] = ky;
		}
		if (keyCount == 0) return this;
		keys = Arrays.copyOf(keys, keyCount);	// trim array to actual size - should be a null-op
		return this.id(keys);
	}

	@Override
	public IPlantVarietyLister plantVariety(List<IPlantVariety> items)
	{
		if (items == null) return this;
		if (items.isEmpty()) return this;
		return this.plantVariety(items.toArray(new IPlantVariety[0]));
	}

	/**
	 *	Select only the PlantVariety entries for these PlantSpecies.
	 *	May be called multiple times to extend the list.
	 *
	 *	@since 3.0.0
	 *	@param vals	a list of PlantSpecies ids
	 *	@return	 this Lister
	 */
	PlantVarietyLister plantSpecies(int... vals)
	{
		if (vals == null) return this;
		if (vals.length == 0)	return this;
		usePlantSpeciesId = true;
		if (plantSpeciesIdNext + vals.length >= plantSpeciesIdList.length)
		{
			plantSpeciesIdList = Arrays.copyOf(plantSpeciesIdList, plantSpeciesIdList.length+vals.length+10);
		}
		for (int val : vals)
		{
			plantSpeciesIdList[plantSpeciesIdNext++] = val;
		}
		this.useWhere = true;
		return this;
	}

	@Override
	public IPlantVarietyLister plantSpecies(IPlantSpecies... items)
	{
		if (items == null) return this;
		if (items.length == 0) return this;
		usePlantSpeciesId = true;
		if (plantSpeciesIdNext + items.length >= plantSpeciesIdList.length)
		{
			plantSpeciesIdList = Arrays.copyOf(plantSpeciesIdList, plantSpeciesIdList.length+items.length+10);
		}
		for (IPlantSpecies item : items)
		{
			if (item == null) continue;
			Integer ky = item.getKey();
			if (ky == null) continue;
			plantSpeciesIdList[plantSpeciesIdNext++] = ky;
		}
		useWhere = true;
		return this;
	}

	@Override
	public IPlantVarietyLister plantSpecies(List<IPlantSpecies> items)
	{
		if (items == null) return this;
		if (items.isEmpty()) return this;
		return this.plantSpecies(items.toArray(new IPlantSpecies[0]));
	}

	void toJson(JsonBuilderFactory builderFactory, JsonWriterFactory writerFactory, File dumpDirectory) throws GNDBException
	{
		useWhere = false;
		fetch();

		JsonArrayBuilder jsonHc = builderFactory.createArrayBuilder();
		for (IPlantVariety ihc : MySQLCache.cachePlantVariety.values())
		{
			PlantVariety hc = (PlantVariety)ihc;
			jsonHc.add(hc.toJson(builderFactory));
		}
        
        JsonObjectBuilder job = builderFactory.createObjectBuilder();
        job.add("JsonMode", "DUMP");
        job.add("JsonNBClass", "PlantVariety");
        job.add("values", jsonHc);
        
		try (JsonWriter writer = writerFactory.createWriter(new FileWriter(new File(dumpDirectory, "PlantVariety.json"), false)))
		{
			writer.writeObject(job.build());
		} catch (IOException ex) {
			LOGGER.error("toJson(): IOException", ex);
		}
	}	// toJson

    /**
     * The cache is likely to be large so set it up with an appropriate size rather than the default (16) to avoid rehashing.
     * 
     * @since   2.0.1
     */
    private void initialiseCache() throws GNDBException
    {
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("initialiseCache()");
        if (MySQLCache.cachePlantVariety == null || MySQLCache.cachePlantVariety.isEmpty())
        {
            long recs = 0;
            try (Connection conn = DBConnection.getConnection(); Statement stmt = conn.createStatement();)
            {
                ResultSet rs = stmt.executeQuery("select count(*) from plantvariety");
                while (rs.next()) 
                {
                    recs = rs.getLong(1);
                }

            }catch (SQLException ex) {
                LOGGER.error("fetch(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
                throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
            }
            LOGGER.debug("PlantVariety table size: {}", recs);
            
            MySQLCache.cachePlantVariety = new HashMap<>((int) (recs*4/3));
            
            if (MySQLCache.cacheVarietyBySpecies == null || MySQLCache.cacheVarietyBySpecies.isEmpty())
            {
                MySQLCache.cacheVarietyBySpecies = new HashMap<>((int) (recs*4/3));
            }
        }
        LOGGER.traceExit(log4jEntryMsg);
    }

}
