int main(void)
{
	char hostname[STRSIZE], port[STRSIZE], dbname[STRSIZE], login[STRSIZE], password[STRSIZE], tablename[STRSIZE], query[STRSIZE*2];
	int noffields, noftuples, i, j;
	PGconn *conn;
	PGresult *exres;

#ifdef WIN32
	system("chcp 65001"); // Устанавливает кодовую страницу utf-8 (для вывода русских символов)
	//setlocale(LC_ALL, ".65001");
#endif

	EnterConnectionInfo(hostname, port, dbname, login, password);
	
	conn = PQsetdbLogin(hostname, port, NULL, NULL, dbname, login, password);
	
	if(PQstatus(conn) == CONNECTION_BAD) {
		printf("connection error\n");
		return 1;
	}

	printf("enter table name: ");
	fgets(tablename, STRSIZE, stdin); tablename[STRSIZE-1] = 0;
	if(strlen(tablename) > 0)
		tablename[strlen(tablename)-1] = 0;

	strcpy(query, "SELECT * FROM ");
	strcat(query, tablename);

	exres = PQexec(conn, query);

	switch(PQresultStatus(exres)) {
		case PGRES_TUPLES_OK:
			break;
		case PGRES_FATAL_ERROR:
			printf("can\'t read table \'%s\'\n", tablename);
			
			goto EXIT;
		case PGRES_EMPTY_QUERY:
			printf("Something goes wrong^_^\'\n");

			goto EXIT;
		case PGRES_COMMAND_OK:
			printf("Table is empty\n");

			goto EXIT;
	}

	//PGRES_TUPLES_OK
	noffields = PQnfields(exres);
	noftuples = PQntuples(exres);
	printf("Table contains %d tuples(rows) with %d fields(columns)\n", noftuples, noffields);
	// Рисуем заголовок
	for(j = 0; j < noffields; j++)
		printf("|%-18s|", PQfname(exres, j));
	printf("\n");
	for(j = 0; j < noffields; j++)
		printf("|__________________|");
	printf("\n");
	// Рисуем остальную таблицу
	for(i = 0; i < noftuples; i++) {
		for(j = 0; j < noffields; j++) {
			printf("|%-18s|", PQgetvalue(exres, i, j));
		}
		printf("\n");
	}
	
EXIT:
	PQclear(exres);
	PQfinish(conn);
	
	return 0;
}
Example #2
0
void gpdb_get_hostlist(int* hostcnt, host_t** host_table, apr_pool_t* global_pool, mmon_options_t* opt)
{
	apr_pool_t* pool;
	PGconn* conn = 0;
	PGresult* result = 0;
	int rowcount, i;
	unsigned int unique_hosts = 0;
	apr_hash_t* htab;
	struct hostinfo_holder_t* hostinfo_holder = NULL;
	host_t* hosts = NULL;
	int e;

	// 0 -- hostname, 1 -- address, 2 -- datadir, 3 -- is_master,
	const char *QUERY = "SELECT distinct hostname, address, case when content < 0 then 1 else 0 end as is_master, MAX(fselocation) as datadir FROM pg_filespace_entry "
			    "JOIN gp_segment_configuration on (dbid = fsedbid) WHERE fsefsoid = (select oid from pg_filespace where fsname='pg_system') "
		  	    "GROUP BY (hostname, address, is_master) order by hostname";

	if (0 != (e = apr_pool_create_alloc(&pool, NULL)))
	{
		gpmon_fatalx(FLINE, e, "apr_pool_create_alloc failed");
	}

	const char* errmsg = gpdb_exec(&conn, &result, QUERY);

	TR2((QUERY));
	TR2(("\n"));

	if (errmsg)
	{
		gpmon_warning(FLINE, "GPDB error %s\n\tquery: %s\n", errmsg, QUERY);
	}
	else
	{
		// hash of hostnames to addresses
		htab = apr_hash_make(pool);

		rowcount = PQntuples(result);

		for (i = 0; i < rowcount; i++)
		{
			char* curr_hostname = PQgetvalue(result, i, 0);

			hostinfo_holder = apr_hash_get(htab, curr_hostname, APR_HASH_KEY_STRING);

			if (!hostinfo_holder)
			{
				hostinfo_holder = apr_pcalloc(pool, sizeof(struct hostinfo_holder_t));
				CHECKMEM(hostinfo_holder);

				apr_hash_set(htab, curr_hostname, APR_HASH_KEY_STRING, hostinfo_holder);

				hostinfo_holder->hostname = curr_hostname;
				hostinfo_holder->is_master = atoi(PQgetvalue(result, i, 2));
				hostinfo_holder->datadir = PQgetvalue(result, i, 3);

				// use permenant memory for address list -- stored for duration

				// populate 1st on list and save to head and tail
				hostinfo_holder->addressinfo_head = hostinfo_holder->addressinfo_tail = calloc(1, sizeof(addressinfo_holder_t));
				CHECKMEM(hostinfo_holder->addressinfo_tail);

				// first is the hostname
				hostinfo_holder->addressinfo_tail->address = strdup(hostinfo_holder->hostname);
				CHECKMEM(hostinfo_holder->addressinfo_tail->address);


				// add a 2nd to the list
				hostinfo_holder->addressinfo_tail->next = calloc(1, sizeof(addressinfo_holder_t));
				CHECKMEM(hostinfo_holder->addressinfo_tail);
				hostinfo_holder->addressinfo_tail = hostinfo_holder->addressinfo_tail->next;

				// second is address
				hostinfo_holder->addressinfo_tail->address = strdup(PQgetvalue(result, i, 1));
				CHECKMEM(hostinfo_holder->addressinfo_tail->address);

				// one for hostname one for address
				hostinfo_holder->address_count = 2;
			}
			else
			{
				// permenant memory for address list -- stored for duration
				hostinfo_holder->addressinfo_tail->next = calloc(1, sizeof(addressinfo_holder_t));
				CHECKMEM(hostinfo_holder->addressinfo_tail);

				hostinfo_holder->addressinfo_tail = hostinfo_holder->addressinfo_tail->next;

				// permenant memory for address list -- stored for duration
				hostinfo_holder->addressinfo_tail->address = strdup(PQgetvalue(result, i, 1));
				CHECKMEM(hostinfo_holder->addressinfo_tail->address);

				hostinfo_holder->address_count++;
			}

		}

		// if we have any appliance specific hosts such as hadoop nodes add them to the hash table
		if (get_appliance_hosts_and_add_to_hosts(pool, htab))
		{
			TR0(("Not an appliance: checking for SW Only hadoop hosts.\n"));
			get_hadoop_hosts_and_add_to_hosts(pool, htab, opt); // Not an appliance, so check for SW only hadoop nodes.
		}

		unique_hosts = apr_hash_count(htab);

		// allocate memory for host list (not freed ever)
		hosts = calloc(unique_hosts, sizeof(host_t));

		apr_hash_index_t* hi;
		void* vptr;
		int hostcounter = 0;
		for (hi = apr_hash_first(0, htab); hi; hi = apr_hash_next(hi))
		{
			// sanity check
			if (hostcounter >= unique_hosts)
			{
				gpmon_fatalx(FLINE, 0, "host counter exceeds unique hosts");
			}

			apr_hash_this(hi, 0, 0, &vptr);
			hostinfo_holder = vptr;

			hosts[hostcounter].hostname = strdup(hostinfo_holder->hostname);
			hosts[hostcounter].data_dir = strdup(hostinfo_holder->datadir);
			if (hostinfo_holder->smon_dir)
			{
				hosts[hostcounter].smon_bin_location = strdup(hostinfo_holder->smon_dir);
			}
			hosts[hostcounter].is_master = hostinfo_holder->is_master;
			hosts[hostcounter].addressinfo_head = hostinfo_holder->addressinfo_head;
			hosts[hostcounter].addressinfo_tail = hostinfo_holder->addressinfo_tail;
			hosts[hostcounter].address_count = hostinfo_holder->address_count;
			hosts[hostcounter].connection_hostname.current = hosts[hostcounter].addressinfo_head;
			hosts[hostcounter].snmp_hostname.current = hosts[hostcounter].addressinfo_head;

			if (hostinfo_holder->is_hdm)
				hosts[hostcounter].is_hdm = 1;

			if (hostinfo_holder->is_hdw)
				hosts[hostcounter].is_hdw = 1;

			if (hostinfo_holder->is_etl)
				hosts[hostcounter].is_etl = 1;

			if (hostinfo_holder->is_hbw)
				hosts[hostcounter].is_hbw = 1;

			if (hostinfo_holder->is_hdc)
				hosts[hostcounter].is_hdc = 1;

			apr_thread_mutex_create(&hosts[hostcounter].mutex, APR_THREAD_MUTEX_UNNESTED, global_pool); // use the global pool so the mutexes last beyond this function

			hostcounter++;
		}

		*hostcnt = hostcounter;
	}

	apr_pool_destroy(pool);
	PQclear(result);
	PQfinish(conn);

	if (!hosts || *hostcnt < 1)
	{
		gpmon_fatalx(FLINE, 0, "no valid hosts found");
	}

	*host_table = hosts;
}
Example #3
0
/****************************************************
 initBuckets

 Initialize the bucket definition list
 If an error occured, write the error to stdout

 @param PGconn *pgConn  Database connection object
 @param int bucketpool_pk
 @param cacheroot_t *pcroot  license cache root

 @return an array of bucket definitions (in eval order)
 or 0 if error.
****************************************************/
FUNCTION pbucketdef_t initBuckets(PGconn *pgConn, int bucketpool_pk, cacheroot_t *pcroot)
{
  char *fcnName = "initBuckets";
  char sqlbuf[256];
  char filepath[256];
  char hostname[256];
  PGresult *result;
  pbucketdef_t bucketDefList = 0;
  int  numRows, rowNum;
  int  rv, numErrors=0;
  struct stat statbuf;

  /* reasonable input validation  */
  if ((!pgConn) || (!bucketpool_pk)) 
  {
    printf("ERROR: %s.%s.%d Invalid input pgConn: %lx, bucketpool_pk: %d.\n",
            __FILE__, fcnName, __LINE__, (unsigned long)pgConn, bucketpool_pk);
    return 0;
  }

  /* get bucket defs from db */
  sprintf(sqlbuf, "select bucket_pk, bucket_type, bucket_regex, bucket_filename, stopon, bucket_name, applies_to from bucket_def where bucketpool_fk=%d order by bucket_evalorder asc", bucketpool_pk);
  result = PQexec(pgConn, sqlbuf);
  if (checkPQresult(pgConn, result, sqlbuf, fcnName, __LINE__)) return 0;
  numRows = PQntuples(result);
  if (numRows == 0) /* no bucket recs for pool?  return error */
  {
    printf("ERROR: %s.%s.%d No bucket defs for pool %d.\n",
            __FILE__, fcnName, __LINE__, bucketpool_pk);
    PQclear(result);
    return 0;
  }

  bucketDefList = calloc(numRows+1, sizeof(bucketdef_t));
  if (bucketDefList == 0)
  {
    printf("ERROR: %s.%s.%d No memory to allocate %d bucket defs.\n",
            __FILE__, fcnName, __LINE__, numRows);
    return 0;
  }

  /* put each db bucket def into bucketDefList in eval order */
  for (rowNum=0; rowNum<numRows; rowNum++)
  {
    bucketDefList[rowNum].bucket_pk = atoi(PQgetvalue(result, rowNum, 0));
    bucketDefList[rowNum].bucket_type = atoi(PQgetvalue(result, rowNum, 1));
    bucketDefList[rowNum].bucketpool_pk = bucketpool_pk;

    /* compile regex if type 3 (REGEX) */
    if (bucketDefList[rowNum].bucket_type == 3)
    {
      rv = regcomp(&bucketDefList[rowNum].compRegex, PQgetvalue(result, rowNum, 2), 
                   REG_NOSUB | REG_ICASE | REG_EXTENDED);
      if (rv != 0)
      {
        printf("ERROR: %s.%s.%d Invalid regular expression for bucketpool_pk: %d, bucket: %s\n",
               __FILE__, fcnName, __LINE__, bucketpool_pk, PQgetvalue(result, rowNum, 5));
        numErrors++;
      }
      bucketDefList[rowNum].regex = strdup(PQgetvalue(result, rowNum, 2));
    }

    bucketDefList[rowNum].dataFilename = strdup(PQgetvalue(result, rowNum, 3));

    /* verify that external file dataFilename exists */
    if (strlen(bucketDefList[rowNum].dataFilename) > 0)
    {
      snprintf(filepath, sizeof(filepath), "%s/bucketpools/%d/%s",
        PROJECTSTATEDIR, bucketpool_pk, bucketDefList[rowNum].dataFilename);
      if (stat(filepath, &statbuf) == -1)
      {
        hostname[0] = 0;
        gethostname(hostname, sizeof(hostname));
        printf("ERROR: %s.%s.%d File: %s is missing on host: %s.  bucketpool_pk: %d, bucket: %s\n",
               __FILE__, fcnName, __LINE__, filepath, hostname, bucketpool_pk, PQgetvalue(result, rowNum, 5));
        numErrors++;
      }
    }

    /* MATCH_EVERY */
    if (bucketDefList[rowNum].bucket_type == 1)
      bucketDefList[rowNum].match_every = getMatchEvery(pgConn, bucketpool_pk, bucketDefList[rowNum].dataFilename, pcroot);

    /* MATCH_ONLY */
    if (bucketDefList[rowNum].bucket_type == 2)
    {
      bucketDefList[rowNum].match_only = getMatchOnly(pgConn, bucketpool_pk, bucketDefList[rowNum].dataFilename, pcroot);
    }

    /* REGEX-FILE */
    if (bucketDefList[rowNum].bucket_type == 5)
    {
      bucketDefList[rowNum].regex_row = getRegexFile(pgConn, bucketpool_pk, bucketDefList[rowNum].dataFilename, pcroot);
    }

    bucketDefList[rowNum].stopon = *PQgetvalue(result, rowNum, 4);
    bucketDefList[rowNum].bucket_name = strdup(PQgetvalue(result, rowNum, 5));
    bucketDefList[rowNum].applies_to = *PQgetvalue(result, rowNum, 6);
  }
  PQclear(result);
  if (numErrors) return 0;

  if (debug)
  {
    for (rowNum=0; rowNum<numRows; rowNum++)
    {
      printf("\nbucket_pk[%d] = %d\n", rowNum, bucketDefList[rowNum].bucket_pk);
      printf("bucket_name[%d] = %s\n", rowNum, bucketDefList[rowNum].bucket_name);
      printf("bucket_type[%d] = %d\n", rowNum, bucketDefList[rowNum].bucket_type);
      printf("dataFilename[%d] = %s\n", rowNum, bucketDefList[rowNum].dataFilename);
      printf("stopon[%d] = %c\n", rowNum, bucketDefList[rowNum].stopon);
      printf("applies_to[%d] = %c\n", rowNum, bucketDefList[rowNum].applies_to);
      printf("nomos_agent_pk[%d] = %d\n", rowNum, bucketDefList[rowNum].nomos_agent_pk);
      printf("bucket_agent_pk[%d] = %d\n", rowNum, bucketDefList[rowNum].bucket_agent_pk);
      printf("regex[%d] = %s\n", rowNum, bucketDefList[rowNum].regex);
    }
  }

  return bucketDefList;
}
Example #4
0
/*
 *
 * main
 *
 */
int
main(int argc, char *argv[])
{
	struct adhoc_opts options;
	int			successResult;
	char	   *password = NULL;
	char	   *password_prompt = NULL;
	bool		new_pass;

	set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("psql"));

	if (argc > 1)
	{
		if ((strcmp(argv[1], "-?") == 0) || (argc == 2 && (strcmp(argv[1], "--help") == 0)))
		{
			usage(NOPAGER);
			exit(EXIT_SUCCESS);
		}
		if (strcmp(argv[1], "--version") == 0 || strcmp(argv[1], "-V") == 0)
		{
			showVersion();
			exit(EXIT_SUCCESS);
		}
	}

#ifdef WIN32
	setvbuf(stderr, NULL, _IONBF, 0);
