操作数据库零件表的四个程序
返回
<![CDATA[
运行perl input_ptables.pl时,
有如下出错指示
.........
DBD::mysql::db do failed: Table 'cookbook.a008278' doesn't exist at input_ptable
s.pl line 86.
DBD::mysql::db do failed: Table 'cookbook.a008278' doesn't exist at input_ptable
s.pl line 86.
DBD::mysql::db do failed: Table 'cookbook.a008278' doesn't exist at input_ptable
s.pl line 86.
说明有大量的TABLES没有生成!需要检查
网页程序不需要执行perl input_ptables.pl,
只要直接执行perl parts04.pl即可!
]]>
零件表插入已有的数据(部分)(input_ptables2.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld,$pref,@rec);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 取出main_type1的编号,同时生成零件表名
@{ $t{ptables} } = ();
$t{sth} = $$pref{dbh}->prepare("SELECT id FROM main_type1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{ptable1} = sprintf("%06d",$rec[0]);
$t{ptable1} = 'a' . $t{ptable1};
push(@{ $t{ptables} },$t{ptable1});
}
$t{sth}->finish;
# 从../txt/ptables.txt读取原有零件表数据
open(IN,"../txt/ptables.txt") or die "Can't open the file ptables.txt\n";
while(<IN>){
if (/^PT/){
chop;
@fld = split(/===/);
push(@{ $$pref{id}{$fld[1]} },$fld[2]);
push(@{ $$pref{name}{$fld[1]} },$fld[3]);
push(@{ $$pref{code}{$fld[1]} },$fld[4]);
push(@{ $$pref{dwg_id}{$fld[1]} },$fld[5]);
push(@{ $$pref{Nuid}{$fld[1]} },$fld[6]);
}
}
close(IN);
# 插入数据
for $n ( 0 .. $#{ $t{ptables} } ) {
$$pref{ptable1} = $t{ptables}[$n];
($pref) = input_ptable1($pref);
}
# 关闭数据库
$$pref{dbh}->disconnect;
print "Finished.\n";
sub input_ptable1 {
my($pref) = @_;
my(%t,$n);
if ( $$pref{id}{$$pref{ptable1}}[0] == 0 ) {
return($pref);
}
$t{maxid} = $$pref{dbh}->selectrow_array("SELECT max(id) FROM $$pref{ptable1}");
$t{maxid} = $t{maxid} + 0;
$t{count} = $t{maxid} - 1;
if ( $t{count} < $#{ $$pref{id}{$$pref{ptable1}} } ) {
print "ptable=$$pref{ptable1},count=$t{count},maxid=$t{maxid},$#{ $$pref{id}{$$pref{ptable1}} }\n";
for $n ( $t{maxid} .. $#{ $$pref{id}{$$pref{ptable1}} } ) {
$t{id} = $n + 1;
$t{name} = $$pref{name}{$$pref{ptable1}}[$n];
$t{code} = $$pref{code}{$$pref{ptable1}}[$n];
$t{dwg_id} = $$pref{dwg_id}{$$pref{ptable1}}[$n];
$t{Nuid} = $$pref{Nuid}{$$pref{ptable1}}[$n];
$t{sql} = 'INSERT INTO ' . $$pref{ptable1};
$t{sql} .= ' (name,code,dwg_id,Nuid,weight,price1,price2) ';
$t{sql} .= 'VALUES("';
$t{sql} .= $t{name} . '","';
$t{sql} .= $t{code} . '","';
$t{sql} .= $t{dwg_id} . '","';
$t{sql} .= $t{Nuid} . '",1,"0=100=1=0000-00-00=1","0=100=1=0000-00-00=1=1");';
$$pref{dbh}->do($t{sql});
}
}
return($pref);
}
]]>
生成数据库零件表(部分)(make_ptables2.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld,$pref,@rec);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 列出现有全部parts的TABLE
@{ $t{ps} } = ();
$t{sth} = $$pref{dbh}->prepare("SHOW tables");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
if ( substr($rec[0],0,1) eq 'a' ) {
$t{pmax1} = $rec[0];
}
# $t{ptable1} = sprintf("%06d",$rec[0]);
# $t{ptable1} = 'a' . $t{ptable1};
}
$t{sth}->finish;
print "pmax1=$t{pmax1}\n";
$t{pm1} = substr($t{pmax1},1,6);
$t{pm1} = $t{pm1} + 0;
# 取出main_type1的编号,同时生成零件表名
@{ $t{ptables} } = ();
$t{sth} = $$pref{dbh}->prepare("SELECT id FROM main_type1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
next if $rec[0] <= $t{pm1};
$t{ptable1} = sprintf("%06d",$rec[0]);
$t{ptable1} = 'a' . $t{ptable1};
push(@{ $t{ptables} },$t{ptable1});
}
$t{sth}->finish;
#print "ptables=@{ $t{ptables} }\n";
# 生成零件表
for $n ( 0 .. $#{ $t{ptables} } ) {
$$pref{ptable1} = $t{ptables}[$n];
($pref) = ptable1($pref);
}
# 关闭数据库
$$pref{dbh}->disconnect;
print "Finished.\n";
sub ptable1 {
my($pref) = @_;
my(%t);
$t{sql} = 'DROP TABLE IF EXISTS ' . $$pref{ptable1} . ';';
$$pref{dbh}->do($t{sql});
$t{sql} = 'CREATE TABLE ' . $$pref{ptable1};
$t{sql} .= ' (';
$t{sql} .= 'id INT AUTO_INCREMENT,';
$t{sql} .= 'name TEXT,';
$t{sql} .= 'code TEXT,';
$t{sql} .= 'dwg_id INT,';
$t{sql} .= 'Nuid INT,';
$t{sql} .= 'weight INT,';
$t{sql} .= 'price1 TEXT,';
$t{sql} .= 'price2 TEXT,';
$t{sql} .= 'memo TEXT,';
$t{sql} .= 'PRIMARY KEY (id));';
$$pref{dbh}->do($t{sql});
return($pref);
}
]]>
生成数据库零件表(全部)(make_ptables.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld,$pref,@rec);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 取出main_type1的编号,同时生成零件表名
@{ $t{ptables} } = ();
$t{sth} = $$pref{dbh}->prepare("SELECT id FROM main_type1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{ptable1} = sprintf("%06d",$rec[0]);
$t{ptable1} = 'a' . $t{ptable1};
push(@{ $t{ptables} },$t{ptable1});
}
$t{sth}->finish;
# 生成零件表
for $n ( 0 .. $#{ $t{ptables} } ) {
$$pref{ptable1} = $t{ptables}[$n];
($pref) = ptable1($pref);
}
# 关闭数据库
$$pref{dbh}->disconnect;
print "Finished.\n";
sub ptable1 {
my($pref) = @_;
my(%t);
$t{sql} = 'DROP TABLE IF EXISTS ' . $$pref{ptable1} . ';';
$$pref{dbh}->do($t{sql});
$t{sql} = 'CREATE TABLE ' . $$pref{ptable1};
$t{sql} .= ' (';
$t{sql} .= 'id INT AUTO_INCREMENT,';
$t{sql} .= 'name TEXT,';
$t{sql} .= 'code TEXT,';
$t{sql} .= 'dwg_id INT,';
$t{sql} .= 'Nuid INT,';
$t{sql} .= 'weight INT,';
$t{sql} .= 'price1 TEXT,';
$t{sql} .= 'price2 TEXT,';
$t{sql} .= 'memo TEXT,';
$t{sql} .= 'PRIMARY KEY (id));';
$$pref{dbh}->do($t{sql});
return($pref);
}
__END__;
]]>
零件表插入已有的数据(全部)(input_ptables.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld,$pref,@rec);
print "This is input_ptables.pl.\n";
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 取出main_type1的编号,同时生成零件表名
@{ $t{ptables} } = ();
$t{sth} = $$pref{dbh}->prepare("SELECT id FROM main_type1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{ptable1} = sprintf("%06d",$rec[0]);
$t{ptable1} = 'a' . $t{ptable1};
push(@{ $t{ptables} },$t{ptable1});
}
$t{sth}->finish;
# 从../txt/ptables.txt读取原有零件表数据
open(IN,"../txt/ptables.txt") or die "Can't open the file ptables.txt\n";
while(<IN>){
if (/^PT/){
chop;
@fld = split(/===/);
push(@{ $$pref{id}{$fld[1]} },$fld[2]);
push(@{ $$pref{name}{$fld[1]} },$fld[3]);
push(@{ $$pref{code}{$fld[1]} },$fld[4]);
push(@{ $$pref{dwg_id}{$fld[1]} },$fld[5]);
push(@{ $$pref{Nuid}{$fld[1]} },$fld[6]);
}
}
close(IN);
# 插入数据
for $n ( 0 .. $#{ $t{ptables} } ) {
$$pref{ptable1} = $t{ptables}[$n];
($pref) = input_ptable1($pref);
}
# 关闭数据库
$$pref{dbh}->disconnect;
print "Finished.\n";
sub input_ptable1 {
my($pref) = @_;
my(%t,$n);
if ( $$pref{id}{$$pref{ptable1}}[0] == 0 ) {
return($pref);
}
for $n ( 0 .. $#{ $$pref{id}{$$pref{ptable1}} } ) {
$t{id} = $n + 1;
$t{name} = $$pref{name}{$$pref{ptable1}}[$n];
$t{code} = $$pref{code}{$$pref{ptable1}}[$n];
$t{dwg_id} = $$pref{dwg_id}{$$pref{ptable1}}[$n];
$t{Nuid} = $$pref{Nuid}{$$pref{ptable1}}[$n];
$t{sql} = 'INSERT INTO ' . $$pref{ptable1};
$t{sql} .= ' (name,code,dwg_id,Nuid,weight,price1,price2) ';
# if ( $t{dwg_id} == 0 ) {
# $t{dwg_id} = 1;
# }
# if ( $t{Nuid} == 0 ) {
# $t{Nuid} = 1;
# }
$t{sql} .= 'VALUES("';
$t{sql} .= $t{name} . '","';
$t{sql} .= $t{code} . '","';
$t{sql} .= $t{dwg_id} . '","';
$t{sql} .= $t{Nuid} . '",1,"0=100=1=0000-00-00=1","0=100=1=0000-00-00=1=1");';
$$pref{dbh}->do($t{sql});
}
return($pref);
}
__END__;
]]>
修改所有零件表的部分数据(change_ptables.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld,$pref,@rec);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 取出main_type1的编号,同时生成零件表名
@{ $t{ptables} } = ();
$t{sth} = $$pref{dbh}->prepare("SELECT id FROM main_type1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{ptable1} = sprintf("%06d",$rec[0]);
$t{ptable1} = 'a' . $t{ptable1};
push(@{ $t{ptables} },$t{ptable1});
}
$t{sth}->finish;
# 修改数据
for $n ( 0 .. $#{ $t{ptables} } ) {
$t{ptable1} = $t{ptables}[$n];
$t{sql} = 'UPDATE ' . $t{ptable1};
$t{sql} .= ' SET price1 = "NULL"';
print "sql=$t{sql}\n";
$$pref{dbh}->do($t{sql});
$t{sql} = 'UPDATE ' . $t{ptable1};
$t{sql} .= ' SET price2 = "NULL"';
print "sql=$t{sql}\n";
$$pref{dbh}->do($t{sql});
}
# 关闭数据库
$$pref{dbh}->disconnect;
]]>
取出已有的数据库零件表数据并写入中间文件(obtain_ptables.pl)
<![CDATA[
use strict;
use DBI;
my(%t,$n,@fld,@rec,$pref);
# 连接数据库
$$pref{dsn} = "DBI:mysql:host=localhost;database=cookbook";
$$pref{dbh} = DBI->connect($$pref{dsn}, "cbuser", "cbpass") or die "Cannot connect to server\n";
$$pref{dbh}->do("SET NAMES utf8");
if(!$$pref{dbh}){
print "SQL read ERROR!\n";
exit;
}
# 取出所有表格名
@{ $t{tables} } = $$pref{dbh}->tables;
$t{all_tables} = join(' ',@{ $t{tables} });
# 取出main_type1的编号,同时生成零件表名
@{ $t{ptables} } = ();
$t{sth} = $$pref{dbh}->prepare("SELECT id FROM main_type1");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
$t{ptable1} = sprintf("%06d",$rec[0]);
$t{ptable1} = 'a' . $t{ptable1};
next unless $t{all_tables} =~ /$t{ptable1}/;
push(@{ $t{ptables} },$t{ptable1});
}
$t{sth}->finish;
# 取出所有现有零件表的数据
for $n ( 0 .. $#{ $t{ptables} } ) {
$$pref{ptable1} = $t{ptables}[$n];
($pref) = read_ptable($pref);
}
# 关闭数据库
$$pref{dbh}->disconnect;
# 写入中间文件(../txt/ptables.txt)
open(OUT,">../txt/ptables.txt");
print OUT 'filename=ptables.txt',"\n";
print OUT 'C===file===id===name===code===dwg_id===Nuid===weight===price1===price2===memo',"\n";
for $n ( 0 .. $#{ $t{ptables} } ) {
$$pref{ptable1} = $t{ptables}[$n];
($pref) = write_ptable($pref);
}
close(OUT);
print "Finished.\n";
sub write_ptable {
my($pref) = @_;
my (%t,$n);
for $n ( 0 .. $#{ $$pref{id}{$$pref{ptable1}} } ) {
$t{id} = $$pref{id}{$$pref{ptable1}}[$n];
$t{name} = $$pref{name}{$$pref{ptable1}}[$n];
# $t{name} =~ s/\x0D\x0A//g;
# $t{name} =~ s/\x0D$//; # 改行符号去掉(如果有的话)
$t{code} = $$pref{code}{$$pref{ptable1}}[$n];
# $t{code} =~ s/\x0D$//; # 改行符号去掉(如果有的话)
$t{dwg_id} = $$pref{dwg_id}{$$pref{ptable1}}[$n];
$t{Nuid} = $$pref{Nuid}{$$pref{ptable1}}[$n];
print OUT 'PT===',$$pref{ptable1};
print OUT '===',$t{id};
print OUT '===',$t{name};
print OUT '===',$t{code};
print OUT '===',$t{dwg_id};
print OUT '===',$t{Nuid};
print OUT "\n";
}
return($pref);
}
sub read_ptable {
my($pref) = @_;
my (%t,@rec);
# 读零件表
$t{sth} = $$pref{dbh}->prepare("SELECT id,name,code,dwg_id,Nuid FROM $$pref{ptable1}");
$t{sth}->execute;
while ( @rec = $t{sth}->fetchrow_array ) {
push(@{ $$pref{id}{$$pref{ptable1}} },$rec[0]);
push(@{ $$pref{name}{$$pref{ptable1}} },$rec[1]);
push(@{ $$pref{code}{$$pref{ptable1}} },$rec[2]);
push(@{ $$pref{dwg_id}{$$pref{ptable1}} },$rec[3]);
push(@{ $$pref{Nuid}{$$pref{ptable1}} },$rec[4]);
}
$t{sth}->finish;
return($pref);
}
__END__;
# 这个操作把不含Nuid的零件表删除(作业中程序,保存下来)
$t{sth} = $$pref{dbh}->prepare("SHOW COLUMNS FROM $$pref{ptable1}");
$t{sth}->execute;
$t{column_list} = '';
while ( @rec = $t{sth}->fetchrow_array ) {
$t{column_list} .= ' ' . $rec[0];
}
$t{sth}->finish;
if ( $t{column_list} !~ /Nuid/ ) {
$t{sql} = 'DROP TABLE IF EXISTS ' . $$pref{ptable1} . ';';
$$pref{dbh}->do($t{sql});
}
]]>
零件表的columns的变动
<![CDATA[
mysql> show columns from a000001;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | text | YES | | NULL | |
| code | text | YES | | NULL | |
| dwg_id | int(11) | YES | | NULL | |
| Nuid | int(11) | YES | | NULL | |
| weight | int(11) | YES | | NULL | |
| price1 | int(11) | YES | | NULL | |
| time1 | date | YES | | NULL | |
| money1 | int(11) | YES | | NULL | |
| makerid | int(11) | YES | | NULL | |
| price2 | text | YES | | NULL | |
| time2 | text | YES | | NULL | |
| money2 | text | YES | | NULL | |
| makerid2 | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
15 rows in set (0.28 sec)
mysql> show columns from a000001;
+--------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | text | YES | | NULL | |
| code | text | YES | | NULL | |
| dwg_id | int(11) | YES | | NULL | |
| Nuid | int(11) | YES | | NULL | |
| weight | int(11) | YES | | NULL | |
| price1 | text | YES | | NULL | |
| price2 | text | YES | | NULL | |
| memo | text | YES | | NULL | |
+--------+---------+------+-----+---------+----------------+
9 rows in set (0.03 sec)
*************************** 10. row ***************************
id: 10
name: p1name
code: p1code
dwg_id: 1
Nuid: 1
weight: 1
price1: 0=100=1=0000-00-00=1
price2: 0=100=1=0000-00-00=1=1
memo: NULL
price1的定义:
0==>价格
100==>Discount
1==>货币单位
0000-00-00=>日期
1==>商社
price2的定义:
0==>价格
100==>Discount
1==>货币单位
0000-00-00=>日期
1==>船东
1==>对应的商社价格(从后面数)
]]>
返回