/* ************************************************************************
#
#  DivConq
#
#  http://divconq.com/
#
#  Copyright:
#    Copyright 2012 eTimeline, LLC. All rights reserved.
#
#  License:
#    See the license.txt file in the project's top-level directory for details.
#
#  Authors:
#    * Andy White
#
************************************************************************ */
package divconq.db.mumps;

import java.io.File;
import java.util.List;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ScheduledFuture;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicLong;

import org.joda.time.DateTime;
import org.joda.time.DateTimeZone;
import org.joda.time.chrono.ISOChronology;

import com.jcraft.jsch.Channel;
import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import com.jcraft.jsch.SftpException;
import com.jcraft.jsch.UserInfo;

import divconq.db.IDatabaseCallback;
import divconq.db.IDatabaseManager;
import divconq.db.IDatabaseRequest;
import divconq.db.DatabaseResult;
import divconq.db.ObjectCallback;
import divconq.db.ObjectResult;
import divconq.db.QueryRequest;
import divconq.db.common.SchemaSyncRequest;
import divconq.lang.BigDateTime;
import divconq.lang.FuncResult;
import divconq.lang.Hub;
import divconq.lang.OperationCallback;
import divconq.lang.OperationResult;
import divconq.lang.ProjectCompiler;
import divconq.lang.TaskContext;
import divconq.lang.Memory;
import divconq.struct.CompositeStruct;
import divconq.struct.FieldStruct;
import divconq.struct.ListStruct;
import divconq.struct.RecordStruct;
import divconq.struct.ScalarStruct;
import divconq.struct.Struct;
import divconq.struct.builder.BuilderStateException;
import divconq.util.BasicSettingsObfuscator;
import divconq.util.HexUtil;
import divconq.util.ISettingsObfuscator;
import divconq.util.StringUtil;
import divconq.util.TimeUtil;
import divconq.xml.XElement;

/**
 * Manages a pool of connections to a MUMPS database over SSH.  Current version supports only one SSH
 * session, but 3 connections.  As a rule MUMPS interactions are fairly fast, however there are times
 * when a procedure could be slow or a connection fails.  The idea with a pool is to get around those
 * occasional speed bumps.  It is expected that 3 connections could handle a moderate to large load.
 * 
 * Database Requests (IDatabaseRequest) are submitted to this pool asynchronously, when a connection
 * becomes available it pulls the request off the queue and processes.  Then a callback is triggered
 * letting the calling code know that a result is available.
 * 
 * Database results are typically collected into an object structure (see CompositeStruct) and
 * returned in one large lump.  As an alternative database results may be processed as a stream, 
 * see ICompositeBuilder and JsonStreamBuilder.
 * 
 * See divconq.test.TestDb for usage examples.
 * 
 * @author Andy
 *
 */
public class DatabaseManager implements IDatabaseManager {
	protected AtomicLong nextseq = new AtomicLong();
	protected XElement config = null;
	
	// queue
	protected LinkedBlockingQueue<DatabaseTask> queue = new LinkedBlockingQueue<DatabaseTask>();
	
	protected AtomicLong totalTasksSubmitted = new AtomicLong();
	protected AtomicLong totalTasksStarted = new AtomicLong();
	protected AtomicLong totalTasksCompleted = new AtomicLong();
	
	// connections
	protected Connection[] pool = null;
	protected int size = 3;
	
	protected AtomicLong failedConnects = new AtomicLong();
	
	protected String id = null;
	protected String hostname = null;
	protected String username = null;
	protected String password = null;
	protected String keyfile = null;
	protected String passphrase = null;
	protected int port = 22;

	protected JSch jsch = new JSch();
	protected Session session = null;
	
	protected ISettingsObfuscator obfus = null; 
	
	protected boolean firstConnect = true;
	
	protected boolean shutdown = false;
	protected ScheduledFuture<?> clock = null;
	protected long scheduleFreq = 60;

