nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

Support of MS SQL on Alfresco 3 Labs beta

michielwilleken

Dear all,

I have searched the Alfresco website quite thoroughly and also read a lot of posts regarding the new Alfresco 3 labs release.

However, in the configuration files it is no longer possible to select MS SQL as the database. I have tried to copy the setings from Alfresco 2.9 without any success. Could anyone confirm or disconfirm that MS SQL is no longer supported in Alfresco 3 Labs?

Find more posts tagged with

Comments

greendevil

I had the same issue.
I found an JIRA issue entry that mentions there is support for other DB's comming. It mention that it needs Activity feeds (ActivityFeed.xml). If you have a look under classes/alfresco/activities, it lists the currently supported db's (by the hibernate dialect). I was thinking of just creating an entry for SQL server, based on the content of one of the other Db's present - may work - have no idea.

benswitzer

Hey guys.

Came across this as well while setting to use SQL Server. I've created the three files that are required.

I'm not sure how to attach files, so I'll just paste their contents here. All three files need to be placed in a directory called \HEAD\root\projects\repository\config\alfresco\activities\org.hibernate.dialect.SQLServerDialect.

ActivityPost.xml

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="ActivityPost">

   <typeAlias alias="ActivityPost" type="org.alfresco.repo.activities.post.ActivityPostDAO"/>

   <resultMap id="ActivityPostResult" class="ActivityPost">
      <result property="id" column="SEQUENCE_ID"/>
      <result property="activityData" column="ACTIVITY_DATA"/>
      <result property="activityType" column="ACTIVITY_TYPE"/>
      <result property="userId" column="POST_USER_ID"/>
      <result property="postDate" column="POST_DATE"/>
      <result property="jobTaskNode" column="JOB_TASK_NODE"/>
      <result property="siteNetwork" column="SITE_NETWORK"/>
      <result property="appTool" column="APP_TOOL"/>
      <result property="status" column="STATUS"/>
      <result property="lastModified" column="LAST_MODIFIED"/>
   </resultMap>

   <select id="select.activity.posts" parameterClass="ActivityPost" resultClass="ActivityPost">
      <![CDATA[
      select 
         sequence_id as id,
         activity_data as activityData,
         activity_type as activityType,
         post_user_id as userId,
         post_date as postDate,
         job_task_node as jobTaskNode,
         site_network as siteNetwork,
         app_tool as appTool,
         status as status
      from 
         alf_activity_post
      where 
         job_task_node = #jobTaskNode# and
         sequence_id >= #minId# and
         sequence_id <= #maxId# and
         status = #status#
      ]]>
   </select>
  
   <select id="select.activity.posts.by.status.only" parameterClass="ActivityPost" resultClass="ActivityPost">
      <![CDATA[
      select 
         sequence_id as id,
         activity_data as activityData,
         activity_type as activityType,
         post_user_id as userId,
         post_date as postDate,
         job_task_node as jobTaskNode,
         site_network as siteNetwork,
         app_tool as appTool,
         status as status
      from 
         alf_activity_post
      where 
         status = #status#
      ]]>
   </select>
  
   <select id="select.activity.post.max.seq" resultClass="long">
      select max(sequence_id) as maxId
      from alf_activity_post
      where status = 'POSTED'
   </select>
  
   <select id="select.activity.post.min.seq" resultClass="long">
      select min(sequence_id) as minId
      from alf_activity_post
      where status = 'POSTED'
   </select>
  
   <select id="select.activity.post.max.jobtasknode" resultClass="int">
      select max(job_task_node) as maxJobTaskNode
      from alf_activity_post
      where status = 'POSTED'
   </select>

   <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 @@identity
      </selectKey>
      
   </insert>

   <delete id="delete.activity.posts.older.than.date" parameterClass="ActivityPost">
      <![CDATA[
      delete from alf_activity_post 
      where post_date < #postDate#
      and status = #status#
      ]]>
   </delete>
  
   <update id="update.activity.post.data" parameterClass="ActivityPost">
      update alf_activity_post set status = #status#, activity_data=#activityData#, site_network=#siteNetwork#, last_modified=#lastModified#
      where sequence_id = #id#
      and status != #status#
   </update>
  
   <update id="update.activity.post.status" parameterClass="ActivityPost">
      update alf_activity_post set status = #status#, last_modified=#lastModified#
      where sequence_id = #id#
      and status != #status#
   </update>

</sqlMap>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

ActivityFeedControl.xml

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="ActivityFeedControl">

   <typeAlias alias="FeedControl" type="org.alfresco.repo.activities.feed.control.FeedControlDAO"/>

   <resultMap id="FeedControlResult" class="FeedControl">
      <result property="id" column="ID"/>
      <result property="feedUserId" column="FEED_USER_ID"/>
      <result property="siteNetwork" column="SITE_NETWORK"/>
      <result property="appTool" column="APP_TOOL"/>
      <result property="lastModified" column="LAST_MODIFIED"/>
   </resultMap>
  
   <select id="select.activity.feedcontrols.for.user" parameterClass="FeedControl" resultClass="FeedControl">
      <![CDATA[
      select id as id, feed_user_id as feedUserId, site_network as siteNetwork, app_tool as appTool
      from alf_activity_feed_control
      where feed_user_id = #feedUserId#
      ]]>
   </select>
   
   <select id="select.activity.feedcontrol" parameterClass="FeedControl" resultClass="long">
      <![CDATA[
      select id as id
      from alf_activity_feed_control
      where feed_user_id = #feedUserId#
      and site_network = #siteNetwork#
      and app_tool = #appTool#
      ]]>
   </select>

   <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 @@identity
      </selectKey>
   </insert>
  
   <delete id="delete.activity.feedcontrol" parameterClass="FeedControl">
      <![CDATA[
      delete from alf_activity_feed_control
      where feed_user_id = #feedUserId#
      and site_network = #siteNetwork#
      and app_tool = #appTool#
      ]]>
   </delete>

