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();
  }
}


?>