Re: [Wikitech-l] Better non-MySQL db support

[Petr Bena]

I would be happy to install some mediawiki on oracle db, but I have no
oracle db on any of my personal servers :/

The main problem of oracle is that it's not very much free - thus it's
not packaged by most of linux vendors and it might be hard to install
for many sysadmins. (In order to install oracle, you don't just need a
system that meets all the technical parameters, but also good
understanding of response files used by their universal installer).

From my experience, installing oracle db on debian or ubuntu is pretty
complicated (installation on oracle linux and such is not that hard
though - it comes with exact version of packages that oracle
requires).


However - it's not a freeware and that automatically makes it very
unpopular for open source OS vendors and very unlikely a choice of
standard webadmin and that makes it hard to test. Even if I was able
to obtain some license to install oracle on any of my servers, I
wouldn't do it as it eats too much resources. On other hand if oracle
granted some license to wikimedia, we could set up a project on
wikimedia labs for this purpose.

On Mon, Feb 25, 2013 at 4:27 PM, Mark A. Hershberger  wrote:
> How can we improve the support for databases like PostgreSQL, Oracle,
> DB2 and MS SQL?
>
> Getting Jenkins involved in testing isn't the (only) answer, though it
> would certainly help.
>
> If developers who were interested in those databases could watch
> includes/db, that would help, as well.
>
> If nothing else, I will make an effort to get the developers for those
> databases involved in RC testing and make the RC available a month
> before release.
>
> --
> http://hexmode.com/
>
> There is no path to peace. Peace is the path.
>-- Mahatma Gandhi, "Non-Violence in Peace and War"
>
> ___
> Wikitech-l mailing list
> Wikitech-l@lists.wikimedia.org
> https://lists.wikimedia.org/mailman/listinfo/wikitech-l

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[OQ]

On Mon, Feb 25, 2013 at 9:27 AM, Mark A. Hershberger  wrote:
> How can we improve the support for databases like PostgreSQL, Oracle,
> DB2 and MS SQL?
>

The main issues arise from not keeping these other DBs in mind when
writing the queries (and also not using the db layer).

Things like quoting timestamps, more strict adherence to the SQL
spec(s), stricter data type checking, etc.

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Chad]

On Mon, Feb 25, 2013 at 7:27 AM, Mark A. Hershberger  wrote:
> Getting Jenkins involved in testing isn't the (only) answer, though it
> would certainly help.
>
> If developers who were interested in those databases could watch
> includes/db, that would help, as well.
>

The latter is the real problem here. We don't have any people
who are dedicated to supporting these. People show up, say
they want to work on supporting these, then disappear.

Covering all the non-mysql/sqlite we "support":
- DB2 has been unmaintained for ages, and personally I'm in favor
of dropping that one altogether.
- MSSQL would be nice to improve.
- Oracle support's not bad (maybe not perfect), freakolowsy would
know more.
- Postgres support needs major work. There's a lot of inconsistencies
between PG and the other backends (especially for install/upgrade).
There *are* people here who care about PG.

-Chad

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Mark A. Hershberger]

On 02/25/2013 10:40 AM, Chad wrote:
> Covering all the non-mysql/sqlite we "support":
> - DB2 has been unmaintained for ages, and personally I'm in favor
> of dropping that one altogether.
> - MSSQL would be nice to improve.
> - Oracle support's not bad (maybe not perfect), freakolowsy would
> know more.
> - Postgres support needs major work. There's a lot of inconsistencies
> between PG and the other backends (especially for install/upgrade).
> There *are* people here who care about PG.

This pretty much mirrors my thinking as well.

Microsoft has been very helpful in the past year by funding some of the
Azure support and MSSQL seems like a natural fit there.  I think we
could get an instance on Azure to help test if we could fit that into
Jenkins somehow.

Other than that, I can only add my +1 to dropping DB2 support.  I don't
know of it being used anywhere.

-- 
http://hexmode.com/

There is no path to peace. Peace is the path.
   -- Mahatma Gandhi, "Non-Violence in Peace and War"

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Danny Joe Bauch]

I'm still alive and willing to continue to work on MSSQL.


On Mon, Feb 25, 2013 at 9:40 AM, Chad  wrote:

> On Mon, Feb 25, 2013 at 7:27 AM, Mark A. Hershberger 
> wrote:
> > Getting Jenkins involved in testing isn't the (only) answer, though it
> > would certainly help.
> >
> > If developers who were interested in those databases could watch
> > includes/db, that would help, as well.
> >
>
> The latter is the real problem here. We don't have any people
> who are dedicated to supporting these. People show up, say
> they want to work on supporting these, then disappear.
>
> Covering all the non-mysql/sqlite we "support":
> - DB2 has been unmaintained for ages, and personally I'm in favor
> of dropping that one altogether.
> - MSSQL would be nice to improve.
> - Oracle support's not bad (maybe not perfect), freakolowsy would
> know more.
> - Postgres support needs major work. There's a lot of inconsistencies
> between PG and the other backends (especially for install/upgrade).
> There *are* people here who care about PG.
>
> -Chad
>
> ___
> Wikitech-l mailing list
> Wikitech-l@lists.wikimedia.org
> https://lists.wikimedia.org/mailman/listinfo/wikitech-l
>
___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Mark A. Hershberger]

On 02/25/2013 10:52 AM, Danny Joe Bauch wrote:
> I'm still alive and willing to continue to work on MSSQL.

Have you tried to run MediaWiki's latest from git against MSSQL?


-- 
http://hexmode.com/

There is no path to peace. Peace is the path.
   -- Mahatma Gandhi, "Non-Violence in Peace and War"

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Danny Joe Bauch]

No. The last version I got running was 1.19, and have not worked on any
since. I could do as you suggest, though.


On Mon, Feb 25, 2013 at 9:54 AM, Mark A. Hershberger wrote:

> On 02/25/2013 10:52 AM, Danny Joe Bauch wrote:
> > I'm still alive and willing to continue to work on MSSQL.
>
> Have you tried to run MediaWiki's latest from git against MSSQL?
>
>
> --
> http://hexmode.com/
>
> There is no path to peace. Peace is the path.
>-- Mahatma Gandhi, "Non-Violence in Peace and War"
>
> ___
> Wikitech-l mailing list
> Wikitech-l@lists.wikimedia.org
> https://lists.wikimedia.org/mailman/listinfo/wikitech-l
>
___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Petr Bena]

It would be best to have automated environment for this, either on
labs or somewhere else. Problem is that it's not possible to install
MSSQL and such on wikimedia labs given the restrictions

On Mon, Feb 25, 2013 at 5:36 PM, Danny Joe Bauch  wrote:
> No. The last version I got running was 1.19, and have not worked on any
> since. I could do as you suggest, though.
>
>
> On Mon, Feb 25, 2013 at 9:54 AM, Mark A. Hershberger 
> wrote:
>
>> On 02/25/2013 10:52 AM, Danny Joe Bauch wrote:
>> > I'm still alive and willing to continue to work on MSSQL.
>>
>> Have you tried to run MediaWiki's latest from git against MSSQL?
>>
>>
>> --
>> http://hexmode.com/
>>
>> There is no path to peace. Peace is the path.
>>-- Mahatma Gandhi, "Non-Violence in Peace and War"
>>
>> ___
>> Wikitech-l mailing list
>> Wikitech-l@lists.wikimedia.org
>> https://lists.wikimedia.org/mailman/listinfo/wikitech-l
>>
> ___
> Wikitech-l mailing list
> Wikitech-l@lists.wikimedia.org
> https://lists.wikimedia.org/mailman/listinfo/wikitech-l

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Mark A. Hershberger]

On 02/25/2013 11:43 AM, Petr Bena wrote:
> It would be best to have automated environment for this, either on
> labs or somewhere else. Problem is that it's not possible to install
> MSSQL and such on wikimedia labs given the restrictions

Which is why I suggested that we work with someone at MS to get an
instance on Azure for testing.


-- 
http://hexmode.com/

There is no path to peace. Peace is the path.
   -- Mahatma Gandhi, "Non-Violence in Peace and War"

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Danny Joe Bauch]

Microsoft was very kind to provide me with an Azure account which I used to
do the testing of 1.18 and 1.19. I've not been in touch with them lately,
nor have I checked whether that account is still open. I would gladly jump
back on that. I'm afraid I got a bit distracted when my job disappeared out
from under me, but I'm back at work at a new job and eager to jump back in
on MSSQL and Azure support.


On Mon, Feb 25, 2013 at 11:04 AM, Mark A. Hershberger wrote:

> On 02/25/2013 11:43 AM, Petr Bena wrote:
> > It would be best to have automated environment for this, either on
> > labs or somewhere else. Problem is that it's not possible to install
> > MSSQL and such on wikimedia labs given the restrictions
>
> Which is why I suggested that we work with someone at MS to get an
> instance on Azure for testing.
>
>
> --
> http://hexmode.com/
>
> There is no path to peace. Peace is the path.
>-- Mahatma Gandhi, "Non-Violence in Peace and War"
>
> ___
> Wikitech-l mailing list
> Wikitech-l@lists.wikimedia.org
> https://lists.wikimedia.org/mailman/listinfo/wikitech-l
>
___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Yuri Astrakhan]

