psql.c

关系型数据库 Postgresql 6.5.2

				table[i] = (char *) malloc(PQgetlength(res, i, 1) * sizeof(char) + 3);
				if (table[i] == NULL)
					perror("malloc");
				strcpy(table[i], "\"");
				strcat(table[i], PQgetvalue(res, i, 1));
				strcat(table[i], "\"");
			}

			PQclear(res);
			for (i = 0; i < nColumns; i++)
				tableDesc(pset, table[i], fout);
			free(table);
		}
		else
		{
			/* Display the information */

			fprintf(fout, "Database    = %s\n", PQdb(pset->db));
			fprintf(fout, " +------------------+----------------------------------+----------+\n");
			fprintf(fout, " |  Owner           |             Relation             |   Type   |\n");
			fprintf(fout, " +------------------+----------------------------------+----------+\n");

			/* next, print out the instances */
			for (i = 0; i < PQntuples(res); i++)
			{
				fprintf(fout, " | %-16.16s", PQgetvalue(res, i, 0));
				fprintf(fout, " | %-32.32s | ", PQgetvalue(res, i, 1));
				rk = PQgetvalue(res, i, 2);
				rr = PQgetvalue(res, i, 3);
				if (strcmp(rk, "r") == 0)
					fprintf(fout, "%-8.8s |", (rr[0] == 't') ? "view?" : "table");
				else if (strcmp(rk, "i") == 0)
					fprintf(fout, "%-8.8s |", "index");
				else
					fprintf(fout, "%-8.8s |", "sequence");
				fprintf(fout, "\n");
			}
			fprintf(fout, " +------------------+----------------------------------+----------+\n");
			fprintf(fout, "\n");
			PQclear(res);
		}
		if (usePipe)
		{
			pclose(fout);
			pqsignal(SIGPIPE, SIG_DFL);
		}
		return 0;

	}
	else
	{
		PQclear(res);
		switch (info_type)
		{
			case 't':
				fprintf(stderr, "Couldn't find any tables!\n");
				break;
			case 'i':
				fprintf(stderr, "Couldn't find any indices!\n");
				break;
			case 'S':
				fprintf(stderr, "Couldn't find any sequences!\n");
				break;
			case 'b':
			default:
				fprintf(stderr, "Couldn't find any tables, sequences or indices!\n");
				break;
		}
		return -1;
	}
}

/*
 * List Tables Grant/Revoke Permissions returns 0 if all went well
 *
 */
