1 package net.ucanaccess.converters;
2
3 import io.github.spannm.jackcess.*;
4 import io.github.spannm.jackcess.Database.FileFormat;
5 import io.github.spannm.jackcess.PropertyMap.Property;
6 import io.github.spannm.jackcess.complex.ComplexValueForeignKey;
7 import io.github.spannm.jackcess.impl.ColumnImpl;
8 import io.github.spannm.jackcess.impl.ColumnImpl.AutoNumberGenerator;
9 import io.github.spannm.jackcess.impl.IndexData;
10 import io.github.spannm.jackcess.impl.IndexImpl;
11 import io.github.spannm.jackcess.impl.query.QueryFormat;
12 import io.github.spannm.jackcess.impl.query.QueryImpl;
13 import io.github.spannm.jackcess.query.Query;
14 import net.ucanaccess.complex.ComplexBase;
15 import net.ucanaccess.converters.TypesMap.AccessType;
16 import net.ucanaccess.exception.UcanaccessSQLException;
17 import net.ucanaccess.ext.FunctionType;
18 import net.ucanaccess.jdbc.BlobKey;
19 import net.ucanaccess.jdbc.DBReference;
20 import net.ucanaccess.triggers.*;
21 import net.ucanaccess.type.ObjectType;
22 import net.ucanaccess.util.Try;
23 import org.hsqldb.error.ErrorCode;
24
25 import java.io.IOException;
26 import java.lang.System.Logger;
27 import java.lang.System.Logger.Level;
28 import java.lang.annotation.Annotation;
29 import java.lang.reflect.Method;
30 import java.math.BigDecimal;
31 import java.sql.*;
32 import java.time.LocalDateTime;
33 import java.time.ZoneId;
34 import java.util.*;
35 import java.util.Date;
36 import java.util.concurrent.atomic.AtomicInteger;
37 import java.util.regex.Matcher;
38 import java.util.regex.Pattern;
39 import java.util.stream.Collectors;
40 import java.util.stream.IntStream;
41 import java.util.stream.Stream;
42
43 @SuppressWarnings({"java:S1192", "java:S2692"})
44 public class LoadJet {
45 private static final AtomicInteger NAMING_COUNTER = new AtomicInteger(0);
46
47 private final Logger logger;
48 private final Connection conn;
49 private final Database dbIO;
50 private boolean err;
51 private final FunctionsLoader functionsLoader = new FunctionsLoader();
52 private final List<String> loadedIndexes = new ArrayList<>();
53 private final List<String> loadedQueries = new ArrayList<>();
54 private final List<String> loadedProcedures = new ArrayList<>();
55 private final List<String> loadedTables = new ArrayList<>();
56 private final TablesLoader tablesLoader = new TablesLoader();
57 private final TriggersLoader triggersGenerator = new TriggersLoader();
58 private final ViewsLoader viewsLoader = new ViewsLoader();
59 private boolean sysSchema;
60 private boolean ff1997;
61 private boolean skipIndexes;
62 private final Metadata metadata;
63
64 public LoadJet(Connection _conn, Database _dbIo) {
65 logger = System.getLogger(getClass().getName());
66 conn = _conn;
67 dbIO = _dbIo;
68 try {
69 ff1997 = FileFormat.V1997.equals(dbIO.getFileFormat());
70 } catch (Exception _ignored) {
71 logger.log(Level.WARNING, _ignored.getMessage());
72 }
73 metadata = new Metadata(_conn);
74 }
75
76 public void loadDefaultValues(Table _t) throws SQLException, IOException {
77 tablesLoader.addTriggersColumnDefault(_t);
78 }
79
80 public void loadDefaultValues(Column _cl) throws SQLException, IOException {
81 tablesLoader.addTriggerColumnDefault(_cl);
82 }
83
84 public String defaultValue4SQL(Column _cl) throws IOException {
85 Object defVal = _cl.getProperties().getValue(PropertyMap.DEFAULT_VALUE_PROP);
86 return defVal == null ? null : tablesLoader.defaultValue4SQL(defVal, _cl.getType());
87 }
88
89 private static boolean hasAutoNumberColumn(Table t) {
90 return t.getColumns().stream().anyMatch(col -> col.isAutoNumber() || DataType.BOOLEAN.equals(col.getType()));
91 }
92
93 public void addFunctions(Class<?> _clazz) {
94 functionsLoader.addFunctions(_clazz, false);
95 }
96
97 private void exec(String _expression, boolean _logging) throws SQLException {
98 try (Statement st = conn.createStatement()) {
99 if (_logging) {
100 logger.log(Level.DEBUG, "Executing {0}", _expression);
101 }
102 st.executeUpdate(_expression);
103 } catch (SQLException _ex) {
104 if (_logging && _ex.getErrorCode() != TablesLoader.HSQL_FK_ALREADY_EXISTS) {
105 logger.log(Level.WARNING, "Cannot execute {0}: {1}", _expression, _ex.getMessage());
106 }
107 throw _ex;
108 }
109 }
110
111 private String escapeIdentifier(String tn) {
112 return SQLConverter.escapeIdentifier(tn, conn);
113 }
114
115 public SQLWarning getLoadingWarnings() {
116 if (viewsLoader.notLoaded.isEmpty() && tablesLoader.unresolvedTables.isEmpty()) {
117 return null;
118 }
119 SQLWarning sqlw = null;
120 for (String s : viewsLoader.notLoaded.keySet()) {
121 String message = s.isEmpty() ? "Cannot load views" : "Cannot load view " + s + ' ' + viewsLoader.notLoaded.get(s);
122 if (sqlw == null) {
123 sqlw = new SQLWarning(message);
124 } else {
125 sqlw.setNextWarning(new SQLWarning(message));
126 }
127 }
128 for (String s : viewsLoader.notLoadedProcedure.keySet()) {
129 String message = s.isEmpty() ? "Cannot load procedures" : "Cannot load procedure " + s + ' ' + viewsLoader.notLoadedProcedure.get(s);
130 if (sqlw == null) {
131 sqlw = new SQLWarning(message);
132 } else {
133 sqlw.setNextWarning(new SQLWarning(message));
134 }
135 }
136 for (String s : tablesLoader.unresolvedTables) {
137 String message = "Cannot resolve table " + s;
138 if (sqlw == null) {
139 sqlw = new SQLWarning(message);
140 } else {
141 sqlw.setNextWarning(new SQLWarning(message));
142 }
143 }
144 return sqlw;
145 }
146
147 public void resetFunctionsDefault() {
148 functionsLoader.resetDefault();
149 }
150
151 @SuppressWarnings("PMD.UseTryWithResources")
152 public void loadDB() throws SQLException, IOException {
153 try {
154 functionsLoader.loadMappedFunctions();
155 tablesLoader.loadTables();
156 viewsLoader.loadViews();
157 conn.commit();
158 SQLConverter.cleanEscaped();
159 } finally {
160 logger.log(Level.DEBUG, "Loaded tables: {0}", loadedTables);
161 logger.log(Level.DEBUG, "Loaded queries: {0}", loadedQueries);
162 logger.log(Level.DEBUG, "Loaded procedures: {0}", loadedProcedures);
163 logger.log(Level.DEBUG, "Loaded indexes: {0}", loadedIndexes);
164 conn.close();
165 }
166 }
167
168 public void synchronisationTriggers(String tableName, boolean hasAutoNumberColumn, boolean hasAppendOnly)
169 throws SQLException {
170 triggersGenerator.synchronisationTriggers(tableName, hasAutoNumberColumn, hasAppendOnly);
171 }
172
173 public Object tryDefault(Object _default) {
174 try (Statement st = conn.createStatement()) {
175 @SuppressWarnings("java:S2077")
176 ResultSet rs = st.executeQuery(String.format("SELECT %s FROM DUAL", _default));
177 if (rs.next()) {
178 return rs.getObject(1);
179 }
180 return null;
181 } catch (Exception _ex) {
182 return null;
183 }
184 }
185
186 public void setSysSchema(boolean _sysSchema) {
187 sysSchema = _sysSchema;
188 }
189
190 public void setSkipIndexes(boolean _skipIndexes) {
191 skipIndexes = _skipIndexes;
192 }
193
194 private final class FunctionsLoader {
195
196 private final Set<String> functionDefinitions = new LinkedHashSet<>();
197
198 private void addAggregates() {
199 functionDefinitions.addAll(List.of(
200 getAggregate("last", "LONGVARCHAR"),
201 getAggregate("last", "DECIMAL(100,10)"),
202 getAggregate("last", "BOOLEAN"),
203 getAggregate("first", "LONGVARCHAR"),
204 getAggregate("first", "DECIMAL(100,10)"),
205 getAggregate("first", "BOOLEAN"),
206 getLastTimestamp(),
207 getFirstTimestamp()));
208 }
209
210 private String getLastTimestamp() {
211 return "CREATE AGGREGATE FUNCTION last(IN val TIMESTAMP, IN flag boolean, INOUT ts TIMESTAMP, INOUT counter INT) "
212 + "RETURNS TIMESTAMP CONTAINS SQL BEGIN ATOMIC IF flag THEN RETURN ts; "
213 + "ELSE IF counter IS NULL THEN SET counter = 0; END IF; SET counter = counter + 1; "
214 + "SET ts = val; RETURN NULL; END IF; END";
215 }
216
217 private String getFirstTimestamp() {
218 return "CREATE AGGREGATE FUNCTION First(IN val TIMESTAMP, IN flag boolean, INOUT ts TIMESTAMP , INOUT counter INT) "
219 + "RETURNS TIMESTAMP CONTAINS SQL BEGIN ATOMIC IF flag THEN RETURN ts; "
220 + "ELSE IF counter IS NULL THEN SET counter = 0; END IF; SET counter = counter + 1; "
221 + "IF counter = 1 THEN SET ts = val; END IF; RETURN NULL; END IF; END";
222 }
223
224 private void addFunction(String _functionName, String _javaMethodName, String _returnType, String... _paramTypes) {
225 StringBuilder code = new StringBuilder();
226 if (DBReference.is2xx()) {
227 String parms = IntStream.rangeClosed(1, _paramTypes.length).mapToObj(i -> "par" + i + ' ' + _paramTypes[i - 1]).collect(Collectors.joining(", "));
228 code.append("CREATE FUNCTION ").append(_functionName)
229 .append('(').append(parms).append(')')
230 .append(" RETURNS ").append(_returnType)
231 .append(" LANGUAGE JAVA DETERMINISTIC NO SQL EXTERNAL NAME ")
232 .append("'CLASSPATH:").append(_javaMethodName).append("'");
233 } else {
234 code.append("CREATE ALIAS ")
235 .append(_functionName)
236 .append(" FOR \"").append(_javaMethodName).append("\"");
237 }
238 functionDefinitions.add(code.toString());
239 }
240
241 private void addFunctions(Class<?> _clazz, boolean _cswitch) {
242 Map<String, String> tmap = TypesMap.getAccess2HsqlTypesMap();
243
244 for (Method method : _clazz.getDeclaredMethods()) {
245
246 List<FunctionType> functionTypes = Stream.of(method.getAnnotations())
247 .filter(ant -> ant.annotationType().equals(FunctionType.class))
248 .map(FunctionType.class::cast)
249 .collect(Collectors.toList());
250
251 for (FunctionType func : functionTypes) {
252 String methodName = _clazz.getName() + '.' + method.getName();
253 String functionName = Objects.requireNonNullElse(func.functionName(), methodName);
254 AccessType[] acts = func.argumentTypes();
255 AccessType ret = func.returnType();
256 String retTypeName = ret.name();
257 String returnType = tmap.getOrDefault(retTypeName, retTypeName);
258 if (AccessType.TEXT.equals(ret)) {
259 returnType += "(255)";
260 }
261 String[] args = new String[acts.length];
262 for (int i = 0; i < args.length; i++) {
263 String typeName = acts[i].name();
264 args[i] = tmap.getOrDefault(typeName, typeName);
265 if (AccessType.TEXT.equals(acts[i])) {
266 args[i] += "(255)";
267 }
268 }
269 if (func.namingConflict()) {
270 SQLConverter.addWAFunctionName(functionName);
271 functionName += "WA";
272 }
273 addFunction(functionName, methodName, returnType, args);
274 }
275
276 }
277 createFunctions();
278 if (_cswitch) {
279 createSwitch();
280 }
281 }
282
283 private void resetDefault() {
284 Class<?> clazz = Functions.class;
285 Method[] mths = clazz.getDeclaredMethods();
286 for (Method mth : mths) {
287 Annotation[] ants = mth.getAnnotations();
288 for (Annotation ant : ants) {
289 if (ant.annotationType().equals(FunctionType.class)) {
290 FunctionType ft = (FunctionType) ant;
291 String functionName = ft.functionName();
292
293 if (ft.namingConflict()) {
294 SQLConverter.addWAFunctionName(functionName);
295 }
296
297 }
298 }
299 }
300
301 }
302
303 private void createFunctions() {
304 for (String functionDef : functionDefinitions) {
305 Try.catching(() -> exec(functionDef, true))
306 .orElse(e -> logger.log(Level.WARNING, "Failed to create function {0}: {1}", functionDef, e.toString()));
307 }
308
309 functionDefinitions.clear();
310 }
311
312 private void createSwitch() {
313 List<DataType> dtypes = List.of(
314 DataType.BINARY, DataType.BOOLEAN, DataType.SHORT_DATE_TIME,
315 DataType.INT, DataType.LONG, DataType.DOUBLE, DataType.MONEY, DataType.NUMERIC,
316 DataType.COMPLEX_TYPE, DataType.MEMO);
317
318 for (DataType dtype : dtypes) {
319 String type = TypesMap.map2hsqldb(dtype);
320
321 for (int i = 1; i < 10; i++) {
322 StringBuilder header = new StringBuilder("CREATE FUNCTION SWITCH(");
323 StringBuilder body = new StringBuilder(" (CASE");
324 String comma = "";
325 for (int j = 0; j < i; j++) {
326 body.append(" WHEN B").append(j).append(" THEN V").append(j);
327 header.append(comma);
328 comma = ", ";
329 header.append('B').append(j).append(" BOOLEAN").append(comma)
330 .append('V').append(j).append(' ').append(type);
331 }
332 body.append(" END)");
333 header.append(") RETURNS ").append(type).append(" RETURN").append(body);
334
335 Try.catching(() -> exec(header.toString(), true))
336 .orElse(ex -> logger.log(Level.WARNING, "Failed to create function {0}: {1}", header, ex.toString()));
337 }
338 }
339
340 }
341
342 private String getAggregate(String _functionName, String _type) {
343 return "CREATE AGGREGATE FUNCTION " + _functionName + "(IN val " + _type + ", IN flag BOOLEAN, INOUT register "
344 + _type + ", INOUT counter INT) RETURNS " + _type + " NO SQL LANGUAGE JAVA "
345 + "EXTERNAL NAME 'CLASSPATH:net.ucanaccess.converters.FunctionsAggregate." + _functionName + "'";
346 }
347
348 private void loadMappedFunctions() {
349 addFunctions(Functions.class, true);
350 addAggregates();
351 createFunctions();
352 }
353 }
354
355 private final class TablesLoader {
356 private static final int HSQL_FK_ALREADY_EXISTS = -ErrorCode.X_42528;
357 private static final int HSQL_UK_ALREADY_EXISTS = -ErrorCode.X_42522;
358 private static final int HSQL_NOT_NULL = -ErrorCode.X_23502;
359 private static final int HSQL_FK_VIOLATION = -ErrorCode.X_23503;
360 private static final int HSQL_UK_VIOLATION = -ErrorCode.X_23505;
361 private static final String SYSTEM_SCHEMA = "SYS";
362 private static final int DEFAULT_STEP = 2000;
363
364 private final List<String> unresolvedTables = new ArrayList<>();
365 private final List<String> calculatedFieldsTriggers = new ArrayList<>();
366 private final List<String> loadingOrder = new LinkedList<>();
367 private final Set<Column> alreadyIndexed = new LinkedHashSet<>();
368 private final Set<String> readOnlyTables = new LinkedHashSet<>();
369
370 private String commaSeparated(List<? extends Index.Column> columns, boolean escape) {
371 String comma = "";
372 StringBuilder sb = new StringBuilder(" (");
373 for (Index.Column cd : columns) {
374 String cl = escape ? escapeIdentifier(cd.getColumn().getName()) : cd.getColumn().getName();
375 sb.append(comma).append(cl);
376 comma = ",";
377 }
378 return sb.append(") ").toString();
379 }
380
381 private String schema(String name, boolean systemTable) {
382 if (systemTable) {
383 return SYSTEM_SCHEMA + '.' + name;
384 }
385 return name;
386 }
387
388 private DataType getReturnType(Column _col) throws IOException {
389 if (_col.getProperties().get(PropertyMap.EXPRESSION_PROP) == null
390 || _col.getProperties().get(PropertyMap.RESULT_TYPE_PROP) == null) {
391 return null;
392 }
393 byte pos = (Byte) _col.getProperties().get(PropertyMap.RESULT_TYPE_PROP).getValue();
394 return DataType.fromByte(pos);
395 }
396
397 private String getHsqldbColumnType(Column _col) throws IOException {
398 String htype;
399 DataType dtyp = _col.getType();
400 DataType rtyp = getReturnType(_col);
401 boolean calcType = false;
402 if (rtyp != null) {
403 dtyp = rtyp;
404 calcType = true;
405 }
406
407 if (dtyp.equals(DataType.TEXT)) {
408 int ln = ff1997 ? _col.getLength() : _col.getLengthInUnits();
409 htype = "VARCHAR(" + ln + ')';
410 } else if (dtyp.equals(DataType.NUMERIC) && (_col.getScale() > 0 || calcType)) {
411 if (calcType) {
412 htype = "NUMERIC(100 ,4)";
413 } else {
414 htype = "NUMERIC(" + (_col.getPrecision() > 0 ? _col.getPrecision() : 100) + ',' + _col.getScale() + ')';
415 }
416 } else if (dtyp.equals(DataType.FLOAT)) {
417 if (calcType) {
418 htype = "NUMERIC(" + (_col.getPrecision() > 0 ? _col.getPrecision() : 100) + ',' + 7 + ')';
419 } else {
420 Object dps = null;
421 Object dpso = _col.getProperties().get("DecimalPlaces");
422 if (dpso != null) {
423 dps = _col.getProperties().get("DecimalPlaces").getValue();
424 }
425 byte dp = dps == null ? 7 : (Byte) dps < 0 ? 7 : (Byte) dps;
426
427 htype = "NUMERIC(" + (_col.getPrecision() > 0 ? _col.getPrecision() : 100) + ',' + dp + ')';
428 }
429 } else {
430 htype = TypesMap.map2hsqldb(dtyp);
431 }
432 return htype;
433 }
434
435 private String getCalculatedFieldTrigger(String _ntn, Column _col, boolean _isCreate)
436 throws IOException, SQLException {
437 DataType dt = getReturnType(_col);
438 String fun = null;
439 if (isNumeric(dt)) {
440 fun = "formulaToNumeric";
441 } else if (isBoolean(dt)) {
442 fun = "formulaToBoolean";
443 } else if (isDate(dt)) {
444 fun = "formulaToDate";
445 } else if (isTextual(dt)) {
446 fun = "formulaToText";
447 }
448 String call = fun == null ? "%s" : fun + "(%s,'" + dt.name() + "')";
449 String ecl = procedureEscapingIdentifier(_col.getName()).replace("%", "%%");
450
451 return _isCreate
452 ? "CREATE TRIGGER expr%d BEFORE INSERT ON " + _ntn + " REFERENCING NEW AS newrow FOR EACH ROW "
453 + " BEGIN ATOMIC SET newrow." + ecl + " = " + call + "; END "
454 : "CREATE TRIGGER expr%d BEFORE UPDATE ON " + _ntn
455 + " REFERENCING NEW AS newrow OLD AS OLDROW FOR EACH ROW BEGIN ATOMIC IF %s THEN "
456 + " SET newrow." + ecl + " = " + call + "; ELSEIF newrow." + ecl + " <> oldrow." + ecl
457 + " THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '"
458 + "The following column is not updatable: " + _col.getName().replace("%", "%%")
459 + "'; END IF; END ";
460 }
461
462 private boolean isNumeric(DataType dt) {
463 return typeGroup(dt, DataType.NUMERIC, DataType.MONEY, DataType.DOUBLE, DataType.FLOAT, DataType.LONG,
464 DataType.INT, DataType.BYTE);
465 }
466
467 private boolean isDate(DataType dt) {
468 return typeGroup(dt, DataType.SHORT_DATE_TIME);
469 }
470
471 private boolean isBoolean(DataType dt) {
472 return typeGroup(dt, DataType.BOOLEAN);
473 }
474
475 private boolean isTextual(DataType dt) {
476 return typeGroup(dt, DataType.MEMO, DataType.TEXT);
477 }
478
479 private boolean typeGroup(DataType dt, DataType... gr) {
480 for (DataType el : gr) {
481 if (el.equals(dt)) {
482 return true;
483 }
484 }
485 return false;
486 }
487
488 private void createSyncrTable(Table t, boolean systemTable) throws SQLException, IOException {
489 createSyncrTable(t, systemTable, true);
490 }
491
492 private void createSyncrTable(Table _t, boolean _systemTable, boolean _constraints) throws SQLException, IOException {
493
494 String tn = _t.getName();
495 if ("DUAL".equalsIgnoreCase(tn)) {
496 SQLConverter.setDualUsedAsTableName(true);
497 }
498 StringBuilder check = new StringBuilder();
499 String ntn = SQLConverter.preEscapingIdentifier(tn);
500
501 int seq = metadata.newTable(tn, ntn, ObjectType.TABLE);
502 ntn = SQLConverter.completeEscaping(ntn);
503 ntn = SQLConverter.checkLang(ntn, conn);
504 ntn = schema(ntn, _systemTable);
505
506 StringBuilder sbC = new StringBuilder("CREATE CACHED TABLE ").append(ntn).append('(');
507
508 String comma = "";
509 for (Column col : _t.getColumns()) {
510 if ("USER".equalsIgnoreCase(col.getName())) {
511 logger.log(Level.WARNING, "You should not use the 'user' reserved word as column name in table {0} "
512 + "(it refers to the database user). "
513 + "Escape it in your SQL e.g. SELECT [user] FROM table WHERE [user] = 'Joe'", _t.getName());
514 }
515 String expr = getExpression(col);
516 if (expr != null && _constraints) {
517 String tgrI = getCalculatedFieldTrigger(ntn, col, true);
518 String tgrU = getCalculatedFieldTrigger(ntn, col, false);
519 calculatedFieldsTriggers.add(String.format(tgrI, NAMING_COUNTER.getAndIncrement(), SQLConverter.convertFormula(expr)));
520 String uc = getUpdateConditions(col);
521 if (!uc.isEmpty()) {
522 calculatedFieldsTriggers.add(String.format(tgrU, NAMING_COUNTER.getAndIncrement(), uc, SQLConverter.convertFormula(expr)));
523 }
524
525 }
526
527 String cn = SQLConverter.preEscapingIdentifier(col.getName());
528 String colType = col.getType().name();
529 if (col.isAutoNumber()) {
530 ColumnImpl cli = (ColumnImpl) col;
531 AutoNumberGenerator ang = cli.getAutoNumberGenerator();
532 if (ang.getType().equals(DataType.LONG)) {
533 colType = "COUNTER";
534 }
535
536 } else if (col.isHyperlink()) {
537 colType = "HYPERLINK";
538 }
539 metadata.newColumn(col.getName(), cn, colType, seq);
540 if (expr != null && _constraints) {
541 metadata.calculatedField(_t.getName(), col.getName());
542 }
543 cn = SQLConverter.completeEscaping(cn);
544 cn = SQLConverter.checkLang(cn, conn);
545 sbC.append(comma).append(cn).append(' ').append(getHsqldbColumnType(col));
546 if (DataType.FLOAT.equals(col.getType())) {
547 check.append(", check (3.4028235E+38>=").append(cn).append(" AND -3.4028235E+38<=").append(cn)
548 .append(")");
549 }
550
551 PropertyMap pm = col.getProperties();
552 Object required = pm.getValue(PropertyMap.REQUIRED_PROP);
553 if (_constraints && required instanceof Boolean && (boolean) required) {
554 sbC.append(" NOT NULL ");
555 }
556 comma = ",";
557 }
558
559 sbC.append(check).append(")");
560 exec(sbC.toString(), true);
561
562 }
563
564 private String getExpression(Column _col) throws IOException {
565 PropertyMap map = _col.getProperties();
566 Property exprp = map.get(PropertyMap.EXPRESSION_PROP);
567
568 if (exprp != null) {
569 Table tl = _col.getTable();
570 String expr = SQLConverter.convertPowOperator((String) exprp.getValue());
571 for (Column col : tl.getColumns()) {
572 expr = expr.replaceAll("\\[(?i)(" + Pattern.quote(col.getName()) + ")\\]", "newrow.$0");
573 }
574 return expr;
575 }
576 return null;
577 }
578
579 private String getUpdateConditions(Column _col) throws IOException {
580 PropertyMap map = _col.getProperties();
581 Property exprp = map.get(PropertyMap.EXPRESSION_PROP);
582
583 if (exprp != null) {
584 Set<String> setu = SQLConverter.getFormulaDependencies(exprp.getValue().toString());
585
586 if (!setu.isEmpty()) {
587 String or = "";
588 StringBuilder cw = new StringBuilder();
589 for (String dep : setu) {
590 dep = escapeIdentifier(dep);
591 cw.append(or).append("oldrow.").append(dep).append("<>").append("newrow.").append(dep);
592 or = " OR ";
593 }
594
595 return cw.toString();
596 }
597
598 }
599 return " FALSE ";
600 }
601
602 private String procedureEscapingIdentifier(String name) {
603 return SQLConverter.procedureEscapingIdentifier(escapeIdentifier(name));
604 }
605
606 private String defaultValue4SQL(Object defaulT, DataType dt) {
607 if (defaulT == null) {
608 return null;
609 }
610 String default4SQL = SQLConverter.convertSQL(" " + defaulT).getSql();
611 if (default4SQL.trim().startsWith("=")) {
612 default4SQL = default4SQL.trim().substring(1);
613 }
614 if (dt.equals(DataType.BOOLEAN)
615 && ("=yes".equalsIgnoreCase(default4SQL) || "yes".equalsIgnoreCase(default4SQL))) {
616 default4SQL = "true";
617 }
618 if (dt.equals(DataType.BOOLEAN)
619 && ("=no".equalsIgnoreCase(default4SQL) || "no".equalsIgnoreCase(default4SQL))) {
620 default4SQL = "false";
621 }
622 if ((dt.equals(DataType.MEMO) || dt.equals(DataType.TEXT))
623 && (!defaulT.toString().startsWith("\"") || !defaulT.toString().endsWith("\""))
624
625 ) {
626 default4SQL = "'" + default4SQL.replace("'", "''") + "'";
627 }
628 return default4SQL;
629 }
630
631 private String createTriggerColumnDefault(Column _col, String _ntn) throws IOException, SQLException {
632 PropertyMap pm = _col.getProperties();
633 String ncn = procedureEscapingIdentifier(_col.getName());
634 Object defVal = pm.getValue(PropertyMap.DEFAULT_VALUE_PROP);
635
636 if (defVal != null && !"GenGUID()".equals(defVal)) {
637 String default4SQL = defaultValue4SQL(defVal, _col.getType());
638 boolean defIsFunction = defVal.toString().trim().endsWith(")") && defVal.toString().indexOf('(') > 0;
639 if (defIsFunction) {
640 metadata.columnDef(_col.getTable().getName(), _col.getName(), defVal.toString());
641 }
642 Object defFound = default4SQL;
643 boolean isNull = (default4SQL + "").equalsIgnoreCase("null");
644 if (!isNull && (defFound = tryDefault(default4SQL)) == null) {
645
646 logger.log(Level.WARNING, "Unknown expression: {0} (default value of column {1} table {2})",
647 defVal, _col.getName(), _col.getTable().getName());
648 } else {
649 if (defFound != null && !defIsFunction) {
650 metadata.columnDef(_col.getTable().getName(), _col.getName(), defFound.toString());
651 }
652 if (_col.getType() == DataType.TEXT && defVal.toString().startsWith("'")
653 && defVal.toString().endsWith("'")
654 && defVal.toString().length() > _col.getLengthInUnits()) {
655 logger.log(Level.WARNING, "Default values should start and end with a double quote, "
656 + "the single quote is considered as part of the default value {0} "
657 + "(column {1}, table {2}). It may result in a data truncation error at run-time due to max column size {3}",
658 defVal, _col.getName(), _col.getTable().getName(), _col.getLengthInUnits());
659 }
660 String triggerName = escapeIdentifier("tr_" + _ntn + "_default" + NAMING_COUNTER.getAndIncrement());
661 return "CREATE TRIGGER " + triggerName + " BEFORE INSERT ON " + _ntn
662 + " REFERENCING NEW ROW AS NEW FOR EACH ROW IF NEW." + ncn + " IS NULL THEN"
663 + " SET NEW." + ncn + " = " + default4SQL + "; END IF";
664 }
665 }
666 return null;
667 }
668
669 private void addTriggerColumnDefault(Column _col) throws SQLException, IOException {
670 String tn = escapeIdentifier(_col.getTable().getName());
671 String trigger = createTriggerColumnDefault(_col, tn);
672 if (trigger != null) {
673 exec(trigger, true);
674 }
675 }
676
677 private void addTriggersColumnDefault(Table _table) throws SQLException, IOException {
678 String tn = escapeIdentifier(_table.getName());
679 for (Column col : _table.getColumns()) {
680 String trigger = createTriggerColumnDefault(col, tn);
681 if (trigger != null) {
682 exec(trigger, true);
683 }
684 }
685 }
686
687 private int countFKs() throws IOException {
688 int i = 0;
689 for (String tn : loadingOrder) {
690 UcanaccessTable table = new UcanaccessTable(dbIO.getTable(tn), tn);
691 if (!unresolvedTables.contains(tn)) {
692 for (Index idxi : table.getIndexes()) {
693
694 IndexImpl idx = (IndexImpl) idxi;
695 if (idx.isForeignKey() && !idx.getReference().isPrimaryTable()) {
696 i++;
697 }
698 }
699 }
700 }
701 return i;
702 }
703
704 private boolean reorder() throws IOException {
705 int maxIteration = countFKs() + 1;
706
707 for (int i = 0; i < maxIteration; i++) {
708 boolean change = false;
709 List<String> loadingOrder0 = new ArrayList<>(loadingOrder);
710 for (String tn : loadingOrder0) {
711 UcanaccessTable table = new UcanaccessTable(dbIO.getTable(tn), tn);
712 if (!unresolvedTables.contains(tn)) {
713 for (Index idxi : table.getIndexes()) {
714
715 IndexImpl idx = (IndexImpl) idxi;
716 if (idx.isForeignKey() && !idx.getReference().isPrimaryTable() && !tryReorder(idx)) {
717 change = true;
718 }
719 }
720 }
721 }
722
723 if (!change) {
724 return true;
725 }
726 }
727
728 return false;
729 }
730
731 private boolean tryReorder(Index idxi) throws IOException {
732 IndexImpl idx = (IndexImpl) idxi;
733 String ctn = idx.getTable().getName();
734 String rtn = idx.getReferencedIndex().getTable().getName();
735 int ict = loadingOrder.indexOf(ctn);
736 int irt = loadingOrder.indexOf(rtn);
737 if (ict < irt) {
738 loadingOrder.remove(ctn);
739 loadingOrder.add(irt, ctn);
740 return false;
741 }
742 return true;
743 }
744
745 private void loadForeignKey(Index idxi, String ctn) throws IOException, SQLException {
746 IndexImpl idx = (IndexImpl) idxi;
747 String rtn = idx.getReferencedIndex().getTable().getName();
748 List<IndexData.ColumnDescriptor> cls = idx.getColumns();
749 if (cls.size() == 1) {
750 alreadyIndexed.add(cls.get(0).getColumn());
751 }
752 String ntn = escapeIdentifier(ctn);
753 if (ntn == null) {
754 return;
755 }
756 String nin = escapeIdentifier(ctn + '_' + idx.getName());
757 String colsIdx = commaSeparated(cls, true);
758 String colsIdxRef = commaSeparated(idx.getReferencedIndex().getColumns(), true);
759
760 StringBuilder ci = new StringBuilder("ALTER TABLE ").append(ntn)
761 .append(" ADD CONSTRAINT ").append(nin);
762 String nrt = escapeIdentifier(rtn);
763
764 if (nrt == null) {
765 return;
766 }
767 ci.append(" FOREIGN KEY ").append(colsIdx).append(" REFERENCES ").append(nrt).append(colsIdxRef);
768
769 if (idx.getReference().isCascadeDeletes()) {
770 ci.append(" ON DELETE CASCADE ");
771 }
772 if (idx.getReference().isCascadeUpdates()) {
773 ci.append(" ON UPDATE CASCADE ");
774 }
775 try {
776 exec(ci.toString(), true);
777 } catch (SQLException _ex) {
778 if (_ex.getErrorCode() == HSQL_FK_ALREADY_EXISTS) {
779 logger.log(Level.WARNING, _ex.getMessage());
780 } else {
781 throw _ex;
782 }
783 }
784 loadedIndexes.add("FK on " + ntn + " Columns:" + commaSeparated(cls, false) + " References " + nrt
785 + " Columns:" + commaSeparated(idx.getReferencedIndex().getColumns(), false));
786 }
787
788 private void loadIndex(Index idx, String tn) throws SQLException {
789 String ntn = escapeIdentifier(tn);
790 if (ntn == null) {
791 return;
792 }
793 String nin = idx.getName();
794 nin = escapeIdentifier(tn + '_' + nin);
795 boolean uk = idx.isUnique();
796 boolean pk = idx.isPrimaryKey();
797 if (!uk && !pk && idx.getColumns().size() == 1) {
798 Column col = idx.getColumns().get(0).getColumn();
799 if (alreadyIndexed.contains(col)) {
800 return;
801 }
802 }
803 if (uk && idx.getColumns().size() == 1) {
804 Column col = idx.getColumns().get(0).getColumn();
805 DataType dt = col.getType();
806 if (dt.equals(DataType.COMPLEX_TYPE)) {
807 return;
808 }
809 }
810
811 StringBuilder ci = new StringBuilder("ALTER TABLE ").append(ntn);
812 String colsIdx = commaSeparated(idx.getColumns(), true);
813 if (pk) {
814 ci.append(" ADD PRIMARY KEY ").append(colsIdx);
815 } else if (uk) {
816 ci.append(" ADD CONSTRAINT ").append(nin)
817 .append(" UNIQUE ").append(colsIdx);
818
819 } else {
820 ci = new StringBuilder("CREATE INDEX ").append(nin).append(" ON ").append(ntn).append(colsIdx);
821 }
822 try {
823 exec(ci.toString(), true);
824 } catch (SQLException _ex) {
825 if (HSQL_UK_ALREADY_EXISTS == _ex.getErrorCode()) {
826 return;
827 }
828 if (idx.isUnique()) {
829 for (Index.Column cd : idx.getColumns()) {
830 if (cd.getColumn().getType().equals(DataType.COMPLEX_TYPE)) {
831 return;
832 }
833 }
834 }
835 logger.log(Level.WARNING, _ex.getMessage());
836 return;
837 } catch (Exception _ex) {
838
839 logger.log(Level.WARNING, _ex.getMessage());
840 return;
841 }
842 String pre = pk ? "Primary Key " : uk ? "Index Unique " : "Index";
843 loadedIndexes.add(pre + " on " + tn + " Columns:" + commaSeparated(idx.getColumns(), false));
844
845 }
846
847 private void createTable(Table t) throws SQLException, IOException {
848 createTable(t, false);
849 }
850
851 private void dropTable(Table t, boolean systemTable) throws SQLException {
852 String tn = t.getName();
853
854 String ntn = schema(escapeIdentifier(tn), systemTable);
855 exec("DROP TABLE " + ntn + " CASCADE", false);
856 metadata.dropTable(tn);
857 }
858
859 private void makeTableReadOnly(Table t, boolean systemTable) throws SQLException {
860 String tn = t.getName();
861 readOnlyTables.add(t.getName());
862 String ntn = schema(escapeIdentifier(tn), systemTable);
863 exec("SET TABLE " + ntn + " READONLY TRUE", false);
864 loadedTables.add(tn + " READONLY");
865 }
866
867 private void recreate(Table _t, boolean _systemTable, Row _record, int _errorCode) throws SQLException, IOException {
868 String type = "";
869 switch (_errorCode) {
870 case HSQL_FK_VIOLATION:
871 type = "Foreign Key";
872 break;
873 case HSQL_NOT_NULL:
874 type = "Not Null";
875 break;
876 case HSQL_UK_VIOLATION:
877 type = "Unique";
878 break;
879 default:
880 break;
881 }
882 logger.log(Level.WARNING, "Detected {0} constraint breach, table {1}, record {2}: making the table {3} read-only",
883 type, _t.getName(), _record, _t.getName());
884
885 dropTable(_t, _systemTable);
886 createSyncrTable(_t, _systemTable, false);
887 if (_errorCode != HSQL_FK_VIOLATION) {
888 loadTableFKs(_t.getName(), false);
889 }
890 loadTableData(_t, _systemTable);
891 makeTableReadOnly(_t, _systemTable);
892
893 }
894
895 private void createTable(Table t, boolean systemTable) throws SQLException, IOException {
896 String tn = t.getName();
897 if (tn.indexOf(' ') > 0) {
898 SQLConverter.addWhiteSpacedTableNames(tn);
899 }
900 String ntn = SQLConverter.escapeIdentifier(tn);
901 if (ntn == null) {
902 return;
903 }
904 createSyncrTable(t, systemTable);
905 }
906
907 private boolean hasAppendOnly(Table t) {
908
909 for (Column c : t.getColumns()) {
910 if (c.isAppendOnly()) {
911 return true;
912 }
913 }
914 return false;
915 }
916
917 private void loadTableData(Table t, boolean systemTable) throws IOException, SQLException {
918 loadTableData(t, systemTable, false);
919 }
920
921 @SuppressWarnings("PMD.UseTryWithResources")
922 private void loadTableData(Table _t, boolean _systemTable, boolean _errorCheck) throws IOException, SQLException {
923 TimeZone prevJackcessTimeZone = _t.getDatabase().getTimeZone();
924 _t.getDatabase().setTimeZone(TimeZone.getTimeZone("UTC"));
925 int step = _errorCheck ? 1 : DEFAULT_STEP;
926 int i = 0;
927 PreparedStatement ps = null;
928
929 try {
930 Iterator<Row> it = _t.iterator();
931
932 while (it.hasNext()) {
933 Row row = it.next();
934 if (row == null) {
935 continue;
936 }
937 List<Object> values = new ArrayList<>();
938 if (ps == null) {
939 ps = sqlInsert(_t, row, _systemTable);
940 }
941 for (Map.Entry<String, Object> entry : row.entrySet()) {
942 values.add(value(entry.getValue(), _t, entry.getKey(), row));
943 }
944 tablesLoader.execInsert(ps, values);
945
946 if (_errorCheck || i > 0 && i % step == 0 || !it.hasNext()) {
947 try {
948 ps.executeBatch();
949 } catch (SQLException _ex) {
950 int ec = _ex.getErrorCode();
951 if (!_errorCheck && ec == HSQL_NOT_NULL) {
952 dropTable(_t, _systemTable);
953 createSyncrTable(_t, _systemTable, true);
954 loadTableData(_t, _systemTable, true);
955 } else {
956 if (ec == HSQL_NOT_NULL || ec == HSQL_FK_VIOLATION || ec == HSQL_UK_VIOLATION) {
957 if (ec == HSQL_FK_VIOLATION) {
958 logger.log(Level.WARNING, _ex.getMessage());
959 }
960 recreate(_t, _systemTable, row, _ex.getErrorCode());
961 } else {
962 throw _ex;
963 }
964 }
965 }
966 if (_errorCheck) {
967 conn.rollback();
968 } else {
969 conn.commit();
970 }
971 }
972 i++;
973
974 }
975 if (i != _t.getRowCount() && step != 1) {
976 logger.log(Level.WARNING, "Error in the metadata of the table {0}: the table's row count in metadata is {1} "
977 + "but {2} records have been found and loaded by UCanAccess. "
978 + "All will work fine, but it's better to repair your database",
979 _t.getName(), _t.getRowCount(), i);
980 }
981 } finally {
982 if (ps != null) {
983 ps.close();
984 }
985 }
986 _t.getDatabase().setTimeZone(prevJackcessTimeZone);
987 }
988
989 private void loadTableFKs(String tn, boolean autoref) throws IOException, SQLException {
990 if (readOnlyTables.contains(tn)) {
991 return;
992 }
993 Table t = dbIO.getTable(tn);
994 UcanaccessTable table = new UcanaccessTable(t, tn);
995 if (t != null) {
996 for (Index idxi : table.getIndexes()) {
997
998 IndexImpl idx = (IndexImpl) idxi;
999 if (idx.isForeignKey() && !idx.getReference().isPrimaryTable()) {
1000 boolean isAuto = idx.getTable().getName().equals(idx.getReferencedIndex().getTable().getName());
1001 if (autoref && isAuto || !autoref && !isAuto) {
1002 loadForeignKey(idx, tn);
1003 }
1004 }
1005 }
1006 }
1007 }
1008
1009 private void createCalculatedFieldsTriggers() {
1010 calculatedFieldsTriggers.forEach(t -> Try.catching(() -> exec(t, false))
1011 .orElse(e -> logger.log(Level.WARNING, e.getMessage())));
1012 }
1013
1014 private void loadTableIndexesUK(String tn) throws IOException, SQLException {
1015 Table t = dbIO.getTable(tn);
1016 UcanaccessTable table = new UcanaccessTable(t, tn);
1017 if (t != null) {
1018 for (Index idx : table.getIndexes()) {
1019 if (!idx.isForeignKey() && (idx.isPrimaryKey() || idx.isUnique())) {
1020 loadIndex(idx, tn);
1021 }
1022 }
1023 }
1024
1025 }
1026
1027 private void loadTableIndexesNotUK(String tn) throws IOException, SQLException {
1028 Table t = dbIO.getTable(tn);
1029 UcanaccessTable table = new UcanaccessTable(t, tn);
1030 if (!skipIndexes && t != null) {
1031 for (Index idx : table.getIndexes()) {
1032 if (!idx.isForeignKey() && !idx.isPrimaryKey() && !idx.isUnique()) {
1033 loadIndex(idx, tn);
1034 }
1035 }
1036 }
1037
1038 }
1039
1040 private void createTables() throws SQLException, IOException {
1041
1042 metadata.createMetadata();
1043
1044 for (String tn : dbIO.getTableNames()) {
1045 if (tn.startsWith("~")) {
1046 logger.log(Level.DEBUG, "Skipping table '{0}'", tn);
1047 continue;
1048 }
1049
1050 try {
1051 Table jt = dbIO.getTable(tn);
1052 UcanaccessTable ut = new UcanaccessTable(jt, tn);
1053
1054 if (TableMetaData.Type.LINKED_ODBC == jt.getDatabase().getTableMetaData(tn).getType()) {
1055 logger.log(Level.WARNING, "Skipping table '{0}' (linked to an ODBC table)", tn);
1056 unresolvedTables.add(tn);
1057 continue;
1058 }
1059
1060 createTable(ut);
1061 loadingOrder.add(ut.getName());
1062
1063 } catch (Exception _ex) {
1064 logger.log(Level.WARNING, "Failed to create table '{0}': {1}", tn, _ex.getMessage());
1065 unresolvedTables.add(tn);
1066 }
1067
1068 }
1069 }
1070
1071 private void createIndexesUK() throws SQLException, IOException {
1072 for (String tn : dbIO.getTableNames()) {
1073 if (!unresolvedTables.contains(tn)) {
1074 loadTableIndexesUK(tn);
1075 conn.commit();
1076 }
1077 }
1078 }
1079
1080 private void createIndexesNotUK() throws SQLException, IOException {
1081 for (String tn : dbIO.getTableNames()) {
1082 if (!unresolvedTables.contains(tn)) {
1083 loadTableIndexesNotUK(tn);
1084 conn.commit();
1085 }
1086 }
1087 }
1088
1089 private void createFKs() throws SQLException, IOException {
1090 for (String tn : dbIO.getTableNames()) {
1091 if (!unresolvedTables.contains(tn)) {
1092 loadTableFKs(tn, false);
1093 conn.commit();
1094 }
1095 }
1096
1097 }
1098
1099 private void createAutoFKs() throws SQLException, IOException {
1100 for (String tn : dbIO.getTableNames()) {
1101 if (!unresolvedTables.contains(tn)) {
1102 try {
1103 loadTableFKs(tn, true);
1104 } catch (SQLException _ex) {
1105 UcanaccessTable t = new UcanaccessTable(dbIO.getTable(tn), tn);
1106 makeTableReadOnly(t, false);
1107 }
1108 conn.commit();
1109 }
1110 }
1111
1112 }
1113
1114 private void loadTablesData() throws SQLException, IOException {
1115 for (String tn : loadingOrder) {
1116 if (!unresolvedTables.contains(tn)) {
1117 UcanaccessTable t = new UcanaccessTable(dbIO.getTable(tn), tn);
1118 loadTableData(t, false);
1119 conn.commit();
1120
1121 }
1122 }
1123 }
1124
1125 private void createTriggers() throws IOException, SQLException {
1126
1127 for (String tn : loadingOrder) {
1128 if (!unresolvedTables.contains(tn) && !readOnlyTables.contains(tn)) {
1129 UcanaccessTable t = new UcanaccessTable(dbIO.getTable(tn), tn);
1130 createSyncrTriggers(t);
1131 }
1132 }
1133 createCalculatedFieldsTriggers();
1134 }
1135
1136 private void createSystemTables() throws SQLException, IOException {
1137 if (sysSchema) {
1138 createSystemSchema();
1139 for (String tn : dbIO.getSystemTableNames()) {
1140 UcanaccessTable t = null;
1141 try {
1142 t = new UcanaccessTable(dbIO.getSystemTable(tn), tn);
1143
1144 if (t != null) {
1145 createTable(t, true);
1146 loadTableData(t, true);
1147 exec("SET TABLE " + schema(SQLConverter.escapeIdentifier(t.getName()), true)
1148 + " READONLY TRUE", false);
1149 exec("GRANT SELECT ON " + schema(SQLConverter.escapeIdentifier(t.getName()), true)
1150 + " TO PUBLIC", false);
1151 }
1152 } catch (Exception _ignored) {
1153 }
1154 }
1155 }
1156 }
1157
1158 private void loadTables() throws SQLException, IOException {
1159 createTables();
1160 createIndexesUK();
1161 boolean reorder = reorder();
1162 if (reorder) {
1163 createFKs();
1164 }
1165 createIndexesNotUK();
1166 loadTablesData();
1167 createTriggers();
1168 if (!reorder) {
1169 createFKs();
1170 }
1171 createAutoFKs();
1172 createSystemTables();
1173 }
1174
1175 private void createSystemSchema() throws SQLException {
1176 exec("CREATE SCHEMA " + SYSTEM_SCHEMA + " AUTHORIZATION DBA", false);
1177 }
1178
1179 private void createSyncrTriggers(Table t) throws SQLException, IOException {
1180 addTriggersColumnDefault(t);
1181 String ntn = escapeIdentifier(t.getName());
1182 triggersGenerator.synchronisationTriggers(ntn, hasAutoNumberColumn(t), hasAppendOnly(t));
1183 loadedTables.add(t.getName());
1184 }
1185
1186 private PreparedStatement sqlInsert(Table t, Map<String, Object> row, boolean systemTable)
1187 throws SQLException {
1188 String tn = t.getName();
1189 String ntn = schema(escapeIdentifier(tn), systemTable);
1190 String comma = "";
1191 StringBuilder sbI = new StringBuilder(" INSERT INTO ").append(ntn).append(" (");
1192 StringBuilder sbE = new StringBuilder(" VALUES( ");
1193 Set<String> se = row.keySet();
1194 comma = "";
1195 for (String cn : se) {
1196 sbI.append(comma).append(escapeIdentifier(cn));
1197 sbE.append(comma).append(" ? ");
1198 comma = ",";
1199 }
1200 sbI.append(") ");
1201 sbE.append(')');
1202 sbI.append(sbE);
1203
1204 return conn.prepareStatement(sbI.toString());
1205 }
1206
1207 private Object value(Object value, Table table, String columnName, Row row) throws SQLException {
1208 if (value == null) {
1209 return null;
1210 }
1211 if (value instanceof Float) {
1212 if (value.equals(Float.NaN)) {
1213 return value;
1214 }
1215 return new BigDecimal(value.toString());
1216 }
1217 if (value instanceof Date && !(value instanceof Timestamp)) {
1218 return LocalDateTime.ofInstant(((Date) value).toInstant(), ZoneId.of("UTC"));
1219 }
1220 if (value instanceof ComplexValueForeignKey) {
1221 try {
1222 return ComplexBase.convert((ComplexValueForeignKey) value);
1223 } catch (IOException _ex) {
1224 throw new UcanaccessSQLException(_ex);
1225 }
1226 }
1227 if (value instanceof byte[] && BlobKey.hasPrimaryKey(table)) {
1228 BlobKey bk = new BlobKey(table, columnName, row);
1229 return bk.getBytes();
1230 }
1231
1232 if (value instanceof Byte) {
1233 return SQLConverter.asUnsigned((Byte) value);
1234 }
1235 return value;
1236 }
1237
1238 private void execInsert(PreparedStatement st, List<Object> values) throws SQLException {
1239 int i = 1;
1240 for (Object value : values) {
1241 st.setObject(i++, value);
1242 }
1243
1244 st.addBatch();
1245 }
1246
1247 }
1248
1249 private final class TriggersLoader {
1250 void loadTrigger(String tableName, String nameSuffix, String when, Class<? extends TriggerBase> clazz) throws SQLException {
1251 String triggerName = escapeIdentifier("tr_" + tableName + '_' + nameSuffix);
1252 String q0 = DBReference.is2xx() ? "" : "QUEUE 0 ";
1253 exec("CREATE TRIGGER " + triggerName + ' ' + when + " ON " + tableName + " FOR EACH ROW " + q0
1254 + "CALL \"" + clazz.getName() + "\"", true);
1255 }
1256
1257 void synchronisationTriggers(String tableName, boolean hasAutoNumberColumn, boolean hasAutoAppendOnly) throws SQLException {
1258
1259
1260
1261
1262 loadTrigger(tableName, "generic_insert", "AFTER INSERT", TriggerInsert.class);
1263 loadTrigger(tableName, "generic_update", "AFTER UPDATE", TriggerUpdate.class);
1264 loadTrigger(tableName, "generic_delete", "AFTER DELETE", TriggerDelete.class);
1265 if (hasAutoAppendOnly) {
1266 loadTrigger(tableName, "append_only", "BEFORE INSERT", TriggerAppendOnly.class);
1267 loadTrigger(tableName, "append_only_upd", "BEFORE UPDATE", TriggerAppendOnly.class);
1268 }
1269 if (hasAutoNumberColumn) {
1270 loadTrigger(tableName, "autonumber", "BEFORE INSERT", TriggerAutoNumber.class);
1271 loadTrigger(tableName, "autonumber_validate", "BEFORE UPDATE", TriggerAutoNumber.class);
1272 }
1273 }
1274 }
1275
1276 private final class ViewsLoader {
1277 private static final int OBJECT_ALREADY_EXISTS = -ErrorCode.X_42504;
1278 private static final int OBJECT_NOT_FOUND = -ErrorCode.X_42501;
1279 private static final int UNEXPECTED_TOKEN = -ErrorCode.X_42581;
1280
1281 private final Map<String, String> notLoaded = new LinkedHashMap<>();
1282 private final Map<String, String> notLoadedProcedure = new LinkedHashMap<>();
1283
1284 private boolean loadView(Query _q) throws SQLException {
1285 return loadView(_q, null);
1286 }
1287
1288 private void registerQueryColumns(Query _q, int _seq) throws SQLException {
1289 QueryImpl qi = (QueryImpl) _q;
1290 for (QueryImpl.Row row : qi.getRows()) {
1291
1292 if (QueryFormat.COLUMN_ATTRIBUTE.equals(row._attribute)) {
1293 String name = row._name1;
1294
1295 if (name == null) {
1296 int beginIndex = Math.max(row._expression.lastIndexOf('['), row._expression.lastIndexOf('.'));
1297
1298 if (beginIndex < 0 || beginIndex == row._expression.length() - 1
1299 || row._expression.endsWith(")")) {
1300 continue;
1301 }
1302 name = row._expression.substring(beginIndex + 1);
1303 if (name.endsWith("]")) {
1304 name = name.substring(0, name.length() - 1);
1305 }
1306 if (name.contentEquals("*")) {
1307 String table = row._expression.substring(0, beginIndex);
1308 List<String> result = metadata.getColumnNames(table);
1309 if (result != null) {
1310 for (String column : result) {
1311 metadata.newColumn(column, SQLConverter.preEscapingIdentifier(column), null, _seq);
1312 }
1313
1314 }
1315
1316 }
1317 }
1318
1319 metadata.newColumn(name, SQLConverter.preEscapingIdentifier(name), null, _seq);
1320
1321 }
1322 }
1323 }
1324
1325 private boolean loadView(Query _query, String queryWkt) throws SQLException {
1326 String qnn = SQLConverter.preEscapingIdentifier(_query.getName());
1327 if (qnn == null) {
1328 return false;
1329 }
1330 int seq = metadata.newTable(_query.getName(), qnn, ObjectType.VIEW);
1331 registerQueryColumns(_query, seq);
1332 qnn = SQLConverter.completeEscaping(qnn, false);
1333 qnn = SQLConverter.checkLang(qnn, conn, false);
1334 if (qnn.indexOf(' ') > 0) {
1335 SQLConverter.addWhiteSpacedTableNames(_query.getName());
1336 }
1337
1338 String querySql = Optional.ofNullable(queryWkt).orElse(_query.toSQLString());
1339 Pivot pivot = null;
1340 boolean isPivot = _query.getType().equals(Query.Type.CROSS_TAB);
1341 if (isPivot) {
1342 pivot = new Pivot(conn);
1343
1344 if (!pivot.parsePivot(querySql) || (querySql = pivot.toSQL(_query.getName())) == null) {
1345 notLoaded.put(_query.getName(), "cannot load this query");
1346
1347 return false;
1348 }
1349
1350 }
1351 querySql = new DFunction(conn, querySql).toSQL();
1352 StringBuilder sb = new StringBuilder("CREATE VIEW ").append(qnn).append(" AS ").append(querySql);
1353 String v = null;
1354 try {
1355 v = SQLConverter.convertSQL(sb.toString(), true).getSql();
1356
1357 if (v.trim().endsWith(";")) {
1358 v = v.trim().substring(0, v.length() - 1);
1359 }
1360 exec(v, false);
1361 loadedQueries.add(_query.getName());
1362 notLoaded.remove(_query.getName());
1363 if (pivot != null) {
1364 pivot.registerPivot(SQLConverter.preEscapingIdentifier(_query.getName()));
1365 }
1366 return true;
1367 } catch (Exception _ex) {
1368 if (_ex instanceof SQLSyntaxErrorException) {
1369 if (queryWkt == null && OBJECT_ALREADY_EXISTS == ((SQLSyntaxErrorException) _ex).getErrorCode()) {
1370 return loadView(_query, solveAmbiguous(querySql));
1371 } else {
1372 SQLSyntaxErrorException sqle = (SQLSyntaxErrorException) _ex;
1373 if (sqle.getErrorCode() == OBJECT_NOT_FOUND || UNEXPECTED_TOKEN == sqle.getErrorCode()) {
1374 ParametricQuery pq = new ParametricQuery(conn, (QueryImpl) _query);
1375 pq.setIssueWithParameterName(sqle.getErrorCode() == UNEXPECTED_TOKEN);
1376 pq.createSelect();
1377 if (pq.loaded()) {
1378 loadedQueries.add(_query.getName());
1379 notLoaded.remove(_query.getName());
1380 return true;
1381 }
1382
1383 }
1384 }
1385 }
1386
1387 String cause = UcanaccessSQLException.explainCause(_ex);
1388
1389 notLoaded.put(_query.getName(), ": " + cause);
1390
1391 if (!err) {
1392 logger.log(Level.WARNING, "Error at first loading attempt of view \''{0}\'', converted view \''{1}\'', error {2}",
1393 _query.getName(), v, _ex.toString());
1394 err = true;
1395 }
1396 return false;
1397 }
1398 }
1399
1400 @SuppressWarnings("java:S5852")
1401 private String solveAmbiguous(String sql) {
1402 try {
1403 sql = sql.replaceAll("\\s+", " ");
1404 Pattern pat = Pattern.compile("(.*)\\s+SELECT(\\s.*\\s)FROM(\\s)(.*)", Pattern.CASE_INSENSITIVE);
1405 Matcher mtc = pat.matcher(sql);
1406 if (mtc.find()) {
1407 String select = mtc.group(2);
1408 String pre = mtc.group(1) == null ? "" : mtc.group(1);
1409 String[] split = select.split(",", -1);
1410 StringBuilder sb = new StringBuilder(pre).append(" select ");
1411 List<String> lkl = new LinkedList<>();
1412
1413 Pattern patAlias = Pattern.compile("\\s+AS\\s+", Pattern.CASE_INSENSITIVE);
1414 for (String s : split) {
1415 int i = s.lastIndexOf('.');
1416 boolean alias = patAlias.matcher(s).find();
1417 if (i < 0 || alias) {
1418 lkl.add(s);
1419 } else {
1420 String k = s.substring(i + 1);
1421 if (lkl.contains(k)) {
1422 int idx = lkl.indexOf(k);
1423 String old = lkl.get(lkl.indexOf(k));
1424 lkl.remove(old);
1425 lkl.add(idx, split[idx] + " AS [" + split[idx].trim() + ']');
1426 lkl.add(s + " AS [" + s.trim() + ']');
1427 } else {
1428 lkl.add(k);
1429 }
1430 }
1431 }
1432 String comma = "";
1433 for (String s : lkl) {
1434 sb.append(comma).append(s);
1435
1436 comma = ",";
1437 }
1438 sb.append(" FROM ").append(mtc.group(4));
1439
1440 return sb.toString();
1441 } else {
1442 return sql;
1443 }
1444 } catch (Exception _ex) {
1445 return sql;
1446 }
1447 }
1448
1449 private void loadViews() throws SQLException {
1450 List<Query> lq = null;
1451 List<Query> procedures = new ArrayList<>();
1452 try {
1453 lq = dbIO.getQueries();
1454 Iterator<Query> it = lq.iterator();
1455 while (it.hasNext()) {
1456 Query q = it.next();
1457 if (!q.getType().equals(Query.Type.SELECT) && !q.getType().equals(Query.Type.UNION)
1458 && !q.getType().equals(Query.Type.CROSS_TAB)) {
1459 procedures.add(q);
1460 it.remove();
1461 }
1462
1463 }
1464 queryPorting(lq);
1465 } catch (Exception _ex) {
1466 notLoaded.put("", "");
1467 }
1468 loadProcedures(procedures);
1469
1470 }
1471
1472 private void loadProcedures(List<Query> procedures) {
1473 for (Query q : procedures) {
1474 ParametricQuery pq = new ParametricQuery(conn, (QueryImpl) q);
1475 if (!q.getType().equals(Query.Type.DATA_DEFINITION)) {
1476 pq.createProcedure();
1477 if (pq.loaded()) {
1478 loadedProcedures.add(pq.getSignature());
1479
1480 } else {
1481 String msg = pq.getException() == null ? "" : pq.getException().getMessage();
1482 notLoadedProcedure.put(q.getName(), msg);
1483
1484 }
1485
1486 }
1487 }
1488 }
1489
1490 private void queryPorting(List<Query> lq) throws SQLException {
1491 List<String> arn = new ArrayList<>();
1492 for (Query q : lq) {
1493 arn.add(q.getName().toLowerCase());
1494 }
1495 boolean heavy = false;
1496 while (!lq.isEmpty()) {
1497 List<Query> arq = new ArrayList<>();
1498 for (Query q : lq) {
1499 String qtxt = null;
1500 boolean qryGot = true;
1501 try {
1502 qtxt = q.toSQLString().toLowerCase();
1503 } catch (Exception _ignored) {
1504 qryGot = false;
1505 }
1506 boolean foundDep = false;
1507 if (qryGot && !heavy) {
1508 for (String name : arn) {
1509 if (qtxt.contains(name)) {
1510 foundDep = true;
1511 break;
1512 }
1513 }
1514 }
1515 if (qryGot && !foundDep && loadView(q)) {
1516 arq.add(q);
1517 arn.remove(q.getName().toLowerCase());
1518 }
1519 }
1520 if (arq.isEmpty()) {
1521 if (heavy) {
1522 break;
1523 } else {
1524 heavy = true;
1525 }
1526 }
1527 lq.removeAll(arq);
1528 }
1529 Pivot.clearPrepared();
1530 }
1531 }
1532
1533 }