There are some bugs that also prevents accurate unit testing on multiple
backends (these are the ones I hit personally):

* *Bug 37702*  - Cloned
tables for unittests do not have references and constraints
* *Bug 44790*  - Sqlite
category table contains duplicates in unit tests





On Mon, Feb 25, 2013 at 12:04 PM, Mark A. Hershberger wrote:

> On 02/25/2013 11:43 AM, Petr Bena wrote:
> > It would be best to have automated environment for this, either on
> > labs or somewhere else. Problem is that it's not possible to install
> > MSSQL and such on wikimedia labs given the restrictions
>
> Which is why I suggested that we work with someone at MS to get an
> instance on Azure for testing.
>
>
> --
> http://hexmode.com/
>
> There is no path to peace. Peace is the path.
>-- Mahatma Gandhi, "Non-Violence in Peace and War"
>
> ___
> Wikitech-l mailing list
> Wikitech-l@lists.wikimedia.org
> https://lists.wikimedia.org/mailman/listinfo/wikitech-l
>
___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Sumana Harihareswara]

On 02/25/2013 07:40 AM, Chad wrote:
> On Mon, Feb 25, 2013 at 7:27 AM, Mark A. Hershberger  
> wrote:
>> Getting Jenkins involved in testing isn't the (only) answer, though it
>> would certainly help.
>>
>> If developers who were interested in those databases could watch
>> includes/db, that would help, as well.
>>
> 
> The latter is the real problem here. We don't have any people
> who are dedicated to supporting these. People show up, say
> they want to work on supporting these, then disappear.
> 
> Covering all the non-mysql/sqlite we "support":
> - DB2 has been unmaintained for ages, and personally I'm in favor
> of dropping that one altogether.
> - MSSQL would be nice to improve.
> - Oracle support's not bad (maybe not perfect), freakolowsy would
> know more.
> - Postgres support needs major work. There's a lot of inconsistencies
> between PG and the other backends (especially for install/upgrade).
> There *are* people here who care about PG.
> 
> -Chad

Yeah.  Just wanted to point people to prior roundups on this topic --
see
http://thread.gmane.org/gmane.science.linguistics.wikipedia.technical/56384
&
https://www.mediawiki.org/wiki/Bug_management/Triage/Databases_2002
for some people Mark could reach out to to ask for testing & development
help, and for this summary of what needed doing as of November 2011:

> How you can help MediaWiki administrators who don't use MySQL:
>
> * Write tests or specs.  Ben and DJ Bauch want to work on better unit
> testing per https://bugzilla.wikimedia.org/show_bug.cgi?id=32118
> (special page SQL queries), and could use specifications to test
> against.  Improve https://www.mediawiki.org/wiki/Database_testing .

The question: what do we need to test more often to keep RDBMSes happy?
 https://www.mediawiki.org/wiki/Database_testing &
https://www.mediawiki.org/wiki/New_installer/Test_plan Permissions,
searching, schema setup, schema changes, quoting & identifiers, weird
page names, transactions, import, dump, & interface coverage seem like
the main culprits.

And I think we can mostly agree that MySQL/MariaDB (InnoDB & secondarily
MyISAM), SQLite, and PostgreSQL are higher priority than SQL Server/SQL
Server Express, Oracle, and DB2, although others might volunteer as
maintainers and get some of that switched around. :-)

> * Try to reproduce this installation failure on SQLite or PostgreSQL:
> https://bugzilla.wikimedia.org/show_bug.cgi?id=28172

Now fixed.

> * Fix "Database layer should automagically add GROUP BY columns on
> backends that need them (postgres)"
> https://bugzilla.wikimedia.org/show_bug.cgi?id=26273 .  This is a large
> project.

Still needs fixing.

> * Make a meta-schema so that we no longer use tables.sql as a canonical
> source.  Chad and Max started in
> http://svn.wikimedia.org/viewvc/mediawiki/branches/abstract-schema/ .
> See
> https://www.mediawiki.org/wiki/Bug_management/Triage/Databases_2002#Ideas:
> for more discussion.  This is a large project.

Where's this branch now, if it's still useful?

Thanks for working on this, Mark!

-- 
Sumana Harihareswara
Engineering Community Manager
Wikimedia Foundation

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Chad]

On Mon, Feb 25, 2013 at 7:48 AM, Mark A. Hershberger  wrote:
> Other than that, I can only add my +1 to dropping DB2 support.  I don't
> know of it being used anywhere.
>

https://gerrit.wikimedia.org/r/#/c/50764/

-Chad

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Chad]

