Any Jasperserver db script for Oracle?

[jbchurn]

Hi,

I'm a newbie to Jasperserver.

I intend to use my existing oracle database for jasperserver database.

But where can I find/download the jasperserver database script in oracle database script format?

Please advise.

Thank you.

Regards,
Jap.

[jbchurn]

Is there anyone can advise?

 

Thanks.

[gabrielinux]

Not sure... but check out this post:  http://jasperforge.org/plugins/espforum/view.php?group_id=112&forumid=102&topicid=157

 

[jbchurn]

Hi,

 

I afraid the post you refer to does not help much because I'm looking for Oracle script to populate the Jasperserver database.

[jbchurn]

I found the following thread that contains information on building DDL for different database from source code.

 

http://jasperforge.org/plugins/espforum/view.php?group_id=112&forumid=102&topicid=44640

[halfer]

I've mentioned this on another thread just now, but thought I would add the same sentiment here. I can see why you would want to move the JasperServer core database to your existing db platform, but my guess is that it's more trouble than it's worth. We use JasperServer + MySQL on a server just for the basic reporting server, and then connect to Oracle sitting on a separate server.

 

You should find it will run more quickly configured like this anyway, since JasperServer then does not have to make db calls to a separate server (or if you are running Oracle on the same box as JasperServer then you haven't reached a performance ceiling anyway).

[jbchurn]

Hi,

Thanks for your advice and I will consider it.

 

Between, since you use Oracle as JasperServer core database before, can you advise me how to generate the sql script for Oracle database?

 

I try to following some thread to build from source, but I still yet to find the script. Your advice is very much appreciated.

 

See my thread on this topic.

http://jasperforge.org/plugins/espforum/view.php?group_id=112&forumid=102&topicid=46448

 

Thanks.

[kitakura]

Attached is JS 2.0.1 schema. 

Please use /jasperserver-3.0/scripts/upgrade/upgrade-postgresql-2.1.0-3.0.0 to upgrade from JS 2.0.1 to 3.0.

 

[mmflynn]

JasperServer Pro includes repository scripts/support for commercial databases such as Oracle.

 

Mary

[jbchurn]

Hi Kitakura,

 

When I click on the attachment, the file I download is empty.

 

Between, can you teach me how you generate the script in Oracle? Would like to learn from you for my future use.

 

Thank you.

[jbchurn]

Hi Mary,

 

I do aware of that, but I'm on the JasperServer Community Edition. So, I don't have the script.

 

Thank you.

[kitakura]

My DBA found this somewhere.  I am sorry I don't know how to create schema dump.

Once again, this is JS 2 so you need to upgrade to 3.0 using /jasperserver-3.0/scripts/upgrade/upgrade-postgresql-2.1.0-3.0.0.

Code:

-- Oracle META-DATA Schema Create statements for JasperServer 2.0.1-- Adapted from the MySQL Schema Create ddl by J.Murray on 9 Oct 2007-- Distribute freely.  No responsibility taken for any errors or omissions.    CREATE TABLE JIBEANDATASOURCE (        ID NUMBER(19) NOT NULL,        BEANNAME VARCHAR(100) NOT NULL,        BEANMETHOD VARCHAR(100),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JICONTENTRESOURCE (        ID NUMBER(19) NOT NULL,        DATA BLOB,        FILE_TYPE VARCHAR(20),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JICUSTOMDATASOURCE (        ID NUMBER(19) NOT NULL,        SERVICECLASS VARCHAR(250) NOT NULL,        PRIMARY KEY (ID)    ) ;    CREATE TABLE JICUSTOMDATASOURCEPROPERTY (        DS_ID NUMBER(19) NOT NULL,        VALUE VARCHAR(4000),        NAME VARCHAR(100) NOT NULL,        PRIMARY KEY (DS_ID, NAME)    ) ;    CREATE TABLE JIDATATYPE (        ID NUMBER(19) NOT NULL,        TYPE NUMBER(4),        MAXLENGTH NUMBER(10),        DECIMALS NUMBER(10),        REGULAREXPR VARCHAR(255),        MINVALUE RAW(255),        MAXVALUE RAW(255),        STRICTMIN NUMBER(1),        STRICTMAX NUMBER(1),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIFILERESOURCE (        ID NUMBER(19) NOT NULL,        DATA BLOB,        FILE_TYPE VARCHAR(20),        REFERENCE NUMBER(19),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIINPUTCONTROL (        ID NUMBER(19) NOT NULL,        TYPE NUMBER(4),        MANDATORY NUMBER(1),        READONLY NUMBER(1),        VISIBLE NUMBER(1),        DATA_TYPE NUMBER(19),        LIST_OF_VALUES NUMBER(19),        LIST_QUERY NUMBER(19),        QUERY_VALUE_COLUMN VARCHAR(40),        DEFAULTVALUE RAW(255),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIINPUTCONTROLQUERYCOLUMN (        INPUT_CONTROL_ID NUMBER(19) NOT NULL,        QUERY_COLUMN VARCHAR(40) NOT NULL,        COLUMN_INDEX NUMBER(10) NOT NULL,        PRIMARY KEY (INPUT_CONTROL_ID, COLUMN_INDEX)    ) ;    CREATE TABLE JIJNDIJDBCDATASOURCE (        ID NUMBER(19) NOT NULL,        JNDINAME VARCHAR(100) NOT NULL,        TIMEZONE VARCHAR(100),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIJDBCDATASOURCE (        ID NUMBER(19) NOT NULL,        DRIVER VARCHAR(100) NOT NULL,        PASSWORD VARCHAR(100),        CONNECTIONURL VARCHAR(200),        USERNAME VARCHAR(100),        TIMEZONE VARCHAR(100),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JILISTOFVALUES (        ID NUMBER(19) NOT NULL,        PRIMARY KEY (ID)    ) ;    CREATE TABLE JILISTOFVALUESITEM (        ID NUMBER(19) NOT NULL,        LABEL VARCHAR(255),        VALUE RAW(255),        IDX NUMBER(10) NOT NULL,        PRIMARY KEY (ID, IDX)    ) ;    CREATE TABLE JILOGEVENT (        ID NUMBER(19) NOT NULL ,        OCCURRENCE_DATE DATE NOT NULL,        EVENT_TYPE NUMBER(4) NOT NULL,        USERNAME VARCHAR(100),        COMPONENT VARCHAR(100),        MESSAGE VARCHAR(250) NOT NULL,        RESOURCE_URI VARCHAR(250),        EVENT_TEXT VARCHAR(4000),        EVENT_DATA LONG RAW,        EVENT_STATE NUMBER(4),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIMONDRIANCONNECTION (        ID NUMBER(19) NOT NULL,        REPORTDATASOURCE NUMBER(19),        MONDRIANSCHEMA NUMBER(19),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIMONDRIANXMLADEFINITION (        ID NUMBER(19) NOT NULL,        CATALOG VARCHAR(100) NOT NULL,        MONDRIANCONNECTION NUMBER(19),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIOBJECTPERMISSION (        ID NUMBER(19) NOT NULL ,        URI VARCHAR(255) NOT NULL,        RECIPIENTOBJECTCLASS VARCHAR(100),        RECIPIENTOBJECTID NUMBER(19),        PERMISSIONMASK NUMBER(10) NOT NULL,        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIOLAPCLIENTCONNECTION (        ID NUMBER(19) NOT NULL,        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIOLAPUNIT (        ID NUMBER(19) NOT NULL,        OLAPCLIENTCONNECTION NUMBER(19),        MDX_QUERY VARCHAR(4000) NOT NULL,        VIEW_OPTIONS BLOB,        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIPROFILEATTRIBUTE (        ID NUMBER(19) NOT NULL ,        ATTRNAME VARCHAR(255) NOT NULL,        ATTRVALUE VARCHAR(255) NOT NULL,        PRINCIPALOBJECTCLASS VARCHAR(255) NOT NULL,        PRINCIPALOBJECTID NUMBER(19) NOT NULL,        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIQUERY (        ID NUMBER(19) NOT NULL,        DATASOURCE NUMBER(19),        QUERY_LANGUAGE VARCHAR(40) NOT NULL,        SQL_QUERY VARCHAR(4000) NOT NULL,        PRIMARY KEY (ID)    ) ;	CREATE TABLE JIREPORTDATASOURCE (        ID  NUMBER(19)                                NOT NULL,        PRIMARY KEY (ID)    );	    CREATE TABLE JIREPORTJOB (        ID NUMBER(19) NOT NULL ,        VERSION NUMBER(10) NOT NULL,        USERNAME VARCHAR(100) NOT NULL,        LABEL VARCHAR(100) NOT NULL,        DESCRIPTION VARCHAR(4000),        REPORT_UNIT_URI VARCHAR(200) NOT NULL,        JOB_TRIGGER NUMBER(19) NOT NULL,        BASE_OUTPUT_NAME VARCHAR(100) NOT NULL,        OUTPUT_LOCALE VARCHAR(20),        CONTENT_DESTINATION NUMBER(19),        MAIL_NOTIFICATION NUMBER(19),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIREPORTJOBCALENDARTRIGGER (        ID NUMBER(19) NOT NULL,        MINUTES VARCHAR(200) NOT NULL,        HOURS VARCHAR(80) NOT NULL,        DAYS_TYPE NUMBER(4) NOT NULL,        WEEK_DAYS VARCHAR(20),        MONTH_DAYS VARCHAR(100),        MONTHS VARCHAR(40) NOT NULL,        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIREPORTJOBMAIL (        ID NUMBER(19) NOT NULL ,        VERSION NUMBER(10) NOT NULL,        SUBJECT VARCHAR(100) NOT NULL,        MESSAGE VARCHAR(4000) NOT NULL,        SEND_TYPE NUMBER(4) NOT NULL,        SKIP_EMPTY NUMBER(1) NOT NULL,        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIREPORTJOBMAILRECIPIENT (        DESTINATION_ID NUMBER(19) NOT NULL,        RECIPIENT_TYPE NUMBER(4) NOT NULL,        ADDRESS VARCHAR(100) NOT NULL,        RECIPIENT_IDX NUMBER(10) NOT NULL,        PRIMARY KEY (DESTINATION_ID, RECIPIENT_IDX)    ) ;    CREATE TABLE JIREPORTJOBOUTPUTFORMAT (        REPORT_JOB_ID NUMBER(19) NOT NULL,        OUTPUT_FORMAT NUMBER(4) NOT NULL,        PRIMARY KEY (REPORT_JOB_ID, OUTPUT_FORMAT)    ) ;    CREATE TABLE JIREPORTJOBPARAMETER (        JOB_ID NUMBER(19) NOT NULL,        PARAMETER_VALUE RAW(1000),        PARAMETER_NAME VARCHAR(100) NOT NULL,        PRIMARY KEY (JOB_ID, PARAMETER_NAME)    ) ;    CREATE TABLE JIREPORTJOBREPODEST (        ID NUMBER(19) NOT NULL ,        VERSION NUMBER(10) NOT NULL,        FOLDER_URI VARCHAR(200) NOT NULL,        SEQUENTIAL_FILENAMES NUMBER(1) NOT NULL,        OVERWRITE_FILES NUMBER(1) NOT NULL,        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIREPORTJOBSIMPLETRIGGER (        ID NUMBER(19) NOT NULL,        OCCURRENCE_COUNT NUMBER(10) NOT NULL,        RECURRENCE_INTERVAL NUMBER(10),        RECURRENCE_INTERVAL_UNIT NUMBER(4),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIREPORTJOBTRIGGER (        ID NUMBER(19) NOT NULL ,        VERSION NUMBER(10) NOT NULL,        TIMEZONE VARCHAR(40),        START_TYPE NUMBER(4) NOT NULL,        START_DATE DATE,        END_DATE DATE,        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIREPORTUNIT (        ID NUMBER(19) NOT NULL,        REPORTDATASOURCE NUMBER(19),        QUERY NUMBER(19),        MAINREPORT NUMBER(19),        CONTROLRENDERER VARCHAR(100),        REPORTRENDERER VARCHAR(100),        PROMPTCONTROLS NUMBER(1),        CONTROLSLAYOUT NUMBER(4),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIREPORTUNITINPUTCONTROL (        REPORT_UNIT_ID NUMBER(19) NOT NULL,        INPUT_CONTROL_ID NUMBER(19) NOT NULL,        CONTROL_INDEX NUMBER(10) NOT NULL,        PRIMARY KEY (REPORT_UNIT_ID, CONTROL_INDEX)    ) ;    CREATE TABLE JIREPORTUNITRESOURCE (        REPORT_UNIT_ID NUMBER(19) NOT NULL,        RESOURCE_ID NUMBER(19) NOT NULL,        RESOURCE_INDEX NUMBER(10) NOT NULL,        PRIMARY KEY (REPORT_UNIT_ID, RESOURCE_INDEX)    ) ;    CREATE TABLE JIREPOSITORYCACHE (        ID NUMBER(19) NOT NULL ,        URI VARCHAR(200) NOT NULL,        CACHE_NAME VARCHAR(20) NOT NULL,        DATA BLOB,        VERSION NUMBER(10) NOT NULL,        VERSION_DATE DATE NOT NULL,        ITEM_REFERENCE NUMBER(19),        PRIMARY KEY (ID),        UNIQUE (URI, CACHE_NAME)    ) ;    CREATE TABLE JIRESOURCE (        ID NUMBER(19) NOT NULL ,        VERSION NUMBER(10) NOT NULL,        NAME VARCHAR(100) NOT NULL,        PARENT_FOLDER NUMBER(19) NOT NULL,        CHILDRENFOLDER NUMBER(19),        LABEL VARCHAR(100) NOT NULL,        DESCRIPTION VARCHAR(250),        CREATION_DATE DATE NOT NULL,        PRIMARY KEY (ID),        UNIQUE (NAME, PARENT_FOLDER)    ) ;    CREATE TABLE JIRESOURCEFOLDER (        ID NUMBER(19) NOT NULL ,        VERSION NUMBER(10) NOT NULL,        URI VARCHAR(250) NOT NULL,        HIDDEN NUMBER(1),        NAME VARCHAR(100) NOT NULL,        LABEL VARCHAR(100) NOT NULL,        DESCRIPTION VARCHAR(250),        PARENT_FOLDER NUMBER(19),        CREATION_DATE DATE NOT NULL,        PRIMARY KEY (ID),        UNIQUE (URI)    ) ;    CREATE TABLE JIROLE (        ID NUMBER(19) NOT NULL ,        ROLENAME VARCHAR(100) NOT NULL UNIQUE,        EXTERNALLYDEFINED NUMBER(1),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIUSER (        ID NUMBER(19) NOT NULL ,        USERNAME VARCHAR(100) NOT NULL UNIQUE,        FULLNAME VARCHAR(100) NOT NULL,        EMAILADDRESS VARCHAR(100),        PASSWORD VARCHAR(100),        EXTERNALLYDEFINED NUMBER(1),        ENABLED NUMBER(1),        PRIMARY KEY (ID)    ) ;    CREATE TABLE JIUSERROLE (        ROLEID NUMBER(19) NOT NULL,        USERID NUMBER(19) NOT NULL,        PRIMARY KEY (USERID, ROLEID)    ) ;    CREATE TABLE JIXMLACONNECTION (        ID NUMBER(19) NOT NULL,        CATALOG VARCHAR(100) NOT NULL,        USERNAME VARCHAR(100) NOT NULL,        PASSWORD VARCHAR(100) NOT NULL,        DATASOURCE VARCHAR(100) NOT NULL,        URI VARCHAR(100) NOT NULL,        PRIMARY KEY (ID)    ) ;CREATE SEQUENCE HIBERNATE_SEQUENCE START WITH 100;CREATE OR REPLACE TRIGGER JIUSER_PK_BIR  BEFORE INSERT  on JIUSER  for each rowdeclarebegin   if nvl(:new.ID,-1) < 1 then       select HIBERNATE_SEQUENCE.nextval into :new.ID from dual;   end if;end;CREATE OR REPLACE TRIGGER JIROLE_PK_BIR  BEFORE INSERT  on JIROLE  for each rowdeclarebegin   if nvl(:new.ID,-1) < 1 then       select HIBERNATE_SEQUENCE.nextval into :new.ID from dual;   end if;end;CREATE OR REPLACE TRIGGER JIRESOURCEFOLDER_PK_BIR  BEFORE INSERT  on JIRESOURCEFOLDER  for each rowdeclarebegin   if nvl(:new.ID,-1) < 1 then       select HIBERNATE_SEQUENCE.nextval into :new.ID from dual;   end if;end;CREATE OR REPLACE TRIGGER JIRESOURCE_PK_BIR  BEFORE INSERT  on JIRESOURCE  for each rowdeclarebegin   if nvl(:new.ID,-1) < 1 then       select HIBERNATE_SEQUENCE.nextval into :new.ID from dual;   end if;end;CREATE OR REPLACE TRIGGER JIREPOSITORYCACHE_PK_BIR  BEFORE INSERT  on JIREPOSITORYCACHE  for each rowdeclarebegin   if nvl(:new.ID,-1) < 1 then       select HIBERNATE_SEQUENCE.nextval into :new.ID from dual;   end if;end;CREATE OR REPLACE TRIGGER JIREPORTJOBTRIGGER_PK_BIR  BEFORE INSERT  on JIREPORTJOBTRIGGER  for each rowdeclarebegin   if nvl(:new.ID,-1) < 1 then       select HIBERNATE_SEQUENCE.nextval into :new.ID from dual;   end if;end;CREATE OR REPLACE TRIGGER JIREPORTJOBREPODEST_PK_BIR  BEFORE INSERT  on JIREPORTJOBREPODEST  for each rowdeclarebegin   if nvl(:new.ID,-1) < 1 then       select HIBERNATE_SEQUENCE.nextval into :new.ID from dual;   end if;end;CREATE OR REPLACE TRIGGER JIREPORTJOBMAIL_PK_BIR  BEFORE INSERT  on JIREPORTJOBMAIL  for each rowdeclarebegin   if nvl(:new.ID,-1) < 1 then       select HIBERNATE_SEQUENCE.nextval into :new.ID from dual;   end if;end;CREATE OR REPLACE TRIGGER JIREPORTJOB_PK_BIR  BEFORE INSERT  on JIREPORTJOB  for each rowdeclarebegin   if nvl(:new.ID,-1) < 1 then       select HIBERNATE_SEQUENCE.nextval into :new.ID from dual;   end if;end;CREATE OR REPLACE TRIGGER JIPROFILEATTRIBUTE_PK_BIR  BEFORE INSERT  on JIPROFILEATTRIBUTE  for each rowdeclarebegin   if nvl(:new.ID,-1) < 1 then       select HIBERNATE_SEQUENCE.nextval into :new.ID from dual;   end if;end;CREATE OR REPLACE TRIGGER JILOGEVENT_PK_BIR  BEFORE INSERT  on JILOGEVENT  for each rowdeclarebegin   if nvl(:new.ID,-1) < 1 then       select HIBERNATE_SEQUENCE.nextval into :new.ID from dual;   end if;end;CREATE OR REPLACE TRIGGER JIOBJECTPERMISSION_PK_BIR  BEFORE INSERT  on JIOBJECTPERMISSION  for each rowdeclarebegin   if nvl(:new.ID,-1) < 1 then       select HIBERNATE_SEQUENCE.nextval into :new.ID from dual;   end if;end;	    ALTER TABLE JIBEANDATASOURCE         ADD CONSTRAINT FK_JIBEANDATASOURCEID         FOREIGN KEY (ID)         REFERENCES JIRESOURCE (ID);    ALTER TABLE JICONTENTRESOURCE         ADD CONSTRAINT FK_JICONTENTRESOURCEID         FOREIGN KEY (ID)         REFERENCES JIRESOURCE (ID);    ALTER TABLE JICUSTOMDATASOURCE         ADD CONSTRAINT FK_JICUSTOMDATASOURCEID         FOREIGN KEY (ID)         REFERENCES JIRESOURCE (ID);    CREATE INDEX IDX_JICUSTOMDATASOURCEPROPDSID ON JICUSTOMDATASOURCEPROPERTY (DS_ID);     ALTER TABLE JICUSTOMDATASOURCEPROPERTY         ADD CONSTRAINT FK_JICUSTOMDATASOURCEPROPDSID         FOREIGN KEY (DS_ID)         REFERENCES JICUSTOMDATASOURCE (ID);    ALTER TABLE JIDATATYPE         ADD CONSTRAINT FK_JIDATATYPEID         FOREIGN KEY (ID)         REFERENCES JIRESOURCE (ID);    CREATE INDEX IDX_JIFILERESOURCEREF ON JIFILERESOURCE (REFERENCE);     ALTER TABLE JIFILERESOURCE         ADD CONSTRAINT FK_JIFILERESOURCEREF         FOREIGN KEY (REFERENCE)         REFERENCES JIFILERESOURCE (ID);    ALTER TABLE JIFILERESOURCE         ADD CONSTRAINT FK_JIFILERESOURCEID         FOREIGN KEY (ID)         REFERENCES JIRESOURCE (ID);    CREATE INDEX IDX_JIINPUTCONTROLDT ON JIINPUTCONTROL (DATA_TYPE);     CREATE INDEX IDX_JIINPUTCONTROLLQ ON JIINPUTCONTROL (LIST_QUERY);     CREATE INDEX IDX_JIINPUTCONTROLLOV ON JIINPUTCONTROL (LIST_OF_VALUES);     ALTER TABLE JIINPUTCONTROL         ADD CONSTRAINT FK_JIINPUTCONTROLDT         FOREIGN KEY (DATA_TYPE)         REFERENCES JIDATATYPE (ID);    ALTER TABLE JIINPUTCONTROL         ADD CONSTRAINT FK_JIINPUTCONTROLLQ         FOREIGN KEY (LIST_QUERY)         REFERENCES JIQUERY (ID);    ALTER TABLE JIINPUTCONTROL         ADD CONSTRAINT FK_JIINPUTCONTROLLOV        FOREIGN KEY (LIST_OF_VALUES)         REFERENCES JILISTOFVALUES (ID);    ALTER TABLE JIINPUTCONTROL         ADD CONSTRAINT FK_JIINPUTCONTROLID         FOREIGN KEY (ID)         REFERENCES JIRESOURCE (ID);	CREATE INDEX IDX_JIINPUTCONTROLQUERYCOLICID ON JIINPUTCONTROLQUERYCOLUMN (INPUT_CONTROL_ID);			    ALTER TABLE JIINPUTCONTROLQUERYCOLUMN         ADD CONSTRAINT FK_JIINPUTCONTROLQUERYCOLICID         FOREIGN KEY (INPUT_CONTROL_ID)         REFERENCES JIINPUTCONTROL (ID);    ALTER TABLE JIJNDIJDBCDATASOURCE         ADD CONSTRAINT FK_JIJNDIJDBCDATASOURCEID         FOREIGN KEY (ID)         REFERENCES JIRESOURCE (ID);    ALTER TABLE JIJDBCDATASOURCE         ADD CONSTRAINT FK_JIJDBCDATASOURCEID         FOREIGN KEY (ID)         REFERENCES JIRESOURCE (ID);    ALTER TABLE JILISTOFVALUES         ADD CONSTRAINT FK_JILISTOFVALUESID         FOREIGN KEY (ID)         REFERENCES JIRESOURCE (ID);    ALTER TABLE JILISTOFVALUESITEM         ADD CONSTRAINT FK_JILISTOFVALUESITEM         FOREIGN KEY (ID)         REFERENCES JILISTOFVALUES (ID);	CREATE INDEX IDX_JIMONDRIANCONNECTIONMS ON JIMONDRIANCONNECTION (MONDRIANSCHEMA);	CREATE INDEX IDX_JIMONDRIANCONNECTIONRDS ON JIMONDRIANCONNECTION (REPORTDATASOURCE);		    ALTER TABLE JIMONDRIANCONNECTION         ADD CONSTRAINT FK_JIMONDRIANCONNECTIONMS         FOREIGN KEY (MONDRIANSCHEMA)         REFERENCES JIFILERESOURCE (ID);    ALTER TABLE JIMONDRIANCONNECTION         ADD CONSTRAINT FK_JIMONDRIANCONNECTIONRDS         FOREIGN KEY (REPORTDATASOURCE)         REFERENCES JIRESOURCE (ID);    ALTER TABLE JIMONDRIANCONNECTION         ADD CONSTRAINT FK_JIMONDRIANCONNECTIONID         FOREIGN KEY (ID)         REFERENCES JIOLAPCLIENTCONNECTION (ID);	CREATE INDEX IDX_JIMONDRIANXMLADEFINITIONMC ON JIMONDRIANXMLADEFINITION (MONDRIANCONNECTION);    ALTER TABLE JIMONDRIANXMLADEFINITION         ADD CONSTRAINT FK_JIMONDRIANXMLADEFINITIONMC         FOREIGN KEY (MONDRIANCONNECTION)         REFERENCES JIMONDRIANCONNECTION (ID);    ALTER TABLE JIMONDRIANXMLADEFINITION         ADD CONSTRAINT FK_JIMONDRIANXMLADEFINITIONID         FOREIGN KEY (ID)         REFERENCES JIRESOURCE (ID);    ALTER TABLE JIOLAPCLIENTCONNECTION         ADD CONSTRAINT FK_JIOLAPCLIENTCONNECTIONID         FOREIGN KEY (ID)         REFERENCES JIRESOURCE (ID);	CREATE INDEX IDX_JIOLAPUNITOLAP ON JIOLAPUNIT (OLAPCLIENTCONNECTION);    ALTER TABLE JIOLAPUNIT         ADD CONSTRAINT FK_JIOLAPUNITOLAP         FOREIGN KEY (OLAPCLIENTCONNECTION)         REFERENCES JIOLAPCLIENTCONNECTION (ID);    ALTER TABLE JIOLAPUNIT         ADD CONSTRAINT FK_JIOLAPUNITID         FOREIGN KEY (ID)         REFERENCES JIRESOURCE (ID);    ALTER TABLE JIQUERY         ADD CONSTRAINT FK_JIQUERYDS         FOREIGN KEY (DATASOURCE)         REFERENCES JIRESOURCE (ID);    ALTER TABLE JIQUERY         ADD CONSTRAINT FK_JIQUERYID         FOREIGN KEY (ID)         REFERENCES JIRESOURCE (ID);	CREATE INDEX IDX_JIREPORTJOBMN ON JIREPORTJOB (MAIL_NOTIFICATION);	CREATE INDEX IDX_JIREPORTJOBCD ON JIREPORTJOB (CONTENT_DESTINATION);	CREATE INDEX IDX_JIREPORTJOBJT ON JIREPORTJOB (JOB_TRIGGER);    ALTER TABLE JIREPORTJOB         ADD CONSTRAINT FK_JIREPORTJOBMN         FOREIGN KEY (MAIL_NOTIFICATION)         REFERENCES JIREPORTJOBMAIL (ID);    ALTER TABLE JIREPORTJOB         ADD CONSTRAINT FK_JIREPORTJOBCD         FOREIGN KEY (CONTENT_DESTINATION)         REFERENCES JIREPORTJOBREPODEST (ID);    ALTER TABLE JIREPORTJOB         ADD CONSTRAINT FK_JIREPORTJOBTRIG         FOREIGN KEY (JOB_TRIGGER)         REFERENCES JIREPORTJOBTRIGGER (ID);    ALTER TABLE JIREPORTJOBCALENDARTRIGGER         ADD CONSTRAINT FK_JIREPORTJOBCALENDARTRIGID         FOREIGN KEY (ID)         REFERENCES JIREPORTJOBTRIGGER (ID);	CREATE INDEX IDX_JIREPORTJOBMAILRECDEST ON JIREPORTJOBMAILRECIPIENT (DESTINATION_ID);    ALTER TABLE JIREPORTJOBMAILRECIPIENT         ADD CONSTRAINT FK_JIREPORTJOBMAILRECDEST         FOREIGN KEY (DESTINATION_ID)         REFERENCES JIREPORTJOBMAIL (ID);	CREATE INDEX IDX_JIREPORTJOBOUTPUTFMTRJID ON JIREPORTJOBOUTPUTFORMAT (REPORT_JOB_ID);    ALTER TABLE JIREPORTJOBOUTPUTFORMAT         ADD CONSTRAINT FK_JIREPORTJOBOUTPUTFMTRJID         FOREIGN KEY (REPORT_JOB_ID)         REFERENCES JIREPORTJOB (ID);	CREATE INDEX IDX_JIREPORTJOBPARAMETERJOB ON JIREPORTJOBPARAMETER (JOB_ID);    ALTER TABLE JIREPORTJOBPARAMETER         ADD CONSTRAINT FK_JIREPORTJOBPARAMETERJID         FOREIGN KEY (JOB_ID)         REFERENCES JIREPORTJOB (ID);    ALTER TABLE JIREPORTJOBSIMPLETRIGGER         ADD CONSTRAINT FK_JIREPORTJOBSIMPLETRIGID         FOREIGN KEY (ID)         REFERENCES JIREPORTJOBTRIGGER (ID);    CREATE INDEX IDX_JIREPORTUNITQUERY ON JIREPORTUNIT (QUERY);    CREATE INDEX IDX_JIREPORTUNITRDS ON JIREPORTUNIT (REPORTDATASOURCE);    CREATE INDEX IDX_JIREPORTUNITMAIN ON JIREPORTUNIT (MAINREPORT);    ALTER TABLE JIREPORTUNIT         ADD CONSTRAINT FK_JIREPORTUNITQUERY         FOREIGN KEY (QUERY)         REFERENCES JIQUERY (ID);    ALTER TABLE JIREPORTUNIT         ADD CONSTRAINT FK_JIREPORTUNITRDS         FOREIGN KEY (REPORTDATASOURCE)         REFERENCES JIRESOURCE (ID);    ALTER TABLE JIREPORTUNIT         ADD CONSTRAINT FK_JIREPORTUNITMAIN         FOREIGN KEY (MAINREPORT)         REFERENCES JIFILERESOURCE (ID);    ALTER TABLE JIREPORTUNIT         ADD CONSTRAINT FK_JIREPORTUNITID         FOREIGN KEY (ID)         REFERENCES JIRESOURCE (ID);    CREATE INDEX IDX_JIREPORTUNITINPUTCTLICID ON JIREPORTUNITINPUTCONTROL (INPUT_CONTROL_ID);		    CREATE INDEX IDX_JIREPORTUNITINPUTCTLIRUID ON JIREPORTUNITINPUTCONTROL (REPORT_UNIT_ID);				    ALTER TABLE JIREPORTUNITINPUTCONTROL         ADD CONSTRAINT FK_JIREPORTUNITINPUTCTLICID         FOREIGN KEY (INPUT_CONTROL_ID)         REFERENCES JIINPUTCONTROL (ID);    ALTER TABLE JIREPORTUNITINPUTCONTROL         ADD CONSTRAINT FK_JIREPORTUNITINPUTCTLRUID         FOREIGN KEY (REPORT_UNIT_ID)         REFERENCES JIREPORTUNIT (ID);	CREATE INDEX IDX_JIREPORTUNITRESOURCERID ON JIREPORTUNITRESOURCE (RESOURCE_ID); 	CREATE INDEX IDX_JIREPORTUNITRESOURCERUID ON JIREPORTUNITRESOURCE (REPORT_UNIT_ID); 		    ALTER TABLE JIREPORTUNITRESOURCE         ADD CONSTRAINT FK_JIREPORTUNITRESOURCERID         FOREIGN KEY (RESOURCE_ID)         REFERENCES JIFILERESOURCE (ID);    ALTER TABLE JIREPORTUNITRESOURCE         ADD CONSTRAINT FK_JIREPORTUNITRESOURCERRUID         FOREIGN KEY (REPORT_UNIT_ID)         REFERENCES JIREPORTUNIT (ID);	CREATE INDEX IDX_JIREPOSITORYCACHEIREF ON JIREPOSITORYCACHE (ITEM_REFERENCE);     ALTER TABLE JIREPOSITORYCACHE         ADD CONSTRAINT FK_JIREPOSITORYCACHEIREF         FOREIGN KEY (ITEM_REFERENCE)         REFERENCES JIREPOSITORYCACHE (ID);	CREATE INDEX IDX_JIRESOURCEPF ON JIRESOURCE (PARENT_FOLDER); 	CREATE INDEX IDX_JIRESOURCECF ON JIRESOURCE (CHILDRENFOLDER);     ALTER TABLE JIRESOURCE         ADD CONSTRAINT FK_JIRESOURCEPF         FOREIGN KEY (PARENT_FOLDER)         REFERENCES JIRESOURCEFOLDER (ID);    ALTER TABLE JIRESOURCE         ADD CONSTRAINT FK_JIRESOURCECF         FOREIGN KEY (CHILDRENFOLDER)         REFERENCES JIRESOURCEFOLDER (ID);	CREATE INDEX IDX_JIRESOURCEFOLDERPF ON JIRESOURCEFOLDER (PARENT_FOLDER);             ALTER TABLE JIRESOURCEFOLDER         ADD CONSTRAINT FK_JIRESOURCEFOLDERPF         FOREIGN KEY (PARENT_FOLDER)         REFERENCES JIRESOURCEFOLDER (ID);	CREATE INDEX IDX_JIUSERROLEUSER ON JIUSERROLE (USERID); 	CREATE INDEX IDX_JIUSERROLEROLE ON JIUSERROLE (ROLEID);     ALTER TABLE JIUSERROLE         ADD CONSTRAINT FK_JIUSERROLEUSER         FOREIGN KEY (USERID)         REFERENCES JIUSER (ID);    ALTER TABLE JIUSERROLE         ADD CONSTRAINT FK_JIUSERROLEROLE         FOREIGN KEY (ROLEID)         REFERENCES JIROLE (ID);    ALTER TABLE JIXMLACONNECTION         ADD CONSTRAINT FK_JIXMLACONNECTIONID         FOREIGN KEY (ID)         REFERENCES JIOLAPCLIENTCONNECTION (ID);CREATE TABLE QRTZ_JOB_DETAILS(JOB_NAME VARCHAR(80) NOT NULL,JOB_GROUP VARCHAR(80) NOT NULL,DESCRIPTION VARCHAR(120) NULL,JOB_CLASS_NAME VARCHAR(128) NOT NULL,IS_DURABLE VARCHAR(1) NOT NULL,IS_VOLATILE VARCHAR(1) NOT NULL,IS_STATEFUL VARCHAR(1) NOT NULL,REQUESTS_RECOVERY VARCHAR(1) NOT NULL,JOB_DATA BLOB NULL,PRIMARY KEY (JOB_NAME,JOB_GROUP));CREATE TABLE QRTZ_JOB_LISTENERS (JOB_NAME VARCHAR(80) NOT NULL,JOB_GROUP VARCHAR(80) NOT NULL,JOB_LISTENER VARCHAR(80) NOT NULL,PRIMARY KEY (JOB_NAME,JOB_GROUP,JOB_LISTENER));    CREATE INDEX IDX_QRTZ_JOB_LISTENERS_JNJG ON QRTZ_JOB_LISTENERS (JOB_NAME, JOB_GROUP);     ALTER TABLE QRTZ_JOB_LISTENERS        ADD CONSTRAINT FK_QRTZ_JOB_LISTENERS_JNJG         FOREIGN KEY (JOB_NAME,JOB_GROUP)         REFERENCES QRTZ_JOB_DETAILS (JOB_NAME,JOB_GROUP);CREATE TABLE QRTZ_TRIGGERS (TRIGGER_NAME VARCHAR(80) NOT NULL,TRIGGER_GROUP VARCHAR(80) NOT NULL,JOB_NAME VARCHAR(80) NOT NULL,JOB_GROUP VARCHAR(80) NOT NULL,IS_VOLATILE VARCHAR(1) NOT NULL,DESCRIPTION VARCHAR(120) NULL,NEXT_FIRE_TIME NUMBER(13) NULL,PREV_FIRE_TIME NUMBER(13) NULL,TRIGGER_STATE VARCHAR(16) NOT NULL,TRIGGER_TYPE VARCHAR(8) NOT NULL,START_TIME NUMBER(13) NOT NULL,END_TIME NUMBER(13) NULL,CALENDAR_NAME VARCHAR(80) NULL,MISFIRE_INSTR NUMBER(2) NULL,JOB_DATA BLOB NULL,PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP));    CREATE INDEX IDX_QRTZ_TRIGGERS_JNJG ON QRTZ_TRIGGERS (JOB_NAME, JOB_GROUP);     ALTER TABLE QRTZ_TRIGGERS        ADD CONSTRAINT FK_QRTZ_TRIGGERS_JNJG         FOREIGN KEY (JOB_NAME,JOB_GROUP)         REFERENCES QRTZ_JOB_DETAILS (JOB_NAME,JOB_GROUP);CREATE TABLE QRTZ_SIMPLE_TRIGGERS (TRIGGER_NAME VARCHAR(80) NOT NULL,TRIGGER_GROUP VARCHAR(80) NOT NULL,REPEAT_COUNT NUMBER(7) NOT NULL,REPEAT_INTERVAL NUMBER(12) NOT NULL,TIMES_TRIGGERED NUMBER(7) NOT NULL,PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP));     ALTER TABLE QRTZ_SIMPLE_TRIGGERS        ADD CONSTRAINT FK_QRTZ_SIMPLE_TRIGGERS_TNTG         FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)         REFERENCES QRTZ_TRIGGERS (TRIGGER_NAME,TRIGGER_GROUP);CREATE TABLE QRTZ_CRON_TRIGGERS (TRIGGER_NAME VARCHAR(80) NOT NULL,TRIGGER_GROUP VARCHAR(80) NOT NULL,CRON_EXPRESSION VARCHAR(80) NOT NULL,TIME_ZONE_ID VARCHAR(80),PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP));     ALTER TABLE QRTZ_CRON_TRIGGERS        ADD CONSTRAINT FK_QRTZ_CRON_TRIGGERS_TNTG         FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)         REFERENCES QRTZ_TRIGGERS (TRIGGER_NAME,TRIGGER_GROUP);CREATE TABLE QRTZ_BLOB_TRIGGERS (TRIGGER_NAME VARCHAR(80) NOT NULL,TRIGGER_GROUP VARCHAR(80) NOT NULL,BLOB_DATA BLOB NULL,PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP));     ALTER TABLE QRTZ_BLOB_TRIGGERS        ADD CONSTRAINT FK_QRTZ_BLOB_TRIGGERS_TNTG         FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)         REFERENCES QRTZ_TRIGGERS (TRIGGER_NAME,TRIGGER_GROUP);CREATE TABLE QRTZ_TRIGGER_LISTENERS (TRIGGER_NAME VARCHAR(80) NOT NULL,TRIGGER_GROUP VARCHAR(80) NOT NULL,TRIGGER_LISTENER VARCHAR(80) NOT NULL,PRIMARY KEY (TRIGGER_NAME,TRIGGER_GROUP,TRIGGER_LISTENER));     CREATE INDEX IDX_QRTZ_TRIG_LISTENERS_TNTG ON QRTZ_TRIGGER_LISTENERS (TRIGGER_NAME, TRIGGER_GROUP);      ALTER TABLE QRTZ_TRIGGER_LISTENERS        ADD CONSTRAINT FK_QRTZ_TRIG_LISTENERS_TNTG         FOREIGN KEY (TRIGGER_NAME,TRIGGER_GROUP)         REFERENCES QRTZ_TRIGGERS (TRIGGER_NAME,TRIGGER_GROUP);CREATE TABLE QRTZ_CALENDARS (CALENDAR_NAME VARCHAR(80) NOT NULL,CALENDAR BLOB NOT NULL,PRIMARY KEY (CALENDAR_NAME));CREATE TABLE QRTZ_PAUSED_TRIGGER_GRPS (TRIGGER_GROUP VARCHAR(80) NOT NULL,PRIMARY KEY (TRIGGER_GROUP));CREATE TABLE QRTZ_FIRED_TRIGGERS (ENTRY_ID VARCHAR(95) NOT NULL,TRIGGER_NAME VARCHAR(80) NOT NULL,TRIGGER_GROUP VARCHAR(80) NOT NULL,IS_VOLATILE VARCHAR(1) NOT NULL,INSTANCE_NAME VARCHAR(80) NOT NULL,FIRED_TIME NUMBER(13) NOT NULL,STATE VARCHAR(16) NOT NULL,JOB_NAME VARCHAR(80) NULL,JOB_GROUP VARCHAR(80) NULL,IS_STATEFUL VARCHAR(1) NULL,REQUESTS_RECOVERY VARCHAR(1) NULL,PRIMARY KEY (ENTRY_ID));CREATE TABLE QRTZ_SCHEDULER_STATE (INSTANCE_NAME VARCHAR(80) NOT NULL,LAST_CHECKIN_TIME NUMBER(13) NOT NULL,CHECKIN_INTERVAL NUMBER(13) NOT NULL,RECOVERER VARCHAR(80) NULL,PRIMARY KEY (INSTANCE_NAME));CREATE TABLE QRTZ_LOCKS (LOCK_NAME VARCHAR(40) NOT NULL,PRIMARY KEY (LOCK_NAME));INSERT INTO QRTZ_LOCKS VALUES('TRIGGER_ACCESS');INSERT INTO QRTZ_LOCKS VALUES('JOB_ACCESS');INSERT INTO QRTZ_LOCKS VALUES('CALENDAR_ACCESS');INSERT INTO QRTZ_LOCKS VALUES('STATE_ACCESS');INSERT INTO QRTZ_LOCKS VALUES('MISFIRE_ACCESS');INSERT INTO JIUSER ( ID, USERNAME, FULLNAME, EMAILADDRESS, PASSWORD, EXTERNALLYDEFINED,ENABLED ) VALUES (1, 'jasperadmin', 'Jasper Administrator', NULL, 'newPassword', 0, 1); INSERT INTO JIUSER ( ID, USERNAME, FULLNAME, EMAILADDRESS, PASSWORD, EXTERNALLYDEFINED,ENABLED ) VALUES (2, 'anonymousUser', 'Anonymous User', NULL, NULL, 0, 1); INSERT INTO JIROLE ( ID, ROLENAME, EXTERNALLYDEFINED ) VALUES ( 1, 'ROLE_USER', 0); INSERT INTO JIROLE ( ID, ROLENAME, EXTERNALLYDEFINED ) VALUES ( 2, 'ROLE_ADMINISTRATOR', 0); INSERT INTO JIROLE ( ID, ROLENAME, EXTERNALLYDEFINED ) VALUES ( 3, 'ROLE_ANONYMOUS', 0); INSERT INTO JIUSERROLE ( ROLEID, USERID ) VALUES ( 2, 1); INSERT INTO JIUSERROLE ( ROLEID, USERID ) VALUES ( 1, 1); INSERT INTO JIUSERROLE ( ROLEID, USERID ) VALUES ( 3, 2); COMMIT;-----uri null errorInsert into JIOBJECTPERMISSION (ID, URI, RECIPIENTOBJECTCLASS, RECIPIENTOBJECTID, PERMISSIONMASK) Values  (1, 'repo:/', 'com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoRole', 2, 1);Insert into JIOBJECTPERMISSION (ID, URI, RECIPIENTOBJECTCLASS, RECIPIENTOBJECTID, PERMISSIONMASK) Values  (2, 'repo:/', 'com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoRole', 1, 2);Insert into JIRESOURCEFOLDER (ID, VERSION, URI, HIDDEN, NAME, LABEL, DESCRIPTION, PARENT_FOLDER, CREATION_DATE) Values (1, 0, '/', 0, '/', 'root', 'Root of the folder hierarchy', NULL, SYSDATE);COMMIT;

[jbchurn]

Looks like it's really hard to find someone to provide advice on creating jasperserver database script in Oracle.

 

[swood]

Have a look at the Source Build Guide to see how to generate the script for Oracle.

JasperServer Professional Edition comes with Oracle support build in. You can get it at www.jaspersoft.com/downloads.html

 

Sherman

Jaspersoft

[halfer]

jbchurn wrote:

Hi, Thanks for your advice and I will consider it. Between, since you use Oracle as JasperServer core database before, can you advise me how to generate the sql script for Oracle database?

Sorry, you misread my post. We've never used Oracle as the JasperServer core database. I see in the other post you've referenced that you are rebuilding the server - honestly it looks more trouble than it's worth... use MySQL. Why are you so keen to use Oracle anyway?

[senthilm]

Hi sherman, I have JasperServer-Source-Build-Guide.pdf. It contains only for mysql and PostgreSQL. How can i build oracle script for JS 3.5.0 CE? i want to configure JS 3.50 with oracle 9i. Kindly help me to proceed.. Thanks & Regards, senthilm

 

[swood]

As I said in a previous post in this thread, "Have a look at the Source Build Guide to see how to generate the script for Oracle."

 

 

Sherman

Jaspersoft

[senthilm]

Yes. it is working. but i am getting error while viewing report after the creation of report from jrxml file.
Error is..

11:09:08,738  WARN JDBCExceptionReporter,http-8080-2:77 - SQL Error: 17090, SQLS
tate: null
11:09:08,738 ERROR JDBCExceptionReporter,http-8080-2:78 - operation not allowed:
 streams type cannot be used in batching
11:09:08,738 ERROR AbstractFlushingEventListener,http-8080-2:301 - Could not syn
chronize database state with session
org.hibernate.exception.GenericJDBCException: could not insert: [com.jaspersoft.
jasperserver.api.metadata.common.service.impl.hibernate.persistent.CachedItem]
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException
(SQLStateConverter.java:103)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.j
ava:91)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelp
er.java:43)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstrac
tEntityPersister.java:2217)
        at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstrac
tEntityPersister.java:2610)
        at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.ja
va:52)
        at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232)

 

can anybody tell me how to fix the above Error?

 

Thanks in Advance,

senthilm

Post Edited by senthilm at 07/29/2009 05:38