Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number
 
PerlMonks  

comment on
( [id://3333]=superdoc: print w/replies, xml ) Need Help?? 
#!/usr/local/bin/perl
### Database Manager v1.0 ###
### Author: Ben Kittrell  ###
### Created: 03/26/2001   ###

use strict;
use CGI;
use DBI;

my $q = CGI::new();
my $scriptname = $ENV{SCRIPT_NAME};
$scriptname =~ s/.*\/([^\/]*)$/$1/;
my $s = $q->param('_s');
my $database = $q->cookie('Database');
my $host = $q->cookie('Host');
my $driver = $q->cookie('Driver');
my $username = $q->cookie('Username');
my $password = $q->cookie('Password');

if ($s eq "connect") {
    if ($q->param('action') eq "Connect") { 
        $database = $q->param('Database');
        $host = $q->param('Host');
        $driver = $q->param('Driver');
        $username = $q->param('Username');
        $password = $q->param('Password');
    }
    my $dbh = DBI->connect("DBI:$driver:$database:$host", $username, $
+password) or ErrorOut($DBI::errstr);
    $dbh->disconnect;
    my $cookie1 = $q->cookie(-name=>'Database', -value=>$database, -ex
+pires=>'+3M');
    my $cookie2 = $q->cookie(-name=>'Host', -value=>$host, -expires=>'
++3M');
    my $cookie3 = $q->cookie(-name=>'Driver', -value=>$driver, -expire
+s=>'+3M');
    my $cookie4 = $q->cookie(-name=>'Username', -value=>$username, -ex
+pires=>'+3M');
    my $cookie5 = $q->cookie(-name=>'Password', -value=>$password, -ex
+pires=>'+3M');
    print $q->header(-cookie=>[$cookie1,$cookie2,$cookie3,$cookie4,$co
+okie5]);
    print &header("Connected");
    print "<ul><li><a href=\"$scriptname?_s=tables\">Show Tables</a></
+li>\n
    <li><a href=\"$scriptname?_s=createform\">Create Tables</a></li>\n
    <li><a href=\"$scriptname?_s=commandline\">Command Line</a></li>\n
    <li><a href=\"$scriptname?_s=logout\">Log Out</a></li></ul>";
    print &footer;
} elsif ($s eq "tables") {
    my $htmlout;
    my $table = $q->param('table');
    my $dbh = DBI->connect("DBI:$driver:$database:$host", $username, $
+password) or ErrorOut($DBI::errstr);
    if ($q->param('action') eq "drop") {
        $dbh->do("DROP TABLE $table");
    }
    my $sth = $dbh->prepare("SHOW TABLES");
    $sth->execute();
    while (my $results = $sth->fetchrow_hashref) {
        my $key = join '', keys %$results;
        my %results = %$results;
        if (!$htmlout) {
            $htmlout .= "<tr><th colspan=3>$key</th></tr>";
        }
        $htmlout .= "<tr><td><a href=\"$scriptname?_s=showtable&table=
+$results{$key}\">$results{$key}</a></td>
        <td><a href=\"$scriptname?_s=edit&table=$results{$key}\">edit<
+/a></td>
        <td><a href=\"$scriptname?_s=tables&table=$results{$key}&actio
+n=drop\">drop</a></td></tr>";
    }
    $sth->finish;
    print $q->header();
    print &header("Show Tables");
    print "<table border=1>$htmlout</table>";
    print "<p><a href=\"$scriptname?_s=connect\">Home</a></p>";
    print &footer;;
    $dbh->disconnect;
} elsif ($s eq "showtable") {
    my $dbh = DBI->connect("DBI:$driver:$database:$host", $username, $
+password) or die "Connect Error: $DBI::errstr";
    my $table = $q->param('table');
    my $sth = $dbh->prepare("SHOW FIELDS FROM $table");
    $sth->execute();
    my $htmlout = "<tr><th>Field</th><th>Type</th><th>Default</th><th>
+Null</th><th>Key</th><th>Extra</th></tr>\n";
    while (my $results = $sth->fetchrow_hashref) {
        my %results = %$results;
        $htmlout .= "<tr><td>$results{'Field'}&nbsp;</td><td>$results{
+'Type'}&nbsp;</td>
        <td>$results{'Default'}&nbsp;</td><td>$results{'Null'}&nbsp;</
+td>
        <td>$results{'Key'}&nbsp;</td><td>$results{'Extra'}&nbsp;</td>
+</tr>\n";
    }
    $sth->finish;
    print $q->header();
    print &header("$table");
    print "<table border=1>$htmlout</table>";
    print "<a href=\"$scriptname?_s=showvalues&table=$table\">See the 
+Values</a>";
    print "<p><a href=\"$scriptname?_s=connect\">Home</a></p>";
    print &footer;
    $dbh->disconnect
} elsif ($s eq "showvalues") {
    my $dbh = DBI->connect("DBI:$driver:$database:$host", $username, $
+password) or die "Connect Error: $DBI::errstr";
    my $htmlout;
    my $table = $q->param('table');
    my $sth = $dbh->prepare("SELECT * FROM $table");
    $sth->execute();
    while (my $results = $sth->fetchrow_hashref) {
        my %results = %$results;
        if (!$htmlout) {
            $htmlout = "<tr>";
            foreach my $key (keys %results) {
                $htmlout .= "<th>$key</th>";
            }
            $htmlout .= "</tr>\n";
        }
        $htmlout .= "<tr>";
        foreach my $key (keys %results) {
            $htmlout .= "<td>$results{$key}&nbsp;</td>";
        }
        $htmlout .= "</tr>\n";
    }
    $sth->finish;
    print $q->header();
    print &header("Values");
    print "<table border=1>$htmlout</table>";
    print "<p><a href=\"$scriptname?_s=connect\">Home</a></p>";
    print &footer;
    $dbh->disconnect
} elsif ($s eq "logout") {
    my $cookie1 = $q->cookie(-name=>'Database', -value=>'', -expires=>
+'now');
    my $cookie2 = $q->cookie(-name=>'Host', -value=>'', -expires=>'now
+');
    my $cookie3 = $q->cookie(-name=>'Driver', -value=>'', -expires=>'n
+ow');
    my $cookie4 = $q->cookie(-name=>'Username', -value=>'', -expires=>
+'now');
    my $cookie5 = $q->cookie(-name=>'Password', -value=>'', -expires=>
+'now');
    print $q->header(-cookie=>[$cookie1,$cookie2,$cookie3,$cookie4,$co
+okie5]);
    print &header("Log out");
    print "Logged out";
    print &footer;
} elsif ($s eq "edit") {
    my $dbh = DBI->connect("DBI:$driver:$database:$host", $username, $
+password) or die "Connect Error: $DBI::errstr";
    my $table = $q->param('table');
    my $field = $q->param('field');
    if ($q->param('action') eq "drop") {
        $dbh->do("ALTER TABLE $table DROP $field");
    }
    my $adderr;
    if  ($q->param('action') eq "add") {
        my $sqlstr = "ALTER TABLE $table ADD " . $q->param('name') . "
+ " . $q->param('type');
        $sqlstr .= " DEFAULT '" . $q->param('default') . "'" if $q->pa
+ram('default');
        $sqlstr .= " not null" unless ($q->param('notnull') eq "yes");
        $dbh->do($sqlstr);
        $adderr = $dbh->errstr;
    }
    if  ($q->param('action') eq "modify") {
        my $sqlstr = "ALTER TABLE $table CHANGE " . $q->param('field')
+ . " "  . $q->param('name') . " " . $q->param('type');
        $sqlstr .= " DEFAULT '" . $q->param('default') . "'" if $q->pa
+ram('default');
        $sqlstr .= " not null" unless ($q->param('notnull') eq "yes");
        $dbh->do($sqlstr);
        $adderr = $dbh->errstr;
    }
    if  ($q->param('action') eq "Privilegize") {
        my $privs = join ", ", $q->param('privileges');
        my $sqlStr = $q->param('grchoice');
        $sqlStr .= " ALL PRIVILEGES" if $q->param('all');
        $sqlStr .= " $privs" if $privs && !$q->param('all');
        $sqlStr .= ", " if ($q->param('grant') && ($privs || $q->param
+('all')) && ($q->param('grchoice') eq "REVOKE"));
        $sqlStr .= " GRANT OPTION" if ($q->param('grant') && $q->param
+('grchoice') eq "REVOKE");
        $sqlStr .= " ON $table ";
        $sqlStr .= "TO " . $q->param('gusername') if ($q->param('grcho
+ice') eq "GRANT");
        $sqlStr .= "FROM " . $q->param('gusername') if ($q->param('grc
+hoice') eq "REVOKE");
        $sqlStr .= " IDENTIFIED BY '" . $q->param('gpassword') . "'" i
+f ($q->param('gpassword') && $q->param('grchoice') eq "GRANT");
        $sqlStr .= " WITH GRANT OPTION" if ($q->param('grant') && $q->
+param('grchoice') eq "GRANT");
        $dbh->do($sqlStr);
        $adderr = $dbh->errstr;
    }
    my $sth = $dbh->prepare("SHOW FIELDS FROM $table");
    $sth->execute();
    my $htmlout = "<tr><th>Field</th><th>Type/Size</th><th>Default</th
+><th>Allow Null</th><th>Key</th><th>Extra</th><th>&nbsp;</th><th>&nbs
+p;</th></tr>\n";
    while (my $results = $sth->fetchrow_hashref) {
        my %results = %$results;
        my $notnull = "checked" if ($results{'Null'} eq "YES");
        $htmlout .= "<form action=\"$scriptname\" method=\"POST\"><tr>
+<td><input type=text name=name value=\"$results{'Field'}\" size=15>&n
+bsp;</td>
        <td><input type=text name=type value=\"$results{'Type'}\" size
+=15>&nbsp;</td>
        <td><input type=text name=default value=\"$results{'Default'}\
+" size=10>&nbsp;</td>
        <td><input type=checkbox name=notnull value=yes $notnull>&nbsp
+;</td>
        <td>$results{'Key'}&nbsp;</td><td>$results{'Extra'}&nbsp;</td>
        <td><a href=\"$scriptname?_s=edit&table=$table&field=$results{
+'Field'}&action=drop\">drop</a></td>
        <td><input type=submit name=action value=modify><input type=hi
+dden name=table value=$table>
        <input type=hidden name=field value=\"$results{'Field'}\"><inp
+ut type=hidden name=\"_s\" value=edit></td></tr></form>\n";
    }
    $htmlout .= "<form action=\"$scriptname\" method=\"POST\"><tr><td>
+<input type=text name=name size=15></td><td><input type=text name=typ
+e size=15></td>
    <td><input type=text name=default size=10></td><td><input type=che
+ckbox name=notnull value=yes></td>
    <td>&nbsp;</td><td>&nbsp;</td><td>&nbsp;</td>
    <td><input type=submit name=action value=add><input type=hidden na
+me=table value=$table><input type=hidden name=\"_s\" value=edit></td>
+</tr></form>";
    $htmlout = "<table border=1>$htmlout</table>";
    $htmlout .= "<br><b>Grant/Revoke Privileges:</b><br><form action=\
+"$scriptname\" method=\"POST\"><table border=1>
    <tr><td colspan=3 align=center><select name=\"grchoice\"><option v
+alue=\"GRANT\">GRANT</option><option value=\"REVOKE\">REVOKE</option>
+</select></td></tr>\n
    <tr><td colspan=2>ALL</td><td><input type=\"checkbox\" name=\"all\
+" value=\"Yes\"></td></tr><tr>\n
    <tr><td colspan=2>SELECT</td><td><input type=\"checkbox\" name=\"p
+rivileges\" value=\"SELECT\"></td></tr>\n
    <tr><td colspan=2>INSERT</td><td><input type=\"checkbox\" name=\"p
+rivileges\" value=\"INSERT\"></td></tr>\n
    <tr><td colspan=2>UPDATE</td><td><input type=\"checkbox\" name=\"p
+rivileges\" value=\"UPDATE\"></td></tr>\n
    <tr><td colspan=2>DELETE</td><td><input type=\"checkbox\" name=\"p
+rivileges\" value=\"DELETE\"></td></tr>\n
    <tr><td colspan=2>CREATE</td><td><input type=\"checkbox\" name=\"p
+rivileges\" value=\"CREATE\"></td></tr>\n
    <tr><td colspan=2>DROP</td><td><input type=\"checkbox\" name=\"pri
+vileges\" value=\"DROP\"></td></tr>\n
    <tr><td colspan=2>ALTER</td><td><input type=\"checkbox\" name=\"pr
+ivileges\" value=\"ALTER\"></td></tr>\n
    <tr><td colspan=2>INDEX</td><td><input type=\"checkbox\" name=\"pr
+ivileges\" value=\"INDEX\"></td></tr>\n
    <tr><td colspan=2>GRANT</td><td><input type=\"checkbox\" name=\"gr
+ant\" value=\"Yes\"></td></tr>\n
    <tr><td>User\@Host:</td><td colspan=2><input type=\"text\" name=\"
+gusername\" size=10></td></tr>\n
    <tr><td>Password:</td><td colspan=2><input type=\"password\" name=
+\"gpassword\" size=10></td></tr>\n
    <tr><td colspan=3 align=center><input type=\"submit\" name=\"actio
+n\" value=\"Privilegize\"></td></tr>\n
    </table><input type=hidden name=\"_s\" value=edit><input type=hidd
+en name=\"table\" value=\"$table\"></form>";
    $sth->finish;
    print $q->header();
    print &header("$table");
    print $adderr;
    print "$htmlout";
    print "<a href=\"$scriptname?_s=showvalues&table=$table\">See the 
+Values</a>";
    print "<p><a href=\"$scriptname?_s=connect\">Home</a></p>";
    print &footer;
    $dbh->disconnect
} elsif ($s eq "createform") {
    my $htmlout;
    if ($q->param('_action') eq "Finished") {
        my $tablename = $q->param('tablename');
        my $sqlStr;
        my $primary;
        my @params = $q->param();
        foreach my $param (@params) {
            if ($q->param($param) && $param =~ s/^fieldname(\d*)$/$1/)
+ {
                $sqlStr .= ", " if $sqlStr;
                $sqlStr .= $q->param("fieldname$param") . " " . $q->pa
+ram("fieldtype$param");
                $sqlStr .= " DEFAULT '" . $q->param("fielddefault$para
+m") . "'" if $q->param("fielddefault$param");
                $sqlStr .= " NOT NULL" unless ($q->param("allownull$pa
+ram") eq "Yes");
                $sqlStr .= " " . $q->param("extra$param") if $q->param
+("extra$param");
                $primary .= ", " if $primary && $q->param("primary$par
+am");
                $primary .= $q->param("fieldname$param") if $q->param(
+"primary$param");
            }
        }
        $primary = ", PRIMARY KEY($primary)" if $primary;
        my $queryStr = "CREATE TABLE $tablename ($sqlStr$primary)";
        my $dbh = DBI->connect("DBI:$driver:$database:$host", $usernam
+e, $password) or die "Connect Error: $DBI::errstr";
        $dbh->do($queryStr);
        my $createerr = $dbh->errstr;
        $dbh->disconnect;
        if ($createerr) {
            $htmlout = "$createerr\n$queryStr";
        } else {
            $htmlout = "Table Created";
        }
    } else {
        my $start = $q->param('_start') || 1;
        $htmlout = "<form action=\"$scriptname\" method=\"POST\">\n";
        my $end = $start + 5;
        my @params = $q->param();
        if ($q->param('_action') eq "Add More Fields") {
            foreach my $param (@params) {
                if ($param !~ /^_/ && $q->param($param)) { 
                    $htmlout .= "<input type=\"hidden\" name=\"$param\
+" value=\"" . $q->param($param) . "\">\n";
                }
            }
        } else {
            $htmlout .= "Table Name:<input type=\"text\" name=\"tablen
+ame\">";
        }
        $htmlout .= "<table border=1><tr><th>Field</th><th>Type/Size</
+th><th>Default</th><th>Allow Null</th><th>Key</th><th>Extra</th></tr>
+\n";
        for (my $i=$start; $i<$end; $i++) {
            $htmlout .= "<tr><td><input type=\"text\" name=\"fieldname
+$i\"></td>
            <td><input type=\"text\" name=\"fieldtype$i\"></td>
            <td><input type=\"text\" name=\"fielddefault$i\"></td>
            <td><input type=\"checkbox\" name=\"allownull$i\" value=\"
+Yes\"></td>
            <td><input type=\"checkbox\" name=\"primary$i\" value=\"Ye
+s\"></td>
            <td><input type=\"text\" name=\"extra$i\"></td></tr>\n";
        }
        $htmlout .= "</table><input type=\"hidden\" name=\"_start\" va
+lue=\"$end\">
        <input type=\"hidden\" name=\"_s\" value=\"createform\">
        <input type=\"submit\" name=\"_action\" value=\"Add More Field
+s\">
        <input type=\"submit\" name=\"_action\" value=\"Finished\"></f
+orm>\n";
    }
    print $q->header();
    print &header("Create Table");
    print $htmlout;
    print "<p><a href=\"dbman.mpl?_s=connect\">Home</a></p>";
    print &footer;
} elsif ($s eq "commandline") {
    my $htmlout;
    my $err;
    if ($q->param('action') eq "Submit") {
        my $dbh = DBI->connect("DBI:$driver:$database:$host", $usernam
+e, $password) or die "Connect Error: $DBI::errstr";
        if ($q->param('query') =~ /^SELECT/i) {
            my $sth = $dbh->prepare($q->param('query'));
            $sth->execute();
            $err = $sth->errstr;
            while (my $results = $sth->fetchrow_hashref) {
                my %results = %$results;
                if (!$htmlout) {
                    $htmlout = "<tr>";
                    foreach my $key (keys %results) {
                        $htmlout .= "<th>$key</th>";
                    }
                    $htmlout .= "</tr>\n";
                }
                $htmlout .= "<tr>";
                foreach my $key (keys %results) {
                    $htmlout .= "<td>$results{$key}&nbsp;</td>";
                }
                $htmlout .= "</tr>\n";
            }
            $sth->finish;
            $htmlout = "<table border=1>$htmlout</table>";
        } else {
            my $sth = $dbh->prepare($q->param('query'));
            $sth->execute();
            $err = $sth->errstr;
            $htmlout = $sth->rows . " rows affected";
            $sth->finish;
        }
        $dbh->disconnect;
    }
    print $q->header();
    print &header("Command Line");
    if ($err) {
        print "$err<br>";
    } elsif ($htmlout) {
        print "$htmlout<br>";
    }
    print <<HTMLOUT;
    <form action="$scriptname" method="POST">
    <input type="hidden" name="_s" value="commandline">
    <input type="text" name="query" size="40">
    <input type="submit" name="action" value="Submit">
    </form>
HTMLOUT
    print "<p><a href=\"dbman.mpl?_s=connect\">Home</a></p>";
    print &footer;
} else {
    print $q->header();
    print &header("Login");
    print <<HTMLOUT;
    <form action="$scriptname" method="POST">
    <input type="hidden" name="_s" value="connect">
    <table>
      <tr><td align="right">Database:</td>
      <td><input type="text" name="Database" value="$database"><br></t
+d></tr>
      <tr><td align="right">Host:</td>
      <td><input type="text" name="Host" value="$host"><br></td></tr>
      <tr><td align="right">Driver:</td>
      <td><input type="text" name="Driver" value="$driver"><br></td></
+tr>
      <tr><td align="right">Username:</td>        
      <td><input type="text" name="Username" value="$username"><br></t
+d></tr>
      <tr><td align="right">Password:</td>
      <td><input type="password" name="Password" value="$password"><br
+></td></tr>
      <tr><td colspan="2" align="center"><input type="submit" name="ac
+tion" value="Connect"> 
      <input type="reset" value="Reset"></td></tr>
    </table>
    </form>
HTMLOUT
    print &footer;
}    

sub header {
    my $output = <<HTMLOUT;
    <html><head><title>$_[0]</title></head>
    <body>
    <h1>DB Manager</h1>
HTMLOUT
    return $output;
}

sub footer {
    my $output = <<HTMLOUT;
    <p align="right"><a href="http://benk.hypermart.net/kaos">thabenks
+ta</a></p>
    </body></html>
HTMLOUT
    return $output;
}

sub ErrorOut {
    print $q->header();
    print "<h1>Error:</h1><p>$_[0]</p>";
    exit;
}

[download]

In reply to DB Mangager by thabenksta

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":


  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others goofing around in the Monastery: (5)
As of 2024-04-18 06:23 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found