Hi!
People have been wondering a lot about our database drivers recently. Two days ago I was asked specifically which ones we support. I think the subject needs clarifying, especially in light of the new installer and associated update refactoring. First and foremost, people need to remember that MediaWiki is written with MySQL in mind. It's the primary target, and can always be expected to work. That being said, I think that supporting other DBMSes is great and I'm glad we do.
Earlier today on IRC, I outlined what I consider to be the DBMSes we support and a rough criteria of why I think so. "Full support" means that the schema and DatabaseBase subclass should be fully functional. Patches should be written when schema changes occur so people can stay up to date. "Partial support" means that there is a functional DatabaseBase subclass and working schema. There may be some edge cases it doesn't support. Updaters probably aren't written. "Experimental" is anything less. Typically a half-implemented DatabaseBase subclass exists.
Given those criteria, I think that the following have "full support" in MediaWiki: * MySQL * SQLite * PostgreSQL
"Partial support": * Oracle (works, but lacks updates)
"Experimental" * MSSQL * DB2 * Informix?
When the new installer ships (hopefully) in 1.17, it will contain support for MySQL, SQLite and PostgreSQL. I'm in favor of adding Oracle in as well, as long as it's clearly labelled as still a work in progress.
As far as the "experimental" group go, I see no harm in leaving them in SVN. They are still mostly in development (some more active than others) and keeping the various subclasses around won't hurt anything. Once support gets a little more solid, then we can look to adding them to the installer (once they're done, it should just be a 1-line addition to Installer::$dbTypes, plus some extra i18n)
-Chad
On Fri, Sep 3, 2010 at 2:44 PM, Chad innocentkiller@gmail.com wrote:
Given those criteria, I think that the following have "full support" in MediaWiki:
- MySQL
- SQLite
- PostgreSQL
In practice, though, SQLite and PostgreSQL are more likely to break than MySQL, right? If so, we should make this clear in the installer UI. Or are they really about as well-supported as MySQL these days, minus a moderate lag in schema updates for pgsql?
Ideally, we could run test suites by default on all available DBs instead of just on the one the wiki currently uses. In particular, SQLite currently uses the same schema as MySQL and is available in PHP by default, plus it doesn't require any setup (providing admin login, etc.), so it would be great if we could run SQLite tests right now whenever people run tests. It would be great if people could set up pgsql to automatically run too, but that would require manual setup. This is the kind of thing automated tests are really helpful for. (But that's kind of tangential.)
On 03.09.2010, 23:19 Aryeh wrote:
SQLite currently uses the same schema as MySQL
Minus updates, which may be incompatible, and which people forget to add when they're adding updates to MySQL.
[...] it doesn't require any setup (providing admin login, etc.), so it would be great if we could run SQLite tests right now whenever people run tests.
I've been playing with automatic tests that verify the SQLite schema (proper conversion from MySQL, etc) and upgrades. These tests take advantage of SQLite's memory tables and therefore run conveniently fast to include them into standard unit tests. They also depend only on SQLite support and don't require any particular $wgDBtype. Of course, doing the same for other backends would be much harder. Will finish this work once my IRL deadline will be dealt with.
...By the way, upgrade tests grumble something about categories;)
On Fri, Sep 3, 2010 at 3:41 PM, Max Semenik maxsem.wiki@gmail.com wrote:
...By the way, upgrade tests grumble something about categories;)
You can't expect anyone to write upgrades for database backends they've never used before, let alone ones like Oracle or MSSQL that they might not even have access to. The only sane thing is to have an abstraction layer. Once we have that, there's no reason in principle that support for non-MySQL backends should ever have to break, so we can talk about running real non-MySQL sites off trunk, or running tests for all databases on every checkin. That would be a really nice step forward -- I imagine trunk is unusable for non-MySQL users as a general rule.
On 04/09/10 06:10, Aryeh Gregor wrote:
On Fri, Sep 3, 2010 at 3:41 PM, Max Semenik maxsem.wiki@gmail.com wrote:
...By the way, upgrade tests grumble something about categories;)
You can't expect anyone to write upgrades for database backends they've never used before, let alone ones like Oracle or MSSQL that they might not even have access to. The only sane thing is to have an abstraction layer. Once we have that, there's no reason in principle that support for non-MySQL backends should ever have to break, so we can talk about running real non-MySQL sites off trunk, or running tests for all databases on every checkin. That would be a really nice step forward -- I imagine trunk is unusable for non-MySQL users as a general rule.
It would be great if the maintainers of the less-commonly used DBMSes could write up development guides and put them in phase3/docs. These guides could describe relevant differences in SQL syntax compared to MySQL, and explain how to write schema files.
MSSQL, DB2 and Informix all have free (as in beer) versions available for download that we could use for testing:
http://www.microsoft.com/sqlserver/2005/en/us/express.aspx http://www-01.ibm.com/software/data/db2/express/ http://www-01.ibm.com/software/data/informix/developer-edition/
MSSQL only runs on Windows, which is a nuisance for many of us, but still feasible for pre-release testing.
-- Tim Starling
On Sun, Sep 5, 2010 at 22:56, Tim Starling tstarling@wikimedia.org wrote:
It would be great if the maintainers of the less-commonly used DBMSes could write up development guides and put them in phase3/docs. These guides could describe relevant differences in SQL syntax compared to MySQL, and explain how to write schema files.
I have a reference table for the MySQL, Postgres, and DB2 data types in the current schema on the wiki:
http://www.mediawiki.org/wiki/Manual:IBM_DB2#SQL_data_types
It's somewhat hard to predict what the important differences in SQL syntax would be. Is there anything specific that comes to mind? If not, I can just write MySQL/DB2 differences up as I encounter them
Would it be useful to link to the reference documentation for things like CREATE TABLE, ALTER TABLE, ALTER COLUMN, etc in the short term?
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ib...
Regards,
Leons Petrazickis http://lpetr.org/blog/
On Sun, Sep 5, 2010 at 10:56 PM, Tim Starling tstarling@wikimedia.org wrote:
It would be great if the maintainers of the less-commonly used DBMSes could write up development guides and put them in phase3/docs. These guides could describe relevant differences in SQL syntax compared to MySQL, and explain how to write schema files.
I got them started in r72533.
-Chad
On Fri, Sep 3, 2010 at 1:44 PM, Chad innocentkiller@gmail.com wrote:
Given those criteria, I think that the following have "full support" in MediaWiki:
- MySQL
- SQLite
- PostgreSQL
"Partial support":
- Oracle (works, but lacks updates)
"Experimental"
- MSSQL
- DB2
- Informix?
Postgres probably falls between partial and full. Pretty much everything major works, there just a few areas in seldom used features that dont work at all, or work sometimes. I also was going through the schema and noticed there was a decent disparity between table indexes on what is indexed and what the indexes cover wrt MySQL and Postgres.
On 04.09.2010, 0:33 OQ wrote:
I also was going through the schema and noticed there was a decent disparity between table indexes on what is indexed and what the indexes cover wrt MySQL and Postgres.
This is generally OK because different databases may use different strategies when executing the same query. Of course, this means case-by-case analysis for every discrepancy.
On Fri, Sep 3, 2010 at 4:33 PM, OQ overlordq@gmail.com wrote:
I also was going through the schema and noticed there was a decent disparity between table indexes on what is indexed and what the indexes cover wrt MySQL and Postgres.
This is almost certainly deliberate, no? PostgreSQL supports different types of indexes and can use them in different ways. For instance, it can filter using one index and sort using another, although not as efficiently as if it were one index.
On Fri, Sep 3, 2010 at 4:43 PM, Max Semenik maxsem.wiki@gmail.com wrote:
Most updates for MySQL will just work for SQLite. You can use php maintenance/sqlite.php --check-syntax <filename> to check it.
Error: SQLite support not found
Can't check SQL syntax: SQLite not found Backtrace: #0 /var/www/git-trunk/phase3/maintenance/sqlite.php(123): Sqlite::checkSqlSyntax(Array) #1 /var/www/git-trunk/phase3/maintenance/sqlite.php(48): SqliteMaintenance->checkSyntax() #2 /var/www/git-trunk/phase3/maintenance/doMaintenance.php(104): SqliteMaintenance->execute() #3 /var/www/git-trunk/phase3/maintenance/sqlite.php(133): require_once('/var/www/git-tr...') #4 {main}
Sigh, stupid distributors. Anyway, after installing php5-sqlite, both of the patches now fail because CHANGE isn't supported. I guess it would work if I just changed CHANGE to DROP then ADD? That would wipe out cl_sortkey's contents on MySQL, but I suppose it's not a big deal.
Anyway, if the patches usually work without modification, why doesn't SQLite just use the same patches as MySQL, with special-casing for exceptions? Even if the status quo were documented (it's not), it's not ideal to expect people who never use SQLite to go out of their way and take extra steps to keep it up-to-date.
On 04.09.2010, 0:59 Aryeh wrote:
Anyway, if the patches usually work without modification, why doesn't SQLite just use the same patches as MySQL, with special-casing for exceptions? Even if the status quo were documented (it's not), it's not ideal to expect people who never use SQLite to go out of their way and take extra steps to keep it up-to-date.
SQLite actually shares some patches with MySQL, and the whole approach is documented at http://www.mediawiki.org/wiki/Manual:Database_access#SQLite_compatibility
On Fri, Sep 3, 2010 at 5:05 PM, Max Semenik maxsem.wiki@gmail.com wrote:
SQLite actually shares some patches with MySQL, and the whole approach is documented at http://www.mediawiki.org/wiki/Manual:Database_access#SQLite_compatibility
Only adding one column per statement would be pretty terrible for MySQL, since it would require a ton of table rebuilds. So the patches will have to stay separate, and be maintained by the people concerned about the particular database, I guess.
Aryeh Gregor wrote:
On Fri, Sep 3, 2010 at 5:05 PM, Max Semenik maxsem.wiki@gmail.com wrote:
SQLite actually shares some patches with MySQL, and the whole approach is documented at http://www.mediawiki.org/wiki/Manual:Database_access#SQLite_compatibility
Only adding one column per statement would be pretty terrible for MySQL, since it would require a ton of table rebuilds. So the patches will have to stay separate, and be maintained by the people concerned about the particular database, I guess.
Oro it is changed to accept multicolumn ALTERs (that would require changing Database::sourceStream()...)
On Fri, Sep 3, 2010 at 6:39 PM, Platonides Platonides@gmail.com wrote:
Oro it is changed to accept multicolumn ALTERs (that would require changing Database::sourceStream()...)
Yeah, that occurred to me later. If that's the only real problem, we could fake an abstraction layer by just regexing the ALTERs. It wouldn't be very complicated, and we could always fall back to manual patches if that fails.
wikitech-l@lists.wikimedia.org