2015-12-14
　某２『絶望しない程度にコード削減しようか』
　　私『…はい』
　gensql()のコード削減。

2015-12-14
　ライブツアーのセットリストを公演毎に横に並べる SQL動的生成＆UNION利用編で生成されたSQLで実際にHTML化を試みたもの。想像通りめんどくさくなった。

tour_id=227 の「NANA MIZUKI LIVE SENSATION 2003」の例。

テーブルの罫線はCSSで指定している。

ライブツアーのセットリストを公演毎に横に並べるのCGI b.plをこれで差し替えできる。

定義しているサブルーチン gensql()は、ライブツアーのセットリストを公演毎に横に並べる SQL動的生成＆UNION利用編のSQL生成ロジックgensql.plの内容をそのまま使ってる。
絶望的に長いソースになってしまった。もっと処理に依存させたデータ構造ならSQL生成処理も簡素化するかもしれない。

b.pl

use Encode;
use DBI;
use CGI;
 
my $dbname="nanamizuki";
my $dbhost="your.database.host.addr";
my $dbuser="db user id";
my $dbpass="db password";
 
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$dbhost",$dbuser,$dbpass) or die  "cannot connect database.";
my $cgi = CGI->new();
 
my $cgi_id = $cgi->param("id");
 
my $events_count = 0;
 
  $dbh->{AutoCommit}=0;
 
  ## 指定ツアーの公演回数を取得
  my $sth0 = $dbh->prepare('SELECT count(*) from events WHERE tour_id=?');
  $sth0->bind_param( 1, $cgi_id);
  $sth0->execute();
  $sth0->bind_col(  1, \$events_count   );
  $sth0->fetch();
  $sth0->finish();
  $dbh->commit();
 
  my @cols;
  my $sth1 = $dbh->prepare( gensql($events_count, $cgi_id) );
  $sth1->execute();
 
  for(my $cnt = 0; $cnt < $events_count+2 ; $cnt++)
  {
    $cols[$cnt]=" ";
    $sth1->bind_col( $cnt+1, \$cols[$cnt] );
  }
 
  print $cgi->header({charset=>'utf-8'});
  print $cgi->start_html( { title   =>encode('utf-8',decode('euc-jp','公演情報')),
                            charset =>'utf-8',
                            style   =>{ code =>["td { white-space: nowrap; border-style: none none none solid; }",
                                                "td.R    { border-style: none  solid none  solid; }",
                                                "td.topL { border-style: solid none  none  solid; }",
                                                "td.topR { border-style: solid solid none  solid; }",
                                                "td.btmL { border-style: none  none  solid solid; }",
                                                "td.btmR { border-style: none  solid solid solid; }" ] } });
  print $cgi->start_table();
  print "\n";
 
  my $sel1,$sel2;
  my $cssclass    = "";
  my $data        = "";
 
  while( $sth1->fetchrow_hashref() != undef )
  {
    print $cgi->start_Tr();
    for(my $i=0; $i<$events_count+2; $i++)
    {
      if ($i == 0) { $sel1 = $cols[$i]; next; }
      if ($i == 1) { $sel2 = $cols[$i]; next; }
 
      $cssclass = "";
 
      if (($sel1 == 0)&&($sel2 == 1)) { $cssclass = "topL"; $cssclass = "topR" if ($i == ($events_count+2-1)); }
      if (($sel1 == 0)&&($sel2 == 2)) { $cssclass = "";     $cssclass = "R"    if ($i == ($events_count+2-1)); }
      if (($sel1 == 0)&&($sel2 == 3)) { $cssclass = "btmL"; $cssclass = "btmR" if ($i == ($events_count+2-1)); }
      if ($sel1 == 1)                 { $cssclass = "";     $cssclass = "R"    if ($i == ($events_count+2-1)); }
 
      $cols[$i] = "" if ("---" eq $cols[$i]);
 
      $data = encode('utf-8', $cols[$i]);
      $data = encode('utf-8', sprintf("%d. %s", $sel2, $cols[$i])) if (($sel1 == 1)&&("" ne $cols[$i]));
 
      print $cgi->td(                     $data ) if ("" eq $cssclass);
      print $cgi->td( {class=>$cssclass}, $data ) if ("" ne $cssclass);
    }
 
    print $cgi->end_Tr();
    print "\n";
  }
 
  $sth1->finish();
  $dbh->commit();
 
  print $cgi->end_table();
  print $cgi->end_html();
 
  $dbh->disconnect();
  exit(0);
 
sub gensep()
{
  my ($count) = @_;
  my $sql;
 
  ## 区切りSQL
  $sql = "SELECT 0, 3, ";
  for(my $cnt=0; $cnt<$count; $cnt++)
  {
    $sql .=',' if ($cnt != 0);
    $sql .= "'---'";
  }
 
  return $sql;
}
sub gensql()
{
  my ($count, $id)=@_;
  my $sql  = "";
  my $sql2 = "";
 
  ## 公演日を横につなげる
  $sql = 'SELECT 0 as sel1, 1 as sel2, ';
  for(my $cnt=0; $cnt<$count; $cnt++)
  {
    $sql .=',' if ($cnt != 0);
    $sql .= sprintf("cast(max(case stage when %d then event_date else null end) as varchar) as \"col%d\"\n", $cnt+1, $cnt);
  }
  $sql .= sprintf(" FROM events WHERE tour_id=%d\n", $id);
 
  ## 開催場所、会場を横につなげる
  my @v1 = ( {tbl=>'places', col=>'place'}, {tbl=>'venues', col=>'venue'} );
  foreach my $item ( @v1 )
  {
    $sql .= " UNION ALL\n SELECT 0, 2, ";
    for(my $cnt=0; $cnt<$count; $cnt++)
    {
      $sql .=',' if ($cnt != 0);
      $sql .= sprintf("max(case t1.stage when %d then t2.%s else null end)\n", $cnt+1, $item->{"col"});
    }
    $sql .= sprintf(" FROM events t1 left outer join %s t2 on (t1.%s_id=t2.id) WHERE tour_id=%d\n",$item->{"tbl"}, $item->{"col"}, $id);
  }
 
  $sql .= " UNION ALL\n " . &gensep($count) . "\n";
 
  ## セットリスト、アンコール演目、Wアンコール演目を横につなげる
  foreach my $item (1,2,3)
  {
    $sql .= " UNION ALL\n SELECT 1 as sel1, s1.order_index as sel2, ";
    $sql2 = "SELECT order_index, ";
    for(my $cnt=0; $cnt<$count; $cnt++)
    {
      $sql2 .=',' if ($cnt != 0);
      $sql2 .= sprintf("max(case t1.stage when %d then t3.song_title else null end) as \"col%d\"\n", $cnt+1, $cnt);
 
      $sql .=',' if ($cnt != 0);
      $sql .= sprintf("s1.col%d\n", $cnt);
    }
    $sql2 .= sprintf(" FROM events t1 left       join setlists t2 on (t1.id      = t2.event_id)
                                      left outer join songs    t3 on (t2.song_id = t3.id)
                     WHERE tour_id=%d
                       AND t2.list_type=%d
                     GROUP BY t2.order_index
                     ORDER BY t2.order_index", $id, $item);
    $sql .= " FROM (" . $sql2 . ") s1\n";
 
    $sql .= " UNION ALL\n " .  &gensep($count) . "\n";
  }
 
  return $sql;
}