Multiple database and storage engine support

[robocoder]

This ticket narrows the scope of changes contemplated in #1368:

• Zend_Db supported adapters
  • e.g., Postgresql and Mssql
  • NoSQL is out of scope for Piwik 1.x
• MySQL storage engines
  • e.g., InnoDB
  • possibility of subclassing would allow a Sharding schema
• Use Zend_Db for the tracker
  • eliminates some redundancy, eg core/Tracker/Config and core/Tracker/Db
  • increases cohesiveness of core/Db and lowers the learning curve for new devs
  • eliminate more require_once in favor of the auto loader everywhere

ToDo:

• Installation
  • dropdown for schemas
• Piwik.php:
  • move MySQL-specific bulk loader to Schema/Myisam.php; leave a wrapper behind
• better support for users with the resources to run db clusters:
  • add Db::factory($dbConfigName); see read vs write, tracker vs reports
  • always use Zend_Registry::get($dbConfigName)->methodName() and remove the plugin helper functions: Piwik_Exec, Piwik_FetchAll, Piwik_FetchRow, Piwik_FetchOne, and Piwik_Query
• (vipsoft: further scope analysis of current code in trunk)

[anonymous-matomo-user]

How does MSSQL support currently look like?

[robocoder]

dk: there's a stub adapter; it's not functional yet.

[robocoder]

Proposed dbConfigNames:

• 'log-master' will primarily be used by the tracker
• 'log' and 'archive-master' will be used by archive processing
• 'archive' will be used by reports
• 'piwik' and 'piwik-master' are Piwik's other tables (eg option, session, sites, users, etc)

Alternate naming scheme: log_r, log_w (or log_rw)

This compat-buster may break some third-party plugins. To mitigate:

• the existing "database" config is copied to the other configs
• 'db' could be an alias for 'piwik-master'
• cursory inspection of the known 3rd party plugins

[robocoder]

Note: For role-based deployment, we need a config file setting that tells the server what role(s) it serves. We won't be carving up the codebase by role (eg "oh, these files are only used by the tracker, etc")

[mattab]

Moving to feature requests, but of course we can keep working on it, I just don't think it will be ready for 2.0... As long as there is no team member keen to build & support another sql driver, then it is unfortunately/ realistically not going to happen, so if you are interested (and have a lot of time :), please let us know here!

[robocoder]

Dropping the helper functions would definitely have to be post 1.x (for BC).

Another naming convention I came across is -bp for batch processing (e.g., long running reports).

[anonymous-matomo-user]

How are chances that this feature will be ready in 1.x? Any updates?

[mattab]

We will probably not work on this in 1.x - we are however planning to work on nosql support and do some experiments, maybe with mongoDB...

[robocoder]

This is certainly something I'd like to work on but depends on how long the 1.x development cycle is and of course, my availability.

[anonymous-matomo-user]

hi vipsoft

in your opinion, is there any chance that i could use piwik with mssql for a customer next year?

[robocoder]

dk: there's a chance, but not highly probable on the current horizon; how urgent is this? are you able to deploy with mysql initially (e.g., for evaluation, and migrate to mssql later)?

[robocoder]

Patch to load plugin-based db adapter, e.g.,

Index: Tracker.php
===================================================================
--- Tracker.php (revision 5686)
+++ Tracker.php (working copy)
@@ -207,6 +207,17 @@
            case 'MYSQLI':
                require_once PIWIK_INCLUDE_PATH .'/core/Tracker/Db/Mysqli.php';
                return new Piwik_Tracker_Db_Mysqli($configDb);
+
+           default:
+               $name = ucwords(str_replace('_', ' ', $configDb['adapter']));
+               $pluginName = str_replace(' ', '', $name);
+               $pathName = PIWIK_INCLUDE_PATH . '/plugins/' . $pluginName . '/Tracker/Db/' . str_replace(' ', '/', $name) . '.php';
+               $className = 'Piwik_' . $pluginName . '_Tracker_Db_' . str_replace(' ', '_', $name);
+               if (file_exists($pathName))
+               {
+                   require_once $pathName;
+                   return new $className($configDb);
+               }
        }

        throw new Exception('Unsupported database adapter '.$configDb['adapter']);

[robocoder]

Attachment: Sqlrelay - Tracker adapter
tracker_Sqlrelay.php

[anonymous-matomo-user]

thank you vipsoft. we will try that one out.
it's not that urgent, but still important.
we setup piwik with mysql now but still need to move to mssql.
how about performance? any experiences with that? any issues?

[anonymous-matomo-user]

A few questions regarding multi database support :

• Would a project like Doctrine DAL (http://www.doctrine-project.org/projects/dbal.html) be a credible option to implement this ?
• If someone is volonteering for this bounty would it be possible to implement this on 1.x, 2.0 or would it be postponed to further releases ?