Statistics
| Revision:

root / trunk / extensions / extJDBC / src / com / iver / cit / gvsig / fmap / drivers / jdbc / postgis / PostGisDriver.java @ 38040

History | View | Annotate | Download (47.9 KB)

1
/*
2
 * Created on 04-mar-2005
3
 *
4
 * gvSIG. Sistema de Informaci?n Geogr?fica de la Generalitat Valenciana
5
 *
6
 * Copyright (C) 2004 IVER T.I. and Generalitat Valenciana.
7
 *
8
 * This program is free software; you can redistribute it and/or
9
 * modify it under the terms of the GNU General Public License
10
 * as published by the Free Software Foundation; either version 2
11
 * of the License, or (at your option) any later version.
12
 *
13
 * This program is distributed in the hope that it will be useful,
14
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
15
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
16
 * GNU General Public License for more details.
17
 *
18
 * You should have received a copy of the GNU General Public License
19
 * along with this program; if not, write to the Free Software
20
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
21
 *
22
 * For more information, contact:
23
 *
24
 *  Generalitat Valenciana
25
 *   Conselleria d'Infraestructures i Transport
26
 *   Av. Blasco Ib??ez, 50
27
 *   46010 VALENCIA
28
 *   SPAIN
29
 *
30
 *      +34 963862235
31
 *   gvsig@gva.es
32
 *      www.gvsig.gva.es
33
 *
34
 *    or
35
 *
36
 *   IVER T.I. S.A
37
 *   Salamanca 50
38
 *   46005 Valencia
39
 *   Spain
40
 *
41
 *   +34 963163400
42
 *   dac@iver.es
43
 */
44
package com.iver.cit.gvsig.fmap.drivers.jdbc.postgis;
45

    
46
import java.awt.geom.Rectangle2D;
47
import java.math.BigDecimal;
48
import java.math.BigInteger;
49
import java.nio.ByteBuffer;
50
import java.sql.Connection;
51
import java.sql.PreparedStatement;
52
import java.sql.ResultSet;
53
import java.sql.ResultSetMetaData;
54
import java.sql.SQLException;
55
import java.sql.Statement;
56
import java.sql.Timestamp;
57
import java.sql.Types;
58
import java.util.ArrayList;
59
import java.util.Calendar;
60
import java.util.GregorianCalendar;
61
import java.util.HashMap;
62
import java.util.Hashtable;
63
import java.util.Map;
64
import java.util.Random;
65

    
66
import org.apache.log4j.Logger;
67
import org.joda.time.DateTime;
68
import org.postgis.PGbox2d;
69
import org.postgis.PGbox3d;
70
import org.postgresql.util.PSQLException;
71
import org.postgresql.util.PSQLState;
72

    
73
import com.hardcode.gdbms.driver.exceptions.InitializeWriterException;
74
import com.hardcode.gdbms.driver.exceptions.ReadDriverException;
75
import com.hardcode.gdbms.engine.data.edition.DataWare;
76
import com.hardcode.gdbms.engine.values.Value;
77
import com.hardcode.gdbms.engine.values.ValueFactory;
78
import com.iver.andami.PluginServices;
79
import com.iver.cit.gvsig.fmap.core.FShape;
80
import com.iver.cit.gvsig.fmap.core.ICanReproject;
81
import com.iver.cit.gvsig.fmap.core.IGeometry;
82
import com.iver.cit.gvsig.fmap.drivers.ConnectionJDBC;
83
import com.iver.cit.gvsig.fmap.drivers.DBException;
84
import com.iver.cit.gvsig.fmap.drivers.DBLayerDefinition;
85
import com.iver.cit.gvsig.fmap.drivers.DefaultJDBCDriver;
86
import com.iver.cit.gvsig.fmap.drivers.DriverAttributes;
87
import com.iver.cit.gvsig.fmap.drivers.FieldDescription;
88
import com.iver.cit.gvsig.fmap.drivers.IConnection;
89
import com.iver.cit.gvsig.fmap.drivers.IFeatureIterator;
90
import com.iver.cit.gvsig.fmap.drivers.WKBParser3;
91
import com.iver.cit.gvsig.fmap.drivers.XTypes;
92
import com.iver.cit.gvsig.fmap.edition.IWriteable;
93
import com.iver.cit.gvsig.fmap.edition.IWriter;
94

    
95
/**
96
 * @author FJP
97
 */
