Revision 46105
| 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/test/java/org/gvsig/fmap/dal/store/jdbc2/AbstractTestUtils.java | ||
|---|---|---|
| 19 | 19 |
import org.gvsig.fmap.dal.DataServerExplorerParameters; |
| 20 | 20 |
import org.gvsig.fmap.dal.DataStore; |
| 21 | 21 |
import static org.gvsig.fmap.dal.DataStore.H2SPATIAL_PROVIDER_NAME; |
| 22 |
import org.gvsig.fmap.dal.DataStoreParameters; |
|
| 22 | 23 |
import org.gvsig.fmap.dal.DatabaseWorkspaceManager; |
| 23 | 24 |
import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.FIELD_RESOURCES_NAME; |
| 24 | 25 |
import static org.gvsig.fmap.dal.DatabaseWorkspaceManager.TABLE_RESOURCES_NAME; |
| ... | ... | |
| 420 | 421 |
return explorer; |
| 421 | 422 |
} |
| 422 | 423 |
|
| 423 |
public void drop_tables(JDBCServerExplorer explorer, String...tables) {
|
|
| 424 |
public void drop_tables(JDBCServerExplorer explorer, String...tables) throws Exception {
|
|
| 424 | 425 |
for (String table : tables) {
|
| 425 | 426 |
String sql = "DROP TABLE IF EXISTS \""+table+"\""; |
| 426 | 427 |
explorer.execute(sql); |
| 428 |
removeResource(explorer, table, "dal"); |
|
| 427 | 429 |
} |
| 428 | 430 |
} |
| 429 | 431 |
|
| 432 |
public void initWorkspace(String name) throws Exception {
|
|
| 433 |
JDBCServerExplorer explorer = this.openServerExplorer(name); |
|
| 434 |
DataManager manager = DALLocator.getDataManager(); |
|
| 435 |
DatabaseWorkspaceManager ws = manager.createDatabaseWorkspaceManager(explorer.getParameters()); |
|
| 436 |
ws.connect(); |
|
| 437 |
if (!ws.existsTable(DatabaseWorkspaceManager.TABLE_CONFIGURATION)) {
|
|
| 438 |
ws.createTable(DatabaseWorkspaceManager.TABLE_CONFIGURATION); |
|
| 439 |
} |
|
| 440 |
if (!ws.existsTable(DatabaseWorkspaceManager.TABLE_RESOURCES)) {
|
|
| 441 |
ws.createTable(DatabaseWorkspaceManager.TABLE_RESOURCES); |
|
| 442 |
} |
|
| 443 |
} |
|
| 444 |
|
|
| 430 | 445 |
// public abstract String getExpectedResourcesPrefix(); // Ex. "h2spatial" |
| 431 | 446 |
|
| 432 | 447 |
public abstract String getExpectedsPath(); |
| 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/test/java/org/gvsig/fmap/dal/store/jdbc2/AbstractTestComputedAttributes.java | ||
|---|---|---|
| 19 | 19 |
import org.slf4j.LoggerFactory; |
| 20 | 20 |
|
| 21 | 21 |
public abstract class AbstractTestComputedAttributes extends TestCase {
|
| 22 |
|
|
| 22 |
|
|
| 23 | 23 |
protected static final Logger LOGGER = LoggerFactory.getLogger(AbstractTestComputedAttributes.class); |
| 24 | 24 |
|
| 25 | 25 |
protected AbstractTestUtils utils; |
| ... | ... | |
| 27 | 27 |
public AbstractTestComputedAttributes(String testName) {
|
| 28 | 28 |
super(testName); |
| 29 | 29 |
} |
| 30 |
|
|
| 30 |
|
|
| 31 | 31 |
@Override |
| 32 | 32 |
protected void setUp() throws Exception {
|
| 33 | 33 |
super.setUp(); |
| 34 | 34 |
new DefaultLibrariesInitializer().fullInitialize(); |
| 35 | 35 |
} |
| 36 |
|
|
| 36 |
|
|
| 37 | 37 |
@Override |
| 38 | 38 |
protected void tearDown() throws Exception {
|
| 39 | 39 |
super.tearDown(); |
| 40 | 40 |
} |
| 41 |
|
|
| 41 |
|
|
| 42 | 42 |
public AbstractTestUtils utils() {
|
| 43 | 43 |
if (this.utils == null) {
|
| 44 | 44 |
this.utils = this.createUtils(); |
| ... | ... | |
| 47 | 47 |
} |
| 48 | 48 |
|
| 49 | 49 |
protected abstract AbstractTestUtils createUtils(); |
| 50 |
|
|
| 50 |
|
|
| 51 | 51 |
// TODO add test methods here. The name must begin with 'test'. For example: |
| 52 | 52 |
// public void testHello() {}
|
| 53 |
|
|
| 54 | 53 |
public void testComputed1() throws Exception {
|
| 55 |
if( !utils().isTheDatabaseAvailable() ) {
|
|
| 56 |
return; |
|
| 57 |
} |
|
| 58 |
FeatureStore sourceStore = utils().openSourceStore2(); |
|
| 59 |
JDBCServerExplorer explorer = utils().openServerExplorer("testCreate");
|
|
| 54 |
try {
|
|
| 55 |
if (!utils().isTheDatabaseAvailable()) {
|
|
| 56 |
return; |
|
| 57 |
} |
|
| 58 |
FeatureStore sourceStore = utils().openSourceStore2(); |
|
| 59 |
JDBCServerExplorer explorer = utils().openServerExplorer("testCreate");
|
|
| 60 |
utils().initWorkspace("testCreate");
|
|
| 61 |
|
|
| 62 |
utils().info_jdbc(explorer); |
|
| 60 | 63 |
|
| 61 |
utils().info_jdbc(explorer); |
|
| 64 |
utils().drop_tables(explorer, "testCreateTarget2"); |
|
| 65 |
utils().create_table_from(explorer, "testCreateTarget2", sourceStore); |
|
| 66 |
utils().insert_into_from(explorer, "testCreateTarget2", sourceStore, FeatureStore.MODE_APPEND); |
|
| 62 | 67 |
|
| 63 |
utils().drop_tables(explorer, "testCreateTarget2"); |
|
| 64 |
utils().create_table_from(explorer, "testCreateTarget2", sourceStore); |
|
| 65 |
utils().insert_into_from(explorer, "testCreateTarget2", sourceStore, FeatureStore.MODE_APPEND); |
|
| 66 |
|
|
| 67 |
FeatureStore dbstore = utils().openStore(explorer,"testCreateTarget2"); |
|
| 68 |
dbstore.edit(); |
|
| 69 |
FeatureType featureType = dbstore.getDefaultFeatureType(); |
|
| 70 |
EditableFeatureType eFeatureType = featureType.getEditable(); |
|
| 71 |
eFeatureType.add("Compu1",
|
|
| 72 |
DataTypes.INTEGER, |
|
| 73 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 74 |
eFeatureType, |
|
| 75 |
ExpressionUtils.createExpression("ID*2")
|
|
| 76 |
)); |
|
| 77 |
eFeatureType.add("Compu2",
|
|
| 78 |
DataTypes.INTEGER, |
|
| 79 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 80 |
eFeatureType, |
|
| 81 |
ExpressionUtils.createExpression("Poblacion+10000+Compu1")
|
|
| 82 |
)); |
|
| 83 |
dbstore.update(eFeatureType); |
|
| 84 |
dbstore.finishEditing(); |
|
| 85 |
List<Feature> features = dbstore.getFeatures(); |
|
| 86 |
for (int i = 0; i < features.size(); i++) {
|
|
| 87 |
Feature feature = features.get(i); |
|
| 88 |
assertEquals("Compu1 "+i, feature.getInt("ID") * 2, feature.getInt("Compu1"));
|
|
| 89 |
if(feature.get("Poblacion")==null) {
|
|
| 90 |
assertEquals("Compu2 "+i, null, feature.get("Compu2"));
|
|
| 91 |
} else {
|
|
| 92 |
assertEquals("Compu2 "+i, feature.getInt("Poblacion") + 10000 + feature.getInt("Compu1"), feature.getInt("Compu2"));
|
|
| 68 |
FeatureStore dbstore = utils().openStore(explorer, "testCreateTarget2"); |
|
| 69 |
dbstore.edit(); |
|
| 70 |
FeatureType featureType = dbstore.getDefaultFeatureType(); |
|
| 71 |
EditableFeatureType eFeatureType = featureType.getEditable(); |
|
| 72 |
eFeatureType.add("Compu1",
|
|
| 73 |
DataTypes.INTEGER, |
|
| 74 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 75 |
eFeatureType, |
|
| 76 |
ExpressionUtils.createExpression("ID*2")
|
|
| 77 |
)); |
|
| 78 |
eFeatureType.add("Compu2",
|
|
| 79 |
DataTypes.INTEGER, |
|
| 80 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 81 |
eFeatureType, |
|
| 82 |
ExpressionUtils.createExpression("Poblacion+10000+Compu1")
|
|
| 83 |
)); |
|
| 84 |
dbstore.update(eFeatureType); |
|
| 85 |
dbstore.finishEditing(); |
|
| 86 |
List<Feature> features = dbstore.getFeatures(); |
|
| 87 |
for (int i = 0; i < features.size(); i++) {
|
|
| 88 |
Feature feature = features.get(i); |
|
| 89 |
assertEquals("Compu1 " + i, feature.getInt("ID") * 2, feature.getInt("Compu1"));
|
|
| 90 |
if (feature.get("Poblacion") == null) {
|
|
| 91 |
assertEquals("Compu2 " + i, null, feature.get("Compu2"));
|
|
| 92 |
} else {
|
|
| 93 |
assertEquals("Compu2 " + i, feature.getInt("Poblacion") + 10000 + feature.getInt("Compu1"), feature.getInt("Compu2"));
|
|
| 94 |
} |
|
| 93 | 95 |
} |
| 96 |
DisposeUtils.dispose(dbstore); |
|
| 97 |
} catch (Throwable th) {
|
|
| 98 |
LOGGER.warn("", th);
|
|
| 99 |
throw th; |
|
| 94 | 100 |
} |
| 95 |
DisposeUtils.dispose(dbstore); |
|
| 96 | 101 |
} |
| 97 |
|
|
| 102 |
|
|
| 98 | 103 |
@SuppressWarnings("UnusedAssignment")
|
| 99 | 104 |
public void testComputed2() throws Exception {
|
| 100 |
if( !utils().isTheDatabaseAvailable() ) {
|
|
| 101 |
return; |
|
| 102 |
} |
|
| 103 |
String testTableName = "testComputedAttributes2"; |
|
| 104 |
JDBCServerExplorer explorer = utils().openServerExplorer("computedAttr");
|
|
| 105 |
try {
|
|
| 106 |
if (!utils().isTheDatabaseAvailable()) {
|
|
| 107 |
return; |
|
| 108 |
} |
|
| 109 |
String testTableName = "testComputedAttributes2"; |
|
| 110 |
JDBCServerExplorer explorer = utils().openServerExplorer("computedAttr");
|
|
| 105 | 111 |
|
| 106 |
utils().info_jdbc(explorer); |
|
| 107 |
utils().drop_tables(explorer, testTableName); |
|
| 108 |
|
|
| 109 |
FeatureStore sourceStore = utils().openSourceStore2(); |
|
| 110 |
|
|
| 111 |
utils().create_table_from(explorer, testTableName, sourceStore); |
|
| 112 |
utils().insert_into_from(explorer, testTableName, sourceStore, FeatureStore.MODE_APPEND); |
|
| 113 |
|
|
| 114 |
FeatureStore dbstore = utils().openStore(explorer,testTableName); |
|
| 115 |
|
|
| 116 |
dbstore.edit(); |
|
| 117 |
FeatureType featureType = dbstore.getDefaultFeatureType(); |
|
| 118 |
EditableFeatureType eFeatureType = featureType.getEditable(); |
|
| 119 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
|
| 120 |
eFeatureType.add("CompuID",
|
|
| 121 |
DataTypes.INTEGER, |
|
| 122 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 123 |
eFeatureType, |
|
| 124 |
ExpressionUtils.createExpression("MOD(ID,10)")
|
|
| 125 |
)); |
|
| 126 |
eFeatureType.add("CompuPob",
|
|
| 127 |
DataTypes.INTEGER, |
|
| 128 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 129 |
eFeatureType, |
|
| 130 |
ExpressionUtils.createExpression("Poblacion+1")
|
|
| 131 |
)); |
|
| 132 |
eFeatureType.add("CompuProv",
|
|
| 133 |
DataTypes.STRING, |
|
| 134 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 135 |
eFeatureType, |
|
| 136 |
ExpressionUtils.createExpression("UPPER(Provincia)")
|
|
| 137 |
)); |
|
| 138 |
EditableFeatureAttributeDescriptor extraColumn1 = |
|
| 139 |
query.getExtraColumn().add("ExtraID900", DataTypes.INTEGER);
|
|
| 140 |
EditableFeatureAttributeDescriptor extraColumn2 = |
|
| 141 |
query.getExtraColumn().add("ExtraPobDen", DataTypes.INTEGER);
|
|
| 142 |
EditableFeatureAttributeDescriptor extraColumn3 = |
|
| 143 |
query.getExtraColumn().add("ExtraAno", DataTypes.INTEGER);
|
|
| 112 |
utils().info_jdbc(explorer); |
|
| 113 |
utils().drop_tables(explorer, testTableName); |
|
| 144 | 114 |
|
| 145 |
extraColumn1.setFeatureAttributeEmulator( |
|
| 146 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 147 |
eFeatureType, |
|
| 148 |
ExpressionUtils.createExpression("CompuID+900")));
|
|
| 149 |
extraColumn2.setFeatureAttributeEmulator( |
|
| 150 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 151 |
eFeatureType, |
|
| 152 |
ExpressionUtils.createExpression("CompuPob+Densidad")));
|
|
| 153 |
extraColumn3.setFeatureAttributeEmulator( |
|
| 154 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 155 |
eFeatureType, |
|
| 156 |
ExpressionUtils.createExpression("EXTRACT(YEAR FROM Fecha)")));
|
|
| 157 |
|
|
| 158 |
query.getGroupByColumns().add("Comunidad");
|
|
| 159 |
query.getGroupByColumns().add("CompuProv");
|
|
| 160 |
query.getGroupByColumns().add("ExtraAno");
|
|
| 161 |
query.getAggregateFunctions().put("ID", "MIN");
|
|
| 162 |
query.getAggregateFunctions().put("Poblacion", "MIN");
|
|
| 163 |
query.getAggregateFunctions().put("CompuID", "MIN");
|
|
| 164 |
query.getAggregateFunctions().put("CompuPob", "SUM");
|
|
| 165 |
query.getAggregateFunctions().put("ExtraID900", "SUM");
|
|
| 166 |
query.getAggregateFunctions().put("ExtraPobDen", "SUM");
|
|
| 167 |
dbstore.update(eFeatureType); |
|
| 168 |
dbstore.finishEditing(); |
|
| 169 |
|
|
| 170 |
|
|
| 171 |
List<Feature> features0 = dbstore.getFeatures(query); |
|
| 172 |
ArrayList<Feature> features = new ArrayList<>(); |
|
| 173 |
|
|
| 174 |
System.out.println("ID,Comunidad,Provincia,Ciudad,Poblacion,Densidad,Fecha,CompuID,CompuPob,CompuProv,ExtraID900,ExtraPobDen,ExtraAno");
|
|
| 175 |
for (int i = 0; i < features0.size(); i++) {
|
|
| 176 |
Feature feature = features0.get(i); |
|
| 177 |
features.add(feature.getCopy()); |
|
| 178 |
System.out.print(feature.toString()); |
|
| 179 |
System.out.print(","+feature.get("ExtraID900"));
|
|
| 180 |
System.out.print(","+feature.get("ExtraPobDen"));
|
|
| 181 |
System.out.println(","+feature.get("ExtraAno"));
|
|
| 115 |
FeatureStore sourceStore = utils().openSourceStore2(); |
|
| 116 |
|
|
| 117 |
utils().create_table_from(explorer, testTableName, sourceStore); |
|
| 118 |
utils().insert_into_from(explorer, testTableName, sourceStore, FeatureStore.MODE_APPEND); |
|
| 119 |
|
|
| 120 |
FeatureStore dbstore = utils().openStore(explorer, testTableName); |
|
| 121 |
|
|
| 122 |
dbstore.edit(); |
|
| 123 |
FeatureType featureType = dbstore.getDefaultFeatureType(); |
|
| 124 |
EditableFeatureType eFeatureType = featureType.getEditable(); |
|
| 125 |
FeatureQuery query = sourceStore.createFeatureQuery(); |
|
| 126 |
eFeatureType.add("CompuID",
|
|
| 127 |
DataTypes.INTEGER, |
|
| 128 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 129 |
eFeatureType, |
|
| 130 |
ExpressionUtils.createExpression("MOD(ID,10)")
|
|
| 131 |
)); |
|
| 132 |
eFeatureType.add("CompuPob",
|
|
| 133 |
DataTypes.INTEGER, |
|
| 134 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 135 |
eFeatureType, |
|
| 136 |
ExpressionUtils.createExpression("Poblacion+1")
|
|
| 137 |
)); |
|
| 138 |
eFeatureType.add("CompuProv",
|
|
| 139 |
DataTypes.STRING, |
|
| 140 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 141 |
eFeatureType, |
|
| 142 |
ExpressionUtils.createExpression("UPPER(Provincia)")
|
|
| 143 |
)); |
|
| 144 |
EditableFeatureAttributeDescriptor extraColumn1 |
|
| 145 |
= query.getExtraColumn().add("ExtraID900", DataTypes.INTEGER);
|
|
| 146 |
EditableFeatureAttributeDescriptor extraColumn2 |
|
| 147 |
= query.getExtraColumn().add("ExtraPobDen", DataTypes.INTEGER);
|
|
| 148 |
EditableFeatureAttributeDescriptor extraColumn3 |
|
| 149 |
= query.getExtraColumn().add("ExtraAno", DataTypes.INTEGER);
|
|
| 150 |
|
|
| 151 |
extraColumn1.setFeatureAttributeEmulator( |
|
| 152 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 153 |
eFeatureType, |
|
| 154 |
ExpressionUtils.createExpression("CompuID+900")));
|
|
| 155 |
extraColumn2.setFeatureAttributeEmulator( |
|
| 156 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 157 |
eFeatureType, |
|
| 158 |
ExpressionUtils.createExpression("CompuPob+Densidad")));
|
|
| 159 |
extraColumn3.setFeatureAttributeEmulator( |
|
| 160 |
new DefaultFeatureAttributeEmulatorExpression( |
|
| 161 |
eFeatureType, |
|
| 162 |
ExpressionUtils.createExpression("EXTRACT(YEAR FROM Fecha)")));
|
|
| 163 |
|
|
| 164 |
query.getGroupByColumns().add("Comunidad");
|
|
| 165 |
query.getGroupByColumns().add("CompuProv");
|
|
| 166 |
query.getGroupByColumns().add("ExtraAno");
|
|
| 167 |
query.getAggregateFunctions().put("ID", "MIN");
|
|
| 168 |
query.getAggregateFunctions().put("Poblacion", "MIN");
|
|
| 169 |
query.getAggregateFunctions().put("CompuID", "MIN");
|
|
| 170 |
query.getAggregateFunctions().put("CompuPob", "SUM");
|
|
| 171 |
query.getAggregateFunctions().put("ExtraID900", "SUM");
|
|
| 172 |
query.getAggregateFunctions().put("ExtraPobDen", "SUM");
|
|
| 173 |
dbstore.update(eFeatureType); |
|
| 174 |
dbstore.finishEditing(); |
|
| 175 |
|
|
| 176 |
List<Feature> features0 = dbstore.getFeatures(query); |
|
| 177 |
ArrayList<Feature> features = new ArrayList<>(); |
|
| 178 |
|
|
| 179 |
System.out.println("ID,Comunidad,Provincia,Ciudad,Poblacion,Densidad,Fecha,CompuID,CompuPob,CompuProv,ExtraID900,ExtraPobDen,ExtraAno");
|
|
| 180 |
for (int i = 0; i < features0.size(); i++) {
|
|
| 181 |
Feature feature = features0.get(i); |
|
| 182 |
features.add(feature.getCopy()); |
|
| 183 |
System.out.print(feature.toString()); |
|
| 184 |
System.out.print("," + feature.get("ExtraID900"));
|
|
| 185 |
System.out.print("," + feature.get("ExtraPobDen"));
|
|
| 186 |
System.out.println("," + feature.get("ExtraAno"));
|
|
| 187 |
} |
|
| 188 |
System.out.println("ID,Comunidad,Provincia,Ciudad,Poblacion,Densidad,Fecha,CompuID,CompuPob,CompuProv,ExtraID900,ExtraPobDen,ExtraAno");
|
|
| 189 |
|
|
| 190 |
features0 = null; |
|
| 191 |
String[] header = new String[]{"ID", "Comunidad", "Provincia", "Ciudad", "Poblacion", "Densidad", "Fecha", "CompuID", "CompuPob", "CompuProv", "ExtraID900", "ExtraPobDen", "ExtraAno"};
|
|
| 192 |
ArrayList<Object[]> values = new ArrayList<>(); |
|
| 193 |
values.add(new Object[]{0, null, null, null, null, null, null, 0, null, "", 900, null, null});
|
|
| 194 |
values.add(new Object[]{5, "GVA", null, null, 200, null, null, 5, 201, "ALICANTE", 905, 206, 2019});
|
|
| 195 |
values.add(new Object[]{6, "GVA", null, null, 20, null, null, 6, 422, "ALICANTE", 1813, 1135, 2020});
|
|
| 196 |
values.add(new Object[]{8, "GVA", null, null, 100, null, null, 8, 702, "CASTELLON", 1817, 719, 2019});
|
|
| 197 |
values.add(new Object[]{1, "GVA", null, null, 500, null, null, 1, 1502, "VALENCIA", 1803, 1505, 2019});
|
|
| 198 |
values.add(new Object[]{3, "GVA", null, null, 50, null, null, 3, 352, "VALENCIA", 1807, 359, 2020});
|
|
| 199 |
for (int i = 0; i < features.size(); i++) {
|
|
| 200 |
for (int j = 0; j < header.length; j++) {
|
|
| 201 |
assertEquals("feature[" + i + "][" + header[j] + "]:", values.get(i)[j], features.get(i).get(header[j]));
|
|
| 202 |
} |
|
| 203 |
} |
|
| 204 |
|
|
| 205 |
DisposeUtils.dispose(dbstore); |
|
| 206 |
} catch (Throwable th) {
|
|
| 207 |
LOGGER.warn("", th);
|
|
| 208 |
throw th; |
|
| 182 | 209 |
} |
| 183 |
System.out.println("ID,Comunidad,Provincia,Ciudad,Poblacion,Densidad,Fecha,CompuID,CompuPob,CompuProv,ExtraID900,ExtraPobDen,ExtraAno");
|
|
| 184 |
|
|
| 185 |
features0 = null; |
|
| 186 |
String[] header = new String[]{"ID","Comunidad","Provincia","Ciudad","Poblacion","Densidad","Fecha","CompuID","CompuPob","CompuProv","ExtraID900","ExtraPobDen","ExtraAno"};
|
|
| 187 |
ArrayList<Object[]> values = new ArrayList<>(); |
|
| 188 |
values.add(new Object[]{0, null, null, null, null, null, null, 0, null,"",900,null,null});
|
|
| 189 |
values.add(new Object[]{1, "GVA", null, null, 500, null, null, 1, 1502, "VALENCIA",1803,1505,2019});
|
|
| 190 |
values.add(new Object[]{3, "GVA", null, null, 50, null, null, 3, 352, "VALENCIA",1807,359,2020});
|
|
| 191 |
values.add(new Object[]{5, "GVA", null, null, 200, null, null, 5, 201, "ALICANTE",905,206,2019});
|
|
| 192 |
values.add(new Object[]{6, "GVA", null, null, 20, null, null, 6, 422, "ALICANTE",1813,1135,2020});
|
|
| 193 |
values.add(new Object[]{8, "GVA", null, null, 100, null, null, 8, 702, "CASTELLON",1817,719,2019});
|
|
| 194 |
for (int i = 0; i < features.size(); i++) {
|
|
| 195 |
for (int j = 0; j < header.length; j++) {
|
|
| 196 |
assertEquals("feature["+i+"]["+header[j]+"]:", values.get(i)[j],features.get(i).get(header[j]));
|
|
| 197 |
} |
|
| 198 |
} |
|
| 199 |
|
|
| 200 |
DisposeUtils.dispose(dbstore); |
|
| 201 | 210 |
} |
| 202 | 211 |
|
| 203 | 212 |
} |
| 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/operations/ResultSetForSetProviderOperation.java | ||
|---|---|---|
| 60 | 60 |
import org.gvsig.fmap.geom.DataTypes; |
| 61 | 61 |
import org.gvsig.tools.dynobject.DynField; |
| 62 | 62 |
import org.gvsig.tools.evaluator.Evaluator; |
| 63 |
import org.gvsig.tools.lang.CloneableUtils; |
|
| 63 | 64 |
import org.gvsig.tools.util.ContainerUtils; |
| 64 | 65 |
|
| 65 | 66 |
public class ResultSetForSetProviderOperation extends AbstractConnectionOperation {
|
| ... | ... | |
| 396 | 397 |
if (!StringUtils.isEmpty(baseOrder)) {
|
| 397 | 398 |
select.order_by().custom(baseOrder); |
| 398 | 399 |
} |
| 399 |
//Si hay especificado un offset (se esta paginando) siempre deberemos ordenar por un campo con valores unicos. |
|
| 400 |
//Anadiremos la clave primaria siempre en este caso para asegurarnos de que el orden de los registros es siempre el mismo. |
|
| 401 |
// if (!select.has_order_by()) {
|
|
| 402 |
// Si no tenemos order by comprobamos si lo necesitamos y lo a?adimos. |
|
| 403 |
if (offset > 0 || (offset == 0 && limit > 0)) {
|
|
| 404 |
// No tengo claro que (offset==0 && limit>0) sea lo mas correcto, |
|
| 405 |
// Pero cuando se va a paginar y se pide la primera pagina offset es |
|
| 406 |
// 0 y limit>0, y si no ordenamos ya esa primera pagina los resultados |
|
| 407 |
// que se obtienen no son correctos, ya que la primera pagina se saca |
|
| 408 |
// sin ordenar y el resto ordenadas. |
|
| 409 |
// Probablemente deberiamos tener alguna otra forma de detectar que |
|
| 410 |
// estamos paginanado ya que asi no distinguimo si solo queremos |
|
| 411 |
// obtener los primeros elementos sin importarnos su orden. |
|
| 412 |
if (select.has_group_by()) {
|
|
| 413 |
ExpressionBuilder.Value group = select.getGroups().get(0); |
|
| 414 |
// if(!(group instanceof ExpressionBuilder.Function)) {
|
|
| 415 |
// expbuilder.getattr(this.table.getTable(), group.XXX); |
|
| 416 |
// } |
|
| 417 |
select.order_by().value(group).ascending(); |
|
| 418 | 400 |
|
| 419 |
} else if (primaryKeys.isEmpty()) {
|
|
| 420 |
// Muy probablemente si no tiene pk sea una vista, asi que |
|
| 421 |
// pasaremos de ordenar y esperemos que la vista este ya ordenada. |
|
| 422 |
select.disable_check_order_and_offset(); |
|
| 423 |
} else {
|
|
| 424 |
for (String attrName : primaryKeys) {
|
|
| 425 |
// Se precisa indicar un orden para usar OFFSET. |
|
| 426 |
if(select.getOrderBy(attrName)==null){
|
|
| 427 |
select.order_by().column(attrName).ascending(); |
|
| 428 |
} |
|
| 429 |
} |
|
| 401 |
if (select.has_group_by()) { // && isPaginated()) {
|
|
| 402 |
// Cuando paginamos debemos ordenar por las columnas del groupby. |
|
| 403 |
// Ordenamos siempre para obtener el mismo resultado cuando paginamos |
|
| 404 |
// y no paginamos. |
|
| 405 |
for (ExpressionBuilder.Value group : select.getGroups()) {
|
|
| 406 |
if (select.getOrderBy(group) == null) {
|
|
| 407 |
ExpressionBuilder.Value v = (ExpressionBuilder.Value) CloneableUtils.cloneQuietly(group); |
|
| 408 |
select.order_by().value(v).ascending(); |
|
| 409 |
valuesToRemoveFeatureType.add(v); |
|
| 430 | 410 |
} |
| 431 | 411 |
} |
| 432 |
// } |
|
| 433 |
for (String attrName : primaryKeys) {
|
|
| 434 |
if(select.getOrderBy(attrName)==null){
|
|
| 435 |
select.order_by().column(attrName).ascending(); |
|
| 412 |
} |
|
| 413 |
|
|
| 414 |
if (primaryKeys.isEmpty()) {
|
|
| 415 |
// Muy probablemente si no tiene pk sea una vista, asi que |
|
| 416 |
// pasaremos de ordenar y esperemos que la vista este ya ordenada. |
|
| 417 |
select.disable_check_order_and_offset(); |
|
| 418 |
} else {
|
|
| 419 |
// Siempre ordenamos por la clave primaria |
|
| 420 |
for (String attrName : primaryKeys) {
|
|
| 421 |
if (select.getOrderBy(attrName) == null) {
|
|
| 422 |
select.order_by().column(attrName).ascending(); |
|
| 423 |
} |
|
| 436 | 424 |
} |
| 437 | 425 |
} |
| 426 |
|
|
| 438 | 427 |
if (limit > 0) {
|
| 439 | 428 |
select.limit(limit); |
| 440 | 429 |
} else {
|
| ... | ... | |
| 454 | 443 |
for (ExpressionBuilder.Value value : valuesToRemoveFeatureType) {
|
| 455 | 444 |
value.setProperty(PROP_FEATURE_TYPE, null); |
| 456 | 445 |
} |
| 457 |
this.helper.expandCalculedColumns(sqlbuilder);
|
|
| 446 |
this.helper.expandCalculedColumns(sqlbuilder); |
|
| 458 | 447 |
this.helper.processSpecialFunctions(sqlbuilder, storeType, extraColumnNames); |
| 459 | 448 |
String sql = sqlbuilder.toString(); |
| 460 | 449 |
return sql; |
| 461 | 450 |
} |
| 451 |
|
|
| 452 |
private boolean isPaginated() {
|
|
| 453 |
// No tengo claro que (offset==0 && limit>0) sea lo mas correcto, |
|
| 454 |
// Pero cuando se va a paginar y se pide la primera pagina offset es |
|
| 455 |
// 0 y limit>0, y si no ordenamos ya esa primera pagina los resultados |
|
| 456 |
// que se obtienen no son correctos, ya que la primera pagina se saca |
|
| 457 |
// sin ordenar y el resto ordenadas. |
|
| 458 |
// Probablemente deberiamos tener alguna otra forma de detectar que |
|
| 459 |
// estamos paginanado ya que asi no distinguimo si solo queremos |
|
| 460 |
// obtener los primeros elementos sin importarnos su orden. |
|
| 461 |
return (offset > 0 || (offset == 0 && limit > 0)); |
|
| 462 |
} |
|
| 462 | 463 |
|
| 463 | 464 |
public ResultSetEntry createResultSet() throws DataException {
|
| 464 | 465 |
List<FeatureAttributeDescriptor> columns = new ArrayList<>(); |
| trunk/org.gvsig.desktop/org.gvsig.desktop.compat.cdc/org.gvsig.fmap.dal/org.gvsig.fmap.dal.api/src/main/java/org/gvsig/fmap/dal/SQLBuilder.java | ||
|---|---|---|
| 191 | 191 |
public OrderByBuilder column(String name); |
| 192 | 192 |
|
| 193 | 193 |
public boolean isColumn(String name); |
| 194 |
|
|
| 195 |
public boolean isColumn(Value value); |
|
| 194 | 196 |
|
| 195 | 197 |
// @Deprecated |
| 196 | 198 |
// public OrderByBuilder column(Value name); |
| ... | ... | |
| 219 | 221 |
public OrderByBuilder order_by(); |
| 220 | 222 |
|
| 221 | 223 |
public OrderByBuilder getOrderBy(String column); |
| 224 |
|
|
| 225 |
public OrderByBuilder getOrderBy(Value column); |
|
| 222 | 226 |
|
| 223 | 227 |
public SelectBuilder group_by(Value... column); |
| 224 | 228 |
|
| trunk/org.gvsig.desktop/org.gvsig.desktop.compat.cdc/org.gvsig.fmap.dal/org.gvsig.fmap.dal.spi/src/main/java/org/gvsig/fmap/dal/feature/spi/SQLBuilderBase.java | ||
|---|---|---|
| 73 | 73 |
protected TableNameBuilder table_name; |
| 74 | 74 |
|
| 75 | 75 |
protected abstract class AbstractStatementPart extends AbstractValue {
|
| 76 |
|
|
| 76 |
|
|
| 77 | 77 |
} |
| 78 | 78 |
|
| 79 | 79 |
protected abstract class AbstractStatement extends AbstractStatementPart {
|
| 80 |
|
|
| 80 |
@Override |
|
| 81 |
public Value clone() throws CloneNotSupportedException {
|
|
| 82 |
throw new CloneNotSupportedException(); |
|
| 83 |
} |
|
| 81 | 84 |
} |
| 82 | 85 |
|
| 83 | 86 |
protected class ColumnDescriptorBase implements ColumnDescriptor {
|
| ... | ... | |
| 328 | 331 |
this.name = name; |
| 329 | 332 |
this.table = table; |
| 330 | 333 |
} |
| 334 |
|
|
| 335 |
@Override |
|
| 336 |
public ColumnBase clone() throws CloneNotSupportedException {
|
|
| 337 |
ColumnBase other = (ColumnBase) super.clone(); |
|
| 338 |
other.table = (TableNameBuilder) org.gvsig.tools.lang.Cloneable.cloneQuietly(table); |
|
| 339 |
return other; |
|
| 340 |
} |
|
| 331 | 341 |
|
| 342 |
|
|
| 332 | 343 |
@Override |
| 333 | 344 |
public String name() {
|
| 334 | 345 |
return this.name; |
| ... | ... | |
| 392 | 403 |
|
| 393 | 404 |
public TableNameBuilderBase() {
|
| 394 | 405 |
} |
| 395 |
|
|
| 406 |
|
|
| 396 | 407 |
@Override |
| 397 | 408 |
public void accept(Visitor visitor, VisitorFilter filter) {
|
| 398 | 409 |
if (filter==null || filter.accept(this)) {
|
| ... | ... | |
| 527 | 538 |
this.distinct = false; |
| 528 | 539 |
this.all = false; |
| 529 | 540 |
} |
| 530 |
|
|
| 541 |
|
|
| 531 | 542 |
@Override |
| 543 |
public CountBuilderBase clone() throws CloneNotSupportedException {
|
|
| 544 |
CountBuilderBase other = (CountBuilderBase) super.clone(); |
|
| 545 |
other.value = (Value) org.gvsig.tools.lang.Cloneable.cloneQuietly(value); |
|
| 546 |
return other; |
|
| 547 |
} |
|
| 548 |
|
|
| 549 |
@Override |
|
| 532 | 550 |
public CountBuilder all() {
|
| 533 | 551 |
this.all = true; |
| 534 | 552 |
return this; |
| ... | ... | |
| 586 | 604 |
this.table = table; |
| 587 | 605 |
this.expression = expression; |
| 588 | 606 |
} |
| 607 |
|
|
| 608 |
@Override |
|
| 609 |
public JoinBase clone() throws CloneNotSupportedException {
|
|
| 610 |
JoinBase other = (JoinBase) super.clone(); |
|
| 611 |
other.table = (TableNameBuilder) org.gvsig.tools.lang.Cloneable.cloneQuietly(table); |
|
| 612 |
other.expression = (Value) org.gvsig.tools.lang.Cloneable.cloneQuietly(expression); |
|
| 613 |
return other; |
|
| 614 |
} |
|
| 589 | 615 |
|
| 590 | 616 |
@Override |
| 591 | 617 |
public String toString() {
|
| ... | ... | |
| 625 | 651 |
this.passthrough = null; |
| 626 | 652 |
this.joins = null; |
| 627 | 653 |
} |
| 654 |
|
|
| 655 |
@Override |
|
| 656 |
public FromBuilderBase clone() throws CloneNotSupportedException {
|
|
| 657 |
FromBuilderBase other = (FromBuilderBase) super.clone(); |
|
| 658 |
other.tableName = (TableNameBuilder) org.gvsig.tools.lang.Cloneable.cloneQuietly(tableName); |
|
| 659 |
if (joins!=null) {
|
|
| 660 |
for (int i = 0; i < joins.size(); i++) {
|
|
| 661 |
other.joins.set(i, (JoinBase) joins.get(i).clone()); |
|
| 662 |
} |
|
| 663 |
} |
|
| 664 |
return other; |
|
| 665 |
} |
|
| 628 | 666 |
|
| 629 | 667 |
@Override |
| 630 | 668 |
public FromBuilder left_join(TableNameBuilder table, Value expression) {
|
| ... | ... | |
| 705 | 743 |
protected Value value = null; |
| 706 | 744 |
protected boolean asGeometry = false; |
| 707 | 745 |
protected TableNameBuilder table; |
| 746 |
|
|
| 747 |
@Override |
|
| 748 |
public SelectColumnBuilderBase clone() throws CloneNotSupportedException {
|
|
| 749 |
SelectColumnBuilderBase other = (SelectColumnBuilderBase) super.clone(); |
|
| 750 |
other.value = (Value) org.gvsig.tools.lang.Cloneable.cloneQuietly(value); |
|
| 751 |
other.name = (Variable) org.gvsig.tools.lang.Cloneable.cloneQuietly(name); |
|
| 752 |
other.table = (TableNameBuilder) org.gvsig.tools.lang.Cloneable.cloneQuietly(table); |
|
| 753 |
return other; |
|
| 754 |
} |
|
| 708 | 755 |
|
| 709 | 756 |
@Override |
| 710 | 757 |
public void accept(Visitor visitor, VisitorFilter filter) {
|
| ... | ... | |
| 851 | 898 |
public OrderByBuilderBase() {
|
| 852 | 899 |
this.ascending = true; |
| 853 | 900 |
} |
| 854 |
|
|
| 901 |
|
|
| 855 | 902 |
@Override |
| 903 |
public OrderByBuilderBase clone() throws CloneNotSupportedException {
|
|
| 904 |
OrderByBuilderBase other = (OrderByBuilderBase) super.clone(); |
|
| 905 |
other.value = (Value) org.gvsig.tools.lang.Cloneable.cloneQuietly(value); |
|
| 906 |
return other; |
|
| 907 |
} |
|
| 908 |
|
|
| 909 |
@Override |
|
| 856 | 910 |
public void accept(Visitor visitor, VisitorFilter filter) {
|
| 857 | 911 |
if (filter==null || filter.accept(this)) {
|
| 858 | 912 |
visitor.visit(this); |
| ... | ... | |
| 877 | 931 |
} |
| 878 | 932 |
|
| 879 | 933 |
@Override |
| 934 |
public boolean isColumn(Value value) {
|
|
| 935 |
if(value instanceof ExpressionBuilder.Variable){
|
|
| 936 |
return isColumn(((ExpressionBuilder.Variable)value).name()); |
|
| 937 |
} |
|
| 938 |
return this.value == value; |
|
| 939 |
} |
|
| 940 |
|
|
| 941 |
@Override |
|
| 880 | 942 |
public OrderByBuilder value(Value expression) {
|
| 881 | 943 |
this.value = expression; |
| 882 | 944 |
return this; |
| ... | ... | |
| 1157 | 1219 |
return order; |
| 1158 | 1220 |
} |
| 1159 | 1221 |
|
| 1222 |
public OrderByBuilder getOrderBy(Value column) {
|
|
| 1223 |
if(this.order_by == null){
|
|
| 1224 |
return null; |
|
| 1225 |
} |
|
| 1226 |
for (OrderByBuilder orderByBuilder : this.order_by) {
|
|
| 1227 |
if(orderByBuilder.isColumn(column)){
|
|
| 1228 |
return orderByBuilder; |
|
| 1229 |
} |
|
| 1230 |
} |
|
| 1231 |
return null; |
|
| 1232 |
} |
|
| 1233 |
|
|
| 1160 | 1234 |
public OrderByBuilder getOrderBy(String column) {
|
| 1161 | 1235 |
if(this.order_by == null){
|
| 1162 | 1236 |
return null; |
| ... | ... | |
| 1375 | 1449 |
this.role = role; |
| 1376 | 1450 |
this.privileges = new HashSet<>(); |
| 1377 | 1451 |
} |
| 1452 |
|
|
| 1453 |
@Override |
|
| 1454 |
public GrantRoleBuilderBase clone() throws CloneNotSupportedException {
|
|
| 1455 |
GrantRoleBuilderBase other = (GrantRoleBuilderBase) super.clone(); |
|
| 1456 |
other.table = (TableNameBuilder) org.gvsig.tools.lang.Cloneable.cloneQuietly(table); |
|
| 1457 |
other.privileges = (Set<Privilege>) org.gvsig.tools.lang.Cloneable.cloneQuietly(privileges); |
|
| 1458 |
|
|
| 1459 |
return other; |
|
| 1460 |
} |
|
| 1378 | 1461 |
|
| 1379 | 1462 |
@Override |
| 1380 | 1463 |
public GrantRoleBuilder privilege(Privilege privilege) {
|
| trunk/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.h2spatial/org.gvsig.h2spatial.h2gis132/org.gvsig.h2spatial.h2gis132.provider/src/test/resources/org/gvsig/fmap/dal/store/h2/resultSetForSetProvider.sql | ||
|---|---|---|
| 16 | 16 |
NULL AS "Bool4", NULL AS "Float", NULL AS "Bool5", NULL AS "Decimal", |
| 17 | 17 |
NULL AS "Geometry" |
| 18 | 18 |
FROM "PUBLIC"."test" |
| 19 |
GROUP BY "test"."Long" ORDER BY "ID" ASC; |
|
| 19 |
GROUP BY "test"."Long" ORDER BY "test"."Long" ASC, "ID" ASC;
|
|
| 20 | 20 |
|
| 21 | 21 |
-- Subselect |
| 22 | 22 |
SELECT |
| ... | ... | |
| 49 | 49 |
FROM "countries" |
| 50 | 50 |
WHERE (("TEST"."STRING" = "countries"."CONTINENT") AND
|
| 51 | 51 |
("countries"."LASTCENSUS" < 0)) LIMIT 1)),TRUE,FALSE)
|
| 52 |
GROUP BY "test"."Long" ORDER BY "ID" ASC; |
|
| 52 |
GROUP BY "test"."Long" ORDER BY "test"."Long" ASC, "ID" ASC;
|
|
| 53 | 53 |
|
| 54 | 54 |
-- Simple |
| 55 | 55 |
SELECT |
| ... | ... | |
| 107 | 107 |
SUM(("Long" + 300)) AS "Compu2",
|
| 108 | 108 |
SUM(((20 + "Byte") + ("ID" * 2))) AS "Extra2",
|
| 109 | 109 |
(("Long" + 10) + (("ID" * 2))) AS "Extra1"
|
| 110 |
FROM "PUBLIC"."test" GROUP BY "test"."Long", (("Long" + 10) + (("ID" * 2))), (("ID" * 2)) ORDER BY "ID" ASC;
|
|
| 110 |
FROM "PUBLIC"."test" GROUP BY "test"."Long", (("Long" + 10) + (("ID" * 2))), (("ID" * 2))
|
|
| 111 |
ORDER BY "test"."Long" ASC, "Extra1" ASC, "Compu1" ASC, "ID" ASC; |
|
| trunk/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.h2spatial/org.gvsig.h2spatial.h2gis132/org.gvsig.h2spatial.h2gis132.provider/src/test/resources/org/gvsig/fmap/dal/store/h2/usecases/arena2/testGroupByForeignValue.sql | ||
|---|---|---|
| 2 | 2 |
-- ResultSetForSetProvider SQL |
| 3 | 3 |
|
| 4 | 4 |
-- Select |
| 5 |
SELECT NULL AS "LID_CONDUCTOR", NULL AS "ID_ACCIDENTE", NULL AS "LID_VEHICULO", NULL AS "ID_VEHICULO", NULL AS "POSIBLE_RESPONSABLE", NULL AS "FECHA_NACIMIENTO", NULL AS "SEXO", NULL AS "NACIONALIDAD", NULL AS "PAIS_RESIDENCIA", NULL AS "PROVINCIA_RESIDENCIA", NULL AS "MUNICIPIO_RESIDENCIA", NULL AS "ASISTENCIA_SANITARIA", NULL AS "INFLU_FACT_ATENCION", NULL AS "FACTORES_ATENCION", NULL AS "INFLU_PRES_ERROR", NULL AS "PRESUNTOS_ERRORES", NULL AS "CARACT_PERMISO", NULL AS "CLASE_PERMISO", NULL AS "FECHA_PERMISO", NULL AS "MOTIVO_DESPLAZAMIENTO", NULL AS "DESPLAZAMIENTO_PREVISTO", NULL AS "ACC_SEG_CINTURON", NULL AS "ACC_SEG_CASCO", NULL AS "ACC_SEG_BRAZOS", NULL AS "ACC_SEG_ESPALDA", NULL AS "ACC_SEG_TORSO", NULL AS "ACC_SEG_MANOS", NULL AS "ACC_SEG_PIERNAS", NULL AS "ACC_SEG_PIES", NULL AS "ACC_SEG_PRENDA_REF", NULL AS "INFLU_ALCOHOL", NULL AS "PRUEBA_ALCOHOLEMIA", NULL AS "TASA_ALCOHOLEMIA1", NULL AS "TASA_ALCOHOLEMIA2", NULL AS "PRUEBA_ALC_SANGRE", NULL AS "SIGNOS_INFLU_ALCOHOL", NULL AS "INFLU_DROGAS", NULL AS "PRUEBA_DROGAS", NULL AS "AMP", NULL AS "CONFIRMADO_AMP", NULL AS "BDZ", NULL AS "CONFIRMADO_BDZ", NULL AS "COC", NULL AS "CONFIRMADO_COC", NULL AS "THC", NULL AS "CONFIRMADO_THC", NULL AS "METH", NULL AS "CONFIRMADO_METH", NULL AS "OPI", NULL AS "CONFIRMADO_OPI", NULL AS "OTRAS", NULL AS "CONFIRMADO_OTRAS", NULL AS "SIGNOS_INFLU_DROGAS", NULL AS "INFLU_PRES_INFRAC_COND", NULL AS "PRES_INFRAC_COND", NULL AS "PRES_INFRAC_SIN_LUCES", NULL AS "PRES_INFRAC_SIN_TRIANGULO", NULL AS "INFLU_PRES_INFRAC_VEL", NULL AS "PRES_INFRAC_VEL_COND", NULL AS "INFLU_OTRA_INFRAC", NULL AS "OTRA_INFRAC_COND", NULL AS "EXTRA", SUM(1) AS "Acc", EXTRACT(YEAR FROM "public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE") AS "ano", "public"."ARENA2_ACCIDENTES"."COD_PROVINCIA" AS "provincia", CASE WHEN (("ASISTENCIA_SANITARIA" = 1)) THEN ('Muerto') WHEN (("ASISTENCIA_SANITARIA" = 2)) THEN ('Grave') WHEN (((((("ASISTENCIA_SANITARIA" = 3) OR ("ASISTENCIA_SANITARIA" = 4)) OR ("ASISTENCIA_SANITARIA" = 5)) OR ("ASISTENCIA_SANITARIA" = 6)) OR ("ASISTENCIA_SANITARIA" = 7))) THEN ('Leve') WHEN (("ASISTENCIA_SANITARIA" = 8)) THEN ('Ileso') ELSE ('Se Desconoce') END AS "lesividad" FROM "public"."ARENA2_CONDUCTORES" LEFT JOIN "public"."ARENA2_ACCIDENTES" ON ( ("public"."ARENA2_CONDUCTORES"."ID_ACCIDENTE") = ("public"."ARENA2_ACCIDENTES"."ID_ACCIDENTE") ) LEFT JOIN "public"."ARENA2_VEHICULOS" ON ( ("public"."ARENA2_CONDUCTORES"."LID_VEHICULO") = ("public"."ARENA2_VEHICULOS"."LID_VEHICULO") ) WHERE ((("public"."ARENA2_ACCIDENTES"."TITULARIDAD_VIA" = 2) AND ((("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 5) OR ("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 6)) OR ("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 7))) AND (("public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE" >= DATE '2019-01-01') AND ("public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE" <= DATE '2019-12-31'))) GROUP BY EXTRACT(YEAR FROM "public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE"), "public"."ARENA2_ACCIDENTES"."COD_PROVINCIA", CASE WHEN (("ASISTENCIA_SANITARIA" = 1)) THEN ('Muerto') WHEN (("ASISTENCIA_SANITARIA" = 2)) THEN ('Grave') WHEN (((((("ASISTENCIA_SANITARIA" = 3) OR ("ASISTENCIA_SANITARIA" = 4)) OR ("ASISTENCIA_SANITARIA" = 5)) OR ("ASISTENCIA_SANITARIA" = 6)) OR ("ASISTENCIA_SANITARIA" = 7))) THEN ('Leve') WHEN (("ASISTENCIA_SANITARIA" = 8)) THEN ('Ileso') ELSE ('Se Desconoce') END ORDER BY "provincia" ASC, "LID_CONDUCTOR" ASC;
|
|
| 5 |
SELECT NULL AS "LID_CONDUCTOR", NULL AS "ID_ACCIDENTE", NULL AS "LID_VEHICULO", NULL AS "ID_VEHICULO", NULL AS "POSIBLE_RESPONSABLE", NULL AS "FECHA_NACIMIENTO", NULL AS "SEXO", NULL AS "NACIONALIDAD", NULL AS "PAIS_RESIDENCIA", NULL AS "PROVINCIA_RESIDENCIA", NULL AS "MUNICIPIO_RESIDENCIA", NULL AS "ASISTENCIA_SANITARIA", NULL AS "INFLU_FACT_ATENCION", NULL AS "FACTORES_ATENCION", NULL AS "INFLU_PRES_ERROR", NULL AS "PRESUNTOS_ERRORES", NULL AS "CARACT_PERMISO", NULL AS "CLASE_PERMISO", NULL AS "FECHA_PERMISO", NULL AS "MOTIVO_DESPLAZAMIENTO", NULL AS "DESPLAZAMIENTO_PREVISTO", NULL AS "ACC_SEG_CINTURON", NULL AS "ACC_SEG_CASCO", NULL AS "ACC_SEG_BRAZOS", NULL AS "ACC_SEG_ESPALDA", NULL AS "ACC_SEG_TORSO", NULL AS "ACC_SEG_MANOS", NULL AS "ACC_SEG_PIERNAS", NULL AS "ACC_SEG_PIES", NULL AS "ACC_SEG_PRENDA_REF", NULL AS "INFLU_ALCOHOL", NULL AS "PRUEBA_ALCOHOLEMIA", NULL AS "TASA_ALCOHOLEMIA1", NULL AS "TASA_ALCOHOLEMIA2", NULL AS "PRUEBA_ALC_SANGRE", NULL AS "SIGNOS_INFLU_ALCOHOL", NULL AS "INFLU_DROGAS", NULL AS "PRUEBA_DROGAS", NULL AS "AMP", NULL AS "CONFIRMADO_AMP", NULL AS "BDZ", NULL AS "CONFIRMADO_BDZ", NULL AS "COC", NULL AS "CONFIRMADO_COC", NULL AS "THC", NULL AS "CONFIRMADO_THC", NULL AS "METH", NULL AS "CONFIRMADO_METH", NULL AS "OPI", NULL AS "CONFIRMADO_OPI", NULL AS "OTRAS", NULL AS "CONFIRMADO_OTRAS", NULL AS "SIGNOS_INFLU_DROGAS", NULL AS "INFLU_PRES_INFRAC_COND", NULL AS "PRES_INFRAC_COND", NULL AS "PRES_INFRAC_SIN_LUCES", NULL AS "PRES_INFRAC_SIN_TRIANGULO", NULL AS "INFLU_PRES_INFRAC_VEL", NULL AS "PRES_INFRAC_VEL_COND", NULL AS "INFLU_OTRA_INFRAC", NULL AS "OTRA_INFRAC_COND", NULL AS "EXTRA", SUM(1) AS "Acc", EXTRACT(YEAR FROM "public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE") AS "ano", "public"."ARENA2_ACCIDENTES"."COD_PROVINCIA" AS "provincia", CASE WHEN (("ASISTENCIA_SANITARIA" = 1)) THEN ('Muerto') WHEN (("ASISTENCIA_SANITARIA" = 2)) THEN ('Grave') WHEN (((((("ASISTENCIA_SANITARIA" = 3) OR ("ASISTENCIA_SANITARIA" = 4)) OR ("ASISTENCIA_SANITARIA" = 5)) OR ("ASISTENCIA_SANITARIA" = 6)) OR ("ASISTENCIA_SANITARIA" = 7))) THEN ('Leve') WHEN (("ASISTENCIA_SANITARIA" = 8)) THEN ('Ileso') ELSE ('Se Desconoce') END AS "lesividad" FROM "public"."ARENA2_CONDUCTORES" LEFT JOIN "public"."ARENA2_ACCIDENTES" ON ( ("public"."ARENA2_CONDUCTORES"."ID_ACCIDENTE") = ("public"."ARENA2_ACCIDENTES"."ID_ACCIDENTE") ) LEFT JOIN "public"."ARENA2_VEHICULOS" ON ( ("public"."ARENA2_CONDUCTORES"."LID_VEHICULO") = ("public"."ARENA2_VEHICULOS"."LID_VEHICULO") ) WHERE ((("public"."ARENA2_ACCIDENTES"."TITULARIDAD_VIA" = 2) AND ((("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 5) OR ("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 6)) OR ("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 7))) AND (("public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE" >= DATE '2019-01-01') AND ("public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE" <= DATE '2019-12-31'))) GROUP BY EXTRACT(YEAR FROM "public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE"), "public"."ARENA2_ACCIDENTES"."COD_PROVINCIA", CASE WHEN (("ASISTENCIA_SANITARIA" = 1)) THEN ('Muerto') WHEN (("ASISTENCIA_SANITARIA" = 2)) THEN ('Grave') WHEN (((((("ASISTENCIA_SANITARIA" = 3) OR ("ASISTENCIA_SANITARIA" = 4)) OR ("ASISTENCIA_SANITARIA" = 5)) OR ("ASISTENCIA_SANITARIA" = 6)) OR ("ASISTENCIA_SANITARIA" = 7))) THEN ('Leve') WHEN (("ASISTENCIA_SANITARIA" = 8)) THEN ('Ileso') ELSE ('Se Desconoce') END
|
|
| 6 |
ORDER BY "provincia" ASC, "ano" ASC, "lesividad" ASC, "LID_CONDUCTOR" ASC; |
|
| 6 | 7 |
|
| 7 | 8 |
-- Count |
| 8 | 9 |
SELECT COUNT(*) FROM ( SELECT COUNT(*) FROM "public"."ARENA2_CONDUCTORES" LEFT JOIN "public"."ARENA2_ACCIDENTES" ON ( ("public"."ARENA2_CONDUCTORES"."ID_ACCIDENTE") = ("public"."ARENA2_ACCIDENTES"."ID_ACCIDENTE") ) LEFT JOIN "public"."ARENA2_VEHICULOS" ON ( ("public"."ARENA2_CONDUCTORES"."LID_VEHICULO") = ("public"."ARENA2_VEHICULOS"."LID_VEHICULO") ) WHERE ((("public"."ARENA2_ACCIDENTES"."TITULARIDAD_VIA" = 2) AND ((("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 5) OR ("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 6)) OR ("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 7))) AND (("public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE" >= DATE '2019-01-01') AND ("public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE" <= DATE '2019-12-31'))) GROUP BY EXTRACT(YEAR FROM "public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE"), "public"."ARENA2_ACCIDENTES"."COD_PROVINCIA", CASE WHEN (("ASISTENCIA_SANITARIA" = 1)) THEN ('Muerto') WHEN (("ASISTENCIA_SANITARIA" = 2)) THEN ('Grave') WHEN (((((("ASISTENCIA_SANITARIA" = 3) OR ("ASISTENCIA_SANITARIA" = 4)) OR ("ASISTENCIA_SANITARIA" = 5)) OR ("ASISTENCIA_SANITARIA" = 6)) OR ("ASISTENCIA_SANITARIA" = 7))) THEN ('Leve') WHEN (("ASISTENCIA_SANITARIA" = 8)) THEN ('Ileso') ELSE ('Se Desconoce') END) as _subquery_alias_ ;
|
| trunk/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.h2spatial/org.gvsig.h2spatial.h2gis132/org.gvsig.h2spatial.h2gis132.provider/src/test/resources/org/gvsig/fmap/dal/store/h2/usecases/arena2/testOrderByExtraValue.sql | ||
|---|---|---|
| 1 |
|
|
| 2 |
-- ResultSetForSetProvider SQL |
|
| 3 |
|
|
| 4 |
-- Select |
|
| 5 |
SELECT |
|
| 6 |
NULL AS "LID_CONDUCTOR", NULL AS "ID_ACCIDENTE", NULL AS "LID_VEHICULO", NULL AS "ID_VEHICULO", NULL AS "POSIBLE_RESPONSABLE", |
|
| 7 |
NULL AS "FECHA_NACIMIENTO", NULL AS "SEXO", NULL AS "NACIONALIDAD", NULL AS "PAIS_RESIDENCIA", NULL AS "PROVINCIA_RESIDENCIA", |
|
| 8 |
NULL AS "MUNICIPIO_RESIDENCIA", NULL AS "ASISTENCIA_SANITARIA", NULL AS "INFLU_FACT_ATENCION", NULL AS "FACTORES_ATENCION", |
|
| 9 |
NULL AS "INFLU_PRES_ERROR", NULL AS "PRESUNTOS_ERRORES", NULL AS "CARACT_PERMISO", NULL AS "CLASE_PERMISO", NULL AS "FECHA_PERMISO", |
|
| 10 |
NULL AS "MOTIVO_DESPLAZAMIENTO", NULL AS "DESPLAZAMIENTO_PREVISTO", NULL AS "ACC_SEG_CINTURON", NULL AS "ACC_SEG_CASCO", |
|
| 11 |
NULL AS "ACC_SEG_BRAZOS", NULL AS "ACC_SEG_ESPALDA", NULL AS "ACC_SEG_TORSO", NULL AS "ACC_SEG_MANOS", NULL AS "ACC_SEG_PIERNAS", |
|
| 12 |
NULL AS "ACC_SEG_PIES", NULL AS "ACC_SEG_PRENDA_REF", NULL AS "INFLU_ALCOHOL", NULL AS "PRUEBA_ALCOHOLEMIA", NULL AS "TASA_ALCOHOLEMIA1", |
|
| 13 |
NULL AS "TASA_ALCOHOLEMIA2", NULL AS "PRUEBA_ALC_SANGRE", NULL AS "SIGNOS_INFLU_ALCOHOL", NULL AS "INFLU_DROGAS", NULL AS "PRUEBA_DROGAS", |
|
| 14 |
NULL AS "AMP", NULL AS "CONFIRMADO_AMP", NULL AS "BDZ", NULL AS "CONFIRMADO_BDZ", NULL AS "COC", NULL AS "CONFIRMADO_COC", NULL AS "THC", |
|
| 15 |
NULL AS "CONFIRMADO_THC", NULL AS "METH", NULL AS "CONFIRMADO_METH", NULL AS "OPI", NULL AS "CONFIRMADO_OPI", NULL AS "OTRAS", |
|
| 16 |
NULL AS "CONFIRMADO_OTRAS", NULL AS "SIGNOS_INFLU_DROGAS", NULL AS "INFLU_PRES_INFRAC_COND", NULL AS "PRES_INFRAC_COND", |
|
| 17 |
NULL AS "PRES_INFRAC_SIN_LUCES", NULL AS "PRES_INFRAC_SIN_TRIANGULO", NULL AS "INFLU_PRES_INFRAC_VEL", NULL AS "PRES_INFRAC_VEL_COND", |
|
| 18 |
NULL AS "INFLU_OTRA_INFRAC", NULL AS "OTRA_INFRAC_COND", NULL AS "EXTRA", |
|
| 19 |
SUM(1) AS "Acc", |
|
| 20 |
EXTRACT(YEAR FROM "public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE") AS "ano", |
|
| 21 |
"public"."ARENA2_ACCIDENTES"."COD_PROVINCIA" AS "provincia", |
|
| 22 |
CASE |
|
| 23 |
WHEN (("ASISTENCIA_SANITARIA" = 1)) THEN ('Muerto')
|
|
| 24 |
WHEN (("ASISTENCIA_SANITARIA" = 2)) THEN ('Grave')
|
|
| 25 |
WHEN (((((("ASISTENCIA_SANITARIA" = 3) OR ("ASISTENCIA_SANITARIA" = 4)) OR ("ASISTENCIA_SANITARIA" = 5)) OR ("ASISTENCIA_SANITARIA" = 6)) OR ("ASISTENCIA_SANITARIA" = 7))) THEN ('Leve')
|
|
| 26 |
WHEN (("ASISTENCIA_SANITARIA" = 8)) THEN ('Ileso')
|
|
| 27 |
ELSE ('Se Desconoce')
|
|
| 28 |
END AS "lesividad" |
|
| 29 |
FROM "public"."ARENA2_CONDUCTORES" |
|
| 30 |
LEFT JOIN "public"."ARENA2_ACCIDENTES" ON ( ("public"."ARENA2_CONDUCTORES"."ID_ACCIDENTE") = ("public"."ARENA2_ACCIDENTES"."ID_ACCIDENTE") )
|
|
| 31 |
LEFT JOIN "public"."ARENA2_VEHICULOS" ON ( ("public"."ARENA2_CONDUCTORES"."LID_VEHICULO") = ("public"."ARENA2_VEHICULOS"."LID_VEHICULO") )
|
|
| 32 |
WHERE |
|
| 33 |
((("public"."ARENA2_ACCIDENTES"."TITULARIDAD_VIA" = 2) AND
|
|
| 34 |
((("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 5) OR
|
|
| 35 |
("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 6)) OR
|
|
| 36 |
("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 7))) AND
|
|
| 37 |
(("public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE" >= DATE '2019-01-01') AND
|
|
| 38 |
("public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE" <= DATE '2019-12-31')))
|
|
| 39 |
GROUP BY |
|
| 40 |
EXTRACT(YEAR FROM "public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE"), |
|
| 41 |
"public"."ARENA2_ACCIDENTES"."COD_PROVINCIA", |
|
| 42 |
CASE |
|
| 43 |
WHEN (("ASISTENCIA_SANITARIA" = 1)) THEN ('Muerto')
|
|
| 44 |
WHEN (("ASISTENCIA_SANITARIA" = 2)) THEN ('Grave')
|
|
| 45 |
WHEN (((((("ASISTENCIA_SANITARIA" = 3) OR ("ASISTENCIA_SANITARIA" = 4)) OR ("ASISTENCIA_SANITARIA" = 5)) OR ("ASISTENCIA_SANITARIA" = 6)) OR ("ASISTENCIA_SANITARIA" = 7))) THEN ('Leve')
|
|
| 46 |
WHEN (("ASISTENCIA_SANITARIA" = 8)) THEN ('Ileso')
|
|
| 47 |
ELSE ('Se Desconoce')
|
|
| 48 |
END |
|
| 49 |
ORDER BY "ano" ASC, "provincia" ASC, "lesividad" ASC, "LID_CONDUCTOR" ASC |
|
| 50 |
LIMIT 15; |
|
| 51 |
|
|
| 52 |
-- Count |
|
| 53 |
SELECT COUNT(*) FROM ( SELECT COUNT(*) FROM "public"."ARENA2_CONDUCTORES" |
|
| 54 |
LEFT JOIN "public"."ARENA2_ACCIDENTES" ON ( ("public"."ARENA2_CONDUCTORES"."ID_ACCIDENTE") = ("public"."ARENA2_ACCIDENTES"."ID_ACCIDENTE") )
|
|
| 55 |
LEFT JOIN "public"."ARENA2_VEHICULOS" ON ( ("public"."ARENA2_CONDUCTORES"."LID_VEHICULO") = ("public"."ARENA2_VEHICULOS"."LID_VEHICULO") )
|
|
| 56 |
WHERE |
|
| 57 |
((("public"."ARENA2_ACCIDENTES"."TITULARIDAD_VIA" = 2) AND
|
|
| 58 |
((("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 5) OR
|
|
| 59 |
("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 6)) OR
|
|
| 60 |
("public"."ARENA2_VEHICULOS"."TIPO_VEHICULO" = 7))) AND
|
|
| 61 |
(("public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE" >= DATE '2019-01-01') AND
|
|
| 62 |
("public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE" <= DATE '2019-12-31')))
|
|
| 63 |
GROUP BY |
|
| 64 |
EXTRACT(YEAR FROM "public"."ARENA2_ACCIDENTES"."FECHA_ACCIDENTE"), |
|
| 65 |
"public"."ARENA2_ACCIDENTES"."COD_PROVINCIA", |
|
| 66 |
CASE |
|
| 67 |
WHEN (("ASISTENCIA_SANITARIA" = 1)) THEN ('Muerto')
|
|
| 68 |
WHEN (("ASISTENCIA_SANITARIA" = 2)) THEN ('Grave')
|
|
| 69 |
WHEN (((((("ASISTENCIA_SANITARIA" = 3) OR ("ASISTENCIA_SANITARIA" = 4)) OR ("ASISTENCIA_SANITARIA" = 5)) OR ("ASISTENCIA_SANITARIA" = 6)) OR ("ASISTENCIA_SANITARIA" = 7))) THEN ('Leve')
|
|
| 70 |
WHEN (("ASISTENCIA_SANITARIA" = 8)) THEN ('Ileso')
|
|
| 71 |
ELSE ('Se Desconoce')
|
|
| 72 |
END |
|
| 73 |
) as _subquery_alias_ ; |
|
| trunk/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.h2spatial/org.gvsig.h2spatial.h2gis132/org.gvsig.h2spatial.h2gis132.provider/src/test/java/org/gvsig/fmap/dal/store/h2/operations/usecases/TestArena2OrderByExtraValue.java | ||
|---|---|---|
| 1 |
package org.gvsig.fmap.dal.store.h2.operations.usecases; |
|
| 2 |
|
|
| 3 |
import java.util.List; |
|
| 4 |
import junit.framework.TestCase; |
|
| 5 |
import org.gvsig.expressionevaluator.ExpressionUtils; |
|
| 6 |
import org.gvsig.fmap.dal.DataTypes; |
|
| 7 |
import org.gvsig.fmap.dal.feature.EditableFeatureAttributeDescriptor; |
|
| 8 |
import org.gvsig.fmap.dal.feature.EditableFeatureType; |
|
| 9 |
import org.gvsig.fmap.dal.feature.FeatureQuery; |
|
| 10 |
import org.gvsig.fmap.dal.feature.FeatureStore; |
|
| 11 |
import org.gvsig.fmap.dal.feature.FeatureType; |
|
| 12 |
import org.gvsig.fmap.dal.impl.expressionevaluator.DefaultFeatureAttributeEmulatorExpression; |
|
| 13 |
import org.gvsig.fmap.dal.store.h2.TestUtils; |
|
| 14 |
import org.gvsig.fmap.dal.store.jdbc2.JDBCHelper; |
|
| 15 |
import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory; |
|
| 16 |
import org.gvsig.fmap.dal.store.jdbc2.OperationsFactory.TableReference; |
|
| 17 |
import org.gvsig.fmap.dal.store.jdbc2.spi.JDBCSQLBuilderBase; |
|
| 18 |
import org.gvsig.fmap.dal.store.jdbc2.spi.operations.CountOperation; |
|
| 19 |
import org.gvsig.fmap.dal.store.jdbc2.spi.operations.ResultSetForSetProviderOperation; |
|
| 20 |
import org.gvsig.tools.library.impl.DefaultLibrariesInitializer; |
|
| 21 |
import org.slf4j.Logger; |
|
| 22 |
import org.slf4j.LoggerFactory; |
|
| 23 |
|
|
| 24 |
@SuppressWarnings("UseSpecificCatch")
|
|
| 25 |
public class TestArena2OrderByExtraValue extends TestCase {
|
|
| 26 |
|
|
| 27 |
private static final Logger LOGGER = LoggerFactory.getLogger(TestArena2OrderByExtraValue.class); |
|
| 28 |
|
|
| 29 |
public TestArena2OrderByExtraValue(String testName) {
|
|
| 30 |
super(testName); |
|
| 31 |
} |
|
| 32 |
|
|
| 33 |
@Override |
|
| 34 |
protected void setUp() throws Exception {
|
|
| 35 |
super.setUp(); |
|
| 36 |
new DefaultLibrariesInitializer().fullInitialize(); |
|
| 37 |
} |
|
| 38 |
|
|
| 39 |
@Override |
|
| 40 |
protected void tearDown() throws Exception {
|
|
| 41 |
super.tearDown(); |
|
| 42 |
} |
|
| 43 |
|
|
| 44 |
protected List<String> getExpectedSQLs(String name) throws Exception {
|
|
| 45 |
return TestUtils.getSQLs(name); |
|
| 46 |
} |
|
| 47 |
|
|
| 48 |
public void testOrderByExtraValue() throws Exception {
|
|
| 49 |
try {
|
|
| 50 |
JDBCHelper helper = TestUtils.createJDBCHelper(); |
|
| 51 |
JDBCSQLBuilderBase sqlbuilder = helper.createSQLBuilder(); |
|
| 52 |
OperationsFactory operations = helper.getOperations(); |
|
| 53 |
|
|
| 54 |
List<String> expectedSQLs = getExpectedSQLs("usecases/arena2/testOrderByExtraValue.sql");
|
|
| 55 |
|
|
| 56 |
FeatureStore arena2_conductores = TestUtils.openStoreTest("usecases/arena2/ARENA2_CONDUCTORES.csv");
|
|
| 57 |
FeatureStore arena2_accidentes = TestUtils.openStoreTest("usecases/arena2/ARENA2_ACCIDENTES.csv");
|
|
| 58 |
FeatureStore arena2_vehiculos = TestUtils.openStoreTest("usecases/arena2/ARENA2_VEHICULOS.csv");
|
|
| 59 |
|
|
| 60 |
TableReference table = operations.createTableReference( |
|
| 61 |
"dbtest", |
|
| 62 |
"public", //sqlbuilder.default_schema(), |
|
| 63 |
"ARENA2_CONDUCTORES", |
|
| 64 |
null |
|
| 65 |
); |
|
| 66 |
FeatureType featureType = arena2_conductores.getDefaultFeatureType(); |
|
| 67 |
EditableFeatureType eFeatureType = featureType.getEditable(); |
|
| 68 |
FeatureQuery query = arena2_conductores.createFeatureQuery(); |
|
| 69 |
query.setFilter("( ( ( (FOREING_VALUE('ID_ACCIDENTE.TITULARIDAD_VIA') = 2) )) AND \n" +
|
|
| 70 |
"( (( (FOREING_VALUE('LID_VEHICULO.TIPO_VEHICULO') = 5) ) OR ( (FOREING_VALUE('LID_VEHICULO.TIPO_VEHICULO') = 6) ) OR ( (FOREING_VALUE('LID_VEHICULO.TIPO_VEHICULO') = 7) )))) AND \n" +
|
|
| 71 |
"( (( (FOREING_VALUE('ID_ACCIDENTE.FECHA_ACCIDENTE') >= DATE '2019-01-01') ) AND ( (FOREING_VALUE('ID_ACCIDENTE.FECHA_ACCIDENTE') <= DATE '2019-12-31') )))");
|
|
| 72 |
addExtraColumn(eFeatureType, query, "ano", DataTypes.INTEGER, "EXTRACT(YEAR FROM FOREING_VALUE('ID_ACCIDENTE.FECHA_ACCIDENTE'))");
|
|
| 73 |
addExtraColumn(eFeatureType, query, "provincia", DataTypes.STRING, "FOREING_VALUE('ID_ACCIDENTE.COD_PROVINCIA')");
|
|
| 74 |
addExtraColumn(eFeatureType, query, "lesividad", DataTypes.STRING, "CASE\n" |
|
| 75 |
+ "WHEN ASISTENCIA_SANITARIA=1 THEN 'Muerto'\n" |
|
| 76 |
+ "WHEN ASISTENCIA_SANITARIA=2 THEN 'Grave'\n" |
|
| 77 |
+ "WHEN ASISTENCIA_SANITARIA=3 or ASISTENCIA_SANITARIA=4 or ASISTENCIA_SANITARIA=5 or ASISTENCIA_SANITARIA=6 or ASISTENCIA_SANITARIA=7 THEN 'Leve'\n" |
|
| 78 |
+ "WHEN ASISTENCIA_SANITARIA=8 THEN 'Ileso'\n" |
|
| 79 |
+ "ELSE 'Se Desconoce'\n" |
|
| 80 |
+ "END CASE"); |
|
| 81 |
addExtraColumn(eFeatureType, query, "Acc", DataTypes.INTEGER, "1"); |
|
| 82 |
|
|
| 83 |
query.getGroupByColumns().add("ano");
|
|
| 84 |
query.getGroupByColumns().add("provincia");
|
|
| 85 |
query.getGroupByColumns().add("lesividad");
|
|
| 86 |
query.getAggregateFunctions().put("Acc", "SUM");
|
|
| 87 |
query.getOrder().add("ano");
|
|
| 88 |
query.getOrder().add("provincia");
|
|
| 89 |
query.getOrder().add("lesividad");
|
|
| 90 |
|
|
| 91 |
|
|
| 92 |
CountOperation count = operations.createCount(eFeatureType, table, null, query); |
|
| 93 |
String sqlcount = count.getSQL(); |
|
| 94 |
System.out.println("###### SQL:" + sqlcount);
|
|
| 95 |
System.out.println("###### EXP:" + expectedSQLs.get(1));
|
|
| 96 |
assertEquals("Count SQL", expectedSQLs.get(1), sqlcount);
|
|
| 97 |
|
|
| 98 |
ResultSetForSetProviderOperation resultSetForSetProvider = operations.createResultSetForSetProvider( |
|
| 99 |
table, |
|
| 100 |
null, |
|
| 101 |
null, |
|
| 102 |
query, |
|
| 103 |
eFeatureType, |
|
| 104 |
eFeatureType, |
|
| 105 |
15, |
|
| 106 |
0, |
|
| 107 |
0 |
|
| 108 |
); |
|
| 109 |
String sql = resultSetForSetProvider.getSQL(); |
|
| 110 |
System.out.println("###### SQL:" + sql);
|
|
| 111 |
System.out.println("###### EXP:" + expectedSQLs.get(0));
|
|
| 112 |
|
|
| 113 |
assertEquals("Select SQL", expectedSQLs.get(0), sql);
|
|
| 114 |
|
|
| 115 |
|
|
| 116 |
} catch (Throwable th) {
|
|
| 117 |
th.printStackTrace(); |
|
| 118 |
throw th; |
|
| 119 |
} |
|
| 120 |
} |
|
| 121 |
|
|
| 122 |
private void addExtraColumn(EditableFeatureType eFeatureType, FeatureQuery query, String name, int type, String exp) {
|
|
| 123 |
EditableFeatureAttributeDescriptor extraColumn = query.getExtraColumn().add(name, type); |
|
| 124 |
extraColumn.setFeatureAttributeEmulator(new DefaultFeatureAttributeEmulatorExpression(eFeatureType, ExpressionUtils.createExpression(exp))); |
|
| 125 |
} |
|
| 126 |
|
|
| 127 |
} |
|
| trunk/org.gvsig.desktop/org.gvsig.desktop.plugin/org.gvsig.h2spatial/org.gvsig.h2spatial.h2gis132/org.gvsig.h2spatial.h2gis132.provider/src/test/java/org/gvsig/fmap/dal/store/h2/operations/sql/TestResultSetForSetProvider.java | ||
|---|---|---|
| 338 | 338 |
|
| 339 | 339 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get(1), sql);
|
| 340 | 340 |
} catch (Throwable th) {
|
| 341 |
LOGGER.warn("",th);
|
|
| 341 | 342 |
throw th; |
| 342 | 343 |
} |
| 343 | 344 |
} |
| ... | ... | |
| 392 | 393 |
|
| 393 | 394 |
assertEquals("ResultSetForSetProvider SQL", expectedSQLs.get(10), sql);
|
| 394 | 395 |
} catch (Throwable th) {
|
| 396 |
LOGGER.warn("", th);
|
|
| 395 | 397 |
throw th; |
| 396 | 398 |
} |
| 397 | 399 |
} |
| trunk/org.gvsig.desktop/org.gvsig.desktop.library/org.gvsig.expressionevaluator/org.gvsig.expressionevaluator.lib/org.gvsig.expressionevaluator.lib.api/src/main/java/org/gvsig/expressionevaluator/ExpressionBuilder.java | ||
|---|---|---|
| 152 | 152 |
public void accept(Visitor visitor, VisitorFilter filter); |
| 153 | 153 |
} |
| 154 | 154 |
|
| 155 |
public interface Value extends Visitable, PropertiesSupport {
|
|
| 155 |
public interface Value extends Visitable, PropertiesSupport, org.gvsig.tools.lang.Cloneable {
|
|
| 156 | 156 |
public String toString(Formatter<Value> formatter); |
| 157 | 157 |
public void replace(Value target, Value replacement); |
| 158 | 158 |
public void copyPropertiesFrom(PropertiesSupport properties); |
| 159 |
|
|
| 160 |
@Override |
|
| 161 |
public Value clone() throws CloneNotSupportedException; |
|
| 162 |
|
|
| 159 | 163 |
} |
| 160 | 164 |
|
| 161 | 165 |
public interface Group extends Value {
|
| ... | ... | |
| 212 | 216 |
|
| 213 | 217 |
public abstract class AbstractValue implements Value {
|
| 214 | 218 |
|
| 215 |
protected final PropertiesSupport properties;
|
|
| 219 |
protected PropertiesSupportHelper properties;
|
|
| 216 | 220 |
|
| 217 | 221 |
protected AbstractValue() {
|
| 218 | 222 |
this.properties = new PropertiesSupportHelper(); |
| 219 | 223 |
} |
| 224 |
|
|
| 225 |
@Override |
|
| 226 |
public Value clone() throws CloneNotSupportedException {
|
|
| 227 |
AbstractValue other = (AbstractValue) super.clone(); |
|
| 228 |
other.properties = properties.clone(); |
|
| 229 |
return other; |
|
| 230 |
} |
|
| 220 | 231 |
|
| 232 |
|
|
| 221 | 233 |
@Override |
| 222 | 234 |
public void copyPropertiesFrom(PropertiesSupport properties) {
|
| 223 | 235 |
for (Map.Entry<String, Object> entry : properties.getProperties().entrySet()) {
|
| trunk/org.gvsig.desktop/org.gvsig.desktop.library/org.gvsig.expressionevaluator/org.gvsig.expressionevaluator.lib/org.gvsig.expressionevaluator.lib.impl/src/main/java/org/gvsig/expressionevaluator/impl/DefaultExpressionBuilder.java | ||
|---|---|---|
| 70 | 70 |
public GroupBase(Value value) {
|
| 71 | 71 |
this.value = value; |
| 72 | 72 |
} |
| 73 |
|
|
| 74 |
@Override |
|
| 75 |
public GroupBase clone() throws CloneNotSupportedException {
|
|
| 76 |
GroupBase other = (GroupBase) super.clone(); |
|
| 77 |
other.value = value.clone(); |
|
| 78 |
return other; |
|
| 79 |
} |
|
| 73 | 80 |
|
| 74 | 81 |
@Override |
| 75 | 82 |
public Value value() {
|
| ... | ... | |
| 114 | 121 |
this.name = name; |
| 115 | 122 |
this.builder = builder; |
| 116 | 123 |
} |
| 124 |
|
|
| 125 |
public VariableBase clone() throws CloneNotSupportedException {
|
|
| 126 |
VariableBase other = (VariableBase) super.clone(); |
|
| 127 |
return other; |
|
| 128 |
} |
|
| 117 | 129 |
|
| 118 | 130 |
@Override |
| 119 | 131 |
public String name() {
|
| ... | ... | |
| 165 | 177 |
this.name = null; |
| 166 | 178 |
this.value = null; |
| 167 | 179 |
} |
| 180 |
|
|
| 181 |
public ParameterBase clone() throws CloneNotSupportedException {
|
|
| 182 |
ParameterBase other = (ParameterBase) super.clone(); |
|
| 183 |
return other; |
|
| 184 |
} |
|
| 168 | 185 |
|
| 169 | 186 |
@Override |
| 170 | 187 |
public Parameter as_constant() {
|
| ... | ... | |
| 279 | 296 |
this.builder = builder; |
| 280 | 297 |
} |
| 281 | 298 |
|
| 299 |
public Constant clone() throws CloneNotSupportedException {
|
|
| 300 |
ConstantBase other = (ConstantBase) super.clone(); |
|
| 301 |
return other; |
|
| 302 |
} |
|
| 303 |
|
|
| 282 | 304 |
@Override |
| 283 | 305 |
public Object value() {
|
| 284 | 306 |
return this.value; |
| ... | ... | |
| 318 | 340 |
public ExpressionBuilder builder() {
|
| 319 | 341 |
return this.builder; // Ojo, no esta en el API. |
| 320 | 342 |
} |
| 343 |
|
|
| 321 | 344 |
} |
| 322 | 345 |
|
| 323 | 346 |
public class CustomBase extends AbstractValue implements Custom {
|
| ... | ... | |
| 331 | 354 |
public CustomBase(Object value) {
|
| 332 | 355 |
this.value = value; |
| 333 | 356 |
} |
| 357 |
|
|
| 358 |
public Custom clone() throws CloneNotSupportedException {
|
|
| 359 |
CustomBase other = (CustomBase) super.clone(); |
|
| 360 |
if(other.values!=null) {
|
|
| 361 |
for (int i = 0; i < values.size(); i++) {
|
|
| 362 |
Value v = (Value) values.get(i).clone(); |
|
| 363 |
other.values.set(i, v); |
|
| 364 |
} |
|
| 365 |
} |
|
| 366 |
|
|
| 367 |
return other; |
|
| 368 |
} |
|
| 334 | 369 |
|
| 335 | 370 |
@Override |
| 336 | 371 |
public void accept(Visitor visitor, VisitorFilter filter) {
|
| ... | ... | |
| 404 | 439 |
this.name = name; |
| 405 | 440 |
this.format = format; |
| 406 | 441 |
} |
| 442 |
|
|
| 443 |
public FunctionBase clone() throws CloneNotSupportedException {
|
|
| 444 |
FunctionBase other = (FunctionBase) super.clone(); |
|
| 445 |
if (other.parameters != null) {
|
|
| 446 |
for (int i = 0; i < parameters.size(); i++) {
|
|
| 447 |
Value v = (Value) parameters.get(i).clone(); |
|
| 448 |
other.parameters.set(i, v); |
|
| 449 |
} |
|
| 450 |
} |
|
| 407 | 451 |
|
| 452 |
return other; |
|
| 453 |
} |
|
| 454 |
|
|
| 408 | 455 |
public FunctionBase(String name) {
|
| 409 | 456 |
this(name,null); |
| 410 | 457 |
} |
| ... | ... | |
| 511 | 558 |
super(name); |
| 512 | 559 |
this.instance = instance; |
| 513 | 560 |
} |
| 561 |
|
|
| 562 |
public MethodBase clone() throws CloneNotSupportedException {
|
|
| 563 |
MethodBase other = (MethodBase) super.clone(); |
|
| 564 |
other.instance = instance.clone(); |
|
| 565 |
return other; |
|
| 566 |
} |
|
| 567 |
|
|
| 514 | 568 |
|
| 515 | 569 |
@Override |
| 516 | 570 |
public Value instance() {
|
| trunk/org.gvsig.desktop/org.gvsig.desktop.library/org.gvsig.expressionevaluator/org.gvsig.expressionevaluator.geometry/org.gvsig.expressionevaluator.geometry.lib/org.gvsig.expressionevaluator.geometry.lib.impl/src/main/java/org/gvsig/expressionevaluator/impl/DefaultGeometryExpressionBuilderHelper.java | ||
|---|---|---|
| 68 | 68 |
} |
| 69 | 69 |
|
| 70 | 70 |
@Override |
| 71 |
public Value clone() throws CloneNotSupportedException {
|
|
| 72 |
GeometryParameterBase other = (GeometryParameterBase) super.clone(); |
|
| 73 |
other.srs = (Value) org.gvsig.tools.lang.Cloneable.cloneQuietly(srs); |
|
| 74 |
return other; |
|
| 75 |
} |
|
| 76 |
|
|
| 77 |
|
|
| 78 |
|
|
| 79 |
@Override |
|
| 71 | 80 |
public void accept(Visitor visitor, VisitorFilter filter) {
|
| 72 | 81 |
super.accept(visitor, filter); |
| 73 | 82 |
if (this.srs != null) {
|
Also available in: Unified diff