[Rt-devel] DBIx::SearchBuilder and two database connections
Tina Schade
Tina.Schade at gmx.de
Fri Jul 23 09:19:16 EDT 2004
Am Freitag, 23. Juli 2004 14:33 schrieben Sie:
> Tina Schade wrote:
> > Am Freitag, 23. Juli 2004 11:54 schrieben Sie:
> >>Tina Schade wrote:
> >>>Hello folks,
> >>>
> >>>I have the problem that my RT-Search doesn´t work correctly. The reason
> >>>is that I have createt a second local DBIx::Handle for my own database
> >>>connection. Now RT searches for tickets in my database sometimes, but
> >>> of course it couldn´t find relation tickets in my database. But RT does
> >>> it not always. Sometimes I get results, when I click two or three time
> >>> the reload button!
> >>>
> >>>
> >>>When I created the $CDB::Handle global, so RT works but searchs always
> >>> in my own database, although the Handle has different names and
> >>> parameters.
> >>
> >>How do you create this object? Some code?
> >
> > I created a new folder in /rt3/lib/ which is called CDB. In this folder
> > are my classes for my own database, like in RT (see attachment-Machine.pm
> > and Machines.pm). There are also a class Handle.pm (see attachment). Then
> > I have the file CDB.pm in /rt3/lib/. In webmux.pl I initialized it with
> > use CDB; and CDB::Init(). How you can see I doesn´t create the connection
> > for my database in CDB::Init(). This I make in my own autohandler with
> > local $CDB::Handle = $CDB::Handle->new() and $CDB::Handle->Connect().
>
> You don't need localize $CDB::Handle( I tried it too, but had problems I
> don't remeber exactly which ). Just create new $CDB::Handle object if it
> doesn't exist(you do it) and always call connect. Apache::DBI will do
> work for you. And also you'll be able to run your code from console
> scripts.
>
Ok, I tried to create $CDB::Handle global, as you can see in CDB.pm. But with
this, RT-search doesn´t work at all!!! So my idea was to make it local and so
at least RT-search works sometimes. Is the mistake that I initialize CDB in
webmux.pl after RT? Also in this way:
use RT;
use CDB;
...
RT::Init();
CDB::Init()
I can´t understand why the RT-search brakes with my Handle? I mailed with
someone else who had similar problems two years ago. But there was the
problem that SearchBuilder makes the Handle global. He meant that
Searchbuilder patched this already...
Could it be thats that already a problem? I printed out the parameter of the
$session{'tickets'}. There stand that DBIxHandle is the RT::Handle. And thats
the same like in SearchBuilder $self. But in $session{'tickets'} I haven´t
the parameter 'records'. It stands for the statementhandle, doesn´t it? Would
it be useful to set the parameter to $session{'tickets'} in Web.pm?
Here are my printouts when I search after tickets:
in /html/autohandler - Handle = RT::Handle=HASH(0x9436dcc),
Apache::DBI::db=HASH(0x8f2a0d8)und das andere Handle?
CDB::Handle=HASH(0x943a884)
Rt::Handle: dsn = dbi:Pg:dbname=rt3;host=localhost
Rt::Handle: DisconnectHandleOnDestroy =
CDB Handle: dsn = dbi:Pg:dbname=keystone;host=roessl
CDB Handle: DisconnectHandleOnDestroy =
Web.pm 599 - TicketsHash _open_parens = HASH(0x88f803c)
Web.pm 599 - TicketsHash _sql_watchersc = 0
Web.pm 599 - TicketsHash table = Tickets
Web.pm 599 - TicketsHash _sql_linkalias =
Web.pm 599 - TicketsHash _sql_trattachalias =
Web.pm 599 - TicketsHash tables =
Web.pm 599 - TicketsHash _sql_localdepth = 0
Web.pm 599 - TicketsHash is_limited = 1
Web.pm 599 - TicketsHash order =
Web.pm 599 - TicketsHash _sql_query = ( 'Owner' = '40' ) AND ( 'Status' =
'open' ) AND ( 'Created' > '2004-03-31 22:00:00' )
Web.pm 599 - TicketsHash user = RT::CurrentUser=HASH(0x88f7598)
Web.pm 599 - TicketsHash restrictions = HASH(0x88f7bf8)
Web.pm 599 - TicketsHash primary_key = id
Web.pm 599 - TicketsHash count_all = 0
Web.pm 599 - TicketsHash _sql_opstack = ARRAY(0x88f7d90)
Web.pm 599 - TicketsHash looking_at_effective_id = 0
Web.pm 599 - TicketsHash must_redo_search = 1
Web.pm 599 - TicketsHash subclauses = HASH(0x88f7dcc)
Web.pm 599 - TicketsHash _sql_looking_at = HASH(0x88f7df0)
Web.pm 599 - TicketsHash _sql_depth = 0
Web.pm 599 - TicketsHash alias_count = 0
Web.pm 599 - TicketsHash where_clause =
Web.pm 599 - TicketsHash RecalcTicketLimits = 0
Web.pm 599 - TicketsHash _sql_subclause = a
Web.pm 599 - TicketsHash order_clause = ORDER BY main.id DESC
Web.pm 599 - TicketsHash restriction_index = 6
Web.pm 599 - TicketsHash _sql_transalias =
Web.pm 599 - TicketsHash DBIxHandle = RT::Handle=HASH(0x88f7ea4)
Web.pm 599 - TicketsHash limit_clause =
Web.pm 599 - TicketsHash _sql_keywordsc = 0
Web.pm 599 - TicketsHash auxillary_tables =
Web.pm 599 - TicketsHash _sql_keywordalias =
Web.pm 599 - TicketsHash itemscount = 0
Web.pm 599 - TicketsHash show_rows = 50
Web.pm 599 - TicketsHash _sql_columns_to_display = ARRAY(0x88f7f1c)
Web.pm 599 - TicketsHash _sql_first = 0
Web.pm 599 - TicketsHash left_joins = HASH(0x88f7f34)
Web.pm 599 - TicketsHash _sql_linksc = 0
Web.pm 599 - TicketsHash aliases = ARRAY(0x88f7f58)
Web.pm 599 - TicketsHash TicketRestrictions = HASH(0x88f8018)
Web.pm 599 - TicketsHash first_row = 0
Web.pm 599 - TicketsHash looking_at_type = 0
Web das Handle: dsn = dbi:Pg:dbname=rt3;host=localhost
Web das Handle: DisconnectHandleOnDestroy =
SearchBuilder.pm 216 - Query: SELECT count(main.id) FROM Tickets main
WHERE ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND
((main.Type = 'ticket')) AND ( ( (main.Owner = '40') ) AND ( (main.Status
= 'open') ) AND ( (main.Created > '2004-03-31 20:00') ) )
SearchBuilder.pm 224 - Welches Handle habe ich ? dsn =
dbi:Pg:dbname=rt3;host=localhost
SearchBuilder.pm 224 - Welches Handle habe ich ? DisconnectHandleOnDestroy
= SearchBuilder.pm 236 self->records/Statementhandle =
DBI::st=HASH(0x96c7f34)
In rt.log I get always this errors:
[Thu Jul 22 09:16:52 2004] [warning]: DBD::Pg::st execute failed: ERROR:
Relation "tickets" does not exist at
/usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder.pm line 243.
(/opt/rt3/lib/RT.pm:250)
[Thu Jul 22 09:16:52 2004] [warning]: DBIx::SearchBuilder error:ERROR:
Relation "tickets" does not exist
Query String is SELECT count(main.id) FROM Tickets main WHERE
((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND
((main.Type = 'ticket')) AND ( ( (main.Owner = '40') ) AND ( (main.Status
= 'open') ) AND ( (main.Created > '2004-03-31 20:00') ) )
(/opt/rt3/lib/RT.pm:250)
[Thu Jul 22 09:16:52 2004] [warning]: DBD::Pg::st fetchrow_array failed:
no statement executing at
/usr/lib/perl5/site_perl/5.8.3/DBIx/SearchBuilder.pm line 257.
(/opt/rt3/lib/RT.pm:250)
>> But I think, when I make subclasses of SearchBuilder and
> > SearchBuilder::Record and put them in my folder CDB, then it can work,
> > couldn´t it?
>
> You can do it as RT do. It will allow you set default handle, and
> primary key. As I can see you code is ok. You have only one small glitch
> you shouldn't change $self->{'aliases'}, current table always get alias
> 'main'.
>
> Try to use standalone script to test your API. It must work.
I can try it, but there where I need my own database connection to show the
datas in the customfields, I get the right datas. And also anything else in
RT works except the search! So Apache::DBI can differ the two db-connection,
because RT wouldn´t work at all. The question is also, why does the search of
RT break with the second handle? And what doesn´t I regard in use RT or
Mason?
Thanks already.
>
> Best regards. Ruslan.
>
> >>>I create the local $CDB::Handle connection in my own autohandler, which
> >>>is in path /rt3/share/html/Ticket/. I also tried to disconnect my
> >>>database in /rt3/share/html/autohandler, with no success.
> >>>
> >>>It also doesn´t work to create my $handle = DBIx::SearchBuilder->new()
> >>> in my own autohandler.
> >>
> >>It's wrong idea. You shoul subclass DBIx::SearchBuilder and
> >>DBIx::SearchBuilder::Record or use RT's classes.
> >>
> >>>Now my question. Does I can use DBIx::SearchBuilder for my second
> >>>database connection? Does SearchBuilder bear up more than one
> >>>database-connection in one application, expecially RT?
> >>>How can I realise two database-connection else, without creating and
> >>>closing a connection in every file I need it?
> >>
> >>It's not a problem of DBIx::SB as I think. IMHO you override something
> >>in RT namespace that break things in RT.
> >>
> >>>I use DBIx::SearchBuilder 1.00_06, a Postgres database, RT 3.0.11, with
> >>>Apache 2 and mod_perl2 on a SuSE 9.1 system.
> >>>
> >>>Thanks for your help!
> >>>
> >>>Grettings,
> >>>Tina
> >>>_______________________________________________
> >>>Rt-devel mailing list
> >>>Rt-devel at lists.bestpractical.com
> >>>http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-deve
> >
> > l
> >
> >
> > ------------------------------------------------------------------------
> >
> >
> > package CDB;
> >
> > use strict;
> > use vars qw($Handle);
> >
> > =item Init
> >
> > Connect to the database.
> >
> > =cut
> >
> > sub Init {
> > require CDB::Handle;
> >
> > use CDB::Customer;
> > use CDB::Machine;
> > use CDB::Contactperson;
> >
> > unless ($Handle && $Handle->dbh && $Handle->dbh->ping) {
> > $Handle = CDB::Handle->new();
> > }
> > #$Handle->Connect();
> >
> > #my $customer = new CDB::Customer($Handle);
> > #my $machine = new CDB::Machine($Handle);
> > #my $contactperson = new CDB::Contactperson($Handle);
> > }
> >
> > 1;
> >
> >
> > ------------------------------------------------------------------------
> >
> > package CDB::Handle;
> >
> > use strict;
> > use vars qw/@ISA/;
> >
> > eval "use DBIx::SearchBuilder::Handle::Pg;
> > \@ISA= qw(DBIx::SearchBuilder::Handle::Pg);";
> > #TODO check for errors here.
> >
> >
> > my $DatabaseUser = '####';
> > my $DatabasePassword = '';
> > my $Host = '####';
> > my $DatabaseName = '###';
> > my $DatabaseType = 'Pg';
> >
> >
> > =head2 Connect
> >
> > Connects to KDB database handle.
> > Takes nothing. Calls SUPER::Connect with the needed args
> >
> > =cut
> >
> > sub Connect {
> >
> > my $self = shift;
> >
> > $self->SUPER::Connect( User => $DatabaseUser,
> > Password => $DatabasePassword, );
> >
> > $self->dbh->{LongReadLen} = $RT::MaxAttachmentSize;
> > }
> >
> > =item BuildDSN
> >
> > Build the DSN for the KDB database. doesn't take any parameters.
> >
> > =cut
> >
> >
> > sub BuildDSN {
> > my $self = shift;
> >
> > $self->SUPER::BuildDSN(Host => $Host,
> > Database => $DatabaseName,
> > Port => $RT::DatabasePort,
> > Driver => $DatabaseType,
> > DisconnectHandleOnDestroy => 1);
> > }
> >
> > 1;
> >
> >
> > ------------------------------------------------------------------------
> >
> > # written by Tina Schade, Cand. Med. Inform.
> > # 08.03.2004, 13.04.2004
> >
> > package CDB::Machine;
> > use DBIx::SearchBuilder::Record;
> > use vars qw( @ISA );
> >
> > @ISA = ('DBIx::SearchBuilder::Record');
> >
> > sub _Init {
> > my $self = shift;
> > $self->_Handle($CDB::Handle);
> > $self->Table('kdb_maschinen');
> >
> > return $self;
> > }
> >
> > sub _PrimaryKeys {
> > my $self = shift;
> > return['id'];
> > }
> >
> > sub Create {
> > my $self = shift;
> > my %args = (HostName => '',
> > Domain => '',
> > Workstation_Typ => '',
> > BetriebsSystem_Version => '',
> > Ip_Adresse => '',
> > SubnetzMaske => '',
> > EthernetAdresse => '',
> > KernelVersion => '',
> > Chili_Version => '',
> > Aktiv => '',
> > Datum_ErstInstallation => '',
> > Expire_Datum => '',
> > Datum_Letztes_Update => '',
> > Tel_Data => '',
> > Tel_Voice => '',
> > AenderungsZeitstempel => '',
> > @_);
> >
> > $shift->SUPER::Create(hostname => $args{'HostName'},
> > domain => $args{'Domain'},
> > workstation_typ => $args{'Worstation_Typ'},
> > betriebsSystem_version => $args{'BetriebsSystem_Version'},
> > ip_adresse => $args{'Ip_Adresse'},
> > subnetzmaske => $args{'SubnetzMaske'},
> > ethernetadresse => $args{'EthernetAdresse'},
> > kernelversion => $args{'KernelVersion'},
> > chili_version => $args{'Chili_Version'},
> > aktiv => $args{'Aktiv'},
> > datum_erstinstallation => $args{'Datum_ErstInstallation'},
> > expire_datum => $args{'Expire_Datum'},
> > datum_letztes_update => $args{'Datum_Letztes_Update'},
> > tel_data => $args{'Tel_Data'},
> > tel_voice => $args{'Tel_Voice'},
> > aenderungszeitstempel => $args{'AenderungsZeitstempel'}
> > );
> > }
> >
> > sub _ClassAccessible {
> > {HostName => {read => 1, write => 1, type => 'varchar(30)', default
> > => '0'}, Domain => {read => 1, write => 1, type => 'varchar(30)', defautl
> > => ''}, Workstation_Typ => {read => 1, write => 1, type => 'varchar(30)',
> > default => ''}, BetriebsSystem_Version => {read => 1, write => 1, type
> > => 'varchar(30)', default => ''}, Ip_Adresse => {read => 1, write => 1,
> > type => 'varchar(20)', default => ''}, SubnetzMaske => {read => 1, write
> > => 1, type => 'varchar(20)', default => ''}, EthernetAdresse => {read =>
> > 1, write => 1, type => 'varchar(20)', default => ''}, KernelVersion =>
> > {read => 1, write => 1, type => 'varchar(30)', default => ''},
> > Chili_Version => {read => 1, write => 1, type => 'varchar(8)', default =>
> > ''}, Aktiv => {read => 1, write => 1, type => 'varchar(10)', default =>
> > ''}, Datum_ErstInstallation => {read => 1, write => 1, type =>
> > 'datetime', default => ''}, Expire_Datum => {read => 1, write => 1, type
> > => 'datetime', default => ''}, Datum_Letztes_Update => {read => 1, write
> > => 1, type => 'datetime', default => ''}, Tel_Data => {read => 1, write
> > => 1, type => 'varchar(20)', default => ''}, Tel_Voice => {read => 1,
> > write => 1, type => 'varchar(20)', default => ''}, AenderungsZeitstempel
> > => {read => 1, write => 1, type => 'datetime', default => ''},
> > kdb_kunden_id => {read => 1, type => 'int(12)', default => '0'},
> > kdb_zugangsarten_id =>{read => 1, type => 'int(12)', default => '0'},
> > kdb_wartungsvertraege_id => {read => 1, type => 'int(12)', default =>
> > ''}, kdb_passwoerter_id => {read => 1, type => 'int(12)', default => ''}
> > };
> > }
> >
> > 1;
> >
> >
> > ------------------------------------------------------------------------
> >
> > # written by Tina Schade, Cand. Inform. Med.
> > # 13.04.2004
> > use strict;
> > package CDB::Machines;
> >
> > use CDB::Machine;
> > use DBIx::SearchBuilder;
> >
> > use vars qw( @ISA );
> > @ISA = qw(DBIx::SearchBuilder);
> >
> > sub _Init {
> > my $self = shift;
> > print "Init Machines: HAndle = $CDB::Handle<br>\n";
> > my @aliases = ('kdb_maschinen');
> > $self->{'DBIxHandle'} = $CDB::Handle;
> > $self->{'table'} = 'kdb_maschinen';
> > $self->{'primary_key'} = 'id';
> > $self->{'aliases'} = \@aliases;
> > $self->{'itemscount'} = 0;
> >
> > return ( $self );
> > }
> >
> >
> > =item NewItem
> >
> > Returns an empty new Kunde item
> >
> > =cut
> >
> > sub NewItem {
> > my $self = shift;
> > return(new CDB::Machine($CDB::Handle));
> > }
> >
> >
> > 1;
More information about the Rt-devel
mailing list