#endif

	pset.progname = get_progname(argv[0]);

	pset.db = NULL;
	setDecimalLocale();
	pset.encoding = PQenv2encoding();
	pset.queryFout = stdout;
	pset.queryFoutPipe = false;
	pset.copyStream = NULL;
	pset.last_error_result = NULL;
	pset.cur_cmd_source = stdin;
	pset.cur_cmd_interactive = false;

	/* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */
	pset.popt.topt.format = PRINT_ALIGNED;
	pset.popt.topt.border = 1;
	pset.popt.topt.pager = 1;
	pset.popt.topt.pager_min_lines = 0;
	pset.popt.topt.start_table = true;
	pset.popt.topt.stop_table = true;
	pset.popt.topt.default_footer = true;

	pset.popt.topt.unicode_border_linestyle = UNICODE_LINESTYLE_SINGLE;
	pset.popt.topt.unicode_column_linestyle = UNICODE_LINESTYLE_SINGLE;
	pset.popt.topt.unicode_header_linestyle = UNICODE_LINESTYLE_SINGLE;

	refresh_utf8format(&(pset.popt.topt));

	/* We must get COLUMNS here before readline() sets it */
	pset.popt.topt.env_columns = getenv("COLUMNS") ? atoi(getenv("COLUMNS")) : 0;

	pset.notty = (!isatty(fileno(stdin)) || !isatty(fileno(stdout)));

	pset.getPassword = TRI_DEFAULT;

	EstablishVariableSpace();

	SetVariable(pset.vars, "VERSION", PG_VERSION_STR);

	/* Default values for variables */
	SetVariableBool(pset.vars, "AUTOCOMMIT");
	SetVariable(pset.vars, "VERBOSITY", "default");
	SetVariable(pset.vars, "SHOW_CONTEXT", "errors");
	SetVariable(pset.vars, "PROMPT1", DEFAULT_PROMPT1);
	SetVariable(pset.vars, "PROMPT2", DEFAULT_PROMPT2);
	SetVariable(pset.vars, "PROMPT3", DEFAULT_PROMPT3);

	parse_psql_options(argc, argv, &options);

	/*
	 * If no action was specified and we're in non-interactive mode, treat it
	 * as if the user had specified "-f -".  This lets single-transaction mode
	 * work in this case.
	 */
	if (options.actions.head == NULL && pset.notty)
		simple_action_list_append(&options.actions, ACT_FILE, NULL);

	/* Bail out if -1 was specified but will be ignored. */
	if (options.single_txn && options.actions.head == NULL)
	{
		fprintf(stderr, _("%s: -1 can only be used in non-interactive mode\n"), pset.progname);
		exit(EXIT_FAILURE);
	}

	if (!pset.popt.topt.fieldSep.separator &&
		!pset.popt.topt.fieldSep.separator_zero)
	{
		pset.popt.topt.fieldSep.separator = pg_strdup(DEFAULT_FIELD_SEP);
		pset.popt.topt.fieldSep.separator_zero = false;
	}
	if (!pset.popt.topt.recordSep.separator &&
		!pset.popt.topt.recordSep.separator_zero)
	{
		pset.popt.topt.recordSep.separator = pg_strdup(DEFAULT_RECORD_SEP);
		pset.popt.topt.recordSep.separator_zero = false;
	}

	if (options.username == NULL)
		password_prompt = pg_strdup(_("Password: "******"Password for user %s: "),
								   options.username);

	if (pset.getPassword == TRI_YES)
		password = simple_prompt(password_prompt, 100, false);

	/* loop until we have a password if requested by backend */
	do
	{
#define PARAMS_ARRAY_SIZE	8
		const char **keywords = pg_malloc(PARAMS_ARRAY_SIZE * sizeof(*keywords));
		const char **values = pg_malloc(PARAMS_ARRAY_SIZE * sizeof(*values));

		keywords[0] = "host";
		values[0] = options.host;
		keywords[1] = "port";
		values[1] = options.port;
		keywords[2] = "user";
		values[2] = options.username;
		keywords[3] = "password";
		values[3] = password;
		keywords[4] = "dbname";
		values[4] = (options.list_dbs && options.dbname == NULL) ?
			"postgres" : options.dbname;
		keywords[5] = "fallback_application_name";
		values[5] = pset.progname;
		keywords[6] = "client_encoding";
		values[6] = (pset.notty || getenv("PGCLIENTENCODING")) ? NULL : "auto";
		keywords[7] = NULL;
		values[7] = NULL;

		new_pass = false;
		pset.db = PQconnectdbParams(keywords, values, true);
		free(keywords);
		free(values);

		if (PQstatus(pset.db) == CONNECTION_BAD &&
			PQconnectionNeedsPassword(pset.db) &&
			password == NULL &&
			pset.getPassword != TRI_NO)
		{
			PQfinish(pset.db);
			password = simple_prompt(password_prompt, 100, false);
			new_pass = true;
		}
	} while (new_pass);

	free(password);
	free(password_prompt);

	if (PQstatus(pset.db) == CONNECTION_BAD)
	{
		fprintf(stderr, "%s: %s", pset.progname, PQerrorMessage(pset.db));
		PQfinish(pset.db);
		exit(EXIT_BADCONN);
	}

	setup_cancel_handler();

	PQsetNoticeProcessor(pset.db, NoticeProcessor, NULL);

	SyncVariables();

	if (options.list_dbs)
	{
		int			success;

		if (!options.no_psqlrc)
			process_psqlrc(argv[0]);

		success = listAllDbs(NULL, false);
		PQfinish(pset.db);
		exit(success ? EXIT_SUCCESS : EXIT_FAILURE);
	}

	if (options.logfilename)
	{
		pset.logfile = fopen(options.logfilename, "a");
		if (!pset.logfile)
		{
			fprintf(stderr, _("%s: could not open log file \"%s\": %s\n"),
					pset.progname, options.logfilename, strerror(errno));
			exit(EXIT_FAILURE);
		}
	}

	if (!options.no_psqlrc)
		process_psqlrc(argv[0]);

	/*
	 * If any actions were given by user, process them in the order in which
	 * they were specified.  Note single_txn is only effective in this mode.
	 */
	if (options.actions.head != NULL)
	{
		PGresult   *res;
		SimpleActionListCell *cell;

		successResult = EXIT_SUCCESS;	/* silence compiler */

		if (options.single_txn)
		{
			if ((res = PSQLexec("BEGIN")) == NULL)
			{
				if (pset.on_error_stop)
				{
					successResult = EXIT_USER;
					goto error;
				}
			}
			else
				PQclear(res);
		}

		for (cell = options.actions.head; cell; cell = cell->next)
		{
			if (cell->action == ACT_SINGLE_QUERY)
			{
				if (pset.echo == PSQL_ECHO_ALL)
					puts(cell->val);

				successResult = SendQuery(cell->val)
					? EXIT_SUCCESS : EXIT_FAILURE;
			}
			else if (cell->action == ACT_SINGLE_SLASH)
			{
				PsqlScanState scan_state;

				if (pset.echo == PSQL_ECHO_ALL)
					puts(cell->val);

				scan_state = psql_scan_create(&psqlscan_callbacks);
				psql_scan_setup(scan_state,
								cell->val, strlen(cell->val),
								pset.encoding, standard_strings());

				successResult = HandleSlashCmds(scan_state, NULL) != PSQL_CMD_ERROR
					? EXIT_SUCCESS : EXIT_FAILURE;

				psql_scan_destroy(scan_state);
			}
			else if (cell->action == ACT_FILE)
			{
				successResult = process_file(cell->val, false);
			}
			else
			{
				/* should never come here */
				Assert(false);
			}

			if (successResult != EXIT_SUCCESS && pset.on_error_stop)
				break;
		}

		if (options.single_txn)
		{
			if ((res = PSQLexec("COMMIT")) == NULL)
			{
				if (pset.on_error_stop)
				{
					successResult = EXIT_USER;
					goto error;
				}
			}
			else
				PQclear(res);
		}

error:
		;
	}

	/*
	 * or otherwise enter interactive main loop
	 */
	else
	{
		connection_warnings(true);
		if (!pset.quiet)
			printf(_("Type \"help\" for help.\n\n"));
		initializeInput(options.no_readline ? 0 : 1);
		successResult = MainLoop(stdin);
	}

	/* clean up */
	if (pset.logfile)
		fclose(pset.logfile);
	PQfinish(pset.db);
	setQFout(NULL);

	return successResult;
}
Example #5
0
int
main(int argc, char *argv[])
{
	static int	if_exists = 0;

	static struct option long_options[] = {
		{"host", required_argument, NULL, 'h'},
		{"port", required_argument, NULL, 'p'},
		{"username", required_argument, NULL, 'U'},
		{"no-password", no_argument, NULL, 'w'},
		{"password", no_argument, NULL, 'W'},
		{"echo", no_argument, NULL, 'e'},
		{"interactive", no_argument, NULL, 'i'},
		{"if-exists", no_argument, &if_exists, 1},
		{NULL, 0, NULL, 0}
	};

	const char *progname;
	int			optindex;
	int			c;

	char	   *dropuser = NULL;
	char	   *host = NULL;
	char	   *port = NULL;
	char	   *username = NULL;
	enum trivalue prompt_password = TRI_DEFAULT;
	bool		echo = false;
	bool		interactive = false;

	PQExpBufferData sql;

	PGconn	   *conn;
	PGresult   *result;

	progname = get_progname(argv[0]);
	set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));

	handle_help_version_opts(argc, argv, "dropuser", help);

	while ((c = getopt_long(argc, argv, "h:p:U:wWei", long_options, &optindex)) != -1)
	{
		switch (c)
		{
			case 'h':
				host = pg_strdup(optarg);
				break;
			case 'p':
				port = pg_strdup(optarg);
				break;
			case 'U':
				username = pg_strdup(optarg);
				break;
			case 'w':
				prompt_password = TRI_NO;
				break;
			case 'W':
				prompt_password = TRI_YES;
				break;
			case 'e':
				echo = true;
				break;
			case 'i':
				interactive = true;
				break;
			case 0:
				/* this covers the long options */
				break;
			default:
				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
				exit(1);
		}
	}

	switch (argc - optind)
	{
		case 0:
			break;
		case 1:
			dropuser = argv[optind];
			break;
		default:
			fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
					progname, argv[optind + 1]);
			fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
			exit(1);
	}

	if (dropuser == NULL)
	{
		if (interactive)
			dropuser = simple_prompt("Enter name of role to drop: ", 128, true);
		else
		{
			fprintf(stderr, _("%s: missing required argument role name\n"), progname);
			fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
			exit(1);
		}
	}

	if (interactive)
	{
		printf(_("Role \"%s\" will be permanently removed.\n"), dropuser);
		if (!yesno_prompt("Are you sure?"))
			exit(0);
	}

	initPQExpBuffer(&sql);
	appendPQExpBuffer(&sql, "DROP ROLE %s%s;",
					  (if_exists ? "IF EXISTS " : ""), fmtId(dropuser));

	conn = connectDatabase("postgres", host, port, username, prompt_password,
						   progname, false, false);

	if (echo)
		printf("%s\n", sql.data);
	result = PQexec(conn, sql.data);

	if (PQresultStatus(result) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, _("%s: removal of role \"%s\" failed: %s"),
				progname, dropuser, PQerrorMessage(conn));
		PQfinish(conn);
		exit(1);
	}

	PQclear(result);
	PQfinish(conn);
	exit(0);
}
Example #6
0
/*
 * Retrieve columns name and type of a table related a given layer
 */