98
public class PostGisDriver extends DefaultJDBCDriver implements ICanReproject,
99
                IWriteable {
100

    
101

    
102
        private static Logger logger = Logger.getLogger(PostGisDriver.class
103
                        .getName());
104

    
105
        private static int FETCH_SIZE = 5000;
106

    
107
        // To avoid problems when using wkb_cursor with same layer.
108
        // I mean, when you add twice or more the same layer using
109
        // the same connection
110

    
111
        private static int CURSOR_ID = 0;
112

    
113
        private int myCursorId;
114

    
115
        private PostGISWriter writer = new PostGISWriter();
116

    
117
        private WKBParser3 parser = new WKBParser3();
118

    
119
        private int fetch_min = -1;
120

    
121
        private int fetch_max = -1;
122

    
123
        private String sqlOrig;
124

    
125
        /**
126
         * Used by setAbsolutePosition
127
         */
128
        private String sqlTotal;
129

    
130
        private String strEPSG = null;
131

    
132
        private String originalEPSG = null;
133

    
134
        private Rectangle2D fullExtent = null;
135

    
136
        private String completeWhere;
137

    
138
        boolean bShapeTypeRevised = false;
139

    
140

    
141
        /**
142
         * It stores all schemas privileges, in order to avoid checking it everytime
143
         * canRead() is called.
144
         */
145
        private Map<String, Boolean> schemasUsage = new HashMap<String, Boolean>();
146

    
147
        private String cursorName;
148

    
149
        private static final BigInteger _nbase = new BigInteger("10000");
150

    
151
        private static final BigInteger _nbasePow2 = _nbase.pow(2);
152

    
153
        private static final BigInteger _nbasePow4 = _nbase.pow(4);
154

    
155
        private static final long nbaseLong = _nbase.longValue();
156

    
157
        private static final long nbaseLongPow2 = nbaseLong * nbaseLong;
158

    
159
        private static final int nbaseInt = (int) nbaseLong;
160

    
161
        public static final String NAME = "PostGIS JDBC Driver";
162

    
163
        protected static BigInteger getNBase() {
164
                return _nbase;
165
        }
166

    
167
        protected static BigInteger getNBasePow2() {
168
                return _nbasePow2;
169
        }
170

    
171
        protected static BigInteger getNBasePow4() {
172
                return _nbasePow4;
173
        }
174

    
175
        static {
176
                try {
177
                        Class.forName("org.postgresql.Driver");
178
                } catch (ClassNotFoundException e) {
179
                        throw new RuntimeException(e);
180
                }
181
        }
182

    
183
        /**
184
         *
185
         */
186
        public PostGisDriver() {
187
                // To avoid problems when using wkb_cursor with same layer.
188
                // I mean, when you add twice or more the same layer using
189
                // the same connection
190
                CURSOR_ID++;
191
                myCursorId = CURSOR_ID;
192
        }
193

    
194
        /*
195
         * (non-Javadoc)
196
         * 
197
         * @see
198
         * com.iver.cit.gvsig.fmap.drivers.VectorialDriver#getDriverAttributes()
199
         */
200
        public DriverAttributes getDriverAttributes() {
201
                return null;
202
        }
203

    
204
        /*
205
         * (non-Javadoc)
206
         * 
207
         * @see com.hardcode.driverManager.Driver#getName()
208
         */
209
        public String getName() {
210
                return NAME;
211
        }
212

    
213
        /**
214
         * @throws ReadDriverException
215
         * @see com.iver.cit.gvsig.fmap.layers.ReadableVectorial#getShape(int)
216
         */
217
        public IGeometry getShape(int index) throws ReadDriverException {
218
                IGeometry geom = null;
219
                try {
220
                        setAbsolutePosition(index);
221
                        // strAux = rs.getString(1);
222
                        // geom = parser.read(strAux);
223
                        if (rs != null) {
224
                                byte[] data = rs.getBytes(1);
225
                                if (data == null) // null geometry.
226
                                        return null;
227
                                geom = parser.parse(data);
228
                        }
229
                } catch (SQLException e) {
230
                        throw new ReadDriverException(this.getName(), e);
231
                }
232

    
233
                return geom;
234
        }
235

    
236
        /**
237
         * First, the geometry field. After, the rest of fields
238
         * 
239
         * @return
240
         */
241
        /*
242
         * public String getTotalFields() { String strAux = "AsBinary(" +
243
         * getLyrDef().getFieldGeometry() + ")"; String[] fieldNames =
244
         * getLyrDef().getFieldNames(); for (int i=0; i< fieldNames.length; i++) {
245
         * strAux = strAux + ", " + fieldNames[i]; } return strAux; }
246
         */
247

    
248
        /**
249
         * Antes de llamar a esta funci?n hay que haber fijado el workingArea si se
250
         * quiere usar.
251
         * 
252
         * @param conn
253
         * @throws DBException
254
         */
255
        public void setData(IConnection conn, DBLayerDefinition lyrDef)
256
                        throws DBException {
257
                this.conn = conn;
258
                // TODO: Deber?amos poder quitar Conneciton de la llamada y meterlo
259
                // en lyrDef desde el principio.
260

    
261
                lyrDef.setConnection(conn);
262
                setLyrDef(lyrDef);
263

    
264
                getTableEPSG_and_shapeType(conn, lyrDef);
265

    
266
                getLyrDef().setSRID_EPSG(originalEPSG);
267

    
268
                try {
269
                        ((ConnectionJDBC) conn).getConnection().setAutoCommit(false);
270
                        sqlOrig = "SELECT " + getTotalFields() + " FROM "
271
                        + getLyrDef().getComposedTableName() + " ";
272

    
273
                        if (canReproject(strEPSG)) {
274
                                completeWhere = getCompoundWhere(sqlOrig, workingArea, strEPSG);
275
                        } else {
276
                                completeWhere = getCompoundWhere(sqlOrig, workingArea,
277
                                                originalEPSG);
278
                        }
279

    
280
                        sqlTotal = sqlOrig + completeWhere + " ORDER BY " + getLyrDef().getFieldID();
281

    
282
                        logger.info("Cadena SQL:" + sqlTotal);
283
                        Statement st = ((ConnectionJDBC) conn).getConnection().createStatement(
284
                                        ResultSet.TYPE_SCROLL_INSENSITIVE,
285
                                        ResultSet.CONCUR_READ_ONLY);
286

    
287
                        myCursorId++;
288
                        cursorName = "wkb_cursor_" + myCursorId + getTableName();
289
                        
290
                        st.execute("declare " + cursorName + " binary scroll cursor with hold for " + sqlTotal);
291
                        
292
                        rs = st.executeQuery("fetch forward " + FETCH_SIZE + " in " + cursorName);
293
                        fetch_min = 0;
294
                        fetch_max = FETCH_SIZE - 1;
295
                        metaData = rs.getMetaData();
296
                        doRelateID_FID();
297

    
298
                        writer.setCreateTable(false);
299
                        writer.setWriteAll(false);
300
                        writer.initialize(lyrDef);
301

    
302
                } catch (SQLException e) {
303
                        
304
                        try {
305
                                ((ConnectionJDBC) conn).getConnection().rollback();
306
                        } catch (SQLException e1) {
307
                                logger.warn("Unable to rollback connection after problem (" + e.getMessage() + ") in setData()");
308
                        }
309
                        
310
                        try {
311
                                if (rs != null) { rs.close(); }
312
                        } catch (SQLException e1) {
313
                                throw new DBException(e);
314
                        }
315
                        throw new DBException(e);
316
                } catch (InitializeWriterException e) {
317
                        throw new DBException(e);
318
                }
319
        }
320

    
321
        /**
322
         * @see com.iver.cit.gvsig.fmap.layers.ReadableVectorial#getFullExtent()
323
         */
324
        public Rectangle2D getFullExtent() throws ReadDriverException {
325
                if (fullExtent == null) {
326
                        try {
327
                                Statement s = ((ConnectionJDBC) conn).getConnection()
328
                                                .createStatement();
329
                                String query = "SELECT extent(\""
330
                                    + getLyrDef().getFieldGeometry()
331
                                    + "\") AS FullExtent FROM " + getLyrDef().getComposedTableName()
332
                                    + " " + getCompleteWhere();
333
                                ResultSet r = s.executeQuery(query);
334
                                r.next();
335
                                String strAux = r.getString(1);
336
                                System.out.println("fullExtent = " + strAux);
337
                                if (strAux == null) {
338
                                        logger.debug("La capa " + getLyrDef().getName()
339
                                                        + " no tiene FULLEXTENT");
340
                                        return null;
341
                                }
342
                                if (strAux.startsWith("BOX3D")) {
343
                                        PGbox3d regeom = new PGbox3d(strAux);
344
                                        double x = regeom.getLLB().x;
345
                                        double y = regeom.getLLB().y;
346
                                        double w = regeom.getURT().x - x;
347
                                        double h = regeom.getURT().y - y;
348
                                        fullExtent = new Rectangle2D.Double(x, y, w, h);
349
                                } else {
350
                                        PGbox2d regeom = new PGbox2d(strAux);
351
                                        double x = regeom.getLLB().x;
352
                                        double y = regeom.getLLB().y;
353
                                        double w = regeom.getURT().x - x;
354
                                        double h = regeom.getURT().y - y;
355
                                        fullExtent = new Rectangle2D.Double(x, y, w, h);
356
                                }
357
                        } catch (SQLException e) {
358
                                throw new ReadDriverException(this.getName(), e);
359
                        }
360

    
361
                }
362

    
363
                return fullExtent;
364
        }
365

    
366
        /*
367
         * (non-Javadoc)
368
         * 
369
         * @see
370
         * com.iver.cit.gvsig.fmap.drivers.VectorialDatabaseDriver#getGeometryIterator
371
         * (java.lang.String)
372
         */
373
        public IFeatureIterator getFeatureIterator(String sql)
374
                        throws ReadDriverException {
375
                PostGisFeatureIterator geomIterator = null;
376
                geomIterator = myGetFeatureIterator(sql);
377
                geomIterator.setLyrDef(getLyrDef());
378

    
379
                return geomIterator;
380
        }
381

    
382
    private PostGisFeatureIterator myGetFeatureIterator(String sql)
383
            throws ReadDriverException {
384
        PostGisFeatureIterator geomIterator = null;
385
        try {
386
            String provCursorName = "wkb_cursor_prov_"
387
                    + Long.toString(Math.abs(new Random().nextLong()))
388
                    + getTableName();
389
            bCursorActivo = true;
390
            geomIterator = new PostGisFeatureIterator(
391
                    ((ConnectionJDBC) conn).getConnection(), provCursorName,
392
                    sql);
393
        } catch (SQLException e) {
394
            throw new ReadDriverException("PostGIS Driver", e);
395
                }
396
        return geomIterator;
397
    }
398

    
399
        public IFeatureIterator getFeatureIterator(Rectangle2D r, String strEPSG)
400
        throws ReadDriverException {
401
                if (workingArea != null) {
402
                    r = r.createIntersection(workingArea);
403
                }
404

    
405
                String sqlAux;
406
                if (canReproject(strEPSG)) {
407
                        sqlAux = sqlOrig + getCompoundWhere(sqlOrig, r, strEPSG);
408
                } else {
409
                        sqlAux = sqlOrig + getCompoundWhere(sqlOrig, r, originalEPSG);
410
                }
411

    
412
                System.out.println("SqlAux getFeatureIterator = " + sqlAux);
413

    
414
                return getFeatureIterator(sqlAux);
415
        }
416

    
417
        /**
418
         * Le pasas el rect?ngulo que quieres pedir. La primera vez es el
419
         * workingArea, y las siguientes una interseccion de este rectangulo con el
420
         * workingArea
421
         * 
422
         * @param r
423
         * @param strEPSG
424
         * @return
425
         */
426
        private String getCompoundWhere(String sql, Rectangle2D r, String strEPSG) {
427
                if (r == null)
428
                        return getWhereClause();
429

    
430
                double xMin = r.getMinX();
431
                double yMin = r.getMinY();
432
                double xMax = r.getMaxX();
433
                double yMax = r.getMaxY();
434
                String wktBox = "GeometryFromText('LINESTRING(" + xMin + " " + yMin
435
                + ", " + xMax + " " + yMin + ", " + xMax + " " + yMax + ", "
436
                + xMin + " " + yMax + ")', " + strEPSG + ")";
437
                String sqlAux;
438
                if (getWhereClause().toUpperCase().indexOf("WHERE") != -1)
439
                    sqlAux = getWhereClause() + " AND \"" + getLyrDef().getFieldGeometry() + "\" && " + wktBox;
440
                else
441
                    sqlAux = "WHERE \"" + getLyrDef().getFieldGeometry() + "\" && "
442
                        + wktBox;
443
                return sqlAux;
444
        }
445

    
446
        /**
447
         * @see com.iver.cit.gvsig.fmap.drivers.IVectorialDatabaseDriver#getConnectionStringBeginning()
448
         */
449
        public String getConnectionStringBeginning() {
450
                return "jdbc:postgresql:";
451
        }
452

    
453
        /*
454
         * (non-Javadoc)
455
         * 
456
         * @see com.iver.cit.gvsig.fmap.drivers.DefaultDBDriver#getFieldValue(long,
457
         * int)
458
         */
459
        @Override
460
        public Value getFieldValue(long rowIndex, int idField)
461
        throws ReadDriverException {
462
                // EL ABSOLUTE NO HACE QUE SE VUELVAN A LEER LAS
463
                // FILAS, ASI QUE MONTAMOS ESTA HISTORIA PARA QUE
464
                // LO HAGA
465
                int index = (int) (rowIndex);
466
                try {
467
                        setAbsolutePosition(index);
468
                        int fieldId = idField + 2;
469
                        return getFieldValue(rs, fieldId);
470

    
471
                } catch (SQLException e) {
472
                        throw new ReadDriverException("PostGIS Driver", e);
473
                }
474
        }
475

    
476
        static Value getFieldValue(ResultSet aRs, int fieldId) throws SQLException {
477
                ResultSetMetaData metaData = aRs.getMetaData();
478
                byte[] byteBuf = aRs.getBytes(fieldId);
479
                if (byteBuf == null)
480
                        return ValueFactory.createNullValue();
481
                else {
482
                        ByteBuffer buf = ByteBuffer.wrap(byteBuf);
483
                        if (metaData.getColumnType(fieldId) == Types.VARCHAR)
484
                                return ValueFactory.createValue(aRs.getString(fieldId));
485
                        if (metaData.getColumnType(fieldId) == Types.CHAR){
486
                                String character = aRs.getString(fieldId);
487
                                if (character != null){
488
                                        return ValueFactory.createValue(character.trim());
489
                                }else{
490
                                        return ValueFactory.createValue(character);
491
                                }
492
                        }
493
                        if (metaData.getColumnType(fieldId) == Types.FLOAT)
494
                                return ValueFactory.createValue(buf.getFloat());
495
                        if (metaData.getColumnType(fieldId) == Types.DOUBLE)
496
                                return ValueFactory.createValue(buf.getDouble());
497
                        if (metaData.getColumnType(fieldId) == Types.REAL)
498
                                return ValueFactory.createValue(buf.getFloat());
499
                        if (metaData.getColumnType(fieldId) == Types.INTEGER)
500
                                return ValueFactory.createValue(buf.getInt());
501
                        if (metaData.getColumnType(fieldId) == Types.SMALLINT)
502
                                return ValueFactory.createValue(buf.getShort());
503
                        if (metaData.getColumnType(fieldId) == Types.BIGINT)
504
                                return ValueFactory.createValue(buf.getLong());
505
                        if (metaData.getColumnType(fieldId) == Types.BIT)
506
                                return ValueFactory.createValue((byteBuf[0] == 1));
507
                        if (metaData.getColumnType(fieldId) == Types.BOOLEAN)
508
                                return ValueFactory.createValue(aRs.getBoolean(fieldId));
509
                        if (metaData.getColumnType(fieldId) == Types.DATE) {
510
                                long daysAfter2000 = buf.getInt();
511
                                DateTime year2000 = new DateTime(2000, 1,1,0,0,0);
512
                                DateTime dt = year2000.plusDays((int)daysAfter2000);
513
                                Calendar cal = GregorianCalendar.getInstance();
514
                                cal.set(dt.getYear(), dt.getMonthOfYear()-1, dt.getDayOfMonth());
515
//                                System.out.println(dt + " convertido:" + cal.getTime());
516
                                return ValueFactory.createValue(cal.getTime());
517
                        }
518
                        if (metaData.getColumnType(fieldId) == Types.TIME) {
519
                                // TODO:
520
                                // throw new RuntimeException("TIME type not implemented yet");
521
                                return ValueFactory.createValue("NOT IMPLEMENTED YET");
522
                        }
523
                        if (metaData.getColumnType(fieldId) == Types.TIMESTAMP) {
524
                                double segsReferredTo2000 = buf.getDouble();
525
                                long real_msecs = (long) (XTypes.NUM_msSecs2000 + segsReferredTo2000 * 1000);
526
                                Timestamp valTimeStamp = new Timestamp(real_msecs);
527
                                return ValueFactory.createValue(valTimeStamp);
528
                        }
529

    
530
                        if (metaData.getColumnType(fieldId) == Types.NUMERIC) {
531
                                // System.out.println(metaData.getColumnName(fieldId) + " "
532
                                // + metaData.getColumnClassName(fieldId));
533
                                // short ndigits = buf.getShort();
534
                                // short weight = buf.getShort();
535
                                // short sign = buf.getShort();
536
                                // short dscale = buf.getShort();
537
                                // String strAux;
538
                                // if (sign == 0)
539
                                // strAux = "+";
540
                                // else
541
                                // strAux = "-";
542
                                //
543
                                // for (int iDigit = 0; iDigit < ndigits; iDigit++) {
544
                                // short digit = buf.getShort();
545
                                // strAux = strAux + digit;
546
                                // if (iDigit == weight)
547
                                // strAux = strAux + ".";
548
                                //
549
                                // }
550
                                // strAux = strAux + "0";
551

    
552
                                BigDecimal dec;
553
                                dec = getBigDecimal(buf.array());
554
                                // dec = new BigDecimal(strAux);
555
                                // System.out.println(ndigits + "_" + weight + "_" + dscale
556
                                // + "_" + strAux);
557
                                // System.out.println(strAux + " Big= " + dec);
558
                                return ValueFactory.createValue(dec.doubleValue());
559
                        }
560

    
561
                }
562

    
563
                return ValueFactory.createNullValue();
564

    
565
        }
566

    
567
        private static BigDecimal getBigDecimal(byte[] number) throws SQLException {
568

    
569
                short ndigits = (short) (((number[0] & 0xff) << 8) | (number[1] & 0xff));
570
                short weight = (short) (((number[2] & 0xff) << 8) | (number[3] & 0xff));
571
                short sign = (short) (((number[4] & 0xff) << 8) | (number[5] & 0xff));
572
                short dscale = (short) (((number[6] & 0xff) << 8) | (number[7] & 0xff));
573

    
574
                if (sign == (short) 0xC000) {
575
                        // Numeric NaN - BigDecimal doesn't support this
576
                        throw new PSQLException(
577
                                        "The numeric value is NaN - can't convert to BigDecimal",
578
                                        PSQLState.NUMERIC_VALUE_OUT_OF_RANGE);
579
                }
580

    
581
                final int bigDecimalSign = sign == 0x4000 ? -1 : 1;
582

    
583
                // System.out.println("ndigits=" + ndigits
584
                // +",\n wieght=" + weight
585
                // +",\n sign=" + sign
586
                // +",\n dscale=" + dscale);
587
                // // for (int i=8; i < number.length; i++) {
588
                // System.out.println("numer[i]=" + (int) (number[i] & 0xff));
589
                // }
590

    
591
                int tail = ndigits % 4;
592
                int bytesToParse = (ndigits - tail) * 2 + 8;
593
                // System.out.println("numberParseLength="+numberParseLength);
594
                int i;
595
                BigInteger unscaledValue = BigInteger.ZERO;
596
                final BigInteger nbase = getNBase();
597
                final BigInteger nbasePow2 = getNBasePow2();
598
                final BigInteger nbasePow4 = getNBasePow4();
599

    
600

    
601
                byte[] buffer = new byte[8];
602

    
603
                // System.out.println("tail = " + tail + " bytesToParse = " +
604
                // bytesToParse);
605

    
606
                for (i = 8; i < bytesToParse; i += 8) {
607
                        // This Hi and Lo aren't bytes Hi Li, but decimal Hi Lo!! (Big &
608
                        // Small)
609
                        long valHi = (((number[i] & 0xff) << 8) | (number[i + 1] & 0xff))
610
                                        * 10000
611
                                        + (((number[i + 2] & 0xff) << 8) | (number[i + 3] & 0xff));
612
                        long valLo = (((number[i + 4] & 0xff) << 8) | (number[i + 5] & 0xff))
613
                                        * 10000
614
                                        + (((number[i + 6] & 0xff) << 8) | (number[i + 7] & 0xff));
615
                        long val = valHi * nbaseLongPow2 + valLo;
616
                        buffer[0] = (byte) (val >>> 56);
617
                        buffer[1] = (byte) (val >>> 48);
618
                        buffer[2] = (byte) (val >>> 40);
619
                        buffer[3] = (byte) (val >>> 32);
620
                        buffer[4] = (byte) (val >>> 24);
621
                        buffer[5] = (byte) (val >>> 16);
622
                        buffer[6] = (byte) (val >>> 8);
623
                        buffer[7] = (byte) (val >>> 0);
624

    
625
                        BigInteger valBigInteger = new BigInteger(bigDecimalSign, buffer);
626
                        unscaledValue = unscaledValue.multiply(nbasePow4)
627
                                        .add(valBigInteger);
628
                }
629
                tail = tail % 2;
630
                bytesToParse = (ndigits - tail) * 2 + 8;
631
                // System.out.println("tail = " + tail + " bytesToParse = " +
632
                // bytesToParse);
633

    
634
                buffer = new byte[4];
635
                for (; i < bytesToParse; i += 4) {
636
                        int val = (((number[i] & 0xff) << 8) | (number[i + 1] & 0xff))
637
                                        * nbaseInt
638
                                        + (((number[i + 2] & 0xff) << 8) | (number[i + 3] & 0xff));
639
                        buffer[0] = (byte) (val >>> 24);
640
                        buffer[1] = (byte) (val >>> 16);
641
                        buffer[2] = (byte) (val >>> 8);
642
                        buffer[3] = (byte) val;
643
                        BigInteger valBigInteger = new BigInteger(bigDecimalSign, buffer);
644
                        unscaledValue = unscaledValue.multiply(nbasePow2)
645
                                        .add(valBigInteger);
646
                }
647

    
648
                // Add the rest of number
649
                if (tail % 2 == 1) {
650
                        buffer = new byte[2];
651
                        buffer[0] = number[number.length - 2];
652
                        buffer[1] = number[number.length - 1];
653
                        BigInteger valBigInteger = new BigInteger(bigDecimalSign, buffer);
654
                        unscaledValue = unscaledValue.multiply(nbase).add(valBigInteger);
655
                        // System.out.println("Value (2)  unscaled =" + unscaledValue
656
                        // +", valBI = "+ valBigInteger);
657
                }
658

    
659

    
660
                // Calculate scale offset
661
                final int databaseScale = (ndigits - weight - 1) * 4; // Number of
662
                                                                                                                                // digits in
663
                                                                                                                                // nbase
664
                // TODO This number of digits should be calculeted depending on nbase
665
                // (getNbase());
666

    
667
                BigDecimal result = new BigDecimal(unscaledValue, databaseScale);
668
                return result;
669

    
670
        }
671

    
672
        public void open() {
673
                /*
674
                 * try { st = conn.createStatement(); st.setFetchSize(2000); if
675
                 * (bCursorActivo) close(); st.execute("declare wkb_cursor binary cursor
676
                 * for " + sqlOrig); rs = st.executeQuery("fetch forward all in
677
                 * wkb_cursor"); // st.execute("BEGIN"); bCursorActivo = true; } catch
678
                 * (SQLException e) { e.printStackTrace(); throw new
679
                 * com.iver.cit.gvsig.fmap.DriverException(e); }
680
                 */
681

    
682
        }
683

    
684
        private void setAbsolutePosition(int index) throws SQLException {
685
                // TODO: USAR LIMIT Y ORDER BY, Y HACERLO TAMBI?N PARA
686
                // MYSQL
687

    
688
                if (rs == null) {
689
                        // ha habido un error previo. Es mejor poner un error y no seguir.
690
                        try {
691
                                reload();
692
                        }
693
                        catch (Exception e) {
694
                                e.printStackTrace();
695
                                throw new SQLException(e);
696
                        }
697
                }
698
                        
699
                // EL ABSOLUTE NO HACE QUE SE VUELVAN A LEER LAS 
700
                // FILAS, ASI QUE MONTAMOS ESTA HISTORIA PARA QUE
701
                // LO HAGA
702

    
703
                if ((index >= fetch_min) && (index <= fetch_max)) {
704
                        // Est? en el intervalo, as? que lo podemos posicionar
705
                                
706
                } else {
707
                        // calculamos el intervalo correcto
708
                        fetch_min = (index / FETCH_SIZE) * FETCH_SIZE;
709
                        fetch_max = fetch_min + FETCH_SIZE - 1;
710
                        // y cogemos ese cacho
711
                        rs.close();
712
                        
713
                        Statement st = ((ConnectionJDBC)conn).getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
714
                        ResultSet.CONCUR_READ_ONLY);
715
                        
716
//                        myCursorId++;
717
//                        st.execute("declare "
718
//                                        + getTableName()
719
//                                        + myCursorId
720
//                                        + "_wkb_cursorAbsolutePosition binary scroll cursor with hold for "
721
//                                        + sqlTotal);
722
                        st.executeQuery("fetch absolute " + fetch_min + " in " + cursorName);
723
//                                        + getTableName() + myCursorId
724
//                                        + "_wkb_cursorAbsolutePosition");
725
                        
726
                        rs = st.executeQuery("fetch forward " + FETCH_SIZE + " in " + cursorName);
727
//                                        + getTableName() + myCursorId
728
//                                        + "_wkb_cursorAbsolutePosition");
729
                        
730

    
731
                }
732
                rs.absolute(index - fetch_min + 1);
733

    
734
        }
