Implementing 2 or more DB backends

[ArielRivas]

Hello:

This is the first time I am setting up an OTRS environment and although I have read all the documentation I could find relating to the things that I wish to accomplish, I am stumbling on some basic concepts.

I am trying to accomplish the following:

Alter the customer / tickets screens so they could contain up to 30 different fields which would come from four different tables from an external mysql database.

In order to accomplish this and following the OTRS documentation and various posts on this and other forms I have generated the following Config.pm code from the Default.pm. For the sake of simplicity this Config.pm only has two external DB backends.

Code: Select all

package Kernel::Config;

use strict;
use warnings;
use utf8;

sub Load {
    my $Self = shift;

    # ---------------------------------------------------- #
    # database settings                                    #
    # ---------------------------------------------------- #

    # The database host
    $Self->{'DatabaseHost'} = 'localhost';

    # The database name
    $Self->{'Database'} = 'otrs';

    # The database user
    $Self->{'DatabaseUser'} = 'otrs';

    # The password of database user. You also can use bin/otrs.CryptPassword.pl
    # for crypted passwords
    $Self->{'DatabasePw'} = 'dbPass';

    # The database DSN for MySQL ==> more: "perldoc DBD::mysql"
    $Self->{DatabaseDSN} = "DBI:mysql:database=$Self->{Database};host=$Self->{DatabaseHost};";

    # ---------------------------------------------------- #
    # fs root directory
    # ---------------------------------------------------- #
    $Self->{Home} = '/opt/otrs';

    # ---------------------------------------------------- #
    # insert your own config settings "here"               #
    # config settings taken from Kernel/Config/Defaults.pm #
    # ---------------------------------------------------- #
    # $Self->{SessionUseCookie} = 0;
    # $Self->{CheckMXRecord} = 0;

    # ---------------------------------------------------- #
    # data inserted by installer                           #
    # ---------------------------------------------------- #
    # $DIBI$

    # --------------------------------------------------- #
    #             Start of config options!!!              #
    #                 CustomerUser stuff                  #
    # --------------------------------------------------- #

    # CustomerUser
    # (customer user database backend and settings)
    
    # CUSTOMER BACKEND Nº 1
    $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=rcmovil;host=hostIP',
            User => 'userID',
            Password => 'dbPass',
            Table => 'clientes',
            # CaseSensitive will control if the SQL statements need LOWER()
            #   function calls to work case insensitively. Setting this to
            #   1 will improve performance dramatically on large databases.
            CaseSensitive => 0,
        },
        # customer uniq id
        CustomerKey => 'id_cliente',
        # customer #
        CustomerID             => 'id_cliente',
        #CustomerValid          => 'valid_id',
        #CustomerValid          => 'id_cliente',
        CustomerUserListFields => [ 'email', 'Msisdn', 'id_cliente' ],
        CustomerUserSearchFields           => [ 'Msisdn', 'mombre', 'apellido1', 'apellido2', 'id_cliente', 'email' ],
        CustomerUserSearchPrefix           => '*',
        CustomerUserSearchSuffix           => '*',
        CustomerUserSearchListLimit        => 250,
        CustomerUserPostMasterSearchFields => ['email'],
        CustomerUserNameFields     => [ 'mombre', 'apellido1', 'apellido2' ],
        CustomerUserEmailUniqCheck => 0,
