{"id":86,"date":"2006-10-10T16:39:16","date_gmt":"2006-10-10T07:39:16","guid":{"rendered":"https:\/\/deskplate.net\/blog\/2006\/10\/10\/db%e5%ae%9a%e7%be%a9%e6%9b%b8%e5%87%ba%e5%8a%9b%e3%83%97%e3%83%ad%e3%82%b0%e3%83%a9%e3%83%a0\/"},"modified":"2006-10-10T16:39:16","modified_gmt":"2006-10-10T07:39:16","slug":"db%e5%ae%9a%e7%be%a9%e6%9b%b8%e5%87%ba%e5%8a%9b%e3%83%97%e3%83%ad%e3%82%b0%e3%83%a9%e3%83%a0","status":"publish","type":"post","link":"https:\/\/deskplate.net\/blog\/archives\/86","title":{"rendered":"DB\u5b9a\u7fa9\u66f8\u51fa\u529b\u30d7\u30ed\u30b0\u30e9\u30e0"},"content":{"rendered":"<p>Perl\u30e2\u30b8\u30e5\u30fc\u30eb\u3092\u4f7f\u3063\u3066\u30a8\u30af\u30bb\u30eb\u30d5\u30a1\u30a4\u30eb\u3092\u4f5c\u308a\u307e\u3059<br \/>\n\u30bd\u30fc\u30b9\u306f\u3053\u3093\u306a\u611f\u3058\u3002<br \/>\n\u8a2d\u5b9a\u3092\u5909\u3048\u308c\u3070PostgreSQL, MySQL \u4e21\u65b9\u3067\u4f7f\u3048\u307e\u3059<br \/>\n#!C:\\Perl\\bin\\perl<br \/>\n#!\/usr\/local\/bin\/perl<br \/>\nuse strict;<br \/>\nuse DBI;<br \/>\nuse Jcode;<br \/>\nuse Spreadsheet::WriteExcel;<br \/>\nuse DBIx::DBSchema;<br \/>\n#\u8a2d\u5b9a<br \/>\nmy $DSN\t                     = &#8216;dbi:mysql:DB\u540d:\u30b5\u30fc\u30d0\u30fc\u540d&#8217;;<br \/>\nmy $DB_USER \t= &#8216;\u30e6\u30fc\u30b6\u30fc\u540d&#8217;;<br \/>\nmy $DB_PASSWD\t= &#8216;\u30d1\u30b9\u30ef\u30fc\u30c9&#8217;;<br \/>\nmy $XLS_FILE\t= &#8216;\u51fa\u529b\u30d5\u30a1\u30a4\u30eb\u540d&#8217;;<br \/>\nmy $dbh = DBI->connect($DSN, $DB_USER, $DB_PASSWD) or die $DBI::errstr;<br \/>\nmy $xls = Spreadsheet::WriteExcel->new($XLS_FILE);<br \/>\nmy $schema = new_native DBIx::DBSchema $dbh;<br \/>\nfor my $table ($schema->tables()) {<br \/>\nmy $work = $xls->addworksheet(&#8220;$table&#8221;);<br \/>\n$work->write(0, 0, &#8216;INDEXES&#8217;);<br \/>\nmy $index_ref = $dbh->selectall_arrayref(&#8220;SHOW INDEX FROM $table&#8221;);<br \/>\nwrite_worksheet($work, $index_ref, 1);<br \/>\n$work->write(3 + $#{$index_ref}, 0, &#8216;COLUMNS&#8217;);<br \/>\nmy $column_ref = $dbh->selectall_arrayref(&#8220;DESCRIBE $table&#8221;);<br \/>\nwrite_worksheet($work, $column_ref, 4 + $#{$index_ref});<br \/>\n}<br \/>\n$dbh->disconnect;<br \/>\nsub write_worksheet {<br \/>\nmy($work, $ref, $offset) = @_;<br \/>\nfor my $row (0 .. $#{$ref}) {<br \/>\nfor my $col (0 .. $#{$ref->[$row]}) {<br \/>\n$work->write($row + $offset, $col, $ref->[$row]->[$col]);<br \/>\n}<br \/>\n}<br \/>\n}<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Perl\u30e2\u30b8\u30e5\u30fc\u30eb\u3092\u4f7f\u3063\u3066\u30a8\u30af\u30bb\u30eb\u30d5\u30a1\u30a4<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,2,11],"tags":[],"class_list":["post-86","post","type-post","status-publish","format-standard","hentry","category-mysql","category-perl","category-postgresql"],"_links":{"self":[{"href":"https:\/\/deskplate.net\/blog\/wp-json\/wp\/v2\/posts\/86","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/deskplate.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/deskplate.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/deskplate.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/deskplate.net\/blog\/wp-json\/wp\/v2\/comments?post=86"}],"version-history":[{"count":0,"href":"https:\/\/deskplate.net\/blog\/wp-json\/wp\/v2\/posts\/86\/revisions"}],"wp:attachment":[{"href":"https:\/\/deskplate.net\/blog\/wp-json\/wp\/v2\/media?parent=86"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/deskplate.net\/blog\/wp-json\/wp\/v2\/categories?post=86"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/deskplate.net\/blog\/wp-json\/wp\/v2\/tags?post=86"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}