[otrs-cvs] otrs/scripts/test/DB Bug9092.t,NONE,1.1

Comments:
Update of /home/cvs/otrs/scripts/test/DB
In directory lancelot:/tmp/cvs-serv21230/scripts/test/DB

Added Files:
	Bug9092.t 
Log Message:
Added test for bug#9092.

Author: mg

--- NEW FILE: Bug9092.t ---
# --
# Bug9092.t - database tests
# Copyright (C) 2001-2013 OTRS AG, http://otrs.org/
# --
# $Id: Bug9092.t,v 1.1 2013/01/29 08:32:47 mg Exp $
# --
# This software comes with ABSOLUTELY NO WARRANTY. For details, see
# the enclosed file COPYING for license information (AGPL). If you
# did not receive this file, see http://www.gnu.org/licenses/agpl.txt.
# --

use strict;
use warnings;
use vars qw($Self);

=cut
This test is supposed to verify the solution for bug#9092, which showed
that on certain versions of MySQL with InnoDB, dropping indices causes
problems because the indices actually contain foreign key constraint data.

A workaround is to first drop the foreign key constraints, then drop the indices, and
then re-add the foreign keyconstraints again.
=cut

use Kernel::System::XML;

my $XMLObject = Kernel::System::XML->new( %{$Self} );
my $DBObject  = Kernel::System::DB->new( %{$Self} );

my @Tests = (

    # article flag table as defined in OTRS 3.1
    '<Table Name="bug9092">
        <Column Name="article_id" Required="true" Type="BIGINT"/>
        <Column Name="article_key" Required="true" Size="50" Type="VARCHAR"/>
        <Column Name="article_value" Required="false" Size="50" Type="VARCHAR"/>
        <Column Name="create_time" Required="true" Type="DATE"/>
        <Column Name="create_by" Required="true" Type="INTEGER"/>
        <Index Name="bug9092_article_id_create_by">
            <IndexColumn Name="article_id"/>
            <IndexColumn Name="create_by"/>
        </Index>
        <Index Name="bug9092_article_id_article_key">
            <IndexColumn Name="article_id"/>
            <IndexColumn Name="article_key"/>
        </Index>
        <Index Name="bug9092_create_by">
            <IndexColumn Name="create_by"/>
        </Index>
        <Index Name="bug9092_article_id">
            <IndexColumn Name="article_id"/>
        </Index>
        <ForeignKey ForeignTable="article">
            <Reference Local="article_id" Foreign="id"/>
        </ForeignKey>
        <ForeignKey ForeignTable="users">
            <Reference Local="create_by" Foreign="id"/>
        </ForeignKey>
    </Table>',

    # insert test data
    '<Insert Table="bug9092">
        <Data Key="article_id">1</Data>
        <Data Key="article_key" Type="Quote">Seen</Data>
        <Data Key="article_value" Type="Quote">1</Data>
        <Data Key="create_time" Type="">current_timestamp</Data>
        <Data Key="create_by">1</Data>
    </Insert>',

    # drop foreign keys
    '<TableAlter Name="bug9092">
        <ForeignKeyDrop ForeignTable="article">
            <Reference Local="article_id" Foreign="id"/>
        </ForeignKeyDrop>
        <ForeignKeyDrop ForeignTable="users">
            <Reference Local="create_by" Foreign="id"/>
        </ForeignKeyDrop>
    </TableAlter>',

    # drop unneeded indices
    '<TableAlter Name="bug9092">
        <IndexDrop Name="bug9092_create_by"/>
        <IndexDrop Name="bug9092_article_id_article_key"/>
    </TableAlter>',

    # recreate foreign keys
    '<TableAlter Name="bug9092">
        <ForeignKeyCreate ForeignTable="article">
            <Reference Local="article_id" Foreign="id"/>
        </ForeignKeyCreate>
        <ForeignKeyCreate ForeignTable="users">
            <Reference Local="create_by" Foreign="id"/>
        </ForeignKeyCreate>
    </TableAlter>',

    # cleanup test table
    '<TableDrop Name="bug9092"/>',
);

for my $Test (@Tests) {
    my @XMLARRAY = $XMLObject->XMLParse( String => $Test );
    my @SQL = $DBObject->SQLProcessor( Database => \@XMLARRAY );
    my @SQLPost = $DBObject->SQLProcessorPost( Database => \@XMLARRAY );

    for my $SQL ( @SQL, @SQLPost ) {
        $Self->True(
            $DBObject->Do( SQL => $SQL ) || 0,
            "$SQL",
        );
    }
}

1;
---------------------------------------------------------------------
OTRS mailing list: cvs-log - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/cvs-log
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/cvs-log