#        # show now own tickets in customer panel, CompanyTickets
#        CustomerUserExcludePrimaryCustomerID => 0,
#        # generate auto logins
#        AutoLoginCreation => 0,
#        # generate auto login prefix
#        AutoLoginCreationPrefix => 'auto',
#        # admin can change customer preferences
#        AdminSetPreferences => 1,
#        # use customer company support (reference to company, See CustomerCompany settings)
#        CustomerCompanySupport => 1,
#        # cache time to live in sec. - cache any database queries
#        CacheTTL => 0,
#        # just a read only source
        ReadOnly => 1,
        Map => [
            # note: Login, Email and CustomerID needed!
            # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly, http-link-target
            [ 'UserFirstname',  'Firstname',  'mombre', 			1, 1, 'var', '', 0 ],
            [ 'UserLastname',   'Lastname',   'apellido1',  	1, 1, 'var', '', 0 ],
            [ 'UserLogin',      'Username',   'Msisdn',      1, 1, 'int', '', 0 ],
            [ 'UserEmail',      'Email',      'email',      	1, 1, 'var', '', 0 ],
            [ 'UserCustomerID', 'CustomerID', 'id_cliente', 	0, 1, 'int', '', 0 ],
            [ 'UserMobile',      'Mobile',    'Msisdn',     	1, 0, 'int', '', 0 ],
        ],
        # default selections
        Selections => {
#            UserTitle => {
#                'Mr.' => 'Mr.',
#                'Mrs.' => 'Mrs.',
#            },
        },
    };
    
    # CUSTOMER BAKEND Nº 2  
    $Self->{CustomerUser1} = {
        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=rcmovil;host=hostIP',
            User => 'userID',
            Password => 'dbPass',
            Table => 'RC_SECOND',
            # CaseSensitive will control if the SQL statements need LOWER()
            #   function calls to work case insensitively. Setting this to
            #   1 will improve performance dramatically on large databases.
            CaseSensitive => 0,
        },

        # customer uniq id
        CustomerKey => 'RCS_ID',

        # customer #
        CustomerID             => 'RCS_ID',
        #CustomerValid          => 'valid_id',
        #CustomerValid          => 'RCS_ID',
        CustomerUserListFields => [ 'RCS_Name', 'RCS_LastName', 'RCS_Comments', ],

#        CustomerUserListFields => ['login', 'first_name', 'last_name', 'customer_id', 'email'],
        CustomerUserSearchFields           => [ 'RCS_Login', 'RCS_Name', 'RCS_ID' ],
        CustomerUserSearchPrefix           => '*',
        CustomerUserSearchSuffix           => '*',
        CustomerUserSearchListLimit        => 250,
        CustomerUserPostMasterSearchFields => ['RCS_Mail'],
        CustomerUserNameFields     => [ 'RCS_Name', 'RCS_LastName' ],
        CustomerUserEmailUniqCheck => 0,

#        # show now own tickets in customer panel, CompanyTickets
#        CustomerUserExcludePrimaryCustomerID => 0,
#        # generate auto logins
#        AutoLoginCreation => 0,
#        # generate auto login prefix
#        AutoLoginCreationPrefix => 'auto',
#        # admin can change customer preferences
#        AdminSetPreferences => 1,
#        # use customer company support (reference to company, See CustomerCompany settings)
#        CustomerCompanySupport => 1,
#        # cache time to live in sec. - cache any database queries
#        CacheTTL => 0,
#        # just a read only source
        ReadOnly => 1,
        Map => [

            # note: Login, Email and CustomerID needed!
            # var, frontend, storage, shown (1=always,2=lite), required, storage-type, http-link, readonly, http-link-target
            [ 'UserFirstname',  'ARM Firstname',  'RCS_Name', 			1, 1, 'var', '', 0 ],
            [ 'UserLastname',   'ARM Lastname',   'RCS_LastName',  	1, 1, 'var', '', 0 ],
            [ 'UserLogin',      'ARM Username',   'RCS_Login',      1, 1, 'int', '', 0 ],
            [ 'UserEmail',      'Email',      		'RCS_Mail',      	1, 1, 'var', '', 0 ],
            [ 'UserCustomerID', 'ARM CustomerID', 'RCS_ID', 				0, 1, 'int', '', 0 ],
            [ 'UserComment',    'ARM Comment',    'RCS_Comments',   1, 1, 'var', '', 0 ],
           # [ 'ValidID',          'ARM Valid',       'RCS_ID',     0, 1, 'int', '', 0 ],
        ],

        # default selections
        Selections => {

#            UserTitle => {
#                'Mr.' => 'Mr.',
#                'Mrs.' => 'Mrs.',
#            },
        },
    };
    
    

# Customer backend till here

}

# ---------------------------------------------------- #
# needed system stuff (don't edit this)                #
# ---------------------------------------------------- #
use strict;
use warnings;

use vars qw(@ISA);

use Kernel::Config::Defaults;
push (@ISA, 'Kernel::Config::Defaults');

# -----------------------------------------------------#

1;