	/**
	 * Provide the setting for this database pool, but do not yet connect.
	 * 
	 * @param config configuration for this database pool
	 * @return messages about the success of this call
	 */
	@Override
	public OperationResult init(XElement config) {
		OperationResult or = new OperationResult(true);
		
		or.trace(0, "dcDb Connection Pool Initializing");
		
		this.config = config;
		
		this.id = config.getAttribute("Id", "default");		
		
		this.hostname = config.getAttribute("Host");
		this.username = config.getAttribute("User");
		
		this.port = (int) StringUtil.parseInt(config.getAttribute("Port"), 22);
		
    	this.password = config.getAttribute("Password");
    	
    	if (StringUtil.isNotEmpty(this.password))
    		this.password = Hub.instance.getClock().getObfuscator().decryptHexToString(this.password).toString();

		this.keyfile = config.getAttribute("KeyFile");
		
    	this.passphrase = config.getAttribute("Passphrase");
    	
    	if (StringUtil.isNotEmpty(this.passphrase))
    		this.passphrase = Hub.instance.getClock().getObfuscator().decryptHexToString(this.passphrase).toString();
    	
    	// TODO config size
    	this.pool = new Connection[this.size];
		
		if (StringUtil.isNotEmpty(this.keyfile)) {				
			FuncResult<File> kf = Hub.instance.getResources().getProjectResource(this.keyfile);

			or.copyMessages(kf);
			
			if (kf.getResult() != null) 
				try {
					this.jsch.addIdentity(kf.getResult().getPath(), this.passphrase);
				} 
				catch (Exception x) {
					or.errorTr(300, kf.getResult().getPath(), x);	
				}
		}
		
		String obclass = config.getAttribute("TimerClass");
		
		if (StringUtil.isNotEmpty(obclass)) {
			try {
				Class<?> obc = this.getClass().getClassLoader().loadClass(obclass);
				this.obfus = (ISettingsObfuscator) obc.newInstance();
			}
			catch (Exception x) {
				or.error(208, obclass);
			}
		}

		if (this.obfus == null)
			this.obfus = new BasicSettingsObfuscator();
		
		or.trace(0, "dcDb Connection Pool Initialized");
		
		return or;
	}
	
	/**
	 * Database data containing sensitive info may be obscured (encypted or hashed)
	 * to make it hard for a hacker to get anything useful from just a copy of the
	 * database file.  How data is obscured is based on the Clock's settings.
	 * 
	 * For more details on how this works @see divconq.util.ISettingsObfuscator
	 *  
	 * @return the obfuscator used with this settings file 
	 */
	@Override
	public ISettingsObfuscator getObfuscator() {
		return this.obfus;
	}
	
	/**
	 * Takes the settings from "init" and sets up the pool for operation,
	 * including making the connection to the database server.
	 * 
	 * @return messages about the success of this call
	 */
	@Override
	public OperationResult start() {
		final OperationResult or = new OperationResult(true);
		
		or.trace(0, "dcDb Connection Pool Starting");
		
		if (this.config == null) {
			or.errorTr(301);
			return or;
		}
		
		try {
			or.trace(0, "Openning Database Connection via SSH");
			
			this.session = this.jsch.getSession(this.username, this.hostname, this.port);
			
	    	if (StringUtil.isNotEmpty(this.password))
	    		this.session.setPassword(this.password);

			this.session.setUserInfo(new UserInfo() {
				@Override
				public void showMessage(String message) {
					or.info(0, "SSH session message: " + message);
				}

				@Override
				public boolean promptYesNo(String message) {
					return true;
				}

				@Override
				public boolean promptPassword(String message) {
					return false;
				}

				@Override
				public boolean promptPassphrase(String message) {
					return false;
				}

				@Override
				public String getPassword() {
					return DatabaseManager.this.password;
				}

				@Override
				public String getPassphrase() {
					return null;
				}
			});

			this.session.connect(30000); // making a connection with timeout.
			this.session.setTimeout(20000);   // 20 second read timeout
			
			or.trace(0, "Database Connected via SSH");
		} 
		catch (Exception x) {
			or.errorTr(302, this.id, x);
			this.session = null;
			return or;
		}
		
		for (int i = 0; i < this.pool.length; i++) 
			or.copyMessages(this.initSlot(i));
		
		
		this.submit(new QueryRequest("dcSeed"), new ObjectCallback() {			
			@Override
			public void process(ObjectResult res) {
				if (res.hasErrors()) {
					TaskContext.getOrHub().errorTr(116, DatabaseManager.this.id);
				}
				else {
					RecordStruct rec = (RecordStruct) res.getResult();
					String seed = rec.getFieldAsString("Seed");
					
					Memory msd = new Memory(300);
					msd.write(seed);
					msd.setPosition(0);
					
					XElement clockconf = new XElement("Clock");
					
					if (DatabaseManager.this.config.hasAttribute("Clock")) {
						clockconf.setAttribute("Id", DatabaseManager.this.config.getAttribute("Clock"));
					}
					else {
						XElement clock1 = Hub.instance.getConfig().selectFirst("Clock");
						clockconf.setAttribute("Id", clock1.getAttribute("Id"));
					}
					
					clockconf.setAttribute("Feed", HexUtil.bufferToHex(msd));

					DatabaseManager.this.obfus.init(clockconf);
				}
			}
		}); 
		
		this.clock = Hub.instance.getClock().schedulePeriodicInternal(new Runnable() {
			@Override
			public void run() {
				if (DatabaseManager.this.shutdown)
					return;
				
				// check for connections in timeout state
				
				// after 5 minutes TODO config
				long stamp = System.currentTimeMillis() - (1 * 60 * 1000);
				
				for (Connection w : DatabaseManager.this.pool)
					if (w != null)
						w.stopTimeout(stamp);

				// TODO future try restore lost sessions 
				
				// if we are still off line after that then something is wrong, clear the task queue so callers aren't waiting too long 
				if (DatabaseManager.this.isOffline()) {
					DatabaseTask task = DatabaseManager.this.queue.poll();
					
					while (task != null) { 
						task.result.errorTr(303);
						task.complete();
						
						task = DatabaseManager.this.queue.poll();
					}
				}					
			}
		}, this.scheduleFreq);
		
		or.info(0, "dcDb Connection Pool Started");
		
		return or;
    }
	
