Showing results for 
Search instead for 
Do you mean 

SQL Scripts for SEC_SESSION and WF_FILTERSETTINGS

by Technical Evangelist 2 weeks ago - edited 2 weeks ago (20 Views)

When you work with Workflows you typically need the following tables created in the database schema used by a Workflow connection:

  • SEC_SESSION - for storing session information and variables
  • WF_FILTERSETTINGS - for persisting user's filter settings done in FormFilter(s)

Please see below the DDL scripts for creating such tables.

Oracle

--------------------------------------------------------
--  DDL for Table SEC_SESSION
--------------------------------------------------------
CREATE TABLE "SEC_SESSION" 
(
   "SESSIONID" VARCHAR2(36 CHAR), 
   "NAME" VARCHAR2(128 CHAR), 
   "SESSIONVALUE" VARCHAR2(4000 CHAR), 
   "SESSIONDATE" DATE DEFAULT sysdate, 
   "DATATYPE" VARCHAR2(512 CHAR), 
   "SESSIONOBJECT" BLOB
);

--------------------------------------------------------
--  Constraints for Table SEC_SESSION
--------------------------------------------------------
ALTER TABLE "SEC_SESSION" ADD CONSTRAINT "PK_SESSION" PRIMARY KEY ("SESSIONID", "NAME");
ALTER TABLE "SEC_SESSION" MODIFY ("SESSIONID" NOT NULL ENABLE); 
ALTER TABLE "SEC_SESSION" MODIFY ("NAME" NOT NULL ENABLE); 
ALTER TABLE "SEC_SESSION" MODIFY ("SESSIONDATE" NOT NULL ENABLE); 
ALTER TABLE "SEC_SESSION" MODIFY ("DATATYPE" NOT NULL ENABLE);

--------------------------------------------------------
--  DDL for Table WF_FILTERSETTINGS
--------------------------------------------------------
CREATE TABLE "WF_FILTERSETTINGS" 
(
   "ID" VARCHAR2(36 CHAR), 
   "WORKFLOWUSER" VARCHAR2(50 CHAR), 
   "WORKFLOW" VARCHAR2(30 CHAR), 
   "FORM" VARCHAR2(50 CHAR), 
   "FILTER" VARCHAR2(512 CHAR), 
   "NAME" VARCHAR2(128 CHAR) DEFAULT 0, 
   "FILTEROBJECT" BLOB
);
   
--------------------------------------------------------
--  Constraints for Table WF_FILTERSETTINGS
--------------------------------------------------------
ALTER TABLE "WF_FILTERSETTINGS" MODIFY ("ID" NOT NULL ENABLE);
ALTER TABLE "WF_FILTERSETTINGS" MODIFY ("WORKFLOW" NOT NULL ENABLE);
ALTER TABLE "WF_FILTERSETTINGS" MODIFY ("FORM" NOT NULL ENABLE);
ALTER TABLE "WF_FILTERSETTINGS" MODIFY ("NAME" NOT NULL ENABLE);
ALTER TABLE "WF_FILTERSETTINGS" ADD CONSTRAINT "WF_FILTERSETTINGS_PK" PRIMARY KEY ("ID");

SQL Server

CREATE TABLE [dbo].[WF_FILTERSETTINGS](
	[ID] [varchar](36) NOT NULL,
	[WORKFLOWUSER] [varchar](50) NULL,
	[WORKFLOW] [varchar](30) NOT NULL,
	[FORM] [varchar](50) NOT NULL,
	[FILTER] [varchar](512) NULL,
	[NAME] [varchar](128) NOT NULL,
	[FILTEROBJECT] [varbinary](max) NULL,
 CONSTRAINT [WF_FILTERSETTINGS_PK] PRIMARY KEY CLUSTERED ([ID] ASC) 
) 
 
CREATE TABLE [dbo].[SEC_SESSION](
	[SESSIONID] [varchar](36) NOT NULL,
	[NAME] [varchar](128) NOT NULL,
	[SESSIONVALUE] [varchar](4000) NULL,
	[SESSIONDATE] [datetime2](0) NOT NULL,
	[DATATYPE] [varchar](512) NOT NULL,
	[SESSIONOBJECT] [varbinary](max) NULL,
 CONSTRAINT [PK_SESSION] PRIMARY KEY CLUSTERED ([SESSIONID] ASC, [NAME] ASC)
) 
GO

ALTER TABLE [dbo].[SEC_SESSION] ADD  DEFAULT (sysdatetime()) FOR [SESSIONDATE]
GO

PostgreSQL

 

-- PostgreSQL 9.5/9.6
-- table sec_session
CREATE TABLE public.sec_session
(
    sessionid character varying(36) NOT NULL,
    name character varying(128) NOT NULL,
    sessionvalue character varying(4000),
    sessiondate timestamp without time zone NOT NULL DEFAULT ('now'::text)::timestamp without time zone,
    datatype character varying(512) NOT NULL,
    sessionobject bytea,
    CONSTRAINT pk_session PRIMARY KEY (sessionid, name)
)
TABLESPACE pg_default;

ALTER TABLE public.sec_session OWNER to postgres;

CREATE INDEX sec_session_idx
    ON public.sec_session USING btree
    (sessionid)
    TABLESPACE pg_default;   
    
-- table wf_filtersettings
CREATE TABLE public.wf_filtersettings
(
    id character varying(36) NOT NULL,
    workflowuser character varying(50) COLLATE pg_catalog."default",
    workflow character varying(30) NOT NULL,
    form character varying(50) NOT NULL,
    filter character varying(512) COLLATE pg_catalog."default",
    name character varying(128) NOT NULL DEFAULT 0,
    filterobject bytea,
    CONSTRAINT wf_filtersettings_pk PRIMARY KEY (id)
)
TABLESPACE pg_default;

ALTER TABLE public.wf_filtersettings OWNER to postgres;

 

Contributors