ShpDumperOpenTable()

int ShpDumperOpenTable

(

SHPDUMPERSTATE *

state

)

Definition at line 1313 of file pgsql2shp-core.c.

{
        PGresult *res;
 
        char buf[256];
        int gidfound = 0, i, j, ret, status;
        stringbuffer_t sb;
        char *quoted = NULL;
 
        /* Open the column map if one was specified */
        if (state->config->column_map_filename)
        {
                ret = colmap_read(state->config->column_map_filename,
                                  &state->column_map, state->message, SHPDUMPERMSGLEN);
                if (!ret) return SHPDUMPERERR;
        }
 
        /* If a user-defined query has been specified, create and point the state to our new table */
        if (state->config->usrquery)
        {
                state->table = core_asprintf("__pgsql2shp%lu_tmp_table", (long)getpid());
                stringbuffer_init(&sb);
                stringbuffer_aprintf(&sb,
                        "CREATE TEMP TABLE \"%s\" AS %s",
                        state->table, state->config->usrquery);
                res = PQexec(state->conn, stringbuffer_getstring(&sb));
                stringbuffer_release(&sb);
 
                /* Execute the code to create the table */
                if (PQresultStatus(res) != PGRES_COMMAND_OK)
                {
                        snprintf(state->message, SHPDUMPERMSGLEN, _("Error executing user query: %s"), PQresultErrorMessage(res));
                        PQclear(res);
                        return SHPDUMPERERR;
                }
                PQclear(res);
        }
        else
        {
                /* Simply point the state to copies of the supplied schema and table */
                state->table = strdup(state->config->table);
                if (state->config->schema)
                        state->schema = strdup(state->config->schema);
        }
 
        stringbuffer_init(&sb);
        /* Get the list of columns and their types for the selected table */
        if (state->schema)
        {
                stringbuffer_aprintf(&sb,
                        "SELECT a.attname, a.atttypid, "
                "a.atttypmod, a.attlen FROM "
                    "pg_attribute a, pg_class c, pg_namespace n WHERE "
                    "n.nspname = '%s' AND a.attrelid = c.oid AND "
                    "n.oid = c.relnamespace AND "
                    "a.atttypid != 0 AND "
                    "a.attnum > 0 AND c.relname = '%s'",
                    state->schema,
                    state->table);
        }
        else
        {
                stringbuffer_aprintf(&sb,
                    "SELECT a.attname, a.atttypid, "
                    "a.atttypmod, a.attlen FROM "
                    "pg_attribute a, pg_class c WHERE "
                    "a.attrelid = c.oid and a.attnum > 0 AND "
                    "a.atttypid != 0 AND "
                    "c.relname = '%s' AND "
                    "pg_catalog.pg_table_is_visible(c.oid)",
                    state->table);
        }
 
        LWDEBUGF(3, "query is: %s\n", stringbuffer_getstring(&sb));
 
        res = PQexec(state->conn, stringbuffer_getstring(&sb));
        stringbuffer_release(&sb);
 
        if (PQresultStatus(res) != PGRES_TUPLES_OK)
        {
                snprintf(state->message, SHPDUMPERMSGLEN, _("Error querying for attributes: %s"), PQresultErrorMessage(res));
                PQclear(res);
                return SHPDUMPERERR;
        }
 
        if (!PQntuples(res))
        {
                snprintf(state->message, SHPDUMPERMSGLEN, _("Table %s does not exist"), state->table);
                PQclear(res);
                return SHPDUMPERERR;
        }
 
        /* If a shapefile name was specified, use it. Otherwise simply use the table name. */
        if (state->config->shp_file != NULL)
                state->shp_file = state->config->shp_file;
        else
                state->shp_file = state->table;
 
        /* Create the dbf file: */
        /* If there's a user-specified encoding hanging around, try and use that. */
        /* Otherwise, just use UTF-8 encoding, since that's usually our client encoding. */
        if ( getenv("PGCLIENTENCODING") )
        {
                char *codepage = encoding2codepage(getenv("PGCLIENTENCODING"));
                if (!codepage)
                        return SHPDUMPERERR;
                state->dbf = DBFCreateEx(state->shp_file, codepage);
                free(codepage);
        }
        else
        {
                state->dbf = DBFCreateEx(state->shp_file, "UTF-8");
        }
 
        if (!state->dbf)
        {
                snprintf(state->message, SHPDUMPERMSGLEN, _("Could not create dbf file %s"), state->shp_file);
                return SHPDUMPERERR;
        }
 
        /* Mimic old behaviour and skip the EOF character (1A) */
        DBFSetWriteEndOfFileChar(state->dbf, 0);
 
        /*
         * Scan the result setting fields to be returned in mainscan
         * query, filling the type_ary, and creating .dbf and .shp files.
         */
        state->dbffieldnames = malloc(sizeof(char *) * PQntuples(res));
        state->dbffieldtypes = malloc(sizeof(int) * PQntuples(res));
        state->pgfieldnames = malloc(sizeof(char *) * PQntuples(res));
        state->pgfieldlens = malloc(sizeof(int) * PQntuples(res));
        state->pgfieldtypmods = malloc(sizeof(int) * PQntuples(res));
        state->fieldcount = 0;
        int tmpint = 1;
 
        for (i = 0; i < PQntuples(res); i++)
        {
                char *ptr;
 
                int pgfieldtype, pgtypmod, pgfieldlen;
                char *pgfieldname;
 
                int dbffieldtype, dbffieldsize, dbffielddecs;
                char *dbffieldname;
 
                pgfieldname = PQgetvalue(res, i, 0);
                pgfieldtype = atoi(PQgetvalue(res, i, 1));
                pgtypmod = atoi(PQgetvalue(res, i, 2));
                pgfieldlen = atoi(PQgetvalue(res, i, 3));
                dbffieldtype = -1;
                dbffieldsize = 0;
                dbffielddecs = 0;
 
                /*
                 * This is a geometry/geography column
                 */
                if (pgfieldtype == state->geom_oid || pgfieldtype == state->geog_oid)
                {
                        /* If no geometry/geography column has been found yet... */
                        if (!state->geo_col_name)
                        {
                                /* If either no geo* column name was provided (in which case this is
                                   the first match) or we match the provided column name, we have
                                   found our geo* column */
                                if (!state->config->geo_col_name || !strcmp(state->config->geo_col_name, pgfieldname))
                                {
                                        dbffieldtype = 9;
 
                                        state->geo_col_name = strdup(pgfieldname);
                                }
                        }
                }
 
                /*
                 * Everything else (non geometries) will be
                 * a DBF attribute.
                 */
 
                /* Skip gid (if not asked to do otherwise */
                if (!strcmp(pgfieldname, "gid") )
                {
                        gidfound = 1;
 
                        if (!state->config->includegid)
                                continue;
                }
 
                /* Unescape any reserved column names */
                ptr = pgfieldname;
                if (!state->config->unescapedattrs)
                {
                        if (*ptr == '_')
                                ptr += 2;
                }
 
                /*
                 * This needs special handling since both xmin and _xmin
                 * becomes __xmin when escaped
                 */
 
                /* Limit dbf field name to 10-digits */
                dbffieldname = malloc(11);
                strncpy(dbffieldname, ptr, 10);
                dbffieldname[10] = '\0';
 
                /* If a column map file has been passed in,
                 * use this to create the dbf field name from
                 * the PostgreSQL column name */
                {
                        const char *mapped = colmap_dbf_by_pg(&state->column_map, pgfieldname);
                        if (mapped)
                        {
                                strncpy(dbffieldname, mapped, 10);
                                dbffieldname[10] = '\0';
                        }
                }
 
                /*
                 * make sure the fields all have unique names,
                 */
                tmpint = 1;
                for (j = 0; j < state->fieldcount; j++)
                {
                        if (!strncasecmp(dbffieldname, state->dbffieldnames[j], 10))
                        {
                                sprintf(dbffieldname, "%.7s_%.2d", ptr, abs(tmpint) % 100);
                                tmpint++;
                                continue;
                        }
                }
 
                /* make UPPERCASE if keep_fieldname_case = 0 */
                if (!state->config->keep_fieldname_case)
                {
                        size_t nameit;
                        for (nameit = 0; nameit < strlen(dbffieldname); nameit++)
                                dbffieldname[nameit] = toupper(dbffieldname[nameit]);
                }
 
                /* Issue warning if column has been renamed */
                if (strcasecmp(dbffieldname, pgfieldname))
                {
                        snprintf(buf, sizeof(buf), _("Warning, field %s renamed to %s\n"), pgfieldname, dbffieldname);
                        /* Note: we concatenate all warnings from the main loop as this is useful information */
                        if (SHPDUMPERMSGLEN > (strlen(state->message) + 1))
                                strncat(state->message, buf, SHPDUMPERMSGLEN - (strlen(state->message) + 1));
 
                        ret = SHPDUMPERWARN;
                }
 
 
                /*
                 * Find appropriate type of dbf attributes
                 */
 
                /* int2 type */
                if (pgfieldtype == 21)
                {
                        /*
                         * Longest text representation for
                         * an int2 type (16bit) is 6 bytes
                         * (-32768)
                         */
                        dbffieldtype = FTInteger;
                        dbffieldsize = 6;
                        dbffielddecs = 0;
                }
 
                /* int4 type */
                else if (pgfieldtype == 23)
                {
                        /*
                         * Longest text representation for
                         * an int4 type (32bit) is 11 bytes
                         * (-2147483648)
                         */
                        dbffieldtype = FTInteger;
                        dbffieldsize = 11;
                        dbffielddecs = 0;
                }
 
                /* int8 type */
                else if (pgfieldtype == 20)
                {
                        /*
                         * Longest text representation for
                         * an int8 type (64bit) is 20 bytes
                         * (-9223372036854775808)
                         */
                        dbffieldtype = FTInteger;
                        dbffieldsize = 19;
                        dbffielddecs = 0;
                }
 
                /*
                 * double or numeric types:
                 *    700: float4
                 *    701: float8
                 *   1700: numeric
                 *
                 *
                 * TODO: stricter handling of sizes
                 */
                else if (pgfieldtype == 700 || pgfieldtype == 701 || pgfieldtype == 1700)
                {
                        dbffieldtype = FTDouble;
                        dbffieldsize = 32;
                        dbffielddecs = 10;
                }
 
                /*
                 * Boolean field, we use FTLogical
                 */
                else if (pgfieldtype == 16)
                {
                        dbffieldtype = FTLogical;
                        dbffieldsize = 1;
                        dbffielddecs = 0;
                }
 
                /*
                 * Date field
                 */
                else if (pgfieldtype == 1082)
                {
                        dbffieldtype = FTDate;
                        dbffieldsize = 8;
                        dbffielddecs = 0;
                }
 
                /*
                 * time, timetz, timestamp, or timestamptz field.
                 */
                else if (pgfieldtype == 1083 || pgfieldtype == 1266 || pgfieldtype == 1114 || pgfieldtype == 1184)
                {
                        int secondsize;
 
                        switch (pgtypmod)
                        {
                        case -1:
                                secondsize = 6 + 1;
                                break;
                        case 0:
                                secondsize = 0;
                                break;
                        default:
                                secondsize = pgtypmod + 1;
                                break;
                        }
 
                        /* We assume the worst case scenario for all of these:
                         * date = '5874897-12-31' = 13
                         * date = '294276-11-20' = 12 (with --enable-interger-datetimes)
                         * time = '00:00:00' = 8
                         * zone = '+01:39:52' = 9 (see Europe/Helsinki around 1915)
                         */
 
                        /* time */
                        if (pgfieldtype == 1083)
                        {
                                dbffieldsize = 8 + secondsize;
                        }
                        /* timetz */
                        else if (pgfieldtype == 1266)
                        {
                                dbffieldsize = 8 + secondsize + 9;
                        }
                        /* timestamp */
                        else if (pgfieldtype == 1114)
                        {
                                dbffieldsize = 13 + 1 + 8 + secondsize;
                        }
                        /* timestamptz */
                        else if (pgfieldtype == 1184)
                        {
                                dbffieldsize = 13 + 1 + 8 + secondsize + 9;
                        }
 
                        dbffieldtype = FTString;
                        dbffielddecs = 0;
                }
 
                /*
                 * uuid type 36 bytes (12345678-9012-3456-7890-123456789012)
                 */
                else if (pgfieldtype == 2950)
                {
                        dbffieldtype = FTString;
                        dbffieldsize = 36;
                        dbffielddecs = 0;
                }
 
                /*
                 * For variable-sized fields we know about, we use
                 * the maximum allowed size.
                  * 1042 is bpchar,  1043 is varchar
                 */
                else if ((pgfieldtype == 1042 || pgfieldtype == 1043) && pgtypmod != -1)
                {
                        /*
                         * mod is maximum allowed size, including
                         * header which contains *real* size.
                         */
                        dbffieldtype = FTString;
                        dbffieldsize = pgtypmod - 4; /* 4 is header size */
                        dbffielddecs = 0;
                }
 
                /* For all other valid non-geometry/geography fields... */
                else if (dbffieldtype == -1)
                {
                        /*
                        * For types we don't know anything about, all
                        * we can do is query the table for the maximum field
                        * size.
                        */
                        dbffieldsize = getMaxFieldSize(state->conn, state->schema, state->table, pgfieldname);
                        if (dbffieldsize == -1)
                        {
                                free(dbffieldname);
                                return 0;
                        }
 
                        if (!dbffieldsize)
                                dbffieldsize = 32;
 
                        /* might 0 be a good size ? */
 
                        dbffieldtype = FTString;
                        dbffielddecs = 0;
 
                        /* Check to make sure the final field size isn't too large */
                        if (dbffieldsize > MAX_DBF_FIELD_SIZE)
                        {
                                /* Note: we concatenate all warnings from the main loop as this is useful information */
                                snprintf(buf, sizeof(buf), _("Warning: values of field '%s' exceeding maximum dbf field width (%d) "
                                        "will be truncated.\n"), dbffieldname, MAX_DBF_FIELD_SIZE);
 
                                if (SHPDUMPERMSGLEN > (strlen(state->message) + 1))
                                        strncat(state->message, buf, SHPDUMPERMSGLEN - (strlen(state->message)+1));
 
                                dbffieldsize = MAX_DBF_FIELD_SIZE;
 
                                ret = SHPDUMPERWARN;
                        }
                }
 
                LWDEBUGF(3, "DBF FIELD_NAME: %s, SIZE: %d\n", dbffieldname, dbffieldsize);
 
                if (dbffieldtype != 9)
                {
                        /* Add the field to the DBF file */
                        if (DBFAddField(state->dbf, dbffieldname, dbffieldtype, dbffieldsize, dbffielddecs) == -1)
                        {
                                snprintf(state->message, SHPDUMPERMSGLEN, _("Error: field %s of type %d could not be created."), dbffieldname, dbffieldtype);
                                free(dbffieldname);
 
                                return SHPDUMPERERR;
                        }
 
                        /* Add the field information to our field arrays */
                        state->dbffieldnames[state->fieldcount] = dbffieldname;
                        state->dbffieldtypes[state->fieldcount] = dbffieldtype;
                        state->pgfieldnames[state->fieldcount] = pgfieldname;
                        state->pgfieldlens[state->fieldcount] = pgfieldlen;
                        state->pgfieldtypmods[state->fieldcount] = pgtypmod;
 
                        state->fieldcount++;
                }
                else
                {
                        free(dbffieldname);
                }
        }
 
        /* Now we have generated the field lists, grab some info about the table */
        status = getTableInfo(state);
        if (status == SHPDUMPERERR)
                return SHPDUMPERERR;
 
        LWDEBUGF(3, "rows: %d\n", state->rowcount);
        LWDEBUGF(3, "shptype: %c\n", state->outtype);
        LWDEBUGF(3, "shpouttype: %d\n", state->outshptype);
 
        /* If we didn't find a geometry/geography column... */
        if (!state->geo_col_name)
        {
                if (state->config->geo_col_name)
                {
                        /* A geo* column was specified, but not found */
                        snprintf(state->message, SHPDUMPERMSGLEN, _("%s: no such attribute in table %s"), state->config->geo_col_name, state->table);
 
                        return SHPDUMPERERR;
                }
                else
                {
                        /* No geo* column specified so we can only create the DBF section -
                           but let's issue a warning... */
                        snprintf(buf, sizeof(buf), _("No geometry column found.\nThe DBF file will be created but not the shx or shp files.\n"));
                        if (SHPDUMPERMSGLEN > (strlen(state->message) + 1))
                                strncat(state->message, buf, SHPDUMPERMSGLEN - (strlen(state->message)+1));
 
                        state->shp = NULL;
 
                        ret = SHPDUMPERWARN;
                }
        }
        else
        {
                /* Since we have found a geo* column, open the shapefile */
                state->shp = SHPCreate(state->shp_file, state->outshptype);
                if (!state->shp)
                {
                        snprintf(state->message, SHPDUMPERMSGLEN, _("Could not open shapefile %s!"), state->shp_file);
 
                        return SHPDUMPERERR;
                }
        }
 
        /* Now we have the complete list of fieldnames, let's generate the SQL query. First let's make sure
           we reserve enough space for tables with lots of columns */
        j = 0;
 
        /*TODO: this really should be rewritten to use stringbuffer */
        for (i = 0; i < state->fieldcount; i++)
                j += strlen( state->pgfieldnames[i]) + 10;      /*add extra space for the quotes to quote identify and any embedded quotes that may need escaping */
 
        stringbuffer_init(&sb);
 
        stringbuffer_append(&sb, "DECLARE cur ");
        if (state->config->binary)
                stringbuffer_append(&sb, "BINARY ");
 
        stringbuffer_append(&sb, "CURSOR FOR SELECT ");
 
        for (i = 0; i < state->fieldcount; i++)
        {
                /* Comma-separated column names */
                if (i > 0) {
                        stringbuffer_append(&sb, ",");
                }
 
                quoted = quote_identifier(state->pgfieldnames[i]);
                if (state->config->binary)
                {
                        stringbuffer_aprintf(&sb, "%s::text", quoted);
                }
                else
                {
                        stringbuffer_append(&sb, quoted);
                }
                free(quoted);
        }
 
        /* If we found a valid geometry/geography column then use it */
        if (state->geo_col_name)
        {
                /* If this is the (only) column, no need for the initial comma */
                if (state->fieldcount > 0) {
                        stringbuffer_append(&sb, ",");
                }
 
                quoted = quote_identifier(state->geo_col_name);
#ifndef WORDS_BIGENDIAN
                if (state->pgis_major_version > 0) {
                        stringbuffer_aprintf(&sb, "ST_asEWKB(ST_SetSRID(%s::geometry, 0), 'XDR') AS _geoX", quoted);
                }
                else
                {
                        stringbuffer_aprintf(&sb, "asbinary(%s::geometry, 'XDR') AS _geoX", quoted);
                }
#else
                if (state->pgis_major_version > 0)
                {
                        stringbuffer_aprintf(&sb, "ST_AsEWKB(ST_SetSRID(%s::geometry, 0), 'NDR') AS _geoX", quoted);
                }
                else
                {
                        stringbuffer_aprintf(&sb, "asbinary(%s::geometry, 'NDR') AS _geoX", quoted);
                }
#endif
                free(quoted);
        }
 
        if (state->schema)
        {
                stringbuffer_aprintf(&sb,
                    " FROM \"%s\".\"%s\"",
                    state->schema, state->table);
        }
        else
        {
                stringbuffer_aprintf(&sb,
                    " FROM \"%s\"",
                    state->table);
        }
 
        /* Order by 'gid' (if found) */
        if (gidfound)
        {
                stringbuffer_append(&sb, " ORDER BY \"gid\"");
        }
 
        /* Now we've finished with the result set, we can dispose of it */
        PQclear(res);
 
        LWDEBUGF(3, "FINAL QUERY: %s\n", stringbuffer_getstring(&sb));
 
        /*
         * Begin the transaction
         * (a cursor can only be defined inside a transaction block)
         */
        res = PQexec(state->conn, "BEGIN");
        if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                snprintf(state->message, SHPDUMPERMSGLEN, _("Error starting transaction: %s"), PQresultErrorMessage(res));
                PQclear(res);
                return SHPDUMPERERR;
        }
 
        PQclear(res);
 
        /* Execute the main scan query */
        state->main_scan_query = stringbuffer_getstringcopy(&sb);
        stringbuffer_release(&sb);
        res = PQexec(state->conn, state->main_scan_query);
        if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                snprintf(state->message, SHPDUMPERMSGLEN, _("Error executing main scan query: %s"), PQresultErrorMessage(res));
                PQclear(res);
                return SHPDUMPERERR;
        }
 
        PQclear(res);
 
        /* Setup initial scan state */
        state->currow = 0;
        state->curresrow = 0;
        state->currescount = 0;
        state->fetchres = NULL;
 
        /* Generate the fetch query */
        state->fetch_query = core_asprintf("FETCH %d FROM cur", state->config->fetchsize);
 
        return SHPDUMPEROK;
}