static void ows_storage_fill_attributes(ows * o, ows_layer * l)
{
  buffer *sql;
  PGresult *res;
  buffer *b, *t;
  int i, end;
  list_node *ln;

  assert(o);
  assert(l);
  assert(l->storage);

  sql = buffer_init();

  buffer_add_str(sql, "SELECT a.attname AS field, t.typname AS type ");
  buffer_add_str(sql, "FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n WHERE n.nspname = '");
  buffer_copy(sql, l->storage->schema);
  buffer_add_str(sql, "' AND c.relname = '");
  buffer_copy(sql, l->storage->table);
  buffer_add_str(sql, "' AND c.relnamespace = n.oid AND a.attrelid = c.oid AND a.atttypid = t.oid");
  if (l->include_items) {
    buffer_add_str(sql, " AND a.attname IN (");
    for (ln = l->include_items->first ; ln ; ln = ln->next) {
      buffer_add_str(sql, "'");
      buffer_copy(sql, ln->value);
      buffer_add_str(sql, "', ");
    }
    if (l->include_items->first && l->storage->pkey) {
      buffer_add_str(sql, "'");
      buffer_copy(sql, l->storage->pkey );
      buffer_add_str(sql, "',");
    }

    buffer_add_str(sql, " '');");
  } else {
    buffer_add_str(sql, " AND a.attnum > 0;");
  }

  res = ows_psql_exec(o, sql->buf);
  buffer_free(sql);

  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
    PQclear(res);
    ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to access pg_* tables.", "fill_attributes");
    return;
  }

  for (i = 0, end = PQntuples(res); i < end; i++) {
    b = buffer_init();
    t = buffer_init();
    buffer_add_str(b, PQgetvalue(res, i, 0));
    buffer_add_str(t, PQgetvalue(res, i, 1));

    /* If the column is a geometry, get its real geometry type */
    if (buffer_cmp(t, "geometry")) {
      PGresult *geom_res;
      buffer *geom_sql = buffer_init();
      buffer_add_str(geom_sql, "SELECT type from geometry_columns where f_table_schema='");
      buffer_copy(geom_sql, l->storage->schema);
      buffer_add_str(geom_sql,"' and f_table_name='");
      buffer_copy(geom_sql, l->storage->table);
      buffer_add_str(geom_sql,"' and f_geometry_column='");
      buffer_copy(geom_sql, b);
      buffer_add_str(geom_sql,"';");

      geom_res = ows_psql_exec(o, geom_sql->buf);
      buffer_free(geom_sql);

      if (PQresultStatus(geom_res) != PGRES_TUPLES_OK || PQntuples(geom_res) == 0) {
        PQclear(res);
        PQclear(geom_res);
        ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED,
                  "Unable to access geometry_columns table, try Populate_Geometry_Columns()", "fill_attributes");
        return;
      }

      buffer_empty(t);
      buffer_add_str(t, PQgetvalue(geom_res, 0, 0));
      PQclear(geom_res);
    }

    array_add(l->storage->attributes, b, t);
  }
  PQclear(res);

}
void PQclear_app(finalizeonce *res) {
  if (res->isfinalized)
    return;
  PQclear((PGresult *) (res->encapobj));
  res->isfinalized = 1;
}
Example #8
0
int	main(int argc, char *argv[])
{
  int Pid;
  int c;
  int rvExist1=0, rvExist2=0;
  PGresult *result;
  char *NewDir=".";
  char *AgentName = "ununpack";
  char *AgentARSName = "ununpack_ars";
  char *agent_desc = "Unpacks archives (iso, tar, etc)";
  int   Recurse=0;
  int   ars_pk = 0;
  int   user_pk = 0;
  long  Pfile_size = 0;
  char *ListOutName=NULL;
  char *Fname = NULL;
  char *FnameCheck = NULL;
  char *COMMIT_HASH;
  char *VERSION;
  char agent_rev[PATH_MAX];
  struct stat Stat;

  /* connect to the scheduler */
  fo_scheduler_connect(&argc, argv, &pgConn);

  while((c = getopt(argc,argv,"ACc:d:FfHhL:m:PQiqRr:T:t:U:VvXx")) != -1)
  {
    switch(c)
    {
      case 'A':	SetContainerArtifact=0; break;
      case 'C':	ForceContinue=1; break;
      case 'c':	break;  /* handled by fo_scheduler_connect() */
      case 'd':	
        /* if there is a %U in the path, substitute a unique ID */
        NewDir=PathCheck(optarg);
        break;
      case 'F':	UseRepository=1; break;
      case 'f':	ForceDuplicate=1; break;
      case 'L':	ListOutName=optarg; break;
      case 'm':
        MaxThread = atoi(optarg);
        if (MaxThread < 1) MaxThread=1;
        break;
      case 'P':	PruneFiles=1; break;
      case 'R':	Recurse=-1; break;
      case 'r':	Recurse=atoi(optarg); break;
      case 'i':
        if (!IsExe("dpkg-source",Quiet))
          LOG_WARNING("dpkg-source is not available on this system.  This means that debian source packages will NOT be unpacked.");
        SafeExit(0);
        break; /* never reached */
      case 'Q':
        UseRepository=1;

        user_pk = fo_scheduler_userID(); /* get user_pk for user who queued the agent */

        /* Get the upload_pk from the scheduler */
        if((Upload_Pk = fo_scheduler_next()) == NULL) SafeExit(0);
        break;
      case 'q':	Quiet=1; break;
      case 'T':
        memset(REP_GOLD,0,sizeof(REP_GOLD));
        strncpy(REP_GOLD,optarg,sizeof(REP_GOLD)-1);
        break;
      case 't':
        memset(REP_FILES,0,sizeof(REP_FILES));
        strncpy(REP_FILES,optarg,sizeof(REP_FILES)-1);
        break;
      case 'U':	
        UseRepository = 1;
        Recurse = -1;
        Upload_Pk = optarg; 
        break;
      case 'V': printf("%s", BuildVersion);SafeExit(0);
      case 'v':	Verbose++; break;
      case 'X':	UnlinkSource=1; break;
      case 'x':	UnlinkAll=1; break;
      default:
        Usage(argv[0], BuildVersion);
        SafeExit(25);
    }
  }

  /* Open DB and Initialize CMD table */
  if (UseRepository) 
  {
    /* Check Permissions */
    if (GetUploadPerm(pgConn, atoi(Upload_Pk), user_pk) < PERM_WRITE)
    {
      LOG_ERROR("You have no update permissions on upload %s", Upload_Pk);
      SafeExit(100);
    }
        
    COMMIT_HASH = fo_sysconfig(AgentName, "COMMIT_HASH");
    VERSION = fo_sysconfig(AgentName, "VERSION");
    sprintf(agent_rev, "%s.%s", VERSION, COMMIT_HASH);
    /* Get the unpack agent key */
    agent_pk = fo_GetAgentKey(pgConn, AgentName, atoi(Upload_Pk), agent_rev,agent_desc);

    InitCmd();

    /* Make sure ars table exists */
    if (!fo_CreateARSTable(pgConn, AgentARSName)) SafeExit(0);

    /* Has this user previously unpacked this upload_pk successfully?
     *    In this case we are done.  No new ars record is needed since no
     *    processing is initiated.
     * The unpack version is ignored.
     */
    snprintf(SQL,MAXSQL,
        "SELECT ars_pk from %s where upload_fk='%s' and ars_success=TRUE",
           AgentARSName, Upload_Pk);
    result =  PQexec(pgConn, SQL);
    if (fo_checkPQresult(pgConn, result, SQL, __FILE__, __LINE__)) SafeExit(101);

    if (PQntuples(result) > 0) /* if there is a value */
    {  
      PQclear(result);
      LOG_WARNING("Upload_pk %s, has already been unpacked.  No further action required", 
              Upload_Pk)
      SafeExit(0);
    }
    PQclear(result);

    /* write the unpack_ars start record */
    ars_pk = fo_WriteARS(pgConn, ars_pk, atoi(Upload_Pk), agent_pk, AgentARSName, 0, 0);

    /* Get Pfile path and Pfile_Pk, from Upload_Pk */
  snprintf(SQL,MAXSQL,
        "SELECT pfile.pfile_sha1 || '.' || pfile.pfile_md5 || '.' || pfile.pfile_size AS pfile, pfile_fk, pfile_size FROM upload INNER JOIN pfile ON upload.pfile_fk = pfile.pfile_pk WHERE upload.upload_pk = '%s'", 
           Upload_Pk);
    result =  PQexec(pgConn, SQL);
    if (fo_checkPQresult(pgConn, result, SQL, __FILE__, __LINE__)) SafeExit(102);

    if (PQntuples(result) > 0) /* if there is a value */
    {  
      Pfile = strdup(PQgetvalue(result,0,0));
      Pfile_Pk = strdup(PQgetvalue(result,0,1));
      Pfile_size = atol(PQgetvalue(result, 0, 2));
      if (Pfile_size == 0)
      {  
        PQclear(result);
        LOG_WARNING("Uploaded file (Upload_pk %s), is zero length.  There is nothing to unpack.", 
                      Upload_Pk)
        SafeExit(0);
      }

      PQclear(result);
    }

    // Determine if uploadtree records should go into a separate table.
    // If the input file size is > 500MB, then create a separate uploadtree_{upload_pk} table
    // that inherits from the master uploadtree table.
    // Save uploadtree_tablename, it will get written to upload.uploadtree_tablename later.
    if (Pfile_size > 500000000)
    {
      sprintf(uploadtree_tablename, "uploadtree_%s", Upload_Pk);
      if (!fo_tableExists(pgConn, uploadtree_tablename))
      {
        snprintf(SQL,MAXSQL,"CREATE TABLE %s (LIKE uploadtree INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES); ALTER TABLE %s ADD CONSTRAINT %s CHECK (upload_fk=%s); ALTER TABLE %s INHERIT uploadtree", 
               uploadtree_tablename, uploadtree_tablename, uploadtree_tablename, Upload_Pk, uploadtree_tablename);
        PQsetNoticeProcessor(pgConn, SQLNoticeProcessor, SQL);  // ignore notice about implicit primary key index creation
        result =  PQexec(pgConn, SQL);
        // Ignore postgres notice about creating an implicit index
        if (PQresultStatus(result) != PGRES_NONFATAL_ERROR)
          if (fo_checkPQcommand(pgConn, result, SQL, __FILE__, __LINE__)) SafeExit(103);
        PQclear(result);
      }
    }
    else
      strcpy(uploadtree_tablename, "uploadtree_a");

  }

  CheckCommands(Quiet);
  if (NewDir) MkDir(NewDir);
  if (Verbose) { fclose(stderr) ; stderr=stdout; } /* don't interlace! */
  if (ListOutName != NULL)
  {
    if ((ListOutName[0]=='-') && (ListOutName[1]=='\0'))
      ListOutFile=stdout;
    else ListOutFile = fopen(ListOutName,"w");
    if (!ListOutFile)
    {
      LOG_ERROR("pfile %s Unable to write to %s\n",Pfile_Pk,ListOutName)
      SafeExit(104);
    }
    else
    {
      /* Start the file */
      fputs("<xml tool=\"ununpack\" ",ListOutFile);
      fputs("version=\"",ListOutFile);
      fputs(Version,ListOutFile);
      fputs("\" ",ListOutFile);
      fputs("compiled_date=\"",ListOutFile);
      fputs(__DATE__,ListOutFile);
      fputs(" ",ListOutFile);
      fputs(__TIME__,ListOutFile);
      fputs("\"",ListOutFile);
      fputs(">\n",ListOutFile);
    }
    /* Problem: When parallel processing, the XML may be generated out
	   of order.  Solution?  When using XML, only use 1 thread. */
    MaxThread=1;
  }

  // Set ReunpackSwitch if the uploadtree records are missing from the database.
  if (!ReunpackSwitch && UseRepository)
  {
    snprintf(SQL,MAXSQL,"SELECT uploadtree_pk FROM uploadtree WHERE upload_fk=%s limit 1;",Upload_Pk);
    result =  PQexec(pgConn, SQL);
    if (fo_checkPQresult(pgConn, result, SQL, __FILE__, __LINE__)) SafeExit(105);
    if (PQntuples(result) == 0) ReunpackSwitch=1;
    PQclear(result);
  }

  /*** process files from command line ***/
  for( ; optind<argc; optind++)
  {
    CksumFile *CF=NULL;
    Cksum *Sum;
    int i;
    if (Fname) { free(Fname); Fname=NULL; }
    if (ListOutName != NULL)
    {
      fprintf(ListOutFile,"<source source=\"%s\" ",argv[optind]);
      if (UseRepository && !fo_RepExist(REP_FILES,argv[optind]))
      {
        /* make sure the source exists in the src repository */
        if (fo_RepImport(argv[optind],REP_FILES,argv[optind],1) != 0)
        {
          LOG_ERROR("Failed to import '%s' as '%s' into the repository",argv[optind],argv[optind])
          SafeExit(106);
        }
      }
    }

    if (UseRepository)
    {
      if (fo_RepExist(REP_FILES,argv[optind]))
      {
        Fname=fo_RepMkPath(REP_FILES,argv[optind]);
      }
      else if (fo_RepExist(REP_GOLD,argv[optind]))
      {
        Fname=fo_RepMkPath(REP_GOLD,argv[optind]);
        if (fo_RepImport(Fname,REP_FILES,argv[optind],1) != 0)
        {
          LOG_ERROR("Failed to import '%s' as '%s' into the repository",Fname,argv[optind])
          SafeExit(107);
        }
      }

      if (Fname)
      {
        FnameCheck = Fname;
        CF = SumOpenFile(Fname);
      }
      else
      {
        LOG_ERROR("NO file unpacked.  File %s does not exist either in GOLD or FILES", Pfile);
        SafeExit(108);
      }
      /* else: Fname is NULL and CF is NULL */
    }
    else 
    {
      FnameCheck = argv[optind];
      CF = SumOpenFile(argv[optind]);
    }

    /* Check file to unpack.  Does it exist?  Is it zero length? */
    if (stat(FnameCheck,&Stat)) 
    {
      LOG_ERROR("File to unpack is unavailable: %s, error: %s", Fname, strerror(errno));
      SafeExit(109);
    }
    else
    if (Stat.st_size < 1)
    {
      LOG_WARNING("File to unpack is empty: %s", Fname);
      SafeExit(110);
    }

    if (ListOutFile)
    {
      if (CF)
      {
        Sum = SumComputeBuff(CF);
        SumCloseFile(CF);
        if (Sum)
        {
          fputs("fuid=\"",ListOutFile);
          for(i=0; i<20; i++)
          { fprintf(ListOutFile,"%02X",Sum->SHA1digest[i]); }
          fputs(".",ListOutFile);
          for(i=0; i<16; i++)
          { fprintf(ListOutFile,"%02X",Sum->MD5digest[i]); }
          fputs(".",ListOutFile);
          fprintf(ListOutFile,"%Lu",(long long unsigned int)Sum->DataLen);
          fputs("\" ",ListOutFile);
          free(Sum);
        } /* if Sum */
      } /* if CF */
      else /* file too large to mmap (probably) */
      {
        FILE *Fin;
        Fin = fopen(argv[optind],"rb");
        if (Fin)
        {
          Sum = SumComputeFile(Fin);
          if (Sum)
          {
            fputs("fuid=\"",ListOutFile);
            for(i=0; i<20; i++)
            { fprintf(ListOutFile,"%02X",Sum->SHA1digest[i]); }
            fputs(".",ListOutFile);
            for(i=0; i<16; i++)
            { fprintf(ListOutFile,"%02X",Sum->MD5digest[i]); }
            fputs(".",ListOutFile);
            fprintf(ListOutFile,"%Lu",(long long unsigned int)Sum->DataLen);
            fputs("\" ",ListOutFile);
            free(Sum);
          }
          fclose(Fin);
        }
      } /* else no CF */
      fprintf(ListOutFile,">\n"); /* end source XML */
    }
    if (Fname)	TraverseStart(Fname,"called by main via args",NewDir,Recurse);
    else		TraverseStart(argv[optind],"called by main",NewDir,Recurse);
    if (ListOutName != NULL) fprintf(ListOutFile,"</source>\n");
  } /* end for */

  /* free memory */
  if (Fname) { free(Fname); Fname=NULL; }

  /* process pfile from scheduler */
  if (Pfile)
  {
    if (0 == (rvExist1 = fo_RepExist2(REP_FILES,Pfile)))
    {
      Fname=fo_RepMkPath(REP_FILES,Pfile);
    }
    else if (0 == (rvExist2 = fo_RepExist2(REP_GOLD,Pfile)))
    {
      Fname=fo_RepMkPath(REP_GOLD,Pfile);
      if (fo_RepImport(Fname,REP_FILES,Pfile,1) != 0)
      {
        LOG_ERROR("Failed to import '%s' as '%s' into the repository",Fname,Pfile)
        SafeExit(111);
      }
    }
    if (Fname)
    {
      TraverseStart(Fname,"called by main via env",NewDir,Recurse);
      free(Fname);
      Fname=NULL;
    }
    else
    {
      LOG_ERROR("NO file unpacked!");
      if (rvExist1 > 0)
      {
        Fname=fo_RepMkPath(REP_FILES, Pfile);
        LOG_ERROR("Error is %s for %s", strerror(rvExist1), Fname);
      }
      if (rvExist2 > 0)
      {
        Fname=fo_RepMkPath(REP_GOLD, Pfile);
        LOG_ERROR("Error is %s for %s", strerror(rvExist2), Fname);
      }
      SafeExit(112);
    }
  }

  /* recurse on all the children */
  if (Thread > 0) do
  {
    Pid = ParentWait();
    Thread--;
    if (Pid >= 0)
    {
      if (!Queue[Pid].ChildEnd)
      {
        /* copy over data */
        if (Recurse > 0)
          Traverse(Queue[Pid].ChildRecurse,NULL,"called by wait",NULL,Recurse-1,&Queue[Pid].PI);
        else if (Recurse < 0)
          Traverse(Queue[Pid].ChildRecurse,NULL,"called by wait",NULL,Recurse,&Queue[Pid].PI);
      }
    }
  } while(Pid >= 0);

  if (MagicCookie) magic_close(MagicCookie);
  if (ListOutFile)
  {
    fprintf(ListOutFile,"<summary files_regular=\"%d\" files_compressed=\"%d\" artifacts=\"%d\" directories=\"%d\" containers=\"%d\" />\n",
        TotalFiles,TotalCompressedFiles,TotalArtifacts,
        TotalDirectories,TotalContainers);
    fputs("</xml>\n",ListOutFile);
  }
  if (pgConn)
  {
    /* If it completes, mark it! */
    if (Upload_Pk)
    {
      snprintf(SQL,MAXSQL,"UPDATE upload SET upload_mode = (upload_mode | (1<<5)), uploadtree_tablename='%s' WHERE upload_pk = '%s';",uploadtree_tablename, Upload_Pk);
      result =  PQexec(pgConn, SQL); /* UPDATE upload */
      if (fo_checkPQcommand(pgConn, result, SQL, __FILE__ ,__LINE__)) SafeExit(113);
      PQclear(result);

      snprintf(SQL,MAXSQL,"UPDATE %s SET realparent = getItemParent(uploadtree_pk) WHERE upload_fk = '%s'",uploadtree_tablename, Upload_Pk);
      result =  PQexec(pgConn, SQL); /* UPDATE uploadtree */
      if (fo_checkPQcommand(pgConn, result, SQL, __FILE__ ,__LINE__)) SafeExit(114);
      PQclear(result);
    }

    if (ars_pk) fo_WriteARS(pgConn, ars_pk, atoi(Upload_Pk), agent_pk, AgentARSName, 0, 1);
  }
  if (ListOutFile && (ListOutFile != stdout))
  {
    fclose(ListOutFile);
  }

  if (UnlinkAll && MaxThread > 1)
  {
    /* Delete temporary files */
    if (strcmp(NewDir, ".")) RemoveDir(NewDir);
  }
 
  SafeExit(0);
  return(0);  // never executed but makes the compiler happy
} 
Example #9
0
/*
 * This vacuums LOs of one database. It returns 0 on success, -1 on failure.
 */
int
vacuumlo(char *database, struct _param * param)
{
	PGconn	   *conn;
	PGresult   *res,
			   *res2;
	char		buf[BUFSIZE];
	int			matched;
	int			deleted;
	int			i;
	static char *password = NULL;
	bool		new_pass;

	if (param->pg_prompt == TRI_YES && password == NULL)
		password = simple_prompt("Password: "******"Connection to database \"%s\" failed\n",
					database);
			return -1;
		}

		if (PQstatus(conn) == CONNECTION_BAD &&
			PQconnectionNeedsPassword(conn) &&
			password == NULL &&
			param->pg_prompt != TRI_NO)
		{
			PQfinish(conn);
			password = simple_prompt("Password: "******"Connection to database \"%s\" failed:\n%s",
				database, PQerrorMessage(conn));
		PQfinish(conn);
		return -1;
	}

	if (param->verbose)
	{
		fprintf(stdout, "Connected to %s\n", database);
		if (param->dry_run)
			fprintf(stdout, "Test run: no large objects will be removed!\n");
	}

	/*
	 * Don't get fooled by any non-system catalogs
	 */
	res = PQexec(conn, "SET search_path = pg_catalog");
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "Failed to set search_path:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}
	PQclear(res);

	/*
	 * First we create and populate the LO temp table
	 */
	buf[0] = '\0';
	strcat(buf, "CREATE TEMP TABLE vacuum_l AS ");
	if (PQserverVersion(conn) >= 90000)
		strcat(buf, "SELECT oid AS lo FROM pg_largeobject_metadata");
	else
		strcat(buf, "SELECT DISTINCT loid AS lo FROM pg_largeobject");
	res = PQexec(conn, buf);
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "Failed to create temp table:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}
	PQclear(res);

	/*
	 * Analyze the temp table so that planner will generate decent plans for
	 * the DELETEs below.
	 */
	buf[0] = '\0';
	strcat(buf, "ANALYZE vacuum_l");
	res = PQexec(conn, buf);
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "Failed to vacuum temp table:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}
	PQclear(res);

	/*
	 * Now find any candidate tables that have columns of type oid.
	 *
	 * NOTE: we ignore system tables and temp tables by the expedient of
	 * rejecting tables in schemas named 'pg_*'.  In particular, the temp
	 * table formed above is ignored, and pg_largeobject will be too. If
	 * either of these were scanned, obviously we'd end up with nothing to
	 * delete...
	 *
	 * NOTE: the system oid column is ignored, as it has attnum < 1. This
	 * shouldn't matter for correctness, but it saves time.
	 */
	buf[0] = '\0';
	strcat(buf, "SELECT s.nspname, c.relname, a.attname ");
	strcat(buf, "FROM pg_class c, pg_attribute a, pg_namespace s, pg_type t ");
	strcat(buf, "WHERE a.attnum > 0 AND NOT a.attisdropped ");
	strcat(buf, "      AND a.attrelid = c.oid ");
	strcat(buf, "      AND a.atttypid = t.oid ");
	strcat(buf, "      AND c.relnamespace = s.oid ");
	strcat(buf, "      AND t.typname in ('oid', 'lo') ");
	strcat(buf, "      AND c.relkind = 'r'");
	strcat(buf, "      AND s.nspname !~ '^pg_'");
	res = PQexec(conn, buf);
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, "Failed to find OID columns:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}

	for (i = 0; i < PQntuples(res); i++)
	{
		char	   *schema,
				   *table,
				   *field;

		schema = PQgetvalue(res, i, 0);
		table = PQgetvalue(res, i, 1);
		field = PQgetvalue(res, i, 2);

		if (param->verbose)
			fprintf(stdout, "Checking %s in %s.%s\n", field, schema, table);

		snprintf(buf, BUFSIZE,
				 "DELETE FROM vacuum_l "
				 "WHERE lo IN (SELECT \"%s\" FROM \"%s\".\"%s\")",
				 field, schema, table);
		res2 = PQexec(conn, buf);
		if (PQresultStatus(res2) != PGRES_COMMAND_OK)
		{
			fprintf(stderr, "Failed to check %s in table %s.%s:\n",
					field, schema, table);
			fprintf(stderr, "%s", PQerrorMessage(conn));
			PQclear(res2);
			PQclear(res);
			PQfinish(conn);
			return -1;
		}
		PQclear(res2);
	}
	PQclear(res);

	/*
	 * Run the actual deletes in a single transaction.	Note that this would
	 * be a bad idea in pre-7.1 Postgres releases (since rolling back a table
	 * delete used to cause problems), but it should be safe now.
	 */
	res = PQexec(conn, "begin");
	PQclear(res);

	/*
	 * Finally, those entries remaining in vacuum_l are orphans.
	 */
	buf[0] = '\0';
	strcat(buf, "SELECT lo ");
	strcat(buf, "FROM vacuum_l");
	res = PQexec(conn, buf);
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, "Failed to read temp table:\n");
		fprintf(stderr, "%s", PQerrorMessage(conn));
		PQclear(res);
		PQfinish(conn);
		return -1;
	}

	matched = PQntuples(res);
	deleted = 0;
	for (i = 0; i < matched; i++)
	{
		Oid			lo = atooid(PQgetvalue(res, i, 0));

		if (param->verbose)
		{
			fprintf(stdout, "\rRemoving lo %6u   ", lo);
			fflush(stdout);
		}

		if (param->dry_run == 0)
		{
			if (lo_unlink(conn, lo) < 0)
			{
				fprintf(stderr, "\nFailed to remove lo %u: ", lo);
				fprintf(stderr, "%s", PQerrorMessage(conn));
			}
			else
				deleted++;
		}
		else
			deleted++;
	}
	PQclear(res);

	/*
	 * That's all folks!
	 */
	res = PQexec(conn, "end");
	PQclear(res);

	PQfinish(conn);

	if (param->verbose)
		fprintf(stdout, "\r%s %d large objects from %s.\n",
		   (param->dry_run ? "Would remove" : "Removed"), deleted, database);

	return 0;
}
/**
 * Iterate over the results for a particular key
 * in the datastore.
 *
 * @param cls closure (our "struct Plugin")
 * @param key
 * @param type entries of which type are relevant?
 * @param iter maybe NULL (to just count)
 * @param iter_cls closure for iter
 * @return the number of results found
 */
