help with customer backend

[tsolodov]

Hello all, sorry for my bad eanglish.
I install otrs 2.4 on my server.
I have oracle DB whith my clients, but information of clients stored in multiple tables.(such as: address, account..etc) I read in manual how i may connect clients data from one table, i understood it, but i don't understand how i may connect data from multiple tables. Please help me.

[crythias]

My first blush guess regarding this is to have a "view" that returns what you're looking for. http://www.dba-oracle.com/concepts/views.htm

[tsolodov]

My first blush guess regarding this is to have a "view" that returns what you're looking for. http://www.dba-oracle.com/concepts/views.htm

I know what is view, I want make it without view. I can not change the scheme DB.
Thank you for answer...

[crythias]

I have two options that have come to me.

The code (Kernel/System/CustomerUser/DB.pm) says:

Code: Select all

    $SQL .= " FROM $Self->{CustomerTable} WHERE ";

Anything you want to put in Config.pm Table=> that is valid SQL you should be able to do. (I am sorry, I can't test it, but it should make sense?) You'll want to make this ReadOnly => 1; because the Update/Insert for new users and password changes should happen in your Oracle interface, not in your OTRS Interface. If you do not make it ReadOnly, the INSERT and UPDATE code in DB.pm may not handle your changes properly:

Code: Select all

    my $SQL = "INSERT INTO $Self->{CustomerTable} (";
    for my $Entry ( @{ $Self->{CustomerUserMap}->{Map} } ) {
        if ( $Entry->[0] !~ /^UserPassword$/i ) {
            $SQL .= " $Entry->[2], ";

Kernel/Config.pm suggestion:

Code: Select all

    $Self->{CustomerUser} = {
        Name   => 'Database Backend',
        Module => 'Kernel::System::CustomerUser::DB',
        Params => {
            # if you want to use an external database, add the
            # required settings
#            DSN => 'DBI:odbc:yourdsn',
#            DSN => 'DBI:mysql:database=customerdb;host=customerdbhost',
#            User => '',
#            Password => '',
            Table => 'customer_user LEFT JOIN address_table ON customer_user.column_name=address_table.column_name',
        },
        CustomerID             => 'customer_user.customer_id',
        CustomerValid          => 'customer_user.valid_id',
        CustomerUserListFields => [ 'customer_user.first_name', 'customer_user.last_name', 'address_table.email' ],
        ReadOnly => 1,

I think I have another way, but I can't test it, and you may not like it.

The idea is that you use MSAccess to connect to the Oracle database and join the tables in a query. Then you might be able to use an ODBC connection to the MSAccess database and use the Query as the table to get your data.

This would likely have a performance hit if referencing thousands of records constantly, and if it works at all, you should probably expect the information to be read-only.