Inserting, update and deleteting a database under conditions

[Nikos]

Hello, i'am trying to perl code the following ideas i have commented
as remarks but my attempts were fruitless.
Its complicated and although i tried to work some code, my code does
not do what the remarks say.
#===============================================================================

my @files = glob "$ENV{'DOCUMENT_ROOT'}/data/text/*.txt";
my @menu_files = map m{([^/]+)\.txt}, @files;

my @dbfiles = $db->do( 'SELECT title, body FROM articles' );
my $replace = $db->prepare( 'INSERT INTO article (title, body) VALUES
(?, ?)' );


foreach ( @menu_files ) {

#Check every filename(item in @menu_files) as well as its content
to see if is already inserted in table articles under columns title
and body

#============ INSERT NEW FILENAMES & CONTENTS INTO DATABASE IF ANY
=============
#If filename and content dont already exist in the db then do
insert them

#===== UPDATE THE DB ENTRY IF THERE IS A MODIFICATION IN A FILES
CONTENTS =====
#If filename exists in the db under column title but its content is
different from the database's content then update column body in the
db

#======== DELETE ANY DB ENTRY OF A FILENAME THATDOESNT EXIST ANY
MORE =========
#Delete all db records that doesnt have a corresponding filename in
the hdd
}

 

[smallpond]

Hello, i'am trying to perl code the following ideas i have commented
as remarks but my attempts were fruitless.
Its complicated and although i tried to work some code, my code does
not do what the remarks say.
#===============================================================================

my @files = glob "$ENV{'DOCUMENT_ROOT'}/data/text/*.txt";
my @menu_files = map m{([^/]+)\.txt}, @files;

my @dbfiles = $db->do( 'SELECT title, body FROM articles' );
my $replace = $db->prepare( 'INSERT INTO article (title, body) VALUES
(?, ?)' );

foreach ( @menu_files ) {

#Check every filename(item in @menu_files) as well as its content
to see if is already inserted in table articles under columns title
and body

#============ INSERT NEW FILENAMES & CONTENTS INTO DATABASE IF ANY
=============
#If filename and content dont already exist in the db then do
insert them

#===== UPDATE THE DB ENTRY IF THERE IS A MODIFICATION IN A FILES
CONTENTS =====
#If filename exists in the db under column title but its content is
different from the database's content then update column body in the
db

#======== DELETE ANY DB ENTRY OF A FILENAME THATDOESNT EXIST ANY
MORE =========
#Delete all db records that doesnt have a corresponding filename in
the hdd

}

It sounds like you just want to make the db match the directories.
Why not just drop the tables and recreate them from your globs?
--S

 

[Michele Dondi]

Hello, i'am trying to perl code the following ideas i have commented
as remarks but my attempts were fruitless.

Nik,

before reposting your question verbatim here, you should let people
know that you asked it in perlmonks first. So people will know what
that has been told you, and you will save them some duplication of
efforts:

http://perlmonks.org/?node_id=661326


Michele

 

[Nikos]

before reposting your question verbatim here, you should let people
know that you asked it in perlmonks first. So people will know what
that has been told you, and you will save them some duplication of
efforts:

Sorry Michael i asked there and here at the sametime in case my
question ouldn't be answered there, and it wasnt. can you help?

 

[Nikos]

It sounds like you just want to make the db match the directories.
Why not just drop the tables and recreate them from your globs?

Yes that would be the easiest thing to do bu because that code would
run every time my index.pl runs that would sow down the scripts if it
needed dropping all the tables in the db recreate them then open all
files and then also fill in the tables will ALL information regarding
filenames and corresponding file contents. iam already doing this in
my other script called init.pl an my index pl i just want to keep
things synchronized among db anf hdd. Here is my int.pl code uses
to do what you described.

#=========== INSERTING MULTIPLE FILENAMES & CONTENTS INTO DATABASE
=============
my $data;
my $insert = $db->prepare( 'INSERT INTO articles (title, body) VALUES
(?, ?)' );

my @files = glob "$ENV{'DOCUMENT_ROOT'}/data/text/*.txt";

foreach (@files)
{
open FILE, "$_" or die $!;
Encode::from_to($_, 'ISO-8859-7', 'utf-8'); #Convert the
filename to utf8 encoding
($_) = map m{([^/]+)\.txt}, $_; #Strip out of the
string the path and extension leaving only the filename intact

$data = <FILE>; #Grab the whole
file contents at once

$insert->execute($_, $data) or print $db->errstr;
close FILE;
}

 

[Michele Dondi]

Sorry Michael i asked there and here at the sametime in case my
question ouldn't be answered there, and it wasnt. can you help?

I'm afraid I can't, since it seems to me that your question *was*
answered there. But since this differs from your own perception, I
doubt I can add anything. It would be nice of you to specify, instead,
*what* of those answers did failt o satisfy you.


Michele