static unsigned int
postgres_plugin_get (void *cls, const struct GNUNET_HashCode * key,
                     enum GNUNET_BLOCK_Type type,
                     GNUNET_DATACACHE_Iterator iter, void *iter_cls)
{
  struct Plugin *plugin = cls;
  uint32_t btype = htonl (type);

  const char *paramValues[] = {
    (const char *) key,
    (const char *) &btype,
  };
  int paramLengths[] = {
    sizeof (struct GNUNET_HashCode),
    sizeof (btype),
  };
  const int paramFormats[] = { 1, 1 };
  struct GNUNET_TIME_Absolute expiration_time;
  uint32_t size;
  unsigned int cnt;
  unsigned int i;
  unsigned int path_len;
  const struct GNUNET_PeerIdentity *path;
  PGresult *res;

  res =
      PQexecPrepared (plugin->dbh, (type == 0) ? "getk" : "getkt",
                      (type == 0) ? 1 : 2, paramValues, paramLengths,
                      paramFormats, 1);
  if (GNUNET_OK !=
      GNUNET_POSTGRES_check_result (plugin->dbh, res, PGRES_TUPLES_OK, "PQexecPrepared",
				    (type == 0) ? "getk" : "getkt"))
  {
    LOG (GNUNET_ERROR_TYPE_DEBUG,
	 "Ending iteration (postgres error)\n");
    return 0;
  }

  if (0 == (cnt = PQntuples (res)))
  {
    /* no result */
    LOG (GNUNET_ERROR_TYPE_DEBUG, 
	 "Ending iteration (no more results)\n");
    PQclear (res);
    return 0;
  }
  if (iter == NULL)
  {
    PQclear (res);
    return cnt;
  }
  if ((4 != PQnfields (res)) || (sizeof (uint64_t) != PQfsize (res, 0)) ||
      (sizeof (uint32_t) != PQfsize (res, 1)))
  {
    GNUNET_break (0);
    PQclear (res);
    return 0;
  }
  for (i = 0; i < cnt; i++)
  {
    expiration_time.abs_value =
        GNUNET_ntohll (*(uint64_t *) PQgetvalue (res, i, 0));
    type = ntohl (*(uint32_t *) PQgetvalue (res, i, 1));
    size = PQgetlength (res, i, 2);
    path_len = PQgetlength (res, i, 3);
    if (0 != (path_len % sizeof (struct GNUNET_PeerIdentity)))
    {
      GNUNET_break (0);
      path_len = 0;
    }
    path_len %= sizeof (struct GNUNET_PeerIdentity);
    path = (const struct GNUNET_PeerIdentity *) PQgetvalue (res, i, 3);
    LOG (GNUNET_ERROR_TYPE_DEBUG, 
	 "Found result of size %u bytes and type %u in database\n",
	 (unsigned int) size, (unsigned int) type);
    if (GNUNET_SYSERR ==
        iter (iter_cls, key, size, PQgetvalue (res, i, 2),
              (enum GNUNET_BLOCK_Type) type,
	      expiration_time,
	      path_len,
	      path))
    {
      LOG (GNUNET_ERROR_TYPE_DEBUG, 
	   "Ending iteration (client error)\n");
      PQclear (res);
      return cnt;
    }
  }
  PQclear (res);
  return cnt;
}
/**
 * @brief Get a database handle
 *
 * @param plugin global context
 * @return GNUNET_OK on success, GNUNET_SYSERR on error
 */
static int
init_connection (struct Plugin *plugin)
{
  PGresult *ret;

  plugin->dbh = GNUNET_POSTGRES_connect (plugin->env->cfg,
					 "datacache-postgres");
  if (NULL == plugin->dbh)
    return GNUNET_SYSERR;
  ret =
      PQexec (plugin->dbh,
              "CREATE TEMPORARY TABLE gn090dc ("
              "  type INTEGER NOT NULL DEFAULT 0,"
              "  discard_time BIGINT NOT NULL DEFAULT 0,"
              "  key BYTEA NOT NULL DEFAULT '',"
              "  value BYTEA NOT NULL DEFAULT '',"
              "  path BYTEA DEFAULT '')"
	      "WITH OIDS");
  if ( (ret == NULL) || 
       ((PQresultStatus (ret) != PGRES_COMMAND_OK) && 
	(0 != strcmp ("42P07",    /* duplicate table */
		      PQresultErrorField
		      (ret,
		       PG_DIAG_SQLSTATE)))))
  {
    (void) GNUNET_POSTGRES_check_result (plugin->dbh, ret, 
					 PGRES_COMMAND_OK, "CREATE TABLE",
					 "gn090dc");
    PQfinish (plugin->dbh);
    plugin->dbh = NULL;
    return GNUNET_SYSERR;
  }
  if (PQresultStatus (ret) == PGRES_COMMAND_OK)
  {
    if ((GNUNET_OK !=
         GNUNET_POSTGRES_exec (plugin->dbh, "CREATE INDEX idx_key ON gn090dc (key)")) ||
        (GNUNET_OK !=
         GNUNET_POSTGRES_exec (plugin->dbh, "CREATE INDEX idx_dt ON gn090dc (discard_time)")))
    {
      PQclear (ret);
      PQfinish (plugin->dbh);
      plugin->dbh = NULL;
      return GNUNET_SYSERR;
    }
  }
  PQclear (ret);
  ret =
      PQexec (plugin->dbh,
              "ALTER TABLE gn090dc ALTER value SET STORAGE EXTERNAL");
  if (GNUNET_OK !=
      GNUNET_POSTGRES_check_result (plugin->dbh, ret, PGRES_COMMAND_OK, "ALTER TABLE", "gn090dc"))
  {
    PQfinish (plugin->dbh);
    plugin->dbh = NULL;
    return GNUNET_SYSERR;
  }
  PQclear (ret);
  ret = PQexec (plugin->dbh, "ALTER TABLE gn090dc ALTER key SET STORAGE PLAIN");
  if (GNUNET_OK !=
      GNUNET_POSTGRES_check_result (plugin->dbh, ret, PGRES_COMMAND_OK, "ALTER TABLE", "gn090dc"))
  {
    PQfinish (plugin->dbh);
    plugin->dbh = NULL;
    return GNUNET_SYSERR;
  }
  PQclear (ret);
  if ((GNUNET_OK !=
       GNUNET_POSTGRES_prepare (plugin->dbh, "getkt",
                   "SELECT discard_time,type,value,path FROM gn090dc "
                   "WHERE key=$1 AND type=$2 ", 2)) ||
      (GNUNET_OK !=
       GNUNET_POSTGRES_prepare (plugin->dbh, "getk",
                   "SELECT discard_time,type,value,path FROM gn090dc "
                   "WHERE key=$1", 1)) ||
      (GNUNET_OK !=
       GNUNET_POSTGRES_prepare (plugin->dbh, "getm",
                   "SELECT length(value),oid,key FROM gn090dc "
                   "ORDER BY discard_time ASC LIMIT 1", 0)) ||
      (GNUNET_OK !=
       GNUNET_POSTGRES_prepare (plugin->dbh, "delrow", "DELETE FROM gn090dc WHERE oid=$1", 1)) ||
      (GNUNET_OK !=
       GNUNET_POSTGRES_prepare (plugin->dbh, "put",
                   "INSERT INTO gn090dc (type, discard_time, key, value, path) "
                   "VALUES ($1, $2, $3, $4, $5)", 5)))
  {
    PQfinish (plugin->dbh);
    plugin->dbh = NULL;
    return GNUNET_SYSERR;
  }
  return GNUNET_OK;
}
Example #12
0
/*+++++++++++++++++++++++++ Main Program or Function +++++++++++++++*/
void SCIA_LV1_DEL_ENTRY( PGconn *conn, bool be_verbose, const char *flname )
{
    register int nr;

    unsigned int indx;

    char     sql_query[SQL_STR_SIZE];
    char     procStage[2];
    char     *cpntr, sciafl[SHORT_STRING_LENGTH];
    int      nrow, numChar;

    PGresult *res, *res_update;
    /*
     * strip path of file-name
     */
    if ( (cpntr = strrchr( flname, '/' )) != NULL ) {
        (void) nadc_strlcpy( sciafl, ++cpntr, SHORT_STRING_LENGTH );
    } else {
        (void) nadc_strlcpy( sciafl, flname, SHORT_STRING_LENGTH );
    }
    /*
     * check if we have to reverse field "softVersion[1]" in table "stateinfo"
     */
    numChar = snprintf( sql_query, SQL_STR_SIZE, SELECT_FROM_STATEINFO,
                        sciafl );
    if ( be_verbose )
        (void) printf( "%s(): %s [%-d]\n", __func__, sql_query, numChar );
    if ( numChar >= SQL_STR_SIZE )
        NADC_RETURN_ERROR( NADC_ERR_STRLEN, "sql_query" );
    res = PQexec( conn, sql_query );
    if ( PQresultStatus( res ) != PGRES_TUPLES_OK ) {
        NADC_RETURN_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
    }
    /* return when no entries are found */
    if ( (nrow = PQntuples( res )) == 0 ) goto done;
    /*
     * update field "softVersion[1]" in table "stateinfo"
     */
    (void) nadc_strlcpy( procStage, sciafl+10, 2 );
    for ( nr = 0; nr < nrow; nr++ ) {
        cpntr = PQgetvalue( res, nr, 0 );
        indx = (unsigned int) strtoul( cpntr, (char **)NULL, 10 );
        numChar = snprintf( sql_query, SQL_STR_SIZE,
                            UPDATE_STATEINFO, indx, procStage );
        if ( be_verbose )
            (void) printf( "%s(): %s [%-d]\n", __func__, sql_query, numChar );
        if ( numChar >= SQL_STR_SIZE )
            NADC_RETURN_ERROR( NADC_ERR_STRLEN, "sql_query" );
        res_update = PQexec( conn, sql_query );
        if ( PQresultStatus( res_update ) != PGRES_COMMAND_OK ) {
            NADC_ERROR( NADC_ERR_SQL,
                        PQresultErrorMessage(res_update) );
            PQclear( res_update );
            goto done;
        }
        PQclear( res_update );
    }
done:
    PQclear( res );
    /*
     * remove entry from table "meta__1P"
     * referenced entries in table "stateinfo_meta__0P" are removed by the engine
     */
    numChar = snprintf( sql_query, SQL_STR_SIZE, DELETE_FROM_META, sciafl );
    if ( be_verbose )
        (void) printf( "%s(): %s [%-d]\n", __func__, sql_query, numChar );
    if ( numChar >= SQL_STR_SIZE )
        NADC_RETURN_ERROR( NADC_ERR_STRLEN, "sql_query" );
    res = PQexec( conn, sql_query );
    if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
        NADC_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
    PQclear( res );
}
Example #13
0
/*
 * ExecQueryUsingCursor: run a SELECT-like query using a cursor
 *
 * This feature allows result sets larger than RAM to be dealt with.
 *
 * Returns true if the query executed successfully, false otherwise.
 *
 * If pset.timing is on, total query time (exclusive of result-printing) is
 * stored into *elapsed_msec.
 */
static bool
ExecQueryUsingCursor(const char *query, double *elapsed_msec)
{
	bool		OK = true;
	PGresult   *results;
	PQExpBufferData buf;
	printQueryOpt my_popt = pset.popt;
	FILE	   *queryFout_copy = pset.queryFout;
	bool		queryFoutPipe_copy = pset.queryFoutPipe;
	bool		started_txn = false;
	bool		did_pager = false;
	int			ntuples;
	char		fetch_cmd[64];
	instr_time	before,
				after;
	int			flush_error;

	*elapsed_msec = 0;

	/* initialize print options for partial table output */
	my_popt.topt.start_table = true;
	my_popt.topt.stop_table = false;
	my_popt.topt.prior_records = 0;

	if (pset.timing)
		INSTR_TIME_SET_CURRENT(before);

	/* if we're not in a transaction, start one */
	if (PQtransactionStatus(pset.db) == PQTRANS_IDLE)
	{
		results = PQexec(pset.db, "BEGIN");
		OK = AcceptResult(results) &&
			(PQresultStatus(results) == PGRES_COMMAND_OK);
		PQclear(results);
		if (!OK)
			return false;
		started_txn = true;
	}

	/* Send DECLARE CURSOR */
	initPQExpBuffer(&buf);
	appendPQExpBuffer(&buf, "DECLARE _psql_cursor NO SCROLL CURSOR FOR\n%s",
					  query);

	results = PQexec(pset.db, buf.data);
	OK = AcceptResult(results) &&
		(PQresultStatus(results) == PGRES_COMMAND_OK);
	PQclear(results);
	termPQExpBuffer(&buf);
	if (!OK)
		goto cleanup;

	if (pset.timing)
	{
		INSTR_TIME_SET_CURRENT(after);
		INSTR_TIME_SUBTRACT(after, before);
		*elapsed_msec += INSTR_TIME_GET_MILLISEC(after);
	}

	snprintf(fetch_cmd, sizeof(fetch_cmd),
			 "FETCH FORWARD %d FROM _psql_cursor",
			 pset.fetch_count);

	/* prepare to write output to \g argument, if any */
	if (pset.gfname)
	{
		/* keep this code in sync with PrintQueryTuples */
		pset.queryFout = stdout;	/* so it doesn't get closed */

		/* open file/pipe */
		if (!setQFout(pset.gfname))
		{
			pset.queryFout = queryFout_copy;
			pset.queryFoutPipe = queryFoutPipe_copy;
			OK = false;
			goto cleanup;
		}
	}

	/* clear any pre-existing error indication on the output stream */
	clearerr(pset.queryFout);

	for (;;)
	{
		if (pset.timing)
			INSTR_TIME_SET_CURRENT(before);

		/* get FETCH_COUNT tuples at a time */
		results = PQexec(pset.db, fetch_cmd);

		if (pset.timing)
		{
			INSTR_TIME_SET_CURRENT(after);
			INSTR_TIME_SUBTRACT(after, before);
			*elapsed_msec += INSTR_TIME_GET_MILLISEC(after);
		}

		if (PQresultStatus(results) != PGRES_TUPLES_OK)
		{
			/* shut down pager before printing error message */
			if (did_pager)
			{
				ClosePager(pset.queryFout);
				pset.queryFout = queryFout_copy;
				pset.queryFoutPipe = queryFoutPipe_copy;
				did_pager = false;
			}

			OK = AcceptResult(results);
			psql_assert(!OK);
			PQclear(results);
			break;
		}

		ntuples = PQntuples(results);

		if (ntuples < pset.fetch_count)
		{
			/* this is the last result set, so allow footer decoration */
			my_popt.topt.stop_table = true;
		}
		else if (pset.queryFout == stdout && !did_pager)
		{
			/*
			 * If query requires multiple result sets, hack to ensure that
			 * only one pager instance is used for the whole mess
			 */
			pset.queryFout = PageOutput(100000, my_popt.topt.pager);
			did_pager = true;
		}

		printQuery(results, &my_popt, pset.queryFout, pset.logfile);

		PQclear(results);

		/* after the first result set, disallow header decoration */
		my_popt.topt.start_table = false;
		my_popt.topt.prior_records += ntuples;

		/*
		 * Make sure to flush the output stream, so intermediate results are
		 * visible to the client immediately.  We check the results because if
		 * the pager dies/exits/etc, there's no sense throwing more data at
		 * it.
		 */
		flush_error = fflush(pset.queryFout);

		/*
		 * Check if we are at the end, if a cancel was pressed, or if there
		 * were any errors either trying to flush out the results, or more
		 * generally on the output stream at all.  If we hit any errors
		 * writing things to the stream, we presume $PAGER has disappeared and
		 * stop bothering to pull down more data.
		 */
		if (ntuples < pset.fetch_count || cancel_pressed || flush_error ||
			ferror(pset.queryFout))
			break;
	}

	/* close \g argument file/pipe, restore old setting */
	if (pset.gfname)
	{
		/* keep this code in sync with PrintQueryTuples */
		setQFout(NULL);

		pset.queryFout = queryFout_copy;
		pset.queryFoutPipe = queryFoutPipe_copy;

		free(pset.gfname);
		pset.gfname = NULL;
	}
	else if (did_pager)
	{
		ClosePager(pset.queryFout);
		pset.queryFout = queryFout_copy;
		pset.queryFoutPipe = queryFoutPipe_copy;
	}

cleanup:
	if (pset.timing)
		INSTR_TIME_SET_CURRENT(before);

	/*
	 * We try to close the cursor on either success or failure, but on failure
	 * ignore the result (it's probably just a bleat about being in an aborted
	 * transaction)
	 */
	results = PQexec(pset.db, "CLOSE _psql_cursor");
	if (OK)
	{
		OK = AcceptResult(results) &&
			(PQresultStatus(results) == PGRES_COMMAND_OK);
	}
	PQclear(results);

	if (started_txn)
	{
		results = PQexec(pset.db, OK ? "COMMIT" : "ROLLBACK");
		OK &= AcceptResult(results) &&
			(PQresultStatus(results) == PGRES_COMMAND_OK);
		PQclear(results);
	}

	if (pset.timing)
	{
		INSTR_TIME_SET_CURRENT(after);
		INSTR_TIME_SUBTRACT(after, before);
		*elapsed_msec += INSTR_TIME_GET_MILLISEC(after);
	}

	return OK;
}
Example #14
0
/*
 * SendQuery: send the query string to the backend
 * (and print out results)
 *
 * Note: This is the "front door" way to send a query. That is, use it to
 * send queries actually entered by the user. These queries will be subject to
 * single step mode.
 * To send "back door" queries (generated by slash commands, etc.) in a
 * controlled way, use PSQLexec().
 *
 * Returns true if the query executed successfully, false otherwise.
 */
