Running ContentBox with Oracle

Communities ContentBox CMS
1

Has anyone been able to run ContentBox with Oracle 11G? I am working on getting the latest stable release to run with Oracle 11G. Was wondering if anyone else has been able to do this. I have taken a microsoft sql server 2012 database and converted it for Oracle. Not really sure how I should handle the Auto Increment Identities. Here is the script.

CREATE TABLE CB_COMMENTSUBSCRIPTIONS
(
SUBSCRIPTIONID NUMBER(10) NOT NULL,
FK_CONTENTID NUMBER(10) NOT NULL
)

CREATE TABLE cb_author (
authorID NUMBER(10) NOT NULL,
firstName VARCHAR2(100) NOT NULL,
lastName VARCHAR2(100) NOT NULL,
email VARCHAR2(255) NOT NULL,
username VARCHAR2(100) NOT NULL,
password VARCHAR2(100) NOT NULL,
isActive NUMBER(3) NOT NULL,
lastLogin DATE,
createdDate DATE NOT NULL,
biography varchar2(4000),
preferences varchar2(4000),
FK_roleID NUMBER(10) NOT NULL,
PRIMARY KEY (authorID)
);
CREATE TABLE cb_authorPermissions (
FK_authorID NUMBER(10) NOT NULL,
FK_permissionID NUMBER(10) NOT NULL
);
CREATE TABLE cb_category (
categoryID NUMBER(10) NOT NULL,
category VARCHAR2(200) NOT NULL,
slug VARCHAR2(200) NOT NULL,
PRIMARY KEY (categoryID)
);

CREATE TABLE cb_comment (
commentID NUMBER(10) NOT NULL,
content varchar2(4000) NOT NULL,
author VARCHAR2(100) NOT NULL,
authorIP VARCHAR2(100) NOT NULL,
authorEmail VARCHAR2(255) NOT NULL,
authorURL VARCHAR2(255),
createdDate DATE NOT NULL,
isApproved NUMBER(3) NOT NULL,
FK_contentID NUMBER(10) NOT NULL,
PRIMARY KEY (commentID)
);
CREATE TABLE cb_commentSubscriptions (
subscriptionID NUMBER(10) NOT NULL,
FK_contentID NUMBER(10) NOT NULL,
constraint cb_commentSubscriptions_pk PRIMARY KEY (subscriptionID)
);
CREATE TABLE cb_content (
contentID NUMBER(10) NOT NULL,
contentType VARCHAR2(255) NOT NULL,
title VARCHAR2(200) NOT NULL,
slug VARCHAR2(200) NOT NULL,
createdDate DATE NOT NULL,
publishedDate DATE,
expireDate DATE,
isPublished NUMBER(3) NOT NULL,
allowComments NUMBER(3) NOT NULL,
passwordProtection VARCHAR2(100),
HTMLKeywords VARCHAR2(160),
HTMLDescription VARCHAR2(160),
cache NUMBER(3) NOT NULL,
cacheLayout NUMBER(3) NOT NULL,
cacheTimeout NUMBER(10),
cacheLastAccessTimeout NUMBER(10),
markup VARCHAR2(100) NOT NULL,
showInSearch NUMBER(3) NOT NULL,
FK_authorID NUMBER(10) NOT NULL,
FK_parentID NUMBER(10),
PRIMARY KEY (contentID)
);
CREATE TABLE cb_contentCategories (
FK_contentID NUMBER(10) NOT NULL,
FK_categoryID NUMBER(10) NOT NULL
);
CREATE TABLE cb_contentStore (
contentID NUMBER(10) NOT NULL,
description VARCHAR2(500),
PRIMARY KEY (contentID)
);
CREATE TABLE cb_contentVersion (
contentVersionID NUMBER(10) NOT NULL,
content varchar2(4000) NOT NULL,
changelog varchar2(4000),
version NUMBER(10) NOT NULL,
createdDate DATE NOT NULL,
isActive NUMBER(3) NOT NULL,
FK_authorID NUMBER(10) NOT NULL,
FK_contentID NUMBER(10) NOT NULL,
PRIMARY KEY (contentVersionID)
);