What I have noticed thus far and for whatever it is worth, is that if the names in $Self->{CustomerUser} = { are different than those in Default.pm, the Default.pm configuration will override the configuration in Config.pm.

One thing to note, as all the documentation stated that login, email and customerId needed to be present in each backend, so fields mgmc_Msisdn, mgmc_email and mgmc_id_cliente from table mgm_clientes and mapped to the OTRS variables 'UserLogin', 'UserEmail' and 'UserCustomerID' contain the same data as the fields RCS_Login, RCS_Mail and RCS_ID from table RC_SECOND and mapped to the OTRS variables 'UserLogin', 'UserEmail' and 'UserCustomerID'

In the case I have posted, as the first backend name (CustomerUser) is the same as the one in the Default.pm (CustomerUser), the first backend configuration mapping is displayed once you click on a particular customer USERNAME.

However, none of the data from the second table mapping (CustomerUser1) is displayed and no data (neither from CustomerUser or CustomerUser1) is populated either. It simply displays the Edit Customer Screen but without any data populated in the corresponding fields. Kind of what you would expect to see if you need to create a new customer.

What am I doing wrong for it not to show the secondary data? and why is the data not populating in the fields?

On a related issue, on the UI screen "Customers / Customer User Administration", Is there another or easier way to change the columns you wish to appear without having to create a new theme by adding and editing the AdminCustomerUser.dtl into "Kernel/Output/HTML/Company" or is this the common practice and standard way of changing these types of screens?

Sorry for the long post, but I thought I would put in as much detail in order for you all to better understand the context.

I really appreciate any help or guidance anyone can provide.

Thank you very much,

Ariel

[ArielRivas]

Hi again:

I have tried doing this through the documentation and related posts I have found here and elswhere, but I have not been able to move much. I have tried to do everything that seemed relevant in the related issues I have seen on this forum, but I must be comitting some very basic errors.

In order to proceed and attempt to solve the second issue I posted earlier, I created a VIEW to emulate the two different backends and from the OTRS perspective have only one backend table.

As in the other post, there are no errors in syslog, but when I click on "Customer User Administration" and then on any of the USERNAME links, I get the "Edit Customer Screen" but non of the corresponding data is populated. All form objects are empty.

Does anyone have any idea of what I am doing wrong causing the data not to populate in the "customer edit screen"?

Here is the Config.pm

BTW. OTRS version is 3.2.10

Code: Select all

    $Self->{CustomerUser} = {
        Name   => 'Database Backend',
        Module => 'Kernel::System::CustomerUser::DB',
        Params => {
            DSN => 'DBI:mysql:database=rcmovil;host=192.168.151.20',
            User => 'root',
            Password => 'RC-m0v1l..',
            Table => 'zTable',
            CaseSensitive => 0,
        },     
        CustomerKey => 'mgmc_id_cliente',
        CustomerID             => 'mgmc_id_cliente',
        CustomerValid          => 'valid_id',
        CustomerUserListFields => [ 'mgmc_Msisdn', 'RCS_Name', 'RCS_LastName', 'mgmc_id_cliente', 'mgmc_email' ],
        CustomerUserSearchFields           => [ 'mgmc_Msisdn', 'RCS_Name', 'RCS_LastName', 'mgmc_id_cliente' ],
        CustomerUserSearchPrefix           => '*',
        CustomerUserSearchSuffix           => '*',
        CustomerUserSearchListLimit        => 250,
        CustomerUserPostMasterSearchFields => ['mgmc_id_cliente'],
        CustomerUserNameFields             => [ 'title', 'RCS_Name', 'RCS_LastName' ],
        CustomerUserEmailUniqCheck         => 1,
        CacheTTL => 60 * 60 * 24,
        Map => [
            [ 'UserTitle',      'Title',      'title',      1, 0, 'var', '', 0 ],
            [ 'UserLogin',      'Username',   'mgmc_Msisdn',      1, 1, 'int', '', 0 ],
            [ 'UserEmail',      'Email',      'mgmc_email',      	1, 1, 'var', '', 0 ],
            [ 'UserCustomerID', 'CustomerID', 'mgmc_id_cliente', 	0, 1, 'int', '', 0 ],
            [ 'UserMobile',      'Mobile',    'mgmc_Msisdn',     	1, 0, 'int', '', 0 ],
            
            [ 'UserFirstname',  'ARM Firstname',  'RCS_Name', 			1, 1, 'var', '', 0 ],
            [ 'UserLastname',   'ARM Lastname',   'RCS_LastName',  	1, 1, 'var', '', 0 ],
            [ 'UserComment',    'ARM Comment',    'RCS_Comments',   1, 1, 'var', '', 0 ],            
            [ 'UserPhone',        'Phone',       'phone',        1, 0, 'var', '', 0 ],
            [ 'UserFax',          'Fax',         'fax',          1, 0, 'var', '', 0 ],
            [ 'UserMobile',       'Mobile',      'mobile',       1, 0, 'var', '', 0 ],
            [ 'UserStreet',       'Street',      'street',       1, 0, 'var', '', 0 ],
            [ 'UserZip',          'Zip',         'zip',          1, 0, 'var', '', 0 ],
            [ 'UserCity',         'City',        'city',         1, 0, 'var', '', 0 ],
            [ 'UserCountry',      'Country',     'country',      1, 0, 'var', '', 0 ],
            [ 'UserComment',      'Comment',     'comments',     1, 0, 'var', '', 0 ],
        ],
        Selections => {

            UserTitle => {
                'Mr.' => 'Mr.',
                'Mrs.' => 'Mrs.',
            },
        },
    }; 
 }; 
