Category Archives: PostgreSQL

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;

#! c:/Perl/bin/perl
use strict;
use warnings;
use Getopt::Std;
 
use DbDefInfo;
use WriteExcelDbDefInfo;
 
my $usage = <<"USAGE";
USAGE: $0 -u <username> -p <passwd> [-h <hostname>] -d <dbname> -t <dbtype> [-o <output_file_name>] [-s <schema_name>]
create table definition excel file from database. postgresql only.
 <username>         connect user name.
 <passwd>           connect user password.
 <hostname>         connect server name.
 <dbname>           connect database name.
 <dbtype>           postgresql 'PgPP'
 <output_file_name> excel file name which will create.
 <schema_name>      schema name.
USAGE
 
our ($opt_u, $opt_p, $opt_d, $opt_h, $opt_o, $opt_s, $opt_t);
getopt('oupdhst');
unless ($opt_u && $opt_p && $opt_d && $opt_t) {
  print STDERR $usage;
  exit(1);
}
 
my $settings = {
  user => $opt_u,
  passwd => $opt_p,
  dbname => $opt_d,
  host => $opt_h || 'localhost',
  output => $opt_o || 'table_def.xls',
  schema_name => $opt_s || 'public',
  type => $opt_t
};
 
my @tables = get_table_def_infos($settings);
write_db_def_info(\@tables, $settings);