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

[Jörn Friedrich Dreyer]

On 27.08.2012 19:20, Victor Dubiniuk wrote:
> I'm not an Oracle fan neither. But it seems to be the matter of syntax
> and naming conventions mostly. So it would be nice to have a page with
> clear "Do" and "Don't"  checklist at least.
I have not yet worked on any of the website pages but would like to
update http://owncloud.org/dev/apps/database/

By now I learned the following with regard to MDB2:

ALWAYS escape identifiers with ` (backticks).
SELECT * FROM oc_appconfig; becomes SELECT * FROM `oc_appconfig`;

Don't use CURRENT_TIMESTAMP with the MDB2 'timestamp' datatype in your
database.xml. It will become a DATETIME column that is incompatible with
CURRENT_TIMESTAMP in every database. Instead use '0000-00-00 00:00:00'.

now() and CURRENT_TIMESTAMP are fine in SQL queries (owncloud provides a
hack for this).

Instead of using the MDB2 'clob' type use 'text' and set a length. CLOB
is incompatible with indexes on some databases or MDB2 does not handle
it correctly. Without a length MDB2 will pick an internal length which
it thinks is right but breaks current mysql eg. And it will force you to
think on sensible text lengths which in general is "a good thing"

I think that's it. Someone please correct me if I'm wrong.

Jörn

+1 for doctrine ;)


> 
> On Aug 27, 2012 7:56 PM, "Tim Bell" <Tim.Bell at cern.ch
> <mailto:Tim.Bell at cern.ch>> wrote:
> 
> 
>     I would be a strong supporter of Oracle owncloud support in 4.5.
>      There is a
>     strong need for an enterprise private cloud solution such as
>     owncloud with
>     good multi-platform support even when these are proprietary (e.g.
>     Windows
>     and iOS clients)
> 
>     Sites like us have major open source investments but also are
>     pragmatic when
>     there are other considerations such as in-house DBA skills and tool
>     investment.
> 
>     Tim Bell
>     CERN
> 
>     > -----Original Message-----
>     > From: owncloud-bounces at kde.org <mailto:owncloud-bounces at kde.org>
>     [mailto:owncloud-bounces at kde.org <mailto:owncloud-bounces at kde.org>] On
>     > Behalf Of Thomas Tanghus
>     > Sent: 27 August 2012 18:29
>     > To: owncloud at kde.org <mailto:owncloud at kde.org>
>     > Subject: Re: [Owncloud] Commiting Oracle support tomorrow, beware
>     of SQL
>     > without escaped identifiers
>     >
>     > On Monday 27 August 2012 17:08 Diederik de Haas wrote:
>     > > On Monday 27 August 2012 12:20:14 Thomas Müller wrote:
>     > > > > Oracle support is already working and we have 4 weeks left
>     to fix
>     > > > > remaining small problems.
>     > >
>     > > Another solution would be to postpone Oracle support after 4.5
>     > > release, so it gets the full time between 4.5 and 5.0.
>     >
>     > +1
>     >
>     > --
>     > Med venlig hilsen / Best Regards
>     >
>     > Thomas Tanghus
>     > _______________________________________________
>     > Owncloud mailing list
>     > Owncloud at kde.org <mailto:Owncloud at kde.org>
>     > https://mail.kde.org/mailman/listinfo/owncloud
> 
>     _______________________________________________
>     Owncloud mailing list
>     Owncloud at kde.org <mailto: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
> 


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