Revision 44376 trunk/org.gvsig.desktop/org.gvsig.desktop.compat.cdc/org.gvsig.fmap.dal/org.gvsig.fmap.dal.db/org.gvsig.fmap.dal.db.jdbc/src/main/java/org/gvsig/fmap/dal/store/jdbc2/spi/JDBCHelperBase.java
JDBCHelperBase.java | ||
---|---|---|
5 | 5 |
import org.gvsig.fmap.dal.store.jdbc2.spi.operations.OperationsFactoryBase; |
6 | 6 |
import java.sql.Connection; |
7 | 7 |
import java.sql.ResultSet; |
8 |
import java.util.ArrayList; |
|
9 |
import java.util.List; |
|
10 |
import org.apache.commons.lang3.ArrayUtils; |
|
8 | 11 |
import org.apache.commons.lang3.StringUtils; |
9 | 12 |
import org.apache.commons.lang3.mutable.MutableBoolean; |
10 | 13 |
import org.gvsig.expressionevaluator.Code; |
14 |
import org.gvsig.expressionevaluator.ExpressionBuilder; |
|
11 | 15 |
import org.gvsig.expressionevaluator.ExpressionBuilder.GeometrySupportType; |
12 | 16 |
import static org.gvsig.expressionevaluator.ExpressionBuilder.GeometrySupportType.EWKB; |
13 | 17 |
import static org.gvsig.expressionevaluator.ExpressionBuilder.GeometrySupportType.NATIVE; |
... | ... | |
18 | 22 |
import org.gvsig.expressionevaluator.Function; |
19 | 23 |
import org.gvsig.expressionevaluator.SymbolTable; |
20 | 24 |
import org.gvsig.fmap.dal.DataTypes; |
25 |
import org.gvsig.fmap.dal.SQLBuilder; |
|
21 | 26 |
import org.gvsig.fmap.dal.exception.DataException; |
22 | 27 |
import org.gvsig.fmap.dal.exception.InitializeException; |
23 | 28 |
import org.gvsig.fmap.dal.feature.FeatureAttributeDescriptor; |
24 | 29 |
import org.gvsig.fmap.dal.feature.FeatureQueryOrder; |
25 | 30 |
import org.gvsig.fmap.dal.feature.FeatureType; |
31 |
import org.gvsig.fmap.dal.feature.ForeingKey; |
|
26 | 32 |
|
27 | 33 |
import org.gvsig.fmap.dal.feature.spi.FeatureProvider; |
34 |
import org.gvsig.fmap.dal.feature.spi.SQLBuilderBase; |
|
28 | 35 |
import org.gvsig.fmap.dal.resource.exception.AccessResourceException; |
29 | 36 |
import org.gvsig.fmap.dal.resource.spi.ResourceConsumer; |
30 | 37 |
import org.gvsig.fmap.dal.resource.spi.ResourceProvider; |
... | ... | |
160 | 167 |
return QUOTE_FOR_USE_IN_STRINGS; |
161 | 168 |
} |
162 | 169 |
|
170 |
protected boolean supportCaller(Code.Caller caller) { |
|
171 |
if( StringUtils.equalsIgnoreCase(caller.name(), "FOREING_VALUE") ) { |
|
172 |
return true; |
|
173 |
} |
|
174 |
Function function = caller.function(); |
|
175 |
if( function==null ) { |
|
176 |
return false; |
|
177 |
} |
|
178 |
if( !function.isSQLCompatible() ) { |
|
179 |
return false; |
|
180 |
} |
|
181 |
if( !this.hasSpatialFunctions() ) { |
|
182 |
if( StringUtils.equalsIgnoreCase(function.group(),Function.GROUP_OGC) ) { |
|
183 |
return false; |
|
184 |
} |
|
185 |
} |
|
186 |
return true; |
|
187 |
} |
|
188 |
|
|
163 | 189 |
@Override |
164 | 190 |
public boolean supportFilter(final FeatureType type, Evaluator filter) { |
165 | 191 |
// No podemos filtrar cuando: |
... | ... | |
194 | 220 |
// proveedor dice que no soporta funciones espaciales. |
195 | 221 |
// Tambien comprobaremos que el filtro no usa ningun campo calculado. |
196 | 222 |
final MutableBoolean isCompatible = new MutableBoolean(true); |
197 |
final boolean supportSpatialFunctions = this.hasSpatialFunctions(); |
|
198 | 223 |
ExpressionEvaluatorManager manager = ExpressionEvaluatorLocator.getManager(); |
199 | 224 |
Code code = manager.compile(sql); |
200 | 225 |
SymbolTable symbolTable = manager.createSymbolTable(); |
... | ... | |
207 | 232 |
switch(code.code()) { |
208 | 233 |
case Code.CALLER: |
209 | 234 |
Code.Caller caller = (Code.Caller) code; |
210 |
Function function = caller.function(); |
|
211 |
if( function==null ) { |
|
235 |
if( !supportCaller(caller) ) { |
|
212 | 236 |
isCompatible.setValue(false); |
213 | 237 |
throw new VisitCanceledException(); |
214 | 238 |
} |
215 |
if( !function.isSQLCompatible() ) { |
|
216 |
isCompatible.setValue(false); |
|
217 |
throw new VisitCanceledException(); |
|
218 |
} |
|
219 |
if( !supportSpatialFunctions ) { |
|
220 |
if( StringUtils.equalsIgnoreCase(function.group(),Function.GROUP_OGC) ) { |
|
221 |
isCompatible.setValue(false); |
|
222 |
throw new VisitCanceledException(); |
|
223 |
} |
|
224 |
} |
|
225 | 239 |
break; |
226 | 240 |
|
227 | 241 |
case Code.IDENTIFIER: |
... | ... | |
398 | 412 |
|
399 | 413 |
@Override |
400 | 414 |
public void fetchFeature(FeatureProvider feature, ResultSetEntry rs) throws DataException { |
401 |
fetchFeature(feature, rs.get(), rs.getColumns()); |
|
415 |
fetchFeature(feature, rs.get(), rs.getColumns(), rs.getExtraValueNames());
|
|
402 | 416 |
} |
403 | 417 |
|
404 | 418 |
@Override |
405 |
public void fetchFeature(FeatureProvider feature, ResultSet rs, FeatureAttributeDescriptor[] columns) throws DataException { |
|
419 |
public void fetchFeature(FeatureProvider feature, ResultSet rs, FeatureAttributeDescriptor[] columns, String[] extraValueNames) throws DataException {
|
|
406 | 420 |
Object value; |
407 |
FeatureAttributeDescriptor column; |
|
408 | 421 |
try { |
409 |
for (int index = 0; index < columns.length; index++) {
|
|
410 |
column = columns[index];
|
|
422 |
int rsIndex = 1;
|
|
423 |
for (FeatureAttributeDescriptor column : columns) {
|
|
411 | 424 |
switch (column.getType()) { |
412 | 425 |
case DataTypes.GEOMETRY: |
413 |
value = this.getGeometryFromColumn(rs, index + 1);
|
|
426 |
value = this.getGeometryFromColumn(rs, rsIndex++);
|
|
414 | 427 |
break; |
415 | 428 |
default: |
416 |
value = rs.getObject(index + 1);
|
|
429 |
value = rs.getObject(rsIndex++);
|
|
417 | 430 |
if( value instanceof Blob ) { |
418 | 431 |
Blob blob = (Blob)value; |
419 | 432 |
value = blob.getBytes(0, (int) blob.length()); |
... | ... | |
422 | 435 |
} |
423 | 436 |
feature.set(column.getIndex(), value); |
424 | 437 |
} |
438 |
if( ArrayUtils.isNotEmpty(extraValueNames) ) { |
|
439 |
feature.setExtraValueNames(extraValueNames); |
|
440 |
for (int index = 0; index < extraValueNames.length; index++) { |
|
441 |
value = rs.getObject(rsIndex++); |
|
442 |
if( value instanceof Blob ) { |
|
443 |
Blob blob = (Blob)value; |
|
444 |
value = blob.getBytes(0, (int) blob.length()); |
|
445 |
blob.free(); |
|
446 |
} |
|
447 |
feature.setExtraValue(index, value); |
|
448 |
} |
|
449 |
} |
|
425 | 450 |
} catch (Exception ex) { |
426 | 451 |
throw new JDBCCantFetchValueException(ex); |
427 | 452 |
} |
... | ... | |
542 | 567 |
return true; |
543 | 568 |
} |
544 | 569 |
|
570 |
@Override |
|
571 |
public String[] replaceForeingValueFunction(SQLBuilder sqlbuilder, FeatureType type) { |
|
572 |
// See test SQLBuilderTest->testForeingValue() |
|
573 |
final ExpressionBuilder where = sqlbuilder.select().where(); |
|
574 |
if( where == null || where.isEmpty() ) { |
|
575 |
return null; |
|
576 |
} |
|
577 |
final SQLBuilder.TableNameBuilder table = sqlbuilder.select().from().table(); |
|
578 |
final ExpressionBuilder expbuilder = sqlbuilder.expression(); |
|
579 |
|
|
580 |
final List<String> foreing_value_args = new ArrayList<>(); |
|
581 |
final List<ExpressionBuilder.Value[]> value_replacements = new ArrayList<>(); |
|
582 |
|
|
583 |
// Buscamos las llamadas a la funcion "foreing_value" y nos quedamos |
|
584 |
// el argumento de esta asi como por que tendriamos que sustituirla |
|
585 |
// una vez hechos los left joins que toquen. |
|
586 |
where.accept(new ExpressionBuilder.Visitor() { |
|
587 |
@Override |
|
588 |
public void visit(ExpressionBuilder.Visitable value) { |
|
589 |
// Requiere que sea la funcion "FOREING_VALUE con un solo |
|
590 |
// argumento que sea una constante de tipo string. |
|
591 |
if( !(value instanceof ExpressionBuilder.Function) ) { |
|
592 |
return; |
|
593 |
} |
|
594 |
ExpressionBuilder.Function function = (ExpressionBuilder.Function) value; |
|
595 |
if( !StringUtils.equalsIgnoreCase(function.name(), "FOREING_VALUE") ) { |
|
596 |
return; |
|
597 |
} |
|
598 |
if( function.parameters().size()!=1 ) { |
|
599 |
return; |
|
600 |
} |
|
601 |
ExpressionBuilder.Value arg = function.parameters().get(0); |
|
602 |
if( !(arg instanceof ExpressionBuilder.Constant) ) { |
|
603 |
return; |
|
604 |
} |
|
605 |
Object arg_value = ((ExpressionBuilder.Constant) arg).value(); |
|
606 |
if( !(arg_value instanceof CharSequence) ) { |
|
607 |
return; |
|
608 |
} |
|
609 |
String foreing_value_arg = arg_value.toString(); |
|
610 |
String[] foreingNameParts = StringUtils.split(foreing_value_arg, "[.]"); |
|
611 |
if( foreingNameParts.length!=2 ) { |
|
612 |
// De momento solo tratamos joins entre dos tablas. |
|
613 |
return; |
|
614 |
} |
|
615 |
String columnNameLocal = foreingNameParts[0]; |
|
616 |
String columnNameForeing = foreingNameParts[1]; |
|
617 |
FeatureAttributeDescriptor attr = type.getAttributeDescriptor(columnNameLocal); |
|
618 |
if( !attr.isForeingKey() ) { |
|
619 |
// Uhm... si el argumento no referencia a un campo que es |
|
620 |
// clave ajena no lo procesamos. |
|
621 |
// ? Deberiamos lanzar un error ? |
|
622 |
return; |
|
623 |
} |
|
624 |
// Nos guardaremos por que hay que reemplazar la funcion |
|
625 |
// FOREING_VALUE, y su argumento para mas tarde. |
|
626 |
ForeingKey foreingKey = attr.getForeingKey(); |
|
627 |
SQLBuilder.TableNameBuilder foreingTable = sqlbuilder.createTableNameBuilder() |
|
628 |
.database(table.getDatabase()) |
|
629 |
.schema(table.getSchema()) |
|
630 |
.name(foreingKey.getTableName()); |
|
631 |
// Reemplzaremos la funcion FOREING_VALUE, por el acceso al campo |
|
632 |
// que toca de la tabla a la que referencia la clave ajena. |
|
633 |
ExpressionBuilder.Variable function_replacement = sqlbuilder.column(foreingTable, columnNameForeing); |
|
634 |
value_replacements.add( |
|
635 |
new ExpressionBuilder.Value[] { |
|
636 |
function, |
|
637 |
function_replacement |
|
638 |
} |
|
639 |
); |
|
640 |
foreing_value_args.add(foreing_value_arg); |
|
641 |
} |
|
642 |
}, null); |
|
643 |
|
|
644 |
// Si no habia ningun llamada a la funcion FOREING_VALUE, no hay que |
|
645 |
// hacer nada. |
|
646 |
if( foreing_value_args.isEmpty() ) { |
|
647 |
return null; |
|
648 |
} |
|
649 |
|
|
650 |
// Calculamos que referencias de columnas hemos de cambiar para |
|
651 |
// que no aparezcan ambiguedades al hacer el join (nombres de |
|
652 |
// columna de una y otra tabla que coincidan). |
|
653 |
// Para las columnas que puedan dar conflicto se prepara un reemplazo |
|
654 |
// de estas que tenga el nombre de tabla. |
|
655 |
for (ExpressionBuilder.Variable variable : sqlbuilder.variables()) { |
|
656 |
if( variable instanceof SQLBuilderBase.ColumnBase ) { |
|
657 |
continue; |
|
658 |
} |
|
659 |
for (String foreingName : foreing_value_args) { |
|
660 |
String[] foreingNameParts = foreingName.split("[.]"); |
|
661 |
if( foreingNameParts.length != 2) { |
|
662 |
continue; |
|
663 |
} |
|
664 |
String columnNameLocal = foreingNameParts[0]; |
|
665 |
String columnNameForeing = foreingNameParts[1]; |
|
666 |
if( !StringUtils.equalsIgnoreCase(variable.name(), columnNameForeing) ) { |
|
667 |
continue; |
|
668 |
} |
|
669 |
ExpressionBuilder.Variable variable_replacement = sqlbuilder.column( |
|
670 |
table, |
|
671 |
variable.name() |
|
672 |
); |
|
673 |
value_replacements.add( |
|
674 |
new ExpressionBuilder.Value[] { |
|
675 |
variable, |
|
676 |
variable_replacement |
|
677 |
} |
|
678 |
); |
|
679 |
} |
|
680 |
} |
|
681 |
|
|
682 |
// Realizamos los reemplazos calculados previamente (value_replacements). |
|
683 |
for (ExpressionBuilder.Value[] replaceValue : value_replacements) { |
|
684 |
ExpressionBuilder.Value target = replaceValue[0]; |
|
685 |
ExpressionBuilder.Value replacement = replaceValue[1]; |
|
686 |
sqlbuilder.select().replace(target, replacement); |
|
687 |
} |
|
688 |
|
|
689 |
// A?adimos a la SQL los "LEFT JOIN" que toca para poder acceder |
|
690 |
// a los valores referenciados por la funcion FOREING_VALUE. |
|
691 |
// Ademas a?adimos los valores referenciados por la funcion FOREING_VALUE |
|
692 |
// como columnas de la SQL para poder acceder a ellos si fuese necesario. |
|
693 |
for (String foreingName : foreing_value_args) { |
|
694 |
String[] foreingNameParts = foreingName.split("[.]"); |
|
695 |
if( foreingNameParts.length != 2) { |
|
696 |
continue; |
|
697 |
} |
|
698 |
String columnNameLocal = foreingNameParts[0]; |
|
699 |
String columnNameForeing = foreingNameParts[1]; |
|
700 |
FeatureAttributeDescriptor attr = type.getAttributeDescriptor(columnNameLocal); |
|
701 |
if( attr.isForeingKey() ) { |
|
702 |
ForeingKey foreingKey = attr.getForeingKey(); |
|
703 |
SQLBuilder.TableNameBuilder foreingTable = sqlbuilder.createTableNameBuilder() |
|
704 |
.database(table.getDatabase()) |
|
705 |
.schema(table.getSchema()) |
|
706 |
.name(foreingKey.getTableName()); |
|
707 |
SQLBuilder.TableNameBuilder mainTable = sqlbuilder.createTableNameBuilder() |
|
708 |
.database(table.getDatabase()) |
|
709 |
.schema(table.getSchema()) |
|
710 |
.name(table.getName()); |
|
711 |
|
|
712 |
sqlbuilder.select().from() |
|
713 |
.left_join( |
|
714 |
foreingTable, |
|
715 |
expbuilder.eq( |
|
716 |
sqlbuilder.column(mainTable, columnNameLocal), |
|
717 |
sqlbuilder.column(foreingTable,foreingKey.getCodeName()) |
|
718 |
) |
|
719 |
); |
|
720 |
sqlbuilder.select().column().name(foreingTable,columnNameForeing); |
|
721 |
} |
|
722 |
} |
|
723 |
|
|
724 |
return foreing_value_args.toArray(new String[foreing_value_args.size()]); |
|
725 |
} |
|
545 | 726 |
} |
Also available in: Unified diff