Gossamer Forum
Home : Products : DBMan : Installation :

delete records after a specific time?

(Page 2 of 3)
> > > >
Quote Reply
Re: delete records after a specific time? In reply to
Thanks Friends!!!!

Such a little question an so many helping answers!!! The internet community works !!

Markus
Quote Reply
Re: delete records after a specific time? In reply to
A quick question about the $removeby_field variable...In the default.cfg file, how do you set up the Remove Date field. I have tried using the following value in the %db_def array:

Code:
RemoveAd => [26, 'date', 15, 15, 1, '', ''],

Is this correct??

Also, is there a way to modify this particular mod that people can add in a number of days and then have the auto deletion routine take that and compute it with the date_to_unix routine.

Or am I missing something here??

TIA.

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us

[This message has been edited by Eliot (edited August 09, 1999).]
Quote Reply
Re: delete records after a specific time? In reply to
Gee, Eliot, I don't have any idea. It would take a whole lot of studying of the code to know. Maybe Mart can help you, since he works with this.


------------------
JPD





Quote Reply
Re: delete records after a specific time? In reply to
For whay about the $removeby_field, I've made a selected field value, so the user can choose, how many days his record will stay in the db. In my .cfg, the db_def. the code:
Code:
Remove => [19,'numer', 4, 5, 1, '14', ''],
and to define the selected fields:
Code:
Remove => '1,3,7,14,21,42',
,if thats what you mean. Your other question I do not understand, maybe more detailed???
Quote Reply
Re: delete records after a specific time? In reply to
mart,

Ah...That's what I was looking for, which was a way for Record Owners to specify the days by a drop-down menu or something rather than typing in the exact date format.

Thanks. I think I got it now. Smile

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us

[This message has been edited by Eliot (edited August 10, 1999).]
Quote Reply
Re: delete records after a specific time? In reply to
I have a question about how this mod works.
When I click on the Delete Old Records Link, I get the following:

Code:
Comparing: '934948800' vs '7' ...
Comparing: '934948800' vs '14' ...
Comparing: '934948800' vs '7' ...
Comparing: '934948800' vs '7' ...
Comparing: '934948800' vs '30' ...

Is this how it is supposed to work? It seems odd that it is comparing old sessions of users with number of days that the user has specified to show the Record.

I am using Alex's later codes that he posted in this forum.

Just wondering...If anyone can explain to me how this auto deletion works, I would greatly appreciate it.

Thanks.


------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us
Quote Reply
Re: delete records after a specific time? In reply to
The "Comparing" line was a debugging feature that Alex added to the code.

It's not comparing old sessions of users, but is comparing the current Unix date.





------------------
JPD





Quote Reply
Re: delete records after a specific time? In reply to
Ah...so, it is working fine??

Great!

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us
Quote Reply
Re: delete records after a specific time? In reply to
Quote:
How about:

code:


sub auto_delete {
# ------------------------------------------
# Automatically removes entries older then $remove
# days old.
#
my $remove = 30; # Number of days old.
my $date_field = 2; # Position of date field.
my $today = &date_to_unix(&get_date);
my $removeby = $today - ($remove * 86400);
my (@lines, @values);
open (DB, $db_file_name) or &cgierr ("Can't open: $db_file_name. Reason: $!");
if ($db_use_flock) { flock (DB, 1); }
@lines = <DB>;
close DB;
open (DB, ">$db_file_name") or &cgierr ("Can't open: $db_file_name. Reason: $!");
if ($db_use_flock) { flock (DB, 2); }
foreach (@lines) {
next if /^#/;
next if /^\s*$/;
chomp;
@values = &split_decode ($_);
if ($removeby > &date_to_unix($values[$date_field])) {
next;
}
print DB $_, "\n";
}
close DB;
}

[\quote]

