Script DB for Sybase ASE and SQL Server !!!

[khsam2001]

hi

i post here the database script  for jasperServer, this script work well with Sybase ASE , and to test them with SQLServer.

if you have this exception :

[see nested exception: com.sybase.jdbc2.jdbc.SybSQLException: FOR UPDATE can not be used in a SELECT which is not part of the declaration of a cursor or which is not inside a stored procedure.]

 

clear the conent of "js.quartz.base.properties" at : <tomcatHome>webapps/jasperserver/WEB-INF/

and insert this lignes :

 

# This file contains only properties that don't normally change.
# It gets used in the Spring bean for the Quartz scheduler.
# Other properties are set by referring to settings in js.quartz.properties

org.quartz.scheduler.instanceName=JasperServerScheduler
org.quartz.threadPool.threadCount=2
org.quartz.threadPool.threadPriority=3

org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.MSSQLDelegate
org.quartz.jobStore.misfireThreshold=180000
org.quartz.jobStore.selectWithLockSQL=SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?
org.quartz.jobStore.class=org.springframework.scheduling.quartz.LocalDataSourceJobStore
org.quartz.jobStore.dontSetNonManagedTXConnectionAutoCommitFalse=true
org.quartz.jobStore.dontSetAutoCommitFalse=true

good luck !!!

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

script "create tables" :

/*
* 
* date : 10/02/2009
* author : @SKH
*/

IF OBJECT_ID ('dbo.JIBeanDatasource') IS NOT NULL
    DROP TABLE dbo.JIBeanDatasource
GO

IF OBJECT_ID ('dbo.JIContentResource') IS NOT NULL
    DROP TABLE dbo.JIContentResource
GO

IF OBJECT_ID ('dbo.JICustomDatasourceproperty') IS NOT NULL
    DROP TABLE dbo.JICustomDatasourceproperty
GO

IF OBJECT_ID ('dbo.JIInputControlQueryColumn') IS NOT NULL
    DROP TABLE dbo.JIInputControlQueryColumn
GO

IF OBJECT_ID ('dbo.JIJNDIJdbcDatasource') IS NOT NULL
    DROP TABLE dbo.JIJNDIJdbcDatasource
GO

IF OBJECT_ID ('dbo.JIJdbcDatasource') IS NOT NULL
    DROP TABLE dbo.JIJdbcDatasource
GO

IF OBJECT_ID ('dbo.JIListOfValuesItem') IS NOT NULL
    DROP TABLE dbo.JIListOfValuesItem
GO

IF OBJECT_ID ('dbo.JILogEvent') IS NOT NULL
    DROP TABLE dbo.JILogEvent
GO

IF OBJECT_ID ('dbo.JIMondrianXMLADefinition') IS NOT NULL
    DROP TABLE dbo.JIMondrianXMLADefinition
GO

IF OBJECT_ID ('dbo.JIObjectPermission') IS NOT NULL
    DROP TABLE dbo.JIObjectPermission
GO

IF OBJECT_ID ('dbo.JIReportJobCalendarTrigger') IS NOT NULL
    DROP TABLE dbo.JIReportJobCalendarTrigger
GO

IF OBJECT_ID ('dbo.JIReportJobMailRecipient') IS NOT NULL
    DROP TABLE dbo.JIReportJobMailRecipient
GO

IF OBJECT_ID ('dbo.JIReportJobOutputFormat') IS NOT NULL
    DROP TABLE dbo.JIReportJobOutputFormat
GO

IF OBJECT_ID ('dbo.JIReportJobParameter') IS NOT NULL
    DROP TABLE dbo.JIReportJobParameter
GO

IF OBJECT_ID ('dbo.JIOlapUnit') IS NOT NULL
    DROP TABLE dbo.JIOlapUnit
GO

IF OBJECT_ID ('dbo.JIProfileAttribute') IS NOT NULL
    DROP TABLE dbo.JIProfileAttribute
GO

IF OBJECT_ID ('dbo.JIReportJobSimpleTrigger') IS NOT NULL
    DROP TABLE dbo.JIReportJobSimpleTrigger
GO

IF OBJECT_ID ('dbo.JIReportUnitInputControl') IS NOT NULL
    DROP TABLE dbo.JIReportUnitInputControl
GO

IF OBJECT_ID ('dbo.JIReportUnitResource') IS NOT NULL
    DROP TABLE dbo.JIReportUnitResource
GO

IF OBJECT_ID ('dbo.JIRepositoryCache') IS NOT NULL
    DROP TABLE dbo.JIRepositoryCache
GO

IF OBJECT_ID ('dbo.JIUserRole') IS NOT NULL
    DROP TABLE dbo.JIUserRole
GO
IF OBJECT_ID ('dbo.JIXMLAConnection') IS NOT NULL
    DROP TABLE dbo.JIXMLAConnection
GO

IF OBJECT_ID ('dbo.JICustomDatasource') IS NOT NULL
    DROP TABLE dbo.JICustomDatasource
GO

IF OBJECT_ID ('dbo.JIInputControl') IS NOT NULL
    DROP TABLE dbo.JIInputControl
GO

IF OBJECT_ID ('dbo.JIListOfValues') IS NOT NULL
    DROP TABLE dbo.JIListOfValues
GO

IF OBJECT_ID ('dbo.JIMondrianConnection') IS NOT NULL
    DROP TABLE dbo.JIMondrianConnection
GO

IF OBJECT_ID ('dbo.JIOlapClientConnection') IS NOT NULL
    DROP TABLE dbo.JIOlapClientConnection
GO

IF OBJECT_ID ('dbo.JIReportJob') IS NOT NULL
    DROP TABLE dbo.JIReportJob
GO

IF OBJECT_ID ('dbo.JIReportJobMail') IS NOT NULL
    DROP TABLE dbo.JIReportJobMail
GO

IF OBJECT_ID ('dbo.JIReportJobRepoDest') IS NOT NULL
    DROP TABLE dbo.JIReportJobRepoDest
GO

IF OBJECT_ID ('dbo.JIReportJobTrigger') IS NOT NULL
    DROP TABLE dbo.JIReportJobTrigger
GO

IF OBJECT_ID ('dbo.JIReportUnit') IS NOT NULL
    DROP TABLE dbo.JIReportUnit
GO

IF OBJECT_ID ('dbo.JIRole') IS NOT NULL
    DROP TABLE dbo.JIRole
