Category Archives: PostgreSQL

package DbDefInfo;
use strict;
use warnings;
our($VERSION, @ISA, @EXPORT, @EXPORT_OK);
require Exporter;
use Carp;
$VERSION = '0.0001';
@ISA = qw(Exporter);
@EXPORT = qw(get_table_def_infos);
@EXPORT_OK = qw();
 
use DBI;
my $column_info_query = <<EOD;
  SELECT DISTINCT
    PG_ATTRIBUTE.ATTNAME AS NAME,
    PG_TYPE.TYPNAME AS TYPE,
    PG_ATTRIBUTE.ATTTYPMOD - 4 AS SUBTYPE,
    PG_ATTRIBUTE.ATTNOTNULL AS NOTNULL,
    PG_ATTRDEF.ADSRC AS DEFAULT,
    PG_DESCRIPTION.DESCRIPTION AS DESCRIPTION,
    PG_ATTRIBUTE.ATTNUM AS NO,
    PG_ATTRIBUTE.ATTALIGN
  FROM
    PG_ATTRIBUTE
  INNER JOIN PG_CLASS ON 
    PG_CLASS.OID = PG_ATTRIBUTE.ATTRELID
  INNER JOIN PG_TYPE ON
    PG_ATTRIBUTE.ATTTYPID = PG_TYPE.OID
  LEFT JOIN PG_DESCRIPTION ON
    PG_CLASS.OID = PG_DESCRIPTION.OBJOID AND
    PG_ATTRIBUTE.ATTNUM = PG_DESCRIPTION.OBJSUBID
  LEFT JOIN PG_ATTRDEF ON 
      PG_ATTRIBUTE.ATTRELID = PG_ATTRDEF.ADRELID AND
      PG_ATTRIBUTE.ATTNUM = PG_ATTRDEF.ADNUM
  WHERE
    PG_CLASS.RELNAME='%s' AND
    PG_ATTRIBUTE.ATTNUM > 0
  ORDER BY
    PG_ATTRIBUTE.ATTNUM 
EOD
 
my $keys_info_query = <<EOD;
  SELECT
   C.RELNAME AS TABLENAME, 
   A.ATTNAME AS COLUMNNAME,
   FCLASS.RELNAME AS FTABLENAME,
   FATTR.ATTNAME AS FCOLUMNNAME
  FROM
    PG_CONSTRAINT N
  INNER JOIN  PG_CLASS C
   ON N.CONRELID = C.OID
  INNER JOIN PG_ATTRIBUTE A
   ON A.ATTRELID = C.OID
  LEFT JOIN 
   PG_CLASS AS FCLASS ON
    N.CONFRELID = FCLASS.OID
  LEFT JOIN PG_ATTRIBUTE FATTR
   ON FATTR.ATTRELID = C.OID AND
      FATTR.ATTNUM = ANY(N.CONFKEY)
  WHERE
    A.ATTNUM =ANY (N.CONKEY) AND
    C.RELNAME = '%s'
EOD
 
my $all_table_query = <<EOD;
  SELECT DISTINCT
    PG_CLASS.RELNAME,
    PG_DESCRIPTION.DESCRIPTION
  FROM
    PG_CLASS
  INNER JOIN 
    PG_TABLES ON
      PG_CLASS.RELNAME = PG_TABLES.TABLENAME
  LEFT JOIN
    PG_DESCRIPTION ON
      PG_CLASS.OID = PG_DESCRIPTION.OBJOID AND
      PG_DESCRIPTION.OBJSUBID = 0
  WHERE
    SCHEMANAME = '%s' AND PG_CLASS.RELKIND = 'r'
EOD
 
sub get_all_tables {
  my ($db, $schema_name) = @_;
  my $st = $db->prepare(sprintf $all_table_query, $schema_name);
  my $res = $st->execute;
  my @table_infos;
  while (my ($table_name, $table_comment) = $st->fetchrow) {
    push @table_infos, {table_name => $table_name, table_comment => $table_comment};
  }
  $st->finish;
  return @table_infos;
}
 
