Installation with Oracle

RQ · Post by RQ » Thu Sep 20, 2007 9:38 am

Hi there,
this subject has been raised already, but in another, much less appropriate place (Accessibility forum), so it didn't get much attention. Hence, I'm raising it again here.

I would like to install CMS Made Simple and configure it to work with Oracle. So, what I did was install it on MySQL (btw it would really be cool if the installer supported AdoDB too), then duplicated everything I could to an Oracle scheme. I'm not saying "everything" because there were a few problems, namely, a name of the table "cms_additional_htmlblob_users_seq" was too long (33 chars, while Oracle seems to have a limit of 30 characters for table/sequence names). Anyway, after duplicating all the data, I configured cmsms the following way:

Code: Select all

define('ADODB_ASSOC_CASE', 0);         # required to get table and column names in lowercase
$config['dbms'] = 'oci8po';            # ADOdb calls it "portable version of oci8 driver"
$config['db_hostname'] = false;        # read adodb-oci8.inc.php for more info about connection modes
$config['db_username'] = 'scheme_name';
$config['db_password'] = 'password';
$config['db_name'] = 'service_name';   # from tnsnames.ora
$config['use_adodb_lite'] = false;

Now the problem. CMSMS actually works with such configuration, but not fully. It reads and displays the content (or at least pages) appropriately, and it also allows to edit content. However, whenever I try to create NEW content, I get a following warning from PHP:

Warning: ociexecute() [function.ociexecute]: OCIStmtExecute: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 in /path/to/cmsms/lib/adodb/drivers/adodb-oci8.inc.php on line 1026

adodb_oci8po._execute(INSERT INTO cms_content (content_id, content_name, content_alias, type, owner_id, parent_id, template_id, item_order, hierarchy,..., Array[23]) % line 765, file: adodb-oci8.inc.php
adodb_oci8po.execute(INSERT INTO cms_content (content_id, content_name, content_alias, type, owner_id, parent_id, template_id, item_order, hierarchy,..., Array[23]) % line 1130, file: class.content.inc.php
content.insert() % line 943, file: class.content.inc.php
content.save() % line 287, file: addcontent.php

Warning: ociexecute() [function.ociexecute]: OCIStmtExecute: ORA-00933: SQL command not properly ended in /path/to/cmsms/lib/adodb/drivers/adodb-oci8.inc.php on line 1026

adodb_oci8po._execute(SELECT * FROM cms_module_search_items WHERE
extra_attr = ? AND content_id = ? LIMIT 1, Array[2]) % line 765, file: adodb-oci8.inc.php
adodb_oci8po.execute(SELECT * FROM cms_module_search_items WHERE
extra_attr = ? AND content_id = ? LIMIT 1, Array[2]) % line 408, file: Search.module.php
search.doevent(Core, ContentEditPost, Array[1]) % line 129, file: class.events.inc.php
events.sendevent(Core, ContentEditPost, Array[1]) % line 955, file: class.content.inc.php
content.save() % line 287, file: addcontent.php

Well, the second error is a bit strange, but the first one causes me real problems. Here's what debug output below says about it:

(oci8po): INSERT INTO cms_content (content_id, content_name, content_alias, type, owner_id, parent_id, template_id, item_order, hierarchy, id_hierarchy, active, default_content, show_in_menu, cachable, menu_text, markup, metadata, titleattribute, accesskey, tabindex, last_modified_by, create_date, modified_date) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) [ (0=>'142') (1=>'testuuuuukas') (2=>'testuuuuukas') (3=>'content') (4=>'1') (5=>'52') (6=>'20') (7=>'3') (8=>'') (9=>'') (10=>'1') (11=>'0') (12=>'1') (13=>'1') (14=>'testuuuuukas') (15=>'html') (16=>'') (17=>'') (18=>'') (19=>'') (20=>'1') (21=>'TO_DATE('2007-09-20, 10:53:11 AM','RRRR-MM-DD, HH:MI:SS AM')') (22=>'TO_DATE('2007-09-20, 10:53:11 AM','RRRR-MM-DD, HH:MI:SS AM')') ]

Meanwhile here's an example of a working statement that succeedes:

(oci8po):
INSERT INTO cms_content_props
(
content_id,
type,
prop_name,
param1,
param2,
param3,
content,
modified_date
)
VALUES
(
?,?,?,'','','',?,TO_DATE('2007-09-20, 12:32:18 PM','RRRR-MM-DD, HH:MI:SS AM')
)
[ (0=>'143') (1=>'string') (2=>'content_en') (3=>'<p>Informacija Å¡iuo metu rengiama.</p>') ]

Comparing these two statements, I get to think that my problem (the content isn't being added to the cms_content table) is caused by the fact that in the first (non-working) case both "TO_DATE" parameters are passed in the parameter array as strings, hence Oracle sees them as strings and not TO_DATE calls. But I may be wrong here.

The question is – what should I do to solve this problem? I'll be gratefull for any help.

Versions: CMSMS 1.1, PHP 4.4.7, OCI8 module $Revision: 1.183.2.18.2.4 $, AdoDB 4.94, SunOS 5.9.

RQ · Post by RQ » Thu Sep 20, 2007 10:07 am

Hm, ok. I managed to make CMSMS create new content by patching class.content.inc.php the following way:

Code: Select all

--- ./lib/classes/class.content.inc.php.orig	Fri Aug 24 11:02:00 2007
+++ ./lib/classes/class.content.inc.php	Thu Sep 20 12:55:30 2007
@@ -1101,7 +1101,8 @@
 
 		$this->mModifiedDate = $this->mCreationDate = trim($db->DBTimeStamp(time()), "'");
 
-		$query = "INSERT INTO ".$config["db_prefix"]."content (content_id, content_name, content_alias, type, owner_id, parent_id, template_id, item_order, hierarchy, id_hierarchy, active, default_content, show_in_menu, cachable, menu_text, markup, metadata, titleattribute, accesskey, tabindex, last_modified_by, create_date, modified_date) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+/* RQ */
+		$query = "INSERT INTO ".$config["db_prefix"]."content (content_id, content_name, content_alias, type, owner_id, parent_id, template_id, item_order, hierarchy, id_hierarchy, active, default_content, show_in_menu, cachable, menu_text, markup, metadata, titleattribute, accesskey, tabindex, last_modified_by, create_date, modified_date) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," . $this->mModifiedDate . "," . $this->mCreationDate . ")";
 
 		$dbresult = $db->Execute($query, array(
 			$newid,
@@ -1124,9 +1125,9 @@
 			$this->mTitleAttribute,
 			$this->mAccessKey,
 			$this->mTabIndex,
-			$this->mLastModifiedBy,
-			$this->mModifiedDate,
-			$this->mCreationDate
+			$this->mLastModifiedBy //,
+//			$this->mModifiedDate,
+//			$this->mCreationDate
 			));
 
 		if (! $dbresult)

The second error is still there though... Plus, there are more errors apparently. Should I manually modify all date passes in the same way in all files?

cyberman · Post by **cyberman** » Thu Sep 20, 2007 3:05 pm

To the first - I've never used oracle.

Which AdoDB version do you are using?

There are two Adodb version which are supported, AdoDB and AdoDB lite. CMSms default is AdoDB lite.

http://adodb.sourceforge.net/
http://adodblite.sourceforge.net/

But for instance (full) AdoDB Version has a better working support for SQLite. Maybe it's the same with oracle.

You have to load full adodb and can switch it then in config.php. Set use_adodb_lite to false.

Dont forget a "Clear cache"

.

RQ · Post by RQ » Thu Sep 20, 2007 8:32 pm

cyberman wrote: Which AdoDB version do you are using?

There are two Adodb version which are supported, AdoDB and AdoDB lite. CMSms default is AdoDB lite.

Come on, you haven't really read my posting, have you?

The only thing I forgot to mention in it was the version of AdoDB, which I'll add there now.

And yes, AdoDB supports oracle through oci8 module.

Cheers!

cyberman · Post by **cyberman** » Fri Sep 21, 2007 1:11 pm

cyberman wrote: To the first - I've never used oracle.

... and I have no skills with oracle and only limited coding skills. I'm only a supporter.

Oracle is official not supported. But I've tried to help to make the creation of an oracle powered CMSms easier.

And if you read my answer correctly (also between the lines) you will found my answer - I haven't one.

Good luck! Wish you success!

RQ · Post by RQ » Fri Sep 21, 2007 1:57 pm

cyberman wrote:
cyberman wrote: To the first - I've never used oracle.
... and I have no skills with oracle and only limited coding skills. I'm only a supporter.

Oracle is official not supported. But I've tried to help to make the creation of an oracle powered CMSms easier.

And if you read my answer correctly (also between the lines) you will found my answer - I haven't one.

Good luck! Wish you success!

Please don't feel insulted for my comment if you do. I just teased you a bit because answers to your questions are provided in my first posting. See the config part of it: I use AdoDB (not AdoDB Lite), and its oci8po module which is designed specifically for oracle (not SQLite or anything else).

Its version was the latest I found when creating the website, and that is 4.94 (this is the only thing I forgot to mention in my first posting).

Thanks again for your support.

Oh, BTW I had an IRC chat with a few developers of cmsms yesterday and today. It seems like I'll attempt to create a patch (for CMSMS1.2 though) which should make it support Oracle a bit better.

Regards, and have a nice weekend!

Pierre M. · Post by **Pierre M.** » Fri Sep 21, 2007 3:41 pm

Hello,

RQ wrote: ...there were a few problems, namely, a name of the table "cms_additional_htmlblob_users_seq" was too long (33 chars, while Oracle seems to have a limit of 30 characters for table/sequence names).

Isn't this a technical stopper for Oracle support ?
I like to prefix my db objetcs. Taking the above example, it could lead to "cms_some_project_name_some_version_additional_htmlblob_users_seq".

But cool you are ircing to make it for 1.2.

Pierre M.

calguy1000 · Post by **calguy1000** » Fri Sep 21, 2007 3:48 pm

if you're having problems with the dates, and the table names you're up for a large project. that may not be worth it.

The Dates are required for a lot of functionality in alot of modules. They should work, or be made to work.

Table names in CMS Made simple are fairly long, and there are longer ones in alot of the modules.
It looks like this will be a sizeable project with only you doing it, and little support. and a constant headache with maintenance. I'm not sure its worth it.

RQ · Post by RQ » Fri Sep 21, 2007 4:13 pm

Pierre M. wrote: Hello,

RQ wrote: ...there were a few problems, namely, a name of the table "cms_additional_htmlblob_users_seq" was too long (33 chars, while Oracle seems to have a limit of 30 characters for table/sequence names).
Isn't this a technical stopper for Oracle support ?
I like to prefix my db objetcs. Taking the above example, it could lead to "cms_some_project_name_some_version_additional_htmlblob_users_seq".

You can always abbreviate stuff, and there are always limits, bigger or smaller, aren't there?

But cool you are ircing to make it for 1.2.

Yeah, and I have this little paranoia that I'd have to redo that for 2.0...

calguy1000 wrote: if you're having problems with the dates, and the table names you're up for a large project. that may not be worth it.

The Dates are required for a lot of functionality in alot of modules. They should work, or be made to work.

That's what I'm trying to do – make them work. Seems easy, so far... I hope that it's as easy as it seems.

Table names in CMS Made simple are fairly long, and there are longer ones in alot of the modules.
It looks like this will be a sizeable project with only you doing it, and little support. and a constant headache with maintenance. I'm not sure its worth it.

The project is a university website.
Like I said, I think it's rather possible to change those names to something shorter. And it's actually easier than making dates work.

Not that I'm gonna do that (for now).

RQ · Post by RQ » Sat Sep 22, 2007 1:16 pm

OK, so I patched CMSMS 1.2beta1 already. The patch is here, and I hope I'll convince developers to integrate it into SVN.

After updating the website in question from CMSMS1.1, I ran upgrade.php, and then had to re-add a line to the config to make CMSMS actually work instead of hitting an infinite loop while enumerating plugins:

Code: Select all

define('ADODB_ASSOC_CASE', 0);         # required to get table and column names in lowercase

The problem I'm facing now, when trying to reach Content > Pages, seems more related to instability of the beta I checked out from SVN:

Fatal error: Call to undefined method: newsmodule->getname() in /path/to/cmsms/modules/News/contenttype.news.php on line 226

calguy1000 · Post by **calguy1000** » Sat Sep 22, 2007 3:32 pm

The patch won't be in for 1.2. It's too late in the process
but I did fix the error with GetName() and the stupid news content type (it's deprecated by the way).

RQ · Post by RQ » Sun Sep 23, 2007 6:45 pm

calguy1000 wrote: The patch won't be in for 1.2. It's too late in the process

Where could it go then?

calguy1000 wrote: but I did fix the error with GetName() and the stupid news content type (it's deprecated by the way).

Hm, what isn't then?

calguy1000 · Post by **calguy1000** » Sun Sep 23, 2007 6:52 pm

Well:
a) 1.2 will hopefully be the last release of the 1.x series of CMS Made Simple (hopefully). So we can work on 2.0
b) your patch will have to be re-done for 2.0 there's going to be significant changes in the underlying schema in 2.0
c) we don't have a testing environment for oracle, so we really can't support this patch at this time. In order to support oracle, we' would need to get a long standing user, with oracle access, who is willing to maintain oracle support on our development team.

RQ · Post by RQ » Mon Sep 24, 2007 4:44 am

calguy1000 wrote: Well:
a) 1.2 will hopefully be the last release of the 1.x series of CMS Made Simple (hopefully). So we can work on 2.0
b) your patch will have to be re-done for 2.0 there's going to be significant changes in the underlying schema in 2.0
c) we don't have a testing environment for oracle, so we really can't support this patch at this time. In order to support oracle, we' would need to get a long standing user, with oracle access, who is willing to maintain oracle support on our development team.