On Mon, Feb 25, 2013 at 9:53 AM, Sumana Harihareswara
 wrote:
>> * Make a meta-schema so that we no longer use tables.sql as a canonical
>> source.  Chad and Max started in
>> http://svn.wikimedia.org/viewvc/mediawiki/branches/abstract-schema/ .
>> See
>> https://www.mediawiki.org/wiki/Bug_management/Triage/Databases_2002#Ideas:
>> for more discussion.  This is a large project.
>
> Where's this branch now, if it's still useful?
>

Still in SVN, we never migrated it. It worked as far as installing,
but we never got to the upgrade point. Worth dusting off, but no
way it'll merge cleanly right now.

-Chad

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Freako F. Freakolowsky]

I'm still active on Oracle front, but as you might have noticed i have 
been [WARNING ... understatement ahead] a bit busy in the last 1,5 year 
so i just can't manage to pay as much attention to what's going on as i 
would like to ...


I used to get paid by my company to do this, but due to some budget cuts 
and some other projects that keep me busy at the office and over the 
weekends i'm now maintaining this in the little i have left of my own 
spare time. I'm still trying to stay up to date, but i just can't manage 
to stay involved in the actual development and the comunity as i used 
to, but if anyone needs my help with testing and fixing bugs in my 
department, my mailbox is always open. Because of this lack of time on 
my part i can't keep track of all the changes being made and i would 
appretiate it if somone could drop me a line if there are oracle 
specific issues ... as i'm doing a lot of DBA work i've got access to 
Oracle installations of all shapes and sizes, so i can test on almost 
any Oracle version.


As to the current state ... in the last 6 months most of the changes to 
the code just was keeping up to date with schema changes ... no major 
issues so i'm quite satisfied with the current state, i still wouldn't 
suggest it to anyone who would expect it to just plug and play, but if 
the admin has any DBA-fu moves up his sleeve it performs nicely. I'm 
planning get back into the game and do some development in a month or so 
(when i finish one of my other projects) to bring some of the extensions 
i've developed for my company and my clients into git and update my 
farms to a more recent version (i'm still running those farms on 1.17) ...



... so ... yeah ... i'm still alive and kicking here ... glad to see ppl 
still remember i exist :D


LP, Jure


On 25. 02. 2013 16:40, Chad wrote:

On Mon, Feb 25, 2013 at 7:27 AM, Mark A. Hershberger  wrote:

Getting Jenkins involved in testing isn't the (only) answer, though it
would certainly help.

If developers who were interested in those databases could watch
includes/db, that would help, as well.


The latter is the real problem here. We don't have any people
who are dedicated to supporting these. People show up, say
they want to work on supporting these, then disappear.

Covering all the non-mysql/sqlite we "support":
- DB2 has been unmaintained for ages, and personally I'm in favor
of dropping that one altogether.
- MSSQL would be nice to improve.
- Oracle support's not bad (maybe not perfect), freakolowsy would
know more.
- Postgres support needs major work. There's a lot of inconsistencies
between PG and the other backends (especially for install/upgrade).
There *are* people here who care about PG.

-Chad

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l



___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Greg Sabino Mullane]

Mark A. Hershberger wrote:
> How can we improve the support for databases like PostgreSQL, Oracle,
> DB2 and MS SQL?
> 
> Getting Jenkins involved in testing isn't the (only) answer, though it
> would certainly help.
> 
> If developers who were interested in those databases could watch
> includes/db, that would help, as well.

I've tackled this problem, and will share my experience. The problem being how 
to keep all the schemas in sync. I looked at the existing solution someone 
else started, but found it pretty rough. My idea (I called it "abstract 
schema") 
was a central SQL file, which used a fairly straightforward SQL-ish syntax, 
and then a parser that could read that file and create versions for MySQL, 
Postgres, etc. Or just load that information into memory for the install 
and upgrade process. I actually had a working prototype for that, which 
worked quite well. It made the upgrades in particular very smooth, as 
there were no more patch files needed, the installer simply read the current 
canonical schema state and made the necessary changes.

The big, showstopping problem was trying to map the existing tables.sql 
file (the main MySQL one) into the new system. There is no straightforward 
mapping possible, as every single table in the system I had to try and 
figure out why column such-and-such was using this type, and why sometimes 
there was a default and other times not for similar cases, etc. It was quite 
the nightmare, so that's why I eventually abandoned the work. The tables.sql 
file is quite obviously organically grown, and has little rhyme or reason. 
I did come up with some basic naming rules (esp. for indexes), which means 
that the first release with the new system will rename a lot of objects, 
but as they are not referenced directly, that should not be a problem.

I'm happy to dig out my notes if anyone wants some examples of the 
type mapping issues. I think an abstract tables.sql is a good general 
approach, but getting from here to there is going to require a lot 
of work slogging through those data types.

-- 
Greg Sabino Mullane g...@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8


signature.asc
Description: Digital signature
___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Chad]

