@robocoder opened this Issue on May 20th 2010 Contributor

Users have requested Piwik support for non-MySQL databases, e.g., PostgreSQL (#500), MSSQL (#1335), and NoSQL (see mailing list).

Obstacles:

  • MySQL schema defined in core/Piwik.php
  • MySQL'ish SQL is embedded throughout Piwik (e.g., ON DUPLICATE, crc(), and MySQL-specific error codes)
  • inconstent use of wrappers (e.g., Piwik_Query()) vs direct calls, e.g., Zend_Registry::get('db')->query(), $db->insert(), or $db->update()
  • the "only MySQL is supported" policy which stems from (1) tracker performance and (2) ongoing testing/support
  • the NoSQL proposals for Zend_Framework are not Zend_Db-based

Considerations:

  • third-party plugins must be able to use the data access abstraction without us necessarily adding these third-party queries to core
  • while most of the non-MySQL databases have a one-to-one relationship between the adapter and schema, in future, we want to be able to support multiple storage engines for MySQL (e.g., MYISAM, Innodb, spider, Infinidb)
  • MariaDB is supposed to be a drop-in replacement, so this should be transparent to the MySQL adapters
  • drizzle is a stripped down MySQL fork; since it currently uses the MySQL port, perhaps it could be treated as a storage engine? (see #1296)
  • watch out for '_' used in tables prefix, as it matches any character in a LIKE expression

TODO:

  • refactor Piwik.php into core/Db/Schema/*
    • tableInsertBatch() in core/Piwik.php
  • installer support for multiple schemas
  • helper methods for plugins to ALTER existing tables, or CREATE/DROP tables
  • during install, add a warning explaining that only Mysql is supported, others are "experimental", when the user doesn't select mysql.
  • when enabling new plugins, check that they have the drivers for the current DB (mysql, mssql, postgresql)
    • if they don't, user can still enable it for his db, but with high risk that, if it is not implemented for MySQL, it might not work on your db.
  • refactor SQL into query classes/methods
    • in #1335, it is suggested that we implement the Repository pattern. Should Criteria correspond to an entity (table) or aggregate (tables involved in a JOIN)? Or should Criteria correspond to a query? (BTW we're not keen on the name "Criteria".)
    • abstract isErrNo
    • tracker queries should also be in core/Db/Query instead of core/Tracker/Db/Query; similarly, for core Piwik plugins
  • discourage direct use of Zend_Registry::get('db')
  • write many, many unit tests and phpdocs; see #818

Propose changing instances of:

Piwik_Query($sql, $optional_parameter_array);
// or
Zend_Registry::get('db')->query($sql, $optional_parameter_array);

to:

Piwik_Repository::execute('Db_QueryName_XYZ', $optional_parameter_array);

The $sql is moved into Query classes, defined in:

  • core/Db/Query/SchemaABC.php, or
  • core/Db/Query/SchemaABC/QueryName.php
  • core/Db/Query/SchemaABC/QueryName/XYZ.php

(Any of the above is acceptable and handled by the autoloader.)

Piwik_Repository::execute($queryName, $parameters) could be something like:

$schemaClassName = 'Piwik_Db_Query_' . Zend_Registry::get('schema');
$query = $schemaClassName::factory($queryName, $parameters);

$db = Zend_Registry::get('db');
return $db->query($query, $parameters);

The factory method returns an object or string (e.g., schema-specific SQL). Non-MySQL schemas can either:

  • subclass the MySQL MYISAM class and return its own SQL
  • leave it undefined; in which case, a mock object is used (default to MySQL implementation?)

The prefix on the query name tells the factory method how to construct the class name. A query name prefixed by Db is treated as a core query. A query name prefixed by the plugin's name, e.g., GeoIP refers to a query in the GeoIP plugin's Query folder.

Additional ideas:

@halfdan commented on May 20th 2010 Member

Considering that the 1.0 version jump will increase popularity of Piwik and more developers might start developing plugins, shouldn't we have a stable data access layer by then? (meaning: Shouldn't the target version be 1.0 instead of after 1.0?)

If you guys need help making these changes, I'm more than happy to help.

@robocoder commented on May 20th 2010 Contributor

Post-1.0 means it isn't on the 1.0 roadmap, and that 1.0's release doesn't depend on this feature. Any/all help is welcome since many have asked for this feature, and it currently isn't the highest priority on my plate.

I've glossed over some implementation details, so feel free to ask questions, request feedback on patches, or prod me on some task.

@robocoder commented on May 20th 2010 Contributor

We should also reconsider using Zend for the Tracker.

Pros:

  • 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
    Cons:
  • performance overhead; should benchmark the throughput; I suspect the overhead is negligible relative to the overall request time or time to execute SQL queries
@mattab commented on May 24th 2010 Owner

I moved some items from 'additional ideas' to TODO.

Side note: this Data access abstraction would make it easier to build sharding into Piwik, as we could have a Sharding schema, subclassing Mysql, that would modify queries to access the right shard when applicable.

@robocoder commented on June 3rd 2010 Contributor

(In [refs #1368 - hard-code the list of supported adapters since it seems unlikely we'll have adapters as plugins; no longer depends on globr() or hack from 1632)

@robocoder commented on June 3rd 2010 Contributor

(In [2265]) refs #1368 - Piwik::prefixTable() is deprecated

@robocoder commented on June 3rd 2010 Contributor

(In [2266]) refs #1368 - Piwik::prefixTable() is deprecated

@robocoder commented on June 3rd 2010 Contributor

(In [2267]) refs #1368 - remove deprecated functions: prefixTable (since 0.2.27), isPhpCliMode (since 0.4.4), and getMysqlVersion (since 0.4.4)

@robocoder commented on June 3rd 2010 Contributor

(In [2268]) refs #1368 - initial refactoring of MySQL schema methods from Piwik.php

@robocoder commented on June 5th 2010 Contributor

(In [2273]) refs #1368 - rename Piwik_Db_iAdapter to Piwik_Db_Adapter_Interface for consistency

@robocoder commented on June 5th 2010 Contributor

(In [2274]) refs #1368 - further refactoring of Piwik.php; initial abstraction of MySQL schema

@robocoder commented on June 5th 2010 Contributor

(In [2276]) refs #1368 - move low-level $dbInfos mangling to adapter

@robocoder commented on June 5th 2010 Contributor

(In [2277]) refs #1368

@robocoder commented on June 6th 2010 Contributor

(In [2278]) refs #1335, refs #1368 - check in Jason's Piwik_Db_Adapter_Pdo_Mssql class (excluding the schema and query methods being refactored elsewhere; edited to conform to coding style guidelines; note: a lot of the logic in getConnection() looks like it should be moved upstream to ZF (2.0 CTP1 related?) especially if CTP2 introduces breaking changes to the DSN

@robocoder commented on June 6th 2010 Contributor

(In [2279]) refs #1368 - preprocessing the SQL to be made obsolete by query classes/methods

@robocoder commented on June 6th 2010 Contributor

(In [2281]) refs #1368 - more refactoring and phpdocs

  • The abstraction of the DDL is more or less complete. I still have to implement isAvailable() in core/Db/Schema/Myisam.php.
  • The default schema will be MyISAM. The update script will add: schema=Myisam to the ![database] section
  • The schema object is lazy loaded.
@robocoder commented on June 7th 2010 Contributor

(In [2282]) refs #1368 - implemented isAvailable() and private method hasStorageEngine(); added Schema.loadSchema hook for Sharding plugin; more phpdocs

@robocoder commented on July 28th 2010 Contributor

(In [2739]) refs #1368 - use schema instead of adapter; in strict mode, update() can't be abstract

@robocoder commented on November 3rd 2010 Contributor
@mattab commented on February 17th 2011 Owner

Moving as it is more long term project

@mattab commented on April 28th 2011 Owner

This will definitely be worked on to some extent (most likely: tracking code, archiving code for performance optimization) in 1.x, but it will be fully implemented (eg. sites/users/goals SQL will stay as is).

@anonymous-piwik-user commented on May 6th 2011

Are there any updates?

Is it possible to get piwik at least tracking with mssql?

@robocoder commented on May 6th 2011 Contributor

dk-at-cabag: if there were updates, it would appear here in the trac log. This is a low priority enhancement. In terms of timeframe, I plan to work on this after the plugins repository is online.

re: mssql. You can try to port Jason's code (see #1335) to the latest Piwik release.

@robocoder commented on May 15th 2011 Contributor

(In [4690]) refs #1368 - refactoring the batch insert code

@robocoder commented on May 16th 2011 Contributor

(In [4693]) refs #1368

@robocoder commented on July 27th 2011 Contributor

See ticket #2593

  • NoSQL is out-of-scope for Piwik 1.x, but may be re-opened at a later time (#2592); anyone got a patch? ;)
  • the centralization of the Repository design pattern doesn't fit Piwik's plugin/modular architecture, so it's no longer being considered
This Issue was closed on July 27th 2011
Powered by GitHub Issue Mirror