	/**
	 * @return if too many failed connection attempt then pool goes into offline mode (recovery approach planned in future releases)
	 */
	protected boolean isOffline() {
		return (this.failedConnects.get() > 2) || this.shutdown;		
	}
	
	/**
	 * Each connection is stored in a "slot".  When a connection goes bad then slot is emptied
	 * and then a new connection is set in the slot.  Calling this method will replace any 
	 * connection currently in this slot, so do not call unless the slot is empty or known to
	 * have a bad connection in it.
	 *  
	 * @param slot which slot needs to initialize
	 * @return messages about the success of this call
	 */
	protected OperationResult initSlot(int slot) {
		OperationResult or = new OperationResult(true);
		
		Connection oldconn = this.pool[slot];
		
		// should already be disconnected, just ensure it 
		if (oldconn != null) {
			or.trace(0, "Removing old dcDb connection for slot: " + slot);
			oldconn.disconnect();
		}
		
		// TODO add better logic for the failed connection tracking and recovery
		if (!this.isOffline()) {
			or.trace(0, "Adding dcDb connection for slot: " + slot);
			
			Connection conn = new Connection();
			or.copyMessages(conn.init(this.config, this));
			
			if (or.hasErrors())
				return or;
			
			or.trace(0, "Starting dcDb connection for slot: " + slot);
			
			or.copyMessages(conn.start());
			
			// if not start then increment a failed conn counter
			if (or.hasErrors()) {
				or.errorTr(305, this.id, slot);  
				this.failedConnects.intValue();
				return or;
			}
			
			or.trace(0, "dcDb connected for slot: " + slot);
			this.pool[slot] = conn;
			this.failedConnects.set(0);
			
			or.info(0, "Database Pool slot " + slot + " changed, now have new connection");
		}
		else {
			or.info(0, "Database Pool slot " + slot + " changed, slot retired");
		}
		
		return or;
	}
	
