/*
 * Copyright (C) 2018, 2019, 2021, 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.2.0   Support hsqldb dialect
    2.3.0   Retrieve generated keys properly!
    2.4.0   Support MS SQLServer
	3.0.0	Use DBCommentHandler
	3.0.4	On delete, remove from cacheVarietyBySpecies
	3.1.0	Use jakarta implementation of JSON
 */

package uk.co.gardennotebook.mysql;

import uk.co.gardennotebook.spi.*;

import uk.co.gardennotebook.util.StoryLineTree;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import jakarta.json.JsonObject;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.logging.log4j.message.EntryMessage;
import org.apache.logging.log4j.Level;

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

	private IPlantVariety oldInstance = null;

	private final boolean newInstance;

	private boolean changedAncestor = false;

	private int id;
	private int plantSpeciesId;
	private boolean changedPlantSpeciesId = false;

	/*
	*	The usual name for this variety.
@apiNote
The values in this column are NOT unique
	*/
	private String commonName;
	private boolean changedCommonName = false;

	/*
	*	The formal horticultural name for this variety.  Rarely different from the species' latin name.
	*/
	private String latinName;
	private boolean changedLatinName = false;

	/*
	*	This implements the concept of synonyms.<BR>
All varieties which are synonyms of each other will have the same value for synonymSet which is a user chosen string.  A null value means the variety is NOT a synonym.
@apiNote
To get a PV and all its synonyms:
select p2.* from PV p1, PV p2 where p1.id in (...) and (p2.id = p1.id or p2.synonymSet = p1.synonymSet) and p1.PSid = p2.PSid
<BR>
this relies on the synonymSet being null if NOT in a synonymSet
<P>
Note that the choice of using a String value was pretty much forced on me by the need to be nullable.  The (unexpected) benefit is that the user can chose the value and maintenance just falls out of the generators.  I had to get past the idea that a PV is a synonym of some 'correct' PV - all names are equally valid!
	*/
	private String synonymSet;
	private boolean changedSynonymSet = false;
	private String description;
	private boolean changedDescription = false;

	/*
	*	The plant's function in the garden, typically vegetable, ornamental, weed.
	*/
	private String utility;
	private boolean changedUtility = false;

	/*
	*	The plant's hardiness or tenderness, typically hardy, half hardy, tender.
	*/
	private String hardiness;
	private boolean changedHardiness = false;

	/*
	*	Typically annual, biennial or perennial.  Variations such as 'perennial grown as annual' (e.g. runner bean).
	*/
	private String lifeType;
	private boolean changedLifeType = false;

	/*
	*	Such as climber, shrub, tree.
	*/
	private String plantType;
	private boolean changedPlantType = false;
	private LocalDateTime lastUpdated;
	private LocalDateTime created;
	private boolean somethingChanged = false;