On Mon, Feb 25, 2013 at 1:29 PM, Greg Sabino Mullane  wrote:
> Mark A. Hershberger wrote:
>> How can we improve the support for databases like PostgreSQL, Oracle,
>> DB2 and MS SQL?
>>
>> Getting Jenkins involved in testing isn't the (only) answer, though it
>> would certainly help.
>>
>> If developers who were interested in those databases could watch
>> includes/db, that would help, as well.
>
> I've tackled this problem, and will share my experience. The problem being how
> to keep all the schemas in sync. I looked at the existing solution someone
> else started, but found it pretty rough. My idea (I called it "abstract 
> schema")
> was a central SQL file, which used a fairly straightforward SQL-ish syntax,
> and then a parser that could read that file and create versions for MySQL,
> Postgres, etc. Or just load that information into memory for the install
> and upgrade process. I actually had a working prototype for that, which
> worked quite well. It made the upgrades in particular very smooth, as
> there were no more patch files needed, the installer simply read the current
> canonical schema state and made the necessary changes.
>
> The big, showstopping problem was trying to map the existing tables.sql
> file (the main MySQL one) into the new system. There is no straightforward
> mapping possible, as every single table in the system I had to try and
> figure out why column such-and-such was using this type, and why sometimes
> there was a default and other times not for similar cases, etc. It was quite
> the nightmare, so that's why I eventually abandoned the work. The tables.sql
> file is quite obviously organically grown, and has little rhyme or reason.
> I did come up with some basic naming rules (esp. for indexes), which means
> that the first release with the new system will rename a lot of objects,
> but as they are not referenced directly, that should not be a problem.
>
> I'm happy to dig out my notes if anyone wants some examples of the
> type mapping issues. I think an abstract tables.sql is a good general
> approach, but getting from here to there is going to require a lot
> of work slogging through those data types.
>

This was exactly what we tried with the abstract-schema branch,
before it was abandoned. The place we got stuck on was handling
updates.

-Chad

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Tyler Romeo]

Where is the abstract-schema branch now? The only thing I remember about
that was a brief mailing list discussion (I think started by Daniel
Friesnen) about making such a format. What exactly were the hold-ups with
updates?

*--*
*Tyler Romeo*
Stevens Institute of Technology, Class of 2015
Major in Computer Science
www.whizkidztech.com | tylerro...@gmail.com


On Mon, Feb 25, 2013 at 4:35 PM, Chad  wrote:

> On Mon, Feb 25, 2013 at 1:29 PM, Greg Sabino Mullane 
> wrote:
> > Mark A. Hershberger wrote:
> >> How can we improve the support for databases like PostgreSQL, Oracle,
> >> DB2 and MS SQL?
> >>
> >> Getting Jenkins involved in testing isn't the (only) answer, though it
> >> would certainly help.
> >>
> >> If developers who were interested in those databases could watch
> >> includes/db, that would help, as well.
> >
> > I've tackled this problem, and will share my experience. The problem
> being how
> > to keep all the schemas in sync. I looked at the existing solution
> someone
> > else started, but found it pretty rough. My idea (I called it "abstract
> schema")
> > was a central SQL file, which used a fairly straightforward SQL-ish
> syntax,
> > and then a parser that could read that file and create versions for
> MySQL,
> > Postgres, etc. Or just load that information into memory for the install
> > and upgrade process. I actually had a working prototype for that, which
> > worked quite well. It made the upgrades in particular very smooth, as
> > there were no more patch files needed, the installer simply read the
> current
> > canonical schema state and made the necessary changes.
> >
> > The big, showstopping problem was trying to map the existing tables.sql
> > file (the main MySQL one) into the new system. There is no
> straightforward
> > mapping possible, as every single table in the system I had to try and
> > figure out why column such-and-such was using this type, and why
> sometimes
> > there was a default and other times not for similar cases, etc. It was
> quite
> > the nightmare, so that's why I eventually abandoned the work. The
> tables.sql
> > file is quite obviously organically grown, and has little rhyme or
> reason.
> > I did come up with some basic naming rules (esp. for indexes), which
> means
> > that the first release with the new system will rename a lot of objects,
> > but as they are not referenced directly, that should not be a problem.
> >
> > I'm happy to dig out my notes if anyone wants some examples of the
> > type mapping issues. I think an abstract tables.sql is a good general
> > approach, but getting from here to there is going to require a lot
> > of work slogging through those data types.
> >
>
> This was exactly what we tried with the abstract-schema branch,
> before it was abandoned. The place we got stuck on was handling
> updates.
>
> -Chad
>
> ___
> Wikitech-l mailing list
> Wikitech-l@lists.wikimedia.org
> https://lists.wikimedia.org/mailman/listinfo/wikitech-l
>
___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Chad]