735

    
736
        /**
737
         * @see com.iver.cit.gvsig.fmap.drivers.IVectorialDatabaseDriver#getGeometryField(java.lang.String)
738
         */
739
        public String getGeometryField(String fieldName) {
740
            return "AsEWKB(\"" + fieldName + "\", 'XDR')";
741
        }
742

    
743
        /**
744
         * @see com.hardcode.gdbms.engine.data.driver.ObjectDriver#getPrimaryKeys()
745
         */
746
        public int[] getPrimaryKeys() {
747
                // TODO Auto-generated method stub
748
                return null;
749
        }
750

    
751
        /**
752
         * @see com.iver.cit.gvsig.fmap.drivers.IVectorialJDBCDriver#getDefaultPort()
753
         */
754
        public int getDefaultPort() {
755
                return 5432;
756
        }
757

    
758
        /**
759
         * @see com.hardcode.gdbms.engine.data.driver.ObjectDriver#write(com.hardcode.gdbms.engine.data.edition.DataWare)
760
         */
761
        public void write(DataWare arg0) {
762
                // TODO Auto-generated method stub
763

    
764
        }
765

    
766
        /*
767
         * (non-Javadoc)
768
         * 
769
         * @see com.iver.cit.gvsig.fmap.core.ICanReproject#getSourceProjection()
770
         */
