使用 Perl 读取文件行并将其写入各种数据库表的最佳方法是什么？

Question

我正在尝试读取一个包含票号的平面文件，并将数据写入数据库。每个票证的格式相似，在行(R(和列(C(中都有不同长度大小的记录(用括号内的数字表示(。数据库有3个表，分别命名为R1_table、R2_table和R3_table，用于存储平面文件中的行。票证的第1、2和3行中的记录都分别以10、20和30开头，这就是我用来确定行数据应该放在哪个数据库表中的内容。平面文件如下所示；

# Ticket_0001 
10 Field_R1_C1(40) Field_R1_C2(32) Field_R1_C3(8) ... Field_R1_Cn(3)
20 Field_R2_C1(20) Field_R2_C2(12) Field_R2_C3(18) ... Field_R2_Cn(6)
30 Field_R3_C1(30) Field_R3_C2(10) Field_R3_C3(2) ... Field_R3_Cn(12)
...
# Ticket_000n
10 Field_R1_C1(40) Field_R1_C2(32) Field_R1_C3(8) ... Field_R1_Cn(3)
20 Field_R2_C1(20) Field_R2_C2(12) Field_R2_C3(18) ... Field_R2_Cn(6)
30 Field_R3_C1(30) Field_R3_C2(10) Field_R3_C3(2) ... Field_R3_Cn(12)

使用下面的这种长方法，我可以将所有记录写入数据库，但我觉得应该有一种更好、更短的方法来做到这一点，而不必复制和粘贴相同的代码，也不必更改表和列名。

my $R1_table = "R1_table"; # table name for row 1 records
my @R1_columns = qw(Field_R1_C1 Field_R1_C2 Field_R1_C3 ... Field_R1_Cn); # column names for R1_table
my @R1_column_start = (1, 41, 73, ...250); # starting positions for columns in R1_table
my @R1_column_end = (40, 72, 80 ... 300); #ending positions for columns R1_table
...
# same for the next tables R2_table and R3_table
my $filename = 'ticket.dat';
open(my $fh, '<:encoding(UTF-8)', $filename) or die "Could not open file '$filename' $!";
while (my $row = <$fh>) {
write_to_db($row); # call subroutine and write record to database
}

sub write_to_db 
{
my $row = shift;
my $record_type = substr($row, 0, 2); # to identify which table to write to
my $record; 
my $column_size;
my @insert_data;
my $offset = 0;
if($record_type eq '10'){
for(my $i = 0; $i <= $#R1_column_end; $i++){
# break row data into columns
$offset = $R1_column_start[$i] - 1;
$column_size = $R1_column_end[$i] - $R1_column_start[$i] + 1;
$record = substr($row, $offset, $column_size); 
push @insert_data, $record; # add column to an array
}
my $sql_fields = join(",", @R1_columns);
my $sql_placeholders = join(",", map {"?"} @R1_columns);
# prepare your statement for connecting to the database
$dbh->prepare("INSERT INTO $R1_table ($sql_fields) VALUES ($sql_placeholders)")->execute(@insert_data);
}
if($record_type eq '20'){#same body as above but with R2_table and columns instead }
if($record_type eq '30'){#same body as above but with R3_table and columns instead }
}

我希望你明白我想说的话。我的问题是，读取文件行并将其写入数据库中的各种表的最佳方式是什么？

Answer 1

使用哈希来保存所有信息，而不是为不同的表使用不同的变量：

my %tables = (
R1 => [ [ 'Field_R1_C1', 11, 40 ],
[ 'Field_R1_C2', 41, 72 ],
[ 'Field_R1_C3', 73, 80 ],
...,
[ 'Field_R1_Cn', 250, 300 ] ],
R2 => [ [ ... ] ],
);

然后修改写入子以使用此结构：

sub write_to_db {
my ($row) = @_;
my $record_type = substr $row, 0, 2;
my %table_names = ( 10 => 'R1',
20 => 'R2',
30 => 'R3' );
my $table = $table_names{$record_type};
die "Unknown table for record type $record_typen"
unless defined $table;
my @insert_data;
for my $column (@{ $tables{$table} }) {
my ($column_name, $column_start, $column_end) = @$column;
my $record = substr $row, $column_start - 1,
$column_end - $column_start + 1;
push @insert_data, $record;
}
my $sql_columns = join ',', map $_->[0], @{ $tables{$table} };
my $placeholders = join ',', ('?') x @insert_data;
my $insert = $dbh->prepare(
"INSERT INTO $table ($sql_columns) VALUES ($placeholders)");
$insert->execute(@insert_data);
}

并将其称为每一行：

write_to_db($row);