GO

IF OBJECT_ID ('dbo.JIUser') IS NOT NULL
    DROP TABLE dbo.JIUser
GO

IF OBJECT_ID ('dbo.JIDataType') IS NOT NULL
    DROP TABLE dbo.JIDataType
GO

IF OBJECT_ID ('dbo.JIFileResource') IS NOT NULL
    DROP TABLE dbo.JIFileResource
GO

IF OBJECT_ID ('dbo.JIQuery') IS NOT NULL
    DROP TABLE dbo.JIQuery
GO

IF OBJECT_ID ('dbo.JIResource') IS NOT NULL
    DROP TABLE dbo.JIResource
GO

IF OBJECT_ID ('dbo.JIResourceFolder') IS NOT NULL
    DROP TABLE dbo.JIResourceFolder
GO

    CREATE TABLE JIBeanDatasource (
        id INT NOT NULL,
        beanName VARCHAR(100) NOT NULL,
        beanMethod VARCHAR(100) NULL
    )
ALTER TABLE JIBeanDatasource ADD CONSTRAINT pk_JIBeanDatasource  PRIMARY KEY (id)

    CREATE TABLE JIContentResource (
        id INT NOT NULL,
        data IMAGE NULL,
        file_type VARCHAR(20) NULL
    )
ALTER TABLE JIContentResource ADD CONSTRAINT pk_JIContentResource PRIMARY KEY (id)   

    CREATE TABLE JICustomDatasource (
        id INT NOT NULL,
        serviceClass VARCHAR(250) NOT NULL
    )
ALTER TABLE JICustomDatasource ADD CONSTRAINT pk_JICustomDatasource PRIMARY KEY (id)   
   
   
    CREATE TABLE JICustomDatasourceproperty (
        ds_id INT NOT NULL,
        value TEXT NULL,
        name VARCHAR(100) NOT NULL
    )
ALTER TABLE JICustomDatasourceproperty ADD CONSTRAINT pk_JICustomDatasourceproperty PRIMARY KEY (ds_id, name) 
   
   
    CREATE TABLE JIDataType (
        id INT NOT NULL,
        type TINYINT NULL,
        maxLength INT NULL,
        decimals INT NULL,
        regularExpr VARCHAR(255) NULL,
        minValue IMAGE NULL,
        maxValue IMAGE NULL,
        strictMin BIT  ,
        strictMax BIT 
    )
ALTER TABLE JIDataType ADD CONSTRAINT pk_JIDataType PRIMARY KEY (id)


    CREATE TABLE JIFileResource (
        id INT NOT NULL,
        data IMAGE NULL,
        file_type VARCHAR(20) NULL,
        reference INT NULL
    )
ALTER TABLE JIFileResource ADD CONSTRAINT pk_JIFileResource PRIMARY KEY (id)     

   
    CREATE TABLE JIInputControl (
        id INT NOT NULL,
        type TINYINT NULL,
        mandatory BIT  ,
        readOnly BIT  ,
        visible BIT  ,
        data_type INT NULL,
        list_of_values INT NULL,
        list_query INT NULL,
        query_value_column VARCHAR(200) NULL,
        defaultValue IMAGE NULL
    )
ALTER TABLE JIInputControl ADD CONSTRAINT pk_JIInputControl PRIMARY KEY (id)


    CREATE TABLE JIInputControlQueryColumn (
        input_control_id INT NOT NULL,
        query_column VARCHAR(200) NOT NULL,
        column_index INT NOT NULL
    )
ALTER TABLE JIInputControlQueryColumn ADD CONSTRAINT pk_JIInputControlQueryColumn PRIMARY KEY (input_control_id, column_index)


    CREATE TABLE JIJNDIJdbcDatasource (
        id INT NOT NULL,
        jndiName VARCHAR(100) NOT NULL,
        timezone VARCHAR(100) NULL
    )
ALTER TABLE JIJNDIJdbcDatasource ADD CONSTRAINT pk_JIJNDIJdbcDatasource PRIMARY KEY (id)


    CREATE TABLE JIJdbcDatasource (
        id INT NOT NULL,
        driver VARCHAR(100) NOT NULL,
        password VARCHAR(100) NULL,
        connectionUrl TEXT NULL,
        username VARCHAR(100) NULL,
        timezone VARCHAR(100) NULL
    )
ALTER TABLE JIJdbcDatasource ADD CONSTRAINT pk_JIJdbcDatasource PRIMARY KEY (id)


    CREATE TABLE JIListOfValues (
        id INT NOT NULL
    )
ALTER TABLE JIListOfValues ADD CONSTRAINT pk_JIListOfValues PRIMARY KEY (id)

    CREATE TABLE JIListOfValuesItem (
        id INT NOT NULL,
        label VARCHAR(255) NULL,
        value IMAGE NULL,
        idx INT NOT NULL
    )
ALTER TABLE JIListOfValuesItem ADD CONSTRAINT pk_JIListOfValuesItem PRIMARY KEY (id,idx)

    CREATE TABLE JILogEvent (
        id INT  IDENTITY NOT NULL,
        occurrence_date DATETIME NOT NULL,
        event_type TINYINT NOT NULL,
        username VARCHAR(100) NULL,
        component VARCHAR(100) NULL,
        message VARCHAR(250) NOT NULL,
        resource_uri VARCHAR(250) NULL,
        event_text IMAGE NULL,
        event_data IMAGE NULL,
        event_state TINYINT NULL
    )
ALTER TABLE JILogEvent ADD CONSTRAINT pk_JILogEvent PRIMARY KEY (id)    
   
    CREATE TABLE JIMondrianConnection (
        id INT NOT NULL,
        reportDataSource INT NULL,
        mondrianSchema INT NULL
    )
ALTER TABLE JIMondrianConnection ADD CONSTRAINT pk_JIMondrianConnection PRIMARY KEY (id)


    CREATE TABLE JIMondrianXMLADefinition (
        id INT NOT NULL,
        catalog VARCHAR(100) NOT NULL,
        mondrianConnection INT NULL
    )
ALTER TABLE JIMondrianXMLADefinition ADD CONSTRAINT pk_JIMondrianXMLADefinition PRIMARY KEY (id)

    CREATE TABLE JIObjectPermission (
        id INT IDENTITY NOT NULL,
        uri VARCHAR(255) NOT NULL,
        recipientobjectclass VARCHAR(100) NULL,
        recipientobjectid INT NULL,
        permissionMask INT NOT NULL
    )
