change entrys in plain text database

Hi !
I have got a plain text database of the following structure:

item1|item2|item3
item1|item2|item3
item1|item2|item3
item1|item2|item3

and so on.
What is the most easy way to change lets say item2 in line 1 to "changeditem2" ?

I want to do this with Perl. Moreover the way of modifying the db must be very secure, I don't want to lose datas. It is possible that more than 1 person accesses the db at the same time.

regards
ItsMe
ItsMeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ozoCommented:
use Fcntl ':flock'; # import LOCK_* constants
open LOCK,">>file.lock" or die "Can't open file.lock because $!";
flock LOCK,LOCK_EX;
{local $^I=".bak"; local @ARGV=("database.txt");
  while( <> ){
    s/item2/changeditem2/ if $. == 1;
    print;
  }
}
close LOCK;
0
ItsMeAuthor Commented:
Hi ozo ! Thank you ! can you explain what the code above is doing and perhaps write it as a sub procedure ?
I need to access the Items by their position because it is possible that some items have the same value in the same line.

I would prefer:
sub changeitem(Line,ItemNo,NewValue);

Oh, and how must I write the use line ?
I already have

use LWP::Simple;

How can I add Fcntl to tis ?

regards
Itsme
0
maneshrCommented:
try this.........


====================change_item.db
Manesh|Rao|27
Frank|Williams|34
Bill|Gates|36
Jerry|Bolder|45
Louisa|Walkins|65

==================change_item.pl
#!/usr/local/bin/perl

use Fcntl ':flock'; # import LOCK_* constants

$db_file="/home/webuser/manesh/tmp/change_item.db";

open LOCK,">>file.lock" or die "Can't open file.lock because $!";
flock LOCK,LOCK_EX;
{
  local $^I=".bak";
  local @ARGV=($db_file);
  print changeitem(4,2,"Bolder");
}
close LOCK;

##  Line number of the record in the file
##  Item no = column number of the | delimited record. Starting with 1
##  New Value = value to be put in that column
sub changeitem(){
  my($Line,$ItemNo,$NewValue)=@_;
  my($records,$columns,@new_rec,@columns,$rec_num);

  open(DB,$db_file) || die $!;

  $/="";
  my($db)=<DB>; ##  Read the Entire file at a go
  $/="\n";
  close(DB);

  my(@db)=split(/\n/,$db);

  $records=scalar(@db); ##  Get the total # of records

  if ($Line>$records){
    print "DB has only $records records\n";
    return 0;
  }

  foreach($[ .. $#db){
    $rec_num=$_+1;
    @columns=split(/\|/,$db[$_]); ##  Split each record
    $columns=scalar @columns; ##  Get the total columns for the record
    if ($ItemNo>$columns){
      print "DB has only $columns columns\/Items\n";
      return 0;
    }

    if ($rec_num == $Line){ ##  matching line found
      $columns[($ItemNo-1)]=$NewValue;  ##  Replace the item with new value
      $db[$_]=join('|',@columns);
    }
    push(@new_rec,$db[$_]);
  }

  open(DB,">$db_file") || die $!;
  foreach(@new_rec){
    print DB $_."\n";
  }
  close(DB);

  return 1;
}
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

ahoffmannCommented:
. and keep in mind that flock() is not NFS-proofen :-(
0
ItsMeAuthor Commented:
can you explain me what flock is ?
0
maneshrCommented:
flock() tells the UNIX kernel that we want to arrange some sort of
shared or exclusive access (in this case, exclusive) to a particular open file. If a
file is ``flocked'' exclusively, and a second process comes along wanting the
same, it gets blocked until the process with the flock releases it (by closing the
file, calling flock() again with the proper parameter, or simply exiting).


more info at

http://www.stonehenge.com/merlyn/WebTechniques/col04.html
0
ItsMeAuthor Commented:
Thanks ! I'll try it in a few days !
0
ItsMeAuthor Commented:
Thanks, is it possible to not use the line parameter but an key:

key1|blabla|blabla...
key2|blabla|blabla...
key3|blabla|blabla...
key4|blabla|blabla...

so instead of change (0,0,'test') I would like to use change ('key1',0,'test'). I tried to get this but it doesn't work. perhaps you could modify the script ?

thanks
ItsMe
0
maneshrCommented:
would the key be unique??
if yes,i would be able to give you the modified ver. fast.

Rgds
0
ItsMeAuthor Commented:
yes. each key is unique.
0
maneshrCommented:
lovely!!

here goes....

notice that i have changed just 3 lines and moved an if statement after the for loop and added a var to do the same.
====================================================
#!/usr/local/bin/perl

use Fcntl ':flock'; # import LOCK_* constants

$db_file="/home/webuser/manesh/tmp/change_item.db";

open LOCK,">>file.lock" or die "Can't open file.lock because $!";
flock LOCK,LOCK_EX;
{
  local $^I=".bak";
  local @ARGV=($db_file);
  #print changeitem(4,2,"Bolder");
  print changeitem("Key1",3,"Bolder");
}
close LOCK;

##  Line number of the record in the file
##  Item no = column number of the | delimited record. Starting with 1
##  New Value = value to be put in that column
sub changeitem(){
  my($Line,$ItemNo,$NewValue)=@_;
  my($records,$columns,@new_rec,@columns,$rec_num);
  my($flag)=0;

  open(DB,$db_file) || die $!;

  $/="";
  my($db)=<DB>; ##  Read the Entire file at a go
  $/="\n";

  close(DB);

  my(@db)=split(/\n/,$db);

  $records=scalar(@db); ##  Get the total # of records

  foreach($[ .. $#db){
    $rec_num=$_+1;
    @columns=split(/\|/,$db[$_]); ##  Split each record
    $columns=scalar @columns; ##  Get the total columns for the record
    if ($ItemNo>$columns){
      print "DB has only $columns columns\/Items\n";
      return 0;
    }

    #if ($rec_num == $Line){  ##  matching line found
    if ($columns[0]=~ /^$Line$/){ ##  matching line found
      $columns[($ItemNo-1)]=$NewValue;  ##  Replace the item with new value
      $db[$_]=join('|',@columns);
      $flag++;  ##  To show that a match was found
    }
    push(@new_rec,$db[$_]);

  }

  #if ($Line>$records){
  if (!$flag){
    print "A matching record was not found!\n";
    return 0;
  }

  open(DB,">$db_file") || die $!;
  foreach(@new_rec){
    print DB $_."\n";
  }
  close(DB);

  return 1;
}
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ItsMeAuthor Commented:
Thank you very much !
I'll try it when I wake up tomorrow :-)
What does this do: ?

open LOCK,">>file.lock" or die "Can't open file.lock because $!";
flock LOCK,LOCK_EX;
{
  local $^I=".bak";
  local @ARGV=($db_file);
  #print changeitem(4,2,"Bolder");
  print changeitem("Key1",3,"Bolder");
}
close LOCK;

I'm relatively new in Perl. I don't know what the meaning of ^? is...
0
maneshrCommented:
Guten nacht!!
0
ItsMeAuthor Commented:
Danke schön.

Cool ! Where are u from ?

0
maneshrCommented:
i bin aus Indien, aber i kann ein bischen deutsch.
0
ItsMeAuthor Commented:
thanx !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Scripting Languages

From novice to tech pro — start learning today.