OpenConcerto

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

svn://code.openconcerto.org/openconcerto

Rev

Rev 174 | Details | Compare with Previous | 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
 
177 ilm 16
import org.openconcerto.sql.users.User;
17
 
174 ilm 18
import java.io.IOException;
19
import java.io.StringReader;
20
import java.sql.Connection;
21
import java.sql.SQLException;
177 ilm 22
import java.sql.Timestamp;
174 ilm 23
import java.util.ArrayList;
177 ilm 24
import java.util.Collections;
174 ilm 25
import java.util.HashMap;
26
import java.util.List;
27
import java.util.Map;
28
import java.util.Map.Entry;
29
 
30
import org.postgresql.copy.CopyManager;
31
import org.postgresql.core.BaseConnection;
32
import org.postgresql.jdbc.PgConnection;
33
 
34
public class SQLInsert {
35
    private List<SQLField> fields = new ArrayList<>();
36
    private List<Object> values = new ArrayList<>();
37
    private SQLTable table;
38
 
177 ilm 39
    public void importValuesFrom(SQLRowAccessor row) {
40
        SQLField pk = row.getTable().getKey();
41
        for (String field : row.getFields()) {
42
            final SQLField sqlField = row.getTable().getField(field);
43
            if (!pk.equals(sqlField)) {
44
                if (sqlField.isForeignKey()) {
45
                    add(sqlField, row.getForeignIDNumber(field));
46
                } else {
47
                    add(sqlField, row.getObject(field));
48
                }
49
            }
50
        }
51
    }
52
 
53
    public int getFieldsSize() {
54
        return this.fields.size();
55
    }
56
 
57
    public List<SQLField> getFields() {
58
        return Collections.unmodifiableList(this.fields);
59
    }
60
 
174 ilm 61
    public void add(SQLField field, Object value) {
62
        if (this.table == null) {
63
            this.table = field.getTable();
64
        } else {
65
            if (!this.table.equals(field.getTable())) {
66
                throw new IllegalArgumentException(field + " is not in table " + this.table.toString());
67
            }
68
        }
177 ilm 69
        int index = this.fields.indexOf(field);
70
        if (index >= 0) {
71
            throw new IllegalArgumentException(field + " field already in list");
72
        }
174 ilm 73
        this.fields.add(field);
74
        this.values.add(value);
75
    }
76
 
77
    public void set(SQLField field, Object value) {
78
        if (this.table == null) {
79
            this.table = field.getTable();
80
        } else {
81
            if (!this.table.equals(field.getTable())) {
82
                throw new IllegalArgumentException(field + " is not in table " + this.table.toString());
83
            }
84
        }
85
        int index = this.fields.indexOf(field);
86
        if (index < 0) {
87
            throw new IllegalArgumentException(field + " not in field list");
88
        }
89
        this.values.set(index, value);
90
    }
91
 
177 ilm 92
    public void addCreationTrackedField(User u, SQLTable table) {
93
        Timestamp now = new Timestamp(System.currentTimeMillis());
94
        final SQLField creationDateField = table.getCreationDateField();
95
        final SQLField creationUserField = table.getCreationUserField();
96
        if (creationDateField != null && creationUserField != null) {
97
            add(creationDateField, now);
98
            add(creationUserField, u.getId());
99
        }
100
 
101
        final SQLField modifDateField = table.getModifDateField();
102
        final SQLField modifUserField = table.getModifUserField();
103
        if (modifDateField != null && modifUserField != null) {
104
            add(modifDateField, now);
105
            add(modifUserField, u.getId());
106
        }
107
    }
108
 
174 ilm 109
    public boolean contains(SQLField field) {
110
        return this.fields.indexOf(field) >= 0;
111
    }
112
 
113
    public Object getValue(SQLField field) {
114
        int index = this.fields.indexOf(field);
115
        if (index < 0) {
116
            throw new IllegalArgumentException(field + " not in field list");
117
        }
118
        return this.values.get(index);
119
    }
120
 
121
    public String asString() {
122
        if (this.fields.isEmpty()) {
123
            throw new IllegalStateException("not fields added");
124
        }
125
        final StringBuilder builder = new StringBuilder();
126
        builder.append("INSERT INTO ");
127
        builder.append(this.table.getSQLName());
128
        builder.append(" (");
129
        //
130
        int stop = this.fields.size();
131
        for (int i = 0; i < stop; i++) {
132
            builder.append(this.fields.get(i).getQuotedName());
133
            if (i < stop - 1) {
134
                builder.append(',');
135
            }
136
        }
137
 
138
        builder.append(") VALUES (");
139
 
140
        for (int i = 0; i < stop; i++) {
141
            Object value = this.values.get(i);
142
            final SQLField field = this.fields.get(i);
143
            final Class<?> javaType = field.getType().getJavaType();
144
            Object str = value;
177 ilm 145
 
146
            if (value != null) {
147
                if (!javaType.isInstance(value)) {
148
                    str = SQLRowValues.convert(value.getClass(), value, javaType);
149
                }
150
                builder.append(field.getType().toString(str));
151
            } else {
152
                builder.append("null");
174 ilm 153
            }
154
            if (i < stop - 1) {
155
                builder.append(',');
156
            }
157
        }
158
 
159
        builder.append(")");
160
        return builder.toString();
161
    }
162
 
163
    /**
164
     * Insert multiple rows. Rows can be from different tables.
165
     *
166
     * Speed : 19 000 inserts /s on PostgreSQL
167
     */
168
    public static void executeMultiple(final DBSystemRoot sysRoot, final List<SQLInsert> queries) throws SQLException {
169
        if (queries.isEmpty()) {
170
            throw new IllegalArgumentException("no inserts");
171
        }
172
        final Map<SQLTable, List<SQLInsert>> map = new HashMap<>();
173
        for (final SQLInsert q : queries) {
174
            List<SQLInsert> list = map.get(q.table);
175
            if (list == null) {
176
                list = new ArrayList<>();
177
                map.put(q.table, list);
178
            }
179
            list.add(q);
180
        }
181
        for (Entry<SQLTable, List<SQLInsert>> entry : map.entrySet()) {
182
            executeSimilarInserts(sysRoot, entry.getValue(), false);
183
        }
184
    }
185
 
186
    /**
187
     * Insert multiple rows with a batch. Rows can be from different tables.
188
     *
189
     * Speed : 8 000 inserts /s on PostgreSQL
190
     */
191
    public static long executeMultipleWithBatch(final DBSystemRoot sysRoot, final List<SQLInsert> queries) throws SQLException {
192
        if (queries.isEmpty()) {
193
            throw new IllegalArgumentException("no inserts");
194
        }
195
        final List<String> l = new ArrayList<>(queries.size());
196
        for (final SQLInsert i : queries)
197
            l.add(i.asString());
198
        return sysRoot.getDataSource().executeBatch(l, true).get0();
199
    }
200
 
201
    /**
202
     * Insert multiple rows in the same table, using COPY on PostgreSQL for the maximum speed
203
     *
204
     * Speed : 26 000 inserts /s on PostgreSQL
205
     */
206
    public static long executeSimilarWithCopy(final DBSystemRoot sysRoot, final List<SQLInsert> queries) throws SQLException {
207
        SQLDataSource dataSource = sysRoot.getDataSource();
208
        if (dataSource.getSystem() != SQLSystem.POSTGRESQL) {
209
            return executeSimilarInserts(sysRoot, queries, true).size();
210
        }
211
        if (queries.isEmpty()) {
212
            throw new IllegalArgumentException("no inserts");
213
        }
214
        SQLInsert insert0 = queries.get(0);
215
        final long timeMs = System.currentTimeMillis();
216
        final long time = System.nanoTime();
217
        final long afterCache = time;
218
 
219
        final String query = insert0 + "..." + queries.size() + " queries";
220
 
221
        return dataSource.useConnection(new ConnectionHandlerNoSetup<Long, SQLException>() {
222
            @Override
223
            public Long handle(SQLDataSource ds) throws SQLException {
224
                final Connection conn = ds.getConnection().unwrap(PgConnection.class);
225
                final CopyManager copyManager = new CopyManager((BaseConnection) conn);
226
                final StringBuilder allValues = new StringBuilder(insert0.fields.size() * 10 * queries.size());
227
                final List<String> fi = new ArrayList<>();
228
                for (SQLField f : insert0.fields) {
229
                    fi.add(f.getQuotedName());
230
                }
231
                for (final SQLInsert q : queries) {
232
                    if (!q.table.equals(insert0.table)) {
233
                        throw new IllegalArgumentException("table " + q.table.getName() + " is not " + insert0.table.getTable());
234
                    }
235
                    if (!q.fields.equals(insert0.fields)) {
236
                        throw new IllegalArgumentException("fields" + q.fields + " not equals to " + insert0.fields);
237
                    }
238
                    int stop = q.values.size();
239
                    for (int i = 0; i < stop; i++) {
240
                        Object value = q.values.get(i);
241
                        final SQLField field = q.fields.get(i);
242
                        final Class<?> javaType = field.getType().getJavaType();
243
                        Object str = value;
244
                        if (!javaType.isInstance(value)) {
245
                            str = SQLRowValues.convert(value.getClass(), value, javaType);
246
                        }
247
                        // FIXME null, escapes (see SQLSyntaxPG)
248
                        allValues.append(field.getType().toString(str));
249
                        if (i < stop - 1) {
250
                            allValues.append('\t');
251
                        }
252
                    }
253
                    allValues.append('\n');
254
                }
255
 
256
                final long afterQueryInfo = System.nanoTime();
257
                final long afterExecute;
258
                final long afterHandle;
259
                final long count;
260
                try {
261
                    count = copyManager.copyIn("COPY " + insert0.table.getSQLName() + "(" + String.join(",", fi) + ") FROM STDIN WITH (ENCODING 'UTF-8') ", new StringReader(allValues.toString()));
262
                } catch (IOException e) {
263
                    throw new SQLException(e);
264
                }
265
                afterExecute = System.nanoTime();
266
                afterHandle = afterExecute;
267
                SQLRequestLog.log(query, "multiple similar with copy", conn, timeMs, time, afterCache, afterQueryInfo, afterExecute, afterHandle, System.nanoTime(), queries.size());
268
 
269
                return count;
270
            }
271
        });
272
    }
273
 
274
    /**
275
     * Insert multiple rows in the same table
276
     *
277
     * Speed : 20 000 inserts /s on PostgreSQL
278
     */
279
    public static List<Number> executeSimilarInserts(final DBSystemRoot sysRoot, final List<SQLInsert> queries, boolean returnIds) throws SQLException {
280
        SQLDataSource dataSource = sysRoot.getDataSource();
281
 
282
        if (queries.isEmpty()) {
283
            throw new IllegalArgumentException("no inserts");
284
        }
285
        SQLInsert insert0 = queries.get(0);
286
        final long timeMs = System.currentTimeMillis();
287
        final long time = System.nanoTime();
288
        final long afterCache = time;
289
 
290
        final String query = insert0 + "..." + queries.size() + " queries";
291
        if (sysRoot.getServer().getSQLSystem() != SQLSystem.POSTGRESQL) {
292
            List<Number> res = new ArrayList<>();
293
            for (SQLInsert sqlInsert : queries) {
294
                res.add((Number) sysRoot.getDataSource().executeScalar(sqlInsert.asString()));
295
            }
296
            return res;
297
        } else {
298
            return dataSource.useConnection(new ConnectionHandlerNoSetup<List<Number>, SQLException>() {
299
                @Override
300
                public List<Number> handle(SQLDataSource ds) throws SQLException {
301
                    final Connection conn = ds.getConnection();
302
                    final List<String> fi = new ArrayList<>();
303
                    for (SQLField f : insert0.fields) {
304
                        fi.add(f.getQuotedName());
305
                    }
306
 
307
                    final long afterQueryInfo = System.nanoTime();
308
                    final long afterExecute;
309
                    final long afterHandle;
310
                    final StringBuilder sql = new StringBuilder(insert0.fields.size() * 10 * (queries.size() + 1));
311
                    sql.append("(" + String.join(",", fi) + ")");
312
                    sql.append(" VALUES ");
313
                    int size = queries.size();
314
                    for (int j = 0; j < size; j++) {
315
                        final SQLInsert q = queries.get(j);
316
                        if (!q.table.equals(insert0.table)) {
317
                            throw new IllegalArgumentException("table " + q.table.getName() + " is not " + insert0.table.getTable());
318
                        }
319
                        if (!q.fields.equals(insert0.fields)) {
320
                            throw new IllegalArgumentException("fields" + q.fields + " not equals to " + insert0.fields);
321
                        }
322
                        sql.append("(");
323
                        int stop = q.values.size();
324
                        for (int i = 0; i < stop; i++) {
325
                            Object value = q.values.get(i);
326
                            final SQLField field = q.fields.get(i);
327
                            final Class<?> javaType = field.getType().getJavaType();
328
                            Object str = value;
329
                            if (!field.isNullable() && value == null) {
330
                                throw new IllegalStateException("null value for " + field);
331
                            }
332
                            if (value != null && !javaType.isInstance(value)) {
333
                                str = SQLRowValues.convert(value.getClass(), value, javaType);
334
                            }
335
                            // FIXME : escape ' (quote)
336
                            sql.append(field.getType().toString(str));
337
 
338
                            if (i < stop - 1) {
339
                                sql.append(',');
340
                            }
341
                        }
342
                        sql.append(")");
343
                        if (j < size - 1) {
344
                            sql.append(',');
345
                        }
346
                    }
347
                    List<Number> res;
348
                    if (!returnIds) {
349
                        // TODO return count
350
                        SQLRowValues.insertCount(insert0.table, sql.toString());
351
                        res = null;
352
                    } else {
353
                        res = SQLRowValues.insertIDs(insert0.table, sql.toString());
354
                    }
355
                    afterExecute = System.nanoTime();
356
                    afterHandle = afterExecute;
357
                    SQLRequestLog.log(query, "multiple similar insert", conn, timeMs, time, afterCache, afterQueryInfo, afterExecute, afterHandle, System.nanoTime(), queries.size());
358
 
359
                    return res;
360
                }
361
            });
362
        }
363
    }
364
 
365
}