ALTER TABLE JIObjectPermission ADD CONSTRAINT pk_JIObjectPermission PRIMARY KEY (id)
   
    CREATE TABLE JIOlapClientConnection (
        id INT NOT NULL
    )
ALTER TABLE JIOlapClientConnection ADD CONSTRAINT pk_JIOlapClientConnection PRIMARY KEY (id)


    CREATE TABLE JIOlapUnit (
        id INT NOT NULL,
        olapClientConnection INT NULL,
        mdx_query TEXT NOT NULL,
        view_options IMAGE NULL
    )
ALTER TABLE JIOlapUnit ADD CONSTRAINT pk_JIOlapUnit PRIMARY KEY (id)


    CREATE TABLE JIProfileAttribute (
        id INT IDENTITY NOT NULL,
        attrName VARCHAR(255) NOT NULL,
        attrValue VARCHAR(255) NOT NULL,
        principalobjectclass VARCHAR(255) NOT NULL,
        principalobjectid INT NOT NULL
    )
ALTER TABLE JIProfileAttribute ADD CONSTRAINT pk_JIProfileAttribute PRIMARY KEY (id)


    CREATE TABLE JIQuery (
        id INT NOT NULL,
        dataSource INT NULL,
        query_language VARCHAR(40) NOT NULL,
        sql_query TEXT NOT NULL
    )
ALTER TABLE JIQuery ADD CONSTRAINT pk_JIQuery PRIMARY KEY (id)


    CREATE TABLE JIReportJob (
        id INT IDENTITY NOT NULL,
        version INT NOT NULL,
        username VARCHAR(100) NOT NULL,
        label VARCHAR(100) NOT NULL,
        description TEXT NULL,
        report_unit_uri VARCHAR(200) NOT NULL,
        job_trigger INT NOT NULL,
        base_output_name VARCHAR(100) NOT NULL,
        output_locale VARCHAR(20) NULL,
        content_destination INT NULL,
        mail_notification INT NULL
    )
ALTER TABLE JIReportJob ADD CONSTRAINT pk_JIReportJob PRIMARY KEY (id)

    CREATE TABLE JIReportJobCalendarTrigger (
        id INT NOT NULL,
        minutes VARCHAR(200) NOT NULL,
        hours VARCHAR(80) NOT NULL,
        days_type TINYINT NOT NULL,
        week_days VARCHAR(20) NULL,
        month_days VARCHAR(100) NULL,
        months VARCHAR(40) NOT NULL
    )
ALTER TABLE JIReportJobCalendarTrigger ADD CONSTRAINT pk_JIReportJobCalendarTrigger PRIMARY KEY (id)


    CREATE TABLE JIReportJobMail (
        id INT IDENTITY NOT NULL,
        version INT NOT NULL,
        subject VARCHAR(100) NOT NULL,
        message TEXT NOT NULL,
        send_type TINYINT NOT NULL,
        skip_empty BIT NOT NULL
    )
ALTER TABLE JIReportJobMail ADD CONSTRAINT pk_JIReportJobMail PRIMARY KEY (id)


    CREATE TABLE JIReportJobMailRecipient (
        destination_id INT NOT NULL,
        recipient_type TINYINT NOT NULL,
        address VARCHAR(100) NOT NULL,
        recipient_idx INT NOT NULL
    )
ALTER TABLE JIReportJobMailRecipient ADD CONSTRAINT pk_JIReportJobMailRecipient PRIMARY KEY (destination_id, recipient_idx)


    CREATE TABLE JIReportJobOutputFormat (
        report_job_id INT NOT NULL,
        output_format TINYINT NOT NULL
    )
ALTER TABLE JIReportJobOutputFormat ADD CONSTRAINT pk_JIReportJobOutputFormat PRIMARY KEY (report_job_id, output_format)


    CREATE TABLE JIReportJobParameter (
        job_id INT NOT NULL,
        parameter_value IMAGE NULL,
        parameter_name VARCHAR(100) NOT NULL
    )
ALTER TABLE JIReportJobParameter ADD CONSTRAINT pk_JIReportJobParameter PRIMARY KEY (job_id, parameter_name)


    CREATE TABLE JIReportJobRepoDest (
        id INT IDENTITY NOT NULL,
        version INT NOT NULL,
        folder_uri VARCHAR(200) NOT NULL,
        sequential_filenames BIT NOT NULL,
        overwrite_files BIT NOT NULL,
        output_description VARCHAR(100) NULL,
        timestamp_pattern VARCHAR(250) NULL
    )
ALTER TABLE JIReportJobRepoDest ADD CONSTRAINT pk_JIReportJobRepoDest PRIMARY KEY (id)


    CREATE TABLE JIReportJobSimpleTrigger (
        id INT NOT NULL,
        occurrence_count INT NOT NULL,
        recurrence_interval INT NULL,
        recurrence_interval_unit TINYINT NULL
    )
ALTER TABLE JIReportJobSimpleTrigger ADD CONSTRAINT pk_JIReportJobSimpleTrigger PRIMARY KEY (id)


    CREATE TABLE JIReportJobTrigger (
        id INT IDENTITY NOT NULL,
        version INT NOT NULL,
        timezone VARCHAR(40) NULL,
        start_type TINYINT NOT NULL,
        start_date DATETIME NULL,
        end_date DATETIME NULL
    )
ALTER TABLE JIReportJobTrigger ADD CONSTRAINT pk_JIReportJobTrigger PRIMARY KEY (id)


    CREATE TABLE JIReportUnit (
        id INT NOT NULL,
        reportDataSource INT NULL,
        query INT NULL,
        mainReport INT NULL,
        controlrenderer VARCHAR(100) NULL,
        reportrenderer VARCHAR(100) NULL,
        promptcontrols BIT ,
        controlslayout TINYINT NULL
    )
ALTER TABLE JIReportUnit ADD CONSTRAINT pk_JIReportUnit PRIMARY KEY (id)


    CREATE TABLE JIReportUnitInputControl (
        report_unit_id INT NOT NULL,
        input_control_id INT NOT NULL,
        control_index INT NOT NULL
    )