# ---------------------------------------------------- #
# needed system stuff (don't edit this)                #
# ---------------------------------------------------- #
use strict;
use warnings;
use vars qw(@ISA);
use Kernel::Config::Defaults;
push (@ISA, 'Kernel::Config::Defaults');
# -----------------------------------------------------#
1;

Please let me know if anyone can help.

Thanks,

Ariel

[crythias]

Your posts are confusing.
Config.pm overrides Defaults.pm
multiple back ends are implemented with an index (number) appended to the keys for each backend. (see the docs)
only one back end is referenced per user
To reference multiple backends of data for a given user, you'd need to figure a way to reference all of that data from one query.
Any field that is referenced in customer user Map (third entry) must have data from the referenced Params/Table.

[ArielRivas]

Hi Crythias.

Thanks for the reply and apologies for the confusion.

In my first post, each backend is indexed as per the docs, but I am indeed trying to get several data points from different backends so if I understand your statement "only one back end is referenced per user", this would explain why it is not working as I expected. If this is correct, although we can connect to 10 backends, we cannot spread accross one single user data points from multiple backends. Is this correct?

In my second post I changed the code where instead of using multiple backends, I used a Database VIEW to join mutilpe data points. The experience here is that although it populated the correct data in the Customer Management screen, it led to the following question:

When I click on any of the links in the USERNAME column in the Customer Management screen, it would open the Edit Customer screen but all fields in the form where empty.

Why are these fields not populated with their corresponding data? Do you know what configuration issues I may have that would cause this?

Thanks,

Ariel

[crythias]

although we can connect to 10 backends, we cannot spread accross one single user data points from multiple backends. Is this correct?

Correct.

Why are these fields not populated with their corresponding data? Do you know what configuration issues I may have that would cause this?

I cannot say. I don't have a good answer if it's populating elsewhere. On the other hand, it may not be editable in any case, and the fields need to be read-only yes in the map.

[ArielRivas]

Thanks for the quick reply.

But if they are not editable, how could you customer info?

I have been trying to observe the difference between this implementation and another that does not have an external backend, where I am able to edit fields in the Customer Edit Screen.

Looking at the Config.pm's of each implementation, they are pretty much identical with the excepyion of the backend portion.

Therefore, I have taken the Config.pm of the non-external-backend implementation and adjusted the database password and observed that it allowed me to create and edit all the Customer Edit Screen fields.

Therefore, this would mean, that there is something in my external-backend Config.pm which is causing this issue, but from the docs, it seems to be all in order.

Any ideas?

Below are the two Config.pm. The first is my external-backend Config.pm and the second one is the non-external-backend Config.pm.

Thanks for the help.

Cheers,

Ariel

Code: Select all

