topic Re: Support for Sybase Database in Alfresco Archive

Support for Sybase Database

hrabe — Mon, 28 Feb 2011 11:59:04 GMT

fyi, start with sybase support following http://docs.codehaus.org/display/ACT/How+to+add+support+for+new+databases guidlines. I'm in contact with Bernd Rücker to provide CI asap.

Re: Support for Sybase Database

hrabe — Tue, 01 Mar 2011 16:03:03 GMT

Who had the idea of a unique constraint on a nullable column? For each database there is a given workaround expect Sybase.

Re: Support for Sybase Database

frederikherema1 — Wed, 02 Mar 2011 07:06:21 GMT

Wich column are you reffering to?

Re: Support for Sybase Database

hrabe — Wed, 02 Mar 2011 07:59:05 GMT

searching for workaround for h2 expression

alter table ACT_RU_EXECUTION
    add constraint ACT_UNIQ_RU_BUS_KEY
    unique(PROC_DEF_ID_, BUSINESS_KEY_);

alter table ACT_HI_PROCINST
    add constraint ACT_UNIQ_HI_BUS_KEY
    unique(PROC_DEF_ID_, BUSINESS_KEY_);

Re: Support for Sybase Database

hrabe — Wed, 02 Mar 2011 12:17:12 GMT

Found a solution for Sybase 15:

create unique index ACT_UNIQ_RU_BUS_KEY on ACT_RU_EXECUTION
(case when BUSINESS_KEY_ is null then ('pk-' + ID_) else ('bk-' + BUSINESS_KEY_) end, PROC_DEF_ID_);

But next problem raises on ant task 'create.db.schema' cause of the sql autocommit default which is false. Sybase can not create a functional index on a multi-statement transaction. The autocommit could be set over the specific db properties file which can lead to an undefined db state if one transaction cause an error.

The CycleDbSqlSessionFactory#dbSchemaCreate() must also be able to switch the autocommit mode.

Another option is to use mutiple sql files each executes in one transaction.

Feedback?

Re: Support for Sybase Database

bernd_ruecker — Sat, 05 Mar 2011 11:04:03 GMT

By the way: The same problem one of our customers experienced on Informix. The nullable but unique business key seems to get a problem on some databases…

The Autocommit-Mode sounds reasonable 🙂

Re: Support for Sybase Database

hrabe — Tue, 08 Mar 2011 13:21:07 GMT

Scripts for Sybase 15

activiti.sybase.create.sql


create table ACT_GE_PROPERTY (
    NAME_ varchar(64) NOT NULL,
    VALUE_ varchar(300) NULL,
    REV_ integer NULL,
    primary key (NAME_)
);

insert into ACT_GE_PROPERTY
values ('schema.version', '5.1', 1);

insert into ACT_GE_PROPERTY
values ('schema.history', 'create(5.1)', 1);

insert into ACT_GE_PROPERTY
values ('next.dbid', '1', 1);

create table ACT_GE_BYTEARRAY (
    ID_ varchar(64) NOT NULL,
    REV_ integer NULL,
    NAME_ varchar(255) NULL,
    DEPLOYMENT_ID_ varchar(64) NULL,
    BYTES_ image NULL,
    primary key (ID_)
);

create table ACT_RE_DEPLOYMENT (
    ID_ varchar(64) NOT NULL,
    NAME_ varchar(255) NULL,
    DEPLOY_TIME_ datetime NULL,
    primary key (ID_)
);

create table ACT_RU_EXECUTION (
    ID_ varchar(64) NOT NULL,
    REV_ integer NULL,
    PROC_INST_ID_ varchar(64) NULL,
    BUSINESS_KEY_ varchar(255) NULL,
    PARENT_ID_ varchar(64) NULL,
    PROC_DEF_ID_ varchar(64)  NULL,
    SUPER_EXEC_ varchar(64) NULL,
    ACT_ID_ varchar(255) NULL,
    IS_ACTIVE_ bit,
    IS_CONCURRENT_ bit,
    IS_SCOPE_ bit,
    primary key (ID_)
);