Alex,
Your code works great- but I was wondering if there is a way to incorporate deleting the "expired" user's database record with also deleting their password from the password file as well. I have a database that has 1 record per user. I also have a field which contains their account status, either they are a "Guest" or a "Member". I would like to only "autodelete" those users with the "Guest" status.

Another question is that I changed the field that the user's record was created to the US date format: mm-dd-yyyy. Is there anything special I will need to do with the &date-to-unix section in order for this mod to work for me?

Any and all help would be very much appreciated! Thanks so much!

~SwanSong
Quote Reply
Re: delete records after a specific time? In reply to
This has been discussed in another Thread over in the DBMAN Discussion Forum. BTW: If you are looking for an automated stand alone auto deletion script, check out the Resource Center.

Regards,

------------------
Eliot Lee
Founder and Editor
Anthro TECH, L.L.C
http://www.anthrotech.com/
info@anthrotech.com
==========================
Coconino Community College
http://www.coco.cc.az.us/
Web Technology
Coordinator
elee@coco.cc.az.us
Quote Reply
Re: delete records after a specific time? In reply to
I would need more information about how the status of a user is determined and about what exactly you want to do. Also, I think maybe we should start a new thread for this. It's getting really long and confusing.

Yes, you will have to change sub date_to_unix to match your change in sub get_date.


------------------
JPD





Quote Reply
Re: delete records after a specific time? In reply to
JPDeni,
Thanks for the reply...To answer your question, the status of a user is determined by a field called "member_option" (data field #2 in my .cfg file). This field will contain only 1 of 2 things. Either "Guest" or "Member".

What I want to do is exactly what Alex's code (posted above) does but with the following modifications:

1. Only delete a user's database record who has expired after a set amount of days, (Alex uses 30 days), BUT ONLY if the member_option (data field #2) equals "Guest".

2. If the the member_option field = "Guest" and their account has past the expiration days, then also delete there password line from the password file.

3. I have a creation_date field (data field #5) of when a user's account has been created. I am using the US date formation so this field contains a date that is formated like this: mm-dd-yyyy. In order for this mod to work for me, I need to know what changes I need to make in the &date-to-unix sub routine.

That's it.

Again, I would really appreciate your time and help with this. If I left anything out that you need to help build this mod, please let me know. Thank you so much in advance! =)

Sincerely,

~Sublime
Quote Reply
Re: delete records after a specific time? In reply to
Let's see if we can work it out.

Code:
sub auto_delete {
# ------------------------------------------
# Automatically removes entries older then $remove
# days old.
#
my $remove = 30; # Number of days old.
my $date_field = 5; # Position of date field.
my $today = &date_to_unix(&get_date);
my $removeby = $today - ($remove * 86400);
my (@lines, @values);

open (DB, $db_file_name) or &cgierr ("Can't open: $db_file_name. Reason: $!");
if ($db_use_flock) { flock (DB, 1); }
@lines = <DB>;
close DB;

open (DB, ">$db_file_name") or &cgierr ("Can't open: $db_file_name. Reason: $!");
if ($db_use_flock) { flock (DB, 2); }
foreach (@lines) {
next if /^#/;
next if /^\s*$/;
chomp;
@values = &split_decode ($_);
if (($removeby > &date_to_unix($values[$date_field])) && ($values[2] eq 'Guest')) {
open (PASS, "<$auth_pw_file") or &cgierr ("unable to open: $auth_pw_file.\nReason: $!");
if ($db_use_flock) { flock(PASS, 1) }
@passlines = <PASS>;
close PASS;

open (PASS, ">$auth_pw_file") or &cgierr ("unable to open: $auth_pw_file.\nReason: $!");
if ($db_use_flock) {
flock(PASS, 2) or &cgierr("unable to get exclusive lock on $auth_pw_file.\nReason: $!");
}
foreach $passline (@passlines) {
($line =~ /^$values[$auth_user_field]:/) ?
($found = 1) :
print PASS $passline;
}
close PASS;
}
else {
print DB $_, "\n";
}
}
close DB;
}

sub date_to_unix {
# --------------------------------------------------------
# This routine must take your date format and return the time a la UNIX time().
# Some things to be careful about..
# int your values just in case to remove spaces, etc.
# catch the fatal error timelocal will generate if you have a bad date..
# don't forget that the month is indexed from 0!
#
my ($date) = $_[0];
my ($time);
my ($mon, $day, $year) = split(/-/, $_[0]);
unless ($day and $mon and $year) { return undef; }

use Time::Local;
eval {
$day = int($day); $year = int($year) - 1900;
$mon = int($mon) - 1;
$time = timelocal(0,0,0,$day, $mon, $year);
};
if ($@) { return undef; } # Could return 0 if you want.
return ($time);
}

This should do what you want. I did check it for syntax errors, but be sure that you back up your data before you run it, just in case there's a problem.


------------------
JPD





Quote Reply
Re: delete records after a specific time? In reply to
JPDeni,
Wow! Thanks for such a quick response! =)
After testing the above modifications, (thankfully backing up all my data), it deleted every record in my database and didn't touch anything in the password file. Strange- yet true. Hmmmm....Any insight?

