Datum
check_SPI_gettype(PG_FUNCTION_ARGS)
{
int fnumber = PG_GETARG_INT32(0);
Relation rel = relation_open(RelationRelationId, AccessShareLock);
char *name = SPI_gettype(RelationGetDescr(rel), fnumber);
relation_close(rel, AccessShareLock);
PG_RETURN_TEXT_P(cstring_to_text(name));
}
Datum
check_foreign_key(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
Trigger *trigger; /* to get trigger name */
int nargs; /* # of args specified in CREATE TRIGGER */
char **args; /* arguments: as described above */
char **args_temp;
int nrefs; /* number of references (== # of plans) */
char action; /* 'R'estrict | 'S'etnull | 'C'ascade */
int nkeys; /* # of key columns */
Datum *kvals; /* key values */
char *relname; /* referencing relation name */
Relation rel; /* triggered relation */
HeapTuple trigtuple = NULL; /* tuple to being changed */
HeapTuple newtuple = NULL; /* tuple to return */
TupleDesc tupdesc; /* tuple description */
EPlan *plan; /* prepared plan(s) */
Oid *argtypes = NULL; /* key types to prepare execution plan */
bool isnull; /* to know is some column NULL or not */
bool isequal = true; /* are keys in both tuples equal (in UPDATE) */
char ident[2 * NAMEDATALEN]; /* to identify myself */
int is_update = 0;
int ret;
int i,
r;
#ifdef DEBUG_QUERY
elog(DEBUG4, "check_foreign_key: Enter Function");
#endif
/*
* Some checks first...
*/
/* Called by trigger manager ? */
if (!CALLED_AS_TRIGGER(fcinfo))
/* internal error */
elog(ERROR, "check_foreign_key: not fired by trigger manager");
/* Should be called for ROW trigger */
if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
/* internal error */
elog(ERROR, "check_foreign_key: must be fired for row");
/* Not should be called for INSERT */
if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
/* internal error */
elog(ERROR, "check_foreign_key: cannot process INSERT events");
/* Have to check tg_trigtuple - tuple being deleted */
trigtuple = trigdata->tg_trigtuple;
/*
* But if this is UPDATE then we have to return tg_newtuple. Also, if key
* in tg_newtuple is the same as in tg_trigtuple then nothing to do.
*/
is_update = 0;
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
{
newtuple = trigdata->tg_newtuple;
is_update = 1;
}
trigger = trigdata->tg_trigger;
nargs = trigger->tgnargs;
args = trigger->tgargs;
if (nargs < 5) /* nrefs, action, key, Relation, key - at
* least */
/* internal error */
elog(ERROR, "check_foreign_key: too short %d (< 5) list of arguments", nargs);
nrefs = pg_atoi(args[0], sizeof(int), 0);
if (nrefs < 1)
/* internal error */
elog(ERROR, "check_foreign_key: %d (< 1) number of references specified", nrefs);
action = tolower((unsigned char) *(args[1]));
if (action != 'r' && action != 'c' && action != 's')
/* internal error */
elog(ERROR, "check_foreign_key: invalid action %s", args[1]);
nargs -= 2;
args += 2;
nkeys = (nargs - nrefs) / (nrefs + 1);
if (nkeys <= 0 || nargs != (nrefs + nkeys * (nrefs + 1)))
/* internal error */
elog(ERROR, "check_foreign_key: invalid number of arguments %d for %d references",
nargs + 2, nrefs);
rel = trigdata->tg_relation;
tupdesc = rel->rd_att;
/* Connect to SPI manager */
if ((ret = SPI_connect()) < 0)
/* internal error */
elog(ERROR, "check_foreign_key: SPI_connect returned %d", ret);
/*
* We use SPI plan preparation feature, so allocate space to place key
* values.
*/
kvals = (Datum *) palloc(nkeys * sizeof(Datum));
/*
* Construct ident string as TriggerName $ TriggeredRelationId and try to
* find prepared execution plan(s).
*/
snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
plan = find_plan(ident, &FPlans, &nFPlans);
/* if there is no plan(s) then allocate argtypes for preparation */
if (plan->nplans <= 0)
argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
/*
* else - check that we have exactly nrefs plan(s) ready
*/
else if (plan->nplans != nrefs)
/* internal error */
elog(ERROR, "%s: check_foreign_key: # of plans changed in meantime",
trigger->tgname);
/* For each column in key ... */
for (i = 0; i < nkeys; i++)
{
/* get index of column in tuple */
int fnumber = SPI_fnumber(tupdesc, args[i]);
/* Bad guys may give us un-existing column in CREATE TRIGGER */
if (fnumber < 0)
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_COLUMN),
errmsg("there is no attribute \"%s\" in relation \"%s\"",
args[i], SPI_getrelname(rel))));
/* Well, get binary (in internal format) value of column */
kvals[i] = SPI_getbinval(trigtuple, tupdesc, fnumber, &isnull);
/*
* If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
* DON'T FORGET return tuple! Executor inserts tuple you're returning!
* If you return NULL then nothing will be inserted!
*/
if (isnull)
{
SPI_finish();
return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
}
/*
* If UPDATE then get column value from new tuple being inserted and
* compare is this the same as old one. For the moment we use string
* presentation of values...
*/
if (newtuple != NULL)
{
char *oldval = SPI_getvalue(trigtuple, tupdesc, fnumber);
char *newval;
/* this shouldn't happen! SPI_ERROR_NOOUTFUNC ? */
if (oldval == NULL)
/* internal error */
elog(ERROR, "check_foreign_key: SPI_getvalue returned %d", SPI_result);
newval = SPI_getvalue(newtuple, tupdesc, fnumber);
if (newval == NULL || strcmp(oldval, newval) != 0)
isequal = false;
}
if (plan->nplans <= 0) /* Get typeId of column */
argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
}
args_temp = args;
nargs -= nkeys;
args += nkeys;
/*
* If we have to prepare plans ...
*/
if (plan->nplans <= 0)
{
SPIPlanPtr pplan;
char sql[8192];
char **args2 = args;
plan->splan = (SPIPlanPtr *) malloc(nrefs * sizeof(SPIPlanPtr));
for (r = 0; r < nrefs; r++)
{
relname = args2[0];
/*---------
* For 'R'estrict action we construct SELECT query:
*
* SELECT 1
* FROM _referencing_relation_
* WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
*
* to check is tuple referenced or not.
*---------
*/
if (action == 'r')
snprintf(sql, sizeof(sql), "select 1 from %s where ", relname);
/*---------
* For 'C'ascade action we construct DELETE query
*
* DELETE
* FROM _referencing_relation_
* WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
*
* to delete all referencing tuples.
*---------
*/
/*
* Max : Cascade with UPDATE query i create update query that
* updates new key values in referenced tables
*/
else if (action == 'c')
{
if (is_update == 1)
{
int fn;
char *nv;
int k;
snprintf(sql, sizeof(sql), "update %s set ", relname);
for (k = 1; k <= nkeys; k++)
{
int is_char_type = 0;
char *type;
fn = SPI_fnumber(tupdesc, args_temp[k - 1]);
nv = SPI_getvalue(newtuple, tupdesc, fn);
type = SPI_gettype(tupdesc, fn);
if ((strcmp(type, "text") && strcmp(type, "varchar") &&
strcmp(type, "char") && strcmp(type, "bpchar") &&
strcmp(type, "date") && strcmp(type, "timestamp")) == 0)
is_char_type = 1;
#ifdef DEBUG_QUERY
elog(DEBUG4, "check_foreign_key Debug value %s type %s %d",
nv, type, is_char_type);
#endif
/*
* is_char_type =1 i set ' ' for define a new value
*/
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql),
" %s = %s%s%s %s ",
args2[k], (is_char_type > 0) ? "'" : "",
nv, (is_char_type > 0) ? "'" : "", (k < nkeys) ? ", " : "");
is_char_type = 0;
}
strcat(sql, " where ");
}
else
/* DELETE */
snprintf(sql, sizeof(sql), "delete from %s where ", relname);
}
/*
* For 'S'etnull action we construct UPDATE query - UPDATE
* _referencing_relation_ SET Fkey1 null [, Fkey2 null [...]]
* WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]] - to set key columns in
* all referencing tuples to NULL.
*/
else if (action == 's')
{
snprintf(sql, sizeof(sql), "update %s set ", relname);
for (i = 1; i <= nkeys; i++)
{
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql),
"%s = null%s",
args2[i], (i < nkeys) ? ", " : "");
}
strcat(sql, " where ");
}
/* Construct WHERE qual */
for (i = 1; i <= nkeys; i++)
{
snprintf(sql + strlen(sql), sizeof(sql) - strlen(sql), "%s = $%d %s",
args2[i], i, (i < nkeys) ? "and " : "");
}
/* Prepare plan for query */
pplan = SPI_prepare(sql, nkeys, argtypes);
if (pplan == NULL)
/* internal error */
elog(ERROR, "check_foreign_key: SPI_prepare returned %d", SPI_result);
/*
* Remember that SPI_prepare places plan in current memory context
* - so, we have to save plan in Top memory context for later use.
*/
if (SPI_keepplan(pplan))
/* internal error */
elog(ERROR, "check_foreign_key: SPI_keepplan failed");
plan->splan[r] = pplan;
args2 += nkeys + 1; /* to the next relation */
}
plan->nplans = nrefs;
#ifdef DEBUG_QUERY
elog(DEBUG4, "check_foreign_key Debug Query is : %s ", sql);
#endif
}
/*
* If UPDATE and key is not changed ...
*/
if (newtuple != NULL && isequal)
{
SPI_finish();
return PointerGetDatum(newtuple);
}
/*
* Ok, execute prepared plan(s).
*/
for (r = 0; r < nrefs; r++)
{
/*
* For 'R'estrict we may to execute plan for one tuple only, for other
* actions - for all tuples.
*/
int tcount = (action == 'r') ? 1 : 0;
relname = args[0];
snprintf(ident, sizeof(ident), "%s$%u", trigger->tgname, rel->rd_id);
plan = find_plan(ident, &FPlans, &nFPlans);
ret = SPI_execp(plan->splan[r], kvals, NULL, tcount);
/* we have no NULLs - so we pass ^^^^ here */
if (ret < 0)
ereport(ERROR,
(errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
errmsg("SPI_execp returned %d", ret)));
/* If action is 'R'estrict ... */
if (action == 'r')
{
/* If there is tuple returned by SELECT then ... */
if (SPI_processed > 0)
ereport(ERROR,
(errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
errmsg("\"%s\": tuple is referenced in \"%s\"",
trigger->tgname, relname)));
}
else
{
#ifdef REFINT_VERBOSE
elog(NOTICE, "%s: " UINT64_FORMAT " tuple(s) of %s are %s",
trigger->tgname, SPI_processed, relname,
(action == 'c') ? "deleted" : "set to null");
#endif
}
args += nkeys + 1; /* to the next relation */
}
SPI_finish();
return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
}
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;
}
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;
}
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;
}
Datum
funny_dup17(PG_FUNCTION_ARGS)
{
TriggerData *trigdata = (TriggerData *) fcinfo->context;
TransactionId *xid;
int *level;
bool *recursion;
Relation rel;
TupleDesc tupdesc;
HeapTuple tuple;
char *query,
*fieldval,
*fieldtype;
char *when;
int inserted;
int selected = 0;
int ret;
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "funny_dup17: not fired by trigger manager");
tuple = trigdata->tg_trigtuple;
rel = trigdata->tg_relation;
tupdesc = rel->rd_att;
if (TRIGGER_FIRED_BEFORE(trigdata->tg_event))
{
xid = &fd17b_xid;
level = &fd17b_level;
recursion = &fd17b_recursion;
when = "BEFORE";
}
else
{
xid = &fd17a_xid;
level = &fd17a_level;
recursion = &fd17a_recursion;
when = "AFTER ";
}
if (!TransactionIdIsCurrentTransactionId(*xid))
{
*xid = GetCurrentTransactionId();
*level = 0;
*recursion = true;
}
if (*level == 17)
{
*recursion = false;
return PointerGetDatum(tuple);
}
if (!(*recursion))
return PointerGetDatum(tuple);
(*level)++;
SPI_connect();
fieldval = SPI_getvalue(tuple, tupdesc, 1);
fieldtype = SPI_gettype(tupdesc, 1);
query = (char *) palloc(100 + NAMEDATALEN * 3 +
strlen(fieldval) + strlen(fieldtype));
sprintf(query, "insert into %s select * from %s where %s = '%s'::%s",
SPI_getrelname(rel), SPI_getrelname(rel),
SPI_fname(tupdesc, 1),
fieldval, fieldtype);
if ((ret = SPI_exec(query, 0)) < 0)
elog(ERROR, "funny_dup17 (fired %s) on level %3d: SPI_exec (insert ...) returned %d",
when, *level, ret);
inserted = SPI_processed;
sprintf(query, "select count (*) from %s where %s = '%s'::%s",
SPI_getrelname(rel),
SPI_fname(tupdesc, 1),
fieldval, fieldtype);
if ((ret = SPI_exec(query, 0)) < 0)
elog(ERROR, "funny_dup17 (fired %s) on level %3d: SPI_exec (select ...) returned %d",
when, *level, ret);
if (SPI_processed > 0)
{
selected = DatumGetInt32(DirectFunctionCall1(int4in,
CStringGetDatum(SPI_getvalue(
SPI_tuptable->vals[0],
SPI_tuptable->tupdesc,
1
))));
}
elog(DEBUG4, "funny_dup17 (fired %s) on level %3d: %d/%d tuples inserted/selected",
when, *level, inserted, selected);
SPI_finish();
(*level)--;
if (*level == 0)
*xid = InvalidTransactionId;
return PointerGetDatum(tuple);
}
Datum tkd_query(PG_FUNCTION_ARGS){
char *command;
int i, j;
int ret, curdm; // return value, current dimetion
int call_cntr;
int max_calls;
int *retarr;
FuncCallContext *funcctx; // context switch variable
AttInMetadata *attinmeta; // not known------------------------
TupleDesc tupdesc; // tuple descriptor, for getting data
D = 0;
/*
* this section will be executed only for the first call of function
* connect to postgres server and execute the first command and get data
*/
if(SRF_IS_FIRSTCALL()){
MemoryContext oldcontext;
/*
* 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);
/*
* get arguments, convert given text object to a C string that can be used by server
* */
command = text_to_cstring(PG_GETARG_TEXT_P(0));
/*
* arguments error
* */
if(!PG_ARGISNULL(1))
K = PG_GETARG_INT32(1);
else
K = 1;
if(!PG_ARGISNULL(2))
dominating_type = PG_GETARG_INT32(2);
else
dominating_type = 0;
SPI_connect(); // open internal connection to database
ret = SPI_exec(command, 0); // run the SQL command, 0 for no limit of returned row number
N = SPI_processed; // save the number of rows
dataset = (Dataset *)palloc(sizeof(Dataset)*(N+2));
candidateset = (int *)palloc(sizeof(int)*(N+2));
if(dataset == NULL){
exit(1);
}
/*
* some rows are fetched
* */
if(ret > 0 && SPI_tuptable != NULL){
TupleDesc tupdesc;
SPITupleTable *tuptable;
HeapTuple tuple;
char *type_name;
/*
* get tuple description
* */
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
/*
* for each colum, check type
* */
for(i = 1; i <= tupdesc->natts; ++i){
type_name = SPI_gettype(tupdesc, i);// get type of data
if(strcmp(type_name,"int4") == 0 || strcmp(type_name,"int2") ==0 )//add float4 or flat8 types if want (2)
++D;
}
/*
* for each tuple
* and palloc all memory needed
* */
kesai = (int *)palloc(sizeof(int)*(N+2));
lbound = (int *)palloc(sizeof(int)*(N+2));
ubound = (int *)palloc(sizeof(int)*(N+2));
//ari = (int *)palloc(sizeof(int)*(N+2));
goods = (int *)palloc(sizeof(int)*(N+2));
goodv = (int *)palloc(sizeof(int)*(N+2));
Q = (int *)palloc(sizeof(int)*(N+2));
P = (int *)palloc(sizeof(int)*(N+2));
nonD = (int *)palloc(sizeof(int)*(N+2));
whichbin = (int *)palloc(sizeof(int)*(N+2));
incomparable = (int *)palloc(sizeof(int)*(N+2));
tagT = (int *)palloc(sizeof(int)*(N+2));
Pi = (int **)palloc(sizeof(int *)*(N+2));
Qi = (int **)palloc(sizeof(int *)*(N+2));
for(i = 0; i < N; ++i){
Pi[i] = (int *)palloc(sizeof(int)*(N+2));
Qi[i] = (int *)palloc(sizeof(int)*(N+2));
}
arr = (int **)palloc(sizeof(int)*(D+2));
score = (int *)palloc(sizeof(int)*(N+2));
queue = (int *)palloc(sizeof(int)*(N+2));
missd = (int *)palloc(sizeof(int)*(D+2));
maxscore = (int *)palloc(sizeof(int)*(N+2));
for(i = 0; i < N; ++i){
dataset[i].missing = (int *)palloc(sizeof(int)*(D+2));
dataset[i].value = (int *)palloc(sizeof(int)*(D+2));
dataset[i].T = (int *)palloc(sizeof(int)*(D+2));
if(dataset[i].missing == NULL || dataset[i].value == NULL || dataset[i].T == NULL){
exit(1);
}
curdm = 0;
tuple = tuptable->vals[i]; // get the ith tuple
/*
* for each dimention of a tuple
* */
for(j = 1; j <= tupdesc->natts; ++j) {
type_name = SPI_gettype(tupdesc, j);
if(strcmp(type_name,"int4") == 0 || strcmp(type_name,"int2") == 0 ){
if(SPI_getvalue(tuple, tupdesc, j) == NULL) { // value is missing
dataset[i].missing[curdm] = 1;
/*if(dominating_type == 0)
dataset[i].value[curdm] = T1MISS;
else
dataset[i].value[curdm] = T2MISS;*/
}
else{ // value is not missing
dataset[i].missing[curdm] = 0;
dataset[i].value[curdm] = atof(SPI_getvalue(tuple, tupdesc, j));
}
++curdm;
}
}
}
}
pfree(command);
tkd_exec(); // call to execute tkd query
funcctx->max_calls = K;
/*
* allocate local variable retstruct and store the result tuple init
* */
retarr = (int *)palloc(sizeof(int)*(K+2));
if(retarr == NULL){
exit(1);
}
for(i = 0; i < K; ++i )
retarr[i] = candidateset[i+1];
funcctx->user_fctx = retarr;
if(get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("function returning record called in context that cannot accept type record")));
/* Generate attribute metadata needed later to produce tuples from raw C strings */
attinmeta = TupleDescGetAttInMetadata(tupdesc);
funcctx->attinmeta = attinmeta;
/* MemoryContext switch to old context */
MemoryContextSwitchTo(oldcontext);
}
funcctx = SRF_PERCALL_SETUP();
call_cntr = funcctx->call_cntr;
max_calls = funcctx->max_calls;
attinmeta = funcctx->attinmeta;
retarr = funcctx->user_fctx;
if(call_cntr < max_calls){
char **values;
HeapTuple tuple;
HeapTuple ret_tuple;
Datum result;
TupleDesc tupdesc;
SPITupleTable *tuptable;
tupdesc = SPI_tuptable->tupdesc;
tuptable = SPI_tuptable;
/*
* Prepare a values array for building the returned tuple.
* This should be an array of C strings which will
* be processed later by the type input functions.
*/
values = (char **)palloc((tupdesc->natts+2) * sizeof(char *));
values = (char **)palloc((tupdesc->natts+2) * sizeof(char *));
if(values == NULL){
exit(1);
}
for(i = 0; i < tupdesc->natts; ++i ){
tuple = tuptable->vals[retarr[call_cntr]];
values[i] = (SPI_getvalue(tuple, tupdesc, i+1));
}
ret_tuple = BuildTupleFromCStrings(attinmeta, values); // build a return tuple
result = HeapTupleGetDatum(ret_tuple); // make the tuple into a datum
SRF_RETURN_NEXT(funcctx,result);
}
else{
SPI_finish();
SRF_RETURN_DONE(funcctx);
}
}