ALTER TABLE JIReportUnitInputControl ADD CONSTRAINT pk_JIReportUnitInputControl PRIMARY KEY (report_unit_id, control_index)


    CREATE TABLE JIReportUnitResource (
        report_unit_id INT NOT NULL,
        resource_id INT NOT NULL,
        resource_index INT NOT NULL
    )
ALTER TABLE JIReportUnitResource ADD CONSTRAINT pk_JIReportUnitResource PRIMARY KEY (report_unit_id, resource_index)


    CREATE TABLE JIRepositoryCache (
        id INT IDENTITY NOT NULL,
        uri VARCHAR(200) NOT NULL,
        cache_name VARCHAR(20) NOT NULL,
        data IMAGE NULL,
        version INT NOT NULL,
        version_date DATETIME NOT NULL,
        item_reference INT NULL
    )
ALTER TABLE JIRepositoryCache ADD CONSTRAINT pk_JIRepositoryCache PRIMARY KEY (id)


    CREATE TABLE JIResource (
        id INT IDENTITY NOT NULL,
        version INT NOT NULL,
        name VARCHAR(100) NOT NULL,
        parent_folder INT NOT NULL,
        childrenFolder INT NULL,
        label VARCHAR(100) NOT NULL,
        description VARCHAR(250) NULL,
        creation_date DATETIME NOT NULL
    )
ALTER TABLE JIResource ADD CONSTRAINT pk_JIResource PRIMARY KEY (id)

    CREATE TABLE JIResourceFolder (
        id INT IDENTITY NOT NULL,
        version INT NOT NULL,
        uri VARCHAR(250) NOT NULL,
        hidden BIT ,
        name VARCHAR(100) NOT NULL,
        label VARCHAR(100) NOT NULL,
        description VARCHAR(250) NULL,
        parent_folder INT NULL,
        creation_date DATETIME NOT NULL
    )
ALTER TABLE JIResourceFolder ADD CONSTRAINT pk_JIResourceFolder PRIMARY KEY (id)


   CREATE TABLE JIUserRole (
        roleId INT NOT NULL,
        userId INT NOT NULL
    )
ALTER TABLE JIUserRole ADD CONSTRAINT pk_JIUserRole PRIMARY KEY (userId, roleId)


    CREATE TABLE JIXMLAConnection (
        id INT 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
    )
ALTER TABLE JIXMLAConnection ADD CONSTRAINT pk_JIXMLAConnection PRIMARY KEY (id)



CREATE INDEX FK674BF34A8BF376D   ON JIBeanDatasource (id )

   
CREATE INDEX FKE466FC68A8BF376D  ON JIContentResource (id )
CREATE INDEX FK2BBCEDF5A8BF376D  ON JICustomDatasource (id )

   
CREATE INDEX FKB8A66AEA858A89D1  ON JICustomDatasourceproperty (ds_id )
 
 
CREATE INDEX FK533BCC63A8BF376D  ON JIDataType (id )
   
   
CREATE INDEX FKF75B58895A0C539   ON JIFileResource (reference )
CREATE INDEX FKF75B5889A8BF376D  ON JIFileResource (id )
   
   
CREATE INDEX FKCAC6A512120E06F7  ON JIInputControl (data_type )
CREATE INDEX FKCAC6A51262A86F04  ON JIInputControl (list_of_values )
CREATE INDEX FKCAC6A512A8BF376D  ON JIInputControl (id )
CREATE INDEX FKCAC6A512B37DB6EB  ON JIInputControl (list_query )
  
   
CREATE INDEX FKE436A5CCE7922149  ON JIInputControlQueryColumn (input_control_id )
  
 
CREATE INDEX FKC8BDFCBFA8BF376D  ON JIJdbcDatasource (id )

 
CREATE INDEX FK7F9DA248A8BF376D  ON JIJNDIJdbcDatasource (id )
 
 
CREATE INDEX FK4E86A776A8BF376D  ON JIListOfValues (id )
 
 
CREATE INDEX FKD37CEBA993F0E1F6  ON JIListOfValuesItem (id )

 
CREATE INDEX FK4FF53B191D51BFAD  ON JIMondrianConnection (id )
CREATE INDEX FK4FF53B19324CFECB  ON JIMondrianConnection (reportDataSource )
CREATE INDEX FK4FF53B19C495A60B  ON JIMondrianConnection (mondrianSchema )


CREATE INDEX FK313B2AB8A8BF376D  ON JIMondrianXMLADefinition (id )
CREATE INDEX FK313B2AB8DC098B1   ON JIMondrianXMLADefinition (mondrianConnection )


CREATE INDEX FK3CA3B7D4A8BF376D  ON JIOlapClientConnection (id )


CREATE INDEX FKF034DCCF8F542247  ON JIOlapUnit (olapClientConnection )
CREATE INDEX FKF034DCCFA8BF376D  ON JIOlapUnit (id )


CREATE INDEX FKCBCB0EC92B329A97  ON JIQuery (dataSource )
CREATE INDEX FKCBCB0EC9A8BF376D  ON JIQuery (id )


CREATE INDEX FK156F5F6A74D2696E  ON JIReportJob (job_trigger )
CREATE INDEX FK156F5F6A9EEC902C  ON JIReportJob (content_destination )
CREATE INDEX FK156F5F6AE4D73E35  ON JIReportJob (mail_notification )


CREATE INDEX FKC374C7D0D2B2EB53  ON JIReportJobCalendarTrigger (id )


CREATE INDEX FKBB6DB6D880001AAE  ON JIReportJobMailRecipient (destination_id )


CREATE INDEX FKB42A5CE2C3389A8   ON JIReportJobOutputFormat (report_job_id )


CREATE INDEX FKEAC52B5F2EC643D   ON JIReportJobParameter (job_id )


CREATE INDEX FKB9337C5CD2B2EB53  ON JIReportJobSimpleTrigger (id )


CREATE INDEX FK98818B77324CFECB  ON JIReportUnit (reportDataSource )
CREATE INDEX FK98818B778C8DF21B  ON JIReportUnit (mainReport )
CREATE INDEX FK98818B778FDA11CC  ON JIReportUnit (query )
CREATE INDEX FK98818B77A8BF376D  ON JIReportUnit (id )


CREATE INDEX FK5FBE934AA6A48880  ON JIReportUnitInputControl (report_unit_id )
CREATE INDEX FK5FBE934AE7922149  ON JIReportUnitInputControl (input_control_id )