create table ACT_RU_JOB (
    ID_ varchar(64) NOT NULL,
    REV_ integer NULL,
    TYPE_ varchar(255) NOT NULL,
    LOCK_EXP_TIME_ datetime NULL,
    LOCK_OWNER_ varchar(255) NULL,
    EXCLUSIVE_ bit,
    EXECUTION_ID_ varchar(64) NULL,
    PROCESS_INSTANCE_ID_ varchar(64) NULL,
    RETRIES_ integer NULL,
    EXCEPTION_STACK_ID_ varchar(64) NULL,
    EXCEPTION_MSG_ varchar(255) NULL,
    DUEDATE_ datetime NULL,
    REPEAT_ varchar(255) NULL,
    HANDLER_TYPE_ varchar(255) NULL,
    HANDLER_CFG_ varchar(255) NULL,
    primary key (ID_)
);

create table ACT_ID_GROUP (
    ID_ varchar(64) NOT NULL,
    REV_ integer NULL,
    NAME_ varchar(255) NULL,
    TYPE_ varchar(255) NULL,
    primary key (ID_)
);

create table ACT_ID_MEMBERSHIP (
    USER_ID_ varchar(64) NOT NULL,
    GROUP_ID_ varchar(64) NOT NULL,
    primary key (USER_ID_, GROUP_ID_)
);

create table ACT_ID_USER (
    ID_ varchar(64) NOT NULL,
    REV_ integer NULL,
    FIRST_ varchar(255) NULL,
    LAST_ varchar(255) NULL,
    EMAIL_ varchar(255) NULL,
    PWD_ varchar(255) NULL,
    primary key (ID_)
);

create table ACT_RE_PROCDEF (
    ID_ varchar(64) NOT NULL,
    CATEGORY_ varchar(255) NULL,
    NAME_ varchar(255) NULL,
    KEY_ varchar(255) NULL,
    VERSION_ integer NULL,
    DEPLOYMENT_ID_ varchar(64) NULL,
    RESOURCE_NAME_ varchar(255) NULL,
    DGRM_RESOURCE_NAME_ varchar(255) NULL,
    HAS_START_FORM_KEY_ bit,
    primary key (ID_)
);

create table ACT_RU_TASK (
    ID_ varchar(64) NOT NULL,
    REV_ integer NULL,
    EXECUTION_ID_ varchar(64) NULL,
    PROC_INST_ID_ varchar(64) NULL,
    PROC_DEF_ID_ varchar(64) NULL,
    NAME_ varchar(255) NULL,
    DESCRIPTION_ varchar(255) NULL,
    TASK_DEF_KEY_ varchar(255) NULL,
    ASSIGNEE_ varchar(64) NULL,
    PRIORITY_ integer NULL,
    CREATE_TIME_ datetime NULL,
    primary key (ID_)
);

create table ACT_RU_IDENTITYLINK (
    ID_ varchar(64) NOT NULL,
    REV_ integer NULL,
    GROUP_ID_ varchar(64) NULL,
    TYPE_ varchar(255) NULL,
    USER_ID_ varchar(64) NULL,
    TASK_ID_ varchar(64) NULL,
    primary key (ID_)
);

create table ACT_RU_VARIABLE (
    ID_ varchar(64) NOT NULL,
    REV_ integer  NULL,
    TYPE_ varchar(255) NOT NULL,
    NAME_ varchar(255) NOT NULL,
    EXECUTION_ID_ varchar(64) NULL,
    PROC_INST_ID_ varchar(64) NULL,
    TASK_ID_ varchar(64) NULL,
    BYTEARRAY_ID_ varchar(64) NULL,
    DOUBLE_ float NULL,
    LONG_ numeric  NULL,
    TEXT_ varchar(255) NULL,
    TEXT2_ varchar(255) NULL,
    primary key (ID_)
);

create table ACT_HI_PROCINST (
    ID_ varchar(64) NOT NULL,
    PROC_INST_ID_ varchar(64) NOT NULL,
    BUSINESS_KEY_ varchar(255) NULL,
    PROC_DEF_ID_ varchar(64) NOT NULL,
    START_TIME_ datetime NOT NULL,
    END_TIME_ datetime NULL,
    DURATION_ numeric NULL,
    START_USER_ID_ varchar(255) NULL,
    START_ACT_ID_ varchar(255) NULL,
    END_ACT_ID_ varchar(255) NULL,
    primary key (ID_),
    unique (PROC_INST_ID_)
);

create table ACT_HI_ACTINST (
    ID_ varchar(64) NOT NULL,
    PROC_DEF_ID_ varchar(64) NOT NULL,
    PROC_INST_ID_ varchar(64) NOT NULL,
    EXECUTION_ID_ varchar(64) NOT NULL,
    ACT_ID_ varchar(255) NOT NULL,
    ACT_NAME_ varchar(255) NULL,
    ACT_TYPE_ varchar(255) NOT NULL,
    ASSIGNEE_ varchar(64) NULL,
    START_TIME_ datetime NOT NULL,
    END_TIME_ datetime NULL,
    DURATION_ numeric NULL,
    primary key (ID_)
);

create table ACT_HI_TASKINST (
    ID_ varchar(64) NOT NULL,
    PROC_DEF_ID_ varchar(64) NULL,
    TASK_DEF_KEY_ varchar(255) NULL,
    PROC_INST_ID_ varchar(64) NULL,
    EXECUTION_ID_ varchar(64) NULL,
    NAME_ varchar(255) NULL,
    DESCRIPTION_ varchar(255) NULL,
    ASSIGNEE_ varchar(64) NULL,
    START_TIME_ datetime NOT NULL,
    END_TIME_ datetime NULL,
    DURATION_ numeric NULL,
    DELETE_REASON_ varchar(255) NULL,
    primary key (ID_)
);

create table ACT_HI_DETAIL (
    ID_ varchar(64) NOT NULL,
    TYPE_ varchar(255) NOT NULL,
    PROC_INST_ID_ varchar(64) NOT NULL,
    EXECUTION_ID_ varchar(64) NOT NULL,
    TASK_ID_ varchar(64) NULL,
    ACT_INST_ID_ varchar(64) NULL,
    NAME_ varchar(255) NOT NULL,
    VAR_TYPE_ varchar(255) NULL,
    REV_ integer NULL,
    TIME_ datetime NOT NULL,
    BYTEARRAY_ID_ varchar(64) NULL,
    DOUBLE_ float NULL,
    LONG_ numeric NULL,
    TEXT_ varchar(255) NULL,
    TEXT2_ varchar(255) NULL,
    primary key (ID_)
);


create index ACT_IDX_EXEC_BUSKEY on ACT_RU_EXECUTION(BUSINESS_KEY_);
create index ACT_IDX_TASK_CREATE on ACT_RU_TASK(CREATE_TIME_);
create index ACT_IDX_IDENT_LNK_USER on ACT_RU_IDENTITYLINK(USER_ID_);
create index ACT_IDX_IDENT_LNK_GROUP on ACT_RU_IDENTITYLINK(GROUP_ID_);
create index ACT_IDX_HI_PRO_INST_END on ACT_HI_PROCINST(END_TIME_);
create index ACT_IDX_HI_PRO_I_BUSKEY on ACT_HI_PROCINST(BUSINESS_KEY_);
create index ACT_IDX_HI_ACT_INST_START on ACT_HI_ACTINST(START_TIME_);
create index ACT_IDX_HI_ACT_INST_END on ACT_HI_ACTINST(END_TIME_);
create index ACT_IDX_HI_DETAIL_PROC_INST on ACT_HI_DETAIL(PROC_INST_ID_);
create index ACT_IDX_HI_DETAIL_ACT_INST on ACT_HI_DETAIL(ACT_INST_ID_);
create index ACT_IDX_HI_DETAIL_TIME on ACT_HI_DETAIL(TIME_);
create index ACT_IDX_HI_DETAIL_NAME on ACT_HI_DETAIL(NAME_);

alter table ACT_GE_BYTEARRAY
    add constraint ACT_FK_BYTEARR_DEPL
    foreign key (DEPLOYMENT_ID_)
    references ACT_RE_DEPLOYMENT;

alter table ACT_RU_EXECUTION
    add constraint ACT_FK_EXE_PROCINST
    foreign key (PROC_INST_ID_)
    references ACT_RU_EXECUTION;

alter table ACT_RU_EXECUTION
    add constraint ACT_FK_EXE_PARENT
    foreign key (PARENT_ID_)
    references ACT_RU_EXECUTION;

alter table ACT_RU_EXECUTION
    add constraint ACT_FK_EXE_SUPER
    foreign key (SUPER_EXEC_)
    references ACT_RU_EXECUTION;

alter table ACT_ID_MEMBERSHIP
    add constraint ACT_FK_MEMB_GROUP
    foreign key (GROUP_ID_)
    references ACT_ID_GROUP;

alter table ACT_ID_MEMBERSHIP
    add constraint ACT_FK_MEMB_USER
    foreign key (USER_ID_)
    references ACT_ID_USER;

alter table ACT_RU_IDENTITYLINK
    add constraint ACT_FK_TSKASS_TASK
    foreign key (TASK_ID_)
    references ACT_RU_TASK;