package Kernel::Config;
use strict;
use warnings;
use utf8;
sub Load {
    my $Self = shift;
    $Self->{'DatabaseHost'} = 'localhost';
   	$Self->{'Database'} = 'otrs';
    $Self->{'DatabaseUser'} = 'otrs';
    $Self->{'DatabasePw'} = 'externalDbPass';
    $Self->{DatabaseDSN} = "DBI:mysql:database=$Self->{Database};host=$Self->{DatabaseHost};";
    $Self->{Home} = '/opt/otrs';
    $Self->{CustomerUser} = {
        Name   => 'Database Backend',
        Module => 'Kernel::System::CustomerUser::DB',
        Params => {
            DSN => 'DBI:mysql:database=rcmovil;host=192.168.151.20',
            User => 'dbUser',
            Password => 'externalDbPass',
            Table => 'dbTable',
            CaseSensitive => 0,
        },     
        CustomerKey => 'mgmc_id_cliente',
        CustomerID             => 'mgmc_id_cliente',
        CustomerValid          => 'valid_id',
        CustomerUserListFields => [ 'mgmc_Msisdn', 'RCS_Name', 'RCS_LastName', 'mgmc_id_cliente', 'mgmc_email' ],
        CustomerUserSearchFields           => [ 'mgmc_Msisdn', 'RCS_Name', 'RCS_LastName', 'mgmc_id_cliente' ],
        CustomerUserSearchPrefix           => '*',
        CustomerUserSearchSuffix           => '*',
        CustomerUserSearchListLimit        => 250,
        CustomerUserPostMasterSearchFields => ['mgmc_email'],
        CustomerUserNameFields             => [ 'title', 'RCS_Name', 'RCS_LastName' ],
        CustomerUserEmailUniqCheck         => 1,
        ReadOnly => 1,
        Map => [
            [ 'UserLogin',      'Username',   'mgmc_Msisdn',      1, 1, 'int', '', 0 ],
            [ 'UserEmail',      'Email',      'mgmc_email',      	1, 1, 'var', '', 0 ],
            [ 'UserCustomerID', 'CustomerID', 'mgmc_id_cliente', 	0, 1, 'int', '', 0 ],
            [ 'UserMobile',      'Mobile',    'mgmc_Msisdn',     	1, 0, 'int', '', 0 ],
            [ 'UserFirstname',  'ARM Firstname',  'RCS_Name', 			1, 1, 'var', '', 0 ],
            [ 'UserLastname',   'ARM Lastname',   'RCS_LastName',  	1, 1, 'var', '', 0 ],
            [ 'UserComment',    'ARM Comment',    'RCS_Comments',   1, 1, 'var', '', 0 ],            
           # [ 'ValidID',          'Valid',       'valid_id',     0, 1, 'int', '', 0 ],
        ],
        Selections => {
        },
    };  
} 
use strict;
use warnings;
use vars qw(@ISA);
use Kernel::Config::Defaults;
push (@ISA, 'Kernel::Config::Defaults');
1;

The non Extenal Backen Config.pm

Code: Select all

package Kernel::Config;
use utf8;
sub Load {
    my $Self = shift;
    $Self->{'DatabaseHost'} = 'localhost';
    $Self->{'Database'} = 'otrs';
    $Self->{'DatabaseUser'} = 'otrs';
    $Self->{'DatabasePw'} = 'internalDbPass';
    $Self->{DatabaseDSN} = "DBI:mysql:database=$Self->{Database};host=$Self->{DatabaseHost};";
    $Self->{Home} = '/opt/otrs';
use strict;
use warnings;
use vars qw(@ISA $VERSION);
$VERSION = qw($Revision: 1.25 $)[1];
use Kernel::Config::Defaults;
push (@ISA, 'Kernel::Config::Defaults');
1;

[crythias]

But if they are not editable, how could you customer info?

Think about your request. How do you gather information from multiple backends and expect to remember which field belongs to which backend and then how to write to that backend?

[ArielRivas]

Think about your request. How do you gather information from multiple backends and expect to remember which field belongs to which backend and then how to write to that backend?

I guess I am not explaining myself very well but it is a common practice to extract various data points from one user from different tables although obviously the mapping needs to have a one to one relationship between origin DB tables and OTRS variables. Hoewever, since you already indicated that OTRS did not permit to map data from mutiple tables for one user, I have created a Database VIEW as a work around to that problem so in essence OTRS is pulling only from one backend as per the posted Config.pm file.

But the real essence of the problem now is that why does the mapped information I have in the posted Config.pm not display in the Customeer Edit Screen.

That is, if I use the default OTRS tables, the data appears in the Customeer Edit Screen and can be edited without any issue, but if I use a backend, then it cannot. Therefore and given the last test I did yesterday, and everything else being equal, the problem can only be in the external backend Config.pm; but I am at a complete loss at what it could be.