static PyObject * PLy_cursor_query(const char *query) { PLyCursorObject *cursor; volatile MemoryContext oldcontext; volatile ResourceOwner oldowner; if ((cursor = PyObject_New(PLyCursorObject, &PLy_CursorType)) == NULL) return NULL; cursor->portalname = NULL; cursor->closed = false; cursor->mcxt = AllocSetContextCreate(TopMemoryContext, "PL/Python cursor context", ALLOCSET_DEFAULT_MINSIZE, ALLOCSET_DEFAULT_INITSIZE, ALLOCSET_DEFAULT_MAXSIZE); PLy_typeinfo_init(&cursor->result, cursor->mcxt); oldcontext = CurrentMemoryContext; oldowner = CurrentResourceOwner; PLy_spi_subtransaction_begin(oldcontext, oldowner); PG_TRY(); { PLyExecutionContext *exec_ctx = PLy_current_execution_context(); SPIPlanPtr plan; Portal portal; pg_verifymbstr(query, strlen(query), false); plan = SPI_prepare(query, 0, NULL); if (plan == NULL) elog(ERROR, "SPI_prepare failed: %s", SPI_result_code_string(SPI_result)); portal = SPI_cursor_open(NULL, plan, NULL, NULL, exec_ctx->curr_proc->fn_readonly); SPI_freeplan(plan); if (portal == NULL) elog(ERROR, "SPI_cursor_open() failed: %s", SPI_result_code_string(SPI_result)); cursor->portalname = MemoryContextStrdup(cursor->mcxt, portal->name); PLy_spi_subtransaction_commit(oldcontext, oldowner); } PG_CATCH(); { PLy_spi_subtransaction_abort(oldcontext, oldowner); return NULL; } PG_END_TRY(); Assert(cursor->portalname != NULL); return (PyObject *) cursor; }
Portal pgr_SPI_cursor_open(SPIPlanPtr SPIplan) { PGR_DBG("Opening Portal"); Portal SPIportal; SPIportal = SPI_cursor_open(NULL, SPIplan, NULL, NULL, true); if (SPIportal == NULL) { elog(ERROR, "SPI_cursor_open returns NULL"); } return SPIportal; }
Datum tsquery_rewrite_query(PG_FUNCTION_ARGS) { TSQuery query = PG_GETARG_TSQUERY_COPY(0); text *in = PG_GETARG_TEXT_P(1); TSQuery rewrited = query; MemoryContext outercontext = CurrentMemoryContext; MemoryContext oldcontext; QTNode *tree; char *buf; SPIPlanPtr plan; Portal portal; bool isnull; if (query->size == 0) { PG_FREE_IF_COPY(in, 1); PG_RETURN_POINTER(rewrited); } tree = QT2QTN(GETQUERY(query), GETOPERAND(query)); QTNTernary(tree); QTNSort(tree); buf = text_to_cstring(in); SPI_connect(); if ((plan = SPI_prepare(buf, 0, NULL)) == NULL) elog(ERROR, "SPI_prepare(\"%s\") failed", buf); if ((portal = SPI_cursor_open(NULL, plan, NULL, NULL, true)) == NULL) elog(ERROR, "SPI_cursor_open(\"%s\") failed", buf); SPI_cursor_fetch(portal, true, 100); if (SPI_tuptable == NULL || SPI_tuptable->tupdesc->natts != 2 || SPI_gettypeid(SPI_tuptable->tupdesc, 1) != TSQUERYOID || SPI_gettypeid(SPI_tuptable->tupdesc, 2) != TSQUERYOID) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("ts_rewrite query must return two tsquery columns"))); while (SPI_processed > 0 && tree) { uint64 i; for (i = 0; i < SPI_processed && tree; i++) { Datum qdata = SPI_getbinval(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 1, &isnull); Datum sdata; if (isnull) continue; sdata = SPI_getbinval(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 2, &isnull); if (!isnull) { TSQuery qtex = DatumGetTSQuery(qdata); TSQuery qtsubs = DatumGetTSQuery(sdata); QTNode *qex, *qsubs = NULL; if (qtex->size == 0) { if (qtex != (TSQuery) DatumGetPointer(qdata)) pfree(qtex); if (qtsubs != (TSQuery) DatumGetPointer(sdata)) pfree(qtsubs); continue; } qex = QT2QTN(GETQUERY(qtex), GETOPERAND(qtex)); QTNTernary(qex); QTNSort(qex); if (qtsubs->size) qsubs = QT2QTN(GETQUERY(qtsubs), GETOPERAND(qtsubs)); oldcontext = MemoryContextSwitchTo(outercontext); tree = findsubquery(tree, qex, qsubs, NULL); MemoryContextSwitchTo(oldcontext); QTNFree(qex); if (qtex != (TSQuery) DatumGetPointer(qdata)) pfree(qtex); QTNFree(qsubs); if (qtsubs != (TSQuery) DatumGetPointer(sdata)) pfree(qtsubs); if (tree) { /* ready the tree for another pass */ QTNClearFlags(tree, QTN_NOCHANGE); QTNSort(tree); } } } SPI_freetuptable(SPI_tuptable); SPI_cursor_fetch(portal, true, 100); } SPI_freetuptable(SPI_tuptable); SPI_cursor_close(portal); SPI_freeplan(plan); SPI_finish(); if (tree) { QTNBinary(tree); rewrited = QTN2QT(tree); QTNFree(tree); PG_FREE_IF_COPY(query, 0); } else { SET_VARSIZE(rewrited, HDRSIZETQ); rewrited->size = 0; } pfree(buf); PG_FREE_IF_COPY(in, 1); PG_RETURN_POINTER(rewrited); }
static int compute_apsp_warshall(char* sql, bool directed, bool has_reverse_cost, apsp_element_t **pair, int *pair_count) { int i; int SPIcode; void *SPIplan; Portal SPIportal; bool moredata = TRUE; int ntuples; edge_t *edges = NULL; int total_tuples = 0; edge_columns_t edge_columns = {.id= -1, .source= -1, .target= -1, .cost= -1, .reverse_cost= -1}; int v_max_id=0; int v_min_id=INT_MAX; int s_count = 0; int t_count = 0; char *err_msg; int ret = -1; register int z; // set<int> vertices; DBG("start compute_apsp_warshall\n"); SPIcode = SPI_connect(); if (SPIcode != SPI_OK_CONNECT) { elog(ERROR, "compute_apsp_warshall: couldn't open a connection to SPI"); return -1; } SPIplan = SPI_prepare(sql, 0, NULL); if (SPIplan == NULL) { elog(ERROR, "compute_apsp_warshall: couldn't create query plan via SPI"); return -1; } if ((SPIportal = SPI_cursor_open(NULL, SPIplan, NULL, NULL, true)) == NULL) { elog(ERROR, "compute_apsp_warshall: SPI_cursor_open('%s') returns NULL", sql); return -1; } while (moredata == TRUE) { SPI_cursor_fetch(SPIportal, TRUE, TUPLIMIT); if (edge_columns.id == -1) { if (fetch_edge_columns(SPI_tuptable, &edge_columns, has_reverse_cost) == -1) return finish(SPIcode, ret); } ntuples = SPI_processed; total_tuples += ntuples; if (!edges) edges = palloc(total_tuples * sizeof(edge_t)); else edges = repalloc(edges, total_tuples * sizeof(edge_t)); if (edges == NULL) { elog(ERROR, "Out of memory"); return finish(SPIcode, ret); } DBG("Number of tuples fetched: %i",ntuples); if (ntuples > 0) { int t; SPITupleTable *tuptable = SPI_tuptable; TupleDesc tupdesc = SPI_tuptable->tupdesc; for (t = 0; t < ntuples; t++) { HeapTuple tuple = tuptable->vals[t]; fetch_edge(&tuple, &tupdesc, &edge_columns, &edges[total_tuples - ntuples + t]); // vertices.insert(edges[total_tuples - ntuples + t].source); // vertices.insert(edges[total_tuples - ntuples + t].target); } SPI_freetuptable(tuptable); } else { moredata = FALSE; } } #ifdef DEBUG for (i = 0; i < total_tuples; i++) { DBG("Step %i src_vertex_id %i ", i, edges[i].source); DBG(" dest_vertex_id %i ", edges[i].target); DBG(" cost %f ", edges[i].cost); } #endif DBG("Calling boost_apsp\n"); //start_vertex -= v_min_id; //end_vertex -= v_min_id; ret = boost_apsp(edges, total_tuples, 0, //vertices.size() directed, has_reverse_cost, pair, pair_count, &err_msg); DBG("Boost message: \n%s",err_msg); DBG("SIZE %i\n",*pair_count); /* //:::::::::::::::::::::::::::::::: //:: restoring original vertex id //:::::::::::::::::::::::::::::::: for(z=0;z<*path_count;z++) { //DBG("vetex %i\n",(*path)[z].vertex_id); (*path)[z].vertex_id+=v_min_id; } DBG("ret = %i\n", ret); DBG("*path_count = %i\n", *path_count); DBG("ret = %i\n", ret); */ if (ret < 0) { //elog(ERROR, "Error computing path: %s", err_msg); ereport(ERROR, (errcode(ERRCODE_E_R_E_CONTAINING_SQL_NOT_PERMITTED), errmsg("Error computing path: %s", err_msg))); } return finish(SPIcode, ret); } PG_FUNCTION_INFO_V1(apsp_warshall); Datum apsp_warshall(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; int call_cntr; int max_calls; TupleDesc tuple_desc; apsp_element_t *pair; /* stuff done only on the first call of the function */ if (SRF_IS_FIRSTCALL()) { MemoryContext oldcontext; int pair_count = 0; int ret; /* create a function context for cross-call persistence */ funcctx = SRF_FIRSTCALL_INIT(); /* switch to memory context appropriate for multiple function calls */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); ret = compute_apsp_warshall(text2char(PG_GETARG_TEXT_P(0)), PG_GETARG_BOOL(1), PG_GETARG_BOOL(2), &pair, &pair_count); #ifdef DEBUG DBG("Ret is %i", ret); if (ret >= 0) { int i; for (i = 0; i < pair_count; i++) { DBG("Step: %i, source_id: %i, target_id: %i, cost: %f ", i, pair[i].src_vertex_id, pair[i].dest_vertex_id, pair[i].cost); } } #endif /* total number of tuples to be returned */ funcctx->max_calls = pair_count; funcctx->user_fctx = pair; funcctx->tuple_desc = BlessTupleDesc(RelationNameGetTupleDesc("pgr_costResult")); MemoryContextSwitchTo(oldcontext); } /* stuff done on every call of the function */ funcctx = SRF_PERCALL_SETUP(); call_cntr = funcctx->call_cntr; max_calls = funcctx->max_calls; tuple_desc = funcctx->tuple_desc; pair = (apsp_element_t*) funcctx->user_fctx; if (call_cntr < max_calls) /* do when there is more left to send */ { HeapTuple tuple; Datum result; Datum *values; char* nulls; /* This will work for some compilers. If it crashes with segfault, try to change the following block with this one values = palloc(4 * sizeof(Datum)); nulls = palloc(4 * sizeof(char)); values[0] = call_cntr; nulls[0] = ' '; values[1] = Int32GetDatum(path[call_cntr].vertex_id); nulls[1] = ' '; values[2] = Int32GetDatum(path[call_cntr].edge_id); nulls[2] = ' '; values[3] = Float8GetDatum(path[call_cntr].cost); nulls[3] = ' '; */ values = palloc(4 * sizeof(Datum)); nulls = palloc(4 * sizeof(char)); values[0] = Int32GetDatum(call_cntr); nulls[0] = ' '; values[1] = Int32GetDatum(pair[call_cntr].src_vertex_id); nulls[1] = ' '; values[2] = Int32GetDatum(pair[call_cntr].dest_vertex_id); nulls[2] = ' '; values[3] = Float8GetDatum(pair[call_cntr].cost); nulls[3] = ' '; tuple = heap_formtuple(tuple_desc, values, nulls); /* make the tuple into a datum */ result = HeapTupleGetDatum(tuple); /* clean up (this is not really necessary) */ pfree(values); pfree(nulls); SRF_RETURN_NEXT(funcctx, result); } else /* do when there is no more left */ { SRF_RETURN_DONE(funcctx); } }
static int compute_driving_distance(char* sql, int source_vertex_id, float8 distance, bool directed, bool has_reverse_cost, path_element_t **path, int *path_count) { int SPIcode; void *SPIplan; Portal SPIportal; bool moredata = TRUE; int ntuples; edge_t *edges = NULL; int total_tuples = 0; edge_columns_t edge_columns = {.id= -1, .source= -1, .target= -1, .cost= -1, .reverse_cost= -1}; int v_max_id=0; int v_min_id=INT_MAX; char *err_msg; int ret = -1; int s_count = 0; register int z; DBG("start driving_distance\n"); SPIcode = SPI_connect(); if (SPIcode != SPI_OK_CONNECT) { elog(ERROR, "driving_distance: couldn't open a connection to SPI"); return -1; } SPIplan = SPI_prepare(sql, 0, NULL); if (SPIplan == NULL) { elog(ERROR, "driving_distance: couldn't create query plan via SPI"); return -1; } if ((SPIportal = SPI_cursor_open(NULL, SPIplan, NULL, NULL, true)) == NULL) { elog(ERROR, "driving_distance: SPI_cursor_open('%s') returns NULL", sql); return -1; } while (moredata == TRUE) { SPI_cursor_fetch(SPIportal, TRUE, TUPLIMIT); if (edge_columns.id == -1) { if (fetch_edge_columns(SPI_tuptable, &edge_columns, has_reverse_cost) == -1) return finish(SPIcode, ret); } ntuples = SPI_processed; total_tuples += ntuples; if (!edges) edges = palloc(total_tuples * sizeof(edge_t)); else edges = repalloc(edges, total_tuples * sizeof(edge_t)); if (edges == NULL) { elog(ERROR, "Out of memory"); return finish(SPIcode, ret); } if (ntuples > 0) { int t; SPITupleTable *tuptable = SPI_tuptable; TupleDesc tupdesc = SPI_tuptable->tupdesc; for (t = 0; t < ntuples; t++) { HeapTuple tuple = tuptable->vals[t]; fetch_edge(&tuple, &tupdesc, &edge_columns, &edges[total_tuples - ntuples + t]); } SPI_freetuptable(tuptable); } else { moredata = FALSE; } } //defining min and max vertex id DBG("Total %i tuples", total_tuples); for(z=0; z<total_tuples; z++) { if(edges[z].source<v_min_id) v_min_id=edges[z].source; if(edges[z].source>v_max_id) v_max_id=edges[z].source; if(edges[z].target<v_min_id) v_min_id=edges[z].target; if(edges[z].target>v_max_id) v_max_id=edges[z].target; DBG("%i <-> %i", v_min_id, v_max_id); } //:::::::::::::::::::::::::::::::::::: //:: reducing vertex id (renumbering) //:::::::::::::::::::::::::::::::::::: for(z=0; z<total_tuples; z++) { //check if edges[] contains source if(edges[z].source == source_vertex_id || edges[z].target == source_vertex_id) ++s_count; edges[z].source-=v_min_id; edges[z].target-=v_min_id; DBG("%i - %i", edges[z].source, edges[z].target); } if(s_count == 0) { elog(ERROR, "Start vertex was not found."); return -1; } source_vertex_id -= v_min_id; profstop("extract", prof_extract); profstart(prof_dijkstra); DBG("Calling boost_dijkstra\n"); ret = boost_dijkstra_dist(edges, total_tuples, source_vertex_id, distance, directed, has_reverse_cost, path, path_count, &err_msg); DBG("Back from boost_dijkstra\n"); if (ret < 0) { elog(ERROR, "Error computing path: %s", err_msg); } profstop("dijkstra", prof_dijkstra); profstart(prof_store); //:::::::::::::::::::::::::::::::: //:: restoring original vertex id //:::::::::::::::::::::::::::::::: for(z=0; z<*path_count; z++) { //DBG("vetex %i\n",(*path)[z].vertex_id); (*path)[z].vertex_id+=v_min_id; } return finish(SPIcode, ret); } PG_FUNCTION_INFO_V1(driving_distance); Datum driving_distance(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; int call_cntr; int max_calls; TupleDesc tuple_desc; path_element_t *path = 0; /* stuff done only on the first call of the function */ if (SRF_IS_FIRSTCALL()) { MemoryContext oldcontext; int path_count = 0; int ret; // XXX profiling messages are not thread safe profstart(prof_total); profstart(prof_extract); /* create a function context for cross-call persistence */ funcctx = SRF_FIRSTCALL_INIT(); /* switch to memory context appropriate for multiple function calls */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); ret = compute_driving_distance(text2char(PG_GETARG_TEXT_P(0)), // sql PG_GETARG_INT32(1), // source vertex PG_GETARG_FLOAT8(2), // distance or time PG_GETARG_BOOL(3), PG_GETARG_BOOL(4), &path, &path_count); if (ret < 0) { elog(ERROR, "Error computing path"); } #ifdef DEBUG DBG("Ret is %i", ret); int i; for (i = 0; i < path_count; i++) { DBG("Step %i vertex_id %i ", i, path[i].vertex_id); DBG(" edge_id %i ", path[i].edge_id); DBG(" cost %f ", path[i].cost); } #endif /* total number of tuples to be returned */ funcctx->max_calls = path_count; funcctx->user_fctx = path; funcctx->tuple_desc = BlessTupleDesc( RelationNameGetTupleDesc("pgr_costResult")); MemoryContextSwitchTo(oldcontext); } /* stuff done on every call of the function */ funcctx = SRF_PERCALL_SETUP(); call_cntr = funcctx->call_cntr; max_calls = funcctx->max_calls; tuple_desc = funcctx->tuple_desc; path = (path_element_t*) funcctx->user_fctx; if (call_cntr < max_calls) { /* do when there is more left to send */ HeapTuple tuple; Datum result; Datum *values; char* nulls; values = palloc(4 * sizeof(Datum)); nulls = palloc(4 * sizeof(char)); values[0] = Int32GetDatum(call_cntr); nulls[0] = ' '; values[1] = Int32GetDatum(path[call_cntr].vertex_id); nulls[1] = ' '; values[2] = Int32GetDatum(path[call_cntr].edge_id); nulls[2] = ' '; values[3] = Float8GetDatum(path[call_cntr].cost); nulls[3] = ' '; tuple = heap_formtuple(tuple_desc, values, nulls); /* make the tuple into a datum */ result = HeapTupleGetDatum(tuple); /* clean up (this is not really necessary) */ pfree(values); pfree(nulls); SRF_RETURN_NEXT(funcctx, result); } else { /* do when there is no more left */ if (path) free(path); profstop("store", prof_store); profstop("total", prof_total); #ifdef PROFILE elog(NOTICE, "_________"); #endif DBG("Returning value"); SRF_RETURN_DONE(funcctx); } }
int write2sqlite(char *sqlitedb_name,char *dataset_name, char *sql_string, char *twkb_name,char *id_name,char *idx_geom,char *idx_tbl, char *idx_id, int create) { char *err_msg; int spi_conn; int proc, rc; /*Sqlite*/ sqlite3 *db; TupleDesc tupdesc; SPITupleTable *tuptable; HeapTuple tuple; int i, j; SPIPlanPtr plan; char insert_str[SQLSTRLEN]; Portal cur; void *val_p; int val_int; int64 val_int64; float8 val_float; bool null_check; char *pg_type; int tot_rows = 0; sqlite3_stmt *prepared_statement; spi_conn = SPI_connect(); if (spi_conn!=SPI_OK_CONNECT) ereport(ERROR, ( errmsg("Failed to open SPI Connection"))); /*Open the sqlite db to write to*/ rc = sqlite3_open(sqlitedb_name, &db); if (rc != SQLITE_OK) { sqlite3_close(db); ereport(ERROR, ( errmsg("Cannot open SQLite database"))); } plan = SPI_prepare(sql_string,0,NULL); //ret = SPI_exec(sql_string, 0); cur = SPI_cursor_open("our_cursor", plan,NULL,NULL,true); elog(INFO, "build sql-strings and create table if : %d",create); create_sqlite_table(&cur,db, insert_str,dataset_name,twkb_name, id_name,create); elog(INFO, "back from creating table"); elog(INFO, "inserted sql = %s",insert_str); //TODO add error handling sqlite3_prepare_v2(db,insert_str,strlen(insert_str), &prepared_statement,NULL); do { sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &err_msg); SPI_cursor_fetch(cur, true,10000); proc = SPI_processed; tot_rows += proc; // if (ret > 0 && SPI_tuptable != NULL) // { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; for (j = 0; j < proc; j++) { tuple = tuptable->vals[j]; for (i = 1; i <= tupdesc->natts; i++) { pg_type = SPI_gettype(tupdesc, i); if(strcmp(pg_type, "bool")==0) { val_int = (bool) (DatumGetBool(SPI_getbinval(tuple,tupdesc,i, &null_check)) ? 1:0); if(null_check) sqlite3_bind_null(prepared_statement, i); else sqlite3_bind_int(prepared_statement, i,(int) val_int); } if(strcmp(pg_type, "int2")==0) { val_int = (int) DatumGetInt16(SPI_getbinval(tuple,tupdesc,i, &null_check)); //TODO add error handling if(null_check) sqlite3_bind_null(prepared_statement, i); else sqlite3_bind_int(prepared_statement, i,val_int); } else if(strcmp(pg_type, "int4")==0) { val_int = (int) DatumGetInt32(SPI_getbinval(tuple,tupdesc,i, &null_check)); //TODO add error handling if(null_check) sqlite3_bind_null(prepared_statement, i); else sqlite3_bind_int(prepared_statement, i,val_int); } else if(strcmp(pg_type, "int8")==0) { val_int64 = (int64) DatumGetInt64(SPI_getbinval(tuple,tupdesc,i, &null_check)); //TODO add error handling if(null_check) sqlite3_bind_null(prepared_statement, i); else sqlite3_bind_int64(prepared_statement, i,val_int64); } else if(strcmp(pg_type, "float4")==0) { val_float = (float8) DatumGetFloat4(SPI_getbinval(tuple,tupdesc,i, &null_check)); //TODO add error handling if(null_check) sqlite3_bind_null(prepared_statement, i); else sqlite3_bind_double(prepared_statement, i,val_float); } else if(strcmp(pg_type, "float8")==0) { val_float = (float8) DatumGetFloat8(SPI_getbinval(tuple,tupdesc,i, &null_check)); //TODO add error handling if(null_check) sqlite3_bind_null(prepared_statement, i); else sqlite3_bind_double(prepared_statement, i,val_float); } else if(strcmp(pg_type, "bytea")==0) { val_p = (void*) PG_DETOAST_DATUM(SPI_getbinval(tuple,tupdesc,i, &null_check)); //TODO add error handling if(null_check) sqlite3_bind_null(prepared_statement, i); else sqlite3_bind_blob(prepared_statement, i, (const void*) VARDATA_ANY(val_p), VARSIZE_ANY(val_p)-VARHDRSZ, SQLITE_TRANSIENT); } else { // val = (void*) PG_DETOAST_DATUM(SPI_getbinval(tuple,tupdesc,i, &null_check)); //TODO add error handling sqlite3_bind_text(prepared_statement,i,SPI_getvalue(tuple, tupdesc, i),-1,NULL); } } sqlite3_step(prepared_statement); sqlite3_clear_bindings(prepared_statement); sqlite3_reset(prepared_statement); } sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &err_msg); elog(INFO, "inserted %d rows in table",tot_rows); } while (proc > 0); if(dataset_name && idx_geom && idx_id) create_spatial_index(db,dataset_name,idx_tbl, idx_geom, idx_id, sql_string,create); else elog(INFO, "Finnishing without spatial index"); SPI_finish(); sqlite3_close(db); return 0; }
static TSVectorStat * ts_stat_sql(MemoryContext persistentContext, text *txt, text *ws) { char *query = text_to_cstring(txt); int i; TSVectorStat *stat; bool isnull; Portal portal; SPIPlanPtr plan; if ((plan = SPI_prepare(query, 0, NULL)) == NULL) /* internal error */ elog(ERROR, "SPI_prepare(\"%s\") failed", query); if ((portal = SPI_cursor_open(NULL, plan, NULL, NULL, true)) == NULL) /* internal error */ elog(ERROR, "SPI_cursor_open(\"%s\") failed", query); SPI_cursor_fetch(portal, true, 100); if (SPI_tuptable == NULL || SPI_tuptable->tupdesc->natts != 1 || !IsBinaryCoercible(SPI_gettypeid(SPI_tuptable->tupdesc, 1), TSVECTOROID)) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("ts_stat query must return one tsvector column"))); stat = MemoryContextAllocZero(persistentContext, sizeof(TSVectorStat)); stat->maxdepth = 1; if (ws) { char *buf; buf = VARDATA(ws); while (buf - VARDATA(ws) < VARSIZE(ws) - VARHDRSZ) { if (pg_mblen(buf) == 1) { switch (*buf) { case 'A': case 'a': stat->weight |= 1 << 3; break; case 'B': case 'b': stat->weight |= 1 << 2; break; case 'C': case 'c': stat->weight |= 1 << 1; break; case 'D': case 'd': stat->weight |= 1; break; default: stat->weight |= 0; } } buf += pg_mblen(buf); } } while (SPI_processed > 0) { for (i = 0; i < SPI_processed; i++) { Datum data = SPI_getbinval(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 1, &isnull); if (!isnull) stat = ts_accum(persistentContext, stat, data); } SPI_freetuptable(SPI_tuptable); SPI_cursor_fetch(portal, true, 100); } SPI_freetuptable(SPI_tuptable); SPI_cursor_close(portal); SPI_freeplan(plan); pfree(query); return stat; }
static int load_lex(LEXICON *lex, char *tab) { int ret; SPIPlanPtr SPIplan; Portal SPIportal; bool moredata = TRUE; #ifdef DEBUG struct timeval t1, t2; double elapsed; #endif char *sql; int ntuples; int total_tuples = 0; lex_columns_t lex_columns = {seq: -1, word: -1, stdword: -1, token: -1}; int seq; char *word; char *stdword; int token; DBG("start load_lex\n"); SET_TIME(t1); if (!tab || !strlen(tab)) { elog(NOTICE, "load_lex: rules table is not usable"); return -1; } if (!tableNameOk(tab)) { elog(NOTICE, "load_lex: lex and gaz table names may only be alphanum and '.\"_' characters (%s)", tab); return -1; } sql = SPI_palloc(strlen(tab)+65); strcpy(sql, "select seq, word, stdword, token from "); strcat(sql, tab); strcat(sql, " order by id "); /* get the sql for the lexicon records and prepare the query */ SPIplan = SPI_prepare(sql, 0, NULL); if (SPIplan == NULL) { elog(NOTICE, "load_lex: couldn't create query plan for the lex data via SPI (%s)", sql); return -1; } /* get the sql for the lexicon records and prepare the query */ SPIplan = SPI_prepare(sql, 0, NULL); if (SPIplan == NULL) { elog(NOTICE, "load_lex: couldn't create query plan for the lexicon data via SPI"); return -1; } if ((SPIportal = SPI_cursor_open(NULL, SPIplan, NULL, NULL, true)) == NULL) { elog(NOTICE, "load_lex: SPI_cursor_open('%s') returns NULL", sql); return -1; } while (moredata == TRUE) { //DBG("calling SPI_cursor_fetch"); SPI_cursor_fetch(SPIportal, TRUE, TUPLIMIT); if (SPI_tuptable == NULL) { elog(NOTICE, "load_lex: SPI_tuptable is NULL"); return -1; } if (lex_columns.seq == -1) { ret = fetch_lex_columns(SPI_tuptable, &lex_columns); if (ret) return ret; } ntuples = SPI_processed; //DBG("Reading edges: %i - %i", total_tuples, total_tuples+ntuples); total_tuples += ntuples; if (ntuples > 0) { int t; Datum binval; bool isnull; SPITupleTable *tuptable = SPI_tuptable; TupleDesc tupdesc = SPI_tuptable->tupdesc; for (t = 0; t < ntuples; t++) { //if (t%100 == 0) { DBG(" t: %i", t); } HeapTuple tuple = tuptable->vals[t]; GET_INT_FROM_TUPLE(seq,lex_columns.seq,"load_lex: seq contains a null value"); GET_TEXT_FROM_TUPLE(word,lex_columns.word); GET_TEXT_FROM_TUPLE(stdword,lex_columns.stdword); GET_INT_FROM_TUPLE(token,lex_columns.token,"load_lex: token contains a null value"); lex_add_entry(lex, seq, word, stdword, token); } //DBG("calling SPI_freetuptable"); SPI_freetuptable(tuptable); //DBG("back from SPI_freetuptable"); } else moredata = FALSE; } SET_TIME(t2); ELAPSED_T(t1, t2); DBG("Time to read %i lexicon records: %.1f ms.", total_tuples, elapsed); return 0; } static int fetch_rules_columns(SPITupleTable *tuptable, rules_columns_t *rules_cols) { int err = 0; FETCH_COL(rules_cols,rule,"rule"); if (err) { elog(NOTICE, "rules queries must return column 'rule'"); return -1; } CHECK_TYP(rules_cols,rule,TEXTOID); if (err) { elog(NOTICE, "rules column type must be: 'rule' text"); return -1; } return 0; }
static int load_rules(RULES *rules, char *tab) { int ret; SPIPlanPtr SPIplan; Portal SPIportal; bool moredata = TRUE; #ifdef DEBUG struct timeval t1, t2; double elapsed; #endif char *sql; int rule_arr[MAX_RULE_LENGTH]; int ntuples; int total_tuples = 0; rules_columns_t rules_columns = {rule: -1}; char *rule; DBG("start load_rules\n"); SET_TIME(t1); if (!tab || !strlen(tab)) { elog(NOTICE, "load_rules: rules table is not usable"); return -1; } if (!tableNameOk(tab)) { elog(NOTICE, "load_rules: rules table name may only be alphanum and '.\"_' characters (%s)", tab); return -1; } sql = SPI_palloc(strlen(tab)+35); strcpy(sql, "select rule from "); strcat(sql, tab); strcat(sql, " order by id "); /* get the sql for the lexicon records and prepare the query */ SPIplan = SPI_prepare(sql, 0, NULL); if (SPIplan == NULL) { elog(NOTICE, "load_rules: couldn't create query plan for the rule data via SPI (%s)", sql); return -1; } if ((SPIportal = SPI_cursor_open(NULL, SPIplan, NULL, NULL, true)) == NULL) { elog(NOTICE, "load_rules: SPI_cursor_open('%s') returns NULL", sql); return -1; } while (moredata == TRUE) { //DBG("calling SPI_cursor_fetch"); SPI_cursor_fetch(SPIportal, TRUE, TUPLIMIT); if (SPI_tuptable == NULL) { elog(NOTICE, "load_rules: SPI_tuptable is NULL"); return -1; } if (rules_columns.rule == -1) { ret = fetch_rules_columns(SPI_tuptable, &rules_columns); if (ret) return ret; } ntuples = SPI_processed; //DBG("Reading edges: %i - %i", total_tuples, total_tuples+ntuples); if (ntuples > 0) { int t; SPITupleTable *tuptable = SPI_tuptable; TupleDesc tupdesc = SPI_tuptable->tupdesc; for (t = 0; t < ntuples; t++) { int nr; //if (t%100 == 0) { DBG(" t: %i", t); } HeapTuple tuple = tuptable->vals[t]; GET_TEXT_FROM_TUPLE(rule,rules_columns.rule); nr = parse_rule(rule, rule_arr); if (nr == -1) { elog(NOTICE, "load_roles: rule exceeds 128 terms"); return -1; } ret = rules_add_rule(rules, nr, rule_arr); if (ret != 0) { elog(NOTICE,"load_roles: failed to add rule %d (%d): %s", total_tuples+t+1, ret, rule); return -1; } } //DBG("calling SPI_freetuptable"); SPI_freetuptable(tuptable); //DBG("back from SPI_freetuptable"); } else moredata = FALSE; total_tuples += ntuples; } ret = rules_ready(rules); if (ret != 0) { elog(NOTICE, "load_roles: failed to ready the rules: err: %d", ret); return -1; } SET_TIME(t2); ELAPSED_T(t1, t2); DBG("Time to read %i rule records: %.1f ms.", total_tuples, elapsed); return 0; }
static int compute_alpha_shape(char* sql, vertex_t **res, int *res_count) { int SPIcode; void *SPIplan; Portal SPIportal; bool moredata = TRUE; int ntuples; vertex_t *vertices = NULL; int total_tuples = 0; vertex_columns_t vertex_columns = {.id= -1, .x= -1, .y= -1}; char *err_msg; int ret = -1; DBG("start alpha_shape\n"); SPIcode = SPI_connect(); if (SPIcode != SPI_OK_CONNECT) { elog(ERROR, "alpha_shape: couldn't open a connection to SPI"); return -1; } SPIplan = SPI_prepare(sql, 0, NULL); if (SPIplan == NULL) { elog(ERROR, "alpha_shape: couldn't create query plan via SPI"); return -1; } if ((SPIportal = SPI_cursor_open(NULL, SPIplan, NULL, NULL, true)) == NULL) { elog(ERROR, "alpha_shape: SPI_cursor_open('%s') returns NULL", sql); return -1; } while (moredata == TRUE) { SPI_cursor_fetch(SPIportal, TRUE, TUPLIMIT); if (vertex_columns.id == -1) { if (fetch_vertices_columns(SPI_tuptable, &vertex_columns) == -1) return finish(SPIcode, ret); } ntuples = SPI_processed; total_tuples += ntuples; if (!vertices) vertices = palloc(total_tuples * sizeof(vertex_t)); else vertices = repalloc(vertices, total_tuples * sizeof(vertex_t)); if (vertices == NULL) { elog(ERROR, "Out of memory"); return finish(SPIcode, ret); } if (ntuples > 0) { int t; SPITupleTable *tuptable = SPI_tuptable; TupleDesc tupdesc = SPI_tuptable->tupdesc; for (t = 0; t < ntuples; t++) { HeapTuple tuple = tuptable->vals[t]; fetch_vertex(&tuple, &tupdesc, &vertex_columns, &vertices[total_tuples - ntuples + t]); } SPI_freetuptable(tuptable); } else { moredata = FALSE; } } // if (total_tuples < 2) //this was the buggy code of the pgrouting project. // TODO: report this as a bug to the pgrouting project // the CGAL alpha-shape function crashes if called with less than three points!!! if (total_tuples == 0) { elog(ERROR, "Distance is too short. no vertex for alpha shape calculation. alpha shape calculation needs at least 3 vertices."); } if (total_tuples == 1) { elog(ERROR, "Distance is too short. only 1 vertex for alpha shape calculation. alpha shape calculation needs at least 3 vertices."); } if (total_tuples == 2) { elog(ERROR, "Distance is too short. only 2 vertices for alpha shape calculation. alpha shape calculation needs at least 3 vertices."); } if (total_tuples < 3) { // elog(ERROR, "Distance is too short ...."); return finish(SPIcode, ret); } DBG("Calling CGAL alpha-shape\n"); profstop("extract", prof_extract); profstart(prof_alpha); ret = alpha_shape(vertices, total_tuples, res, res_count, &err_msg); profstop("alpha", prof_alpha); profstart(prof_store); if (ret < 0) { //elog(ERROR, "Error computing shape: %s", err_msg); ereport(ERROR, (errcode(ERRCODE_E_R_E_CONTAINING_SQL_NOT_PERMITTED), errmsg("Error computing shape: %s", err_msg))); } return finish(SPIcode, ret); } PG_FUNCTION_INFO_V1(alphashape); Datum alphashape(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; int call_cntr; int max_calls; TupleDesc tuple_desc; vertex_t *res = 0; /* stuff done only on the first call of the function */ if (SRF_IS_FIRSTCALL()) { MemoryContext oldcontext; int res_count; int ret; // XXX profiling messages are not thread safe profstart(prof_total); profstart(prof_extract); /* create a function context for cross-call persistence */ funcctx = SRF_FIRSTCALL_INIT(); /* switch to memory context appropriate for multiple function calls */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); ret = compute_alpha_shape(text2char(PG_GETARG_TEXT_P(0)), &res, &res_count); /* total number of tuples to be returned */ DBG("Conting tuples number\n"); funcctx->max_calls = res_count; funcctx->user_fctx = res; DBG("Total count %i", res_count); if (get_call_result_type(fcinfo, NULL, &tuple_desc) != TYPEFUNC_COMPOSITE) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("function returning record called in context " "that cannot accept type record"))); funcctx->tuple_desc = BlessTupleDesc(tuple_desc); MemoryContextSwitchTo(oldcontext); } /* stuff done on every call of the function */ DBG("Strange stuff doing\n"); funcctx = SRF_PERCALL_SETUP(); call_cntr = funcctx->call_cntr; max_calls = funcctx->max_calls; tuple_desc = funcctx->tuple_desc; res = (vertex_t*) funcctx->user_fctx; DBG("Trying to allocate some memory\n"); if (call_cntr < max_calls) /* do when there is more left to send */ { HeapTuple tuple; Datum result; Datum *values; char* nulls; /* This will work for some compilers. If it crashes with segfault, try to change the following block with this one values = palloc(3 * sizeof(Datum)); nulls = palloc(3 * sizeof(char)); values[0] = call_cntr; nulls[0] = ' '; values[1] = Float8GetDatum(res[call_cntr].x); nulls[1] = ' '; values[2] = Float8GetDatum(res[call_cntr].y); nulls[2] = ' '; */ values = palloc(2 * sizeof(Datum)); nulls = palloc(2 * sizeof(char)); values[0] = Float8GetDatum(res[call_cntr].x); nulls[0] = ' '; values[1] = Float8GetDatum(res[call_cntr].y); nulls[1] = ' '; DBG("Heap making\n"); tuple = heap_formtuple(tuple_desc, values, nulls); DBG("Datum making\n"); /* make the tuple into a datum */ result = HeapTupleGetDatum(tuple); DBG("Trying to free some memory\n"); /* clean up (this is not really necessary) */ pfree(values); pfree(nulls); SRF_RETURN_NEXT(funcctx, result); } else /* do when there is no more left */ { if (res) free(res); profstop("store", prof_store); profstop("total", prof_total); #ifdef PROFILE elog(NOTICE, "_________"); #endif SRF_RETURN_DONE(funcctx); } }
static int compute_shortest_path_shooting_star(char* sql, int source_edge_id, int target_edge_id, bool directed, bool has_reverse_cost, path_element_t **path, int *path_count) { int SPIcode; void *SPIplan; Portal SPIportal; bool moredata = TRUE; int ntuples; edge_shooting_star_t *edges = NULL; int total_tuples = 0; // int v_max_id=0; // int v_min_id=INT_MAX; int e_max_id=0; int e_min_id=INT_MAX; edge_shooting_star_columns_t edge_columns = {id: -1, source: -1, target: -1, cost: -1, reverse_cost: -1, s_x: -1, s_y: -1, t_x: -1, t_y: -1, to_cost: -1, rule: -1}; char *err_msg; int ret = -1; register int z, t; int s_count=0; int t_count=0; DBG("start shortest_path_shooting_star\n"); SPIcode = SPI_connect(); if (SPIcode != SPI_OK_CONNECT) { elog(ERROR, "shortest_path_shooting_star: couldn't open a connection to SPI"); return -1; } SPIplan = SPI_prepare(sql, 0, NULL); if (SPIplan == NULL) { elog(ERROR, "shortest_path_shooting_star: couldn't create query plan via SPI"); return -1; } if ((SPIportal = SPI_cursor_open(NULL, SPIplan, NULL, NULL, true)) == NULL) { elog(ERROR, "shortest_path_shooting_star: SPI_cursor_open('%s') returns NULL", sql); return -1; } while (moredata == TRUE) { SPI_cursor_fetch(SPIportal, TRUE, TUPLIMIT); if (edge_columns.id == -1) { if (fetch_edge_shooting_star_columns(SPI_tuptable, &edge_columns, has_reverse_cost) == -1) return finish(SPIcode, ret); } //DBG("***%i***", ret); ntuples = SPI_processed; total_tuples += ntuples; if (!edges) edges = palloc(total_tuples * sizeof(edge_shooting_star_t)); else edges = repalloc(edges, total_tuples * sizeof(edge_shooting_star_t)); if (edges == NULL) { elog(ERROR, "Out of memory"); return finish(SPIcode, ret); } if (ntuples > 0) { int t; SPITupleTable *tuptable = SPI_tuptable; TupleDesc tupdesc = SPI_tuptable->tupdesc; for (t = 0; t < ntuples; t++) { HeapTuple tuple = tuptable->vals[t]; fetch_edge_shooting_star(&tuple, &tupdesc, &edge_columns, &edges[total_tuples - ntuples + t]); } SPI_freetuptable(tuptable); } else { moredata = FALSE; } } DBG("Total %i tuples", total_tuples); for(z=0; z<total_tuples; z++) { if(edges[z].id<e_min_id) e_min_id=edges[z].id; if(edges[z].id>e_max_id) e_max_id=edges[z].id; } DBG("E : %i <-> %i", e_min_id, e_max_id); for(z=0; z<total_tuples; ++z) { //check if edges[] contains source and target if(edges[z].id == source_edge_id) ++s_count; if(edges[z].id == target_edge_id) ++t_count; //edges[z].source-=v_min_id; //edges[z].target-=v_min_id; } DBG("Total %i tuples", total_tuples); if(s_count == 0) { elog(ERROR, "Start edge was not found."); return -1; } if(t_count == 0) { elog(ERROR, "Target edge was not found."); return -1; } DBG("Total %i tuples", total_tuples); DBG("Calling boost_shooting_star <%i>\n", total_tuples); //time_t stime = time(NULL); ret = boost_shooting_star(edges, total_tuples, source_edge_id, target_edge_id, directed, has_reverse_cost, path, path_count, &err_msg, e_max_id); //time_t etime = time(NULL); //DBG("Path was calculated in %f seconds. \n", difftime(etime, stime)); DBG("SIZE %i\n",*path_count); DBG("ret = %i\n",ret); if (ret < 0) { ereport(ERROR, (errcode(ERRCODE_E_R_E_CONTAINING_SQL_NOT_PERMITTED), errmsg("Error computing path: %s", err_msg))); } return finish(SPIcode, ret); } PG_FUNCTION_INFO_V1(shortest_path_shooting_star); Datum shortest_path_shooting_star(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; int call_cntr; int max_calls; TupleDesc tuple_desc; path_element_t *path = 0; /* stuff done only on the first call of the function */ if (SRF_IS_FIRSTCALL()) { MemoryContext oldcontext; int path_count = 0; int ret; /* create a function context for cross-call persistence */ funcctx = SRF_FIRSTCALL_INIT(); /* switch to memory context appropriate for multiple function calls */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); ret = compute_shortest_path_shooting_star(text2char(PG_GETARG_TEXT_P(0)), PG_GETARG_INT32(1), PG_GETARG_INT32(2), PG_GETARG_BOOL(3), PG_GETARG_BOOL(4), &path, &path_count); #ifdef DEBUG DBG("Ret is %i", ret); if (ret >= 0) { int i; for (i = 0; i < path_count; i++) { DBG("Step # %i vertex_id %i ", i, path[i].vertex_id); DBG(" edge_id %i ", path[i].edge_id); DBG(" cost %f ", path[i].cost); } } #endif /* total number of tuples to be returned */ DBG("Conting tuples number\n"); funcctx->max_calls = path_count; funcctx->user_fctx = path; DBG("Path count %i", path_count); funcctx->tuple_desc = BlessTupleDesc(RelationNameGetTupleDesc("pgr_costResult")); MemoryContextSwitchTo(oldcontext); } /* stuff done on every call of the function */ funcctx = SRF_PERCALL_SETUP(); call_cntr = funcctx->call_cntr; max_calls = funcctx->max_calls; tuple_desc = funcctx->tuple_desc; path = (path_element_t*) funcctx->user_fctx; DBG("Trying to allocate some memory\n"); if (call_cntr < max_calls) /* do when there is more left to send */ { HeapTuple tuple; Datum result; Datum *values; char* nulls; values = palloc(4 * sizeof(Datum)); nulls = palloc(4 * sizeof(char)); values[0] = Int32GetDatum(call_cntr); nulls[0] = ' '; values[1] = Int32GetDatum(path[call_cntr].vertex_id); nulls[1] = ' '; values[2] = Int32GetDatum(path[call_cntr].edge_id); nulls[2] = ' '; values[3] = Float8GetDatum(path[call_cntr].cost); nulls[3] = ' '; tuple = heap_formtuple(tuple_desc, values, nulls); /* make the tuple into a datum */ result = HeapTupleGetDatum(tuple); /* clean up (this is not really necessary) */ pfree(values); pfree(nulls); SRF_RETURN_NEXT(funcctx, result); } else /* do when there is no more left */ { if (path) free(path); SRF_RETURN_DONE(funcctx); } }
int compute_kshortest_path(char* sql, int start_vertex, int end_vertex, int no_paths, bool has_reverse_cost, ksp_path_element_t **ksp_path, int *path_count) { int SPIcode; void *SPIplan; Portal SPIportal; bool moredata = TRUE; int ntuples; ksp_edge_t *edges = NULL; int total_tuples = 0; #ifndef _MSC_VER ksp_edge_columns_t edge_columns = {.id= -1, .source= -1, .target= -1, .cost= -1, .reverse_cost= -1}; #else // _MSC_VER ksp_edge_columns_t edge_columns = {-1, -1, -1, -1, -1}; #endif // _MSC_VER int v_max_id=0; int v_min_id=INT_MAX; int s_count = 0; int t_count = 0; char *err_msg=(char *)""; int ret = -1; register int z; DBG("start kshortest_path %s\n",sql); SPIcode = SPI_connect(); if (SPIcode != SPI_OK_CONNECT) { elog(ERROR, "kshortest_path: couldn't open a connection to SPI"); return -1; } SPIplan = SPI_prepare(sql, 0, NULL); if (SPIplan == NULL) { elog(ERROR, "kshortest_path: couldn't create query plan via SPI"); return -1; } if ((SPIportal = SPI_cursor_open(NULL, SPIplan, NULL, NULL, true)) == NULL) { elog(ERROR, "shortest_path: SPI_cursor_open('%s') returns NULL", sql); return -1; } while (moredata == TRUE) { SPI_cursor_fetch(SPIportal, TRUE, TUPLIMIT); if (edge_columns.id == -1) { if (ksp_fetch_edge_columns(SPI_tuptable, &edge_columns, has_reverse_cost) == -1) return ksp_finish(SPIcode, ret); } ntuples = SPI_processed; total_tuples += ntuples; if (!edges) edges = (ksp_edge_t *)palloc(total_tuples * sizeof(ksp_edge_t)); else edges = (ksp_edge_t *)repalloc(edges, total_tuples * sizeof(ksp_edge_t)); if (edges == NULL) { elog(ERROR, "Out of memory"); return ksp_finish(SPIcode, ret); } if (ntuples > 0) { int t; SPITupleTable *tuptable = SPI_tuptable; TupleDesc tupdesc = SPI_tuptable->tupdesc; for (t = 0; t < ntuples; t++) { HeapTuple tuple = tuptable->vals[t]; ksp_fetch_edge(&tuple, &tupdesc, &edge_columns, &edges[total_tuples - ntuples + t]); } SPI_freetuptable(tuptable); } else { moredata = FALSE; } } DBG("Total %i tuples", total_tuples); for(z=0; z<total_tuples; z++) { //check if edges[] contains source and target if(edges[z].source == start_vertex || edges[z].target == start_vertex) ++s_count; if(edges[z].source == end_vertex || edges[z].target == end_vertex) ++t_count; DBG("%i - %i", edges[z].source, edges[z].target); } DBG("Total %i tuples", total_tuples); if(s_count == 0) { elog(ERROR, "Start vertex was not found."); return -1; } if(t_count == 0) { elog(ERROR, "Target vertex was not found."); return -1; } DBG("Calling doKpaths\n"); DBG("SIZE %i\n",total_tuples); ret = doKpaths(edges, total_tuples, start_vertex, end_vertex, no_paths, has_reverse_cost, ksp_path, path_count, &err_msg); DBG("SIZE %i\n",*path_count); DBG("ret = %i\n", ret); if (ret < 0) { //elog(ERROR, "Error computing path: %s", err_msg); ereport(ERROR, (errcode(ERRCODE_E_R_E_CONTAINING_SQL_NOT_PERMITTED), errmsg("Error computing path: %s", err_msg))); } return ksp_finish(SPIcode, ret); } ksp_path_element_t * get_ksp_memory(int size,ksp_path_element_t *path){ if(path ==0 ){ path=malloc(size * sizeof(ksp_path_element_t)); } else { path=realloc(path,size * sizeof(ksp_path_element_t)); } return path; }
static int compute_shortest_path(char* sql, int start_vertex, int end_vertex, bool directed, bool has_reverse_cost, path_element_t **path, int *path_count) { int SPIcode; void *SPIplan; Portal SPIportal; bool moredata = TRUE; int ntuples; edge_t *edges = NULL; int total_tuples = 0; edge_columns_t edge_columns = {id: -1, source: -1, target: -1, cost: -1, reverse_cost: -1}; int v_max_id=0; int v_min_id=INT_MAX; int s_count = 0; int t_count = 0; char *err_msg; int ret = -1; register int z; DBG("start shortest_path\n"); SPIcode = SPI_connect(); if (SPIcode != SPI_OK_CONNECT) { elog(ERROR, "shortest_path: couldn't open a connection to SPI"); return -1; } SPIplan = SPI_prepare(sql, 0, NULL); if (SPIplan == NULL) { elog(ERROR, "shortest_path: couldn't create query plan via SPI"); return -1; } if ((SPIportal = SPI_cursor_open(NULL, SPIplan, NULL, NULL, true)) == NULL) { elog(ERROR, "shortest_path: SPI_cursor_open('%s') returns NULL", sql); return -1; } while (moredata == TRUE) { SPI_cursor_fetch(SPIportal, TRUE, TUPLIMIT); if (edge_columns.id == -1) { if (fetch_edge_columns(SPI_tuptable, &edge_columns, has_reverse_cost) == -1) return finish(SPIcode, ret); } ntuples = SPI_processed; total_tuples += ntuples; if (!edges) edges = palloc(total_tuples * sizeof(edge_t)); else edges = repalloc(edges, total_tuples * sizeof(edge_t)); if (edges == NULL) { elog(ERROR, "Out of memory"); return finish(SPIcode, ret); } if (ntuples > 0) { int t; SPITupleTable *tuptable = SPI_tuptable; TupleDesc tupdesc = SPI_tuptable->tupdesc; for (t = 0; t < ntuples; t++) { HeapTuple tuple = tuptable->vals[t]; fetch_edge(&tuple, &tupdesc, &edge_columns, &edges[total_tuples - ntuples + t]); } SPI_freetuptable(tuptable); } else { moredata = FALSE; } } //defining min and max vertex id DBG("Total %i tuples", total_tuples); for(z=0; z<total_tuples; z++) { if(edges[z].source<v_min_id) v_min_id=edges[z].source; if(edges[z].source>v_max_id) v_max_id=edges[z].source; if(edges[z].target<v_min_id) v_min_id=edges[z].target; if(edges[z].target>v_max_id) v_max_id=edges[z].target; DBG("%i <-> %i", v_min_id, v_max_id); } //:::::::::::::::::::::::::::::::::::: //:: reducing vertex id (renumbering) //:::::::::::::::::::::::::::::::::::: for(z=0; z<total_tuples; z++) { //check if edges[] contains source and target if(edges[z].source == start_vertex || edges[z].target == start_vertex) ++s_count; if(edges[z].source == end_vertex || edges[z].target == end_vertex) ++t_count; edges[z].source-=v_min_id; edges[z].target-=v_min_id; DBG("%i - %i", edges[z].source, edges[z].target); } DBG("Total %i tuples", total_tuples); if(s_count == 0) { elog(ERROR, "Source vertex: %d was not found as vertex of any of the input edges.", start_vertex); return -1; } if(t_count == 0) { elog(ERROR, "Target vertex: %d was not found as vertex of any of the input edges.", end_vertex); return -1; } DBG("Calling boost_dijkstra\n"); start_vertex -= v_min_id; end_vertex -= v_min_id; ret = boost_dijkstra(edges, total_tuples, start_vertex, end_vertex, directed, has_reverse_cost, path, path_count, &err_msg); DBG("SIZE %i\n",*path_count); //:::::::::::::::::::::::::::::::: //:: restoring original vertex id //:::::::::::::::::::::::::::::::: for(z=0;z<*path_count;z++) { //DBG("vetex %i\n",(*path)[z].vertex_id); (*path)[z].vertex_id+=v_min_id; } DBG("ret = %i\n", ret); DBG("*path_count = %i\n", *path_count); DBG("ret = %i\n", ret); if (ret < 0) { //elog(ERROR, "Error computing path: %s", err_msg); ereport(ERROR, (errcode(ERRCODE_E_R_E_CONTAINING_SQL_NOT_PERMITTED), errmsg("Error computing path: %s", err_msg))); } if (edges) { /* clean up input egdes */ pfree (edges); } return finish(SPIcode, ret); } PG_FUNCTION_INFO_V1(shortest_path); Datum shortest_path(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; int call_cntr; int max_calls; TupleDesc tuple_desc; path_element_t *path = NULL; char *sql = NULL; /* stuff done only on the first call of the function */ if (SRF_IS_FIRSTCALL()) { MemoryContext oldcontext; int path_count = 0; int ret; /* create a function context for cross-call persistence */ funcctx = SRF_FIRSTCALL_INIT(); /* switch to memory context appropriate for multiple function calls */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); /* edge sql query */ sql = text2char(PG_GETARG_TEXT_P(0)); ret = compute_shortest_path(sql, PG_GETARG_INT32(1), PG_GETARG_INT32(2), PG_GETARG_BOOL(3), PG_GETARG_BOOL(4), &path, &path_count); /* clean up sql query string */ if (sql) { pfree (sql); } #ifdef DEBUG DBG("Ret is %i", ret); if (ret >= 0) { int i; for (i = 0; i < path_count; i++) { DBG("Step %i vertex_id %i ", i, path[i].vertex_id); DBG(" edge_id %i ", path[i].edge_id); DBG(" cost %f ", path[i].cost); } } #endif /* total number of tuples to be returned */ funcctx->max_calls = path_count; funcctx->user_fctx = path; funcctx->tuple_desc = BlessTupleDesc(RelationNameGetTupleDesc("path_result")); MemoryContextSwitchTo(oldcontext); } /* stuff done on every call of the function */ funcctx = SRF_PERCALL_SETUP(); call_cntr = funcctx->call_cntr; max_calls = funcctx->max_calls; tuple_desc = funcctx->tuple_desc; path = (path_element_t*) funcctx->user_fctx; if (call_cntr < max_calls) /* do when there is more left to send */ { HeapTuple tuple; Datum result; Datum *values; char* nulls; /* This will work for some compilers. If it crashes with segfault, try to change the following block with this one values = palloc(4 * sizeof(Datum)); nulls = palloc(4 * sizeof(char)); values[0] = call_cntr; nulls[0] = ' '; values[1] = Int32GetDatum(path[call_cntr].vertex_id); nulls[1] = ' '; values[2] = Int32GetDatum(path[call_cntr].edge_id); nulls[2] = ' '; values[3] = Float8GetDatum(path[call_cntr].cost); nulls[3] = ' '; */ values = palloc(3 * sizeof(Datum)); nulls = palloc(3 * sizeof(char)); values[0] = Int32GetDatum(path[call_cntr].vertex_id); nulls[0] = ' '; values[1] = Int32GetDatum(path[call_cntr].edge_id); nulls[1] = ' '; values[2] = Float8GetDatum(path[call_cntr].cost); nulls[2] = ' '; tuple = heap_formtuple(tuple_desc, values, nulls); /* make the tuple into a datum */ result = HeapTupleGetDatum(tuple); /* clean up (this is not really necessary) */ pfree(values); pfree(nulls); SRF_RETURN_NEXT(funcctx, result); } else /* do when there is no more left */ { if (path) { /* clean up returned edge paths must be a free because it's malloc'd */ free (path); path = NULL; } SRF_RETURN_DONE(funcctx); } }
static PyObject * PLy_cursor_plan(PyObject *ob, PyObject *args) { PLyCursorObject *cursor; volatile int nargs; int i; PLyPlanObject *plan; volatile MemoryContext oldcontext; volatile ResourceOwner oldowner; if (args) { if (!PySequence_Check(args) || PyString_Check(args) || PyUnicode_Check(args)) { PLy_exception_set(PyExc_TypeError, "plpy.cursor takes a sequence as its second argument"); return NULL; } nargs = PySequence_Length(args); } else nargs = 0; plan = (PLyPlanObject *) ob; if (nargs != plan->nargs) { char *sv; PyObject *so = PyObject_Str(args); if (!so) PLy_elog(ERROR, "could not execute plan"); sv = PyString_AsString(so); PLy_exception_set_plural(PyExc_TypeError, "Expected sequence of %d argument, got %d: %s", "Expected sequence of %d arguments, got %d: %s", plan->nargs, plan->nargs, nargs, sv); Py_DECREF(so); return NULL; } if ((cursor = PyObject_New(PLyCursorObject, &PLy_CursorType)) == NULL) return NULL; cursor->portalname = NULL; cursor->closed = false; cursor->mcxt = AllocSetContextCreate(TopMemoryContext, "PL/Python cursor context", ALLOCSET_DEFAULT_MINSIZE, ALLOCSET_DEFAULT_INITSIZE, ALLOCSET_DEFAULT_MAXSIZE); PLy_typeinfo_init(&cursor->result, cursor->mcxt); oldcontext = CurrentMemoryContext; oldowner = CurrentResourceOwner; PLy_spi_subtransaction_begin(oldcontext, oldowner); PG_TRY(); { PLyExecutionContext *exec_ctx = PLy_current_execution_context(); Portal portal; char *volatile nulls; volatile int j; if (nargs > 0) nulls = palloc(nargs * sizeof(char)); else nulls = NULL; for (j = 0; j < nargs; j++) { PyObject *elem; elem = PySequence_GetItem(args, j); if (elem != Py_None) { PG_TRY(); { plan->values[j] = plan->args[j].out.d.func(&(plan->args[j].out.d), -1, elem); } PG_CATCH(); { Py_DECREF(elem); PG_RE_THROW(); } PG_END_TRY(); Py_DECREF(elem); nulls[j] = ' '; } else { Py_DECREF(elem); plan->values[j] = InputFunctionCall(&(plan->args[j].out.d.typfunc), NULL, plan->args[j].out.d.typioparam, -1); nulls[j] = 'n'; } } portal = SPI_cursor_open(NULL, plan->plan, plan->values, nulls, exec_ctx->curr_proc->fn_readonly); if (portal == NULL) elog(ERROR, "SPI_cursor_open() failed: %s", SPI_result_code_string(SPI_result)); cursor->portalname = MemoryContextStrdup(cursor->mcxt, portal->name); PLy_spi_subtransaction_commit(oldcontext, oldowner); } PG_CATCH(); { int k; /* cleanup plan->values array */ for (k = 0; k < nargs; k++) { if (!plan->args[k].out.d.typbyval && (plan->values[k] != PointerGetDatum(NULL))) { pfree(DatumGetPointer(plan->values[k])); plan->values[k] = PointerGetDatum(NULL); } } Py_DECREF(cursor); PLy_spi_subtransaction_abort(oldcontext, oldowner); return NULL; } PG_END_TRY(); for (i = 0; i < nargs; i++) { if (!plan->args[i].out.d.typbyval && (plan->values[i] != PointerGetDatum(NULL))) { pfree(DatumGetPointer(plan->values[i])); plan->values[i] = PointerGetDatum(NULL); } } Assert(cursor->portalname != NULL); return (PyObject *) cursor; }
Datum tsquery_rewrite(PG_FUNCTION_ARGS) { QUERYTYPE *query = (QUERYTYPE *) DatumGetPointer(PG_DETOAST_DATUM_COPY(PG_GETARG_DATUM(0))); text *in = PG_GETARG_TEXT_P(1); QUERYTYPE *rewrited = query; QTNode *tree; char *buf; void *plan; Portal portal; bool isnull; int i; if (query->size == 0) { PG_FREE_IF_COPY(in, 1); PG_RETURN_POINTER(rewrited); } tree = QT2QTN(GETQUERY(query), GETOPERAND(query)); QTNTernary(tree); QTNSort(tree); buf = (char *) palloc(VARSIZE(in)); memcpy(buf, VARDATA(in), VARSIZE(in) - VARHDRSZ); buf[VARSIZE(in) - VARHDRSZ] = '\0'; SPI_connect(); if (tsqOid == InvalidOid) get_tsq_Oid(); if ((plan = SPI_prepare(buf, 0, NULL)) == NULL) elog(ERROR, "SPI_prepare('%s') returns NULL", buf); if ((portal = SPI_cursor_open(NULL, plan, NULL, NULL, false)) == NULL) elog(ERROR, "SPI_cursor_open('%s') returns NULL", buf); SPI_cursor_fetch(portal, true, 100); if (SPI_tuptable->tupdesc->natts != 2) elog(ERROR, "number of fields doesn't equal to 2"); if (SPI_gettypeid(SPI_tuptable->tupdesc, 1) != tsqOid) elog(ERROR, "column #1 isn't of tsquery type"); if (SPI_gettypeid(SPI_tuptable->tupdesc, 2) != tsqOid) elog(ERROR, "column #2 isn't of tsquery type"); while (SPI_processed > 0 && tree) { for (i = 0; i < SPI_processed && tree; i++) { Datum qdata = SPI_getbinval(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 1, &isnull); Datum sdata; if (isnull) continue; sdata = SPI_getbinval(SPI_tuptable->vals[i], SPI_tuptable->tupdesc, 2, &isnull); if (!isnull) { QUERYTYPE *qtex = (QUERYTYPE *) DatumGetPointer(PG_DETOAST_DATUM(qdata)); QUERYTYPE *qtsubs = (QUERYTYPE *) DatumGetPointer(PG_DETOAST_DATUM(sdata)); QTNode *qex, *qsubs = NULL; if (qtex->size == 0) { if (qtex != (QUERYTYPE *) DatumGetPointer(qdata)) pfree(qtex); if (qtsubs != (QUERYTYPE *) DatumGetPointer(sdata)) pfree(qtsubs); continue; } qex = QT2QTN(GETQUERY(qtex), GETOPERAND(qtex)); QTNTernary(qex); QTNSort(qex); if (qtsubs->size) qsubs = QT2QTN(GETQUERY(qtsubs), GETOPERAND(qtsubs)); tree = findsubquery(tree, qex, SPIMemory, qsubs, NULL); QTNFree(qex); if (qtex != (QUERYTYPE *) DatumGetPointer(qdata)) pfree(qtex); QTNFree(qsubs); if (qtsubs != (QUERYTYPE *) DatumGetPointer(sdata)) pfree(qtsubs); } } SPI_freetuptable(SPI_tuptable); SPI_cursor_fetch(portal, true, 100); } SPI_freetuptable(SPI_tuptable); SPI_cursor_close(portal); SPI_freeplan(plan); SPI_finish(); if (tree) { QTNBinary(tree); rewrited = QTN2QT(tree, PlainMemory); QTNFree(tree); PG_FREE_IF_COPY(query, 0); } else { rewrited->len = HDRSIZEQT; rewrited->size = 0; } pfree(buf); PG_FREE_IF_COPY(in, 1); PG_RETURN_POINTER(rewrited); }
/* * Takes the prepared plan rsaved_plan and creates a cursor * for it using the values specified in ragvalues. * */ SEXP plr_SPI_cursor_open(SEXP cursor_name_arg,SEXP rsaved_plan, SEXP rargvalues) { saved_plan_desc *plan_desc = (saved_plan_desc *) R_ExternalPtrAddr(rsaved_plan); void *saved_plan = plan_desc->saved_plan; int nargs = plan_desc->nargs; Oid *typeids = plan_desc->typeids; FmgrInfo *typinfuncs = plan_desc->typinfuncs; int i; Datum *argvalues = NULL; char *nulls = NULL; bool isnull = false; SEXP obj; SEXP result = NULL; MemoryContext oldcontext; char cursor_name[64]; Portal portal=NULL; PREPARE_PG_TRY; PUSH_PLERRCONTEXT(rsupport_error_callback, "pg.spi.cursor_open"); /* Divide rargvalues */ if (nargs > 0) { if (!Rf_isVectorList(rargvalues)) error("%s", "second parameter must be a list of arguments " \ "to the prepared plan"); if (length(rargvalues) != nargs) error("list of arguments (%d) is not the same length " \ "as that of the prepared plan (%d)", length(rargvalues), nargs); argvalues = (Datum *) palloc(nargs * sizeof(Datum)); nulls = (char *) palloc(nargs * sizeof(char)); } for (i = 0; i < nargs; i++) { PROTECT(obj = VECTOR_ELT(rargvalues, i)); argvalues[i] = get_scalar_datum(obj, typeids[i], typinfuncs[i], &isnull); if (!isnull) nulls[i] = ' '; else nulls[i] = 'n'; UNPROTECT(1); } strncpy(cursor_name, CHAR(STRING_ELT(cursor_name_arg,0)), 64); /* switch to SPI memory context */ oldcontext = MemoryContextSwitchTo(plr_SPI_context); /* * trap elog/ereport so we can let R finish up gracefully * and generate the error once we exit the interpreter */ PG_TRY(); { /* Open the cursor */ portal = SPI_cursor_open(cursor_name,saved_plan, argvalues, nulls,1); } PLR_PG_CATCH(); PLR_PG_END_TRY(); /* back to caller's memory context */ MemoryContextSwitchTo(oldcontext); if(portal==NULL) error("SPI_cursor_open() failed"); else result = R_MakeExternalPtr(portal, R_NilValue, R_NilValue); POP_PLERRCONTEXT; return result; }
static int compute_sql_asm_tsp(char* sql, int sourceVertexId, bool reverseCost, tspPathElementType **path, int *pathCount) { int SPIcode; void *SPIplan; Portal SPIportal; bool moredata = TRUE; int ntuples; tspEdgeType *edges = NULL; int totalTuples = 0; DBG("Sql %s source %d reverse %s",sql,sourceVertexId,reverseCost==true?"true":"false"); tspEdgeType edgeColumns = {.id= -1, .source= -1, .target= -1, .cost= -1 }; char *errMesg; int ret = -1; errMesg=palloc(sizeof(char) * 300); DBG("start compute_sql_asm_tsp %i",*pathCount); SPIcode = SPI_connect(); if (SPIcode != SPI_OK_CONNECT) { elog(ERROR, "compute_sql_asm_tsp: couldn't open a connection to SPI"); return -1; } SPIplan = SPI_prepare(sql, 0, NULL); if (SPIplan == NULL) { elog(ERROR, "compute_sql_asm_tsp: couldn't create query plan via SPI"); return -1; } if ((SPIportal = SPI_cursor_open(NULL, SPIplan, NULL, NULL, true)) == NULL) { elog(ERROR, "compute_sql_asm_tsp: SPI_cursor_open('%s') returns NULL", sql); return -1; } while (moredata == TRUE) { SPI_cursor_fetch(SPIportal, TRUE, TUPLIMIT); if (edgeColumns.id == -1) { if (!fetchEdgeTspColumns(SPI_tuptable, &edgeColumns,reverseCost)) return finish(SPIcode, ret); } ntuples = SPI_processed; totalTuples += ntuples; if (!edges){ edges = palloc(totalTuples * sizeof(tspEdgeType)); } else { edges = repalloc(edges, totalTuples * sizeof(tspEdgeType)); } if (edges == NULL) { elog(ERROR, "Out of memory"); return finish(SPIcode, ret); } if (ntuples > 0) { int t; SPITupleTable *tuptable = SPI_tuptable; TupleDesc tupdesc = SPI_tuptable->tupdesc; for (t = 0; t < ntuples; t++) { HeapTuple tuple = tuptable->vals[t]; fetchEdgeTsp(&tuple, &tupdesc, &edgeColumns, &edges[totalTuples - ntuples + t],reverseCost); } SPI_freetuptable(tuptable); } else { moredata = FALSE; } } DBG("Total %i tuples", totalTuples); DBG("Calling tsp functions total tuples <%i> initial path count <%i>", totalTuples,*pathCount); ret=processATSPData(edges,totalTuples,sourceVertexId,reverseCost, path, pathCount,errMesg); DBG("SIZE %i elements to process",*pathCount); if (!ret ) { elog(ERROR, "Error computing path: %s", errMesg); } return finish(SPIcode, ret); } PG_FUNCTION_INFO_V1(sql_asm_tsp); Datum sql_asm_tsp(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; int callCntr; int maxCalls; TupleDesc tupleDesc; tspPathElementType *path; /* stuff done only on the first call of the function */ if (SRF_IS_FIRSTCALL()) { MemoryContext oldcontext; int pathCount = 0; int ret; /* create a function context for cross-call persistence */ funcctx = SRF_FIRSTCALL_INIT(); /* switch to memory context appropriate for multiple function calls */ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); ret = compute_sql_asm_tsp(text2char(PG_GETARG_TEXT_P(0)), PG_GETARG_INT32(1), PG_GETARG_BOOL(2), &path, &pathCount); #ifdef DEBUG if (ret >= 0) { int i; for (i = 0; i < pathCount; i++) { DBG("Step # %i vertexId %i cost %.4f", i, path[i].vertexId,path[i].cost); } } #endif /* total number of tuples to be returned */ funcctx->max_calls = pathCount; funcctx->user_fctx = path; DBG("Path count %i", pathCount); funcctx->tuple_desc = BlessTupleDesc(RelationNameGetTupleDesc("pgr_costResult")); MemoryContextSwitchTo(oldcontext); } funcctx = SRF_PERCALL_SETUP(); callCntr = funcctx->call_cntr; maxCalls = funcctx->max_calls; tupleDesc = funcctx->tuple_desc; path = (tspPathElementType*) funcctx->user_fctx; if (callCntr < maxCalls) { /* do when there is more left to send */ HeapTuple tuple; Datum result; Datum *values; char* nulls; values = palloc(4 * sizeof(Datum)); nulls = palloc(4 * sizeof(char)); values[0] = Int32GetDatum(callCntr); nulls[0] = ' '; values[1] = Int32GetDatum(path[callCntr].vertexId); nulls[1] = ' '; values[2] = Float8GetDatum(0); // edge id not supplied by this method nulls[2] = ' '; values[3] = Float8GetDatum(path[callCntr].cost); nulls[3] = ' '; tuple = heap_formtuple(tupleDesc, values, nulls); /* make the tuple into a datum */ result = HeapTupleGetDatum(tuple); /* clean up (this is not really necessary) */ pfree(values); pfree(nulls); SRF_RETURN_NEXT(funcctx, result); } else { /* do when there is no more left */ SRF_RETURN_DONE(funcctx); } }
int create_spatial_index(sqlite3 *db,char *dataset_name, char *idx_tbl,char * idx_geom, char *idx_id, char *sql_string, int create) { char sql_txt_pg[SQLSTRLEN]; char sql_txt_sqlite[SQLSTRLEN]; int rc; char *err_msg; SPIPlanPtr plan; sqlite3_stmt *prepared_statement; Portal cur; char *pg_type; int val_int, proc,j; float8 val_float; int64 val_int64; bool null_check; TupleDesc tupdesc; SPITupleTable *tuptable; HeapTuple tuple; int tot_rows = 0; if(create) { snprintf(sql_txt_pg,sizeof(sql_txt_pg), " %s%s%s", "CREATE VIRTUAL TABLE ", dataset_name, "_idx_geom USING rtree(id,minX, maxX,minY, maxY)"); rc = sqlite3_exec(db, sql_txt_pg, NULL, 0, &err_msg); if (rc != SQLITE_OK ) { sqlite3_free(err_msg); sqlite3_close(db); ereport(ERROR, ( errmsg("Problem creating table: %s", err_msg))); //fprintf(stderr, "SQL error: %s\n", err_msg); } elog(INFO, "create table string: %s", sql_txt_pg); } snprintf(sql_txt_pg,sizeof(sql_txt_pg), " %s%s%s%s%s%s%s%s%s", "with o as (", sql_string, "), g as( select ", idx_id, " id,", idx_geom, " geom from ", idx_tbl, ") select g.id, st_xmin(g.geom) minx,st_xmax(g.geom) maxx,st_ymin(g.geom) miny,st_ymax(g.geom) maxy from g inner join o on g.id=o.id"); elog(INFO, "select table string: %s", sql_txt_pg); plan = SPI_prepare(sql_txt_pg,0,NULL); //ret = SPI_exec(sql_string, 0); cur = SPI_cursor_open("index_cursor", plan,NULL,NULL,true); snprintf(sql_txt_sqlite,sizeof(sql_txt_sqlite), " %s%s%s", "insert into ", dataset_name, "_idx_geom (id,minX, maxX,minY, maxY) values(?,?,?,?,?)"); elog(INFO, "insert table string: %s", sql_txt_sqlite); sqlite3_prepare_v2(db,sql_txt_sqlite,strlen(sql_txt_sqlite), &prepared_statement,NULL); do { sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &err_msg); SPI_cursor_fetch(cur, true,100000); proc = SPI_processed; tot_rows += proc; // if (ret > 0 && SPI_tuptable != NULL) // { tupdesc = SPI_tuptable->tupdesc; tuptable = SPI_tuptable; for (j = 0; j < proc; j++) { tuple = tuptable->vals[j]; pg_type = SPI_gettype(tupdesc, 1); if(strcmp(pg_type, "int2")==0) { val_int = (int) DatumGetInt16(SPI_getbinval(tuple,tupdesc,1, &null_check)); //TODO add error handling if(null_check) sqlite3_bind_null(prepared_statement, 1); else sqlite3_bind_int(prepared_statement, 1,val_int); } else if(strcmp(pg_type, "int4")==0) { val_int = (int) DatumGetInt32(SPI_getbinval(tuple,tupdesc,1, &null_check)); //TODO add error handling if(null_check) sqlite3_bind_null(prepared_statement, 1); else sqlite3_bind_int(prepared_statement, 1,val_int); } else if(strcmp(pg_type, "int8")==0) { val_int64 = (int64) DatumGetInt64(SPI_getbinval(tuple,tupdesc,1, &null_check)); //TODO add error handling if(null_check) sqlite3_bind_null(prepared_statement, 1); else sqlite3_bind_int64(prepared_statement,1,val_int64); } val_float = (float8) DatumGetFloat8(SPI_getbinval(tuple,tupdesc,2, &null_check)); //TODO add error handling if(null_check) sqlite3_bind_null(prepared_statement, 2); else sqlite3_bind_double(prepared_statement,2,val_float); val_float = (float8) DatumGetFloat8(SPI_getbinval(tuple,tupdesc,3, &null_check)); //TODO add error handling if(null_check) sqlite3_bind_null(prepared_statement, 3); else sqlite3_bind_double(prepared_statement,3,val_float); val_float = (float8) DatumGetFloat8(SPI_getbinval(tuple,tupdesc,4, &null_check)); //TODO add error handling if(null_check) sqlite3_bind_null(prepared_statement, 4); else sqlite3_bind_double(prepared_statement,4,val_float); val_float = (float8) DatumGetFloat8(SPI_getbinval(tuple,tupdesc,5, &null_check)); //TODO add error handling if(null_check) sqlite3_bind_null(prepared_statement, 5); else sqlite3_bind_double(prepared_statement,5,val_float); sqlite3_step(prepared_statement); sqlite3_clear_bindings(prepared_statement); sqlite3_reset(prepared_statement); } sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &err_msg); elog(INFO, "inserted %d rows in index",tot_rows); } while (proc > 0); return 0; }
SV * plperl_spi_query(char *query) { SV *cursor; /* * Execute the query inside a sub-transaction, so we can cope with errors * sanely */ MemoryContext oldcontext = CurrentMemoryContext; ResourceOwner oldowner = CurrentResourceOwner; BeginInternalSubTransaction(NULL); /* Want to run inside function's memory context */ MemoryContextSwitchTo(oldcontext); PG_TRY(); { void *plan; Portal portal = NULL; /* Create a cursor for the query */ plan = SPI_prepare(query, 0, NULL); if (plan) portal = SPI_cursor_open(NULL, plan, NULL, NULL, false); if (portal) cursor = newSVpv(portal->name, 0); else cursor = newSV(0); /* Commit the inner transaction, return to outer xact context */ ReleaseCurrentSubTransaction(); MemoryContextSwitchTo(oldcontext); CurrentResourceOwner = oldowner; /* * AtEOSubXact_SPI() should not have popped any SPI context, but just * in case it did, make sure we remain connected. */ SPI_restore_connection(); } PG_CATCH(); { ErrorData *edata; /* Save error info */ MemoryContextSwitchTo(oldcontext); edata = CopyErrorData(); FlushErrorState(); /* Abort the inner transaction */ RollbackAndReleaseCurrentSubTransaction(); MemoryContextSwitchTo(oldcontext); CurrentResourceOwner = oldowner; /* * If AtEOSubXact_SPI() popped any SPI context of the subxact, it will * have left us in a disconnected state. We need this hack to return * to connected state. */ SPI_restore_connection(); /* Punt the error to Perl */ croak("%s", edata->message); /* Can't get here, but keep compiler quiet */ return NULL; } PG_END_TRY(); return cursor; }
static int compute_trsp( char* sql, int dovertex, long start_id, double start_pos, long end_id, double end_pos, bool directed, bool has_reverse_cost, char* restrict_sql, path_element_t **path, uint32_t *path_count) { int SPIcode; SPIPlanPtr SPIplan; Portal SPIportal; bool moredata = TRUE; size_t ntuples; edge_t *edges = NULL; size_t total_tuples = 0; #ifndef _MSC_VER edge_columns_t edge_columns = {.id= -1, .source= -1, .target= -1, .cost= -1, .reverse_cost= -1}; #else // _MSC_VER edge_columns_t edge_columns = {-1, -1, -1, -1, -1}; #endif //_MSC_VER restrict_t *restricts = NULL; size_t total_restrict_tuples = 0; #ifndef _MSC_VER restrict_columns_t restrict_columns = {.target_id= -1, .via_path= -1, .to_cost= -1}; #else // _MSC_VER restrict_columns_t restrict_columns = {-1, -1, -1}; #endif //_MSC_VER long v_max_id=0; long v_min_id=INT_MAX; /* track if start and end are both in edge tuples */ int s_count = 0; int t_count = 0; char *err_msg; int ret = -1; uint32_t z; PGR_DBG("start turn_restrict_shortest_path\n"); SPIcode = SPI_connect(); if (SPIcode != SPI_OK_CONNECT) { elog(ERROR, "turn_restrict_shortest_path: couldn't open a connection to SPI"); return -1; } SPIplan = SPI_prepare(sql, 0, NULL); if (SPIplan == NULL) { elog(ERROR, "turn_restrict_shortest_path: couldn't create query plan via SPI"); return -1; } if ((SPIportal = SPI_cursor_open(NULL, SPIplan, NULL, NULL, true)) == NULL) { elog(ERROR, "turn_restrict_shortest_path: SPI_cursor_open('%s') returns NULL", sql); return -1; } while (moredata == TRUE) { //PGR_DBG("calling SPI_cursor_fetch"); SPI_cursor_fetch(SPIportal, TRUE, TUPLIMIT); if (SPI_tuptable == NULL) { elog(ERROR, "SPI_tuptable is NULL"); return finish(SPIcode, -1); } if (edge_columns.id == -1) { if (fetch_edge_columns(SPI_tuptable, &edge_columns, has_reverse_cost) == -1) return finish(SPIcode, ret); } ntuples = SPI_processed; //PGR_DBG("Reading edges: %i - %i", total_tuples, total_tuples+ntuples); total_tuples += ntuples; if (ntuples > 0) { if (!edges) edges = palloc(total_tuples * sizeof(edge_t)); else edges = repalloc(edges, total_tuples * sizeof(edge_t)); if (edges == NULL) { elog(ERROR, "Out of memory"); return finish(SPIcode, ret); } uint32_t t; SPITupleTable *tuptable = SPI_tuptable; TupleDesc tupdesc = SPI_tuptable->tupdesc; for (t = 0; t < ntuples; t++) { //if (t%100 == 0) { PGR_DBG(" t: %i", t); } HeapTuple tuple = tuptable->vals[t]; fetch_edge(&tuple, &tupdesc, &edge_columns, &edges[total_tuples - ntuples + t]); } //PGR_DBG("calling SPI_freetuptable"); SPI_freetuptable(tuptable); //PGR_DBG("back from SPI_freetuptable"); } else { moredata = FALSE; } } SPI_cursor_close(SPIportal); //defining min and max vertex id //PGR_DBG("Total %i edge tuples", total_tuples); for(z=0; z<total_tuples; z++) { if(edges[z].source<v_min_id) v_min_id=edges[z].source; if(edges[z].source>v_max_id) v_max_id=edges[z].source; if(edges[z].target<v_min_id) v_min_id=edges[z].target; if(edges[z].target>v_max_id) v_max_id=edges[z].target; //PGR_DBG("%i <-> %i", v_min_id, v_max_id); } //:::::::::::::::::::::::::::::::::::: //:: reducing vertex id (renumbering) //:::::::::::::::::::::::::::::::::::: for(z=0; z<total_tuples; z++) { //check if edges[] contains source and target if (dovertex) { if(edges[z].source == start_id || edges[z].target == start_id) ++s_count; if(edges[z].source == end_id || edges[z].target == end_id) ++t_count; } else { if(edges[z].id == start_id) ++s_count; if(edges[z].id == end_id) ++t_count; } edges[z].source-=v_min_id; edges[z].target-=v_min_id; edges[z].cost = edges[z].cost; //PGR_DBG("edgeID: %i SRc:%i - %i, cost: %f", edges[z].id,edges[z].source, edges[z].target,edges[z].cost); } PGR_DBG("Min vertex id: %ld , Max vid: %ld",v_min_id,v_max_id); PGR_DBG("Total %ld edge tuples", total_tuples); if(s_count == 0) { elog(ERROR, "Start id was not found."); return -1; } if(t_count == 0) { elog(ERROR, "Target id was not found."); return -1; } if (dovertex) { start_id -= v_min_id; end_id -= v_min_id; } PGR_DBG("Fetching restriction tuples\n"); if (restrict_sql == NULL) { PGR_DBG("Sql for restrictions is null."); } else { SPIplan = SPI_prepare(restrict_sql, 0, NULL); if (SPIplan == NULL) { elog(ERROR, "turn_restrict_shortest_path: couldn't create query plan via SPI"); return -1; } if ((SPIportal = SPI_cursor_open(NULL, SPIplan, NULL, NULL, true)) == NULL) { elog(ERROR, "turn_restrict_shortest_path: SPI_cursor_open('%s') returns NULL", restrict_sql); return -1; } moredata = TRUE; while (moredata == TRUE) { SPI_cursor_fetch(SPIportal, TRUE, TUPLIMIT); if (restrict_columns.target_id == -1) { if (fetch_restrict_columns(SPI_tuptable, &restrict_columns) == -1) { PGR_DBG("fetch_restrict_columns failed!"); return finish(SPIcode, ret); } } ntuples = SPI_processed; total_restrict_tuples += ntuples; //PGR_DBG("Reading Restrictions: %i", total_restrict_tuples); if (ntuples > 0) { if (!restricts) restricts = palloc(total_restrict_tuples * sizeof(restrict_t)); else restricts = repalloc(restricts, total_restrict_tuples * sizeof(restrict_t)); if (restricts == NULL) { elog(ERROR, "Out of memory"); return finish(SPIcode, ret); } uint32_t t; SPITupleTable *tuptable = SPI_tuptable; TupleDesc tupdesc = SPI_tuptable->tupdesc; for (t = 0; t < ntuples; t++) { HeapTuple tuple = tuptable->vals[t]; fetch_restrict(&tuple, &tupdesc, &restrict_columns, &restricts[total_restrict_tuples - ntuples + t]); } SPI_freetuptable(tuptable); } else { moredata = FALSE; } } SPI_cursor_close(SPIportal); } #ifdef DEBUG_OFF int t; for (t=0; t<total_restrict_tuples; t++) { PGR_DBG("restricts: %.2f, %i, %i, %i, %i, %i, %i", restricts[t].to_cost, restricts[t].target_id, restricts[t].via[0], restricts[t].via[1], restricts[t].via[2], restricts[t].via[3], restricts[t].via[4]); } #endif PGR_DBG("Total %ld restriction tuples", total_restrict_tuples); if (dovertex) { PGR_DBG("Calling trsp_node_wrapper\n"); /** hack always returns 0 -1 when installed on EDB VC++ 64-bit without this **/ #if defined(__MINGW64__) // elog(NOTICE,"Calling trsp_node_wrapper\n"); #endif ret = trsp_node_wrapper(edges, (uint32_t)total_tuples, restricts, (uint32_t)total_restrict_tuples, start_id, end_id, directed, has_reverse_cost, path, path_count, &err_msg); } else { PGR_DBG("Calling trsp_edge_wrapper\n"); ret = trsp_edge_wrapper(edges, (uint32_t)total_tuples, restricts, (uint32_t)total_restrict_tuples, start_id, start_pos, end_id, end_pos, directed, has_reverse_cost, path, path_count, &err_msg); } PGR_DBG("Message received from inside:"); PGR_DBG("%s",err_msg); //PGR_DBG("SIZE %i\n",*path_count); //:::::::::::::::::::::::::::::::: //:: restoring original vertex id //:::::::::::::::::::::::::::::::: for(z=0;z<*path_count;z++) { //PGR_DBG("vetex %i\n",(*path)[z].vertex_id); if (z || (*path)[z].vertex_id != -1) (*path)[z].vertex_id+=v_min_id; } PGR_DBG("ret = %i\n", ret); PGR_DBG("*path_count = %i\n", *path_count); if (ret < 0) { //elog(ERROR, "Error computing path: %s", err_msg); ereport(ERROR, (errcode(ERRCODE_E_R_E_CONTAINING_SQL_NOT_PERMITTED), errmsg("Error computing path: %s", err_msg))); } return finish(SPIcode, ret); } PG_FUNCTION_INFO_V1(turn_restrict_shortest_path_vertex); PGDLLEXPORT Datum turn_restrict_shortest_path_vertex(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; uint32_t call_cntr; uint32_t max_calls; TupleDesc tuple_desc; path_element_t *path; char * sql; // stuff done only on the first call of the function if (SRF_IS_FIRSTCALL()) { MemoryContext oldcontext; uint32_t path_count = 0; int ret = -1; if (ret == -1) {}; // to avoid warning set but not used int i; // create a function context for cross-call persistence funcctx = SRF_FIRSTCALL_INIT(); // switch to memory context appropriate for multiple function calls oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); // verify that the first 5 args are not NULL for (i=0; i<5; i++) if(PG_ARGISNULL(i)) { elog(ERROR, "turn_restrict_shortest_path(): Argument %i may not be NULL", i+1); } if (PG_ARGISNULL(5)) sql = NULL; else { sql = text2char(PG_GETARG_TEXT_P(5)); if (strlen(sql) == 0) sql = NULL; } PGR_DBG("Calling compute_trsp"); ret = compute_trsp(text2char(PG_GETARG_TEXT_P(0)), 1, // do vertex PG_GETARG_INT32(1), 0.5, PG_GETARG_INT32(2), 0.5, PG_GETARG_BOOL(3), PG_GETARG_BOOL(4), sql, &path, &path_count); #ifdef DEBUG double total_cost = 0; PGR_DBG("Ret is %i", ret); if (ret >= 0) { int i; for (i = 0; i < path_count; i++) { // PGR_DBG("Step %i vertex_id %i ", i, path[i].vertex_id); // PGR_DBG(" edge_id %i ", path[i].edge_id); // PGR_DBG(" cost %f ", path[i].cost); total_cost+=path[i].cost; } } PGR_DBG("Total cost is: %f",total_cost); #endif // total number of tuples to be returned funcctx->max_calls = path_count; funcctx->user_fctx = path; funcctx->tuple_desc = BlessTupleDesc(RelationNameGetTupleDesc("pgr_costResult")); MemoryContextSwitchTo(oldcontext); } // stuff done on every call of the function funcctx = SRF_PERCALL_SETUP(); call_cntr = (uint32_t)funcctx->call_cntr; max_calls = (uint32_t)funcctx->max_calls; tuple_desc = funcctx->tuple_desc; path = (path_element_t*) funcctx->user_fctx; if (call_cntr < max_calls) // do when there is more left to send { HeapTuple tuple; Datum result; Datum *values; bool* nulls; values = palloc(4 * sizeof(Datum)); nulls = palloc(4 * sizeof(bool)); values[0] = Int32GetDatum(call_cntr); nulls[0] = false; values[1] = Int32GetDatum(path[call_cntr].vertex_id); nulls[1] = false; values[2] = Int32GetDatum(path[call_cntr].edge_id); nulls[2] = false; values[3] = Float8GetDatum(path[call_cntr].cost); nulls[3] = false; tuple = heap_form_tuple(tuple_desc, values, nulls); // make the tuple into a datum result = HeapTupleGetDatum(tuple); // clean up (this is not really necessary) pfree(values); pfree(nulls); SRF_RETURN_NEXT(funcctx, result); } else // do when there is no more left { PGR_DBG("Going to free path"); if (path) free(path); SRF_RETURN_DONE(funcctx); } } PG_FUNCTION_INFO_V1(turn_restrict_shortest_path_edge); PGDLLEXPORT Datum turn_restrict_shortest_path_edge(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; uint32_t call_cntr; uint32_t max_calls; TupleDesc tuple_desc; path_element_t *path; char * sql; // stuff done only on the first call of the function if (SRF_IS_FIRSTCALL()) { MemoryContext oldcontext; uint32_t path_count = 0; #ifdef DEBUG int ret = -1; #endif int i; double s_pos; double e_pos; // create a function context for cross-call persistence funcctx = SRF_FIRSTCALL_INIT(); // switch to memory context appropriate for multiple function calls oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); // verify that the first 5 args are not NULL for (i=0; i<7; i++) { if(i==2 || i==4) continue; if(PG_ARGISNULL(i)) { elog(ERROR, "turn_restrict_shortest_path(): Argument %i may not be NULL", i+1); } } if (PG_ARGISNULL(2)) s_pos = 0.5; else { s_pos = PG_GETARG_FLOAT8(2); if (s_pos < 0.0) s_pos = 0.5; if (s_pos > 1.0) s_pos = 0.5; } if (PG_ARGISNULL(4)) e_pos = 0.5; else { e_pos = PG_GETARG_FLOAT8(4); if (e_pos < 0.0) e_pos = 0.5; if (e_pos > 1.0) e_pos = 0.5; } if (PG_ARGISNULL(7)) sql = NULL; else { sql = text2char(PG_GETARG_TEXT_P(7)); if (strlen(sql) == 0) sql = NULL; } PGR_DBG("Calling compute_trsp"); #ifdef DEBUG ret = #endif compute_trsp(text2char(PG_GETARG_TEXT_P(0)), 0, //sdo edge PG_GETARG_INT32(1), s_pos, PG_GETARG_INT32(3), e_pos, PG_GETARG_BOOL(5), PG_GETARG_BOOL(6), sql, &path, &path_count); #ifdef DEBUG double total_cost = 0; PGR_DBG("Ret is %i", ret); if (ret >= 0) { int i; for (i = 0; i < path_count; i++) { // PGR_DBG("Step %i vertex_id %i ", i, path[i].vertex_id); // PGR_DBG(" edge_id %i ", path[i].edge_id); // PGR_DBG(" cost %f ", path[i].cost); total_cost+=path[i].cost; } } PGR_DBG("Total cost is: %f",total_cost); #endif // total number of tuples to be returned funcctx->max_calls = path_count; funcctx->user_fctx = path; funcctx->tuple_desc = BlessTupleDesc(RelationNameGetTupleDesc("pgr_costResult")); MemoryContextSwitchTo(oldcontext); } // stuff done on every call of the function funcctx = SRF_PERCALL_SETUP(); call_cntr = (uint32_t)funcctx->call_cntr; max_calls = (uint32_t)funcctx->max_calls; tuple_desc = funcctx->tuple_desc; path = (path_element_t*) funcctx->user_fctx; if (call_cntr < max_calls) // do when there is more left to send { HeapTuple tuple; Datum result; Datum *values; bool* nulls; values = palloc(4 * sizeof(Datum)); nulls = palloc(4 * sizeof(bool)); values[0] = Int32GetDatum(call_cntr); nulls[0] = false; values[1] = Int32GetDatum(path[call_cntr].vertex_id); nulls[1] = false; values[2] = Int32GetDatum(path[call_cntr].edge_id); nulls[2] = false; values[3] = Float8GetDatum(path[call_cntr].cost); nulls[3] = false; tuple = heap_form_tuple(tuple_desc, values, nulls); // make the tuple into a datum result = HeapTupleGetDatum(tuple); // clean up (this is not really necessary) pfree(values); pfree(nulls); SRF_RETURN_NEXT(funcctx, result); } else // do when there is no more left { PGR_DBG("Going to free path"); if (path) free(path); SRF_RETURN_DONE(funcctx); } }