alter table ACT_RU_TASK
    add constraint ACT_FK_TASK_EXE
    foreign key (EXECUTION_ID_)
    references ACT_RU_EXECUTION;

alter table ACT_RU_TASK
    add constraint ACT_FK_TASK_PROCINST
    foreign key (PROC_INST_ID_)
    references ACT_RU_EXECUTION;

alter table ACT_RU_TASK
  add constraint ACT_FK_TASK_PROCDEF
  foreign key (PROC_DEF_ID_)
  references ACT_RE_PROCDEF;

alter table ACT_RU_VARIABLE
    add constraint ACT_FK_VAR_EXE
    foreign key (EXECUTION_ID_)
    references ACT_RU_EXECUTION;

alter table ACT_RU_VARIABLE
    add constraint ACT_FK_VAR_PROCINST
    foreign key (PROC_INST_ID_)
    references ACT_RU_EXECUTION;

alter table ACT_RU_VARIABLE
    add constraint ACT_FK_VAR_BYTEARRAY
    foreign key (BYTEARRAY_ID_)
    references ACT_GE_BYTEARRAY;

alter table ACT_RU_JOB
    add constraint ACT_FK_JOB_EXCEPTION
    foreign key (EXCEPTION_STACK_ID_)
    references ACT_GE_BYTEARRAY;

create unique index ACT_UNIQ_RU_BUS_KEY on ACT_RU_EXECUTION
  (case when BUSINESS_KEY_ is null then ('pk-' + ID_) else ('bk-' + BUSINESS_KEY_) end, PROC_DEF_ID_);

create unique index ACT_UNIQ_HI_BUS_KEY on ACT_HI_PROCINST
   (case when BUSINESS_KEY_ is null then ('pk-' + ID_) else ('bk-' + BUSINESS_KEY_) end, PROC_DEF_ID_);

activiti.sybase.drop.sql


drop index ACT_RU_EXECUTION.ACT_IDX_EXEC_BUSKEY;
drop index ACT_RU_TASK.ACT_IDX_TASK_CREATE;
drop index ACT_RU_IDENTITYLINK.ACT_IDX_IDENT_LNK_USER;
drop index ACT_RU_IDENTITYLINK.ACT_IDX_IDENT_LNK_GROUP;
drop index ACT_HI_PROCINST.ACT_IDX_HI_PRO_INST_END;
drop index ACT_HI_PROCINST.ACT_IDX_HI_PRO_I_BUSKEY;
drop index ACT_HI_ACTINST.ACT_IDX_HI_ACT_INST_START;
drop index ACT_HI_ACTINST.ACT_IDX_HI_ACT_INST_END;
drop index ACT_HI_DETAIL.ACT_IDX_HI_DETAIL_PROC_INST;
drop index ACT_HI_DETAIL.ACT_IDX_HI_DETAIL_ACT_INST;
drop index ACT_HI_DETAIL.ACT_IDX_HI_DETAIL_TIME;
drop index ACT_HI_DETAIL.ACT_IDX_HI_DETAIL_NAME;

alter table ACT_GE_BYTEARRAY
  drop constraint ACT_FK_BYTEARR_DEPL;

alter table ACT_RU_EXECUTION
  drop constraint ACT_FK_EXE_PROCINST;

alter table ACT_RU_EXECUTION
  drop constraint ACT_FK_EXE_PARENT;

alter table ACT_RU_EXECUTION
  drop constraint ACT_FK_EXE_SUPER;

alter table ACT_ID_MEMBERSHIP
  drop constraint ACT_FK_MEMB_GROUP;

alter table ACT_ID_MEMBERSHIP
  drop constraint ACT_FK_MEMB_USER;

alter table ACT_RU_IDENTITYLINK
  drop constraint ACT_FK_TSKASS_TASK;

alter table ACT_RU_TASK
 drop constraint ACT_FK_TASK_EXE;

alter table ACT_RU_TASK
 drop constraint ACT_FK_TASK_PROCINST;

alter table ACT_RU_TASK
 drop constraint ACT_FK_TASK_PROCDEF;

alter table ACT_RU_VARIABLE
  drop constraint ACT_FK_VAR_EXE;

alter table ACT_RU_VARIABLE
  drop constraint ACT_FK_VAR_PROCINST;

alter table ACT_RU_VARIABLE
  drop constraint ACT_FK_VAR_BYTEARRAY;

alter table ACT_RU_JOB
  drop constraint ACT_FK_JOB_EXCEPTION;