static int
rightsList(PsqlSettings *pset)
{
	char		listbuf[512];
	int			nColumns;
	int			i;
	int			maxCol1Len;
	int			maxCol2Len;
	int			usePipe = 0;
	char	   *pagerenv;
	FILE	   *fout;
	PGresult   *res;

#ifdef TIOCGWINSZ
	if (pset->notty == 0 &&
		(ioctl(fileno(stdout), TIOCGWINSZ, &screen_size) == -1 ||
		 screen_size.ws_col == 0 ||
		 screen_size.ws_row == 0))
	{
#endif
		screen_size.ws_row = 24;
		screen_size.ws_col = 80;
#ifdef TIOCGWINSZ
	}
#endif

	listbuf[0] = '\0';
	strcat(listbuf, "SELECT relname, relacl ");
	strcat(listbuf, "FROM pg_class ");
	/* Currently, we ignore indexes since they have no meaningful rights */
	strcat(listbuf, "WHERE ( relkind = 'r' OR relkind = 'S') ");
	strcat(listbuf, "  and relname !~ '^pg_'");
	strcat(listbuf, "  ORDER BY relname ");
	if (!(res = PSQLexec(pset, listbuf)))
		return -1;
	/* first, print out the attribute names */
	nColumns = PQntuples(res);
	if (nColumns > 0)
	{
		if (pset->notty == 0 &&
			(pagerenv = getenv("PAGER")) &&
			pagerenv[0] != '\0' &&
			screen_size.ws_row <= nColumns + 7 &&
			(fout = popen(pagerenv, "w")))
		{
			usePipe = 1;
			pqsignal(SIGPIPE, SIG_IGN);
		}
		else
			fout = stdout;

		/* choose column widths */
		maxCol1Len = strlen("Relation");
		maxCol2Len = strlen("Grant/Revoke Permissions");
		for (i = 0; i < PQntuples(res); i++)
		{
			int			l = strlen(PQgetvalue(res, i, 0));

			if (l > maxCol1Len)
				maxCol1Len = l;
			l = strlen(PQgetvalue(res, i, 1));
			if (l > maxCol2Len)
				maxCol2Len = l;
		}

		/* Display the information */

		fprintf(fout, "Database    = %s\n", PQdb(pset->db));
		fprintf(fout, " +");
		emitNtimes(fout, "-", maxCol1Len + 2);
		fprintf(fout, "+");
		emitNtimes(fout, "-", maxCol2Len + 2);
		fprintf(fout, "+\n");
		fprintf(fout, " | %-*s | %-*s |\n",
				maxCol1Len, "Relation",
				maxCol2Len, "Grant/Revoke Permissions");
		fprintf(fout, " +");
		emitNtimes(fout, "-", maxCol1Len + 2);
		fprintf(fout, "+");
		emitNtimes(fout, "-", maxCol2Len + 2);
		fprintf(fout, "+\n");

		/* next, print out the instances */
		for (i = 0; i < PQntuples(res); i++)
		{
			fprintf(fout, " | %-*s | %-*s |\n",
					maxCol1Len, PQgetvalue(res, i, 0),
					maxCol2Len, PQgetvalue(res, i, 1));
		}

		fprintf(fout, " +");
		emitNtimes(fout, "-", maxCol1Len + 2);
		fprintf(fout, "+");
		emitNtimes(fout, "-", maxCol2Len + 2);
		fprintf(fout, "+\n");

		PQclear(res);
		if (usePipe)
		{
			pclose(fout);
			pqsignal(SIGPIPE, SIG_DFL);
		}
		return 0;
	}
	else
	{
		PQclear(res);
		fprintf(stderr, "Couldn't find any tables!\n");
		return -1;
	}
}

static void
emitNtimes(FILE *fout, const char *str, int N)
{
	int			i;

	for (i = 0; i < N; i++)
		fputs(str, fout);
}

/*
 * Describe a table
 *
 * Describe the columns in a database table. returns 0 if all went well
 *
 *
 */
