Loader script - Design question

artanisace has asked for the wisdom of the Perl Monks concerning the following question:

Hello monks, I'm totally new to Perl (just 2 days coding!), and so far I'm liking it. I've been assigned a task in Perl, that basically is this:

I should script a loader, that will read a .tsv with 3 columns
This records should be loaded into a database, which will have the same columns that the .tsv plus a 'Timestamp' one
There are several conditions to operate in the database; # If there already exists record in DB with given ID, that record is updated with information from file and Update_timestamp is set to system current timestamp. # If new record is detected in file, new record is created in DB with attributes from file # and Update_timestamp is set to system current timestamp. # If any update is detected, record in DB is updated and Update_timestamp is set to system current timestamp. # If record does not exist in the file, no action is taken.

my biggest concern is that I don't know which will be the best way to "compare" records from the file and the DB to properly update the DB. For now, I have used Text::CSV for reading the file and converting it to a hash ref array, but I'm not sure what will be the best solution for comparing this data with the DB (I'm using MySQL)... I've tried 'selectall_hashref' from DBI class and the result is messed... Dear wise monks, what would be your approach

Comment on Loader script - Design question

Replies are listed 'Best First'.
Re: Loader script - Design question by Anonymous Monk on Feb 21, 2016 at 14:44 UTC
Sounds like you want to use `INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;` [download] http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html	[reply] [d/l]
Re: Loader script - Design question by Cody Fendant (Hermit) on Feb 23, 2016 at 01:56 UTC
You don't need `selectall_hashref` if you're just looking at one record, which is what you should be doing if you have an ID. You need `selectrow_hashref`. It might be useful if you could say what you mean by "the result is messed" The `selectrow_hashref` should give you something like: `$ref = { 'column_1' => 'foo', 'column_2' => 'bar', 'id' => 12345 };` [download] At which point you can just check whether your column_1 from the CSV file matches `$ref->{column_1}` etc.	[reply] [d/l] [select]
Re^2: Loader script - Design question by artanisace (Initiate) on Feb 23, 2016 at 12:38 UTC
This is what I meant by the result is messed, if I do this for example: `# Update DB with file content my @keys = ('ID'); my $hash = $dbh->selectall_hashref("SELECT * FROM users", \@keys); print Dumper \$hash;` [download] This happens: http://postimg.org/image/3rhdfupnx/ If I add more values to @keys, the 'chain' of hashref gets bigger... and I don't really know what's happening :S	[reply] [d/l]
Re^3: Loader script - Design question by poj (Abbot) on Feb 23, 2016 at 12:54 UTC
the 'chain' of hashref gets bigger. That's what selectall_hashref does The $key_field parameter defines which column, or columns, are used as keys in the returned hash. It can either be the name of a single field, or a reference to an array containing multiple field names. Using multiple names yields a tree of nested hashes. What structure do you want ? poj	[reply]
Re^4: Loader script - Design question by artanisace (Initiate) on Feb 23, 2016 at 13:14 UTC
Re^5: Loader script - Design question by poj (Abbot) on Feb 23, 2016 at 13:56 UTC
Some notes below your chosen depth have not been shown here