771
        public String getSourceProjection(IConnection conn, DBLayerDefinition dbld) {
772
                if (originalEPSG == null)
773
                        getTableEPSG_and_shapeType(conn, dbld);
774
                return originalEPSG;
775
        }
776

    
777
        /**
778
         * Las tablas con geometr?as est?n en la tabla GEOMETRY_COLUMNS y de ah?
779
         * sacamos en qu? proyecci?n est?n. El problema es que si el usuario hace
780
         * una vista de esa tabla, no estar? dada de alta aqu? y entonces gvSIG no
781
         * se entera en qu? proyecci?n est? trabajando (y le ponemos un -1 como mal
782
         * menor). El -1 implica que luego no podremos reproyectar al vuelo desde la
783
         * base de datos. OJO: ES SENSIBLE A MAYUSCULAS / MINUSCULAS!!!
784
         */
785
        private void getTableEPSG_and_shapeType(IConnection conn,
786
                        DBLayerDefinition dbld) {
787
                try {
788
                        Statement stAux = ((ConnectionJDBC) conn).getConnection()
789
                                        .createStatement();
790

    
791
                        // String sql =
792
                        // "SELECT * FROM GEOMETRY_COLUMNS WHERE F_TABLE_NAME = '"
793
                        // + getTableName() + "' AND F_GEOMETRY_COLUMN = '" +
794
                        // getLyrDef().getFieldGeometry() + "'";
795
                        String sql;
796
                        if (dbld.getSchema() == null || dbld.getSchema().equals("")) {
797
                                sql = "SELECT * FROM GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA = current_schema() AND F_TABLE_NAME = '"
798
                                                + dbld.getTableName()
799
                                                + "' AND F_GEOMETRY_COLUMN = '"
800
                                                + dbld.getFieldGeometry() + "'";
801
                        } else {
802
                                sql = "SELECT * FROM GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA = '"
803
                                                + dbld.getSchema() + "' AND F_TABLE_NAME = '"
804
                                                + dbld.getTableName() + "' AND F_GEOMETRY_COLUMN = '"
805
                                                + dbld.getFieldGeometry() + "'";
806
                        }
807

    
808
                        ResultSet rs = stAux.executeQuery(sql);
809
                        if (rs.next()) {
810
                                originalEPSG = "" + rs.getInt("SRID");
811
                                String geometryType = rs.getString("TYPE");
812
                                int shapeType = FShape.MULTI;
813
                                if (geometryType.compareToIgnoreCase("POINT") == 0)
814
                                        shapeType = FShape.POINT;
815
                                else if (geometryType.compareToIgnoreCase("LINESTRING") == 0)
816
                                        shapeType = FShape.LINE;
817
                                else if (geometryType.compareToIgnoreCase("POLYGON") == 0)
818
                                        shapeType = FShape.POLYGON;
819
                                else if (geometryType.compareToIgnoreCase("MULTIPOINT") == 0)
820
                                        shapeType = FShape.MULTIPOINT;
821
                                else if (geometryType.compareToIgnoreCase("MULTILINESTRING") == 0)
822
                                        shapeType = FShape.LINE;
823
                                else if (geometryType.compareToIgnoreCase("MULTILINESTRINGM") == 0) // MCoord
824
                                        shapeType = FShape.LINE | FShape.M;
825
                                else if (geometryType.compareToIgnoreCase("MULTIPOLYGON") == 0)
826
                                        shapeType = FShape.POLYGON;
827
                                dbld.setShapeType(shapeType);
828
                                
829
                                //jomarlla
830
                                int dimension  = rs.getInt("COORD_DIMENSION");
831
                                dbld.setDimension(dimension);
832

    
833
                        } else {
834
                                originalEPSG = "-1";
835
                        }
836

    
837
                        rs.close();
838
                } catch (SQLException e) {
839
                        // TODO Auto-generated catch block
840
                        originalEPSG = "-1";
841
                        logger.error(e);
842
                        e.printStackTrace();
843
                }
844

    
845
        }
