Hi everybody! I'm currently deve

[feiner99]

Hi everybody!

I'm currently developping a small database interface
using Perl. The whole thing deals with courses at a
school for fire fighters in Regensburg (Bavaria, Germany).
The goal is to delete entries automatically by comparing
the courses beginning date with the current date. The
deleted data should be written into a new CSV file to
have a complete overview at the end of the year.

An example: let's pretend the course "Bootsführer" starts
on 13-05-02 (European date format: the 13th of May, 2002).
It should be deleted and re-written in a new CSV file
(same format) some days ahead, let's say on 06-05-02.

The data file looks like this:

01-02-C21-001-02|W|Bootsführer|13-05-02|17-05-02|12
01-02-C28-001-02|W|Techn. Hilfeleistung RW|11-03-02|15-03-02|32
01-02-C04-001-02|G|Leiter einer Feuerwehr|07-01-02|10-01-02|24

(and so forth...)

Here is what I assume:

1. We have to accomplish some date comparison based on epoch time
(seconds since 01/01/1970)

2. We need to establish some temporary file during the processing
of the original file otherwise we cannot write the entries to be
deleted into a new file. (...Can we?)

3. May be a cron job is necessary to make sure that the required
processing is done without having to call the script manually (or,
whilst users log in), e.g. at 11:55 p.m. each and every day...

Perhaps somebody out there has already been confronted with similar tasks.

I'd appreciate your help very much! - Thank you in advance for any useful hint.

Greetings from Bavaria -

Andreas
andreasfeiner@firemail.de

If you can't read German:

Feuerwehr = fire department
Bootsführer = boat captain
Techn(ische) Hilfeleistung = technical support
Leiter = chief

Field 4 (Perl count: 3): beginning of the resp. course
Field 5 (Perl count: 4): ending of course
Date format: dd-mm-yy

 

[hmerrill]

Andreas, different databases deal with dates differently - what database are you using? MySQL has a current date/time function called SYSDATE() or NOW(), and Oracle uses SYSDATE - you should probably do the date math in the database, since you have to get the current date/time from the database. So your SQL will need to compare the current date/time(one of the above database functions) to the course start date. What is the column type of the column that stores the course start date?

Establishing a file to append to is a simple open for writing(or output) like this - the >> signifies *append*:

my $delete_file = "/path/to/delete/file";
open(OUT, ">>$delete_file&quot || die "Can't open $delete_file for append!";

A cron job would be suitable.
Hardy Merrill
Mission Critical Linux, Inc.

http://www.missioncriticallinux.com

 

[feiner99]

Dear Hardy,

well, I'm using >>>> no SQL at all <<<<. It's just a simple flat file separated by pipe symbols (|) as you see.

Even more: the whole thing should work without any additional perl modules to be installed (e.g. DBI).

Here's my coding for date comparison:

$Tage=7; # user can set it as needed

############################################################
# Zeitvergleich - date comparison
############################################################

sub auto_expire
 {

use strict;
use Time::Local;
use vars qw ($Tage);

#Datumsvergleich (date comparison)

# Beispieldatum nach Timestamp in datenfeld[3] (= Lehrgangsbeginn)
my $dbdate='datenfeld[3]';
$dbdate =~ s/-//g;

#Relevante Werte extrahieren (extracting relevant fields)
my ($dbday, $dbmonth, $dbyear)= $dbdate =~ m/(\d{2})(\d{2})(\d){2}/;

if ($dbyear < 10) { $dbyear = &quot;0$dbyear&quot;; }

my $dbmonth = $dbmonth - 01;

#in Epochenzeit wandeln (convert to UNIX epoch time)
$dbdate = timelocal(0,0,0,$dbday,$dbmonth,$dbyear+2000);

#Aktuelles Datum abzüglich der Verfallsfrist
#Current date minus the days the file should be deleted aforehead

#my $Tage = $Tage;

my $Verfallsfrist = $Tage*86400;
my $Jetztwert = time();
my $Jetztzeit = localtime($Jetztwert);

my $Zeitdifferenz = $dbdate - $Jetztwert - $Verfallsfrist;

}

############################################################

What do you think? Is it okay?

Do you have any codings to accomplish the atomized deletion of entries and the cron job?

Thank you for your hints!

Andreas
andreasfeiner@firemail.de