Dépôt officiel du code source de l'ERP OpenConcerto

/*
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
 * 
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
 * 
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
 * language governing permissions and limitations under the License.
 * 
 * When distributing the software, include this License Header Notice in each file.
 */
 
 package org.openconcerto.sql.model;

import java.io.IOException;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
import org.postgresql.jdbc.PgConnection;

public class SQLInsert {
    private List<SQLField> fields = new ArrayList<>();
    private List<Object> values = new ArrayList<>();
    private SQLTable table;

    public void add(SQLField field, Object value) {
        if (this.table == null) {
            this.table = field.getTable();
        } else {
            if (!this.table.equals(field.getTable())) {
                throw new IllegalArgumentException(field + " is not in table " + this.table.toString());
            }
        }
        this.fields.add(field);
        this.values.add(value);
    }

    public void set(SQLField field, Object value) {
        if (this.table == null) {
            this.table = field.getTable();
        } else {
            if (!this.table.equals(field.getTable())) {
                throw new IllegalArgumentException(field + " is not in table " + this.table.toString());
            }
        }
        int index = this.fields.indexOf(field);
        if (index < 0) {
            throw new IllegalArgumentException(field + " not in field list");
        }
        this.values.set(index, value);
    }

    public boolean contains(SQLField field) {
        return this.fields.indexOf(field) >= 0;
    }

    public Object getValue(SQLField field) {
        int index = this.fields.indexOf(field);
        if (index < 0) {
            throw new IllegalArgumentException(field + " not in field list");
        }
        return this.values.get(index);
    }

    public String asString() {
        if (this.fields.isEmpty()) {
            throw new IllegalStateException("not fields added");
        }
        final StringBuilder builder = new StringBuilder();
        builder.append("INSERT INTO ");
        builder.append(this.table.getSQLName());
        builder.append(" (");
        //
        int stop = this.fields.size();
        for (int i = 0; i < stop; i++) {
            builder.append(this.fields.get(i).getQuotedName());
            if (i < stop - 1) {
                builder.append(',');
            }
        }

        builder.append(") VALUES (");

        for (int i = 0; i < stop; i++) {
            Object value = this.values.get(i);
            final SQLField field = this.fields.get(i);
            final Class<?> javaType = field.getType().getJavaType();
            Object str = value;
            if (value != null && !javaType.isInstance(value)) {
                str = SQLRowValues.convert(value.getClass(), value, javaType);
            }
            builder.append(field.getType().toString(str));

            if (i < stop - 1) {
                builder.append(',');
            }
        }

        builder.append(")");
        return builder.toString();
    }

    /**
     * Insert multiple rows. Rows can be from different tables.
     * 
     * Speed : 19 000 inserts /s on PostgreSQL
     */
    public static void executeMultiple(final DBSystemRoot sysRoot, final List<SQLInsert> queries) throws SQLException {
        if (queries.isEmpty()) {
            throw new IllegalArgumentException("no inserts");
        }
        final Map<SQLTable, List<SQLInsert>> map = new HashMap<>();
        for (final SQLInsert q : queries) {
            List<SQLInsert> list = map.get(q.table);
            if (list == null) {
                list = new ArrayList<>();
                map.put(q.table, list);
            }
            list.add(q);
        }
        for (Entry<SQLTable, List<SQLInsert>> entry : map.entrySet()) {
            executeSimilarInserts(sysRoot, entry.getValue(), false);
        }
    }

    /**
     * Insert multiple rows with a batch. Rows can be from different tables.
     * 
     * Speed : 8 000 inserts /s on PostgreSQL
     */
    public static long executeMultipleWithBatch(final DBSystemRoot sysRoot, final List<SQLInsert> queries) throws SQLException {
        if (queries.isEmpty()) {
            throw new IllegalArgumentException("no inserts");
        }
        final List<String> l = new ArrayList<>(queries.size());
        for (final SQLInsert i : queries)
            l.add(i.asString());
        return sysRoot.getDataSource().executeBatch(l, true).get0();
    }

