[TYPO3] Seeking advice on how to use two databases...

[Tim Riemenschneider]

Daniel Smedegaard Buus schrieb:
> Peter Russ wrote:
> 
>> Daniel Smedegaard Buus schrieb:
>>> Francois Suter wrote:
>>>
>>>> Hi,
>>>>
>>>>> Okay, but would it be possible still to use the API that TYPO3
>>>>> provides, by
>>>>> having two instances of the DBAL for instance? Or would I have to do
>>>>> everything from scratch and lose all the easy good stuff like getting
>>>>> editors in the backend by simply using the extension kickstarter,
>>>>> etc.?
>>>> With DBAL you can connect transparently to several databases, at the
>>>> price of some performance cost (never tried it, but that's was
>>>> Karsten says anyway).
>>>>
>>> I've heard that, too, I'm just not so sure that "several databases" means
>>> several databases concurrently. As I understand it, it's more a question
>>> of freedom of choice? But I'm not positive. Anyway, if anyone has
>>> actually tried this, it'd be cool to get some experience reports :)
>>
>> Depends on the definition of DBs? TYPO3 DBs? Or TYPO3 and different on
>> MySql or Oracle or MSSql or PostGres ...
>>
>> For pure TYPO3 DBAL might be your favorite solution.
>> For a flexible mixture you might use t3lib_db functionality for TYPO3
>> and make use of ext adodb.
>>
> 
> Hi Peter :)

Well, I'm not Peter, but I answer anyway ;-)
> To the best of my knowledge, placing all these items in a separate database
> would sever all ties to TYPO3 and its lovely API and gift shop extensions,
> except for what I'd then code by hand in PHP using native PHP functions...
> Or what? I'd love to be told otherwise, and if you have any links to
> examples or documentation on doing (or simply understanding - you might
> notice, I'm not completely grasping this) this separation while keeping the
> advantages given by TYPO3 (otherwise, why use it?), then please please post
> them.
> 


By using the ext. dbal this should be possible.
While TYPO3 only supports one database, this means, that everything is 
in one "logical" DB. You can distribute the content into several "real" 
DBs on a per-table basis.
See: 
http://typo3.org/documentation/document-library/extension-manuals/dbal/current/view/1/1/

The only conditions is, that you can't use JOIN across DB-borders, which 
is easily understandable.
(Example:
Tables:
Person with Fields: id, name, worksFor
Company with: id: name

when using dbal (and different databases) with these, this does not work:
SELECT person.name, company.name FROM person,company WHERE 
person.worksFor == company.id
and in PHP: echo "Person ".$result['person.name']." works for 
".$result['company.name'];

since neither database knows of the other. (To which of the two 
databases should this query be send??)

As a workaround you can split this into two DB-queries, each can be send 
to the right DB:
q1: SELECT name,worksFor from person
q2: SELECT name from company where id==$q1[worksFor]
echo "Person ".$q1['name']." works for ".$q2['name'];

you get the idea.)

(Disclaimer: I haven't used dbal myself.... so don't blame me, if this 
doesn't work.....)

cu
Tim