Add created tables to ext_tables.sql

[ste101]

Please add the created tables to the file ext_tables.sql so that the database analyser will be happy.

[aimeos]

It's too much hassle to duplicate the schema for TYPO3 and keep that in sync. My hope is that we can convince the TYPO3 guys to leave non-TYPO3 tables untouched in their analyzer ...

[aimeos]

There's a request in the TYPO3 ticket system for this: https://forge.typo3.org/issues/33136

[AndreasA]

There is a possibility to hook into the expected sql schema service (SignalSlot) and dynamically append the correct "CREATE table" statements but i still think it is a bit of a hazzle. But if you are interested in implementing that feature: \TYPO3\CMS\Install\Service\SqlExpectedSchemaService::emitTablesDefinitionIsBeingBuiltSignal

[aimeos]

If it would accept DBAL table definitions, this would be an option. Otherwise, we still hope that TYPO3 fixes the problem finally

[AndreasA]

Hmm. Maybe but I don't think so. It basically has to be the CREATE TABLE structure that TYPO3 uses in ext_tables.sql

TYPO3 to fix this itself would be best though :)

[ghost]

I think the cleanest solution for the time being is to use another DB for the main resource domains of aimeos and just make an exception for 'db-customer'. Here's just an easy step-by-step workaround if you want to keep the Typo3 InstallTool happy:

In EXT:YOUR_aimeos_EXTENSION/Resources/Private/Config/resource.php:

return [
    'db' => [
        'adapter'        => 'mysql',
        'host'           => '...',
        'port'           => '...',
        'database'       => 'aimeos',
        'username'       => '...',
        'password'       => '...',
        'stmt'           => array( "SET NAMES 'utf8'", "SET SESSION sql_mode='ANSI'" ),
        'limit'          => 2,
        'opt-persistent' => 0,
    ],
    'db-customer' => [
        'adapter'        => 'mysql',
        'host'           => '...',
        'port'           => '...',
        'database'       => 'typo3',
        'username'       => '...',
        'password'       => '...',
        'stmt'           => array( "SET NAMES 'utf8'", "SET SESSION sql_mode='ANSI'" ),
        'limit'          => 2,
        'opt-persistent' => 0,
    ],
];

For the remaining tables that remain in Typo3 ('db-customers' aka fe_users) and fields we added this to our ext_tables.sql (it might need a bit of fiddling between 2016/2017 versions):

CREATE TABLE fe_users_address (
    id INT(11) NOT NULL auto_increment,
    siteid INT(11) default NULL,
    parentid INT(11) NOT NULL default NULL,
    company VARCHAR(100) NOT NULL default NULL,
    vatid VARCHAR(32) NOT NULL default NULL,
    salutation VARCHAR(8) NOT NULL default NULL,
    title VARCHAR(64) NOT NULL default NULL,
    firstname VARCHAR(64) NOT NULL default NULL,
    lastname VARCHAR(64) NOT NULL default NULL,
    address1 VARCHAR(255) NOT NULL default NULL,
    address2 VARCHAR(255) NOT NULL default NULL,
    address3 VARCHAR(255) NOT NULL default NULL,
    postal VARCHAR(16) NOT NULL default NULL,
    city VARCHAR(255) NOT NULL default NULL,
    state VARCHAR(255) NOT NULL default NULL,
    langid VARCHAR(5) default NULL,
    countryid CHAR(2) default NULL,
    telephone VARCHAR(32) NOT NULL default NULL,
    email VARCHAR(255) NOT NULL default NULL,
    telefax VARCHAR(255) NOT NULL default NULL,
    website VARCHAR(255) NOT NULL default NULL,
    flag INT(11) NOT NULL default NULL,
    pos SMALLINT(6) NOT NULL default NULL,
    mtime datetime NOT NULL default NULL,
    ctime datetime NOT NULL default NULL,
    editor VARCHAR(255) NOT NULL default NULL,
    longitude decimal(8,6) default NULL,
    latitude decimal(8,6) default NULL,

    PRIMARY KEY (id),

    KEY idx_t3feuad_address1 (address1),
    KEY idx_t3feuad_city (city),
    KEY idx_t3feuad_email (email),
    KEY idx_t3feuad_last_first (lastname,firstname),
    KEY idx_t3feuad_pid (parentid),
    KEY idx_t3feuad_post_addr1 (postal,address1),
    KEY idx_t3feuad_post_city (postal,city)
);

CREATE TABLE fe_users_list (
    id INT(11) NOT NULL auto_increment,
    typeid INT(11) NOT NULL default NULL,
    parentid INT(11) NOT NULL default NULL,
    siteid INT(11) NOT NULL default NULL,
    domain VARCHAR(32) NOT NULL default NULL,
    refid VARCHAR(32) NOT NULL default NULL,
    start datetime default NULL,
    end datetime default NULL,
    config text NOT NULL,
    pos INT(11) NOT NULL default NULL,
    status SMALLINT(6) NOT NULL default NULL,
    mtime datetime NOT NULL default NULL,
    ctime datetime NOT NULL default NULL,
    editor VARCHAR(255) NOT NULL default NULL,

    PRIMARY KEY (id),

    KEY IDX_921EAF9BE70B032 (typeid),
    KEY idx_t3feuli_pid_sid_end (parentid,siteid,end),
    KEY idx_t3feuli_pid_sid_pos (parentid,siteid,pos),
    KEY idx_t3feuli_pid_sid_rid_dom_tid (parentid,siteid,refid,domain,typeid),
    KEY idx_t3feuli_pid_sid_start (parentid,siteid,start),
    KEY idx_t3feuli_sid_stat_start_end (siteid,status,start,end),
    KEY unq_t3feuli_sid_dm_rid_tid_pid (siteid,domain,refid,typeid,parentid)
);

CREATE TABLE fe_users_list_type (
    id INT(11) NOT NULL auto_increment,
    siteid INT(11) NOT NULL default NULL,
    domain VARCHAR(32) NOT NULL default NULL,
    code VARCHAR(32) NOT NULL default NULL,
    label VARCHAR(255) NOT NULL default NULL,
    status SMALLINT(6) NOT NULL default NULL,
    mtime datetime NOT NULL default NULL,
    ctime datetime NOT NULL default NULL,
    editor VARCHAR(255) NOT NULL default NULL,

    PRIMARY KEY (id),

    KEY idx_t3feulity_sid_code (siteid,code),
    KEY idx_t3feulity_sid_label (siteid,label),
    KEY idx_t3feulity_sid_status (siteid,status),
    KEY unq_t3feulity_sid_dom_code (siteid,domain,code)
);

Documentation: https://aimeos.org/docs/Developers/Several_databases

[aimeos]

The situation has improved a bit as the Aimeos table definitions are now directly fed into TYPO3 and the install tool doesn't want to remove all tables any more.

Unfortunately, the allowed definitions of ext_tables.sql are very limited and it doesn't support CHAR columns and foreign key constraints. As a result, it suggests to change CHAR to VARCHAR and wants to drop the foreign key constraints. The CHAR issue is a minor one but dropping the foreign key constraints is a really bad thing.

We've talked to several TYPO3 core devs and the situation might improve in TYPO3 10 if the table Doctrine DBAL definitions can be passed to TYPO3 directly without the conversion to the ext_tables.sql format.

[aimeos]

Problem has been worked around by a few modifiactions in Aimeos database structure. The install tool shows no problems any more when Aimeos extension is installed