取出数据库旧表数据,然后生成新表程序
戻る
<![CDATA[
#!C:/perl/bin/perl
# change_main_type1.pl 07.12.10
use strict;
use DBI;
my (%t,@rec);
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
open(OUT,">main_type1_tmp.txt");
$t{sth} = $t{dbh}->prepare("select * from main_type1");
$t{sth}->execute;
while (@rec = $t{sth}->fetchrow_array) {
$t{line} = join("==>",@rec);
print OUT $t{line},"\n";
}
$t{sth}->finish;
$t{dbh}->disconnect;
close(OUT);
print "The output file is main_type1_tmp.txt.\n";
__END__
--------------------------------------------------------------
#!C:/perl/bin/perl
# change_main_type2.pl 07.12.10
use strict;
my (%t,@fld,$n);
# データを読む
open(IN,"main_type1_tmp.txt");
while(<IN>){
chop;
@fld = split(/==>/);
next unless $fld[1];
push(@{ $t{name} },$fld[1]);
}
close(IN);
# テーブルを生成
open(OUT,">../sql/main_type1.sql");
print OUT 'DROP TABLE IF EXISTS main_type1;',"\n";
print OUT 'CREATE TABLE main_type1',"\n";
print OUT '(',"\n";
print OUT ' id INT AUTO_INCREMENT,',"\n";
print OUT ' name VARCHAR(50),',"\n";
print OUT ' series VARCHAR(50),',"\n";
print OUT ' DWG VARCHAR(50),',"\n";
print OUT ' memo VARCHAR(200),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
# データを挿入
for $n ( 0 .. $#{ $t{name} } ) {
print OUT 'INSERT INTO main_type1 (name) VALUES("';
print OUT $t{name}[$n],'");',"\n";
}
close(OUT);
print "The output table is main_type1.sql.\n";
__END__
--------------------------------------------------------------
# make_ptables.pl
# パーツテーブルの生成
use strict;
use DBI;
my(%t,$n,@fld);
print "This is make_ptables.pl.\n";
# テーブルの名前を生成する
open(IN,"main_type1_tmp.txt") or die "Can't open the file main_type1_tmp.txt.\n";
while(<IN>){
chop;
@fld = split(/==>/);
next unless $fld[1];
$t{NO5} = sprintf("%06d",$fld[0]);
push(@{ $t{ID} },$t{NO5});
}
close(IN);
# データベースに連結
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$t{dbh}->do("SET NAMES utf8");
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
for $n ( 0 .. $#{ $t{ID} } ) {
#for $n ( 0 .. 0 ) {
$t{NO5} = $t{ID}[$n];
$t{table} = 'a' . $t{NO5};
# 同名テーブルが存在している場合,削除
$t{sql} = 'DROP TABLE IF EXISTS ' . $t{table} . ';';
$t{dbh}->do($t{sql});
# テーブルを作成する
$t{sql} = 'CREATE TABLE ' . $t{table};
$t{sql} .= ' (';
$t{sql} .= 'id INT AUTO_INCREMENT,';
$t{sql} .= 'name VARCHAR(50),';
$t{sql} .= 'code VARCHAR(50),';
$t{sql} .= 'engine INT,';
$t{sql} .= 'price VARCHAR(50),';
$t{sql} .= 'money INT,';
$t{sql} .= 'memo TEXT,';
$t{sql} .= 'PRIMARY KEY (id));';
$t{dbh}->do($t{sql});
print "The table $t{table} is created.\n";
}
$t{dbh}->disconnect;
__END__;
--------------------------------------------------------------
#!C:/perl/bin/perl
# change_main_type1.pl 07.12.10,16
use strict;
use DBI;
use utf8;
my (%t,@rec);
$t{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$t{dbh} = DBI->connect($t{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$t{dbh}->do("SET NAMES utf8");
if(!$t{dbh}){
print "SQL read ERROR!\n";
exit;
}
open(OUT,">main_type1_tmp.txt");
$t{sth} = $t{dbh}->prepare("select * from main_type1");
$t{sth}->execute;
while (@rec = $t{sth}->fetchrow_array) {
$t{line} = join("==>",@rec);
print OUT $t{line},"\n";
}
$t{sth}->finish;
$t{dbh}->disconnect;
close(OUT);
print "The output file is main_type1_tmp.txt.\n";
__END__
--------------------------------------------------------------
#!C:/perl/bin/perl
# change_main_type2.pl 07.12.10,16
use strict;
my (%t,@fld,$n);
# データを読む
open(IN,"main_type1_tmp.txt");
while(<IN>){
chop;
@fld = split(/==>/);
next unless $fld[0];
push(@{ $t{name} },$fld[1]);
}
close(IN);
# テーブルを生成
open(OUT,">../sql/main_type1.sql");
print OUT 'DROP TABLE IF EXISTS main_type1;',"\n";
print OUT 'CREATE TABLE main_type1',"\n";
print OUT '(',"\n";
print OUT ' id INT AUTO_INCREMENT,',"\n";
print OUT ' name VARCHAR(50),',"\n";
print OUT ' series VARCHAR(50),',"\n";
print OUT ' GR TEXT,',"\n";
print OUT ' DWG TEXT,',"\n";
print OUT ' memo VARCHAR(200),',"\n";
print OUT ' PRIMARY KEY (id)',"\n";
print OUT ');',"\n\n";
# データを挿入
for $n ( 0 .. $#{ $t{name} } ) {
print OUT 'INSERT INTO main_type1 (name) VALUES("';
print OUT $t{name}[$n],'");',"\n";
}
close(OUT);
print "The output table is main_type1.sql.\n";
__END__
--------------------------------------------------------------
]]>
戻る