[PHP-users 4000] Re: データのファイルへの吐き出しについて
Youichi Iwakiri
php-users@php.gr.jp
Thu, 29 Nov 2001 17:53:50 +0900
いわきりです
Haruhisa Fujita wrote in <20011128155505.C56A.HARUO-F@ai-e.aisin.co.jp>:
>PostgreSQLのバックスラッシュコマンド「\copy」を
>使おうとしているのですが、phpからこのコマンドは実行
>できないものでしょうか？
>助言お願いします。
以下のパッチを書いたんですけど使えますかね?
#テストが十分でないからちょっと怖いですが (^^;
ご希望の動作は結果からファイルを作成する事のようですので
こんな感じになるのかも
fwrite(fp, implode("\n", pg_copy_out("table_name")));
patchの内容
pgsql拡張ライブラリに関数を追加
array pg_copy_out(int connection_id,
string table_name [, string delimiter [, string null_as]])
戻り値
array型 レコード単位でarrayに格納
error時はFALSEを返す
使用例
<script language="php">
$conn = pg_connect("host=localhost dbname=test_db");
$result = pg_copy_out($conn, "t1");
print_r($result);
$result = pg_copy_out($conn, "t1", ':');
print_r($result);
$result = pg_copy_out($conn, "t1", "\t", "\0");
print_r($result);
pg_close($conn);
</script>
結果
Array
(
[0] => 0 \N
[1] => 2 0
[2] => 4 2
[3] => 5 3
[4] => 6 4
[5] => 7 0
[6] => 8 7
[7] => 9 0
[8] => 3 9
)
Array
(
[0] => 0:\N
[1] => 2:0
[2] => 4:2
[3] => 5:3
[4] => 6:4
[5] => 7:0
[6] => 8:7
[7] => 9:0
[8] => 3:9
)
Array
(
[0] => 0
[1] => 2 0
[2] => 4 2
[3] => 5 3
[4] => 6 4
[5] => 7 0
[6] => 8 7
[7] => 9 0
[8] => 3 9
)
以下、パッチです
PHP-4.0.6に付属のpgsql拡張モジュール用です
PHP-4.0.6 + PostgreSQL 7.1.3で動作確認を行いました。
patchの当て方
% cd $PHP_SRC_DIR/ext/pgsql
% patch < patch_file
--- pgsql.c Mon Jun 4 17:06:30 2001
+++ pgsql.c.with_copy_func Thu Nov 29 16:38:57 2001
@@ -83,6 +83,8 @@
PHP_FE(pg_loreadall, NULL)
PHP_FE(pg_loimport, NULL)
PHP_FE(pg_loexport, NULL)
+/* This function support '\\COPY' in psql internal commands */
+ PHP_FE(pg_copy_out, NULL)
PHP_FE(pg_put_line, NULL)
PHP_FE(pg_end_copy, NULL)
#if HAVE_PQCLIENTENCODING
@@ -777,6 +779,138 @@
RETURN_FALSE;
}
RETURN_TRUE;
+}
+/* }}} */
+
+#define COPYBUFSIZ 8192
+
+/* {{{ proto int pg_copy_out(int connection, string table_name [, string delimiter [, string null_as]])
+ Send null-terminated string to backend server*/
+PHP_FUNCTION(pg_copy_out)
+{
+ zval **table_name, **pgsql_link = NULL, **delimiter, **null_as;
+ char *query;
+ char pg_delimiter = '\t';
+ char *pg_null_as = "\\\\N";
+ static char *query_template = "COPY \"\" TO STDOUT DELIMITERS ':' WITH NULL AS ''";
+ int id = -1;
+ PGconn *pgsql;
+ PGresult *pgsql_result;
+ ExecStatusType status;
+ int result = 0;
+ int copydone = 0;
+ char copybuf[COPYBUFSIZ];
+ char *csv = (char *)NULL;
+ int ret;
+ PGLS_FETCH();
+
+ switch(ZEND_NUM_ARGS()) {
+ case 2:
+ if (zend_get_parameters_ex(2, &pgsql_link, &table_name)==FAILURE) {
+ RETURN_FALSE;
+ }
+ break;
+ case 3:
+ if (zend_get_parameters_ex(3, &pgsql_link, &table_name, &delimiter)==FAILURE) {
+ RETURN_FALSE;
+ }
+ convert_to_string_ex(delimiter);
+ pg_delimiter = *Z_STRVAL_PP(delimiter);
+ break;
+ case 4:
+ if (zend_get_parameters_ex(4, &pgsql_link, &table_name, &delimiter, &null_as)==FAILURE) {
+ RETURN_FALSE;
+ }
+ pg_delimiter = *Z_STRVAL_PP(delimiter);
+ pg_null_as = emalloc(sizeof(strlen(Z_STRVAL_PP(null_as))+1));
+ strcpy(pg_null_as, Z_STRVAL_PP(null_as));
+ break;
+ default:
+ WRONG_PARAM_COUNT;
+ break;
+ }
+
+ ZEND_FETCH_RESOURCE2(pgsql, PGconn *, pgsql_link, id, "PostgreSQL link", le_link, le_plink);
+
+ convert_to_string_ex(table_name);
+ /* create SQL
+ "COPY table_name TO STDOUT DELIMITERS '\t' WITH NULL AS 'null string'"
+ */
+ query = emalloc(sizeof(query_template) + sizeof(strlen(Z_STRVAL_PP(table_name))) + sizeof(strlen(Z_STRVAL_PP(null_as))) + 1);
+ sprintf(query, "COPY \"%s\" TO STDOUT DELIMITERS '%c' WITH NULL AS '%s'",
+ Z_STRVAL_PP(table_name),
+ pg_delimiter,
+ pg_null_as);
+
+ pgsql_result = PQexec(pgsql, query);
+
+ efree(query);
+
+ if (pgsql_result) {
+ status = PQresultStatus(pgsql_result);
+ } else {
+ status = (ExecStatusType) PQstatus(pgsql);
+ }
+
+ switch (status) {
+ case PGRES_COPY_OUT: /* successful command that did not return rows */
+ if (pgsql_result) {
+ /* Initialize return array */
+ if (array_init(return_value) == FAILURE) {
+ RETURN_FALSE;
+ }
+ while (!copydone)
+ {
+ ret = PQgetline(pgsql, copybuf, COPYBUFSIZ);
+
+ if (copybuf[0] == '\\' &&
+ copybuf[1] == '.' &&
+ copybuf[2] == '\0')
+ {
+ copydone = 1; /* we're at the end */
+ }
+ else
+ {
+ if (csv == (char *)NULL) {
+ csv = (char *)emalloc(sizeof(char)*(COPYBUFSIZ+1));
+ strcpy(csv, copybuf);
+ } else {
+ csv = (char *)erealloc(csv, strlen(csv) + sizeof(char)*COPYBUFSIZ);
+ strcat(csv, copybuf);
+ }
+
+
+ switch (ret)
+ {
+ case EOF:
+ copydone = 1;
+ /* FALLTHROUGH */
+ case 0:
+ add_next_index_string(return_value, csv, 1);
+ efree(csv);
+ csv = (char *)NULL;
+ break;
+ case 1:
+ break;
+ }
+ }
+ }
+ ret = !PQendcopy(pgsql);
+ } else {
+ RETURN_FALSE;
+ }
+ break;
+ case PGRES_EMPTY_QUERY:
+ case PGRES_BAD_RESPONSE:
+ case PGRES_NONFATAL_ERROR:
+ case PGRES_FATAL_ERROR:
+ case PGRES_COMMAND_OK:
+ case PGRES_COPY_IN:
+ default:
+ php_error(E_WARNING, "PostgreSQL query failed: %s", PQerrorMessage(pgsql));
+ RETURN_FALSE;
+ break;
+ }
}
/* }}} */
--- php_pgsql.h Sun May 6 10:34:56 2001
+++ php_pgsql.h.with_copy_func Thu Nov 29 14:04:24 2001
@@ -88,6 +88,7 @@
PHP_FUNCTION(pg_loexport);
PHP_FUNCTION(pg_lolseek);
PHP_FUNCTION(pg_lotell);
+PHP_FUNCTION(pg_copy_out);
PHP_FUNCTION(pg_put_line);
PHP_FUNCTION(pg_end_copy);
#if HAVE_PQCLIENTENCODING