Wiki : SupportedRDBMS
Documentation Home :: Categories :: Index :: Recent Changes :: Comments :: Search :: Help :: Login/RegisterSupported RDBMS
Up to .762 only MySQL was supported by PostNuke as database server. Beginning with .8 and the completed change-over to DbUtil basically all RDBMS supported by ADOdb can be used, at least in the core. 3rd party modules and blocks, especially old style module, will surely fail here.
Basically means that there are a lot of differences that currently need adjustments in the table initialization code of the core modules in order to run on MySQL, Oracle and Postgres at the same time.
MySQL
MySQL is our historically supported RDBMS. Use of the this database engine throughout the products history as caused some MySQL specfic database code to be used in the product. Part of the process of enabling true DB independence is isolating and correcting any MySQL specific code.
Integer Field Sizes
When first utilising ADODB's data dictionary representing integers using a mix of ADODB and MySQL field terminology slipped into the code. This has already been removed from all core code but 3rd party extensions may exist that that use this incorrect terminology.
Integers are represented under ADODB's data dictionary language using the values I1 to I4 for different sized integers. In MySQL integers are represented using tinyint(x), int(x) etc. where the optional x parameter is the field width (integers shorter than the field width are padded). In an earlier revision of the Example module (hence many 3rd party extensions) integer fields were represented as I(X).
Oracle?
Oracle support is being implemented right now and its far from being complete or even stable.
When using Oracle (tested with 10.2) and ADOdb 4.90 a small fix in the ADOdb code necessary:
includes/classes/adodb/data-dict/datadict-oci8.inc.php (line 78f)
case 'C2': return 'NVARCHAR2';
case 'X2': return 'NVARCHAR2(4000)';
case 'X2': return 'NVARCHAR2(4000)';
This will be included in the next ADOdb release
Known problems:
BLOBs / CLOBs
Some tables use BLOBs for storing large portions of data (e.g. sessions table). While this is not a problem with MySQL, it is one with Oracle. Using BLOBS or CLOBS need some severe workarounds. Using the above mentioned X2 (NVARCHAR2(4000)) instead will decrease the max. size from 65536 to just 4000 bytes. This *might* be a problem and needs some further tests.
Possible solution:: DBUtil must be aware of the fieldtypes (basically "is it a CLOB/BLOB or not?") and use different SELECT, INSERT and UPDATE functions in this case. It looks like there is no way to do this in a platform independent way so the best thing is to trap this in DBUtil. In order to achieve this it might be necessary to change the pntables.php and the resulting $pntable array containing the table information. The idea is to move the definitions of the database fields from pninit.php to pntables.php. However, the init process itself will remain in pninit.php in order to be 100% backwards compatible.
A 2nd draft of this structure (working on my dev box right now, soon to be committed to SVN, 100% backwards compatible):
$pntable['hooks_column'] = array ('id' => 'pn_id',
'object' => 'pn_object',
'action' => 'pn_action',
'smodule' => 'pn_smodule',
'stype' => 'pn_stype',
'tarea' => 'pn_tarea',
'tmodule' => 'pn_tmodule',
'ttype' => 'pn_ttype',
'tfunc' => 'pn_tfunc');
$pntable['hooks_column_def'] = array ('id' => 'I AUTO PRIMARY',
'object' => 'C(64) NOTNULL',
'action' => 'C(64) NOTNULL',
'smodule' => 'C(64)',
'stype' => 'C(64)',
'tarea' => 'C(64) NOTNULL',
'tmodule' => 'C(64) NOTNULL',
'ttype' => 'C(64) NOTNULL',
'tfunc' => 'C(64) NOTNULL');
// set additional indexes, maybe the module name itself is needed too, not sure yet
$pntable['hooks_column_idx'] = array('smodule' => 'smodule',
'smodule_tmodule' => array('smodule', 'tmodule'));
'object' => 'pn_object',
'action' => 'pn_action',
'smodule' => 'pn_smodule',
'stype' => 'pn_stype',
'tarea' => 'pn_tarea',
'tmodule' => 'pn_tmodule',
'ttype' => 'pn_ttype',
'tfunc' => 'pn_tfunc');
$pntable['hooks_column_def'] = array ('id' => 'I AUTO PRIMARY',
'object' => 'C(64) NOTNULL',
'action' => 'C(64) NOTNULL',
'smodule' => 'C(64)',
'stype' => 'C(64)',
'tarea' => 'C(64) NOTNULL',
'tmodule' => 'C(64) NOTNULL',
'ttype' => 'C(64) NOTNULL',
'tfunc' => 'C(64) NOTNULL');
// set additional indexes, maybe the module name itself is needed too, not sure yet
$pntable['hooks_column_idx'] = array('smodule' => 'smodule',
'smodule_tmodule' => array('smodule', 'tmodule'));
Length of object names
Object names may not be longer than 30 chars. Normally nothing to worry about, but Oracle does not know AUTOINCREMENT index fields. Therefore ADOdb adds a SEQUENCE (for counting) and a TRIGGER (to increase the SEQUENCE before an INSERT is done).
The SEQUENCE is
CREATE SEQUENCE SEQ_nuke_modules
and the TRIGGER is
CREATE OR REPLACE TRIGGER TRIG_SEQ_nuke_modules BEFORE INSERT ON nuke_modules FOR EACH ROW WHEN (NEW.pn_id IS NULL OR NEW.pn_id = 0) BEGIN SELECT SEQ_nuke_modules.NEXTVAL INTO :new.pn_id FROM dual; END;
So the object names for tables like modules_dependencies or group_applications exceed 30 chars (TRIG_SEQ_nuke_modules_dependencies or TRIG_SEQ_nuke_group_applications) when the table prefix is too long.
Solution: These table names will be shortened to max. 16 chars, in this case module_deps and group_appl, and the admin is requested to use table prefixes with a maximum length of 4 chars only. This ensures a maximum length of 30 chars.
Note: If you are using .8 MS1 on a production site - bad luck, you have been warned not to do this.
Duplicate index fields
Some core modules have more than one index on the same data field. MySQL ignores this (phpmyadmin show a warning, thats it), while Oracle stops with an error when the 2nd index is about to be added. This needs fixing, but can be done quite easily.
Default values and NOTNULL
Some default values (mainly datetime fields) and fields with NOTNULL need some checks.
Postgres
to do
other RDBMS
|