private boolean changedComments = false;
	private DBCommentHandler commentHandler;	//	compiler will not allow declaration as final

	/**
	*	Constructor to use for a new entry
	*	This will create an 'orphan' PlantVariety, the PlantSpecies parent MUST be specified before saving
	*/
	PlantVarietyBuilder()
	{
		this(null, null);
	}

	/**
	*	Constructor to use for a new entry
	*	The new PlantVariety will be created as a child of the specified PlantSpecies
	*
	*	@param	species	the parent species for this variety, may not be null
	*/
	PlantVarietyBuilder(final IPlantSpecies species)
	{
		this(species, null);
	}

	/**
	*	constructor to use to edit or delete an existing entry
	*
	*	@param	oldVal	the existing item to modify or delete; if null a new entry will be created
	*/
	PlantVarietyBuilder(final IPlantVariety oldVal)
	{
		this(null, oldVal);
	}

	/**
	*	Constructor to use to edit or delete an existing entry
	*
	*	@param	species	the parent species for this variety.  If null the parent PlantSpecies will be taken from the specified 'old' PlantVariety
	*	@param	oldVal	the existing item to modify or delete; if null a new entry will be created
	*/
	PlantVarietyBuilder(final IPlantSpecies species, final IPlantVariety oldVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("constructor(): species={}, oldVal={}", species, oldVal);
		boolean speciesIsNull = (species == null || species.getKey() == null );
		boolean varietyIsNull = (oldVal == null || oldVal.getKey() == null );
		if (varietyIsNull)
		{
			newInstance = true;
			oldInstance = null;
			if (!speciesIsNull)
			{
				if (species instanceof PlantSpecies)
				{
					this.plantSpeciesId = ((PlantSpecies)species).getId();
				}
				else
				{
					Integer ky = species.getKey();
					if (ky != null)
						this.plantSpeciesId = ky;
				}
			}
			this.id = -1;
			commentHandler = new DBCommentHandler(NotebookEntryType.PLANTVARIETY);
			return;
		}

		newInstance = false;
		oldInstance = oldVal;

		PlantVariety baseObj;
		if (oldVal instanceof PlantVariety)
		{
			baseObj = (PlantVariety)oldVal;
			this.id = baseObj.getId();
			this.plantSpeciesId = baseObj.getPlantSpeciesId();
			this.commonName = baseObj.getCommonName();
			this.latinName = baseObj.getLatinName().orElse(null);
			this.synonymSet = baseObj.getSynonymSet().orElse(null);
			this.description = baseObj.getDescription().orElse(null);
			this.utility = baseObj.getUtility().orElse(null);
			this.hardiness = baseObj.getHardiness().orElse(null);
			this.lifeType = baseObj.getLifeType().orElse(null);
			this.plantType = baseObj.getPlantType().orElse(null);
			this.lastUpdated = baseObj.getLastUpdated();
			this.created = baseObj.getCreated();
		}
		else
		{
			Object ky = oldVal.getKey();
			if (ky == null) return;
			this.id = (Integer)ky;
			ky = oldVal.getPlantSpecies();
			if (ky == null)
			{
				this.plantSpeciesId = 0;
			}
			else
			{
				this.plantSpeciesId = ((IPlantSpecies)ky).getKey();
			}
			this.commonName = oldVal.getCommonName();
			this.latinName = oldVal.getLatinName().orElse(null);
			this.synonymSet = oldVal.getSynonymSet().orElse(null);
			this.description = oldVal.getDescription().orElse(null);
			this.utility = oldVal.getUtility().orElse(null);
			this.hardiness = oldVal.getHardiness().orElse(null);
			this.lifeType = oldVal.getLifeType().orElse(null);
			this.plantType = oldVal.getPlantType().orElse(null);
			this.lastUpdated = oldVal.getLastUpdated();
			this.created = oldVal.getCreated();
		}
		commentHandler = new DBCommentHandler(NotebookEntryType.PLANTVARIETY, this.id);
		LOGGER.traceExit();
	}	//	constructor()

	/**
	*	give the (new) value of plantSpeciesId
	*
	*	@param	newVal	the new value
	*	@return	this Builder
	*/
	IPlantVarietyBuilder plantSpeciesId(final int newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("plantSpeciesId(): oldVal={}, newVal={}", this.plantSpeciesId, newVal);
		if (this.plantSpeciesId == newVal) return this;
		//	cannot move a plant variety to a different species - would clobber all sorts of other data
		if (this.plantSpeciesId > 0) return this;
		this.plantSpeciesId = newVal;
		changedPlantSpeciesId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}
	@Override
	public IPlantVarietyBuilder plantSpecies(final IPlantSpecies newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("plantSpecies(): oldVal={}, newVal={}", this.plantSpeciesId, newVal);
		if (newVal == null) return this;
		if (this.plantSpeciesId == newVal.getKey()) return this;
		//	cannot move a plant variety to a different species - would clobber all sorts of other data
		if (this.plantSpeciesId > 0) return this;
		this.plantSpeciesId = newVal.getKey();
		changedPlantSpeciesId = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IPlantVarietyBuilder commonName(final String newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("commonName(): oldVal={}, newVal={}", this.commonName, newVal);

		if (newVal == null) return this;
		if (newVal.equals(this.commonName)) return this;
		this.commonName = newVal;
		changedCommonName = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IPlantVarietyBuilder latinName(final String newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("latinName(): oldVal={}, newVal={}", this.latinName, newVal);

		if (newVal == null && this.latinName == null) return this;
		if (newVal != null && newVal.equals(this.latinName)) return this;
		this.latinName = newVal;
		changedLatinName = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IPlantVarietyBuilder synonymSet(final String newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("synonymSet(): oldVal={}, newVal={}", this.synonymSet, newVal);

		if (newVal == null && this.synonymSet == null) return this;
		if (newVal != null && newVal.equals(this.synonymSet)) return this;
		this.synonymSet = newVal;
		changedSynonymSet = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IPlantVarietyBuilder description(final String newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("description(): oldVal={}, newVal={}", this.description, newVal);

		if (newVal == null && this.description == null) return this;
		if (newVal != null && newVal.equals(this.description)) return this;
		this.description = newVal;
		changedDescription = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IPlantVarietyBuilder utility(final String newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("utility(): oldVal={}, newVal={}", this.utility, newVal);

		if (newVal == null && this.utility == null) return this;
		if (newVal != null && newVal.equals(this.utility)) return this;
		this.utility = newVal;
		changedUtility = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IPlantVarietyBuilder hardiness(final String newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("hardiness(): oldVal={}, newVal={}", this.hardiness, newVal);

		if (newVal == null && this.hardiness == null) return this;
		if (newVal != null && newVal.equals(this.hardiness)) return this;
		this.hardiness = newVal;
		changedHardiness = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IPlantVarietyBuilder lifeType(final String newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("lifeType(): oldVal={}, newVal={}", this.lifeType, newVal);

		if (newVal == null && this.lifeType == null) return this;
		if (newVal != null && newVal.equals(this.lifeType)) return this;
		this.lifeType = newVal;
		changedLifeType = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IPlantVarietyBuilder plantType(final String newVal)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("plantType(): oldVal={}, newVal={}", this.plantType, newVal);

		if (newVal == null && this.plantType == null) return this;
		if (newVal != null && newVal.equals(this.plantType)) return this;
		this.plantType = newVal;
		changedPlantType = true;
		somethingChanged = true;
		LOGGER.traceExit();
		return this;
	}

	@Override
	public IPlantVarietyBuilder addComment(final String... newVals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("addComment[array]()");

		commentHandler.addComment(newVals);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit("addComment");
		return this;
	}

	@Override
	public IPlantVarietyBuilder addComment(final List<String> newVals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("addComment<list>()");

		commentHandler.addComment(newVals);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit("addComment");
		return this;
	}

	/**
	*	remove a comment from this item
	*
	*	@param	newVals	the comment to remove.  If the comment does not exist, this is a null-op
	*	@return	 this Builder
	*/
	IPlantVarietyBuilder deleteComment(int... newVals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

		commentHandler.deleteComment(newVals);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit(log4jEntryMsg);
		return this;
	}

	@Override
	public IPlantVarietyBuilder deleteComment(final IComment... newVals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

		commentHandler.deleteComment(newVals);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit(log4jEntryMsg);
		return this;
	}

	@Override
	public IPlantVarietyBuilder deleteComment(final List<IComment> vals)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("deleteComment()");

		commentHandler.deleteComment(vals);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit(log4jEntryMsg);
		return this;
	}

	@Override
	public IPlantVarietyBuilder changeComment(final IComment base, final String comment)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("changeComment()");

		commentHandler.changeComment(base, comment);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit(log4jEntryMsg);
		return this;
	}

	@Override
	public IPlantVarietyBuilder changeComment(final IComment base, final LocalDate date)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("changeComment()");

		commentHandler.changeComment(base, date);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit(log4jEntryMsg);
		return this;
	}

	@Override
	public IPlantVarietyBuilder changeComment(final IComment base, final LocalDate date, final String comment)
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("changeComment()");

		commentHandler.changeComment(base, date, comment);
		changedComments = commentHandler.isChangedComments();
		LOGGER.traceExit(log4jEntryMsg);
		return this;
	}

	@Override
	public IPlantVariety save() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("save(): somethingChanged: {}, newInstance: {}, changedComments: {}, changedAncestor: {}",
                                                                somethingChanged, newInstance, changedComments, changedAncestor);

		if (!somethingChanged && !newInstance && !changedComments && !changedAncestor)
		{
			LOGGER.traceExit("nothing changed");
			return MySQLCache.cachePlantVariety.get(this.id);
		}

		if (newInstance)
		{
			doInsert();
		}
		else if (somethingChanged)
		{
			doUpdate();
		}

		if (changedComments)
		{
			commentHandler.setParentId(this.id);
			commentHandler.save();
		}

// mark cache as dirty
		if (!newInstance &&(somethingChanged || changedComments))
		{
			MySQLCache.cachePlantVariety.remove(this.id);
            MySQLCache.completePlantVariety = false;
            MySQLCache.cacheVarietyBySpecies.get(this.plantSpeciesId).remove(this.id);
		}
// populate the cache
		//	make sure that cacheVarietyBySpecies has all current varieties
		//	otherwise it only picks up the new addition
		if (newInstance)
		{
			new PlantVarietyLister().plantSpecies(this.plantSpeciesId).fetch();
		}

		new PlantVarietyLister().id(this.id).fetch();
		IPlantVariety newValue = MySQLCache.cachePlantVariety.get(this.id);
		if (oldInstance != null)
		{
			oldInstance.flagReplaced(newValue);
		}

	//	tell any parent beans the child list has mutated
	//	only additions and deletions matter, other changes will be reflected through the child bean
		if (newInstance)
		{
			MySQLCache.cachePlantSpecies.get(plantSpeciesId).flagChildAdded(newValue);
		}
		else
		{	//	updated
			if (changedPlantSpeciesId)
			{
				if (oldInstance != null)
				{
					MySQLCache.cachePlantSpecies.get(oldInstance.getPlantSpecies().getKey()).flagChildDeleted(oldInstance);
				}
				MySQLCache.cachePlantSpecies.get(newValue.getPlantSpecies().getKey()).flagChildAdded(newValue);
			}
		}

		//	stop multiple saves!
		oldInstance = null;

		somethingChanged = false;
		changedComments = false;
		changedAncestor = false;
		changedPlantSpeciesId = false;
		changedCommonName = false;
		changedLatinName = false;
		changedSynonymSet = false;
		changedDescription = false;
		changedUtility = false;
		changedHardiness = false;
		changedLifeType = false;
		changedPlantType = false;

		LOGGER.traceExit(log4jEntryMsg);
		return newValue;
	}	//	save()

	@Override
	public boolean needSave()
	{
		return somethingChanged || changedComments || changedAncestor;
	}	// needSave()

	@Override
	public boolean canSave()
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("canSave(): newInstance={}", newInstance);

		if (!needSave())
		{//	save() will be a null-op but that's OK
			return true;
		}
		if (this.plantSpeciesId <= 0)
		{
			LOGGER.debug("plantSpeciesId not set");
			return false;
		}
		if (this.commonName == null ||
			this.commonName.isEmpty() )
		{
			LOGGER.debug("commonName not set");
			return false;
		}
		return true;
	}	// canSave()

	@Override
	public boolean canDelete() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("canDelete(): newInstance={}", newInstance);

		if (newInstance) return LOGGER.traceExit(log4jEntryMsg, false);

		boolean  readValue = false;
        
		try (	Connection conn = DBConnection.getConnection();
				Statement stmt = conn.createStatement();	)
		{
            String query;
            ResultSet rs;

			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> query = "select exists (select 1 from husbandry where plantVarietyId = " + this.id + ") as fred";
				case hsqldb -> query = "select exists (select 1 from husbandry where plantVarietyId = " + this.id + ") as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from husbandry where plantVarietyId = " + this.id + ") THEN 1 ELSE 0 END as fred";
				default -> {
					LOGGER.error("canDelete(): no known rdbms");
					throw new GNDBException(new IllegalStateException("PlantVarietyBuilder: canDelete(): no known RDBMS"));
				}
			}
LOGGER.debug("canDelete(): query: {}", query);
			rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
            rs.close();
LOGGER.debug("canDelete(): readValue: {}", readValue);
			if (readValue)
			{
				return LOGGER.traceExit(log4jEntryMsg, false);
			}

			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> query = "select exists (select 1 from afflictionevent where plantVarietyId = " + this.id + ") as fred";
				case hsqldb -> query = "select exists (select 1 from afflictionevent where plantVarietyId = " + this.id + ") as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from afflictionevent where plantVarietyId = " + this.id + ") THEN 1 ELSE 0 END as fred";
				default -> {
					LOGGER.error("canDelete(): no known rdbms");
					throw new GNDBException(new IllegalStateException("PlantVarietyBuilder: canDelete(): no known RDBMS"));
				}
			}