	/**
	 * Gracefully stop the database pool, allowing for up to 1 minute for shutdown.
	 * 
	 * @return messages about the success of this call
	 */
	@Override
	public OperationResult stop() {
		OperationResult or = new OperationResult(true);
		
		or.trace(0, "dcDb Connection Pool Stopping");
		
		this.shutdown = true;
		
		if (this.clock != null) {
			this.clock.cancel(false);
			this.clock = null;
		}
		
		or.trace(0, "Connection Pool Stopping Nice");
		
		for (Connection w : this.pool)
			if (w != null)
				w.stopNice();
		
		or.trace(0, "Connection Pool Waiting");
				
		// wait a minute for things to finish up.
		for (int i = 0; i < 60; i++) {
			for (Connection w : this.pool) 
				if ((w != null) && w.busy)
					break;
			
			try {
				Thread.sleep(1);
			}
			catch (Exception x) {				
			}
		}
		
		int stillbusy = 0;
		
		for (Connection w : this.pool) 
			if ((w != null) && w.busy)
				stillbusy++;
		
		or.trace(0, "Connection Pool Busy after wait: " + stillbusy);
		
		or.trace(0, "Connection Pool Interrupt Remaining Workers");
		
		for (Connection w : this.pool) 
			if (w != null)
				w.stop();
		
		or.trace(0, "Connection Pool Stopped");
		
		if (session != null) {
			this.session.disconnect();
			this.session = null;
		}
		
		for (int i = 0; i < this.pool.length; i++) 
			this.pool[i] = null;
		
		return or;
    }
	
	/**
	 * Asynchronously submit a database request for processing.  If the submit succeeds then look
	 * for the result (even if in error) to come to the callback.  If submit does not succeed then
	 * look for the result to come from the method return, and callback will not be called. 
	 *  
	 *  @param request request object to be processed
	 *  @param cb  the callback to use when database response is complete
	 *  @return messages about the success of this call
	 */
	@Override
	public OperationResult submit(IDatabaseRequest request, ObjectCallback cb) {
		return this.submit(request, new ObjectResult(), cb);
	}
	
	/**
	 * Asynchronously submit a database request for processing.  If the submit succeeds then look
	 * for the result (even if in error) to come to the callback.  If submit does not succeed then
	 * look for the result to come from the method return, and callback will not be called. 
	 *  
	 *  @param request request object to be processed
	 *  @param resp object to process and hold the result
	 *  @param cb  the callback to use when database response is complete
	 *  @return messages about the success of this call
	 */
	@Override
	public OperationResult submit(IDatabaseRequest request, DatabaseResult resp, IDatabaseCallback cb) {
		OperationResult or = new OperationResult(true);
		
		if (cb == null) {
			or.errorTr(306);
			return or;
		}
		
		if (request == null) {
			or.errorTr(307);		
			return or;
		}
		
		if (this.isOffline()) {
			or.errorTr(308);		
			return or;
		}
		
		if (resp == null) 
			resp = new ObjectResult();
		
		DatabaseTask task = this.buildRequest(or, request, resp, cb);
		
		if (resp.hasErrors() || (task == null)) {
			or.errorTr(309);	
			return or;
		}
		
		this.queue.add(task);
		this.totalTasksSubmitted.incrementAndGet();
				
		for (int i = 0; i < this.pool.length; i++) {
			Connection w = this.pool[i];
		
			if ((w != null) && w.stopping)
				this.initSlot(i);
		}
		
		return or;
	}
	
	/**
	 * Block on task queue until a task becomes available
	 * 
	 * @return a task ready for processing
	 * @throws InterruptedException
	 */
	protected DatabaseTask take() throws InterruptedException {
		return this.queue.poll(100, TimeUnit.MILLISECONDS);
	}
	
	/**
	 * @param offset in seconds from now
	 * @return a valid timestamp for use in dcDb auditing
	 */
	protected String allocateStamp(int offset) {
		long ns = this.nextseq.getAndIncrement();
		
		if (ns > 9999) {
			synchronized (this.nextseq) {
				this.nextseq.set(0);
				ns = this.nextseq.getAndIncrement();
			}
		}
		
		return TimeUtil.stampFmt.print(new DateTime(DateTimeZone.UTC).plusSeconds(offset)) 
			+ String.format("%04d", ns);
	}
	