846

    
847
        /*
848
         * (non-Javadoc)
849
         * 
850
         * @see com.iver.cit.gvsig.fmap.core.ICanReproject#getDestProjection()
851
         */
852
        public String getDestProjection() {
853
                return strEPSG;
854
        }
855

    
856
        /*
857
         * (non-Javadoc)
858
         * 
859
         * @see
860
         * com.iver.cit.gvsig.fmap.core.ICanReproject#setDestProjection(java.lang
861
         * .String)
862
         */
863
        public void setDestProjection(String toEPSG) {
864
                this.strEPSG = toEPSG;
865
        }
866

    
867
        /*
868
         * (non-Javadoc)
869
         * 
870
         * @see
871
         * com.iver.cit.gvsig.fmap.core.ICanReproject#canReproject(java.lang.String)
872
         */
873
        public boolean canReproject(String toEPSGdestinyProjection) {
874
                // TODO POR AHORA, REPROYECTA SIEMPRE gvSIG.
875
                return false;
876
        }
877

    
878
        /*
879
         * (non-Javadoc)
880
         * 
881
         * @see com.iver.cit.gvsig.fmap.drivers.DefaultDBDriver#doRelateID_FID()
882
         */
883
        protected void doRelateID_FID() throws DBException {
884
                hashRelate = new Hashtable();
885
                try {
886
                        String strSQL = "SELECT " + getLyrDef().getFieldID() + " FROM "
887
                        + getLyrDef().getComposedTableName() + " ";
888
                        // + getLyrDef().getWhereClause();
889
                        if (canReproject(strEPSG)) {
890
                                strSQL = strSQL
891
                                + getCompoundWhere(strSQL, workingArea, strEPSG);
892
                        } else {
893
                                strSQL = strSQL
894
                                + getCompoundWhere(strSQL, workingArea, originalEPSG);
895
                        }
896
                        strSQL = strSQL + " ORDER BY " + getLyrDef().getFieldID();
897
                        Statement s = ((ConnectionJDBC) getConnection()).getConnection()
898
                                        .createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
899
                                                        ResultSet.CONCUR_READ_ONLY);
900
                        int fetchSize = 5000;
901
                        ResultSet r = s.executeQuery(strSQL);
902
                        int id = 0;
903
                        String gid;
904
                        while (r.next()) {
905
                                gid = r.getString(1);
906
                                
907
                                if (gid == null) {
908
                                        throw new SQLException(
909
                                                        PluginServices.getText(null, "Found_null_id_in_table") + ": " +
910
                                                        getLyrDef().getComposedTableName());
911
                                }
912
                                
913
                                Value aux = ValueFactory.createValue(gid);
914
                                hashRelate.put(aux, new Integer(id));
915
                                // System.out.println("ASOCIANDO CLAVE " + aux + " CON VALOR " +
916
                                // id);
917
                                id++;
918
                                // System.out.println("Row " + id + ":" + strAux);
919
                        }
920
                        s.close();
921
                        numReg = id;
922

    
923
                        /*
924
                         * for (int index = 0; index < getShapeCount(); index++) { Value aux
925
                         * = getFieldValue(index, idFID_FieldName-2); hashRelate.put(aux,
926
                         * new Integer(index)); // System.out.println("Row " + index +
927
                         * " clave=" + aux); }
928
                         */
929
                        /*
930
                         * int index = 0;
931
                         * 
932
                         * while (rs.next()) { Value aux = getFieldValue(index,
933
                         * idFID_FieldName-2); hashRelate.put(aux, new Integer(index));
934
                         * index++; System.out.println("Row " + index + " clave=" + aux); }
935
                         * numReg = index;
936
                         */
937
                        // rs.beforeFirst();
938
                        /*
939
                         * } catch (com.hardcode.gdbms.engine.data.driver.DriverException e)
940
                         * { // TODO Auto-generated catch block e.printStackTrace();
941
                         */
942
                } catch (SQLException e) {
943
                        throw new DBException(e);
944
                }
945
        }
946

    
947
        public String getSqlTotal() {
948
                return sqlTotal;
949
        }
950

    
951
        /**
952
         * @return Returns the completeWhere.
953
         */
954
        public String getCompleteWhere() {
955
                return completeWhere;
956
        }
957

    
958
        /*
959
         * (non-Javadoc)
960
         * 
961
         * @see com.iver.cit.gvsig.fmap.drivers.DefaultDBDriver#close()
962
         */
963
        public void close() {
964
                super.close();
965
                /*
966
                 * if (bCursorActivo) { try { // st =
967
                 * conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
968
                 * ResultSet.CONCUR_READ_ONLY); st.execute("CLOSE wkb_cursor_prov"); //
969
                 * st.close(); } catch (SQLException e) { // TODO Auto-generated catch
970
                 * block e.printStackTrace(); } bCursorActivo = false; }
971
                 */
972

    
973
        }
974

    
975
        /*
976
         * (non-Javadoc)
977
         * 
978
         * @see
979
         * com.iver.cit.gvsig.fmap.drivers.VectorialDatabaseDriver#getFeatureIterator
980
         * (java.awt.geom.Rectangle2D, java.lang.String, java.lang.String[])
981
         */