References _, shp_dumper_config::binary, colmap_dbf_by_pg(), colmap_read(), shp_dumper_state::column_map, shp_dumper_config::column_map_filename, shp_dumper_state::config, shp_dumper_state::conn, core_asprintf(), shp_dumper_state::currescount, shp_dumper_state::curresrow, shp_dumper_state::currow, shp_dumper_state::dbf, DBFAddField(), DBFCreateEx(), shp_dumper_state::dbffieldnames, shp_dumper_state::dbffieldtypes, DBFSetWriteEndOfFileChar(), encoding2codepage(), shp_dumper_state::fetch_query, shp_dumper_state::fetchres, shp_dumper_config::fetchsize, shp_dumper_state::fieldcount, free(), FTDate, FTDouble, FTInteger, FTLogical, FTString, shp_dumper_config::geo_col_name, shp_dumper_state::geo_col_name, shp_dumper_state::geog_oid, shp_dumper_state::geom_oid, getMaxFieldSize(), getTableInfo(), shp_dumper_config::includegid, shp_dumper_config::keep_fieldname_case, LWDEBUGF, shp_dumper_state::main_scan_query, malloc(), MAX_DBF_FIELD_SIZE, shp_dumper_state::message, shp_dumper_state::outshptype, shp_dumper_state::outtype, shp_dumper_state::pgfieldlens, shp_dumper_state::pgfieldnames, shp_dumper_state::pgfieldtypmods, shp_dumper_state::pgis_major_version, quote_identifier(), window::res, shp_dumper_state::rowcount, shp_dumper_config::schema, shp_dumper_state::schema, shp_dumper_state::shp, shp_dumper_config::shp_file, shp_dumper_state::shp_file, SHPCreate(), SHPDUMPERERR, SHPDUMPERMSGLEN, SHPDUMPEROK, SHPDUMPERWARN, stringbuffer_append(), stringbuffer_aprintf(), stringbuffer_getstring(), stringbuffer_getstringcopy(), stringbuffer_init(), stringbuffer_release(), shp_dumper_config::table, shp_dumper_state::table, shp_dumper_config::unescapedattrs, and shp_dumper_config::usrquery.

Referenced by main(), and pgui_action_export().

Here is the call graph for this function:

Here is the caller graph for this function: