430 lines
15 KiB
Perl
430 lines
15 KiB
Perl
package TTXMySQLSetup;
|
|
#
|
|
# This module is a part of Trouble Ticket Express package
|
|
# http://www.troubleticketexpress.com
|
|
#
|
|
# COPYRIGHT: 2002-2006, United Web Coders
|
|
# http://www.unitedwebcoders.com
|
|
#
|
|
# $Revision: 203 $
|
|
# $Date: 2006-07-18 00:28:33 -0400 (Tue, 18 Jul 2006) $
|
|
#
|
|
|
|
$TTXMySQLSetup::VERSION='2.22';
|
|
BEGIN {
|
|
$TTXMySQLSetup::REVISION = '$Revision: 203 $';
|
|
if ($TTXMySQLSetup::REVISION =~ /(\d+)/) {
|
|
$TTXMySQLSetup::REVISION = $1;
|
|
}
|
|
};
|
|
|
|
use TTXConfig;
|
|
require TTXUser;
|
|
require TTXTickets;
|
|
use strict;
|
|
|
|
my $cfg = {};
|
|
my $query = {};
|
|
my $newdb;
|
|
my $doimport;
|
|
my $pwd;
|
|
|
|
# ======================================================================= setup1
|
|
|
|
sub setup1 {
|
|
$cfg = $_[0];
|
|
$query = $_[1];
|
|
my $error;
|
|
$pwd = cleanit($query, 'pwd');
|
|
my $dbid = cleanit($query, 'dbid') || $cfg->get('dbid');
|
|
my $host = cleanit($query, 'host') || $cfg->get('dbhost') || 'localhost';
|
|
my $port = cleanit($query, 'port') || $cfg->get('dbport') || 3306;
|
|
my $user = cleanit($query, 'user') || $cfg->get('dbuser');
|
|
my $pass = cleanit($query, 'pass') || $cfg->get('dbpass');
|
|
my $prefix = cleanit($query, 'prefix');
|
|
$prefix = $cfg->get('dbpref') if !$query->param('do');
|
|
my $imgurl = $cfg->get('imgurl');
|
|
#
|
|
# Check if DBI module installed
|
|
#
|
|
eval "use DBI";
|
|
$error = "The DBI Perl library was not found on this server.<br>Please install DBI library prior to running this program." if $@;
|
|
#
|
|
# Check if MySQL DBD driver installed
|
|
#
|
|
if ($error eq undef) {
|
|
my @drivers = DBI->available_drivers(1);
|
|
$error = "The MySQL DBD driver was not found on this server.<br>Please install MySQL DBD driver prior to running this program."
|
|
if !grep(/mysql/, @drivers);
|
|
}
|
|
if ($error eq undef && $query->param('do')) {
|
|
$cfg->set('dbid', $dbid);
|
|
$cfg->set('dbhost', $host);
|
|
$cfg->set('dbport', $port);
|
|
$cfg->set('dbuser', $user);
|
|
$cfg->set('dbpass', $pass);
|
|
$cfg->set('dbpref', $prefix);
|
|
$doimport = 1 if $cfg->get('dbmode') ne 'mysql';
|
|
$error = build($cfg);
|
|
if ($error eq undef) {
|
|
eval "use TTXMySQLTickets";
|
|
if ($@) {
|
|
$cfg->set('dbmode', 'plaintext');
|
|
} else {
|
|
$cfg->set('dbmode', 'mysql');
|
|
$cfg->set('dbschema', '2.22');
|
|
}
|
|
$cfg->save();
|
|
}
|
|
}
|
|
if ($query->param('do') && $error eq undef ) {
|
|
ok();
|
|
return;
|
|
}
|
|
my $warning;
|
|
if ($cfg->get('dbmode') eq 'mysql') {
|
|
$warning = "YOU HAVE ALREADY ENABLED MYSQL MODE<br>\n".
|
|
"THIS SCRIPT WILL RESET YOUR TICKETS DATABASE<br>\n".
|
|
"ALL TICKETS SUBMITTED USING MYSQL MODE WILL BE LOST!";
|
|
}
|
|
$error = "<br><b><font color=red>Error: $error</font></b><br><br>" if $error ne undef;
|
|
$warning = "\n<br><b><font color=red>$warning</font></b><br><br>" if $warning ne undef;
|
|
print <<EOT;
|
|
<center><b>MySQL Module Setup</b><br>
|
|
<span class=sm>(go to <a href="setup.cgi?cmd=setup4&pwd=$pwd">Main Setup Utility</a> form)</span>
|
|
<br>$error$warning
|
|
<form action=$ENV{SCRIPT_NAME} method=post>
|
|
<input type=hidden name=cmd value=setup1>
|
|
<input type=hidden name=do value=1>
|
|
<input type=hidden name=pwd value=$pwd>
|
|
<table border=0 cellpadding=0 cellspacing=0>
|
|
<tr height=1><td colspan=2 align=center height=1 bgcolor="#9b9b9b"><img
|
|
src="$imgurl/dot.gif" height=1></td></tr>
|
|
<tr><td colspan=2><br>
|
|
MySQL database access info. Please contact your hosting provider or system admin if not sure.<br><br>
|
|
</td></tr>
|
|
<tr><td align=right><b>MySQL Host:</b></td><td> <input type=text name=host value="$host"></td></tr>
|
|
<tr><td align=right><b>MySQL Port:</b></td><td> <input type=text name=port value="$port"></td></tr>
|
|
<tr><td align=right><b>Database ID:</b></td><td> <input type=text name=dbid value="$dbid"></td></tr>
|
|
<tr><td align=right><b>MySQL User:</b></td><td> <input type=text name=user value="$user"></td></tr>
|
|
<tr><td align=right><b>MySQL Password:</b></td><td> <input type=password name=pass value="$pass"></td></tr>
|
|
|
|
<tr><td colspan=2 align=center> </td></tr>
|
|
<tr height=1><td colspan=2 align=center height=1 bgcolor="#9b9b9b"><img
|
|
src="$imgurl/dot.gif" height=1></td></tr>
|
|
<tr><td colspan=2><br>
|
|
If you have access to a single database or would like to use existing database, you may
|
|
provide a prefix, which will be added to the names of all the Trouble
|
|
Ticket Express tables.<br><br>
|
|
</td></tr>
|
|
<tr><td align=right><b>Table prefix</b> (optional)<b>:</b></td><td> <input type=text name=prefix value="$prefix"></td></tr>
|
|
<tr><td colspan=2 align=center>
|
|
<br><input type=submit value="Save Settings">
|
|
</td></tr>
|
|
</form>
|
|
</table>
|
|
<br>
|
|
<br>Login to <a href="ttx.cgi?cmd=login"><b>Trouble Ticket Express</b></a>
|
|
</center>
|
|
EOT
|
|
}
|
|
# =========================================================================== ok
|
|
|
|
sub ok {
|
|
print "<center><table width=600><tr><td align=left><center><b>Congratulations!</b></center><br><br>\n";
|
|
if ($newdb) {
|
|
print "You have created Trouble Ticket Express database and imported existing tickets.\n"
|
|
} elsif ($doimport) {
|
|
print "You have imported existing tickets into Trouble Ticket Express database.\n"
|
|
} else {
|
|
print "You are proud owner of the Trouble Ticket Express MySQL Edition.\n".
|
|
"Please do not run database setup utility unless you want to make changes to the database settings.\n"
|
|
}
|
|
print "<br><br>\n";
|
|
if ($cfg->get('dbmode') ne 'mysql') {
|
|
print <<EOT;
|
|
<b>IMPORTANT</b>
|
|
Trouble Ticket Express still uses plain text database.
|
|
Neither new tickets, nor follow-up messages to existing tickets will be added to the MySQL database.
|
|
In order to enable MySQL features you must install a MySQL add-on module to your web server.
|
|
The module may be ordered on-line at our web site. After uploading the MySQL add-on module to your web server,
|
|
please run this script once more - it will update your MySQL database to ensure it includes
|
|
recent data from your plain text database and will switch Trouble Ticket Express into MySQL mode.
|
|
<br><br>
|
|
<center>
|
|
<a href="http://www.troubleticketexpress.com/mysql.html"><b>Order MySQL add-on module</b></a>
|
|
</center>
|
|
<br><br>
|
|
EOT
|
|
} else {
|
|
print "Trouble Ticket Express now uses MySQL database.<br><br>\n";
|
|
}
|
|
print "<center><a href=\"setup.cgi?cmd=setup4&pwd=$pwd\"><b>Main Setup Utility</b></a><br>".
|
|
"<a href=\"ttx.cgi?cmd=login\"><b>Trouble Ticket Express</b></a></center>\n";
|
|
print "</td></tr></table></center>\n";
|
|
}
|
|
# ====================================================================== cleanit
|
|
|
|
sub cleanit {
|
|
my ($query, $input) = @_;
|
|
my $val = $query->param($input);
|
|
$val =~ s/^\s+//;
|
|
$val =~ s/\s+$//;
|
|
$query->param(-name => $input, -value => $val);
|
|
return $val;
|
|
}
|
|
# ======================================================================== build
|
|
|
|
sub build {
|
|
my $cfg = $_[0];
|
|
$newdb = 0;
|
|
my @manda = ('dbid', 'dbuser', 'dbpass');
|
|
my $id2name = {
|
|
dbid => 'Database ID',
|
|
dbuser => 'MySQL User ID',
|
|
dbpass => 'MySQL User Password'
|
|
};
|
|
my $error;
|
|
foreach my $fld (@manda) {
|
|
my $val = $cfg->get($fld);
|
|
if ($val eq undef) {
|
|
$error .= "Missing ".$id2name->{$fld}."<br>\n";
|
|
}
|
|
}
|
|
#
|
|
# Step one - connect to mysql database engine
|
|
#
|
|
my $dsn = "DBI:mysql:database=".$cfg->get('dbid').";host=".$cfg->get('dbhost').";port=".$cfg->get('dbport');
|
|
my $dbh = DBI->connect($dsn, $cfg->get('dbuser'), $cfg->get('dbpass'),
|
|
{RaiseError => 0, AutoCommit => 1});
|
|
if (!$dbh) {
|
|
# got to check as to why
|
|
if ($DBI::err !~ /\b1049\b/) {
|
|
$error = "Error connecting to a database. $DBI::errstr";
|
|
return $error;
|
|
} else {
|
|
# Well, we are allowed to connect but database does not exist.
|
|
# Will try to create...
|
|
my $drh = DBI->install_driver('mysql');
|
|
if (!$drh) {
|
|
$error = "Error loading mysql DBD driver. $DBI::errstr";
|
|
return $error;
|
|
}
|
|
my $rc = $drh->func('createdb',
|
|
$cfg->get('dbid'),
|
|
$cfg->get('dbhost'),
|
|
$cfg->get('dbuser'),
|
|
$cfg->get('dbpass'),
|
|
'admin');
|
|
if (!$rc) {
|
|
$error = "Failed to create database '".$cfg->get('dbid')."'";
|
|
return $error;
|
|
}
|
|
$newdb = 1;
|
|
$dbh = DBI->connect($dsn, $cfg->get('dbuser'), $cfg->get('dbpass'),
|
|
{RaiseError => 0, AutoCommit => 1});
|
|
if (!$dbh) {
|
|
$error = "Error connecting to a database. $DBI::errstr";
|
|
return $error;
|
|
}
|
|
}
|
|
}
|
|
#
|
|
# Still alive? Good. Try to create tables now.
|
|
#
|
|
my $sth = $dbh->prepare("SHOW TABLES");
|
|
$sth->execute();
|
|
my %tables;
|
|
while (my @row = $sth->fetchrow_array()) {
|
|
$tables{$row[0]} = 1;
|
|
}
|
|
if (!$tables{$cfg->get('dbpref')."tickets"} && !$tables{$cfg->get('dbpref')."messages"}) {
|
|
$doimport = 1;
|
|
} elsif ($doimport) {
|
|
$dbh->do("DROP TABLE ".$cfg->get('dbpref')."tickets") if $tables{$cfg->get('dbpref')."tickets"};
|
|
$dbh->do("DROP TABLE ".$cfg->get('dbpref')."messages") if $tables{$cfg->get('dbpref')."messages"};
|
|
$tables{$cfg->get('dbpref')."tickets"} = 0;
|
|
$tables{$cfg->get('dbpref')."messages"} = 0;
|
|
}
|
|
foreach my $table ('tickets', 'messages') {
|
|
if (!$tables{$cfg->get('dbpref').$table}) {
|
|
$error = addtable($dbh, $cfg, $table);
|
|
return $error if $error ne undef;
|
|
}
|
|
}
|
|
#
|
|
# Import data if needed
|
|
#
|
|
if ($doimport) {
|
|
my @tickfields = TTXTickets::_fields();
|
|
my $boundary = TTXTickets::_boundary();
|
|
my $fn = $cfg->get('ticketdb');
|
|
return undef if ! -f $fn;
|
|
return "Database import failed: error reading ticket database" if !open(DB, $fn);
|
|
my @buff = <DB>;
|
|
close DB;
|
|
chomp @buff;
|
|
$| = 1;
|
|
$dbh->do("SET AUTOCOMMIT=0;");
|
|
my $cmtcnt = 0;
|
|
foreach my $line (@buff) {
|
|
print "\n";
|
|
++$cmtcnt;
|
|
if ($cmtcnt > 99) {
|
|
$dbh->do("COMMIT;");
|
|
$cmtcnt = 0;
|
|
}
|
|
my @fields = split(/\|/, $line);
|
|
my %tik;
|
|
my $sql = "INSERT IGNORE INTO ".$cfg->get('dbpref')."tickets VALUES(";
|
|
my $values;
|
|
foreach my $fld (@tickfields) {
|
|
$tik{$fld} = shift @fields;
|
|
$tik{$fld} = '' if $tik{$fld} eq undef;
|
|
$values .= ',' if $values ne undef;
|
|
if ($fld =~ /^(open|updated|closed)$/) {
|
|
$values .= $dbh->quote(gmt($tik{$fld}));
|
|
} else {
|
|
$values .= $dbh->quote($tik{$fld});
|
|
}
|
|
}
|
|
$sql .= "$values)";
|
|
if (!$dbh->do($sql)) {
|
|
$error = "Error importing ticket #"."$tik{id}. ".$dbh->errstr;
|
|
last;
|
|
}
|
|
my $msgfn = $cfg->get('basedir')."/tickets/$tik{id}.cgi";
|
|
if (-f $msgfn && open(MSG, $msgfn)) {
|
|
my $rawmsg;
|
|
read(MSG, $rawmsg, 64*1024);
|
|
close MSG;
|
|
my @messages = split(/\n$boundary\n/, $rawmsg);
|
|
foreach my $msg (@messages) {
|
|
my @msgparts = split(/\n\n/, $msg);
|
|
my $rawheader = shift @msgparts;
|
|
my $body = join("\n\n", @msgparts);
|
|
my @headerlines = split(/\n/, $rawheader);
|
|
chomp @headerlines;
|
|
my %header;
|
|
foreach my $line (@headerlines) {
|
|
if ($line =~ /^([a-zA-Z][a-zA-Z0-9-]*):\s*(.*)$/) {
|
|
$header{uc $1} = $2;
|
|
}
|
|
}
|
|
my $author = $header{FROM} || '-';
|
|
my $date = gmt($header{DATE});
|
|
my $internal = $header{INTERNAL} ? '1':'0';
|
|
my $isoper = $header{OPERATOR} ? '1':'0';
|
|
$rawheader = '';
|
|
foreach my $hdrkey (sort keys %header) {
|
|
next if grep(/^$hdrkey$/, ('FROM', 'DATE', 'INTERNAL', 'BODY', 'OPERATOR'));
|
|
$rawheader .= "$hdrkey: $header{$hdrkey}\n";
|
|
}
|
|
if (!$dbh->do("INSERT IGNORE INTO ".$cfg->get('dbpref')."messages (TID, AUTHOR, DATE, ISOPER, INTERNAL, HEADERS, MSG) VALUES ".
|
|
"('$tik{id}',".$dbh->quote($author).",'$date','$isoper','$internal',".
|
|
$dbh->quote($rawheader).','.$dbh->quote($body).')')) {
|
|
return "Error importing messages of ticket #"."$tik{id}. ".$dbh->errstr;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
$dbh->do("COMMIT");
|
|
}
|
|
return $error;
|
|
}
|
|
# ========================================================================== gmt
|
|
|
|
sub gmt {
|
|
my $tm;
|
|
if ($_[0] ne undef) { $tm = $_[0]; }
|
|
else { $tm = time(); }
|
|
my ($s, $min, $h, $d, $m, $y) = (gmtime($tm));
|
|
$y += 1900;
|
|
++$m;
|
|
$m = "0$m" if $m < 10;
|
|
$d = "0$d" if $d < 10;
|
|
$h = "0$h" if $h < 10;
|
|
$min = "0$min" if $min < 10;
|
|
$s = "0$s" if $s < 10;
|
|
return "$y-$m-$d $h:$min:$s";
|
|
}
|
|
# ===================================================================== addtable
|
|
|
|
sub addtable {
|
|
my ($dbh, $cfg, $table) = @_;
|
|
my $tablename = $cfg->get('dbpref').$table;
|
|
my $sql;
|
|
if ($table eq 'tickets') {
|
|
$sql = <<EOT;
|
|
CREATE TABLE $tablename (
|
|
ID bigint(20) DEFAULT '0' NOT NULL ,
|
|
ACCESSKEY varchar(64) DEFAULT '' NOT NULL ,
|
|
OPEN datetime DEFAULT '0000-00-00 00:00:00' NOT NULL ,
|
|
UPDATED datetime DEFAULT '0000-00-00 00:00:00' NOT NULL ,
|
|
CLOSED datetime DEFAULT '0000-00-00 00:00:00' NOT NULL ,
|
|
STATUS char(3) DEFAULT '' NOT NULL ,
|
|
OPER varchar(32) DEFAULT '' NOT NULL ,
|
|
EMAIL varchar(128) DEFAULT '' NOT NULL ,
|
|
NAME varchar(128) DEFAULT '' NOT NULL ,
|
|
SUBJECT varchar(255) DEFAULT '' NOT NULL ,
|
|
LNAME varchar(50) DEFAULT '' NOT NULL ,
|
|
C0 varchar(255) DEFAULT '' NOT NULL ,
|
|
C1 varchar(255) DEFAULT '' NOT NULL ,
|
|
C2 varchar(255) DEFAULT '' NOT NULL ,
|
|
C3 varchar(255) DEFAULT '' NOT NULL ,
|
|
C4 varchar(255) DEFAULT '' NOT NULL ,
|
|
C5 varchar(255) DEFAULT '' NOT NULL ,
|
|
C6 varchar(255) DEFAULT '' NOT NULL ,
|
|
C7 varchar(255) DEFAULT '' NOT NULL ,
|
|
C8 varchar(255) DEFAULT '' NOT NULL ,
|
|
C9 varchar(255) DEFAULT '' NOT NULL ,
|
|
GRP varchar(10) DEFAULT '' NOT NULL ,
|
|
EOT
|
|
my $cnt = int ($cfg->get('cfldcnt'));
|
|
if (!$cfg->get('itemidx')) {
|
|
$cfg->set('itemidx', ($cnt > 10) ? $cnt:10);
|
|
$cfg->save();
|
|
}
|
|
if ($cnt > 10) {
|
|
my $i;
|
|
for ($i = 10; $i < $cnt; ++$i) {
|
|
$sql .= "ITEM varchar(255) DEFAULT '' NOT NULL,\n" if $i == $cfg->get('itemidx');
|
|
$sql .= "C$i varchar(255) DEFAULT '' NOT NULL,\n";
|
|
}
|
|
$sql .= "ITEM varchar(255) DEFAULT '' NOT NULL,\n" if $i == $cfg->get('itemidx');
|
|
} else {
|
|
$sql .= "ITEM varchar(255) DEFAULT '' NOT NULL,\n";
|
|
}
|
|
$sql .= <<EOT;
|
|
PRIMARY KEY (ID),
|
|
UNIQUE ID (ID),
|
|
KEY ID_2 (ID)
|
|
)
|
|
EOT
|
|
} elsif ($table eq 'messages') {
|
|
$sql = <<EOT;
|
|
CREATE TABLE $tablename (
|
|
MID BIGINT AUTO_INCREMENT NOT NULL,
|
|
TID BIGINT NOT NULL DEFAULT '0',
|
|
AUTHOR VARCHAR(32) DEFAULT '' NOT NULL ,
|
|
DATE DATETIME DEFAULT '0000-00-00 00:00:00' NOT NULL ,
|
|
INTERNAL CHAR(1) DEFAULT '0' NOT NULL ,
|
|
ISOPER CHAR(1) DEFAULT '0' NOT NULL ,
|
|
HEADERS TEXT DEFAULT '' NOT NULL,
|
|
MSG TEXT DEFAULT '' NOT NULL,
|
|
PRIMARY KEY (MID),
|
|
UNIQUE ID (MID),
|
|
KEY TID (TID)
|
|
)
|
|
EOT
|
|
}
|
|
$sql =~ s/\n/ /g;
|
|
if (!$dbh->do($sql)) {
|
|
return "Error creating table $tablename, ".$dbh->errstr;
|
|
}
|
|
return undef;
|
|
}
|
|
1;
|
|
#
|
|
|