Date index for Jan 2004

On Fri, 02 Jan 2004 20:09:14 +0100
Ivo Jansch <ivo dot 

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Hello,
> 
> Joop van de Wege wrote:
> 
> > I have been digging around in Achievo-1.0.1 to see if I can add Oracle
> > support to it and I'm stuck at how it is supposed to be.
> > Can somebody point me in the right direction for this?
> 
> To make the setup script work, a file called class.atkoci9ddl.inc must
> be implemented. Currently, the database abstraction layer has these files:
> 
> class.atk*db.inc       -> takes care of connections, commits, executing
>                           queries, retrieving results
> class.atk*query.inc    -> takes care of generating vendor specific
>                           sql insert, update, delete and select
>                           statements
> class.atk*ddl.inc      -> takes care of generating vendor specific ddl
>                           statements (create/alter/drop table etc)
> 
> Where '*' can be something like 'oci8', 'mysql', 'pgsql', 'oci9' etc.
> 
> All files are located in the atk/db subdirectory.
> 
> As you can see, the *query and *db classes are already implemented for
> several versions of oracle.
> 
> 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 and it seems to work but several other issues
have come up.

> The other problem is 'hardcoded' queries in Achievo. There are some that
> are generic enough to be usable in other environments than myqsl, others
> may need some tweaking.
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).
I choose, for the moment, to use VARCHAR2(4000) instead of CLOB since
4000 chars is alot of text to type for an description or remark -;)

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.
I got the following query, fixed layout,  by logging in and from PIM
goto SCHEDULER:
ORA-01417: a table may be outer joined to at most one other table (errno: 1417)
SELECT DISTINCT schedule.id, schedule.startdate, schedule.enddate,
                schedule.starttime, schedule.endtime, schedule.title,
                schedule.description, schedule.location, schedule.allday,
                schedule.publicitem, schedule.owner, schedule.status,
                schedule.all_users, schedule.scheduletype, owner.userid
                      AS al_a
           FROM schedule, schedule_attendee, person, person owner
          WHERE schedule_attendee.schedule_id = schedule.id(+)
            AND person.id = schedule_attendee.person_id(+)
            AND owner.id = schedule.owner(+)
            AND schedule.startdate <= '2004-01-07'
            AND schedule.enddate >= '2004-01-07'
            AND (   schedule.owner = 'joop'
                 OR person.userid = 'joop'
                 OR schedule.all_users = 1
                )
       ORDER BY starttime
Removing the first (+) seems to fix the problem but I don't have enough
data in the tables to be sure not to break anything.
After having a chat with my DBA we came to the conclusion that the last
outer join is probably not needed but since we lack a deep understanding
of the application we are not sure.
Can anyone comment on this.

Then I went to Oracle9 database and tried it there with the mysql syntax
of joining with the following result, just login and the first page will
show it:
ORA-00905: missing keyword (errno: 905)
SELECT hours.id,hours.activitydate,userid.lastname AS al_a
,userid.firstname AS al_b,userid.id AS al_c
,userid.cellular AS al_d,userid.fax AS al_e
,hours.phaseid,B.name AS al_f,A.name AS al_g
,C.name AS al_h,hours.activityid,hours.remark,hours.time,hours.entrydate
FROM hours
LEFT JOIN person userid ON hours.userid=userid.id
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
ORDER BY hours.id

The problem here is 'JOIN activity C' which seems to be valid mysql
syntax but I can't find nothing, that I can understand, in the mysql
manuals that explains this line. It looks like it is not complete but
there is no error so it works but what is it supposed todo?

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.

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)
- maybe the application should generate foreignkey contraints there
where appropriate to help itself. I came across this while importing
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. Besides that it would help
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.

Let me state that despite all of this I really really like Achievo -;)
and would like to get Oracle support back into Achievo.

Joop

-- 
Joop van de Wege <JoopvandeWege dot