CREATE TABLE cb_customfield (
customFieldID NUMBER(10) NOT NULL,
key VARCHAR2(255) NOT NULL,
value varchar2(4000) NOT NULL,
FK_contentID NUMBER(10),
PRIMARY KEY (customFieldID)
);
CREATE TABLE cb_entry (
contentID NUMBER(10) NOT NULL,
excerpt varchar2(4000),
PRIMARY KEY (contentID)
);
CREATE TABLE cb_loginAttempts (
loginAttemptsID NUMBER(10) NOT NULL,
value VARCHAR2(255) NOT NULL,
attempts NUMBER(10) NOT NULL,
createdDate DATE NOT NULL,
lastLoginSuccessIP VARCHAR2(100),
PRIMARY KEY (loginAttemptsID)
);
CREATE TABLE cb_menu (
menuID NUMBER(10) NOT NULL,
title VARCHAR2(200) NOT NULL,
slug VARCHAR2(200) NOT NULL,
menuClass VARCHAR2(160),
listClass VARCHAR2(160),
listType VARCHAR2(20),
createdDate DATE NOT NULL,
PRIMARY KEY (menuID)
);
CREATE TABLE cb_menuItem (
menuItemID NUMBER(10) NOT NULL,
menuType VARCHAR2(255) NOT NULL,
title VARCHAR2(200) NOT NULL,
label VARCHAR2(200),
itemClass VARCHAR2(200),
data VARCHAR2(255),
active NUMBER(3),
FK_menuID NUMBER(10) NOT NULL,
FK_parentID NUMBER(10),
js VARCHAR2(255),
urlClass VARCHAR2(255),
menuSlug VARCHAR2(255),
contentSlug VARCHAR2(255),
target VARCHAR2(255),
mediaPath VARCHAR2(255),
url VARCHAR2(255),
PRIMARY KEY (menuItemID)
);
CREATE TABLE cb_module (
moduleID NUMBER(10) NOT NULL,
name VARCHAR2(255) NOT NULL,
title VARCHAR2(255),
version VARCHAR2(255),
entryPoint VARCHAR2(255),
author VARCHAR2(255),
webURL VARCHAR2(500),
forgeBoxSlug VARCHAR2(255),
description varchar2(2000),
isActive NUMBER(3) NOT NULL,
PRIMARY KEY (moduleID)
);
CREATE TABLE cb_page (
contentID NUMBER(10) NOT NULL,
layout VARCHAR2(200),
mobileLayout VARCHAR2(200),
“order” NUMBER(10),
showInMenu NUMBER(3) NOT NULL,
excerpt varchar2(4000),
SSLOnly NUMBER(3) NOT NULL,
PRIMARY KEY (contentID)
);
CREATE TABLE cb_permission (
permissionID NUMBER(10) NOT NULL,
permission VARCHAR2(255) NOT NULL,
description VARCHAR2(500),
PRIMARY KEY (permissionID)
);
CREATE TABLE cb_relatedContent (
FK_contentID NUMBER(10) NOT NULL,
FK_relatedContentID NUMBER(10) NOT NULL
);
CREATE TABLE cb_role (
roleID NUMBER(10) NOT NULL,
role VARCHAR2(255) NOT NULL,
description VARCHAR2(500),
PRIMARY KEY (roleID)
);
CREATE TABLE cb_rolePermissions (
FK_roleID NUMBER(10) NOT NULL,
FK_permissionID NUMBER(10) NOT NULL
);
CREATE TABLE cb_securityRule (
ruleID NUMBER(10) NOT NULL,
whitelist VARCHAR2(255),
securelist VARCHAR2(255) NOT NULL,
roles VARCHAR2(255),
permissions VARCHAR2(500),
redirect VARCHAR2(500) NOT NULL,
useSSL NUMBER(3),
“order” NUMBER(10) NOT NULL,
“match” VARCHAR2(50),
PRIMARY KEY (ruleID)
);
CREATE TABLE cb_setting (
settingID NUMBER(10) NOT NULL,
name VARCHAR2(100) NOT NULL,
value varchar2(4000) ,
PRIMARY KEY (settingID)
);
CREATE TABLE cb_stats (
statsID NUMBER(10) NOT NULL,
hits NUMBER(19),
FK_contentID NUMBER(10),
PRIMARY KEY (statsID)
);
CREATE TABLE cb_subscribers (
subscriberID NUMBER(10) NOT NULL,
subscriberEmail VARCHAR2(255) NOT NULL,
subscriberToken VARCHAR2(255) NOT NULL,
createdDate DATE NOT NULL,
PRIMARY KEY (subscriberID)
);
CREATE TABLE cb_subscriptions (
subscriptionID NUMBER(10) NOT NULL,
subscriptionToken VARCHAR2(255) NOT NULL,
type VARCHAR2(255) NOT NULL,
createdDate DATE NOT NULL,
FK_subscriberID NUMBER(10) NOT NULL,
PRIMARY KEY (subscriptionID)
);