982
        public IFeatureIterator getFeatureIterator(Rectangle2D r, String strEPSG,
983
                        String[] alphaNumericFieldsNeeded) throws ReadDriverException {
984
                String sqlAux = null;
985
                DBLayerDefinition lyrDef = getLyrDef();
986
                DBLayerDefinition clonedLyrDef = cloneLyrDef(lyrDef);
987
                ArrayList<FieldDescription> myFieldsDesc = new ArrayList<FieldDescription>(); // =
988
                                                                                                                                                                                // new
989
                                                                                                                                                                                // FieldDescription[alphaNumericFieldsNeeded.length+1];
990
                try {
991
                        if (workingArea != null)
992
                                r = r.createIntersection(workingArea);
993
                        // if (getLyrDef()==null){
994
                        // load();
995
                        // throw new DriverException("Fallo de la conexi?n");
996
                        // }
997
                        String strAux = getGeometryField(lyrDef.getFieldGeometry());
998

    
999
                        boolean found = false;
1000
                        int fieldIndex = -1;
1001
                        if (alphaNumericFieldsNeeded != null) {
1002
                                FieldDescription[] fieldsDesc = lyrDef.getFieldsDesc();
1003

    
1004
                                for (int i = 0; i < alphaNumericFieldsNeeded.length; i++) {
1005
                                        fieldIndex = lyrDef
1006
                                                        .getFieldIdByName(alphaNumericFieldsNeeded[i]);
1007
                                        if (fieldIndex < 0) {
1008
                                                throw new RuntimeException(
1009
                                                                "No se ha encontrado el nombre de campo "
1010
                                                                                + metaData.getColumnName(i));
1011
                                        }
1012
                                        strAux = strAux
1013
                                                        + ", "
1014
                                                        + PostGIS
1015
                                                                        .escapeFieldName(lyrDef.getFieldNames()[fieldIndex]);
1016
                                        if (alphaNumericFieldsNeeded[i].equalsIgnoreCase(lyrDef
1017
                                                        .getFieldID())) {
1018
                                                found = true;
1019
                                                clonedLyrDef.setIdFieldID(i);
1020
                                        }
1021

    
1022
                                        myFieldsDesc.add(fieldsDesc[fieldIndex]);
1023
                                }
1024
                        }
1025
                        // Nos aseguramos de pedir siempre el campo ID
1026
                        if (found == false) {
1027
                                strAux = strAux + ", " + lyrDef.getFieldID();
1028
                                myFieldsDesc.add(lyrDef.getFieldsDesc()[lyrDef
1029
                                                .getIdField(lyrDef.getFieldID())]);
1030
                                clonedLyrDef.setIdFieldID(myFieldsDesc.size() - 1);
1031
                        }
1032
                        clonedLyrDef.setFieldsDesc((FieldDescription[]) myFieldsDesc
1033
                                        .toArray(new FieldDescription[] {}));
1034

    
1035
                        String sqlProv = "SELECT " + strAux + " FROM "
1036
                        + lyrDef.getComposedTableName() + " ";
1037
                        // + getLyrDef().getWhereClause();
1038

    
1039
                        if (canReproject(strEPSG)) {
1040
                                sqlAux = sqlProv + getCompoundWhere(sqlProv, r, strEPSG);
1041
                        } else {
1042
                                sqlAux = sqlProv + getCompoundWhere(sqlProv, r, originalEPSG);
1043
                        }
1044

    
1045
                        System.out.println("SqlAux getFeatureIterator = " + sqlAux);
1046
                        PostGisFeatureIterator geomIterator = null;
1047
                        geomIterator = myGetFeatureIterator(sqlAux);
1048
                        geomIterator.setLyrDef(clonedLyrDef);
1049
                        return geomIterator;
1050
                } catch (Exception e) {
1051
                        // e.printStackTrace();
1052
                        // SqlDriveExceptionType type = new SqlDriveExceptionType();
1053
                        // type.setDriverName("PostGIS Driver");
1054
                        // type.setSql(sqlAux);
1055
                        // type.setLayerName(getTableName());
1056
                        // type.setSchema(null);
1057
                        throw new ReadDriverException("PostGIS Driver", e);
1058

    
1059
                        // throw new DriverException(e);
1060
                }
1061
        }
1062

    
1063
        /*
1064
         * public void preProcess() throws EditionException { writer.preProcess(); }
1065
         * 
1066
         * public void process(IRowEdited row) throws EditionException {
1067
         * writer.process(row); }
1068
         * 
1069
         * public void postProcess() throws EditionException { writer.postProcess();
1070
         * }
1071
         * 
1072
         * public String getCapability(String capability) { return
1073
         * writer.getCapability(capability); }
1074
         * 
1075
         * public void setCapabilities(Properties capabilities) {
1076
         * writer.setCapabilities(capabilities); }
1077
         * 
1078
         * public boolean canWriteAttribute(int sqlType) { return
1079
         * writer.canWriteAttribute(sqlType); }
1080
         * 
1081
         * public boolean canWriteGeometry(int gvSIGgeometryType) { return
1082
         * writer.canWriteGeometry(gvSIGgeometryType); }
1083
         * 
1084
         * public void initialize(ITableDefinition layerDef) throws EditionException
1085
         * { writer.setCreateTable(false); writer.setWriteAll(false); // Obtenemos
1086
         * el DBLayerDefinition a partir del driver
1087
         * 
1088
         * DBLayerDefinition dbLyrDef = getLyrDef();
1089
         * 
1090
         * 
1091
         * writer.initialize(dbLyrDef); }
1092
         */
1093
        public boolean isWritable() {
1094
                // CHANGE FROM CARTOLAB
1095
                // return true;
1096
                return writer.canSaveEdits();
1097
                // END CHANGE CARTOLAB
1098
        }
1099

    
1100
        public IWriter getWriter() {
1101
                return writer;
1102
        }
1103

    
1104
        public String[] getGeometryFieldsCandidates(IConnection conn,
1105
                        String table_name) throws DBException {
1106
                ArrayList list = new ArrayList();
1107
                try {
1108
                        Statement stAux = ((ConnectionJDBC) conn).getConnection()
1109
                                        .createStatement();
1110
                        String[] tokens = table_name.split("\\u002E", 2);
1111
                        String sql;
1112
                        if (tokens.length > 1) {
1113
                                sql = "select * from GEOMETRY_COLUMNS WHERE F_TABLE_SCHEMA = '"
1114
                                                + tokens[0] + "' AND F_TABLE_NAME = '" + tokens[1]
1115
                                                + "'";
1116
                        } else {
1117
                                sql = "select * from GEOMETRY_COLUMNS"
1118
                                                + " where F_TABLE_SCHEMA = current_schema() AND F_TABLE_NAME = '"
1119
                                                + table_name + "'";
1120

    
1121
                        }
1122

    
1123
                        // String sql =
1124
                        // "SELECT * FROM GEOMETRY_COLUMNS WHERE F_TABLE_NAME = '"
1125
                        // + table_name + "'";
1126

    
1127
                        ResultSet rs = stAux.executeQuery(sql);
1128
                        while (rs.next()) {
1129
                                String geomCol = rs.getString("F_GEOMETRY_COLUMN");
1130
                                list.add(geomCol);
1131
                        }
1132
                        rs.close();
1133
                        stAux.close();
1134
                } catch (SQLException e) {
1135
                        closeConnection(conn);
1136
                        throw new DBException(e);
1137
                }
1138
                return (String[]) list.toArray(new String[0]);
1139
        }
1140

    
1141
        // public String[] getTableFields(IConnection conex, String table) throws
1142
        // DBException {
1143
        // try{
1144
        // Statement st = ((ConnectionJDBC)conex).getConnection().createStatement();
1145
        // // ResultSet rs = dbmd.getTables(catalog, null,
1146
        // dbLayerDefinition.getTable(), null);
1147
        // ResultSet rs = st.executeQuery("select * from " + table + " LIMIT 1");
1148
        // ResultSetMetaData rsmd = rs.getMetaData();
1149
        //
1150
        // String[] ret = new String[rsmd.getColumnCount()];
1151
        //
1152
        // for (int i = 0; i < ret.length; i++) {
1153
        // ret[i] = rsmd.getColumnName(i+1);
1154
        // }
1155
        //
1156
        // return ret;
1157
        // }catch (SQLException e) {
1158
        // throw new DBException(e);
1159
        // }
1160
        // }
1161

    
1162
        private DBLayerDefinition cloneLyrDef(DBLayerDefinition lyrDef) {
1163
                DBLayerDefinition clonedLyrDef = new DBLayerDefinition();
1164

    
1165
                clonedLyrDef.setName(lyrDef.getName());
1166
                clonedLyrDef.setFieldsDesc(lyrDef.getFieldsDesc());
1167

    
1168
                clonedLyrDef.setShapeType(lyrDef.getShapeType());
1169
                clonedLyrDef.setProjection(lyrDef.getProjection());
1170

    
1171
                clonedLyrDef.setConnection(lyrDef.getConnection());
1172
                clonedLyrDef.setCatalogName(lyrDef.getCatalogName());
1173
                clonedLyrDef.setSchema(lyrDef.getSchema());
1174
                clonedLyrDef.setTableName(lyrDef.getTableName());
1175

    
1176
                clonedLyrDef.setFieldID(lyrDef.getFieldID());
1177
                clonedLyrDef.setFieldGeometry(lyrDef.getFieldGeometry());
1178
                clonedLyrDef.setWhereClause(lyrDef.getWhereClause());
1179
                clonedLyrDef.setWorkingArea(lyrDef.getWorkingArea());
1180
                clonedLyrDef.setSRID_EPSG(lyrDef.getSRID_EPSG());
1181
                clonedLyrDef.setClassToInstantiate(lyrDef.getClassToInstantiate());
1182

    
1183
                clonedLyrDef.setIdFieldID(lyrDef.getIdFieldID());
1184
                clonedLyrDef.setDimension(lyrDef.getDimension());
1185
                clonedLyrDef.setHost(lyrDef.getHost());
1186
                clonedLyrDef.setPort(lyrDef.getPort());
1187
                clonedLyrDef.setDataBase(lyrDef.getDataBase());
1188
                clonedLyrDef.setUser(lyrDef.getUser());
1189
                clonedLyrDef.setPassword(lyrDef.getPassword());
1190
                clonedLyrDef.setConnectionName(lyrDef.getConnectionName());
1191
                return clonedLyrDef;
1192
        }