bool
SendQuery(const char *query)
{
	PGresult   *results;
	PGTransactionStatusType transaction_status;
	double		elapsed_msec = 0;
	bool		OK,
				on_error_rollback_savepoint = false;
	static bool on_error_rollback_warning = false;

	if (!pset.db)
	{
		psql_error("You are currently not connected to a database.\n");
		return false;
	}

	if (pset.singlestep)
	{
		char		buf[3];

		printf(_("***(Single step mode: verify command)*******************************************\n"
				 "%s\n"
				 "***(press return to proceed or enter x and return to cancel)********************\n"),
			   query);
		fflush(stdout);
		if (fgets(buf, sizeof(buf), stdin) != NULL)
			if (buf[0] == 'x')
				return false;
	}
	else if (pset.echo == PSQL_ECHO_QUERIES)
	{
		puts(query);
		fflush(stdout);
	}

	if (pset.logfile)
	{
		fprintf(pset.logfile,
				_("********* QUERY **********\n"
				  "%s\n"
				  "**************************\n\n"), query);
		fflush(pset.logfile);
	}

	SetCancelConn();

	transaction_status = PQtransactionStatus(pset.db);

	if (transaction_status == PQTRANS_IDLE &&
		!pset.autocommit &&
		!command_no_begin(query))
	{
		results = PQexec(pset.db, "BEGIN");
		if (PQresultStatus(results) != PGRES_COMMAND_OK)
		{
			psql_error("%s", PQerrorMessage(pset.db));
			PQclear(results);
			ResetCancelConn();
			return false;
		}
		PQclear(results);
		transaction_status = PQtransactionStatus(pset.db);
	}

	if (transaction_status == PQTRANS_INTRANS &&
		pset.on_error_rollback != PSQL_ERROR_ROLLBACK_OFF &&
		(pset.cur_cmd_interactive ||
		 pset.on_error_rollback == PSQL_ERROR_ROLLBACK_ON))
	{
		if (on_error_rollback_warning == false && pset.sversion < 80000)
		{
			fprintf(stderr, _("The server (version %d.%d) does not support savepoints for ON_ERROR_ROLLBACK.\n"),
					pset.sversion / 10000, (pset.sversion / 100) % 100);
			on_error_rollback_warning = true;
		}
		else
		{
			results = PQexec(pset.db, "SAVEPOINT pg_psql_temporary_savepoint");
			if (PQresultStatus(results) != PGRES_COMMAND_OK)
			{
				psql_error("%s", PQerrorMessage(pset.db));
				PQclear(results);
				ResetCancelConn();
				return false;
			}
			PQclear(results);
			on_error_rollback_savepoint = true;
		}
	}

	if (pset.fetch_count <= 0 || !is_select_command(query))
	{
		/* Default fetch-it-all-and-print mode */
		instr_time	before,
					after;

		if (pset.timing)
			INSTR_TIME_SET_CURRENT(before);

		results = PQexec(pset.db, query);

		/* these operations are included in the timing result: */
		ResetCancelConn();
		OK = (AcceptResult(results) && ProcessCopyResult(results));

		if (pset.timing)
		{
			INSTR_TIME_SET_CURRENT(after);
			INSTR_TIME_SUBTRACT(after, before);
			elapsed_msec = INSTR_TIME_GET_MILLISEC(after);
		}

		/* but printing results isn't: */
		if (OK)
			OK = PrintQueryResults(results);
	}
	else
	{
		/* Fetch-in-segments mode */
		OK = ExecQueryUsingCursor(query, &elapsed_msec);
		ResetCancelConn();
		results = NULL;			/* PQclear(NULL) does nothing */
	}

	/* If we made a temporary savepoint, possibly release/rollback */
	if (on_error_rollback_savepoint)
	{
		const char *svptcmd;

		transaction_status = PQtransactionStatus(pset.db);

		if (transaction_status == PQTRANS_INERROR)
		{
			/* We always rollback on an error */
			svptcmd = "ROLLBACK TO pg_psql_temporary_savepoint";
		}
		else if (transaction_status != PQTRANS_INTRANS)
		{
			/* If they are no longer in a transaction, then do nothing */
			svptcmd = NULL;
		}
		else
		{
			/*
			 * Do nothing if they are messing with savepoints themselves: If
			 * the user did RELEASE or ROLLBACK, our savepoint is gone. If
			 * they issued a SAVEPOINT, releasing ours would remove theirs.
			 */
			if (results &&
				(strcmp(PQcmdStatus(results), "SAVEPOINT") == 0 ||
				 strcmp(PQcmdStatus(results), "RELEASE") == 0 ||
				 strcmp(PQcmdStatus(results), "ROLLBACK") == 0))
				svptcmd = NULL;
			else
				svptcmd = "RELEASE pg_psql_temporary_savepoint";
		}

		if (svptcmd)
		{
			PGresult   *svptres;

			svptres = PQexec(pset.db, svptcmd);
			if (PQresultStatus(svptres) != PGRES_COMMAND_OK)
			{
				psql_error("%s", PQerrorMessage(pset.db));
				PQclear(svptres);

				PQclear(results);
				ResetCancelConn();
				return false;
			}
			PQclear(svptres);
		}
	}

	PQclear(results);

	/* Possible microtiming output */
	if (OK && pset.timing)
		printf(_("Time: %.3f ms\n"), elapsed_msec);

	/* check for events that may occur during query execution */

	if (pset.encoding != PQclientEncoding(pset.db) &&
		PQclientEncoding(pset.db) >= 0)
	{
		/* track effects of SET CLIENT_ENCODING */
		pset.encoding = PQclientEncoding(pset.db);
		pset.popt.topt.encoding = pset.encoding;
		SetVariable(pset.vars, "ENCODING",
					pg_encoding_to_char(pset.encoding));
	}

	PrintNotifications();

	return OK;
}
/*
 * get_tablespace_paths()
 *
 * Scans pg_tablespace and returns a malloc'ed array of all tablespace
 * paths. Its the caller's responsibility to free the array.
 */
static void
get_tablespace_paths(void)
{
	PGconn	   *conn = connectToServer(&old_cluster, "template1");
	PGresult   *res;
	int			tblnum;
	int			i_spclocation;
	char		query[QUERY_ALLOC];

	snprintf(query, sizeof(query),
			 "SELECT	%s "
			 "FROM	pg_catalog.pg_tablespace "
			 "WHERE	spcname != 'pg_default' AND "
			 "		spcname != 'pg_global'",
	/* 9.2 removed the spclocation column */
			 (GET_MAJOR_VERSION(old_cluster.major_version) <= 901) ?
	"spclocation" : "pg_catalog.pg_tablespace_location(oid) AS spclocation");

	res = executeQueryOrDie(conn, "%s", query);

	if ((os_info.num_old_tablespaces = PQntuples(res)) != 0)
		os_info.old_tablespaces = (char **) pg_malloc(
							   os_info.num_old_tablespaces * sizeof(char *));
	else
		os_info.old_tablespaces = NULL;

	i_spclocation = PQfnumber(res, "spclocation");

	for (tblnum = 0; tblnum < os_info.num_old_tablespaces; tblnum++)
	{
		struct stat statBuf;

		os_info.old_tablespaces[tblnum] = pg_strdup(
									 PQgetvalue(res, tblnum, i_spclocation));

		/*
		 * Check that the tablespace path exists and is a directory.
		 * Effectively, this is checking only for tables/indexes in
		 * non-existent tablespace directories.  Databases located in
		 * non-existent tablespaces already throw a backend error.
		 * Non-existent tablespace directories can occur when a data directory
		 * that contains user tablespaces is moved as part of pg_upgrade
		 * preparation and the symbolic links are not updated.
		 */
		if (stat(os_info.old_tablespaces[tblnum], &statBuf) != 0)
		{
			if (errno == ENOENT)
				report_status(PG_FATAL,
							  "tablespace directory \"%s\" does not exist\n",
							  os_info.old_tablespaces[tblnum]);
			else
				report_status(PG_FATAL,
						  "could not stat tablespace directory \"%s\": %s\n",
						   os_info.old_tablespaces[tblnum], strerror(errno));
		}
		if (!S_ISDIR(statBuf.st_mode))
			report_status(PG_FATAL,
						  "tablespace path \"%s\" is not a directory\n",
						  os_info.old_tablespaces[tblnum]);
	}

	PQclear(res);

	PQfinish(conn);

	return;
}
/*+++++++++++++++++++++++++ Main Program or Function +++++++++++++++*/
void SCIA_LV1_MATCH_STATE( PGconn *conn, bool be_verbose,
			   const struct mph_envi *mph, unsigned short numState,
			   const struct lads_scia *lads,
			   const struct sqads1_scia *sqads,
			   const struct state1_scia *state )
{
     register unsigned short ni;
     register int nr;

     char date_str1[UTC_STRING_LENGTH], date_str2[UTC_STRING_LENGTH];

     char sql_query[SQL_STR_SIZE], cbuff[SQL_STR_SIZE];
     char *sql_long_query;

     char           *pntr;
     unsigned short numMatch;
     int            i_indx, i_state, i_date, i_musec, i_softv;
     int            meta_id, nrow, numRows;
     size_t         nc, numChar, sql_long_sz;
     unsigned int   musec;
     double         delayedBy;

     PGresult *res;

     const double SecPerDay = 24. * 60 * 60;

     struct coord_envi    corners[NUM_CORNERS];
     struct stateinfo_rec *stateRow = NULL;
/*
 * get all potential matching states from table "stateinfo"
 */
     if ( numState == 0 ) return;
     MJD_2_DATETIME( state->mjd.days, state->mjd.secnd, 0, date_str1 );
     MJD_2_DATETIME( state[numState-1].mjd.days, state[numState-1].mjd.secnd, 
		     0, date_str2 );
     numChar = snprintf( sql_query, SQL_STR_SIZE, SELECT_FROM_STATEINFO,
			 date_str1, date_str2 );
     if ( be_verbose )
	  (void) printf( "%s(): %s [%-zd]\n", __func__, sql_query, numChar );
     if ( numChar >= SQL_STR_SIZE )
          NADC_RETURN_ERROR( NADC_ERR_STRLEN, "sql_query" );
     res = PQexec( conn, sql_query );
     if ( PQresultStatus( res ) != PGRES_TUPLES_OK )
	  NADC_GOTO_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
     if ( (numRows = PQntuples( res )) == 0 ) 
	  NADC_GOTO_ERROR( NADC_ERR_WARN, NO_DMOP_FOUND );
     stateRow = (struct stateinfo_rec *) 
	  malloc( numRows * sizeof(struct stateinfo_rec) );
     if ( stateRow == NULL )
	  NADC_GOTO_ERROR( NADC_ERR_ALLOC, "stateRow" );

     i_indx  = PQfnumber( res, "pk_stateinfo" );
     i_state = PQfnumber( res, "stateID" );
     i_date  = PQfnumber( res, "dateTimeStart" );
     i_musec = PQfnumber( res, "muSecStart" );
     i_softv = PQfnumber( res, "softVersion" );
     for ( nr = 0; nr < numRows; nr++ ) {
	  pntr = PQgetvalue( res, nr, i_indx );
	  stateRow[nr].indxDMOP = 
	       (unsigned int) strtoul( pntr, (char **) NULL, 10 );
	  pntr = PQgetvalue( res, nr, i_state );
	  stateRow[nr].stateID  = (unsigned char) atoi( pntr );
	  pntr = PQgetvalue( res, nr, i_date );
	  musec = (unsigned int) strtoul( PQgetvalue( res, nr, i_musec ),
					   (char **) NULL, 10 );
	  stateRow[nr].jday = DATETIME_2_JULIAN( pntr, musec );
	  pntr = PQgetvalue( res, nr, i_softv );
	  (void) nadc_strlcpy( stateRow[nr].softVersion, pntr, 4 );
	  stateRow[nr].indxState = USHRT_MAX;
	  stateRow[nr].dtMatch = 30 / SecPerDay;
     }
     PQclear( res );
/*
 * loop over states from files to identify matching states from DMOP list
 */
     for ( ni = 0; ni < numState; ni++ ) {
	  register double jday  = state[ni].mjd.days 
	       + (state[ni].mjd.secnd + state[ni].mjd.musec / 1e6) / SecPerDay;
	  
	  for ( nr = 0; nr < numRows; nr++ ) {
	       register double dtime = fabs(jday - stateRow[nr].jday);

	       if ( (unsigned char) state[ni].state_id == stateRow[nr].stateID
		    && stateRow[nr].dtMatch > dtime ) {
		    stateRow[nr].indxState  = ni;
		    stateRow[nr].dtMatch    = dtime;
	       }
	  }
     }
/*
 * remove false/double matches
 */
     for ( nr = 1; nr < numRows; nr++ ) {
	  if ( stateRow[nr-1].indxState == stateRow[nr].indxState ) {
	       if ( stateRow[nr-1].dtMatch > stateRow[nr].dtMatch )
		    stateRow[nr-1].indxState = USHRT_MAX;
	       else
		    stateRow[nr].indxState = USHRT_MAX;
	  }
     }
/*
 * obtain pk_meta from table meta__1P
 */
     numChar = snprintf( sql_query, SQL_STR_SIZE,
			 "SELECT pk_meta FROM meta__1P WHERE name=\'%s\'",
			 mph->product );
     if ( be_verbose )
          (void) printf( "%s(): %s [%-zd]\n", __func__, sql_query, numChar );
     if ( numChar >= SQL_STR_SIZE )
          NADC_RETURN_ERROR( NADC_ERR_STRLEN, "sql_query" );
      res = PQexec( conn, sql_query );
     if ( PQresultStatus( res ) != PGRES_TUPLES_OK ) {
          NADC_GOTO_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
     }
     if ( (nrow = PQntuples( res )) == 0 ) {
          NADC_GOTO_ERROR( NADC_ERR_FATAL, mph->product );
     }
     pntr = PQgetvalue( res, 0, 0 );
     meta_id = (int) strtol( pntr, (char **) NULL, 10 );
     PQclear( res );
/*
 * allocate memory for SQL string (maximum pk_stateinfo is 2 million ~ 7 digits)
 */
     sql_long_sz = (size_t) (42 + (numRows+1) * (7+1) + 4);
     if ( (sql_long_query = (char *) malloc( sql_long_sz )) == NULL ) {
	  free( stateRow );
	  NADC_RETURN_ERROR( NADC_ERR_ALLOC, "sql_long_query" );
     }
/*
 * insert all matches in table "stateinfo_meta__1P"
 */
     numChar = snprintf( sql_long_query, sql_long_sz, 
			 "INSERT INTO stateinfo_meta__1P VALUES(%-d,\'{",
			 meta_id );
     numMatch = 0;
     delayedBy = 0.;
     for ( nr = 0; nr < numRows; nr++ ) {
	  if ( (ni = stateRow[nr].indxState) == USHRT_MAX ) continue;

/* do not add entry for states without MDS attached */
	  if ( state[ni].flag_mds == (unsigned char) 1 ) continue;

	  nc = numChar;
	  if ( numMatch > 0 ) {
	       numChar += snprintf( sql_long_query+nc, sql_long_sz-nc, 
				    ",%-u", stateRow[nr].indxDMOP );
	  } else {
	       numChar += snprintf( sql_long_query+nc, sql_long_sz-nc, 
				    "%-u", stateRow[nr].indxDMOP );
	  }
	  if ( numChar >= sql_long_sz ) {
	       free( sql_long_query ); free( stateRow );
	       NADC_RETURN_ERROR( NADC_ERR_STRLEN, "sql_long_query" );
	  }
	  numMatch++;
	  delayedBy += (stateRow[nr].dtMatch *= SecPerDay);
     }
     nc = numChar;
     numChar += snprintf( sql_long_query+nc, sql_long_sz-nc, 
			  "}\',\'%1s\')", mph->proc_stage );
     if ( numChar >= sql_long_sz ) {
	  free( sql_long_query ); free( stateRow );
	  NADC_RETURN_ERROR( NADC_ERR_STRLEN, "sql_long_query" );
     }
     if ( be_verbose )
	  (void) printf( "%s(): %s [%-zd]\n", __func__, sql_long_query, numChar );
     res = PQexec( conn, sql_long_query );
     free( sql_long_query );
     if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
          NADC_GOTO_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
     PQclear( res );
/*
 * check number of matches found
 */
     if ( numMatch == 0 ) {
	  free( stateRow );
	  NADC_RETURN_ERROR( NADC_ERR_WARN, NO_MATCHES_FOUND );
     }
     delayedBy /= numMatch;
/*
 * update meta__1P (noEntryDMOP, delayedBy)
 */
     (void) snprintf( sql_query, SQL_STR_SIZE, 
		      "UPDATE meta__1P SET noEntryDMOP=%d,",
		      (int)(numState - numMatch) );
     (void) snprintf( sql_query, SQL_STR_SIZE, 
		      "%s delayedBy=%.3f", strcpy(cbuff,sql_query), 
		      delayedBy );
     numChar = snprintf( sql_query, SQL_STR_SIZE, "%s WHERE pk_meta=%d",
			 strcpy(cbuff,sql_query), meta_id );
     if ( be_verbose )
	  (void) printf( "%s(): %s [%-zd]\n", __func__, sql_query, numChar );
     if ( numChar >= SQL_STR_SIZE )
          NADC_RETURN_ERROR( NADC_ERR_STRLEN, "sql_query" );
     res = PQexec( conn, sql_query );
     if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
	  NADC_GOTO_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
     PQclear( res );
     (void) snprintf( cbuff, SQL_STR_SIZE,
		      "noEntryDMOP=%-d (States:%-hu DMOP:%-d), delayedBy=%-.3f",
		      numState - numMatch, numState, numRows, delayedBy );
     NADC_ERROR( NADC_ERR_NONE, cbuff );
/*
 * Start a transaction block
 */
     res = PQexec( conn, "BEGIN" );
     if ( PQresultStatus( res ) != PGRES_COMMAND_OK ) {
          NADC_GOTO_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
     }
     PQclear( res );
/*
 * update stateinfo (softVersion, orbitPhase, tile)
 */
     for ( nr = 0; nr < numRows; nr++ ) {
	  if ( (ni = stateRow[nr].indxState) == USHRT_MAX ) continue;

          /* do not add entry for states without MDS attached */
	  if ( state[ni].flag_mds == (unsigned char) 1 ) continue;

	  /* only update when procStage of product is higher */
	  if ( mph->proc_stage[0] < stateRow[nr].softVersion[1] ) continue;
	  stateRow[nr].softVersion[1] = mph->proc_stage[0];

	  (void) snprintf( sql_query, SQL_STR_SIZE, 
			   "UPDATE stateinfo SET softVersion=\'%s\',", 
			   stateRow[nr].softVersion );
	  (void) snprintf( sql_query, SQL_STR_SIZE, 
			   "%s orbitPhase=%f,", strcpy(cbuff,sql_query),
			   state[ni].orbit_phase );
	  if ( sqads[ni].flag_saa_region == (unsigned char) 1 )
	       (void) snprintf( sql_query, SQL_STR_SIZE, 
				"%s saaFlag=TRUE,", strcpy(cbuff,sql_query) );
	  else
	       (void) snprintf( sql_query, SQL_STR_SIZE, 
				"%s saaFlag=FALSE,", strcpy(cbuff,sql_query) );
	  CorrectLongitudes( lads[ni].corner, corners ); 
	  (void) snprintf( sql_query, SQL_STR_SIZE, 
			   GEO_POLY_FORMAT, strcpy(cbuff,sql_query), "tile=",
			   corners[0].lon / 1e6, corners[0].lat / 1e6,
			   corners[3].lon / 1e6, corners[3].lat / 1e6,
			   corners[2].lon / 1e6, corners[2].lat / 1e6,
			   corners[1].lon / 1e6, corners[1].lat / 1e6,
			   corners[0].lon / 1e6, corners[0].lat / 1e6,
			   4326 );
	  numChar = snprintf( sql_query, SQL_STR_SIZE, 
			      "%s WHERE pk_stateinfo=%u",
			      strcpy(cbuff,sql_query), stateRow[nr].indxDMOP );
	  if ( be_verbose )
	       (void) printf( "%s(): %s [%-zd]\n", __func__, sql_query, numChar );
	  if ( numChar >= SQL_STR_SIZE ) {
	       NADC_ERROR( NADC_ERR_STRLEN, "sql_query" );
	       PQclear( res );
	       res = PQexec( conn, "ROLLBACK" );
	       if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
		    NADC_ERROR( NADC_ERR_SQL,
				PQresultErrorMessage(res) );
	       goto done;
	  }
	  res = PQexec( conn, sql_query );
	  if ( PQresultStatus( res ) != PGRES_COMMAND_OK ) {
	       NADC_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
	       PQclear( res );
	       res = PQexec( conn, "ROLLBACK" );
	       if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
		    NADC_ERROR( NADC_ERR_SQL,
				PQresultErrorMessage(res) );
	       goto done;
	  }
	  PQclear( res );
     }
/*
 * end the transaction
 */
     res = PQexec( conn, "COMMIT" );
     if ( PQresultStatus( res ) != PGRES_COMMAND_OK )
          NADC_ERROR( NADC_ERR_SQL, PQresultErrorMessage(res) );
 done:
     PQclear( res );
     if ( stateRow != NULL ) free( stateRow );
}
Example #17
0
/*
 * Retrieve pkey column of a table related a given layer
 * And if success try also to retrieve a related pkey sequence
 */