	/**
	 * Take a value and convert it to a format acceptable by this implementation
	 * (e.g. values > 4000 need to turn into lists, so with MUMPS they all become lists)  
	 * 
	 * @param obj value to convert 
	 * @return convert status and the value in its prepared form (e.g. list)
	 */
	@Override
	public FuncResult<Struct> convertValue(Object obj) {
		FuncResult<Struct> resp = new FuncResult<Struct>(false);
		
		ListStruct parts = new ListStruct();
		resp.setResult(parts);
		
		if (obj == null) { 
			parts.addItem((Object) null);
			return resp;
		}
		
		if (obj instanceof ScalarStruct)
			obj = ((ScalarStruct)obj).getGenericValue();

		// character data cannot exceed 4000 *bytes* per list item, go through
		// and grab just enough characters to max out a line and add to list, repeat till end
		if ((obj instanceof CharSequence) || (obj instanceof CompositeStruct)) {
			// TODO eventually optimize these
			
			Memory mem = null;
			
			if (obj instanceof CharSequence) {
				mem = new Memory();			// TODO adjust size based on sequence length, no more than 128KB? and no less than 1024B?
				mem.write((CharSequence)obj);
			}
			else if (obj instanceof CompositeStruct)
				try {
					mem = ((CompositeStruct)obj).toMemory();
				} 
				catch (BuilderStateException x) {
					resp.errorTr(323, x);
					return resp;
				}
			
			mem.setPosition(0);
		
			while (mem.getPosition() < mem.getLength()) {
				CharSequence chars = mem.readCharsCapped(3950);
				
				if (chars.length() > 0)
					parts.addItem(chars);
			}			
		}
		else if (obj instanceof Memory) {
			Memory mem = (Memory) obj;
			
			mem.setPosition(0);
			
			while (mem.getPosition() < mem.getLength())
				parts.addItem(HexUtil.bufferToHex(mem, 1975));
		}
		else
			parts.addItem(obj);
		
		return resp;
	}	
	
	/**
	 * Before a request is submitted to the database queue it is first prepared for M - this steals time
	 * from the requesting thread instead of from the connection thread (as desired).  However, it does
	 * take up some memory to hold the prepared request.
	 *  
	 * @param or tracks the build outcome
	 * @param request request object to prepare
	 * @param resp object to track the database response (only if build is successful, otherwise see "or" above)
	 * @param cb object to call when database response is complete
	 * @return the database task if build succeeds, null otherwise
	 */
	protected DatabaseTask buildRequest(OperationResult or, IDatabaseRequest request, DatabaseResult resp, IDatabaseCallback cb) {
		String rtype = request.isDatabaseUpdate() ? "UPDATE" : "QUERY";
		String name = request.getProcedure();
		CompositeStruct params = request.getDatabaseParams(this);
	
		or.copyMessages(Hub.instance.getSchema().validateProcRequest(name, params));
		
		request.validate(or);
		
		// if did not pass validation
		if (or.hasErrors()) {
			or.errorTr(311);
			return null;
		}
	
		Memory buff = new Memory();
		
		// add in context
		TaskContext tc = TaskContext.get();
		
		if (tc == null) { 
			or.errorTr(310);
			return null;
		}
		
		if (!tc.verify()) {
			or.errorTr(312);
			return null;
		}
		
		buff.write("CONTEXT " + TaskContext.getHubId() + " " + tc.getTaskId() 
				+ " " + tc.getUserId() + " " + this.allocateStamp(0)
				+ " " + tc.getLevel() + " " + tc.getLocale() 
				+ " " + tc.getChronology() + " " + tc.getOrigin() 
				+ "\n");
		
		// add in parameters
		buff.write(request.isBulkyRequest() ? "BPARAMS\n" : "LPARAMS\n");		
		this.buildParams(buff, params, null, or);
		buff.write("!\n");
		
		buff.write(rtype + " " + name + "\n");
		
		// bulky requests can be 41 MB total
		if (request.isBulkyRequest()) {
			if (buff.getLength() > 41 * 1024 * 1024) {
				or.errorTr(324, name, buff.getLength());
				return null;
			}
		}
		// other requests can be 1.25 MB total
		else {
			if (buff.getLength() > (1 * 1024 * 1024) + (256 * 1024)) {
				or.errorTr(324, name, buff.getLength());
				return null;
			}
		}
				
		or.trace(0, "dcDb call prepared for procedure: " + name);
		
		//System.out.println("===============================================================");
		//System.out.println("db request:\n" + buff);
		
		DatabaseTask task = new DatabaseTask();
		task.request = request;
		task.result = resp;
		task.callback = cb;
		task.mem = buff;
		task.context = tc;
		
		return task;
	}
	
