DbImplMySql.java

package com.enioka.jqm.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DbImplMySql implements DbAdapter
{
    private final static String[] IDS = new String[] { "ID" };

    private Map<String, String> queries = new HashMap<String, String>();

    @Override
    public void prepare(Connection cnx)
    {
        queries.putAll(DbImplBase.queries);
        for (Map.Entry<String, String> entry : DbImplBase.queries.entrySet())
        {
            queries.put(entry.getKey(), this.adaptSql(entry.getValue()));
        }

        // Full rewrite for the most critical query.
        queries.put("ji_update_poll",
                "UPDATE JOB_INSTANCE j1 FORCE INDEX (`PRIMARY`) RIGHT JOIN "
                        + "(SELECT j3.ID FROM JOB_INSTANCE j3 FORCE INDEX(`IDX_JOB_INSTANCE_1`) WHERE j3.STATUS = 'SUBMITTED' AND j3.QUEUE = ? AND "
                        + "(j3.HIGHLANDER = FALSE OR (j3.HIGHLANDER = TRUE AND (SELECT COUNT(1) FROM JOB_INSTANCE j4 FORCE INDEX(`IDX_JOB_INSTANCE_2`) "
                        + "WHERE j4.STATUS IN ('ATTRIBUTED' , 'RUNNING') AND j4.JOBDEF = j3.JOBDEF) = 0)) ORDER BY INTERNAL_POSITION FOR UPDATE LIMIT ?) j2 "
                        + "ON j2.ID = j1.ID SET j1.NODE = ?, j1.STATUS = 'ATTRIBUTED', j1.DATE_ATTRIBUTION = CURRENT_TIMESTAMP(3)");
    }

    @Override
    public String getSqlText(String key)
    {
        return queries.get(key);
    }

    @Override
    public String adaptSql(String sql)
    {
        if (sql.contains("CREATE SEQUENCE"))
        {
            return "";
        }
        return sql.replace("MEMORY TABLE", "TABLE").replace("JQM_PK.nextval", "?").replace(" DOUBLE", " DOUBLE PRECISION")
                .replace("UNIX_MILLIS()", "UNIX_TIMESTAMP()").replace("IN(UNNEST(?))", "IN(?)")
                .replace("CURRENT_TIMESTAMP - 1 MINUTE", "(UNIX_TIMESTAMP() - 60)").replace("FROM (VALUES(0))", "FROM DUAL")
                .replace("DNS||':'||PORT", "CONCAT(DNS, ':', PORT)").replace(" TIMESTAMP ", " TIMESTAMP(3) ")
                .replace("CURRENT_TIMESTAMP", "FFFFFFFFFFFFFFFFF@@@@").replace("FFFFFFFFFFFFFFFFF@@@@", "CURRENT_TIMESTAMP(3)");
    }

    @Override
    public boolean compatibleWith(String product)
    {
        return product.contains("mysql");
    }

    @Override
    public String[] keyRetrievalColumn()
    {
        return IDS;
    }

    @Override
    public List<String> preSchemaCreationScripts()
    {
        List<String> res = new ArrayList<String>();
        res.add("/sql/mysql.sql");
        return res;
    }

    @Override
    public void beforeUpdate(Connection cnx, QueryPreparation q)
    {
        List<Object> params = q.parameters;

        // The main query is very different for MySQL, so we must change the parameter order.
        if (q.isKey("ji_update_poll"))
        {
            Object param0 = params.get(0);
            Object param1 = params.get(1);
            Object param2 = params.get(2);
            params.set(0, param1);
            params.set(1, param2);
            params.set(2, param0);
        }

        // There is no way to do parameterized IN(?) queries with MySQL so we must rewrite these queries as IN(?, ?, ?...)
        // This cannot be done at startup, as the ? count may be different for each call.
        if (q.sqlText.contains("IN(?)"))
        {
            int index = q.sqlText.indexOf("IN(?)");
            int nbIn = 0;
            while (index >= 0)
            {
                System.out.println(index);
                index = q.sqlText.indexOf("IN(?)", index + 1);
                nbIn++;
            }

            int nbList = 0;
            ArrayList<Object> newParams = new ArrayList<Object>(q.parameters.size() + 10);
            for (Object o : q.parameters)
            {
                if (o instanceof List<?>)
                {
                    nbList++;
                    List<?> vv = (List<?>) o;
                    if (vv.size() == 0)
                    {
                        throw new DatabaseException("Cannot do a query whith an empty list parameter");
                    }

                    newParams.addAll(vv);

                    String newPrm[] = new String[vv.size()];
                    for (int j = 0; j < vv.size(); j++)
                    {
                        newPrm[j] = "?";
                    }
                    StringBuilder sb = new StringBuilder();
                    for (int j = 0; j < vv.size(); j++)
                    {
                        sb.append("?,");
                    }
                    q.sqlText = q.sqlText.replaceFirst("IN\\(\\?\\)", "IN(" + sb.substring(0, sb.length() - 1) + ")");
                }
                else
                {
                    newParams.add(o);
                }
            }
            q.parameters = newParams;

            if (nbList != nbIn)
            {
                throw new DatabaseException("Mismatch: count of list parameters and of IN clauses is different.");
            }
        }

        // Manually generate a new ID for INSERT orders. (with one exception - history inserts do not need a generated ID)
        if (!q.sqlText.startsWith("INSERT INTO") || q.queryKey.startsWith("history_insert"))
        {
            return;
        }
        try
        {
            CallableStatement s = cnx.prepareCall("{? = CALL NEXTVAL(?)}");

            s.registerOutParameter(1, Types.INTEGER);
            s.setString(2, "MAIN");
            s.execute();
            int res = s.getInt(1);
            params.add(0, res);
            s.close();
            q.preGeneratedKey = res;
        }
        catch (SQLException e)
        {
            throw new DatabaseException(e);
        }
    }

    @Override
    public void setNullParameter(int position, PreparedStatement s) throws SQLException
    {
        // Absolutely stupid: set to null regardless of type.
        s.setObject(position, null);
    }

    @Override
    public String paginateQuery(String sql, int start, int stopBefore, List<Object> prms)
    {
        int pageSize = stopBefore - start;
        sql = String.format("%s LIMIT ? OFFSET ?", sql);
        prms.add(pageSize);
        prms.add(start);
        return sql;
    }
}