On Mon, Feb 25, 2013 at 2:00 PM, Tyler Romeo  wrote:
> Where is the abstract-schema branch now? The only thing I remember about
> that was a brief mailing list discussion (I think started by Daniel
> Friesnen) about making such a format. What exactly were the hold-ups with
> updates?
>

As I said earlier in the thread, it's still in SVN. I didn't bother migrating
it because nobody was caring at the time. If anyone's wanting to dust
it off and make a new branch for core, we can do that.

-Chad

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Brian Wolff]

>
> However - it's not a freeware and that automatically makes it very
> unpopular for open source OS vendors and very unlikely a choice of
> standard webadmin and that makes it hard to test. Even if I was able
> to obtain some license to install oracle on any of my servers, I
> wouldn't do it as it eats too much resources. On other hand if oracle
> granted some license to wikimedia, we could set up a project on
> wikimedia labs for this purpose.
>

Doesnt oracle offer a free "express" eddition?

-bawolff
___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Tyler Romeo]

Oh. Sorry must have missed that. I'll take a look and see if there's
anything interesting.
On Feb 25, 2013 5:05 PM, "Chad"  wrote:

> On Mon, Feb 25, 2013 at 2:00 PM, Tyler Romeo  wrote:
> > Where is the abstract-schema branch now? The only thing I remember about
> > that was a brief mailing list discussion (I think started by Daniel
> > Friesnen) about making such a format. What exactly were the hold-ups with
> > updates?
> >
>
> As I said earlier in the thread, it's still in SVN. I didn't bother
> migrating
> it because nobody was caring at the time. If anyone's wanting to dust
> it off and make a new branch for core, we can do that.
>
> -Chad
>
> ___
> Wikitech-l mailing list
> Wikitech-l@lists.wikimedia.org
> https://lists.wikimedia.org/mailman/listinfo/wikitech-l
___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Matthew Flaschen]

On 02/25/2013 12:53 PM, Sumana Harihareswara wrote:
> The question: what do we need to test more often to keep RDBMSes happy?
>  https://www.mediawiki.org/wiki/Database_testing &
> https://www.mediawiki.org/wiki/New_installer/Test_plan Permissions,
> searching, schema setup, schema changes, quoting & identifiers, weird
> page names, transactions, import, dump, & interface coverage seem like
> the main culprits.

Sure, for starters. :) Bear in mind, if we want to keep support for all
these dbs, every change to the database schema has to (at some point)
result in a change to separate SQL files for each DB (MySQL and SQLite
use the same ones).  For instance, there is a separate active
oracle/tables.sql.

Then, we need to make sure it actually works as intended for all the
dbs.  We do have database tests.  As people noted, just having Jenkins
run all the existing tests for every db would be a major step forward.
We *only* run them for SQLite (not even MySQL!).

See https://bugzilla.wikimedia.org/show_bug.cgi?id=35912

Matt Flaschen

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Nikola Smolenski]

On 26/02/13 04:18, Matthew Flaschen wrote:

Sure, for starters. :) Bear in mind, if we want to keep support for all
these dbs, every change to the database schema has to (at some point)
result in a change to separate SQL files for each DB (MySQL and SQLite
use the same ones).  For instance, there is a separate active
oracle/tables.sql.


I am wondering if it would make sense to give up on SQL, make universal 
table creation functions in PHP, the same way there are for other 
queries, and use that. Has anyone tried this before, is there other 
software that works like this?


___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Dmitriy Sintsov]

26 Февраль 2013 г. 14:27:06 пользователь Nikola Smolenski (smole...@eunet.rs) 
написал:

On 26/02/13 04:18, Matthew Flaschen wrote:
> Sure, for starters. :) Bear in mind, if we want to keep support for all
> these dbs, every change to the database schema has to (at some point)
> result in a change to separate SQL files for each DB (MySQL and SQLite
> use the same ones).    For instance, there is a separate active
> oracle/tables.sql.
I am wondering if it would make sense to give up on SQL, make universal 
table creation functions in PHP, the same way there are for other 
queries, and use that. Has anyone tried this before, is there other 
software that works like this?


http://stackoverflow.com/questions/108699/good-php-orm-library

