0

Since TYPO3 uses doctrine it is possible to use tables from multiple databases in one instance (with some restrictions like no joins).

But what is possible at all?

At the moment I need two external tables for an extension and instead of using them directly I import them to work locally as usual. But the importing has some draw backs.

Draw backs I can accept:

  • the data is not live (changes to the external tables are imported later)
  • the data is read only (changes are done externally anyway)

For importing I use ext:external_import but there are some problems as not all data can be imported in a single run, and then there are errors (e.g. there are reports about duplicate keys, alas there are no duplicate keys in the external tables)

On the other hand I doubt I can use the external tables directly as they have not the usual TYPO3 structure (fields: 'uid', 'pid', 'tstamp', ...). (Maybe they can be mapped in a view?) (of course in the tables I import the data into these fields exist)

Also external changes may be unnoticed and cached content does not reflect current data. In my case that would be a minor problem, as we currently already have no 'live' data, but this needs to be cleaned regularly for cache and for the search index (solr).

What are possible solutions? ? (do they depend on the TYPO3 version?) What are your experiences?

EDIT:

While trying to realize it considering the given answers more doubts appear:

  • the tables are readonly (as they are changed from outside):
    How do I declare it to TYPO3?
  • the tables does not follow the usual name rules, especially one table is named sys_category which in this way conflicts with the TYPO3 table sys_category.
    Can I build a mapping inside of TYPO3?
  • Can I build a view from TYPO3 for renaming tables and fields?
    like:
CREATE View tx_myext_category
SELECT id as uid, name as title, ...
  FROM databasename.sys_category;
Bernd Wilke πφ
  • 10,390
  • 1
  • 19
  • 38

2 Answers2

0

Yes, you can fetch data directly from other databases/tables. Of course it highly depends on the usecases and the data you get:

Georg Ringer
  • 7,779
  • 1
  • 16
  • 34
  • A representation in the BE is not necessary. all data should be shown in the FE. in a list view and detail view. the data in the first table represents a tree (parent pointer) and each record of the second table is assigned to one record in the first table. – Bernd Wilke πφ Apr 14 '21 at 19:03
  • As far as I understand the dcoumentation at least the columns `uid`and `pid` need to exist. no way around? – Bernd Wilke πφ Apr 15 '21 at 05:53
  • It really depends. if you use extbase repository, you need tca and those fields. if you just to raw queries and output that in fluid, everything will be fine – Georg Ringer Apr 15 '21 at 12:26
  • more questions appear: see edit in question – Bernd Wilke πφ Apr 16 '21 at 06:11
0

From my experience, the mapping mechanism only works if the external table has a almost similar structure as TYPO3 tables. You need at least a uid field on the external side. This cannot be mapped! A missing pid field could be managed with on the TYPO3 side, also crdate or tstamp if needed. Just fill the local data array with the values TYPO3 needs.

Problems arise if you have relations to deal with. Many external systems have other ways to handle relations. You could run into many problems if you try to rely only on the mapping mechanism.

Other problems are fields with date format. Most external tables in the MS world use another format as the unixtime.

If you run into problems with the mapping mechanism you can switch to the TYPO3 queryBuilder. This is a powerful fallback. I experienced problems only with a special type of JOIN statements.

But with the TYPO3 queryBuilder you are on your own. You place instances of the queryBuilder code in the repository and add your model code as usual: thus you can continue to work with Fluid in the frontend as you are used to.

ANSWER TO EDIT:

  • With the TYPO3 queryBuilder readonly tables aren't a problem. Just don't implement the setter classes in your models.
  • With TYPO3 queryBuilder you can call any external table with any name. You have full control over the output data in your repository because the mapping is handled inside of it.
  • As far as I know, there is no way to create SQL views in TYPO3 up to v9, neither with the DBAL mapping mechanism nor with the TYPO3. queryBuilder.
Stefan Padberg
  • 505
  • 2
  • 17