1193

    
1194
        public String getTotalFields() {
1195
                StringBuilder strAux = new StringBuilder();
1196
                strAux.append(getGeometryField(getLyrDef().getFieldGeometry()));
1197
                String[] fieldNames = getLyrDef().getFieldNames();
1198
                for (int i = 0; i < fieldNames.length; i++) {
1199
                        strAux.append(", " + PostGIS.escapeFieldName(fieldNames[i]));
1200
                }
1201
                return strAux.toString();
1202
        }
1203

    
1204

    
1205
        /**
1206
         * Gets all field names of a given table.
1207
         * 
1208
         * This method comes from DefaultJDBC.java class. Postgis driver has no method to check
1209
         *  the status of the connection -if it is valid or not. So, as it's not possible to assure 
1210
         *  its status, close the connection when an exception happens and re-open it on demand 
1211
         *  on the proper method will solve the problems related to an invalid status.
1212
         * 
1213
         * @param conn connection object
1214
         * @param table_name table name
1215
         * @return all field names of the given table
1216
         * @throws SQLException
1217
         */
1218
        @Override
1219
        public String[] getAllFields(IConnection conn, String table_name) throws DBException {
1220
                Statement st = null;
1221
                ResultSet rs = null;
1222
                table_name = tableNameToComposedTableName(table_name);
1223

    
1224
                try {
1225
                        st = ((ConnectionJDBC)conn).getConnection().createStatement();
1226
                        rs = st.executeQuery("SELECT * FROM " + table_name + " LIMIT 1");
1227
                        ResultSetMetaData rsmd = rs.getMetaData();
1228
                        String[] ret = new String[rsmd.getColumnCount()];
1229

    
1230
                        for (int i = 0; i < ret.length; i++) {
1231
                                ret[i] = rsmd.getColumnName(i+1);
1232
                        }
1233

    
1234
                        return ret;
1235
                } catch (SQLException e) {
1236
                        // Next time  getConnection() method is called it will be re-opened.
1237
                        // @see com.iver.cit.gvsig.fmap.drivers.ConnectionJDBC.java;
1238
                        closeConnection(conn);
1239
                        throw new DBException(e);
1240
                }
1241
                finally {
1242
                        closeResultSet(rs);
1243
                        closeStatement(st);
1244
                }
1245
        }
1246

    
1247
        /**
1248
         * Gets all field type names of a given table.
1249
         * 
1250
         * This method comes from DefaultJDBC.java class. Postgis driver has no method to check
1251
         *  the status of the connection -if it is valid or not. So, as it's not possible to assure 
1252
         *  its status, close the connection when an exception happens and re-open it on demand 
1253
         *  on the proper method will solve the problems related to an invalid status.
1254
         * 
1255
         * @param conn connection object
1256
         * @param table_name table name
1257
         * @return all field type names of the given table
1258
         * @throws SQLException
1259
         */
1260
        public String[] getAllFieldTypeNames(IConnection conn, String table_name) throws DBException {
1261
            Statement st = null;
1262
            ResultSet rs = null;
1263
            table_name = tableNameToComposedTableName(table_name);
1264
                try {
1265
                        st = ((ConnectionJDBC)conn).getConnection().createStatement();
1266
                        rs = st.executeQuery("SELECT * FROM " + table_name + " LIMIT 1");
1267
                        ResultSetMetaData rsmd = rs.getMetaData();
1268
                        String[] ret = new String[rsmd.getColumnCount()];
1269

    
1270
                        for (int i = 0; i < ret.length; i++) {
1271
                                ret[i] = rsmd.getColumnTypeName(i+1);
1272
                        }
1273
                        return ret;
1274
                } catch (SQLException e) {
1275
                        // Next time  getConnection() method is called it will be re-opened.
1276
                        // @see com.iver.cit.gvsig.fmap.drivers.ConnectionJDBC.java;
1277
                        closeConnection(conn);
1278
                        throw new DBException(e);
1279
                }
1280
                finally{
1281
                        closeStatement(st);
1282
                        closeResultSet(rs);
1283
                }
1284
        }
1285

    
1286
        /**
1287
         *
1288
         * @param tableName
1289
         * @return a string with the schema and the tableName quoted
1290
         */
1291
        private String tableNameToComposedTableName(String tableName) {
1292
                String composedTableName = null;
1293
                // \u002E = unicode character for .
1294
                String[] tokens = tableName.trim().replace("\"", "").split("\\u002E");
1295

    
1296
                if (tokens.length == 1) {
1297
                        composedTableName = "\"" + tokens[0] + "\"";
1298

    
1299
                } else if (tokens.length == 2) {
1300
                        composedTableName = "\"" + tokens[0] + "\".\"" + tokens[1] + "\"";
1301
                } else {
1302
                        // this is a not predictable case, so we return the same
1303
                        composedTableName = tableName;
1304
                }
1305

    
1306
                return composedTableName;
1307
        }
1308

    
1309
        /**
1310
         * Close a ResultSet
1311
         * @param rs, the resultset to be closed
1312
         * @return true if the resulset was correctly closed. false in any other case
1313
         */
1314
        public boolean closeResultSet(ResultSet rs) {
1315
                boolean error = false;
1316

    
1317
                if (rs != null) {
1318
                        try {
1319
                                rs.close();
1320
                                error = true;
1321
                        } catch (SQLException e) {
1322
                                logger.error(e.getMessage(), e);
1323
                        }
1324
                }
1325

    
1326
                return error;
1327
        }
1328

    
1329
        /**
1330
         * Close a Statement
1331
         * @param st, the statement to be closed
1332
         * @return true if the  statement was correctly closed, false in any other case
1333
         */
1334
        public boolean closeStatement(Statement st) {
1335
                boolean error = false;
1336

    
1337
                if (st != null) {
1338
                        try {
1339
                                st.close();
1340
                                error = true;
1341
                        } catch (SQLException e) {
1342
                                logger.error(e.getMessage(), e);
1343
                        }
1344
                }
1345

    
1346
                return error;
1347
        }
1348

    
1349
        /**
1350
         * Close a Connection
1351
         * @param conn, the  connection to be closed
1352
         * @return true if the connection was correctly closed, false in any other case
1353
         */
1354
        public boolean closeConnection(IConnection conn) {
1355
                boolean error = false;
1356

    
1357
                if (conn != null) {
1358
                        try {
1359
                                conn.close();
1360
                                error = true;
1361
                        } catch (DBException e) {
1362
                                logger.error(e.getMessage(), e);
1363
                        }
1364
                }
1365

    
1366
                return error;
1367
        }
1368

    
1369
        /**
1370
         * Tells if user can read contents of the table.
1371
         * 
1372
         *  @param iconn connection with the database where the user is connected
1373
         *  @param tablename to get the permissions over
1374
         *  @return true if can read, either false
1375
         *  @throws SQLException
1376
         */
1377
        public boolean canRead(IConnection iconn, String tablename) throws SQLException {
1378
                String schema = null;
1379
                int dotPos = tablename.indexOf(".");
1380
                if (dotPos > -1) {
1381
                        schema = tablename.substring(0, dotPos);
1382
                }
1383
                tablename = tableNameToComposedTableName(tablename);
1384
                Connection conn = ((ConnectionJDBC) iconn).getConnection();
1385
                boolean checkTable = true;
1386
                if (schema != null) {
1387
                        if (!schemasUsage.containsKey(schema)) {
1388
                                String query = "SELECT has_schema_privilege('" + schema + "', 'USAGE') AS usg";
1389
                                Statement st = conn.createStatement();
1390
                                ResultSet rs = st.executeQuery(query);
1391
                                if (rs.next()) {
1392
                                        schemasUsage.put(schema, rs.getBoolean("usg"));
1393
                                } else {
1394
                                        //this sentence should never be executed...
1395
                                        schemasUsage.put(schema, false);
1396
                                }
1397
                                rs.close();
1398
                                st.close();
1399
                        }
1400
                        checkTable = schemasUsage.get(schema);
1401
                }
1402
                if (checkTable) {
1403
                        String query = "SELECT has_table_privilege('" + tablename + "', 'SELECT') as selct";
1404
                        Statement st = conn.createStatement();
1405
                        ResultSet rs = st.executeQuery(query);
1406
                        if (rs.next()) {
1407
                                return rs.getBoolean("selct");
1408
                        } else {
1409
                                return false;
1410
                        }
1411
                } else return false;
1412
        }
