OverviewThe setup for the Oracle and MySQL persistence layer is a 3 step process. The first two steps are database specific, so please refer to the relevant subsection below.
The Oracle persistence layer is an Orbeon Forms PE feature. The MySQL persistence layer does not support attachments. Database setupOracle Create a user/schema in Oracle, for instance with the following
commands. (In this example "all privileges" are granted to the newly
created user/schema, which is not strictly required. You might want to
fine-tune permissions on your system as appropriate.) > sqlplus sys/password as sysdbaSQL> create user orbeon identified by password ;SQL> grant all privileges to orbeon ;Note:
create table orbeon_form_definition ( created timestamp, last_modified timestamp, username varchar2(256), app varchar2(256), form varchar2(256), deleted char(1), xml xmltype);create index orbeon_form_definition_x on orbeon_form_definition(xml) indextype is ctxsys.contextcreate index orbeon_form_definition_i1 on orbeon_form_definition(app, form);create table orbeon_form_definition_attach ( created timestamp, last_modified timestamp, username varchar2(256), app varchar2(256), form varchar2(256), deleted char(1), file_name varchar2(256), file_content blob);create index orbeon_form_definition_att_i1 on orbeon_form_definition_attach(app, form, file_name);create table orbeon_form_data (create index orbeon_form_data_x on orbeon_form_data(xml) indextype is ctxsys.contextcreate index orbeon_from_data_i1 on orbeon_form_data(app, form, document_id);create table orbeon_form_data_attach ( created timestamp, last_modified timestamp, username varchar2(256), app varchar2(256), form varchar2(256), document_id varchar(256), deleted char(1), file_name varchar2(256), file_content blob);create index orbeon_from_data_attach_i1 on orbeon_form_data_attach(app, form, document_id, file_name);MySQL The MySQL persistence layer relies on XML functions that have been introduced in MySQL 5.1, so you need to be using the MySQL 5.1 (which was released in November 2008) or newer.
Application server setupOracle GeneralAssuming:
TomcatPut the Oracle jar file that contains the JDBC driver (e.g.ojdbc14_g.jar or ojdbc5_g.jar) in the appropriate directory for your application server (on Tomcat: common/lib or simply lib, depending on the version). If you don't already have it, you can download the Oracle JDBC driver from the Oracle site.Setup a JDBC data source for your Oracle instance. With Tomcat, this is done in server.xml, where you define a Resource pointing to the your Oracle instance. In the example below, the Oracle server is running on oracle.acme.com, the instance name is orcl, and the user/schema is orbeon with password password:<Resource name="jdbc/oracle" auth="Container" type="javax.sql.DataSource" initialSize="3" maxActive="10" maxIdle="20" maxWait="30000" validationQuery="select * from dual" driverClassName="oracle.jdbc.OracleDriver" poolPreparedStatements="true" username="${USERNAME}" password="${PASSWORD}" url="jdbc:oracle:thin:@//${HOST}:${PORT}/${INSTANCE}"/>JBoss 5.0.1 / JBoss EAP 5.0.1Please follow the JBoss documentation first, but here are some steps that work for us in our test environment. Place
ojdbc5_g.jar into server/default/lib/.Create an Oracle datasource as server/default/deploy/oracle-ds.xml, for example:
Update WEB-INF/jboss-web.xml to:
MySQL
Orbeon Forms setupWith Orbeon Forms 3.9 In your properties-local.xml:
Dynamic datasource for MySQLYou select the datasource used by the MySQL persistence layer with theoxf.fr.persistence.service.mysql.datasource configuration property. If you have multiple MySQL datasources, you can dynamically choose which one the MySQL persistence layer uses by setting the Orbeon-Datasource HTTP header. When the header is set, it takes precedence over the value of the oxf.fr.persistence.service.mysql.datasource property. In addition to setting the header, you need to add the following property so Form Runner forwards that header to the MySQL persistence layer:<property as="xs:string" name="oxf.http.forward-headers" value="Orbeon-Datasource"/>The header must be present in both the HTTP requests that return a
web page and the Ajax requests. This assumes that you have your own
"proxy" (for instance a servlet filter) that receives requests from the
browser and adds the header before passing requests along to Orbeon
Forms. For security reasons, you should never return the header to the
browser, as this would enable malicious users to access any datasource
they want by changing the value of the header. Typically, your proxy
will work as follows:
With Orbeon Forms 3.10With a single schemaIn yourproperties-local.xml:
With multiple schemas The single schema configuration described in the previous section uses the predefined oracle and mysql providers. To use multiple schemas you need to define you own provider names. For instance, assume that you have two apps, hr and finance, and would like both the form definition and data for those apps to be stored in two separate schemas:
Flat view or table Orbeon Forms stores form data as XML in Oracle and MySQL, which gives it a lot of
flexibility. However, it might be harder for other tools to access this XML
data. For this reason, you might want to provide other tools a way to access the XML data through another "flat" table or view that has one column per form field.Oracle[SINCE: 2011-04-08]Orbeon Forms can create in Oracle a form-specific view of your data, with one column for each form field. You enabled this feature by setting the following property to true. (If you define your own provider for Oracle, use that provider name in the property name, instead of oracle.)<property as="xs:boolean" name="oxf.fr.persistence.oracle.create-flat-view" value="false"/>When you enable this property, upon publishing a form, Oracle persistence layer creates a view specific to that form. The name of the view is based on your app and form name, and has the form: orbeon_flat_#{app}_#{form}. For instance, if your app is hr and you form is expense, then the view is named orbeon_flat_hr_expense. If upon publishing, there is already a view with that name, the Oracle persistence layer deletes it before recreating a new view.The view always has the following 4 metadata columns, with information copied from the equivalent columns in orbeon_form_data: metadata_document_id, metadata_created, metadata_last_modified, and metadata_username. In addition to those 4 columns, you have one column per form field, and each column is named by combining the section name with the control name. You can see an example of generated column names based on section and control names in the following table:
Oracle columns names are limited to 30 characters, so the Oracle persistence layer truncates the section name to 14 characters, the control name to 15 characters, and combines both with an underscore in between. It also converts dashes to underscores, removes any non alphanumerical character, and converts the name to uppercase (so it can be used in queries without quotes). In the vast majority of the cases, this will result in distinct and recognizable column names. In cases where two or more columns would end up having the same name or conflict with one of the metadata column, the Oracle persistence layer adds a number prefix of the form 001_, 002_, 003_… to each column to make it unique. If this happens, you might want to change your section and/or control names to have more recognizable column names.MySQLOrbeon Forms doesn't (yet) provide a way to have a table or view automatically created for a form upon publishing in MySQL, as it does in Oracle. However, you can do this manually. For instance, assume you want to create a "flat"bookshelf table for the sample bookshelf form. You want that table to have 3 columns:
bookshelf table:create table bookshelf ( document_id varchar(256), title text, author text);Choose an appropriate type for your columns, depending on the maximum length for the fields. Then create a trigger, which will update your bookshelf table when form data is saved in orbeon_form_data:delimiter |create trigger bookshelf_trigger before insert on orbeon_form_data for each row begin if new.app = 'orbeon' and new.form = 'bookshelf' then delete from bookshelf where document_id = new.document_id; if new.deleted = 'N' then insert into bookshelf set document_id = new.document_id, title = extractValue(new.xml, '/book/details/title'), author = extractValue(new.xml, '/book/details/author'); end if; end if;end;|Since you are interested in data for Bookshelf form, which is in the app orbeon form bookshelf, the trigger only does something if new.app = 'orbeon' and new.form = 'bookshelf'. To enable auditing, the MySQL persistence layer never deletes or updates data; it only inserts new row. So your trigger only needs to be concerned about updates. On insert, you want to make sure you are not creating duplicates in your bookshelf table, hence the delete statement. When a newly inserted row has delete = 'N', this indicates that a user deleted that document, in which case you don't want to insert a row in your bookshelf table, hence the if test.Auditing and versioningOracleThe Oracle persistence layer never deletes old form definitions or form data:
Note however that, as of November 2011, there is no user interface for this feature. |
|
|
|
Oracle and MySQL Persistence Layers |