static int
tableDesc(PsqlSettings *pset, char *table, FILE *fout)
{
	char		descbuf[512];
	int			nColumns,
				nIndices;
	char	   *rtype;
	char	   *rnotnull;
	char	   *rhasdef;
	int			i;
	int			attlen,
				atttypmod;
	PGresult   *res,
			   *res2;
	int			usePipe = 0;
	char	   *pagerenv;

#ifdef TIOCGWINSZ
	if (pset->notty == 0 &&
		(ioctl(fileno(stdout), TIOCGWINSZ, &screen_size) == -1 ||
		 screen_size.ws_col == 0 ||
		 screen_size.ws_row == 0))
	{
#endif
		screen_size.ws_row = 24;
		screen_size.ws_col = 80;
#ifdef TIOCGWINSZ
	}
#endif

	/* Build the query */

	/*
	 * if the table name is surrounded by double-quotes, then don't
	 * convert case
	 */
	if (*table == '"')
	{
		table++;
		if (*(table + strlen(table) - 1) == '"')
			*(table + strlen(table) - 1) = '\0';
	}
	else
	{
#ifdef MULTIBYTE
		for (i = 0; table[i]; i += PQmblen(table + i))
#else
		for (i = 0; table[i]; i++)
#endif
			if (isascii((unsigned char) table[i]) &&
				isupper(table[i]))
				table[i] = tolower(table[i]);
	}

	descbuf[0] = '\0';
	strcat(descbuf, "SELECT a.attnum, a.attname, t.typname, a.attlen, ");
	strcat(descbuf, "a.atttypmod, a.attnotnull, a.atthasdef ");
	strcat(descbuf, "FROM pg_class c, pg_attribute a, pg_type t ");
	strcat(descbuf, "WHERE c.relname = '");
	strcat(descbuf, table);
	strcat(descbuf, "'");
	strcat(descbuf, "    and a.attnum > 0 ");
	strcat(descbuf, "    and a.attrelid = c.oid ");
	strcat(descbuf, "    and a.atttypid = t.oid ");
	strcat(descbuf, "  ORDER BY attnum ");
	if (!(res = PSQLexec(pset, descbuf)))
		return -1;
	/* first, print out the attribute names */
	nColumns = PQntuples(res);
	if (nColumns > 0)
	{
		if (fout == NULL)
		{
			if (pset->notty == 0 &&
				(pagerenv = getenv("PAGER")) &&
				pagerenv[0] != '\0' &&
				screen_size.ws_row <= nColumns + 7 &&
				(fout = popen(pagerenv, "w")))
			{
				usePipe = 1;
				pqsignal(SIGPIPE, SIG_IGN);
			}
			else
				fout = stdout;
		}

		/*
		 * Extract the veiw name and veiw definition from pg_views. -Ryan
		 * 2/14/99
		 */

		descbuf[0] = '\0';
		strcat(descbuf, "SELECT viewname, definition ");
		strcat(descbuf, "FROM pg_views ");
		strcat(descbuf, "WHERE viewname like '");
		strcat(descbuf, table);
		strcat(descbuf, "' ");
		if (!(res2 = PSQLexec(pset, descbuf)))
			return -1;

		/*
		 * Display the information
		 */
		if (PQntuples(res2))
		{

			/*
			 * display the query. o			 * -Ryan 2/14/99
			 */
			fprintf(fout, "View    = %s\n", table);
			fprintf(fout, "Query   = %s\n", PQgetvalue(res2, 0, 1));
		}
		else
			fprintf(fout, "Table    = %s\n", table);
		PQclear(res2);

		fprintf(fout, "+----------------------------------+----------------------------------+-------+\n");
		fprintf(fout, "|              Field               |              Type                | Length|\n");
		fprintf(fout, "+----------------------------------+----------------------------------+-------+\n");

		/* next, print out the instances */
		for (i = 0; i < PQntuples(res); i++)
		{
			char		type_str[33];

			fprintf(fout, "| %-32.32s | ", PQgetvalue(res, i, 1));
			rtype = PQgetvalue(res, i, 2);
			attlen = atoi(PQgetvalue(res, i, 3));
			atttypmod = atoi(PQgetvalue(res, i, 4));
			rnotnull = PQgetvalue(res, i, 5);
			rhasdef = PQgetvalue(res, i, 6);

			strcpy(type_str, rtype);
			if (strcmp(rtype, "bpchar") == 0)
				strcpy(type_str, "char()");
			else if (strcmp(rtype, "varchar") == 0)
				strcpy(type_str, "varchar()");
			else if (rtype[0] == '_')
			{
				strcpy(type_str, rtype + 1);
				strncat(type_str, "[]", 32 - strlen(type_str));
				type_str[32] = '\0';
			}

			if (rnotnull[0] == 't')
			{
				strncat(type_str, " not null", 32 - strlen(type_str));
				type_str[32] = '\0';
			}
			if (rhasdef[0] == 't')
			{
				descbuf[0] = '\0';
				strcat(descbuf, "SELECT d.adsrc ");
				strcat(descbuf, "FROM pg_attrdef d, pg_class c ");
				strcat(descbuf, "WHERE c.relname = '");
				strcat(descbuf, table);
				strcat(descbuf, "'");
				strcat(descbuf, "    and c.oid = d.adrelid ");
				strcat(descbuf, "    and d.adnum = ");
				strcat(descbuf, PQgetvalue(res, i, 0));
				if (!(res2 = PSQLexec(pset, descbuf)))
					return -1;
				strcat(type_str, " default ");
				strncat(type_str, PQgetvalue(res2, 0, 0), 32 - strlen(type_str));
				type_str[32] = '\0';
			}
			fprintf(fout, "%-32.32s |", type_str);

			if (strcmp(rtype, "text") == 0)
				fprintf(fout, "%6s |", "var");
			else if (strcmp(rtype, "bpchar") == 0 ||
					 strcmp(rtype, "varchar") == 0)
				fprintf(fout, "%6i |", atttypmod != -1 ? atttypmod - VARHDRSZ : 0);
			else if (strcmp(rtype, "numeric") == 0)
				fprintf(fout, "%3i.%-2i |",
						((atttypmod - VARHDRSZ) >> 16) & 0xffff,
						(atttypmod - VARHDRSZ) & 0xffff);
			else
			{
				if (attlen > 0)
					fprintf(fout, "%6i |", attlen);
				else
					fprintf(fout, "%6s |", "var");
			}
			fprintf(fout, "\n");
		}
		fprintf(fout, "+----------------------------------+----------------------------------+-------+\n");
		PQclear(res);

		/* display defined indexes for this table */
		descbuf[0] = '\0';
		strcat(descbuf, "SELECT c2.relname ");
		strcat(descbuf, "FROM pg_class c, pg_class c2, pg_index i ");
		strcat(descbuf, "WHERE c.relname = '");
		strcat(descbuf, table);
		strcat(descbuf, "'");
		strcat(descbuf, "    and c.oid = i.indrelid ");
		strcat(descbuf, "    and i.indexrelid = c2.oid ");
		strcat(descbuf, "  ORDER BY c2.relname ");
		if ((res = PSQLexec(pset, descbuf)))
		{
			nIndices = PQntuples(res);
			if (nIndices > 0)
			{

				/*
				 * Display the information
				 */

				if (nIndices == 1)
					fprintf(fout, "Index:    ");
				else
					fprintf(fout, "Indices:  ");

				/* next, print out the instances */
				for (i = 0; i < PQntuples(res); i++)
					if (i == 0)
						fprintf(fout, "%s\n", PQgetvalue(res, i, 0));
					else
						fprintf(fout, "          %s\n", PQgetvalue(res, i, 0));
				fprintf(fout, "\n");
			}
			PQclear(res);
		}
		if (usePipe)
		{
			pclose(fout);
			pqsignal(SIGPIPE, SIG_DFL);
		}
		return 0;
	}
	else
	{
		PQclear(res);
		fprintf(stderr, "Couldn't find table %s!\n", table);
		return -1;
	}
}

