/* Copyright 2003 Elliotte Rusty Harold
   
   This library is free software; you can redistribute it and/or modify
   it under the terms of version 2.1 of the GNU Lesser General Public 
   License as published by the Free Software Foundation.
   
   This library 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 Lesser General Public License for more details.
   
   You should have received a copy of the GNU Lesser General Public
   License along with this library. If not, see
   <https://www.gnu.org/licenses/>.
   
   You can contact Elliotte Rusty Harold by sending e-mail to
   elharo@ibiblio.org. Please include the word "XOM" in the
   subject line. The XOM home page is located at https://xom.nu/
*/

package nu.xom.samples;

import nu.xom.*;
import java.sql.*;
import java.io.*;


/**
 * <p>
 *   Based on Example 8-13 in Processing XML with Java
 * </p>
 * 
 * @author Elliotte Rusty Harold
 * @version 1.0
 */
public class DatabaseBuilder  {

    private Connection connection;
      
    
    // The string passed to the constructor must be a JDBC URL that
    // contains all necessary information for connecting to the
    // database such as host, port, username, password, and
    // database name. For example, 
    // jdbc:mysql://host:port]/dbname?user=username&password=pass
    // The driver should have been loaded before this method is
    // called
    public DatabaseBuilder(String jdbcURL) throws SQLException {
        connection = DriverManager.getConnection(jdbcURL);
    }
  
    
    public Document build(String selectQuery) 
      throws SQLException, ParsingException {
    
        Statement statement = connection.createStatement();
        ResultSet data = statement.executeQuery(selectQuery);
        ResultSetMetaData metadata = data.getMetaData();
        int numFields = metadata.getColumnCount();
      
        Element table = new Element("table");
      
        while (data.next()) {
            Element record = new Element("record");
            for (int field = 1; field <= numFields; field++) {
                Element fieldElement = new Element("field");
                int type = metadata.getColumnType(field);
                String typeName = getSchemaType(type);
                Attribute typeAtt = new Attribute("xsi:type", 
                  "http://www.w3.org/2001/XMLSchema-instance", typeName);
                fieldElement.addAttribute(typeAtt);
                String name = metadata.getColumnName(field);
                Attribute nameAtt = new Attribute("name", name);
                fieldElement.addAttribute(nameAtt);
                // Convert nulls to empty elements with xsi:nil="true"
                Object value = data.getObject(field);
                if (value == null) { // null value in database
                    Attribute nilAtt = new Attribute("xsi:nil", 
                      "http://www.w3.org/2001/XMLSchema-instance", "true");
                    fieldElement.addAttribute(nilAtt);
                }
                else { // non-null value
                    fieldElement.appendChild(convertToXML(data, field, type));
                }
                record.appendChild(fieldElement);
            }
            table.appendChild(record);
        }
        statement.close();
      
        table.addNamespaceDeclaration("xsi",
          "http://www.w3.org/2001/XMLSchema-instance");
        table.addNamespaceDeclaration("xsd", "http://www.w3.org/2001/XMLSchema");
        return new Document(table);
    
    }

    
    // I want the XML document to store values in the standard W3C
    // XML Schema Language forms. This requires certain conversions 
    // depending on the type of the data
    private Node convertToXML(ResultSet data, int field, int type)
      throws SQLException, ParsingException {

        switch (type) {
          case Types.BINARY: 
          case Types.VARBINARY: 
          case Types.LONGVARBINARY: 
            return hexEncode(data.getBinaryStream(field));
          case Types.BLOB:
            Blob blob = data.getBlob(field);
            return hexEncode(blob.getBinaryStream());
          case Types.CLOB: 
            Clob clob = data.getClob(field);
            Reader r = clob.getCharacterStream();
            char[] text = new char[1024];
            int numRead;
            try {
              StringBuilder result = new StringBuilder();
              while ((numRead = r.read(text, 0, 1024)) != -1) {
                result.append(escapeText(text, 0, numRead)); 
              }
              return new Text(result.toString());
            }
            catch (IOException ex) {
              throw new ParsingException("Read from CLOB failed", ex); 
            }
          case Types.ARRAY:
            Array array = data.getArray(field);
            return writeArray(array);
          default: // All other types can be handled as strings
            Object o = data.getObject(field); 
            if (o == null) return new Text("");                
            String s = o.toString(); 
            char[] value = s.toCharArray();
            return escapeText(value, 0, value.length);
        }     

    }
  
    
    private Text hexEncode(InputStream in) {
    
    	StringBuilder result = new StringBuilder();
        try {
            int octet;
            while ((octet = in.read()) != -1) {
                StringWriter out = new StringWriter(2);
                if (octet < 16) out.write('0');
                out.write(Integer.toHexString(octet));
                result.append(out.toString());
            }
            return new Text(result.toString());
        }
        catch (IOException ex) {
          throw new XMLException("Error while hex-encoding", ex);
        }
    
    }
 
    
  // String types may contain C0 control characters that are
  // not legal in XML. I convert these to the Unicode replacement
  // character 0xFFFD
    private Text escapeText(char[] text, int start, int length) {
    	StringBuilder result = new StringBuilder(length);
        for (int i = start; i < length; i++) {
            result.append(escapeChar(text[i]));
        }
        return new Text(result.toString());
    }