static void ows_storage_fill_pkey(ows * o, ows_layer * l)
{
  buffer *sql;
  PGresult *res;

  assert(o);
  assert(l);
  assert(l->storage);

  sql = buffer_init();

  buffer_add_str(sql, "SELECT c.column_name FROM information_schema.constraint_column_usage c, pg_namespace n ");
  buffer_add_str(sql, "WHERE n.nspname = '");
  buffer_copy(sql, l->storage->schema);
  buffer_add_str(sql, "' AND c.table_name = '");
  buffer_copy(sql, l->storage->table);
  buffer_add_str(sql, "' AND c.constraint_name = (");

  buffer_add_str(sql, "SELECT c.conname FROM pg_class r, pg_constraint c, pg_namespace n ");
  buffer_add_str(sql, "WHERE r.oid = c.conrelid AND relname = '");
  buffer_copy(sql, l->storage->table);
  buffer_add_str(sql, "' AND r.relnamespace = n.oid AND n.nspname = '");
  buffer_copy(sql, l->storage->schema);
  buffer_add_str(sql, "' AND c.contype = 'p')");

  res = ows_psql_exec(o, sql->buf);
  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
    PQclear(res);
    buffer_free(sql);
    ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to access pg_* tables.", "pkey column");
    return;
  }

  /* Layer could have no Pkey indeed... (An SQL view for example) */
  if (l->pkey || PQntuples(res) == 1) {
    l->storage->pkey = buffer_init();
    if (l->pkey) {
      /*TODO check the column (l->pkey) in the table */
      buffer_copy(l->storage->pkey, l->pkey);
    } else {
      buffer_add_str(l->storage->pkey, PQgetvalue(res, 0, 0));
    }
    buffer_empty(sql);
    PQclear(res);

    /* Retrieve the Pkey column number */
    buffer_add_str(sql, "SELECT a.attnum FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n");
    buffer_add_str(sql, " WHERE a.attrelid = c.oid AND a.atttypid = t.oid AND n.nspname='");
    buffer_copy(sql, l->storage->schema);
    buffer_add_str(sql, "' AND c.relname='");
    buffer_copy(sql, l->storage->table);
    buffer_add_str(sql, "' AND a.attname='");
    buffer_copy(sql, l->storage->pkey);
    buffer_add_str(sql, "'");
    res = ows_psql_exec(o, sql->buf);
    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
      PQclear(res);
      buffer_free(sql);
      ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED, "Unable to find pkey column number.", "pkey_column number");
      return;
    }

    /* -1 because column number start at 1 */
    l->storage->pkey_column_number = atoi(PQgetvalue(res, 0, 0)) - 1;
    buffer_empty(sql);
    PQclear(res);

    /* Now try to find a sequence related to this Pkey */
    buffer_add_str(sql, "SELECT pg_get_serial_sequence('");
    buffer_copy(sql, l->storage->schema);
    buffer_add_str(sql, ".\"");
    buffer_copy(sql, l->storage->table);
    buffer_add_str(sql, "\"', '");
    buffer_copy(sql, l->storage->pkey);
    buffer_add_str(sql, "');");

    res = ows_psql_exec(o, sql->buf);
    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
      PQclear(res);
      buffer_free(sql);
      ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED,
                "Unable to use pg_get_serial_sequence.", "pkey_sequence retrieve");
      return;
    }

    /* Even if no sequence found, this function return an empty row
     * so we must check that result string returned > 0 char
     */
    if (PQntuples(res) == 1 && strlen((char *) PQgetvalue(res, 0, 0)) > 0) {
      l->storage->pkey_sequence = buffer_init();
      buffer_add_str(l->storage->pkey_sequence, PQgetvalue(res, 0, 0));
    }

    buffer_empty(sql);
    PQclear(res);
    /* Now try to find a DEFAULT value related to this Pkey */
    buffer_add_str(sql, "SELECT column_default FROM information_schema.columns WHERE table_schema = '");
    buffer_copy(sql, l->storage->schema);
    buffer_add_str(sql, "' AND table_name = '");
    buffer_copy(sql, l->storage->table);
    buffer_add_str(sql, "' AND column_name = '");
    buffer_copy(sql, l->storage->pkey);
    buffer_add_str(sql, "' AND table_catalog = current_database();");

    res = ows_psql_exec(o, sql->buf);
    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
      PQclear(res);
      buffer_free(sql);
      ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED,
                "Unable to SELECT column_default FROM information_schema.columns.",
                "pkey_default retrieve");
      return;
    }

    /* Even if no DEFAULT value found, this function return an empty row
     * so we must check that result string returned > 0 char
     */
    if (PQntuples(res) == 1 && strlen((char *) PQgetvalue(res, 0, 0)) > 0) {
      l->storage->pkey_default = buffer_init();
      buffer_add_str(l->storage->pkey_default, PQgetvalue(res, 0, 0));
    }
  }

  PQclear(res);
  buffer_free(sql);
}
/*
 * vacuum_one_database
 *
 * Process tables in the given database.  If the 'tables' list is empty,
 * process all tables in the database.
 *
 * Note that this function is only concerned with running exactly one stage
 * when in analyze-in-stages mode; caller must iterate on us if necessary.
 *
 * If concurrentCons is > 1, multiple connections are used to vacuum tables
 * in parallel.  In this case and if the table list is empty, we first obtain
 * a list of tables from the database.
 */
static void
vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
					int stage,
					SimpleStringList *tables,
					const char *host, const char *port,
					const char *username, enum trivalue prompt_password,
					int concurrentCons,
					const char *progname, bool echo, bool quiet)
{
	PQExpBufferData sql;
	PGconn	   *conn;
	SimpleStringListCell *cell;
	ParallelSlot *slots = NULL;
	SimpleStringList dbtables = {NULL, NULL};
	int			i;
	bool		failed = false;
	bool		parallel = concurrentCons > 1;
	const char *stage_commands[] = {
		"SET default_statistics_target=1; SET vacuum_cost_delay=0;",
		"SET default_statistics_target=10; RESET vacuum_cost_delay;",
		"RESET default_statistics_target;"
	};
	const char *stage_messages[] = {
		gettext_noop("Generating minimal optimizer statistics (1 target)"),
		gettext_noop("Generating medium optimizer statistics (10 targets)"),
		gettext_noop("Generating default (full) optimizer statistics")
	};

	Assert(stage == ANALYZE_NO_STAGE ||
		   (stage >= 0 && stage < ANALYZE_NUM_STAGES));

	conn = connectDatabase(dbname, host, port, username, prompt_password,
						   progname, false, true);

	if (!quiet)
	{
		if (stage != ANALYZE_NO_STAGE)
			printf(_("%s: processing database \"%s\": %s\n"),
				   progname, PQdb(conn), stage_messages[stage]);
		else
			printf(_("%s: vacuuming database \"%s\"\n"),
				   progname, PQdb(conn));
		fflush(stdout);
	}

	initPQExpBuffer(&sql);

	/*
	 * If a table list is not provided and we're using multiple connections,
	 * prepare the list of tables by querying the catalogs.
	 */
	if (parallel && (!tables || !tables->head))
	{
		PQExpBufferData buf;
		PGresult   *res;
		int			ntups;
		int			i;

		initPQExpBuffer(&buf);

		res = executeQuery(conn,
			"SELECT c.relname, ns.nspname FROM pg_class c, pg_namespace ns\n"
			 " WHERE relkind IN (\'r\', \'m\') AND c.relnamespace = ns.oid\n"
						   " ORDER BY c.relpages DESC;",
						   progname, echo);

		ntups = PQntuples(res);
		for (i = 0; i < ntups; i++)
		{
			appendPQExpBufferStr(&buf,
								 fmtQualifiedId(PQserverVersion(conn),
												PQgetvalue(res, i, 1),
												PQgetvalue(res, i, 0)));

			simple_string_list_append(&dbtables, buf.data);
			resetPQExpBuffer(&buf);
		}

		termPQExpBuffer(&buf);
		tables = &dbtables;

		/*
		 * If there are more connections than vacuumable relations, we don't
		 * need to use them all.
		 */
		if (concurrentCons > ntups)
			concurrentCons = ntups;
		if (concurrentCons <= 1)
			parallel = false;
		PQclear(res);
	}

	/*
	 * Setup the database connections. We reuse the connection we already have
	 * for the first slot.  If not in parallel mode, the first slot in the
	 * array contains the connection.
	 */
	slots = (ParallelSlot *) pg_malloc(sizeof(ParallelSlot) * concurrentCons);
	init_slot(slots, conn, progname);
	if (parallel)
	{
		for (i = 1; i < concurrentCons; i++)
		{
			conn = connectDatabase(dbname, host, port, username, prompt_password,
								   progname, false, true);
			init_slot(slots + i, conn, progname);
		}
	}

	/*
	 * Prepare all the connections to run the appropriate analyze stage, if
	 * caller requested that mode.
	 */
	if (stage != ANALYZE_NO_STAGE)
	{
		int			j;

		/* We already emitted the message above */

		for (j = 0; j < concurrentCons; j++)
			executeCommand((slots + j)->connection,
						   stage_commands[stage], progname, echo);
	}

	cell = tables ? tables->head : NULL;
	do
	{
		ParallelSlot *free_slot;
		const char *tabname = cell ? cell->val : NULL;

		prepare_vacuum_command(&sql, conn, vacopts, tabname);

		if (CancelRequested)
		{
			failed = true;
			goto finish;
		}

		/*
		 * Get the connection slot to use.  If in parallel mode, here we wait
		 * for one connection to become available if none already is.  In
		 * non-parallel mode we simply use the only slot we have, which we
		 * know to be free.
		 */
		if (parallel)
		{
			/*
			 * Get a free slot, waiting until one becomes free if none
			 * currently is.
			 */
			free_slot = GetIdleSlot(slots, concurrentCons, progname);
			if (!free_slot)
			{
				failed = true;
				goto finish;
			}

			free_slot->isFree = false;
		}
		else
			free_slot = slots;

		/*
		 * Execute the vacuum.  If not in parallel mode, this terminates the
		 * program in case of an error.  (The parallel case handles query
		 * errors in GetQueryResult through GetIdleSlot.)
		 */
		run_vacuum_command(free_slot->connection, sql.data,
						   echo, tabname, progname, parallel);

		if (cell)
			cell = cell->next;
	} while (cell != NULL);

	if (parallel)
	{
		int			j;

		for (j = 0; j < concurrentCons; j++)
		{
			/* wait for all connection to return the results */
			if (!GetQueryResult((slots + j)->connection, progname))
				goto finish;

			(slots + j)->isFree = true;
		}
	}

finish:
	for (i = 0; i < concurrentCons; i++)
		DisconnectDatabase(slots + i);
	pfree(slots);

	termPQExpBuffer(&sql);

	if (failed)
		exit(1);
}
Example #19
0
static void ows_layer_storage_fill(ows * o, ows_layer * l, bool is_geom)
{
  buffer * sql;
  PGresult *res;
  int i, end;

  assert(o);
  assert(l);
  assert(l->storage);

  sql = buffer_init();
  if (is_geom) buffer_add_str(sql, "SELECT srid, f_geometry_column FROM geometry_columns");
  else         buffer_add_str(sql, "SELECT srid, f_geography_column FROM geography_columns");

  buffer_add_str(sql, " WHERE f_table_schema='");
  buffer_copy(sql, l->storage->schema);
  buffer_add_str(sql, "' AND f_table_name='");
  buffer_copy(sql, l->storage->table);
  buffer_add_str(sql, "'");
  if (l->include_items) {
    buffer_add_str(sql, is_geom?" AND f_geometry_column IN ('":" AND f_geography_column IN ('");
    list_implode(sql, "','", l->include_items);
    buffer_add_str(sql, "')");
  }
  if (l->exclude_items) {
    buffer_add_str(sql, is_geom?" AND f_geometry_column NOT IN ('":" AND f_geography_column NOT IN ('");
    list_implode(sql, "','", l->exclude_items);
    buffer_add_str(sql, "')");
  }
  buffer_add_str(sql, ";");

  res = ows_psql_exec(o, sql->buf);
  buffer_empty(sql);

  if (PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) == 0) {
    PQclear(res);

    ows_error(o, OWS_ERROR_REQUEST_SQL_FAILED,
              "All config file layers are not availables in geometry_columns or geography_columns",
              "storage");
    return;
  }

  l->storage->srid = atoi(PQgetvalue(res, 0, 0));

  for (i = 0, end = PQntuples(res); i < end; i++)
    list_add_str(l->storage->geom_columns, PQgetvalue(res, i, 1));

  buffer_add_str(sql, "SELECT * FROM spatial_ref_sys WHERE srid=");
  buffer_add_str(sql, PQgetvalue(res, 0, 0));
  buffer_add_str(sql, " AND proj4text like '%%units=m%%'");

  PQclear(res);

  res = ows_psql_exec(o, sql->buf);
  buffer_free(sql);

  if (PQntuples(res) != 1)
    l->storage->is_degree = true;
  else
    l->storage->is_degree = false;

  PQclear(res);

  ows_storage_fill_pkey(o, l);
  ows_storage_fill_attributes(o, l);
  ows_storage_fill_not_null(o, l);
}
/*
 * Vacuum/analyze all connectable databases.
 *
 * In analyze-in-stages mode, we process all databases in one stage before
 * moving on to the next stage.  That ensure minimal stats are available
 * quickly everywhere before generating more detailed ones.
 */
