help with array, while statement

[netrookie]

Newbie here. I'm not sure if I'm supposed to use an array or not. I was able to run a system command that listed out table names in mysql (command line). For each table name given, I want to print out a statement. So far I can execute the system call, and it spits out the list, but that's it. I can't get it to run in the loop to print out the statement. I also just want the table name. My regex maybe wrong too. I know I'm missing something fairly obvious or not using array correctly. Also, I would like this to output to a file that I can execute. If you can me started with that too. Thanks.

#!/usr/bin/perl -w

use strict;

my $manual = qq(mysql -u root db2 -ss -e "show tables");
my $run = system $manual;
my @list = $run;

while (<@list>) {
chomp;
if (/^[^\W\d_]+$/) { # look for any tablename
print "Create table $_ as select * from $_ limit 10\n";
} else {
print "$_: line noise\n";
}
};

 

[travs69]

for (@list)

also this

my $manual = qq(mysql -u root db2 -ss -e "show tables");
my $run = system $manual;
my @list = $run;

can be change to

my @list = qx(mysql -u root db2 -ss -e "show tables");

not tested.. but i'm pretty sure

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[netrookie]

How do I print out the list? using $_? Should I just remove the if condition?

#!/usr/bin/perl -w

use strict;

my @list = qx(mysql -u root db2 -ss -e "show tables");

for (@list)
#chomp;
#if (/^[^\W\d_]+$/) { #any
print "Create table $_ as select * from $_ limit 10\n";
#} else {
# print "$_: line noise\n";
# }
#};

 

[travs69]

Show us what output you are getting with just the print statement.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[netrookie]

Ok, I got to give me some output. Now, I need to work on my pattern match. In my list of tables, some have an underscore and are tagged as line noise. I need to fix that. Can you suggest a better pattern match? Thanks.

Here's the code thus far:

#!/usr/bin/perl -w

use strict;

my @list = qx(mysql -u root db2 -ss -e "show tables");

for (@list) {
#while (<>) {
chomp;
if (/^[^\W\d_]+$/) { # look for any tablename
print "Create table $_ as select * from $_ limit 10\n";
} else {
print "$_: line noise\n";
}
};

Sample output:
Create table Heartbeat as select * from Heartbeat limit 10
alert_reason_1: line noise
alert_reason_2: line noise

 

[netrookie]

Almost there...

I changed this:
if (/^[^\W\d_]+$/) { # look for any tablename

to this:
if (/^[^\W\d]+$/) { # look for any tablename

**basically removed "_"

Now, I have an instance of a tablename that has 2 or more. Any ideas? Thanks again for your help.

Ex.
Create table url_vote_stats as select * from url_vote_stats limit 10
url_vote_stats_0: line noise

 

[travs69]

yeah.. get rid of your else {} cause you don't need it.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[netrookie]

Any ideas on how to fix my pattern matching? I can grab the name with (1) "_", but in the case of table name with more than one, it won't take it. thanks.

 

[travs69]

why don't you show us what your input looks like? Cause it's really hard to help you when we only get partial bits of data.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[blackhawkdoc64]

If you want to play around with regular expressions I have this code that you can use. Just plug in the regex and run it.

#!/usr/bin/perl

use strict;
use warnings;
use diagnostics;

while (<>) {
	chomp;
	if (/[red]regex here[/red]/) {
		print "Matched: |$`<$&>$'|\n";
	} else {
		print "No match: |$_|\n";
	}
}

That's what I use to fine tune my regular expresions. After you run it type something and it will let you know what part if any matched by putting the matching part in the < >.

 

[netrookie]

Here is output from running,
mysql -u root db2 -ss -e "show tables" on command line.

Heartbeat
alert_reason_1
alert_reason_2
alert_sources
alerts
cluster_rules
dns_info
dns_rules
domain_classification
domain_messages
domain_names
domain_rank
domain_reputation
full_urls
history
hosts
ip_range_classification
ip_range_reputation
ip_ranges
location_rules
mailer_rules
mailers
messages
ob_domain_names_history
ob_ip_ranges_history
ob_mailers_history
page_rules
rules
sources
ts_config
url_attributes
url_html
url_vote_stats
url_vote_stats_0
url_vote_stats_1
url_vote_stats_2
url_vote_stats_3
url_vote_stats_4
url_vote_stats_5
url_vote_stats_6
validat

 

[travs69]

i don't understand why your using a regex at all? Thos are all tables. Do you only want the tables with _'s in them or soemthing?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[netrookie]

I want the whole list. I was able in mysql command line to just export the list, removing headers, etc. That's what I need. I wanted to used the perl to generate the "create statement" for all tables listed. That's all. I was just confused how to use that output as input to generate the text that I wanted. What should I change?

 

[travs69]

from the looks of it you only need

#!/usr/bin/perl
use warnings;
use strict;

my @list = qx(mysql -u root db2 -ss -e "show tables");

for (@list) {
   chomp;
   print "Create table $_ as select * from $_ limit 10\n";
};

and not that I'm a great SQL guy or anything.. but why the limit 10 on the create command? I thought that was only for select statements to limit the return data?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[netrookie]

Thanks for all your help guys.
Goal in this was to create a subset of data. I still need to iron it out. I'm testing some mysql upgrades and instead of dumping all data which is a bigger mysql dump, I'd rather create another database with same tables and some data and then use that as my dump.

 

[travs69]

Good luck. Shouldn't be to hard. I know you can do insert commands with select from other tables. insert into db1:table1 select data from db2:table1 limit 10.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;