Administering Bugzilla
Bugzilla Configuration
Bugzilla is configured by changing various parameters, accessed
from the "Edit parameters" link in the page footer. Here are
some of the key parameters on that page. You should run down this
list and set them appropriately after installing Bugzilla.
checklist
maintainer:
The maintainer parameter is the email address of the person
responsible for maintaining this
Bugzilla installation. The address need not be that of a valid Bugzilla
account.
urlbase:
This parameter defines the fully qualified domain name and web
server path to your Bugzilla installation.
For example, if your Bugzilla query page is
http://www.foo.com/bugzilla/query.cgi,
set your urlbase
to http://www.foo.com/bugzilla/.
makeproductgroups:
This dictates whether or not to automatically create groups
when new products are created.
useentrygroupdefault:
Bugzilla products can have a group associated with them, so that
certain users can only see bugs in certain products. When this
parameter is set to on
, this
causes the initial group controls on newly created products
to place all newly-created bugs in the group
having the same name as the product immediately.
After a product is initially created, the group controls
can be further adjusted without interference by
this mechanism.
shadowdb:
You run into an interesting problem when Bugzilla reaches a
high level of continuous activity. MySQL supports only table-level
write locking. What this means is that if someone needs to make a
change to a bug, they will lock the entire table until the operation
is complete. Locking for write also blocks reads until the write is
complete. Note that more recent versions of mysql support row level
locking using different table types. These types are slower than the
standard type, and Bugzilla does not yet take advantage of features
such as transactions which would justify this speed decrease. The
Bugzilla team are, however, happy to hear about any experiences with
row level locking and Bugzilla
The shadowdb
parameter was designed to get around this limitation. While only a
single user is allowed to write to a table at a time, reads can
continue unimpeded on a read-only shadow copy of the database.
Although your database size will double, a shadow database can cause
an enormous performance improvement when implemented on extremely
high-traffic Bugzilla databases.
As a guide, mozilla.org began needing
shadowdb
when they reached around 40,000 Bugzilla users with several hundred
Bugzilla bug changes and comments per day.
The value of the parameter defines the name of the
shadow bug database. You will need to set the host and port settings
from the params page, and set up replication in your database server
so that updates reach this readonly mirror. Consult your database
documentation for more detail.
shutdownhtml:
If you need to shut down Bugzilla to perform administration, enter
some descriptive HTML here and anyone who tries to use Bugzilla will
receive a page to that effect. Obviously, editparams.cgi will
still be accessible so you can remove the HTML and re-enable Bugzilla.
:-)
passwordmail:
Every time a user creates an account, the text of
this parameter (with substitutions) is sent to the new user along with
their password message.
Add any text you wish to the "passwordmail" parameter box. For
instance, many people choose to use this box to give a quick training
blurb about how to use Bugzilla at your site.
movebugs:
This option is an undocumented feature to allow moving bugs
between separate Bugzilla installations. You will need to understand
the source code in order to use this feature. Please consult
movebugs.pl in your Bugzilla source tree for
further documentation, such as it is.
useqacontact:
This allows you to define an email address for each component, in
addition
to that of the default owner, who will be sent carbon copies of
incoming bugs.
usestatuswhiteboard:
This defines whether you wish to have a free-form, overwritable field
associated with each bug. The advantage of the Status Whiteboard is
that it can be deleted or modified with ease, and provides an
easily-searchable field for indexing some bugs that have some trait
in common.
whinedays:
Set this to the number of days you want to let bugs go
in the NEW or REOPENED state before notifying people they have
untouched new bugs. If you do not plan to use this feature, simply do
not set up the whining cron job described in the installation
instructions, or set this value to "0" (never whine).
commenton*:
All these
fields allow you to dictate what changes can pass without comment,
and which must have a comment from the person who changed them.
Often, administrators will allow users to add themselves to the CC
list, accept bugs, or change the Status Whiteboard without adding a
comment as to their reasons for the change, yet require that most
other changes come with an explanation.
Set the "commenton" options according to your site policy. It
is a wise idea to require comments when users resolve, reassign, or
reopen bugs at the very least.
It is generally far better to require a developer comment
when resolving bugs than not. Few things are more annoying to bug
database users than having a developer mark a bug "fixed" without
any comment as to what the fix was (or even that it was truly
fixed!)
supportwatchers:
Turning on this option allows users to ask to receive copies of
all a particular other user's bug email. This is, of
course, subject to the groupset restrictions on the bug; if the
watcher
would not normally be allowed to view a bug, the watcher cannot get
around the system by setting herself up to watch the bugs of someone
with bugs outside her privileges. They would still only receive email
updates for those bugs she could normally view.
User Administration
Creating the Default User
When you first run checksetup.pl after installing Bugzilla, it
will prompt you for the administrative username (email address) and
password for this "super user". If for some reason you delete
the "super user" account, re-running checksetup.pl will again prompt
you for this username and password.
If you wish to add more administrative users, add them to
the "admin" group and, optionally, add edit the tweakparams, editusers,
creategroups, editcomponents, and editkeywords groups to add the
entire admin group to those groups.
Managing Other Users
Creating new users
Your users can create their own user accounts by clicking the
"New Account" link at the bottom of each page (assuming they
aren't logged in as someone else already.) However, should you
desire to create user accounts ahead of time, here is how you do
it.
After logging in, click the "Users" link at the footer of
the query page, and then click "Add a new user".
Fill out the form presented. This page is self-explanatory.
When done, click "Submit".
Adding a user this way will
not
send an email informing them of their username and password.
While useful for creating dummy accounts (watchers which
shuttle mail to another system, for instance, or email
addresses which are a mailing list), in general it is
preferable to log out and use the
New Account
button to create users, as it will pre-populate all the
required fields and also notify the user of her account name
and password.
Modifying Users
To see a specific user, search for their login name
in the box provided on the "Edit Users" page. To see all users,
leave the box blank.
You can search in different ways the listbox to the right
of the text entry box. You can match by
case-insensitive substring (the default),
regular expression, or a
reverse
regular expression match, which finds every user name which does NOT
match the regular expression. (Please see
the man regexp
manual page for details on regular expression syntax.)
Once you have found your user, you can change the following
fields:
Login Name:
This is generally the user's full email address. However, if you
have are using the emailsuffix Param, this may just be the user's
login name. Note that users can now change their login names
themselves (to any valid email address.)
Real Name: The user's real name. Note that
Bugzilla does not require this to create an account.
Password:
You can change the user's password here. Users can automatically
request a new password, so you shouldn't need to do this often.
If you want to disable an account, see Disable Text below.
Disable Text:
If you type anything in this box, including just a space, the
user is prevented from logging in, or making any changes to
bugs via the web interface.
The HTML you type in this box is presented to the user when
they attempt to perform these actions, and should explain
why the account was disabled.
Don't disable the administrator account!
The user can still submit bugs via
the e-mail gateway, if you set it up, even if the disabled text
field is filled in. The e-mail gateway should
not
be enabled for secure installations of Bugzilla.
<groupname>:
If you have created some groups, e.g. "securitysensitive", then
checkboxes will appear here to allow you to add users to, or
remove them from, these groups.
canconfirm:
This field is only used if you have enabled the "unconfirmed"
status. If you enable this for a user,
that user can then move bugs from "Unconfirmed" to a "Confirmed"
status (e.g.: "New" status).
creategroups:
This option will allow a user to create and destroy groups in
Bugzilla.
editbugs:
Unless a user has this bit set, they can only edit those bugs
for which they are the assignee or the reporter. Even if this
option is unchecked, users can still add comments to bugs.
editcomponents:
This flag allows a user to create new products and components,
as well as modify and destroy those that have no bugs associated
with them. If a product or component has bugs associated with it,
those bugs must be moved to a different product or component
before Bugzilla will allow them to be destroyed.
editkeywords:
If you use Bugzilla's keyword functionality, enabling this
feature allows a user to create and destroy keywords. As always,
the keywords for existing bugs containing the keyword the user
wishes to destroy must be changed before Bugzilla will allow it
to die.
editusers:
This flag allows a user to do what you're doing right now: edit
other users. This will allow those with the right to do so to
remove administrator privileges from other users or grant them to
themselves. Enable with care.
tweakparams:
This flag allows a user to change Bugzilla's Params
(using editparams.cgi.)
<productname>:
This allows an administrator to specify the products in which
a user can see bugs. The user must still have the
"editbugs" privilege to edit bugs in these products.
Product, Component, Milestone, and Version Administration
Products
Products
are the broadest category in Bugzilla, and tend to represent real-world
shipping products. E.g. if your company makes computer games,
you should have one product per game, perhaps a "Common" product for
units of technology used in multiple games, and maybe a few special
products (Website, Administration...)
Many of Bugzilla's settings are configurable on a per-product
basis. The number of "votes" available to users is set per-product,
as is the number of votes
required to move a bug automatically from the UNCONFIRMED status to the
NEW status.
To create a new product:
Select "products" from the footer
Select the "Add" link in the bottom right
Enter the name of the product and a description. The
Description field may contain HTML.
Don't worry about the "Closed for bug entry", "Maximum Votes
per person", "Maximum votes a person can put on a single bug",
"Number of votes a bug in this Product needs to automatically get out
of the UNCOMFIRMED state", and "Version" options yet. We'll cover
those in a few moments.
Components
Components are subsections of a Product. E.g. the computer game
you are designing may have a "UI"
component, an "API" component, a "Sound System" component, and a
"Plugins" component, each overseen by a different programmer. It
often makes sense to divide Components in Bugzilla according to the
natural divisions of responsibility within your Product or
company.
Each component has a owner and (if you turned it on in the parameters),
a QA Contact. The owner should be the primary person who fixes bugs in
that component. The QA Contact should be the person who will ensure
these bugs are completely fixed. The Owner, QA Contact, and Reporter
will get email when new bugs are created in this Component and when
these bugs change. Default Owner and Default QA Contact fields only
dictate the
default assignments;
these can be changed on bug submission, or at any later point in
a bug's life.
To create a new Component:
Select the "Edit components" link from the "Edit product"
page
Select the "Add" link in the bottom right.
Fill out the "Component" field, a short "Description",
the "Initial Owner" and "Initial QA Contact" (if enabled.)
The Component and Description fields may contain HTML;
the "Initial Owner" field must be a login name
already existing in the database.
Versions
Versions are the revisions of the product, such as "Flinders
3.1", "Flinders 95", and "Flinders 2000". Version is not a multi-select
field; the usual practice is to select the most recent version with
the bug.
To create and edit Versions:
From the "Edit product" screen, select "Edit Versions"
You will notice that the product already has the default
version "undefined". Click the "Add" link in the bottom right.
Enter the name of the Version. This field takes text only.
Then click the "Add" button.
Milestones
Milestones are "targets" that you plan to get a bug fixed by. For
example, you have a bug that you plan to fix for your 3.0 release, it
would be assigned the milestone of 3.0.
Milestone options will only appear for a Product if you turned
on the "usetargetmilestone" Param in the "Edit Parameters" screen.
To create new Milestones, set Default Milestones, and set
Milestone URL:
Select "Edit milestones" from the "Edit product" page.
Select "Add" in the bottom right corner.
text
Enter the name of the Milestone in the "Milestone" field. You
can optionally set the "sortkey", which is a positive or negative
number (-255 to 255) that defines where in the list this particular
milestone appears. This is because milestones often do not
occur in alphanumeric order For example, "Future" might be
after "Release 1.2". Select "Add".
From the Edit product screen, you can enter the URL of a
page which gives information about your milestones and what
they mean.
If you want your milestone document to be restricted so
that it can only be viewed by people in a particular Bugzilla
group, the best way is to attach the document to a bug in that
group, and make the URL the URL of that attachment.
Voting
Voting allows users to be given a pot of votes which they can allocate
to bugs, to indicate that they'd like them fixed.
This allows developers to gauge
user need for a particular enhancement or bugfix. By allowing bugs with
a certain number of votes to automatically move from "UNCONFIRMED" to
"NEW", users of the bug system can help high-priority bugs garner
attention so they don't sit for a long time awaiting triage.
To modify Voting settings:
Navigate to the "Edit product" screen for the Product you
wish to modify
Maximum Votes per person:
Setting this field to "0" disables voting.
Maximum Votes a person can put on a single
bug":
It should probably be some number lower than the
"Maximum votes per person". Don't set this field to "0" if
"Maximum votes per person" is non-zero; that doesn't make
any sense.
Number of votes a bug in this product needs to
automatically get out of the UNCONFIRMED state:
Setting this field to "0" disables the automatic move of
bugs from UNCONFIRMED to NEW.
Once you have adjusted the values to your preference, click
"Update".
Groups and Group Security
Groups allow the administrator
to isolate bugs or products that should only be seen by certain people.
The association between products and groups is controlled from
the product edit page under Edit Group Controls.
If the makeproductgroups param is on, a new group will be automatically
created for every new product.
On the product edit page, there is a page to edit the
Group Controls
for a product and determine which groups are applicable, default,
and mandatory for each product as well as controlling entry
for each product and being able to set bugs in a product to be
totally read-only unless some group restrictions are met.
For each group, it is possible to specify if membership in that
group is...
required for bug entry,
Not applicable to this product(NA),
a possible restriction for a member of the
group to place on a bug in this product(Shown),
a default restriction for a member of the
group to place on a bug in this product(Default),
or a mandatory restriction to be placed on bugs
in this product(Mandatory).
Not applicable by non-members to this product(NA),
a possible restriction for a non-member of the
group to place on a bug in this product(Shown),
a default restriction for a non-member of the
group to place on a bug in this product(Default),
or a mandatory restriction to be placed on bugs
in this product when entered by a non-member(Mandatory).
required in order to make any change
to bugs in this product including comments.
To create Groups:
Select the groups
link in the footer.
Take a moment to understand the instructions on the Edit
Groups
screen, then select the Add Group
link.
Fill out the Group
, Description
,
and User RegExp
fields.
User RegExp
allows you to automatically
place all users who fulfill the Regular Expression into the new group.
When you have finished, click Add
.
The User Regexp is a perl regexp and, if not anchored, will match
any part of an address. So, if you do not want to grant access
into 'mycompany.com' to 'badperson@mycompany.com.hacker.net', use
'@mycompany\.com$' as the regexp.
After you add your new group, edit the new group. On the
edit page, you can specify other groups that should be included
in this group and which groups should be permitted to add and delete
users from this group.
Note that group permissions are such that you need to be a member
of all the groups a bug is in, for whatever
reason, to see that bug. Similarly, you must be a member
of all of the entry groups for a product
to add bugs to a product and you must be a member
of all of the canedit groups for a product
in order to make any change to bugs in that
product.
Bugzilla Security
Poorly-configured MySQL and Bugzilla installations have
given attackers full access to systems in the past. Please take these
guidelines seriously, even for Bugzilla machines hidden away behind
your firewall. 80% of all computer trespassers are insiders, not
anonymous crackers.
These instructions must, of necessity, be somewhat vague since
Bugzilla runs on so many different platforms. If you have refinements
of these directions, please submit a bug to &bzg-bugs;.
This is not meant to be a comprehensive list of every possible
security issue regarding the tools mentioned in this section. There is
no subsitute for reading the information written by the authors of any
software running on your system.
TCP/IP Ports
TCP/IP defines 65,000 some ports for trafic. Of those, Bugzilla
only needs 1... 2 if you need to use features that require e-mail such
as bug moving or the e-mail interface from contrib. You should audit
your server and make sure that you aren't listening on any ports you
don't need to be. You may also wish to use some kind of firewall
software to be sure that trafic can only be recieved on ports you
specify.
MySQL
MySQL ships by default with many settings that should be changed.
By defaults it allows anybody to connect from localhost without a
password and have full administrative capabilities. It also defaults to
not have a root password (this is not the same as
the system root). Also, many installations default to running
mysqld as the system root.
Consult the documentation that came with your system for
information on making mysqld run as an
unprivleged user.
You should also be sure to disable the anonymous user account
and set a password for the root user. This is accomplished using the
following commands:
bash$ mysql mysql
mysql> DELETE FROM user WHERE user = '';
mysql> UPDATE user SET password = password('new_password') WHERE user = 'root';
mysql> FLUSH PRIVILEGES;
From this point forward you will need to use
mysql -u root -p and enter
new_password when prompted when using the
mysql client.
If you run MySQL on the same machine as your httpd server, you
should consider disabling networking from within MySQL by adding
the following to your /etc/my.conf:
[myslqd]
# Prevent network access to MySQL.
skip-networking
You may also consider running MySQL, or even all of Bugzilla
in a chroot jail; however, instructions for doing that are beyond
the scope of this document.
Daemon Accounts
Many daemons, such as Apache's httpd and MySQL's mysqld default to
running as either root
or nobody
. Running
as root
introduces obvious security problems, but the
problems introduced by running everything as nobody
may
not be so obvious. Basically, if you're running every daemon as
nobody
and one of them gets comprimised, they all get
comprimised. For this reason it is recommended that you create a user
account for each daemon.
You will need to set the webservergroup to
the group you created for your webserver to run as in
localconfig. This will allow
./checksetup.pl to better adjust the file
permissions on your Bugzilla install so as to not require making
anything world-writable.
Web Server Access Controls
There are many files that are placed in the Bugzilla directory
area that should not be accessable from the web. Because of the way
Bugzilla is currently layed out, the list of what should and should
not be accessible is rather complicated. A new installation method
is currently in the works which should solve this by allowing files
that shouldn't be accessible from the web to be placed in directory
outside the webroot. See
bug
44659 for more information.
In the main Bugzilla directory, you should:
Block:
*.pl
*localconfig*
runtests.sh
But allow:
localconfig.js
localconfig.rdf
In data:
Block everything
But allow:
duplicates.rdf
In data/webdot:
If you use a remote webdot server:
Block everything
But allow
*.dot
only for the remote webdot server
Otherwise, if you use a local GraphViz:
Block everything
But allow:
*.png
*.gif
*.jpg
*.map
And if you don't use any dot:
Block everything
In Bugzilla:
Block everything
In template:
Block everything
Bugzilla ships with the ability to generate
.htaccess files instructing
Apache which files
should and should not be accessible. For more information, see
.
You should test to make sure that the files mentioned above are
not accessible from the Internet, especially your
localconfig file which contains your database
password. To test, simply point your web browser at the file; for
example, to test mozilla.org's installation, we'd try to access
. You should
get a 403 Forbidden
error.
Not following the instructions in this section, including
testing, may result in sensitive information being globally
accessible.
You should check to see if instructions
have been included for your web server. You should also compare those
instructions with this list to make sure everything is properly
accounted for.
Template Customization
One of the large changes for 2.16 was the templatization of the
entire user-facing UI, using the
Template Toolkit.
Administrators can now configure the look and feel of Bugzilla without
having to edit Perl files or face the nightmare of massive merge
conflicts when they upgrade to a newer version in the future.
Templatization also makes localized versions of Bugzilla possible,
for the first time. In the future, a Bugzilla installation may
have templates installed for multiple localizations, and select
which ones to use based on the user's browser language setting.
What to Edit
There are two different ways of editing of Bugzilla's templates,
and which you use depends mainly on how you upgrade Bugzilla. The
template directory structure is that there's a top level directory,
template, which contains a directory for
each installed localization. The default English templates are
therefore in en. Underneath that, there
is the default directory and optionally the
custom directory. The default
directory contains all the templates shipped with Bugzilla, whereas
the custom directory does not exist at first and
must be created if you want to use it.
The first method of making customizations is to directly edit the
templates in template/en/default. This is
probably the best method for small changes if you are going to use
the CVS method of upgrading, because if you then execute a
cvs update, any template fixes will get
automagically merged into your modified versions.
If you use this method, your installation will break if CVS conflicts
occur.
The other method is to copy the templates into a mirrored directory
structure under template/en/custom. The templates
in this directory automatically override those in default.
This is the technique you
need to use if you use the overwriting method of upgrade, because
otherwise your changes will be lost. This method is also better if
you are using the CVS method of upgrading and are going to make major
changes, because it is guaranteed that the contents of this directory
will not be touched during an upgrade, and you can then decide whether
to continue using your own templates, or make the effort to me<?php
/**
*
* @package install
* @version $Id$
* @copyright (c) 2006 phpBB Group
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*
*/
$updates_to_version = '3.0.B4';
if (defined('IN_PHPBB') && defined('IN_INSTALL'))
{
return;
}
/**
*/
define('IN_PHPBB', true);
define('IN_INSTALL', true);
$phpbb_root_path = './../';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
// Report all errors, except notices
//error_reporting(E_ALL ^ E_NOTICE);
error_reporting(E_ALL);
@set_time_limit(0);
// Include essential scripts
include($phpbb_root_path . 'config.' . $phpEx);
if (!isset($dbms))
{
die("Please read: <a href='../docs/INSTALL.html'>INSTALL.html</a> before attempting to update.");
}
// Load Extensions
if (!empty($load_extensions))
{
$load_extensions = explode(',', $load_extensions);
foreach ($load_extensions as $extension)
{
@dl(trim($extension));
}
}
// Include files
require($phpbb_root_path . 'includes/acm/acm_' . $acm_type . '.' . $phpEx);
require($phpbb_root_path . 'includes/cache.' . $phpEx);
require($phpbb_root_path . 'includes/template.' . $phpEx);
require($phpbb_root_path . 'includes/session.' . $phpEx);
require($phpbb_root_path . 'includes/auth.' . $phpEx);
require($phpbb_root_path . 'includes/functions.' . $phpEx);
require($phpbb_root_path . 'includes/functions_admin.' . $phpEx);
require($phpbb_root_path . 'includes/constants.' . $phpEx);
require($phpbb_root_path . 'includes/db/' . $dbms . '.' . $phpEx);
require($phpbb_root_path . 'includes/utf/utf_tools.' . $phpEx);
$cache = new cache();
$db = new $sql_db();
// Connect to DB
$db->sql_connect($dbhost, $dbuser, $dbpasswd, $dbname, $dbport, false);
// We do not need this any longer, unset for safety purposes
unset($dbpasswd);
$sql = "SELECT config_value
FROM " . CONFIG_TABLE . "
WHERE config_name = 'default_lang'";
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);
// And finally, load the relevant language files
include($phpbb_root_path . 'language/' . $row['config_value'] . '/common.' . $phpEx);
include($phpbb_root_path . 'language/' . $row['config_value'] . '/acp/common.' . $phpEx);
include($phpbb_root_path . 'language/' . $row['config_value'] . '/install.' . $phpEx);
// Set PHP error handler to ours
//set_error_handler('msg_handler');
// Define some variables for the database update
// Database column types mapping
$dbms_type_map = array(
'mysql_41' => array(
'INT:' => 'int(%d)',
'BINT' => 'bigint(20)',
'UINT' => 'mediumint(8) UNSIGNED',
'UINT:' => 'int(%d) UNSIGNED',
'TINT:' => 'tinyint(%d)',
'USINT' => 'smallint(4) UNSIGNED',
'BOOL' => 'tinyint(1) UNSIGNED',
'VCHAR' => 'varchar(255)',
'VCHAR:' => 'varchar(%d)',
'CHAR:' => 'char(%d)',
'XSTEXT' => 'text',
'XSTEXT_UNI'=> 'varchar(100)',
'STEXT' => 'text',
'STEXT_UNI' => 'varchar(255)',
'TEXT' => 'text',
'TEXT_UNI' => 'text',
'MTEXT' => 'mediumtext',
'MTEXT_UNI' => 'mediumtext',
'TIMESTAMP' => 'int(11) UNSIGNED',
'DECIMAL' => 'decimal(5,2)',
'VCHAR_UNI' => 'varchar(255)',
'VCHAR_UNI:'=> 'varchar(%d)',
'VCHAR_CI' => 'varchar(255)',
'VARBINARY' => 'varbinary(255)',
),
'mysql_40' => array(
'INT:' => 'int(%d)',
'BINT' => 'bigint(20)',
'UINT' => 'mediumint(8) UNSIGNED',
'UINT:' => 'int(%d) UNSIGNED',
'TINT:' => 'tinyint(%d)',
'USINT' => 'smallint(4) UNSIGNED',
'BOOL' => 'tinyint(1) UNSIGNED',
'VCHAR' => 'varchar(255)',
'VCHAR:' => 'varchar(%d)',
'CHAR:' => 'char(%d)',
'XSTEXT' => 'text',
'XSTEXT_UNI'=> 'text',
'STEXT' => 'text',
'STEXT_UNI' => 'text',
'TEXT' => 'text',
'TEXT_UNI' => 'text',
'MTEXT' => 'mediumtext',
'MTEXT_UNI' => 'mediumtext',
'TIMESTAMP' => 'int(11) UNSIGNED',
'DECIMAL' => 'decimal(5,2)',
'VCHAR_UNI' => 'text',
'VCHAR_UNI:'=> array('varchar(%d)', 'limit' => array('mult', 3, 255, 'text')),
'VCHAR_CI' => 'text',
'VARBINARY' => 'varbinary(255)',
),
'firebird' => array(
'INT:' => 'INTEGER',
'BINT' => 'DOUBLE PRECISION',
'UINT' => 'INTEGER',
'UINT:' => 'INTEGER',
'TINT:' => 'INTEGER',
'USINT' => 'INTEGER',
'BOOL' => 'INTEGER',
'VCHAR' => 'VARCHAR(255) CHARACTER SET NONE',
'VCHAR:' => 'VARCHAR(%d) CHARACTER SET NONE',
'CHAR:' => 'CHAR(%d) CHARACTER SET NONE',
'XSTEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
'STEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
'TEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
'MTEXT' => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
'XSTEXT_UNI'=> 'VARCHAR(100) CHARACTER SET UTF8',
'STEXT_UNI' => 'VARCHAR(255) CHARACTER SET UTF8',
'TEXT_UNI' => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
'MTEXT_UNI' => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
'TIMESTAMP' => 'INTEGER',
'DECIMAL' => 'DOUBLE PRECISION',
'VCHAR_UNI' => 'VARCHAR(255) CHARACTER SET UTF8',
'VCHAR_UNI:'=> 'VARCHAR(%d) CHARACTER SET UTF8',
'VCHAR_CI' => 'VARCHAR(255) CHARACTER SET UTF8',
'VARBINARY' => 'CHAR(255) CHARACTER SET NONE',
),
'mssql' => array(
'INT:' => '[int]',
'BINT' => '[float]',
'UINT' => '[int]',
'UINT:' => '[int]',
'TINT:' => '[int]',
'USINT' => '[int]',
'BOOL' => '[int]',
'VCHAR' => '[varchar] (255)',
'VCHAR:' => '[varchar] (%d)',
'CHAR:' => '[char] (%d)',
'XSTEXT' => '[varchar] (1000)',
'STEXT' => '[varchar] (3000)',
'TEXT' => '[varchar] (8000)',
'MTEXT' => '[text]',
'XSTEXT_UNI'=> '[varchar] (100)',
'STEXT_UNI' => '[varchar] (255)',
'TEXT_UNI' => '[varchar] (4000)',
'MTEXT_UNI' => '[text]',
'TIMESTAMP' => '[int]',
'DECIMAL' => '[float]',
'VCHAR_UNI' => '[varchar] (255)',
'VCHAR_UNI:'=> '[varchar] (%d)',
'VCHAR_CI' => '[varchar] (255)',
'VARBINARY' => '[varchar] (255)',
),
'oracle' => array(
'INT:' => 'number(%d)',
'BINT' => 'number(20)',
'UINT' => 'number(8)',
'UINT:' => 'number(%d)',
'TINT:' => 'number(%d)',
'USINT' => 'number(4)',
'BOOL' => 'number(1)',
'VCHAR' => 'varchar2(255)',
'VCHAR:' => 'varchar2(%d)',
'CHAR:' => 'char(%d)',
'XSTEXT' => 'varchar2(1000)',
'STEXT' => 'varchar2(3000)',
'TEXT' => 'clob',
'MTEXT' => 'clob',
'XSTEXT_UNI'=> 'varchar2(300)',
'STEXT_UNI' => 'varchar2(765)',
'TEXT_UNI' => 'clob',
'MTEXT_UNI' => 'clob',
'TIMESTAMP' => 'number(11)',
'DECIMAL' => 'number(5, 2)',
'VCHAR_UNI' => 'varchar2(765)',
'VCHAR_UNI:'=> array('varchar2(%d)', 'limit' => array('mult', 3, 765, 'clob')),
'VCHAR_CI' => 'varchar2(255)',
'VARBINARY' => 'raw(255)',
),
'sqlite' => array(
'INT:' => 'int(%d)',
'BINT' => 'bigint(20)',
'UINT' => 'INTEGER UNSIGNED', //'mediumint(8) UNSIGNED',
'UINT:' => 'INTEGER UNSIGNED', // 'int(%d) UNSIGNED',
'TINT:' => 'tinyint(%d)',
'USINT' => 'INTEGER UNSIGNED', //'mediumint(4) UNSIGNED',
'BOOL' => 'INTEGER UNSIGNED', //'tinyint(1) UNSIGNED',
'VCHAR' => 'varchar(255)',
'VCHAR:' => 'varchar(%d)',
'CHAR:' => 'char(%d)',
'XSTEXT' => 'text(65535)',
'STEXT' => 'text(65535)',
'TEXT' => 'text(65535)',
'MTEXT' => 'mediumtext(16777215)',
'XSTEXT_UNI'=> 'text(65535)',
'STEXT_UNI' => 'text(65535)',
'TEXT_UNI' => 'text(65535)',
'MTEXT_UNI' => 'mediumtext(16777215)',
'TIMESTAMP' => 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
'DECIMAL' => 'decimal(5,2)',
'VCHAR_UNI' => 'varchar(255)',
'VCHAR_UNI:'=> 'varchar(%d)',
'VCHAR_CI' => 'varchar(255)',
'VARBINARY' => 'blob',
),
'postgres' => array(
'INT:' => 'INT4',
'BINT' => 'INT8',
'UINT' => 'INT4', // unsigned
'UINT:' => 'INT4', // unsigned
'USINT' => 'INT2', // unsigned
'BOOL' => 'INT2', // unsigned
'TINT:' => 'INT2',
'VCHAR' => 'varchar(255)',
'VCHAR:' => 'varchar(%d)',
'CHAR:' => 'char(%d)',
'XSTEXT' => 'varchar(1000)',
'STEXT' => 'varchar(3000)',
'TEXT' => 'varchar(8000)',
'MTEXT' => 'TEXT',
'XSTEXT_UNI'=> 'varchar(100)',
'STEXT_UNI' => 'varchar(255)',
'TEXT_UNI' => 'varchar(4000)',
'MTEXT_UNI' => 'TEXT',
'TIMESTAMP' => 'INT4', // unsigned
'DECIMAL' => 'decimal(5,2)',
'VCHAR_UNI' => 'varchar(255)',
'VCHAR_UNI:'=> 'varchar(%d)',
'VCHAR_CI' => 'varchar_ci',
'VARBINARY' => 'bytea',
),
);
// A list of types being unsigned for better reference in some db's
$unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP');
// Only an example, but also commented out
$database_update_info = array(
// Changes within this version
'3.0.b3' => array(
// Change the following columns...
'change_columns' => array(
BBCODES_TABLE => array(
'bbcode_helpline' => array('VCHAR_UNI', ''),
),
USERS_TABLE => array(
'user_occ' => array('TEXT_UNI', ''),
),
CONFIG_TABLE => array(
'config_value' => array('VCHAR_UNI', ''),
),
),
// Add the following columns
'add_columns' => array(
GROUPS_TABLE => array(
'group_founder_manage' => array('BOOL', 0),
),
USERS_TABLE => array(
'user_pass_convert' => array('BOOL', 0),
),
),
),
// Latest version
'3.0.b4' => array(),
);
// Determine mapping database type
switch ($db->sql_layer)
{
case 'mysql':
$map_dbms = 'mysql_40';
break;
case 'mysql4':
case 'mysqli':
$map_dbms = 'mysql_41';
break;
case 'mssql':
case 'mssql_odbc':
$map_dbms = 'mssql';
break;
default:
$map_dbms = $db->sql_layer;
break;
}
$error_ary = array();
$errored = false;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" dir="<?php echo $lang['DIRECTION']; ?>" lang="<?php echo $lang['USER_LANG']; ?>" xml:lang="<?php echo $lang['USER_LANG']; ?>">
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8" />
<meta http-equiv="content-language" content="<?php echo $lang['USER_LANG']; ?>" />
<meta http-equiv="content-style-type" content="text/css" />
<meta http-equiv="imagetoolbar" content="no" />
<title><?php echo $lang['UPDATING_TO_LATEST_STABLE']; ?></title>
<link href="../adm/style/admin.css" rel="stylesheet" type="text/css" media="screen" />
</head>
<body>
<div id="wrap">
<div id="page-header"> </div>
<div id="page-body">
<div class="panel">
<span class="corners-top"><span></span></span>
<div id="content">
<div id="main">
<h1><?php echo $lang['UPDATING_TO_LATEST_STABLE']; ?></h1>
<br />
<p><?php echo $lang['DATABASE_TYPE']; ?> :: <strong><?php echo $db->sql_layer; ?></strong><br />
<?php
$sql = "SELECT config_value
FROM " . CONFIG_TABLE . "
WHERE config_name = 'version'";
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);
echo $lang['PREVIOUS_VERSION'] . ' :: <strong>' . $row['config_value'] . '</strong><br />';
echo $lang['UPDATED_VERSION'] . ' :: <strong>' . $updates_to_version . '</strong>';
$current_version = strtolower($row['config_value']);
$latest_version = strtolower($updates_to_version);
// Schema updates
?>
</p><br /><br />
<h1><?php echo $lang['UPDATE_DATABASE_SCHEMA']; ?></h1>
<br />
<p><?php echo $lang['PROGRESS']; ?> :: <strong>
<?php
flush();
// We go through the schema changes from the lowest to the highest version
// We skip those versions older than the current version
$no_updates = true;
foreach ($database_update_info as $version => $schema_changes)
{
if (version_compare($version, $current_version, '<'))
{
continue;
}
if (!sizeof($schema_changes))
{
continue;
}
$no_updates = false;
// Change columns?
if (!empty($schema_changes['change_columns']))
{
foreach ($schema_changes['change_columns'] as $table => $columns)
{
foreach ($columns as $column_name => $column_data)
{
sql_column_change($map_dbms, $table, $column_name, $column_data);
}
}
}
// Add columns?
if (!empty($schema_changes['add_columns']))
{
foreach ($schema_changes['add_columns'] as $table => $columns)
{
foreach ($columns as $column_name => $column_data)
{
// Only add the column if it does not exist yet
if (!column_exists($map_dbms, $table, $column_name))
{
sql_column_add($map_dbms, $table, $column_name, $column_data);
}
}
}
}
}
_write_result($no_updates, $errored, $error_ary);
// Data updates
$error_ary = array();
$errored = $no_updates = false;
?>
<br /><br />
<h1><?php echo $lang['UPDATING_DATA']; ?></h1>
<br />
<p><?php echo $lang['PROGRESS']; ?> :: <strong>
<?php
flush();
$no_updates = true;
// some code magic
if (version_compare($current_version, '3.0.b3', '<'))
{
// Set group_founder_manage for administrators group
$sql = 'SELECT group_id
FROM ' . GROUPS_TABLE . "
WHERE group_name = 'ADMINISTRATORS'
AND group_type = " . GROUP_SPECIAL;
$result = $db->sql_query($sql);
$group_id = (int) $db->sql_fetchfield('group_id');
$db->sql_freeresult($result);
if ($group_id)
{
$sql = 'UPDATE ' . GROUPS_TABLE . ' SET group_founder_manage = 1 WHERE group_id = ' . $group_id;
_sql($sql, $errored, $error_ary);
}
add_bots();
$no_updates = false;
}
_write_result($no_updates, $errored, $error_ary);
$error_ary = array();
$errored = $no_updates = false;
?>
<br /><br />
<h1><?php echo $lang['UPDATE_VERSION_OPTIMIZE']; ?></h1>
<br />
<p><?php echo $lang['PROGRESS']; ?> :: <strong>
<?php
flush();
// update the version
$sql = "UPDATE " . CONFIG_TABLE . "
SET config_value = '$updates_to_version'
WHERE config_name = 'version'";
_sql($sql, $errored, $error_ary);
/* Optimize/vacuum analyze the tables where appropriate
// this should be done for each version in future along with
// the version number update
switch ($db->sql_layer)
{
case 'mysql':
case 'mysqli':
case 'mysql4':
$sql = 'OPTIMIZE TABLE ' . $table_prefix . 'auth_access, ' . $table_prefix . 'banlist, ' . $table_prefix . 'categories, ' . $table_prefix . 'config, ' . $table_prefix . 'disallow, ' . $table_prefix . 'forum_prune, ' . $table_prefix . 'forums, ' . $table_prefix . 'groups, ' . $table_prefix . 'posts, ' . $table_prefix . 'posts_text, ' . $table_prefix . 'privmsgs, ' . $table_prefix . 'privmsgs_text, ' . $table_prefix . 'ranks, ' . $table_prefix . 'search_results, ' . $table_prefix . 'search_wordlist, ' . $table_prefix . 'search_wordmatch, ' . $table_prefix . 'sessions_keys' . $table_prefix . 'smilies, ' . $table_prefix . 'themes, ' . $table_prefix . 'themes_name, ' . $table_prefix . 'topics, ' . $table_prefix . 'topics_watch, ' . $table_prefix . 'user_group, ' . $table_prefix . 'users, ' . $table_prefix . 'vote_desc, ' . $table_prefix . 'vote_results, ' . $table_prefix . 'vote_voters, ' . $table_prefix . 'words';
_sql($sql, $errored, $error_ary);
break;
case 'postgresql':
_sql("VACUUM ANALYZE", $errored, $error_ary);
break;
}
*/
_write_result($no_updates, $errored, $error_ary);
?>
<br />
<h1><?php echo $lang['UPDATE_COMPLETED']; ?></h1>
<br />
<p style="color:red"><?php echo $lang['UPDATE_FILES_NOTICE']; ?></p>
<p><?php echo $lang['COMPLETE_LOGIN_TO_BOARD']; ?></p>
<?php
// Now we purge the session table as well as all cache files
$cache->purge();
?>
</div>
</div>
<span class="corners-bottom"><span></span></span>
</div>
</div>
<div id="page-footer">
Powered by phpBB © <?php echo date('Y'); ?> <a href="http://www.phpbb.com/">phpBB Group</a>
</div>
</div>
</body>
</html>
<?php
/**
* Function for triggering an sql statement
*/
function _sql($sql, &$errored, &$error_ary, $echo_dot = true)
{
global $db;
if (defined('DEBUG_EXTRA'))
{
echo "<br />\n{$sql}\n<br />";
}
$db->sql_return_on_error(true);
$result = $db->sql_query($sql);
if ($db->sql_error_triggered)
{
$errored = true;
$error_ary['sql'][] = $db->sql_error_sql;
$error_ary['error_code'][] = $db->_sql_error();
}
$db->sql_return_on_error(false);
if ($echo_dot)
{
echo ". \n";
flush();
}
return $result;
}
function _write_result($no_updates, $errored, $error_ary)
{
global $lang;
if ($no_updates)
{
echo ' ' . $lang['NO_UPDATES_REQUIRED'] . '</strong></p>';
}
else
{
echo ' <span class="success">' . $lang['DONE'] . '</span></strong><br />' . $lang['RESULT'] . ' :: ';
if ($errored)
{
echo ' <strong>' . $lang['SOME_QUERIES_FAILED'] . '</strong> <ul>';
for ($i = 0; $i < sizeof($error_ary['sql']); $i++)
{
echo '<li>' . $lang['ERROR'] . ' :: <strong>' . $error_ary['error_code'][$i]['message'] . '</strong><br />';
echo $lang['SQL'] . ' :: <strong>' . $error_ary['sql'][$i] . '</strong><br /><br /></li>';
}
echo '</ul> <br /><br />' . $lang['SQL_FAILURE_EXPLAIN'] . '</p>';
}
else
{
echo '<strong>' . $lang['NO_ERRORS'] . '</strong></p>';
}
}
}
/**
* Check if a specified column exist
*/
function column_exists($dbms, $table, $column_name)
{
global $db;
$db->sql_return_on_error(true);
$sql = "SELECT $column_name FROM $table";
$result = $db->sql_query_limit($sql, 1);
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);
$error = ($db->sql_error_triggered) ? true : false;
$db->sql_return_on_error(false);
return (!$error) ? true : false;
}
/**
* Function to prepare some column information for better usage
*/
function prepare_column_data($dbms, $column_data)
{
global $dbms_type_map, $unsigned_types;
// Get type
if (strpos($column_data[0], ':') !== false)
{
list($orig_column_type, $column_length) = explode(':', $column_data[0]);
if (!is_array($dbms_type_map[$dbms][$orig_column_type . ':']))
{
$column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'], $column_length);
}
else
{
if (isset($dbms_type_map[$dbms][$orig_column_type . ':']['rule']))
{
switch ($dbms_type_map[$dbms][$orig_column_type . ':']['rule'][0])
{
case 'div':
$column_length /= $dbms_type_map[$dbms][$orig_column_type . ':']['rule'][1];
$column_length = ceil($column_length);
$column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'][0], $column_length);
break;
}
}
if (isset($dbms_type_map[$dbms][$orig_column_type . ':']['limit']))
{
switch ($dbms_type_map[$dbms][$orig_column_type . ':']['limit'][0])
{
case 'mult':
$column_length *= $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][1];
if ($column_length > $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][2])
{
$column_type = $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][3];
}
else
{
$column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'][0], $column_length);
}
break;
}
}
}
$orig_column_type .= ':';
}
else
{
$orig_column_type = $column_data[0];
$column_type = $dbms_type_map[$dbms][$column_data[0]];
}
// Adjust default value if db-dependant specified
if (is_array($column_data[1]))
{
$column_data[1] = (isset($column_data[1][$dbms])) ? $column_data[1][$dbms] : $column_data[1]['default'];
}
$sql = '';
switch ($dbms)
{
case 'firebird':
$sql .= " {$column_type} ";
if (!is_null($column_data[1]))
{
$sql .= 'DEFAULT ' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' ';
}
$sql .= 'NOT NULL';
// This is a UNICODE column and thus should be given it's fair share
if (preg_match('/^X?STEXT_UNI|VCHAR_(CI|UNI:?)/', $column_data[0]))
{
$sql .= ' COLLATE UNICODE';
}
break;
case 'mssql':
$sql .= " {$column_type} ";
if (!is_null($column_data[1]))
{
// For hexadecimal values do not use single quotes
if (strpos($column_data[1], '0x') === 0)
{
$sql .= 'DEFAULT (' . $column_data[1] . ') ';
}
else
{
$sql .= 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') ';
}
}
$sql .= 'NOT NULL';
break;
case 'mysql_40':
case 'mysql_41':
$sql .= " {$column_type} ";
// For hexadecimal values do not use single quotes
if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text')
{
$sql .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' ";
}
$sql .= 'NOT NULL';
break;
case 'oracle':
$sql .= " {$column_type} ";
$sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : '';
// In Oracle empty strings ('') are treated as NULL.
// Therefore in oracle we allow NULL's for all DEFAULT '' entries
$sql .= ($column_data[1] === '') ? '' : 'NOT NULL';
break;
case 'postgres':
$sql .= " {$column_type} ";
$sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : '';
$sql .= 'NOT NULL';
// Unsigned? Then add a CHECK contraint
if (in_array($orig_column_type, $unsigned_types))
{
$sql .= " CHECK ({$column_name} >= 0)";
}
break;
case 'sqlite':
$sql .= ' ' . $column_type . ' NOT NULL ';
$sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}'" : '';
break;
}
return array(
'column_type_sql' => $sql,
);
}
/**
* Add new column
*/
function sql_column_add($dbms, $table_name, $column_name, $column_data)
{
global $errored, $error_ary;
$column_data = prepare_column_data($dbms, $column_data);
switch ($dbms)
{
case 'firebird':
$sql = 'ALTER TABLE "' . $table_name . '" ADD "' . $column_name . '" ' . $column_data['column_type_sql'];
_sql($sql, $errored, $error_ary);
break;
case 'mssql':
$sql = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql'];
_sql($sql, $errored, $error_ary);
break;
case 'mysql_40':
case 'mysql_41':
$sql = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'];
_sql($sql, $errored, $error_ary);
break;
case 'oracle':
$sql = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
_sql($sql, $errored, $error_ary);
break;
case 'postgres':
$sql = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql'];
_sql($sql, $errored, $error_ary);
break;
case 'sqlite':
if (version_compare(sqlite_libversion(), '3.0') == -1)
{
$sql = "SELECT sql
FROM sqlite_master
WHERE type = 'table'
AND name = '{$table_name}'
ORDER BY type DESC, name;";
$result = _sql($sql, $errored, $error_ary);
if (!$result)
{
break;
}
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);
$db->sql_transaction('begin');
// Create a backup table and populate it, destroy the existing one
$db->sql_query(preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']));
$db->sql_query('INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name);
$db->sql_query('DROP TABLE ' . $table_name);
preg_match('#\((.*)\)#s', $row['sql'], $matches);
$new_table_cols = trim($matches[1]);
$old_table_cols = preg_split('/,(?=[\\sa-z])/im', $new_table_cols);
$column_list = array();
foreach ($old_table_cols as $declaration)
{
$entities = preg_split('#\s+#', trim($declaration));
if ($entities == 'PRIMARY')
{
continue;
}
$column_list[] = $entities[0];
}
$columns = implode(',', $column_list);
$new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols;
// create a new table and fill it up. destroy the temp one
$db->sql_query('CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');');
$db->sql_query('INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;');
$db->sql_query('DROP TABLE ' . $table_name . '_temp');
$db->sql_transaction('commit');
}
else
{
$sql = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' [' . $column_data['column_type_sql'] . ']';
_sql($sql, $errored, $error_ary);
}
break;
}
}
/**
* Change column type (not name!)
*/
function sql_column_change($dbms, $table_name, $column_name, $column_data)
{
global $dbms_type_map, $db;
global $errored, $error_ary;
$column_data = prepare_column_data($dbms, $column_data);
switch ($dbms)
{
case 'firebird':
// Change type...
$sql = 'ALTER TABLE "' . $table_name . '" ALTER COLUMN "' . $column_name . '" TYPE ' . ' ' . $column_data['column_type_sql'];
_sql($sql, $errored, $error_ary);
break;
case 'mssql':
$sql = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
_sql($sql, $errored, $error_ary);
break;
case 'mysql_40':
case 'mysql_41':
$sql = 'ALTER TABLE `' . $table_name . '` CHANGE `' . $column_name . '` `' . $column_name . '` ' . $column_data['column_type_sql'];
_sql($sql, $errored, $error_ary);
break;
case 'oracle':
$sql = 'ALTER TABLE ' . $table_name . ' MODIFY ' . $column_name . ' ' . $column_data['column_type_sql'];
_sql($sql, $errored, $error_ary);
break;
case 'postgres':
$sql = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET ' . $column_data['column_type_sql'];
_sql($sql, $errored, $error_ary);
break;
case 'sqlite':
$sql = "SELECT sql
FROM sqlite_master
WHERE type = 'table'
AND name = '{$table_name}'
ORDER BY type DESC, name;";
$result = _sql($sql, $errored, $error_ary);
if (!$result)
{
break;
}
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);
$db->sql_transaction('begin');
// Create a temp table and populate it, destroy the existing one
$db->sql_query(preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']));
$db->sql_query('INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name);
$db->sql_query('DROP TABLE ' . $table_name);
preg_match('#\((.*)\)#s', $row['sql'], $matches);
$new_table_cols = trim($matches[1]);
$old_table_cols = preg_split('/,(?=[\\sa-z])/im', $new_table_cols);
$column_list = array();
foreach ($old_table_cols as $key => $declaration)
{
$entities = preg_split('#\s+#', trim($declaration));
$column_list[] = $entities[0];
if ($entities[0] == $column_name)
{
$old_table_cols[$key] = $column_name . ' ' . $column_data['column_type_sql'];
}
}
$columns = implode(',', $column_list);
// create a new table and fill it up. destroy the temp one
$db->sql_query('CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');');
$db->sql_query('INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;');
$db->sql_query('DROP TABLE ' . $table_name . '_temp');
$db->sql_transaction('commit');
break;
}
}
/**
* Add search robots to the database
*/
function add_bots()
{
global $db, $config, $phpbb_root_path, $phpEx;
$sql = 'SELECT *
FROM ' . CONFIG_TABLE;
$result = $db->sql_query($sql);
$config = array();
while ($row = $db->sql_fetchrow($result))
{
$config[$row['config_name']] = $row['config_value'];
}
$db->sql_freeresult($result);
// Obtain any submitted data
$sql = 'SELECT group_id
FROM ' . GROUPS_TABLE . "
WHERE group_name = 'BOTS'";
$result = $db->sql_query($sql);
$group_id = (int) $db->sql_fetchfield('group_id');
$db->sql_freeresult($result);
if (!$group_id)
{
return;
}
// First of all, remove the old bots...
$sql = 'SELECT bot_id
FROM ' . BOTS_TABLE . "
WHERE bot_name IN ('Alexa', 'Fastcrawler', 'Googlebot', 'Inktomi')";
$result = $db->sql_query($sql);
$bot_ids = array();
while ($row = $db->sql_fetchrow($result))
{
$bot_ids[] = $row['bot_id'];
}
$db->sql_freeresult($result);
if (sizeof($bot_ids))
{
// We need to delete the relevant user, usergroup and bot entries ...
$sql_id = ' IN (' . implode(', ', $bot_ids) . ')';
$sql = 'SELECT bot_name, user_id
FROM ' . BOTS_TABLE . "
WHERE bot_id $sql_id";
$result = $db->sql_query($sql);
$user_id_ary = array();
while ($row = $db->sql_fetchrow($result))
{
$user_id_ary[] = (int) $row['user_id'];
}
$db->sql_freeresult($result);
$sql = 'DELETE FROM ' . BOTS_TABLE . "
WHERE bot_id $sql_id";
$db->sql_query($sql);
$_tables = array(USERS_TABLE, USER_GROUP_TABLE);
foreach ($_tables as $table)
{
$sql = "DELETE FROM $table
WHERE " . $db->sql_in_set('user_id', $user_id_ary);
$db->sql_query($sql);
}
}
if (!function_exists('user_add'))
{
include($phpbb_root_path . 'includes/functions_user.' . $phpEx);
}
global $errored, $error_ary;
$bot_list = array(
'AdsBot [Google]' => array('AdsBot-Google', ''),
'Alexa [Bot]' => array('ia_archiver', ''),
'Alta Vista [Bot]' => array('Scooter/', ''),
'Ask Jeeves [Bot]' => array('Ask Jeeves', ''),
'Baidu [Spider]' => array('Baiduspider+(', ''),
'Exabot [Bot]' => array('Exabot/', ''),
'FAST Enterprise [Crawler]' => array('FAST Enterprise Crawler', ''),
'FAST WebCrawler [Crawler]' => array('FAST-WebCrawler/', ''),
'Francis [Bot]' => array('http://www.neomo.de/', ''),
'Gigabot [Bot]' => array('Gigabot/', ''),
'Google Adsense [Bot]' => array('Mediapartners-Google/', ''),
'Google Desktop' => array('Google Desktop', ''),
'Google Feedfetcher' => array('Feedfetcher-Google', ''),
'Google [Bot]' => array('Googlebot', ''),
'Heise IT-Markt [Crawler]' => array('heise-IT-Markt-Crawler', ''),
'Heritrix [Crawler]' => array('heritrix/1.', ''),
'IBM Research [Bot]' => array('ibm.com/cs/crawler', ''),
'ICCrawler - ICjobs' => array('ICCrawler - ICjobs', ''),
'ichiro [Crawler]' => array('ichiro/2', ''),
'Majestic-12 [Bot]' => array('MJ12bot/', ''),
'Metager [Bot]' => array('MetagerBot/', ''),
'MSN NewsBlogs' => array('msnbot-NewsBlogs/', ''),
'MSN [Bot]' => array('msnbot/', ''),
'MSNbot Media' => array('msnbot-media/', ''),
'NG-Search [Bot]' => array('NG-Search/', ''),
'Nutch [Bot]' => array('http://lucene.apache.org/nutch/', ''),
'Nutch/CVS [Bot]' => array('NutchCVS/', ''),
'OmniExplorer [Bot]' => array('OmniExplorer_Bot/', ''),
'Online link [Validator]' => array('online link validator', ''),
'psbot [Picsearch]' => array('psbot/0', ''),
'Seekport [Bot]' => array('Seekbot/', ''),
'Sensis [Crawler]' => array('Sensis Web Crawler', ''),
'SEO Crawler' => array('SEO search Crawler/', ''),
'Seoma [Crawler]' => array('Seoma [SEO Crawler]', ''),
'SEOSearch [Crawler]' => array('SEOsearch/', ''),
'Snappy [Bot]' => array('Snappy/1.1 ( http://www.urltrends.com/ )', ''),
'Steeler [Crawler]' => array('http://www.tkl.iis.u-tokyo.ac.jp/~crawler/', ''),
'Synoo [Bot]' => array('SynooBot/', ''),
'Telekom [Bot]' => array('crawleradmin.t-info@telekom.de', ''),
'TurnitinBot [Bot]' => array('TurnitinBot/', ''),
'Voyager [Bot]' => array('voyager/1.0', ''),
'W3 [Sitesearch]' => array('W3 SiteSearch Crawler', ''),
'W3C [Linkcheck]' => array('W3C-checklink/', ''),
'W3C [Validator]' => array('W3C_*Validator', ''),
'WiseNut [Bot]' => array('http://www.WISEnutbot.com', ''),
'Yacy [Bot]' => array('yacybot', ''),
'Yahoo MMCrawler [Bot]' => array('Yahoo-MMCrawler/', ''),
'Yahoo Slurp [Bot]' => array('Yahoo! DE Slurp', ''),
'Yahoo [Bot]' => array('Yahoo! Slurp', ''),
'YahooSeeker [Bot]' => array('YahooSeeker/', ''),
);
foreach ($bot_list as $bot_name => $bot_ary)
{
$user_row = array(
'user_type' => USER_IGNORE,
'group_id' => $group_id,
'username' => $bot_name,
'user_regdate' => time(),
'user_password' => '',
'user_colour' => '9E8DA7',
'user_email' => '',
'user_lang' => $config['default_lang'],
'user_style' => 1,
'user_timezone' => 0,
'user_dateformat' => $config['default_dateformat'],
);
$user_id = user_add($user_row);
if ($user_id)
{
$sql = 'INSERT INTO ' . BOTS_TABLE . ' ' . $db->sql_build_array('INSERT', array(
'bot_active' => 1,
'bot_name' => $bot_name,
'user_id' => $user_id,
'bot_agent' => $bot_ary[0],
'bot_ip' => $bot_ary[1],
));
_sql($sql, $errored, $error_ary);
}
}
}
?>