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

[Jörn Friedrich Dreyer]

First of all I would like to apologize for pushing in code that touches
every SQL query. That was not very professional development. I should
have added the code in smaller chunks. First putting in place the
libraries needed for oracle and then introduce the limit parameter. Up
to this point nothing should have broken. Then I could have gradually
escaped the identifiers in SQL queries app by app resulting in a much
more understandable process. With the CI in place I would even have been
notified via email when I broke a test.

so +1 for more tests (working on it)

Furthermore, I'm really sorry I broke the new sharing SQL (fix just
committed, all tests pass) and will look into mysql and psql tomorrow.

That being said, I am eager to get a lot of users for owncloud by
providing our customers with oracle support. Timing is an important
thing here, and I wanted to test the implementation thoroughly, thats
why I committed the code. My main activities will now go into creating
automated tests for owncloud.

so long

Jörn


On 26.08.2012 02:02, Thomas Müller 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!
> 
> 
> 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


-- 
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)