	/**
	 * Takes a composite structure and turns it into parameters to prepare a valid mumps data structure.
	 * 
	 * @param sb parameters get stored in memory before being written to database
	 * @param composite java structure to convert to mumps structure
	 * @param subscripts any prepending subscripts to go before this structure in mumps strucuture
	 * @param resp tracks the preparation outcome
	 */
	protected void buildParams(Memory sb, CompositeStruct composite, String subscripts, OperationResult resp) {
		if (composite == null) 
			return;
		
		if (composite instanceof RecordStruct)
			for (FieldStruct f : ((RecordStruct)composite).getFields()) {
				Object v = f.getValue();
				
				String fsub = ((subscripts == null) ? "" : subscripts + ",") + this.escapeAsKey(f.getName(), resp);
				
				if (v instanceof RecordStruct) 
					this.buildParams(sb, (RecordStruct)v, fsub, resp);
				else if (v instanceof ListStruct) 
					this.buildParams(sb, (ListStruct)v, fsub, resp);
				else 
					sb.write(fsub + "\n" + this.prepValue(v, resp) + "\n");		// TODO check that fsub is not too big
				
				if (resp.hasErrors())
					break;
			}
		else
			for (int i = 0; i < ((ListStruct)composite).getSize(); i++) {
				Object v = ((ListStruct)composite).getItem(i);
				
				String fsub = ((subscripts == null) ? "" : subscripts + ",") + i;
				
				if (v instanceof RecordStruct) 
					this.buildParams(sb, (RecordStruct)v, fsub, resp);
				else if (v instanceof ListStruct) 
					this.buildParams(sb, (ListStruct)v, fsub, resp);
				else 
					sb.write(fsub + "\n" + this.prepValue(v, resp) + "\n");		// TODO check that fsub is not too big
				
				if (resp.hasErrors())
					break;
			}
	}
	
	/**
	 * Parameter values need escaping before they can be sent to M.  
	 * 
	 * @param obj value to convert or escape
	 * @param resp tracks preparation success
	 * @return the value in its prepared form
	 */
	protected String prepValue(Object obj, OperationResult resp) {
		// TODO add handling for dates, others?
		// especially scalar Structs 
		// TODO base off types in Schema first, try to convert input into correct type (if found string for example)
		
		if (obj == null) 
			return this.escapeAsValue("", resp);
		
		if (obj instanceof ScalarStruct)
			obj = ((ScalarStruct)obj).getGenericValue();
		
		if (obj == null) 
			return this.escapeAsValue("", resp);
		
		if (obj instanceof Number)
			return obj.toString();
		
		if (obj instanceof Boolean)
			return ((Boolean)obj) ? "1" : "0";

		// TODO convert this to use a configurable date-time, that way each database can configure how it wishes to store dates
		// including BCE support and year padding for more than 20000 year support (also resolve Gregorian/Julian dates) 
		if (obj instanceof DateTime)
			return this.escapeAsValue(TimeUtil.stampFmt.print(((DateTime)obj).toDateTime(ISOChronology.getInstanceUTC())), resp);
		
		if (obj instanceof BigDateTime)
			return this.escapeAsValue(((BigDateTime)obj).toString(), resp);
		
		if (obj instanceof CharSequence)
			return this.escapeAsValue(obj.toString(), resp);  // TODO make this more efficient
		
		//  this should be caught by convertValue
		//if (obj instanceof Memory)
		//	return this.escapeAsValue(obj.toString(), resp);  // TODO make this emit HEX more efficient
		
		//  this should be caught by convertValue
		//if (obj instanceof Struct)
		//	return this.escapeAsValue(obj.toString(), resp);	// TODO make this more efficient
		
		return this.escapeAsValue("", resp);
	}	

	/**
	 * Parameter keys may not be blank, greater than 250 characters, contain any restricted characters 
	 * including \f, \b, \r, \n, \t or "
	 *  
	 * @param key name of the parameter
	 * @param resp errors with key are added to this result
	 * @return if key name is allowed then return quoted name, else null
	 */
	protected String escapeAsKey(String key, OperationResult resp) {
		if (StringUtil.isEmpty(key)) {
			resp.errorTr(313);
			return null;
		}
		
		if (key.length() > 250) {
			resp.errorTr(314, key);
			return null;
		}
		
		// give up if restricted chars
		if (StringUtil.containsRestrictedChars(key) || key.contains("\f")  || key.contains("\b")
				  || key.contains("\r")  || key.contains("\t")  || key.contains("\n") || key.contains("\"")) 
		{
			resp.errorTr(315, key);
			return null;
		}
		
		return "\"" + key + "\"";
	}
	
