Installing Alfresco Community 32 and Ingres 10

[resplin]

Obsolete Pages{{Obsolete}}

The official documentation is at: http://docs.alfresco.com

Ingres 3.2

Table of Contents

• 1 Updates


• 2 Preamble


• 3 Installing Alfresco and Ingres
  
  
  
  • 3.1 Alfresco 3.2
  
  
  • 3.2 Ingres Database 10
    
    
    
    • 3.2.1 Installing using the 'ingbuild' tool
    
    
  
  


• 4 Configuring Alfresco and Ingres
  
  
  
  • 4.1 Ingres Database
    
    
    
    • 4.1.1 Create the database which will be used by Alfresco
    
    
  
  
  • 4.2 Alfresco
    
    
    
    • 4.2.1 Modify the content inside the Alfresco web application archive
      
      
      
      • 4.2.1.1 Database scripts
      
      
      • 4.2.1.2 Ibatis mappings
      
      
    
    
  
  


• 5 Known issues

Updates

• Ingres 10 will include a new function named 'last_identity'. This helps to avoid the workaround with the 'max' function which is mentioned below. Further information are available here: http://community.ingres.com/wiki/Defacto_Datatypes_and_Functions#LAST_IDENTITY_function
  

• The optimized Ingres database schema for Alfresco 3.2 is now available: ftp://ftp.ingres.com/maida01/wiki.alfresco.com/alf32_schema_opt.zip
  

Preamble

This is the second tutorial with the subject 'Installing Alfresco & Ingres'. The first one can be found at Installing_Alfresco_Labs_3b_and_Ingres ( we will then use the link 1 for future references ) and describes very detailed how to install Alfresco 3.0 with Ingres. It is recommended to read it because the following tutorial will only explain the differences regarding Alfresco 3.2 and Ingres 10.

Installing Alfresco and Ingres

Alfresco 3.2

You should use Alfresco's GUI installer. The installation routine is quite the same as for Alfresco 3.0.

Ingres Database 10

The Community Edition of 'Ingres Database 10' can be downloaded from  http://esd.ingres.com .

-Attention please:- Please be aware of that Ingres 10 is still in development and is not generally available until now. So you should currently ( current date is 2009-08-24 ) not use it in production.

-Note:- The current GA version (Enterprise Edition) of Ingres is 9.2. It comes with full Enterprise Support. If you want to use Ingres with Alfresco in production you should use the Enterprise Edition. Please contact Ingres (products@ingres.com) for further information.

Installing using the 'ingbuild' tool

The previous article 1 explained how to perform an ingbuild installation completely manually. It is also possible to use the scripts those are part of the installation package. The current archive ( which means ingres-10.0.0-105-NPTL-gpl-pc-linux-ingbuild-i386.tgz ) contains two script files:

• ingres_express_install.sh
  
• install.sh
  

The 'ingres_express_install.sh' script performs a default installation by using e.G. the installation id II. You should use the 'install.sh' script to have more control on the parameters of your Ingres Database installation. It allows you to set:

• Installation identifier
  
• Time zone information
  
• Character set (UTF8 or non UTF8)
  
• ...
  

Please check the success of the installation as mentioned in 1.

Configuring Alfresco and Ingres

Ingres Database

The configuration of the Ingres DBMS works the same way as described in 1. You should use the configuration script which is attached there.

Create the database which will be used by Alfresco

The database schema differs. It will be created automatically during the first startup of Alfresco, but I will publish an optimized version for Ingres soon.

Alfresco

The way how to configure Alfresco 3.2 changed a bit. Instead the 'custom-respository.properties' file now the file '.../tomcat/shared/classes/alfresco-global.properties' should contain the configuration of the database connection. This new configuration file now also contains the Hibernate related configuration.

#
# Sample database connection properties
#-------------
db.name=alfresco
db.username=ingres
db.password=password_of_user_ingres
db.host=localhost
db.port=A37