By the way, MySQL 5.6 is out and it supports fulltext search indexation for 
InnoDB tables. They also promise better peformance on large hardware.
Still cannot find 12.04 ppa for 5.6.10, though and do not want to go troubles 
installing from source (although I installed MySQL from source some years ago)..
Why going another database engines besides MySQL / MariaDB?
Dmitriy

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Luke Welling WMF]

Do we have an official position on cross database compatibility?

Some of the MediaWiki SQL is in separate files and can be easily directed
at a specific database engine.  A lot of it though is scattered as
fragments though other code and is going to be run on any engine we connect
to.

Specifically, do we use MySQL specific syntax that is more efficient (but
breaks elsewhere) or do we attempt to write lowest common denominator SQL
that will run more places, but not run as efficiently on our primary target?

The only SQL coding standard doc I have seen treats Database and MySQL as
synonyms: http://www.mediawiki.org/wiki/Manual:Coding_conventions/Database

Luke Welling


On Tue, Feb 26, 2013 at 5:37 AM, Dmitriy Sintsov  wrote:

>
>
>  26 Февраль 2013 г. 14:27:06 пользователь Nikola Smolenski (
>> smole...@eunet.rs) написал:
>>
>>
>> On 26/02/13 04:18, Matthew Flaschen wrote:
>> > Sure, for starters. :) Bear in mind, if we want to keep support for all
>> > these dbs, every change to the database schema has to (at some point)
>> > result in a change to separate SQL files for each DB (MySQL and SQLite
>> > use the same ones).For instance, there is a separate active
>> > oracle/tables.sql.
>> I am wondering if it would make sense to give up on SQL, make universal
>> table creation functions in PHP, the same way there are for other queries,
>> and use that. Has anyone tried this before, is there other software that
>> works like this?
>>
>
> http://stackoverflow.com/**questions/108699/good-php-orm-**library
>
> By the way, MySQL 5.6 is out and it supports fulltext search indexation
> for InnoDB tables. They also promise better peformance on large hardware.
> Still cannot find 12.04 ppa for 5.6.10, though and do not want to go
> troubles installing from source (although I installed MySQL from source
> some years ago)..
> Why going another database engines besides MySQL / MariaDB?
> Dmitriy
>
>
> __**_
> Wikitech-l mailing list
> Wikitech-l@lists.wikimedia.org
> https://lists.wikimedia.org/**mailman/listinfo/wikitech-l
>
___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Marc A. Pelletier]

On 02/26/2013 02:14 PM, Luke Welling WMF wrote:

Do we have an official position on cross database compatibility?


It would be nice if we did.  In my own production environments, I always 
use postgres.  To date, mw has been "good enough" with its support that 
I've never had significant integration problems, but looking forward is 
always a little worrying.


-- Marc


___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Matthew Flaschen]

On 02/26/2013 02:14 PM, Luke Welling WMF wrote:
> Specifically, do we use MySQL specific syntax that is more efficient (but
> breaks elsewhere) or do we attempt to write lowest common denominator SQL
> that will run more places, but not run as efficiently on our primary target?

There's a DB layer for runtime operations (select, update, insert,
etc.), and my understanding is that's supposed to be used for pretty
much everything.

However, part of the optimization is choosing indices, which as you
noted is db-specific (part of tables.sql)

Matt Flaschen

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Daniel Friesen]

For reference this is the RFC that was discussed in my thread on the  
subject.


https://www.mediawiki.org/wiki/Requests_for_comment/Abstract_table_definitions

Should probably dig up some gmane/archive links for both this and that  
discussion and add them to the RFC page.


--
~Daniel Friesen (Dantman, Nadir-Seen-Fire) [http://danielfriesen.name/]

On Mon, 25 Feb 2013 14:28:06 -0800, Tyler Romeo   
wrote:



Oh. Sorry must have missed that. I'll take a look and see if there's
anything interesting.
On Feb 25, 2013 5:05 PM, "Chad"  wrote:

On Mon, Feb 25, 2013 at 2:00 PM, Tyler Romeo   
wrote:
> Where is the abstract-schema branch now? The only thing I remember  
about

> that was a brief mailing list discussion (I think started by Daniel
> Friesnen) about making such a format. What exactly were the hold-ups  
with

> updates?
>

As I said earlier in the thread, it's still in SVN. I didn't bother
migrating
it because nobody was caring at the time. If anyone's wanting to dust
it off and make a new branch for core, we can do that.

-Chad



___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Greg Sabino Mullane]

Luke Welling asked:
> Specifically, do we use MySQL specific syntax that is more efficient (but
> breaks elsewhere) or do we attempt to write lowest common denominator SQL
> that will run more places, but not run as efficiently on our primary target?