CREATE INDEX FK8B1C4CA5865B10DA  ON JIReportUnitResource (resource_id )
CREATE INDEX FK8B1C4CA5A6A48880  ON JIReportUnitResource (report_unit_id )
 
   
CREATE INDEX FKE7338B19E7C5A6    ON JIRepositoryCache (item_reference )
CREATE UNIQUE INDEX uri           ON JIRepositoryCache (uri , cache_name )


CREATE INDEX FKD444826DA08E2155  ON JIResource (parent_folder )
CREATE INDEX FKD444826DA58002DF  ON JIResource (childrenFolder )


CREATE UNIQUE INDEX name          ON JIResource (name , parent_folder )

CREATE INDEX FK7F24453BA08E2155  ON JIResourceFolder (parent_folder )
CREATE UNIQUE INDEX uri           ON JIResourceFolder (uri )
  
CREATE INDEX FKD8B5C1403C31045   ON JIUserRole (roleId )
CREATE INDEX FKD8B5C14091865AF   ON JIUserRole (userId )
 


 
    ALTER TABLE JIBeanDatasource
        ADD CONSTRAINT FK674BF34A8BF376D
        FOREIGN KEY (id)
        REFERENCES JIResource (id)

    ALTER TABLE JIContentResource
        ADD CONSTRAINT FKE466FC68A8BF376D
        FOREIGN KEY (id)
        REFERENCES JIResource (id)

    ALTER TABLE JICustomDatasource
        ADD CONSTRAINT FK2BBCEDF5A8BF376D
        FOREIGN KEY (id)
        REFERENCES JIResource (id)

    ALTER TABLE JICustomDatasourceproperty
        ADD CONSTRAINT FKB8A66AEA858A89D1
        FOREIGN KEY (ds_id)
        REFERENCES JICustomDatasource (id)

    ALTER TABLE JIDataType
        ADD CONSTRAINT FK533BCC63A8BF376D
        FOREIGN KEY (id)
        REFERENCES JIResource (id)

    ALTER TABLE JIFileResource
        ADD CONSTRAINT FKF75B58895A0C539
        FOREIGN KEY (reference)
        REFERENCES JIFileResource (id)

    ALTER TABLE JIFileResource
        ADD CONSTRAINT FKF75B5889A8BF376D
        FOREIGN KEY (id)
        REFERENCES JIResource (id)

    ALTER TABLE JIInputControl
        ADD CONSTRAINT FKCAC6A512120E06F7
        FOREIGN KEY (data_type)
        REFERENCES JIDataType (id)

    ALTER TABLE JIInputControl
        ADD CONSTRAINT FKCAC6A512B37DB6EB
        FOREIGN KEY (list_query)
        REFERENCES JIQuery (id)

    ALTER TABLE JIInputControl
        ADD CONSTRAINT FKCAC6A51262A86F04
        FOREIGN KEY (list_of_values)
        REFERENCES JIListOfValues (id)

    ALTER TABLE JIInputControl
        ADD CONSTRAINT FKCAC6A512A8BF376D
        FOREIGN KEY (id)
        REFERENCES JIResource (id)

    ALTER TABLE JIInputControlQueryColumn
        ADD CONSTRAINT FKE436A5CCE7922149
        FOREIGN KEY (input_control_id)
        REFERENCES JIInputControl (id)

    ALTER TABLE JIJNDIJdbcDatasource
        ADD CONSTRAINT FK7F9DA248A8BF376D
        FOREIGN KEY (id)
        REFERENCES JIResource (id)

    ALTER TABLE JIJdbcDatasource
        ADD CONSTRAINT FKC8BDFCBFA8BF376D
        FOREIGN KEY (id)
        REFERENCES JIResource (id)

    ALTER TABLE JIListOfValues
        ADD CONSTRAINT FK4E86A776A8BF376D
        FOREIGN KEY (id)
        REFERENCES JIResource (id)

    ALTER TABLE JIListOfValuesItem
        ADD CONSTRAINT FKD37CEBA993F0E1F6
        FOREIGN KEY (id)
        REFERENCES JIListOfValues (id)

    ALTER TABLE JIMondrianConnection
        ADD CONSTRAINT FK4FF53B19C495A60B
        FOREIGN KEY (mondrianSchema)
        REFERENCES JIFileResource (id)

    ALTER TABLE JIMondrianConnection
        ADD CONSTRAINT FK4FF53B19324CFECB
        FOREIGN KEY (reportDataSource)
        REFERENCES JIResource (id)

    ALTER TABLE JIMondrianConnection
        ADD CONSTRAINT FK4FF53B191D51BFAD
        FOREIGN KEY (id)
        REFERENCES JIOlapClientConnection (id)

    ALTER TABLE JIMondrianXMLADefinition
        ADD CONSTRAINT FK313B2AB8DC098B1
        FOREIGN KEY (mondrianConnection)
        REFERENCES JIMondrianConnection (id)

    ALTER TABLE JIMondrianXMLADefinition
        ADD CONSTRAINT FK313B2AB8A8BF376D
        FOREIGN KEY (id)
        REFERENCES JIResource (id)

    ALTER TABLE JIOlapClientConnection
        ADD CONSTRAINT FK3CA3B7D4A8BF376D
        FOREIGN KEY (id)
        REFERENCES JIResource (id)

    ALTER TABLE JIOlapUnit
        ADD CONSTRAINT FKF034DCCF8F542247
        FOREIGN KEY (olapClientConnection)
        REFERENCES JIOlapClientConnection (id)

    ALTER TABLE JIOlapUnit
        ADD CONSTRAINT FKF034DCCFA8BF376D
        FOREIGN KEY (id)
        REFERENCES JIResource (id)

    ALTER TABLE JIQuery
        ADD CONSTRAINT FKCBCB0EC92B329A97
        FOREIGN KEY (dataSource)
        REFERENCES JIResource (id)

    ALTER TABLE JIQuery
        ADD CONSTRAINT FKCBCB0EC9A8BF376D
        FOREIGN KEY (id)
        REFERENCES JIResource (id)

    ALTER TABLE JIReportJob
        ADD CONSTRAINT FK156F5F6AE4D73E35
        FOREIGN KEY (mail_notification)
        REFERENCES JIReportJobMail (id)

    ALTER TABLE JIReportJob
        ADD CONSTRAINT FK156F5F6A9EEC902C
        FOREIGN KEY (content_destination)
        REFERENCES JIReportJobRepoDest (id)

    ALTER TABLE JIReportJob
        ADD CONSTRAINT FK156F5F6A74D2696E
        FOREIGN KEY (job_trigger)
        REFERENCES JIReportJobTrigger (id)

    ALTER TABLE JIReportJobCalendarTrigger
        ADD CONSTRAINT FKC374C7D0D2B2EB53
        FOREIGN KEY (id)
        REFERENCES JIReportJobTrigger (id)

    ALTER TABLE JIReportJobMailRecipient
        ADD CONSTRAINT FKBB6DB6D880001AAE
        FOREIGN KEY (destination_id)
        REFERENCES JIReportJobMail (id)

    ALTER TABLE JIReportJobOutputFormat
        ADD CONSTRAINT FKB42A5CE2C3389A8
        FOREIGN KEY (report_job_id)
        REFERENCES JIReportJob (id)

    ALTER TABLE JIReportJobParameter
        ADD CONSTRAINT FKEAC52B5F2EC643D
        FOREIGN KEY (job_id)
        REFERENCES JIReportJob (id)

    ALTER TABLE JIReportJobSimpleTrigger
        ADD CONSTRAINT FKB9337C5CD2B2EB53
        FOREIGN KEY (id)
        REFERENCES JIReportJobTrigger (id)

    ALTER TABLE JIReportUnit
        ADD CONSTRAINT FK98818B778FDA11CC
        FOREIGN KEY (query)
        REFERENCES JIQuery (id)

    ALTER TABLE JIReportUnit
        ADD CONSTRAINT FK98818B77324CFECB
        FOREIGN KEY (reportDataSource)
        REFERENCES JIResource (id)

    ALTER TABLE JIReportUnit
        ADD CONSTRAINT FK98818B778C8DF21B
        FOREIGN KEY (mainReport)
        REFERENCES JIFileResource (id)

    ALTER TABLE JIReportUnit
        ADD CONSTRAINT FK98818B77A8BF376D
        FOREIGN KEY (id)
        REFERENCES JIResource (id)

    ALTER TABLE JIReportUnitInputControl
        ADD CONSTRAINT FK5FBE934AE7922149
        FOREIGN KEY (input_control_id)
        REFERENCES JIInputControl (id)

    ALTER TABLE JIReportUnitInputControl
        ADD CONSTRAINT FK5FBE934AA6A48880
        FOREIGN KEY (report_unit_id)
        REFERENCES JIReportUnit (id)

    ALTER TABLE JIReportUnitResource
        ADD CONSTRAINT FK8B1C4CA5865B10DA
        FOREIGN KEY (resource_id)
        REFERENCES JIFileResource (id)

    ALTER TABLE JIReportUnitResource
        ADD CONSTRAINT FK8B1C4CA5A6A48880
        FOREIGN KEY (report_unit_id)
        REFERENCES JIReportUnit (id)

    ALTER TABLE JIRepositoryCache
        ADD CONSTRAINT FKE7338B19E7C5A6
        FOREIGN KEY (item_reference)
        REFERENCES JIRepositoryCache (id)

    ALTER TABLE JIResource
        ADD CONSTRAINT FKD444826DA08E2155
        FOREIGN KEY (parent_folder)
        REFERENCES JIResourceFolder (id)

    ALTER TABLE JIResource
        ADD CONSTRAINT FKD444826DA58002DF
        FOREIGN KEY (childrenFolder)
        REFERENCES JIResourceFolder (id)

    ALTER TABLE JIResourceFolder
        ADD CONSTRAINT FK7F24453BA08E2155
        FOREIGN KEY (parent_folder)
        REFERENCES JIResourceFolder (id)
   
    ALTER TABLE JIXMLAConnection
        ADD CONSTRAINT FK94C688A71D51BFAD
        FOREIGN KEY (id)
        REFERENCES JIOlapClientConnection (id)


    CREATE TABLE JIRole (
        id INT IDENTITY NOT NULL,
        rolename VARCHAR(100) NOT NULL unique,
        externallyDefined BIT
    )
