Beispiel #1
0
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));
}
Beispiel #2
0
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);
}
Beispiel #3
0
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;
}
Beispiel #4
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;
}
Beispiel #5
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;
}
Beispiel #6
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);
}
Beispiel #7
0
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);
	}
}