/**

A plugin for ImageJ(C) to save/read images into/from a PostgreSQL database.
Copyright (C) 2005 Albert Cardona.

This program 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 (http://www.gnu.org/licenses/gpl.txt )

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, write to the Free Software
Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA. 

You may contact Albert Cardona at acardona at ini.phys.ethz.ch
Institute of Neuroinformatics, University of Zurich / ETH, Switzerland.

*/


/*
 * A plugin to save files in a PostgreSQL database and open already saved files.
 *
 * Assumed is:
 * 	- a JDBC (java database driver) in path when ImageJ is launched. It's the statement:  Class.forName("org.postgresql.Driver");
 * 	- an existing database in a postgresql server
 * 	- an existing table in that postgresql database
 * 	- the columns image_name (String) and image_bytes (bytea) in that table, in this order (you may edit the names of these columns below)
 * 	- that the user has privileges to insert data into that table.
 *
 * 	IMPORTANT:
 * 	Edit the fields below appropiately to match your database!
 */


import ij.plugin.PlugIn;
import ij.WindowManager;
import ij.ImagePlus;
import ij.ImageStack;
import ij.IJ;
import ij.ImageJ;
import ij.gui.GenericDialog;
import ij.io.FileInfo;
import ij.io.TiffEncoder;
import ij.io.TiffDecoder;
import ij.io.FileOpener;
import ij.io.Opener;
import ij.process.ImageProcessor;
import ij.measure.Calibration;

import java.sql.*;
import java.text.*;
import java.io.*;
import java.util.ArrayList;

public class PostgreSQL_ implements PlugIn {


	/** Edit this to save you typing*/
	String database_name = "shared";
	String table_name = "images";
	String user_name = "imagej";
	String password = "rasband";

	/** Edit this to match your column names in the database*/
	String column_image_name = "image_name";
	String column_image_bytes = "image_bytes";
	
	Connection connection = null;
	boolean driver_loaded = false;

	//testing functions
	Statement func_id;

	public void run(String arg) {
		
		// a dialog to ask about what to do
		GenericDialog gd1= new GenericDialog("Do what?");
		final String[] options1 = new String[]{"save current image", "open image"};
		gd1.addChoice("Do what: ", options1, options1[0]);
		gd1.showDialog();
		if (gd1.wasCanceled()) {
			return;
		}
		int choice1 = gd1.getNextChoiceIndex();
		switch(choice1) {
			case 0: //save
				saveCurrentImage();
				break;
			case 1: //open
				openSavedImage();
				break;
		}
	}
	
	void saveCurrentImage() {
		//check if any image is open
		ImagePlus current_img = WindowManager.getCurrentImage();
		if (null == current_img) {
			IJ.showMessage("No images open.");
			return;
		}
		//ask for data
		GenericDialog gd = new GenericDialog("Enter data");
		gd.addStringField("database name: ", database_name);
		gd.addStringField("user name: ", user_name);
		gd.addStringField("password: ", password);
		gd.addStringField("table name: ", table_name);
		gd.addStringField("image name: ", current_img.getTitle());
		gd.addStringField("column image name: ", column_image_name);
		gd.addStringField("column image bytes: ", column_image_bytes);
		gd.showDialog();
		if (gd.wasCanceled()) {
			return;
		}
		//gather data
		database_name = gd.getNextString();
		user_name = gd.getNextString();
		password = gd.getNextString();
		table_name = gd.getNextString();
		String image_name = gd.getNextString();
		column_image_name = gd.getNextString();
		column_image_bytes = gd.getNextString();
		
		//save image into database
		boolean check = saveImagePlus(current_img, image_name);
		if (!check) {
			IJ.log("Image NOT saved.");
		}
	}

