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 org.openconcerto.sql.users.User;

import java.io.IOException;

import java.io.StringReader;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.Timestamp;

import java.util.ArrayList;

import java.util.Collections;

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 importValuesFrom(SQLRowAccessor row) {

        SQLField pk = row.getTable().getKey();

        for (String field : row.getFields()) {

            final SQLField sqlField = row.getTable().getField(field);

            if (!pk.equals(sqlField)) {

                if (sqlField.isForeignKey()) {

                    add(sqlField, row.getForeignIDNumber(field));

                } else {

                    add(sqlField, row.getObject(field));

                }

            }

        }

    }

    public int getFieldsSize() {

        return this.fields.size();

    }

    public List<SQLField> getFields() {

        return Collections.unmodifiableList(this.fields);

    }

    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());

            }

        }

        int index = this.fields.indexOf(field);

        if (index >= 0) {

            throw new IllegalArgumentException(field + " field already in list");

        }

        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 void addCreationTrackedField(User u, SQLTable table) {

        Timestamp now = new Timestamp(System.currentTimeMillis());

        final SQLField creationDateField = table.getCreationDateField();

        final SQLField creationUserField = table.getCreationUserField();

        if (creationDateField != null && creationUserField != null) {

            add(creationDateField, now);

            add(creationUserField, u.getId());

        }

        final SQLField modifDateField = table.getModifDateField();

        final SQLField modifUserField = table.getModifUserField();

        if (modifDateField != null && modifUserField != null) {

            add(modifDateField, now);

            add(modifUserField, u.getId());

        }

    }

    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) {

                if (!javaType.isInstance(value)) {

                    str = SQLRowValues.convert(value.getClass(), value, javaType);

                }

                builder.append(field.getType().toString(str));

            } else {

                builder.append("null");

            }

            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;

                }

            });

        }

    }

}