drop table ACT_GE_PROPERTY;
drop table ACT_GE_BYTEARRAY ;
drop table ACT_RE_DEPLOYMENT;
drop table ACT_RU_EXECUTION;
drop table ACT_ID_GROUP;
drop table ACT_ID_MEMBERSHIP;
drop table ACT_ID_USER;
drop table ACT_RU_JOB;
drop table ACT_RE_PROCDEF;
drop table ACT_RU_TASK;
drop table ACT_RU_IDENTITYLINK;
drop table ACT_RU_VARIABLE;
drop table ACT_HI_PROCINST;
drop table ACT_HI_ACTINST;
drop table ACT_HI_TASKINST;
drop table ACT_HI_DETAIL;

To support these scripts is it necessary to add autocommit="true" at sql on target name="create.db.schema"

Re: Support for Sybase Database

transconnect — Fri, 06 May 2011 14:05:24 GMT

Hi all,

I have ported activiti 5.5 to Sybase Adaptive Server Anywhere (>= Rel 9). But i'm not familar with Maven and though a have not followed the tips explained in the wiki. If subject of interest, I can post these scripts here.

But my problem was, that the Sybase JDBC driver jConnect does not Support JDBC-conform BLOB/CLOB access.
Though I have changed all mybatis mappings like that:


jdbcType=BLOB, typeHandler=org.apache.ibatis.type.ByteArrayTypeHandler

Another problem is, that the used alias name membership refers to a reserved word. Though I have changed this to membershp to solve the problem.

In the global mappings.xml I have added the type handlers


               <typeHandlers>
                  <typeHandler javaType="_byte[]" jdbcType="LONGVARBINARY" handler="org.apache.ibatis.type.ByteArrayTypeHandler"/>
                  <typeHandler javaType="string"  jdbcType="LONGVARCHAR"   handler="org.apache.ibatis.type.StringTypeHandler"/>
               </typeHandlers>

With best regards, Torsten

Re: Support for Sybase Database

hrabe — Tue, 24 May 2011 09:23:57 GMT

Patches for version 5.5 attached to http://jira.codehaus.org/browse/ACT-726

@Torsten
- How you solve the constraint problem with nullable columns?
- How you find the problem with the reserved key word membership? All maven tests run successfully on sybase.

I used the sybase sql type image instead of blob/clob. So no changes to the mapping are necessary.

Re: Support for Sybase Database

transconnect — Mon, 30 May 2011 06:44:59 GMT

Hi hrabe,

How you solve the constraint problem with nullable columns?

No problem on ASA using
create index ACT_UNIQ_RU_BUS_KEY on ACT_RU_EXECUTION (PROC_DEF_ID_, BUSINESS_KEY_);create index ACT_UNIQ_HI_BUS_KEY on ACT_HI_PROCINST (PROC_DEF_ID_, BUSINESS_KEY_);

How you find the problem with the reserved key word membership? All maven tests run successfully on sybase.

After startup, some action (I think the deployment of BARs) failed with an error message pointing to a problem with reserved words.

I used the sybase sql type image instead of blob/clob. So no changes to the mapping are necessary.

I used also Image-Type in the database column in ASA. But the mybatis configuration uses Blob/Clob, the jConnect driver does not support. Dou You have used jConnect or jTDS? On ASA jTDS does not work, only jConnect.

Re: Support for Sybase Database

hrabe — Mon, 30 May 2011 11:07:16 GMT

No problem on ASA using
create index ACT_UNIQ_RU_BUS_KEY on ACT_RU_EXECUTION (PROC_DEF_ID_, BUSINESS_KEY_);create index ACT_UNIQ_HI_BUS_KEY on ACT_HI_PROCINST (PROC_DEF_ID_, BUSINESS_KEY_);

You have to a use unique index. So you did not solve the constraint problem with nullable columns.

How you find the problem with the reserved key word membership?

Membership is only on ASA a reserved keyword not on ASE.

jConnect driver does not support

I used jTDS. On ASE jTDS works.

Do you need to use ASA?

Re: Support for Sybase Database

transconnect — Mon, 30 May 2011 11:42:50 GMT

Hi hrabe,

You have to a use unique index. So you did not solve the constraint problem with nullable columns.

You are right, this is not accurate. But I have assumed, Activiti works fine on other DBs, though the Java code is working and no unique constraint violation will occur.

Do you need to use ASA?

Yes, we have OEMed that DB in our products.