FWD » Core Development » Database

/**

 * 

 */

package sandbox;

import java.io.*;

import java.nio.charset.*;

import java.sql.*;

import java.text.*;

import java.util.*;

/**

 * A program which generates the first 255 characters associated with an input character set, converts them

 * to the JVM's internal character set (i.e., UTF-16), and inserts them into a UTF8 database. From there,

 * they are queried and output in the database's collation order.

 * <p>

 * Supported databases are PostgreSQL and H2.

 * <p>

 * Some prerequisite setup is needed for this program to work with PostgreSQL:

 * <ol>

 * <li>Use the default (typically {@code main}) cluster. Assumptions:

 * <ul>

 * <li>listens on default port 5432;

 * <li>uses default UTF8 encoding and picks up its locale from a UTF8 OS locale (e.g., {@code en_US.UTF8};

 * </ul>

 * <li>connect to the cluster as a superuser (e.g., {@code postgres});

 * <li>create a {@code fwd} role: {@code create role fwd with login password 'fwd'};

 * <li>create a {@code fwd} database: {@code create database fwd}.

 * <li>install {@code citext} data type support: {@code create extension citext} (while connected to the

 *     {@code fwd} database);

 * </ol>

 * <p>

 * There are no specific prerequisites for H2; a transient, in-memory database is used.

 */

public class CollationTest

{

   /** Insert statement */

   private static final String insert =

      "insert into tt1(codepoint_ori, encoded_cs, encoded_ci) values (?, ?, ?)";

   /** Query for case-insensitive collation */

   private static final String qci =

      "select codepoint_ori, ascii(encoded_ci), encoded_ci " +

      "from tt1 " +

      "order by encoded_ci, ascii(encoded_ci) %s";

   /** Query for case-sensitive collation */

   private static final String qcs =

      "select codepoint_ori, ascii(encoded_cs), encoded_cs " +

      "from tt1 " +

      "order by encoded_cs, ascii(encoded_cs) %s";

   /** Result set output header text */

   private static final String rsHdr =

      " codepoint src | codepoint tgt | hex codepoint | character ";

   /** Result set output header separator */

   private static final String rsSep =

      "---------------+---------------+---------------+-----------";

   /** Result set row output spec */

   private static final String rsRow =

      "           %3d |          %4d |          %4X | %s         ";

   /** Name of original character set */

   private static String originalEncoding = null;

   /** Listening port (PostgreSQL only) */

   private static String port = "5432";

   /** JDBC URL */

   private static String jdbcURL = null;

   /** Collation */

   private static String collation = null;

   /**

    * Print program syntax with optional error message.

    * <p>

    * Expected syntax is:

    * <pre>

    * CollationTest <charset name> <database type>

    * 

    * where...

    *    <charset name> is the name or alias of an available character set

    *    <database type> is 1 for PostgreSQL; 2 for H2

    *    <collation> (optional, only valid for H2) collation descriptor

    * </pre>

    * 

    * @param   message

    *          Optional error message; may by {@code null}.

    */

   private static void syntax(String message)

   {

      if (message != null)

      {

         System.err.println("ERROR: " + message);

         System.err.println();

      }

      System.err.println("Syntax: java CollationTest <charset name> { 1 [<port>] | 2 [<collation>] }");

      System.err.println();

      System.err.println("where...");

      System.err.println("   <charset name> is the name or alias of an available character set");

      System.err.println("   1 for PostgreSQL; 2 for H2");

      System.err.println("   <port> (optional, only valid for PostgreSQL) listening port; defaults to 5432");

      System.err.println("   <collation> (optional, only valid for H2) collation descriptor");

      System.err.println();

      System.err.println("Available charset names [aliases]:");

      System.err.println("----------------------------------");

      for (String name : Charset.availableCharsets().keySet())

      {

         System.err.print(name);

         System.err.println(" " + Charset.forName(name).aliases());

      }

   }

   /**

    * Convert a short value to the smallest number of bytes, omitting the high order byte if it is zero.

    * 

    * @param   i

    *          An integer whose value uses no more than two bytes.

    * 

    * @return  Array of one or two bytes, representing the binary value of the given input value, high order

    *          byte first.

    */

   private static byte[] shortToBytes(int i)

   {

      byte high = (byte) (i >> 8);

      byte low = (byte) i;

      return high > 0 ? new byte[] { high, low } : new byte[] { low };

   }

   /**

    * Return an array of the first 255 encoded characters of the given character set, where each encoded

    * character has been converted to a Unicode string.

    * 

    * @param   charset

    *          A valid charset name or alias.

    * 

    * @return  First 255 characters of the given character set, as Unicode strings.

    */

   private static String[] first255Encoded(Charset charset)

   {

      int len = 255;

      String[] encoded = new String[len];

      for (int i = 1; i <= len; i++)

      {

         byte[] ba = shortToBytes(i);

         String s = new String(ba, 0, ba.length, charset);

         encoded[i - 1] = s;

//         System.out.println("codepoint: " + i + "; encoded string: '" + s + "'");

      }

      return encoded;

   }

   /**

    * Perform the necessary setup for the PostgreSQL database.

    * 

    * @param   stmt

    *          SQL statement.

    * @param   newCollation

    *          Ignored; {@code lc_collate} setting must be set at cluster initialization and cannot be

    *          changed thereafter.

    * 

    * @throws  SQLException

    *          if an error is encountered executing a SQL statement.

    */

   private static void setupPostgreSQL(Statement stmt, String newCollation)

   throws SQLException

   {

      try (ResultSet rs = stmt.executeQuery("select datcollate from pg_database where datname = 'fwd'"))

      {

         if (rs.next())

         {

            collation = rs.getString(1);

         }

      }

      if (newCollation != null)

      {

         System.err.println("Collation parameter '" + newCollation + "' cannot be set; parameter ignored");

      }

      stmt.execute("create temporary table tt1 (codepoint_ori int, encoded_cs text, encoded_ci citext)");

   }

   /**

    * Perform the necessary setup for the H2 database.

    * 

    * @param   stmt

    *          SQL statement.

    * 

    * @throws  SQLException

    *          if an error is encountered executing a SQL statement.

    */

   private static void setupH2(Statement stmt, String newCollation)

   throws SQLException

   {

      if (newCollation != null)

      {

         stmt.execute("set collation " + newCollation);

         collation = newCollation;

      }

      stmt.execute("create temporary table tt1 " +

                   "(codepoint_ori int, encoded_cs varchar, encoded_ci varchar_ignorecase)");

   }

   /**

    * Generate collated query output to file. The file name is composed of the distinctive elements of the

    * query:

    * <ul>

    * <li>the database type

    * <li>the original character encoding

    * <li>the case-sensitivity of the collation

    * <li>today's date

    * <li>the direction applied to the secondary sort by UTF codepoint (1 for asc, 2 for desc)

    * </ul>

    * 

    * @param   stmt

    *          SQL statement

    * @param   caseSensitive

    *          {@code true} for case-sensitive collation, {@code false} for case-insensitive.

    * @param   ascending

    *          Direction of the secondary sort {@code true} for ascending, {@code false} for descending.

    * 

    * @throws  SQLException

    *          if an error occurs executing the query or fetching the results.

    */

   private static void generateOutput(Statement stmt, boolean caseSensitive, boolean ascending)

   throws SQLException,

          IOException

   {

      int begin = jdbcURL.indexOf(':') + 1;

      int end = jdbcURL.indexOf(':', begin);

      String db = jdbcURL.substring(begin, end);

      SimpleDateFormat format = new SimpleDateFormat("yyyyMMdd");

      String date = format.format(Calendar.getInstance().getTime());

      String enc = originalEncoding.toLowerCase();

      String coll = collation == null ? "no_collation" : collation.replace(' ', '_');

      int sort = ascending ? 1 : 2;

      String fileSpec = "%s_%s_to_utf_case-%ssensitive_%s_%s_%d.txt";

      String file = String.format(fileSpec, db, enc, caseSensitive ? "" : "in", coll, date, sort);

      try (PrintStream ps = new PrintStream(new FileOutputStream(file)))

      {

         ps.println("Database         : " + jdbcURL);

         ps.println("Case-sensitive   : " + caseSensitive);

         ps.println("Original Encoding: " + originalEncoding);

         ps.println("Collation        : " + collation);

         ps.println();

         String query = caseSensitive ? qcs : qci;

         ResultSet rs = stmt.executeQuery(String.format(query, ascending ? "asc" : "desc"));

         ps.println(rsHdr);

         ps.println(rsSep);

         while (rs.next())

         {

            int cpori = rs.getInt(1);

            int cputf = rs.getInt(2);

            String ch = rs.getString(3);

            ps.println(String.format(rsRow, cpori, cputf, cputf, ch));

         }

      }

   }

   /**

    * Command line entry point; expects a character set name/alias and a database type to indicate which

    * database to use.

    * 

    * @see #syntax(String)

    */

   public static void main(String[] args)

   {

      int numArgs = args.length;

      if (numArgs < 2)

      {

         syntax("Incorrect argument(s)");

         return;

      }

      String coll = collation;

      // required argument: charset name

      originalEncoding = args[0];

      SQLFunc setup = null;

      try

      {

         switch (args[1])

         {

            case "1":

               if (numArgs == 3)

               {

                  port = args[2];

               }

               jdbcURL = String.format("jdbc:postgresql://localhost:%s/fwd", port);

               setup = CollationTest::setupPostgreSQL;

               break;

            case "2":

               if (numArgs == 3)

               {

                  coll = args[2];

               }

               jdbcURL = "jdbc:h2:mem:fwd";

               setup = CollationTest::setupH2;

               break;

            default:

               throw new IllegalArgumentException();

         }

      }

      catch (Exception exc)

      {

         syntax("Invalid database type");

         return;

      }

      Charset charset;

      try

      {

         charset = Charset.forName(originalEncoding);

         originalEncoding = charset.displayName();

      }

      catch (IllegalArgumentException exc)

      {

         syntax("Invalid charset name: " + exc.getMessage());

         return;

      }

      String[] encoded = first255Encoded(charset);

      int len = encoded.length;

      try (Connection conn = DriverManager.getConnection(jdbcURL, "fwd", "fwd"))

      {

         Statement stmt = conn.createStatement();

         setup.execute(stmt, coll);

         PreparedStatement ps = conn.prepareStatement(insert);

         for (int i = 1; i <= len; i++)

         {

            String s = encoded[i - 1];

            ps.setInt(1, i);

            ps.setString(2, s);

            ps.setString(3, s);

            ps.addBatch();

         }

         ps.executeBatch();

         ps.close();

         // case-sensitive sort

         generateOutput(stmt, true, true);

         generateOutput(stmt, true, false);

         // case-insensitive sort

         generateOutput(stmt, false, true);

         generateOutput(stmt, false, false);

      }

      catch (SQLException | IOException exc)

      {

         exc.printStackTrace();

      }

   }

   /**

    * Functional interface which accepts a SQL statement and possibly throws {@code SQLException}.

    */

   private static interface SQLFunc

   {

      void execute(Statement stmt, String newCollation)

      throws SQLException;

   }

}