What is the best way of using information from another database using symfony

Question

I have a management application being build in Symfony, I require to fetch information from other application, Genesys which is an enterprise software to handle various types of communications from phone to online chat help, the database for Genesys historical data is object oriented and easy to use, but I wonder what would be the best way to interact with it, my Symfony project already has the connection set up, however I am not certain if I should just get information using queries or to create and map objects according to Genesys design, and an SDK or library is out of the question since Genesys only provides such software for Java and Microsoft .Net.

config.yml

# Doctrine Configuration
doctrine:
    dbal:
         default_connection: cscc
        connections:
            cscc:
                driver:   pdo_mysql
                host:     "%database_host%"
                port:     "%database_port%"
                dbname:   "%database_name%"
                user:     "%database_user%"
                password: "%database_password%"
                charset:  UTF8                
            infomart:
                driver:   pdo_sqlsrv
                host:     '%database_host2%'
                port:     '%database_port2%'
                dbname:   '%database_name2%'
                user:     '%database_user2%'
                password: '%database_password2%'
                charset:  UTF8       
             wfm:
                driver:   pdo_sqlsrv
                host:     '%database_host3%'
                port:     '%database_port3%'
                dbname:   '%database_name3%'
                user:     '%database_user3%'
                password: '%database_password3%'
                charset:  UTF8

Consider the following: the type of queries I'll be required to use in a pure sql approach are kind of complicated, and long

Select DISTINCT INTERACTION_FACT_GI2.START_TS_TIME as cal_date
,RESOURCE_GI2.EMPLOYEE_ID as Windows_User
,RESOURCE_GI2.RESOURCE_NAME Ignition_ID
,RESOURCE_GI2.AGENT_FIRST_NAME
,RESOURCE_GI2.AGENT_LAST_NAME
,INTERACTION_FACT_GI2.SOURCE_ADDRESS as Phone
,INTERACTION_FACT_GI2.TARGET_ADDRESS
,TicketType
,AgentComment
,ItemCategory
,Problem
,Solution
,storeID
,LoyaltyNumber
,OrderNumber
 FROM [IRF_USER_DATA_CUST_1] 
 JOIN INTERACTION_RESOURCE_FACT_GI2 ON      (INTERACTION_RESOURCE_FACT_GI2.INTERACTION_RESOURCE_ID=IRF_USER_DATA_CUST_1.INTERACTION_RESOURCE_ID)
  JOIN INTERACTION_FACT_GI2 on (INTERACTION_FACT_GI2.INTERACTION_ID=INTERACTION_RESOURCE_FACT_GI2.INTERACTION_ID)
  JOIN DATE_TIME ON (DATE_TIME.DATE_TIME_KEY=INTERACTION_FACT_GI2.START_DATE_TIME_KEY)
  JOIN MEDIA_TYPE ON (MEDIA_TYPE.MEDIA_TYPE_KEY=INTERACTION_RESOURCE_FACT_GI2.MEDIA_TYPE_KEY)
  JOIN RESOURCE_GI2 ON (INTERACTION_RESOURCE_FACT_GI2.RESOURCE_KEY=RESOURCE_GI2.RESOURCE_KEY)
  JOIN [INTERACTION_TYPE] ON [INTERACTION_TYPE].[INTERACTION_TYPE_KEY] = INTERACTION_FACT_GI2.INTERACTION_TYPE_KEY

 Where [IRF_USER_DATA_CUST_1].TICKETTYPE =''
  AND [IRF_USER_DATA_CUST_1].TICKETTYPE IS NOT NULL
  AND MEDIA_TYPE.MEDIA_NAME IN ('Voice','Email')
  AND [INTERACTION_TYPE].INTERACTION_TYPE= 'Inbound'
  AND INTERACTION_FACT_GI2.START_TS_TIME  > '2017-01-26 00:00:00' and  INTERACTION_FACT_GI2.START_TS_TIME < '2017-01-27 23:59:59'

  order by INTERACTION_FACT_GI2.START_TS_TIME

What do you mean "my Symfony project already has the connection set up"? Does that mean you have 2 database Doctrine `connections` defined, the second one being the Genesys database connection? I use something like this (2 databases), so I'm trying to understand what you have and how I can give you a simple solution. — Alvin Bunk, Mar 02 '17 at 16:52
Yes I actually have 3 database connections in total for doctrine defined and Genesys has about 11 different databases, but so far my requirements can be satisfied with the information from the two main ones and my application's own database. — Julio Villalba, Mar 02 '17 at 17:09
Ok, can you please show your `config.yml` file, in particular the `doctrine: dbal: connections:` section. — Alvin Bunk, Mar 02 '17 at 17:11

gskema · Accepted Answer · 2017-03-02T18:23:28.763

1

You should definitely create and map objects to Genesys database. Generally, you want to push away vendor (Genesys in this case) related syntax and interactions away from the website business code.

To do this, create a UserRepository (and other repositories, depending on what objects you manage), which internally uses GenesysClient. In GenesysClient you can write the Genesys specific queries.

This way your code is decoupled from Genesys, is more readable, necessary changes can be made inside GenesysClient if needed.

edited Mar 02 '17 at 18:23

answered Mar 02 '17 at 14:53

gskema

3,141
2
20
39

I really won't be managing any kind of authentication from genesys so no real need for users other than reporting needs, actually the whole purpose of the connection would be to be able to report Call, email and other KPIs directly from the symfony project – Julio Villalba Mar 02 '17 at 15:56
do you also think in the terms of keeping it separated from the main business code, should I isolate all genesys related code to a separate bundle as well – Julio Villalba Mar 02 '17 at 15:57
@JulioVillalba I'm just using `Users` as an example. I'm sure that you have other objects that you need to manage in your Symfony code (Call, Email, Etc.). I'd definitely create entities (models) for such objects. You can make a separate bundle or just a separate namespaced folder with the classes, the important part is that you need to define the Genesys client as a service and the inject it to your entity manager (repository). Then use your the repository wherever you need (e.g. controllers, forms, lists, etc.) – gskema Mar 02 '17 at 18:26

score 0 · Answer 2 · answered Mar 02 '17 at 17:48

0

So let's say you had to connect to your infomart database connection, and you needed to get $someID which represents a PERSON in the PERSON table; you would create a connection and run a query like so:

$someID = ...
...

$infomart = $this->get('doctrine.dbal.infomart_connection');

$query = "SELECT P.NAME,P.DATE,S.USER
          FROM PERSON P
          WHERE P.ID = '".$someID."'";
$result = $infomart->fetchAll( $query );

You'll need to still figure out the proper SQL query that you need to run on your MS SQL Server, but then there is no dependency on any other Bundle or API. I do something similar with an Oracle database.

answered Mar 02 '17 at 17:48

Alvin Bunk

7,621
3
29
45

would this be the better approach in terms of efficiency and maintainability even if the queries may be required to be long and difficult to handle without any type of object in php to put it on? – Julio Villalba Mar 02 '17 at 18:01
I can't imagine you making various Entities in Symfony to represent all your objects in the Genesys database and then having to `store` all that data. The other issue, is you are storing the data in two places, in Genesys and in Symfony. I had to make the same choice, whether I would map in Entities our Oracle Banner database (which has millions of records) or to instead just query the database. As you can imagine, it's extremely simple to query it. – Alvin Bunk Mar 02 '17 at 18:06

What is the best way of using information from another database using symfony

2 Answers2