	boolean saveImagePlus(ImagePlus img, String img_name) {
		
		//Create connection, if not already done
		if (null == connection) {
			boolean check = connectToDatabase(database_name, user_name, password);
			if (!check) {
				IJ.log("Can't connect to database.");
				return false;
			}
		}
		
		//Generate InputStream for the ImagePlus

		//	doing things as in FileSaver class by Wayne Rasband
		FileInfo fi = img.getFileInfo();
		Object info = img.getProperty("Info");
		if (info != null && (info instanceof String)) {
			fi.info = (String)info;
		}
		fi.description = getDescriptionString(fi, img);
		//see whether this is a stack or not
		if (fi.nImages > 1) {
			IJ.log("saving a stack!");
			//virtual stacks would be supported? I don't think so because the FileSaver.saveAsTiffStack(String path) doesn't.
			if (fi.pixels == null && img.getStack().isVirtual()) {
				//don't save it!
				IJ.showMessage("Virtual stacks not supported.");
				return false;
			}
			//setup stack things as in FileSaver.saveAsTiffStack(String path)
			fi.sliceLabels = img.getStack().getSliceLabels();
		}
		TiffEncoder te = new TiffEncoder(fi);
		//duplicated memory solution
		ByteArrayInputStream i_stream = null;
		//not useful, as this is for the buffer not the real size//int estimated_size = img.getWidth() * img.getHeight() * img.getStack().getSize();
		ByteArrayOutputStream o_bytes = new ByteArrayOutputStream();
		try {
			//easy solution, uses double as much memory resources:
			DataOutputStream o_stream = new DataOutputStream(new BufferedOutputStream(o_bytes));
			
			te.write(o_stream);
			//o_stream.flush(); //not needed, I don't know why. Wayne Rasband does not use it either in his FileSaver class, but it works
			i_stream = new ByteArrayInputStream(o_bytes.toByteArray());
			o_stream.close();

			///////////
			//test for stacks: save to file
			/*
			String path = "/InternetDownloads/tmp/file1.tif";
			DataOutputStream os = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(path)));
			te.write(os);
			os.close();
			*/
			//Result: saves only 1 slice for a 20x20 image, but all 3 slices for a 2x2 image. ????? buffer problems? Maybe I got confused and only saves the first slice (sounds more reasonable)
			//////////
			
		}catch(Exception e) {
			IJ.log("Problems at writing the image using the TiffEncoder.write(dos) :\n " + e);
		}
		
		//Put the image stream into the given table
		//String query = "insert into " + table_name + " (" + column_image_name + "," + column_image_bytes + ") values ('" + img_name + "', ?)";
		try {
		//	PreparedStatement prepared_statement = connection.prepareStatement(query);
		//	prepared_statement.setBinaryStream(1, i_stream, i_stream.available());
		//	prepared_statement.executeUpdate();
		//	IJ.showStatus("Image saved successfully.");
			
			//Testing functions:
			// 1 - create function
			func_id = connection.createStatement();
			func_id.execute("CREATE OR REPLACE FUNCTION addImageAndGetId(text, bytea) RETURNS refcursor AS ' DECLARE cursor refcursor; BEGIN INSERT INTO " + table_name + " (" + column_image_name + "," + column_image_bytes + ") VALUES ($1, $2); OPEN cursor FOR SELECT id FROM " + table_name + " ORDER BY id DESC LIMIT 1; RETURN cursor; END;' LANGUAGE plpgsql");
			// 2 - execute function
			// we must be inside a transaction for cursors to work
			connection.setAutoCommit(false);
			//String query1 = "SELECT id FROM addImageAndGetId('" + img_name + "', ?)";
			CallableStatement cst = connection.prepareCall("{ ? = CALL addImageAndGetId('" + img_name + "', ?) }");
			cst.registerOutParameter(1, Types.OTHER);
			cst.setBinaryStream(2, i_stream, i_stream.available());
			cst.execute();
			// 3 - retrieve data from function execution
			ResultSet result = (ResultSet)cst.getObject(1);
			while (result.next()) {
				IJ.log("id of the recently added image: " + result.getLong("id"));
			}
			result.close();
			cst.close();
			IJ.showStatus("Image saved successfully.");
			//close connection
			connection.close();
		}catch(SQLException sqle) {
			IJ.log("ERROR at executing SQL query:\n");
			new IJError(sqle);
			//close stream
			try {
				i_stream.close();
			}catch(IOException ioe) {
				new IJError(ioe);
			}
			return false;
		}
		//close stream
		try {
			i_stream.close();
		}catch(IOException ioe) {
			new IJError(ioe);
		}
		
		return true;
	}