static void
vacuum_all_databases(vacuumingOptions *vacopts,
					 bool analyze_in_stages,
					 const char *maintenance_db, const char *host,
					 const char *port, const char *username,
					 enum trivalue prompt_password,
					 int concurrentCons,
					 const char *progname, bool echo, bool quiet)
{
	PGconn	   *conn;
	PGresult   *result;
	PQExpBufferData connstr;
	int			stage;
	int			i;

	conn = connectMaintenanceDatabase(maintenance_db, host, port,
									  username, prompt_password, progname);
	result = executeQuery(conn,
			"SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",
						  progname, echo);
	PQfinish(conn);

	initPQExpBuffer(&connstr);
	if (analyze_in_stages)
	{
		/*
		 * When analyzing all databases in stages, we analyze them all in the
		 * fastest stage first, so that initial statistics become available
		 * for all of them as soon as possible.
		 *
		 * This means we establish several times as many connections, but
		 * that's a secondary consideration.
		 */
		for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
		{
			for (i = 0; i < PQntuples(result); i++)
			{
				resetPQExpBuffer(&connstr);
				appendPQExpBuffer(&connstr, "dbname=");
				appendConnStrVal(&connstr, PQgetvalue(result, i, 0));

				vacuum_one_database(connstr.data, vacopts,
									stage,
									NULL,
									host, port, username, prompt_password,
									concurrentCons,
									progname, echo, quiet);
			}
		}
	}
	else
	{
		for (i = 0; i < PQntuples(result); i++)
		{
			resetPQExpBuffer(&connstr);
			appendPQExpBuffer(&connstr, "dbname=");
			appendConnStrVal(&connstr, PQgetvalue(result, i, 0));

			vacuum_one_database(connstr.data, vacopts,
								ANALYZE_NO_STAGE,
								NULL,
								host, port, username, prompt_password,
								concurrentCons,
								progname, echo, quiet);
		}
	}
	termPQExpBuffer(&connstr);

	PQclear(result);
}
Example #21
0
static switch_status_t insert_cdr(const char *values)
{
    char *sql = NULL, *path = NULL;
    PGresult *res;

    sql = switch_mprintf("INSERT INTO %s (%s) VALUES (%s);", globals.db_table, globals.db_schema->columns, values);
    assert(sql);

    if (globals.debug) {
        switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_NOTICE, "Query: \"%s\"\n", sql);
    }

    switch_mutex_lock(globals.db_mutex);

    if (!globals.db_online || PQstatus(globals.db_connection) != CONNECTION_OK) {
        globals.db_connection = PQconnectdb(globals.db_info);
    }

    if (PQstatus(globals.db_connection) == CONNECTION_OK) {
        globals.db_online = 1;
    } else {
        switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_CRIT, "Connection to database failed: %s", PQerrorMessage(globals.db_connection));
        goto error;
    }

    res = PQexec(globals.db_connection, sql);
    if (PQresultStatus(res) != PGRES_COMMAND_OK) {
        switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_CRIT, "INSERT command failed: %s", PQresultErrorMessage(res));
        PQclear(res);
        goto error;
    }

    PQclear(res);
    switch_safe_free(sql);

    switch_mutex_unlock(globals.db_mutex);

    return SWITCH_STATUS_SUCCESS;


error:

    PQfinish(globals.db_connection);
    globals.db_online = 0;
    switch_mutex_unlock(globals.db_mutex);

    /* SQL INSERT failed for whatever reason. Spool the attempted query to disk */
    if (globals.spool_format == SPOOL_FORMAT_SQL) {
        path = switch_mprintf("%s%scdr-spool.sql", globals.spool_dir, SWITCH_PATH_SEPARATOR);
        assert(path);
        spool_cdr(path, sql);
    } else {
        path = switch_mprintf("%s%scdr-spool.csv", globals.spool_dir, SWITCH_PATH_SEPARATOR);
        assert(path);
        spool_cdr(path, values);
    }

    switch_safe_free(path);
    switch_safe_free(sql);

    return SWITCH_STATUS_FALSE;
}
Example #22
0
/*
 *	check_for_isn_and_int8_passing_mismatch()
 *
 *	contrib/isn relies on data type int8, and in 8.4 int8 can now be passed
 *	by value.  The schema dumps the CREATE TYPE PASSEDBYVALUE setting so
 *	it must match for the old and new servers.
 */
static void
check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster)
{
	int			dbnum;
	FILE	   *script = NULL;
	bool		found = false;
	char		output_path[MAXPGPATH];

	prep_status("Checking for contrib/isn with bigint-passing mismatch");

	if (old_cluster.controldata.float8_pass_by_value ==
		new_cluster.controldata.float8_pass_by_value)
	{
		/* no mismatch */
		check_ok();
		return;
	}

	snprintf(output_path, sizeof(output_path), "%s/contrib_isn_and_int8_pass_by_value.txt",
			 os_info.cwd);

	for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
	{
		PGresult   *res;
		bool		db_used = false;
		int			ntups;
		int			rowno;
		int			i_nspname,
					i_proname;
		DbInfo	   *active_db = &cluster->dbarr.dbs[dbnum];
		PGconn	   *conn = connectToServer(cluster, active_db->db_name);

		/* Find any functions coming from contrib/isn */
		res = executeQueryOrDie(conn,
								"SELECT n.nspname, p.proname "
								"FROM	pg_catalog.pg_proc p, "
								"		pg_catalog.pg_namespace n "
								"WHERE	p.pronamespace = n.oid AND "
								"		p.probin = '$libdir/isn'");

		ntups = PQntuples(res);
		i_nspname = PQfnumber(res, "nspname");
		i_proname = PQfnumber(res, "proname");
		for (rowno = 0; rowno < ntups; rowno++)
		{
			found = true;
			if (script == NULL && (script = fopen(output_path, "w")) == NULL)
				pg_log(PG_FATAL, "Could not open file \"%s\": %s\n",
					   output_path, getErrorText(errno));
			if (!db_used)
			{
				fprintf(script, "Database: %s\n", active_db->db_name);
				db_used = true;
			}
			fprintf(script, "  %s.%s\n",
					PQgetvalue(res, rowno, i_nspname),
					PQgetvalue(res, rowno, i_proname));
		}

		PQclear(res);

		PQfinish(conn);
	}

	if (script)
		fclose(script);

	if (found)
	{
		pg_log(PG_REPORT, "fatal\n");
		pg_log(PG_FATAL,
			   "Your installation contains \"contrib/isn\" functions which rely on the\n"
			   "bigint data type.  Your old and new clusters pass bigint values\n"
			   "differently so this cluster cannot currently be upgraded.  You can\n"
			   "manually upgrade databases that use \"contrib/isn\" facilities and remove\n"
			   "\"contrib/isn\" from the old cluster and restart the upgrade.  A list of\n"
			   "the problem functions is in the file:\n"
			   "    %s\n\n", output_path);
	}
	else
		check_ok();
}
Example #23
0
/*
 * Start the log streaming
 */
static void
StreamLog(void)
{
	PGresult   *res;
	uint32		timeline;
	XLogRecPtr	startpos;

	/*
	 * Connect in replication mode to the server
	 */
	conn = GetConnection();
	if (!conn)
		/* Error message already written in GetConnection() */
		return;

	/*
	 * Run IDENTIFY_SYSTEM so we can get the timeline and current xlog
	 * position.
	 */
	res = PQexec(conn, "IDENTIFY_SYSTEM");
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, _("%s: could not send replication command \"%s\": %s"),
				progname, "IDENTIFY_SYSTEM", PQerrorMessage(conn));

		disconnect_and_exit(1);
	}
	if (PQntuples(res) != 1 || PQnfields(res) != 3)
	{
		fprintf(stderr,
				_("%s: could not identify system: got %d rows and %d fields, expected %d rows and %d fields\n"),
				progname, PQntuples(res), PQnfields(res), 1, 3);

		disconnect_and_exit(1);
	}
	timeline = atoi(PQgetvalue(res, 0, 1));
	if (sscanf(PQgetvalue(res, 0, 2), "%X/%X", &startpos.xlogid, &startpos.xrecoff) != 2)
	{
		fprintf(stderr,
				_("%s: could not parse transaction log location \"%s\"\n"),
				progname, PQgetvalue(res, 0, 2));
		disconnect_and_exit(1);
	}
	PQclear(res);

	/*
	 * Figure out where to start streaming.
	 */
	startpos = FindStreamingStart(startpos, timeline);

	/*
	 * Always start streaming at the beginning of a segment
	 */
	startpos.xrecoff -= startpos.xrecoff % XLOG_SEG_SIZE;

	/*
	 * Start the replication
	 */
	if (verbose)
		fprintf(stderr,
				_("%s: starting log streaming at %X/%X (timeline %u)\n"),
				progname, startpos.xlogid, startpos.xrecoff, timeline);

	ReceiveXlogStream(conn, startpos, timeline, NULL, basedir,
					  stop_streaming, standby_message_timeout, false);

	PQfinish(conn);
}
Example #24
0
/*
 * check_for_reg_data_type_usage()
 *	pg_upgrade only preserves these system values:
 *		pg_class.relfilenode
 *		pg_type.oid
 *		pg_enum.oid
 *
 *	Most of the reg* data types reference system catalog info that is
 *	not preserved, and hence these data types cannot be used in user
 *	tables upgraded by pg_upgrade.
 */
static void
check_for_reg_data_type_usage(ClusterInfo *cluster)
{
	int			dbnum;
	FILE	   *script = NULL;
	bool		found = false;
	char		output_path[MAXPGPATH];

	prep_status("Checking for reg* system OID user data types");

	snprintf(output_path, sizeof(output_path), "%s/tables_using_reg.txt",
			 os_info.cwd);

	for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)
	{
		PGresult   *res;
		bool		db_used = false;
		int			ntups;
		int			rowno;
		int			i_nspname,
					i_relname,
					i_attname;
		DbInfo	   *active_db = &cluster->dbarr.dbs[dbnum];
		PGconn	   *conn = connectToServer(cluster, active_db->db_name);

		res = executeQueryOrDie(conn,
								"SELECT n.nspname, c.relname, a.attname "
								"FROM	pg_catalog.pg_class c, "
								"		pg_catalog.pg_namespace n, "
								"		pg_catalog.pg_attribute a "
								"WHERE	c.oid = a.attrelid AND "
								"		NOT a.attisdropped AND "
								"		a.atttypid IN ( "
		  "			'pg_catalog.regproc'::pg_catalog.regtype, "
								"			'pg_catalog.regprocedure'::pg_catalog.regtype, "
		  "			'pg_catalog.regoper'::pg_catalog.regtype, "
								"			'pg_catalog.regoperator'::pg_catalog.regtype, "
		 "			'pg_catalog.regclass'::pg_catalog.regtype, "
		/* regtype.oid is preserved, so 'regtype' is OK */
		"			'pg_catalog.regconfig'::pg_catalog.regtype, "
								"			'pg_catalog.regdictionary'::pg_catalog.regtype) AND "
								"		c.relnamespace = n.oid AND "
							  "		n.nspname != 'pg_catalog' AND "
						 "		n.nspname != 'information_schema'");

		ntups = PQntuples(res);
		i_nspname = PQfnumber(res, "nspname");
		i_relname = PQfnumber(res, "relname");
		i_attname = PQfnumber(res, "attname");
		for (rowno = 0; rowno < ntups; rowno++)
		{
			found = true;
			if (script == NULL && (script = fopen(output_path, "w")) == NULL)
				pg_log(PG_FATAL, "Could not open file \"%s\": %s\n",
					   output_path, getErrorText(errno));
			if (!db_used)
			{
				fprintf(script, "Database: %s\n", active_db->db_name);
				db_used = true;
			}
			fprintf(script, "  %s.%s.%s\n",
					PQgetvalue(res, rowno, i_nspname),
					PQgetvalue(res, rowno, i_relname),
					PQgetvalue(res, rowno, i_attname));
		}

		PQclear(res);

		PQfinish(conn);
	}

	if (script)
		fclose(script);

	if (found)
	{
		pg_log(PG_REPORT, "fatal\n");
		pg_log(PG_FATAL,
			   "Your installation contains one of the reg* data types in user tables.\n"
			   "These data types reference system OIDs that are not preserved by\n"
			   "pg_upgrade, so this cluster cannot currently be upgraded.  You can\n"
			   "remove the problem tables and restart the upgrade.  A list of the problem\n"
			   "columns is in the file:\n"
			   "    %s\n\n", output_path);
	}
	else
		check_ok();
}
Example #25
0
void
sql_clear_query( int *qryhandle ) {
    PQclear((PGresult *)*qryhandle);
}
Example #26
0
/*
 * get_rel_infos()
 *
 * gets the relinfos for all the user tables of the database referred
 * by "db".
 *
 * NOTE: we assume that relations/entities with oids greater than
 * FirstNormalObjectId belongs to the user
 */