ALTER TABLE JIRole ADD CONSTRAINT pk_JIRole PRIMARY KEY (id)


    CREATE TABLE JIUser (
        id INT IDENTITY NOT NULL,
        username VARCHAR(100) NOT NULL unique,
        fullname VARCHAR(100) NOT NULL,
        emailAddress VARCHAR(100) NULL,
        password VARCHAR(100) NULL,
        externallyDefined BIT ,
        enabled BIT ,
        previousPasswordChangeTime DATETIME NULL
    )
ALTER TABLE JIUser ADD CONSTRAINT pk_JIUser PRIMARY KEY (id)
   
CREATE UNIQUE INDEX rolename      ON JIRole (rolename )

CREATE UNIQUE INDEX username      ON JIUser (username )


    ALTER TABLE JIUserRole
        ADD CONSTRAINT FKD8B5C14091865AF
        FOREIGN KEY (userId)
        REFERENCES JIUser (id)

    ALTER TABLE JIUserRole
        ADD CONSTRAINT FKD8B5C1403C31045
        FOREIGN KEY (roleId)
        REFERENCES JIRole (id)

/*==============================================================================*/
/* End of script. */
/*==============================================================================*/



script Quartz Tables :

/*==============================================================================================*/
/* Quartz database tables creation script for Sybase ASE 12.5 */
/* Written by Pertti Laiho (email: pertti.laiho@deio.net), 9th May 2003 */
/* */
/* Compatible with Quartz version 1.1.2 */
/* */
/* Sybase ASE works ok with the MSSQL delegate class. That means in your Quartz properties */
/* file, you'll need to set: */
/* org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.MSSQLDelegate */
/*==============================================================================================*/

use jasperserver
go

/*==============================================================================*/
/* Clear all tables: */
/*==============================================================================*/

