[Owncloud] Commiting Oracle support tomorrow, beware of SQL without escaped identifiers

[Thomas Müller]

Am Sonntag, dem 26.08.2012 um 11:41 schrieb Frank Karlitschek:
> On 26.08.2012, at 02:02, Thomas Müller <thomas.mueller at tmit.eu> wrote:
> 
> > Hi,
> > 
> > due to the big impact Oracle has on the ownCloud code and the pretty high risk
> > to screw up the system (which is actually the case - git master is unusable).
> > 
> > I'd like to vote for a revert of the change in the current state of the release cycle.
> > Last week MTGap and members of the community started to test 4.5 especially
> > with the focus on the new sharing features. Putting another big change set on top
> > will actually kill all the testing effort.
> > 
> > I simply think the risk is to high to introduce bug which will give us headaches.
> > 
> > In addition to that I'd like to ask the community's opinion on ownCloud supporting 
> > Oracle database. My point of view is pretty clear - which is a non-business point of view:
> >    I don't need it - I don't want it - I don't want to maintain it!
> > 
> > As soon as some more elaborate database layer is in place (e.g. doctrine) and 
> > the impact of the database management systems on the code are minimal I don't 
> > care which databases are supported, but until that point let us stick with our 3 
> > databases for now - it's already a hell of testing!
> 
> I agree with you in general. I don´t like Oracle either.
> The thing is that we have requests for supporting it from users and customers so we have to do it.
> I don´t think it´s a good idea to try to keep this in a separate branch because this would
> make the testing really difficult and longtime support close to impossible.
> 

I understand, but the question is how much time the community developers loose to maintain Oracle sql semantics?
And I'm not taking about now but also about the upcoming years.

The community is already fighting with the 3 supported database.
And the syntax is pretty similar - Oracle is just different to some extent.

> Oracle support is already working and we have 4 weeks left to fix remaining small problems.
>

I think the point of time has been choose suboptimal - tomorrow beta1 shall be delivered.
A much better time would have been to merge this some week ago.

Possible solution: extend the testing phase now for 2 more weeks - might make sense anyhow due to github moves.

For the long run we need to agree on a database layer which abstracts away all these nasty database specialties.
doctrine has already been named several times - maybe we find some more.

Can we please add this to the agenda for the next dev meeting?

> What do you think?
>

But still I don't want Oracle in ownCloud :-/
To be clear: I don't want MS-Sql either ;-)
 

Tom

> 
> Frank
> 
> 
> 
> > 
> > 
> > Take care,
> > 
> > Tom aka DeepDiver
> > 
> > 
> > Am Freitag, dem 24.08.2012 um 19:31 schrieb Jörn Friedrich Dreyer:
> >> I am currently merging master into my personal stable4-oracle branch and
> >> will commit oracle support tomorrow. This will allow us to give the
> >> implementation a lot of testing before owncloud customers will be using
> >> it in production.
> >> 
> >> On the bad side I had to make changes to ALL SQL statements.
> >> On the good side the changes are consistent and leave no room for
> >> exceptions:
> >> 1. Escape every identifier with backticks ('SELECT user' ... becomes
> >> 'SELECT `user`)
> >> 2. Move LIMIT & OFFSET SQL to a parameter for OC_DB::prepare()
> >> 3. Disable using PDO for oracle.
> >> 
> >> Why:
> >> 1. In contrast to mysql, postgrasql and sqlite oracle uppercases
> >> unescaped identifiers. This leads to a ton of nameclashes with 'user',
> >> 'uid', 'gid' and others, so escaping them is the right way.
> >> 2. oracle does not know limit and offset, which is why mdb2 also
> >> provides limit and offset parameters to add the necessary SQL or a
> >> workaround for oracle.
> >> 3. PDO for oracle is unstable and in general a PITA to set up.
> >> 
> >> If you want to help testing with oracle suport, there is an express
> >> version free of charge limited to 18GB of data and one CPU:
> >> http://www.oracle.com/technetwork/products/express-edition/downloads/index.html
> >> 
> >> +1 for introducing doctrine
> >> 
> >> so long
> >> 
> >> Jörn
> >> 
> >> -- 
> >> Jörn Friedrich Dreyer (jfd at owncloud.com)
> >> Software Developer
> >> ownCloud GmbH
> >> 
> >> Your Data, Your Cloud, Your Way!
> >> 
> >> ownCloud GmbH, GF: Markus Rex, Holger Dyroff
> >> Schloßäckerstrasse 26a, 90443 Nürnberg, HRB 28050 (AG Nürnberg)
> >> _______________________________________________
> >> Owncloud mailing list
> >> Owncloud at kde.org
> >> https://mail.kde.org/mailman/listinfo/owncloud
> > _______________________________________________
> > Owncloud mailing list
> > Owncloud at kde.org
> > https://mail.kde.org/mailman/listinfo/owncloud