/*
 * Get object comments
 *
 * Describe the columns in a database table. returns 0 if all went well
 *
 *
 */
static int
objectDescription(PsqlSettings *pset, char *object)
{
	char		descbuf[512];
	PGresult   *res;
	int			i;
	bool		success;

	/* Build the query */

	while (isspace(*object))
		object++;

	/*
	 * if the object name is surrounded by double-quotes, then don't
	 * convert case
	 */
	if (*object == '"')
	{
		object++;
		if (*(object + strlen(object) - 1) == '"')
			*(object + strlen(object) - 1) = '\0';
	}
	else
	{
#ifdef MULTIBYTE
		for (i = 0; object[i]; i += PQmblen(object + i))
#else
		for (i = 0; object[i]; i++)
#endif
			if (isupper(object[i]))
				object[i] = tolower(object[i]);
	}

	descbuf[0] = '\0';
	if (strchr(object, '.') != NULL)
	{
		char		table[NAMEDATALEN],
					column[NAMEDATALEN];

		StrNCpy(table, object,
				((strchr(object, '.') - object + 1) < NAMEDATALEN) ?
				(strchr(object, '.') - object + 1) : NAMEDATALEN);
		StrNCpy(column, strchr(object, '.') + 1, NAMEDATALEN);
		strcat(descbuf, "SELECT DISTINCT description ");
		strcat(descbuf, "FROM pg_class, pg_attribute, pg_description ");
		strcat(descbuf, "WHERE pg_class.relname = '");
		strcat(descbuf, table);
		strcat(descbuf, "' and ");
		strcat(descbuf, "pg_class.oid = pg_attribute.attrelid and ");
		strcat(descbuf, "pg_attribute.attname = '");
		strcat(descbuf, column);
		strcat(descbuf, "' and ");
		strcat(descbuf, " pg_attribute.oid = pg_description.objoid ");
		if (!(res = PSQLexec(pset, descbuf)))
			return -1;
	}
	else
	{
		strcat(descbuf, "SELECT DISTINCT description ");
		strcat(descbuf, "FROM pg_class, pg_description ");
		strcat(descbuf, "WHERE pg_class.relname ~ '^");
		strcat(descbuf, object);
		strcat(descbuf, "'");