IF OBJECT_ID('QRTZ_JOB_LISTENERS') IS NOT NULL
delete from QRTZ_JOB_LISTENERS
go
IF OBJECT_ID('QRTZ_TRIGGER_LISTENERS') IS NOT NULL
delete from QRTZ_TRIGGER_LISTENERS
go
IF OBJECT_ID('QRTZ_FIRED_TRIGGERS') IS NOT NULL
delete from QRTZ_FIRED_TRIGGERS
go
IF OBJECT_ID('QRTZ_PAUSED_TRIGGER_GRPS') IS NOT NULL
delete from QRTZ_PAUSED_TRIGGER_GRPS
go
IF OBJECT_ID('QRTZ_SCHEDULER_STATE') IS NOT NULL
delete from QRTZ_SCHEDULER_STATE
go
IF OBJECT_ID('QRTZ_LOCKS') IS NOT NULL
delete from QRTZ_LOCKS
go
IF OBJECT_ID('QRTZ_SIMPLE_TRIGGERS') IS NOT NULL
delete from QRTZ_SIMPLE_TRIGGERS
go
IF OBJECT_ID('QRTZ_CRON_TRIGGERS') IS NOT NULL
delete from QRTZ_CRON_TRIGGERS
go
IF OBJECT_ID('QRTZ_BLOB_TRIGGERS') IS NOT NULL
delete from QRTZ_BLOB_TRIGGERS
go
IF OBJECT_ID('QRTZ_TRIGGERS') IS NOT NULL
delete from QRTZ_TRIGGERS
go
IF OBJECT_ID('QRTZ_JOB_DETAILS') IS NOT NULL
delete from QRTZ_JOB_DETAILS
go
IF OBJECT_ID('QRTZ_CALENDARS') IS NOT NULL
delete from QRTZ_CALENDARS
go

/*==============================================================================*/
/* Drop constraints: */
/*==============================================================================*/

alter table QRTZ_JOB_LISTENERS
drop constraint FK_job_listeners_job_details
go

alter table QRTZ_TRIGGERS
drop constraint FK_triggers_job_details
go

alter table QRTZ_CRON_TRIGGERS
drop constraint FK_cron_triggers_triggers
go

alter table QRTZ_SIMPLE_TRIGGERS
drop constraint FK_simple_triggers_triggers
go

alter table QRTZ_TRIGGER_LISTENERS
drop constraint FK_trigger_listeners_triggers
go

alter table QRTZ_BLOB_TRIGGERS
drop constraint FK_blob_triggers_triggers
go

/*==============================================================================*/
/* Drop tables: */
/*==============================================================================*/

drop table QRTZ_JOB_LISTENERS
go
drop table QRTZ_TRIGGER_LISTENERS
go
drop table QRTZ_FIRED_TRIGGERS
go
drop table QRTZ_PAUSED_TRIGGER_GRPS
go
drop table QRTZ_SCHEDULER_STATE
go
drop table QRTZ_LOCKS
go
drop table QRTZ_SIMPLE_TRIGGERS
go
drop table QRTZ_CRON_TRIGGERS
go
drop table QRTZ_BLOB_TRIGGERS
go
drop table QRTZ_TRIGGERS
go
drop table QRTZ_JOB_DETAILS
go
drop table QRTZ_CALENDARS
go

/*==============================================================================*/
/* Create tables: */
/*==============================================================================*/

create table QRTZ_CALENDARS (
CALENDAR_NAME varchar(80) not null,
CALENDAR image not null
)
go

create table QRTZ_CRON_TRIGGERS (
TRIGGER_NAME varchar(80) not null,
TRIGGER_GROUP varchar(80) not null,
CRON_EXPRESSION varchar(120) not null,
TIME_ZONE_ID varchar(80) null
)
go

create table QRTZ_PAUSED_TRIGGER_GRPS (
TRIGGER_GROUP  varchar(80) not null
)
go

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 bit not null,
INSTANCE_NAME varchar(80) not null,
FIRED_TIME numeric(13,0) not null,
STATE varchar(16) not null,
JOB_NAME varchar(80) null,
JOB_GROUP varchar(80) null,
IS_STATEFUL bit not null,
REQUESTS_RECOVERY bit not null
)
go

create table QRTZ_SCHEDULER_STATE (
INSTANCE_NAME varchar(80) not null,
LAST_CHECKIN_TIME numeric(13,0) not null,
CHECKIN_INTERVAL numeric(13,0) not null,
RECOVERER VARCHAR(80) NULL
)
go

create table QRTZ_LOCKS (
LOCK_NAME  varchar(40) not null
)
go

insert into QRTZ_LOCKS values('TRIGGER_ACCESS')
go
insert into QRTZ_LOCKS values('JOB_ACCESS')
go
insert into QRTZ_LOCKS values('CALENDAR_ACCESS')
go
insert into QRTZ_LOCKS values('STATE_ACCESS')
go
INSERT INTO QRTZ_LOCKS values('MISFIRE_ACCESS')
go

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 bit not null,
IS_VOLATILE bit not null,
IS_STATEFUL bit not null,
REQUESTS_RECOVERY bit not null,
JOB_DATA image null
)
go

create table QRTZ_JOB_LISTENERS (
JOB_NAME varchar(80) not null,
JOB_GROUP varchar(80) not null,
JOB_LISTENER varchar(80) not null
)
go

create table QRTZ_SIMPLE_TRIGGERS (
TRIGGER_NAME varchar(80) not null,
TRIGGER_GROUP varchar(80) not null,
REPEAT_COUNT numeric(13,0) not null,
REPEAT_INTERVAL numeric(13,0) not null,
TIMES_TRIGGERED numeric(13,0) not null
)
go

create table QRTZ_BLOB_TRIGGERS (
TRIGGER_NAME varchar(80) not null,
TRIGGER_GROUP varchar(80) not null,
BLOB_DATA image null
)
go

create table QRTZ_TRIGGER_LISTENERS (
TRIGGER_NAME varchar(80) not null,
TRIGGER_GROUP varchar(80) not null,
TRIGGER_LISTENER varchar(80) not null
)
go

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 bit not null,
DESCRIPTION varchar(120) null,
NEXT_FIRE_TIME numeric(13,0) null,
PREV_FIRE_TIME numeric(13,0) null,
TRIGGER_STATE varchar(16) not null,
TRIGGER_TYPE varchar(8) not null,
START_TIME numeric(13,0) not null,
END_TIME numeric(13,0) null,
CALENDAR_NAME varchar(80) null,
MISFIRE_INSTR smallint null,
JOB_DATA image null
)
go

/*==============================================================================*/
/* Create primary key constraints: */
/*==============================================================================*/

alter table QRTZ_CALENDARS
add constraint PK_qrtz_calendars primary key clustered (CALENDAR_NAME)
go

