oracle / multi-db support

[giunta.gaetano@sea-aeropo]

hello folks.

I started a clean-up of the db schema creation sql script. Patch posted to sf.net

Ultimate goal isportability to 1 - oracle, 2 - any adodb supported db

Any comments on previous experiences / known problems is apprectiated.

here's a cryptic list of things to do / consider:
### G. Giunta 2006-10-6
### cleanups and preparation for transition to multi database schema
###
# tabs to spaces: OK
# no double quotes for strings: OK
# TYPE=MyISAM vs. ENGINE=MyISAM: OK (synonims in mysql)
# \' vs. '' escaping: OK
# integer vs. int: OK (synonims in mysql)
# no single quotes around numeric default values: OK
# consistent usage of UPPER/lowercase
# all inserts should use colnames
# drop either all tables before creation or none
# INDEX vs.KEY
# whitespace
# alter table vs. unique

# no single quotes around numeric vals in inserts, and always quotes around string vals
# a couple of tables do not have 'ENGINE=MyISAM': is it correct ?
# remove default null, since it is the default anyway (?)
# convert CHAR to VARCHAR in tables that have other varchar cols - mysql converts them silently anyway (?)
# change tinytext to varchar(255) (?)
# change all char cols to varchar (since that's what other db use for no-trailing-whitespace cols) (?)
# remove length from tyniint where > 4 (?)
# nb: 'decimal' cols change in mysql 5.0.3 and up
# remove bacticks since we are strict on col/table naming
# possible oracle problem with varchar not null default '' (?)
# usage of sql function MD5() in insert stmt

bye
gaetano

[caseydk]

Sounds great. The next place you should go to is the Query class. That is where all sql *should* be generated throughout the system.

Unfortunately, some modules - especially non-core - have sql embedded directly in them.

[giunta.gaetano@sea-aeropo]

Scanning the complete source code I leave to 'real' dp developers.
I only play around with useless stuff, you know... :mrgreen:

In the meantime, I imported the db creation script into adodb-datadict format, and uploaded it to sf.net, along with a script to demo transformation from datatict 'source' to real sql.

Please feel free to test the generated sql on as many databases as you can, and report any errors.

PS: I had to patch the adodb-datadict files, and posted the fixes to the relevant mailing list...

[caseydk]

Scanning the complete source code I leave to 'real' dp developers.

Ah, so this will be *mostly* Oracle/multi-db support... but not complete.

I don't think I'll end up thanking you for this one... ;)

[giunta.gaetano@sea-aeropo]

Hopely users will...

Jokes aside, I think there is an immediate benefit in using datadict: it seems capable of working out by itself the diffs between a live schema a nd a schema definition, so only keeping the definition file up-to-date will also have solved the problem of upgrading existing installs (to be tested of course. I think that shema differences will be handled mostly ok, but inserting new rows will have to be done by hand).

BTW: there is also the possibility of adding foreign keys in the schema defs, for those definitions that support it. Might be useful...

[giunta.gaetano@sea-aeropo]

Many improvements with a little bit of coding.

I updated the patch on sf.net. The sql generated is (almost) valid on both mysql and oracle (tested on 9.2), with the only remaining problem being the 'hardcoded' date values to be inserted in dp_version.

I would be glad to have feedback on anybody testing this, especially on postgres or firebird.

Bye
Gaetano

[caseydk]

Can you attach the link here?

Thanks.

[giunta.gaetano@sea-aeropo]

the patch is available here:

http://sourceforge.net/tracker/download.php?group_id=21656&atid=372485&file_id=197898&aid=1572694

to set it up, dump the contents in a dir of your choice, then overwrite your current adodb-datadict.inc.php with the patched version

bye
Gaetano

[oniTony]

testing the generated SQL with Oracle 10g, I've run into the following errors at

CREATE UNIQUE INDEX idx_syskey_id ON syskeys (syskey_id);


ORA-01408: Such column list already indexed

since "PRIMARY KEY (syskey_id)" has already been called. Remove Create Index line.