    /**
     * Insert multiple rows in the same table, using COPY on PostgreSQL for the maximum speed
     * 
     * Speed : 26 000 inserts /s on PostgreSQL
     */
    public static long executeSimilarWithCopy(final DBSystemRoot sysRoot, final List<SQLInsert> queries) throws SQLException {
        SQLDataSource dataSource = sysRoot.getDataSource();
        if (dataSource.getSystem() != SQLSystem.POSTGRESQL) {
            return executeSimilarInserts(sysRoot, queries, true).size();
        }
        if (queries.isEmpty()) {
            throw new IllegalArgumentException("no inserts");
        }
        SQLInsert insert0 = queries.get(0);
        final long timeMs = System.currentTimeMillis();
        final long time = System.nanoTime();
        final long afterCache = time;

        final String query = insert0 + "..." + queries.size() + " queries";

        return dataSource.useConnection(new ConnectionHandlerNoSetup<Long, SQLException>() {
            @Override
            public Long handle(SQLDataSource ds) throws SQLException {
                final Connection conn = ds.getConnection().unwrap(PgConnection.class);
                final CopyManager copyManager = new CopyManager((BaseConnection) conn);
                final StringBuilder allValues = new StringBuilder(insert0.fields.size() * 10 * queries.size());
                final List<String> fi = new ArrayList<>();
                for (SQLField f : insert0.fields) {
                    fi.add(f.getQuotedName());
                }
                for (final SQLInsert q : queries) {
                    if (!q.table.equals(insert0.table)) {
                        throw new IllegalArgumentException("table " + q.table.getName() + " is not " + insert0.table.getTable());
                    }
                    if (!q.fields.equals(insert0.fields)) {
                        throw new IllegalArgumentException("fields" + q.fields + " not equals to " + insert0.fields);
                    }
                    int stop = q.values.size();
                    for (int i = 0; i < stop; i++) {
                        Object value = q.values.get(i);
                        final SQLField field = q.fields.get(i);
                        final Class<?> javaType = field.getType().getJavaType();
                        Object str = value;
                        if (!javaType.isInstance(value)) {
                            str = SQLRowValues.convert(value.getClass(), value, javaType);
                        }
                        // FIXME null, escapes (see SQLSyntaxPG)
                        allValues.append(field.getType().toString(str));
                        if (i < stop - 1) {
                            allValues.append('\t');
                        }
                    }
                    allValues.append('\n');
                }

                final long afterQueryInfo = System.nanoTime();
                final long afterExecute;
                final long afterHandle;
                final long count;
                try {
                    count = copyManager.copyIn("COPY " + insert0.table.getSQLName() + "(" + String.join(",", fi) + ") FROM STDIN WITH (ENCODING 'UTF-8') ", new StringReader(allValues.toString()));
                } catch (IOException e) {
                    throw new SQLException(e);
                }
                afterExecute = System.nanoTime();
                afterHandle = afterExecute;
                SQLRequestLog.log(query, "multiple similar with copy", conn, timeMs, time, afterCache, afterQueryInfo, afterExecute, afterHandle, System.nanoTime(), queries.size());

                return count;
            }
        });
    }

    /**
     * Insert multiple rows in the same table
     * 
     * Speed : 20 000 inserts /s on PostgreSQL
     */
    public static List<Number> executeSimilarInserts(final DBSystemRoot sysRoot, final List<SQLInsert> queries, boolean returnIds) throws SQLException {
        SQLDataSource dataSource = sysRoot.getDataSource();

        if (queries.isEmpty()) {
            throw new IllegalArgumentException("no inserts");
        }
        SQLInsert insert0 = queries.get(0);
        final long timeMs = System.currentTimeMillis();
        final long time = System.nanoTime();
        final long afterCache = time;

        final String query = insert0 + "..." + queries.size() + " queries";
        if (sysRoot.getServer().getSQLSystem() != SQLSystem.POSTGRESQL) {
            List<Number> res = new ArrayList<>();
            for (SQLInsert sqlInsert : queries) {
                res.add((Number) sysRoot.getDataSource().executeScalar(sqlInsert.asString()));
            }
            return res;
        } else {
            return dataSource.useConnection(new ConnectionHandlerNoSetup<List<Number>, SQLException>() {
                @Override
                public List<Number> handle(SQLDataSource ds) throws SQLException {
                    final Connection conn = ds.getConnection();
                    final List<String> fi = new ArrayList<>();
                    for (SQLField f : insert0.fields) {
                        fi.add(f.getQuotedName());
                    }

                    final long afterQueryInfo = System.nanoTime();
                    final long afterExecute;
                    final long afterHandle;
                    final StringBuilder sql = new StringBuilder(insert0.fields.size() * 10 * (queries.size() + 1));
                    sql.append("(" + String.join(",", fi) + ")");
                    sql.append(" VALUES ");
                    int size = queries.size();
                    for (int j = 0; j < size; j++) {
                        final SQLInsert q = queries.get(j);
                        if (!q.table.equals(insert0.table)) {
                            throw new IllegalArgumentException("table " + q.table.getName() + " is not " + insert0.table.getTable());
                        }
                        if (!q.fields.equals(insert0.fields)) {
                            throw new IllegalArgumentException("fields" + q.fields + " not equals to " + insert0.fields);
                        }
                        sql.append("(");
                        int stop = q.values.size();
                        for (int i = 0; i < stop; i++) {
                            Object value = q.values.get(i);
                            final SQLField field = q.fields.get(i);
                            final Class<?> javaType = field.getType().getJavaType();
                            Object str = value;
                            if (!field.isNullable() && value == null) {
                                throw new IllegalStateException("null value for " + field);
                            }
                            if (value != null && !javaType.isInstance(value)) {
                                str = SQLRowValues.convert(value.getClass(), value, javaType);
                            }
                            // FIXME : escape ' (quote)
                            sql.append(field.getType().toString(str));

                            if (i < stop - 1) {
                                sql.append(',');
                            }
                        }
                        sql.append(")");
                        if (j < size - 1) {
                            sql.append(',');
                        }
                    }
                    List<Number> res;
                    if (!returnIds) {
                        // TODO return count
                        SQLRowValues.insertCount(insert0.table, sql.toString());
                        res = null;
                    } else {
                        res = SQLRowValues.insertIDs(insert0.table, sql.toString());
                    }
                    afterExecute = System.nanoTime();
                    afterHandle = afterExecute;
                    SQLRequestLog.log(query, "multiple similar insert", conn, timeMs, time, afterCache, afterQueryInfo, afterExecute, afterHandle, System.nanoTime(), queries.size());

                    return res;
                }
            });
        }
    }

}