Comparison script before creating table?

[dbrimlow]

I don't know if this can be done.

I have data dumps that are uploaded via a 3rd party. They upload the .psv file into a folder a few times daily similar to this:
>>/usr/home/master/usr/home/somefolder/somedatadump.psv<<

I have a crontab that runs a few times daily to open mysql and run a command .sql file similar to this:

>>* * * * /usr/home/master/usr/local/bin/mysql -u uname -p pword < /usr/home/master/command/command.sql 1> /dev/null<<

The command.sql file is basic like this:

>>use somedatabase;

drop table if exists sometable;

CREATE TABLE sometable (
numb int(10) unsigned NOT NULL default '0',
ai varchar(15) default NULL,
etc char(3) default NULL,
and_so_on char(3) default NULL,
PRIMARY KEY (numb)
);

load data infile "/usr/home/master/usr/home/somefolder/somedatadump.psv" into table sometable fields terminated by "|";<<

Okay. This works fine. The problem is, @ twice a year the 3rd party "file system" gets full; when this happens it uploads blank "somedatadump.psv" files. This obviously drops the old table and creates it anew with no data.

I would like to be able to check the "somedatadump.psv" file to be sure it has data before dropping and recreating the table.

I don't know if I can do a comparison in the beginning of the "command.sql" file, something like this:

>>
if "/usr/home/master/usr/home/somefolder/somedatadump.psv" NOT NULL ;

use somedatabase;

drop table if exists sometable;

CREATE TABLE sometable (
numb int(10) unsigned NOT NULL default '0',
ai varchar(15) default NULL,
etc char(3) default NULL,
and_so_on char(3) default NULL,
PRIMARY KEY (numb)
);

load data infile "/usr/home/master/usr/home/somefolder/somedatadump.psv" into table sometable fields terminated by "|";<<

Not sure what syntax applies or if "else" is required.

Thanks,

Dave

[wickning1]

I've never done any SQL scripting, so hopefully someone else can provide a better answer.

However, you could always wrap it with another scripting engine like BASH or Perl, which probably provide a lot more flexibility. Then you just use your cron job to run the (for example) Perl script.

[dbrimlow]

