ShpLoaderGetSQLHeader()

int ShpLoaderGetSQLHeader	(	SHPLOADERSTATE *	state,
		char **	strheader
	)

TODO: if the table has more then one geometry column the DROP TABLE call will leave spurious records in geometry_columns.

If the geometry column in the table being dropped does not match 'the_geom' or the name specified with -g an error is returned by DropGeometryColumn.

The table to be dropped might not exist.

Definition at line 1273 of file shp2pgsql-core.c.

References _, shp_loader_state::config, shp_loader_config::encoding, shp_loader_state::field_names, shp_loader_state::geo_col, shp_loader_config::geography, shp_loader_config::idxtablespace, malloc(), shp_loader_state::message, shp_loader_state::num_fields, shp_loader_config::opt, shp_loader_state::pgdims, shp_loader_state::pgfieldtypes, shp_loader_state::pgtype, shp_loader_config::readshape, shp_loader_config::schema, SHPLOADERERR, SHPLOADERMSGLEN, SHPLOADEROK, SRID_UNKNOWN, stringbuffer_aprintf(), stringbuffer_clear(), stringbuffer_create(), stringbuffer_destroy(), stringbuffer_getstring(), shp_loader_config::table, shp_loader_config::tablespace, shp_loader_state::to_srid, shp_loader_state::types, shp_loader_config::usetransaction, and shp_loader_state::widths.

Referenced by main(), and pgui_action_import().