alter table QRTZ_CRON_TRIGGERS
add constraint PK_qrtz_cron_triggers primary key clustered (TRIGGER_NAME, TRIGGER_GROUP)
go

alter table QRTZ_FIRED_TRIGGERS
add constraint PK_qrtz_fired_triggers primary key clustered (ENTRY_ID)
go

alter table QRTZ_PAUSED_TRIGGER_GRPS
add constraint PK_qrtz_paused_trigger_grps primary key clustered (TRIGGER_GROUP)
go

alter table QRTZ_SCHEDULER_STATE
add constraint PK_qrtz_scheduler_state primary key clustered (INSTANCE_NAME)
go

alter table QRTZ_LOCKS
add constraint PK_qrtz_locks primary key clustered (LOCK_NAME)
go

alter table QRTZ_JOB_DETAILS
add constraint PK_qrtz_job_details primary key clustered (JOB_NAME, JOB_GROUP)
go

alter table QRTZ_JOB_LISTENERS
add constraint PK_qrtz_job_listeners primary key clustered (JOB_NAME, JOB_GROUP, JOB_LISTENER)
go

alter table QRTZ_SIMPLE_TRIGGERS
add constraint PK_qrtz_simple_triggers primary key clustered (TRIGGER_NAME, TRIGGER_GROUP)
go

alter table QRTZ_TRIGGER_LISTENERS
add constraint PK_qrtz_trigger_listeners primary key clustered (TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_LISTENER)
go

alter table QRTZ_TRIGGERS
add constraint PK_qrtz_triggers primary key clustered (TRIGGER_NAME, TRIGGER_GROUP)
go

alter table QRTZ_BLOB_TRIGGERS
add constraint PK_qrtz_blob_triggers primary key clustered (TRIGGER_NAME, TRIGGER_GROUP)
go


/*==============================================================================*/
/* Create foreign key constraints: */
/*==============================================================================*/

alter table QRTZ_CRON_TRIGGERS
add constraint FK_cron_triggers_triggers foreign key (TRIGGER_NAME,TRIGGER_GROUP)
references QRTZ_TRIGGERS (TRIGGER_NAME,TRIGGER_GROUP)
go

alter table QRTZ_JOB_LISTENERS
add constraint FK_job_listeners_job_details foreign key (JOB_NAME,JOB_GROUP)
references QRTZ_JOB_DETAILS (JOB_NAME,JOB_GROUP)
go

alter table QRTZ_SIMPLE_TRIGGERS
add constraint FK_simple_triggers_triggers foreign key (TRIGGER_NAME,TRIGGER_GROUP)
references QRTZ_TRIGGERS (TRIGGER_NAME,TRIGGER_GROUP)
go

alter table QRTZ_TRIGGER_LISTENERS
add constraint FK_trigger_listeners_triggers foreign key (TRIGGER_NAME,TRIGGER_GROUP)
references QRTZ_TRIGGERS (TRIGGER_NAME,TRIGGER_GROUP)
go

alter table QRTZ_TRIGGERS
add constraint FK_triggers_job_details foreign key (JOB_NAME,JOB_GROUP)
references QRTZ_JOB_DETAILS (JOB_NAME,JOB_GROUP)
go

alter table QRTZ_BLOB_TRIGGERS
add constraint FK_blob_triggers_triggers foreign key (TRIGGER_NAME,TRIGGER_GROUP)
references QRTZ_TRIGGERS (TRIGGER_NAME,TRIGGER_GROUP)
go

/*==============================================================================*/
/* End of script. */
/*==============================================================================*/

script insert Role :

/*
* 
* date : 06/02/2009
* author : @skh
*/

IF OBJECT_ID('JIRole') IS NOT NULL
    delete from JIRole
go

INSERT INTO JIRole (rolename,externallyDefined) VALUES ("ROLE_USER",0)
INSERT INTO JIRole (rolename,externallyDefined) VALUES ("ROLE_ADMINISTRATOR",0)
INSERT INTO JIRole (rolename,externallyDefined) VALUES ("ROLE_ANONYMOUS",0)
INSERT INTO JIRole (rolename,externallyDefined) VALUES ("ROLE_PORTLET",0)

IF OBJECT_ID('JIUser') IS NOT NULL
    delete from JIUser
go

INSERT INTO  JIUser  (username,  fullname,  emailAddress,  password,   externallyDefined, enabled  )
VALUES  ("anonymoususer","Anonymous User",NULL,"",0,1)
 
INSERT INTO  JIUserRole  ( userId,roleId)
     select u.id, r.id from JIUser u, JIRole r where u.username = "anonymoususer" and r.rolename = "ROLE_ANONYMOUS"

-- add jasperadmin user

INSERT INTO JIUser (username,fullname,emailAddress,password,externallyDefined,enabled)
VALUES  ("jasperadmin","Jasper Administrator",NULL,"jasperadmin",0,1)
 
INSERT INTO JIUserRole (userId,roleId)
    select u.id, r.id from JIUser u, JIRole r where u.username = "jasperadmin" and r.rolename = "ROLE_ADMINISTRATOR"

INSERT INTO JIUserRole (userId,roleId)
    select u.id, r.id from JIUser u, JIRole r where u.username = "jasperadmin" and r.rolename = "ROLE_USER"

IF OBJECT_ID('JIResourceFolder') IS NOT NULL
    delete from JIResourceFolder
go

INSERT INTO JIResourceFolder (version,uri,hidden,name,label,description,parent_folder,creation_date)
VALUES (0,"/",0,"/","root","Root of the folder hierarchy", NULL, getdate())

IF OBJECT_ID('JIObjectPermission') IS NOT NULL
    delete from JIObjectPermission
go

INSERT INTO JIObjectPermission (uri,recipientobjectclass,permissionMask,recipientobjectid)
    select "repo:/","com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoRole", 1, r.id
    from JIRole r where rolename = "ROLE_ADMINISTRATOR"

INSERT INTO JIObjectPermission (uri,recipientobjectclass,permissionMask,recipientobjectid)
    select "repo:/","com.jaspersoft.jasperserver.api.metadata.user.domain.impl.hibernate.RepoRole", 2, r.id
    from JIRole r where rolename = "ROLE_USER"
 

/*==============================================================================*/
/* End of script. */
/*==============================================================================*/

Post Edited by khadir samir

Post Edited by khadir samir at 02/13/09 10:40