Orbeon Forms community mailing list

Working on PostgreSQL Support

Classic

List

Threaded

14 messages Options

Aaron Spike

Working on PostgreSQL Support

Now that I've finished making every possible mistake while building and configuring Orbeon Forms, I would like to take a (rather blind) stab at implementing support for PostgreSQL. You were kind enough to point me to the relevant code sections on Twitter:

https://github.com/orbeon/orbeon-forms/tree/master/src/main/scala/org/orbeon/oxf/fr/relational/crud

https://github.com/orbeon/orbeon-forms/tree/master/src/resources/apps/fr/persistence/relational

I'm currently working my way through those sections. The first problem I have encountered is that Orbeon hands the XML document to the database as a raw string.

https://github.com/orbeon/orbeon-forms/blob/master/src/main/scala/org/orbeon/oxf/fr/relational/crud/CreateUpdateDelete.scala#L224

Other databases seem to be using an XML data type:

https://github.com/orbeon/orbeon-forms/blob/master/src/resources/apps/fr/persistence/relational/ddl/db2-4_6.sql#L37

I would expect something like XMLPARSE( DOCUMENT ? ) and I guess PostgreSQL does too:

2014-10-01 08:28:40 CDT orbeon orbeon ERROR: column "xml" is of type xml but expression is of type character varying at character 232

2014-10-01 08:28:40 CDT orbeon orbeon HINT: You will need to rewrite or cast the expression.

2014-10-01 08:28:40 CDT orbeon orbeon STATEMENT: INSERT INTO orbeon_form_data

( created, last_modified_time, last_modified_by, app, form, form_version, document_id, deleted, draft, username, groupname, xml )

VALUES ( $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12 )

I know nothing about Scala and next to nothing about Java. I'm attempting to alter this. Please let me know if I'm taking this in an incorrect direction:

diff --git a/src/main/scala/org/orbeon/oxf/fr/relational/crud/CreateUpdateDelete.scala b/src/main/scala/org/orbeon/oxf/fr/relational/crud/CreateUpdateDelete.scala

index f9c251c..c35a583 100644

--- a/src/main/scala/org/orbeon/oxf/fr/relational/crud/CreateUpdateDelete.scala

+++ b/src/main/scala/org/orbeon/oxf/fr/relational/crud/CreateUpdateDelete.scala