    private char escapeChar(char c) {
        if (c >= 0x20) return c;
        else if (c == '\n') return c;
        else if (c == '\r') return c;
        else if (c == '\t') return c;
        return '\uFFFD';
    }
 
    private Node writeArray(Array array)
      throws SQLException, ParsingException {
    
        ResultSet data = array.getResultSet();
        int type = array.getBaseType();
        String typeName = getSchemaType(type);
    
        Element arrayElement = new Element("array");
        while (data.next()) {
            Element component = new Element("component");
            component.addAttribute(new Attribute("xsi:type", 
              "http://www.w3.org/2001/XMLSchema-instance",  
              typeName));
            component.appendChild(convertToXML(data, 2, type));
            arrayElement.appendChild(component);
        }
        return arrayElement;
    }
  
    
    public static String getSchemaType(int type) {
   
        switch (type) {
          case Types.ARRAY:         return "array";
          case Types.BIGINT:        return "xsd:long";
          case Types.BINARY:        return "xsd:hexBinary";
          case Types.BIT:           return "xsd:boolean";
          case Types.BLOB:          return "xsd:hexBinary";
          case Types.CHAR:          return "xsd:string";
          case Types.CLOB:          return "xsd:string";
          case Types.DATE:          return "xsd:date";
          case Types.DECIMAL:       return "xsd:decimal";
          case Types.DOUBLE:        return "xsd:double";
          case Types.FLOAT:         return "xsd:decimal";
          case Types.INTEGER:       return "xsd:int";
          case Types.JAVA_OBJECT:   return "xsd:string";
          case Types.LONGVARBINARY: return "xsd:hexBinary";
          case Types.LONGVARCHAR:   return "xsd:string";
          case Types.NUMERIC:       return "xsd:decimal";
          case Types.REAL:          return "xsd:float";
          case Types.REF:           return "xsd:IDREF";
          case Types.SMALLINT:      return "xsd:short";
          case Types.STRUCT:        return "struct";
          case Types.TIME:          return "xsd:time";
          case Types.TIMESTAMP:     return "xsd:dateTime";
          case Types.TINYINT:       return "xsd:byte";
          case Types.VARBINARY:     return "xsd:hexBinary";
                                    // most general type
          default:                  return "xsd:string"; 
      }
    
    }

    
    public static void main(String[] args) {
    
        if (args.length < 2) {
            System.out.println(
              "Usage: java DatabaseBuilder URL query driverClass");
            return;
        }
        String url = args[0];
        String query = args[1];
        String driverClass = "org.gjt.mm.mysql.Driver"; // MySQL
        if (args.length >= 3) driverClass = args[2];
    
        try {
          // Load JDBC driver
          Class.forName(driverClass).newInstance();
          // Technically, the newInstance() call isn't needed, 
          // but the MM.MySQL documentation suggests this to 
          // "work around some broken JVMs"
        
          DatabaseBuilder builder = new DatabaseBuilder(url);
          Serializer out = new Serializer(System.out);
          
          out.setIndent(2);
          Document doc = builder.build(query);
          out.write(doc);
          out.flush();
      }
      catch (InstantiationException ex) { 
          System.out.println(driverClass + " could not be instantiated");
      }
      catch (ClassNotFoundException ex) { 
          System.out.println(driverClass + " could not be found");
      }
      catch (Exception ex) { // SQL, SAX, and IO
          ex.printStackTrace();
      }
    
  }
  
}