Date index for Jan 2004
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [achievo] Oracle support for Achievo
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hello Joop,
Joop van de Wege wrote:
>>What's missing is the ddl class. If this is implemented properly,
>>setup.php should be able to install oracle databases.
> I have implemented this
Great!
> These are the issues I have found at the moment:
> In the calendar module DISTINCT is used together, initially, with CLOB
> columns which is not allowed in Oracle8/9, but I doubt whether the
> distinct is needed because you also select the *.id from those tables
> and they are unique by nature (Primarykey).
Yes, but if they are joined with some other table it could make the
resultset bigger. Which query did you mean?
> Then I found a couple of queries which had outer joins to the same table
> more than once which Oracle8 doesn't like but Oracle9 seems to have no
> problems with it if you use the new join syntax which should be
> compatible with mysql, but isn''t just completly.
Given that 10G is coming soon, I think it's ok to have it work with
Oracle9 but not with 8.
> WHERE schedule_attendee.schedule_id = schedule.id(+)
> AND person.id = schedule_attendee.person_id(+)
> AND owner.id = schedule.owner(+)
This looks weird. Perhaps Lineke can comment on this, I think she build
this query.
What I think this should do is retrieve appointments even if they don't
have attendees. Maybe this should be rewritten to the new join syntax.
> JOIN activity C
> LEFT JOIN phase A ON A.id=hours.phaseid
> LEFT JOIN project B ON B.id=A.projectid
> WHERE activitydate='2004-01-07'
> AND hours.userid='1'
> AND C.id=hours.activityid
^^^^^^^^^^^^^^^^^^^^
This is the condition for the JOIN. I guess it's the class.oci*query.inc
file that treats a non-left join like that. That should be easily
fixable, so it put's this condition in the ON clause instead of in the
WHERE.
> Further I had one module which assumed mysql as database backend by
> using directly 'LIMIT 0,1' in its query, quotation module to be exact.
Making the modules compatible is a separate issue. It would be a good
start if the base Achievo would work.
> That brings me to 2 other things:
> - there seems to be a standard way of building queries using atk classes
> but that is not always used (calender does, quotation doesn't)
It depends a bit on the circumstances. Quotation was originally build
for internal use here, and we use mysql. So no need to invest in oracle
compatible queries. Now we released it for others, so now it would be
nice to make it compatible.
> - maybe the application should generate foreignkey contraints there
> where appropriate to help itself.
Yes, that would be nice. I believe I put a TODO somewhere in the
setup.php or class.setup.inc files but I'm not sure.
One issue we might run into though if we enable constraints: there are
some places where the order of master record delete vs child record
delete might be the wrong way around. But those shouldn't be too hard to
fix.
> data from mysql to oracle using insert statements where some statements
> were rejected due to some export restriction, some column contained a
> space character which got lost in the export.
Was it a column generated by Achievo's setup.php script? If so, can you
tell me which one?
> to document the table relations and thus to find out if for example some
> queries indeed need outer join functionality or not.
> The schema from the website is real out of date.
I know. I should update that some time. I'm not sure if Martine still
reads the list. She created it, perhaps she would like to update it?
> Let me state that despite all of this I really really like Achievo -;)
> and would like to get Oracle support back into Achievo.
That would be nice to have. We did not pursue the Oracle and Pgsql
support a while ago, because we had no one willing to test and fix the
issues, so it's nice to have someone in the project looking at that.
Greetings,
Ivo
- --
Ivo Jansch <ivo dot
ibuildings.nl BV - information technology
http://www.ibuildings.nl
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE//mIq95NUnGFZm9cRAqumAJ4id1aFhOwK+md5ckK/2jOlNk9EZACeJSms
9VCr+SYOYVueKrnX5NOiY84=
=Isxi
-----END PGP SIGNATURE-----