sub get_keys_infos {
  my ($db, $table_name) = @_;
  my @keys;
  my $st_keys = $db->prepare(sprintf($keys_info_query, $table_name));
  my $res_columns = $st_keys->execute;
  while (my $row_hashref = $st_keys->fetchrow_hashref) {
    push @keys, $row_hashref;
  }
  $st_keys->finish;
  return @keys;
}
 
sub get_column_infos {
  my ($db, $table_name) = @_;
  my @columns;
  my $st_columns = $db->prepare(sprintf($column_info_query, $table_name));
  my $res_columns = $st_columns->execute;
  while (my $row_hashref = $st_columns->fetchrow_hashref) {
    push @columns, $row_hashref;
  }
  $st_columns->finish;
  return @columns;
}
 
sub get_table_def_infos {
  my $settings = shift;
  my $db = DBI->connect(
    "DBI:$settings->{type}:host=$settings->{host};dbname=$settings->{dbname}",
    $settings->{user},
    $settings->{passwd})
    or die "DBI connect failed : $DBI::errstr";
  my @rows;
  for my $table_info (get_all_tables($db, $settings->{schema_name})) {
    my @columns = get_column_infos($db, $table_info->{table_name});
    my @keys = get_keys_infos($db, $table_info->{table_name});
    push @rows, {
      table_info => $table_info,
      column_infos => \@columns,
      key_infos => \@keys
    };
  }
  $db->disconnect;
  return @rows;
}
1;

package WriteExcelDbDefInfo;
use strict;
use warnings;
our($VERSION, @ISA, @EXPORT, @EXPORT_OK);
require Exporter;
use Carp;
use Data::Dumper;
$VERSION = '0.0001';
@ISA = qw(Exporter);
@EXPORT = qw(write_db_def_info);
@EXPORT_OK = qw();
use Spreadsheet::WriteExcel;
 
sub utf16 {
  my $string = shift;
  use Encode qw(from_to);
  Encode::from_to($string, "utf8", "utf16");
  return $string;
}
 
sub set_width {
  my ($worksheet, $data) = @_;
  my $col = 0;
  for my $width (@$data) {
    $worksheet->set_column($col, $col++, $width);
  }
}
 
sub print_table_header {
  my ($workbook, $worksheet, $table_name, $table_comment) = @_;
  my $format_header = $workbook->addformat();
  $format_header->set_properties(merge => 1, border => 1, bold => 1,
    valign => 'top', align => 'center',
    bg_color => 32, color => 9);
  my $format = $workbook->addformat();
  $format->set_properties(merge => 1, border => 1, valign => 'top', align => 'left');
  my $comment;
  my $index = index($table_comment, ';');
  ($table_comment, $comment) = (substr($table_comment, 0, $index),
    substr($table_comment, $index + 1))
  if $index > -1;
  $worksheet->merge_range('A1:B1', utf16('テーブル名'), $format_header, 1);
  $worksheet->merge_range('C1:E1', utf16($table_name), $format, 1);
  $worksheet->merge_range('F1:H1', utf16($table_comment), $format, 1);
  $worksheet->merge_range('A2:H2', utf16($comment), $format, 1);
  $worksheet->set_row(1, 30);
}
 
sub print_data {
  my ($workbook, $worksheet, $row, $data, $format_hash) = @_;
  my $col = 0;
  my $format_left = $workbook->addformat(%$format_hash);
  my $format = $workbook->addformat(%$format_hash);
  $format->set_properties(bottom => 1, top => 1,
    valign => 'top', align => 'center',
    bg_color => 32, color => 9);
  my $i = 0;
  for my $d (@$data) {
    $worksheet->write_unicode($row, $col++, utf16($d), $format);
    $i++;
  }
}
 