LOGGER.debug("canDelete(): query: {}", query);
			rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
LOGGER.debug("canDelete(): readValue: {}", readValue);
			if (readValue)
			{
				return LOGGER.traceExit(log4jEntryMsg, false);
			}

			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> query = "select exists (select 1 from groundwork where plantVarietyId = " + this.id + ") as fred";
				case hsqldb -> query = "select exists (select 1 from groundwork where plantVarietyId = " + this.id + ") as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from groundwork where plantVarietyId = " + this.id + ") THEN 1 ELSE 0 END as fred";
				default -> {
					LOGGER.error("canDelete(): no known rdbms");
					throw new GNDBException(new IllegalStateException("PlantVarietyBuilder: canDelete(): no known RDBMS"));
				}
			}
LOGGER.debug("canDelete(): query: {}", query);
			rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
            rs.close();
LOGGER.debug("canDelete(): readValue: {}", readValue);
			if (readValue)
			{
				return LOGGER.traceExit(log4jEntryMsg, false);
			}

			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> query = "select exists (select 1 from plantnote where plantVarietyId = " + this.id + ") as fred";
				case hsqldb -> query = "select exists (select 1 from plantnote where plantVarietyId = " + this.id + ") as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from plantnote where plantVarietyId = " + this.id + ") THEN 1 ELSE 0 END as fred";
				default -> {
					LOGGER.error("canDelete(): no known rdbms");
					throw new GNDBException(new IllegalStateException("PlantVarietyBuilder: canDelete(): no known RDBMS"));
				}
			}
