How to correctly configure JR with PostgreSQL?

[Mihai Anescu <ma...@totalsoft.ro>]

Hello,

So, we want to configure JR to work with a Postgres DB.

I looked into the JR site, found some doc, also in the
SimpleDbPersistenceManager javadoc, I got this configuration:

The following is a fragment from a sample configuration using
PostgreSQL: 
<PersistenceManager
class="org.apache.jackrabbit.core.persistence.db.SimpleDbPersistenceMana
ger>
       <param name="driver" value="org.postgresql.Driver"/>
       <param name="url" value="jdbc:postgresql://localhost/test"/>
       <param name="schema" value="postgresql"/>
       <param name="user" value="postgres"/>
       <param name="password" value="postgres"/>
       <param name="schemaObjectPrefix" value="${wsp.name}_"/>
       <param name="externalBLOBs" value="false"/>
</PersistenceManager>

I also read the part about the repository, now I have this part (which
works, after a few failed attempts, read bellow):

    <Workspace name="${wsp.name}">
        <!--
            virtual file system of the workspace:
            class: FQN of class implementing the FileSystem interface
        -->
        <FileSystem
class="org.apache.jackrabbit.core.fs.local.LocalFileSystem">
            <param name="path" value="${wsp.home}"/>
        </FileSystem>
        <!--
            persistence manager of the workspace:
            class: FQN of class implementing the PersistenceManager
interface
        -->

		<PersistenceManager
class="org.apache.jackrabbit.core.persistence.db.SimpleDbPersistenceMana
ger">
			   <param name="driver"
value="org.postgresql.Driver"/>
			   <param name="url"
value="jdbc:postgresql://localhost:5432/scrap"/>
			   <param name="schema" value="postgresql"/>
			   <param name="user" value="user"/>
			   <param name="password" value="password"/>
			   <param name="schemaObjectPrefix"
value="${wsp.name}_"/>
			   <param name="externalBLOBs" value="false"/>
		</PersistenceManager>
        <!--
            Search index and the file system it uses.
            class: FQN of class implementing the QueryHandler interface
        -->
        <SearchIndex
class="org.apache.jackrabbit.core.query.lucene.SearchIndex">
            <param name="path" value="${wsp.home}/index"/>
        </SearchIndex>
    </Workspace>

Now starts the questions & problems:
1) Why do I also need a FileSystem repository? Is this the way that JR
is buit? What gets stored in the DB and what in the FS?
2) The schema mentioned in the sample and in my working version
('postgresql') does not exist. It is using the default ('public') schema
of the scrap DB.
3) If I put another schema (I tried 'repository') I get an error: schema
does not exist (I created it by hand before launching the server)
4) If I use another value for schemaObjectPrefix (I tried 'jr_'), I get
another error: SimpleDbPersistenceManager: ERROR: type "varbinary" does
not exist.
5) If I try another url value
('jdbc:postgresql://localhost:5432/scrap?repository') I get the same
error as for number 4.

Are these known bugs? Is there another way to use the DB to store the
repository? I would like to use another schema (only for JR), since the
public one is used by another application.

Any help appreciated...

Regards,
Mihai

Re: How to correctly configure JR with PostgreSQL?

[Brian Thompson <el...@gmail.com>]

The only thing that requires the local filesystem is the Lucene search
index.  Everything else, including the workspace, can be put into the DB by
using a DB Persistence manager

Here's how I have my workspaces configured:

    <Workspace name="${wsp.name}">
        <!--
            virtual file system of the workspace:
            class: FQN of class implementing the FileSystem interface
        -->
        <FileSystem class="org.apache.jackrabbit.core.fs.db.DbFileSystem">
            <param name="driver" value="
com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
            <param name="url"
value="jdbc:sqlserver://servername:1433;databaseName=my_db_name"/>
            <param name="schema" value="mssql"/>
            <param name="user" value="my_user"/>
            <param name="password" value="my_password"/>
            <param name="schemaObjectPrefix" value="wsk_"/>
        </FileSystem>
        <!--
            persistence manager of the workspace:
            class: FQN of class implementing the PersistenceManager
interface
        -->
        <PersistenceManager class="
org.apache.jackrabbit.core.state.db.SimpleDbPersistenceManager">
            <param name="driver" value="
com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
            <param name="url"
value="jdbc:sqlserver://servername:1433;databaseName=my_db_name"/>
            <param name="schema" value="mssql"/>
            <param name="user" value="my_user"/>
            <param name="password" value="my_password"/>
            <param name="schemaObjectPrefix" value="${wsp.name}_"/>
            <param name="externalBLOBs" value="false"/>
        </PersistenceManager>
        <!--
            Search index and the file system it uses.
            class: FQN of class implementing the QueryHandler interface
        -->
        <SearchIndex class="
org.apache.jackrabbit.core.query.lucene.SearchIndex">
            <param name="path" value="${wsp.home}/index"/>
        </SearchIndex>
    </Workspace>

Though I haven't tried this with Postgres, hopefully this example will be
helpful :)

Good luck!  I'd be interested in knowing what ends up working for you.

-Brian


On 7/6/07, Mihai Anescu <ma...@totalsoft.ro> wrote:
>
> Hello,
>
> So, we want to configure JR to work with a Postgres DB.
>
> I looked into the JR site, found some doc, also in the
> SimpleDbPersistenceManager javadoc, I got this configuration:
>
> Now starts the questions & problems:
> 1) Why do I also need a FileSystem repository? Is this the way that JR
> is buit? What gets stored in the DB and what in the FS?
> 2) The schema mentioned in the sample and in my working version
> ('postgresql') does not exist. It is using the default ('public') schema
> of the scrap DB.
> 3) If I put another schema (I tried 'repository') I get an error: schema
> does not exist (I created it by hand before launching the server)
> 4) If I use another value for schemaObjectPrefix (I tried 'jr_'), I get
> another error: SimpleDbPersistenceManager: ERROR: type "varbinary" does
> not exist.
> 5) If I try another url value
> ('jdbc:postgresql://localhost:5432/scrap?repository') I get the same
> error as for number 4.
>
> Are these known bugs? Is there another way to use the DB to store the
> repository? I would like to use another schema (only for JR), since the
> public one is used by another application.
>
> Any help appreciated...
>
> Regards,
> Mihai
>
>