PostgreSQLで設計されたDBを、CSV形式でファイルへドキュメント化
ポストグレースで設計されたDBを、CSV形式でファイルにドキュメント化
Usage : php postgres2txt.php
Usage : php postgres2txt.php
Usage : php postgres2txt.php
Usage : php postgres2txt.php
<?php /* ポストグレースで設計されたDBを、CSV形式でファイルにドキュメント化 Usage : php postgres2txt.php <DATABASE> Usage : php postgres2txt.php <DATABASE> <username> Usage : php postgres2txt.php <DATABASE> <username> <password> Usage : php postgres2txt.php <DATABASE> <username> <password> <host> */ ini_set("error_reporting",E_COMPILE_ERROR|E_ERROR|E_CORE_ERROR); ini_set("display_errors",1); $argv = $_SERVER["argv"]; if ( (count($argv) < 2 ) ||(5 < count($argv) ) ){ print "Usage : php {$argv[0]} <DATABASE> \n"; print "Usage : php {$argv[0]} <DATABASE> <username> \n"; print "Usage : php {$argv[0]} <DATABASE> <username> <password> \n"; print "Usage : php {$argv[0]} <DATABASE> <username> <password> <host>\n"; exit; } $database = $argv[1]; $username = $argv[2]; $password = $argv[3]; $host = $argv[4]; $o = new database; $o->init($database,$username,$password,$host); $table_list = $o->get_table_list(); $meta_columns = $o->get_meta_columns(); $meta_default = $o->get_meta_default(); $meta_key = $o->get_meta_key(); foreach($table_list as $key => $value2) { print $value2["tablename"]; print ","; print "({$value2[tablecomment]})"; print "\n"; print "フィールド,データ型,NOT NULL,デフォルト,コメント\n"; foreach($meta_columns[$value2["tablename"]] as $key1 => $value1) { print $value1["attname"]; print ","; print $value1["type"]; print ","; if($value1["attnotnull"] == "t") { print "NOT NULL"; } print ","; print $value1["adsrc"]; print ","; print $value1["comment"]; print "\n"; } print "\n"; } class database { var $database; function _get_all_data() { $this->table_list = $this->db->do_getall($this->_get_sql_table_list()); foreach($this->table_list as $key => $value) { $this->meta_columns[$value["tablename"]] = $this->db->do_getall($this->_get_sql_meta_columns_sql($value["tablename"])); $this->meta_default[$value["tablename"]] = $this->db->do_getall($this->_get_sql_meta_default_sql($value["tablename"])); $this->meta_key[$value["tablename"]] = $this->db->do_getall($this->_get_sql_meta_key_sql($value["tablename"])); } } var $db; var $table_list; function get_table_list() { return $this->table_list; } var $meta_columns; function get_meta_columns() { return $this->meta_columns; } var $meta_default; function get_meta_default() { return $this->meta_default; } var $meta_key; function get_meta_key() { return $this->meta_key; } function init($database,$username,$password,$host) { $this->database = $database; if(!$username) { $username = "postgres"; } if(!$host) { $host = "localhost"; } $this->db = new db_access; $dsn = "pgsql://{$username}:{$password}@{$host}/{$this->database}"; $this->db->set_dsn($dsn); $this->db->do_connect(); $this->_get_all_data(); } function _get_sql_meta_key_sql($table) { $table = addslashes($table); //------------------------------------------------------------ //metaKeySQL //------------------------------------------------------------ $sql = " SELECT ic.relname AS index_name , a.attname AS column_name , i.indisunique AS unique_key , i.indisprimary AS primary_key FROM pg_class bc , pg_class ic , pg_index i , pg_attribute a WHERE bc.oid = i.indrelid AND ic.oid = i.indexrelid AND ( i.indkey[0] = a.attnum OR i.indkey[1] = a.attnum OR i.indkey[2] = a.attnum OR i.indkey[3] = a.attnum OR i.indkey[4] = a.attnum OR i.indkey[5] = a.attnum OR i.indkey[6] = a.attnum OR i.indkey[7] = a.attnum ) AND a.attrelid = bc.oid AND bc.relname = '{$table}' ; "; return $sql; } function _get_sql_meta_default_sql($table) { $table = addslashes($table); //------------------------------------------------------------ //metaDefaultsSQL //------------------------------------------------------------ $sql = " SELECT d.adnum as num , d.adsrc as def from pg_attrdef d , pg_class c where d.adrelid=c.oid and c.relname='{$table}' order by d.adnum ; "; return $sql; } function _get_sql_meta_columns_sql($table) { $table = addslashes($table); //------------------------------------------------------------ //metaColumnsSQL //------------------------------------------------------------ $sql = " SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod) as type, a.atttypmod, a.attnotnull, a.atthasdef, adef.adsrc, a.attstattarget, a.attstorage, t.typstorage, ( SELECT 1 FROM pg_catalog.pg_depend pd, pg_catalog.pg_class pc WHERE pd.objid=pc.oid AND pd.classid=pc.tableoid AND pd.refclassid=pc.tableoid AND pd.refobjid=a.attrelid AND pd.refobjsubid=a.attnum AND pd.deptype='i' AND pc.relkind='S' ) IS NOT NULL AS attisserial, pg_catalog.col_description(a.attrelid, a.attnum) AS comment FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_attrdef adef ON a.attrelid=adef.adrelid AND a.attnum=adef.adnum LEFT JOIN pg_catalog.pg_type t ON a.atttypid=t.oid WHERE a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname='{$table}' AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = 'public')) AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum "; return $sql; } function _get_sql_table_list() { // ------------------------------------------------------------ // table list // ------------------------------------------------------------ $sql = " SELECT c.relname AS tablename , pg_catalog.pg_get_userbyid(c.relowner) AS tableowner , pg_catalog.obj_description(c.oid, 'pg_class') AS tablecomment FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND nspname='public' ORDER BY c.relname ; "; return $sql; } } class db_access { function set_dsn($dsn) { $this->dsn = $dsn; } var $dsn = null; var $_result = array(); var $conid = null; function do_connect() { require_once("DB.php"); $this->conid = DB::connect($this->dsn); if ( DB::isError($this->conid) ) { die ($this->conid->getMessage()); } } function do_getall($sql) { $result = $this->conid->getAll($sql,DB_FETCHMODE_ASSOC); //DB_FETCHMODE_ASSOC,DB_FETCHMODE_ORDERED if (DB::isError($result)) { die ($result->getDebugInfo($result)); } return $result; } function do_query($sql) { $result = $this->conid->query($sql); if (DB::isError( $result )) { die ( $result->getDebugInfo() ); } } function disconnect() { $this->conid->disconnect(); } } ?>