</sqlMap>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

ActivityFeed.xml

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="ActivityFeed">

   <typeAlias alias="ActivityFeed" type="org.alfresco.repo.activities.feed.ActivityFeedDAO"/>

   <resultMap id="ActivityFeedResult" class="ActivityFeed">
      <result property="id" column="ID"/>
      <result property="feedUserId" column="FEED_USER_ID"/>
      <result property="postUserId" column="POST_USER_ID"/>
      <result property="postDate" column="POST_DATE"/>
      <result property="postId" column="POST_ID"/> <!– not an explicit FK constraint, can dangle if and when activity post is deleted –>
      <result property="siteNetwork" column="SITE_NETWORK"/>
      <result property="activityType" column="ACTIVITY_TYPE"/>
      <result property="activitySummary" column="ACTIVITY_SUMMARY"/>
      <result property="activitySummaryFormat" column="ACTIVITY_FORMAT"/>
      <result property="feedDate" column="FEED_DATE"/>
   </resultMap>
  
   <select id="select.activity.feed.for.feeduser" parameterClass="ActivityFeed" resultClass="ActivityFeed">
      <![CDATA[
      select id as id, activity_type as activityType, activity_format as activitySummaryFormat, activity_summary as activitySummary, feed_user_id as feedUserId, post_user_id as postUserId, site_network as siteNetwork, post_date postDate
      from alf_activity_feed
      where feed_user_id = #feedUserId#
      and post_user_id != #feedUserId#
      and activity_format = #activitySummaryFormat#
      order by post_date desc
      ]]>
   </select>
   
   <select id="select.activity.feed.for.feeduser.and.site" parameterClass="ActivityFeed" resultClass="ActivityFeed">
      <![CDATA[
      select id as id, activity_type as activityType, activity_format as activitySummaryFormat, activity_summary as activitySummary, feed_user_id as feedUserId, post_user_id as postUserId, site_network as siteNetwork, post_date postDate
      from alf_activity_feed
      where feed_user_id = #feedUserId#
      and post_user_id != #feedUserId#
      and site_network = #siteNetwork#
      and activity_format = #activitySummaryFormat#
      order by post_date desc
      ]]>
   </select>
   
   <select id="select.activity.feed.for.site" parameterClass="ActivityFeed" resultClass="ActivityFeed">
      <![CDATA[
      select id as id, activity_type as activityType, activity_format as activitySummaryFormat, activity_summary as activitySummary, post_user_id as postUserId, site_network as siteNetwork, post_date postDate
      from alf_activity_feed
      where feed_user_id = ''
      and site_network = #siteNetwork#
      and activity_format = #activitySummaryFormat#
      order by post_date desc
      ]]>
   </select>

   <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 @@identity
      </selectKey>
   </insert>
  
   <delete id="delete.activity.feed.entries.older.than.date" parameterClass="Date">
      <![CDATA[
      delete from alf_activity_feed where post_date < #keepdate#
      ]]>
   </delete>

</sqlMap>
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

michielwilleken

Thanks a lot Ben!
Excellent comment, we have been able to use Microsoft SQL Server using your settings.

To be more precise, we had to create the directory:
C:\Alfresco\tomcat\webapps\alfresco\WEB-INF\classes\alfresco\activities\org.alfresco.repo.domain.hibernate.dialect.AlfrescoSQLServerDialect
where we put the files you specified.

custom-repository.properties:
db.schema.update=true
db.driver=net.sourceforge.jtds.jdbc.Driver
db.name=alfresco
db.url=jdbc:jtds:sqlserver://localhost:1433
db.username=sa
db.password=Sinas203
db.pool.initial=10
db.pool.max=20

custom-hibernate-dialect.properties:
hibernate.dialect=org.alfresco.repo.domain.hibernate.dialect.AlfrescoSQLServerDialect

tlarson

Hi guys,

I've followed your post to install my copy of labs beta on my workstation with mssql 2000(using jtds). however, i keep getting this:

Exception in thread "Thread-11" java.lang.OutOfMemoryError: Java heap space
at org.alfresco.repo.avm.AVMRemoteTransportService.run(AVMRemoteTranspor
tService.java:160)
at java.lang.Thread.run(Thread.java:595)
14:22:16,220 ERROR [web.context.ContextLoader] Context initialization failed
org.alfresco.error.AlfrescoRuntimeException: Bootstrap failed
…..
…..
…..

Please help with the out of mem err.

the second time I run it, this is the error i get:

ERROR [domain.schema.SchemaBootstrap] Schema auto-update failed
org.alfresco.error.AlfrescoRuntimeException: The schema script could not be foun
d at location classpath:alfresco/dbscripts/upgrade/1.4/${db.script.dialect}/Alfr
escoSchemaUpdate-1.4-1.sql
…..
…..
…..

since it doesn't come with ms sql support, it problly doesnt know how to find ${db.script.dialect}. is there a work-around?

also, does anyone know if there's a well documented install help for ms sql? this information is scattered.

TIA.