Neither: we use the already-existing methods, and have those examine 
db-specific attributes to modify their behavior. The SQL itself stays 
pretty basic: I don't know that I've ever seen SQL (in core anyway), 
that varied enough between backends to require a differentiation. If 
we do encounter such a thing, it's probably best to pick the simplest 
variation (if possible), or the MySQL one (if not), and have attributes 
determine which variant is used (e.g. if ( $dbr->left_join_expensive() )

Matt Flaschen wrote:
> However, part of the optimization is choosing indices, which as you
> noted is db-specific (part of tables.sql)

Not sure what you mean - index hints? Yeah, that could be a little tricky, 
but luckily the Postgres part, at any rate, doesn't have to worry about 
those (as our planner is smart enough to pick the best index itself ;).
I can't think of a clean way to abstract that anyway, as just needing 
an index hint for MySQL does mean the same is needed on Oracle, and 
vice-versa. So you'd already have a very database specific argument 
for each query anyway, such that you would never have to worry if other 
dbs had the same index.

-- 
Greg Sabino Mullane g...@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8


signature.asc
Description: Digital signature
___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Matthew Flaschen]

On 02/27/2013 10:29 PM, Greg Sabino Mullane wrote:
> Not sure what you mean - index hints? Yeah, that could be a little tricky, 
> but luckily the Postgres part, at any rate, doesn't have to worry about 
> those (as our planner is smart enough to pick the best index itself ;).
> I can't think of a clean way to abstract that anyway, as just needing 
> an index hint for MySQL does mean the same is needed on Oracle, and 
> vice-versa. So you'd already have a very database specific argument 
> for each query anyway, such that you would never have to worry if other 
> dbs had the same index.

No, I was just talking about defining the indices (obviously, the query
planner is out of luck if you don't define them properly).  E.g. in the
PostgeSQL tables.sql file:

CREATE INDEX archive_name_title_timestamp ON archive
(ar_namespace,ar_title,ar_timestamp);

Even though often this syntax is the same cross-db, since the whole file
is db-specific (except MySQL and SQLite share), people have the option
of db-specific index variants.

You're right index hints at query time could conceivably help, but it
doesn't seem like a priority.

Matt Flaschen

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Freako F. Freakolowsky]

On 28. 02. 2013 04:29, Greg Sabino Mullane wrote:
I can't think of a clean way to abstract that anyway, as just needing 
an index hint for MySQL does mean the same is needed on Oracle, and 
vice-versa.
Please don't use index hints on Oracle ... since 9i you get far better 
performances using CBO (cost based optimizer) rather that RBO (rule 
based optimizer). You use index optimizer hints only when you have an 
edge case (or an incompetent DBA).
If you have performance issues with CBO you just have to tweak the stats 
on indexes and leave the actual query optimization to the DB.


LP, Jure

___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Greg Sabino Mullane]

Daniel Friesen let us know:
> For reference this is the RFC that was discussed in my thread on the  
> subject.
> 
> https://www.mediawiki.org/wiki/Requests_for_comment/Abstract_table_definitions
> 
> Should probably dig up some gmane/archive links for both this and that  
> discussion and add them to the RFC page.

Excellent, thank you for that link. That mirrors a lot of my thinking. 
I'm going to take a fresh look at my data type issues with that page 
as a guideline.


-- 
Greg Sabino Mullane g...@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8


signature.asc
Description: Digital signature
___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

Re: [Wikitech-l] Better non-MySQL db support

[Greg Sabino Mullane]

Matthew Flaschen wrote:
> No, I was just talking about defining the indices (obviously, the query
> planner is out of luck if you don't define them properly).  E.g. in the
> PostgeSQL tables.sql file:
> 
> CREATE INDEX archive_name_title_timestamp ON archive
> (ar_namespace,ar_title,ar_timestamp);
> 
> Even though often this syntax is the same cross-db, since the whole file
> is db-specific (except MySQL and SQLite share), people have the option
> of db-specific index variants.

Ah, I see what you mean. Yes, some of that is linked to the capability 
of the database itself (e.g. can it do a bitmap index scan). So perhaps 
a suggested index in the abstract schema could be tied to such attributes, 
and simply not created if the db cannot / should not. Or simply tie it to 
a specific db type if absolutely needed. I can't recall seeing a case where 
there would be a *choice* of indexes (e.g. if you can support this index, 
use it, otherwise, do this one), but that's a SMOP once we encounter 
that case I suppose. :)

-- 
Greg Sabino Mullane g...@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8


signature.asc
Description: Digital signature
___
Wikitech-l mailing list
Wikitech-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikitech-l