	boolean connectToDatabase(String database_name, String user_name, String password) {
		
		//load the JDBC driver for PostgreSQL, only if not already loaded
		if (!driver_loaded) {
			try {
				Class.forName("org.postgresql.Driver");
				driver_loaded = true;
			}catch(ClassNotFoundException cnfe) {
				IJ.log("Could not load org.postgresql.Driver: " + cnfe);
				return false;
			}
		}

		//connect to database, only if not already connected
		if (null == connection) {
			try {
				connection = DriverManager.getConnection("jdbc:postgresql:" + database_name, user_name, password);
			}catch(Exception e) {
				IJ.log("Can't connect to database: " + e);
				return false;
			}
		}

		return true;
	}


	boolean openSavedImage() {

		// 1 - connect to database and retrieve full list of image names
		if (null == connection) {
			GenericDialog gd = new GenericDialog("Enter data");
			gd.addStringField("database name: ", database_name);
			gd.addStringField("user name: ", user_name);
			gd.addStringField("password: ", password);
			gd.addStringField("table name: ", table_name);
			gd.addStringField("column image name: ", column_image_name);
			gd.addStringField("column image bytes: ", column_image_bytes);
			gd.showDialog();
			if (gd.wasCanceled()) {
				return false;
			}
			//gather data
			database_name = gd.getNextString();
			user_name = gd.getNextString();
			password = gd.getNextString();
			table_name = gd.getNextString();
			column_image_name = gd.getNextString();
			column_image_bytes = gd.getNextString();

			boolean check = connectToDatabase(database_name, user_name, password);
			if (!check) {
				IJ.log("Can't connect to database.");
				return false;
			}
		}

		// 2 - Retrieve list of image names from database
		String query = "select " + column_image_name + " from " + table_name;
		ArrayList al = new ArrayList();
		try {
			ResultSet result = connection.createStatement().executeQuery(query);
			while (result.next()) {
				al.add(result.getString(column_image_name));
			}
			result.close();
		}catch(Exception e) {
			IJ.log("Can't retrieve list of saved image names:\n" + e);
			try {
				connection.close();
			}catch(SQLException sqle) {
				IJ.log("Can't close connection to database:\n " + sqle);
			}
			return false;
		}

		// 3 - show list of image names in a Choice in a GenericDialog, and open the selected one
		try {
			if (0 == al.size()) {
				IJ.log("No images in database!");
				throw new Exception(); //to close connection etc.
			}
			String[] image_names = new String[al.size()];
			al.toArray(image_names);
			GenericDialog gd = new GenericDialog("Select image to load");
			gd.addChoice("Image name: ", image_names, image_names[0]);
			gd.showDialog();
			if (gd.wasCanceled()) {
				connection.close();
				return false;
			}
			String selected_image_name = gd.getNextChoice();
			String query2 = "select " + column_image_name + "," + column_image_bytes +" from " + table_name + " where " + column_image_name + "='" + selected_image_name + "'";
			ResultSet result2 = connection.createStatement().executeQuery(query2);
			if (result2.next()) {
				//using a bytea as "image_bytes" column
				InputStream i_stream = result2.getBinaryStream(column_image_bytes);
				//ImagePlus img = new Opener().openTiff(i_stream, selected_image_name);

				FileInfo[] info = null;
				try {
					TiffDecoder td = new TiffDecoder(i_stream, selected_image_name);
					info = td.getTiffInfo();
				}catch(FileNotFoundException fnfe) {
					new IJError(fnfe);
				}catch(Exception e) {
					new IJError(e);
				}
				if (null == info) {
					i_stream.close();
					return false;
				}
				ImagePlus img = null;
				if (info.length>1) {
					IJ.log("Opening a stack now");
					img = new Opener().openTiffStack(info);
					if (null == img) {
						IJ.log("stack is null!");
						//resort to open the first one
						img = new FileOpener(info[0]).open(false);
					}
				} else {
					IJ.log("Opening a single slice image");
					FileOpener fo = new FileOpener(info[0]);
					img = fo.open(false);
					if (null == img) {
						IJ.log("image is null!");
					}
				}


				try {
					i_stream.close();
				}catch(IOException ioe) {
					new IJError(ioe);
				}
				if (null != img) {
					img.show();
				} else {
					i_stream.close();
					IJ.log("Can't show() a null ImagePlus");
					throw new Exception(); //to close connection etc.
				}

			}

			connection.close();

		} catch(Exception e) {
			new IJError(e);
			IJ.log("problems:\n" + e);
			try {
				connection.close();
			}catch(SQLException sqle) {
				IJ.log("Can't close connection to database:\n " + sqle);
			}
			return false;
		}

		return true;
	}