~SwanSong
Quote Reply
Re: delete records after a specific time? In reply to
Did you replace sub date_to_unix?

Did you try Alex's code before, which didn't check for 'Guest' or remove the password file?



------------------
JPD





Quote Reply
Re: delete records after a specific time? In reply to
JPDeni,
Yes...I double checked and did replace the date-to-unix sub routine with what you gave me above. As far as testing Alex's code- I could have swarn it worked before. Reading back through this thread, I saw where you had said that Alex's piece of code (in it's original form) was the way to go.

I also double checked the data fields and they are also correct. I don't know if this makes a difference, but I don't auto-generate a key-id field, I use a field called "handle" as the key because no 2 people can have the same one.

Probably just shooting in the dark- but I figured any info might be helpful. Could you please double check the code you gave me.

I didn't mean this to become a big headache for you and I'm sorry. Thought it would be a fairly simple mod.

~SwanSong

P.S. Does it matter where I call the &auto_delete sub routine? Right now I'm calling it from just before the sub html_home in my html.pl file.
Quote Reply
Re: delete records after a specific time? In reply to
Here's my get_date and date_to_unix routines...Figured this could be helpful in debugging:

Code:
sub get_date {
# --------------------------------------------------------
# Returns the date in the format "mm-ddd-yyyy".
# Warning: If you change the default format, you must also modify the &date_to_unix
# subroutine below which converts your date format into a unix time in seconds for sorting
# purposes.
my ($time) = @_;
($time) &#0124; &#0124; ($time = time());
my ($sec, $min, $hour, $day, $mon, $year, $dweek, $dyear, $daylight) = localtime($time);
++$mon;
($mon < 10) and ($mon = "0$mon");
($day < 10) and ($day = "0$day");
$year = $year + 1900;

return "$mon-$day-$year";
}
sub date_to_unix {
# --------------------------------------------------------
# This routine must take your date format and return the time a la UNIX time().
# Some things to be careful about..
# int your values just in case to remove spaces, etc.
# catch the fatal error timelocal will generate if you have a bad date..
# don't forget that the month is indexed from 0!
#
my ($date) = $_[0];
my ($time);
my ($mon, $day, $year) = split(/-/, $_[0]);
unless ($day and $mon and $year) { return undef; }
use Time::Local;
eval {
$day = int($day); $year = int($year) - 1900;
$mon = int($mon) - 1;
$time = timelocal(0,0,0,$day, $mon, $year);
};
if ($@) { return undef; } # Could return 0 if you want.
return ($time);
}

Quote Reply
Re: delete records after a specific time? In reply to
Let's do one thing at a time.

First, do a search using the date field to make sure your get_date and date_to_unix are working okay.

If that seems to be all right, replace the code I gave you with the code that Alex wrote and check that. (Even if you've done it before, do it again, just to humor me. Smile )

If that all works correctly, then the problem is with my code. Seems to me like it should work, but I've been known to make a mistake from time to time. Smile


------------------
JPD





Quote Reply
Re: delete records after a specific time? In reply to
I just got an idea.

Change

Code:
foreach (@lines) {
next if /^#/;
next if /^\s*$/;
chomp;
@values = &split_decode ($_);

to

Code:
foreach $line (@lines) {
if ($line =~ /^#/) { next; }
if ($line =~ /^\s*$/) { next; }
chomp $line;
@values = &split_decode ($line);

also change

Code:
print DB $_, "\n";

to

Code:
print DB $line, "\n";

See if that works better.




------------------
JPD





Quote Reply
Re: delete records after a specific time? In reply to
Code:
sub auto_delete {
# ------------------------------------------
# Automatically removes entries older then $remove
# days old.
#
my $remove = 30; # Number of days old.
my $date_field = 5; # Position of date field.
my $today = &date_to_unix(&get_date);
my $removeby = $today - ($remove * 86400);
my (@lines, @values);

open (DB, $db_file_name) or &cgierr ("Can't open: $db_file_name. Reason: $!");
if ($db_use_flock) { flock (DB, 1); }
@lines = <DB>;
close DB;

open (DB, ">$db_file_name") or &cgierr ("Can't open: $db_file_name. Reason: $!");
if ($db_use_flock) { flock (DB, 2); }


foreach $line (@lines) {
if ($line =~ /^#/) { next; }
if ($line =~ /^\s*$/) { next; }
chomp $line;
@values = &split_decode ($line);

if (($removeby > &date_to_unix($values[$date_field])) && ($values[2] eq 'Guest')) {

&auth_logging("System Auto-Deleted Expired Guest: $values[3],$values[2]");
open (PASS, "<$auth_pw_file") or &cgierr ("unable to open: $auth_pw_file.\nReason: $!");
if ($db_use_flock) { flock(PASS, 1) }
@passlines = <PASS>;
close PASS;
open (PASS, ">$auth_pw_file") or &cgierr ("unable to open: $auth_pw_file.\nReason: $!");
if ($db_use_flock) {
flock(PASS, 2) or &cgierr("unable to get exclusive lock on $auth_pw_file.\nReason: $!");
}
foreach $passline (@passlines) {
($line =~ /^$values[$auth_user_field]:/) ?
($found = 1) :
print PASS $passline;
}
close PASS;
}
else {
print DB $line, "\n";
}
}
close DB;
}

JPDeni,
The above code now works (note that it incorporates your latest fixes). It deletes the database record of any "Guest" after 30 days, BUT- it does *not* delelete their password line from the password file. Please help! I promise not to bug you anymore once this issue is resolved. *GRIN*

Thanks so much again and again for your help and time, JP!! You are the bomb! =)

~SwanSong
Quote Reply
Re: delete records after a specific time? In reply to
Well, that's progress. Smile

You do have an $auth_user_field set in your .cfg file, right? And you are running the script from within DBMan and not as a separate script -- yes?





------------------
JPD





Quote Reply
Re: delete records after a specific time? In reply to
JPDeni,
Yes to both questions *GRIN*



------------------
~~~SwanSong
Quote Reply
Re: delete records after a specific time? In reply to
I found my error.

Instead of

Code:
($line =~ /^$values[$auth_user_field]:/) ?

use

Code:
($passline =~ /^$values[$auth_user_field]:/) ?

I forgot to change one of the variable names.


------------------
JPD





Quote Reply
Re: delete records after a specific time? In reply to
Thank you!! That did the trick! =)

(Sorry to take up so much of your time. Looks like I also managed to double the size of this thread. *L*)

JPDeni, you're awesome! I can't thank you enough...

~SwanSong
Quote Reply
Re: delete records after a specific time? In reply to
I'm just glad we finally got it worked out. Smile


------------------
JPD





> > > >