LOGGER.debug("canDelete(): query: {}", query);
			rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
            rs.close();
LOGGER.debug("canDelete(): readValue: {}", readValue);
			if (readValue)
			{
				return LOGGER.traceExit(log4jEntryMsg, false);
			}

			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> query = "select exists (select 1 from product where plantVarietyId = " + this.id + ") as fred";
				case hsqldb -> query = "select exists (select 1 from product where plantVarietyId = " + this.id + ") as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from product where plantVarietyId = " + this.id + ") THEN 1 ELSE 0 END as fred";
				default -> {
					LOGGER.error("canDelete(): no known rdbms");
					throw new GNDBException(new IllegalStateException("PlantVarietyBuilder: canDelete(): no known RDBMS"));
				}
			}
LOGGER.debug("canDelete(): query: {}", query);
			rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
            rs.close();
LOGGER.debug("canDelete(): readValue: {}", readValue);
			if (readValue)
			{
				return LOGGER.traceExit(log4jEntryMsg, false);
			}

			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> query = "select exists (select 1 from reminder where plantVarietyId = " + this.id + ") as fred";
				case hsqldb -> query = "select exists (select 1 from reminder where plantVarietyId = " + this.id + ") as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from reminder where plantVarietyId = " + this.id + ") THEN 1 ELSE 0 END as fred";
				default -> {
					LOGGER.error("canDelete(): no known rdbms");
					throw new GNDBException(new IllegalStateException("PlantVarietyBuilder: canDelete(): no known RDBMS"));
				}
			}
LOGGER.debug("canDelete(): query: {}", query);
			rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
            rs.close();