[!--quoteo(post=359706:date=Mar 29 2006, 11:48 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 29 2006, 11:48 AM) [snapback]359706[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I've never done any SQL scripting, so hopefully someone else can provide a better answer.

However, you could always wrap it with another scripting engine like BASH or Perl, which probably provide a lot more flexibility. Then you just use your cron job to run the (for example) Perl script.
[/quote]

I don't understand. Do you mean to use a script language to actually perform the whole process?

Hmmm. Interesting.

Like, say, a php page that would perform the whole task instead of the mysql itself using the command.sql text.

It would check the .psv file for data by inserting it into a test table, running a simple query and if field "numb" is NOT null, drop the test table and UPDATE (instead of re-creating) the "sometable" in the DB. The crontab would "submit" the php page at specified times during the day.

This has potential. And I will think about the pros and cons as well as the proper logic.

I would still, however, like to know if the SQL sripting could work.

Thanks, wickning1. I never did mosey down that particular Avenue.

Dave

[fenway]

Even if there was a way to do this -- which there isn't, AFAIK -- MySQL syntax doesn't really support control structures without stored procedures, so you wouldn't be able to abort it anyway. Either go with a middleware script that runs file system checks, or have the CRON tell you what happened; you can always COUNT() the number of rows in the table you just made, and get back this info.

[wickning1]

I was thinking something like this (Perl).

[code]#!/usr/bin/perl
if (length(`cat /usr/home/master/usr/home/somefolder/somedatadump.psv`) > 0) {

print `/usr/home/master/usr/local/bin/mysql -u uname -p pword < /usr/home/master/command/command.sql`;

}
[/code]

[fenway]

Alternatively, you simply use the "-z" file test, which succeeds if the file exists and has zero size:

[code]#!/usr/bin/perl
print `/usr/home/master/usr/local/bin/mysql -u uname -p pword < /usr/home/master/command/command.sql` unless -z '/usr/home/master/usr/home/somefolder/somedatadump.psv';
[/code]

[dbrimlow]

After leaving here to run through the logic, I realized that php would not even work (how would I have the cron actually "run" the php page?).

The perl looks interesting. And it reminded me of a similar crontab command I had used a while ago to actually test for data after loading to a temp db table. But that was very flakey.

So, the perl script 1 (wickning1) would only run the command.sql if the psv file was greater than zero (which is what I need, since the psv file either has something in it or litterally nothing; and no row would have less than 0 anyway.

Script 2 (Fenway) using the "unless -z" would also only run if the psv file is greater than zero.

They both test for the same condition before allowing the command.sql to run.

So, I would have this command say, "pipecheck.cgi", and simply have the crontab run it:

* * * * /usr/bin/perl/pipecheck.cgi

I haven't used perl/cgi in over 7 or 8 years. And even then I didn't code from scratch (the danger of pre-written scripts).

Dave

[wickning1]

Yeah, you've got the right idea. The code is untested but either mine or fenway's example should work without modification.

I usually name perl scripts .pl if they are to be called from the command line, .cgi only if they'll be serving a web page.. but it doesn't really matter.

[fenway]

I have stumbled on some ways to run PHP from a CRON job, but don't bother -- stick with Perl.

[dbrimlow]

I finally got to revisitiing this on my "list of things to do".

It looks to me like wickning1's initial idea of using "bash" is the solution.

Anyone familiar with bash syntax? Would this work:

(I would have cron run this as "pipefilechk.sh")

[code]#!/bin/bash

if [-s /usr/home/master/usr/home/somefolder/somedatadump.psv]
then

/usr/home/master/usr/local/bin/mysql -u uname -p pword < /usr/home/master/command/command.sql[/code]

[fenway]

Looks fine to me.... that must have been a long to-do list if it took 9 months.

[dbrimlow]

[quote author=fenway link=topic=89634.msg493817#msg493817 date=1167511421]
Looks fine to me.... that must have been a long to-do list if it took 9 months.
[/quote]

LOL, yeah built and set-up our intranet server (SuSe 10.1), revised 60% of a 2,000 page website from tables and markup style to css, built three interactive websites with custom management forms, and have been trying to see what needs to be done to convert hundreds of pages made with php 3 & 4.0.3 to php 5 (all the security functions and non-backwards compatibility) as well as learn new security standards for the latest Free BSD, Apache and MySQL before upgrading our web server.

Oh, and I spent WAYYY too much time trying fighting to make a floating (flexible) CSS layout work in IE 5.x and 5.5 only to find that it was blowing up in 800 x 600 res in most platforms anyway.

I figurerd I may as well pick some of the easier stuff from the list, now. This year burnt me out.

[fenway]

Good thing the year is over in a few hours ;-)

[artacus]

[quote]
After leaving here to run through the logic, I realized that php would not even work (how would I have the cron actually "run" the php page?).[/quote]

I don't know how well versed you are in perl, but if you run into a problem like this again, its quite easy to do this with PHP. You probably already have PHP CLI (command line interface) installed, you use that to call a script directly from cron. If you don't like that approach, you can schedule a cron job to use wget to request your script.

[fenway]

Everything's easier in Perl.

[dbrimlow]

!@$#%&

I'm getting an error that I simply cannot figure out. Here is the bash ... the syntax seems right to me (according to all the resource help files I read):

Using SuSe 10.1, the shell script checks that the PSV file has content (greater than zero - hence "-s") then loads the sql text command into MySQL. The syntax seems correct, but I get an error that there is an unrecognized "token" before "fi".

The file is named pipefilechk.sh and it is in the /bin/ folder. I try to call it in command line with "exec pipefilechk.sh".

I also have it in a crontab, but it is not working either way.

*******************
#!/bin/bash

if [ -s /usr/local/src/blist.psv ]
then
/usr/bin/mysql -u someuser -p somepassword < /usr/local/src/blist.sql
fi
********************

When I just type the "then" command directly into command line, it works; so we know this isn't causing the error.

The fact that it says there is an unrecognized "token".

Anyone know BASH enough to see why it is failing?

[effigy]

The syntax looks correct and works OK for me. Is there more to the code? What editor are you using?

[dbrimlow]

No, that's the whole code.

I'm using gedit, then in a konsole monitor to run it.

I also tried calling it in a crontab.