update_table_chooser_from_database()

static void update_table_chooser_from_database ( )

static

Definition at line 670 of file shp2pgsql-gui.c.

{
        PGresult *result, *geocol_result;
        GtkTreeIter iter, geocol_iter;
        GtkListStore *dumper_geocol_combo_list;
        char *connection_string, *sql_form, *query, *schema, *table, *geocol_query, *geocol_name=NULL;
        int hasgeo, i, j;
 
        /* Open a connection to the database */
        connection_string = ShpDumperGetConnectionStringFromConn(conn);
        pg_connection = PQconnectdb(connection_string);
 
        /* Here we find a list of all tables/views that not in a pg_* schema (or information_schema) and
           we return the schema name, table name and whether or not the table/view contains any geo
           columns */
        query = "SELECT tableoids.oid, n.nspname, tableoids.relname, COALESCE((SELECT 1 from pg_attribute WHERE attrelid = tableoids.oid AND atttypid IN (SELECT oid FROM pg_type WHERE typname in ('geometry', 'geography')) LIMIT 1), 0) hasgeo FROM (SELECT c.oid, c.relname, c.relnamespace FROM pg_class c WHERE c.relkind IN ('r', 'v', 'm', 'f','p') AND c.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname NOT ILIKE 'pg_%' AND nspname <> 'information_schema')) tableoids, pg_namespace n WHERE tableoids.relnamespace = n.oid ORDER BY n.nspname, tableoids.relname";
 
        result = PQexec(pg_connection, query);
 
        /* Free any existing entries in the model */
        gtk_list_store_clear(chooser_table_list_store);
 
        /* Now insert one row for each query result */
        for (i = 0; i < PQntuples(result); i++)
        {
                size_t sz;
                gtk_list_store_insert_before(chooser_table_list_store, &iter, NULL);
 
                /* Look up the geo columns; if there are none then we set the field to (None). If we have just one
                   column then we set the column name directly. If we have more than one then we create a combo
                   dropdown containing the column names */
                schema = PQgetvalue(result, i, PQfnumber(result, "nspname"));
                table = PQgetvalue(result, i, PQfnumber(result, "relname"));
 
                sql_form = "SELECT n.nspname, c.relname, a.attname FROM pg_class c, pg_namespace n, pg_attribute a WHERE c.relnamespace = n.oid AND n.nspname = '%s' AND c.relname = '%s' AND a.attrelid = c.oid AND a.atttypid IN (SELECT oid FROM pg_type WHERE typname in ('geometry', 'geography'))";
 
                sz = strlen(sql_form) + strlen(schema) + strlen(table) + 1;
                geocol_query = malloc(sz);
                snprintf(geocol_query, sz, sql_form, schema, table);
 
                geocol_result = PQexec(pg_connection, geocol_query);
 
                /* Create a combo list loaded with the column names. Note that while we create the model and load
                   the data here, we don't actually display the geo column in this dialog. Instead we build the
                   list here so that we can pass to the export table list store when creating a new entry. This
                   is to ensure that the export table list model can directly represent a SHPDUMPERCONFIG. */
                dumper_geocol_combo_list = gtk_list_store_new(TABLECHOOSER_GEOCOL_COMBO_COLUMNS, G_TYPE_STRING);
 
                if (PQntuples(geocol_result) > 0)
                {
                        /* Load the columns into the list store */
                        for (j = 0; j < PQntuples(geocol_result); j++)
                        {
                                geocol_name = PQgetvalue(geocol_result, j, PQfnumber(geocol_result, "attname"));
 
                                gtk_list_store_insert_before(dumper_geocol_combo_list, &geocol_iter, (GtkTreeIter *)TABLECHOOSER_GEOCOL_COMBO_TEXT);
                                gtk_list_store_set(dumper_geocol_combo_list, &geocol_iter,
                                                TABLECHOOSER_GEOCOL_COMBO_TEXT, geocol_name,
                                                -1);
                        }
                }
                else
                {
                        /* Add a "default" entry */
                        geocol_name = NULL;
 
                        gtk_list_store_insert_before(dumper_geocol_combo_list, &geocol_iter, (GtkTreeIter *)TABLECHOOSER_GEOCOL_COMBO_TEXT);
                        gtk_list_store_set(dumper_geocol_combo_list, &geocol_iter,
                                                TABLECHOOSER_GEOCOL_COMBO_TEXT, _("(None)"),
                                                -1);
                }
 
                /* Free the query result */
                PQclear(geocol_result);
 
                /* Free the query string */
                free(geocol_query);
 
                /* Set the list store data */
                hasgeo = atoi(PQgetvalue(result, i, PQfnumber(result, "hasgeo")));
                gtk_list_store_set(chooser_table_list_store, &iter,
                           TABLECHOOSER_SCHEMA_COLUMN, schema,
                           TABLECHOOSER_TABLE_COLUMN, table,
                           TABLECHOOSER_GEO_LISTSTORE_COLUMN, dumper_geocol_combo_list,
                           TABLECHOOSER_GEO_COLUMN, geocol_name,
                           TABLECHOOSER_HASGEO_COLUMN, hasgeo,
                           -1);
        }
 
        /* Clear up the result set */
        PQclear(result);
 
        /* Close the existing connection */
        PQfinish(pg_connection);
        pg_connection = NULL;
 
        return;
}

References _, chooser_table_list_store, conn, free(), malloc(), pg_connection, result, ShpDumperGetConnectionStringFromConn(), TABLECHOOSER_GEO_COLUMN, TABLECHOOSER_GEO_LISTSTORE_COLUMN, TABLECHOOSER_GEOCOL_COMBO_COLUMNS, TABLECHOOSER_GEOCOL_COMBO_TEXT, TABLECHOOSER_HASGEO_COLUMN, TABLECHOOSER_SCHEMA_COLUMN, and TABLECHOOSER_TABLE_COLUMN.

Referenced by pgui_action_open_table_dialog().

Here is the call graph for this function:

Here is the caller graph for this function: