OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
174 ilm 1
/*
2
 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
3
 *
4
 * Copyright 2011 OpenConcerto, by ILM Informatique. All rights reserved.
5
 *
6
 * The contents of this file are subject to the terms of the GNU General Public License Version 3
7
 * only ("GPL"). You may not use this file except in compliance with the License. You can obtain a
8
 * copy of the License at http://www.gnu.org/licenses/gpl-3.0.html See the License for the specific
9
 * language governing permissions and limitations under the License.
10
 *
11
 * When distributing the software, include this License Header Notice in each file.
12
 */
13
 
14
 package org.openconcerto.sql.model;
15
 
16
import java.io.IOException;
17
import java.io.StringReader;
18
import java.sql.Connection;
19
import java.sql.SQLException;
20
import java.util.ArrayList;
21
import java.util.HashMap;
22
import java.util.List;
23
import java.util.Map;
24
import java.util.Map.Entry;
25
 
26
import org.postgresql.copy.CopyManager;
27
import org.postgresql.core.BaseConnection;
28
import org.postgresql.jdbc.PgConnection;
29
 
30
public class SQLInsert {
31
    private List<SQLField> fields = new ArrayList<>();
32
    private List<Object> values = new ArrayList<>();
33
    private SQLTable table;
34
 
35
    public void add(SQLField field, Object value) {
36
        if (this.table == null) {
37
            this.table = field.getTable();
38
        } else {
39
            if (!this.table.equals(field.getTable())) {
40
                throw new IllegalArgumentException(field + " is not in table " + this.table.toString());
41
            }
42
        }
43
        this.fields.add(field);
44
        this.values.add(value);
45
    }
46
 
47
    public void set(SQLField field, Object value) {
48
        if (this.table == null) {
49
            this.table = field.getTable();
50
        } else {
51
            if (!this.table.equals(field.getTable())) {
52
                throw new IllegalArgumentException(field + " is not in table " + this.table.toString());
53
            }
54
        }
55
        int index = this.fields.indexOf(field);
56
        if (index < 0) {
57
            throw new IllegalArgumentException(field + " not in field list");
58
        }
59
        this.values.set(index, value);
60
    }
61
 
62
    public boolean contains(SQLField field) {
63
        return this.fields.indexOf(field) >= 0;
64
    }
65
 
66
    public Object getValue(SQLField field) {
67
        int index = this.fields.indexOf(field);
68
        if (index < 0) {
69
            throw new IllegalArgumentException(field + " not in field list");
70
        }
71
        return this.values.get(index);
72
    }
73
 
74
    public String asString() {
75
        if (this.fields.isEmpty()) {
76
            throw new IllegalStateException("not fields added");
77
        }
78
        final StringBuilder builder = new StringBuilder();
79
        builder.append("INSERT INTO ");
80
        builder.append(this.table.getSQLName());
81
        builder.append(" (");
82
        //
83
        int stop = this.fields.size();
84
        for (int i = 0; i < stop; i++) {
85
            builder.append(this.fields.get(i).getQuotedName());
86
            if (i < stop - 1) {
87
                builder.append(',');
88
            }
89
        }
90
 
91
        builder.append(") VALUES (");
92
 
93
        for (int i = 0; i < stop; i++) {
94
            Object value = this.values.get(i);
95
            final SQLField field = this.fields.get(i);
96
            final Class<?> javaType = field.getType().getJavaType();
97
            Object str = value;
98
            if (value != null && !javaType.isInstance(value)) {
99
                str = SQLRowValues.convert(value.getClass(), value, javaType);
100
            }
101
            builder.append(field.getType().toString(str));
102
 
103
            if (i < stop - 1) {
104
                builder.append(',');
105
            }
106
        }
107
 
108
        builder.append(")");
109
        return builder.toString();
110
    }
111
 
112
    /**
113
     * Insert multiple rows. Rows can be from different tables.
114
     *
115
     * Speed : 19 000 inserts /s on PostgreSQL
116
     */
117
    public static void executeMultiple(final DBSystemRoot sysRoot, final List<SQLInsert> queries) throws SQLException {
118
        if (queries.isEmpty()) {
119
            throw new IllegalArgumentException("no inserts");
120
        }
121
        final Map<SQLTable, List<SQLInsert>> map = new HashMap<>();
122
        for (final SQLInsert q : queries) {
123
            List<SQLInsert> list = map.get(q.table);
124
            if (list == null) {
125
                list = new ArrayList<>();
126
                map.put(q.table, list);
127
            }
128
            list.add(q);
129
        }
130
        for (Entry<SQLTable, List<SQLInsert>> entry : map.entrySet()) {
131
            executeSimilarInserts(sysRoot, entry.getValue(), false);
132
        }
133
    }
134
 
135
    /**
136
     * Insert multiple rows with a batch. Rows can be from different tables.
137
     *
138
     * Speed : 8 000 inserts /s on PostgreSQL
139
     */
140
    public static long executeMultipleWithBatch(final DBSystemRoot sysRoot, final List<SQLInsert> queries) throws SQLException {
141
        if (queries.isEmpty()) {
142
            throw new IllegalArgumentException("no inserts");
143
        }
144
        final List<String> l = new ArrayList<>(queries.size());
145
        for (final SQLInsert i : queries)
146
            l.add(i.asString());
147
        return sysRoot.getDataSource().executeBatch(l, true).get0();
148
    }
149
 
150
    /**
151
     * Insert multiple rows in the same table, using COPY on PostgreSQL for the maximum speed
152
     *
153
     * Speed : 26 000 inserts /s on PostgreSQL
154
     */
155
    public static long executeSimilarWithCopy(final DBSystemRoot sysRoot, final List<SQLInsert> queries) throws SQLException {
156
        SQLDataSource dataSource = sysRoot.getDataSource();
157
        if (dataSource.getSystem() != SQLSystem.POSTGRESQL) {
158
            return executeSimilarInserts(sysRoot, queries, true).size();
159
        }
160
        if (queries.isEmpty()) {
161
            throw new IllegalArgumentException("no inserts");
162
        }
163
        SQLInsert insert0 = queries.get(0);
164
        final long timeMs = System.currentTimeMillis();
165
        final long time = System.nanoTime();
166
        final long afterCache = time;
167
 
168
        final String query = insert0 + "..." + queries.size() + " queries";
169
 
170
        return dataSource.useConnection(new ConnectionHandlerNoSetup<Long, SQLException>() {
171
            @Override
172
            public Long handle(SQLDataSource ds) throws SQLException {
173
                final Connection conn = ds.getConnection().unwrap(PgConnection.class);
174
                final CopyManager copyManager = new CopyManager((BaseConnection) conn);
175
                final StringBuilder allValues = new StringBuilder(insert0.fields.size() * 10 * queries.size());
176
                final List<String> fi = new ArrayList<>();
177
                for (SQLField f : insert0.fields) {
178
                    fi.add(f.getQuotedName());
179
                }
180
                for (final SQLInsert q : queries) {
181
                    if (!q.table.equals(insert0.table)) {
182
                        throw new IllegalArgumentException("table " + q.table.getName() + " is not " + insert0.table.getTable());
183
                    }
184
                    if (!q.fields.equals(insert0.fields)) {
185
                        throw new IllegalArgumentException("fields" + q.fields + " not equals to " + insert0.fields);
186
                    }
187
                    int stop = q.values.size();
188
                    for (int i = 0; i < stop; i++) {
189
                        Object value = q.values.get(i);
190
                        final SQLField field = q.fields.get(i);
191
                        final Class<?> javaType = field.getType().getJavaType();
192
                        Object str = value;
193
                        if (!javaType.isInstance(value)) {
194
                            str = SQLRowValues.convert(value.getClass(), value, javaType);
195
                        }
196
                        // FIXME null, escapes (see SQLSyntaxPG)
197
                        allValues.append(field.getType().toString(str));
198
                        if (i < stop - 1) {
199
                            allValues.append('\t');
200
                        }
201
                    }
202
                    allValues.append('\n');
203
                }
204
 
205
                final long afterQueryInfo = System.nanoTime();
206
                final long afterExecute;
207
                final long afterHandle;
208
                final long count;
209
                try {
210
                    count = copyManager.copyIn("COPY " + insert0.table.getSQLName() + "(" + String.join(",", fi) + ") FROM STDIN WITH (ENCODING 'UTF-8') ", new StringReader(allValues.toString()));
211
                } catch (IOException e) {
212
                    throw new SQLException(e);
213
                }
214
                afterExecute = System.nanoTime();
215
                afterHandle = afterExecute;
216
                SQLRequestLog.log(query, "multiple similar with copy", conn, timeMs, time, afterCache, afterQueryInfo, afterExecute, afterHandle, System.nanoTime(), queries.size());
217
 
218
                return count;
219
            }
220
        });
221
    }
222
 
223
    /**
224
     * Insert multiple rows in the same table
225
     *
226
     * Speed : 20 000 inserts /s on PostgreSQL
227
     */
228
    public static List<Number> executeSimilarInserts(final DBSystemRoot sysRoot, final List<SQLInsert> queries, boolean returnIds) throws SQLException {
229
        SQLDataSource dataSource = sysRoot.getDataSource();
230
 
231
        if (queries.isEmpty()) {
232
            throw new IllegalArgumentException("no inserts");
233
        }
234
        SQLInsert insert0 = queries.get(0);
235
        final long timeMs = System.currentTimeMillis();
236
        final long time = System.nanoTime();
237
        final long afterCache = time;
238
 
239
        final String query = insert0 + "..." + queries.size() + " queries";
240
        if (sysRoot.getServer().getSQLSystem() != SQLSystem.POSTGRESQL) {
241
            List<Number> res = new ArrayList<>();
242
            for (SQLInsert sqlInsert : queries) {
243
                res.add((Number) sysRoot.getDataSource().executeScalar(sqlInsert.asString()));
244
            }
245
            return res;
246
        } else {
247
            return dataSource.useConnection(new ConnectionHandlerNoSetup<List<Number>, SQLException>() {
248
                @Override
249
                public List<Number> handle(SQLDataSource ds) throws SQLException {
250
                    final Connection conn = ds.getConnection();
251
                    final List<String> fi = new ArrayList<>();
252
                    for (SQLField f : insert0.fields) {
253
                        fi.add(f.getQuotedName());
254
                    }
255
 
256
                    final long afterQueryInfo = System.nanoTime();
257
                    final long afterExecute;
258
                    final long afterHandle;
259
                    final StringBuilder sql = new StringBuilder(insert0.fields.size() * 10 * (queries.size() + 1));
260
                    sql.append("(" + String.join(",", fi) + ")");
261
                    sql.append(" VALUES ");
262
                    int size = queries.size();
263
                    for (int j = 0; j < size; j++) {
264
                        final SQLInsert q = queries.get(j);
265
                        if (!q.table.equals(insert0.table)) {
266
                            throw new IllegalArgumentException("table " + q.table.getName() + " is not " + insert0.table.getTable());
267
                        }
268
                        if (!q.fields.equals(insert0.fields)) {
269
                            throw new IllegalArgumentException("fields" + q.fields + " not equals to " + insert0.fields);
270
                        }
271
                        sql.append("(");
272
                        int stop = q.values.size();
273
                        for (int i = 0; i < stop; i++) {
274
                            Object value = q.values.get(i);
275
                            final SQLField field = q.fields.get(i);
276
                            final Class<?> javaType = field.getType().getJavaType();
277
                            Object str = value;
278
                            if (!field.isNullable() && value == null) {
279
                                throw new IllegalStateException("null value for " + field);
280
                            }
281
                            if (value != null && !javaType.isInstance(value)) {
282
                                str = SQLRowValues.convert(value.getClass(), value, javaType);
283
                            }
284
                            // FIXME : escape ' (quote)
285
                            sql.append(field.getType().toString(str));
286
 
287
                            if (i < stop - 1) {
288
                                sql.append(',');
289
                            }
290
                        }
291
                        sql.append(")");
292
                        if (j < size - 1) {
293
                            sql.append(',');
294
                        }
295
                    }
296
                    List<Number> res;
297
                    if (!returnIds) {
298
                        // TODO return count
299
                        SQLRowValues.insertCount(insert0.table, sql.toString());
300
                        res = null;
301
                    } else {
302
                        res = SQLRowValues.insertIDs(insert0.table, sql.toString());
303
                    }
304
                    afterExecute = System.nanoTime();
305
                    afterHandle = afterExecute;
306
                    SQLRequestLog.log(query, "multiple similar insert", conn, timeMs, time, afterCache, afterQueryInfo, afterExecute, afterHandle, System.nanoTime(), queries.size());
307
 
308
                    return res;
309
                }
310
            });
311
        }
312
    }
313
 
314
}