I know, and I'm not asking you to support Oracle. However, if I understand correctly, that way of passing a date (not in an array of arguments, but as a part of the statement) is the preferred one, and the patch doesn't break compatibility with MySQL or anything else (this should be tested though).

There are some other bugs in SQL statements used in CMSMS that seem to appear when using Oracle, and I guess I'd have to fix those too. However, I just don't want to deviate too far from the original sources just because I'd like to be able to update as smoothly as possible.

Anyhow:
a) would you reconsider the patch now, please?

b) what if I create a patch for the 2.0 branch the same way?
c) would you accept a recomendation of using shorter table names than in 1.2 in your new schema? As I said before, Oracle has a limit of 30 characters per table/sequence name, and, particularly, a string "additional_htmlblob_users_seq" is already 29 characters long, hence leaving only one character available for the table prefix. I suggest that you strip at least 3 more characters from this name. Again, I don't ask you to support Oracle, but this would certainly make it easier to install CMSMS on it
d) what is the status of 2.0 at the moment?
e) you said the news content type is deprecated. Did you talk about the whole News module? What should I use instead of it then?

cyberman · Post by **cyberman** » Mon Sep 24, 2007 6:39 pm

RQ wrote: d) what is the status of 2.0 at the moment?

It's planned to release V2 in first quarter 2008.

e) you said the news content type is deprecated. Did you talk about the whole News module?

No he meant only the content type ...

CMS Made Simple Forums

Installation with Oracle

Installation with Oracle

Re: Installation with Oracle

Re: Installation with Oracle

Re: Installation with Oracle

Re: Installation with Oracle

Re: Installation with Oracle

Re: Installation with Oracle

Re: Installation with Oracle

Re: Installation with Oracle

Re: Installation with Oracle

Re: Installation with Oracle

Re: Installation with Oracle

Re: Installation with Oracle

Re: Installation with Oracle

Re: Installation with Oracle