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 ResultSet rs = st.executeQuery(String.format("SELECT %s FROM DUAL", _default));
176 if (rs.next()) {
177 return rs.getObject(1);
178 }
179 return null;
180 } catch (Exception _ex) {
181 return null;
182 }
183 }
184
185 public void setSysSchema(boolean _sysSchema) {
186 sysSchema = _sysSchema;
187 }
188
189 public void setSkipIndexes(boolean _skipIndexes) {
190 skipIndexes = _skipIndexes;
191 }
192
193 private final class FunctionsLoader {
194
195 private final Set<String> functionDefinitions = new LinkedHashSet<>();
196
197 private void addAggregates() {
198 functionDefinitions.addAll(List.of(
199 getAggregate("last", "LONGVARCHAR"),
200 getAggregate("last", "DECIMAL(100,10)"),
201 getAggregate("last", "BOOLEAN"),
202 getAggregate("first", "LONGVARCHAR"),
203 getAggregate("first", "DECIMAL(100,10)"),
204 getAggregate("first", "BOOLEAN"),
205 getLastTimestamp(),
206 getFirstTimestamp()));
207 }
208
209 private String getLastTimestamp() {
210 return "CREATE AGGREGATE FUNCTION last(IN val TIMESTAMP, IN flag boolean, INOUT ts TIMESTAMP, INOUT counter INT) "
211 + "RETURNS TIMESTAMP CONTAINS SQL BEGIN ATOMIC IF flag THEN RETURN ts; "
212 + "ELSE IF counter IS NULL THEN SET counter = 0; END IF; SET counter = counter + 1; "
213 + "SET ts = val; RETURN NULL; END IF; END";
214 }
215
216 private String getFirstTimestamp() {
217 return "CREATE AGGREGATE FUNCTION First(IN val TIMESTAMP, IN flag boolean, INOUT ts TIMESTAMP , INOUT counter INT) "
218 + "RETURNS TIMESTAMP CONTAINS SQL BEGIN ATOMIC IF flag THEN RETURN ts; "
219 + "ELSE IF counter IS NULL THEN SET counter = 0; END IF; SET counter = counter + 1; "
220 + "IF counter = 1 THEN SET ts = val; END IF; RETURN NULL; END IF; END";
221 }
222
223 private void addFunction(String _functionName, String _javaMethodName, String _returnType, String... _paramTypes) {
224 StringBuilder code = new StringBuilder();
225 if (DBReference.is2xx()) {
226 String parms = IntStream.rangeClosed(1, _paramTypes.length).mapToObj(i -> "par" + i + ' ' + _paramTypes[i - 1]).collect(Collectors.joining(", "));
227 code.append("CREATE FUNCTION ").append(_functionName)
228 .append('(').append(parms).append(')')
229 .append(" RETURNS ").append(_returnType)
230 .append(" LANGUAGE JAVA DETERMINISTIC NO SQL EXTERNAL NAME ")
231 .append("'CLASSPATH:").append(_javaMethodName).append("'");
232 } else {
233 code.append("CREATE ALIAS ")
234 .append(_functionName)
235 .append(" FOR \"").append(_javaMethodName).append("\"");
236 }
237 functionDefinitions.add(code.toString());
238 }
239
240 private void addFunctions(Class<?> _clazz, boolean _cswitch) {
241 Map<String, String> tmap = TypesMap.getAccess2HsqlTypesMap();
242
243 for (Method method : _clazz.getDeclaredMethods()) {
244
245 List<FunctionType> functionTypes = Stream.of(method.getAnnotations())
246 .filter(ant -> ant.annotationType().equals(FunctionType.class))
247 .map(FunctionType.class::cast)
248 .collect(Collectors.toList());
249
250 for (FunctionType func : functionTypes) {
251 String methodName = _clazz.getName() + '.' + method.getName();
252 String functionName = Objects.requireNonNullElse(func.functionName(), methodName);
253 AccessType[] acts = func.argumentTypes();
254 AccessType ret = func.returnType();
255 String retTypeName = ret.name();
256 String returnType = tmap.getOrDefault(retTypeName, retTypeName);
257 if (AccessType.TEXT.equals(ret)) {
258 returnType += "(255)";
259 }
260 String[] args = new String[acts.length];
261 for (int i = 0; i < args.length; i++) {
262 String typeName = acts[i].name();
263 args[i] = tmap.getOrDefault(typeName, typeName);
264 if (AccessType.TEXT.equals(acts[i])) {
265 args[i] += "(255)";
266 }
267 }
268 if (func.namingConflict()) {
269 SQLConverter.addWAFunctionName(functionName);
270 functionName += "WA";
271 }
272 addFunction(functionName, methodName, returnType, args);
273 }
274
275 }
276 createFunctions();
277 if (_cswitch) {
278 createSwitch();
279 }
280 }
281
282 private void resetDefault() {
283 Class<?> clazz = Functions.class;
284 Method[] mths = clazz.getDeclaredMethods();
285 for (Method mth : mths) {
286 Annotation[] ants = mth.getAnnotations();
287 for (Annotation ant : ants) {
288 if (ant.annotationType().equals(FunctionType.class)) {
289 FunctionType ft = (FunctionType) ant;
290 String functionName = ft.functionName();
291
292 if (ft.namingConflict()) {
293 SQLConverter.addWAFunctionName(functionName);
294 }
295
296 }
297 }
298 }
299
300 }
301
302 private void createFunctions() {
303 for (String functionDef : functionDefinitions) {
304 Try.catching(() -> exec(functionDef, true))
305 .orElse(e -> logger.log(Level.WARNING, "Failed to create function {0}: {1}", functionDef, e.toString()));
306 }
307
308 functionDefinitions.clear();
309 }
310
311 private void createSwitch() {
312 List<DataType> dtypes = List.of(
313 DataType.BINARY, DataType.BOOLEAN, DataType.SHORT_DATE_TIME,
314 DataType.INT, DataType.LONG, DataType.DOUBLE, DataType.MONEY, DataType.NUMERIC,
315 DataType.COMPLEX_TYPE, DataType.MEMO);
316
317 for (DataType dtype : dtypes) {
318 String type = TypesMap.map2hsqldb(dtype);
319
320 for (int i = 1; i < 10; i++) {
321 StringBuilder header = new StringBuilder("CREATE FUNCTION SWITCH(");
322 StringBuilder body = new StringBuilder(" (CASE");
323 String comma = "";
324 for (int j = 0; j < i; j++) {
325 body.append(" WHEN B").append(j).append(" THEN V").append(j);
326 header.append(comma);
327 comma = ", ";
328 header.append('B').append(j).append(" BOOLEAN").append(comma)
329 .append('V').append(j).append(' ').append(type);
330 }
331 body.append(" END)");
332 header.append(") RETURNS ").append(type).append(" RETURN").append(body);
333
334 Try.catching(() -> exec(header.toString(), true))
335 .orElse(ex -> logger.log(Level.WARNING, "Failed to create function {0}: {1}", header, ex.toString()));
336 }
337 }
338
339 }
340
341 private String getAggregate(String _functionName, String _type) {
342 return "CREATE AGGREGATE FUNCTION " + _functionName + "(IN val " + _type + ", IN flag BOOLEAN, INOUT register "
343 + _type + ", INOUT counter INT) RETURNS " + _type + " NO SQL LANGUAGE JAVA "
344 + "EXTERNAL NAME 'CLASSPATH:net.ucanaccess.converters.FunctionsAggregate." + _functionName + "'";
345 }
346
347 private void loadMappedFunctions() {
348 addFunctions(Functions.class, true);
349 addAggregates();
350 createFunctions();
351 }
352 }
353
354 private final class TablesLoader {
355 private static final int HSQL_FK_ALREADY_EXISTS = -ErrorCode.X_42528;
356 private static final int HSQL_UK_ALREADY_EXISTS = -ErrorCode.X_42522;
357 private static final int HSQL_NOT_NULL = -ErrorCode.X_23502;
358 private static final int HSQL_FK_VIOLATION = -ErrorCode.X_23503;
359 private static final int HSQL_UK_VIOLATION = -ErrorCode.X_23505;
360 private static final String SYSTEM_SCHEMA = "SYS";
361 private static final int DEFAULT_STEP = 2000;
362
363 private final List<String> unresolvedTables = new ArrayList<>();
364 private final List<String> calculatedFieldsTriggers = new ArrayList<>();
365 private final List<String> loadingOrder = new LinkedList<>();
366 private final Set<Column> alreadyIndexed = new LinkedHashSet<>();
367 private final Set<String> readOnlyTables = new LinkedHashSet<>();
368
369 private String commaSeparated(List<? extends Index.Column> columns, boolean escape) throws SQLException {
370 String comma = "";
371 StringBuilder sb = new StringBuilder(" (");
372 for (Index.Column cd : columns) {
373 String cl = escape ? escapeIdentifier(cd.getColumn().getName()) : cd.getColumn().getName();
374 sb.append(comma).append(cl);
375 comma = ",";
376 }
377 return sb.append(") ").toString();
378 }
379
380 private String schema(String name, boolean systemTable) {
381 if (systemTable) {
382 return SYSTEM_SCHEMA + '.' + name;
383 }
384 return name;
385 }
386
387 private DataType getReturnType(Column _col) throws IOException {
388 if (_col.getProperties().get(PropertyMap.EXPRESSION_PROP) == null
389 || _col.getProperties().get(PropertyMap.RESULT_TYPE_PROP) == null) {
390 return null;
391 }
392 byte pos = (Byte) _col.getProperties().get(PropertyMap.RESULT_TYPE_PROP).getValue();
393 return DataType.fromByte(pos);
394 }
395
396 private String getHsqldbColumnType(Column _col) throws IOException {
397 String htype;
398 DataType dtyp = _col.getType();
399 DataType rtyp = getReturnType(_col);
400 boolean calcType = false;
401 if (rtyp != null) {
402 dtyp = rtyp;
403 calcType = true;
404 }
405
406 if (dtyp.equals(DataType.TEXT)) {
407 int ln = ff1997 ? _col.getLength() : _col.getLengthInUnits();
408 htype = "VARCHAR(" + ln + ')';
409 } else if (dtyp.equals(DataType.NUMERIC) && (_col.getScale() > 0 || calcType)) {
410 if (calcType) {
411 htype = "NUMERIC(100 ,4)";
412 } else {
413 htype = "NUMERIC(" + (_col.getPrecision() > 0 ? _col.getPrecision() : 100) + ',' + _col.getScale() + ')';
414 }
415 } else if (dtyp.equals(DataType.FLOAT)) {
416 if (calcType) {
417 htype = "NUMERIC(" + (_col.getPrecision() > 0 ? _col.getPrecision() : 100) + ',' + 7 + ')';
418 } else {
419 Object dps = null;
420 Object dpso = _col.getProperties().get("DecimalPlaces");
421 if (dpso != null) {
422 dps = _col.getProperties().get("DecimalPlaces").getValue();
423 }
424 byte dp = dps == null ? 7 : (Byte) dps < 0 ? 7 : (Byte) dps;
425
426 htype = "NUMERIC(" + (_col.getPrecision() > 0 ? _col.getPrecision() : 100) + ',' + dp + ')';
427 }
428 } else {
429 htype = TypesMap.map2hsqldb(dtyp);
430 }
431 return htype;
432 }
433
434 private String getCalculatedFieldTrigger(String _ntn, Column _col, boolean _isCreate)
435 throws IOException, SQLException {
436 DataType dt = getReturnType(_col);
437 String fun = null;
438 if (isNumeric(dt)) {
439 fun = "formulaToNumeric";
440 } else if (isBoolean(dt)) {
441 fun = "formulaToBoolean";
442 } else if (isDate(dt)) {
443 fun = "formulaToDate";
444 } else if (isTextual(dt)) {
445 fun = "formulaToText";
446 }
447 String call = fun == null ? "%s" : fun + "(%s,'" + dt.name() + "')";
448 String ecl = procedureEscapingIdentifier(_col.getName()).replace("%", "%%");
449
450 return _isCreate
451 ? "CREATE TRIGGER expr%d BEFORE INSERT ON " + _ntn + " REFERENCING NEW AS newrow FOR EACH ROW "
452 + " BEGIN ATOMIC SET newrow." + ecl + " = " + call + "; END "
453 : "CREATE TRIGGER expr%d BEFORE UPDATE ON " + _ntn
454 + " REFERENCING NEW AS newrow OLD AS OLDROW FOR EACH ROW BEGIN ATOMIC IF %s THEN "
455 + " SET newrow." + ecl + " = " + call + "; ELSEIF newrow." + ecl + " <> oldrow." + ecl
456 + " THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '"
457 + "The following column is not updatable: " + _col.getName().replace("%", "%%")
458 + "'; END IF; END ";
459 }
460
461 private boolean isNumeric(DataType dt) {
462 return typeGroup(dt, DataType.NUMERIC, DataType.MONEY, DataType.DOUBLE, DataType.FLOAT, DataType.LONG,
463 DataType.INT, DataType.BYTE);
464 }
465
466 private boolean isDate(DataType dt) {
467 return typeGroup(dt, DataType.SHORT_DATE_TIME);
468 }
469
470 private boolean isBoolean(DataType dt) {
471 return typeGroup(dt, DataType.BOOLEAN);
472 }
473
474 private boolean isTextual(DataType dt) {
475 return typeGroup(dt, DataType.MEMO, DataType.TEXT);
476 }
477
478 private boolean typeGroup(DataType dt, DataType... gr) {
479 for (DataType el : gr) {
480 if (el.equals(dt)) {
481 return true;
482 }
483 }
484 return false;
485 }
486
487 private void createSyncrTable(Table t, boolean systemTable) throws SQLException, IOException {
488 createSyncrTable(t, systemTable, true);
489 }
490
491 private void createSyncrTable(Table _t, boolean _systemTable, boolean _constraints) throws SQLException, IOException {
492
493 String tn = _t.getName();
494 if ("DUAL".equalsIgnoreCase(tn)) {
495 SQLConverter.setDualUsedAsTableName(true);
496 }
497 StringBuilder check = new StringBuilder();
498 String ntn = SQLConverter.preEscapingIdentifier(tn);
499
500 int seq = metadata.newTable(tn, ntn, ObjectType.TABLE);
501 ntn = SQLConverter.completeEscaping(ntn);
502 ntn = SQLConverter.checkLang(ntn, conn);
503 ntn = schema(ntn, _systemTable);
504
505 StringBuilder sbC = new StringBuilder("CREATE CACHED TABLE ").append(ntn).append('(');
506
507 String comma = "";
508 for (Column col : _t.getColumns()) {
509 if ("USER".equalsIgnoreCase(col.getName())) {
510 logger.log(Level.WARNING, "You should not use the 'user' reserved word as column name in table {0} "
511 + "(it refers to the database user). "
512 + "Escape it in your SQL e.g. SELECT [user] FROM table WHERE [user] = 'Joe'", _t.getName());
513 }
514 String expr = getExpression(col);
515 if (expr != null && _constraints) {
516 String tgrI = getCalculatedFieldTrigger(ntn, col, true);
517 String tgrU = getCalculatedFieldTrigger(ntn, col, false);
518 calculatedFieldsTriggers.add(String.format(tgrI, NAMING_COUNTER.getAndIncrement(), SQLConverter.convertFormula(expr)));
519 String uc = getUpdateConditions(col);
520 if (!uc.isEmpty()) {
521 calculatedFieldsTriggers.add(String.format(tgrU, NAMING_COUNTER.getAndIncrement(), uc, SQLConverter.convertFormula(expr)));
522 }
523
524 }
525
526 String cn = SQLConverter.preEscapingIdentifier(col.getName());
527 String colType = col.getType().name();
528 if (col.isAutoNumber()) {
529 ColumnImpl cli = (ColumnImpl) col;
530 AutoNumberGenerator ang = cli.getAutoNumberGenerator();
531 if (ang.getType().equals(DataType.LONG)) {
532 colType = "COUNTER";
533 }
534
535 } else if (col.isHyperlink()) {
536 colType = "HYPERLINK";
537 }
538 metadata.newColumn(col.getName(), cn, colType, seq);
539 if (expr != null && _constraints) {
540 metadata.calculatedField(_t.getName(), col.getName());
541 }
542 cn = SQLConverter.completeEscaping(cn);
543 cn = SQLConverter.checkLang(cn, conn);
544 sbC.append(comma).append(cn).append(' ').append(getHsqldbColumnType(col));
545 if (DataType.FLOAT.equals(col.getType())) {
546 check.append(", check (3.4028235E+38>=").append(cn).append(" AND -3.4028235E+38<=").append(cn)
547 .append(")");
548 }
549
550 PropertyMap pm = col.getProperties();
551 Object required = pm.getValue(PropertyMap.REQUIRED_PROP);
552 if (_constraints && required instanceof Boolean && (boolean) required) {
553 sbC.append(" NOT NULL ");
554 }
555 comma = ",";
556 }
557
558 sbC.append(check).append(")");
559 exec(sbC.toString(), true);
560
561 }
562
563 private String getExpression(Column _col) throws IOException {
564 PropertyMap map = _col.getProperties();
565 Property exprp = map.get(PropertyMap.EXPRESSION_PROP);
566
567 if (exprp != null) {
568 Table tl = _col.getTable();
569 String expr = SQLConverter.convertPowOperator((String) exprp.getValue());
570 for (Column col : tl.getColumns()) {
571 expr = expr.replaceAll("\\[(?i)(" + Pattern.quote(col.getName()) + ")\\]", "newrow.$0");
572 }
573 return expr;
574 }
575 return null;
576 }
577
578 private String getUpdateConditions(Column _col) throws IOException, SQLException {
579 PropertyMap map = _col.getProperties();
580 Property exprp = map.get(PropertyMap.EXPRESSION_PROP);
581
582 if (exprp != null) {
583 Set<String> setu = SQLConverter.getFormulaDependencies(exprp.getValue().toString());
584
585 if (!setu.isEmpty()) {
586 String or = "";
587 StringBuilder cw = new StringBuilder();
588 for (String dep : setu) {
589 dep = escapeIdentifier(dep);
590 cw.append(or).append("oldrow.").append(dep).append("<>").append("newrow.").append(dep);
591 or = " OR ";
592 }
593
594 return cw.toString();
595 }
596
597 }
598 return " FALSE ";
599 }
600
601 private String procedureEscapingIdentifier(String name) throws SQLException {
602 return SQLConverter.procedureEscapingIdentifier(escapeIdentifier(name));
603 }
604
605 private String defaultValue4SQL(Object defaulT, DataType dt) {
606 if (defaulT == null) {
607 return null;
608 }
609 String default4SQL = SQLConverter.convertSQL(" " + defaulT).getSql();
610 if (default4SQL.trim().startsWith("=")) {
611 default4SQL = default4SQL.trim().substring(1);
612 }
613 if (dt.equals(DataType.BOOLEAN)
614 && ("=yes".equalsIgnoreCase(default4SQL) || "yes".equalsIgnoreCase(default4SQL))) {
615 default4SQL = "true";
616 }
617 if (dt.equals(DataType.BOOLEAN)
618 && ("=no".equalsIgnoreCase(default4SQL) || "no".equalsIgnoreCase(default4SQL))) {
619 default4SQL = "false";
620 }
621 if ((dt.equals(DataType.MEMO) || dt.equals(DataType.TEXT))
622 && (!defaulT.toString().startsWith("\"") || !defaulT.toString().endsWith("\""))
623
624 ) {
625 default4SQL = "'" + default4SQL.replace("'", "''") + "'";
626 }
627 return default4SQL;
628 }
629
630 private String createTriggerColumnDefault(Column _col, String _ntn) throws IOException, SQLException {
631 PropertyMap pm = _col.getProperties();
632 String ncn = procedureEscapingIdentifier(_col.getName());
633 Object defVal = pm.getValue(PropertyMap.DEFAULT_VALUE_PROP);
634
635 if (defVal != null && !"GenGUID()".equals(defVal)) {
636 String default4SQL = defaultValue4SQL(defVal, _col.getType());
637 boolean defIsFunction = defVal.toString().trim().endsWith(")") && defVal.toString().indexOf('(') > 0;
638 if (defIsFunction) {
639 metadata.columnDef(_col.getTable().getName(), _col.getName(), defVal.toString());
640 }
641 Object defFound = default4SQL;
642 boolean isNull = (default4SQL + "").equalsIgnoreCase("null");
643 if (!isNull && (defFound = tryDefault(default4SQL)) == null) {
644
645 logger.log(Level.WARNING, "Unknown expression: {0} (default value of column {1} table {2})",
646 defVal, _col.getName(), _col.getTable().getName());
647 } else {
648 if (defFound != null && !defIsFunction) {
649 metadata.columnDef(_col.getTable().getName(), _col.getName(), defFound.toString());
650 }
651 if (_col.getType() == DataType.TEXT && defVal.toString().startsWith("'")
652 && defVal.toString().endsWith("'")
653 && defVal.toString().length() > _col.getLengthInUnits()) {
654 logger.log(Level.WARNING, "Default values should start and end with a double quote, "
655 + "the single quote is considered as part of the default value {0} "
656 + "(column {1}, table {2}). It may result in a data truncation error at run-time due to max column size {3}",
657 defVal, _col.getName(), _col.getTable().getName(), _col.getLengthInUnits());
658 }
659 String triggerName = escapeIdentifier("tr_" + _ntn + "_default" + NAMING_COUNTER.getAndIncrement());
660 return "CREATE TRIGGER " + triggerName + " BEFORE INSERT ON " + _ntn
661 + " REFERENCING NEW ROW AS NEW FOR EACH ROW IF NEW." + ncn + " IS NULL THEN"
662 + " SET NEW." + ncn + " = " + default4SQL + "; END IF";
663 }
664 }
665 return null;
666 }
667
668 private void addTriggerColumnDefault(Column _col) throws SQLException, IOException {
669 String tn = escapeIdentifier(_col.getTable().getName());
670 String trigger = createTriggerColumnDefault(_col, tn);
671 if (trigger != null) {
672 exec(trigger, true);
673 }
674 }
675
676 private void addTriggersColumnDefault(Table _table) throws SQLException, IOException {
677 String tn = escapeIdentifier(_table.getName());
678 for (Column col : _table.getColumns()) {
679 String trigger = createTriggerColumnDefault(col, tn);
680 if (trigger != null) {
681 exec(trigger, true);
682 }
683 }
684 }
685
686 private int countFKs() throws IOException {
687 int i = 0;
688 for (String tn : loadingOrder) {
689 UcanaccessTable table = new UcanaccessTable(dbIO.getTable(tn), tn);
690 if (!unresolvedTables.contains(tn)) {
691 for (Index idxi : table.getIndexes()) {
692
693 IndexImpl idx = (IndexImpl) idxi;
694 if (idx.isForeignKey() && !idx.getReference().isPrimaryTable()) {
695 i++;
696 }
697 }
698 }
699 }
700 return i;
701 }
702
703 private boolean reorder() throws IOException {
704 int maxIteration = countFKs() + 1;
705
706 for (int i = 0; i < maxIteration; i++) {
707 boolean change = false;
708 List<String> loadingOrder0 = new ArrayList<>(loadingOrder);
709 for (String tn : loadingOrder0) {
710 UcanaccessTable table = new UcanaccessTable(dbIO.getTable(tn), tn);
711 if (!unresolvedTables.contains(tn)) {
712 for (Index idxi : table.getIndexes()) {
713
714 IndexImpl idx = (IndexImpl) idxi;
715 if (idx.isForeignKey() && !idx.getReference().isPrimaryTable() && !tryReorder(idx)) {
716 change = true;
717 }
718 }
719 }
720 }
721
722 if (!change) {
723 return true;
724 }
725 }
726
727 return false;
728 }
729
730 private boolean tryReorder(Index idxi) throws IOException {
731 IndexImpl idx = (IndexImpl) idxi;
732 String ctn = idx.getTable().getName();
733 String rtn = idx.getReferencedIndex().getTable().getName();
734 int ict = loadingOrder.indexOf(ctn);
735 int irt = loadingOrder.indexOf(rtn);
736 if (ict < irt) {
737 loadingOrder.remove(ctn);
738 loadingOrder.add(irt, ctn);
739 return false;
740 }
741 return true;
742 }
743
744 private void loadForeignKey(Index idxi, String ctn) throws IOException, SQLException {
745 IndexImpl idx = (IndexImpl) idxi;
746 String rtn = idx.getReferencedIndex().getTable().getName();
747 List<IndexData.ColumnDescriptor> cls = idx.getColumns();
748 if (cls.size() == 1) {
749 alreadyIndexed.add(cls.get(0).getColumn());
750 }
751 String ntn = escapeIdentifier(ctn);
752 if (ntn == null) {
753 return;
754 }
755 String nin = escapeIdentifier(ctn + '_' + idx.getName());
756 String colsIdx = commaSeparated(cls, true);
757 String colsIdxRef = commaSeparated(idx.getReferencedIndex().getColumns(), true);
758
759 StringBuilder ci = new StringBuilder("ALTER TABLE ").append(ntn)
760 .append(" ADD CONSTRAINT ").append(nin);
761 String nrt = escapeIdentifier(rtn);
762
763 if (nrt == null) {
764 return;
765 }
766 ci.append(" FOREIGN KEY ").append(colsIdx).append(" REFERENCES ").append(nrt).append(colsIdxRef);
767
768 if (idx.getReference().isCascadeDeletes()) {
769 ci.append(" ON DELETE CASCADE ");
770 }
771 if (idx.getReference().isCascadeUpdates()) {
772 ci.append(" ON UPDATE CASCADE ");
773 }
774 try {
775 exec(ci.toString(), true);
776 } catch (SQLException _ex) {
777 if (_ex.getErrorCode() == HSQL_FK_ALREADY_EXISTS) {
778 logger.log(Level.WARNING, _ex.getMessage());
779 } else {
780 throw _ex;
781 }
782 }
783 loadedIndexes.add("FK on " + ntn + " Columns:" + commaSeparated(cls, false) + " References " + nrt
784 + " Columns:" + commaSeparated(idx.getReferencedIndex().getColumns(), false));
785 }
786
787 private void loadIndex(Index idx, String tn) throws SQLException {
788 String ntn = escapeIdentifier(tn);
789 if (ntn == null) {
790 return;
791 }
792 String nin = idx.getName();
793 nin = escapeIdentifier(tn + '_' + nin);
794 boolean uk = idx.isUnique();
795 boolean pk = idx.isPrimaryKey();
796 if (!uk && !pk && idx.getColumns().size() == 1) {
797 Column col = idx.getColumns().get(0).getColumn();
798 if (alreadyIndexed.contains(col)) {
799 return;
800 }
801 }
802 if (uk && idx.getColumns().size() == 1) {
803 Column col = idx.getColumns().get(0).getColumn();
804 DataType dt = col.getType();
805 if (dt.equals(DataType.COMPLEX_TYPE)) {
806 return;
807 }
808 }
809
810 StringBuilder ci = new StringBuilder("ALTER TABLE ").append(ntn);
811 String colsIdx = commaSeparated(idx.getColumns(), true);
812 if (pk) {
813 ci.append(" ADD PRIMARY KEY ").append(colsIdx);
814 } else if (uk) {
815 ci.append(" ADD CONSTRAINT ").append(nin)
816 .append(" UNIQUE ").append(colsIdx);
817
818 } else {
819 ci = new StringBuilder("CREATE INDEX ").append(nin).append(" ON ").append(ntn).append(colsIdx);
820 }
821 try {
822 exec(ci.toString(), true);
823 } catch (SQLException _ex) {
824 if (HSQL_UK_ALREADY_EXISTS == _ex.getErrorCode()) {
825 return;
826 }
827 if (idx.isUnique()) {
828 for (Index.Column cd : idx.getColumns()) {
829 if (cd.getColumn().getType().equals(DataType.COMPLEX_TYPE)) {
830 return;
831 }
832 }
833 }
834 logger.log(Level.WARNING, _ex.getMessage());
835 return;
836 } catch (Exception _ex) {
837
838 logger.log(Level.WARNING, _ex.getMessage());
839 return;
840 }
841 String pre = pk ? "Primary Key " : uk ? "Index Unique " : "Index";
842 loadedIndexes.add(pre + " on " + tn + " Columns:" + commaSeparated(idx.getColumns(), false));
843
844 }
845
846 private void createTable(Table t) throws SQLException, IOException {
847 createTable(t, false);
848 }
849
850 private void dropTable(Table t, boolean systemTable) throws SQLException {
851 String tn = t.getName();
852
853 String ntn = schema(escapeIdentifier(tn), systemTable);
854 exec("DROP TABLE " + ntn + " CASCADE", false);
855 metadata.dropTable(tn);
856 }
857
858 private void makeTableReadOnly(Table t, boolean systemTable) throws SQLException {
859 String tn = t.getName();
860 readOnlyTables.add(t.getName());
861 String ntn = schema(escapeIdentifier(tn), systemTable);
862 exec("SET TABLE " + ntn + " READONLY TRUE", false);
863 loadedTables.add(tn + " READONLY");
864 }
865
866 private void recreate(Table _t, boolean _systemTable, Row _record, int _errorCode) throws SQLException, IOException {
867 String type = "";
868 switch (_errorCode) {
869 case HSQL_FK_VIOLATION:
870 type = "Foreign Key";
871 break;
872 case HSQL_NOT_NULL:
873 type = "Not Null";
874 break;
875 case HSQL_UK_VIOLATION:
876 type = "Unique";
877 break;
878 default:
879 break;
880 }
881 logger.log(Level.WARNING, "Detected {0} constraint breach, table {1}, record {2}: making the table {3} read-only",
882 type, _t.getName(), _record, _t.getName());
883
884 dropTable(_t, _systemTable);
885 createSyncrTable(_t, _systemTable, false);
886 if (_errorCode != HSQL_FK_VIOLATION) {
887 loadTableFKs(_t.getName(), false);
888 }
889 loadTableData(_t, _systemTable);
890 makeTableReadOnly(_t, _systemTable);
891
892 }
893
894 private void createTable(Table t, boolean systemTable) throws SQLException, IOException {
895 String tn = t.getName();
896 if (tn.indexOf(' ') > 0) {
897 SQLConverter.addWhiteSpacedTableNames(tn);
898 }
899 String ntn = SQLConverter.escapeIdentifier(tn);
900 if (ntn == null) {
901 return;
902 }
903 createSyncrTable(t, systemTable);
904 }
905
906 private boolean hasAppendOnly(Table t) {
907
908 for (Column c : t.getColumns()) {
909 if (c.isAppendOnly()) {
910 return true;
911 }
912 }
913 return false;
914 }
915
916 private void loadTableData(Table t, boolean systemTable) throws IOException, SQLException {
917 loadTableData(t, systemTable, false);
918 }
919
920 @SuppressWarnings("PMD.UseTryWithResources")
921 private void loadTableData(Table _t, boolean _systemTable, boolean _errorCheck) throws IOException, SQLException {
922 TimeZone prevJackcessTimeZone = _t.getDatabase().getTimeZone();
923 _t.getDatabase().setTimeZone(TimeZone.getTimeZone("UTC"));
924 int step = _errorCheck ? 1 : DEFAULT_STEP;
925 int i = 0;
926 PreparedStatement ps = null;
927
928 try {
929 Iterator<Row> it = _t.iterator();
930
931 while (it.hasNext()) {
932 Row row = it.next();
933 if (row == null) {
934 continue;
935 }
936 List<Object> values = new ArrayList<>();
937 if (ps == null) {
938 ps = sqlInsert(_t, row, _systemTable);
939 }
940 for (Map.Entry<String, Object> entry : row.entrySet()) {
941 values.add(value(entry.getValue(), _t, entry.getKey(), row));
942 }
943 tablesLoader.execInsert(ps, values);
944
945 if (_errorCheck || i > 0 && i % step == 0 || !it.hasNext()) {
946 try {
947 ps.executeBatch();
948 } catch (SQLException _ex) {
949 int ec = _ex.getErrorCode();
950 if (!_errorCheck && ec == HSQL_NOT_NULL) {
951 dropTable(_t, _systemTable);
952 createSyncrTable(_t, _systemTable, true);
953 loadTableData(_t, _systemTable, true);
954 } else {
955 if (ec == HSQL_NOT_NULL || ec == HSQL_FK_VIOLATION || ec == HSQL_UK_VIOLATION) {
956 if (ec == HSQL_FK_VIOLATION) {
957 logger.log(Level.WARNING, _ex.getMessage());
958 }
959 recreate(_t, _systemTable, row, _ex.getErrorCode());
960 } else {
961 throw _ex;
962 }
963 }
964 }
965 if (_errorCheck) {
966 conn.rollback();
967 } else {
968 conn.commit();
969 }
970 }
971 i++;
972
973 }
974 if (i != _t.getRowCount() && step != 1) {
975 logger.log(Level.WARNING, "Error in the metadata of the table {0}: the table's row count in metadata is {1} "
976 + "but {2} records have been found and loaded by UCanAccess. "
977 + "All will work fine, but it's better to repair your database",
978 _t.getName(), _t.getRowCount(), i);
979 }
980 } finally {
981 if (ps != null) {
982 ps.close();
983 }
984 }
985 _t.getDatabase().setTimeZone(prevJackcessTimeZone);
986 }
987
988 private void loadTableFKs(String tn, boolean autoref) throws IOException, SQLException {
989 if (readOnlyTables.contains(tn)) {
990 return;
991 }
992 Table t = dbIO.getTable(tn);
993 UcanaccessTable table = new UcanaccessTable(t, tn);
994 if (t != null) {
995 for (Index idxi : table.getIndexes()) {
996
997 IndexImpl idx = (IndexImpl) idxi;
998 if (idx.isForeignKey() && !idx.getReference().isPrimaryTable()) {
999 boolean isAuto = idx.getTable().getName().equals(idx.getReferencedIndex().getTable().getName());
1000 if (autoref && isAuto || !autoref && !isAuto) {
1001 loadForeignKey(idx, tn);
1002 }
1003 }
1004 }
1005 }
1006 }
1007
1008 private void createCalculatedFieldsTriggers() {
1009 calculatedFieldsTriggers.forEach(t -> Try.catching(() -> exec(t, false))
1010 .orElse(e -> logger.log(Level.WARNING, e.getMessage())));
1011 }
1012
1013 private void loadTableIndexesUK(String tn) throws IOException, SQLException {
1014 Table t = dbIO.getTable(tn);
1015 UcanaccessTable table = new UcanaccessTable(t, tn);
1016 if (t != null) {
1017 for (Index idx : table.getIndexes()) {
1018 if (!idx.isForeignKey() && (idx.isPrimaryKey() || idx.isUnique())) {
1019 loadIndex(idx, tn);
1020 }
1021 }
1022 }
1023
1024 }
1025
1026 private void loadTableIndexesNotUK(String tn) throws IOException, SQLException {
1027 Table t = dbIO.getTable(tn);
1028 UcanaccessTable table = new UcanaccessTable(t, tn);
1029 if (!skipIndexes && t != null) {
1030 for (Index idx : table.getIndexes()) {
1031 if (!idx.isForeignKey() && !idx.isPrimaryKey() && !idx.isUnique()) {
1032 loadIndex(idx, tn);
1033 }
1034 }
1035 }
1036
1037 }
1038
1039 private void createTables() throws SQLException, IOException {
1040
1041 metadata.createMetadata();
1042
1043 for (String tn : dbIO.getTableNames()) {
1044 if (tn.startsWith("~")) {
1045 logger.log(Level.DEBUG, "Skipping table '{0}'", tn);
1046 continue;
1047 }
1048
1049 try {
1050 Table jt = dbIO.getTable(tn);
1051 UcanaccessTable ut = new UcanaccessTable(jt, tn);
1052
1053 if (TableMetaData.Type.LINKED_ODBC == jt.getDatabase().getTableMetaData(tn).getType()) {
1054 logger.log(Level.WARNING, "Skipping table '{0}' (linked to an ODBC table)", tn);
1055 unresolvedTables.add(tn);
1056 continue;
1057 }
1058
1059 createTable(ut);
1060 loadingOrder.add(ut.getName());
1061
1062 } catch (Exception _ex) {
1063 logger.log(Level.WARNING, "Failed to create table '{0}': {1}", tn, _ex.getMessage());
1064 unresolvedTables.add(tn);
1065 continue;
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 HashMap<>();
1282 private final Map<String, String> notLoadedProcedure = new HashMap<>();
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 q, String queryWKT) throws SQLException {
1326 String qnn = SQLConverter.preEscapingIdentifier(q.getName());
1327 if (qnn == null) {
1328 return false;
1329 }
1330 int seq = metadata.newTable(q.getName(), qnn, ObjectType.VIEW);
1331 registerQueryColumns(q, seq);
1332 qnn = SQLConverter.completeEscaping(qnn, false);
1333 qnn = SQLConverter.checkLang(qnn, conn, false);
1334 if (qnn.indexOf(' ') > 0) {
1335 SQLConverter.addWhiteSpacedTableNames(q.getName());
1336 }
1337
1338 String querySQL = queryWKT == null ? q.toSQLString() : queryWKT;
1339 Pivot pivot = null;
1340 boolean isPivot = q.getType().equals(Query.Type.CROSS_TAB);
1341 if (isPivot) {
1342 pivot = new Pivot(conn);
1343
1344 if (!pivot.parsePivot(querySQL) || (querySQL = pivot.toSQL(q.getName())) == null) {
1345 notLoaded.put(q.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(q.getName());
1362 notLoaded.remove(q.getName());
1363 if (pivot != null) {
1364 pivot.registerPivot(SQLConverter.preEscapingIdentifier(q.getName()));
1365 }
1366 return true;
1367 } catch (Exception _ex) {
1368 if (_ex instanceof SQLSyntaxErrorException) {
1369 if (queryWKT == null && ((SQLSyntaxErrorException) _ex).getErrorCode() == OBJECT_ALREADY_EXISTS) {
1370 return loadView(q, solveAmbiguous(querySQL));
1371 } else {
1372 SQLSyntaxErrorException sqle = (SQLSyntaxErrorException) _ex;
1373 if (sqle.getErrorCode() == OBJECT_NOT_FOUND || sqle.getErrorCode() == UNEXPECTED_TOKEN) {
1374 ParametricQuery pq = new ParametricQuery(conn, (QueryImpl) q);
1375 pq.setIssueWithParameterName(sqle.getErrorCode() == UNEXPECTED_TOKEN);
1376 pq.createSelect();
1377 if (pq.loaded()) {
1378 loadedQueries.add(q.getName());
1379 notLoaded.remove(q.getName());
1380 return true;
1381 }
1382
1383 }
1384 }
1385 }
1386
1387 String cause = UcanaccessSQLException.explainCause(_ex);
1388
1389 notLoaded.put(q.getName(), ": " + cause);
1390
1391 if (!err) {
1392 logger.log(Level.WARNING, "Error occured at the first loading attempt of {0}", q.getName());
1393 logger.log(Level.WARNING, "Converted view was: {0}", v);
1394 logger.log(Level.WARNING, "Error message was: {0}", _ex.getMessage());
1395 err = true;
1396 }
1397 return false;
1398 }
1399 }
1400
1401 @SuppressWarnings("java:S5852")
1402 private String solveAmbiguous(String sql) {
1403 try {
1404 sql = sql.replaceAll("\\s+", " ");
1405 Pattern pat = Pattern.compile("(.*)\\s+SELECT(\\s.*\\s)FROM(\\s)(.*)", Pattern.CASE_INSENSITIVE);
1406 Matcher mtc = pat.matcher(sql);
1407 if (mtc.find()) {
1408 String select = mtc.group(2);
1409 String pre = mtc.group(1) == null ? "" : mtc.group(1);
1410 String[] split = select.split(",", -1);
1411 StringBuilder sb = new StringBuilder(pre).append(" select ");
1412 List<String> lkl = new LinkedList<>();
1413
1414 Pattern patAlias = Pattern.compile("\\s+AS\\s+", Pattern.CASE_INSENSITIVE);
1415 for (String s : split) {
1416 int i = s.lastIndexOf('.');
1417 boolean alias = patAlias.matcher(s).find();
1418 if (i < 0 || alias) {
1419 lkl.add(s);
1420 } else {
1421 String k = s.substring(i + 1);
1422 if (lkl.contains(k)) {
1423 int idx = lkl.indexOf(k);
1424 String old = lkl.get(lkl.indexOf(k));
1425 lkl.remove(old);
1426 lkl.add(idx, split[idx] + " AS [" + split[idx].trim() + ']');
1427 lkl.add(s + " AS [" + s.trim() + ']');
1428 } else {
1429 lkl.add(k);
1430 }
1431 }
1432 }
1433 String comma = "";
1434 for (String s : lkl) {
1435 sb.append(comma).append(s);
1436
1437 comma = ",";
1438 }
1439 sb.append(" FROM ").append(mtc.group(4));
1440
1441 return sb.toString();
1442 } else {
1443 return sql;
1444 }
1445 } catch (Exception _ex) {
1446 return sql;
1447 }
1448 }
1449
1450 private void loadViews() throws SQLException {
1451 List<Query> lq = null;
1452 List<Query> procedures = new ArrayList<>();
1453 try {
1454 lq = dbIO.getQueries();
1455 Iterator<Query> it = lq.iterator();
1456 while (it.hasNext()) {
1457 Query q = it.next();
1458 if (!q.getType().equals(Query.Type.SELECT) && !q.getType().equals(Query.Type.UNION)
1459 && !q.getType().equals(Query.Type.CROSS_TAB)) {
1460 procedures.add(q);
1461 it.remove();
1462 }
1463
1464 }
1465 queryPorting(lq);
1466 } catch (Exception _ex) {
1467 notLoaded.put("", "");
1468 }
1469 loadProcedures(procedures);
1470
1471 }
1472
1473 private void loadProcedures(List<Query> procedures) {
1474 for (Query q : procedures) {
1475 ParametricQuery pq = new ParametricQuery(conn, (QueryImpl) q);
1476 if (!q.getType().equals(Query.Type.DATA_DEFINITION)) {
1477 pq.createProcedure();
1478 if (pq.loaded()) {
1479 loadedProcedures.add(pq.getSignature());
1480
1481 } else {
1482 String msg = pq.getException() == null ? "" : pq.getException().getMessage();
1483 notLoadedProcedure.put(q.getName(), msg);
1484
1485 }
1486
1487 }
1488 }
1489 }
1490
1491 private void queryPorting(List<Query> lq) throws SQLException {
1492 List<String> arn = new ArrayList<>();
1493 for (Query q : lq) {
1494 arn.add(q.getName().toLowerCase());
1495 }
1496 boolean heavy = false;
1497 while (!lq.isEmpty()) {
1498 List<Query> arq = new ArrayList<>();
1499 for (Query q : lq) {
1500 String qtxt = null;
1501 boolean qryGot = true;
1502 try {
1503 qtxt = q.toSQLString().toLowerCase();
1504 } catch (Exception _ignored) {
1505 qryGot = false;
1506 }
1507 boolean foundDep = false;
1508 if (qryGot && !heavy) {
1509 for (String name : arn) {
1510 if (qtxt.contains(name)) {
1511 foundDep = true;
1512 break;
1513 }
1514 }
1515 }
1516 if (qryGot && !foundDep && loadView(q)) {
1517 arq.add(q);
1518 arn.remove(q.getName().toLowerCase());
1519 }
1520 }
1521 if (arq.isEmpty()) {
1522 if (heavy) {
1523 break;
1524 } else {
1525 heavy = true;
1526 }
1527 }
1528 lq.removeAll(arq);
1529 }
1530 Pivot.clearPrepared();
1531 }
1532 }
1533
1534 }