[giunta.gaetano@sea-aeropo]

I did notice that error too, but i'm loath to remove it without closer inspection:

- the rest of the script goes ok (if you set your sql script editor tool to continue past any errors reported, at least)

- I think if I remove it there could be a missing index on the table with mysql:

Apparently oracle creates the unique index to enforce the primary key constraint.
Do all other databases do the same, or do they enforce pk using only some kind of constraint mechanism, while the created index could be used to speed up queries?

[oniTony]

One serious install issue I've run into, so that all the triggers come out invalid. They compile, but break upon execution.

It worked after I've created all the triggers via SQL*Plus shell, but that calls for manual database setup.

Also, just a heads up -- there is a _lot_ of code that needs to be changed in dotProject itself. There are about 700 instances where SQL statements are hardcoded, not created via ADODB. Most are pretty minor/basic, but some portion of this would have to be modified. Especially every concat_ws() operator which I belive is a MySQL feature.

additionally Oracle returns column names IN CAPITALS. this means that every line such as

$dPconfig["{$c['config_name']}"] = $c['config_value'];

needs to be updated to

$dPconfig["{$c['CONFIG_NAME']}"] = $c['CONFIG_VALUE'];

otherwise PHP throws 'index not found' errors

edit: also UNIX_TIMESTAMP() (also MySQL function) needs to be replaced with something overly complicated

SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) AS dt FROM dual;

http://jehiah.com/archive/oracle-date-to-unix-timestamp

