Database related stuff you might need to know when working with OXID

Wiki
,
1

There are several less known things you might struggle wirh, when trying to work with custom database tables and columns in OXID eshop.

collation

There are two different collations used in the database: latin1_general_ci and utf8_general_ci.
But why?
latin1_general_ci is used for internal ID columns, like OXID, oxloadid of cms pages, oxparentid of products, etc. Those are usually md5 hashes and do not contain any umlauts or special characters
utf8_general_ci is used for columns that store data for humans, is can contain all language specific characters like accents, umlauts and stuff like that.
When adding custom tables or columns for your modules, make sure to specify the collation in your sql queries, becase otherwise it would be the database’s default collation, which might be different from what you would expect.

ID columns / primary keys

OXID eShop uses an “OXID” char(32) column as primary key in it’s tables. For models inherited from BaseModel the shop framework will try to use OXID column as primary key and automatically generate oxIDs on save even if there is no “OXID” column in your table, so you will get some exceptions.
If your module comes with some predefined entities like payment methods, cms pages or categories, which are inserted into default tables during the module activation, you can have shorter hardcoded IDs for them if the table does not have oxshopid column (which means that this table does not contain data limited to specific subshop) or you know for sure, that your module will only be used with CE/PE shops, just like most payment modules do with payment methods (because payment methods are not limited to a particular subshop).

custom multilanguage columns in OXID’s tables

this is actually pretty easy: just name your columns like this: mycolumn_1, mycolumn_2, anothercolumn_1, anothercolumn_2 etc and OXID will automatically recognize them as multilanguage columns

custom multilanguage tables

OXID uses database views for loading content in requested language.
If you want to add multilanguage support to your custom models, you have to tell OXID, which of your custom tables need views.
There are 3 ways achieve that:

  1. extend \OxidEsales\Eshop\Core\Language in your module and overwrite the getMultiLangTables() method like this:
    public function getMultiLangTables() {
    return array_merge(parent::getMultiLangTables(),['mycustomtable']);
}
  2. add your table’s name to $this->aMultiLangTables parameter in config.inc.php
  3. add your table’s name to aMultiLangTables in oxconfig table, but make sure to check if it already exists and merge your table’s name into it. Best way to do this would be your module’s activation and deactivation events.
    $oConfig = \OxidEsales\Eshop\Core\Registry::getConfig();
$aMultiLangTables = $oConfig->getConfigParameter('aMultiLangTables')) ?? [];
$aMultiLangTables[] = 'mycustomtable';
$oConfig->saveShopConfVar('arr','aMultiLangTables',$aMultiLangTables, $oConfig->getShopId());
    also make sure to remove your table’s name the same way on module deactivation

timestamps

if you have a custom table for your models and you want timestamp or date columns with default values inserted by mysql, e.g. CURRENT_TIMESTAMP, you have to name your column “oxtimestamp”, otherwise OXID will try to save data into this field and your timestamps will allways be “0000-00-00 00:00:00”.
You also can add protected $_aSkipSaveFields = ['mytimestamp']; property to your model’s class, which tells OXID not to write any data into those fields.

2 Likes