	/** Returns a string containing information about the specified  image; copied directly from ImageJ's ij.io.FileSaver class from Wayne Rasband */
	String getDescriptionString(FileInfo fi, ImagePlus imp) {
		StringBuffer sb = new StringBuffer(100);
		sb.append("ImageJ="+ImageJ.VERSION+"\n");
		if (fi.nImages>1)
			sb.append("images="+fi.nImages+"\n");
		int channels = imp.getNChannels();
		if (channels>1)
			sb.append("channels="+channels+"\n");
		int slices = imp.getNSlices();
		if (slices>1)
			sb.append("slices="+slices+"\n");
		int frames = imp.getNFrames();
		if (frames>1)
			sb.append("frames="+frames+"\n");
		if (fi.unit!=null)
			sb.append("unit="+fi.unit+"\n");
		if (fi.valueUnit!=null) {
			sb.append("cf="+fi.calibrationFunction+"\n");
			if (fi.coefficients!=null) {
				for (int i=0; i<fi.coefficients.length; i++)
					sb.append("c"+i+"="+fi.coefficients[i]+"\n");
			}
			sb.append("vunit="+fi.valueUnit+"\n");
		}
		
		// get stack z-spacing and fps
		if (fi.nImages>1) {
			if (fi.pixelDepth!=0.0 && fi.pixelDepth!=1.0)
				sb.append("spacing="+fi.pixelDepth+"\n");
			if (fi.frameInterval!=0.0) {
				double fps = 1.0/fi.frameInterval;
				if ((int)fps==fps)
					sb.append("fps="+(int)fps+"\n");
				else
					sb.append("fps="+fps+"\n");
			}
		}
		
		// get min and max display values
		ImageProcessor ip = imp.getProcessor();
		double min = ip.getMin();
		double max = ip.getMax();
		int type = imp.getType();
		boolean enhancedLut = (type==ImagePlus.GRAY8 || type==ImagePlus.COLOR_256) && (min!=0.0 || max !=255.0);
		if (enhancedLut || type==ImagePlus.GRAY16 || type==ImagePlus.GRAY32) {
			sb.append("min="+min+"\n");
			sb.append("max="+max+"\n");
		}
		
		// get non-zero origins
		Calibration cal = imp.getCalibration();
		if (cal.xOrigin!=0.0)
			sb.append("xorigin="+cal.xOrigin+"\n");
		if (cal.yOrigin!=0.0)
			sb.append("yorigin="+cal.yOrigin+"\n");
		if (cal.zOrigin!=0.0)
			sb.append("zorigin="+cal.zOrigin+"\n");
		if (cal.info!=null && cal.info.length()<=64 && cal.info.indexOf('=')==-1 && cal.info.indexOf('\n')==-1)
			sb.append("info="+cal.info+"\n");			
		sb.append((char)0);
		return new String(sb);
	}
}