ALTER TABLE cb_author
ADD FOREIGN KEY (FK_roleID)
REFERENCES cb_role (roleID);

ALTER TABLE cb_authorPermissions
ADD FOREIGN KEY (FK_permissionID)
REFERENCES cb_permission (permissionID);

ALTER TABLE cb_authorPermissions
ADD FOREIGN KEY (FK_authorID)
REFERENCES cb_author (authorID);

ALTER TABLE cb_comment
ADD FOREIGN KEY (FK_contentID)
REFERENCES cb_content (contentID);

ALTER TABLE cb_content
ADD FOREIGN KEY (FK_parentID)
REFERENCES cb_content (contentID);

ALTER TABLE cb_content
ADD FOREIGN KEY (FK_authorID)
REFERENCES cb_author (authorID);

ALTER TABLE cb_contentCategories
ADD FOREIGN KEY (FK_contentID)
REFERENCES cb_content (contentID);

ALTER TABLE cb_contentCategories
ADD FOREIGN KEY (FK_categoryID)
REFERENCES cb_category (categoryID);

ALTER TABLE cb_contentStore
ADD FOREIGN KEY (contentID)
REFERENCES cb_content (contentID);

ALTER TABLE cb_contentVersion
ADD FOREIGN KEY (FK_contentID)
REFERENCES cb_content (contentID);

ALTER TABLE cb_contentVersion
ADD FOREIGN KEY (FK_authorID)
REFERENCES cb_author (authorID);

ALTER TABLE cb_customfield
ADD FOREIGN KEY (FK_contentID)
REFERENCES cb_content (contentID);

ALTER TABLE cb_entry
ADD FOREIGN KEY (contentID)
REFERENCES cb_content (contentID);

ALTER TABLE cb_menuItem
ADD FOREIGN KEY (FK_menuID)
REFERENCES cb_menu (menuID);

ALTER TABLE cb_menuItem
ADD FOREIGN KEY (FK_parentID)
REFERENCES cb_menuItem (menuItemID);

ALTER TABLE cb_page
ADD FOREIGN KEY (contentID)
REFERENCES cb_content (contentID);

ALTER TABLE cb_relatedContent
ADD FOREIGN KEY (FK_contentID)
REFERENCES cb_content (contentID);

ALTER TABLE cb_relatedContent
ADD FOREIGN KEY (FK_relatedContentID)
REFERENCES cb_content (contentID);

ALTER TABLE cb_rolePermissions
ADD constraint FOREIGN KEY (FK_permissionID)
REFERENCES cb_permission (permissionID);

ALTER TABLE cb_rolePermissions
ADD FOREIGN KEY (FK_roleID)
REFERENCES cb_role (roleID);

ALTER TABLE cb_stats
ADD FOREIGN KEY (FK_contentID)
REFERENCES cb_content (contentID);

ALTER TABLE cb_subscriptions
ADD FOREIGN KEY (FK_subscriberID)
REFERENCES cb_subscribers (subscriberID);

ALTER TABLE CB_COMMENTSUBSCRIPTIONS ADD (
constraint fk_commentsub_subscriptions FOREIGN KEY (SUBSCRIPTIONID)
REFERENCES CB_SUBSCRIPTIONS (SUBSCRIPTIONID),
constraint fk_commentsub_content FOREIGN KEY (FK_CONTENTID)
REFERENCES CB_CONTENT (CONTENTID));

Thanks,

George Murphy

2

Personally, it won’t be ContentBox but the conversion to Oracle you did.

Which I think you know, so here is what I would do. Fire up a test instance and install fresh for Oracle and then compare your script to what the table should be. If you’re game, you could then write scripts to migrate the data over.

3

I have the database running. Here is what I have done. I created sequences for all of the database tables that needed it.

I referenced those sequences in the models like this. property name=“roleID” fieldtype=“id” generator=“sequence” sequence=“cb_role_seq” setter=“false”; Searched for fieldtype=“id” to gather all of the tables that needed to have the generator changed from native to sequence.

I am not getting this error.

Error Type: org.hibernate.exception.GenericJDBCException : 907
Error Messages: could not execute query

4

Hi Andrew,

I took your suggestion and loaded the Oracle database without any issues. I did run into timeout errors. Never got the app to fully load.