drop table

[ace_gene]

I need to put user's input file in a temporary table in the database, and I would like to drop the table after 24hours. Is there a way that I can do this automatically? Thanks!

[raymie]

I assume that you want this done without having to be there to do it...

The answer would be a cron job or if using windows a scheduled task.

[ace_gene]

If it's just a file, I am thinking that I can write a script in the first page of the service, so each time the page is open, the files older than 24 hours in the upload folder will be deleted. Is there a similar way for dropping all tables that are older than 24hours?

[raymie]

If I was writing the function I would probably have a table which contains the table names created as temp tables with the timestamp.

I would the select from this table using the timestamp to get those greater than 1 day old and use the row data to build the drop commands.

Personally however I would implement the deletion via a cron tab (written in PERL) and schedule it to run every so often on the server so that I did not need someone to go to the site in order to have the tables dropped.

Why complicate the online side of the site when you can control these things using the server.

[ace_gene]

Why complicate the online side of the site when you can control these things using the server. [/B]

This is absolutly right. But I have no idea about cron job. Could you please explain how can I do this in more detail? Thanks!

[raymie]

What webserver do you use, if it is apache under unix then it is a simple matter.

You first need to check with your host to make sure that they allow you to use cron jobs.

If you confirm that and then also check that you can use perl scripts then let me know and I will write the script for you to check a tables contents as a sample.

I will explain further about cron jobs once you confirm that this is the route you can take.

[ace_gene]

I am using apache under linux. I have the root previledge so I can do anything I want.
I am looking forward to your script. Many thanks!

Originally posted by raymie
What webserver do you use, if it is apache under unix then it is a simple matter.

You first need to check with your host to make sure that they allow you to use cron jobs.

If you confirm that and then also check that you can use perl scripts then let me know and I will write the script for you to check a tables contents as a sample.

I will explain further about cron jobs once you confirm that this is the route you can take.

[raymie]

This is a cut and paste from a working script so hopefully it will still work for you. Not saying that it is brilliant code but it is from the simplest perl crontab I have.

#!/usr/bin/perl -w
use CGI qw(param);
use DBI;


$db="xxxxxxxxxt";

# Connect to the database region.
&dbstartup;

$query = "SELECT a.seq, a.peer, a.username, a.domain, a.page, a.pagename, a.lastread, a.lasttstamp, b.email, b.emailfrom  FROM web a, user b where a.username = b.username and a.active = 'Y'";
&dbquerystart;
 while ($row = $sth->fetchrow_hashref)
  {
        $wuser = $row->{'username'};
        $wemail = $row->{'email'};
        $wemailfrom = $row->{'emailfrom'};
        $wpeer = $row->{'peer'};
        $wdomain = $row->{'domain'};
        $wpage = $row->{'page'};
        $wpagename = $row->{'pagename'};
        $wlastread = $row->{'lastread'};
        $wseq = $row->{'seq'};
        &process_row;
 }
&dbqueryend;



#Closedown the database connection
&dbshutdown;


exit(0);

sub process_row {


.... drop the table stuff would go in here ie

$query2 = "DROP ............";
&dbquerystart2;
&dbqueryend2;

 return 1; 
}


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

sub dbstartup {

# Connect to the database region.
 $sock = "/tmp/mysql.xxxxxxx";
 $user = $db;
 $pass = $db;
 $dsn = "DBI:mysql:$db;mysql_socket=$sock";
 $dbh = DBI->connect($dsn,$user,$pass);

 return 1; 
}


sub dbshutdown {

 $dbh->disconnect();

  return 1; 
}


# Startup and SQL statement (stored in $query) and get the rowcount
sub dbquerystart {

 $sth = $dbh->prepare($query);
 $sth->execute;
 $rcount = $sth->rows;
 return 1; 
}

# close off the SQL statement last executed
sub dbqueryend {

$sth->finish();
 return 1; 
}


# Startup and SQL statement (stored in $query) and get the rowcount
sub dbquerystart2 {

 $sth2 = $dbh->prepare($query2);
 $sth2->execute;
 $rcount2 = $sth2->rows;
 return 1; 
}

# close off the SQL statement last executed
sub dbqueryend2 {

$sth2->finish();
 return 1; 
}

To setup the crontab you want to be logged in as the user you want to use (for simiplicity) and have a file containing the details of what you want to run and when (see man crontan)

ie.

0,15,30,45 1-23/1 * /home/xxxx/mindit.pl >>/home/xxxx/weblog 2>>/home/xxxx/weblog2

this would run a script every 15 minutes every day while the hour is between 1-23 and sticks the output in the mentioned files.

Save the file for example as cronfile and then enter the command crontab cronfile when logged in as the user.