edit2: ` character used to quote tables in MySQL is considered invalid in Oracle

[giunta.gaetano@sea-aeropo]

additionally Oracle returns column names IN CAPITALS

This is not a major problem if all databases do work with uppercase column names - just a but of tedious regexp search and replace.
If they do not (and I suppose some do), we could simply change the function that populates $dPconfig, or, maybe even better, set ADODB_ASSOC_CASE = 0 and let adodb do the gruntwork!

Maybe we could use ADODB_ASSOC_CASE as stopgap measure, while we convert everything to uppercase?

One serious install issue I've run into, so that all the triggers come out invalid. They compile, but break upon execution.

Sorry, but they compiled as valid for me (Oracle 9.2.0), and I tried a couple of inserts by hand without problems.
I created the structures using TOAD sql editor, after having generated the sql script from the php code I posted. Could you please give more details?

UNIX_TIMESTAMP(), ws_concat()
- ws_concat can be easily replaced using php code, which has great array merge / string split functions
- UNIX_TIMESTAMP is a bit more of a pain. Maybe we should look for an adodb implementation?

Thanks for carrying out all the testing, keep it up!

[oniTony]

Sorry, but they compiled as valid for me (Oracle 9.2.0), and I tried a couple of inserts by hand without problems.
I created the structures using TOAD sql editor, after having generated the sql script from the php code I posted. Could you please give more details?
I've pasted the generated SQL into db/dotproject.sql, and let the install script create everything.

I've then tried recreating the triggers via QuantumDB plugin for Eclipse - they compile, but throw 'failed to re-validate' error when called upon.

as I've mentioned before, after pasting the exact same code into SQL*Plus, the triggers started to work properly.

I'm using Oracle 10.2.0.1.0, though this might have something to do with weird trigger permission settings.


- UNIX_TIMESTAMP is a bit more of a pain. Maybe we should look for an adodb implementation?
There are just a couple of instances of the usage. I'm thinking of replacing

UNIX_TIMESTAMP() - UNIX_TIMESTAMP(session_created) as session_lifespan

with

(select sysdate - session_created FROM dual)* (86400) as session_lifespan

since session times are already stored as dates, I think this would work... I also think that session threshold has just increased to days. :???: I haven't tested it really.

Actually I'm still trying to figure out those sessions, they are being problematic.

[oniTony]

there's a problem with generated database scheme

the BLOB fields are for <i>"binary"</i> objects, and result in

ORA-01465 - Invalid Hex Number

error.

CLOB (Character LOB ?) should be used instead.

[ajdonnison]

Surely binary is a superset of character? I.e. every character is binary, but not every binary octet is a character. It seems a little odd that you can't plonk some text into a BLOB and get back the text you put in.

BTW, I believe the TEXT type in MySQL is equivalent to the Oracle CLOB type.

[giunta.gaetano@sea-aeropo]

fyi: the way I wrote the adodb spec file was meant (in intention) to create oracle CLOBs.
I'll have a qwik look at the code today if I have a bit of spare time...

[oniTony]

there's a problem with GACL tables

CREATE TABLE gacl_aco_map (
acl_id DECIMAL(10) DEFAULT 0 NOT NULL,
section_value VARCHAR(80) DEFAULT '0' NOT NULL,
value VARCHAR(80) DEFAULT '',
PRIMARY KEY (acl_id)
);

Oracle's PRIMARY KEY has an implicit UNIQUE constraint, which breaks the map tables, since the expected data is as

"10";"system";"login"
"11";"application";"access"
"11";"application";"add"
"11";"application";"delete"
"11";"application";"edit"
"11";"application";"view"

Since the primary key is still required for a unique id of a row, all fields must be included (or a new one created just for this purpose)

PRIMARY KEY(acl_id, section_value, value)

which by the way would be the same as the MySQL version

PRIMARY KEY (`acl_id`,`section_value`,`value`)


and so the updated table structures are!

CREATE TABLE gacl_aco_map (
acl_id DECIMAL(10) DEFAULT 0 NOT NULL,
section_value VARCHAR(80) DEFAULT '0' NOT NULL,
value VARCHAR(80) DEFAULT '',
PRIMARY KEY (acl_id, section_value, value)
);

CREATE TABLE gacl_aro_groups (
id DECIMAL(10) DEFAULT 0 NOT NULL,
parent_id DECIMAL(10) DEFAULT 0 NOT NULL,
lft DECIMAL(10) DEFAULT 0 NOT NULL,
rgt DECIMAL(10) DEFAULT 0 NOT NULL,
name VARCHAR(255) DEFAULT '',
value VARCHAR(80) DEFAULT '',
PRIMARY KEY (id, value)
);
CREATE INDEX gacl_parent_id_aro_groups ON gacl_aro_groups (parent_id);
CREATE INDEX gacl_lft_rgt_aro_groups ON gacl_aro_groups (lft, rgt);
-- CREATE UNIQUE INDEX gacl_value_aro_groups ON gacl_aro_groups (value);
-- value not unique on its own

CREATE TABLE gacl_axo_groups (
id DECIMAL(10) DEFAULT 0 NOT NULL,
parent_id DECIMAL(10) DEFAULT 0 NOT NULL,
lft DECIMAL(10) DEFAULT 0 NOT NULL,
rgt DECIMAL(10) DEFAULT 0 NOT NULL,
name VARCHAR(255) DEFAULT '',
value VARCHAR(80) DEFAULT '',
PRIMARY KEY (id, value)
);
CREATE INDEX gacl_parent_id_axo_groups ON gacl_axo_groups (parent_id);
CREATE INDEX gacl_lft_rgt_axo_groups ON gacl_axo_groups (lft, rgt);
-- CREATE UNIQUE INDEX gacl_value_axo_groups ON gacl_axo_groups (value);
-- value not unique on its own


The rest of the tables seem to have proper PRIMARY KEY / UNIQUE indexes.

[oniTony]

remember that Oracle column names are in capitals?

in db/upgrade_permissions.php, couple of indexes need to changed to caps

// line 155~ish
$res = db_exec($sql);
if ($res) {
while ($row = db_fetch_assoc($res)) {
// Add the basic ARO
$perms->add_object("user", $row['USER_USERNAME'], $row['USER_ID'], 1, 0, "aro");
if ($row["PERMISSION_ID"]) {
$perms->add_group_object($admin_role, "user", $row['USER_ID'], "aro");
}
}
}

Otherwise the users (admin) will silenty! not be granted permissions to log in. Yikes!

[oniTony]

Oracle handles JOIN operations differently. Mainly it's

LEFT JOIN table alias ON ...

as oppose to MySQL's

LEFT JOIN table AS alias ON ...


the fix is in classes/query.class.php, line 813~ish

function make_join($join_clause)
{
$result = "";
if (! isset($join_clause))
return $result;
if (is_array($join_clause)) {
foreach ($join_clause as $join) {
$result .= ' ' . strtoupper($join['type']) . ' JOIN ' . $this->_table_prefix . $join['table'] . '';
if ($join['alias'])
/* -> */ $result .= ' ' . $join['alias']; // ' AS ' . $join['alias'];
if (is_array($join['condition'])) {
$result .= ' USING (' . implode(',', $join['condition']) . ')';
} else {
$result .= ' ON ' . $join['condition'];
}
}
} else {
$result .= ' LEFT JOIN ' . $this->_table_prefix . $join_clause . '';
}
return $result;
}

Though even then this syntax only works for Oracle 9i or later. (lucky me, 10g). Oracle 8i uses a weird (+) operator, and the queries would have to be restructured conciderably.

...
FROM table a, another_table b
WHERE a.key = b.key(+);

[giunta.gaetano@sea-aeropo]

I just uploaded a new version of the sql-generating patch to sf.net. The generated sql is valid on oracle (with a couple of innocuous errors thrown) and mysql.

Problems that should be fixed:

- columns part of a multi-column pk and having a non-unique index (gacl_aro_groups.value, gacl_axo_groups.value)

- pk definition for gacl_aco_map

- BLOB fields that had better be defined as CLOB

As for the other problems:

- function make_join($join_clause): I second the patch, as the syintax without AS works ok in mysql too, at least in version 5. Screw oracle 8 and non-sql99 compliant databases for now

- db/upgrade_permissions.php: please test adding
define('ADODB_ASSOC_CASE', 0);
before inclusion of adodb.inc.php. It should really help speed up porting

- UNIX_TIMESTAMP(): the sql you proposed is clean and fast, but it will only work on oracle. I think we should opt for a global solution

bye
Gaetano

[oniTony]

where are you hosting those files on SF? Previously you've linked to the file directly, what is the project name?

for the UNIXTIMESTAMP, there might already be an ADOdb implemintation (adodb.inc.php)

/**
* Also in ADORecordSet.
* @param $v is a date string in YYYY-MM-DD format
*
* @return date in unix timestamp format, or 0 if before TIMESTAMP_FIRST_YEAR, or false if invalid date format
*/
function UnixDate($v)
{


Alternativly, since UNIXTIMESTAMP is only implemented to calculate session's lifespan from datetime difference into seconds, the math could be exported from SQL to PHP.

I can't get ADODB_ASSOC_CASE to work. Which is making thing very very difficult with db_loadObject( $sql, $this ) calls.

[giunta.gaetano@sea-aeropo]

where are you hosting those files on SF? Previously you've linked to the file directly, what is the project name?

project name is dotproject :o

The adodb function looks like it might be of use, but, afaict is not a straight copy-n-paste replacement

adodb_assoc_case: I took a short look at the adob code, and it seems only the oci8po driver supports it, not the std oci8 driver. I guess it must be an execution speed tradeoff decidion...

[oniTony]

ah, I found the files :) My only concern that's left with the structure are the sessions. My working version is

DROP TABLE sessions CASCADE CONSTRAINTS;
CREATE TABLE sessions (
session_id VARCHAR(40) DEFAULT '' NOT NULL,
session_data CLOB,
session_updated DATE DEFAULT SYSDATE,
session_created DATE DEFAULT SYSDATE NOT NULL,
PRIMARY KEY (session_id)
);
CREATE INDEX idx_session_updated ON sessions (session_updated);
CREATE INDEX idx_session_created ON sessions (session_created);

In dotProject's MySQL, session_id is varchar(40), not (64).

There is also no session_user field. And since the shema defines it as "NOT NULL", it breaks on every session INSERT.

session_updated should default to CURRENT_TIMESTAMP (SYSDATE in Oracle)

I took the liberty of defaulting session_created to SYSDATE as well (instead of 1969, or MySQL's 0000), though it doesn't matter since it's NOT NULL, and dotProject always passes SYSDATE value to it on INSERT.

---

You're right, ADOdb's unixtime is not a 'paste-in' solution. For complete database independance, I would export math to PHP, so instead of

if ($max < $qid->fields['SESSION_LIFESPAN']

it would be something like

if ($max < (math_to_calculate_lifespan) )

The whole thing is limited to just two functions: dPsessionRead and dPsessionGC, no big deal.


only the oci8po driver supports [adodb_assoc_case], not the std oci8 driver

so does the question becomes - does every other driver support (ADODB_ASSOC_CASE, 1) to work with changed['CODE'] ? Perhaps adodb-oci8.inc.php could be extended to support ASSOC_CASE, 0 ? Insercept fetched results? :???:

[oniTony]

ok, so here's the hack:

ensure that

define('ADODB_ASSOC_CASE', 0);

is set. top of index.php should be sufficient for application's execution, and it might be a good idea to do if(!defined('ADODB_ASSOC_CASE') check for the install files.

making this work for _every_ database under the ADOdb sun!

in lib/adodb/adodb.inc.php

function &FetchRow()
{
if ($this->EOF) {
$false = false;
return $false;
}
// $arr = $this->fields;

// ensure the constant is defined
if (!defined('ADODB_ASSOC_CASE')) {
// default to low case, since dotProject assumes that
define('ADODB_ASSOC_CASE', 0);
}

// ported from adodb-oci8po.inc.php
// function _updatefields()
$arr = array();
$lowercase = (ADODB_ASSOC_CASE == 0);

foreach($this->fields as $k => $v) {
if (is_integer($k)) $arr[$k] = $v;
else {
if ($lowercase)
$arr[strtolower($k)] = $v;
else
$arr[strtoupper($k)] = $v;
}
}

$this->_currentRow++;
if (!$this->_fetch()) $this->EOF = true;

return $arr;
}


I essentially took oci8po's _updatefields() handling of ADODB_ASSOC_CASE and placed it on a much more general adodb.inc.php level. Success, though I suspect an unneccessary performance hit with databases where such update is not neccessary (MySQL?).

It would probably make more sense to move this down to drivers of specific databases that need this, but in case of oci8, that's 4 functions implementing @OCIfetchinto that would need to be modified.

[oniTony]

projects table is missing a field

project_active DECIMAL(4) DEFAULT 1,

[oniTony]

The query.class.php would have to be seriously overhauled to allow for database level function calls. It should ultimately look up if ADOdb's drivers can handle the call, and if not, fall back onto a PHP generic implemintation if possible.

Case of point (other then the previously discussed UNIXTIMESTAMP) - modules/admin/index.php

$q->addQuery('DISTINCT UPPER(SUBSTRING(user_username, 1, 1)) AS L');

Oracle's function is SUBSTR (and luckly takes the same arguments). I can only guess at the plethora of variations for all the other databases.

[giunta.gaetano@sea-aeropo]

Duh, you're right about some inconsistencies in table columns.

The reason is I started out from a current CVS version of the dotproject.sql file.

If the next DP version is going to be built from CVS head, there is not much sense in maintaining a multi-db script that rebuilds the 204 schema (except for testing, of course).
Otoh, even if dp 205 is ever released, I do not think the maintainers would include something as highly experimental as this in a pointpoint release: it would contain only critical / security related fixes.

I am now uploading 2 schema files, one for 204 (testing) and one for cvs, to sf.net (NB: the default data inserts for 204 are still missing)

About default values for the sessions.session_updated col: in the current sql I cannot find any trace of it, and it is not in the dp 204 sql either.

It really does make sense, anyway, so I think I will
-leave the 204 version with the current default values, ie. zero
-fix the cvs version

About fixing adodb: please post any patches to the adob forums (you can find the link on adodb.sf.net). The author is quite responsive, and hopefully we can get some feedback...

[caseydk]

Actually, the recommended database abstraction tool is PDO as demonstrated by it's coverage in the ZCE exam, official support from a number of organizations, and coverage in the php manual. I'm not saying that a conversion for dP is on the agenda, but it's something to keep in mind.

[oniTony]

Actually, the recommended database abstraction tool is PDO
Ah, but the PDO requires PHP5, which would break dotProject for people on out-dated PHP4 servers (such as myself :???: )

Additionally the oracle drivers for PDO are documented (http://ca3.php.net/manual/en/ref.pdo-oci.php) to be "EXPERIMENTAL"

[caseydk]

You missed the second half of what I said:

"I'm not saying that a conversion for dP is on the agenda, but it's something to keep in mind."

At some point, we're going to have to convert to full PHP5 / mysql5 support... that's steadily happening as you can see in these forums.

The next step would be deprecating PHP4 support and taking advantage of the useful - and often better - functionality in PHP5.

THERE IS NO PLAN/DISCUSSION/EFFORT TO DO THIS, but at some point it is going to happen.

[oniTony]

sorry Keith, I misunderstood what you were saying.

dotProject could certanly benifit from PHP5's functionality, and I do realize that getting dotProject to work cross-database requires enough changes for a major point release.

Though I'm still interested in persuing the possibility of getting dotProject to run with Oracle and, other than perhaps giunta.gaetano@sea-aeropo, it looks like I'll be hacking at the 2.0.4 and ADOdb alone.

That being said, here's another embedded MySQL gem in query.class.php

function prepare($clear = false)
{
switch ($this->type) {
...
case 'drop':
$q = 'DROP TABLE IF EXISTS ' . $this->_table_prefix . $this->create_table;
break;

IF EXISTS does not exist (pun totally intended) in Oracle. The replacement is kind of complicated and bulky, it might be simpler to just ignore the errors silenty :???:

The creation of temporary tables in Oracle also requires an extra keyword
CREATE GLOBAL TEMPORARY TABLE
and an ending clause

ON COMMIT PRESERVE ROWS;

alternativly there's ON COMMIT DELETE ROWS;

but since the tables are dropped anyways, preserving rows might be a safe bet.

[oniTony]

Actually with Oracle's nested selects

SELECT a FROM b JOIN (SELECT c FROM d) ON c.id = a.id

all this temporary tables mess is avoided all together.

A much neater solution (quite possibly a performance improvement) alas this is limited to Oracle and other (postgres ?) databases supporting the feature. For full DB independance, perhaps this section of code could be loaded based on the configuration, although that would make two versions to maintain.

[giunta.gaetano@sea-aeropo]

AFAICT, migrating first to a "fully portable adodb version" is a goal worth the effort:
- people might use it without having to wait for the mysql5/php5 version
- all mysql-isms found and fixed now are preparatory work for a migration to dbo

As a side note, mysql5 is fine with me, but I think deprecating php4 is only going to hurt the project, as php5 adoption has been less than stellar so far, and a lot of people are already waiting for php 6, preferring to skip version 5 altogether.

[oniTony]

- people might use it without having to wait for the mysql5/php5 version

As I've mentioned before, there are a _lot_ of fixes to be made. It would have to be a complete fork of the project. Granted that some people might be interested in alternative database options.


- all mysql-isms found and fixed now are preparatory work for a migration to dbo
The database cleanup would certanly be benificial. Some code (taks/organizer.php) scares me

$taskgroups = mysql_query($sql);
while($tg = mysql_fetch_array($taskgroups)) {

Tasks _is_ a core module, right?

[oniTony]

I've run into some odd errors, and ultimately that prompted me to examine the Oracle install SQL once more. This would basically be the summary of major differences between 2.0.4 and CVS:

There are new fields:

company_country VARCHAR(100) DEFAULT ''
event_url VARCHAR(255),
forum_message_count DECIMAL(10) DEFAULT 0 NOT NULL,
task_dep_reset_dates DECIMAL(3) DEFAULT 0,
pref_group VARCHAR(255) DEFAULT '',
pref_type VARCHAR(255) DEFAULT ''


and the entire TICKETS table is missing (Ticketsmith taken out / turned off?)

but there are some new tables found

CREATE TABLE user_prefs_list (
pref_list_id DECIMAL(10) NOT NULL,
pref_name VARCHAR(255) NOT NULL,
pref_list_name VARCHAR(255) NOT NULL,

CREATE TABLE webcal_projects (

CREATE TABLE webcal_resources (


Now I thought that 2.0.5 was going to be a bug-fix release, but there's a clear extension of preference options, as well as improvement to some tables. Introduction of webcal would definitly fall under 'new feature' ;)

(and the reason I come across this, is that Oracle is very picky about the SELECT * FROM table GROUP BY statements, and needs every new field to be included in such statements, otherwise it breaks.)

[caseydk]

AFAICT, migrating first to a "fully portable adodb version" is a goal worth the effort:
- people might use it without having to wait for the mysql5/php5 version
- all mysql-isms found and fixed now are preparatory work for a migration to dbo

Agreed.

As a side note, mysql5 is fine with me, but I think deprecating php4 is only going to hurt the project, as php5 adoption has been less than stellar so far, and a lot of people are already waiting for php 6, preferring to skip version 5 altogether.

Yes, I'm quite aware. I spoke at the DCPHP Conference two weeks ago and attended the Zend PHP Conference this week and had the opportunity to have a beer with many of the people involved. ;) PHP5 penetration is approximately 25% of all PHP hosts out there with the remainder being PHP4 only. If you stop to look at the specific versions, it gets even crazier.

I didn't say it would happen any time soon, but at some point it *is* going to happen just like PHP3 is no longer supported. I am working on a number of things - such as Unit Testing using phpUnit and a REST interface - that will only ever be PHP5 compatible.

Hopefully this will convince some of our users to upgrade... and if they don't, they can still use dotProject, they just won't be able to use these modules.

[oniTony]

mmm... PHP5 support :mrgreen:

One feature that's missing from Oracle (and is vital for dotProject's functionality) is LAST_INSERT_ID() .

A hack around this assumes that all the sequences are named as seq_$table and modifies ADOdb to use that

if ($table != ''){
$temp = $this->GetCol("SELECT seq_".$table.".currval FROM dual");
return $temp[0];
}

Global change of db_insert_id() to db_insert_id($table) (or the appropriate table name when variable is not available) passes the table name information down to the abstraction layer.

[oniTony]

sorry giunta.gaetano, I just can't seem to do a complete check of the database schema..

for CREATE TABLE user_tasks (

it should have been PRIMARY KEY (user_id, task_id) (not user_id, user_type)

user_type isn't even an index, so both
CREATE INDEX user_type ON user_tasks (user_type);
CREATE INDEX index_ut_to_tasks ON user_tasks (task_id);
should be dropped

I'm hoping that's the last of it, I haven't been running into any DB schema related problems lately

[caseydk]

Excellent, thanks, it'd be nice if that was the last thing. ;)

[oniTony]

This would probably be the most appropriate place to post such a personal message:

The work term is over, and the project has been complete. I've gotten dotProject to work with Oracle 10g, but it's been a mess. I get the rest of the year (that would be a week) off, and then it's back to University to study. only 4 more years, only 4 more years... *sigh*

You guy have been great, thank you for all your support. :)

(yeah, I'll still visit on occasion, and continue to subscribe to Keith's blog, but who knows what other projects (actually I have a list) I will pursue)

Cheers,
-- Tony

[MacOfTheEast]

You guy have been great, thank you for all your support. :)
Thank you, Tony. It's been great "working" with you. We wish you all the best for the Holiday, New Year, and your future travels.

Very Best Regards,
Mac

[caseydk]

Tony,

You have been a great member of this community and I greatly appreciate your thoughts, contributions, and sense of humor. ;)

Have a good one and keep up the good work!