static PyObject *
PLy_cursor_iternext(PyObject *self)
{
PLyCursorObject *cursor;
PyObject *ret;
volatile MemoryContext oldcontext;
volatile ResourceOwner oldowner;
Portal portal;
cursor = (PLyCursorObject *) self;
if (cursor->closed)
{
PLy_exception_set(PyExc_ValueError, "iterating a closed cursor");
return NULL;
}
portal = GetPortalByName(cursor->portalname);
if (!PortalIsValid(portal))
{
PLy_exception_set(PyExc_ValueError,
"iterating a cursor in an aborted subtransaction");
return NULL;
}
oldcontext = CurrentMemoryContext;
oldowner = CurrentResourceOwner;
PLy_spi_subtransaction_begin(oldcontext, oldowner);
PG_TRY();
{
SPI_cursor_fetch(portal, true, 1);
if (SPI_processed == 0)
{
PyErr_SetNone(PyExc_StopIteration);
ret = NULL;
}
else
{
if (cursor->result.is_rowtype != 1)
PLy_input_tuple_funcs(&cursor->result, SPI_tuptable->tupdesc);
ret = PLyDict_FromTuple(&cursor->result, SPI_tuptable->vals[0],
SPI_tuptable->tupdesc);
}
SPI_freetuptable(SPI_tuptable);
PLy_spi_subtransaction_commit(oldcontext, oldowner);
}
PG_CATCH();
{
PLy_spi_subtransaction_abort(oldcontext, oldowner);
return NULL;
}
PG_END_TRY();
return ret;
}
static int luaP_cursorfetch (lua_State *L) {
luaP_Cursor *c = (luaP_Cursor *) luaP_checkudata(L, 1, PLLUA_CURSORMT);
luaP_TRY {
SPI_cursor_fetch(c->cursor, 1, luaL_optlong(L, 2, FETCH_ALL));
} luaP_CATCH;
if (SPI_processed > 0) /* any rows? */
luaP_pushtuptable(L, c->cursor);
else
lua_pushnil(L);
return 1;
}
SEXP
plr_SPI_cursor_fetch(SEXP cursor_in,SEXP forward_in, SEXP rows_in)
{
Portal portal=NULL;
int ntuples;
SEXP result = NULL;
MemoryContext oldcontext;
int forward;
int rows;
PREPARE_PG_TRY;
PUSH_PLERRCONTEXT(rsupport_error_callback, "pg.spi.cursor_fetch");
portal = R_ExternalPtrAddr(cursor_in);
if(!IS_LOGICAL(forward_in))
{
error("pg.spi.cursor_fetch arg2 must be boolean");
return result;
}
if(!IS_INTEGER(rows_in))
{
error("pg.spi.cursor_fetch arg3 must be an integer");
return result;
}
forward = LOGICAL_DATA(forward_in)[0];
rows = INTEGER_DATA(rows_in)[0];
/* switch to SPI memory context */
oldcontext = MemoryContextSwitchTo(plr_SPI_context);
PG_TRY();
{
/* Open the cursor */
SPI_cursor_fetch(portal,forward,rows);
}
PLR_PG_CATCH();
PLR_PG_END_TRY();
/* back to caller's memory context */
MemoryContextSwitchTo(oldcontext);
/* check the result */
ntuples = SPI_processed;
if (ntuples > 0)
{
result = rpgsql_get_results(ntuples, SPI_tuptable);
SPI_freetuptable(SPI_tuptable);
}
else
result = R_NilValue;
POP_PLERRCONTEXT;
return result;
}
static int luaP_rowsaux (lua_State *L) {
luaP_Cursor *c;
luaP_Tuple* t;
unsigned int i;
uint32 processed = 0;
BEGINLUA;
c = (luaP_Cursor *) lua_touserdata(L, lua_upvalueindex(1));
if (c->tupleQueue && tq_isempty(c->tupleQueue)){
pfree(c->tupleQueue);
c->tupleQueue = NULL;
}
if (c->tupleQueue == NULL){
PLLUA_PG_CATCH_RETHROW(
SPI_cursor_fetch(c->cursor, 1, FETCH_CSR_Q);
);
static int luaP_rowsaux (lua_State *L) {
luaP_Cursor *c = (luaP_Cursor *) lua_touserdata(L, lua_upvalueindex(1));
int init = lua_toboolean(L, lua_upvalueindex(2));
luaP_TRY {
SPI_cursor_fetch(c->cursor, 1, 1);
} luaP_CATCH;
if (SPI_processed > 0) { /* any row? */
if (!init) { /* register tupdesc */
lua_pushinteger(L, (int) InvalidOid);
luaP_pushdesctable(L, SPI_tuptable->tupdesc);
lua_rawset(L, LUA_REGISTRYINDEX);
lua_pushboolean(L, 1);
lua_replace(L, lua_upvalueindex(2));
}
luaP_pushtuple(L, SPI_tuptable->tupdesc, SPI_tuptable->vals[0],
InvalidOid, 1);
}
else {
SPI_cursor_close(c->cursor);
lua_pushnil(L);
}
return 1;
}
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;
}
void
pgr_get_customers_data(
char *customers_sql,
Customer_t **customers,
size_t *total_customers) {
const int tuple_limit = 1000000;
PGR_DBG("pgr_get_customers_data");
PGR_DBG("%s", customers_sql);
Column_info_t info[9];
int i;
for (i = 0; i < 9; ++i) {
info[i].colNumber = -1;
info[i].type = 0;
info[i].strict = true;
info[i].eType = ANY_NUMERICAL;
}
/*!
int64_t id;
double x;
double y;
double demand;
double Etime;
double Ltime;
double Stime;
int64_t Pindex;
int64_t Dindex;
double Ddist;
*/
info[0].name = strdup("id");
info[1].name = strdup("x");
info[2].name = strdup("y");
info[3].name = strdup("demand");
info[4].name = strdup("opentime");
info[5].name = strdup("closetime");
info[6].name = strdup("servicetime");
info[7].name = strdup("pindex");
info[8].name = strdup("dindex");
info[0].eType = ANY_INTEGER;
info[7].eType = ANY_INTEGER;
info[8].eType = ANY_INTEGER;
size_t ntuples;
size_t total_tuples;
void *SPIplan;
SPIplan = pgr_SPI_prepare(customers_sql);
Portal SPIportal;
SPIportal = pgr_SPI_cursor_open(SPIplan);
bool moredata = TRUE;
(*total_customers) = total_tuples = 0;
/* on the first tuple get the column numbers */
while (moredata == TRUE) {
SPI_cursor_fetch(SPIportal, TRUE, tuple_limit);
if (total_tuples == 0) {
pgr_fetch_column_info(info, 9);
}
ntuples = SPI_processed;
total_tuples += ntuples;
PGR_DBG("SPI_processed %ld", ntuples);
if (ntuples > 0) {
if ((*customers) == NULL)
(*customers) = (Customer_t *)palloc0(
total_tuples * sizeof(Customer_t));
else
(*customers) = (Customer_t *)repalloc(
(*customers), total_tuples * sizeof(Customer_t));
if ((*customers) == NULL) {
elog(ERROR, "Out of memory");
}
size_t t;
SPITupleTable *tuptable = SPI_tuptable;
TupleDesc tupdesc = SPI_tuptable->tupdesc;
PGR_DBG("processing %ld", ntuples);
for (t = 0; t < ntuples; t++) {
HeapTuple tuple = tuptable->vals[t];
fetch_customer(&tuple, &tupdesc, info,
&(*customers)[total_tuples - ntuples + t]);
}
SPI_freetuptable(tuptable);
} else {
moredata = FALSE;
}
}
SPI_cursor_close(SPIportal);
if (total_tuples == 0) {
(*total_customers) = 0;
PGR_DBG("NO customers");
return;
}
(*total_customers) = total_tuples;
PGR_DBG("Finish reading %ld data, %ld", total_tuples, (*total_customers));
}
static
void
get_edges_basic(
char *sql,
pgr_basic_edge_t **edges,
size_t *totalTuples,
bool ignore_id) {
clock_t start_t = clock();
const int tuple_limit = 1000000;
size_t ntuples;
size_t total_tuples;
size_t valid_edges;
Column_info_t info[5];
int i;
for (i = 0; i < 5; ++i) {
info[i].colNumber = -1;
info[i].type = 0;
info[i].strict = true;
info[i].eType = ANY_INTEGER;
}
info[0].name = strdup("id");
info[1].name = strdup("source");
info[2].name = strdup("target");
info[3].name = strdup("going");
info[4].name = strdup("coming");
info[0].strict = !ignore_id;
info[4].strict = false;
info[3].eType = ANY_NUMERICAL;
info[4].eType = ANY_NUMERICAL;
void *SPIplan;
SPIplan = pgr_SPI_prepare(sql);
Portal SPIportal;
SPIportal = pgr_SPI_cursor_open(SPIplan);
bool moredata = TRUE;
(*totalTuples) = total_tuples = valid_edges = 0;
int64_t default_id = 0;
while (moredata == TRUE) {
SPI_cursor_fetch(SPIportal, TRUE, tuple_limit);
if (total_tuples == 0)
pgr_fetch_column_info(info, 5);
ntuples = SPI_processed;
total_tuples += ntuples;
if (ntuples > 0) {
if ((*edges) == NULL)
(*edges) = (pgr_basic_edge_t *)palloc0(
total_tuples * sizeof(pgr_basic_edge_t));
else
(*edges) = (pgr_basic_edge_t *)repalloc(
(*edges), total_tuples * sizeof(pgr_basic_edge_t));
if ((*edges) == NULL) {
elog(ERROR, "Out of memory");
}
size_t t;
SPITupleTable *tuptable = SPI_tuptable;
TupleDesc tupdesc = SPI_tuptable->tupdesc;
for (t = 0; t < ntuples; t++) {
HeapTuple tuple = tuptable->vals[t];
fetch_basic_edge(&tuple, &tupdesc, info,
&default_id,
&(*edges)[total_tuples - ntuples + t],
&valid_edges);
}
SPI_freetuptable(tuptable);
} else {
moredata = FALSE;
}
}
SPI_cursor_close(SPIportal);
if (total_tuples == 0 || valid_edges == 0) {
PGR_DBG("No edges found");
}
(*totalTuples) = total_tuples;
PGR_DBG("Reading %ld edges", total_tuples);
time_msg("reading edges", start_t, clock());
}
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;
}
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);
}
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_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 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);
}
}
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_fetch(PyObject *self, PyObject *args)
{
PLyCursorObject *cursor;
int count;
PLyResultObject *ret;
volatile MemoryContext oldcontext;
volatile ResourceOwner oldowner;
Portal portal;
if (!PyArg_ParseTuple(args, "i", &count))
return NULL;
cursor = (PLyCursorObject *) self;
if (cursor->closed)
{
PLy_exception_set(PyExc_ValueError, "fetch from a closed cursor");
return NULL;
}
portal = GetPortalByName(cursor->portalname);
if (!PortalIsValid(portal))
{
PLy_exception_set(PyExc_ValueError,
"iterating a cursor in an aborted subtransaction");
return NULL;
}
ret = (PLyResultObject *) PLy_result_new();
if (ret == NULL)
return NULL;
oldcontext = CurrentMemoryContext;
oldowner = CurrentResourceOwner;
PLy_spi_subtransaction_begin(oldcontext, oldowner);
PG_TRY();
{
SPI_cursor_fetch(portal, true, count);
if (cursor->result.is_rowtype != 1)
PLy_input_tuple_funcs(&cursor->result, SPI_tuptable->tupdesc);
Py_DECREF(ret->status);
ret->status = PyInt_FromLong(SPI_OK_FETCH);
Py_DECREF(ret->nrows);
ret->nrows = PyInt_FromLong(SPI_processed);
if (SPI_processed != 0)
{
int i;
Py_DECREF(ret->rows);
ret->rows = PyList_New(SPI_processed);
for (i = 0; i < SPI_processed; i++)
{
PyObject *row = PLyDict_FromTuple(&cursor->result,
SPI_tuptable->vals[i],
SPI_tuptable->tupdesc);
PyList_SetItem(ret->rows, i, row);
}
}
SPI_freetuptable(SPI_tuptable);
PLy_spi_subtransaction_commit(oldcontext, oldowner);
}
PG_CATCH();
{
PLy_spi_subtransaction_abort(oldcontext, oldowner);
return NULL;
}
PG_END_TRY();
return (PyObject *) ret;
}
int create_sqlite_table(Portal *cur,sqlite3 *db,char *insert_str, char *dataset_name, char *twkb_name, char *id_name, int create)
{
char create_table_string[SQLSTRLEN];
char tmp_str[64];
TupleDesc tupdesc;
int i, rc;
int strlengd = 0;
int strlengd_ins = 0;
char *err_msg, sqlitetype[15] ;
char field_name[128];
char value_list[256];
int strlengd_vals = 0;
/*Get fielads definition by fetching 0 rows*/
SPI_cursor_fetch(*cur, true,0);
if(create)
{
snprintf(create_table_string, sizeof(create_table_string), " %s%s%s","create table ",dataset_name,"(");
strlengd = strlen(create_table_string);
}
snprintf(insert_str,SQLSTRLEN, " %s%s%s","insert into " ,dataset_name,"(");
strlengd_ins = strlen(insert_str);
snprintf(value_list,sizeof(value_list), " %s","values(");
strlengd_vals = strlen(value_list);
tupdesc = SPI_tuptable->tupdesc;
for (i = 1; i <= tupdesc->natts; i++)
{
snprintf(field_name, sizeof(field_name), "%s", SPI_fname(tupdesc, i));
//convert type to sqlite type
getsqlitetype(SPI_gettype(tupdesc, i), sqlitetype);
if (strcmp(field_name, id_name)==0)
{
snprintf(tmp_str, sizeof(tmp_str), " %s%s",
" id integer primary key",
(i == tupdesc->natts) ? " " : ", ");
//construct the insert string with field names
snprintf(insert_str+strlengd_ins, SQLSTRLEN-strlengd_ins, "%s%s",
"id",
(i == tupdesc->natts) ? " " : ", ");
strlengd_ins += 3; //adding 1 for the comma-sign
}
else if (strcmp(field_name, twkb_name)==0)
{
snprintf(tmp_str, sizeof(tmp_str), " %s%s",
" twkb blob",
(i == tupdesc->natts) ? " " : ", ");
//construct the insert string with field names
snprintf(insert_str+strlengd_ins, SQLSTRLEN-strlengd_ins, "%s%s",
"twkb",
(i == tupdesc->natts) ? " " : ", ");
strlengd_ins += 5; //adding 1 for the comma-sign
}
else
{
//put together field name, type and comma sign if not last column
snprintf(tmp_str, sizeof(tmp_str), " %s %s%s",
field_name,
sqlitetype,
(i == tupdesc->natts) ? " " : ", ");
//construct the insert string with field names
snprintf(insert_str+strlengd_ins, SQLSTRLEN-strlengd_ins, "%s%s",
field_name,
(i == tupdesc->natts) ? " " : ", ");
strlengd_ins += strlen(field_name)+1; //adding 1 for the comma-sign
}
if(create)
{
//put the column name and type in the create-table sql-string
snprintf(create_table_string+strlengd, sizeof(create_table_string)-strlengd, " %s",tmp_str);
strlengd += strlen(tmp_str);
}
//construct the value part of the insert
snprintf(value_list+strlengd_vals, sizeof(value_list)-strlengd_vals, "%s%s",
"?",
(i == tupdesc->natts) ? " " : ", ");
strlengd_vals += 2; //adding 1 for the comma-sign
// elog(INFO, "strlength %d, temp: %s",strlengd_ins, insert_str);
}
if(create)
snprintf(create_table_string+strlengd, sizeof(create_table_string)-strlengd, " %s",")");
elog(INFO, " SQLSTRLEN-strlengd_ins: %d, insert sql: %s", SQLSTRLEN-strlengd_ins, insert_str);
// snprintf(insert_str+strlengd_ins, SQLSTRLEN-strlengd_ins, " %s",")");
snprintf(insert_str+strlengd_ins, SQLSTRLEN-strlengd_ins, " %s%s%s",")",value_list,")" );
elog(INFO, "sql: %s", create_table_string);
elog(INFO, "insert sql: %s", insert_str);
if(create)
{
rc = sqlite3_exec(db, create_table_string, 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);
}
}
return 0;
}
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);
}
}
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);
}
}
void
pgr_get_restriction_data(
char *restrictions_sql,
Restrict_t **restrictions,
size_t *total_restrictions) {
const int tuple_limit = 1000000;
clock_t start_t = clock();
PGR_DBG("pgr_get_restriction_data");
PGR_DBG("%s", restrictions_sql);
Column_info_t info[3];
int i;
for (i = 0; i < 3; ++i) {
info[i].colNumber = -1;
info[i].type = 0;
info[i].strict = true;
info[i].eType = ANY_INTEGER;
}
info[0].name = strdup("id");
info[1].name = strdup("cost");
info[2].name = strdup("restricted_edges");
info[1].eType = ANY_NUMERICAL;
info[2].eType = ANY_INTEGER_ARRAY;
#if 0
// experiment starts
size_t total_tuples = (*total_restrictions) ;
(*restrictions) = (Restrict_t *)palloc0(sizeof(Restrict_t));
(*restrictions)[0].id = 1;
(*restrictions)[0].cost = -1;
(*restrictions)[0].restricted_edges[0] = 4;
(*restrictions)[0].restricted_edges[1] = 7;
// experiment ends
#endif
#if 1
size_t ntuples;
size_t total_tuples;
void *SPIplan;
SPIplan = pgr_SPI_prepare(restrictions_sql);
Portal SPIportal;
SPIportal = pgr_SPI_cursor_open(SPIplan);
bool moredata = TRUE;
(*total_restrictions) = total_tuples = 0;
while (moredata == TRUE) {
SPI_cursor_fetch(SPIportal, TRUE, tuple_limit);
if (total_tuples == 0) {
pgr_fetch_column_info(info, 3);
}
ntuples = SPI_processed;
total_tuples += ntuples;
PGR_DBG("SPI_processed %ld", ntuples);
if (ntuples > 0) {
if ((*restrictions) == NULL)
(*restrictions) = (Restrict_t *)palloc0(
total_tuples * sizeof(Restrict_t));
else
(*restrictions) = (Restrict_t *)repalloc(
(*restrictions),
total_tuples * sizeof(Restrict_t));
if ((*restrictions) == NULL) {
elog(ERROR, "Out of memory");
}
size_t t;
SPITupleTable *tuptable = SPI_tuptable;
TupleDesc tupdesc = SPI_tuptable->tupdesc;
PGR_DBG("processing %ld", ntuples);
for (t = 0; t < ntuples; t++) {
HeapTuple tuple = tuptable->vals[t];
fetch_restriction(&tuple, &tupdesc, info,
&(*restrictions)[total_tuples - ntuples + t]);
}
SPI_freetuptable(tuptable);
} else {
moredata = FALSE;
}
}
SPI_cursor_close(SPIportal);
if (total_tuples == 0) {
(*total_restrictions) = 0;
PGR_DBG("NO restrictions");
return;
}
(*total_restrictions) = total_tuples;
#endif
PGR_DBG("Finish reading %ld data, %ld",
total_tuples,
(*total_restrictions));
clock_t end_t = clock();
time_msg(" reading Restrictions", start_t, end_t);
}
/*!
* bigint start_vid,
* bigint end_vid,
* float agg_cost,
*/
void pgr_get_matrixRows(
char *sql,
Matrix_cell_t **rows,
size_t *total_rows) {
clock_t start_t = clock();
const int tuple_limit = 1000000;
size_t ntuples;
size_t total_tuples = 0;
Column_info_t info[3];
int i;
for (i = 0; i < 3; ++i) {
info[i].colNumber = -1;
info[i].type = 0;
info[i].strict = true;
info[i].eType = ANY_INTEGER;
}
info[0].name = strdup("start_vid");
info[1].name = strdup("end_vid");
info[2].name = strdup("agg_cost");
info[2].eType = ANY_NUMERICAL;
void *SPIplan;
SPIplan = pgr_SPI_prepare(sql);
Portal SPIportal;
SPIportal = pgr_SPI_cursor_open(SPIplan);
bool moredata = TRUE;
(*total_rows) = total_tuples;
while (moredata == TRUE) {
SPI_cursor_fetch(SPIportal, TRUE, tuple_limit);
if (total_tuples == 0)
pgr_fetch_column_info(info, 3);
ntuples = SPI_processed;
total_tuples += ntuples;
if (ntuples > 0) {
if ((*rows) == NULL)
(*rows) = (Matrix_cell_t *)palloc0(
total_tuples * sizeof(Matrix_cell_t));
else
(*rows) = (Matrix_cell_t *)repalloc(
(*rows), total_tuples * sizeof(Matrix_cell_t));
if ((*rows) == NULL) {
elog(ERROR, "Out of memory");
}
SPITupleTable *tuptable = SPI_tuptable;
TupleDesc tupdesc = SPI_tuptable->tupdesc;
PGR_DBG("processing %ld edge tupĺes", ntuples);
size_t t;
for (t = 0; t < ntuples; t++) {
HeapTuple tuple = tuptable->vals[t];
pgr_fetch_row(&tuple, &tupdesc, info,
&(*rows)[total_tuples - ntuples + t]);
}
SPI_freetuptable(tuptable);
} else {
moredata = FALSE;
}
}
SPI_cursor_close(SPIportal);
if (total_tuples == 0) {
(*total_rows) = 0;
PGR_DBG("NO rows");
return;
}
(*total_rows) = total_tuples;
time_msg(" reading Edges", start_t, clock());
}
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 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);
}
}
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;
}
SV *
plperl_spi_fetchrow(char *cursor)
{
SV *row;
/*
* Execute the FETCH 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();
{
Portal p = SPI_cursor_find(cursor);
if (!p)
row = newSV(0);
else
{
SPI_cursor_fetch(p, true, 1);
if (SPI_processed == 0)
{
SPI_cursor_close(p);
row = newSV(0);
}
else
{
row = plperl_hash_from_tuple(SPI_tuptable->vals[0],
SPI_tuptable->tupdesc);
}
SPI_freetuptable(SPI_tuptable);
}
/* 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 row;
}
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);
}
}