LOGGER.debug("canDelete(): readValue: {}", readValue);
			if (readValue)
			{
				return LOGGER.traceExit(log4jEntryMsg, false);
			}

			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> query = "select exists (select 1 from saleitem where plantVarietyId = " + this.id + ") as fred";
				case hsqldb -> query = "select exists (select 1 from saleitem where plantVarietyId = " + this.id + ") as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from saleitem where plantVarietyId = " + this.id + ") THEN 1 ELSE 0 END as fred";
				default -> {
					LOGGER.error("canDelete(): no known rdbms");
					throw new GNDBException(new IllegalStateException("PlantVarietyBuilder: canDelete(): no known RDBMS"));
				}
			}
LOGGER.debug("canDelete(): query: {}", query);
			rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
            rs.close();
LOGGER.debug("canDelete(): readValue: {}", readValue);
			if (readValue)
			{
				return LOGGER.traceExit(log4jEntryMsg, false);
			}

			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> query = "select exists (select 1 from todolist where plantVarietyId = " + this.id + ") as fred";
				case hsqldb -> query = "select exists (select 1 from todolist where plantVarietyId = " + this.id + ") as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from todolist where plantVarietyId = " + this.id + ") THEN 1 ELSE 0 END as fred";
				default -> {
					LOGGER.error("canDelete(): no known rdbms");
					throw new GNDBException(new IllegalStateException("PlantVarietyBuilder: canDelete(): no known RDBMS"));
				}
			}
LOGGER.debug("canDelete(): query: {}", query);
			rs = stmt.executeQuery(query);
            rs.next();
            readValue = readValue || rs.getBoolean("fred");
            rs.close();
LOGGER.debug("canDelete(): readValue: {}", readValue);
			if (readValue)
			{
				return LOGGER.traceExit(log4jEntryMsg, false);
			}

			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> query = "select exists (select 1 from reviewreferences where plantVarietyId = " + this.id + ") as fred";
				case hsqldb -> query = "select exists (select 1 from reviewreferences where plantVarietyId = " + this.id + ") as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from reviewreferences where plantVarietyId = " + this.id + ") THEN 1 ELSE 0 END as fred";
				default -> {
					LOGGER.error("canDelete(): no known rdbms");
					throw new GNDBException(new IllegalStateException("PlantVarietyBuilder: canDelete(): no known RDBMS"));
				}
			}
			LOGGER.debug("canDelete(): query: {}", query);
			rs = stmt.executeQuery(query);
			rs.next();
			readValue = readValue || rs.getBoolean("fred");
			rs.close();
			LOGGER.debug("canDelete(): readValue: {}", readValue);
			if (readValue)
			{
				return LOGGER.traceExit(log4jEntryMsg, false);
			}

			switch (DBConnection.DB_IN_USE)
			{
				case MariaDB, MySQL -> query = "select exists (select 1 from croppingactual where plantVarietyId = " + this.id + ") as fred";
				case hsqldb -> query = "select exists (select 1 from croppingactual where plantVarietyId = " + this.id + ") as fred from (values(99))";
				case MSSQLServer -> query = "select CASE WHEN EXISTS (select 1 from croppingactual where plantVarietyId = " + this.id + ") THEN 1 ELSE 0 END as fred";
				default -> {
					LOGGER.error("canDelete(): no known rdbms");
					throw new GNDBException(new IllegalStateException("PlantSpeciesBuilder: canDelete(): no known RDBMS"));
				}
			}
			LOGGER.debug("canDelete(): query: {}", query);
			rs = stmt.executeQuery(query);
			rs.next();
			readValue = readValue || rs.getBoolean("fred");
			rs.close();
			LOGGER.debug("canDelete(): readValue: {}", readValue);
			if (readValue)
			{
				return LOGGER.traceExit(log4jEntryMsg, false);
			}
			stmt.close();
		}catch (SQLException ex) {
			LOGGER.error("canDelete(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}
		return LOGGER.traceExit(log4jEntryMsg, !readValue);
	}	// canDelete()

	@Override
	public void delete() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("delete(): newInstance={}", newInstance);

		if (newInstance) return;
		if (!canDelete()) return;

		int res = 0;
		String query = "delete from plantvariety where plantVarietyId = " + this.id;
LOGGER.debug("delete(): query: {}", query);
		try (	Connection conn = DBConnection.getConnection();
				Statement stmt = conn.createStatement();	)
		{
			res = stmt.executeUpdate(query);
LOGGER.debug("delete(): result: {}", res);
			// tidy up dependencies with nullable keys
			if (res > 0) {
				query = "delete from comment where ownerId = " + this.id + " and ownerType = 'PV'";
				int res2 = stmt.executeUpdate(query);
LOGGER.debug("delete() comments: result: {}", res2);
			}
			stmt.close();
		}catch (SQLException ex) {
			LOGGER.error("delete(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}
		if (res > 0)
		{
			oldInstance.flagDeleted();
			MySQLCache.cachePlantVariety.remove(this.id);
			//	3.0.4	make sure variety has been removed from cacheVarietyBySpecies
			final int psId = oldInstance.getPlantSpecies().getKey();
//			Map<Integer, IPlantVariety> varsForSpecies = MySQLCache.cacheVarietyBySpecies.get(psId);
			if (MySQLCache.cacheVarietyBySpecies.containsKey(psId))
			{
				MySQLCache.cacheVarietyBySpecies.get(psId).remove(oldInstance.getKey());
			}
	//	tell any parent beans the child list has mutated
	//	only additions and deletions matter, other changes will be reflected through the child bean
			MySQLCache.cachePlantSpecies.get(oldInstance.getPlantSpecies().getKey()).flagChildDeleted(oldInstance);
		}
		oldInstance = null;
LOGGER.traceExit(log4jEntryMsg);
	}	// delete()

	private void doUpdate() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("doUpdate(): newInstance={}, somethingChanged={}", newInstance, somethingChanged);

		if (newInstance) return;
		if (!somethingChanged) return;
		StringBuilder query = new StringBuilder("update plantvariety set ");
		if (changedPlantSpeciesId)
		{
			query.append("plantSpeciesId = ?, ");
		}

		if (changedCommonName)
		{
			query.append("commonName = ?, ");
		}

		if (changedLatinName)
		{
			query.append("latinName = ?, ");
		}

		if (changedSynonymSet)
		{
			query.append("synonymSet = ?, ");
		}

		if (changedDescription)
		{
			query.append("description = ?, ");
		}

		if (changedUtility)
		{
			query.append("utility = ?, ");
		}

		if (changedHardiness)
		{
			query.append("hardiness = ?, ");
		}

		if (changedLifeType)
		{
			query.append("lifeType = ?, ");
		}

		if (changedPlantType)
		{
			query.append("plantType = ?, ");
		}

		query.delete(query.length()-2, query.length());
		query.append(" where plantVarietyId = ").append(this.id);
LOGGER.debug("doUpdate(): query={} ", query.toString());
		try (	Connection conn = DBConnection.getConnection();
				PreparedStatement stmt = conn.prepareStatement(query.toString());	)
		{
			int paramIx = 1;
			if (changedPlantSpeciesId)
			{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.plantSpeciesId);
				stmt.setInt(paramIx++, this.plantSpeciesId);
			}

			if (changedCommonName)
			{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.commonName);
				stmt.setString(paramIx++, this.commonName);
			}

			if (changedLatinName)
			{
				if (this.latinName == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.latinName);
					stmt.setString(paramIx++, this.latinName);
				}
			}

			if (changedSynonymSet)
			{
				if (this.synonymSet == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.synonymSet);
					stmt.setString(paramIx++, this.synonymSet);
				}
			}

			if (changedDescription)
			{
				if (this.description == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.description);
					stmt.setString(paramIx++, this.description);
				}
			}

			if (changedUtility)
			{
				if (this.utility == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.utility);
					stmt.setString(paramIx++, this.utility);
				}
			}

			if (changedHardiness)
			{
				if (this.hardiness == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.hardiness);
					stmt.setString(paramIx++, this.hardiness);
				}
			}

			if (changedLifeType)
			{
				if (this.lifeType == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.lifeType);
					stmt.setString(paramIx++, this.lifeType);
				}
			}

			if (changedPlantType)
			{
				if (this.plantType == null)
				{
LOGGER.debug("doUpdate(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doUpdate(): param {}={}", paramIx, this.plantType);
					stmt.setString(paramIx++, this.plantType);
				}
			}

			stmt.executeUpdate();

		}catch (SQLException ex) {
			LOGGER.error("doUpdate(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}
LOGGER.traceExit(log4jEntryMsg);
	}	// doUpdate

	private void doInsert() throws GNDBException
	{
		EntryMessage log4jEntryMsg = LOGGER.traceEntry("doInsert(): newInstance={}", newInstance);

		if (!newInstance) return;
		if (!canSave())
		{
			throw LOGGER.throwing(Level.ERROR, new IllegalStateException("doInsert(): save criteria not met"));
		}
		if (!this.changedPlantSpeciesId)
		{
			throw LOGGER.throwing(Level.ERROR, new IllegalStateException("PlantVarietyBuilder: doInsert(): plantSpeciesId unspecified"));
		}
		if (!this.changedCommonName)
		{
			throw LOGGER.throwing(Level.ERROR, new IllegalStateException("PlantVarietyBuilder: doInsert(): commonName unspecified"));
		}

		StringBuilder query = new StringBuilder("insert into plantvariety (");
		query.append("plantSpeciesId, ");
		query.append("commonName, ");
		if (changedLatinName)
		{
			query.append("latinName, ");
		}

		if (changedSynonymSet)
		{
			query.append("synonymSet, ");
		}

		if (changedDescription)
		{
			query.append("description, ");
		}

		if (changedUtility)
		{
			query.append("utility, ");
		}

		if (changedHardiness)
		{
			query.append("hardiness, ");
		}

		if (changedLifeType)
		{
			query.append("lifeType, ");
		}

		if (changedPlantType)
		{
			query.append("plantType, ");
		}

		query.replace(query.length()-2, query.length(), ") values (");
		query.append("?, ");
		query.append("?, ");
		if (changedLatinName)
		{
			query.append("?, ");
		}

		if (changedSynonymSet)
		{
			query.append("?, ");
		}

		if (changedDescription)
		{
			query.append("?, ");
		}

		if (changedUtility)
		{
			query.append("?, ");
		}

		if (changedHardiness)
		{
			query.append("?, ");
		}

		if (changedLifeType)
		{
			query.append("?, ");
		}

		if (changedPlantType)
		{
			query.append("?, ");
		}

		query.replace(query.length()-2, query.length(), ")");
LOGGER.debug("doInsert(): query={}", query.toString());

		try (	Connection conn = DBConnection.getConnection();
				PreparedStatement stmt = conn.prepareStatement(query.toString(), Statement.RETURN_GENERATED_KEYS); )
		{
			int paramIx = 1;
LOGGER.debug("doInsert(): param {}={}", paramIx, this.plantSpeciesId);
			stmt.setInt(paramIx++, this.plantSpeciesId);
LOGGER.debug("doInsert(): param {}={}", paramIx, this.commonName);
			stmt.setString(paramIx++, this.commonName);
			if (changedLatinName) {
				if (this.latinName == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.latinName);
					stmt.setString(paramIx++, this.latinName);
				}
			}

			if (changedSynonymSet) {
				if (this.synonymSet == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.synonymSet);
					stmt.setString(paramIx++, this.synonymSet);
				}
			}

			if (changedDescription) {
				if (this.description == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.description);
					stmt.setString(paramIx++, this.description);
				}
			}

			if (changedUtility) {
				if (this.utility == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.utility);
					stmt.setString(paramIx++, this.utility);
				}
			}

			if (changedHardiness) {
				if (this.hardiness == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.hardiness);
					stmt.setString(paramIx++, this.hardiness);
				}
			}

			if (changedLifeType) {
				if (this.lifeType == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.lifeType);
					stmt.setString(paramIx++, this.lifeType);
				}
			}

			if (changedPlantType) {
				if (this.plantType == null)
				{
LOGGER.debug("doInsert(): param {} null", paramIx);
					stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
				}
				else
				{
LOGGER.debug("doInsert(): param {}={}", paramIx, this.plantType);
					stmt.setString(paramIx++, this.plantType);
				}
			}

			stmt.executeUpdate();

			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			int newId = rs.getInt(1);