sub print_header {
  my ($workbook, $worksheet, $table) = @_;
  my $table_name = $table->{table_info}->{table_name} || '';
  my $table_comment = $table->{table_info}->{table_comment} || '';
  my $col_width = [4.75, 24.88, 14.5, 10, 7, 7, 15, 57.63];
  my $column_header_data1 = ['No.', '項目名', '(項目名)', '属性', '桁数', 'not null', 'default', '備考'];
  set_width($worksheet, $col_width);
  print_table_header($workbook, $worksheet, $table_name, $table_comment);
  print_data($workbook, $worksheet, 2, $column_header_data1, {bold => 1, border => 1});
}
 
sub print_column_info {
  my ($workbook, $worksheet, $row, $column, $is_last) = @_;
  my ($description, $comment);
  $description = $column->{description} || '';
  my $index = index($description, ';');
  ($description, $comment) = (substr($description, 0, $index),
    substr($description, $index + 1))
  if $index > -1;
  my $format = $workbook->addformat();
  $format->set_properties(bottom => $is_last ? 1 : 4, top => 4, right => 1, left => 1);
  $format->set_align('vjustify');
  my $subtype = ($column->{subtype} > -5) ? $column->{subtype} : '';
  if ($column->{type} eq 'numeric') {
    $subtype = int($subtype / 65536) . ", " . ($subtype % 65536);
  }
  $worksheet->write($row, 0, $column->{no} || '', $format);
  $worksheet->write_unicode($row, 1, utf16($description || ''), $format);
  $worksheet->write($row, 2, $column->{name}, $format);
  $worksheet->write($row, 3, $column->{type} || '', $format);
  $worksheet->write($row, 4, $subtype, $format);
  $worksheet->write_unicode($row, 5, utf16($column->{notnull} ? "○" : ""), $format);
  $worksheet->write_unicode($row, 6, utf16($column->{default} || ''), $format);
  $worksheet->write_unicode($row, 7, utf16($comment || ''), $format);
}
 
sub print_keys_header {
  my ($workbook, $worksheet, $row) = @_;
  my $column_header_data = ['key', 'カラム'];
  print_data($workbook, $worksheet, $row, $column_header_data, {bold => 1, border => 1});
}
 
sub print_key_info {
  my ($workbook, $worksheet, $row, $key, $is_last) = @_;
  my $format = $workbook->addformat();
  $format->set_properties(bottom => $is_last ? 1 : 4, top => 4, right => 1, left => 1);
  $format->set_align('vjustify');
  if ($key->{ftablename}) {
    my $description = "$key->{columnname} -> $key->{ftablename}.$key->{fcolumnname}";
    $worksheet->write($row, 0, 'fk', $format);
    $worksheet->write_unicode($row, 1, utf16($description), $format);
  } else {
    $worksheet->write($row, 0, 'pk', $format);
    $worksheet->write_unicode($row, 1, utf16($key->{columnname}), $format);
  }
}
 
sub write_db_def_info {
  my ($tables_ref, $settings) = @_;
  my $workbook = Spreadsheet::WriteExcel->new($settings->{output});
  for my $table (@$tables_ref) {
    my $worksheet = $workbook->addworksheet($table->{table_info}->{table_name});
    $worksheet->set_landscape();
    $worksheet->fit_to_pages(1, 1);
    print_header($workbook, $worksheet, $table);
    my $columns_ref = $table->{column_infos};
    my $rownum = 3;
    my $i = 0;
    for my $column (@$columns_ref) {
      print_column_info($workbook, $worksheet, $rownum++, $column, scalar(@$columns_ref) - 1 == $i);
      $i++;
    }
    print_keys_header($workbook, $worksheet, $i + 3 + 1);
    my $keys_ref = $table->{key_infos};
    $rownum = 3 + 2 + $i;
    $i = 0;
    for my $key (@$keys_ref) {
      print_key_info($workbook, $worksheet, $rownum++, $key, scalar(@$keys_ref) - 1 == $i);
    }
  }
}
1;