@@ -183,6 +183,7 @@ trait CreateUpdateDelete extends RequestResponse with Common {

// Do insert

locally {

val xmlCol = if (req.provider == "oracle") "xml_clob" else "xml"

+ val xmlVal = if (req.provider == "postgresql") "XMLPARSE( DOCUMENT ? )" else "?"

val isFormDefinition = req.forForm && ! req.forAttachment

val now = new Timestamp(System.currentTimeMillis())

@@ -195,36 +196,36 @@ trait CreateUpdateDelete extends RequestResponse with Common {

}

val possibleCols = List(

- true → "created" → param(_.setTimestamp, existingRow.map(_.created).getOrElse(now)),

- true → "last_modified_time" → param(_.setTimestamp, now),

- true → "last_modified_by" → param(_.setString , requestUsername.getOrElse(null)),

- true → "app" → param(_.setString , req.app),

- true → "form" → param(_.setString , req.form),

- true → "form_version" → param(_.setInt , versionToSet),

- req.forData → "document_id" → param(_.setString , req.dataPart.get.documentId),

- true → "deleted" → param(_.setString , if (delete) "Y" else "N"),

- req.forData → "draft" → param(_.setString , if (req.dataPart.get.isDraft) "Y" else "N"),

- req.forAttachment → "file_name" → param(_.setString , req.filename.get),

- req.forAttachment → "file_content" → param(_.setBytes , RequestReader.bytes()),

- isFormDefinition → "form_metadata" → param(_.setString , metadataOpt.getOrElse(null)),

- req.forData → "username" → param(_.setString , existingRow.map(_.username).flatten.getOrElse(requestUsername.getOrElse(null))),

- req.forData → "groupname" → param(_.setString , existingRow.map(_.group ).flatten.getOrElse(requestGroup .getOrElse(null))),

- ! req.forAttachment → xmlCol → param(_.setString , xmlOpt.getOrElse(null))

+ true → "created" → "?" → param(_.setTimestamp, existingRow.map(_.created).getOrElse(now)),

+ true → "last_modified_time" → "?" → param(_.setTimestamp, now),

+ true → "last_modified_by" → "?" → param(_.setString , requestUsername.getOrElse(null)),

+ true → "app" → "?" → param(_.setString , req.app),

+ true → "form" → "?" → param(_.setString , req.form),

+ true → "form_version" → "?" → param(_.setInt , versionToSet),

+ req.forData → "document_id" → "?" → param(_.setString , req.dataPart.get.documentId),

+ true → "deleted" → "?" → param(_.setString , if (delete) "Y" else "N"),

+ req.forData → "draft" → "?" → param(_.setString , if (req.dataPart.get.isDraft) "Y" else "N"),

+ req.forAttachment → "file_name" → "?" → param(_.setString , req.filename.get),

+ req.forAttachment → "file_content" → "?" → param(_.setBytes , RequestReader.bytes()),

+ isFormDefinition → "form_metadata" → "?" → param(_.setString , metadataOpt.getOrElse(null)),

+ req.forData → "username" → "?" → param(_.setString , existingRow.map(_.username).flatten.getOrElse(requestUsername.getOrElse(null))),

+ req.forData → "groupname" → "?" → param(_.setString , existingRow.map(_.group ).flatten.getOrElse(requestGroup .getOrElse(null))),

+ ! req.forAttachment → xmlCol → xmlVal → param(_.setString , xmlOpt.getOrElse(null))

)

val includedCols =

for {

- ((included, col), param) ← possibleCols

+ (((included, col), placeholder), param) ← possibleCols

if included

- } yield col → param

+ } yield col → placeholder → param

val ps = connection.prepareStatement(

s"""|INSERT INTO $table

- | ( ${includedCols.map(_._1 ).mkString(", ")} )

- | VALUES ( ${includedCols.map(_ ⇒ "?").mkString(", ")} )

+ | ( ${includedCols.map(_._1._1).mkString(", ")} )

+ | VALUES ( ${includedCols.map(_._1._2).mkString(", ")} )

|""".stripMargin)

- for (((_, param), i) ← includedCols.zipWithIndex)

+ for ((((_, _), param), i) ← includedCols.zipWithIndex)

param(ps, i + 1)

ps.executeUpdate()

}

This electronic communication, including any attached documents, may contain confidential and/or legally privileged information that is intended only for use by the recipient(s) named above. If you have received this communication in error, please notify the sender immediately and delete the communication and any attachments. Views expressed by the author do not necessarily represent those of Martin Luther College.

--
You received this message because you are subscribed to the Google Groups "Orbeon Forms" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
To post to this group, send email to [hidden email].

Aaron Spike

Re: Working on PostgreSQL Support

Basic support for postgresql seems to be working in my branch:

https://github.com/martinluther/orbeon-forms/tree/postgresql

Aaron Spike

Erik Bruchez

Re: Working on PostgreSQL Support

Administrator

Cool, thanks for sharing!

As mentioned on Twitter, next step might be to look at search.

This is not yet in Scala:

https://github.com/orbeon/orbeon-forms/blob/master/src/resources/apps/fr/persistence/relational/search.xpl

-Erik

ali112

Re: Working on PostgreSQL Support

This post was updated on .

In reply to this post by Aaron Spike

Nice! It can be a good alternative to MySQL limitations with Orbeon.

I haven't worked with PostgreSQL yet. I read you can't index an XML file with it, but you can index the result of an XPATH function on the XML datatype. And PostgreSQL is supported by Amazon RDS.

Erik Bruchez

Re: Working on PostgreSQL Support

Administrator

Yes that would be good.

Although we are also thinking about bypassing database support for search:

https://github.com/orbeon/orbeon-forms/issues/1801

It is just hard to implement all search at the database level when you have to support 5 or 6 databases, some of which being pretty poor at the job.

-Erik

Aaron Spike

Re: Working on PostgreSQL Support

As far as I can tell search is now working. (Though as I explained via twitter, I'm not sure I'm able to test all code paths.) Once again, any feedback and further direction is much appreciated.