#
# Ingres connection using Driver iijdbc.jar
#-------------
db.driver=com.ingres.jdbc.IngresDriver
db.url=jdbc:ingres://${db.host}:${db.port}/${db.name}
hibernate.dialect=org.hibernate.dialect.IngresDialect
hibernate.query.substitutions=true 1, false 0, yes 'Y', no 'N'

-Note:- Do not forget to add the Ingres JDBC driver to the Tomcat's lib directory!

Modify the content inside the Alfresco web application archive

This section contains the most important changes since Alfresco 3.0 . The alfresco.war file can be found at '.../tomcat/webapps/alfresco.war'(let's name it <alfresco.war>). It contains the following relevant folders/subfolders:

• <alfresco.war>/WEB-INF/classes/alfresco/dbscripts
  
• <alfresco.war>/WEB-INF/classes/alfresco/ibatis
  

Both folders contain data and scripts to generate the initial database schema. BTW: Ibatis is an O/R-mapper ( http://ibatis.apache.org ).

Database scripts

We are primarily interested in the scripts those are required to create the database. Therefore the folder '<alfresco.war>/WEB-INF/classes/alfresco/dbscripts/create' needs further investigation. The subfolders of this folder are related to the Alfresco versions. We are interested in the '3.0' and '3.2'. Each of this Alfresco version related folders contains subfolders named by the Hibernate dialect of the supported database systems. My installation of Alfresco 3.2 has only the folders 'org.hibernate.dialect.Dialect' (contains stuff which is the same for all DBMS) ,'org.hibernate.dialect.DerbyDialect' and 'org.hibernate.dialect.MySQLInnoDBDialect' (contains Derby/MySQL specific stuff). So let's add the Ingres specific stuff as well:

<alfresco.war>/WEB-INF/classes/alfresco/dbscripts/create/3.0/org.hibernate.dialect.IngresDialect/create-activities-extras.sql   

--

-- Title:      Activities Schema - Extras (Indexes, Sequences)

-- Database:   Ingres

-- Since:      V3.0.0 Schema

-- Author:     David Maier

--


DROP TABLE alf_activity_post;

CREATE TABLE alf_activity_post (

  sequence_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,

  post_date timestamp NOT NULL,

  status varchar(10) NOT NULL,

  activity_data varchar(4000) NOT NULL,

  post_user_id varchar(255) NOT NULL,

  job_task_node integer NOT NULL,

  site_network varchar(255) default NULL,

  app_tool varchar(36) default NULL,

  activity_type varchar(255) NOT NULL,

  last_modified timestamp NOT NULL,

  primary key (sequence_id)

);

DROP TABLE alf_activity_feed;

CREATE TABLE alf_activity_feed (

  id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,

  post_id bigint default NULL,

  post_date timestamp NOT NULL,

  activity_summary varchar(4000) default NULL,

  feed_user_id varchar(255) NOT NULL,

  activity_type varchar(255) NOT NULL,

  activity_format varchar(10) default NULL,

  site_network varchar(255) default NULL,

  app_tool varchar(36) default NULL,

  post_user_id varchar(255) NOT NULL,

  feed_date timestamp NOT NULL,

  primary key (id)

);

DROP TABLE alf_activity_feed_control;

CREATE TABLE alf_activity_feed_control (

  id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,

  feed_user_id varchar(255) NOT NULL,

  site_network varchar(255) NOT NULL,

  app_tool varchar(36) default NULL,

  last_modified timestamp NOT NULL,

  primary key (id)

);


CREATE INDEX post_jobtasknode_idx ON alf_activity_post(job_task_node);

CREATE INDEX post_status_idx ON alf_activity_post(status);

CREATE INDEX feed_postdate_idx ON alf_activity_feed(post_date);

CREATE INDEX feed_postuserid_idx ON alf_activity_feed(post_user_id);

CREATE INDEX feed_feeduserid_idx ON alf_activity_feed(feed_user_id);

CREATE INDEX feed_sitenetwork_idx ON alf_activity_feed(site_network);

CREATE INDEX feed_activityformat_idx ON alf_activity_feed(activity_format);


CREATE INDEX feedctrl_feeduserid_idx ON alf_activity_feed_control(feed_user_id);



--

-- Record script finish

--

DELETE FROM alf_applied_patch WHERE id = 'patch.db-V3.0-0-CreateActivitiesExtras';

INSERT INTO alf_applied_patch

  (id, description, fixes_from_schema, fixes_to_schema, applied_to_schema, target_schema, applied_on_date, applied_to_server, was_executed, succeeded, report)

  VALUES

  (

    'patch.db-V3.0-0-CreateActivitiesExtras', 'Executed script create V3.0: Created activities extras',

    0, 125, -1, 126, null, 'UNKNOWN', 1, 1, 'Script completed'

  );

<alfresco.war>/WEB-INF/classes/alfresco/dbscripts/create/3.2/org.hibernate.dialect.IngresDialect/AlfrescoPostCreate-3.2-ContentTables.sql

--

-- Title:      Create Content tables

-- Database:   Ingres

-- Since:      V3.2 Schema 2012

-- Author:     Derek Hulley, David Maier

--

-- Please contact support@alfresco.com if you need assistance with the upgrade.

--

CREATE TABLE alf_mimetype

(

   id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,

   version BIGINT NOT NULL,

   mimetype_str VARCHAR(100) NOT NULL,

   PRIMARY KEY (id),

   UNIQUE (mimetype_str)

);


CREATE TABLE alf_encoding

(

   id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,

   version BIGINT NOT NULL,

   encoding_str VARCHAR(100) NOT NULL,

   PRIMARY KEY (id),

   UNIQUE (encoding_str)

);



-- This table may exist during upgrades, but must be removed.

-- The drop statement is therefore optional.

DROP TABLE alf_content_url;                     --(optional)



CREATE TABLE alf_content_url

(

   id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,

   version BIGINT NOT NULL,

   content_url VARCHAR(255) NOT NULL,

   content_url_short VARCHAR(12) NOT NULL,

   content_url_crc BIGINT NOT NULL,

   content_size BIGINT NOT NULL,

   UNIQUE (content_url_short, content_url_crc),

   PRIMARY KEY (id)

);





CREATE TABLE alf_content_data

(

   id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,

   version BIGINT NOT NULL,

   content_url_id BIGINT,

   content_mimetype_id BIGINT,

   content_encoding_id BIGINT,

   content_locale_id BIGINT,

   PRIMARY KEY (id)

);


ALTER TABLE alf_content_data ADD CONSTRAINT fk_alf_cont_url FOREIGN KEY (content_url_id) REFERENCES alf_content_url (id);

ALTER TABLE alf_content_data ADD CONSTRAINT fk_alf_cont_mim FOREIGN KEY (content_mimetype_id) REFERENCES alf_mimetype (id);

ALTER TABLE alf_content_data ADD CONSTRAINT fk_alf_cont_enc FOREIGN KEY (content_encoding_id) REFERENCES alf_encoding (id);

ALTER TABLE alf_content_data ADD CONSTRAINT fk_alf_cont_loc FOREIGN KEY (content_locale_id) REFERENCES alf_locale (id);



CREATE TABLE alf_content_clean

(

   content_url VARCHAR(255) NOT NULL

);


CREATE INDEX idx_alf_contentclean_url ON alf_content_clean(content_url);



--

-- Record script finish

--

DELETE FROM alf_applied_patch WHERE id = 'patch.db-V3.2-ContentTables';

INSERT INTO alf_applied_patch

  (id, description, fixes_from_schema, fixes_to_schema, applied_to_schema, target_schema, applied_on_date, applied_to_server, was_executed, succeeded, report)

  VALUES

  (

    'patch.db-V3.2-ContentTables', 'Manually executed script upgrade V3.2: Content Tables',

    0, 2011, -1, 2012, null, 'UNKOWN', 1, 1, 'Script completed'

  );

<alfresco.war>/WEB-INF/classes/alfresco/dbscripts/create/3.2/org.hibernate.dialect.IngresDialect/AlfrescoPostCreate-3.2-LockTables.sql

--

-- Title:      Create lock tables

-- Database:   Ingres

-- Since:      V3.2 Schema 2011

-- Author:     Derek Hulley, David Maier

--

-- Please contact support@alfresco.com if you need assistance with the upgrade.

--



CREATE TABLE alf_lock_resource

(

   id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,

   version BIGINT NOT NULL,

   qname_ns_id BIGINT NOT NULL,

   qname_localname VARCHAR(255) NOT NULL,

   PRIMARY KEY (id),

   UNIQUE (qname_ns_id, qname_localname)
);


ALTER TABLE alf_lock_resource ADD CONSTRAINT fk_alf_lockr_ns FOREIGN KEY (qname_ns_id) REFERENCES alf_namespace (id);


CREATE TABLE alf_lock

(

   id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,

   version BIGINT NOT NULL,

   shared_resource_id BIGINT NOT NULL,

   excl_resource_id BIGINT NOT NULL,

   lock_token VARCHAR(36) NOT NULL,

   start_time BIGINT NOT NULL,

   expiry_time BIGINT NOT NULL,

   PRIMARY KEY (id),

   UNIQUE (shared_resource_id, excl_resource_id)
);


ALTER TABLE alf_lock ADD CONSTRAINT fk_alf_lock_shared FOREIGN KEY (shared_resource_id) REFERENCES alf_lock_resource (id);

ALTER TABLE alf_lock ADD CONSTRAINT fk_alf_lock_excl FOREIGN KEY (excl_resource_id) REFERENCES alf_lock_resource (id);

--

-- Record script finish

--

DELETE FROM alf_applied_patch WHERE id = 'patch.db-V3.2-LockTables';

INSERT INTO alf_applied_patch

  (id, description, fixes_from_schema, fixes_to_schema, applied_to_schema, target_schema, applied_on_date, applied_to_server, was_executed, succeeded, report)

  VALUES

  (

    'patch.db-V3.2-LockTables', 'Manually executed script upgrade V3.2: Lock Tables',

    0, 2010, -1, 2011, null, 'UNKOWN', 1, 1, 'Script completed'

  );

Ibatis mappings

The folder '<alfresco.war>/WEB-INF/classes/alfresco/ibatis' contains the following Ibatis configuration files:

• activities-SqlMapConfig.xml
  
• content-SqlMapConfig.xml
  
• locks-SqlMapConfig.xml
  
• ibatis-context.xml
  

It also contains subfolders named by the dialect of each natively supported DBMS. To add Ingres support the following steps must be performed:

1. Add a folder named 'org.hibernate.dialect.IngresDialect'
   
2. Copy everything from 'org.hibernate.dialect.Dialect' to 'org.hibernate.dialect.IngresDialect' (those are the *common*.xml map files)
   
3. Modify the Ibatis configuration files mentioned above to use the folder 'org.hibernate.dialect.IngresDialect' instead 'org.hibernate.dialect.Dialect' for the lookup
   
4. Create the following files with the following contents inside the folder 'org.hibernate.dialect.IngresDialect':
   

• activities-insert-SqlMap.xml,
  
• content-insert-SqlMap.xml
  
• locks-insert-SqlMap.xml
  

<alfresco.war>/WEB-INF/classes/alfresco/ibatis/org.hibernate.dialect.IngresDialect/activities-insert-SqlMap.xml

<sqlMap namespace='alfresco.activities'>



   <insert id='insert.activity.feedcontrol' parameterClass='FeedControl'>

   

      insert into alf_activity_feed_control (feed_user_id, site_network, app_tool, last_modified)

      values (#feedUserId#, #siteNetwork#, #appTool#, #lastModified#)

      

      <selectKey resultClass='long' keyProperty='id' type='post'>

         SELECT max(id) FROM alf_activity_feed_control

      </selectKey>

      

   </insert>



   <insert id='insert.activity.feed' parameterClass='ActivityFeed'>

   

      insert into alf_activity_feed (activity_type, activity_summary, activity_format, feed_user_id, post_user_id, post_date, post_id, site_network, app_tool, feed_date) 

      values (#activityType#, #activitySummary#, #activitySummaryFormat#, #feedUserId#, #postUserId#, #postDate#, #postId#, #siteNetwork#, #appTool#, #feedDate#)



      <selectKey resultClass='long' keyProperty='id' type='post'>

         SELECT max(id) FROM alf_activity_feed

      </selectKey>

      

   </insert>



   <insert id='insert.activity.post' parameterClass='ActivityPost'>

   

      insert into alf_activity_post (status, activity_data, post_user_id, post_date, activity_type, site_network, app_tool, job_task_node, last_modified)

      values (#status#, #activityData#, #userId#, #postDate#, #activityType#, #siteNetwork#, #appTool#, #jobTaskNode#, #lastModified#)

      

      <selectKey resultClass='long' keyProperty='id' type='post'>

         SELECT max(sequence_id) FROM alf_activity_post

      </selectKey>

   </insert>



</sqlMap>

<alfresco.war>/WEB-INF/classes/alfresco/ibatis/org.hibernate.dialect.IngresDialect/content-insert-SqlMap.xml

<sqlMap namespace='alfresco.content'>



    <insert id='insert.Mimetype' parameterClass='Mimetype' >

        <include refid='insert.Mimetype.AutoIncrement'/>

        <selectKey resultClass='long' keyProperty='id' type='post'>
        SELECT max(id) FROM alf_mimetype     

        </selectKey>

    </insert>



    <insert id='insert.Encoding' parameterClass='Encoding' >

        <include refid='insert.Encoding.AutoIncrement'/>

        <selectKey resultClass='long' keyProperty='id' type='post'>

        SELECT max(id) FROM alf_encoding

        </selectKey>

    </insert>



    <insert id='insert.ContentUrl' parameterClass='ContentUrl' >

        <include refid='insert.ContentUrl.AutoIncrement'/>

        <selectKey resultClass='long' keyProperty='id' type='post'>

        SELECT max(id) FROM alf_content_url
        </selectKey>

    </insert>



    <insert id='insert.ContentData' parameterClass='ContentData' >

        <include refid='insert.ContentData.AutoIncrement'/>

        <selectKey resultClass='long' keyProperty='id' type='post'>

        SELECT max(id) FROM alf_content_data
        </selectKey>

    </insert>

</sqlMap>

<alfresco.war>/WEB-INF/classes/alfresco/ibatis/org.hibernate.dialect.IngresDialect/locks-insert-SqlMap.xml

<sqlMap namespace='alfresco.lock'>



    <insert id='insert.LockResource' parameterClass='LockResource' >

        <include refid='insert.LockResource.AutoIncrement'/>

        <selectKey resultClass='long' keyProperty='id' type='post'>

        SELECT max(id) FROM alf_lock_resource
        </selectKey>

    </insert>



    <insert id='insert.Lock' parameterClass='Lock' >

        <include refid='insert.Lock.AutoIncrement'/>

        <selectKey resultClass='long' keyProperty='id' type='post'>

        SELECT max(id) FROM alf_lock_seq
        </selectKey>

    </insert>



</sqlMap>

Known issues

• As mentioned before an optimized database schema is required and will be provided soon
  
• Currently we gather the last inserted key value by using the 'max' function. This is a bit dirty and I can imagine possible problems caused by this way.
  
• I found an issue with the 'Studio' application which comes with the Alfresco 3.2 Community Edition. I still have to find out if it is Ingres related. However, the Alfresco Web Client application seems to work.