{
        stringbuffer_t *sb;
        char *ret;
        int j;

        /* Create the stringbuffer containing the header; we use this API as it's easier
           for handling string resizing during append */
        sb = stringbuffer_create();
        stringbuffer_clear(sb);

        /* Set the client encoding if required */
        if (state->config->encoding)
        {
                stringbuffer_aprintf(sb, "SET CLIENT_ENCODING TO UTF8;\n");
        }

        /* Use SQL-standard string escaping rather than PostgreSQL standard */
        stringbuffer_aprintf(sb, "SET STANDARD_CONFORMING_STRINGS TO ON;\n");

        /* Drop table if requested */
        if (state->config->opt == 'd')
        {
                if (state->config->schema)
                {
                        if (state->config->readshape == 1 && (! state->config->geography) )
                        {
                                stringbuffer_aprintf(sb, "SELECT DropGeometryColumn('%s','%s','%s');\n",
                                                     state->config->schema, state->config->table, state->geo_col);
                        }

                        stringbuffer_aprintf(sb, "DROP TABLE IF EXISTS \"%s\".\"%s\";\n", state->config->schema,
                                             state->config->table);
                }
                else
                {
                        if (state->config->readshape == 1  && (! state->config->geography) )
                        {
                                stringbuffer_aprintf(sb, "SELECT DropGeometryColumn('','%s','%s');\n",
                                                     state->config->table, state->geo_col);
                        }

                        stringbuffer_aprintf(sb, "DROP TABLE IF EXISTS \"%s\";\n", state->config->table);
                }
        }

        /* Start of transaction if we are using one */
        if (state->config->usetransaction)
        {
                stringbuffer_aprintf(sb, "BEGIN;\n");
        }

        /* If not in 'append' mode create the spatial table */
        if (state->config->opt != 'a')
        {
                /*
                * Create a table for inserting the shapes into with appropriate
                * columns and types
                */
                if (state->config->schema)
                {
                        stringbuffer_aprintf(sb, "CREATE TABLE \"%s\".\"%s\" (gid serial",
                                             state->config->schema, state->config->table);
                }
                else
                {
                        stringbuffer_aprintf(sb, "CREATE TABLE \"%s\" (gid serial", state->config->table);
                }

                /* Generate the field types based upon the shapefile information */
                for (j = 0; j < state->num_fields; j++)
                {
                        stringbuffer_aprintf(sb, ",\n\"%s\" ", state->field_names[j]);

                        /* First output the raw field type string */
                        stringbuffer_aprintf(sb, "%s", state->pgfieldtypes[j]);

                        /* Some types do have typmods though... */
                        if (!strcmp("varchar", state->pgfieldtypes[j]))
                                stringbuffer_aprintf(sb, "(%d)", state->widths[j]);

                        if (!strcmp("numeric", state->pgfieldtypes[j]))
                        {
                                /* Doubles we just allow PostgreSQL to auto-detect the size */
                                if (state->types[j] != FTDouble)
                                        stringbuffer_aprintf(sb, "(%d,0)", state->widths[j]);
                        }
                }

                /* Add the geography column directly to the table definition, we don't
                   need to do an AddGeometryColumn() call. */
                if (state->config->readshape == 1 && state->config->geography)
                {
                        char *dimschar;

                        if (state->pgdims == 4)
                                dimschar = "ZM";
                        else
                                dimschar = "";

                        if (state->to_srid != SRID_UNKNOWN && state->to_srid != 4326)
                        {
                                snprintf(state->message, SHPLOADERMSGLEN, _("Invalid SRID for geography type: %d"), state->to_srid);
                                stringbuffer_destroy(sb);
                                return SHPLOADERERR;
                        }
                        stringbuffer_aprintf(sb, ",\n\"%s\" geography(%s%s,%d)", state->geo_col, state->pgtype, dimschar, 4326);
                }
                stringbuffer_aprintf(sb, ")");

                /* Tablespace is optional. */
                if (state->config->tablespace != NULL)
                {
                        stringbuffer_aprintf(sb, " TABLESPACE \"%s\"", state->config->tablespace);
                }
                stringbuffer_aprintf(sb, ";\n");

                /* Create the primary key.  This is done separately because the index for the PK needs
                 * to be in the correct tablespace. */

                /* TODO: Currently PostgreSQL does not allow specifying an index to use for a PK (so you get
                 *       a default one called table_pkey) and it does not provide a way to create a PK index
                 *       in a specific tablespace.  So as a hacky solution we create the PK, then move the
                 *       index to the correct tablespace.  Eventually this should be:
                 *           CREATE INDEX table_pkey on table(gid) TABLESPACE tblspc;
                 *           ALTER TABLE table ADD PRIMARY KEY (gid) USING INDEX table_pkey;
                 *       A patch has apparently been submitted to PostgreSQL to enable this syntax, see this thread:
                 *           http://archives.postgresql.org/pgsql-hackers/2011-01/msg01405.php */
                stringbuffer_aprintf(sb, "ALTER TABLE ");

                /* Schema is optional, include if present. */
                if (state->config->schema)
                {
                        stringbuffer_aprintf(sb, "\"%s\".",state->config->schema);
                }
                stringbuffer_aprintf(sb, "\"%s\" ADD PRIMARY KEY (gid);\n", state->config->table);

                /* Tablespace is optional for the index. */
                if (state->config->idxtablespace != NULL)
                {
                        stringbuffer_aprintf(sb, "ALTER INDEX ");
                        if (state->config->schema)
                        {
                                stringbuffer_aprintf(sb, "\"%s\".",state->config->schema);
                        }

                        /* WARNING: We're assuming the default "table_pkey" name for the primary
                         *          key index.  PostgreSQL may use "table_pkey1" or similar in the
                         *          case of a name conflict, so you may need to edit the produced
                         *          SQL in this rare case. */
                        stringbuffer_aprintf(sb, "\"%s_pkey\" SET TABLESPACE \"%s\";\n",
                                                state->config->table, state->config->idxtablespace);
                }

                /* Create the geometry column with an addgeometry call */
                if (state->config->readshape == 1 && (!state->config->geography))
                {
                        /* If they didn't specify a target SRID, see if they specified a source SRID. */
                        int srid = state->to_srid;
                        if (state->config->schema)
                        {
                                stringbuffer_aprintf(sb, "SELECT AddGeometryColumn('%s','%s','%s','%d',",
                                                     state->config->schema, state->config->table, state->geo_col, srid);
                        }
                        else
                        {
                                stringbuffer_aprintf(sb, "SELECT AddGeometryColumn('','%s','%s','%d',",
                                                     state->config->table, state->geo_col, srid);
                        }

                        stringbuffer_aprintf(sb, "'%s',%d);\n", state->pgtype, state->pgdims);
                }
        }

        /* Copy the string buffer into a new string, destroying the string buffer */
        ret = (char *)malloc(strlen((char *)stringbuffer_getstring(sb)) + 1);
        strcpy(ret, (char *)stringbuffer_getstring(sb));
        stringbuffer_destroy(sb);

        *strheader = ret;

        return SHPLOADEROK;
}

Here is the call graph for this function:

Here is the caller graph for this function: