package mara.mybox.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javafx.scene.paint.Color;
import mara.mybox.data.ColorData;
import static mara.mybox.value.AppVariables.logger;

/**
 * @Author Mara
 * @CreateDate 2019-9-28
 * @License Apache License Version 2.0
 */
public class TableColorData extends DerbyBase {

    public TableColorData() {
        Table_Name = "Color_Data";
        Keys = new ArrayList<>() {
            {
                add("rgba");
            }
        };
        Create_Table_Statement
                = " CREATE TABLE Color_Data ( "
                + "  rgba  VARCHAR(16) NOT NULL, "
                + "  palette_index DOUBLE, "
                + "  color_name VARCHAR(1024) , "
                + "  color_value INT NOT NULL, "
                + "  rgb  VARCHAR(16) NOT NULL, "
                + "  srgb VARCHAR(100)  , "
                + "  hsb VARCHAR(100)  , "
                + "  adobeRGB VARCHAR(100)  , "
                + "  appleRGB VARCHAR(100)  , "
                + "  eciRGB VARCHAR(100)  , "
                + "  sRGBLinear VARCHAR(100)  , "
                + "  adobeRGBLinear VARCHAR(100)  , "
                + "  appleRGBLinear VARCHAR(100)  , "
                + "  calculatedCMYK VARCHAR(100)  , "
                + "  eciCMYK VARCHAR(100)  , "
                + "  adobeCMYK VARCHAR(100)  , "
                + "  xyz VARCHAR(100)  , "
                + "  cieLab VARCHAR(100)  , "
                + "  lchab VARCHAR(100)  , "
                + "  cieLuv VARCHAR(100)  , "
                + "  lchuv VARCHAR(100)  , "
                + "  PRIMARY KEY (rgba)"
                + " )";

    }

    public static int size() {
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            conn.setReadOnly(true);
            String sql = " SELECT count(rgba) FROM Color_Data";
            ResultSet results = conn.createStatement().executeQuery(sql);
            if (results.next()) {
                return results.getInt(1);
            } else {
                return 0;
            }
        } catch (Exception e) {
            failed(e);
            return 0;
        }
    }

    public static List<ColorData> read() {
        List<ColorData> palette = new ArrayList<>();
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login)) {
            conn.setReadOnly(true);
            String sql = " SELECT * FROM Color_Data";
            ResultSet results = conn.createStatement().executeQuery(sql);
            while (results.next()) {
                ColorData data = read(results);
                palette.add(data);
            }
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
        }
        return palette;
    }

    public static List<ColorData> read(int offset, int number) {
        List<ColorData> palette = new ArrayList<>();
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login)) {
            conn.setReadOnly(true);
            String sql = " SELECT * FROM Color_Data OFFSET "
                    + offset + " ROWS FETCH NEXT " + number + " ROWS ONLY";
            ResultSet results = conn.createStatement().executeQuery(sql);
            while (results.next()) {
                ColorData data = read(results);
                palette.add(data);
            }
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
        }
        return palette;
    }

    public static ColorData read(Color color) {
        if (color == null) {
            return null;
        }
        return read(color.toString());
    }

    public static ColorData read(String rgba) {
        if (rgba == null) {
            return null;
        }
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login)) {
            conn.setReadOnly(true);
            String sql = " SELECT * FROM Color_Data WHERE rgba='" + rgba + "'";
            Statement statement = conn.createStatement();
            statement.setMaxRows(1);
            ResultSet results = statement.executeQuery(sql);
            if (results.next()) {
                ColorData data = read(results);
                return data;
            }
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
        }
        return null;
    }

    public static ColorData read(ResultSet results) {
        if (results == null) {
            return null;
        }
        try {
            ColorData data = new ColorData();
            data.setRgba(results.getString("rgba"));
            data.setPaletteIndex(results.getDouble("palette_index"));
            data.setInPalette(data.getPaletteIndex() > -1);
            data.setColorName(results.getString("color_name"));
            data.setColorValue(results.getInt("color_value"));
            data.setRgb(results.getString("rgb"));
            data.setSrgb(results.getString("srgb"));
            data.setHsb(results.getString("hsb"));
            data.setAdobeRGB(results.getString("adobeRGB"));
            data.setAppleRGB(results.getString("appleRGB"));
            data.setEciRGB(results.getString("eciRGB"));
            data.setSRGBLinear(results.getString("sRGBLinear"));
            data.setAdobeRGBLinear(results.getString("adobeRGBLinear"));
            data.setAppleRGBLinear(results.getString("appleRGBLinear"));
            data.setCalculatedCMYK(results.getString("calculatedCMYK"));
            data.setEciCMYK(results.getString("eciCMYK"));
            data.setAdobeCMYK(results.getString("adobeCMYK"));
            data.setXyz(results.getString("xyz"));
            data.setCieLab(results.getString("cieLab"));
            data.setLchab(results.getString("lchab"));
            data.setCieLuv(results.getString("cieLuv"));
            data.setLchuv(results.getString("lchuv"));
            data.bindInPalette();
            return data;
        } catch (Exception e) {
            failed(e);
            logger.debug(e.toString());
            return null;
        }

    }

    public static ColorData write(String rgba, boolean replace) {
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            return write(conn, rgba, null, replace);
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return null;
        }
    }

    public static ColorData write(Color color, boolean replace) {
        try {
            return write(color.toString(), replace);
        } catch (Exception e) {
//            // logger.debug(e.toString());
            return null;
        }
    }

    public static int writeColors(List<Color> colors, boolean replace) {
        if (colors == null || colors.isEmpty()) {
            return -1;
        }
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login)) {
            int count = 0;
            conn.setAutoCommit(false);
            for (Color color : colors) {
                String rgba = color.toString();
                if (write(conn, rgba, null, replace) != null) {
                    count++;
                }
            }
            conn.commit();
            return count;
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return -1;
        }
    }

    public static ColorData write(Connection conn, String rgba, String name, boolean replace) {
        if (conn == null) {
            return null;
        }
        try {
            String sql = " SELECT * FROM Color_Data WHERE rgba='" + rgba + "'";
            boolean exist;
            try ( ResultSet results = conn.createStatement().executeQuery(sql)) {
                exist = results.next();
            }
            if (exist) {
                if (replace) {
                    ColorData data = new ColorData(rgba, name).calculate();
                    update(conn, data);
                    return data;
                } else {
                    return null;
                }
            } else {
                ColorData data = new ColorData(rgba, name).calculate();
                insert(conn, data);
                return data;
            }
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return null;
        }
    }

    public static ColorData write(ColorData data, boolean replace) {
        if (data == null) {
            return null;
        }
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            return write(conn, data, replace);
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return null;
        }
    }

    public static int writeData(List<ColorData> dataList, boolean replace) {
        if (dataList == null) {
            return -1;
        }
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            int count = 0;
            conn.setAutoCommit(false);
            for (ColorData data : dataList) {
                if (write(conn, data, replace) != null) {
                    count++;
                }
            }
            conn.commit();
            return count;
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return -1;
        }
    }

    public static ColorData write(Connection conn, ColorData data,
            boolean replace) {
        if (conn == null || data == null) {
            return null;
        }
        try {
            String sql = " SELECT * FROM Color_Data WHERE rgba='" + data.getRgba() + "'";
            Statement statement = conn.createStatement();
            statement.setMaxRows(1);
            boolean exist;
            try ( ResultSet results = statement.executeQuery(sql)) {
                exist = results.next();
            }
            if (exist) {
                if (replace) {
                    update(conn, data);
                } else {
                    return null;
                }
            } else {
                insert(conn, data);
            }
            return data;
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return null;
        }
    }

    public static boolean insert(Connection conn, ColorData data) {
        if (conn == null || data == null) {
            return false;
        }
        try {
            String name = data.getColorName();
            if (name == null) {
                name = "";
            }
            if (data.getSrgb() == null) {
                data.calculate();
            }
            String sql = "INSERT INTO Color_Data "
                    + "(rgba, palette_index, color_name, color_value, rgb, srgb, hsb, adobeRGB ,appleRGB, eciRGB, "
                    + "sRGBLinear, adobeRGBLinear, appleRGBLinear,  calculatedCMYK,  eciCMYK, adobeCMYK, "
                    + " xyz,  cieLab, lchab, cieLuv,  lchuv ) VALUES ("
                    + " '" + data.getRgba() + "', " + data.getPaletteIndex() + ", '" + stringValue(name) + "', "
                    + data.getColorValue() + ", '" + data.getRgb() + "', "
                    + " '" + data.getSrgb() + "', '" + data.getHsb() + "', "
                    + " '" + data.getAdobeRGB() + "', '" + data.getAppleRGB() + "', '" + data.getEciRGB() + "', "
                    + " '" + data.getSRGBLinear() + "', '" + data.getAdobeRGBLinear() + "', "
                    + " '" + data.getAppleRGBLinear() + "', '" + data.getCalculatedCMYK() + "', "
                    + " '" + data.getEciCMYK() + "', '" + data.getAdobeCMYK() + "', "
                    + " '" + data.getXyz() + "', '" + data.getCieLab() + "', "
                    + " '" + data.getLchab() + "', '" + data.getCieLuv() + "', "
                    + " '" + data.getLchuv() + "' "
                    + " )";
            conn.createStatement().executeUpdate(sql);
            return true;
        } catch (Exception e) {
            failed(e);
            logger.debug(e.toString());
            return false;
        }
    }

    public static boolean update(Connection conn, ColorData data) {
        if (conn == null || data == null) {
            return false;
        }
        try {
            String name = data.getColorName();
            if (data.getSrgb() == null) {
                data.calculate();
            }
            String sql = "UPDATE Color_Data SET "
                    + " palette_index=" + data.getPaletteIndex()
                    + ", " + (name == null ? "" : " color_name='" + stringValue(name) + "', ")
                    + " color_value=" + data.getColorValue() + ", rgb='" + data.getRgb() + "', "
                    + " srgb='" + data.getSrgb() + "', hsb='" + data.getHsb() + "', "
                    + " adobeRGB='" + data.getAdobeRGB() + "', appleRGB='" + data.getAppleRGB() + "', eciRGB='" + data.getEciRGB() + "', "
                    + " sRGBLinear='" + data.getSRGBLinear() + "', adobeRGBLinear='" + data.getAdobeRGBLinear() + "', "
                    + " appleRGBLinear='" + data.getAppleRGBLinear() + "', calculatedCMYK='" + data.getCalculatedCMYK() + "', "
                    + " eciCMYK='" + data.getEciCMYK() + "', adobeCMYK='" + data.getAdobeCMYK() + "', "
                    + " xyz='" + data.getXyz() + "', cieLab='" + data.getCieLab() + "', "
                    + " lchab='" + data.getLchab() + "', cieLuv='" + data.getCieLuv() + "', "
                    + " lchuv='" + data.getLchuv() + "'  "
                    + " WHERE rgba='" + data.getRgba() + "'";
            conn.createStatement().executeUpdate(sql);
            return true;
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return false;
        }
    }

    public static boolean setName(Color color, String name) {
        if (color == null || name == null) {
            return false;
        }
        return setName(color.toString(), name);
    }

    public static boolean setName(String rgba, String name) {
        if (name == null) {
            return false;
        }
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            String sql = " SELECT * FROM Color_Data WHERE rgba='" + rgba + "'";
            Statement statement = conn.createStatement();
            statement.setMaxRows(1);
            ResultSet results = statement.executeQuery(sql);
            if (results.next()) {
                sql = "UPDATE Color_Data SET "
                        + " color_name='" + stringValue(name) + "' "
                        + " WHERE rgba='" + rgba + "'";
                conn.createStatement().executeUpdate(sql);
            } else {
                ColorData data = new ColorData(rgba).calculate();
                data.setColorName(name);
                insert(conn, data);
            }
            return true;
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return false;
        }
    }

    public static List<ColorData> readPalette() {
        List<ColorData> palette = new ArrayList<>();
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login)) {
            conn.setReadOnly(true);
            String sql = " SELECT * FROM Color_Data WHERE palette_index > -1 ORDER BY palette_index ASC";
            ResultSet results = conn.createStatement().executeQuery(sql);
            while (results.next()) {
                ColorData data = read(results);
                palette.add(data);
            }
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
        }
        return palette;
    }

    public static ColorData setPalette(Color color, int palette_index) {
        if (color == null) {
            return null;
        }
        return setPalette(color.toString(), palette_index);
    }

    public static boolean addInPalette(List<ColorData> values) {
        if (values == null || values.isEmpty()) {
            return false;
        }
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            String sql = "SELECT MAX(palette_index) as maxp FROM Color_Data WHERE palette_index > -1";
            double index;
            try ( ResultSet results = conn.createStatement().executeQuery(sql)) {
                index = 1;
                if (results.next()) {
                    try {
                        index = results.getDouble("maxp") + 1;
                    } catch (Exception e) {
                    }
                }
            }
            conn.setAutoCommit(false);
            for (int i = 0; i < values.size(); i++) {
                setPalette(conn, values.get(i).getRgba(), index + i);
            }
            conn.commit();
            trimPalette(conn);
            conn.commit();
            return true;
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return false;
        }
    }

    public static boolean removeFromPalette(Color color) {
        if (color == null) {
            return false;
        }
        return removeFromPalette(color.toString());
    }

    public static boolean removeFromPalette(String rgba) {
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            String sql = "UPDATE Color_Data SET palette_index=-1 WHERE rgba='" + rgba + "'";
            conn.createStatement().executeUpdate(sql);
            return true;
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return false;
        }
    }

    public static boolean removeFromPalette(List<ColorData> values) {
        if (values == null || values.isEmpty()) {
            return false;
        }
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            String inStr = "( '" + values.get(0).getRgba() + "' ";
            for (int i = 1; i < values.size(); ++i) {
                inStr += ", '" + values.get(i).getRgba() + "' ";
            }
            inStr += " )";
            String sql = "UPDATE Color_Data SET palette_index=-1 WHERE rgba IN " + inStr;
            conn.createStatement().executeUpdate(sql);
            conn.setAutoCommit(false);
            trimPalette(conn);
            conn.commit();
            return true;
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return false;
        }
    }

    public static ColorData frontPalette(Color color) {
        if (color == null) {
            return null;
        }
        String rgba = color.toString();
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            String sql = "SELECT MIN(palette_index) as minp FROM Color_Data WHERE palette_index > -1";
            ResultSet results = conn.createStatement().executeQuery(sql);
            if (results.next()) {
                try {
                    double index = results.getDouble("minp");
                    return setPalette(conn, rgba, index - (index + 1) / 100d);
                } catch (Exception e) {
                    return setPalette(conn, rgba, 1);
                }
            } else {
                return setPalette(conn, rgba, 1);
            }
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return null;
        }
    }

    public static ColorData endPalette(Color color) {
        if (color == null) {
            return null;
        }
        String rgba = color.toString();
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            String sql = "SELECT MAX(palette_index) as maxp FROM Color_Data WHERE palette_index > -1";
            ResultSet results = conn.createStatement().executeQuery(sql);
            if (results.next()) {
                try {
                    double index = results.getDouble("maxp") + 1;
                    return setPalette(conn, rgba, index);
                } catch (Exception e) {
                    return setPalette(conn, rgba, 1);
                }
            } else {
                return setPalette(conn, rgba, 1);
            }
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return null;
        }
    }

    public static ColorData setPalette(String rgba, double palette_index) {
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {

            return setPalette(conn, rgba, palette_index);
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return null;
        }
    }

    public static ColorData setPalette(Connection conn, String rgba,
            double palette_index) {
        if (conn == null) {
            return null;
        }
        try {
            String sql = " SELECT * FROM Color_Data WHERE rgba='" + rgba + "'";
            Statement statement = conn.createStatement();
            statement.setMaxRows(1);
            ResultSet results = statement.executeQuery(sql);
            ColorData data;
            if (results.next()) {
                data = read(results);
                data.setPaletteIndex(palette_index);
                update(conn, data);
            } else {
                data = new ColorData(rgba).calculate();
                data.setPaletteIndex(palette_index);
                insert(conn, data);
            }
            return data;
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return null;
        }
    }

    public static ColorData endPalette(ColorData data) {
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            String sql = "SELECT MAX(palette_index) as maxp FROM Color_Data WHERE palette_index > -1";
            ResultSet results = conn.createStatement().executeQuery(sql);
            double index = 1;
            if (results.next()) {
                try {
                    index = results.getDouble("maxp") + 1;
                } catch (Exception e) {
                }
            }
            data.setPaletteIndex(index);
            return write(conn, data, true);
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return null;
        }
    }

    public static ColorData frontPalette(ColorData data) {
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            String sql = "SELECT MIN(palette_index) as minp FROM Color_Data WHERE palette_index > -1";
            double index = 1;
            try ( ResultSet results = conn.createStatement().executeQuery(sql)) {
                if (results.next()) {
                    try {
                        double min = results.getDouble("minp");
                        index = min - (min + 1) / 100d;
                    } catch (Exception e) {
                    }
                }
            }
            data.setPaletteIndex(index);
            return write(conn, data, true);
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return null;
        }
    }

    public static boolean clearPalette() {
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            String sql = "UPDATE Color_Data SET palette_index=-1";
            conn.createStatement().executeUpdate(sql);
            return true;
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return false;
        }
    }

    public static boolean updatePaletteColor(List<Color> colors) {
        if (colors == null || colors.isEmpty()) {
            clearPalette();
            return true;
        }
        List<String> values = new ArrayList<>();
        for (Color color : colors) {
            values.add(color.toString());
        }
        return updatePalette(values);
    }

    public static boolean updatePaletteColorData(List<ColorData> colors) {
        if (colors == null || colors.isEmpty()) {
            clearPalette();
            return true;
        }
        List<String> values = new ArrayList<>();
        for (ColorData data : colors) {
            values.add(data.getRgba());
        }
        return updatePalette(values);
    }

    public static boolean updatePalette(List<String> values) {
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            conn.setAutoCommit(false);
            String sql = "UPDATE Color_Data SET palette_index=-1";
            conn.createStatement().executeUpdate(sql);
            conn.commit();
            if (values != null) {
                for (int i = 0; i < values.size(); ++i) {
                    setPalette(conn, values.get(i), i);
                }
            }
            conn.commit();
            return true;
        } catch (Exception e) {
            failed(e);
            logger.debug(e.toString());
            return false;
        }
    }

    public static boolean trimPalette() {
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            conn.setAutoCommit(false);
            trimPalette(conn);
            conn.commit();
            return true;
        } catch (Exception e) {
            failed(e);
            logger.debug(e.toString());
            return false;
        }
    }

    public static boolean trimPalette(Connection conn) {
        try {
            String sql = " SELECT rgba, palette_index FROM Color_Data WHERE palette_index > -1 ORDER BY palette_index ASC";
            List<String> values;
            try ( ResultSet results = conn.createStatement().executeQuery(sql)) {
                values = new ArrayList<>();
                while (results.next()) {
                    values.add(results.getString("rgba"));
                }
            }
            for (int i = 0; i < values.size(); i++) {
                sql = "UPDATE Color_Data SET palette_index=" + i + " WHERE rgba='" + values.get(i) + "'";
                conn.createStatement().executeUpdate(sql);
            }
            return true;
        } catch (Exception e) {
            failed(e);
            logger.debug(e.toString());
            return false;
        }
    }

    public static boolean delete(String rgba) {
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            String sql = "DELETE FROM Color_Data WHERE rgba='" + rgba + "'";
            conn.createStatement().executeUpdate(sql);
            return true;
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return false;
        }
    }

    public static boolean deleteData(List<ColorData> values) {
        if (values == null || values.isEmpty()) {
            return false;
        }
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            String inStr = "( '" + values.get(0).getRgba() + "' ";
            for (int i = 1; i < values.size(); ++i) {
                inStr += ", '" + values.get(i).getRgba() + "' ";
            }
            inStr += " )";
            String sql = "DELETE FROM Color_Data WHERE rgba IN " + inStr;
            conn.createStatement().executeUpdate(sql);
            return true;
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return false;
        }
    }

    public static boolean delete(List<String> values) {
        if (values == null || values.isEmpty()) {
            return false;
        }
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            String inStr = "( '" + values.get(0) + "' ";
            for (int i = 1; i < values.size(); ++i) {
                inStr += ", '" + values.get(i) + "' ";
            }
            inStr += " )";
            String sql = "DELETE FROM Color_Data WHERE rgba IN " + inStr;
            conn.createStatement().executeUpdate(sql);
            return true;
        } catch (Exception e) {
            failed(e);
//            // logger.debug(e.toString());
            return false;
        }
    }

    public static boolean migrate() {
        try ( Connection conn = DriverManager.getConnection(protocol + dbHome() + login);) {
            String sql = " SELECT * FROM SRGB WHERE palette_index >= 0";
            ResultSet olddata = conn.createStatement().executeQuery(sql);
            List<ColorData> oldData = new ArrayList<>();
            while (olddata.next()) {
                ColorData data = new ColorData(olddata.getString("color_value")).calculate();
                String name = olddata.getString("color_name");
                if (name != null && !name.isEmpty()) {
                    data.setColorName(name);
                }
                data.setPaletteIndex(olddata.getInt("palette_index"));
                oldData.add(data);
            }
            for (ColorData data : oldData) {
                write(conn, data, true);
            }
            sql = "DROP TABLE SRGB";
            conn.createStatement().executeUpdate(sql);
            return true;
        } catch (Exception e) {
            failed(e);
//            logger.debug(e.toString());
            return false;
        }
    }

}