https://github.com/martinluther/orbeon-forms/compare/postgresql

Aaron Spike

Re: Working on PostgreSQL Support

Flat view seem to work with postgresql in my branch as well.

What else is there?

Aaron Spike

Erik Bruchez

Re: Working on PostgreSQL Support

Administrator

The form metadata API is probably next, and last:

http://wiki.orbeon.com/forms/doc/developer-guide/form-runner/persistence-api#TOC-Deployed-forms-metadata

-Erik

Aaron Spike

Re: Working on PostgreSQL Support

I'm probably not understanding this fully and I'm not sure how to test it (when I try to access the URIs from that document, eg .../fr/service/postgresql/form, I get 403ed). But It might be possible that I've already done it. The form runner summary works:

https://github.com/martinluther/orbeon-forms/blob/232c13df7f02b21ddeadaadac9dc42f81ca6cf8f/src/resources/apps/fr/persistence/relational/form.xpl#L42

Aaron Spike

Erik Bruchez

Re: Working on PostgreSQL Support

Administrator

Try adding:

<property
as="xs:string"
processor-name="oxf:page-flow"
name="service-public-methods"
value="GET HEAD POST"/>

and restart.

Then you'll be able to load the service from your browser.

What you want to test is the home page:

localhost:8080/orbeon/fr/

-Erik

sodastream

Re: Working on PostgreSQL Support -- Postxdb

Hi,

I'd like to mention that our project has implemented PostgreSQL persistence and run it for about a year.

We are definitely willing to contribute it back to Orbeon. The reason we haven't done so yet is that it's different. It does not use Orbeon's framework for incorporating RDBMSs. Now that we have some experience with its usage, we could profit from discussing our solution. It's called Postxdb.

Two of the hurdles we originally wanted to overcome with Postxdb were,

-- There was no version management of Orbeon forms (there is now)

-- The data model implied by the Orbeon RDBMS framework is not easily used by other applications

Our solution is to decouple from Orbeon's RDBMS framework and hook up to the eXist-db interface. In practice this means that you configure Orbeon forms for eXist-db and drop a war file named exist.war (implementing Postxdb) into your servlet container. It solves the hurdles mentioned above in the following way,

-- Versions of forms are created by modifying form names

-- The resulting data model is straightforward and easily used by other applications. It uses the concepts form definition, form definition version for metadata and item for all types of contents (xml, attachments, etc.).

Postxdb runs on top of Hibernate which means it can easily be reconfigured for most database systems.

The eXist-db API is the only connection between Orbeon and the database. Orbeon thinks it runs eXist-db. The Orbeon source code is untouched. For the benefit of external applications the Postxdb REST API is complemented with a few additional REST services. There is also a gui for inspecting the database.

For those who have tried and been discouraged by eXist-db, because its performance breaks down with increasing concurrency: Postxdb performance degrades gracefully under increasing pressure of concurrency.

During this work we discovered another performance issue. Orbeon uses the pipeline machinery to transform queries from REST notation to SQL. Pipelines are versatile and powerful, but in this case probably overkill. Typically a persistence operation spends more time in the pipeline than in the database.

Postxdb is a drop-in replacement for eXist-db for the special purpose of Orbeon persistence. It is not a general-purpose eXist-db replacement.

Postxdb may not be the ideal solution for everyone, but I'm ready to answer questions.

/Hakan

Aaron Spike

Re: Working on PostgreSQL Support

In reply to this post by Erik Bruchez

What you want to test is the home page:

localhost:8080/orbeon/fr/

The home page works as far as I can tell.

"/orbeon/fr/service/persistence/form" works. "/orbeon/fr/service/postgresql/form" fails with the following error:

2014-10-08 08:48:20,169 ERROR PageFlowControllerProcessor - error caught {controller: "oxf:/apps/fr/page-flow.xml", method: "GET", path: "/fr/service/postgresql/form"}

2014-10-08 08:48:20,181 ERROR PageFlowControllerProcessor -

2014-10-08 08:48:20,181 ERROR PageFlowControllerProcessor - +----------------------------------------------------------------------------------------------------------------------+

2014-10-08 08:48:20,181 ERROR PageFlowControllerProcessor - |An Error has Occurred |