	/**
	 * Parameter (string) values may not be greater than 4000 characters or contain any restricted characters 
	 * including \f, \b.  Special character \r is stripped and special characters \n and " are escaped.
	 *  
	 * @param str value of the parameter
	 * @param resp errors with value are added to this result
	 * @return if value is allowed then return quoted value, else null
	 */
	protected String escapeAsValue(String str, OperationResult resp) {		
		if (str.length() > 4 * 1000) {
			resp.errorTr(316, str.length());
			return null;
		}
		
		if (StringUtil.containsRestrictedChars(str) || str.contains("\f")  || str.contains("\b")) {
			resp.errorTr(317);
			return null;
		}
		
		// \r not allowed, strip
		str = str.replace("\r", "");
		
		// escape double quotes and newlines 
		return "\"" + str.replace("\\", "\\\\").replace("\"", "\"\"").replace("\n", "\\n").replace("\t", "\\t") + "\"";
	}
	
	/**
	 * @return the number of database tasks that have been successfully submitted to the database queue
	 */
	protected long tasksSubmitted() {
		return this.totalTasksSubmitted.get();
	}
	
	/**
	 * When a database tasks has been successfully submitted to the database queue
	 * call this method.
	 */
	protected void incTasksSubmitted() {
		this.totalTasksSubmitted.intValue();
	}
	
	/**
	 * @return  the number of tasks that have been removed from the queue and started by a connection
	 */
	protected long tasksStarted() {
		return this.totalTasksStarted.get();
	}
	
	/**
	 * When a database task has been removed from the queue and started
	 * call this to track how many have started.
	 */
	protected void incTasksStarted() {
		this.totalTasksStarted.intValue();
	}
	
	/**
	 * @return number of database tasks that have successfully been processed
	 */
	protected long tasksCompleted() {
		return this.totalTasksCompleted.get();
	}
	
	/**
	 * When a database task has completed successfully (even if the result contains errors)
	 * call this to track how many completed.
	 */
	protected void incTasksComlpeted() {
		this.totalTasksCompleted.intValue();
	}

	/**
	 * Get a summary of all status and counters for this database pool
	 * 
	 * @return key value pairs for the various counters and flags
	 */
	@Override
	public RecordStruct getStatus() {
		// TODO
		//return this.pool.reconnectCount();
		
		return null;
	}

	/* (non-Javadoc)
	 * @see divconq.db.IDatabaseManager#resetConnections()
	 */
	@Override
	public void resetConnections() {
		for (int i = 0; i < this.pool.length; i++) {
			Connection w = this.pool[i];
		
			if (w != null) 
				w.stopping = true;
		}
	}
	
	/**
	 * Uploads any schema and dictionary changes from a developer's local repository to
	 * the database.
	 * 
	 * @param cb the callback to use when the upload is done
	 */
	public void syncSchema(final OperationCallback cb) {
		this.submit(new SchemaSyncRequest(), new ObjectCallback() {			
			@Override
			public void process(ObjectResult res) {
				cb.copyMessages(res);
				cb.completed();
			}
		});
	}
	
	public void syncScripts(final OperationCallback operationCallback) {
		if ((this.session == null) || !this.session.isConnected()) {
			operationCallback.errorTr(325);
			operationCallback.completed();
			return;
		}
		
		ChannelSftp c = null;

		try {			
			Channel channel = this.session.openChannel("sftp");
			channel.connect();
			c = (ChannelSftp) channel;
			
			// go to routines folder
			c.cd(".fis-gtm/r");
			
			String p2 = ".";
			
			ProjectCompiler comp = new ProjectCompiler();
			FuncResult<List<File>> res = comp.getProcs(Hub.instance.getResources().getPackages());
			
			if (res.hasErrors()) 
				operationCallback.copyMessages(res);
			else {
				for (File f : res.getResult()) {
					c.put(f.getPath(), p2, null, ChannelSftp.OVERWRITE);
				}
			}
		} 
		catch (SftpException x) {
			operationCallback.errorTr(325, x);
		} 
		catch (JSchException x) {
			operationCallback.errorTr(325, x);
		}
		finally {			
			if (c.isConnected())
				c.exit();
		}
		
		operationCallback.completed();
	}
}