LOGGER.debug("doInsert(): newId: {}", newId);
			this.id = newId;
		}catch (SQLException ex) {
			LOGGER.error("doInsert(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}

LOGGER.traceExit(log4jEntryMsg);
	}	// doInsert

	/**
     * Process the whole JSON array from a DUMP
     * 
     * @param newVal    a list of JSON objects representing PlantSpecies as output by a JSON DUMP
     * @throws GNDBException on database error
     * 
     * @since 2.2.5
     */
    void restoreJsonDump(List<JsonObject> newVal) throws GNDBException
    {
        EntryMessage log4jEntryMsg = LOGGER.traceEntry("restoreJsonDump(list JSON)");
        
        if (newVal.isEmpty())
            return;

		StringBuilder query = new StringBuilder("insert into plantvariety (");
        query.append("plantVarietyId, ");
		query.append("plantSpeciesId, ");
		query.append("commonName, ");
		query.append("latinName, ");
		query.append("synonymSet, ");
		query.append("description, ");
		query.append("utility, ");
		query.append("hardiness, ");
		query.append("lifeType, ");
		query.append("plantType, ");
		query.append("lastUpdated, ");
		query.append("created) ");
        if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.hsqldb)
        {
            query.append(" overriding system value ");
        }
        query.append(" values (");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?, ");
		query.append("?) ");
LOGGER.debug("restoreJsonDump(): query={}", query.toString());

		try (	Connection conn = DBConnection.getConnection();
				PreparedStatement stmt = conn.prepareStatement(query.toString());	)
		{
            conn.setAutoCommit(false);
            int txCount = 0;
            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT plantvariety ON");
            }
            
            for (JsonObject jo : newVal)
            {
                if (!"DUMP".equals(jo.getString("JsonMode", "DUMP")))
                {
                    LOGGER.error("PlantVariety DUMP object is not DUMP");
                    throw new IllegalArgumentException("PlantVariety DUMP object is not DUMP");
                }
                if (!"PlantVariety".equals(jo.getString("JsonNBClass", "PlantVariety")))
                {
                    LOGGER.error("PlantVariety DUMP object is not PlantVariety");
                    throw new IllegalArgumentException("PlantVariety DUMP object is not PlantVariety");
                }
                PlantVariety ps = new PlantVariety(jo);
                if (ps.getId() <= 0)
                {//this forces the value of the id field.  The >0 test is a bodge.
                    throw new IllegalArgumentException("PlantVariety DUMP object does not have an id");
                }
                int paramIx = 1;
    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getId());
                stmt.setInt(paramIx++, ps.getId());

    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getPlantSpeciesId());
                stmt.setInt(paramIx++, ps.getPlantSpeciesId());

    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getCommonName());
                stmt.setString(paramIx++, ps.getCommonName());

                if (ps.getLatinName().isEmpty())
                {
    LOGGER.debug("restoreJsonDump(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getLatinName().get());
                    stmt.setString(paramIx++, ps.getLatinName().get());
                }

                if (ps.getSynonymSet().isEmpty())
                {
    LOGGER.debug("restoreJsonDump(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getSynonymSet().get());
                    stmt.setString(paramIx++, ps.getSynonymSet().get());
                }

                if (ps.getDescription().isEmpty())
                {
    LOGGER.debug("restoreJsonDump(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getDescription().get());
                    stmt.setString(paramIx++, ps.getDescription().get());
                }

                if (ps.getUtility().isEmpty())
                {
    LOGGER.debug("restoreJsonDump(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getUtility().get());
                    stmt.setString(paramIx++, ps.getUtility().get());
                }

                if (ps.getHardiness().isEmpty())
                {
    LOGGER.debug("restoreJsonDump(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getHardiness().get());
                    stmt.setString(paramIx++, ps.getHardiness().get());
                }

                if (ps.getLifeType().isEmpty())
                {
    LOGGER.debug("restoreJsonDump(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getLifeType().get());
                    stmt.setString(paramIx++, ps.getLifeType().get());
                }

                if (ps.getPlantType().isEmpty())
                {
    LOGGER.debug("restoreJsonDump(): param {} null", paramIx);
                    stmt.setNull(paramIx++, java.sql.Types.VARCHAR);
                }
                else
                {
    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getPlantType().get());
                    stmt.setString(paramIx++, ps.getPlantType().get());
                }

    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getLastUpdated());
                stmt.setTimestamp(paramIx++, Timestamp.valueOf(ps.getLastUpdated()));
    LOGGER.debug("restoreJsonDump(): param {}={}", paramIx, ps.getCreated());
                stmt.setTimestamp(paramIx++, Timestamp.valueOf(ps.getCreated()));
                
                stmt.executeUpdate();
                
                if (!ps.getComments().isEmpty())
                {
                    if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
                    {
                        conn.createStatement().execute("SET IDENTITY_INSERT plantvariety OFF");
                    }
                    CommentBuilder cb = new CommentBuilder(NotebookEntryType.PLANTVARIETY, ps.getId());
                    cb.doJsonInsert(ps.getComments(), conn);
                    if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
                    {
                        conn.createStatement().execute("SET IDENTITY_INSERT plantvariety ON");
                    }
                }

                if (++txCount > 50)
                {
                    conn.commit();
                    txCount = 0;
                }
            }
            conn.commit();
            if (DBConnection.DB_IN_USE == DBConnection.RDBMS_ENUM.MSSQLServer )
            {
                conn.createStatement().execute("SET IDENTITY_INSERT plantvariety OFF");
            }
		}catch (SQLException ex) {
			LOGGER.error("restoreJsonDump(): SQLException: errorCode: {}, SQLstate: {}, message: {}", ex.getErrorCode(), ex.getSQLState(), ex.getMessage());
			throw new GNDBException(ex, ex.getErrorCode(), ex.getSQLState());
		}

	}	// restoreJsonDump(JsonObjects)

	@Override
	public boolean hasAncestor() throws GNDBException
	{
		//	this class does not participate in story lines
		return false;
	}	//	hasAncestor()

	@Override
	public StoryLineTree<? extends INotebookEntry> getAncestors() throws GNDBException
	{
		//	this class does not participate in story lines
		return StoryLineTree.emptyTree();
	}	//	getAncestors()

	@Override
	public IPlantVarietyBuilder ancestor(IPurchaseItem ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(PurchaseItem)

	@Override
	public IPlantVarietyBuilder ancestor(IGroundwork ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(Groundwork)

	@Override
	public IPlantVarietyBuilder ancestor(IAfflictionEvent ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(AfflictionEvent)

	@Override
	public IPlantVarietyBuilder ancestor(IHusbandry ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(Husbandry)

	@Override
	public IPlantVarietyBuilder ancestor(ISaleItem ancestor) throws GNDBException
	{
		return this;
	}	//	ancestor(SaleItem)

	@Override
	public boolean hasDescendant() throws GNDBException
	{
		//	this class does not participate in story lines
		return false;
	}	//	hasDescendant()

	@Override
	public StoryLineTree<? extends INotebookEntry> getDescendants() throws GNDBException
	{
		//	this class does not participate in story lines
		return StoryLineTree.emptyTree();
	}	//	getDescendants()

	@Override
	public boolean addDescendant(IPurchaseItem descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(PurchaseItem)

	@Override
	public boolean addDescendant(IGroundwork descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(Groundwork)

	@Override
	public boolean addDescendant(IAfflictionEvent descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(AfflictionEvent)

	@Override
	public boolean addDescendant(IHusbandry descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(Husbandry)

	@Override
	public boolean addDescendant(ISaleItem descendant) throws GNDBException
	{
		return false;
	}	//	addDescendant(SaleItem)

}