static void
get_rel_infos(ClusterInfo *cluster, DbInfo *dbinfo)
{
	PGconn	   *conn = connectToServer(cluster,
									   dbinfo->db_name);
	PGresult   *res;
	RelInfo    *relinfos;
	int			ntups;
	int			relnum;
	int			num_rels = 0;
	char	   *nspname = NULL;
	char	   *relname = NULL;
	char	   *tablespace = NULL;
	int			i_spclocation,
				i_nspname,
				i_relname,
				i_oid,
				i_relfilenode,
				i_reltablespace;
	char		query[QUERY_ALLOC];
	char	   *last_namespace = NULL,
			   *last_tablespace = NULL;

	/*
	 * pg_largeobject contains user data that does not appear in pg_dump
	 * --schema-only output, so we have to copy that system table heap and
	 * index.  We could grab the pg_largeobject oids from template1, but it is
	 * easy to treat it as a normal table. Order by oid so we can join old/new
	 * structures efficiently.
	 */

	snprintf(query, sizeof(query),
		/* get regular heap */
			"WITH regular_heap (reloid) AS ( "
			"	SELECT c.oid "
			"	FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n "
			"		   ON c.relnamespace = n.oid "
			"	LEFT OUTER JOIN pg_catalog.pg_index i "
			"		   ON c.oid = i.indexrelid "
			"	WHERE relkind IN ('r', 'm', 'i', 'S') AND "
		/*
		 * pg_dump only dumps valid indexes;  testing indisready is necessary in
		 * 9.2, and harmless in earlier/later versions.
		 */
			"		i.indisvalid IS DISTINCT FROM false AND "
			"		i.indisready IS DISTINCT FROM false AND "
		/* exclude possible orphaned temp tables */
			"	  ((n.nspname !~ '^pg_temp_' AND "
			"	    n.nspname !~ '^pg_toast_temp_' AND "
		/* skip pg_toast because toast index have relkind == 'i', not 't' */
			"	    n.nspname NOT IN ('pg_catalog', 'information_schema', "
			"							'binary_upgrade', 'pg_toast') AND "
			"		  c.oid >= %u) OR "
			"	  (n.nspname = 'pg_catalog' AND "
	"    relname IN ('pg_largeobject', 'pg_largeobject_loid_pn_index'%s) ))), "
		/*
		 * We have to gather the TOAST tables in later steps because we
		 * can't schema-qualify TOAST tables.
		 */
		 /* get TOAST heap */
			"	toast_heap (reloid) AS ( "
			"	SELECT reltoastrelid "
			"	FROM regular_heap JOIN pg_catalog.pg_class c "
			"		ON regular_heap.reloid = c.oid "
			"		AND c.reltoastrelid != %u), "
		 /* get indexes on regular and TOAST heap */
			"	all_index (reloid) AS ( "
			"	SELECT indexrelid "
			"	FROM pg_index "
			"	WHERE indisvalid "
			"    AND indrelid IN (SELECT reltoastrelid "
			"        FROM (SELECT reloid FROM regular_heap "
			"			   UNION ALL "
			"			   SELECT reloid FROM toast_heap) all_heap "
			"            JOIN pg_catalog.pg_class c "
			"            ON all_heap.reloid = c.oid "
			"            AND c.reltoastrelid != %u)) "
		/* get all rels */
			"SELECT c.oid, n.nspname, c.relname, "
			"	c.relfilenode, c.reltablespace, %s "
			"FROM (SELECT reloid FROM regular_heap "
			"	   UNION ALL "
			"	   SELECT reloid FROM toast_heap  "
			"	   UNION ALL "
			"	   SELECT reloid FROM all_index) all_rels "
			"  JOIN pg_catalog.pg_class c "
			"		ON all_rels.reloid = c.oid "
			"  JOIN pg_catalog.pg_namespace n "
			"	   ON c.relnamespace = n.oid "
			"  LEFT OUTER JOIN pg_catalog.pg_tablespace t "
			"	   ON c.reltablespace = t.oid "
	/* we preserve pg_class.oid so we sort by it to match old/new */
			"ORDER BY 1;",
			FirstNormalObjectId,
	/* does pg_largeobject_metadata need to be migrated? */
			(GET_MAJOR_VERSION(old_cluster.major_version) <= 804) ?
	"" : ", 'pg_largeobject_metadata', 'pg_largeobject_metadata_oid_index'",
	InvalidOid, InvalidOid,
	/* 9.2 removed the spclocation column */
			(GET_MAJOR_VERSION(cluster->major_version) <= 901) ?
			"t.spclocation" : "pg_catalog.pg_tablespace_location(t.oid) AS spclocation");

	res = executeQueryOrDie(conn, "%s", query);

	ntups = PQntuples(res);

	relinfos = (RelInfo *) pg_malloc(sizeof(RelInfo) * ntups);

	i_oid = PQfnumber(res, "oid");
	i_nspname = PQfnumber(res, "nspname");
	i_relname = PQfnumber(res, "relname");
	i_relfilenode = PQfnumber(res, "relfilenode");
	i_reltablespace = PQfnumber(res, "reltablespace");
	i_spclocation = PQfnumber(res, "spclocation");

	for (relnum = 0; relnum < ntups; relnum++)
	{
		RelInfo    *curr = &relinfos[num_rels++];

		curr->reloid = atooid(PQgetvalue(res, relnum, i_oid));

		nspname = PQgetvalue(res, relnum, i_nspname);
		curr->nsp_alloc = false;

		/*
		 * Many of the namespace and tablespace strings are identical, so we
		 * try to reuse the allocated string pointers where possible to reduce
		 * memory consumption.
		 */
		/* Can we reuse the previous string allocation? */
		if (last_namespace && strcmp(nspname, last_namespace) == 0)
			curr->nspname = last_namespace;
		else
		{
			last_namespace = curr->nspname = pg_strdup(nspname);
			curr->nsp_alloc = true;
		}

		relname = PQgetvalue(res, relnum, i_relname);
		curr->relname = pg_strdup(relname);

		curr->relfilenode = atooid(PQgetvalue(res, relnum, i_relfilenode));
		curr->tblsp_alloc = false;

		/* Is the tablespace oid non-zero? */
		if (atooid(PQgetvalue(res, relnum, i_reltablespace)) != 0)
		{
			/*
			 * The tablespace location might be "", meaning the cluster
			 * default location, i.e. pg_default or pg_global.
			 */
			tablespace = PQgetvalue(res, relnum, i_spclocation);

			/* Can we reuse the previous string allocation? */
			if (last_tablespace && strcmp(tablespace, last_tablespace) == 0)
				curr->tablespace = last_tablespace;
			else
			{
				last_tablespace = curr->tablespace = pg_strdup(tablespace);
				curr->tblsp_alloc = true;
			}
		}
		else
			/* A zero reltablespace oid indicates the database tablespace. */
			curr->tablespace = dbinfo->db_tablespace;
	}
	PQclear(res);

	PQfinish(conn);

	dbinfo->rel_arr.rels = relinfos;
	dbinfo->rel_arr.nrels = num_rels;
}
Example #27
0
/**
 * \brief main function for the pkgagent
 *
 * There are 2 ways to use the pkgagent agent:
 *   1. Command Line Analysis :: test a rpm file from the command line
 *   2. Agent Based Analysis  :: run from the scheduler
 *
 * +-----------------------+
 * | Command Line Analysis |
 * +-----------------------+
 *
 * To analyze a rpm file from the command line:
 *   file :: if files are rpm package listed, display their meta data
 *   -v   :: verbose (-vv = more verbose)
 *
 *   example:
 *     $ ./pkgagent rpmfile
 *
 * +----------------------+
 * | Agent Based Analysis |
 * +----------------------+
 *
 * To run the pkgagent as an agent simply run with no command line args
 *   no file :: process data from the scheduler
 *   -i      :: initialize the database, then exit
 *
 *   example:
 *     $ upload_pk | ./pkgagent
 *
 * \param argc the number of command line arguments
 * \param argv the command line arguments
 * \return 0 on a successful program execution
 */
int	main	(int argc, char *argv[])
{
    int c;
    char *agent_desc = "Pulls metadata out of RPM or DEBIAN packages";
    //struct rpmpkginfo *glb_rpmpi;
    //struct debpkginfo *glb_debpi;
    int Agent_pk;
    int ars_pk = 0;

    int upload_pk = 0;           // the upload primary key
    int user_pk = 0;           // the upload primary key
    char *AgentARSName = "pkgagent_ars";
    int rv;
    PGresult *ars_result;
    char sqlbuf[1024];
    char *COMMIT_HASH;
    char *VERSION;
    char agent_rev[MAXCMD];
    int CmdlineFlag = 0; /* run from command line flag, 1 yes, 0 not */

    fo_scheduler_connect(&argc, argv, &db_conn);

    //glb_rpmpi = (struct rpmpkginfo *)malloc(sizeof(struct rpmpkginfo));
    //glb_debpi = (struct debpkginfo *)malloc(sizeof(struct debpkginfo));

    COMMIT_HASH = fo_sysconfig("pkgagent", "COMMIT_HASH");
    VERSION = fo_sysconfig("pkgagent", "VERSION");
    sprintf(agent_rev, "%s.%s", VERSION, COMMIT_HASH);
    Agent_pk = fo_GetAgentKey(db_conn, basename(argv[0]), 0, agent_rev, agent_desc);

    /* Process command-line */
    while((c = getopt(argc,argv,"ic:CvVh")) != -1)
    {
        switch(c)
        {
        case 'i':
            PQfinish(db_conn);  /* DB was opened above, now close it and exit */
            exit(0);
        case 'v':
            Verbose++;
            break;
        case 'c':
            break; /* handled by fo_scheduler_connect() */
        case 'C':
            CmdlineFlag = 1;
            break;
        case 'V':
            printf("%s", BuildVersion);
            PQfinish(db_conn);
            return(0);
        default:
            Usage(argv[0]);
            PQfinish(db_conn);
            exit(-1);
        }
    }
    /* If no args, run from scheduler! */
    if (CmdlineFlag == 0)
    {
        user_pk = fo_scheduler_userID(); /* get user_pk for user who queued the agent */

        while(fo_scheduler_next())
        {
            upload_pk = atoi(fo_scheduler_current());

            /* Check Permissions */
            if (GetUploadPerm(db_conn, upload_pk, user_pk) < PERM_WRITE)
            {
                LOG_ERROR("You have no update permissions on upload %d", upload_pk);
                continue;
            }

            if (Verbose) {
                printf("PKG: pkgagent read %d\n", upload_pk);
            }
            if (upload_pk ==0) continue;

            /* check if pkgagent ars table exist?
             * if exist, check duplicate request
             * if not exist, don't check duplicate request
             */
            rv = fo_tableExists(db_conn, AgentARSName);
            if (rv)
            {
                /* check ars table to see if this is duplicate request*/
                snprintf(sqlbuf, sizeof(sqlbuf),
                         "select ars_pk from pkgagent_ars,agent \
          where agent_pk=agent_fk and ars_success=true \
          and upload_fk='%d' and agent_fk='%d'",
                         upload_pk, Agent_pk);
                ars_result = PQexec(db_conn, sqlbuf);
                if (fo_checkPQresult(db_conn, ars_result, sqlbuf, __FILE__, __LINE__)) exit(-1);
                if (PQntuples(ars_result) > 0)
                {
                    PQclear(ars_result);
                    LOG_WARNING("Ignoring requested pkgagent analysis of upload %d - Results are already in database.\n",upload_pk);
                    continue;
                }
                PQclear(ars_result);
            }
            /* Record analysis start in pkgagent_ars, the pkgagent audit trail. */
            ars_pk = fo_WriteARS(db_conn, ars_pk, upload_pk, Agent_pk, AgentARSName, 0, 0);

            /* process the upload_pk pkgagent */
            if(ProcessUpload(upload_pk) != 0) return -1;

            /* Record analysis success in pkgagent_ars. */
            if (ars_pk) fo_WriteARS(db_conn, ars_pk, upload_pk, Agent_pk, AgentARSName, 0, 1);
        }
    }
    else
    {
        if (Verbose) {
            printf("DEBUG: running in cli mode, processing file(s)\n");
        }
        for (; optind < argc; optind++)
        {
            struct rpmpkginfo *rpmpi;
            rpmpi = (struct rpmpkginfo *)malloc(sizeof(struct rpmpkginfo));
            rpmReadConfigFiles(NULL, NULL);
            //if(ProcessUpload(atoi(argv[optind])) == 0)
            if(GetMetadata(argv[optind],rpmpi) != -1)
                printf("OK\n");
            else
                printf("Fail\n");
#ifdef _RPM_4_4_COMPAT
            rpmFreeCrypto();
            int i;
            for(i=0; i< rpmpi->req_size; i++)
                free(rpmpi->requires[i]);
#endif /* After RPM4.4 version*/
            free(rpmpi->requires);
            free(rpmpi);
            rpmFreeMacros(NULL);
        }
    }

    PQfinish(db_conn);
    fo_scheduler_disconnect(0);
    return(0);
} /* main() */
Example #28
0
// check if the wuxing time cycle amplifies the used skill
// returns 1 if it does, 0 if not
int check_wuxing_time(Element dmg_element)
{
	// done in the database to keep the multiplayer in sync, localtime() wouldn't suit this
	PGresult *res;
	res = PQexecPrepared(conn, "get_hour", 0, NULL, NULL, NULL, 0);
		if (PQresultStatus(res) != PGRES_COMMAND_OK)
			syslog(LOG_DEBUG,_("get_hour failed"));

	int hour = atoi(PQgetvalue(res,0,0)); /* return now() - last_logout */
	PQclear(res);


// The real Wu xing cycle has two fire sequences, is this bad for the game balance?
// first time (in medicine theory) is supposed the be empirical organs only,
// second time is supposed to be non-empirical organs only. Can this info be used to balance fire?

// Also, metal would almost never get any bonuses, since few people would play night time
/*
http://www.chuntianacademy.com/wu-xing
03-07 metal
07-11 earth
11-15 fire (first time)
15-19 water
19-23 fire (second time, "non-empirical")
23-03 wood
*/



	switch (hour)
		{
		case 3:
		case 4:
		case 5:
		case 6:
			{
			if (dmg_element == ELEM_METAL)
				return 1;

			break;
			}
		case 7:
		case 8:
		case 9:
		case 10:
			{
			if (dmg_element == ELEM_EARTH)
				return 1;

			break;
			}
		case 11:
		case 12:
		case 13:
		case 14:
			{
			if (dmg_element == ELEM_FIRE)
				return 1;

			break;
			}
		case 15:
		case 16:
		case 17:
		case 18:
			{
			if (dmg_element == ELEM_WATER)
				return 1;

			break;
			}
		case 19:
		case 20:
		case 21:
		case 22:
			{
			if (dmg_element == ELEM_FIRE)
				return 1;

			break;
			}
		case 23:
		case 0:
		case 1:
		case 2:
			{
			if (dmg_element == ELEM_WOOD)
				return 1;

			break;
			}

		default:
			break;
		}


// no bonus
return 0;
}
Example #29
0
/* this function return the password from database. */
int32_t pppd__pgsql_password(PGconn **pgsql, uint8_t *name, uint8_t *secret_name, int32_t *secret_length) {

	/* some common variables. */
	uint8_t query[1024];
	uint8_t query_extended[1024];
	int32_t is_null  = 0;
	uint32_t count   = 0;
	uint32_t found   = 0;
	uint8_t *row     = 0;
	uint8_t *field   = NULL;
	PGresult *result = NULL;

	/* build query for database. */
	snprintf((char *)query, 1024, "SELECT %s, %s, %s FROM %s WHERE %s='%s'", pppd_pgsql_column_pass, pppd_pgsql_column_client_ip, pppd_pgsql_column_server_ip, pppd_pgsql_table, pppd_pgsql_column_user, name);

	/* check if we have an additional postgresql condition. */
	if (pppd_pgsql_condition != NULL) {

		/* build extended query for database. */
		snprintf((char *)query_extended, 1024, " AND %s", pppd_pgsql_condition);

		/* only write 1023 bytes, because strncat writes 1023 bytes plus the terminating null byte. */
		strncat((char *)query, (char *)query_extended, 1023);

		/* clear the memory with the extended query, to build next one if required. */
		memset(query_extended, 0, sizeof(query_extended));
	}

	/* check if we should set an exclusive read lock. */
	if (pppd_pgsql_exclusive     == 1 &&
	    pppd_pgsql_authoritative == 1 &&
	    pppd_pgsql_column_update != NULL) {

		/* only write 1023 bytes, because strncat writes 1023 bytes plus the terminating null byte. */
		strncat((char *)query, " FOR UPDATE", 1023);
	}

	/* loop through number of query retries. */
	for (count = pppd_pgsql_retry_query; count > 0 ; count--) {

		/* check if query was successfully executed. */
		if ((result = PQexec(*pgsql, (char *)query)) != NULL) {

			/* indicate that we fetch a result. */
			found = 1;

			/* query result was ok, so break loop. */
			break;
		}

		/* clear memory to avoid leaks. */
		PQclear(result);
	}

	/* check if no query was executed successfully, very bad :) */
	if (found == 0) {

		/* something on executing query failed. */
		pppd__pgsql_error((uint8_t *)PQerrorMessage(*pgsql));

		/* return with error and terminate link. */
		return PPPD_SQL_ERROR_QUERY;
	}

	/* check if postgresql should return data. */
	if (PQnfields(result) == 0) {

		/* something on executing query failed. */
		pppd__pgsql_error((uint8_t *)PQerrorMessage(*pgsql));

		/* clear memory to avoid leaks. */
		PQclear(result);

		/* return with error and terminate link. */
		return PPPD_SQL_ERROR_QUERY;
	}

	/* check if we have multiple user accounts. */
	if ((PQntuples(result) > 1) && (pppd_pgsql_ignore_multiple == 0)) {

		/* multiple user accounts found. */
		error("Plugin %s: Multiple accounts for %s found in database\n", PLUGIN_NAME_PGSQL, name);

		/* clear memory to avoid leaks. */
		PQclear(result);

		/* return with error and terminate link. */
		return PPPD_SQL_ERROR_QUERY;
	}

	/* loop through all columns. */
	for (count = 0; count < PQnfields(result); count++) {

		/* fetch postgresql field name. */
		field = (uint8_t *)PQfname(result, count);

		/* fetch NULL information. */
		is_null = PQgetisnull(result, 0, count);

		/* first check what result we should get. */
		if (is_null == 0) {

			/* fetch column. */
			row = (uint8_t *)PQgetvalue(result, 0, count);
		} else {

			/* set row to string NULL. */
			row = (uint8_t *)"NULL";
		}

		/* check if column is NULL. */
		if ((is_null == 1) && (pppd_pgsql_ignore_null == 0)) {

			/* NULL user account found. */
			error("Plugin %s: The column %s for %s is NULL in database\n", PLUGIN_NAME_PGSQL, field, name);

			/* clear memory to avoid leaks. */
			PQclear(result);

			/* return with error and terminate link. */
			return PPPD_SQL_ERROR_QUERY;
		}

		/* check if we found password. */
		if (count == 0) {

			/* cleanup memory. */
			memset(secret_name, 0, sizeof(secret_name));

			/* copy password to secret. */
			strncpy((char *)secret_name, (char *)row, MAXSECRETLEN);
			*secret_length = strlen((char *)secret_name);
		}

		/* check if we found client ip. */
		if (count == 1) {

			/* check if ip address was successfully converted into binary data. */
			if (inet_aton((char *)row, (struct in_addr *) &client_ip) == 0) {

				/* error on converting ip address. */
				error("Plugin %s: Client IP address %s is not valid\n", PLUGIN_NAME_PGSQL, row);

				/* clear memory to avoid leaks. */
				PQclear(result);

				/* return with error and terminate link. */
				return PPPD_SQL_ERROR_QUERY;
			}
		}

		/* check if we found server ip. */
		if (count == 2) {

			/* check if ip address was successfully converted into binary data. */
			if (inet_aton((char *)row, (struct in_addr *) &server_ip) == 0) {

				/* error on converting ip address. */
				error("Plugin %s: Server IP address %s is not valid\n", PLUGIN_NAME_PGSQL, row);

				/* clear memory to avoid leaks. */
				PQclear(result);

				/* return with error and terminate link. */
				return PPPD_SQL_ERROR_QUERY;
			}
		}
	}

	/* clear memory to avoid leaks. */
	PQclear(result);

	/* if no error was found, return zero. */
	return 0;
}
Example #30
0
PgSqlDatatable::~PgSqlDatatable()
{
    PQclear((PGresult*)_res);
}