1 /**

     2  * SQL-DK

     3  * Copyright © 2014 František Kučera (frantovo.cz)

     4  *

     5  * This program is free software: you can redistribute it and/or modify

     6  * it under the terms of the GNU General Public License as published by

     7  * the Free Software Foundation, either version 3 of the License, or

     8  * (at your option) any later version.

     9  *

    10  * This program is distributed in the hope that it will be useful,

    11  * but WITHOUT ANY WARRANTY; without even the implied warranty of

    12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

    13  * GNU General Public License for more details.

    14  *

    15  * You should have received a copy of the GNU General Public License

    16  * along with this program. If not, see <http://www.gnu.org/licenses/>.

    17  */

    18 package info.globalcode.sql.dk.formatting;

    19 

    20 import info.globalcode.sql.dk.Constants;

    21 import info.globalcode.sql.dk.NamedParameter;

    22 import info.globalcode.sql.dk.Parameter;

    23 import info.globalcode.sql.dk.Xmlns;

    24 import info.globalcode.sql.dk.configuration.DatabaseDefinition;

    25 import info.globalcode.sql.dk.configuration.Properties;

    26 import info.globalcode.sql.dk.configuration.Property;

    27 import static info.globalcode.sql.dk.formatting.AbstractXmlFormatter.qname;

    28 import java.sql.Array;

    29 import java.sql.SQLException;

    30 import java.util.Date;

    31 import java.util.List;

    32 import java.util.Map;

    33 import java.util.Scanner;

    34 import java.util.logging.Level;

    35 import java.util.logging.Logger;

    36 import javax.xml.namespace.QName;

    37 

    38 /**

    39  * Prints result sets and parameters as tables, SQL as preformatted and updates counts as

    40  * paragraphs. You can pick XHTML fragments (usually tabular data) and use it on your website or use

    41  * whole output as preview or report.

    42  *

    43  * @author Ing. František Kučera (frantovo.cz)

    44  */

    45 public class XhtmlFormatter extends AbstractXmlFormatter {

    46 

    47 	private static final Logger log = Logger.getLogger(XhtmlFormatter.class.getName());

    48 	public static final String NAME = "xhtml"; // bash-completion:formatter

    49 	private static final String DOCTYPE = "html PUBLIC \"-//W3C//DTD XHTML 1.1 plus MathML 2.0 plus SVG 1.1//EN\" \"http://www.w3.org/2002/04/xhtml-math-svg/xhtml-math-svg.dtd\"";

    50 	private static final String CSS_FILE = "info/globalcode/sql/dk/formatter/XhtmlFormatter.css";

    51 	private int statementCounter = 0;

    52 	private int resultSetCounter = 0;

    53 	private int updatesResultCounter = 0;

    54 

    55 	public XhtmlFormatter(FormatterContext formatterContext) {

    56 		super(addDefaults(formatterContext));

    57 	}

    58 

    59 	/**

    60 	 * Do not indent text – preserve whitespace for pre elements

    61 	 */

    62 	private static FormatterContext addDefaults(FormatterContext formatterContext) {

    63 		Properties defaults = new Properties(1);

    64 		defaults.add(new Property(PROPERTY_INDENT_TEXT, "false"));

    65 		formatterContext.getProperties().setLastDefaults(defaults);

    66 		return formatterContext;

    67 	}

    68 

    69 	@Override

    70 	public void writeStartBatch() {

    71 		super.writeStartBatch();

    72 		printStartDocument();

    73 		printDoctype(DOCTYPE);

    74 		printStartElement(qname("html"), singleAttribute(qname("xmlns"), Xmlns.XHTML));

    75 

    76 		printStartElement(qname("head"));

    77 		printTextElement(qname("title"), null, Constants.PROGRAM_NAME + ": batch results");

    78 		printCss();

    79 		printEndElement();

    80 

    81 		printStartElement(qname("body"));

    82 	}

    83 

    84 	private void printCss() {

    85 

    86 		try (Scanner css = new Scanner(getClass().getClassLoader().getResourceAsStream(CSS_FILE))) {

    87 			printStartElement(qname("style"), singleAttribute(qname("type"), "text/css"));

    88 			while (css.hasNext()) {

    89 				printText(css.nextLine(), true);

    90 			}

    91 			printEndElement();

    92 		}

    93 	}

    94 

    95 	@Override

    96 	public void writeEndBatch() {

    97 		super.writeEndBatch();

    98 		printEndElement();

    99 		printEndElement();

   100 		printEndDocument();

   101 	}

   102 

   103 	@Override

   104 	public void writeStartDatabase(DatabaseDefinition databaseDefinition) {

   105 		super.writeStartDatabase(databaseDefinition);

   106 		printTextElement(qname("h1"), null, "Database: " + databaseDefinition.getName());

   107 

   108 		printStartElement(qname("p"));

   109 		printText("This is XHTML output of batch executed at: ", true);

   110 		printText(new Date().toString(), true);

   111 		printEndElement();

   112 	}

   113 

   114 	@Override

   115 	public void writeQuery(String sql) {

   116 		super.writeQuery(sql);

   117 		printTextElement(qname("pre"), null, sql);

   118 	}

   119 

   120 	@Override

   121 	public void writeParameters(List<? extends Parameter> parameters) {

   122 		super.writeParameters(parameters);

   123 

   124 		if (parameters == null || parameters.isEmpty()) {

   125 			printTextElement(qname("p"), null, "(this query has no parameters)");

   126 		} else {

   127 			printTextElement(qname("h3"), null, "Parameters:");

   128 

   129 			printStartElement(qname("table"));

   130 

   131 			printStartElement(qname("thead"));

   132 			printStartElement(qname("tr"));

   133 			printTextElement(qname("td"), null, "id");

   134 			printTextElement(qname("td"), null, "type");

   135 			printTextElement(qname("td"), null, "value");

   136 			printEndElement();

   137 			printEndElement();

   138 

   139 			printStartElement(qname("tbody"));

   140 			for (int i = 0; i < parameters.size(); i++) {

   141 				Parameter p = parameters.get(i);

   142 				printStartElement(qname("tr"));

   143 				String numberOrName;

   144 				if (p instanceof NamedParameter) {

   145 					numberOrName = ((NamedParameter) p).getName();

   146 				} else {

   147 					numberOrName = String.valueOf(i + 1);

   148 				}

   149 				printTextElement(qname("td"), null, numberOrName);

   150 				printTextElement(qname("td"), null, p.getType().name());

   151 				printTableData(p.getValue());

   152 				printEndElement();

   153 			}

   154 			printEndElement();

   155 

   156 			printEndElement();

   157 		}

   158 	}

   159 

   160 	private void printTableData(Object value) {

   161 

   162 		if (value instanceof Array) {

   163 			Array sqlArray = (Array) value;

   164 			try {

   165 				Object[] array = (Object[]) sqlArray.getArray();

   166 				printStartElement(qname("td"));

   167 				printArray(array);

   168 				printEndElement();

   169 			} catch (SQLException e) {

   170 				log.log(Level.SEVERE, "Unable to format array", e);

   171 				printTableData(String.valueOf(value));

   172 			}

   173 		} else {

   174 			Map<QName, String> attributes = null;

   175 			if (value instanceof Number) {

   176 				attributes = singleAttribute(qname("class"), "number");

   177 			} else if (value instanceof Boolean) {

   178 				attributes = singleAttribute(qname("class"), "boolean");

   179 			}

   180 			printTextElement(qname("td"), attributes, String.valueOf(value));

   181 		}

   182 	}

   183 

   184 	private void printArray(Object[] array) {

   185 		printStartElement(qname("ul"));

   186 		for (Object o : array) {

   187 			if (o instanceof Object[]) {

   188 				printStartElement(qname("li"));

   189 				printTextElement(qname("p"), null, "nested array:");

   190 				printArray((Object[]) o);

   191 				printEndElement();

   192 			} else {

   193 				printTextElement(qname("li"), null, String.valueOf(o));

   194 			}

   195 		}

   196 		printEndElement();

   197 	}

   198 

   199 	@Override

   200 	public void writeStartResultSet(ColumnsHeader header) {

   201 		super.writeStartResultSet(header);

   202 		resultSetCounter++;

   203 		printEmptyElement(qname("hr"), null);

   204 		printTextElement(qname("h3"), null, "Result set #" + resultSetCounter);

   205 		printStartElement(qname("table"));

   206 		printStartElement(qname("thead"));

   207 		printStartElement(qname("tr"));

   208 		for (ColumnDescriptor cd : header.getColumnDescriptors()) {

   209 			// TODO: type

   210 			printTextElement(qname("td"), null, cd.getLabel());

   211 		}

   212 		printEndElement();

   213 		printEndElement();

   214 

   215 		printStartElement(qname("tbody"));

   216 	}

   217 

   218 	@Override

   219 	public void writeEndResultSet() {

   220 		super.writeEndResultSet();

   221 		printEndElement();

   222 		printEndElement();

   223 		printTextElement(qname("p"), null, "Record count: " + getCurrentRowCount());

   224 	}

   225 

   226 	@Override

   227 	public void writeStartRow() {

   228 		super.writeStartRow();

   229 		printStartElement(qname("tr"));

   230 	}

   231 

   232 	@Override

   233 	public void writeColumnValue(Object value) {

   234 		super.writeColumnValue(value);

   235 		printTableData(value);

   236 	}

   237 

   238 	@Override

   239 	public void writeEndRow() {

   240 		super.writeEndRow();

   241 		printEndElement();

   242 	}

   243 

   244 	@Override

   245 	public void writeStartStatement() {

   246 		super.writeStartStatement();

   247 		statementCounter++;

   248 		printEmptyElement(qname("hr"), null);

   249 		printTextElement(qname("h2"), null, "SQL statement #" + statementCounter);

   250 		resultSetCounter = 0;

   251 		updatesResultCounter = 0;

   252 	}

   253 

   254 	@Override

   255 	public void writeUpdatesResult(int updatedRowsCount) {

   256 		super.writeUpdatesResult(updatedRowsCount);

   257 		updatesResultCounter++;

   258 		printEmptyElement(qname("hr"), null);

   259 		printTextElement(qname("h3"), null, "Updates result #" + updatesResultCounter);

   260 		printTextElement(qname("p"), null, "Updated rows: " + updatedRowsCount);

   261 	}

   262 }