Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid
 
PerlMonks  

DB Mangager

by thabenksta (Pilgrim)
on Apr 03, 2001 at 01:48 UTC ( #69132=sourcecode: print w/ replies, xml ) Need Help??

Category: Web Stuff
Author/Contact Info Ben Kittrell ben@bensoft.com benk.hypermart.net/kaos
Description:

This program is a web based GUI for your database. It has currently only been tested for MySql, but the idea is for it to work with any DB.

The program give you a list of tables and allows you to Create, Edit and Drop them, as well as viewing their schema and data. It also provides a command line for other functions

Feel free to give me feedback/critisim. If you make any major additions, please let me know.

Update 4/3/2001:
Added privilege granting and revoking funtions.

#!/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;
}

Comment on DB Mangager
Download Code
Re: DB Mangager
by darobin (Monk) on Apr 03, 2001 at 14:11 UTC

    Have you tried Alzabo ? You could write the very same thing for it, but get more info and do a lot of other cool things. You should really check it out.

    -- darobin

Re: DB Mangager
by TheoPetersen (Priest) on Apr 04, 2001 at 17:06 UTC
    Does the Mangager generate cool manga characters from a database? :)

    Oh, I get it, you mean DB Manager. Oh well. There's quite a few of these already, so you might consider adding to a project rather than starting your own. I usually bring up WDBI since I've used it the most, and it already works with multiple databases. It's home page is ominously silent, however; but a quick trip to FreshMeat showed me some new DB definer/managers that I'd never seen before, so no worries.

    If you feel you really must write another one of these yourself, then I suggest taking most of what you have now and moving it to a module named for MySQL. Then install yourself some PostgreSQL and make another module for it. By getting at least two databases working, you'll have migrated a good portion of the non-portable code out of the main program and also will have created a framework for others to add databases, should they be inclined.

      I apologize for the bad spelling and typos. (jerk) just kidding

      I realized that I could probably find something out there just like this, but the point of doing it was to see if I could, and to hopefully learn something(which I did). And the reason I posted it was to get feedback mainly on my coding, to see if there was any blatantly outstanding logic flaws.

      That is a great idea about the modules, I will have to work on that.

      Thanks

      my $name = 'Ben Kittrell'; $name=~s/^(.+)\s(.).+$/\L$1$2/g; my $nick = 'tha' . $name . 'sta';
Re: DB Mangager
by Anonymous Monk on Nov 24, 2005 at 05:00 UTC
    I really don't appreciat emy name being posted for google search results under lame database code. Sincerely, Ben Kittrell Wichita, KS

Back to Code Catacombs

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: sourcecode [id://69132]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (7)
As of 2014-09-19 08:25 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (133 votes), past polls