1413

    
1414
  
1415
        private Integer getGidFieldIndex(String ret[]){
1416
            for (int i=0; i<ret.length; i++) {
1417
                if (ret[i].equalsIgnoreCase("gid")) {
1418
                    return new Integer(i);
1419
                }            
1420
            }
1421
            return null;
1422
        }
1423
        
1424
        private void swapIndexes(String[] ret, int i, int j){
1425
            if(i!=j && i>=0 && i<ret.length && j>=0 && j<ret.length){
1426
                String aux = ret[i];
1427
                ret[i] = ret[j];
1428
                ret[j] = aux;
1429
            }
1430
        }
1431

    
1432
        public String[] getIdFieldsCandidates(IConnection conn, String table_name) throws DBException {
1433

    
1434
            String[] ret = getAllFields(conn, table_name);
1435

    
1436
            String pk = getPrimaryKey(conn, table_name);
1437

    
1438
            if (!pk.equals("")){
1439
                for (int i = 0; i < ret.length; i++) {
1440
                    if (pk.equals(ret[i])) {
1441
                            //swap possible gid col with the first element
1442
                            //in order to make it the default selection on 
1443
                            //combobox
1444
                            swapIndexes(ret, i, 0);
1445
                            break;
1446
                    }
1447
                }
1448
            } else {
1449
                Integer gidFieldIndex = getGidFieldIndex(ret);
1450
                if (gidFieldIndex!=null){
1451
                    //swap possible gid col with the first element
1452
                    //in order to make it the default selection on 
1453
                    //combobox
1454
                    int index = gidFieldIndex.intValue();
1455
                    swapIndexes(ret, index, 0);
1456
                } else {
1457
                    for (int i = 0; i < ret.length; i++) {
1458
                        if (isAutoIncrement(conn, table_name, ret[i])) {
1459
                            //swap possible gid col with the first element
1460
                            //in order to make it the default selection on 
1461
                            //combobox
1462
                            swapIndexes(ret, i, 0);
1463
                            break;
1464
                        }
1465
                    }
1466
                }
1467
            }
1468
            return ret;
1469
        }
1470

    
1471
        private boolean isAutoIncrement(IConnection con, String table_name, String colName) {
1472
                
1473
                String query = "SELECT column_default SIMILAR TO 'nextval%regclass%' AS isautoincremental "
1474
                        + "FROM information_schema.columns " 
1475
                        + "WHERE table_name = ? AND table_schema=? " 
1476
                        + "AND column_name=?";
1477
                
1478
                try {
1479
                        // get schema and table from the composed tablename
1480
                        String[] tokens = table_name.split("\\u002E", 2);
1481
                        String schema = "";
1482
                        String tableName = "";
1483
                        if (tokens.length == 1) {
1484
                                tableName = tokens[0];
1485
                        } else {
1486
                                schema = tokens[0];
1487
                                tableName = tokens[1];
1488
                        }
1489
                        
1490
                        
1491
                        Connection c = ((ConnectionJDBC)con).getConnection();
1492
                        PreparedStatement st = c.prepareStatement(query);
1493
                        st.setString(1, tableName);
1494
                        st.setString(2, schema);
1495
                        st.setString(3, colName);
1496
                        
1497
                        ResultSet rs = st.executeQuery();
1498
                        boolean isAutoincrement = false;
1499
                        if (rs.next()) {
1500
                                isAutoincrement = rs.getBoolean("isautoincremental");
1501
                        }
1502
                        
1503
                        rs.close();
1504
                        st.close();
1505
                        
1506
                        return isAutoincrement;
1507
                } catch (SQLException e) {
1508
                        try {
1509
                                con.close();
1510
                        } catch (DBException e2) {
1511
                                // TODO Auto-generated catch block
1512
                                e.printStackTrace();
1513
                        } 
1514
                        return false;
1515

    
1516
                }
1517
                
1518
                
1519
        }
1520

    
1521
           private String getPrimaryKey(IConnection con, String table_name) {
1522

    
1523
               String query = "SELECT column_name FROM information_schema.key_column_usage" +
1524
                               " WHERE table_name=? AND table_schema=? AND constraint_name=?";
1525

    
1526
                try {
1527
                    // get schema and table from the composed tablename
1528
                    String[] tokens = table_name.split("\\u002E", 2);
1529
                    String schema = "";
1530
                    String tableName = "";
1531
                    if (tokens.length == 1) {
1532
                        tableName = tokens[0];
1533
                    } else {
1534
                        schema = tokens[0];
1535
                        tableName = tokens[1];
1536
                    }
1537
                    
1538
                    
1539
                    Connection c = ((ConnectionJDBC)con).getConnection();
1540
                    PreparedStatement st = c.prepareStatement(query);
1541
                    st.setString(1, tableName);
1542
                    st.setString(2, schema);
1543
                    st.setString(3, tableName+"_pkey");
1544
                    
1545
                    ResultSet rs = st.executeQuery();
1546
                    
1547
                    String primaryKey = "";
1548
                    if (rs.next()) {
1549
                        primaryKey = rs.getString("column_name");
1550
                    }
1551
                    
1552
                    rs.close();
1553
                    st.close();
1554
                    
1555
                    return primaryKey;
1556
                } catch (SQLException e) {
1557
                    try {
1558
                        con.close();
1559
                    } catch (DBException e2) {
1560
                        // TODO Auto-generated catch block
1561
                        e.printStackTrace();
1562
                    } 
1563
                    return "";
1564
                }
1565
            }
1566
           
1567

    
1568
                public void validateData(IConnection _conn, DBLayerDefinition lyrDef) throws DBException {
1569
                        
1570
                        this.conn = _conn;
1571
                        lyrDef.setConnection(conn);
1572
                        setLyrDef(lyrDef);
1573

    
1574
                        getTableEPSG_and_shapeType(conn, lyrDef);
1575

    
1576
                        getLyrDef().setSRID_EPSG(originalEPSG);
1577

    
1578
                        try {
1579
                                ((ConnectionJDBC) conn).getConnection().setAutoCommit(false);
1580
                                sqlOrig = "SELECT " + getTotalFields() + " FROM "
1581
                                + getLyrDef().getComposedTableName() + " ";
1582
                                // + getLyrDef().getWhereClause();
1583
                                if (canReproject(strEPSG)) {
1584
                                        completeWhere = getCompoundWhere(sqlOrig, workingArea, strEPSG);
1585
                                } else {
1586
                                        completeWhere = getCompoundWhere(sqlOrig, workingArea,
1587
                                                        originalEPSG);
1588
                                }
1589
                                // completeWhere = getLyrDef().getWhereClause() + completeWhere;
1590

    
1591
                                String sqlAux = sqlOrig + completeWhere + " ORDER BY "
1592
                                                + getLyrDef().getFieldID();
1593

    
1594
                                sqlTotal = sqlAux;
1595
                                logger.info("Cadena SQL:" + sqlAux);
1596
                                Statement st = ((ConnectionJDBC) conn).getConnection().createStatement(
1597
                                                ResultSet.TYPE_SCROLL_INSENSITIVE,
1598
                                                ResultSet.CONCUR_READ_ONLY);
1599
                                // st.setFetchSize(FETCH_SIZE);
1600
                                // myCursorId++;
1601
                                String temp_index_name = getTableName() + "_temp_wkb_cursor"; 
1602
                                st.execute("declare " + temp_index_name + " binary scroll cursor with hold for "
1603
                                                + sqlAux);
1604
                                rs = st.executeQuery("fetch forward 50 in " + temp_index_name);
1605
                                rs.close();
1606
                                st.execute("close " + temp_index_name);
1607
                                st.close();
1608

    
1609
                        } catch (SQLException e) {
1610
                                
1611
                                try {
1612
                                        ((ConnectionJDBC) conn).getConnection().rollback();
1613
                                } catch (SQLException e1) {
1614
                                        logger.warn("Unable to rollback connection after problem (" + e.getMessage() + ") in setData()");
1615
                                }
1616
                                
1617
                                try {
1618
                                        if (rs != null) { rs.close(); }
1619
                                } catch (SQLException e1) {
1620
                                        throw new DBException(e);
1621
                                }
1622
                                throw new DBException(e);
1623
                        }
1624
                }
1625
}
1626

    
1627
// [eiel-gestion-conexiones]
1628
// [eiel-postgis-3d]
1629